1 ALTER TABLE artifact_idx DROP COLUMN group_artifact_id;
3 DELETE FROM artifact_idx;
4 ALTER TABLE artifact_idx ADD PRIMARY KEY (artifact_id);
5 INSERT INTO artifact_idx (SELECT a.artifact_id, to_tsvector(a.artifact_id::text) || to_tsvector(a.summary) || to_tsvector(a.details) || coalesce(ff_tsvector_agg(to_tsvector(am.body)), to_tsvector('')) AS vectors FROM artifact a LEFT OUTER JOIN artifact_message am USING (artifact_id) GROUP BY a.artifact_id, a.summary, a.details);
6 DROP TABLE artifact_message_idx;
8 DELETE FROM project_task_idx;
9 ALTER TABLE project_task_idx ADD PRIMARY KEY (project_task_id);
10 INSERT INTO project_task_idx (SELECT t.project_task_id, to_tsvector(t.project_task_id::text) || to_tsvector(t.summary) || to_tsvector(t.details) || coalesce(ff_tsvector_agg(to_tsvector(tm.body)), to_tsvector('')) AS vectors FROM project_task t LEFT OUTER JOIN project_messages tm USING (project_task_id) GROUP BY t.project_task_id, t.summary, t.details);
11 DROP TABLE project_messages_idx;
13 CREATE OR REPLACE FUNCTION update_vectors() RETURNS TRIGGER AS '
17 table_name := TG_ARGV[0];
18 -- **** artifact table ****
19 IF table_name = ''artifact'' THEN
20 IF TG_OP = ''DELETE'' THEN
21 DELETE FROM artifact_idx WHERE artifact_id=OLD.artifact_id;
23 DELETE FROM artifact_idx WHERE artifact_id=NEW.artifact_id;
24 INSERT INTO artifact_idx (SELECT a.artifact_id, to_tsvector(a.artifact_id::text) || to_tsvector(a.summary) || to_tsvector(a.details) || coalesce(ff_tsvector_agg(to_tsvector(am.body)), to_tsvector('''')) AS vectors FROM artifact a LEFT OUTER JOIN artifact_message am USING (artifact_id) WHERE a.artifact_id=NEW.artifact_id GROUP BY a.artifact_id, a.summary, a.details);
26 -- **** artifact_message table ****
27 ELSIF table_name = ''artifact_message'' THEN
28 IF TG_OP = ''DELETE'' THEN
29 DELETE FROM artifact_idx WHERE artifact_id=OLD.artifact_id;
31 DELETE FROM artifact_idx WHERE artifact_id=NEW.artifact_id;
32 INSERT INTO artifact_idx (SELECT a.artifact_id, to_tsvector(a.artifact_id::text) || to_tsvector(a.summary) || to_tsvector(a.details) || coalesce(ff_tsvector_agg(to_tsvector(am.body)), to_tsvector('''')) AS vectors FROM artifact a LEFT OUTER JOIN artifact_message am USING (artifact_id) WHERE a.artifact_id=NEW.artifact_id GROUP BY a.artifact_id, a.summary, a.details);
34 -- **** doc_data table ****
35 ELSIF table_name = ''doc_data'' THEN
36 IF TG_OP = ''INSERT'' THEN
37 INSERT INTO doc_data_idx (docid, group_id, vectors) VALUES (NEW.docid, NEW.group_id, to_tsvector(coalesce(NEW.title,'''') ||'' ''|| coalesce(NEW.description,'''')));
38 ELSIF TG_OP = ''UPDATE'' THEN
39 UPDATE doc_data_idx SET group_id=NEW.group_id, vectors=to_tsvector(coalesce(NEW.title,'''') ||'' ''|| coalesce(NEW.description,'''')) WHERE docid=NEW.docid;
40 ELSIF TG_OP = ''DELETE'' THEN
41 DELETE FROM doc_data_idx WHERE docid=OLD.docid;
43 -- **** forum table ****
44 ELSIF table_name = ''forum'' THEN
45 IF TG_OP = ''INSERT'' THEN
46 INSERT INTO forum_idx (msg_id, group_id, vectors) (SELECT f.msg_id, g.group_id, to_tsvector(coalesce(f.subject,'''') ||'' ''||
47 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);
48 ELSIF TG_OP = ''UPDATE'' THEN
49 UPDATE forum_idx SET vectors=to_tsvector(coalesce(NEW.subject,'''') ||'' ''|| coalesce(NEW.body,'''')) WHERE msg_id=NEW.msg_id;
50 ELSIF TG_OP = ''DELETE'' THEN
51 DELETE FROM forum_idx WHERE msg_id=OLD.msg_id;
53 -- **** frs_file table ****
54 ELSIF table_name = ''frs_file'' THEN
55 IF TG_OP = ''INSERT'' THEN
56 INSERT INTO frs_file_idx (file_id, release_id, vectors) VALUES (NEW.file_id, NEW.release_id, to_tsvector(coalesce(NEW.filename,'''')));
57 ELSIF TG_OP = ''UPDATE'' THEN
58 UPDATE frs_file_idx SET vectors=to_tsvector(coalesce(NEW.filename,'''')), release_id=NEW.release_id WHERE file_id=NEW.file_id;
59 ELSIF TG_OP = ''DELETE'' THEN
60 DELETE FROM frs_file_idx WHERE file_id=OLD.file_id;
62 -- **** frs_release table ****
63 ELSIF table_name = ''frs_release'' THEN
64 IF TG_OP = ''INSERT'' THEN
65 INSERT INTO frs_release_idx (release_id, vectors) VALUES (NEW.release_id, to_tsvector(coalesce(NEW.changes,'''') ||'' ''|| coalesce(NEW.notes,'''') ||'' ''|| coalesce(NEW.name,'''')));
66 ELSIF TG_OP = ''UPDATE'' THEN
67 UPDATE frs_release_idx SET vectors=to_tsvector(coalesce(NEW.changes,'''') ||'' ''|| coalesce(NEW.notes,'''') ||'' ''|| coalesce(NEW.name,'''')) WHERE release_id=NEW.release_id;
68 ELSIF TG_OP = ''DELETE'' THEN
69 DELETE FROM frs_release_idx WHERE release_id=OLD.release_id;
70 DELETE FROM frs_file_idx WHERE release_id=OLD.release_id;
72 -- **** groups table ****
73 ELSIF table_name = ''groups'' THEN
74 IF TG_OP = ''INSERT'' THEN
75 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,'''')));
76 ELSIF TG_OP = ''UPDATE'' THEN
77 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;
78 ELSIF TG_OP = ''DELETE'' THEN
79 DELETE FROM groups_idx WHERE group_id=OLD.group_id;
81 -- **** news_bytes table ****
82 ELSIF table_name = ''news_bytes'' THEN
83 IF TG_OP = ''INSERT'' THEN
84 INSERT INTO news_bytes_idx (id, vectors) VALUES (NEW.id, to_tsvector(coalesce(NEW.summary,'''') ||'' ''|| coalesce(NEW.details,'''')));
85 ELSIF TG_OP = ''UPDATE'' THEN
86 UPDATE news_bytes_idx SET vectors=to_tsvector(coalesce(NEW.summary,'''') ||'' ''|| coalesce(NEW.details,'''')) WHERE id=NEW.id;
87 ELSIF TG_OP = ''DELETE'' THEN
88 DELETE FROM news_bytes_idx WHERE id=OLD.id;
90 -- **** project_task table ****
91 ELSIF table_name = ''project_task'' THEN
92 IF TG_OP = ''DELETE'' THEN
93 DELETE FROM project_task_idx WHERE project_task_id=OLD.project_task_id;
95 DELETE FROM project_task_idx WHERE project_task_id=NEW.project_task_id;
96 INSERT INTO project_task_idx (SELECT t.project_task_id, to_tsvector(t.project_task_id::text) || to_tsvector(t.summary) || to_tsvector(t.details) || coalesce(ff_tsvector_agg(to_tsvector(tm.body)), to_tsvector('''')) AS vectors FROM project_task t LEFT OUTER JOIN project_messages tm USING (project_task_id) WHERE t.project_task_id=NEW.project_task_id GROUP BY t.project_task_id, t.summary, t.details);
98 -- **** project_messages table ****
99 ELSIF table_name = ''project_messages'' THEN
100 IF TG_OP = ''DELETE'' THEN
101 DELETE FROM project_task_idx WHERE project_task_id=OLD.project_task_id;
103 DELETE FROM project_task_idx WHERE project_task_id=NEW.project_task_id;
104 INSERT INTO project_task_idx (SELECT t.project_task_id, to_tsvector(t.summary) || to_tsvector(t.details) || coalesce(ff_tsvector_agg(to_tsvector(tm.body)), to_tsvector('''')) AS vectors FROM project_task t LEFT OUTER JOIN project_messages tm USING (project_task_id) WHERE t.project_task_id=NEW.project_task_id GROUP BY t.project_task_id, t.summary, t.details);
106 -- **** skills_data table ****
107 ELSIF table_name = ''skills_data'' THEN
108 IF TG_OP = ''INSERT'' THEN
109 INSERT INTO skills_data_idx (skills_data_id, vectors) VALUES (NEW.skill_data_id, to_tsvector(coalesce(NEW.title,'''') ||'' ''|| coalesce(NEW.keywords,'''')));
110 ELSIF TG_OP = ''UPDATE'' THEN
111 UPDATE skills_data_idx SET vectors=to_tsvector(coalesce(NEW.title,'''') ||'' ''|| coalesce(NEW.keywords,'''')) WHERE skills_data_id=NEW.skills_data_id;
112 ELSIF TG_OP = ''DELETE'' THEN
113 DELETE FROM skills_data_idx WHERE skills_data_id=OLD.skills_data_id;
115 -- **** users table ****
116 ELSIF table_name = ''users'' THEN
117 IF TG_OP = ''INSERT'' THEN
118 INSERT INTO users_idx (user_id, vectors) VALUES (NEW.user_id, to_tsvector(coalesce(NEW.user_name,'''') ||'' ''|| coalesce(NEW.realname,'''')));
119 ELSIF TG_OP = ''UPDATE'' THEN
120 UPDATE users_idx SET vectors=to_tsvector(coalesce(NEW.user_name,'''') ||'' ''|| coalesce(NEW.realname,'''')) WHERE user_id=NEW.user_id;
121 ELSIF TG_OP = ''DELETE'' THEN
122 DELETE FROM users_idx WHERE user_id=OLD.user_id;