-- -- @author Joel Aufrecht (joel@aufrecht.org) -- @creation-date 2004-02-01 -- @cvs-id $Id: vocabulary-tables-create.sql,v 1.1.1.1 2004/02/23 23:00:07 joela Exp $ -- select acs_object_type__create_type ( 'vocab_word', -- object_type 'word', -- pretty_name 'words', -- pretty_plural 'acs_object', -- supertype 'vocab_word', -- table_name 'id', -- id_column null, -- package_name 'f', -- abstract_p null, -- type_extension_table 'vocab_word__name' -- name_method ); create table vocab_word ( id integer constraint vocab_word_id_fk references acs_objects(object_id) constraint vocab_word_pk primary key, word varchar(50) not null, locale varchar(30) constraint vocab_word_locale_fk references ad_locales, ipa_phonetic varchar(100), local_phonetic varchar(100), package_id integer not null constraint vocab_word_package_id_fk references apm_packages ); comment on table vocab_word is ' Each record is a single word in a single locale. Homonyms should have their own entries.'; comment on column vocab_word.word is 'The spelling of the word in its native character set. Should be unicode.'; comment on column vocab_word.ipa_phonetic is 'International Phonetic Alphabet spelling.'; comment on column vocab_word.local_phonetic is 'Spelling in appropriate phonetic alphabet.'; create or replace function vocab_word__name (integer) returns varchar as ' declare p_id alias for $1; v_name varchar; begin select word into v_name from vocab_word where id = p_id; return v_name; end; ' language 'plpgsql'; create or replace function vocab_word__new ( integer, -- package_id varchar, -- word varchar, -- locale varchar, -- ipa_phonetic varchar, -- local_phonetic integer, -- creation_user varchar -- creation_ip ) returns integer as ' declare p_package_id alias for $1; p_word alias for $2; p_locale alias for $3; p_ipa_phonetic alias for $4; p_local_phonetic alias for $5; p_creation_user alias for $6; p_creation_ip alias for $7; v_id integer; begin v_id := acs_object__new ( null, ''vocab_word'', current_timestamp, p_creation_user, p_creation_ip, p_package_id ); insert into vocab_word ( id, package_id, word, locale, ipa_phonetic, local_phonetic ) values ( v_id, p_package_id, p_word, p_locale, p_ipa_phonetic, p_local_phonetic ); PERFORM acs_permission__grant_permission( v_id, p_creation_user, ''admin'' ); return v_id; end; ' language 'plpgsql'; create or replace function vocab_word__delete (integer) returns integer as ' declare p_id alias for $1; begin delete from vocab_word where id = p_id; PERFORM acs_object__delete(p_id); return 0; end; ' language 'plpgsql'; create table vocab_definition ( id serial constraint vocab_def_pk primary key, word_id integer constraint vocab_def_word_fk references vocab_word, sort_order integer, locale varchar(30) constraint vocab_word_locale_fk references ad_locales, definition text, unique (word_id, locale, sort_order) ); comment on table vocab_definition is 'Each record is one definition of one word in one locale. This means that definitions are arbitrary text instead of links to other words. TODO: Perhaps we should optionally link to another word here. so that a definition can be more or less structured.'; create table vocab_phonetic ( id serial constraint vocab_phonetic_pk primary key, phonetic_symbol varchar(5), locale varchar(30) constraint vocab_word_locale_fk references ad_locales, sort_order integer ); comment on table vocab_phonetic is 'Each record is a letter in a phonetic alphabet. Deliberately package_ignorant.'; comment on column vocab_phonetic.locale is 'Null is assumed to be IPA.'; create table vocab_phonetic_example ( id serial constraint vocab_phonetic_example_pk primary key, phonetic_id integer constraint vocab_phonetic_phonetic_fk references vocab_phonetic, word_id integer constraint vocab_phonetic_word_fk references vocab_word, comments varchar(100), unique (phonetic_id, word_id) ); comment on table vocab_phonetic_example is 'Each record is a word with contains a sound of an PHONETIC symbol. Each record is indirectly locale-specific and package-specific, via word. Since we can auto-generate some of this via direct lookup of phonetic symbols within word text, this table is semi-superfluous. It might see life again holding additional or special comments.'; select content_type__create_type( 'vocab_sentence', -- content_type 'content_revision', -- supertype 'sentence', -- pretty_name, 'sentences', -- pretty_plural 'vocab_sentence', -- table_name 'id', -- id_column null -- name_method ); -- we have to create a custom locale field because locale in cr_items -- is varchar(4), even though ad_locales locale is varchar(30) -- TODO: make this FK to ad_locales select content_type__create_attribute( 'vocab_sentence', -- content_type 'locale2', -- attribute_name 'string', -- datatype 'Locale', -- pretty_name 'Locales', -- pretty_plural 1, -- sort_order null, -- default_value 'varchar(30)' -- column_spec ); select content_type__create_attribute( 'vocab_sentence', -- content_type 'sort_order', -- attribute_name 'integer', -- datatype 'Sort Order', -- pretty_name 'Sort Orders', -- pretty_plural 2, -- sort_order null, -- default_value 'integer' -- column_spec ); -- necessary to work around limitation of content repository: select content_folder__register_content_type(-100,'vocab_sentence','t'); create sequence vocab_sentence_name_seq;