Index: openacs-4/packages/contacts/sql/postgresql/contacts-search-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/contacts/sql/postgresql/contacts-search-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/contacts/sql/postgresql/contacts-search-create.sql 30 Nov 2005 15:31:01 -0000 1.10.2.2 @@ -0,0 +1,180 @@ +-- contacts-search-create.sql +-- +-- @author Matthew Geddert openacs@geddert.com +-- @creation-date 2004-07-28 +-- @cvs-id $Id: contacts-search-create.sql,v 1.10.2.2 2005/11/30 15:31:01 miguelm Exp $ +-- +-- + +create table contact_searches ( + search_id integer + constraint contact_searches_id_fk references acs_objects(object_id) on delete cascade + constraint contact_searches_id_pk primary key, + title varchar(255), + owner_id integer + constraint contact_searches_owner_id_fk references acs_objects(object_id) on delete cascade + constraint contact_searches_owner_id_nn not null, + all_or_any varchar(20) + constraint contact_searches_and_or_all_nn not null, + object_type varchar(1000) + constraint contact_searches_object_type_nn not null, + deleted_p boolean default 'f' + constraint contact_searches_deleted_p_nn not null, + aggregated_attribute integer +); + +-- create the content type +select acs_object_type__create_type ( + 'contact_search', -- content_type + 'Contacts Search', -- pretty_name + 'Contacts Searches', -- pretty_plural + 'acs_object', -- supertype + 'contact_searches', -- table_name (should this be pm_task?) + 'search_id', -- id_column + 'contact_search', -- package_name + 'f', -- abstract_p + NULL, -- type_extension_table + NULL -- name_method +); + +create table contact_search_conditions ( + condition_id integer + constraint contact_search_conditions_id_pk primary key, + search_id integer + constraint contact_search_conditions_search_id_fk references contact_searches(search_id) on delete cascade + constraint contact_search_conditions_search_id_nn not null, + type varchar(255) + constraint contact_search_conditions_type_nn not null, + var_list text + constraint contact_search_conditions_var_list_nn not null +); + +create table contact_search_log ( + search_id integer + constraint contact_search_log_search_id_fk references contact_searches(search_id) on delete cascade + constraint contact_search_logs_search_id_nn not null, + user_id integer + constraint contact_search_log_user_id_fk references users(user_id) on delete cascade + constraint contact_search_log_user_id_nn not null, + n_searches integer + constraint contact_search_log_n_searches_nn not null, + last_search timestamptz + constraint contact_search_log_last_search_nn not null, + unique(search_id,user_id) +); + +select define_function_args ('contact_search__new', 'search_id,title,owner_id,all_or_any,object_type,deleted_p;f,creation_date,creation_user,creation_ip,context_id'); + +create or replace function contact_search__new (integer,varchar,integer,varchar,varchar,boolean,timestamptz,integer,varchar,integer) +returns integer as ' +declare + p_search_id alias for $1; + p_title alias for $2; + p_owner_id alias for $3; + p_all_or_any alias for $4; + p_object_type alias for $5; + p_deleted_p alias for $6; + p_creation_date alias for $7; + p_creation_user alias for $8; + p_creation_ip alias for $9; + p_context_id alias for $10; + v_search_id contact_searches.search_id%TYPE; +begin + v_search_id := acs_object__new( + p_search_id, + ''contact_search'', + p_creation_date, + p_creation_user, + p_creation_ip, + coalesce(p_context_id, p_owner_id) + ); + + insert into contact_searches + (search_id,title,owner_id,all_or_any,object_type,deleted_p) + values + (v_search_id,p_title,p_owner_id,p_all_or_any,p_object_type,p_deleted_p); + + return v_search_id; + +end;' language 'plpgsql'; + + + +create or replace function contact_search__log (integer,integer) +returns integer as ' +declare + p_search_id alias for $1; + p_user_id alias for $2; + v_last_search_id integer; + v_exists_p boolean; +begin + -- if the user has used this search in the last 60 minutes we do not log it as a new search + v_last_search_id := search_id + from contact_search_log + where user_id = p_user_id + and last_search > now() - ''1 hour''::interval + order by last_search desc + limit 1; + + if v_last_search_id != p_search_id or v_last_search_id is null then + -- this is a new search we need to log + v_exists_p := ''1''::boolean + from contact_search_log + where search_id = p_search_id + and user_id = p_user_id; + + if v_exists_p then + update contact_search_log + set n_searches = n_searches + 1, + last_search = now() + where search_id = p_search_id + and user_id = p_user_id; + else + insert into contact_search_log + (search_id,user_id,n_searches,last_search) + values + (p_search_id,p_user_id,''1''::integer,now()); + end if; + else + -- we just update the last search time but no n_sesions + update contact_search_log + set last_search = now() + where search_id = p_search_id + and user_id = p_user_id; + end if; + + return ''1''; +end;' language 'plpgsql'; + + +-- Create a sequence and a table for extended searches. + +create sequence contact_extend_search_seq; + +create table contact_extend_options ( + extend_id integer + constraint contact_extend_options_pk primary key, + var_name varchar(100) unique not null, + pretty_name varchar(100) not null, + subquery varchar(5000) not null, + aggregated_p char default 'f' constraint check_aggregate_p check (aggregated_p in ('t','f')), + description varchar(500) +); + +-- Creates a table to map contact_extend_options(extend_id)'s to +-- contact_searches(search_id) + +create table contact_search_extend_map ( + search_id integer + constraint contact_search_extend_map_search_id_fk + references contact_searches (search_id) + on delete cascade, + extend_id integer + constraint contact_search_extend_map_extend_id_fk + references contact_extend_options (extend_id) + on delete cascade, + attribute_id integer + constraint contact_search_extend_map_attribute_id_fk + references acs_attributes (attribute_id) + on delete cascade +); \ No newline at end of file