Index: openacs-4/packages/categories/sql/oracle/categories-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/sql/oracle/categories-create.sql,v diff -u -N -r1.4 -r1.5 --- openacs-4/packages/categories/sql/oracle/categories-create.sql 8 Feb 2004 17:30:45 -0000 1.4 +++ openacs-4/packages/categories/sql/oracle/categories-create.sql 11 Feb 2004 13:25:27 -0000 1.5 @@ -237,9 +237,173 @@ '; +----- +-- Synonyms +----- + +create table category_synonyms ( + synonym_id integer not null + constraint category_synonyms_pk primary key, + category_id integer not null + constraint category_synonyms_cat_fk + references categories on delete cascade, + locale varchar(5) not null + constraint category_synonyms_locale_fk + references ad_locales on delete cascade, + name varchar(100) not null, + synonym_p char(1) default 't' + constraint category_synonyms_synonym_p_ck + check (synonym_p in ('t','f')) +); + +-- to get all synonyms in given locale +create index category_synonyms_locale_ix on category_synonyms(category_id, locale); +-- to sort synonyms by name +create index category_synonyms_name_ix on category_synonyms(category_id, name); + +create sequence category_synonyms_id_seq; + +comment on table category_synonyms is ' + Stores multilingual synonyms of the categories. +'; +comment on column category_synonyms.synonym_id is ' + Primary key. +'; +comment on column category_synonyms.category_id is ' + Category the synonyms are refering to. +'; +comment on column category_synonyms.locale is ' + Language of the synonym. +'; +comment on column category_synonyms.name is ' + Actual synonym of the category in given language. +'; +comment on column category_synonyms.synonym_p is ' + Marks if the entry is a synonym to be edited by user or is a copy + of a category translation and cannot be edited directly. +'; + +create table category_synonym_index ( + -- category synonyms split up in 3-grams to be used by fuzzy search + synonym_id integer not null + constraint category_synonym_index_fk + references category_synonyms on delete cascade, + trigram char(3) not null +); + +-- to get all synonyms of given 3-gram +create index category_syn_index_trigram_ix on category_synonym_index(trigram); +-- to delete all 3-grams of given synonym +create index category_syn_index_synonym_ix on category_synonym_index(synonym_id); + +comment on table category_synonym_index is ' + Stores the synonym cut down in portions of 3 characters + to be used in search. +'; +comment on column category_synonym_index.synonym_id is ' + Id of the synonym refered to. +'; +comment on column category_synonym_index.trigram is ' + 3 character part of the synonym. +'; + +create table category_search ( + query_id integer not null + constraint category_search_id_pk primary key, + search_text varchar(200) not null, + locale varchar(5) not null + constraint category_search_locale_fk + references ad_locales on delete cascade, + queried_count integer default 1 not null, + last_queried date default sysdate not null, + constraint category_search_query_un + unique (search_text, locale) +); + +-- to delete old queries +create index category_search_date_ix on category_search(last_queried); + +create sequence category_search_id_seq; + +comment on table category_search is ' + Stores users multilingual search texts for category synonyms. +'; +comment on column category_search.query_id is ' + Primary key. +'; +comment on column category_search.locale is ' + Language of the search text. +'; +comment on column category_search.search_text is ' + Actual search text in given language. +'; +comment on column category_search.queried_count is ' + Counts how often this search text has been used by users. +'; +comment on column category_search.last_queried is ' + Date of last usage of this search text. + A sweeper will delete search texts not used for a while. +'; + +create table category_search_index ( + query_id integer not null + constraint category_search_index_fk + references category_search on delete cascade, + trigram char(3) not null +); + +-- to get all search texts of given 3-gram +create index category_search_ind_trigram_ix on category_search_index(trigram); +-- to delete all 3-grams of given search text +create index category_search_ind_query_ix on category_search_index(query_id); + +comment on table category_search_index is ' + Stores the search text cut down in portions of 3 characters + to be used in search. +'; +comment on column category_search_index.query_id is ' + Id of the search text refered to. +'; +comment on column category_search_index.trigram is ' + 3 character part of the search text. +'; + +create table category_search_results ( + query_id integer not null + constraint category_results_query_fk + references category_search on delete cascade, + synonym_id integer not null + constraint category_results_synonym_fk + references category_synonyms on delete cascade, + similarity integer not null, + constraint category_search_results_pk + primary key (query_id, synonym_id) +); + +-- to sort all matches found by similarity +create index category_results_similarity_ix on category_search_results (query_id, similarity); + +comment on table category_search_results is ' + Stores the result of a users search in synonyms, + stores matching synonyms and their degree of similarity + to the search text. +'; +comment on column category_search_results.query_id is ' + Id of the search text. +'; +comment on column category_search_results.synonym_id is ' + Id of the synonym found. +'; +comment on column category_search_results.similarity is ' + Percent of similarity between search text and found synonym. +'; + + + @@category-tree-package.sql @@category-package.sql @@category-link-package.sql +@@category-synonym-package.sql @@categories-permissions.sql Index: openacs-4/packages/categories/sql/oracle/categories-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/sql/oracle/categories-drop.sql,v diff -u -N -r1.2 -r1.3 --- openacs-4/packages/categories/sql/oracle/categories-drop.sql 8 Feb 2004 17:30:45 -0000 1.2 +++ openacs-4/packages/categories/sql/oracle/categories-drop.sql 11 Feb 2004 13:25:27 -0000 1.3 @@ -5,6 +5,16 @@ -- @creation-date 2003-04-16 -- +drop table category_search_results; +drop table category_search_index; +drop table category_search; +drop table category_synonym_index; +drop table category_synonyms; +drop sequence category_search_id_seq; +drop sequence category_synonyms_id_seq; +drop trigger ins_synonym_on_ins_transl_trg; +drop trigger upd_synonym_on_upd_transl_trg; + drop table category_links; drop sequence category_links_id_seq; @@ -56,6 +66,7 @@ 'category_tree_grant_permissions','category_admin'); / +drop package category_synonym; drop package category_link; drop package category_tree; drop package category; Index: openacs-4/packages/categories/sql/oracle/category-synonym-package.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/sql/oracle/category-synonym-package.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/categories/sql/oracle/category-synonym-package.sql 11 Feb 2004 13:25:27 -0000 1.1 @@ -0,0 +1,300 @@ +-- +-- The Categories Package +-- Extension for category synonyms +-- +-- @author Bernd Schmeil (bernd@thebernd.de) +-- @author Timo Hentschel (timo@timohentschel.de) +-- @creation-date 2004-01-08 +-- + +CREATE or REPLACE PACKAGE category_synonym AS + FUNCTION new ( + name in category_synonyms.name%TYPE, + locale in category_synonyms.locale%TYPE, + category_id in categories.category_id%TYPE, + synonym_id in category_synonyms.synonym_id%TYPE default null + ) RETURN integer; + + PROCEDURE del ( + synonym_id in category_synonyms.synonym_id%TYPE + ); + + FUNCTION edit ( + synonym_id in category_synonyms.synonym_id%TYPE, + name in category_synonyms.name%TYPE, + locale in category_synonyms.locale%TYPE + ) RETURN integer; + + FUNCTION search ( + search_text in category_search.search_text%TYPE, + locale in category_search.locale%TYPE + ) RETURN integer; + + FUNCTION get_similarity ( + len1 integer, + len2 integer, + matches integer + ) RETURN integer; +END; +/ +show errors + +CREATE OR REPLACE PACKAGE BODY category_synonym AS + + FUNCTION convert_string ( + string in category_search.search_text%TYPE + ) RETURN varchar; + + PROCEDURE reindex ( + synonym_id in category_synonyms.synonym_id%TYPE, + name in category_synonyms.name%TYPE, + locale in category_synonyms.locale%TYPE + ); + + FUNCTION new ( + name in category_synonyms.name%TYPE, + locale in category_synonyms.locale%TYPE, + category_id in categories.category_id%TYPE, + synonym_id in category_synonyms.synonym_id%TYPE default null + ) RETURN integer + IS + v_synonym_id integer; + BEGIN + -- get new synonym_id + if (new.synonym_id is null) then + select category_synonyms_id_seq.nextval() into v_synonym_id from dual; + else + v_synonym_id := new.synonym_id; + end if; + + -- insert synonym data + insert into category_synonyms (synonym_id, category_id, locale, name, synonym_p) + values (v_synonym_id, new.category_id, new.locale, new.name, 't'); + + -- insert in synonym index and search results + category_synonym.reindex (v_synonym_id, new.name, new.locale); + + return v_synonym_id; + END new; + + + PROCEDURE del ( + synonym_id in category_synonyms.synonym_id%TYPE + ) IS + BEGIN + -- delete search results + delete from category_search_results + where synonym_id = del.synonym_id; + + -- delete synonym index + delete from category_synonym_index + where synonym_id = del.synonym_id; + + -- delete synonym + delete from category_synonyms + where synonym_id = del.synonym_id; + END del; + + + FUNCTION edit ( + synonym_id in category_synonyms.synonym_id%TYPE, + name in category_synonyms.name%TYPE, + locale in category_synonyms.locale%TYPE + ) RETURN integer IS + BEGIN + -- update synonym data + update category_synonyms + set name = edit.name, + locale = edit.locale + where synonym_id = edit.synonym_id; + + -- update synonym index and search results + category_synonym.reindex (edit.synonym_id, edit.name, edit.locale); + + return edit.synonym_id; + END edit; + + + FUNCTION search ( + search_text in category_search.search_text%TYPE, + locale in category_search.locale%TYPE + ) RETURN integer + IS + v_search_text varchar(200); + v_query_id integer; + v_len integer; + v_i integer; + BEGIN + -- check if search text already exists + select query_id into v_query_id + from category_search + where search_text = search.search_text + and locale = search.locale; + + -- simply update old search data if already exists + if (v_query_id is not null) then + update category_search + set queried_count = queried_count + 1, + last_queried = sysdate + where query_id = v_query_id; + return v_query_id; + end if; + + -- get new search query id + select category_search_id_seq.nextval() into v_query_id from dual; + + -- convert string to uppercase and substitute special chars + v_search_text := category_synonym.convert_string (search.search_text); + + -- insert search data + insert into category_search (query_id, search_text, locale, queried_count, last_queried) + values (v_query_id, search.search_text, search.locale, 1, sysdate); + + -- build search index + v_len := length (v_search_text) - 2; + v_i := 1; + while (v_i <= v_len) loop + insert into category_search_index + values (v_query_id, substr (v_search_text, v_i , 3)); + v_i := v_i + 1; + end loop; + + -- build search result + insert into category_search_results + select v_query_id, s.synonym_id, + category_synonym.get_similarity (v_len, length (s.name) - 2, count(*)) + from category_search_index si, + category_synonym_index i, + category_synonyms s + where si.query_id = v_query_id + and si.trigram = i.trigram + and s.synonym_id = i.synonym_id + and s.locale = search.locale + group by s.synonym_id, s.name; + + return v_query_id; + END search; + + + FUNCTION get_similarity ( + len1 integer, + len2 integer, + matches integer + ) RETURN integer IS + BEGIN + return (matches * 200 / (len1 + len2)); + END get_similarity; + + +----- +-- helper procs and functions +----- + + FUNCTION convert_string ( + string in category_search.search_text%TYPE + ) RETURN varchar + IS + v_index_string varchar(200); + BEGIN + -- convert string to uppercase and substitute special chars + -- TODO: complete + v_index_string := upper ( + replace ( + replace ( + replace ( + replace ( + replace ( + replace ( + replace (convert_string.string, 'ä', 'AE'), + 'Ä', 'AE'), + 'ö', 'OE'), + 'Ö', 'OE'), + 'ü', 'UE'), + 'Ü', 'UE'), + 'ß', 'SS')); + + return (' ' || v_index_string || ' '); + END convert_string; + + + PROCEDURE reindex ( + synonym_id in category_synonyms.synonym_id%TYPE, + name in category_synonyms.name%TYPE, + locale in category_synonyms.locale%TYPE + ) IS + v_name varchar(200); + v_len integer; + v_i integer; + BEGIN + -- delete old search results for this synonym + delete from category_search_results + where synonym_id = reindex.synonym_id; + + -- delete old synonym index for this synonym + delete from category_synonym_index + where synonym_id = reindex.synonym_id; + + -- convert string to uppercase and substitute special chars + v_name := category_synonym.convert_string (reindex.name); + + -- rebuild synonym index + v_len := length (v_name) - 2; + v_i := 1; + while (v_i <= v_len) loop + insert into category_synonym_index + values (reindex.synonym_id, substr (v_name, v_i , 3)); + v_i := v_i + 1; + end loop; + + -- rebuild search results + insert into category_search_results + select s.query_id, reindex.synonym_id, + category_synonym.get_similarity (v_len, length (s.search_text) - 2, count(*)) + from category_search_index si, + category_synonym_index i, + category_search s + where i.synonym_id = reindex.synonym_id + and si.trigram = i.trigram + and si.query_id = s.query_id + and s.locale = reindex.locale + group by s.query_id, s.search_text; + END reindex; + +END category_synonym; +/ +show errors + +----- +-- triggers for category synonyms +----- + +create trigger ins_synonym_on_ins_transl_trg +after insert on category_translations for each row +declare + v_synonym_id integer; +begin + -- create synonym + v_synonym_id := category_synonym.new (NEW.name, NEW.locale, NEW.category_id, null); + + -- mark synonym as not editable for users + update category_synonyms + set synonym_p = 'f' + where synonym_id = v_synonym_id; +end; + +create trigger upd_synonym_on_upd_transl_trg +before update on category_translations for each row +declare + v_synonym_id integer; +begin + -- get synonym_id of updated category translation + select synonym_id into v_synonym_id + from category_synonyms + where category_id = OLD.category_id + and name = OLD.name + and locale = OLD.locale + and synonym_p = 'f'; + + -- update synonym + category_synonym.edit (v_synonym_id, NEW.name, NEW.locale); +end; Index: openacs-4/packages/categories/sql/oracle/upgrade/upgrade-1.0d3-1.0d4.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/sql/oracle/upgrade/upgrade-1.0d3-1.0d4.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/categories/sql/oracle/upgrade/upgrade-1.0d3-1.0d4.sql 11 Feb 2004 13:25:27 -0000 1.1 @@ -0,0 +1,177 @@ +----- +-- Synonyms +----- + +create table category_synonyms ( + synonym_id integer not null + constraint category_synonyms_pk primary key, + category_id integer not null + constraint category_synonyms_cat_fk + references categories on delete cascade, + locale varchar(5) not null + constraint category_synonyms_locale_fk + references ad_locales on delete cascade, + name varchar(100) not null, + synonym_p char(1) default 't' + constraint category_synonyms_synonym_p_ck + check (synonym_p in ('t','f')) +); + +-- to get all synonyms in given locale +create index category_synonyms_locale_ix on category_synonyms(category_id, locale); +-- to sort synonyms by name +create index category_synonyms_name_ix on category_synonyms(category_id, name); + +create sequence category_synonyms_id_seq; + +comment on table category_synonyms is ' + Stores multilingual synonyms of the categories. +'; +comment on column category_synonyms.synonym_id is ' + Primary key. +'; +comment on column category_synonyms.category_id is ' + Category the synonyms are refering to. +'; +comment on column category_synonyms.locale is ' + Language of the synonym. +'; +comment on column category_synonyms.name is ' + Actual synonym of the category in given language. +'; +comment on column category_synonyms.synonym_p is ' + Marks if the entry is a synonym to be edited by user or is a copy + of a category translation and cannot be edited directly. +'; + +create table category_synonym_index ( + -- category synonyms split up in 3-grams to be used by fuzzy search + synonym_id integer not null + constraint category_synonym_index_fk + references category_synonyms on delete cascade, + trigram char(3) not null +); + +-- to get all synonyms of given 3-gram +create index category_syn_index_trigram_ix on category_synonym_index(trigram); +-- to delete all 3-grams of given synonym +create index category_syn_index_synonym_ix on category_synonym_index(synonym_id); + +comment on table category_synonym_index is ' + Stores the synonym cut down in portions of 3 characters + to be used in search. +'; +comment on column category_synonym_index.synonym_id is ' + Id of the synonym refered to. +'; +comment on column category_synonym_index.trigram is ' + 3 character part of the synonym. +'; + +create table category_search ( + query_id integer not null + constraint category_search_id_pk primary key, + search_text varchar(200) not null, + locale varchar(5) not null + constraint category_search_locale_fk + references ad_locales on delete cascade, + queried_count integer default 1 not null, + last_queried date default sysdate not null, + constraint category_search_query_un + unique (search_text, locale) +); + +-- to delete old queries +create index category_search_date_ix on category_search(last_queried); + +create sequence category_search_id_seq; + +comment on table category_search is ' + Stores users multilingual search texts for category synonyms. +'; +comment on column category_search.query_id is ' + Primary key. +'; +comment on column category_search.locale is ' + Language of the search text. +'; +comment on column category_search.search_text is ' + Actual search text in given language. +'; +comment on column category_search.queried_count is ' + Counts how often this search text has been used by users. +'; +comment on column category_search.last_queried is ' + Date of last usage of this search text. + A sweeper will delete search texts not used for a while. +'; + +create table category_search_index ( + query_id integer not null + constraint category_search_index_fk + references category_search on delete cascade, + trigram char(3) not null +); + +-- to get all search texts of given 3-gram +create index category_search_ind_trigram_ix on category_search_index(trigram); +-- to delete all 3-grams of given search text +create index category_search_ind_query_ix on category_search_index(query_id); + +comment on table category_search_index is ' + Stores the search text cut down in portions of 3 characters + to be used in search. +'; +comment on column category_search_index.query_id is ' + Id of the search text refered to. +'; +comment on column category_search_index.trigram is ' + 3 character part of the search text. +'; + +create table category_search_results ( + query_id integer not null + constraint category_results_query_fk + references category_search on delete cascade, + synonym_id integer not null + constraint category_results_synonym_fk + references category_synonyms on delete cascade, + similarity integer not null, + constraint category_search_results_pk + primary key (query_id, synonym_id) +); + +-- to sort all matches found by similarity +create index category_results_similarity_ix on category_search_results (query_id, similarity); + +comment on table category_search_results is ' + Stores the result of a users search in synonyms, + stores matching synonyms and their degree of similarity + to the search text. +'; +comment on column category_search_results.query_id is ' + Id of the search text. +'; +comment on column category_search_results.synonym_id is ' + Id of the synonym found. +'; +comment on column category_search_results.similarity is ' + Percent of similarity between search text and found synonym. +'; + +@@ ../category-synonym-package.sql + +-- insert existing category translations as synonyms +-- and build synonym index +declare + v_synonym_id integer; + v_translation category_translations%ROWTYPE; +begin + for v_translation in (select * from category_translations) + loop + v_synonym_id := category_synonym.new (v_translation.name, v_translation.locale, v_translation.category_id, null); + update category_synonyms set synonym_p = 'f' where synonym_id = v_synonym_id; + end loop; +end; +/ +show errors Index: openacs-4/packages/categories/sql/postgresql/categories-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/sql/postgresql/categories-create.sql,v diff -u -N -r1.5 -r1.6 --- openacs-4/packages/categories/sql/postgresql/categories-create.sql 10 Feb 2004 11:55:08 -0000 1.5 +++ openacs-4/packages/categories/sql/postgresql/categories-create.sql 11 Feb 2004 13:25:27 -0000 1.6 @@ -281,10 +281,172 @@ category will trigger a categorization in this category. '; +----- +-- Synonyms +----- +create table category_synonyms ( + synonym_id integer not null + constraint category_synonyms_pk primary key, + category_id integer not null + constraint category_synonyms_cat_fk + references categories on delete cascade, + locale varchar(5) not null + constraint category_synonyms_locale_fk + references ad_locales on delete cascade, + name varchar(100) not null, + synonym_p char(1) default 't' + constraint category_synonyms_synonym_p_ck + check (synonym_p in ('t','f')) +); + +-- to get all synonyms in given locale +create index category_synonyms_locale_ix on category_synonyms(category_id, locale); +-- to sort synonyms by name +create index category_synonyms_name_ix on category_synonyms(category_id, name); + +create sequence category_synonyms_id_seq; + +comment on table category_synonyms is ' + Stores multilingual synonyms of the categories. +'; +comment on column category_synonyms.synonym_id is ' + Primary key. +'; +comment on column category_synonyms.category_id is ' + Category the synonyms are refering to. +'; +comment on column category_synonyms.locale is ' + Language of the synonym. +'; +comment on column category_synonyms.name is ' + Actual synonym of the category in given language. +'; +comment on column category_synonyms.synonym_p is ' + Marks if the entry is a synonym to be edited by user or is a copy + of a category translation and cannot be edited directly. +'; + +create table category_synonym_index ( + -- category synonyms split up in 3-grams to be used by fuzzy search + synonym_id integer not null + constraint category_synonym_index_fk + references category_synonyms on delete cascade, + trigram char(3) not null +); + +-- to get all synonyms of given 3-gram +create index category_syn_index_trigram_ix on category_synonym_index(trigram); +-- to delete all 3-grams of given synonym +create index category_syn_index_synonym_ix on category_synonym_index(synonym_id); + +comment on table category_synonym_index is ' + Stores the synonym cut down in portions of 3 characters + to be used in search. +'; +comment on column category_synonym_index.synonym_id is ' + Id of the synonym refered to. +'; +comment on column category_synonym_index.trigram is ' + 3 character part of the synonym. +'; + +create table category_search ( + query_id integer not null + constraint category_search_id_pk primary key, + search_text varchar(200) not null, + locale varchar(5) not null + constraint category_search_locale_fk + references ad_locales on delete cascade, + queried_count integer default 1 not null, + last_queried timestamptz default current_timestamp not null, + constraint category_search_query_un + unique (search_text, locale) +); + +-- to delete old queries +create index category_search_date_ix on category_search(last_queried); + +create sequence category_search_id_seq; + +comment on table category_search is ' + Stores users multilingual search texts for category synonyms. +'; +comment on column category_search.query_id is ' + Primary key. +'; +comment on column category_search.locale is ' + Language of the search text. +'; +comment on column category_search.search_text is ' + Actual search text in given language. +'; +comment on column category_search.queried_count is ' + Counts how often this search text has been used by users. +'; +comment on column category_search.last_queried is ' + Date of last usage of this search text. + A sweeper will delete search texts not used for a while. +'; + +create table category_search_index ( + query_id integer not null + constraint category_search_index_fk + references category_search on delete cascade, + trigram char(3) not null +); + +-- to get all search texts of given 3-gram +create index category_search_ind_trigram_ix on category_search_index(trigram); +-- to delete all 3-grams of given search text +create index category_search_ind_query_ix on category_search_index(query_id); + +comment on table category_search_index is ' + Stores the search text cut down in portions of 3 characters + to be used in search. +'; +comment on column category_search_index.query_id is ' + Id of the search text refered to. +'; +comment on column category_search_index.trigram is ' + 3 character part of the search text. +'; + +create table category_search_results ( + query_id integer not null + constraint category_results_query_fk + references category_search on delete cascade, + synonym_id integer not null + constraint category_results_synonym_fk + references category_synonyms on delete cascade, + similarity integer not null, + constraint category_search_results_pk + primary key (query_id, synonym_id) +); + +-- to sort all matches found by similarity +create index category_results_similarity_ix on category_search_results (query_id, similarity); + +comment on table category_search_results is ' + Stores the result of a users search in synonyms, + stores matching synonyms and their degree of similarity + to the search text. +'; +comment on column category_search_results.query_id is ' + Id of the search text. +'; +comment on column category_search_results.synonym_id is ' + Id of the synonym found. +'; +comment on column category_search_results.similarity is ' + Percent of similarity between search text and found synonym. +'; + + \i category-tree-package.sql \i category-package.sql \i category-link-package.sql +\i category-synonym-package.sql \i categories-permissions.sql Index: openacs-4/packages/categories/sql/postgresql/categories-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/sql/postgresql/categories-drop.sql,v diff -u -N -r1.4 -r1.5 --- openacs-4/packages/categories/sql/postgresql/categories-drop.sql 8 Feb 2004 17:30:45 -0000 1.4 +++ openacs-4/packages/categories/sql/postgresql/categories-drop.sql 11 Feb 2004 13:25:27 -0000 1.5 @@ -5,6 +5,14 @@ -- @creation-date 2003-04-16 -- +drop table category_search_results; +drop table category_search_index; +drop table category_search; +drop table category_synonym_index; +drop table category_synonyms; +drop sequence category_search_id_seq; +drop sequence category_synonyms_id_seq; + drop table category_links; drop sequence category_links_id_seq; @@ -37,6 +45,16 @@ select acs_object_type__drop_type('category_tree', 't'); end; +drop function category_synonym__convert_string (varchar); +drop function category_synonym__get_similarity (integer, integer, bigint); +drop function category_synonym__search (varchar, varchar); +drop function category_synonym__reindex (integer, varchar, varchar); +drop function category_synonym__new (varchar, varchar, integer, integer); +drop function category_synonym__del (integer); +drop function category_synonym__edit (integer, varchar, varchar); +drop function category_synonym__edit_cat_trans_trg () cascade; +drop function category_synonym__new_cat_trans_trg () cascade; + drop function category_link__new (integer,integer); drop function category_link__del (integer); Index: openacs-4/packages/categories/sql/postgresql/category-synonym-package.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/sql/postgresql/category-synonym-package.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/categories/sql/postgresql/category-synonym-package.sql 11 Feb 2004 13:25:27 -0000 1.1 @@ -0,0 +1,273 @@ +-- +-- The Categories Package +-- Extension for category synonyms +-- +-- @author Bernd Schmeil (bernd@thebernd.de) +-- @author Timo Hentschel (timo@timohentschel.de) +-- @creation-date 2004-01-08 +-- + +create or replace function category_synonym__convert_string (varchar(100)) +returns varchar(200) as ' +-- return string to build search index +declare + p_name alias for $1; + v_index_string varchar(200); +begin + -- convert string to uppercase and substitute special chars + -- TODO: complete + v_index_string := upper ( + replace ( + replace ( + replace ( + replace ( + replace ( + replace ( + replace (p_name, ''ä'', ''AE''), + ''Ä'', ''AE''), + ''ö'', ''OE''), + ''Ö'', ''OE''), + ''ü'', ''UE''), + ''Ü'', ''UE''), + ''ß'', ''SS'')); + + return ('' '' || v_index_string || '' ''); +end;' language 'plpgsql'; + +create or replace function category_synonym__get_similarity (integer, integer, bigint) +returns integer as ' +-- calculates similarity of two strings +declare + p_len1 alias for $1; + p_len2 alias for $2; + p_matches alias for $3; +begin + return (p_matches * 200 / (p_len1 + p_len2)); +end;' language 'plpgsql'; + +create or replace function category_synonym__search (varchar(100), varchar(5)) +returns integer as ' +-- return id for search string +declare + p_search_text alias for $1; + p_locale alias for $2; + v_search_text varchar(200); + v_query_id integer; + v_len integer; + v_i integer; +begin + -- check if search text already exists + select query_id into v_query_id + from category_search + where search_text = p_search_text + and locale = p_locale; + + -- simply update old search data if already exists + if (v_query_id is not null) then + update category_search + set queried_count = queried_count + 1, + last_queried = date(''now'') + where query_id = v_query_id; + return (v_query_id); + end if; + + -- get new search query id + v_query_id := nextval (''category_search_id_seq''); + + -- convert string to uppercase and substitute special chars + v_search_text := category_synonym__convert_string (p_search_text); + + -- insert search data + insert into category_search (query_id, search_text, locale, queried_count, last_queried) + values (v_query_id, p_search_text, p_locale, 1, date(''now'')); + + -- build search index + v_len := length (v_search_text) - 2; + v_i := 1; + while (v_i <= v_len) loop + insert into category_search_index + values (v_query_id, substring (v_search_text, v_i , 3)); + v_i := v_i + 1; + end loop; + + -- build search result + insert into category_search_results + select v_query_id, s.synonym_id, + category_synonym__get_similarity (v_len, length (s.name) - 2, count(*)) + from category_search_index si, + category_synonym_index i, + category_synonyms s + where si.query_id = v_query_id + and si.trigram = i.trigram + and s.synonym_id = i.synonym_id + and s.locale = p_locale + group by s.synonym_id, s.name; + + return (v_query_id); +end;' language 'plpgsql'; + +create or replace function category_synonym__reindex (integer, varchar(100), varchar(5)) +returns integer as ' +-- build search index for synonym +declare + p_synonym_id alias for $1; + p_name alias for $2; + p_locale alias for $3; + v_name varchar(200); + v_len integer; + v_i integer; +begin + -- delete old search results for this synonym + delete from category_search_results + where synonym_id = p_synonym_id; + + -- delete old synonym index for this synonym + delete from category_synonym_index + where synonym_id = p_synonym_id; + + -- convert string to uppercase and substitute special chars + v_name := category_synonym__convert_string (p_name); + + -- rebuild synonym index + v_len := length (v_name) - 2; + v_i := 1; + while (v_i <= v_len) loop + insert into category_synonym_index + values (p_synonym_id, substring (v_name, v_i , 3)); + v_i := v_i + 1; + end loop; + + -- rebuild search results + insert into category_search_results + select s.query_id, p_synonym_id, + category_synonym__get_similarity (v_len, length (s.search_text) - 2, count(*)) + from category_search_index si, + category_synonym_index i, + category_search s + where i.synonym_id = p_synonym_id + and si.trigram = i.trigram + and si.query_id = s.query_id + and s.locale = p_locale + group by s.query_id, s.search_text; + + return (1); +end;' language 'plpgsql'; + +create or replace function category_synonym__new (varchar(100), varchar(5), integer, integer) +returns integer as ' +declare + p_name alias for $1; + p_locale alias for $2; + p_category_id alias for $3; + p_synonym_id alias for $4; + v_synonym_id integer; +begin + -- get new synonym_id + if (p_synonym_id is null) then + v_synonym_id := nextval (''category_synonyms_id_seq''); + else + v_synonym_id := p_synonym_id; + end if; + + -- insert synonym data + insert into category_synonyms (synonym_id, category_id, locale, name, synonym_p) + values (v_synonym_id, p_category_id, p_locale, p_name, ''t''); + + -- insert in synonym index and search results + PERFORM category_synonym__reindex (v_synonym_id, p_name, p_locale); + + return (v_synonym_id); +end;' language 'plpgsql'; + +create or replace function category_synonym__del (integer) +returns integer as ' +-- delete synonym +declare + p_synonym_id alias for $1; +begin + -- delete search results + delete from category_search_results + where synonym_id = p_synonym_id; + + -- delete synonym index + delete from category_synonym_index + where synonym_id = p_synonym_id; + + -- delete synonym + delete from category_synonyms + where synonym_id = p_synonym_id; + + return (1); +end;' language 'plpgsql'; + +create or replace function category_synonym__edit (integer, varchar(100), varchar(5)) +returns integer as ' +declare + p_synonym_id alias for $1; + p_new_name alias for $2; + p_locale alias for $3; +begin + -- update synonym data + update category_synonyms + set name = p_new_name, + locale = p_locale + where synonym_id = p_synonym_id; + + -- update synonym index and search results + PERFORM category_synonym__reindex (p_synonym_id, p_new_name, p_locale); + + return (p_synonym_id); +end;' language 'plpgsql'; + + +----- +-- triggers for category synonyms +----- + +create or replace function category_synonym__new_cat_trans_trg () +returns trigger as ' +-- trigger function for inserting category translation +declare + v_synonym_id integer; +begin + -- create synonym + v_synonym_id := category_synonym__new (NEW.name, NEW.locale, NEW.category_id, null); + + -- mark synonym as not editable for users + update category_synonyms + set synonym_p = ''f'' + where synonym_id = v_synonym_id; + + return null; +end;' language 'plpgsql'; + +create or replace function category_synonym__edit_cat_trans_trg () +returns trigger as ' +-- trigger function for updating a category translation +declare + v_synonym_id integer; +begin + -- get synonym_id of updated category translation + select synonym_id into v_synonym_id + from category_synonyms + where category_id = OLD.category_id + and name = OLD.name + and locale = OLD.locale + and synonym_p = ''f''; + + -- update synonym + PERFORM category_synonym__edit (v_synonym_id, NEW.name, NEW.locale); + + return null; +end;' language 'plpgsql'; + + +create trigger category_synonym__insert_cat_trans_trg +after insert +on category_translations for each row +execute procedure category_synonym__new_cat_trans_trg(); + +create trigger category_synonym__update_cat_trans_trg +before update +on category_translations for each row +execute procedure category_synonym__edit_cat_trans_trg(); Index: openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.0d3-1.0d4.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.0d3-1.0d4.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/categories/sql/postgresql/upgrade/upgrade-1.0d3-1.0d4.sql 11 Feb 2004 13:25:27 -0000 1.1 @@ -0,0 +1,200 @@ +drop function category_synonym__convert_string (varchar); +drop function category_synonym__get_similarity (integer, integer, bigint); +drop function category_synonym__search (varchar, varchar); +drop function category_synonym__reindex (integer, varchar, varchar); +drop function category_synonym__new (varchar, varchar, integer, integer); +drop function category_synonym__del (integer); +drop function category_synonym__edit (integer, varchar, varchar); +drop function category_synonym__edit_cat_trans_trg () cascade; +drop function category_synonym__new_cat_trans_trg () cascade; + +drop table category_search_results; +drop table category_search_index; +drop table category_search; +drop table category_synonym_index; +drop table category_synonyms; +drop sequence category_search_id_seq; +drop sequence category_synonyms_id_seq; + +----- +-- Synonyms +----- + +create table category_synonyms ( + synonym_id integer not null + constraint category_synonyms_pk primary key, + category_id integer not null + constraint category_synonyms_cat_fk + references categories on delete cascade, + locale varchar(5) not null + constraint category_synonyms_locale_fk + references ad_locales on delete cascade, + name varchar(100) not null, + synonym_p char(1) default 't' + constraint category_synonyms_synonym_p_ck + check (synonym_p in ('t','f')) +); + +-- to get all synonyms in given locale +create index category_synonyms_locale_ix on category_synonyms(category_id, locale); +-- to sort synonyms by name +create index category_synonyms_name_ix on category_synonyms(category_id, name); + +create sequence category_synonyms_id_seq; + +comment on table category_synonyms is ' + Stores multilingual synonyms of the categories. +'; +comment on column category_synonyms.synonym_id is ' + Primary key. +'; +comment on column category_synonyms.category_id is ' + Category the synonyms are refering to. +'; +comment on column category_synonyms.locale is ' + Language of the synonym. +'; +comment on column category_synonyms.name is ' + Actual synonym of the category in given language. +'; +comment on column category_synonyms.synonym_p is ' + Marks if the entry is a synonym to be edited by user or is a copy + of a category translation and cannot be edited directly. +'; + +create table category_synonym_index ( + -- category synonyms split up in 3-grams to be used by fuzzy search + synonym_id integer not null + constraint category_synonym_index_fk + references category_synonyms on delete cascade, + trigram char(3) not null +); + +-- to get all synonyms of given 3-gram +create index category_syn_index_trigram_ix on category_synonym_index(trigram); +-- to delete all 3-grams of given synonym +create index category_syn_index_synonym_ix on category_synonym_index(synonym_id); + +comment on table category_synonym_index is ' + Stores the synonym cut down in portions of 3 characters + to be used in search. +'; +comment on column category_synonym_index.synonym_id is ' + Id of the synonym refered to. +'; +comment on column category_synonym_index.trigram is ' + 3 character part of the synonym. +'; + +create table category_search ( + query_id integer not null + constraint category_search_id_pk primary key, + search_text varchar(200) not null, + locale varchar(5) not null + constraint category_search_locale_fk + references ad_locales on delete cascade, + queried_count integer default 1 not null, + last_queried timestamptz default current_timestamp not null, + constraint category_search_query_un + unique (search_text, locale) +); + +-- to delete old queries +create index category_search_date_ix on category_search(last_queried); + +create sequence category_search_id_seq; + +comment on table category_search is ' + Stores users multilingual search texts for category synonyms. +'; +comment on column category_search.query_id is ' + Primary key. +'; +comment on column category_search.locale is ' + Language of the search text. +'; +comment on column category_search.search_text is ' + Actual search text in given language. +'; +comment on column category_search.queried_count is ' + Counts how often this search text has been used by users. +'; +comment on column category_search.last_queried is ' + Date of last usage of this search text. + A sweeper will delete search texts not used for a while. +'; + +create table category_search_index ( + query_id integer not null + constraint category_search_index_fk + references category_search on delete cascade, + trigram char(3) not null +); + +-- to get all search texts of given 3-gram +create index category_search_ind_trigram_ix on category_search_index(trigram); +-- to delete all 3-grams of given search text +create index category_search_ind_query_ix on category_search_index(query_id); + +comment on table category_search_index is ' + Stores the search text cut down in portions of 3 characters + to be used in search. +'; +comment on column category_search_index.query_id is ' + Id of the search text refered to. +'; +comment on column category_search_index.trigram is ' + 3 character part of the search text. +'; + +create table category_search_results ( + query_id integer not null + constraint category_results_query_fk + references category_search on delete cascade, + synonym_id integer not null + constraint category_results_synonym_fk + references category_synonyms on delete cascade, + similarity integer not null, + constraint category_search_results_pk + primary key (query_id, synonym_id) +); + +-- to sort all matches found by similarity +create index category_results_similarity_ix on category_search_results (query_id, similarity); + +comment on table category_search_results is ' + Stores the result of a users search in synonyms, + stores matching synonyms and their degree of similarity + to the search text. +'; +comment on column category_search_results.query_id is ' + Id of the search text. +'; +comment on column category_search_results.synonym_id is ' + Id of the synonym found. +'; +comment on column category_search_results.similarity is ' + Percent of similarity between search text and found synonym. +'; + +\i ../category-synonym-package.sql + +-- insert existing category translations as synonyms +-- and build synonym index +create function inline_0 () +returns integer as ' +declare + rec_translations record; + v_synonym_id integer; +begin + for rec_translations in + select category_id, name, locale + from category_translations + loop + v_synonym_id := category_synonym__new (rec_translations.name, rec_translations.locale, rec_translations.category_id, null); + update category_synonyms set synonym_p = ''f'' where synonym_id = v_synonym_id; + end loop; + return 0; +end;' language 'plpgsql'; +select inline_0 (); +drop function inline_0 (); Index: openacs-4/packages/categories/tcl/categories-init.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/tcl/categories-init.tcl,v diff -u -N -r1.2 -r1.3 --- openacs-4/packages/categories/tcl/categories-init.tcl 1 Feb 2004 19:12:51 -0000 1.2 +++ openacs-4/packages/categories/tcl/categories-init.tcl 11 Feb 2004 13:25:28 -0000 1.3 @@ -10,3 +10,5 @@ category::reset_translation_cache category_tree::reset_translation_cache category_tree::reset_cache + +ad_schedule_proc -thread t -schedule_proc ns_schedule_daily [list 0 16] category_synonym::search_sweeper Index: openacs-4/packages/categories/tcl/category-synonyms-procs-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/tcl/category-synonyms-procs-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/categories/tcl/category-synonyms-procs-oracle.xql 11 Feb 2004 13:25:28 -0000 1.1 @@ -0,0 +1,59 @@ + + + + oracle8.1.6 + + + + begin + :1 := category_synonym.new ( + synonym_id => :synonym_id, + name => :name, + locale => :locale, + category_id => :category_id + ); + end; + + + + + + begin + :1 := category_synonym.edit ( + synonym_id => :synonym_id, + name => :name, + locale => :locale + ); + end; + + + + + + begin + category_synonym.del ( + synonym_id => :synonym_id + ); + end; + + + + + + begin + :1 := category_synonym.search ( + search_text => :search_text, + locale => :locale + ); + end; + + + + + + delete from category_search + where last_queried < sysdate - 1 + + + + Index: openacs-4/packages/categories/tcl/category-synonyms-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/tcl/category-synonyms-procs-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/categories/tcl/category-synonyms-procs-postgresql.xql 11 Feb 2004 13:25:28 -0000 1.1 @@ -0,0 +1,51 @@ + + + + postgresql7.1 + + + + select category_synonym__new ( + :name, + :locale, + :category_id, + :synonym_id + ) + + + + + + select category_synonym__edit ( + :synonym_id, + :name, + :locale + ) + + + + + + select category_synonym__del ( + :synonym_id + ) + + + + + + select category_synonym__search ( + :search_text, + :locale + ) + + + + + + delete from category_search + where last_queried < current_timestamp - 1 + + + + Index: openacs-4/packages/categories/tcl/category-synonyms-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/tcl/category-synonyms-procs.tcl,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/categories/tcl/category-synonyms-procs.tcl 11 Feb 2004 13:25:28 -0000 1.1 @@ -0,0 +1,103 @@ +ad_library { + category-synonyms procs for the site-wide categorization package. + + @author Bernd Schmeil (bernd@thebernd.de) + @author Timo Hentschel (timo@timohentschel.de) + + @creation-date 8 January 2004 + @cvs-id $Id: +} + + +namespace eval category_synonym {} + +ad_proc -public category_synonym::add { + -name:required + {-locale ""} + -category_id:required + {-synonym_id ""} +} { + Inserts a new synonym. + + @option name synonym name. + @option locale locale of the language. [ad_conn locale] used by default. + @option category_id id of the category of the synonym to be inserted. + @option synonym_id synonym_id of the synonym to be inserted. + @author Bernd Schmeil (bernd@thebernd.de) + @author Timo Hentschel (timo@timohentschel.de) +} { + if {[empty_string_p $locale]} { + set locale [ad_conn locale] + } + + db_transaction { + set synonym_id [db_exec_plsql insert_synonym ""] + } + + return $synonym_id +} + +ad_proc -public category_synonym::edit { + -synonym_id:required + -name:required + {-locale ""} +} { + Updates a synonym. + + @option synonym_id synonym_id of the synonym to be updated. + @option name synonym name. + @option locale locale of the language. [ad_conn locale] used by default. + @author Bernd Schmeil (bernd@thebernd.de) + @author Timo Hentschel (timo@timohentschel.de) +} { + if {[empty_string_p $locale]} { + set locale [ad_conn locale] + } + + db_transaction { + set synonym_id [db_exec_plsql update_synonym ""] + } + + return $synonym_id +} + +ad_proc -public category_synonym::delete { synonym_id } { + Deletes a synonym. + + @option synonym_id synonym_id of the synonym to be deleted. + @author Bernd Schmeil (bernd@thebernd.de) + @author Timo Hentschel (timo@timohentschel.de) +} { + db_transaction { + db_exec_plsql delete_synonym "" + } +} + +ad_proc -public category_synonym::search { + -search_text:required + {-locale ""} +} { + Gets all matching synonyms for search text in result table. + + @option search_text string to be matched against. + @option locale locale of the language. [ad_conn locale] used by default. + @author Bernd Schmeil (bernd@thebernd.de) + @author Timo Hentschel (timo@timohentschel.de) +} { + if {[empty_string_p $locale]} { + set locale [ad_conn locale] + } + + db_transaction { + set query_id [db_exec_plsql new_search ""] + } + + return $query_id +} + +ad_proc -private category_synonym::search_sweeper { +} { + Deletes results of old searches +} { + db_dml delete_old_searches "" +} Index: openacs-4/packages/categories/www/search-result.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/www/search-result.adp,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/categories/www/search-result.adp 11 Feb 2004 13:25:28 -0000 1.1 @@ -0,0 +1,5 @@ + +@page_title;noquote@ +@context_bar;noquote@ + + Index: openacs-4/packages/categories/www/search-result.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/www/search-result.tcl,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/categories/www/search-result.tcl 11 Feb 2004 13:25:28 -0000 1.1 @@ -0,0 +1,43 @@ +ad_page_contract { + + Displays matched synonyms for search string + + @author Timo Hentschel (timo@timohentschel.de) + @cvs-id $Id: +} { + query_id:integer,notnull +} -properties { + page_title:onevalue + context_bar:onevalue +} + +set user_id [ad_maybe_redirect_for_registration] +set package_id [ad_conn package_id] +set locale [ad_conn locale] + +db_1row get_search_string "" +set page_title "Search Result for \"$search_text\"" +set context_bar "Search Result" + +db_multirow -extend {category_name} search_result get_search_result "" { + set category_name [category::get_name $category_id $locale] +} + + +template::list::create \ + -name search_result \ + -key synonym_id \ + -actions [list "New Search" [export_vars -no_empty -base index { search_text }] "New Search"] \ + -elements { + similarity { + label "Similarity" + } + synonym_name { + label "Search Result" + display_template { + @search_result.synonym_name@ (@search_result.category_name@) + } + } + } + +ad_return_template Index: openacs-4/packages/categories/www/search-result.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/www/search-result.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/categories/www/search-result.xql 11 Feb 2004 13:25:28 -0000 1.1 @@ -0,0 +1,30 @@ + + + + + + + + select search_text + from category_search + where query_id = :query_id + + + + + + + + + select s.category_id, s.synonym_id, r.similarity, s.name as synonym_name, + s.synonym_p + from category_search_results r, category_synonyms s + where s.synonym_id = r.synonym_id + and r.query_id = :query_id + order by r.similarity desc, lower(s.name) + + + + + + Index: openacs-4/packages/categories/www/cadmin/synonym-delete-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/www/cadmin/synonym-delete-2-oracle.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/categories/www/cadmin/synonym-delete-2-oracle.xql 11 Feb 2004 13:25:28 -0000 1.1 @@ -0,0 +1,20 @@ + + + + oracle8.1.6 + + + + + select s.synonym_id + from category_synonyms s, categories c + where s.synonym_id in ([join $synonym_id ,]) + and c.category_id = s.category_id + and acs_permission.permission_p(c.tree_id,:user_id,'category_tree_write') = 't' + and s.synonym_p = 't' + + + + + + Index: openacs-4/packages/categories/www/cadmin/synonym-delete-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/www/cadmin/synonym-delete-2-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/categories/www/cadmin/synonym-delete-2-postgresql.xql 11 Feb 2004 13:25:28 -0000 1.1 @@ -0,0 +1,20 @@ + + + + postgresql7.1 + + + + + select s.synonym_id + from category_synonyms s, categories c + where s.synonym_id in ([join $synonym_id ,]) + and c.category_id = s.category_id + and acs_permission__permission_p(c.tree_id,:user_id,'category_tree_write') = 't' + and s.synonym_p = 't' + + + + + + Index: openacs-4/packages/categories/www/cadmin/synonym-delete-2.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/www/cadmin/synonym-delete-2.tcl,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/categories/www/cadmin/synonym-delete-2.tcl 11 Feb 2004 13:25:28 -0000 1.1 @@ -0,0 +1,27 @@ +ad_page_contract { + + Deletes a category synonym. + + @author Timo Hentschel (timo@timohentschel.de) + @cvs-id $Id: +} { + synonym_id:integer,multiple + category_id:integer,notnull + tree_id:integer,notnull + {locale ""} + object_id:integer,optional +} + +set user_id [ad_maybe_redirect_for_registration] +permission::require_permission -object_id $tree_id -privilege category_tree_write + +db_transaction { + foreach synonym_id [db_list check_synonyms_for_delete ""] { + category_synonym::delete $synonym_id + } +} on_error { + ad_return_complaint 1 {{Error deleting category synonym.}} + return +} + +ad_returnredirect [export_vars -no_empty -base synonyms-view {category_id tree_id locale object_id}] Index: openacs-4/packages/categories/www/cadmin/synonym-delete.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/www/cadmin/synonym-delete.adp,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/categories/www/cadmin/synonym-delete.adp 11 Feb 2004 13:25:28 -0000 1.1 @@ -0,0 +1,16 @@ + +@page_title;noquote@ +@context_bar;noquote@ +@locale;noquote@ + +

