-- ##########################################################################
--
--
--     This is the script which generates the CRSEO version of the
--    BigSur schema. This is based on the BigSur schema which lives
--    in ;
--
--  triplerock.cs.Berkeley.EDU:/usr/local/devel/montage/contrib/BigSur/Schema
--
--     Where necessary, I will create a table which inherits the BigSur
--    stuff, and a view over that table to make it approach the
--    intuition of the application's users.
--     
--    Paul Brown
--
--    $Header: /usr/local/devel/montage/contrib/BigSur/Raster/MSQL/RCS/Raster.msql,v 1.4 1994/12/15 22:36:59 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                          - Changed. The CellValue
--                                        corresponds to 
--                                        Layers.
--
CREATE TABLE SatelliteLayer
OF NEW TYPE SatelliteLayer_t
(
   platform		text			NOT NULL,
   sensor		text			NOT NULL,
   channelNumber	text			NOT NULL,
   measurementMin	double precision,
   measurementMax	double precision
) UNDER CellValue;
--
GRANT ALL ON SatelliteLayer TO PUBLIC;
GRANT USAGE ON TYPE SatelliteLayer_t TO PUBLIC;
--
--
--   SatelliteResolution:	- Tricky. In order to maintain both
--                                an application view of the data, and
--                                a BigSur view, we need to maintain
--                                a synchronization facility.
--
CREATE TABLE SatelliteRes
OF NEW TYPE SatelliteRes_t
(
	SatelliteLayer		ref(SatelliteLayer_t) 	NOT NULL,
	CellVector		ref(CellVector_t)	NOT NULL,
	seqNum			integer			NOT NULL
);
--
GRANT ALL ON SatelliteRes TO PUBLIC;
GRANT USAGE ON TYPE SatelliteRes_t TO PUBLIC;
--
--    I need to keep these in sync. The SatelliteRes table is 
--   a subtype of the CellResolution table in it's semantic. Using the rules 
--   this way enables us to maintain a BigSur view of the Data.
--
--  
CREATE RULE KeepSRSynchedInsert
ON INSERT TO SatelliteRes
DO INSERT INTO CellResolution
(       CellVector,
        CellValue,
        seqNum )
SELECT  ref(SV),
        ref(SB),
        new.seqNum
  FROM CellVector SV, SatelliteLayer SB
 WHERE SV.oid = deref(new.CellVector).oid
   AND SB.oid = deref(new.SatelliteLayer).oid;
--
--
CREATE RULE KeepSRSatelliteResSynchedUpdate
ON UPDATE TO SatelliteRes
DO UPDATE CellResolution CR
      SET CellVector = ( SELECT UNIQUE ref(SV)
                           FROM CellVector SV
                          WHERE SV.oid = deref(new.CellVector).oid),
          CellValue =  ( SELECT UNIQUE ref(SB)
                           FROM SatelliteLayer SB
                          WHERE SB.oid = deref(new.SatelliteLayer).oid),
          seqNum    =  new.seqNum
  WHERE deref(CR.CellVector).oid        = deref(current.CellVector).oid
    AND deref(CR.CellValue).oid         = deref(current.SatelliteLayer).oid
    AND CR.seqNum                       = current.seqNum;
--
--
--   BigSurGrid -   No Changes
--
CREATE TABLE RasterData
OF NEW TYPE RasterData_t
(
	rasterHeader	text
) UNDER Description, BigSurObject;
--
GRANT ALL ON RasterData TO PUBLIC;
GRANT USAGE ON TYPE RasterData_t TO PUBLIC;
--
--   Raster			- This is the top level structure
--                                        which 'ties it all together'. In
--                                        the same way that the 
--  
CREATE TABLE Raster
OF NEW TYPE Raster_t
(
  BigSurGrid		ref(BigSurGrid_t)		NOT NULL,
  CellVector		ref(CellVector_t)		NOT NULL,
  Map_Projection_Name	text				NOT NULL
) UNDER RasterData;
--
GRANT ALL ON Raster TO PUBLIC;
GRANT USAGE ON TYPE Raster_t TO PUBLIC;
--
--    Need to keep this in synch with the BigSurDataObject relation.
--
CREATE RULE SynchSRInsert
ON INSERT TO Raster
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.BigSurGrid).oid),
        ( SELECT UNIQUE ref(CV)
	    FROM CellVector CV
                   WHERE CV.oid = deref(new.CellVector).oid)
);
--
--  UPDATE  - Same as for the INSERT. Note that I'm going the entire
--            set of old attributes here. If none such exists, this should
--            fail invisibly.
--
CREATE RULE SynchSRUpdate
ON UPDATE TO Raster
DO UPDATE BigSurDataObject
      SET Abstract 	= new.Abstract,
	  Purpose  	= new.Purpose,
	  Supplemental 	= new.Supplemental,
	  MetaData 	= ( SELECT UNIQUE ref(M)
                              FROM BigSurAssoc M
                             WHERE M.oid = deref(new.MetaData).oid),
	  BigSurObject	= new.BigSurObject,
	  fileOffSet    = new.fileOffSet,
	  storageFormat	= new.storageFormat,
	  BigSurGrid	= (SELECT UNIQUE ref(G)
            		     FROM BigSurGrid G
           		    WHERE G.oid = deref(new.BigSurGrid).oid),
	  CellVector    = ( SELECT UNIQUE ref(CV)
            		      FROM CellVector CV
			     WHERE CV.oid = deref(new.CellVector).oid)
    WHERE Abstract	        = current.Abstract
      AND Purpose               = current.Purpose
      AND Supplemental          = current.Supplemental
      AND deref(MetaData).oid   = deref(current.MetaData).oid
      AND BigSurObject          = current.BigSurObject
      AND fileOffSet            = current.fileOffSet
      AND storageFormat         = current.storageFormat
      AND deref(BigSurGrid).oid = deref(current.BigSurGrid).oid
      AND deref(CellVector).oid = deref(current.CellVector).oid;
--
--