Index: openacs-4/packages/ref-countries/sql/oracle/ref-countries-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ref-countries/sql/oracle/ref-countries-create.sql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ref-countries/sql/oracle/ref-countries-create.sql	27 Aug 2001 22:28:07 -0000	1.1
@@ -0,0 +1,104 @@
+-- packages/ref-country/sql/oracle/ref-countries-create.sql
+--
+-- @author jon@jongriffin.com.com
+-- @creation-date 2001-08-27
+-- @cvs-id $Id: ref-countries-create.sql,v 1.1 2001/08/27 22:28:07 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
+declare
+    v_id integer;
+begin
+    v_id := acs_reference.new(
+        table_name     => 'COUNTRIES',
+        source         => 'ISO 3166',
+        source_url     => 'http://www.din.de/gremien/nas/nabd/iso3166ma/codlstp1/db_en.html',
+        last_update    => to_date('2000-08-21','YYYY-MM-DD'),
+        effective_date => sysdate
+    );
+commit;
+end;
+/
+
+-- 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
+
+-- add this table into the reference repository
+declare
+    v_id integer;
+begin
+    v_id := acs_reference.new(
+        table_name     => 'COUNTRY_NAMES',
+        source         => 'Internal',
+        source_url     => '',
+        effective_date => sysdate
+    );
+commit;
+end;
+/
+-- ISO country codes
+@ '../common/ref-country-data.sql'
+
+
+
Index: openacs-4/packages/ref-countries/sql/oracle/ref-countries-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ref-countries/sql/oracle/ref-countries-drop.sql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ref-countries/sql/oracle/ref-countries-drop.sql	27 Aug 2001 22:28:07 -0000	1.1
@@ -0,0 +1,35 @@
+-- Drop the ACS Reference Country data
+--
+-- @author jon@jongriffin.com
+-- @cvs-id $Id: ref-countries-drop.sql,v 1.1 2001/08/27 22:28:07 jong Exp $
+
+set serveroutput on
+
+-- drop all associated tables and packages
+-- I am not sure this is a good idea since we have no way to register
+-- if any other packages are using this data.
+
+-- This will probably fail if their is a child table using this.
+-- I can probably make this cleaner also, but ... no time today
+
+declare
+    cursor refsrc_cur is
+	 select   table_name,
+                  package_name,
+                  repository_id
+	 from     acs_reference_repositories
+	 where upper(table_name) = 'COUNTR'
+	 order by repository_id desc;
+begin
+    for rec in refsrc_cur loop
+	 dbms_output.put_line('Dropping ' || rec.table_name);
+	 execute immediate 'drop table ' || rec.table_name;
+	 if rec.package_name is not null then
+	     execute immediate 'drop package ' || rec.package_name;
+         end if;
+         acs_reference.delete(rec.repository_id);
+    end loop;
+end;
+/
+show errors
+
Fisheye: Tag 1.2 refers to a dead (removed) revision in file `openacs-4/packages/ref-countries/sql/oracle/ref-country-create.sql'.
Fisheye: No comparison available.  Pass `N' to diff?
Fisheye: Tag 1.2 refers to a dead (removed) revision in file `openacs-4/packages/ref-countries/sql/oracle/ref-country-drop.sql'.
Fisheye: No comparison available.  Pass `N' to diff?