--

-- @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;