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
--- openacs-4/packages/contacts/sql/postgresql/contacts-search-create.sql	25 Nov 2005 18:11:39 -0000	1.10
+++ /dev/null	1 Jan 1970 00:00:00 -0000
@@ -1,180 +0,0 @@
--- 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 2005/11/25 18:11:39 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