+ Are you sure you want to delete these synonyms: +

+ + + +

+ Delete +     + No, Cancel +

Index: openacs-4/packages/categories/www/cadmin/synonym-delete.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/www/cadmin/synonym-delete.tcl,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/categories/www/cadmin/synonym-delete.tcl 11 Feb 2004 13:25:28 -0000 1.1 @@ -0,0 +1,49 @@ +ad_page_contract { + + Deletes a synonym + + @author Timo Hentschel (timo@timohentschel.de) + @cvs-id $Id: +} { + synonym_id:integer,multiple + category_id:integer,notnull + tree_id:integer,notnull + {locale ""} + object_id:integer,optional +} -properties { + page_title:onevalue + context_bar:onevalue + locale:onevalue + delete_url:onevalue + cancel_url:onevalue +} + +set user_id [ad_maybe_redirect_for_registration] +permission::require_permission -object_id $tree_id -privilege category_tree_write + +set tree_name [category_tree::get_name $tree_id $locale] +set category_name [category::get_name $category_id $locale] +set page_title "Delete synonyms of category \"$tree_name :: $category_name\"" + +set context_bar [category::context_bar $tree_id $locale [value_if_exists object_id]] +lappend context_bar [list [export_vars -no_empty -base synonyms-view { category_id tree_id locale object_id }] "Synonyms of $category_name"] "Delete synonyms" + +set delete_url [export_vars -no_empty -base synonym-delete-2 { synonym_id:multiple category_id tree_id locale object_id }] +set cancel_url [export_vars -no_empty -base synonyms-view { category_id tree_id locale object_id }] + + +db_multirow synonyms get_synonyms_to_delete "" + +template::list::create \ + -name synonyms \ + -no_data "None" \ + -elements { + synonym_name { + label "Name" + } + language { + label "Language" + } + } + +ad_return_template Index: openacs-4/packages/categories/www/cadmin/synonym-delete.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/www/cadmin/synonym-delete.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/categories/www/cadmin/synonym-delete.xql 11 Feb 2004 13:25:28 -0000 1.1 @@ -0,0 +1,17 @@ + + + + + + + select s.name as synonym_name, l.label as language + from category_synonyms s, ad_locales l + where s.locale = l.locale + and s.synonym_id in ([join $synonym_id ,]) + order by lower(l.label), lower(s.name) + + + + + + Index: openacs-4/packages/categories/www/cadmin/synonym-form.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/www/cadmin/synonym-form.adp,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/categories/www/cadmin/synonym-form.adp 11 Feb 2004 13:25:28 -0000 1.1 @@ -0,0 +1,9 @@ + +@page_title;noquote@ +@context_bar;noquote@ +f +synonym_form.name + +
+ +
Index: openacs-4/packages/categories/www/cadmin/synonym-form.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/www/cadmin/synonym-form.tcl,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/categories/www/cadmin/synonym-form.tcl 11 Feb 2004 13:25:28 -0000 1.1 @@ -0,0 +1,59 @@ +ad_page_contract { + + Form to add/edit a synonym. + + @author Timo Hentschel (timo@timohentschel.de) + @cvs-id $Id: +} { + synonym_id:integer,optional + category_id:integer,notnull + tree_id:integer,notnull + {locale ""} + object_id:integer,optional +} -properties { + context_bar:onevalue + page_title:onevalue +} + +set user_id [ad_maybe_redirect_for_registration] +permission::require_permission -object_id $tree_id -privilege category_tree_write + +set tree_name [category_tree::get_name $tree_id $locale] +set category_name [category::get_name $category_id $locale] + +if {[info exists synonym_id]} { + set action "Edit" +} else { + set action "Add" +} +set page_title "$action category synonym of \"$tree_name :: $category_name\"" + +set context_bar [category::context_bar $tree_id $locale [value_if_exists object_id]] +lappend context_bar [list [export_vars -no_empty -base synonyms-view { category_id tree_id locale object_id }] "Synonyms of $category_name"] "$action synonym" + + +set languages [lang::system::get_locale_options] + +ad_form -name synonym_form -action synonym-form -export { category_id tree_id locale object_id } -form { + {synonym_id:key(category_synonyms_id_seq)} + {name:text {label "Name"} {html {size 50 maxlength 200}}} + {language:text(select) {label "Language"} {options $languages}} +} -new_request { + set name "" + if {![empty_string_p [ad_conn locale]]} { + set language [ad_conn locale] + } else { + set language [ad_parameter DefaultLocale acs-lang "en_US"] + } +} -edit_request { + db_1row get_synonym "" +} -new_data { + category_synonym::add -name $name -locale $language -category_id $category_id -synonym_id $synonym_id +} -edit_data { + category_synonym::edit -name $name -locale $language -synonym_id $synonym_id +} -after_submit { + ad_returnredirect [export_vars -no_empty -base synonyms-view {category_id tree_id locale object_id}] + ad_script_abort +} + +ad_return_template Index: openacs-4/packages/categories/www/cadmin/synonym-form.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/www/cadmin/synonym-form.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/categories/www/cadmin/synonym-form.xql 11 Feb 2004 13:25:28 -0000 1.1 @@ -0,0 +1,16 @@ + + + + + + + select name, locale as language + from category_synonyms + where synonym_id = :synonym_id + and synonym_p = 't' + + + + + + Index: openacs-4/packages/categories/www/cadmin/synonyms-view.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/www/cadmin/synonyms-view.adp,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/categories/www/cadmin/synonyms-view.adp 11 Feb 2004 13:25:28 -0000 1.1 @@ -0,0 +1,6 @@ + +@page_title;noquote@ +@context_bar;noquote@ +@locale;noquote@ + + Index: openacs-4/packages/categories/www/cadmin/synonyms-view.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/www/cadmin/synonyms-view.tcl,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/categories/www/cadmin/synonyms-view.tcl 11 Feb 2004 13:25:28 -0000 1.1 @@ -0,0 +1,95 @@ +ad_page_contract { + + Displays list of synonyms of a category. + + @author Timo Hentschel (timo@timohentschel.de) + @cvs-id $Id: +} { + category_id:integer,notnull + tree_id:integer,notnull + {locale ""} + object_id:integer,optional + orderby:optional +} -properties { + page_title:onevalue + context_bar:onevalue + synonyms:multirow +} + +set user_id [ad_maybe_redirect_for_registration] +permission::require_permission -object_id $tree_id -privilege category_tree_write + +set tree_name [category_tree::get_name $tree_id $locale] +set category_name [category::get_name $category_id $locale] +set page_title "Synonyms for category \"$tree_name :: $category_name\"" + +set context_bar [category::context_bar $tree_id $locale [value_if_exists object_id]] +lappend context_bar "Synonyms of $category_name" + + +#---------------------------------------------------------------------- +# List builder +#---------------------------------------------------------------------- + +template::list::create \ + -name synonyms \ + -no_data "None" \ + -key synonym_id \ + -actions [list "Add synonym" [export_vars -no_empty -base synonym-form { category_id tree_id locale object_id }] "Add new synonym"] \ + -bulk_actions { + "Delete" "synonym-delete" "Delete checked synonyms" + } -bulk_action_export_vars { category_id tree_id locale object_id + } -orderby { + default_value language,asc + synonym_name { + label synonym_name + orderby_asc {lower(s.name) asc, lower(l.label) asc} + orderby_desc {lower(s.name) desc, lower(l.label) desc} + } + language { + label language + orderby_asc {lower(l.label) asc, lower(s.name) asc} + orderby_desc {lower(l.label) desc, lower(s.name) desc} + } + } -filters { + category_id {} + tree_id {} + locale {} + object_id {} + } -elements { + edit { + sub_class narrow + display_template { + Edit + } + link_url_col edit_url + link_html {title "Edit this synonym"} + } + synonym_name { + label "Synonym" + link_url_col edit_url + link_html {title "Edit this synonym"} + } + language { + label "Language" + } + delete { + sub_class narrow + display_template { + Delete + } + link_url_col delete_url + link_html { title "Delete synonym" } + } + } + + +db_multirow synonyms get_synonyms "" + +multirow extend synonyms edit_url delete_url +multirow foreach synonyms { + set edit_url [export_vars -no_empty -base synonym-form { synonym_id category_id tree_id locale object_id }] + set delete_url [export_vars -no_empty -base synonym-delete { synonym_id category_id tree_id locale object_id }] +} + +ad_return_template Index: openacs-4/packages/categories/www/cadmin/synonyms-view.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/www/cadmin/synonyms-view.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/categories/www/cadmin/synonyms-view.xql 11 Feb 2004 13:25:28 -0000 1.1 @@ -0,0 +1,18 @@ + + + + + + + select s.synonym_id, s.name as synonym_name, l.label as language + from category_synonyms s, ad_locales l + where l.locale = s.locale + and s.category_id = :category_id + and s.synonym_p = 't' + [template::list::orderby_clause -orderby -name synonyms] + + + + + + Index: openacs-4/packages/categories/www/cadmin/tree-view.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/categories/www/cadmin/tree-view.tcl,v diff -u -N -r1.9 -r1.10 --- openacs-4/packages/categories/www/cadmin/tree-view.tcl 11 Feb 2004 11:44:00 -0000 1.9 +++ openacs-4/packages/categories/www/cadmin/tree-view.tcl 11 Feb 2004 13:25:28 -0000 1.10 @@ -56,7 +56,7 @@ # List builder #---------------------------------------------------------------------- -multirow extend one_tree usage_url add_url edit_url delete_url parent_url phase_in_url phase_out_url links_view_url +multirow extend one_tree usage_url add_url edit_url delete_url parent_url phase_in_url phase_out_url links_view_url synonyms_view_url multirow foreach one_tree { set usage_url [export_vars -no_empty -base category-usage { category_id tree_id locale object_id }] if { $can_write_p } { @@ -65,6 +65,7 @@ set delete_url [export_vars -no_empty -base category-delete { category_id tree_id locale object_id }] set parent_url [export_vars -no_empty -base category-parent-change { category_id tree_id locale object_id }] set links_view_url [export_vars -no_empty -base category-links-view { category_id tree_id locale object_id }] + set synonyms_view_url [export_vars -no_empty -base synonyms-view { category_id tree_id locale object_id }] if { [template::util::is_true $deprecated_p] } { set phase_in_url [export_vars -no_empty -base category-phase-in { category_id tree_id locale object_id }] } else { @@ -82,6 +83,7 @@ Edit } link_url_col edit_url + link_html {title "Edit this category"} } } @@ -112,7 +114,8 @@ label "Actions" display_template { Change parent     - View links + View links     + View synonyms } }