--##########################################################################
--
--Author:Paul Brown
--
--   This file contains the scripts to create the schema for the GCM
--  application. It's data model is based on the Schema concocted at
--  UCLA and UCSB by Jean Anderson, Paul Brown, Ed Mesrobian, Joe Spahr
--  and Keith Sklower. I'm using the BigSur schema as the basis for
--  this schema. 
--
--    The objective is to use the BigSur base schema but produce something
--  that looks as much like the original as possible. This isn't going
--  to be easy. I'll create a table which inherits the BigSur stuff, and
--  create a view of it which will allow insertion.
--
-- $Header: /usr/local/devel/montage/contrib/BigSur/GCM/RCS/GCM.msql,v 1.2 1994/12/09 02:17:23 pbrown Exp pbrown $
--
--###########################################################################
--
SET SCHEMA BigSur;
--
-- Basics: 						bigsur_base.msql
--
--   Description			- Kept the same
--
--   Thesaurus				- Kept the same
--
--   Graphic				- Kept the same
--
--   Code 				- Kept the same
--
--   Security_Code 			- Kept the same
--
--   Contact_Information		- Kept the same
--
-- Section 2:						bigsur_section2.msql
--
--   Citation				- Kept the same
--
-- Section 7:						bigsur_section7.msql
--
--   Metadata_Reference_Information	- Kept the same
--
-- Lineage:						bigsur_lineage.msql
--
--   Process 				- Kept the same
--
--   Parameter				- Kept the same
--
--   Lineage_Param 			- Kept the same
--
-- Section 1:						bigsur_section1.msql
-- 
--   Identification_Information		- Kept the same
--
--   Keyword_Instance			- Kept the same
--
--   Identification_Graphic		- Kept the same
--
--   MetaData				- Kept the same
--
--   BigSurAssoc			- Kept the same
--
-- Cell Structure: 				bigsur_cellstruct.msql
--
--   CellValue                          - 
--
CREATE TABLE GCMVariable
OF NEW TYPE GCMVariable_t
(
class		text		NOT NULL
) UNDER CellValue;
GRANT ALL ON GCMVariable TO PUBLIC;
GRANT USAGE ON TYPE GCMVariable_t TO PUBLIC;
--
CREATE VIEW Variable
( 	attributeName, 
	instancePrimitive, 
	elementsPerValue,
	attributeUnits, 
	class,
	Abstract, 
	Purpose, 
	Supplemental )
AS SELECT	name,
		instanceType,
		elementsPerValue,
		units,
		class,
		Abstract,
		Purpose,
		Supplemental
 FROM GCMVariable;
--
GRANT ALL ON Variable TO PUBLIC;
--
-- CellVector:
--
CREATE TABLE GCMCompoundVariable
OF NEW TYPE GCMCompoundVariable_t
(compoundName		text		NOT NULL,
 variableCount	integer		NOT NULL CHECK ( variableCount > 0 ),
 indexingScheme	text		NOT NULL
) UNDER CellVector;
--
GRANT ALL ON GCMCompoundVariable TO PUBLIC;
GRANT USAGE ON TYPE GCMCompoundVariable_t TO PUBLIC;
--
--
CREATE VIEW CompoundVariable
( 	compoundName, 
	variableCount, 
	indexingScheme,
	Abstract, 
	Purpose, 
	Supplemental )
AS SELECT compoundName,
		variableCount,
		indexingScheme,
		Abstract,
		Purpose,
		Supplemental
 FROM GCMCompoundVariable;
