--#####################################################################
--
-- File: triplerock.CS.Berkeley.EDU:/private/S2K/Meta/FGDC/Src/FGDC_Meta3.msql
--
-- Metadata_Reference_Information
--
--   Information on the currentness of the metadata information, and 
--   the responsible party.
--
--  Metadata_Reference_Information =
--                                     Metadata_Date +
--                                    (Metadata_Review_Date +
--                                     (Metadata_Future_Review_Date )) +
--                                    (Metadata_Contact) +
--                                    Metadata_Standard_Name +
--                                    Metadata_Standard_Version;
-- 
--   Wow! Metadata for the Metadata! This is making my brain ache!!
--
--   Metadata_Date               = date; (date)
--
--   Meta_Data_Review_Date       = date; (date)
--
--   Metadata_Future_Review_Date = date; (date)
--
--   Metadata_Contact            = 
--                                 [Contact_Person_Primary |
--                                   Contact_Organization_Primary ] +
--                                 (Contact_Position) +
--                                 Contact_Mail_Address +
--                                 (Contact_Physical_Address) +
--                                 1{Contact_Voice_Telephone}n +
--                                 (0{Contact_Fax_Telephone}n +
--                                  0{Contact_EMail_Address}n) +
--                                 (Contact_Instructions);
--
--   This is silly. I'll simply store the name of the person, and
--   their position within the organisation. That way there will
--   be a contact id, and I can satidfy the needs of the schema.
--
--   Contact_Person_Primary and Contact_Organization_Primary becomes
--   simply Contact. There will be a table of possible contacts, and
--   the information is derived from them. This could be derived from 
--   some other table of sites etc. I'll pull position into this
--   as well.
--
begin transaction;
--
--   Zip_Codes
--
--            This is the relation to hold a set of valid Zip codes,
--            simply to QA the address. This would be a very lot T
--            rate table. The Status attribute is used when someone
--            enters an unseen Zip.Town.State combination. They will
--            be warned (of course) and if they proceed a new entry
--            will be made in this table provisionally. This is the
--            hook for QA work on the Address, as most Towns know
--            where the named organisation is, even if the street
--            address is totally bogus. If we get the mail to the
--            right Zip, we're pretty close.
--
create table Zip_Codes
of new type Zip_Code_t
(
  Town    text not null,
  State   varchar(4) not null,
  Zip     text not null,
  Status  ref(Code_t)        -- For when a new address with a Zip that doesn't
                             -- seem to exist is added. This will enable tracking
                             -- of QA? addressed. 
);
--
--  Contact_Addresses
--
--         A contact may have several addresses. Note that I have 
--         de-normalised this, assuming that addresses are pretty
--         distinct, at least in the Line_1.
--
create table Contact_Addresses 
of new type Contact_Address_t
(
  Code    ref(Code_t),        -- Type of address; PHYSICAL, MAILING
  Line_1  text not null,
  Zip_Code ref(Zip_Code_t)    -- Zip Code;
) under Valid_Dates;          -- When is this valid
--
--
create table Contact_Phones
of new type Contact_Phone_t
(
  Code    ref(Code_t),           -- Be this WORK, HOME, FAX, TELEX etc 
  Phone   text
) under Valid_Dates;             -- When is this valid
--
--  Now, the Contacts, in detail;
--
create table Contacts
of new type Contact_t
(
  Name      text not null,
  Posit     text,
  Address   setof(Contact_Address_t) virtual,
  Phone     setof(Contact_Phone_t)   virtual
);
--
-- A couple of necessary resolution tables;
--
--
create table Address_Resol
of new type Address_Resol_t
(
  Contact    oid   not null,
  Address    oid   not null
);
--
-- The integrity functions;
--
-- create rule R12
-- on insert to Address_Resol
-- where exists ( select 1
--                  from Address_Resol A
--                 where A.Contact = new.Contact
--                   and A.Address = new.Address )
-- do instead nothing;
--
-- create rule R13
-- on update to Address_Resol
-- where exists ( select 1
--                  from Address_Resol A
--                 where A.Contact = new.Contact
--                   and A.Address = new.Address )
-- do instead nothing;
--
--  And the function to satisfy the thing;
--
create function Address (Contact_t)
returns setof(Contact_Address_t)
as begin
  select C.*
    from Contact_Addresses C, Address_Resol R
   where R.Contact = $1.oid
     and R.Address = C.oid;
end;
--
-- And for the Phone Numbers;
--
create table Phone_Resols
of new type Phone_Resol_t
(
  Contact    oid   not null,
  Phone      oid   not null
);
--
-- The integrity functions;
--
--create rule R14
--on insert to Phone_Resols
--where exists ( select 1
--                 from Phone_Resol A
--                where A.Contact = new.Contact
--                  and A.Phone   = new.Phone )
--do instead nothing;
----
--create rule R15
--on update to Phone_Resols
--where exists ( select 1
--                 from Phone_Resol A
--                where A.Contact = new.Contact
--                  and A.Phone   = new.Phone )
--do instead nothing;
--
--  And the function to satisfy the virtual column;
--
create function Phone (Contact_t)
returns setof(Contact_Phone_t)
as begin
  select C.*
    from Contact_Phones C, Phone_Resols R
   where R.Contact = $1.oid
     and R.Phone   = C.oid
   order by C.From_Date desc;    -- Most recent first
end;
--
-- Meta Data Reference Information
--
create table Metadata_Reference_Informations
of new type Metadata_Reference_Information_t
( 
  Metadata_Date             Valid_Date_t   not null,
  Metadata_Review_Date      Valid_Date_t   not null,
  MetaData_Contact          ref(Contact_t) not null,
  Metadata_Standard_Name    ref(Code_t)   not null,
  Metadata_Standard_Version ref(Code_t)   not null
) ;
--
--  There are no other tables here. The entire thing falls
--  very nicely into place by the tables below it.
--
--
-- ######################################################################
end transaction;
--