1 CREATE TABLE project_messages_idx (
3 project_task_id integer,
7 CREATE OR REPLACE FUNCTION update_vectors() RETURNS TRIGGER AS '
11 table_name := TG_ARGV[0];
12 -- **** artifact table ****
13 IF table_name = ''artifact'' THEN
14 IF TG_OP = ''INSERT'' THEN
15 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,'''')));
16 ELSIF TG_OP = ''UPDATE'' THEN
17 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;
18 ELSIF TG_OP = ''DELETE'' THEN
19 DELETE FROM artifact_idx WHERE artifact_id=OLD.artifact_id;
21 -- **** artifact_message table ****
22 ELSIF table_name = ''artifact_message'' THEN
23 IF TG_OP = ''INSERT'' THEN
24 INSERT INTO artifact_message_idx (id, artifact_id, vectors) VALUES (NEW.id, NEW.artifact_id, to_tsvector(coalesce(NEW.body,'''')));
25 ELSIF TG_OP = ''UPDATE'' THEN
26 UPDATE artifact_message_idx SET artifact_id=NEW.artifact_id, vectors=to_tsvector(coalesce(NEW.body,'''')) WHERE id=NEW.id;
27 ELSIF TG_OP = ''DELETE'' THEN
28 DELETE FROM artifact_message_idx WHERE id=OLD.id;
30 -- **** doc_data table ****
31 ELSIF table_name = ''doc_data'' THEN
32 IF TG_OP = ''INSERT'' THEN
33 INSERT INTO doc_data_idx (docid, group_id, vectors) VALUES (NEW.docid, NEW.group_id, to_tsvector(coalesce(NEW.title,'''') ||'' ''|| coalesce(NEW.description,'''')));
34 ELSIF TG_OP = ''UPDATE'' THEN
35 UPDATE doc_data_idx SET group_id=NEW.group_id, vectors=to_tsvector(coalesce(NEW.title,'''') ||'' ''|| coalesce(NEW.description,'''')) WHERE docid=NEW.docid;
36 ELSIF TG_OP = ''DELETE'' THEN
37 DELETE FROM doc_data_idx WHERE docid=OLD.docid;
39 -- **** forum table ****
40 ELSIF table_name = ''forum'' THEN
41 IF TG_OP = ''INSERT'' THEN
42 INSERT INTO forum_idx (msg_id, group_id, vectors) (SELECT f.msg_id, g.group_id, to_tsvector(coalesce(f.subject,'''') ||'' ''||
43 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);
44 ELSIF TG_OP = ''UPDATE'' THEN
45 UPDATE forum_idx SET vectors=to_tsvector(coalesce(NEW.subject,'''') ||'' ''|| coalesce(NEW.body,'''')) WHERE msg_id=NEW.msg_id;
46 ELSIF TG_OP = ''DELETE'' THEN
47 DELETE FROM forum_idx WHERE msg_id=OLD.msg_id;
49 -- **** frs_file table ****
50 ELSIF table_name = ''frs_file'' THEN
51 IF TG_OP = ''INSERT'' THEN
52 INSERT INTO frs_file_idx (file_id, release_id, vectors) VALUES (NEW.file_id, NEW.release_id, to_tsvector(coalesce(NEW.filename,'''')));
53 ELSIF TG_OP = ''UPDATE'' THEN
54 UPDATE frs_file_idx SET vectors=to_tsvector(coalesce(NEW.filename,'''')), release_id=NEW.release_id WHERE file_id=NEW.file_id;
55 ELSIF TG_OP = ''DELETE'' THEN
56 DELETE FROM frs_file_idx WHERE file_id=OLD.file_id;
58 -- **** frs_release table ****
59 ELSIF table_name = ''frs_release'' THEN
60 IF TG_OP = ''INSERT'' THEN
61 INSERT INTO frs_release_idx (release_id, vectors) VALUES (NEW.release_id, to_tsvector(coalesce(NEW.changes,'''') ||'' ''|| coalesce(NEW.notes,'''') ||'' ''|| coalesce(NEW.name,'''')));
62 ELSIF TG_OP = ''UPDATE'' THEN
63 UPDATE frs_release_idx SET vectors=to_tsvector(coalesce(NEW.changes,'''') ||'' ''|| coalesce(NEW.notes,'''') ||'' ''|| coalesce(NEW.name,'''')) WHERE release_id=NEW.release_id;
64 ELSIF TG_OP = ''DELETE'' THEN
65 DELETE FROM frs_release_idx WHERE release_id=OLD.release_id;
66 DELETE FROM frs_file_idx WHERE release_id=OLD.release_id;
68 -- **** groups table ****
69 ELSIF table_name = ''groups'' THEN
70 IF TG_OP = ''INSERT'' THEN
71 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,'''')));
72 ELSIF TG_OP = ''UPDATE'' THEN
73 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;
74 ELSIF TG_OP = ''DELETE'' THEN
75 DELETE FROM groups_idx WHERE group_id=OLD.group_id;
77 -- **** news_bytes table ****
78 ELSIF table_name = ''news_bytes'' THEN
79 IF TG_OP = ''INSERT'' THEN
80 INSERT INTO news_bytes_idx (id, vectors) VALUES (NEW.id, to_tsvector(coalesce(NEW.summary,'''') ||'' ''|| coalesce(NEW.details,'''')));
81 ELSIF TG_OP = ''UPDATE'' THEN
82 UPDATE news_bytes_idx SET vectors=to_tsvector(coalesce(NEW.summary,'''') ||'' ''|| coalesce(NEW.details,'''')) WHERE id=NEW.id;
83 ELSIF TG_OP = ''DELETE'' THEN
84 DELETE FROM news_bytes_idx WHERE id=OLD.id;
86 -- **** project_task table ****
87 ELSIF table_name = ''project_task'' THEN
88 IF TG_OP = ''INSERT'' THEN
89 INSERT INTO project_task_idx (project_task_id, vectors) VALUES (NEW.project_task_id, to_tsvector(coalesce(NEW.summary,'''') ||'' ''|| coalesce(NEW.details,'''')));
90 ELSIF TG_OP = ''UPDATE'' THEN
91 UPDATE project_task_idx SET vectors=to_tsvector(coalesce(NEW.summary,'''') ||'' ''|| coalesce(NEW.details,'''')) WHERE project_task_id=NEW.project_task_id;
92 ELSIF TG_OP = ''DELETE'' THEN
93 DELETE FROM project_task_idx WHERE project_task_id=OLD.project_task_id;
95 -- **** project_messages table ****
96 ELSIF table_name = ''project_messages'' THEN
97 IF TG_OP = ''INSERT'' THEN
98 INSERT INTO project_messages_idx (id, project_task_id, vectors) VALUES (NEW.project_message_id, NEW.project_task_id, to_tsvector(coalesce(NEW.body,'''')));
99 ELSIF TG_OP = ''UPDATE'' THEN
100 UPDATE project_messages_idx SET project_task_id=NEW.project_task_id, vectors=to_tsvector(coalesce(NEW.body,'''')) WHERE id=NEW.project_message_id;
101 ELSIF TG_OP = ''DELETE'' THEN
102 DELETE FROM project_messages_idx WHERE id=OLD.project_message_id;
104 -- **** skills_data table ****
105 ELSIF table_name = ''skills_data'' THEN
106 IF TG_OP = ''INSERT'' THEN
107 INSERT INTO skills_data_idx (skills_data_id, vectors) VALUES (NEW.skill_data_id, to_tsvector(coalesce(NEW.title,'''') ||'' ''|| coalesce(NEW.keywords,'''')));
108 ELSIF TG_OP = ''UPDATE'' THEN
109 UPDATE skills_data_idx SET vectors=to_tsvector(coalesce(NEW.title,'''') ||'' ''|| coalesce(NEW.keywords,'''')) WHERE skills_data_id=NEW.skills_data_id;
110 ELSIF TG_OP = ''DELETE'' THEN
111 DELETE FROM skills_data_idx WHERE skills_data_id=OLD.skills_data_id;
113 -- **** users table ****
114 ELSIF table_name = ''users'' THEN
115 IF TG_OP = ''INSERT'' THEN
116 INSERT INTO users_idx (user_id, vectors) VALUES (NEW.user_id, to_tsvector(coalesce(NEW.user_name,'''') ||'' ''|| coalesce(NEW.realname,'''')));
117 ELSIF TG_OP = ''UPDATE'' THEN
118 UPDATE users_idx SET vectors=to_tsvector(coalesce(NEW.user_name,'''') ||'' ''|| coalesce(NEW.realname,'''')) WHERE user_id=NEW.user_id;
119 ELSIF TG_OP = ''DELETE'' THEN
120 DELETE FROM users_idx WHERE user_id=OLD.user_id;
128 CREATE TRIGGER projectmessage_ts_update AFTER UPDATE OR INSERT OR DELETE ON project_messages
129 FOR EACH ROW EXECUTE PROCEDURE update_vectors('project_messages');
131 DELETE FROM project_messages_idx;
133 INSERT INTO project_messages_idx (id, project_task_id, vectors)
134 SELECT project_message_id, project_task_id, to_tsvector(coalesce(body,'')) AS vectors
135 FROM project_messages ORDER BY project_message_id;