function getQuery() {
$qpa = db_construct_qpa () ;
- $words = $this->getFTIwords();
-
- $qpa = db_construct_qpa ($qpa,
- 'SELECT x.* FROM (SELECT artifact.artifact_id, artifact.group_artifact_id, artifact.summary, artifact.open_date, users.realname, artifact.summary||$1||artifact.details||$1||coalesce(ff_string_agg(artifact_message.body), $1) as full_string_agg',
- array ($this->field_separator));
- if (forge_get_config('use_fti')) {
- $qpa = db_construct_qpa ($qpa,
- ', (artifact_idx.vectors || coalesce(ff_tsvector_agg(artifact_message_idx.vectors), to_tsvector($1))) AS full_vector_agg',
- array(''));
- }
- $qpa = db_construct_qpa ($qpa,
- ' FROM artifact LEFT OUTER JOIN artifact_message USING (artifact_id) ',
- array ()) ;
- if (forge_get_config('use_fti')) {
- $qpa = db_construct_qpa ($qpa, ' LEFT JOIN artifact_message_idx USING (artifact_id) ', array()) ;
- }
- $qpa = db_construct_qpa ($qpa, ' , users ', array()) ;
if (forge_get_config('use_fti')) {
- $qpa = db_construct_qpa ($qpa,
- ', artifact_idx',
- array ()) ;
- }
- $qpa = db_construct_qpa ($qpa,
- ' WHERE users.user_id = artifact.submitted_by AND artifact.group_artifact_id = $1 ',
- array ($this->artifactId)) ;
- if (forge_get_config('use_fti')) {
- $qpa = db_construct_qpa ($qpa,
- 'AND artifact.artifact_id = artifact_idx.artifact_id ',
- array ()) ;
- }
- $qpa = db_construct_qpa ($qpa,
- 'GROUP BY artifact.artifact_id, artifact.group_artifact_id, artifact.summary, artifact.open_date, users.realname, artifact.details') ;
+ $words = $this->getFTIwords();
- if (forge_get_config('use_fti')) {
- $qpa = db_construct_qpa ($qpa,
- ', artifact_idx.vectors',
- array ()) ;
- }
- $qpa = db_construct_qpa ($qpa,
- ') AS x WHERE ') ;
-
- if (forge_get_config('use_fti')) {
- $qpa = db_construct_qpa ($qpa,
- 'full_vector_agg @@ to_tsquery($1) ',
- array($words));
if (count($this->phrases)) {
$qpa = db_construct_qpa ($qpa,
- 'AND (') ;
- $qpa = $this->addMatchCondition ($qpa, 'x.full_string_agg') ;
+ 'SELECT x.* FROM (SELECT artifact.artifact_id, artifact.group_artifact_id, artifact.summary, artifact.open_date, users.realname, artifact.summary||$1||artifact.details||$1||coalesce(ff_string_agg(artifact_message.body), $1) as full_string_agg, artifact_idx.vectors FROM artifact LEFT OUTER JOIN artifact_message USING (artifact_id), users, artifact_idx WHERE users.user_id = artifact.submitted_by AND artifact.group_artifact_id = $2 AND artifact.artifact_id = artifact_idx.artifact_id AND vectors @@ to_tsquery($3) GROUP BY artifact.artifact_id, artifact.group_artifact_id, artifact.summary, artifact.open_date, users.realname, artifact.details, vectors) AS x WHERE ',
+ array ($this->field_separator, $this->artifactId, $words)) ;
+ $qpa = $this->addMatchCondition ($qpa, 'full_string_agg') ;
$qpa = db_construct_qpa ($qpa,
- ') ') ;
- }
- $qpa = db_construct_qpa ($qpa,
- 'ORDER BY ts_rank(full_vector_agg, to_tsquery($1)) DESC',
+ ' ORDER BY ts_rank(vectors, to_tsquery($1)) DESC',
array($words)) ;
-
- } else {
- $qpa = $this->addIlikeCondition ($qpa, 'x.full_string_agg') ;
- $qpa = db_construct_qpa ($qpa,
- 'ORDER BY x.artifact_id') ;
- }
- return $qpa ;
-
-
-
-
-
-
- $qpa = db_construct_qpa () ;
-
- if (forge_get_config('use_fti')) {
- $words=$this->getFTIwords();
- $artifactId = $this->artifactId;
-
- $qpa = db_construct_qpa ($qpa,
- 'SELECT a.group_artifact_id, a.artifact_id, ts_headline(summary, to_tsquery($1)) AS summary, ',
- array ($words)) ;
- $qpa = db_construct_qpa ($qpa,
- 'a.open_date, users.realname, rank FROM (SELECT a.artifact_id, SUM (ts_rank(ai.vectors, q) + ts_rank(ami.vectors, q)) AS rank, artifact.summary||$1||artifact.details||$1||coalesce(ff_string_agg(artifact_message.body), $1) as full_string_agg FROM artifact a LEFT OUTER JOIN artifact_message am USING (artifact_id)',
- array($this->field_separator)) ;
-
- $qpa = db_construct_qpa ($qpa,
- ', artifact_idx ai, artifact_message_idx ami, to_tsquery($1) q',
- array ($words)) ;
- $qpa = db_construct_qpa ($qpa,
- 'WHERE a.group_artifact_id=$1',
- array ($artifactId)) ;
- $qpa = db_construct_qpa ($qpa,
- ' AND ai.artifact_id = a.artifact_id AND ami.id = am.id AND ((ai.vectors @@ q OR ami.vectors @@ q) ') ;
-
- if (count($this->phrases)) {
- $qpa = db_construct_qpa ($qpa,
- $this->getOperator()) ;
+ } else {
$qpa = db_construct_qpa ($qpa,
- '((') ;
- $qpa = $this->addMatchCondition($qpa, 'a.details');
- $qpa = db_construct_qpa ($qpa,
- ') OR (') ;
- $qpa = $this->addMatchCondition($qpa, 'a.summary');
- $qpa = db_construct_qpa ($qpa,
- ') OR (') ;
- $qpa = $this->addMatchCondition($qpa, 'am.body');
- $qpa = db_construct_qpa ($qpa,
- '))') ;
+ 'SELECT artifact.artifact_id, artifact.group_artifact_id, artifact.summary, artifact.open_date, users.realname, artifact_idx.vectors FROM artifact, users, artifact_idx WHERE users.user_id = artifact.submitted_by AND artifact.group_artifact_id = $1 AND artifact.artifact_id = artifact_idx.artifact_id AND vectors @@ to_tsquery($2) ORDER BY ts_rank(vectors, to_tsquery($2)) DESC',
+ array ($this->artifactId, $words)) ;
}
- $qpa = db_construct_qpa ($qpa,
- ') GROUP BY a.artifact_id) x, artifact a, users WHERE a.artifact_id=x.artifact_id AND users.user_id=a.submitted_by ORDER BY group_artifact_id ASC, rank DESC, a.artifact_id ASC') ;
- } else {
- $qpa = db_construct_qpa ($qpa,
- 'SELECT DISTINCT ON (a.group_artifact_id,a.artifact_id) a.group_artifact_id,a.artifact_id,a.summary,a.open_date,users.realname,a.status_id ') ;
- $qpa = db_construct_qpa ($qpa,
- 'FROM artifact a LEFT OUTER JOIN artifact_message am USING (artifact_id), users WHERE a.group_artifact_id=$1 AND users.user_id=a.submitted_by AND ((',
- array ($this->artifactId)) ;
- $qpa = $this->addIlikeCondition ($qpa, 'cast(a.artifact_id as text)') ;
- $qpa = db_construct_qpa ($qpa,
- ') OR (') ;
- $qpa = $this->addIlikeCondition ($qpa, 'a.details') ;
- $qpa = db_construct_qpa ($qpa,
- ') OR (') ;
- $qpa = $this->addIlikeCondition ($qpa, 'a.summary') ;
+ } else {
$qpa = db_construct_qpa ($qpa,
- ') OR (') ;
- $qpa = $this->addIlikeCondition ($qpa, 'am.body') ;
+ 'SELECT x.* FROM (SELECT artifact.artifact_id, artifact.group_artifact_id, artifact.summary, artifact.open_date, users.realname, artifact.summary||$1||artifact.details||$1||coalesce(ff_string_agg(artifact_message.body), $1) as full_string_agg FROM artifact LEFT OUTER JOIN artifact_message USING (artifact_id), users WHERE users.user_id = artifact.submitted_by AND artifact.group_artifact_id = $2 GROUP BY artifact.artifact_id, artifact.group_artifact_id, artifact.summary, artifact.open_date, users.realname, artifact.details) AS x WHERE ',
+ array ($this->field_separator, $this->artifactId)) ;
+ $qpa = $this->addIlikeCondition ($qpa, 'full_string_agg') ;
$qpa = db_construct_qpa ($qpa,
- ')) ORDER BY group_artifact_id ASC, a.artifact_id ASC') ;
+ ' ORDER BY artifact_id') ;
}
- return $qpa;
+
+ return $qpa ;
}
/**
}
function addMatchCondition($qpa, $fieldName) {
+
+ error_log(print_r($this->phrases, 1));
if(!count($this->phrases)) {
$qpa = db_construct_qpa ($qpa, 'TRUE') ;
return $qpa;
}
- $regexs = array_map ('strtolower',
- array_merge ($this->phrases,
- str_replace(' ', "\s+", $this->phrases)));
+ $regexs = array();
+ foreach ($this->phrases as $p) {
+ $regexs[] = strtolower (preg_replace ("/\s+/", "\s+", $p));
+ }
for ($i = 0; $i < count ($regexs); $i++) {
if ($i > 0) {
function getQuery() {
$qpa = db_construct_qpa () ;
- $qpa = db_construct_qpa ($qpa,
- 'SELECT x.* FROM (SELECT artifact.artifact_id, artifact.group_artifact_id, artifact.summary, artifact.open_date, users.realname, artifact_group_list.name, artifact.summary||$1||artifact.details||$1||coalesce(ff_string_agg(artifact_message.body), $1) as full_string_agg',
- array ($this->field_separator));
if (forge_get_config('use_fti')) {
$words = $this->getFTIwords();
- $qpa = db_construct_qpa ($qpa,
- ', (artifact_idx.vectors || coalesce(ff_tsvector_agg(artifact_message_idx.vectors), to_tsvector($1))) AS full_vector_agg',
- array (''));
- }
- $qpa = db_construct_qpa ($qpa,
- ' FROM artifact LEFT OUTER JOIN artifact_message USING (artifact_id) ',
- array ()) ;
- if (forge_get_config('use_fti')) {
- $qpa = db_construct_qpa ($qpa, ' LEFT JOIN artifact_message_idx USING (artifact_id) ', array()) ;
- }
- $qpa = db_construct_qpa ($qpa, ' , users, artifact_group_list ', array()) ;
- if (forge_get_config('use_fti')) {
- $qpa = db_construct_qpa ($qpa,
- ', artifact_idx ',
- array ()) ;
- }
- $qpa = db_construct_qpa ($qpa,
- ' WHERE users.user_id = artifact.submitted_by AND artifact_group_list.group_artifact_id = artifact.group_artifact_id AND artifact_group_list.group_id = $1 ',
- array ($this->groupId)) ;
- if ($this->sections != SEARCH__ALL_SECTIONS) {
- $qpa = db_construct_qpa ($qpa,
- 'AND artifact_group_list.group_artifact_id = ANY ($1) ',
- array (db_int_array_to_any_clause ($this->sections))) ;
- }
- if (!$this->showNonPublic) {
- $qpa = db_construct_qpa ($qpa,
- 'AND artifact_group_list.is_public = 1 ') ;
- }
- if (forge_get_config('use_fti')) {
- $qpa = db_construct_qpa ($qpa,
- 'AND artifact.artifact_id = artifact_idx.artifact_id ',
- array ()) ;
- }
- $qpa = db_construct_qpa ($qpa,
- 'GROUP BY artifact.artifact_id, artifact.group_artifact_id, artifact.summary, artifact.open_date, users.realname, artifact_group_list.name, artifact.details') ;
- if (forge_get_config('use_fti')) {
- $qpa = db_construct_qpa ($qpa,
- ', artifact_idx.vectors',
- array ()) ;
- }
- $qpa = db_construct_qpa ($qpa,
- ') AS x WHERE ') ;
-
- if (forge_get_config('use_fti')) {
- $qpa = db_construct_qpa ($qpa,
- 'full_vector_agg @@ to_tsquery($1) ',
- array($words));
if (count($this->phrases)) {
$qpa = db_construct_qpa ($qpa,
- 'AND (') ;
- $qpa = $this->addMatchCondition ($qpa, 'x.full_string_agg') ;
+ 'SELECT x.* FROM (SELECT artifact.artifact_id, artifact.group_artifact_id, artifact.summary, artifact.open_date, users.realname, artifact.summary||$1||artifact.details||$1||coalesce(ff_string_agg(artifact_message.body), $1) as full_string_agg, artifact_idx.vectors FROM artifact LEFT OUTER JOIN artifact_message USING (artifact_id), users, artifact_group_list, artifact_idx WHERE users.user_id = artifact.submitted_by AND artifact.group_artifact_id = artifact_group_list.group_artifact_id AND artifact_group_list.group_id = $2 ',
+ array ($this->field_separator, $this->groupId)) ;
+
+ if ($this->sections != SEARCH__ALL_SECTIONS) {
+ $qpa = db_construct_qpa ($qpa,
+ 'AND artifact_group_list.group_artifact_id = ANY ($1) ',
+ array (db_int_array_to_any_clause ($this->sections))) ;
+ }
+
$qpa = db_construct_qpa ($qpa,
- ') ') ;
- }
- $qpa = db_construct_qpa ($qpa,
- 'ORDER BY ts_rank(full_vector_agg, to_tsquery($1)) DESC',
+ ' AND artifact.artifact_id = artifact_idx.artifact_id AND vectors @@ to_tsquery($1) GROUP BY artifact.artifact_id, artifact.group_artifact_id, artifact.summary, artifact.open_date, users.realname, artifact.details, vectors) AS x WHERE ',
+ array ($words)) ;
+ $qpa = $this->addMatchCondition ($qpa, 'full_string_agg') ;
+ $qpa = db_construct_qpa ($qpa,
+ ' ORDER BY ts_rank(vectors, to_tsquery($1)) DESC',
array($words)) ;
-
+ } else {
+ $qpa = db_construct_qpa ($qpa,
+ 'SELECT artifact.artifact_id, artifact.group_artifact_id, artifact.summary, artifact.open_date, users.realname, artifact_idx.vectors FROM artifact, users, artifact_group_list, artifact_idx WHERE users.user_id = artifact.submitted_by AND artifact.group_artifact_id = artifact_group_list.group_artifact_id AND artifact_group_list.group_id = $1 ',
+ array ($this->groupId)) ;
+
+ if ($this->sections != SEARCH__ALL_SECTIONS) {
+ $qpa = db_construct_qpa ($qpa,
+ 'AND artifact_group_list.group_artifact_id = ANY ($1) ',
+ array (db_int_array_to_any_clause ($this->sections))) ;
+ }
+
+ $qpa = db_construct_qpa ($qpa,
+ 'AND artifact.artifact_id = artifact_idx.artifact_id AND vectors @@ to_tsquery($1) ORDER BY ts_rank(vectors, to_tsquery($1)) DESC',
+ array ($words)) ;
+ }
+
} else {
- $qpa = $this->addIlikeCondition ($qpa, 'x.full_string_agg') ;
$qpa = db_construct_qpa ($qpa,
- ' ORDER BY x.name, x.artifact_id') ;
+ 'SELECT x.* FROM (SELECT artifact.artifact_id, artifact.group_artifact_id, artifact.summary, artifact.open_date, users.realname, artifact.summary||$1||artifact.details||$1||coalesce(ff_string_agg(artifact_message.body), $1) as full_string_agg FROM artifact LEFT OUTER JOIN artifact_message USING (artifact_id), users, artifact_group_list WHERE users.user_id = artifact.submitted_by AND artifact.group_artifact_id = artifact_group_list.group_artifact_id AND artifact_group_list.group_id = $2 ',
+ array ($this->field_separator, $this->groupId)) ;
+
+ if ($this->sections != SEARCH__ALL_SECTIONS) {
+ $qpa = db_construct_qpa ($qpa,
+ 'AND artifact_group_list.group_artifact_id = ANY ($1) ',
+ array (db_int_array_to_any_clause ($this->sections))) ;
+ }
+
+ $qpa = db_construct_qpa ($qpa,
+ 'GROUP BY artifact.artifact_id, artifact.group_artifact_id, artifact.summary, artifact.open_date, users.realname, artifact.details) AS x WHERE ',
+ array ()) ;
+ $qpa = $this->addIlikeCondition ($qpa, 'full_string_agg') ;
+ $qpa = db_construct_qpa ($qpa,
+ ' ORDER BY artifact_id') ;
}
+
+ error_log(db_qpa_to_string($qpa).' ');
return $qpa ;
}
--- /dev/null
+ALTER TABLE artifact_idx DROP COLUMN group_artifact_id;
+
+DELETE FROM artifact_idx;
+CREATE UNIQUE INDEX ON artifact_idx (artifact_id);
+INSERT INTO artifact_idx (SELECT a.artifact_id, 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);
+DROP TABLE artifact_message_idx;
+
+DELETE FROM project_task_idx;
+CREATE UNIQUE INDEX ON project_task_idx (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) GROUP BY t.project_task_id);
+DROP TABLE artifact_message_idx;
+
+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
+ DELETE FROM artifact_idx WHERE artifact_id=NEW.artifact_id;
+ INSERT INTO artifact_idx (SELECT a.artifact_id, 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);
+ -- **** artifact_message table ****
+ ELSIF table_name = ''artifact_message'' THEN
+ DELETE FROM artifact_idx WHERE artifact_id=NEW.artifact_id;
+ INSERT INTO artifact_idx (SELECT a.artifact_id, 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);
+ -- **** doc_data table ****
+ ELSIF table_name = ''doc_data'' THEN
+ IF TG_OP = ''INSERT'' THEN
+ INSERT INTO doc_data_idx (docid, group_id, vectors) VALUES (NEW.docid, NEW.group_id, to_tsvector(coalesce(NEW.title,'''') ||'' ''|| coalesce(NEW.description,'''')));
+ ELSIF TG_OP = ''UPDATE'' THEN
+ UPDATE doc_data_idx SET group_id=NEW.group_id, vectors=to_tsvector(coalesce(NEW.title,'''') ||'' ''|| coalesce(NEW.description,'''')) WHERE docid=NEW.docid;
+ ELSIF TG_OP = ''DELETE'' THEN
+ DELETE FROM doc_data_idx WHERE docid=OLD.docid;
+ 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 = ''INSERT'' THEN
+ 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,'''')));
+ ELSIF TG_OP = ''UPDATE'' THEN
+ 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;
+ ELSIF TG_OP = ''DELETE'' THEN
+ DELETE FROM groups_idx WHERE group_id=OLD.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 = ''INSERT'' THEN
+ INSERT INTO project_task_idx (project_task_id, vectors) VALUES (NEW.project_task_id, to_tsvector(coalesce(NEW.summary,'''') ||'' ''|| coalesce(NEW.details,'''')));
+ ELSIF TG_OP = ''UPDATE'' THEN
+ UPDATE project_task_idx SET vectors=to_tsvector(coalesce(NEW.summary,'''') ||'' ''|| coalesce(NEW.details,'''')) WHERE project_task_id=NEW.project_task_id;
+ ELSIF TG_OP = ''DELETE'' THEN
+ DELETE FROM project_task_idx WHERE project_task_id=OLD.project_task_id;
+ END IF;
+ -- **** project_messages table ****
+ ELSIF table_name = ''project_messages'' THEN
+ IF TG_OP = ''INSERT'' THEN
+ INSERT INTO project_messages_idx (id, project_task_id, vectors) VALUES (NEW.project_message_id, NEW.project_task_id, to_tsvector(coalesce(NEW.body,'''')));
+ ELSIF TG_OP = ''UPDATE'' THEN
+ UPDATE project_messages_idx SET project_task_id=NEW.project_task_id, vectors=to_tsvector(coalesce(NEW.body,'''')) WHERE id=NEW.project_message_id;
+ ELSIF TG_OP = ''DELETE'' THEN
+ DELETE FROM project_messages_idx WHERE id=OLD.project_message_id;
+ 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.skill_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';