gforge-db-postgresql 5.1.1-2 / usr / share / gforge / db / FTI.sql

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(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(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(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(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(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(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(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(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(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(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(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(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(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(coalesce(NEW.body,\'\')));
		ELSIF TG_OP = ''UPDATE'' THEN
			UPDATE artifact_message_idx SET artifact_id=NEW.artifact_id, vectors=to_tsvector(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(coalesce(NEW.title,\'\') ||\' \'|| coalesce(NEW.description,\'\')));
		ELSIF TG_OP = ''UPDATE'' THEN
			UPDATE doc_data_idx SET group_id=NEW.group_id, vectors=to_tsvector(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(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(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(coalesce(NEW.filename,\'\')));
		ELSIF TG_OP = ''UPDATE'' THEN
			UPDATE frs_file_idx SET vectors=to_tsvector(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(coalesce(changes,\'\') ||\' \'|| coalesce(notes,\'\') ||\' \'|| coalesce(name,\'\')));
		ELSIF TG_OP = ''UPDATE'' THEN
			UPDATE frs_release_idx SET vectors=to_tsvector(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(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(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(coalesce(NEW.summary,\'\') ||\' \'|| coalesce(NEW.details,\'\')));
		ELSIF TG_OP = ''UPDATE'' THEN
			UPDATE news_bytes_idx SET vectors=to_tsvector(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(coalesce(NEW.summary,\'\') ||\' \'|| coalesce(NEW.details,\'\')));
		ELSIF TG_OP = ''UPDATE'' THEN
			UPDATE project_task_idx SET vectors=to_tsvector(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(coalesce(NEW.title,\'\') ||\' \'|| coalesce(NEW.keywords,\'\')));
		ELSIF TG_OP = ''UPDATE'' THEN
			UPDATE skills_data_idx SET vectors=to_tsvector(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(coalesce(NEW.user_name,\'\') ||\' \'|| coalesce(NEW.realname,\'\')));
		ELSIF TG_OP = ''UPDATE'' THEN
			UPDATE users_idx SET vectors=to_tsvector(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, ts_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, ts_headline(doc_data.title, q) AS title, ts_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 ts_rank(vectors, q) DESC) LOOP
				RETURN NEXT data;
			END LOOP;
		ELSE
			FOR data IN SELECT doc_data.docid, ts_headline(doc_data.title, q), ts_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 ts_rank(vectors, q) DESC) LOOP
				RETURN NEXT data;
			END LOOP;
		END IF;
	ELSE
		IF $4 THEN
			FOR data IN SELECT doc_data.docid, ts_headline(doc_data.title, q), ts_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 ts_rank(vectors, q) DESC) LOOP
				RETURN NEXT data;
			END LOOP;
		ELSE
			FOR data IN SELECT doc_data.docid, ts_headline(doc_data.title, q), ts_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 ts_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, ts_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 ts_rank(vectors, q) DESC) LOOP
				RETURN NEXT data;
			END LOOP;
		ELSE
			FOR data IN SELECT forum.msg_id, ts_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 ts_rank(vectors, q) DESC) LOOP
				RETURN NEXT data;
			END LOOP;
		END IF;
	ELSE
		IF $4 THEN
			FOR data IN SELECT forum.msg_id, ts_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 ts_rank(vectors, q) DESC) LOOP
				RETURN NEXT data;
			END LOOP;
		ELSE
			FOR data IN SELECT forum.msg_id, ts_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 ts_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, ts_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 ts_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 ts_headline(frs_package.name, q) AS package_name, ts_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 ts_rank(r.vectors, q) DESC, ts_rank(f.vectors, q) DESC) LOOP
				RETURN NEXT data;
			END LOOP;
		ELSE
			FOR data IN SELECT ts_headline(frs_package.name, q) AS package_name, ts_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 ts_rank(r.vectors, q) DESC, ts_rank(f.vectors, q) DESC) LOOP
				RETURN NEXT data;
			END LOOP;
		END IF;
	ELSE
		IF $4 THEN
			FOR data IN SELECT ts_headline(frs_package.name, q) AS package_name, ts_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 ts_rank(r.vectors, q) DESC, ts_rank(f.vectors, q) DESC) LOOP
				RETURN NEXT data;
			END LOOP;
		ELSE
			FOR data IN SELECT ts_headline(frs_package.name, q) AS package_name, ts_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 ts_rank(r.vectors, q) DESC, ts_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 ts_headline(group_name, q) as group_name,
	ts_headline(unix_group_name, q) as unix_group_name,
	type_id,
	group_id,
	ts_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 ts_rank(vectors, q) DESC);'
LANGUAGE 'SQL';

CREATE OR REPLACE FUNCTION export_groups_search(text) RETURNS SETOF export_groups_results AS '
	SELECT ts_headline(group_name, q) as group_name,
	ts_headline(unix_group_name, q) as unix_group_name,
	type_id,
	group_id,
	ts_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 ts_rank(vectors, q) DESC);'
LANGUAGE 'SQL';

CREATE OR REPLACE FUNCTION news_bytes_search(text, integer) RETURNS SETOF news_bytes_results AS '
	SELECT ts_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 ts_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, ts_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 ts_rank(pti.vectors, q) DESC) LOOP
				RETURN NEXT data;
			END LOOP;
		ELSE
			FOR data IN SELECT project_task.project_task_id, ts_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 ts_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, ts_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 ts_rank(pti.vectors, q) DESC) LOOP
				RETURN NEXT data;
			END LOOP;
		ELSE
			FOR data IN SELECT project_task.project_task_id, ts_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 ts_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, ts_headline(skills_data.title, q) as title,
	skills_data.start,
	skills_data.finish,
	ts_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 ts_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, ts_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, ts_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, ts_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, ts_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, ts_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, ts_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, ts_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, ts_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 ts_headline(user_name, q) as user_name,
	user_id,
	ts_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 ts_rank(vectors, q) DESC);'
LANGUAGE 'SQL';