--                                
--  Paul Brown
--
--    This is the script for all the Level 0 stuff which will be
--   included higher up in the schema. 
--
--   $Header: /usr/local/devel/montage/contrib/BigSur/Schema/RCS/bigsur_base.msql,v 1.5 1994/12/05 22:39:01 pbrown Exp pbrown $
--
-- Description - FGDC June 8th 1994, Section 1.2
--
--    This relation is to be included in relations where some description
--   is required. This mechanism allows us to search through the
--   broad collection of objects stores in the database for
---  descriptions which are 'interesting' by some criterion.
--
CREATE TABLE Description
OF NEW TYPE Description_t
(
  Abstract       text,          -- 1.2.1
  Purpose        text,          -- 1.2.2
  Supplemental   text           -- 1.2.3
);
--
--  Thesaurus - FGDC June 8th 1994.
--
--   FGDC requires that Keywords be stored for Identification_Information
--  objects. This relation stores the list of valid Keywords - which may
--  be added to of course - so as to maintain some integrity and an
--  explaination for each Keyword.
--
CREATE TABLE Thesaurus
OF NEW TYPE Thesaurus_t
(
  Keyword        text ,          -- Everywhere 1.6x especially
  Thesaurus_Code text            -- Everywhere 1.6x expecially
) UNDER Description;
--
--
--   Graphic - FGDC June 8th 1994. 
--
--     This relation allows the schema to store some Graphic, like a
--    photo or the like, without specifying any content or grid
--    information. This is handy for digital photos, etc.
--
CREATE TABLE Graphic
OF NEW TYPE Graphic_t
(
  Browse_Graphic_File_Name         dlobh,                 -- 1.10.1
  Browse_Graphic_File_Description  text,                  -- 1.10.2
  Browse_Graphic_File_Type         text                   -- 1.10.3
); 
--
--  Codes - These are used all over the place. 
--
--    The Codes mechanism allows us to record a multitude of desirable
--   schema facts in an organized way. This Codes mechanism is used to
--   replace many FGDC things recording access and user constraints.
--
--
CREATE TABLE Code
OF NEW TYPE Code_t
(
   Code_Type            text   ,
   Code_Value           text   
) UNDER Description;
--
--
CREATE TABLE Security_Code
OF NEW TYPE Security_Code_t
UNDER Code;
--
--
--    A kind of hack at Section 10. There is a 1{Contact_Address}n attribute
--  in this table, so for expedient selects I've included a count of rows
--  in the Address array.
--
--  Contact_Information = 
--                        Primary_Contact_Person +
--                        (Primary_Contact_Organization) +
--                        (Contact_Position) +
--			  (Address_Row_Count +
--                         1{Address}n) +
--                        (City) +
--                        (State_Or_Province) +
--                        (Postal_Code) +
--                        (Contact_Phone) +
--                        (Contact_Fax) +
--                        (Contact_E-Mail) +
--                        (Hours_of_Service) +
--                        (Description);
--
CREATE TABLE Contact_Information
OF NEW TYPE Contact_Information_t
(
  Primary_Contact_Person	text	NOT NULL,
  Primary_Contact_Organization	text	NOT NULL,
  Contact_Position		text	NOT NULL,
  Address_Row_Count		integer,   -- The size of the subsequent array.
  Address			text[],
  City				text,
  State_or_Province		text,
  Postal_Code			text,
  Contact_Phone	  		text,
  Contact_Fax			text,
  Contact_E_Mail		text,
  Hours_of_Service		text,
  primary key (Primary_Contact_Person, Primary_Contact_Organization, Contact_Position)
) UNDER Description;
--
--
--
CREATE TABLE NextId (
      NextMaxId      integer NOT NULL
);
--
INSERT INTO NextId (NextMaxId) VALUES ( 1234567 );
--
CREATE FUNCTION GetNextId
RETURNS INTEGER AS
BEGIN
  UPDATE NextId
  USING( lock = table exclusive )
     SET NextMaxId = NextMaxId + 7654321;
  SELECT unique NextMaxId
    FROM NextId;
END;
--
CREATE RULE NextIdSetBack
ON UPDATE TO NextId WHERE new.NextMaxId > 10006721
DO INSTEAD
  UPDATE NextId
   USING ( lock = table exclusive )
     SET NextMaxId = new.NextMaxId - 10006721;
--
--  Permissions - etc
--
GRANT ALL ON Description TO PUBLIC;
GRANT USAGE ON TYPE Description_t TO PUBLIC;
GRANT ALL ON Thesaurus TO PUBLIC;
GRANT USAGE ON TYPE Thesaurus_t TO PUBLIC;
GRANT ALL ON Graphic TO PUBLIC;
GRANT USAGE ON TYPE Graphic_t TO PUBLIC;
GRANT ALL ON Code TO PUBLIC;
GRANT USAGE ON TYPE Code_t TO PUBLIC;
GRANT ALL ON Security_Code TO PUBLIC;
GRANT USAGE ON TYPE Security_Code_t TO PUBLIC;
GRANT ALL ON Contact_Information TO PUBLIC;
GRANT USAGE ON TYPE Contact_Information_t TO PUBLIC;
GRANT USAGE ON FUNCTION GetNextId() TO PUBLIC;
--