Hi,
just got the first prototype running. Here is the table definition for the main table:
create table im_search_objects (
object_id integer
constraint im_search_objects_object_id_fk
references acs_objects
on delete cascade,
-- may include "object types" outside of OpenACS
-- that are not in the "acs_object_types" table.
object_type_id integer
constraint im_search_objects_object_type_id_fk
references im_search_object_types
on delete cascade,
-- What is the topmost container for this object?
-- Allows to speed up the elimination of objects
-- that the current user can't access
biz_object_id integer
constraint im_search_objects_biz_obj_id_fk
references acs_objects
on delete cascade,
-- counter for number of accesses to this object
-- either from the permission() proc or from
-- reading in the server log file.
hit_count integer,
-- Full Text Index
fti tsvector,
-- For tables that don't respect the OpenACS object
-- scheme we may get "object_id"s that start with 0.
primary key (object_id, object_type_id)
);
create index im_search_objects_fti_idx on im_search_objects using gist(fti);
create index im_search_objects_object_id_idx on im_search_objects (object_id);
The following code adds a new object to the search index:
create or replace function users_tsearch ()
returns trigger as '
declare
v_string varchar;
begin
select coalesce(email, '''') || '' '' ||
coalesce(url, '''') || '' '' ||
coalesce(first_names, '''') || '' '' ||
coalesce(last_name, '''') || '' '' ||
coalesce(username, '''') || '' '' ||
coalesce(screen_name, '''') || '' '' ||
coalesce(username, '''')
into v_string
from cc_users
where user_id = new.user_id;
perform im_search_update(new.user_id, ''user'', 0, v_string);
return new;
end;' language 'plpgsql';
CREATE TRIGGER users_tsearch_tr
BEFORE INSERT or UPDATE
ON users
FOR EACH ROW
EXECUTE PROCEDURE users_tsearch();
I can't really see why there should be something "bad" about it right now...
Bests,
Frank
