+++ /dev/null
-SET client_min_messages TO warning;
-
--- ********** Create auxiliar tables **********
-
-CREATE TABLE artifact_idx (
- artifact_id integer,
- group_artifact_id integer,
- vectors tsvector
-);
-
-CREATE TABLE artifact_message_idx (
- id integer,
- artifact_id integer,
- vectors tsvector
-);
-
-CREATE TABLE doc_data_idx (
- docid integer,
- group_id integer,
- vectors tsvector
-);
-
-CREATE TABLE forum_idx (
- msg_id integer,
- group_id integer,
- vectors tsvector
-);
-
-CREATE TABLE frs_file_idx (
- file_id integer,
- release_id integer,
- vectors tsvector
-);
-
-CREATE TABLE frs_release_idx (
- release_id integer,
- vectors tsvector
-);
-
-CREATE TABLE groups_idx (
- group_id integer,
- vectors tsvector
-);
-
-CREATE TABLE news_bytes_idx (
- id integer,
- vectors tsvector
-);
-
-CREATE TABLE project_task_idx (
- project_task_id integer,
- vectors tsvector
-);
-
-CREATE TABLE skills_data_idx (
- skills_data_id integer,
- vectors tsvector
-);
-
-CREATE TABLE users_idx (
- user_id integer,
- vectors tsvector
-);
-
--- ********** Populate with current data and create index **********
-
-INSERT INTO artifact_idx (artifact_id, group_artifact_id, vectors)
-SELECT artifact_id, group_artifact_id, to_tsvector('default', coalesce(details,'') ||'
-'|| coalesce(summary,'')) AS vectors
-FROM artifact ORDER BY artifact_id;
-
-CREATE INDEX artifact_idxFTI ON artifact_idx USING gist(vectors);
-
-INSERT INTO artifact_message_idx (id, artifact_id, vectors)
-SELECT id, artifact_id, to_tsvector('default', coalesce(body,'')) AS vectors
-FROM artifact_message ORDER BY id;
-
-CREATE INDEX artifact_message_idxFTI ON artifact_message_idx USING gist(vectors);
-
-INSERT INTO doc_data_idx (docid, group_id, vectors)
-SELECT docid, group_id, to_tsvector('default', coalesce(title,'') ||'
-'|| coalesce(description,'')) AS vectors
-FROM doc_data ORDER BY docid;
-
-CREATE INDEX doc_data_idxFTI ON doc_data_idx USING gist(vectors);
-
-INSERT INTO forum_idx (msg_id, group_id, vectors)
-(SELECT f.msg_id, g.group_id, to_tsvector('default', coalesce(f.subject,'') ||'
-'|| coalesce(f.body,'')) AS vectors
-FROM forum f, forum_group_list g WHERE f.group_forum_id = g.group_forum_id)
-ORDER BY msg_id;
-
-CREATE INDEX forum_idxFTI ON forum_idx USING gist(vectors);
-
-INSERT INTO frs_file_idx (file_id, release_id, vectors)
-SELECT file_id, release_id, to_tsvector('default', coalesce(filename,'')) AS vectors
-FROM frs_file ORDER BY file_id;
-
-CREATE INDEX frs_file_idxFTI ON frs_file_idx USING gist(vectors);
-
-INSERT INTO frs_release_idx (release_id, vectors)
-SELECT release_id, to_tsvector('default', coalesce(changes,'') ||'
-'|| coalesce(notes,'') ||' '|| coalesce(name,'')) AS vectors
-FROM frs_release ORDER BY release_id;
-
-CREATE INDEX frs_release_idxFTI ON frs_release_idx USING gist(vectors);
-
-INSERT INTO groups_idx (group_id, vectors)
-SELECT group_id, to_tsvector('default', coalesce(group_name,'') ||'
-'|| coalesce(short_description,'') ||' '|| coalesce(unix_group_name,'')) AS vectors
-FROM groups ORDER BY group_id;
-
-CREATE INDEX groups_idxFTI ON groups_idx USING gist(vectors);
-
-INSERT INTO news_bytes_idx (id, vectors)
-SELECT id, to_tsvector('default', coalesce(summary,'') ||'
-'|| coalesce(details,'')) AS vectors
-FROM news_bytes ORDER BY id;
-
-CREATE INDEX news_bytes_idxFTI ON news_bytes_idx USING gist(vectors);
-
-INSERT INTO project_task_idx (project_task_id, vectors)
-SELECT project_task_id, to_tsvector('default', coalesce(summary,'') ||'
-'|| coalesce(details,'')) AS vectors
-FROM project_task ORDER BY project_task_id;
-
---
--- TODO project_messages
---
-
-CREATE INDEX project_task_idxFTI ON project_task_idx USING gist(vectors);
-
-INSERT INTO skills_data_idx (skills_data_id, vectors)
-SELECT skills_data_id, to_tsvector('default', coalesce(title,'') ||'
-'|| coalesce(keywords,'')) AS vectors
-FROM skills_data ORDER BY skills_data_id;
-
-CREATE INDEX skills_data_idxFTI ON skills_data_idx USING gist(vectors);
-
-INSERT INTO users_idx (user_id, vectors)
-SELECT user_id, to_tsvector('default', coalesce(user_name,'') ||'
-'|| coalesce(realname,'')) AS vectors
-FROM users ORDER BY user_id;
-
-CREATE INDEX users_idxFTI ON users_idx USING gist(vectors);
-
--- VACUUM FULL ANALYZE;
-
--- ********** Create trigger function to update idx tables **********
-
-CREATE OR REPLACE FUNCTION update_vectors() RETURNS TRIGGER AS '
-DECLARE
-table_name TEXT;
-BEGIN
- table_name := TG_ARGV[0];
- -- **** artifact table ****
- IF table_name = ''artifact'' THEN
- IF TG_OP = ''INSERT'' THEN
- INSERT INTO artifact_idx (artifact_id, group_artifact_id, vectors) VALUES (NEW.artifact_id, NEW.group_artifact_id, to_tsvector(\'default\', coalesce(NEW.details,\'\') ||\' \'|| coalesce(NEW.summary,\'\')));
- ELSIF TG_OP = ''UPDATE'' THEN
- UPDATE artifact_idx SET group_artifact_id=NEW.group_artifact_id, vectors=to_tsvector(\'default\', coalesce(NEW.details,\'\') ||\' \'|| coalesce(NEW.summary,\'\')) WHERE artifact_id=NEW.artifact_id;
- ELSIF TG_OP = ''DELETE'' THEN
- DELETE FROM artifact_idx WHERE artifact_id=OLD.artifact_id;
- END IF;
- -- **** artifact_message table ****
- ELSIF table_name = ''artifact_message'' THEN
- IF TG_OP = ''INSERT'' THEN
- INSERT INTO artifact_message_idx (id, artifact_id, vectors) VALUES (NEW.id, NEW.artifact_id, to_tsvector(\'default\', coalesce(NEW.body,\'\')));
- ELSIF TG_OP = ''UPDATE'' THEN
- UPDATE artifact_message_idx SET artifact_id=NEW.artifact_id, vectors=to_tsvector(\'default\', coalesce(NEW.body,\'\')) WHERE id=NEW.id;
- ELSIF TG_OP = ''DELETE'' THEN
- DELETE FROM artifact_message_idx WHERE id=OLD.id;
- END IF;
- -- **** doc_data table ****
- ELSIF table_name = ''doc_data'' THEN
- IF TG_OP = ''INSERT'' THEN
- INSERT INTO doc_data_idx (docid, group_id, vectors) VALUES (NEW.docid, NEW.group_id, to_tsvector(\'default\', coalesce(NEW.title,\'\') ||\' \'|| coalesce(NEW.description,\'\')));
- ELSIF TG_OP = ''UPDATE'' THEN
- UPDATE doc_data_idx SET group_id=NEW.group_id, vectors=to_tsvector(\'default\', coalesce(NEW.title,\'\') ||\' \'|| coalesce(NEW.description,\'\')) WHERE docid=NEW.docid;
- ELSIF TG_OP = ''DELETE'' THEN
- DELETE FROM doc_data_idx WHERE docid=OLD.docid;
- END IF;
- -- **** forum table ****
- ELSIF table_name = ''forum'' THEN
- IF TG_OP = ''INSERT'' THEN
- INSERT INTO forum_idx (msg_id, group_id, vectors) (SELECT f.msg_id, g.group_id, to_tsvector(\'default\', coalesce(f.subject,\'\') ||\' \'||
- coalesce(f.body,\'\')) AS vectors FROM forum f, forum_group_list g WHERE f.group_forum_id = g.group_forum_id AND f.msg_id = NEW.msg_id);
- ELSIF TG_OP = ''UPDATE'' THEN
- UPDATE forum_idx SET vectors=to_tsvector(\'default\', coalesce(NEW.subject,\'\') ||\' \'|| coalesce(NEW.body,\'\')) WHERE msg_id=NEW.msg_id;
- ELSIF TG_OP = ''DELETE'' THEN
- DELETE FROM forum_idx WHERE msg_id=OLD.msg_id;
- END IF;
- -- **** frs_file table ****
- ELSIF table_name = ''frs_file'' THEN
- IF TG_OP = ''INSERT'' THEN
- INSERT INTO frs_file_idx (file_id, release_id, vectors) VALUES (NEW.file_id, NEW.release_id, to_tsvector(\'default\', coalesce(NEW.filename,\'\')));
- ELSIF TG_OP = ''UPDATE'' THEN
- UPDATE frs_file_idx SET vectors=to_tsvector(\'default\', coalesce(NEW.filename,\'\')), release_id=NEW.release_id WHERE file_id=NEW.file_id;
- ELSIF TG_OP = ''DELETE'' THEN
- DELETE FROM frs_file_idx WHERE file_id=OLD.file_id;
- END IF;
- -- **** frs_release table ****
- ELSIF table_name = ''frs_release'' THEN
- IF TG_OP = ''INSERT'' THEN
- INSERT INTO frs_release_idx (release_id, vectors) VALUES (NEW.release_id, to_tsvector(\'default\', coalesce(changes,\'\') ||\' \'|| coalesce(notes,\'\') ||\' \'|| coalesce(name,\'\')));
- ELSIF TG_OP = ''UPDATE'' THEN
- UPDATE frs_release_idx SET vectors=to_tsvector(\'default\', coalesce(changes,\'\') ||\' \'|| coalesce(notes,\'\') ||\' \'|| coalesce(name,\'\')) WHERE release_id=NEW.release_id;
- ELSIF TG_OP = ''DELETE'' THEN
- DELETE FROM frs_release_idx WHERE release_id=OLD.release_id;
- DELETE FROM frs_file_idx WHERE release_id=OLD.release_id;
- END IF;
- -- **** groups table ****
- ELSIF table_name = ''groups'' THEN
- IF TG_OP = ''INSERT'' THEN
- INSERT INTO groups_idx (group_id, vectors) VALUES (NEW.group_id, to_tsvector(\'default\', coalesce(NEW.group_name,\'\') ||\' \'|| coalesce(NEW.short_description,\'\') ||\' \'|| coalesce(NEW.unix_group_name,\'\')));
- ELSIF TG_OP = ''UPDATE'' THEN
- UPDATE groups_idx SET vectors=to_tsvector(\'default\', coalesce(NEW.group_name,\'\') ||\' \'|| coalesce(NEW.short_description,\'\') ||\' \'|| coalesce(NEW.unix_group_name,\'\')) WHERE group_id=NEW.group_id;
- ELSIF TG_OP = ''DELETE'' THEN
- DELETE FROM groups_idx WHERE group_id=OLD.group_id;
- END IF;
- -- **** news_bytes table ****
- ELSIF table_name = ''news_bytes'' THEN
- IF TG_OP = ''INSERT'' THEN
- INSERT INTO news_bytes_idx (id, vectors) VALUES (NEW.id, to_tsvector(\'default\', coalesce(NEW.summary,\'\') ||\' \'|| coalesce(NEW.details,\'\')));
- ELSIF TG_OP = ''UPDATE'' THEN
- UPDATE news_bytes_idx SET vectors=to_tsvector(\'default\', coalesce(NEW.summary,\'\') ||\' \'|| coalesce(NEW.details,\'\')) WHERE id=NEW.id;
- ELSIF TG_OP = ''DELETE'' THEN
- DELETE FROM news_bytes_idx WHERE id=OLD.id;
- END IF;
- -- **** project_task table ****
- ELSIF table_name = ''project_task'' THEN
- IF TG_OP = ''INSERT'' THEN
- INSERT INTO project_task_idx (project_task_id, vectors) VALUES (NEW.project_task_id, to_tsvector(\'default\', coalesce(NEW.summary,\'\') ||\' \'|| coalesce(NEW.details,\'\')));
- ELSIF TG_OP = ''UPDATE'' THEN
- UPDATE project_task_idx SET vectors=to_tsvector(\'default\', coalesce(NEW.summary,\'\') ||\' \'|| coalesce(NEW.details,\'\')) WHERE project_task_id=NEW.project_task_id;
- ELSIF TG_OP = ''DELETE'' THEN
- DELETE FROM project_task_idx WHERE project_task_id=OLD.project_task_id;
- END IF;
- -- **** skills_data table ****
- ELSIF table_name = ''skills_data'' THEN
- IF TG_OP = ''INSERT'' THEN
- INSERT INTO skills_data_idx (skills_data_id, vectors) VALUES (NEW.skill_data_id, to_tsvector(\'default\', coalesce(NEW.title,\'\') ||\' \'|| coalesce(NEW.keywords,\'\')));
- ELSIF TG_OP = ''UPDATE'' THEN
- UPDATE skills_data_idx SET vectors=to_tsvector(\'default\', coalesce(NEW.title,\'\') ||\' \'|| coalesce(NEW.keywords,\'\')) WHERE skills_data_id=NEW.skills_data_id;
- ELSIF TG_OP = ''DELETE'' THEN
- DELETE FROM skills_data_idx WHERE skills_data_id=OLD.skills_data_id;
- END IF;
- -- **** users table ****
- ELSIF table_name = ''users'' THEN
- IF TG_OP = ''INSERT'' THEN
- INSERT INTO users_idx (user_id, vectors) VALUES (NEW.user_id, to_tsvector(\'default\', coalesce(NEW.user_name,\'\') ||\' \'|| coalesce(NEW.realname,\'\')));
- ELSIF TG_OP = ''UPDATE'' THEN
- UPDATE users_idx SET vectors=to_tsvector(\'default\', coalesce(NEW.user_name,\'\') ||\' \'|| coalesce(NEW.realname,\'\')) WHERE user_id=NEW.user_id;
- ELSIF TG_OP = ''DELETE'' THEN
- DELETE FROM users_idx WHERE user_id=OLD.user_id;
- END IF;
- END IF;
-
- RETURN NEW;
-END;'
-LANGUAGE 'plpgsql';
-
--- ********** Set up triggers **********
-
-CREATE TRIGGER artifact_ts_update AFTER UPDATE OR INSERT OR DELETE ON artifact
-FOR EACH ROW EXECUTE PROCEDURE update_vectors('artifact');
-
-CREATE TRIGGER doc_data_ts_update AFTER UPDATE OR INSERT OR DELETE ON doc_data
-FOR EACH ROW EXECUTE PROCEDURE update_vectors('doc_data');
-
-CREATE TRIGGER forum_update AFTER UPDATE OR INSERT OR DELETE ON forum
-FOR EACH ROW EXECUTE PROCEDURE update_vectors('forum');
-
-CREATE TRIGGER frs_file_ts_update AFTER UPDATE OR INSERT OR DELETE ON frs_file
-FOR EACH ROW EXECUTE PROCEDURE update_vectors('frs_file');
-
-CREATE TRIGGER frs_release_ts_update AFTER UPDATE OR INSERT OR DELETE ON frs_release
-FOR EACH ROW EXECUTE PROCEDURE update_vectors('frs_release');
-
-CREATE TRIGGER groups_ts_update AFTER UPDATE OR INSERT OR DELETE ON groups
-FOR EACH ROW EXECUTE PROCEDURE update_vectors('groups');
-
-CREATE TRIGGER news_bytes_ts_update AFTER UPDATE OR INSERT OR DELETE ON news_bytes
-FOR EACH ROW EXECUTE PROCEDURE update_vectors('news_bytes');
-
-CREATE TRIGGER project_task_ts_update AFTER UPDATE OR INSERT OR DELETE ON project_task
-FOR EACH ROW EXECUTE PROCEDURE update_vectors('project_task');
-
-CREATE TRIGGER skills_data_ts_update AFTER UPDATE OR INSERT OR DELETE ON skills_data
-FOR EACH ROW EXECUTE PROCEDURE update_vectors('skills_data');
-
-CREATE TRIGGER users_ts_update AFTER UPDATE OR INSERT OR DELETE ON users
-FOR EACH ROW EXECUTE PROCEDURE update_vectors('users');
-
--- ********** Create types for results **********
-
-CREATE TYPE artifact_results AS (group_artifact_id integer,
- artifact_id integer,
- summary text,
- open_date integer,
- realname character varying(32)
-);
-
-CREATE TYPE doc_data_results AS (docid integer,
- title text,
- description text,
- groupname character varying(255)
-);
-
-CREATE TYPE forum_results AS (msg_id integer,
- subject text,
- post_date integer,
- realname character varying(32)
-);
-
-CREATE TYPE frs_results AS (package_name text,
- release_name text,
- release_date integer,
- release_id integer,
- realname character varying(32)
-);
-
-CREATE TYPE groups_results AS (group_name text,
- unix_group_name text,
- type_id integer,
- group_id integer,
- short_description text
-);
-
-CREATE TYPE export_groups_results AS (group_name text,
- unix_group_name text,
- type_id integer,
- group_id integer,
- short_description text,
- license integer,
- register_time integer
-);
-
-CREATE TYPE news_bytes_results AS (summary text,
- post_date integer,
- forum_id integer,
- realname text
-);
-
-CREATE TYPE project_task_results AS (project_task_id integer,
- summary text,
- percent_complete integer,
- start_date integer,
- end_date integer,
- realname text,
- project_name text,
- group_project_id integer
-);
-
-CREATE TYPE skills_data_results AS (skills_data_id integer,
- type integer,
- title text,
- start integer,
- finish integer,
- keywords text
-);
-
-CREATE TYPE trackers_results AS (artifact_id integer,
- group_artifact_id integer,
- summary text,
- open_date integer,
- realname character varying(32),
- name text
-);
-
-CREATE TYPE users_results AS (user_name text,
- user_id integer,
- realname text
-);
-
--- ********** Create search store procedures **********
-
-CREATE OR REPLACE FUNCTION artifact_search(text, int) RETURNS SETOF artifact_results AS '
- SELECT a.group_artifact_id, a.artifact_id, a.summary, a.open_date, u.realname FROM artifact a,
- (SELECT DISTINCT ON (ai.artifact_id) ai.artifact_id, COUNT(ami.id) AS total FROM artifact_idx ai LEFT OUTER JOIN artifact_message_idx ami USING (artifact_id),
- to_tsquery($1) AS q
- WHERE ai.group_artifact_id=$2
- AND (ai.vectors @@ q OR ami.vectors @@ q)
- GROUP BY ai.artifact_id) AS idx, users u, to_tsquery($1) AS q, artifact_idx ai
- WHERE idx.artifact_id=a.artifact_id
- AND u.user_id=a.submitted_by
- AND a.artifact_id=ai.artifact_id
- ORDER BY idx.total DESC, rank(ai.vectors, q) DESC'
-LANGUAGE 'SQL';
-
-CREATE OR REPLACE FUNCTION doc_data_search(text, integer, text, boolean) RETURNS SETOF doc_data_results AS '
- DECLARE
- data users_results;
- BEGIN
- IF $3 <> \'\' THEN
- IF $4 THEN
- FOR data IN SELECT doc_data.docid, headline(doc_data.title, q) AS title, headline(doc_data.description, q) AS description, doc_groups.groupname
- FROM doc_data, doc_groups, to_tsquery($1) AS q
- WHERE doc_data.doc_group = doc_groups.doc_group
- AND doc_data.group_id = $2
- AND doc_groups.doc_group IN (\'$3\')
- AND doc_data.stateid IN (1, 4, 5)
- AND doc_data.docid IN (SELECT docid FROM doc_data_idx, to_tsquery($1) AS q
- WHERE group_id = $2 AND vectors @@ q ORDER BY rank(vectors, q) DESC) LOOP
- RETURN NEXT data;
- END LOOP;
- ELSE
- FOR data IN SELECT doc_data.docid, headline(doc_data.title, q), headline(doc_data.description, q), doc_groups.groupname
- FROM doc_data, doc_groups, to_tsquery($1) AS q
- WHERE doc_data.doc_group = doc_groups.doc_group
- AND doc_data.group_id = $2
- AND doc_groups.doc_group IN (\'$3\')
- AND doc_data.stateid = 1
- AND doc_data.docid IN (SELECT docid FROM doc_data_idx, to_tsquery($1) AS q
- WHERE group_id = $2 AND vectors @@ q ORDER BY rank(vectors, q) DESC) LOOP
- RETURN NEXT data;
- END LOOP;
- END IF;
- ELSE
- IF $4 THEN
- FOR data IN SELECT doc_data.docid, headline(doc_data.title, q), headline(doc_data.description, q), doc_groups.groupname
- FROM doc_data, doc_groups, to_tsquery($1) AS q
- WHERE doc_data.doc_group = doc_groups.doc_group
- AND doc_data.group_id = $2
- AND doc_data.stateid IN (1, 4, 5)
- AND doc_data.docid IN (SELECT docid FROM doc_data_idx, to_tsquery($1) AS q
- WHERE group_id = $2 AND vectors @@ q ORDER BY rank(vectors, q) DESC) LOOP
- RETURN NEXT data;
- END LOOP;
- ELSE
- FOR data IN SELECT doc_data.docid, headline(doc_data.title, q), headline(doc_data.description, q), doc_groups.groupname
- FROM doc_data, doc_groups, to_tsquery($1) AS q
- WHERE doc_data.doc_group = doc_groups.doc_group
- AND doc_data.group_id = $2
- AND doc_data.stateid = 1
- AND doc_data.docid IN (SELECT docid FROM doc_data_idx, to_tsquery($1) AS q
- WHERE group_id = $2 AND vectors @@ q ORDER BY rank(vectors, q) DESC) LOOP
- RETURN NEXT data;
- END LOOP;
- END IF;
- END IF;
- RETURN;
- END;'
-LANGUAGE 'plpgsql';
-
-CREATE TYPE forums_results AS (msg_id integer,
- subject text,
- post_date integer,
- realname character varying(32),
- forum_name text
-);
-
-CREATE OR REPLACE FUNCTION forums_search(text, integer, text, boolean) RETURNS SETOF forums_results AS '
- DECLARE
- data forums_results;
- BEGIN
- IF $3 <> \'\' THEN
- IF $4 THEN
- FOR data IN SELECT forum.msg_id, headline(forum.subject, q) AS subject, forum.post_date, users.realname, forum_group_list.forum_name
- FROM forum, users, forum_group_list, to_tsquery($1) AS q
- WHERE users.user_id = forum.posted_by
- AND forum_group_list.group_forum_id = forum.group_forum_id
- AND forum_group_list.is_public <> 9
- AND forum_group_list.group_forum_id IN (\'$3\')
- AND forum.msg_id IN (SELECT msg_id FROM forum_idx, to_tsquery($1) AS q
- WHERE group_id = $2 AND vectors @@ q ORDER BY rank(vectors, q) DESC) LOOP
- RETURN NEXT data;
- END LOOP;
- ELSE
- FOR data IN SELECT forum.msg_id, headline(forum.subject, q) AS subject, forum.post_date, users.realname, forum_group_list.forum_name
- FROM forum, users, forum_group_list, to_tsquery($1) AS q
- WHERE users.user_id = forum.posted_by
- AND forum_group_list.group_forum_id = forum.group_forum_id
- AND forum_group_list.is_public <> 9
- AND forum_group_list.group_forum_id IN (\'$3\')
- AND forum_group_list.is_public = 1
- AND forum.msg_id IN (SELECT msg_id FROM forum_idx, to_tsquery($1) AS q
- WHERE group_id = $2 AND vectors @@ q ORDER BY rank(vectors, q) DESC) LOOP
- RETURN NEXT data;
- END LOOP;
- END IF;
- ELSE
- IF $4 THEN
- FOR data IN SELECT forum.msg_id, headline(forum.subject, q) AS subject, forum.post_date, users.realname, forum_group_list.forum_name
- FROM forum, users, forum_group_list, to_tsquery($1) AS q
- WHERE users.user_id = forum.posted_by
- AND forum_group_list.group_forum_id = forum.group_forum_id
- AND forum_group_list.is_public <> 9
- AND forum.msg_id IN (SELECT msg_id FROM forum_idx, to_tsquery($1) AS q
- WHERE group_id = $2 AND vectors @@ q ORDER BY rank(vectors, q) DESC) LOOP
- RETURN NEXT data;
- END LOOP;
- ELSE
- FOR data IN SELECT forum.msg_id, headline(forum.subject, q) AS subject, forum.post_date, users.realname, forum_group_list.forum_name
- FROM forum, users, forum_group_list, to_tsquery($1) AS q
- WHERE users.user_id = forum.posted_by
- AND forum_group_list.group_forum_id = forum.group_forum_id
- AND forum_group_list.is_public <> 9
- AND forum_group_list.is_public = 1
- AND forum.msg_id IN (SELECT msg_id FROM forum_idx, to_tsquery($1) AS q
- WHERE group_id = $2 AND vectors @@ q ORDER BY rank(vectors, q) DESC) LOOP
- RETURN NEXT data;
- END LOOP;
- END IF;
- END IF;
- RETURN;
- END;'
-LANGUAGE 'plpgsql';
-
-CREATE OR REPLACE FUNCTION forum_search(text, integer) RETURNS SETOF forum_results AS '
- SELECT forum.msg_id, headline(forum.subject, q) AS subject, forum.post_date, users.realname
- FROM forum, users, to_tsquery($1) AS q
- WHERE users.user_id=forum.posted_by
- AND msg_id IN (SELECT fi.msg_id FROM forum_idx fi, forum f, to_tsquery($1) AS q
- WHERE fi.msg_id = f.msg_id AND f.group_forum_id=$2
- AND vectors @@ q ORDER BY rank(vectors, q) DESC);'
-LANGUAGE 'SQL';
-
-CREATE OR REPLACE FUNCTION frs_search(text, integer, text, boolean) RETURNS SETOF frs_results AS '
- DECLARE
- data frs_results;
- BEGIN
- -- Search in specific sections
- IF $3 <> \'\' THEN
- -- show non public
- IF $4 THEN
- FOR data IN SELECT headline(frs_package.name, q) AS package_name, headline(frs_release.name, q) AS release_name, frs_release.release_date, frs_release.release_id, users.realname
- FROM frs_file, frs_release, users, frs_package, to_tsquery($1) AS q
- WHERE frs_release.released_by = users.user_id
- AND frs_package.package_id = frs_release.package_id
- AND frs_file.release_id=frs_release.release_id
- AND frs_package.group_id=$2
- AND frs_package.package_id IN (\'$3\')
- AND frs_release.release_id IN (SELECT r.release_id FROM frs_release_idx r
- LEFT JOIN frs_file_idx f ON r.release_id=f.release_id, to_tsquery($1) AS q
- WHERE r.vectors @@ q OR f.vectors @@ q ORDER BY rank(r.vectors, q) DESC, rank(f.vectors, q) DESC) LOOP
- RETURN NEXT data;
- END LOOP;
- ELSE
- FOR data IN SELECT headline(frs_package.name, q) AS package_name, headline(frs_release.name, q) AS release_name, frs_release.release_date, frs_release.release_id, users.realname
- FROM frs_file, frs_release, users, frs_package, to_tsquery($1) AS q
- WHERE frs_release.released_by = users.user_id
- AND frs_package.package_id = frs_release.package_id
- AND frs_file.release_id=frs_release.release_id
- AND frs_package.group_id=$2
- AND frs_package.package_id IN (\'$3\')
- AND frs_package.is_public=1
- AND frs_release.release_id IN (SELECT r.release_id FROM frs_release_idx r
- LEFT JOIN frs_file_idx f ON r.release_id=f.release_id, to_tsquery($1) AS q
- WHERE r.vectors @@ q OR f.vectors @@ q ORDER BY rank(r.vectors, q) DESC, rank(f.vectors, q) DESC) LOOP
- RETURN NEXT data;
- END LOOP;
- END IF;
- ELSE
- IF $4 THEN
- FOR data IN SELECT headline(frs_package.name, q) AS package_name, headline(frs_release.name, q) AS release_name, frs_release.release_date, frs_release.release_id, users.realname
- FROM frs_file, frs_release, users, frs_package, to_tsquery($1) AS q
- WHERE frs_release.released_by = users.user_id
- AND frs_package.package_id = frs_release.package_id
- AND frs_file.release_id=frs_release.release_id
- AND frs_package.group_id=$2
- AND frs_release.release_id IN (SELECT r.release_id FROM frs_release_idx r
- LEFT JOIN frs_file_idx f ON r.release_id=f.release_id, to_tsquery($1) AS q
- WHERE r.vectors @@ q OR f.vectors @@ q ORDER BY rank(r.vectors, q) DESC, rank(f.vectors, q) DESC) LOOP
- RETURN NEXT data;
- END LOOP;
- ELSE
- FOR data IN SELECT headline(frs_package.name, q) AS package_name, headline(frs_release.name, q) AS release_name, frs_release.release_date, frs_release.release_id, users.realname
- FROM frs_file, frs_release, users, frs_package, to_tsquery($1) AS q
- WHERE frs_release.released_by = users.user_id
- AND frs_package.package_id = frs_release.package_id
- AND frs_file.release_id=frs_release.release_id
- AND frs_package.group_id=$2
- AND frs_package.is_public=1
- AND frs_release.release_id IN (SELECT r.release_id FROM frs_release_idx r
- LEFT JOIN frs_file_idx f ON r.release_id=f.release_id, to_tsquery($1) AS q
- WHERE r.vectors @@ q OR f.vectors @@ q ORDER BY rank(r.vectors, q) DESC, rank(f.vectors, q) DESC) LOOP
- RETURN NEXT data;
- END LOOP;
- END IF;
- END IF;
- RETURN;
- END;'
-LANGUAGE 'plpgsql';
-
-CREATE OR REPLACE FUNCTION groups_search(text) RETURNS SETOF groups_results AS '
- SELECT headline(group_name, q) as group_name,
- headline(unix_group_name, q) as unix_group_name,
- type_id,
- group_id,
- headline(short_description, q) as short_description
- FROM groups, to_tsquery($1) AS q
- WHERE status IN (\'A\', \'H\') AND is_public=\'1\' AND
- group_id IN (SELECT group_id FROM groups_idx, to_tsquery($1) AS q
- WHERE vectors @@ q ORDER BY rank(vectors, q) DESC);'
-LANGUAGE 'SQL';
-
-CREATE OR REPLACE FUNCTION export_groups_search(text) RETURNS SETOF export_groups_results AS '
- SELECT headline(group_name, q) as group_name,
- headline(unix_group_name, q) as unix_group_name,
- type_id,
- group_id,
- headline(short_description, q) as short_description,
- license,
- register_time
- FROM groups, to_tsquery($1) AS q
- WHERE status IN (\'A\', \'H\') AND is_public=\'1\' AND short_description <> \'\' AND
- group_id IN (SELECT group_id FROM groups_idx, to_tsquery($1) AS q
- WHERE vectors @@ q ORDER BY rank(vectors, q) DESC);'
-LANGUAGE 'SQL';
-
-CREATE OR REPLACE FUNCTION news_bytes_search(text, integer) RETURNS SETOF news_bytes_results AS '
- SELECT headline(news_bytes.summary, q) as summary,
- news_bytes.post_date,
- news_bytes.forum_id,
- users.realname
- FROM news_bytes, users, to_tsquery($1) AS q
- WHERE (news_bytes.group_id=$2 AND news_bytes.is_approved <> ''4'' AND news_bytes.submitted_by=users.user_id) AND
- news_bytes.id IN (SELECT id FROM news_bytes_idx,
- to_tsquery($1) AS q WHERE vectors @@ q ORDER BY rank(vectors, q) DESC);'
-LANGUAGE 'SQL';
-
-CREATE OR REPLACE FUNCTION project_task_search(text, integer, text, boolean) RETURNS SETOF project_task_results AS '
- DECLARE
- data project_task_results;
- BEGIN
- -- Search in specific sections
- IF $3 <> \'\' THEN
- -- show non public
- IF $4 THEN
- FOR data IN SELECT project_task.project_task_id, headline(project_task.summary, q) AS summary, project_task.percent_complete,
- project_task.start_date, project_task.end_date, users.firstname||\' \'||users.lastname AS realname,
- project_group_list.project_name, project_group_list.group_project_id
- FROM project_task, users, project_group_list, to_tsquery($1) AS q
- WHERE project_task.created_by = users.user_id
- AND project_task.group_project_id = project_group_list.group_project_id
- AND project_group_list.group_id = $2
- AND project_group_list.group_project_id IN (\'$3\')
- AND project_task.project_task_id IN (SELECT pti.project_task_id FROM project_task_idx pti, project_task pt, project_group_list pgl, to_tsquery($1) AS q
- WHERE pti.project_task_id=pt.project_task_id
- AND pt.group_project_id=pgl.group_project_id
- AND pgl.group_id = $2
- AND pti.vectors @@ q ORDER BY rank(pti.vectors, q) DESC) LOOP
- RETURN NEXT data;
- END LOOP;
- ELSE
- FOR data IN SELECT project_task.project_task_id, headline(project_task.summary, q) AS summary, project_task.percent_complete,
- project_task.start_date, project_task.end_date, users.firstname||\' \'||users.lastname AS realname,
- project_group_list.project_name, project_group_list.group_project_id
- FROM project_task, users, project_group_list, to_tsquery($1) AS q
- WHERE project_task.created_by = users.user_id
- AND project_task.group_project_id = project_group_list.group_project_id
- AND project_group_list.group_id = $2
- AND project_group_list.group_project_id IN (\'$3\')
- AND project_group_list.is_public = 1
- AND project_task.project_task_id IN (SELECT pti.project_task_id FROM project_task_idx pti, project_task pt, project_group_list pgl, to_tsquery($1) AS q
- WHERE pti.project_task_id=pt.project_task_id
- AND pt.group_project_id=pgl.group_project_id
- AND pgl.group_id = $2
- AND pti.vectors @@ q ORDER BY rank(pti.vectors, q) DESC) LOOP
- RETURN NEXT data;
- END LOOP;
- END IF;
- ELSE
- IF $4 THEN
- FOR data IN SELECT project_task.project_task_id, headline(project_task.summary, q) AS summary, project_task.percent_complete,
- project_task.start_date, project_task.end_date, users.firstname||\' \'||users.lastname AS realname,
- project_group_list.project_name, project_group_list.group_project_id
- FROM project_task, users, project_group_list, to_tsquery($1) AS q
- WHERE project_task.created_by = users.user_id
- AND project_task.group_project_id = project_group_list.group_project_id
- AND project_group_list.group_id = $2
- AND project_task.project_task_id IN (SELECT pti.project_task_id FROM project_task_idx pti, project_task pt, project_group_list pgl, to_tsquery($1) AS q
- WHERE pti.project_task_id=pt.project_task_id
- AND pt.group_project_id=pgl.group_project_id
- AND pgl.group_id = $2
- AND pti.vectors @@ q ORDER BY rank(pti.vectors, q) DESC) LOOP
- RETURN NEXT data;
- END LOOP;
- ELSE
- FOR data IN SELECT project_task.project_task_id, headline(project_task.summary, q) AS summary, project_task.percent_complete,
- project_task.start_date, project_task.end_date, users.firstname||\' \'||users.lastname AS realname,
- project_group_list.project_name, project_group_list.group_project_id
- FROM project_task, users, project_group_list, to_tsquery($1) AS q
- WHERE project_task.created_by = users.user_id
- AND project_task.group_project_id = project_group_list.group_project_id
- AND project_group_list.group_id = $2
- AND project_group_list.is_public = 1
- AND project_task.project_task_id IN (SELECT pti.project_task_id FROM project_task_idx pti, project_task pt, project_group_list pgl, to_tsquery($1) AS q
- WHERE pti.project_task_id=pt.project_task_id
- AND pt.group_project_id=pgl.group_project_id
- AND pgl.group_id = $2
- AND pti.vectors @@ q ORDER BY rank(pti.vectors, q) DESC) LOOP
- RETURN NEXT data;
- END LOOP;
- END IF;
- END IF;
- RETURN;
- END;'
-LANGUAGE 'plpgsql';
-
-CREATE OR REPLACE FUNCTION skills_data_search(text) RETURNS SETOF skills_data_results AS '
- SELECT skills_data.skills_data_id, skills_data.type, headline(skills_data.title, q) as title,
- skills_data.start,
- skills_data.finish,
- headline(skills_data.keywords, q) as keywords
- FROM skills_data, to_tsquery($1) AS q, users, skills_data_types
- WHERE skills_data.user_id=users.user_id
- AND skills_data.type=skills_data_types.type_id
- AND skills_data.skills_data_id IN (SELECT skills_data_id FROM skills_data_idx,
- to_tsquery($1) AS q WHERE vectors @@ q ORDER BY rank(vectors, q) DESC);'
-LANGUAGE 'SQL';
-
-CREATE OR REPLACE FUNCTION trackers_search(text, integer, text, boolean) RETURNS SETOF trackers_results AS '
- DECLARE
- data trackers_results;
- BEGIN
- -- Search in specific sections
- IF $3 <> \'\' THEN
- -- show non public
- IF $4 THEN
- FOR data IN SELECT DISTINCT ON (artifact.artifact_id) artifact.artifact_id, artifact.group_artifact_id, headline(artifact.summary, q) AS summary,
- artifact.open_date, users.realname, artifact_group_list.name
- FROM artifact LEFT OUTER JOIN artifact_message USING (artifact_id), users, artifact_group_list, to_tsquery($1) AS q
- WHERE users.user_id = artifact.submitted_by
- AND artifact_group_list.group_artifact_id = artifact.group_artifact_id
- AND artifact_group_list.group_id = $2
- AND artifact_group_list.group_artifact_id IN (\'$3\')
- AND artifact.artifact_id IN (SELECT a.artifact_id FROM artifact a,
- (SELECT DISTINCT ON (ai.artifact_id) ai.artifact_id, COUNT(ami.id) AS total FROM artifact_idx ai
- LEFT OUTER JOIN artifact_message_idx ami USING (artifact_id), artifact_group_list agl,
- to_tsquery($1) AS q
- WHERE ai.group_artifact_id=agl.group_artifact_id
- AND agl.group_id = $2
- AND (ai.vectors @@ q OR ami.vectors @@ q)
- GROUP BY ai.artifact_id) AS idx, users u, to_tsquery($1) AS q, artifact_idx ai
- WHERE idx.artifact_id=a.artifact_id
- AND u.user_id=a.submitted_by
- AND a.artifact_id=ai.artifact_id
- ORDER BY idx.total DESC, rank(ai.vectors, q) DESC) LOOP
- RETURN NEXT data;
- END LOOP;
- ELSE
- FOR data IN SELECT DISTINCT ON (artifact.artifact_id) artifact.artifact_id, artifact.group_artifact_id, headline(artifact.summary, q) AS summary,
- artifact.open_date, users.realname, artifact_group_list.name
- FROM artifact LEFT OUTER JOIN artifact_message USING (artifact_id), users, artifact_group_list, to_tsquery($1) AS q
- WHERE users.user_id = artifact.submitted_by
- AND artifact_group_list.group_artifact_id = artifact.group_artifact_id
- AND artifact_group_list.group_id = $2
- AND artifact_group_list.group_artifact_id IN (\'$3\')
- AND artifact_group_list.is_public = 1
- AND artifact.artifact_id IN (SELECT a.artifact_id FROM artifact a,
- (SELECT DISTINCT ON (ai.artifact_id) ai.artifact_id, COUNT(ami.id) AS total FROM artifact_idx ai
- LEFT OUTER JOIN artifact_message_idx ami USING (artifact_id), artifact_group_list agl,
- to_tsquery($1) AS q
- WHERE ai.group_artifact_id=agl.group_artifact_id
- AND agl.group_id = $2
- AND (ai.vectors @@ q OR ami.vectors @@ q)
- GROUP BY ai.artifact_id) AS idx, users u, to_tsquery($1) AS q, artifact_idx ai
- WHERE idx.artifact_id=a.artifact_id
- AND u.user_id=a.submitted_by
- AND a.artifact_id=ai.artifact_id
- ORDER BY idx.total DESC, rank(ai.vectors, q) DESC) LOOP
- RETURN NEXT data;
- END LOOP;
- END IF;
- ELSE
- -- show non public
- IF $4 THEN
- FOR data IN SELECT DISTINCT ON (artifact.artifact_id) artifact.artifact_id, artifact.group_artifact_id, headline(artifact.summary, q) AS summary,
- artifact.open_date, users.realname, artifact_group_list.name
- FROM artifact LEFT OUTER JOIN artifact_message USING (artifact_id), users, artifact_group_list, to_tsquery($1) AS q
- WHERE users.user_id = artifact.submitted_by
- AND artifact_group_list.group_artifact_id = artifact.group_artifact_id
- AND artifact_group_list.group_id = $2
- AND artifact.artifact_id IN (SELECT a.artifact_id FROM artifact a,
- (SELECT DISTINCT ON (ai.artifact_id) ai.artifact_id, COUNT(ami.id) AS total FROM artifact_idx ai
- LEFT OUTER JOIN artifact_message_idx ami USING (artifact_id), artifact_group_list agl,
- to_tsquery($1) AS q
- WHERE ai.group_artifact_id=agl.group_artifact_id
- AND agl.group_id = $2
- AND (ai.vectors @@ q OR ami.vectors @@ q)
- GROUP BY ai.artifact_id) AS idx, users u, to_tsquery($1) AS q, artifact_idx ai
- WHERE idx.artifact_id=a.artifact_id
- AND u.user_id=a.submitted_by
- AND a.artifact_id=ai.artifact_id
- ORDER BY idx.total DESC, rank(ai.vectors, q) DESC) LOOP
- RETURN NEXT data;
- END LOOP;
- ELSE
- FOR data IN SELECT DISTINCT ON (artifact.artifact_id) artifact.artifact_id, artifact.group_artifact_id, headline(artifact.summary, q) AS summary,
- artifact.open_date, users.realname, artifact_group_list.name
- FROM artifact LEFT OUTER JOIN artifact_message USING (artifact_id), users, artifact_group_list, to_tsquery($1) AS q
- WHERE users.user_id = artifact.submitted_by
- AND artifact_group_list.group_artifact_id = artifact.group_artifact_id
- AND artifact_group_list.group_id = $2
- AND artifact_group_list.is_public = 1
- AND artifact.artifact_id IN (SELECT a.artifact_id FROM artifact a,
- (SELECT DISTINCT ON (ai.artifact_id) ai.artifact_id, COUNT(ami.id) AS total FROM artifact_idx ai
- LEFT OUTER JOIN artifact_message_idx ami USING (artifact_id), artifact_group_list agl,
- to_tsquery($1) AS q
- WHERE ai.group_artifact_id=agl.group_artifact_id
- AND agl.group_id = $2
- AND (ai.vectors @@ q OR ami.vectors @@ q)
- GROUP BY ai.artifact_id) AS idx, users u, to_tsquery($1) AS q, artifact_idx ai
- WHERE idx.artifact_id=a.artifact_id
- AND u.user_id=a.submitted_by
- AND a.artifact_id=ai.artifact_id
- ORDER BY idx.total DESC, rank(ai.vectors, q) DESC) LOOP
- RETURN NEXT data;
- END LOOP;
- END IF;
- END IF;
- RETURN;
- END;'
-LANGUAGE 'plpgsql';
-
-CREATE OR REPLACE FUNCTION users_search(text) RETURNS SETOF users_results AS '
- SELECT headline(user_name, q) as user_name,
- user_id,
- headline(realname, q) as realname
- FROM users, to_tsquery($1) AS q
- WHERE status = \'A\' AND user_id IN (SELECT user_id FROM users_idx,
- to_tsquery($1) AS q WHERE vectors @@ q ORDER BY rank(vectors, q) DESC);'
-LANGUAGE 'SQL';