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 1a7a4eb617196405b2697dac3763dd67f5f48c29 (commit)
from d859e7435075ae551fd65b6c5b0f81ba51950d16 (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=1a7a4eb617196405b2697dac3763dd67f5f48c29
commit 1a7a4eb617196405b2697dac3763dd67f5f48c29
Author: Franck Villaume <[email protected]>
Date: Tue Jul 5 15:35:05 2016 +0200
fix update_vectors for document version support
diff --git a/src/db/20160705-update_vectors_fix_document_version.sql
b/src/db/20160705-update_vectors_fix_document_version.sql
new file mode 100644
index 0000000..2c7ebb2
--- /dev/null
+++ b/src/db/20160705-update_vectors_fix_document_version.sql
@@ -0,0 +1,123 @@
+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
+ DELETE FROM doc_data_idx WHERE docid = NEW.docid and
version = NEW.version;
+ DELETE FROM doc_data_words_idx WHERE docid = NEW.docid
and version = NEW.version;
+ 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 = ''DELETE'' THEN
+ DELETE FROM doc_data_idx WHERE docid = OLD.docid and
version = OLD.version;
+ DELETE FROM doc_data_words_idx WHERE docid = OLD.docid
and 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';
+
+-- Rebuild all indices
+SELECT rebuild_fti_indices();
-----------------------------------------------------------------------
Summary of changes:
...=> 20160705-update_vectors_fix_document_version.sql} | 17 ++++++-----------
1 file changed, 6 insertions(+), 11 deletions(-)
copy src/db/{20160531-docman-document-version2.sql =>
20160705-update_vectors_fix_document_version.sql} (88%)
hooks/post-receive
--
FusionForge
_______________________________________________
Fusionforge-commits mailing list
[email protected]
http://lists.fusionforge.org/cgi-bin/mailman/listinfo/fusionforge-commits