--
GRANT ALL ON CompoundVariable TO PUBLIC;
--
-- CellResolution:
--
--    This is a tricky part. Because the application will only be able to
--   see the BigSur specified attributes for the ref()ed relations, I need
--   to re-define the Resolution table to allow me to deref() to the
--   application's relations. 
--
--    Then, in order to keep the BigSur stuff synched with my new higher
--   level structures I've defined a relational integrity rule which
--   allows me to keep the BigSur res table in synch with the application's.
--
--    The need for this apparent complication becomes clear once you start
--   loading data. All that you can see in the deref() is the attributes in
--   the BigSur schema, as the type specified in the BigSur ref is the
--   type of the BigSur relation. The type of the application's relation is
--   the one you need to use in the application's res table. I simply
--   can't do the ref().
--
--    Recall that the INSERT, UPDATE and DELETE Rules are required to
--   keep these things 'synched'.
--
--
CREATE TABLE GCMVariableCompRes
OF NEW TYPE GCMVariableCompRes_t
(
   GCMVariable		ref(GCMVariable_t)		NOT NULL,
   GCMCompoundVariable	ref(GCMCompoundVariable_t)	NOT NULL,
   seqNum		integer				NOT NULL,
   variableUncertainty 	double precision,
   variableScalarA 	double precision,
   variableScalarB 	double precision
);
--
GRANT ALL ON GCMVariableCompRes TO PUBLIC;
GRANT USAGE ON TYPE GCMVariableCompRes_t TO PUBLIC;
--
--  And to keep it in synch;
--
CREATE RULE KeepVCResSynchedInsert
ON INSERT TO GCMVariableCompRes
DO INSERT INTO CellResolution
(	CellVector,
	CellValue,
	seqNum )
SELECT 	ref(VE),
	ref(VA),
	new.seqNum
  FROM CellVector VE, CellValue VA
 WHERE VE.oid = deref(new.GCMCompoundVariable).oid
   AND VA.oid = deref(new.GCMVariable).oid;
--
CREATE RULE KeepVCResSynchedUpdate
ON UPDATE TO GCMVariableCompRes
DO UPDATE CellResolution CR
      SET CellVector = ( SELECT UNIQUE ref(VE) 
                           FROM CellVector VE
                          WHERE VE.oid = deref(new.GCMCompoundVariable).oid),
          CellValue =  ( SELECT UNIQUE ref(VA) 
                           FROM CellValue VA
                          WHERE VA.oid = deref(new.GCMVariable).oid),
          seqNum    =  new.seqNum
  WHERE deref(CR.CellVector).oid        = deref(current.GCMCompoundVariable).oid
    AND deref(CR.CellValue).oid         = deref(current.GCMVariable).oid
    AND CR.seqNum			= current.seqNum;
--
--  Delete. Sheesh
--
CREATE VIEW VariableCompRes
( 	CompoundVariable, 
	VariableId, 
	variableUncertainty,
	variableScalarA, 
	variableScalarB, 
	seqNum)
AS SELECT 	GCMCompoundVariable,
		GCMVariable,
		variableUncertainty,
		variableScalarA,
		variableScalarB,
		seqNum
 FROM GCMVariableCompRes;
--
GRANT ALL ON VariableCompRes TO PUBLIC;
--
--
-- Axes and Grids:
--
-- There are some features of the GCM grid which aren't present in
--the BigSur grid, and some BigSur Axis/Grid features unnecesary
--in the GCM. 
--
--      BigSurAxis -  Need to include the attributes which will handle the
--                    recti-linear grids.
--
CREATE TABLE GCMAxis
OF NEW TYPE GCMAxis_t
(
	axisValues		arrayof(double precision)
) UNDER BigSurAxis;
--
GRANT ALL ON GCMAxis TO PUBLIC;
GRANT USAGE ON TYPE GCMAxis_t TO PUBLIC;
--
--
CREATE VIEW Axis
( 	axisName, 
  	axisType, 
	axisinfoUnits, 
	axisInfoSpacing, 
	axisOrigin,
	axisAccuracy, 
	axisCount, 
	axisValues,
	Abstract, 
	Purpose, 
	Supplemental )
AS SELECT	axisName,
		axisType,
		units,
		gridSpacing,
		positionOfOrigin,
		axisAccuracy,
		gridDimensions,
		axisValues,
		Abstract,
		Purpose,
		Supplemental
