Index: openacs-4/packages/acs-reference/sql/postgresql/country.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-reference/sql/postgresql/Attic/country.sql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-reference/sql/postgresql/country.sql	17 Jul 2001 06:26:32 -0000	1.1
@@ -0,0 +1,96 @@
+-- packages/acs-reference/sql/common/country.sql
+--
+-- @author jon@jongriffin.com.com
+-- @creation-date 2000-11-21
+-- @cvs-id $Id: country.sql,v 1.1 2001/07/17 06:26:32 jong Exp $
+
+-- country is taken from ISO 3166
+
+-- probably ought to add a note about analyze for efficiency on non-integer primary keys
+
+create table countries (
+    iso char(2)
+        constraint countries_iso_pk
+        primary key,
+    -- this is the three letter abbreviation - hardly used
+    a3  char(3),
+    -- this is the numeric code - hardly used
+    -- it is a char because of leading zeros so it isn't really a number
+    numeric char(3),
+    -- this violates 3nf but is used for 2 reasons
+    -- 1. to help efficiency
+    -- 2. to make querys not fail if no translation exists yet
+    default_name varchar(100)
+        constraint countries_default_name_nn
+        not null
+        constraint countries_default_name_uq
+        unique
+);
+
+comment on table countries is '
+    This is the country code/english name table from ISO 3166.
+';
+
+comment on column countries.default_name is '
+    This is admittedly a violation of 3NF but it is more efficient and helps with non-translated values.
+See country.sql for more comments.
+';
+ 
+comment on column countries.a3 is '
+   This is the three letter abbreviation - hardly used.
+';
+
+comment on column countries.numeric is ' 
+    This is the numeric code - hardly used.
+';
+
+-- add this table into the reference repository
+
+create function inline_0 ()
+returns integer as '
+begin
+    PERFORM acs_reference__new (
+        ''COUNTRIES'', -- table_name
+        ''ISO 3166'', -- source
+        ''http://www.din.de/gremien/nas/nabd/iso3166ma/codlstp1/db_en.html'', -- source_url
+        to_date('2000-08-21','YYYY-MM-DD'), -- last_update
+        sysdate() -- effective_date
+    );
+
+    return 0
+end;' language 'plpgsql';
+
+select inline_0 ();
+
+drop function inline_0 ();
+
+-- This is the translated mapping of country names
+
+create table country_names (
+    -- lookup into the countries table
+    iso char(2)
+        constraint country_names_iso_fk
+        references countries (iso),
+    -- lookup into the language_codes table
+    language_code 
+        constraint country_names_language_code_fk
+        references language_codes (language_id),
+    -- the translated name
+    name varchar(100)
+);
+
+comment on table country_names is ' 
+    This is the translated mapping of country names and language codes.
+';
+
+comment on column country_names.language_code is '
+    This is a lookup into the iso languages table.
+';
+
+-- I need to know the easy way to add extended chars in sqlplus then I can add french and spanish
+
+-- ISO country codes
+/i ../common/country-data
+
+
+
Index: openacs-4/packages/acs-reference/sql/postgresql/currency.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-reference/sql/postgresql/Attic/currency.sql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-reference/sql/postgresql/currency.sql	17 Jul 2001 06:26:32 -0000	1.1
@@ -0,0 +1,87 @@
+-- packages/acs-reference/sql/common/currency.sql
+--
+-- @author jon@jongriffin.com
+-- @creation-date 2000-11-29
+-- @cvs-id $Id: currency.sql,v 1.1 2001/07/17 06:26:32 jong Exp $
+
+
+create table currencies (
+    -- since currencies 
+    -- 3 char alphabetic
+    codeA char(3)
+        constraint currencies_code_a_pk
+        primary key,
+    -- this is the currency #
+    codeN number,
+    -- this is the minor unit
+    -- not sure of the use but it is in the standar
+    minor_unit char(1),
+    -- explanation per iso
+    note varchar(4000),
+    -- this violates 3nf but is used for 2 reasons
+    -- 1. to help efficiency
+    -- 2. to make querys not fail if no translation exists yet
+    default_name varchar(100)
+        constraint currencies_default_name_nn
+        not null
+);
+
+comment on table currencies is '
+    This is the currency code/english name table from ISO 4217.
+';
+
+-- add this table into the reference repository
+select acs_reference__new (
+    'CURRENCIES', -- table_name
+    'ISO 4217', -- source
+    'http://www.iso.ch', -- source_url
+    to_date('2000-10-30','YYYY-MM-DD'), --last_update
+    sysdate() -- effective_date
+);
+
+-- This is the translated mapping of country names
+
+create table currency_names (
+    -- lookup into the countries table
+    codeA char(3)
+        constraint currency_names_iso_fk
+        references currencies (codeA),
+    -- lookup into the language_codes table
+    language_code 
+        constraint currency_names_lang_code_fk
+        references language_codes (language_id),
+    -- the translated name
+    name varchar(100)
+);
+
+comment on table currency_names is ' 
+    This is the translated mapping of currency names and language codes.
+';
+
+comment on column currency_names.language_code is '
+    This is a lookup into the iso languages table.
+';
+
+-- map from currencies to country
+create table currency_country_map (
+    codeA char(3)
+        constraint currency_country_map_code_fk
+        references currencies (codeA),
+    -- foreign key to relate country to currency
+    -- this can by one => many therefor can't be unique
+    -- i.e. Cuba has USD and CUP
+    country_code char(2)
+        constraint curr_cntry_map_country_fk
+        references countries (iso)
+);
+
+-- I will add a view to join this stuff later.
+
+-- initial data for currencies
+/i ../common/currency-data.sql
+
+
+
+
+
+
Index: openacs-4/packages/acs-reference/sql/postgresql/language.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-reference/sql/postgresql/Attic/language.sql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-reference/sql/postgresql/language.sql	17 Jul 2001 06:26:32 -0000	1.1
@@ -0,0 +1,44 @@
+-- packages/acs-reference/sql/common/language.sql
+--
+-- @author jon@jongriffin.com
+-- @creation-date 2000-11-21
+-- @cvs-id $Id: language.sql,v 1.1 2001/07/17 06:26:32 jong Exp $
+--
+
+
+-- ISO 639
+create table language_codes (
+    language_id char(2)
+        constraint language_codes_language_id_pk
+        primary key,
+    name varchar(100)
+        constraint language_codes_name_uq
+        unique
+        constraint language_codes_name_nn
+        not null
+);
+
+comment on table language_codes is '
+    This is data from the ISO 639 standard on language codes.
+';
+
+comment on column language_codes.language_id is '
+    This is the ISO standard language code
+';
+
+comment on column language_codes.name is '
+    This is the English version of the language name. 
+    I don''t want to get crazy here!
+';
+
+-- now register this table with the repository
+select acs_reference__new(
+    'LANGUAGE_CODES',
+    'ISO 639',
+    'http://www.iso.ch',
+    null, -- last update
+    sysdate()
+);
+
+-- data
+/i ../common/language-data
\ No newline at end of file
Index: openacs-4/packages/acs-reference/sql/postgresql/s-and-p.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-reference/sql/postgresql/Attic/s-and-p.sql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-reference/sql/postgresql/s-and-p.sql	17 Jul 2001 06:26:32 -0000	1.1
@@ -0,0 +1,43 @@
+-- packages/acs-reference/sql/oracle/s-and-p-data.sql
+--
+-- @author jon@jongriffin.com
+-- @creation-date 2000-11-21
+-- @cvs-id $Id: s-and-p.sql,v 1.1 2001/07/17 06:26:32 jong Exp $
+
+create table long_term_issue_ratings (
+    -- this is the sort key
+    rank  integer
+        constraint long_term_issue_rank_pk
+        primary key,
+    -- the actual rating
+    rating char(4)
+        constraint long_term_issue_rating_uq
+        unique
+        constraint long_term_issue_rating_nn
+        not null,
+    description varchar2(1000)
+);
+
+comment on table long_term_issue_ratings is '
+    This is a sample of some of the non-standards based standards.
+    It is the Standard y Poor''s credit ratings.
+';
+
+comment on column long_term_issue_ratings.rank is '
+    This is the rank with AAA+ being highest.
+';
+
+-- now register this table with the repository
+
+select acs_reference__new (
+    'LONG_TERM_ISSUE_RATINGS',
+    'Standard '||chr(38)||' Poor''s',
+    'http://www.standardandpoors.com/ratings/corporates/index.htm',
+    null,
+    effective_date => sysdate()
+);
+
+-- now add data
+/i ../common/s-and-p-data.sql
+
+  
Index: openacs-4/packages/acs-reference/sql/postgresql/us-counties.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-reference/sql/postgresql/Attic/us-counties.sql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-reference/sql/postgresql/us-counties.sql	17 Jul 2001 06:26:32 -0000	1.1
@@ -0,0 +1,91 @@
+-- packages/acs-reference/sql/common/us-counties.sql
+--
+-- @author jon@jongriffin.com
+-- @creation-date 2000-12-05
+-- @cvs-id $Id: us-counties.sql,v 1.1 2001/07/17 06:26:32 jong Exp $
+
+-- This is the combination of state and county FIPS codes.
+-- It is possible to get the state by parsing the first 2 chars
+
+create table us_counties (
+    fips_state_code  char(2)  
+	             constraint us_counties_fips_fk
+                     references us_states(fips_state_code),
+    fips_county_code char(6)  
+                     constraint us_counties_county_code_nn not null,
+    name             varchar2(100)
+                     constraint us_counties_name_nn not null,
+    state_abbrev     char(2)
+                     constraint us_counties_state_abrrev_fk
+                     references us_states(abbrev),
+    population       number,
+    housing_units    number,
+    land_area        number,
+    water_area       number,
+    latitude         number,
+    longitude        number,
+    --
+    -- Primary key for this table is generated by combining the state
+    -- and county FIPS codes.  County code are only unique within a given state.
+    -- 
+    constraint us_counties_unique_fips_pk primary key (fips_county_code, fips_state_code)
+);
+
+comment on table us_counties is '
+    This is the United States county code table from the US Census Bureau
+';
+
+comment on column us_counties.fips_state_code is '
+State FIPS code.
+';
+
+comment on column us_counties.fips_county_code is '
+County FIPS code.
+';
+
+comment on column us_counties.name is '
+Name. Includes name of county. Counties don't nave a type, but do have
+the word "County" as part of the name.
+';
+
+comment on column us_counties.state_abbrev is '
+State abbreviations.
+';
+
+comment on column us_counties.population is '
+Total population (1990).
+';
+
+comment on column us_counties.housing_units is '
+Housing units (1990).
+';
+
+comment on column us_counties.land_area is '
+Land are included (in thousandths of a square kilometer).
+';
+
+comment on column us_counties.water_area is '
+Water area included (in thousandths of a square kilometer).
+';
+
+comment on column us_counties.latitude is '
+Longitude (millionths of a degree, + or - denoting N or S,
+respectively.
+';
+
+comment on column us_counties.longitude is '
+Latitude (millionths of a degree, + or - denoting N or S,
+respectively.
+';
+
+-- add this table into the ACS reference repository
+
+select acs_reference__new (
+    'US_COUNTIES',
+    'US Census Bureau',
+    'http://ftp.census.gov/geo/www/gazetteer/places.html',
+    to_date('1990-12-31','YYYY-MM-DD'),
+    sysdate()
+);
+
+\i ../common/us-counties-data
Index: openacs-4/packages/acs-reference/sql/postgresql/us-states.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-reference/sql/postgresql/Attic/us-states.sql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-reference/sql/postgresql/us-states.sql	17 Jul 2001 06:26:32 -0000	1.1
@@ -0,0 +1,40 @@
+-- packages/acs-reference/sql/common/us-states.sql
+--
+-- @author jon@jongriffin.com
+-- @creation-date 2000-11-28
+-- @cvs-id $Id: us-states.sql,v 1.1 2001/07/17 06:26:32 jong Exp $
+
+create table us_states (
+    abbrev          char(2)
+                    constraint us_states_abbrev_pk primary key,
+    state_name      varchar2(100)
+	            constraint us_states_state_name_nn not null
+                    constraint us_states_state_name_uq unique,
+    fips_state_code char(2)
+                    constraint us_states_fips_state_code_uq unique
+);
+
+comment on table us_states is '
+This is the US states table.
+';
+
+comment on column us_states.abbrev is '
+This is the 2 letter abbreviation for states.
+';
+
+comment on column us_states.fips_state_code is '
+The FIPS code used by the USPS for certain delivery types.
+';
+
+-- add this table into the reference repository
+select acs_reference__new (
+    table_name      => 'US_STATES',
+    source          => 'Internal',
+    source_url      => '',
+    last_update     => sysdate,
+    internal_data_p => 't',
+    effective_date  => sysdate
+);
+
+-- load the data
+\i ../common/us-states-data
Index: openacs-4/packages/acs-reference/sql/postgresql/us-zipcodes.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-reference/sql/postgresql/Attic/us-zipcodes.sql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-reference/sql/postgresql/us-zipcodes.sql	17 Jul 2001 06:26:32 -0000	1.1
@@ -0,0 +1,68 @@
+--
+-- packages/acs-reference/sql/common/us-zipcodes.sql
+--
+-- @author jon@jongriffin.com
+-- @creation-date 2000-12-06
+-- @cvs-id $Id: us-zipcodes.sql,v 1.1 2001/07/17 06:26:32 jong Exp $
+--
+
+create table us_zipcodes (
+    zipcode           char(5)        
+                      constraint us_zipcodes_zipcode_nn not null,
+    name              varchar2(100)       
+                      constraint us_zipcodes_name_nn not null,
+    fips_state_code   char(2)
+                      constraint us_zipcodes_fips_fk 
+                      references us_states(fips_state_code),
+    fips_county_code  char(6)
+	              constraint us_county_codes_nn not null,
+    latitude          number,
+    longitude         number,
+    --
+    -- Some zipcodes straddle state boundaries, so the zipcode itself
+    -- isn't unique. We form a primary key for this table from the
+    -- combination of zipcode and FIPS state code.
+    --
+    constraint us_zipcodes_unique_pk primary key (zipcode, fips_state_code)
+);
+
+comment on table us_zipcodes is '
+This is the table of US zipcodes. It does not include zip+4.
+';
+
+comment on column us_zipcodes.zipcode is '
+5-digit Zipcode.
+';
+
+comment on column us_zipcodes.name is '
+Zipcode name.
+';
+
+comment on column us_zipcodes.fips_state_code is '
+State FIPS code.
+';
+
+comment on column us_zipcodes.fips_county_code is '
+County FIPS code.
+';
+
+comment on column us_zipcodes.longitude is '
+Longitude in decimal degress.
+';
+
+comment on column us_zipcodes.latitude is '
+Latitude in decimal degress.
+';
+
+-- add this table into the reference repository
+select acs_reference__new (
+    table_name     => 'US_ZIPCODES',
+    package_name   => 'US_ZIPCODE',
+    source         => 'US Census Bureau',
+    source_url     => 'http://www.census.gov/geo/www/tiger/zip1999.html',
+    last_update    => to_date('1999-11-30','YYYY-MM-DD'),
+    effective_date => sysdate()
+);
+-- load data
+
+\i ../common/us-zipcodes-data