This is an automated email from the git hooks/post-receive script. It was
generated because a ref change was pushed to the repository containing
the project "FusionForge".
The branch, master has been updated
via 41261c904799fa0cc42283f74861d3a5bb9093b7 (commit)
from cbf6da1d96e2100fe396a34daf27eb2e3815e366 (commit)
Those revisions listed above that are new to this repository have
not appeared on any other notification email; so we list those
revisions in full, below.
- Log -----------------------------------------------------------------
https://scm.fusionforge.org/anonscm/gitweb/?p=fusionforge/fusionforge.git;a=commitdiff;h=41261c904799fa0cc42283f74861d3a5bb9093b7
commit 41261c904799fa0cc42283f74861d3a5bb9093b7
Author: Franck Villaume <[email protected]>
Date: Tue May 31 12:50:35 2016 +0200
docman: document version support: fix search
diff --git a/src/db/20160531-docman-document-version2.sql
b/src/db/20160531-docman-document-version2.sql
new file mode 100644
index 0000000..da5c006
--- /dev/null
+++ b/src/db/20160531-docman-document-version2.sql
@@ -0,0 +1,128 @@
+update doc_data_idx set version = 1;
+update doc_data_words_idx set version = 1;
+
+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 = ''DELETE'' THEN
+ DELETE FROM artifact_idx WHERE
artifact_id=OLD.artifact_id;
+ ELSE
+ DELETE FROM artifact_idx WHERE
artifact_id=NEW.artifact_id;
+ 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);
+ END IF;
+ -- **** artifact_message table ****
+ ELSIF table_name = ''artifact_message'' THEN
+ IF TG_OP = ''DELETE'' THEN
+ DELETE FROM artifact_idx WHERE
artifact_id=OLD.artifact_id;
+ ELSE
+ DELETE FROM artifact_idx WHERE
artifact_id=NEW.artifact_id;
+ 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);
+ END IF;
+ -- **** doc_data_version table ****
+ ELSIF table_name = ''doc_data_version'' THEN
+ IF TG_OP = ''INSERT'' THEN
+ INSERT INTO doc_data_idx (docid, version, group_id,
vectors) VALUES (NEW.docid, NEW.version, (select group_id from doc_data where
docid = NEW.docid), to_tsvector(coalesce(NEW.title,'''') ||'' ''||
coalesce(NEW.description,'''')));
+ INSERT INTO doc_data_words_idx (docid, version,
group_id, vectors) VALUES (NEW.docid, NEW.version, (select group_id from
doc_data where docid = NEW.docid), to_tsvector(coalesce(NEW.title,'''') ||''
''|| coalesce(NEW.description,'''') ||'' ''|| coalesce(NEW.filename,'''') ||''
''|| coalesce(NEW.filetype,'''') ||'' ''|| coalesce(NEW.data_words,'''')));
+ ELSIF TG_OP = ''UPDATE'' THEN
+ UPDATE doc_data_idx SET group_id = (select group_id
from doc_data where docid = NEW.docid),
vectors=to_tsvector(coalesce(NEW.title,'''') ||'' ''||
coalesce(NEW.description,'''')) WHERE docid = NEW.docid and version =
NEW.version;
+ UPDATE doc_data_words_idx SET group_id = (select
group_id from doc_data where docid = NEW.docid),
vectors=to_tsvector(coalesce(NEW.title,'''') ||'' ''||
coalesce(NEW.description,'''') ||'' ''|| coalesce(NEW.filename,'''') ||'' ''||
coalesce(NEW.filetype,'''') ||'' ''|| coalesce(NEW.data_words,'''')) WHERE
docid = NEW.docid and version = NEW.version;
+ ELSIF TG_OP = ''DELETE'' THEN
+ DELETE FROM doc_data_idx WHERE version = OLD.version;
+ DELETE FROM doc_data_words_idx WHERE version =
OLD.version;
+ 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 = ''DELETE'' THEN
+ DELETE FROM groups_idx WHERE group_id=OLD.group_id;
+ ELSE
+ DELETE FROM groups_idx WHERE group_id=NEW.group_id;
+ INSERT INTO groups_idx (group_id, vectors) (SELECT
g.group_id, to_tsvector(coalesce(g.group_name,'''') ||'' ''||
coalesce(g.short_description,'''') ||'' ''||
coalesce(g.unix_group_name,'''')||'' ''||
coalesce(ff_tsvector_agg(to_tsvector(t.name)),to_tsvector(''''))) FROM groups g
LEFT OUTER JOIN project_tags t USING (group_id) WHERE g.group_id = NEW.group_id
GROUP BY g.group_id ORDER BY g.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 = ''DELETE'' THEN
+ DELETE FROM project_task_idx WHERE
project_task_id=OLD.project_task_id;
+ ELSE
+ DELETE FROM project_task_idx WHERE
project_task_id=NEW.project_task_id;
+ 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);
+ END IF;
+ -- **** project_messages table ****
+ ELSIF table_name = ''project_messages'' THEN
+ IF TG_OP = ''DELETE'' THEN
+ DELETE FROM project_task_idx WHERE
project_task_id=OLD.project_task_id;
+ ELSE
+ DELETE FROM project_task_idx WHERE
project_task_id=NEW.project_task_id;
+ 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);
+ 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.skills_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';
+
+drop trigger if exists doc_data_ts_update on doc_data;
+create trigger doc_data_version_ts_update AFTER INSERT OR DELETE OR UPDATE ON
doc_data_version FOR EACH ROW EXECUTE PROCEDURE
update_vectors('doc_data_version');
+
+
+-- Rebuild all indices
+SELECT rebuild_fti_indices();
-----------------------------------------------------------------------
Summary of changes:
...s.sql => 20160531-docman-document-version2.sql} | 39 +++++++++-------------
1 file changed, 16 insertions(+), 23 deletions(-)
copy src/db/{20160502-fti-for-project-tags.sql =>
20160531-docman-document-version2.sql} (79%)
hooks/post-receive
--
FusionForge
_______________________________________________
Fusionforge-commits mailing list
[email protected]
http://lists.fusionforge.org/cgi-bin/mailman/listinfo/fusionforge-commits
