Index: openacs-4/packages/ref-countries/sql/postgresql/ref-countries-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ref-countries/sql/postgresql/ref-countries-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ref-countries/sql/postgresql/ref-countries-create.sql 15 Sep 2001 00:49:28 -0000 1.1 @@ -0,0 +1,95 @@ +-- packages/ref-country/sql/postgresql/ref-country-create.sql +-- +-- @author jon@jongriffin.com.com +-- @creation-date 2001-08-27 +-- @cvs-id $Id: ref-countries-create.sql,v 1.1 2001/09/15 00:49:28 donb 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 + n3 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.n3 is ' + This is the numeric code - hardly used. +'; + +-- add this table into the reference repository + +select acs_reference__new ( + 'COUNTRIES', -- table_name + '2000-08-21', + 'ISO 3166', -- source + 'http://www.din.de/gremien/nas/nabd/iso3166ma/codlstp1/db_en.html', -- source_url + now() -- effective_date + ); + +-- 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 char(2) + 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. +'; + +-- DRB: Added this so the drop script will get rid of it. Currently +-- country_names is unused. + +select acs_reference__new ( + 'COUNTRY_NAMES', -- table_name + null, + 'Internal', -- source + '', -- source_url + now() -- effective_date + ); +-- I need to know the easy way to add extended chars in sqlplus then I can add french and spanish + +-- ISO country codes +begin; +\i ../common/ref-country-data.sql +end; Index: openacs-4/packages/ref-countries/sql/postgresql/ref-countries-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ref-countries/sql/postgresql/ref-countries-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ref-countries/sql/postgresql/ref-countries-drop.sql 15 Sep 2001 00:49:28 -0000 1.1 @@ -0,0 +1,26 @@ +-- Drop the ACS Reference Country data +-- +-- @author jon@jongriffin.com +-- @cvs-id $Id: ref-countries-drop.sql,v 1.1 2001/09/15 00:49:28 donb Exp $ + +-- 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 + +create function inline_0() returns integer as ' +declare + rec acs_reference_repositories%ROWTYPE; +begin + for rec in select * from acs_reference_repositories where upper(table_name) like ''COUNTR%'' loop + execute ''drop table '' || rec.table_name; + perform acs_reference__delete(rec.repository_id); + end loop; + return 0; +end;' language 'plpgsql'; + +select inline_0(); +drop function inline_0(); + Fisheye: Tag 1.2 refers to a dead (removed) revision in file `openacs-4/packages/ref-countries/sql/postgresql/ref-country-create.sql'. Fisheye: No comparison available. Pass `N' to diff? Index: openacs-4/packages/ref-language/sql/postgresql/ref-language-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ref-language/sql/postgresql/ref-language-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/ref-language/sql/postgresql/ref-language-create.sql 15 Sep 2001 00:49:28 -0000 1.1 @@ -0,0 +1,46 @@ +-- packages/ref-language/sql/postgresql/language.sql +-- +-- @author jon@jongriffin.com +-- @creation-date 2000-11-21 +-- @cvs-id $Id: ref-language-create.sql,v 1.1 2001/09/15 00:49:28 donb 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', + null, + 'ISO 639', + 'http://www.iso.ch', + now() +); + +-- data +begin; +\i ../common/ref-language-data.sql +end;