--#####################################################################
--
--  File: trilerock.CS.BErkeley.EDU:/private/S2K/Meta/FGDC/Src/FGDC_Meta2.msql
--  
--  By:  Paul Brown
--
--  On:  June 1994
--
--  Status_Information:
--
--  Status Information deals wth the state, and last update information
--  for the data set. Allows another restriction on the query based
--  on currency of information.
--
--  Status_Information = 
--                        Data_Set_Status +
--                        Release_Date +   
--                        Maintenance_and_Update_Frequency;
--
--  Data_Set_Status = text;
--
--   A codes table, obviously. With some resolution to track the
--   possibility that multiple codes exist per Data Set.
--
--  Release_Date    = date_from +
--                    date_to;
--
--  This is an extention of the FGDC standard, but it allows for a fairly
--  simple sub-set to limit stuff.
--
-- Maintenance_and_Update_Frequency = text;
--
--  Purely a text tag.
--
--   Bottom Up!
--
--  For the Status_Information, I'm going to create a table of common
--  ones. They can be selected, if desired, but the chance to simply pump
--  lots of text in exists as well. There will be some 'standards', mixed
--  in with comments. Can use as a 'root' and 'extension' as well.
--
--  Data_Set_Status  text not null. This will be normalised into the 
--  relation.
-- 
--
--  Note that batches of times may be associated with a given
--  set of data. This provides a way of grouping the data sets
--  based on release date information. Hence there is a 
--  comment in the table, to associate with the release
--  range.
--
begin transaction;
--
create table Valid_Dates
of new type Valid_Date_t
(
  From_Date date  not null,
  To_Date   date,
  Blurb     text
);
--
--  Got to do all this fiddling because there is no 'default'
--  in Illustra (yet!)
-- 
create function EQ (Valid_Date_t, date, date )
returns boolean
as return ((($1.To_Date is null) and ($1.From_Date <= $2 )) or
           (($1.To_Date is not null) 
              and (($1.From_Date, $1.To_Date) overlaps ($2, $3))));
--
-- May need more of these functions.
--
--  Status_Information 
--
--   This can be contained in the appropriate codes tables, and done
--   as a virtual table.
--
--
create table Status_Informations
of new type Status_Information_t
(
   Data_Set_Status      text not null, -- De-Normalize this! There may be lots
                                       -- of redundancy, but I don't know.
   Release_Date         Valid_Date_t,
   Data_Set_Status_Code setof(Basic_Code_t) virtual
);
--
--
-- Resolution table for the Data_Set_Status_Codes
--
create table Data_Set_Status_Resols
of new type Data_Set_Status_Resol_t
(
  Status_Information      oid not null,
  Code                    oid not null
);
--
--  Rules to ensure no duplicates;
--
-- create rule R9
-- on insert to Data_Set_Status_Resols
-- where exists ( select 1
--                  from Data_Set_Status_Resols D
--                 where D.Status_Information = new.Status_Information
--                   and D.Code = new.Code )
-- do instead nothing;
--
-- create rule R10
-- on update to Data_Set_Status_Resols
-- where exists ( select 1
--                  from Data_Set_Status_Resols D
--                 where D.Status_Information = new.Status_Information
--                   and D.Code = new.Code )
-- do instead nothing;
--
--
create function Data_Set_Status_Code(Status_Information_t)
returns setof(Basic_Code_t)
as begin
  select B.*
    from Basic_Codes B, Data_Set_Status_Resols R
   where R.Status_Information = $1.oid
     and R.Code               = B.oid;
end;
--
-- 
end transaction;
--