/usr/share/gforge/db/FTI-20050401.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 136 137 138 139 140 141 142 143 144 145 146 147 | SET client_min_messages TO warning;
DROP FUNCTION update_vectors() CASCADE;
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;
-- **** 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');
|