/usr/share/gforge/db/20111007-add-fti-for-task-messages.sql is in gforge-db-postgresql 5.1.1-2.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 | CREATE TABLE project_messages_idx (
id integer,
project_task_id integer,
vectors tsvector
);
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(NEW.changes,\'\') ||\' \'|| coalesce(NEW.notes,\'\') ||\' \'|| coalesce(NEW.name,\'\')));
ELSIF TG_OP = ''UPDATE'' THEN
UPDATE frs_release_idx SET vectors=to_tsvector(coalesce(NEW.changes,\'\') ||\' \'|| coalesce(NEW.notes,\'\') ||\' \'|| coalesce(NEW.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;
-- **** project_messages table ****
ELSIF table_name = ''project_messages'' THEN
IF TG_OP = ''INSERT'' THEN
INSERT INTO project_messages_idx (id, project_task_id, vectors) VALUES (NEW.project_message_id, NEW.project_task_id, to_tsvector(coalesce(NEW.body,\'\')));
ELSIF TG_OP = ''UPDATE'' THEN
UPDATE project_messages_idx SET project_task_id=NEW.project_task_id, vectors=to_tsvector(coalesce(NEW.body,\'\')) WHERE id=NEW.project_message_id;
ELSIF TG_OP = ''DELETE'' THEN
DELETE FROM project_messages_idx WHERE id=OLD.project_message_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';
CREATE TRIGGER projectmessage_ts_update AFTER UPDATE OR INSERT OR DELETE ON project_messages
FOR EACH ROW EXECUTE PROCEDURE update_vectors('project_messages');
DELETE FROM project_messages_idx;
INSERT INTO project_messages_idx (id, project_task_id, vectors)
SELECT project_message_id, project_task_id, to_tsvector(coalesce(body,'')) AS vectors
FROM project_messages ORDER BY project_message_id;
|