function getQuery() {
$qpa = db_construct_qpa () ;
- $qpa = db_construct_qpa ($qpa,
- 'SELECT x.* FROM (SELECT y.group_project_id, y.project_task_id, y.summary, y.percent_complete, y.start_date, y.end_date, users.realname, project_group_list.project_name, y.full_string_agg',
- array());
+
+
+
if (forge_get_config('use_fti')) {
$words = $this->getFTIwords();
- $qpa = db_construct_qpa ($qpa,
- ', y.full_vector_agg',
- array());
- }
- $qpa = db_construct_qpa ($qpa,
- ' FROM (SELECT project_task.project_task_id, project_task.summary, project_task.percent_complete, project_task.start_date, project_task.end_date, project_task.created_by, project_task.group_project_id, project_task.summary||$1||project_task.details||$1||coalesce(ff_string_agg(project_messages.body), $1) as full_string_agg',
- array($this->field_separator));
- if (forge_get_config('use_fti')) {
- $qpa = db_construct_qpa ($qpa,
- ', project_task_idx.vectors || coalesce(ff_tsvector_agg(project_messages_idx.vectors), to_tsvector($1)) AS full_vector_agg',
- array(''));
- }
- $qpa = db_construct_qpa ($qpa,
- ' FROM project_task LEFT OUTER JOIN project_messages USING (project_task_id)',
- array());
-
- if (forge_get_config('use_fti')) {
- $qpa = db_construct_qpa ($qpa,
- ' LEFT OUTER JOIN project_messages_idx ON (project_messages.project_message_id = project_messages_idx.id) JOIN project_task_idx ON (project_task.project_task_id = project_task_idx.project_task_id)',
- array());
- }
- $qpa = db_construct_qpa ($qpa,
- ' GROUP BY project_task.project_task_id, project_task.summary, project_task.details, project_task.percent_complete, project_task.start_date, project_task.end_date, project_task.created_by, project_task.group_project_id',
- array());
-
- if (forge_get_config('use_fti')) {
- $qpa = db_construct_qpa ($qpa,
- ', project_task_idx.vectors',
- array());
- }
- $qpa = db_construct_qpa ($qpa,
- ') AS y, users, project_group_list',
- array());
-
- if (forge_get_config('use_fti')) {
- $qpa = db_construct_qpa ($qpa,
- ', project_task_idx',
- array());
- }
- $qpa = db_construct_qpa ($qpa,
- ' WHERE y.created_by = users.user_id AND y.group_project_id = project_group_list.group_project_id AND project_group_list.group_id = $1',
- array($this->groupId));
- if ($this->sections != SEARCH__ALL_SECTIONS) {
- $qpa = db_construct_qpa ($qpa,
- ' AND y.group_project_id = ANY ($1)',
- array (db_int_array_to_any_clause ($this->sections))) ;
- }
- if (!$this->showNonPublic) {
- $qpa = db_construct_qpa ($qpa,
- ' AND project_group_list.is_public = 1') ;
- }
- $qpa = db_construct_qpa ($qpa,
- ' GROUP BY y.group_project_id, y.project_task_id, y.summary, y.percent_complete, y.start_date, y.end_date, users.realname, project_group_list.project_name, y.full_string_agg',
- array());
-
- if (forge_get_config('use_fti')) {
- $qpa = db_construct_qpa ($qpa,
- ', y.full_vector_agg',
- array());
- }
- $qpa = db_construct_qpa ($qpa,
- ') AS x WHERE ',
- array());
-
- 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 project_task.project_task_id, project_task.group_project_id, project_task.summary, project_task.percent_complete, project_task.start_date, project_task.end_date, users.realname, project_group_list.project_name, project_task.summary||$1||project_task.details||$1||coalesce(ff_string_agg(project_messages.body), $1) as full_string_agg, project_task_idx.vectors FROM project_task LEFT OUTER JOIN project_messages USING (project_task_id), users, project_group_list, project_task_idx WHERE users.user_id = project_task.created_by AND project_task.group_project_id = project_group_list.group_project_id AND project_group_list.group_id = $2 ',
+ array ($this->field_separator, $this->groupId)) ;
+
+ if ($this->sections != SEARCH__ALL_SECTIONS) {
+ $qpa = db_construct_qpa ($qpa,
+ 'AND project_group_list.group_project_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 project_task.project_task_id = project_task_idx.project_task_id AND vectors @@ to_tsquery($1) GROUP BY project_task.project_task_id, project_task.group_project_id, project_task.summary, project_task.percent_complete, project_task.start_date, project_task.end_date, users.realname, project_group_list.project_name, project_task.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 project_task.project_task_id, project_task.group_project_id, project_task.summary, project_task.percent_complete, project_task.start_date, project_task.end_date, users.realname, project_group_list.project_name, project_task_idx.vectors FROM project_task, users, project_group_list, project_task_idx WHERE users.user_id = project_task.created_by AND project_task.group_project_id = project_group_list.group_project_id AND project_group_list.group_id = $1 ',
+ array ($this->groupId)) ;
+
+ if ($this->sections != SEARCH__ALL_SECTIONS) {
+ $qpa = db_construct_qpa ($qpa,
+ 'AND project_group_list.group_project_id = ANY ($1) ',
+ array (db_int_array_to_any_clause ($this->sections))) ;
+ }
+
+ $qpa = db_construct_qpa ($qpa,
+ 'AND project_task.project_task_id = project_task_idx.project_task_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.project_name, x.project_task_id') ;
+ 'SELECT x.* FROM (SELECT project_task.project_task_id, project_task.group_project_id, project_task.summary, project_task.percent_complete, project_task.start_date, project_task.end_date, users.realname, project_group_list.project_name, project_task.summary||$1||project_task.details||$1||coalesce(ff_string_agg(project_messages.body), $1) as full_string_agg FROM project_task LEFT OUTER JOIN project_messages USING (project_task_id), users, project_group_list WHERE users.user_id = project_task.created_by AND project_task.group_project_id = project_group_list.group_project_id AND project_group_list.group_id = $2 ',
+ array ($this->field_separator, $this->groupId)) ;
+
+ if ($this->sections != SEARCH__ALL_SECTIONS) {
+ $qpa = db_construct_qpa ($qpa,
+ 'AND project_group_list.group_project_id = ANY ($1) ',
+ array (db_int_array_to_any_clause ($this->sections))) ;
+ }
+
+ $qpa = db_construct_qpa ($qpa,
+ 'GROUP BY project_task.project_task_id, project_task.group_project_id, project_task.summary, project_task.percent_complete, project_task.start_date, project_task.end_date, users.realname, project_group_list.project_name, project_task.details) AS x WHERE ',
+ array ()) ;
+ $qpa = $this->addIlikeCondition ($qpa, 'full_string_agg') ;
+ $qpa = db_construct_qpa ($qpa,
+ ' ORDER BY project_task_id') ;
}
return $qpa ;
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.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);
+ 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.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);
+ END IF;
-- **** doc_data table ****
ELSIF table_name = ''doc_data'' THEN
IF TG_OP = ''INSERT'' THEN
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
+ 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);
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;
+ 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);
END IF;
-- **** skills_data table ****
ELSIF table_name = ''skills_data'' THEN