Forum OpenACS Q&A: Re: .LRN Search thoughts and architecture - Please give feedback

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