--
--  This file is 
--
--     triplerock.CS.BErkeley.EDU:/private/S2K/Meta/FGDC/Src/FGDC_Codes.msql
--
--  By: Paul Brown
--
--  On: June 1994
--
--  It contains all the inserts to set up the FGDC Meta-Data schema with the
-- initial Code values.     
--
--  Some examples from the FGDC standards document for Graphic types. I
--  have extended these to include Sound etc. . .
--
begin transaction;
--
insert into Graphic_Type_Codes
( Code, Blurb)
values
( 'CGM', 'Computer Graphics Metafile');
insert into Graphic_Type_Codes
( Code, Blurb)
values
( 'EPS', 'Encapsulated Postscript format.');
insert into Graphic_Type_Codes
( Code, Blurb )
values
( 'GIF', 'Graphic Interchange format.');
insert into Graphic_Type_Codes
( Code, Blurb )
values
( 'JPEG', 'Joint Photographic Experts Group format.');
insert into Graphic_Type_Codes
( Code, Blurb )
values
( 'PBM', 'Portable Bit Map format.');
insert into Graphic_Type_Codes
( Code, Blurb )
values
( 'PS', 'Postscript format.');
insert into Graphic_Type_Codes
( Code, Blurb )
values
( 'TIFF', 'Tagged Image File format.');
insert into Graphic_Type_Codes
( Code, Blurb )
values
( 'XWD', 'X-Windows Dump format.');
insert into Graphic_Type_Codes
( Code, Blurb )
values
( 'TCL-ICON', 'A Tcl/Tk Icon Button format for Tcl/Tk apps.');
insert into Graphic_Type_Codes
( Code, Blurb )
values
( 'XVIEW', 'A file for viewing with XV, the Motif package');
insert into Graphic_Type_Codes
( Code, Blurb )
values
( 'XPLAYBITE', 'A file which can be listened to with xplay.');
--
--  This is a set of BASIC codes which will re-appear throughout the
-- schema. Each code is of a specific type, to allow repetition of
-- code values, but uniqueness within the Code type.
--
insert into Basic_Codes
( Code_Type, Blurb )
values
( 'BASIC_CODE', 'Codes dealing with Codes classification');
insert into Basic_Codes
( Code_Type, Blurb )
values
( 'SEC_CODE', 'Codes dealing with the Security Classification');
insert into Basic_Codes
( Code_Type, Blurb )
values
('ACC_CODE','Codes dealing with the Access_Restrictions_Codes table');
insert into Basic_Codes
( Code_Type, Blurb )
values
('USERR_CODE','Codes dealing with the User_Restrictions_Codes table');
insert into Basic_Codes
( Code_Type, Blurb )
values
('DATA_SET_CODE','Codes dealing with the Data Set Status table');
create table Temp_Codes as
select Code, Code_Type from Codes
where Code_Type is null;
--
insert into Temp_Codes
( Code, Code_Type )
select B.Code_Type,
       'BASIC_CODE'
  from Basic_Codes B
 where not exists
       (select * from Codes C where B.Code_Type = C.Code);
--
insert into Codes
( Code, Code_Type )
select * from Temp_Codes;
--
drop table Temp_Codes;
--
--     Zip Code: For the Address stuff;
--
insert into Basic_Codes
( Code_Type, Blurb )
values
( 'ZIP_CODE', 'A code which deals with ZIP Codes status');
--
create table Temp_Codes as
select Code, Code_Type from Codes
where Code_Type is null;
--
insert into Temp_Codes
( Code, Code_Type )
select B.Code_Type,
       'BASIC_CODE'
  from Basic_Codes B
 where not exists
       (select * from Codes C where B.Code_Type = C.Code);
--
insert into Codes
( Code, Code_Type )
select * from Temp_Codes;
--
drop table Temp_Codes;
--
-- insert into Codes C
-- ( Code, Code_Type)
-- select B.Code_Type,
-- 'BASIC_CODE'
-- from Basic_Codes B
-- where not exists
-- (select * from C where B.Code_Type = C.Code);
--
insert into Basic_Codes
( Code_Type, Blurb )
values
( 'ZIP_OK', 'This ZIP Code has been validated.');
insert into Basic_Codes
( Code_Type, Blurb )
values
( 'ZIP_NEW', 'This ZIP Code has not been investigated.');
--
create table Temp_Codes as
select Code, Code_Type from Codes
where Code_Type is null;
--
insert into Temp_Codes
( Code, Code_Type )
select B.Code_Type,
       'ZIP_CODE'
  from Basic_Codes B
 where not exists
       (select * from Codes C where B.Code_Type = C.Code);
--
insert into Codes
( Code, Code_Type )
select * from Temp_Codes;
--
drop table Temp_Codes;
--
--  Address Codes; Can be all kinds of things;
insert into Basic_Codes
( Code_Type, Blurb )
values
( 'ADDRESS_CODE', 'A code which deals with Address specifications');
--
create table Temp_Codes as
select Code, Code_Type from Codes
where Code_Type is null;
--
insert into Temp_Codes
( Code, Code_Type )
select B.Code_Type,
       'BASIC_CODE'
  from Basic_Codes B
 where not exists
       (select * from Codes C where B.Code_Type = C.Code);
--
insert into Codes
( Code, Code_Type )
select * from Temp_Codes;
--
drop table Temp_Codes;
--
insert into Basic_Codes
( Code_Type, Blurb )
values
( 'WORK_DELIV','Physical address of Contact. Deliveries type of thing.');
insert into Basic_Codes
( Code_Type, Blurb )
values
( 'WORK_MAIL','US Mail Address of Contact.');
--
create table Temp_Codes as
select Code, Code_Type from Codes
where Code_Type is null;
--
insert into Temp_Codes
( Code, Code_Type )
select B.Code_Type,
       'ADDRESS_CODE'
  from Basic_Codes B
 where not exists
       (select * from Codes C where B.Code_Type = C.Code);
--
insert into Codes
( Code, Code_Type )
select * from Temp_Codes;
--
drop table Temp_Codes;
insert into Basic_Codes
( Code_Type, Blurb )
values
( 'PHONE_CODE','A code which deals with Phone Number specifications');
--
create table Temp_Codes as
select Code, Code_Type from Codes
where Code_Type is null;
--
insert into Temp_Codes
( Code, Code_Type )
select B.Code_Type,
       'BASIC_CODE'
  from Basic_Codes B
 where not exists
       (select * from Codes C where B.Code_Type = C.Code);
--
insert into Codes
( Code, Code_Type )
select * from Temp_Codes;
--
drop table Temp_Codes;
--
insert into Basic_Codes
( Code_Type, Blurb )
values
( 'WORK', 'Primary work telephone number.');
insert into Basic_Codes
( Code_Type, Blurb )
values
( 'HOME', 'Home telephone number.');
insert into Basic_Codes
( Code_Type, Blurb )
values
( 'WORK_FAX','FAX Number at work.');
insert into Basic_Codes
( Code_Type, Blurb )
values
( 'HOME_FAX','FAX Number at home.');
insert into Basic_Codes
( Code_Type, Blurb )
values
( 'TELEX','TELEX number.');
--
create table Temp_Codes as
select Code, Code_Type from Codes
where Code_Type is null;
--
insert into Temp_Codes
( Code, Code_Type )
select B.Code_Type,
       'PHONE_CODE'
  from Basic_Codes B
 where not exists
       (select * from Codes C where B.Code_Type = C.Code);
--
insert into Codes
( Code, Code_Type )
select * from Temp_Codes;
--
drop table Temp_Codes;
--
--
end transaction;
--
-- Done with the codes information;
--