1 SET client_min_messages TO warning;
3 -- ********** Create auxiliar tables **********
5 CREATE TABLE artifact_idx (
7 group_artifact_id integer,
11 CREATE TABLE artifact_message_idx (
17 CREATE TABLE doc_data_idx (
23 CREATE TABLE forum_idx (
29 CREATE TABLE frs_file_idx (
35 CREATE TABLE frs_release_idx (
40 CREATE TABLE groups_idx (
45 CREATE TABLE news_bytes_idx (
50 CREATE TABLE project_task_idx (
51 project_task_id integer,
55 CREATE TABLE skills_data_idx (
56 skills_data_id integer,
60 CREATE TABLE users_idx (
65 -- ********** Populate with current data and create index **********
67 INSERT INTO artifact_idx (artifact_id, group_artifact_id, vectors)
68 SELECT artifact_id, group_artifact_id, to_tsvector(coalesce(details,'') ||'
69 '|| coalesce(summary,'')) AS vectors
70 FROM artifact ORDER BY artifact_id;
72 CREATE INDEX artifact_idxFTI ON artifact_idx USING gist(vectors);
74 INSERT INTO artifact_message_idx (id, artifact_id, vectors)
75 SELECT id, artifact_id, to_tsvector(coalesce(body,'')) AS vectors
76 FROM artifact_message ORDER BY id;
78 CREATE INDEX artifact_message_idxFTI ON artifact_message_idx USING gist(vectors);
80 INSERT INTO doc_data_idx (docid, group_id, vectors)
81 SELECT docid, group_id, to_tsvector(coalesce(title,'') ||'
82 '|| coalesce(description,'')) AS vectors
83 FROM doc_data ORDER BY docid;
85 CREATE INDEX doc_data_idxFTI ON doc_data_idx USING gist(vectors);
87 INSERT INTO forum_idx (msg_id, group_id, vectors)
88 (SELECT f.msg_id, g.group_id, to_tsvector(coalesce(f.subject,'') ||'
89 '|| coalesce(f.body,'')) AS vectors
90 FROM forum f, forum_group_list g WHERE f.group_forum_id = g.group_forum_id)
93 CREATE INDEX forum_idxFTI ON forum_idx USING gist(vectors);
95 INSERT INTO frs_file_idx (file_id, release_id, vectors)
96 SELECT file_id, release_id, to_tsvector(coalesce(filename,'')) AS vectors
97 FROM frs_file ORDER BY file_id;
99 CREATE INDEX frs_file_idxFTI ON frs_file_idx USING gist(vectors);
101 INSERT INTO frs_release_idx (release_id, vectors)
102 SELECT release_id, to_tsvector(coalesce(changes,'') ||'
103 '|| coalesce(notes,'') ||' '|| coalesce(name,'')) AS vectors
104 FROM frs_release ORDER BY release_id;
106 CREATE INDEX frs_release_idxFTI ON frs_release_idx USING gist(vectors);
108 INSERT INTO groups_idx (group_id, vectors)
109 SELECT group_id, to_tsvector(coalesce(group_name,'') ||'
110 '|| coalesce(short_description,'') ||' '|| coalesce(unix_group_name,'')) AS vectors
111 FROM groups ORDER BY group_id;
113 CREATE INDEX groups_idxFTI ON groups_idx USING gist(vectors);
115 INSERT INTO news_bytes_idx (id, vectors)
116 SELECT id, to_tsvector(coalesce(summary,'') ||'
117 '|| coalesce(details,'')) AS vectors
118 FROM news_bytes ORDER BY id;
120 CREATE INDEX news_bytes_idxFTI ON news_bytes_idx USING gist(vectors);
122 INSERT INTO project_task_idx (project_task_id, vectors)
123 SELECT project_task_id, to_tsvector(coalesce(summary,'') ||'
124 '|| coalesce(details,'')) AS vectors
125 FROM project_task ORDER BY project_task_id;
128 -- TODO project_messages
131 CREATE INDEX project_task_idxFTI ON project_task_idx USING gist(vectors);
133 INSERT INTO skills_data_idx (skills_data_id, vectors)
134 SELECT skills_data_id, to_tsvector(coalesce(title,'') ||'
135 '|| coalesce(keywords,'')) AS vectors
136 FROM skills_data ORDER BY skills_data_id;
138 CREATE INDEX skills_data_idxFTI ON skills_data_idx USING gist(vectors);
140 INSERT INTO users_idx (user_id, vectors)
141 SELECT user_id, to_tsvector(coalesce(user_name,'') ||'
142 '|| coalesce(realname,'')) AS vectors
143 FROM users ORDER BY user_id;
145 CREATE INDEX users_idxFTI ON users_idx USING gist(vectors);
147 -- VACUUM FULL ANALYZE;
149 -- ********** Create trigger function to update idx tables **********
151 CREATE OR REPLACE FUNCTION update_vectors() RETURNS TRIGGER AS '
155 table_name := TG_ARGV[0];
156 -- **** artifact table ****
157 IF table_name = ''artifact'' THEN
158 IF TG_OP = ''INSERT'' THEN
159 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,\'\')));
160 ELSIF TG_OP = ''UPDATE'' THEN
161 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;
162 ELSIF TG_OP = ''DELETE'' THEN
163 DELETE FROM artifact_idx WHERE artifact_id=OLD.artifact_id;
165 -- **** artifact_message table ****
166 ELSIF table_name = ''artifact_message'' THEN
167 IF TG_OP = ''INSERT'' THEN
168 INSERT INTO artifact_message_idx (id, artifact_id, vectors) VALUES (NEW.id, NEW.artifact_id, to_tsvector(coalesce(NEW.body,\'\')));
169 ELSIF TG_OP = ''UPDATE'' THEN
170 UPDATE artifact_message_idx SET artifact_id=NEW.artifact_id, vectors=to_tsvector(coalesce(NEW.body,\'\')) WHERE id=NEW.id;
171 ELSIF TG_OP = ''DELETE'' THEN
172 DELETE FROM artifact_message_idx WHERE id=OLD.id;
174 -- **** doc_data table ****
175 ELSIF table_name = ''doc_data'' THEN
176 IF TG_OP = ''INSERT'' THEN
177 INSERT INTO doc_data_idx (docid, group_id, vectors) VALUES (NEW.docid, NEW.group_id, to_tsvector(coalesce(NEW.title,\'\') ||\' \'|| coalesce(NEW.description,\'\')));
178 ELSIF TG_OP = ''UPDATE'' THEN
179 UPDATE doc_data_idx SET group_id=NEW.group_id, vectors=to_tsvector(coalesce(NEW.title,\'\') ||\' \'|| coalesce(NEW.description,\'\')) WHERE docid=NEW.docid;
180 ELSIF TG_OP = ''DELETE'' THEN
181 DELETE FROM doc_data_idx WHERE docid=OLD.docid;
183 -- **** forum table ****
184 ELSIF table_name = ''forum'' THEN
185 IF TG_OP = ''INSERT'' THEN
186 INSERT INTO forum_idx (msg_id, group_id, vectors) (SELECT f.msg_id, g.group_id, to_tsvector(coalesce(f.subject,\'\') ||\' \'||
187 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);
188 ELSIF TG_OP = ''UPDATE'' THEN
189 UPDATE forum_idx SET vectors=to_tsvector(coalesce(NEW.subject,\'\') ||\' \'|| coalesce(NEW.body,\'\')) WHERE msg_id=NEW.msg_id;
190 ELSIF TG_OP = ''DELETE'' THEN
191 DELETE FROM forum_idx WHERE msg_id=OLD.msg_id;
193 -- **** frs_file table ****
194 ELSIF table_name = ''frs_file'' THEN
195 IF TG_OP = ''INSERT'' THEN
196 INSERT INTO frs_file_idx (file_id, release_id, vectors) VALUES (NEW.file_id, NEW.release_id, to_tsvector(coalesce(NEW.filename,\'\')));
197 ELSIF TG_OP = ''UPDATE'' THEN
198 UPDATE frs_file_idx SET vectors=to_tsvector(coalesce(NEW.filename,\'\')), release_id=NEW.release_id WHERE file_id=NEW.file_id;
199 ELSIF TG_OP = ''DELETE'' THEN
200 DELETE FROM frs_file_idx WHERE file_id=OLD.file_id;
202 -- **** frs_release table ****
203 ELSIF table_name = ''frs_release'' THEN
204 IF TG_OP = ''INSERT'' THEN
205 INSERT INTO frs_release_idx (release_id, vectors) VALUES (NEW.release_id, to_tsvector(coalesce(changes,\'\') ||\' \'|| coalesce(notes,\'\') ||\' \'|| coalesce(name,\'\')));
206 ELSIF TG_OP = ''UPDATE'' THEN
207 UPDATE frs_release_idx SET vectors=to_tsvector(coalesce(changes,\'\') ||\' \'|| coalesce(notes,\'\') ||\' \'|| coalesce(name,\'\')) WHERE release_id=NEW.release_id;
208 ELSIF TG_OP = ''DELETE'' THEN
209 DELETE FROM frs_release_idx WHERE release_id=OLD.release_id;
210 DELETE FROM frs_file_idx WHERE release_id=OLD.release_id;
212 -- **** groups table ****
213 ELSIF table_name = ''groups'' THEN
214 IF TG_OP = ''INSERT'' THEN
215 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,\'\')));
216 ELSIF TG_OP = ''UPDATE'' THEN
217 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;
218 ELSIF TG_OP = ''DELETE'' THEN
219 DELETE FROM groups_idx WHERE group_id=OLD.group_id;
221 -- **** news_bytes table ****
222 ELSIF table_name = ''news_bytes'' THEN
223 IF TG_OP = ''INSERT'' THEN
224 INSERT INTO news_bytes_idx (id, vectors) VALUES (NEW.id, to_tsvector(coalesce(NEW.summary,\'\') ||\' \'|| coalesce(NEW.details,\'\')));
225 ELSIF TG_OP = ''UPDATE'' THEN
226 UPDATE news_bytes_idx SET vectors=to_tsvector(coalesce(NEW.summary,\'\') ||\' \'|| coalesce(NEW.details,\'\')) WHERE id=NEW.id;
227 ELSIF TG_OP = ''DELETE'' THEN
228 DELETE FROM news_bytes_idx WHERE id=OLD.id;
230 -- **** project_task table ****
231 ELSIF table_name = ''project_task'' THEN
232 IF TG_OP = ''INSERT'' THEN
233 INSERT INTO project_task_idx (project_task_id, vectors) VALUES (NEW.project_task_id, to_tsvector(coalesce(NEW.summary,\'\') ||\' \'|| coalesce(NEW.details,\'\')));
234 ELSIF TG_OP = ''UPDATE'' THEN
235 UPDATE project_task_idx SET vectors=to_tsvector(coalesce(NEW.summary,\'\') ||\' \'|| coalesce(NEW.details,\'\')) WHERE project_task_id=NEW.project_task_id;
236 ELSIF TG_OP = ''DELETE'' THEN
237 DELETE FROM project_task_idx WHERE project_task_id=OLD.project_task_id;
239 -- **** skills_data table ****
240 ELSIF table_name = ''skills_data'' THEN
241 IF TG_OP = ''INSERT'' THEN
242 INSERT INTO skills_data_idx (skills_data_id, vectors) VALUES (NEW.skill_data_id, to_tsvector(coalesce(NEW.title,\'\') ||\' \'|| coalesce(NEW.keywords,\'\')));
243 ELSIF TG_OP = ''UPDATE'' THEN
244 UPDATE skills_data_idx SET vectors=to_tsvector(coalesce(NEW.title,\'\') ||\' \'|| coalesce(NEW.keywords,\'\')) WHERE skills_data_id=NEW.skills_data_id;
245 ELSIF TG_OP = ''DELETE'' THEN
246 DELETE FROM skills_data_idx WHERE skills_data_id=OLD.skills_data_id;
248 -- **** users table ****
249 ELSIF table_name = ''users'' THEN
250 IF TG_OP = ''INSERT'' THEN
251 INSERT INTO users_idx (user_id, vectors) VALUES (NEW.user_id, to_tsvector(coalesce(NEW.user_name,\'\') ||\' \'|| coalesce(NEW.realname,\'\')));
252 ELSIF TG_OP = ''UPDATE'' THEN
253 UPDATE users_idx SET vectors=to_tsvector(coalesce(NEW.user_name,\'\') ||\' \'|| coalesce(NEW.realname,\'\')) WHERE user_id=NEW.user_id;
254 ELSIF TG_OP = ''DELETE'' THEN
255 DELETE FROM users_idx WHERE user_id=OLD.user_id;
263 -- ********** Set up triggers **********
265 CREATE TRIGGER artifact_ts_update AFTER UPDATE OR INSERT OR DELETE ON artifact
266 FOR EACH ROW EXECUTE PROCEDURE update_vectors('artifact');
268 CREATE TRIGGER doc_data_ts_update AFTER UPDATE OR INSERT OR DELETE ON doc_data
269 FOR EACH ROW EXECUTE PROCEDURE update_vectors('doc_data');
271 CREATE TRIGGER forum_update AFTER UPDATE OR INSERT OR DELETE ON forum
272 FOR EACH ROW EXECUTE PROCEDURE update_vectors('forum');
274 CREATE TRIGGER frs_file_ts_update AFTER UPDATE OR INSERT OR DELETE ON frs_file
275 FOR EACH ROW EXECUTE PROCEDURE update_vectors('frs_file');
277 CREATE TRIGGER frs_release_ts_update AFTER UPDATE OR INSERT OR DELETE ON frs_release
278 FOR EACH ROW EXECUTE PROCEDURE update_vectors('frs_release');
280 CREATE TRIGGER groups_ts_update AFTER UPDATE OR INSERT OR DELETE ON groups
281 FOR EACH ROW EXECUTE PROCEDURE update_vectors('groups');
283 CREATE TRIGGER news_bytes_ts_update AFTER UPDATE OR INSERT OR DELETE ON news_bytes
284 FOR EACH ROW EXECUTE PROCEDURE update_vectors('news_bytes');
286 CREATE TRIGGER project_task_ts_update AFTER UPDATE OR INSERT OR DELETE ON project_task
287 FOR EACH ROW EXECUTE PROCEDURE update_vectors('project_task');
289 CREATE TRIGGER skills_data_ts_update AFTER UPDATE OR INSERT OR DELETE ON skills_data
290 FOR EACH ROW EXECUTE PROCEDURE update_vectors('skills_data');
292 CREATE TRIGGER users_ts_update AFTER UPDATE OR INSERT OR DELETE ON users
293 FOR EACH ROW EXECUTE PROCEDURE update_vectors('users');
295 -- ********** Create types for results **********
297 CREATE TYPE artifact_results AS (group_artifact_id integer,
301 realname character varying(32)
304 CREATE TYPE doc_data_results AS (docid integer,
307 groupname character varying(255)
310 CREATE TYPE forum_results AS (msg_id integer,
313 realname character varying(32)
316 CREATE TYPE frs_results AS (package_name text,
318 release_date integer,
320 realname character varying(32)
323 CREATE TYPE groups_results AS (group_name text,
324 unix_group_name text,
327 short_description text
330 CREATE TYPE export_groups_results AS (group_name text,
331 unix_group_name text,
334 short_description text,
336 register_time integer
339 CREATE TYPE news_bytes_results AS (summary text,
345 CREATE TYPE project_task_results AS (project_task_id integer,
347 percent_complete integer,
352 group_project_id integer
355 CREATE TYPE skills_data_results AS (skills_data_id integer,
363 CREATE TYPE trackers_results AS (artifact_id integer,
364 group_artifact_id integer,
367 realname character varying(32),
371 CREATE TYPE users_results AS (user_name text,
376 -- ********** Create search store procedures **********
378 CREATE OR REPLACE FUNCTION artifact_search(text, int) RETURNS SETOF artifact_results AS '
379 SELECT a.group_artifact_id, a.artifact_id, a.summary, a.open_date, u.realname FROM artifact a,
380 (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),
382 WHERE ai.group_artifact_id=$2
383 AND (ai.vectors @@ q OR ami.vectors @@ q)
384 GROUP BY ai.artifact_id) AS idx, users u, to_tsquery($1) AS q, artifact_idx ai
385 WHERE idx.artifact_id=a.artifact_id
386 AND u.user_id=a.submitted_by
387 AND a.artifact_id=ai.artifact_id
388 ORDER BY idx.total DESC, ts_rank(ai.vectors, q) DESC'
391 CREATE OR REPLACE FUNCTION doc_data_search(text, integer, text, boolean) RETURNS SETOF doc_data_results AS '
397 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
398 FROM doc_data, doc_groups, to_tsquery($1) AS q
399 WHERE doc_data.doc_group = doc_groups.doc_group
400 AND doc_data.group_id = $2
401 AND doc_groups.doc_group IN (\'$3\')
402 AND doc_data.stateid IN (1, 4, 5)
403 AND doc_data.docid IN (SELECT docid FROM doc_data_idx, to_tsquery($1) AS q
404 WHERE group_id = $2 AND vectors @@ q ORDER BY ts_rank(vectors, q) DESC) LOOP
408 FOR data IN SELECT doc_data.docid, ts_headline(doc_data.title, q), ts_headline(doc_data.description, q), doc_groups.groupname
409 FROM doc_data, doc_groups, to_tsquery($1) AS q
410 WHERE doc_data.doc_group = doc_groups.doc_group
411 AND doc_data.group_id = $2
412 AND doc_groups.doc_group IN (\'$3\')
413 AND doc_data.stateid = 1
414 AND doc_data.docid IN (SELECT docid FROM doc_data_idx, to_tsquery($1) AS q
415 WHERE group_id = $2 AND vectors @@ q ORDER BY ts_rank(vectors, q) DESC) LOOP
421 FOR data IN SELECT doc_data.docid, ts_headline(doc_data.title, q), ts_headline(doc_data.description, q), doc_groups.groupname
422 FROM doc_data, doc_groups, to_tsquery($1) AS q
423 WHERE doc_data.doc_group = doc_groups.doc_group
424 AND doc_data.group_id = $2
425 AND doc_data.stateid IN (1, 4, 5)
426 AND doc_data.docid IN (SELECT docid FROM doc_data_idx, to_tsquery($1) AS q
427 WHERE group_id = $2 AND vectors @@ q ORDER BY ts_rank(vectors, q) DESC) LOOP
431 FOR data IN SELECT doc_data.docid, ts_headline(doc_data.title, q), ts_headline(doc_data.description, q), doc_groups.groupname
432 FROM doc_data, doc_groups, to_tsquery($1) AS q
433 WHERE doc_data.doc_group = doc_groups.doc_group
434 AND doc_data.group_id = $2
435 AND doc_data.stateid = 1
436 AND doc_data.docid IN (SELECT docid FROM doc_data_idx, to_tsquery($1) AS q
437 WHERE group_id = $2 AND vectors @@ q ORDER BY ts_rank(vectors, q) DESC) LOOP
446 CREATE TYPE forums_results AS (msg_id integer,
449 realname character varying(32),
453 CREATE OR REPLACE FUNCTION forums_search(text, integer, text, boolean) RETURNS SETOF forums_results AS '
459 FOR data IN SELECT forum.msg_id, ts_headline(forum.subject, q) AS subject, forum.post_date, users.realname, forum_group_list.forum_name
460 FROM forum, users, forum_group_list, to_tsquery($1) AS q
461 WHERE users.user_id = forum.posted_by
462 AND forum_group_list.group_forum_id = forum.group_forum_id
463 AND forum_group_list.is_public <> 9
464 AND forum_group_list.group_forum_id IN (\'$3\')
465 AND forum.msg_id IN (SELECT msg_id FROM forum_idx, to_tsquery($1) AS q
466 WHERE group_id = $2 AND vectors @@ q ORDER BY ts_rank(vectors, q) DESC) LOOP
470 FOR data IN SELECT forum.msg_id, ts_headline(forum.subject, q) AS subject, forum.post_date, users.realname, forum_group_list.forum_name
471 FROM forum, users, forum_group_list, to_tsquery($1) AS q
472 WHERE users.user_id = forum.posted_by
473 AND forum_group_list.group_forum_id = forum.group_forum_id
474 AND forum_group_list.is_public <> 9
475 AND forum_group_list.group_forum_id IN (\'$3\')
476 AND forum_group_list.is_public = 1
477 AND forum.msg_id IN (SELECT msg_id FROM forum_idx, to_tsquery($1) AS q
478 WHERE group_id = $2 AND vectors @@ q ORDER BY ts_rank(vectors, q) DESC) LOOP
484 FOR data IN SELECT forum.msg_id, ts_headline(forum.subject, q) AS subject, forum.post_date, users.realname, forum_group_list.forum_name
485 FROM forum, users, forum_group_list, to_tsquery($1) AS q
486 WHERE users.user_id = forum.posted_by
487 AND forum_group_list.group_forum_id = forum.group_forum_id
488 AND forum_group_list.is_public <> 9
489 AND forum.msg_id IN (SELECT msg_id FROM forum_idx, to_tsquery($1) AS q
490 WHERE group_id = $2 AND vectors @@ q ORDER BY ts_rank(vectors, q) DESC) LOOP
494 FOR data IN SELECT forum.msg_id, ts_headline(forum.subject, q) AS subject, forum.post_date, users.realname, forum_group_list.forum_name
495 FROM forum, users, forum_group_list, to_tsquery($1) AS q
496 WHERE users.user_id = forum.posted_by
497 AND forum_group_list.group_forum_id = forum.group_forum_id
498 AND forum_group_list.is_public <> 9
499 AND forum_group_list.is_public = 1
500 AND forum.msg_id IN (SELECT msg_id FROM forum_idx, to_tsquery($1) AS q
501 WHERE group_id = $2 AND vectors @@ q ORDER BY ts_rank(vectors, q) DESC) LOOP
510 CREATE OR REPLACE FUNCTION forum_search(text, integer) RETURNS SETOF forum_results AS '
511 SELECT forum.msg_id, ts_headline(forum.subject, q) AS subject, forum.post_date, users.realname
512 FROM forum, users, to_tsquery($1) AS q
513 WHERE users.user_id=forum.posted_by
514 AND msg_id IN (SELECT fi.msg_id FROM forum_idx fi, forum f, to_tsquery($1) AS q
515 WHERE fi.msg_id = f.msg_id AND f.group_forum_id=$2
516 AND vectors @@ q ORDER BY ts_rank(vectors, q) DESC);'
519 CREATE OR REPLACE FUNCTION frs_search(text, integer, text, boolean) RETURNS SETOF frs_results AS '
523 -- Search in specific sections
527 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
528 FROM frs_file, frs_release, users, frs_package, to_tsquery($1) AS q
529 WHERE frs_release.released_by = users.user_id
530 AND frs_package.package_id = frs_release.package_id
531 AND frs_file.release_id=frs_release.release_id
532 AND frs_package.group_id=$2
533 AND frs_package.package_id IN (\'$3\')
534 AND frs_release.release_id IN (SELECT r.release_id FROM frs_release_idx r
535 LEFT JOIN frs_file_idx f ON r.release_id=f.release_id, to_tsquery($1) AS q
536 WHERE r.vectors @@ q OR f.vectors @@ q ORDER BY ts_rank(r.vectors, q) DESC, ts_rank(f.vectors, q) DESC) LOOP
540 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
541 FROM frs_file, frs_release, users, frs_package, to_tsquery($1) AS q
542 WHERE frs_release.released_by = users.user_id
543 AND frs_package.package_id = frs_release.package_id
544 AND frs_file.release_id=frs_release.release_id
545 AND frs_package.group_id=$2
546 AND frs_package.package_id IN (\'$3\')
547 AND frs_package.is_public=1
548 AND frs_release.release_id IN (SELECT r.release_id FROM frs_release_idx r
549 LEFT JOIN frs_file_idx f ON r.release_id=f.release_id, to_tsquery($1) AS q
550 WHERE r.vectors @@ q OR f.vectors @@ q ORDER BY ts_rank(r.vectors, q) DESC, ts_rank(f.vectors, q) DESC) LOOP
556 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
557 FROM frs_file, frs_release, users, frs_package, to_tsquery($1) AS q
558 WHERE frs_release.released_by = users.user_id
559 AND frs_package.package_id = frs_release.package_id
560 AND frs_file.release_id=frs_release.release_id
561 AND frs_package.group_id=$2
562 AND frs_release.release_id IN (SELECT r.release_id FROM frs_release_idx r
563 LEFT JOIN frs_file_idx f ON r.release_id=f.release_id, to_tsquery($1) AS q
564 WHERE r.vectors @@ q OR f.vectors @@ q ORDER BY ts_rank(r.vectors, q) DESC, ts_rank(f.vectors, q) DESC) LOOP
568 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
569 FROM frs_file, frs_release, users, frs_package, to_tsquery($1) AS q
570 WHERE frs_release.released_by = users.user_id
571 AND frs_package.package_id = frs_release.package_id
572 AND frs_file.release_id=frs_release.release_id
573 AND frs_package.group_id=$2
574 AND frs_package.is_public=1
575 AND frs_release.release_id IN (SELECT r.release_id FROM frs_release_idx r
576 LEFT JOIN frs_file_idx f ON r.release_id=f.release_id, to_tsquery($1) AS q
577 WHERE r.vectors @@ q OR f.vectors @@ q ORDER BY ts_rank(r.vectors, q) DESC, ts_rank(f.vectors, q) DESC) LOOP
586 CREATE OR REPLACE FUNCTION groups_search(text) RETURNS SETOF groups_results AS '
587 SELECT ts_headline(group_name, q) as group_name,
588 ts_headline(unix_group_name, q) as unix_group_name,
591 ts_headline(short_description, q) as short_description
592 FROM groups, to_tsquery($1) AS q
593 WHERE status IN (\'A\', \'H\') AND is_public=\'1\' AND
594 group_id IN (SELECT group_id FROM groups_idx, to_tsquery($1) AS q
595 WHERE vectors @@ q ORDER BY ts_rank(vectors, q) DESC);'
598 CREATE OR REPLACE FUNCTION export_groups_search(text) RETURNS SETOF export_groups_results AS '
599 SELECT ts_headline(group_name, q) as group_name,
600 ts_headline(unix_group_name, q) as unix_group_name,
603 ts_headline(short_description, q) as short_description,
606 FROM groups, to_tsquery($1) AS q
607 WHERE status IN (\'A\', \'H\') AND is_public=\'1\' AND short_description <> \'\' AND
608 group_id IN (SELECT group_id FROM groups_idx, to_tsquery($1) AS q
609 WHERE vectors @@ q ORDER BY ts_rank(vectors, q) DESC);'
612 CREATE OR REPLACE FUNCTION news_bytes_search(text, integer) RETURNS SETOF news_bytes_results AS '
613 SELECT ts_headline(news_bytes.summary, q) as summary,
614 news_bytes.post_date,
617 FROM news_bytes, users, to_tsquery($1) AS q
618 WHERE (news_bytes.group_id=$2 AND news_bytes.is_approved <> ''4'' AND news_bytes.submitted_by=users.user_id) AND
619 news_bytes.id IN (SELECT id FROM news_bytes_idx,
620 to_tsquery($1) AS q WHERE vectors @@ q ORDER BY ts_rank(vectors, q) DESC);'
623 CREATE OR REPLACE FUNCTION project_task_search(text, integer, text, boolean) RETURNS SETOF project_task_results AS '
625 data project_task_results;
627 -- Search in specific sections
631 FOR data IN SELECT project_task.project_task_id, ts_headline(project_task.summary, q) AS summary, project_task.percent_complete,
632 project_task.start_date, project_task.end_date, users.firstname||\' \'||users.lastname AS realname,
633 project_group_list.project_name, project_group_list.group_project_id
634 FROM project_task, users, project_group_list, to_tsquery($1) AS q
635 WHERE project_task.created_by = users.user_id
636 AND project_task.group_project_id = project_group_list.group_project_id
637 AND project_group_list.group_id = $2
638 AND project_group_list.group_project_id IN (\'$3\')
639 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
640 WHERE pti.project_task_id=pt.project_task_id
641 AND pt.group_project_id=pgl.group_project_id
642 AND pgl.group_id = $2
643 AND pti.vectors @@ q ORDER BY ts_rank(pti.vectors, q) DESC) LOOP
647 FOR data IN SELECT project_task.project_task_id, ts_headline(project_task.summary, q) AS summary, project_task.percent_complete,
648 project_task.start_date, project_task.end_date, users.firstname||\' \'||users.lastname AS realname,
649 project_group_list.project_name, project_group_list.group_project_id
650 FROM project_task, users, project_group_list, to_tsquery($1) AS q
651 WHERE project_task.created_by = users.user_id
652 AND project_task.group_project_id = project_group_list.group_project_id
653 AND project_group_list.group_id = $2
654 AND project_group_list.group_project_id IN (\'$3\')
655 AND project_group_list.is_public = 1
656 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
657 WHERE pti.project_task_id=pt.project_task_id
658 AND pt.group_project_id=pgl.group_project_id
659 AND pgl.group_id = $2
660 AND pti.vectors @@ q ORDER BY ts_rank(pti.vectors, q) DESC) LOOP
666 FOR data IN SELECT project_task.project_task_id, ts_headline(project_task.summary, q) AS summary, project_task.percent_complete,
667 project_task.start_date, project_task.end_date, users.firstname||\' \'||users.lastname AS realname,
668 project_group_list.project_name, project_group_list.group_project_id
669 FROM project_task, users, project_group_list, to_tsquery($1) AS q
670 WHERE project_task.created_by = users.user_id
671 AND project_task.group_project_id = project_group_list.group_project_id
672 AND project_group_list.group_id = $2
673 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
674 WHERE pti.project_task_id=pt.project_task_id
675 AND pt.group_project_id=pgl.group_project_id
676 AND pgl.group_id = $2
677 AND pti.vectors @@ q ORDER BY ts_rank(pti.vectors, q) DESC) LOOP
681 FOR data IN SELECT project_task.project_task_id, ts_headline(project_task.summary, q) AS summary, project_task.percent_complete,
682 project_task.start_date, project_task.end_date, users.firstname||\' \'||users.lastname AS realname,
683 project_group_list.project_name, project_group_list.group_project_id
684 FROM project_task, users, project_group_list, to_tsquery($1) AS q
685 WHERE project_task.created_by = users.user_id
686 AND project_task.group_project_id = project_group_list.group_project_id
687 AND project_group_list.group_id = $2
688 AND project_group_list.is_public = 1
689 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
690 WHERE pti.project_task_id=pt.project_task_id
691 AND pt.group_project_id=pgl.group_project_id
692 AND pgl.group_id = $2
693 AND pti.vectors @@ q ORDER BY ts_rank(pti.vectors, q) DESC) LOOP
702 CREATE OR REPLACE FUNCTION skills_data_search(text) RETURNS SETOF skills_data_results AS '
703 SELECT skills_data.skills_data_id, skills_data.type, ts_headline(skills_data.title, q) as title,
706 ts_headline(skills_data.keywords, q) as keywords
707 FROM skills_data, to_tsquery($1) AS q, users, skills_data_types
708 WHERE skills_data.user_id=users.user_id
709 AND skills_data.type=skills_data_types.type_id
710 AND skills_data.skills_data_id IN (SELECT skills_data_id FROM skills_data_idx,
711 to_tsquery($1) AS q WHERE vectors @@ q ORDER BY ts_rank(vectors, q) DESC);'
714 CREATE OR REPLACE FUNCTION trackers_search(text, integer, text, boolean) RETURNS SETOF trackers_results AS '
716 data trackers_results;
718 -- Search in specific sections
722 FOR data IN SELECT DISTINCT ON (artifact.artifact_id) artifact.artifact_id, artifact.group_artifact_id, ts_headline(artifact.summary, q) AS summary,
723 artifact.open_date, users.realname, artifact_group_list.name
724 FROM artifact LEFT OUTER JOIN artifact_message USING (artifact_id), users, artifact_group_list, to_tsquery($1) AS q
725 WHERE users.user_id = artifact.submitted_by
726 AND artifact_group_list.group_artifact_id = artifact.group_artifact_id
727 AND artifact_group_list.group_id = $2
728 AND artifact_group_list.group_artifact_id IN (\'$3\')
729 AND artifact.artifact_id IN (SELECT a.artifact_id FROM artifact a,
730 (SELECT DISTINCT ON (ai.artifact_id) ai.artifact_id, COUNT(ami.id) AS total FROM artifact_idx ai
731 LEFT OUTER JOIN artifact_message_idx ami USING (artifact_id), artifact_group_list agl,
733 WHERE ai.group_artifact_id=agl.group_artifact_id
734 AND agl.group_id = $2
735 AND (ai.vectors @@ q OR ami.vectors @@ q)
736 GROUP BY ai.artifact_id) AS idx, users u, to_tsquery($1) AS q, artifact_idx ai
737 WHERE idx.artifact_id=a.artifact_id
738 AND u.user_id=a.submitted_by
739 AND a.artifact_id=ai.artifact_id
740 ORDER BY idx.total DESC, ts_rank(ai.vectors, q) DESC) LOOP
744 FOR data IN SELECT DISTINCT ON (artifact.artifact_id) artifact.artifact_id, artifact.group_artifact_id, ts_headline(artifact.summary, q) AS summary,
745 artifact.open_date, users.realname, artifact_group_list.name
746 FROM artifact LEFT OUTER JOIN artifact_message USING (artifact_id), users, artifact_group_list, to_tsquery($1) AS q
747 WHERE users.user_id = artifact.submitted_by
748 AND artifact_group_list.group_artifact_id = artifact.group_artifact_id
749 AND artifact_group_list.group_id = $2
750 AND artifact_group_list.group_artifact_id IN (\'$3\')
751 AND artifact_group_list.is_public = 1
752 AND artifact.artifact_id IN (SELECT a.artifact_id FROM artifact a,
753 (SELECT DISTINCT ON (ai.artifact_id) ai.artifact_id, COUNT(ami.id) AS total FROM artifact_idx ai
754 LEFT OUTER JOIN artifact_message_idx ami USING (artifact_id), artifact_group_list agl,
756 WHERE ai.group_artifact_id=agl.group_artifact_id
757 AND agl.group_id = $2
758 AND (ai.vectors @@ q OR ami.vectors @@ q)
759 GROUP BY ai.artifact_id) AS idx, users u, to_tsquery($1) AS q, artifact_idx ai
760 WHERE idx.artifact_id=a.artifact_id
761 AND u.user_id=a.submitted_by
762 AND a.artifact_id=ai.artifact_id
763 ORDER BY idx.total DESC, ts_rank(ai.vectors, q) DESC) LOOP
770 FOR data IN SELECT DISTINCT ON (artifact.artifact_id) artifact.artifact_id, artifact.group_artifact_id, ts_headline(artifact.summary, q) AS summary,
771 artifact.open_date, users.realname, artifact_group_list.name
772 FROM artifact LEFT OUTER JOIN artifact_message USING (artifact_id), users, artifact_group_list, to_tsquery($1) AS q
773 WHERE users.user_id = artifact.submitted_by
774 AND artifact_group_list.group_artifact_id = artifact.group_artifact_id
775 AND artifact_group_list.group_id = $2
776 AND artifact.artifact_id IN (SELECT a.artifact_id FROM artifact a,
777 (SELECT DISTINCT ON (ai.artifact_id) ai.artifact_id, COUNT(ami.id) AS total FROM artifact_idx ai
778 LEFT OUTER JOIN artifact_message_idx ami USING (artifact_id), artifact_group_list agl,
780 WHERE ai.group_artifact_id=agl.group_artifact_id
781 AND agl.group_id = $2
782 AND (ai.vectors @@ q OR ami.vectors @@ q)
783 GROUP BY ai.artifact_id) AS idx, users u, to_tsquery($1) AS q, artifact_idx ai
784 WHERE idx.artifact_id=a.artifact_id
785 AND u.user_id=a.submitted_by
786 AND a.artifact_id=ai.artifact_id
787 ORDER BY idx.total DESC, ts_rank(ai.vectors, q) DESC) LOOP
791 FOR data IN SELECT DISTINCT ON (artifact.artifact_id) artifact.artifact_id, artifact.group_artifact_id, ts_headline(artifact.summary, q) AS summary,
792 artifact.open_date, users.realname, artifact_group_list.name
793 FROM artifact LEFT OUTER JOIN artifact_message USING (artifact_id), users, artifact_group_list, to_tsquery($1) AS q
794 WHERE users.user_id = artifact.submitted_by
795 AND artifact_group_list.group_artifact_id = artifact.group_artifact_id
796 AND artifact_group_list.group_id = $2
797 AND artifact_group_list.is_public = 1
798 AND artifact.artifact_id IN (SELECT a.artifact_id FROM artifact a,
799 (SELECT DISTINCT ON (ai.artifact_id) ai.artifact_id, COUNT(ami.id) AS total FROM artifact_idx ai
800 LEFT OUTER JOIN artifact_message_idx ami USING (artifact_id), artifact_group_list agl,
802 WHERE ai.group_artifact_id=agl.group_artifact_id
803 AND agl.group_id = $2
804 AND (ai.vectors @@ q OR ami.vectors @@ q)
805 GROUP BY ai.artifact_id) AS idx, users u, to_tsquery($1) AS q, artifact_idx ai
806 WHERE idx.artifact_id=a.artifact_id
807 AND u.user_id=a.submitted_by
808 AND a.artifact_id=ai.artifact_id
809 ORDER BY idx.total DESC, ts_rank(ai.vectors, q) DESC) LOOP
818 CREATE OR REPLACE FUNCTION users_search(text) RETURNS SETOF users_results AS '
819 SELECT ts_headline(user_name, q) as user_name,
821 ts_headline(realname, q) as realname
822 FROM users, to_tsquery($1) AS q
823 WHERE status = \'A\' AND user_id IN (SELECT user_id FROM users_idx,
824 to_tsquery($1) AS q WHERE vectors @@ q ORDER BY ts_rank(vectors, q) DESC);'