Index: openacs-4/packages/ref-countries/ref-countries.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ref-countries/ref-countries.info,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ref-countries/ref-countries.info 27 Aug 2001 22:18:49 -0000 1.1
@@ -0,0 +1,37 @@
+
+
+
+
+ Reference Data - Country
+ Reference Data - Countries
+ f
+ t
+
+
+
+ oracle
+ postgresql
+
+ Jon Griffin
+ This is geo-spatial country data.
+ Mayuli Enterprises, LLC
+ Countries supplies the ISO 3166 Country data.
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
Index: openacs-4/packages/ref-countries/sql/common/ref-country-data.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ref-countries/sql/common/Attic/ref-country-data.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ref-countries/sql/common/ref-country-data.sql 27 Aug 2001 22:14:43 -0000 1.1
@@ -0,0 +1,259 @@
+-- packages/ref-country/sql/common/ref-country-data.sql
+--
+-- @author jon@jongriffin.com.com
+-- @creation-date 2001-08-27
+-- @cvs-id $Id: ref-country-data.sql,v 1.1 2001/08/27 22:14:43 jong Exp $
+
+-- country is taken from ISO 3166
+
+-- I need to know the easy way to add extended chars in sqlplus then I can add french and spanish
+
+-- ISO country codes
+
+--set feedback off;
+
+insert into countries (default_name,iso) values ('AFGHANISTAN','AF');
+insert into countries (default_name,iso) values ('ALBANIA','AL');
+insert into countries (default_name,iso) values ('ALGERIA','DZ');
+insert into countries (default_name,iso) values ('AMERICAN SAMOA','AS');
+insert into countries (default_name,iso) values ('ANDORRA','AD');
+insert into countries (default_name,iso) values ('ANGOLA','AO');
+insert into countries (default_name,iso) values ('ANGUILLA','AI');
+insert into countries (default_name,iso) values ('ANTARCTICA','AQ');
+insert into countries (default_name,iso) values ('ANTIGUA AND BARBUDA','AG');
+insert into countries (default_name,iso) values ('ARGENTINA','AR');
+insert into countries (default_name,iso) values ('ARMENIA','AM');
+insert into countries (default_name,iso) values ('ARUBA','AW');
+insert into countries (default_name,iso) values ('AUSTRALIA','AU');
+insert into countries (default_name,iso) values ('AUSTRIA','AT');
+insert into countries (default_name,iso) values ('AZERBAIJAN','AZ');
+insert into countries (default_name,iso) values ('BAHAMAS','BS');
+insert into countries (default_name,iso) values ('BAHRAIN','BH');
+insert into countries (default_name,iso) values ('BANGLADESH','BD');
+insert into countries (default_name,iso) values ('BARBADOS','BB');
+insert into countries (default_name,iso) values ('BELARUS','BY');
+insert into countries (default_name,iso) values ('BELGIUM','BE');
+insert into countries (default_name,iso) values ('BELIZE','BZ');
+insert into countries (default_name,iso) values ('BENIN','BJ');
+insert into countries (default_name,iso) values ('BERMUDA','BM');
+insert into countries (default_name,iso) values ('BHUTAN','BT');
+insert into countries (default_name,iso) values ('BOLIVIA','BO');
+insert into countries (default_name,iso) values ('BOSNIA AND HERZEGOVINA','BA');
+insert into countries (default_name,iso) values ('BOTSWANA','BW');
+insert into countries (default_name,iso) values ('BOUVET ISLAND','BV');
+insert into countries (default_name,iso) values ('BRAZIL','BR');
+insert into countries (default_name,iso) values ('BRITISH INDIAN OCEAN TERRITORY','IO');
+insert into countries (default_name,iso) values ('BRUNEI DARUSSALAM','BN');
+insert into countries (default_name,iso) values ('BULGARIA','BG');
+insert into countries (default_name,iso) values ('BURKINA FASO','BF');
+insert into countries (default_name,iso) values ('BURUNDI','BI');
+insert into countries (default_name,iso) values ('CAMBODIA','KH');
+insert into countries (default_name,iso) values ('CAMEROON','CM');
+insert into countries (default_name,iso) values ('CANADA','CA');
+insert into countries (default_name,iso) values ('CAPE VERDE','CV');
+insert into countries (default_name,iso) values ('CAYMAN ISLANDS','KY');
+insert into countries (default_name,iso) values ('CENTRAL AFRICAN REPUBLIC','CF');
+insert into countries (default_name,iso) values ('CHAD','TD');
+insert into countries (default_name,iso) values ('CHILE','CL');
+insert into countries (default_name,iso) values ('CHINA','CN');
+insert into countries (default_name,iso) values ('CHRISTMAS ISLAND','CX');
+insert into countries (default_name,iso) values ('COCOS (KEELING) ISLANDS','CC');
+insert into countries (default_name,iso) values ('COLOMBIA','CO');
+insert into countries (default_name,iso) values ('COMOROS','KM');
+insert into countries (default_name,iso) values ('CONGO','CG');
+insert into countries (default_name,iso) values ('CONGO, THE DEMOCRATIC REPUBLIC OF THE','CD');
+insert into countries (default_name,iso) values ('COOK ISLANDS','CK');
+insert into countries (default_name,iso) values ('COSTA RICA','CR');
+insert into countries (default_name,iso) values ('COTE D''IVOIRE','CI');
+insert into countries (default_name,iso) values ('CROATIA','HR');
+insert into countries (default_name,iso) values ('CUBA','CU');
+insert into countries (default_name,iso) values ('CYPRUS','CY');
+insert into countries (default_name,iso) values ('CZECH REPUBLIC','CZ');
+insert into countries (default_name,iso) values ('DENMARK','DK');
+insert into countries (default_name,iso) values ('DJIBOUTI','DJ');
+insert into countries (default_name,iso) values ('DOMINICA','DM');
+insert into countries (default_name,iso) values ('DOMINICAN REPUBLIC','DO');
+insert into countries (default_name,iso) values ('EAST TIMOR','TP');
+insert into countries (default_name,iso) values ('ECUADOR','EC');
+insert into countries (default_name,iso) values ('EGYPT','EG');
+insert into countries (default_name,iso) values ('EL SALVADOR','SV');
+insert into countries (default_name,iso) values ('EQUATORIAL GUINEA','GQ');
+insert into countries (default_name,iso) values ('ERITREA','ER');
+insert into countries (default_name,iso) values ('ESTONIA','EE');
+insert into countries (default_name,iso) values ('ETHIOPIA','ET');
+insert into countries (default_name,iso) values ('FALKLAND ISLANDS (MALVINAS)','FK');
+insert into countries (default_name,iso) values ('FAROE ISLANDS','FO');
+insert into countries (default_name,iso) values ('FIJI','FJ');
+insert into countries (default_name,iso) values ('FINLAND','FI');
+insert into countries (default_name,iso) values ('FRANCE','FR');
+insert into countries (default_name,iso) values ('FRENCH GUIANA','GF');
+insert into countries (default_name,iso) values ('FRENCH POLYNESIA','PF');
+insert into countries (default_name,iso) values ('FRENCH SOUTHERN TERRITORIES','TF');
+insert into countries (default_name,iso) values ('GABON','GA');
+insert into countries (default_name,iso) values ('GAMBIA','GM');
+insert into countries (default_name,iso) values ('GEORGIA','GE');
+insert into countries (default_name,iso) values ('GERMANY','DE');
+insert into countries (default_name,iso) values ('GHANA','GH');
+insert into countries (default_name,iso) values ('GIBRALTAR','GI');
+insert into countries (default_name,iso) values ('GREECE','GR');
+insert into countries (default_name,iso) values ('GREENLAND','GL');
+insert into countries (default_name,iso) values ('GRENADA','GD');
+insert into countries (default_name,iso) values ('GUADELOUPE','GP');
+insert into countries (default_name,iso) values ('GUAM','GU');
+insert into countries (default_name,iso) values ('GUATEMALA','GT');
+insert into countries (default_name,iso) values ('GUINEA','GN');
+insert into countries (default_name,iso) values ('GUINEA-BISSAU','GW');
+insert into countries (default_name,iso) values ('GUYANA','GY');
+insert into countries (default_name,iso) values ('HAITI','HT');
+insert into countries (default_name,iso) values ('HEARD ISLAND AND MCDONALD ISLANDS','HM');
+insert into countries (default_name,iso) values ('HOLY SEE (VATICAN CITY STATE)','VA');
+insert into countries (default_name,iso) values ('HONDURAS','HN');
+insert into countries (default_name,iso) values ('HONG KONG','HK');
+insert into countries (default_name,iso) values ('HUNGARY','HU');
+insert into countries (default_name,iso) values ('ICELAND','IS');
+insert into countries (default_name,iso) values ('INDIA','IN');
+insert into countries (default_name,iso) values ('INDONESIA','ID');
+insert into countries (default_name,iso) values ('IRAN, ISLAMIC REPUBLIC OF','IR');
+insert into countries (default_name,iso) values ('IRAQ','IQ');
+insert into countries (default_name,iso) values ('IRELAND','IE');
+insert into countries (default_name,iso) values ('ISRAEL','IL');
+insert into countries (default_name,iso) values ('ITALY','IT');
+insert into countries (default_name,iso) values ('JAMAICA','JM');
+insert into countries (default_name,iso) values ('JAPAN','JP');
+insert into countries (default_name,iso) values ('JORDAN','JO');
+insert into countries (default_name,iso) values ('KAZAKSTAN','KZ');
+insert into countries (default_name,iso) values ('KENYA','KE');
+insert into countries (default_name,iso) values ('KIRIBATI','KI');
+insert into countries (default_name,iso) values ('KOREA, DEMOCRATIC PEOPLE''S REPUBLIC OF','KP');
+insert into countries (default_name,iso) values ('KOREA, REPUBLIC OF','KR');
+insert into countries (default_name,iso) values ('KUWAIT','KW');
+insert into countries (default_name,iso) values ('KYRGYZSTAN','KG');
+insert into countries (default_name,iso) values ('LAO PEOPLE''S DEMOCRATIC REPUBLIC','LA');
+insert into countries (default_name,iso) values ('LATVIA','LV');
+insert into countries (default_name,iso) values ('LEBANON','LB');
+insert into countries (default_name,iso) values ('LESOTHO','LS');
+insert into countries (default_name,iso) values ('LIBERIA','LR');
+insert into countries (default_name,iso) values ('LIBYAN ARAB JAMAHIRIYA','LY');
+insert into countries (default_name,iso) values ('LIECHTENSTEIN','LI');
+insert into countries (default_name,iso) values ('LITHUANIA','LT');
+insert into countries (default_name,iso) values ('LUXEMBOURG','LU');
+insert into countries (default_name,iso) values ('MACAU','MO');
+insert into countries (default_name,iso) values ('MACEDONIA, THE FORMER YUGOSLAV REPUBLIC OF','MK');
+insert into countries (default_name,iso) values ('MADAGASCAR','MG');
+insert into countries (default_name,iso) values ('MALAWI','MW');
+insert into countries (default_name,iso) values ('MALAYSIA','MY');
+insert into countries (default_name,iso) values ('MALDIVES','MV');
+insert into countries (default_name,iso) values ('MALI','ML');
+insert into countries (default_name,iso) values ('MALTA','MT');
+insert into countries (default_name,iso) values ('MARSHALL ISLANDS','MH');
+insert into countries (default_name,iso) values ('MARTINIQUE','MQ');
+insert into countries (default_name,iso) values ('MAURITANIA','MR');
+insert into countries (default_name,iso) values ('MAURITIUS','MU');
+insert into countries (default_name,iso) values ('MAYOTTE','YT');
+insert into countries (default_name,iso) values ('MEXICO','MX');
+insert into countries (default_name,iso) values ('MICRONESIA, FEDERATED STATES OF','FM');
+insert into countries (default_name,iso) values ('MOLDOVA, REPUBLIC OF','MD');
+insert into countries (default_name,iso) values ('MONACO','MC');
+insert into countries (default_name,iso) values ('MONGOLIA','MN');
+insert into countries (default_name,iso) values ('MONTSERRAT','MS');
+insert into countries (default_name,iso) values ('MOROCCO','MA');
+insert into countries (default_name,iso) values ('MOZAMBIQUE','MZ');
+insert into countries (default_name,iso) values ('MYANMAR','MM');
+insert into countries (default_name,iso) values ('NAMIBIA','NA');
+insert into countries (default_name,iso) values ('NAURU','NR');
+insert into countries (default_name,iso) values ('NEPAL','NP');
+insert into countries (default_name,iso) values ('NETHERLANDS','NL');
+insert into countries (default_name,iso) values ('NETHERLANDS ANTILLES','AN');
+insert into countries (default_name,iso) values ('NEW CALEDONIA','NC');
+insert into countries (default_name,iso) values ('NEW ZEALAND','NZ');
+insert into countries (default_name,iso) values ('NICARAGUA','NI');
+insert into countries (default_name,iso) values ('NIGER','NE');
+insert into countries (default_name,iso) values ('NIGERIA','NG');
+insert into countries (default_name,iso) values ('NIUE','NU');
+insert into countries (default_name,iso) values ('NORFOLK ISLAND','NF');
+insert into countries (default_name,iso) values ('NORTHERN MARIANA ISLANDS','MP');
+insert into countries (default_name,iso) values ('NORWAY','NO');
+insert into countries (default_name,iso) values ('OMAN','OM');
+insert into countries (default_name,iso) values ('PAKISTAN','PK');
+insert into countries (default_name,iso) values ('PALAU','PW');
+insert into countries (default_name,iso) values ('PALESTINIAN TERRITORY, OCCUPIED','PS');
+insert into countries (default_name,iso) values ('PANAMA','PA');
+insert into countries (default_name,iso) values ('PAPUA NEW GUINEA','PG');
+insert into countries (default_name,iso) values ('PARAGUAY','PY');
+insert into countries (default_name,iso) values ('PERU','PE');
+insert into countries (default_name,iso) values ('PHILIPPINES','PH');
+insert into countries (default_name,iso) values ('PITCAIRN','PN');
+insert into countries (default_name,iso) values ('POLAND','PL');
+insert into countries (default_name,iso) values ('PORTUGAL','PT');
+insert into countries (default_name,iso) values ('PUERTO RICO','PR');
+insert into countries (default_name,iso) values ('QATAR','QA');
+insert into countries (default_name,iso) values ('REUNION','RE');
+insert into countries (default_name,iso) values ('ROMANIA','RO');
+insert into countries (default_name,iso) values ('RUSSIAN FEDERATION','RU');
+insert into countries (default_name,iso) values ('RWANDA','RW');
+insert into countries (default_name,iso) values ('SAINT HELENA','SH');
+insert into countries (default_name,iso) values ('SAINT KITTS AND NEVIS','KN');
+insert into countries (default_name,iso) values ('SAINT LUCIA','LC');
+insert into countries (default_name,iso) values ('SAINT PIERRE AND MIQUELON','PM');
+insert into countries (default_name,iso) values ('SAINT VINCENT AND THE GRENADINES','VC');
+insert into countries (default_name,iso) values ('SAMOA','WS');
+insert into countries (default_name,iso) values ('SAN MARINO','SM');
+insert into countries (default_name,iso) values ('SAO TOME AND PRINCIPE','ST');
+insert into countries (default_name,iso) values ('SAUDI ARABIA','SA');
+insert into countries (default_name,iso) values ('SENEGAL','SN');
+insert into countries (default_name,iso) values ('SEYCHELLES','SC');
+insert into countries (default_name,iso) values ('SIERRA LEONE','SL');
+insert into countries (default_name,iso) values ('SINGAPORE','SG');
+insert into countries (default_name,iso) values ('SLOVAKIA','SK');
+insert into countries (default_name,iso) values ('SLOVENIA','SI');
+insert into countries (default_name,iso) values ('SOLOMON ISLANDS','SB');
+insert into countries (default_name,iso) values ('SOMALIA','SO');
+insert into countries (default_name,iso) values ('SOUTH AFRICA','ZA');
+insert into countries (default_name,iso) values ('SOUTH GEORGIA AND THE SOUTH SANDWICH ISLANDS','GS');
+insert into countries (default_name,iso) values ('SPAIN','ES');
+insert into countries (default_name,iso) values ('SRI LANKA','LK');
+insert into countries (default_name,iso) values ('SUDAN','SD');
+insert into countries (default_name,iso) values ('SURINAME','SR');
+insert into countries (default_name,iso) values ('SVALBARD AND JAN MAYEN','SJ');
+insert into countries (default_name,iso) values ('SWAZILAND','SZ');
+insert into countries (default_name,iso) values ('SWEDEN','SE');
+insert into countries (default_name,iso) values ('SWITZERLAND','CH');
+insert into countries (default_name,iso) values ('SYRIAN ARAB REPUBLIC','SY');
+insert into countries (default_name,iso) values ('TAIWAN, PROVINCE OF CHINA','TW');
+insert into countries (default_name,iso) values ('TAJIKISTAN','TJ');
+insert into countries (default_name,iso) values ('TANZANIA, UNITED REPUBLIC OF','TZ');
+insert into countries (default_name,iso) values ('THAILAND','TH');
+insert into countries (default_name,iso) values ('TOGO','TG');
+insert into countries (default_name,iso) values ('TOKELAU','TK');
+insert into countries (default_name,iso) values ('TONGA','TO');
+insert into countries (default_name,iso) values ('TRINIDAD AND TOBAGO','TT');
+insert into countries (default_name,iso) values ('TUNISIA','TN');
+insert into countries (default_name,iso) values ('TURKEY','TR');
+insert into countries (default_name,iso) values ('TURKMENISTAN','TM');
+insert into countries (default_name,iso) values ('TURKS AND CAICOS ISLANDS','TC');
+insert into countries (default_name,iso) values ('TUVALU','TV');
+insert into countries (default_name,iso) values ('UGANDA','UG');
+insert into countries (default_name,iso) values ('UKRAINE','UA');
+insert into countries (default_name,iso) values ('UNITED ARAB EMIRATES','AE');
+insert into countries (default_name,iso) values ('UNITED KINGDOM','GB');
+insert into countries (default_name,iso) values ('UNITED STATES','US');
+insert into countries (default_name,iso) values ('UNITED STATES MINOR OUTLYING ISLANDS','UM');
+insert into countries (default_name,iso) values ('URUGUAY','UY');
+insert into countries (default_name,iso) values ('UZBEKISTAN','UZ');
+insert into countries (default_name,iso) values ('VANUATU','VU');
+insert into countries (default_name,iso) values ('VENEZUELA','VE');
+insert into countries (default_name,iso) values ('VIET NAM','VN');
+insert into countries (default_name,iso) values ('VIRGIN ISLANDS, BRITISH','VG');
+insert into countries (default_name,iso) values ('VIRGIN ISLANDS, U.S.','VI');
+insert into countries (default_name,iso) values ('WALLIS AND FUTUNA','WF');
+insert into countries (default_name,iso) values ('WESTERN SAHARA','EH');
+insert into countries (default_name,iso) values ('YEMEN','YE');
+insert into countries (default_name,iso) values ('YUGOSLAVIA','YU');
+insert into countries (default_name,iso) values ('ZAMBIA','ZM');
+insert into countries (default_name,iso) values ('ZIMBABWE','ZW');
+
+-- set feedback on;
+commit;
+
+
+
Index: openacs-4/packages/ref-countries/sql/oracle/ref-country-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ref-countries/sql/oracle/Attic/ref-country-create.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ref-countries/sql/oracle/ref-country-create.sql 27 Aug 2001 22:14:57 -0000 1.1
@@ -0,0 +1,104 @@
+-- packages/ref-country/sql/oracle/ref-country-create.sql
+--
+-- @author jon@jongriffin.com.com
+-- @creation-date 2001-08-27
+-- @cvs-id $Id: ref-country-create.sql,v 1.1 2001/08/27 22:14:57 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-country-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ref-countries/sql/oracle/Attic/ref-country-drop.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ref-countries/sql/oracle/ref-country-drop.sql 27 Aug 2001 22:14:57 -0000 1.1
@@ -0,0 +1,35 @@
+-- Drop the ACS Reference Country data
+--
+-- @author jon@jongriffin.com
+-- @cvs-id $Id: ref-country-drop.sql,v 1.1 2001/08/27 22:14:57 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
+
Index: openacs-4/packages/ref-countries/sql/postgresql/ref-country-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ref-countries/sql/postgresql/Attic/ref-country-create.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ref-countries/sql/postgresql/ref-country-create.sql 27 Aug 2001 22:15:59 -0000 1.1
@@ -0,0 +1,96 @@
+-- packages/ref-country/sql/postgresql/ref-country-create.sql
+--
+-- @author jon@jongriffin.com.com
+-- @creation-date 2001-08-27
+-- @cvs-id $Id: ref-country-create.sql,v 1.1 2001/08/27 22:15:59 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/ref-country-data.sql
+
+
+
Index: openacs-4/packages/ref-countries/www/doc/index.html
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ref-countries/www/doc/index.html,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ref-countries/www/doc/index.html 27 Aug 2001 22:17:24 -0000 1.1
@@ -0,0 +1,25 @@
+
+
+
+Reference Country Documentation
+
+Reference Country Documentation
+
+Engineering Docs
+
+Current docs are always at:
+jongriffin.com
+Release Notes
+
+Please file bugs in the SDM.
+
+
+jon@jongriffin.com
+
+
+
+