FROM GCMAxis;
--
GRANT ALL ON Axis TO PUBLIC;
--
--   BigSurGrid:      - Will include a redundant count of the Grid's rank,
--                      which will always be the same as the 
--                      BigSurGrid.gridAxisCount except where the recti-linear
--                      grid is used.
--
CREATE TABLE GCMGrid
OF NEW TYPE GCMGrid_t
UNDER BigSurGrid;
--
GRANT ALL ON GCMGrid TO PUBLIC;
GRANT USAGE ON TYPE GCMGrid_t TO PUBLIC;
--
--
CREATE VIEW ProductGrid
( 	productGridName, 
	productGridAxisCount, 
	Abstract, 
	Purpose, 
	Supplemental )
AS SELECT 	gridName,
		gridAxisCount,
		Abstract,
		Purpose,
		Supplemental
 FROM GCMGrid;
--
GRANT ALL ON ProductGrid TO PUBLIC;
--
--
--  GridAxisRes:        - Same structure, but will need to keep a 'synched'
--                        copy in the other tables.
--
CREATE TABLE GGridAxisRes
OF NEW TYPE GCMGridAxisRes_t
(
	ProductGrid	ref(GCMGrid_t)	NOT NULL,
	Axis		ref(GCMAxis_t)	NOT NULL,
	seqNum		integer		NOT NULL
);
--
GRANT ALL ON GGridAxisRes TO PUBLIC;
GRANT USAGE ON TYPE GCMGridAxisRes_t TO PUBLIC;
--
--  On Insert
--
CREATE RULE KeepGAResSynched
ON INSERT TO GGridAxisRes
DO INSERT INTO GridAxisRes
( BigSurGrid, BigSurAxis, seqNum )
SELECT	ref(G),
	ref(A),
	new.seqNum
FROM BigSurGrid G, BigSurAxis A
 WHERE G.oid = deref(new.ProductGrid).oid
   AND A.oid = deref(new.Axis).oid;
--
--  On UPDATE
--
CREATE RULE KeepGAResSynchedUpdate
ON UPDATE TO GGridAxisRes
DO UPDATE GridAxisRes GAR
      SET BigSurGrid = ( SELECT UNIQUE ref(G)
                           FROM BigSurGrid G
                          WHERE G.oid = deref(new.ProductGrid).oid),
          BigSurAxis = ( SELECT UNIQUE ref(A)
                           FROM BigSurAxis A
                          WHERE A.oid = deref(new.Axis).oid),
          seqNum    =  new.seqNum
  WHERE deref(GAR.BigSurGrid).oid       = deref(current.ProductGrid).oid
    AND deref(GAR.BigSurAxis).oid       = deref(current.Axis).oid
    AND GAR.seqNum                      = current.seqNum;
--
-- 
CREATE VIEW GCMGridAxisRes
( 	productGrid, 
	axis, 
	seqNum )
AS SELECT ProductGrid,
	Axis,
	seqNum
 FROM GGridAxisRes;
--
GRANT ALL ON GCMGridAxisRes TO PUBLIC;
--
--  DataFile:
--
--   BigSurObject  -- No Change
--
CREATE TABLE GCMDataObject
OF NEW TYPE GCMDataObject_t
(
	ProductGrid		ref(GCMGrid_t)			NOT NULL,
	CompoundVariable	ref(GCMCompoundVariable_t) 	NOT NULL
) UNDER Description, BigSurObject;
--
--  I Need to keep this in synch too. An Insert into here goes into
--  the BigSurDataObject Table. This will be a bit tricky, so
--  hang on.
--
CREATE RULE SynchGCMInsert
ON INSERT TO GCMDataObject
DO INSERT INTO BigSurDataObject
(
	Abstract,
        Purpose,
        Supplemental,
	MetaData,
	BigSurObject,
	fileOffSet,
	storageFormat,
	BigSurGrid,
	CellVector
) VALUES
(		new.Abstract,
		new.Purpose,
		new.Supplemental,
		new.MetaData,
		new.BigSurObject,
		new.fileOffSet,
		new.storageFormat,
		( SELECT UNIQUE ref(G)
		    FROM BigSurGrid G
                   WHERE G.oid = deref(new.ProductGrid).oid),
		( SELECT UNIQUE ref(CV)
		    FROM CellVector CV
                   WHERE CV.oid = deref(new.CompoundVariable).oid)
);
--
--  Ugh! UPDATE!
--