define('SYS_DB_TROVE', $gfconn2);
define('SYS_DB_SEARCH', $gfconn2);
+ db_query_params ('SELECT set_config($1, $2, false)',
+ array('default_text_search_config',
+ 'simple'));
+
// Register top-level "finally" handler to abort current
// transaction in case of error
register_shutdown_function("system_cleanup");
return db_construct_qpa ($old_qpa, $new_qpa[0], $new_qpa[1]) ;
}
+function db_qpa_to_string ($qpa) {
+ $sql = $qpa[0];
+ $params = $qpa[1];
+ foreach ($params as $index => $value) {
+ $sql = preg_replace('/\\$'.($index+1).'(?!\d)/', "'".$value."'", $sql);
+ }
+ return $sql;
+}
+
// Local Variables:
// mode: php
// c-file-style: "bsd"
* @return array query+params array
*/
function getQuery() {
-
-
$qpa = db_construct_qpa () ;
if (forge_get_config('use_fti')) {
- $words=$this->getFormattedWords();
+ $words=$this->getFTIwords();
$artifactId = $this->artifactId;
- if (count($words)) {
- $qpa = db_construct_qpa ($qpa,
- 'SELECT a.group_artifact_id, a.artifact_id, ts_headline(summary, $1) AS summary, ',
- array ($this->getFormattedWords())) ;
- $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 FROM artifact a LEFT OUTER JOIN artifact_message am USING (artifact_id)') ;
+ $qpa = db_construct_qpa ($qpa,
+ 'SELECT a.group_artifact_id, a.artifact_id, ts_headline(summary, $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 FROM artifact a LEFT OUTER JOIN artifact_message am USING (artifact_id)') ;
+ $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,
- ', 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()) ;
- $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,
- '))') ;
- }
+ $this->getOperator()) ;
$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 = $this->addMatchCondition($qpa, 'a.details');
$qpa = db_construct_qpa ($qpa,
- 'SELECT a.group_artifact_id, a.artifact_id, summary, a.open_date, users.realname, rank FROM (SELECT a.artifact_id, 0 AS rank FROM artifact a LEFT OUTER JOIN artifact_message am USING (artifact_id)') ;
-
+ ') OR (') ;
+ $qpa = $this->addMatchCondition($qpa, 'a.summary');
$qpa = db_construct_qpa ($qpa,
- 'WHERE a.group_artifact_id=$1',
- array ($artifactId)) ;
-
- if (count($this->phrases)) {
- $qpa = db_construct_qpa ($qpa,
- ' AND ((') ;
- $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,
- '))') ;
- }
+ ') OR (') ;
+ $qpa = $this->addMatchCondition($qpa, 'am.body');
$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') ;
+ '))') ;
}
+ $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 ') ;
}
function getFTIQuery() {
- $words = $this->getFormattedWords();
+ $words = $this->getFTIwords();
$group_id=$this->groupId;
$qpa = db_construct_qpa () ;
- if(count($this->words)) {
- $qpa = db_construct_qpa($qpa,
- 'SELECT doc_data.docid, doc_data.filename, ts_headline(doc_data.title, q) AS title, ts_headline(doc_data.description, q) AS description doc_groups.groupname FROM doc_data, doc_groups, doc_data_idx, to_tsquery($1) q',
- array (implode (' ', $words)));
- $qpa = db_construct_qpa($qpa,
- ' WHERE doc_data.doc_group = doc_groups.doc_group AND doc_data.docid = doc_data_idx.docid AND (vectors @@ q') ;
- if (count($this->phrases)) {
- $qpa = db_construct_qpa($qpa, $this->getOperator());
- $qpa = db_construct_qpa($qpa, '(');
- $qpa = $this->addMatchCondition($qpa, 'title');
- $qpa = db_construct_qpa($qpa, ') OR (');
- $qpa = $this->addMatchCondition($qpa, 'description');
- $qpa = db_construct_qpa($qpa, ') OR (');
- $qpa = $this->addIlikeCondition($qpa, 'data_words', $this->words);
- $qpa = db_construct_qpa($qpa, ')');
- }
- $qpa = db_construct_qpa($qpa,
- ') AND doc_data.group_id = $1',
- array ($group_id)) ;
- if ($this->sections != SEARCH__ALL_SECTIONS) {
- $qpa = db_construct_qpa($qpa,
- ' AND doc_groups.doc_group = ANY ($1)',
- db_int_array_to_any_clause ($this->sections));
- }
- if ($this->showNonPublic) {
- $qpa = db_construct_qpa($qpa,
- ' AND doc_data.stateid IN (1, 4, 5)');
- } else {
- $qpa = db_construct_qpa($qpa,
- ' AND doc_data.stateid = 1');
- }
- $qpa = db_construct_qpa($qpa,
- ' ORDER BY ts_rank(vectors, q) DESC, groupname ASC');
+
+ $qpa = db_construct_qpa ($qpa,
+ 'SELECT doc_data.docid, doc_data.filename, ts_headline(doc_data.title, q) AS title, ts_headline(doc_data.description, q) AS description doc_groups.groupname FROM doc_data, doc_groups, doc_data_idx, to_tsquery($1) q',
+ array (implode (' ', $words))) ;
+ $qpa = db_construct_qpa ($qpa,
+ ' WHERE doc_data.doc_group = doc_groups.doc_group AND doc_data.docid = doc_data_idx.docid AND (vectors @@ q') ;
+ if (count($this->phrases)) {
+ $qpa = db_construct_qpa ($qpa,
+ $this->getOperator()) ;
+ $qpa = db_construct_qpa ($qpa,
+ '(') ;
+ $qpa = $this->addMatchCondition($qpa, 'title');
+ $qpa = db_construct_qpa ($qpa,
+ ') OR (') ;
+ $qpa = $this->addMatchCondition($qpa, 'description');
+ $qpa = db_construct_qpa ($qpa,
+ ')') ;
+ }
+ $qpa = db_construct_qpa ($qpa,
+ ') AND doc_data.group_id = $1',
+ array ($group_id)) ;
+ if ($this->sections != SEARCH__ALL_SECTIONS) {
+ $qpa = db_construct_qpa ($qpa,
+ ' AND doc_groups.doc_group = ANY ($1)',
+ db_int_array_to_any_clause ($this->sections)) ;
+ }
+ if ($this->showNonPublic) {
+ $qpa = db_construct_qpa ($qpa,
+ ' AND doc_data.stateid IN (1, 4, 5)') ;
} else {
- $qpa = db_construct_qpa($qpa,
- 'SELECT doc_data.docid, filename, title, description doc_groups.groupname FROM doc_data, doc_groups');
- $qpa = db_construct_qpa($qpa,
- 'WHERE doc_data.doc_group = doc_groups.doc_group');
- if (count($this->phrases)) {
- $qpa = db_construct_qpa($qpa,
- $this->getOperator()) ;
- $qpa = db_construct_qpa($qpa,
- '((');
- $qpa = $this->addMatchCondition($qpa, 'title');
- $qpa = db_construct_qpa($qpa,
- ') OR (');
- $qpa = $this->addMatchCondition($qpa, 'description');
- $qpa = db_construct_qpa($qpa,
- '))');
- }
- $qpa = db_construct_qpa($qpa,
- ') AND doc_data.group_id = $1',
- array($group_id));
- if ($this->sections != SEARCH__ALL_SECTIONS) {
- $qpa = db_construct_qpa($qpa,
- 'AND doc_groups.doc_group = ANY ($1) ',
- db_int_array_to_any_clause($this->sections));
- }
- if ($this->showNonPublic) {
- $qpa = db_construct_qpa($qpa,
- ' AND doc_data.stateid IN (1, 4, 5)');
- } else {
- $qpa = db_construct_qpa($qpa,
- ' AND doc_data.stateid = 1');
- }
- $qpa = db_construct_qpa($qpa,
- ' ORDER BY groupname');
+ $qpa = db_construct_qpa ($qpa,
+ ' AND doc_data.stateid = 1') ;
}
+ $qpa = db_construct_qpa ($qpa,
+ ' ORDER BY ts_rank(vectors, q) DESC, groupname ASC') ;
return $qpa ;
}
$qpa = db_construct_qpa () ;
if (forge_get_config('use_fti')) {
- $words = $this->getFormattedWords();
- if(count($this->words)) {
- $qpa = db_construct_qpa ($qpa,
- 'SELECT ts_headline(unix_group_name, q) as unix_group_name, ts_headline(short_description, q) as short_description, type_id, groups.group_id, license, register_time FROM groups, groups_idx, to_tsquery($1) q ',
- array (implode (' ', $words))) ;
- $qpa = db_construct_qpa ($qpa,
- 'WHERE status IN ($1, $2) AND short_description <> $3 AND groups.group_id = groups_idx.group_id',
- array ('A',
- 'H',
- '')) ;
+ $words = $this->getFTIwords();
+
+ $qpa = db_construct_qpa ($qpa,
+ 'SELECT ts_headline(unix_group_name, q) as unix_group_name, ts_headline(short_description, q) as short_description, type_id, groups.group_id, license, register_time FROM groups, groups_idx, to_tsquery($1) q ',
+ array (implode (' ', $words))) ;
+ $qpa = db_construct_qpa ($qpa,
+ 'WHERE status IN ($1, $2) AND short_description <> $3 AND groups.group_id = groups_idx.group_id',
+ array ('A',
+ 'H',
+ '')) ;
+ $qpa = db_construct_qpa ($qpa,
+ ' AND (vectors @@ q' ) ;
+ if (count($this->phrases)) {
$qpa = db_construct_qpa ($qpa,
- ' AND (vectors @@ q' ) ;
- if (count($this->phrases)) {
- $qpa = db_construct_qpa ($qpa,
- $this->getOperator()) ;
- $qpa = db_construct_qpa ($qpa,
- '(') ;
- $qpa = $this->addMatchCondition($qpa, 'group_name');
- $qpa = db_construct_qpa ($qpa,
- ') OR (') ;
- $qpa = $this->addMatchCondition($qpa, 'unix_group_name');
- $qpa = db_construct_qpa ($qpa,
- ') OR (') ;
- $qpa = $this->addMatchCondition($qpa, 'short_description');
- $qpa = db_construct_qpa ($qpa,
- ')') ;
- }
+ $this->getOperator()) ;
$qpa = db_construct_qpa ($qpa,
- ') ORDER BY ts_rank(vectors, q) DESC, group_name ASC') ;
- } else {
+ '(') ;
+ $qpa = $this->addMatchCondition($qpa, 'group_name');
$qpa = db_construct_qpa ($qpa,
- 'SELECT unix_group_name, short_description, type_id, groups.group_id, license, register_time FROM groups ') ;
+ ') OR (') ;
+ $qpa = $this->addMatchCondition($qpa, 'unix_group_name');
$qpa = db_construct_qpa ($qpa,
- 'WHERE status IN ($1, $2) AND short_description <> $3',
- array ('A',
- 'H',
- '')) ;
- if (count($this->phrases)) {
- $qpa = db_construct_qpa ($qpa,
- ' AND ((' ) ;
- $qpa = $this->addMatchCondition($qpa, 'group_name');
- $qpa = db_construct_qpa ($qpa,
- ') OR (') ;
- $qpa = $this->addMatchCondition($qpa, 'unix_group_name');
- $qpa = db_construct_qpa ($qpa,
- ') OR (') ;
- $qpa = $this->addMatchCondition($qpa, 'short_description');
- $qpa = db_construct_qpa ($qpa,
- '))') ;
- }
+ ') OR (') ;
+ $qpa = $this->addMatchCondition($qpa, 'short_description');
$qpa = db_construct_qpa ($qpa,
- ') ORDER BY group_name' ) ;
+ ')') ;
}
+ $qpa = db_construct_qpa ($qpa,
+ ') ORDER BY ts_rank(vectors, q) DESC, group_name ASC') ;
} else {
$qpa = db_construct_qpa ($qpa,
'SELECT group_name,unix_group_name,type_id,groups.group_id, short_description,license,register_time FROM groups WHERE status IN ($1, $2) AND short_description <> $3 AND groups.group_id = groups_idx.group_id',
$qpa = db_construct_qpa () ;
if (forge_get_config('use_fti')) {
- $words = $this->getFormattedWords();
-
-
- if(count($this->words)) {
- $qpa = db_construct_qpa ($qpa,
- 'SELECT forum.msg_id, ts_headline(forum.subject, q) AS subject, forum.post_date, users.realname FROM forum, users, to_tsquery($1) AS q, forum_idx as fi WHERE forum.group_forum_id = $2 AND forum.posted_by = users.user_id AND fi.msg_id = forum.msg_id AND vectors @@ q ',
- array ($words,
- $this->forumId)) ;
- $phraseOp = $this->getOperator();
- } else {
- $qpa = db_construct_qpa ($qpa,
- 'SELECT forum.msg_id, subject, forum.post_date, users.realname FROM forum, users WHERE forum.group_forum_id = $1 AND forum.posted_by = users.user_id ',
- array ($this->forumId)) ;
- }
+ $words = $this->getFTIwords();
+ $qpa = db_construct_qpa ($qpa,
+ 'SELECT forum.msg_id, ts_headline(forum.subject, q) AS subject, forum.post_date, users.realname FROM forum, users, to_tsquery($1) AS q, forum_idx as fi WHERE forum.group_forum_id = $2 AND forum.posted_by = users.user_id AND fi.msg_id = forum.msg_id AND vectors @@ q ',
+ array ($words,
+ $this->forumId)) ;
+ $phraseOp = $this->getOperator();
if(count($this->phrases)) {
$qpa = db_construct_qpa ($qpa,
$qpa = db_construct_qpa ($qpa,
')) ') ;
}
- if(count($this->words)) {
- $qpa = db_construct_qpa ($qpa,
- 'ORDER BY ts_rank(vectors, q) DESC') ;
- } else {
- $qpa = db_construct_qpa ($qpa,
- 'ORDER BY post_date DESC') ;
- }
+ $qpa = db_construct_qpa ($qpa,
+ 'ORDER BY ts_rank(vectors, q) DESC') ;
} else {
$qpa = db_construct_qpa ($qpa,
'SELECT forum.msg_id, forum.subject, forum.post_date, users.realname FROM forum,users WHERE users.user_id=forum.posted_by AND ((') ;
$qpa = db_construct_qpa ($qpa,
'SELECT forum.msg_id, ts_headline(forum.subject, q) AS subject, forum.post_date, users.realname, forum_group_list.forum_name FROM forum, users, forum_group_list, forum_idx, to_tsquery($1) as q ',
- array ($this->getFormattedWords())) ;
+ array ($this->getFTIwords())) ;
$qpa = db_construct_qpa ($qpa,
'WHERE users.user_id = forum.posted_by AND vectors @@ q AND forum.msg_id = forum_idx.msg_id AND forum_group_list.group_forum_id = forum.group_forum_id AND forum_group_list.is_public <> 9 AND forum.group_forum_id IN (SELECT group_forum_id FROM forum_group_list WHERE group_id = $1) ',
array ($this->groupId));
$qpa = db_construct_qpa () ;
if (forge_get_config('use_fti')) {
- if(count($this->words)) {
- $qpa = db_construct_qpa () ;
- $qpa = db_construct_qpa ($qpa,
- '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 FROM frs_file, frs_release, users, frs_package, to_tsquery($1) AS q, frs_release_idx r, frs_file_idx f WHERE frs_release.released_by = users.user_id AND r.release_id = frs_release.release_id AND f.file_id = frs_file.file_id AND frs_package.package_id = frs_release.package_id AND frs_file.release_id=frs_release.release_id AND frs_package.group_id=$2 ',
- array ($this->getFormattedWords(),
- $this->groupId)) ;
- } else {
- $qpa = db_construct_qpa ($qpa,
- 'SELECT frs_package.name as package_name, frs_release.name as release_name, frs_release.release_date, frs_release.release_id, users.realname FROM frs_file, frs_release, users, frs_package WHERE frs_release.released_by = users.user_id AND frs_package.package_id = frs_release.package_id AND frs_file.release_id=frs_release.release_id AND frs_package.group_id=$1 ',
- array ($this->groupId)) ;
- }
+ $qpa = db_construct_qpa () ;
+ $qpa = db_construct_qpa ($qpa,
+ '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 FROM frs_file, frs_release, users, frs_package, to_tsquery($1) AS q, frs_release_idx r, frs_file_idx f WHERE frs_release.released_by = users.user_id AND r.release_id = frs_release.release_id AND f.file_id = frs_file.file_id AND frs_package.package_id = frs_release.package_id AND frs_file.release_id=frs_release.release_id AND frs_package.group_id=$2 ',
+ array ($this->getFTIwords(),
+ $this->groupId)) ;
if ($this->sections != SEARCH__ALL_SECTIONS) {
$qpa = db_construct_qpa ($qpa,
'AND frs_package.package_id = ANY ($1) ',
$qpa = db_construct_qpa ($qpa,
'AND is_public = 1 ') ;
}
- if (count ($this->words)) {
- $qpa = db_construct_qpa ($qpa,
- 'AND (f.vectors @@ q OR r.vectors @@ q) ') ;
- }
+ $qpa = db_construct_qpa ($qpa,
+ 'AND (f.vectors @@ q OR r.vectors @@ q) ') ;
if(count($this->phrases)) {
$qpa = db_construct_qpa ($qpa,
'AND ((') ;
if (forge_get_config('use_fti')) {
$group_id=$this->groupId;
- if (count ($this->words)) {
- $words = $this->getFormattedWords();
- $qpa = db_construct_qpa ($qpa,
- 'SELECT ts_headline(news_bytes.summary, q) as summary, news_bytes.post_date, news_bytes.forum_id, users.realname FROM news_bytes, users, to_tsquery($1) AS q, news_bytes_idx WHERE (news_bytes.group_id=$2 AND news_bytes.is_approved <> 4 AND news_bytes_idx.id = news_bytes.id AND news_bytes.submitted_by=users.user_id) AND (vectors @@ q ',
- array ($words,
- $group_id)) ;
- } else {
- $qpa = db_construct_qpa ($qpa,
- 'SELECT summary, news_bytes.post_date, news_bytes.forum_id, users.realname FROM news_bytes, users WHERE (news_bytes.group_id=$1 AND news_bytes.is_approved <> 4 AND news_bytes.submitted_by=users.user_id) ',
- array ($group_id)) ;
- }
+ $words = $this->getFTIwords();
+ $qpa = db_construct_qpa ($qpa,
+ 'SELECT ts_headline(news_bytes.summary, q) as summary, news_bytes.post_date, news_bytes.forum_id, users.realname FROM news_bytes, users, to_tsquery($1) AS q, news_bytes_idx WHERE (news_bytes.group_id=$2 AND news_bytes.is_approved <> 4 AND news_bytes_idx.id = news_bytes.id AND news_bytes.submitted_by=users.user_id) AND (vectors @@ q ',
+ array ($words,
+ $group_id)) ;
if (count ($this->phrases)) {
- if (count ($this->words)) {
- $qpa = db_construct_qpa ($qpa,
- $this->getOperator()) ;
- }
+ $qpa = db_construct_qpa ($qpa,
+ $this->getOperator()) ;
$qpa = db_construct_qpa ($qpa,
' ((') ;
$qpa = $this->addMatchCondition ($qpa, 'summary') ;
$qpa = db_construct_qpa ($qpa,
'))') ;
}
- if (count ($this->words)) {
- $qpa = db_construct_qpa ($qpa,
- ') ORDER BY ts_rank(vectors, q) DESC, post_date DESC') ;
- } else {
- $qpa = db_construct_qpa ($qpa,
- ') ORDER BY post_date DESC') ;
- }
+ $qpa = db_construct_qpa ($qpa,
+ ') ORDER BY ts_rank(vectors, q) DESC, post_date DESC') ;
} else {
$qpa = db_construct_qpa ($qpa,
'SELECT news_bytes.summary, news_bytes.post_date, news_bytes.forum_id, users.realname FROM news_bytes, users WHERE group_id=$1 AND is_approved <> 4 AND news_bytes.submitted_by = users.user_id AND ((',
* @return array query+params array
*/
function getQuery() {
-
-
$qpa = db_construct_qpa () ;
if (forge_get_config('use_fti')) {
- if (count ($this->words)) {
- $words = $this->getFormattedWords();
- $qpa = db_construct_qpa ($qpa,
- 'SELECT users.user_id, user_name, ts_headline(realname, q) as realname FROM users, to_tsquery($1) AS q, users_idx WHERE status=$2 AND users_idx.user_id = users.user_id AND (vectors @@ q ',
- array ($words,
- 'A'));
- } else {
- $qpa = db_construct_qpa ($qpa,
- 'SELECT users.user_id, user_name, realname FROM users WHERE status=$1 AND users_idx.user_id = users.user_id AND (',
- array ('A'));
- }
+ $words = $this->getFTIwords();
+ $qpa = db_construct_qpa ($qpa,
+ 'SELECT users.user_id, user_name, ts_headline(realname, q) as realname FROM users, to_tsquery($1) AS q, users_idx WHERE status=$2 AND users_idx.user_id = users.user_id AND (vectors @@ q ',
+ array ($words,
+ 'A'));
if (count ($this->phrases)) {
- if (count ($this->words)) {
- $qpa = db_construct_qpa ($qpa,
- $this->getOperator()) ;
- }
+ $qpa = db_construct_qpa ($qpa,
+ $this->getOperator()) ;
$qpa = db_construct_qpa ($qpa,
'(') ;
$qpa = $this->addMatchCondition($qpa, 'user_name');
$qpa = db_construct_qpa ($qpa,
')') ;
}
- if (count ($this->words)) {
- $qpa = db_construct_qpa ($qpa,
- ') ORDER BY ts_rank(vectors, q) DESC, user_name') ;
- } else {
- $qpa = db_construct_qpa ($qpa,
- ') ORDER BY user_name') ;
- }
+ $qpa = db_construct_qpa ($qpa,
+ ') ORDER BY ts_rank(vectors, q) DESC, user_name') ;
} else {
$qpa = db_construct_qpa ($qpa,
'SELECT user_name,user_id,realname FROM users WHERE ((') ;
* @return array query+params array
*/
function getQuery() {
- global $LUSER;
-
$qpa = db_construct_qpa () ;
if (forge_get_config('use_fti')) {
- if (count ($this->words)) {
- $words = $this->getFormattedWords();
- $qpa = db_construct_qpa ($qpa,
- 'SELECT DISTINCT ON (ts_rank(vectors, q), group_name) type_id, g.group_id, ts_headline(group_name, q) as group_name, unix_group_name, ts_headline(short_description, q) as short_description FROM groups AS g, to_tsquery($1) AS q, groups_idx as i WHERE g.status in ($2, $3) ',
- array ($words,
- 'A',
- 'H')) ;
- $qpa = db_construct_qpa ($qpa,
- 'AND vectors @@ q ') ;
- } else {
- $qpa = db_construct_qpa ($qpa,
- 'SELECT DISTINCT ON (group_name) type_id, g.group_id, group_name, unix_group_name, short_description FROM groups AS g WHERE g.status in ($1, $2) ',
- array ('A',
- 'H')) ;
- }
+ $words = $this->getFTIwords();
+ $qpa = db_construct_qpa ($qpa,
+ 'SELECT DISTINCT ON (ts_rank(vectors, q), group_name) type_id, g.group_id, ts_headline(group_name, q) as group_name, unix_group_name, ts_headline(short_description, q) as short_description FROM groups AS g, to_tsquery($1) AS q, groups_idx as i WHERE g.status in ($2, $3) ',
+ array ($words,
+ 'A',
+ 'H')) ;
+ $qpa = db_construct_qpa ($qpa,
+ 'AND vectors @@ q ') ;
if (count($this->phrases)) {
$qpa = db_construct_qpa ($qpa,
' AND ((') ;
$qpa = db_construct_qpa ($qpa,
')) ') ;
}
- if (count ($this->words)) {
- $qpa = db_construct_qpa ($qpa,
- 'AND g.group_id = i.group_id ORDER BY ts_rank(vectors, q) DESC, group_name') ;
- } else {
- $qpa = db_construct_qpa ($qpa,
- 'ORDER BY group_name') ;
- }
+ $qpa = db_construct_qpa ($qpa,
+ 'AND g.group_id = i.group_id ORDER BY ts_rank(vectors, q) DESC, group_name') ;
} else {
$qpa = db_construct_qpa ($qpa,
'SELECT g.group_name AS group_name, g.unix_group_name AS unix_group_name, g.type_id AS type_id, g.group_id AS group_id, g.short_description AS short_description FROM groups g WHERE g.status IN ($1, $2) AND ((',
}
if (is_array ($this->phrases)){
$this->phrases = array_map ('addslashes',$this->phrases);
- } else{
+ } else {
$this->phrases = array();
}
$this->rowsPerPage = $rowsPerPage;
$inQuote = false;
foreach(explode(' ', quotemeta($words)) as $word) {
if($inQuote) {
- if(substr($word, -3) == "\\\\'") {
- $word = substr($word, 0, -3);
+ if(substr($word, -1) == "'") {
+ $word = substr($word, 0, -1);
$inQuote = false;
$phrase .= ' '.$word;
$this->phrases[] = $phrase;
$phrase .= ' '.$word;
}
} else {
- if(substr($word, 0, 3) == "\\\\'") {
- $word = substr($word, 3);
+ if(substr($word, 0, 1) == "'") {
+ $word = substr($word, 1);
$inQuote = true;
- if(substr($word, -3) == "\\\\'") {
+ if(substr($word, -1) == "'") {
// This is a special case where the phrase is just one word
- $word = substr($word, 0, -3);
+ $word = substr($word, 0, -1);
$inQuote = false;
- $this->phrases[] = $word;
+ $this->words[] = $word;
} else {
$phrase = $word;
}
} else {
$qpa = $this->getQuery();
}
- if (forge_get_config('use_fti')) {
- db_query_params('SELECT set_config($1, $2, false)',
- array('default_text_search_config',
- 'simple'));
- }
- $this->result = db_query_qpa(
+
+ $this->result = db_query_qpa (
$qpa,
$this->rowsPerPage + 1,
$this->offset,
}
function addMatchCondition($qpa, $fieldName) {
- if(!count($arr)) {
+ if(!count($this->phrases)) {
$qpa = db_construct_qpa ($qpa, 'TRUE') ;
- } else {
- $regexs = str_replace(' ', "\\\s+", $arr);
- for ($i = 0; $i < count ($regexs); $i++) {
- if ($i > 0) {
- $qpa = db_construct_qpa ($qpa,
- $this->operator) ;
- }
+ return $qpa;
+ }
+
+ $regexs = array_map ('strtolower',
+ array_merge ($this->phrases,
+ str_replace(' ', "\s+", $this->phrases)));
+
+ for ($i = 0; $i < count ($regexs); $i++) {
+ if ($i > 0) {
$qpa = db_construct_qpa ($qpa,
- $fieldName.' ~* $1',
- array ($regexs[$i])) ;
+ $this->operator) ;
}
+ $qpa = db_construct_qpa ($qpa,
+ $fieldName.' ~* $1',
+ array ($regexs[$i])) ;
}
return $qpa;
}
function addIlikeCondition($qpa, $fieldName) {
$wordArgs = array_map ('strtolower',
- array_merge($this->words, str_replace(' ', "\\\s+", $this->phrases)));
+ array_merge($this->words, $this->phrases));
for ($i = 0; $i < count ($wordArgs); $i++) {
if ($i > 0) {
return $this->words;
}
+ /**
+ * getPhrases - returns the array containing phrases we are searching for
+ *
+ * @return array phrases we are searching for
+ */
+ function getPhrases() {
+ return $this->phrases;
+ }
+
/**
* setSections - set the sections list
*
}
/**
- * getFormattedWords - get words formatted in order to be used in the FTI stored procedures
+ * getFTIwords - get words formatted in order to be used in the FTI stored procedures
*
- * @return string words we are searching for, separated by a pipe
- */
- function getFormattedWords() {
+ * @return string words we are searching for, separated by
+ */
+ function getFTIwords() {
+ $bits = $this->words;
+ foreach ($this->phrases as $p) {
+ $bits[] = '('.implode ('&', explode (' ', $p)).')';
+ }
if ($this->isExact) {
- $words = implode('&', $this->words);
+ $query = implode('&', $bits);
} else {
- $words = implode('|', $this->words);
+ $query = implode('|', $bits);
}
- return $words;
+ return $query;
}
}
$qpa = db_construct_qpa () ;
if (forge_get_config('use_fti')) {
- if(count($this->words)) {
- $words = $this->getFormattedWords();
- $qpa = db_construct_qpa ($qpa,
- 'SELECT skills_data.skills_data_id, skills_data.type, skills_data.start, skills_data.finish, ts_headline(skills_data.title, q) as title, ts_headline(skills_data.keywords, q) as keywords FROM skills_data, users, skills_data_types, to_tsquery($1) AS q, skills_data_idx WHERE (vectors @@ q ',
- array ($words)) ;
- } else {
- $qpa = db_construct_qpa ($qpa,
- 'SELECT skills_data.skills_data_id, skills_data.type, skills_data.start, skills_data.finish, FROM skills_data, users, skills_data_types WHERE (vectors @@ q ') ;
- }
-
+ $words = $this->getFTIwords();
+ $qpa = db_construct_qpa ($qpa,
+ 'SELECT skills_data.skills_data_id, skills_data.type, skills_data.start, skills_data.finish, ts_headline(skills_data.title, q) as title, ts_headline(skills_data.keywords, q) as keywords FROM skills_data, users, skills_data_types, to_tsquery($1) AS q, skills_data_idx WHERE (vectors @@ q ',
+ array ($words)) ;
if (count ($this->phrases)) {
- if (count ($this->words)) {
- $qpa = db_construct_qpa ($qpa,
- $this->getOperator()) ;
- }
+ $qpa = db_construct_qpa ($qpa,
+ $this->getOperator()) ;
$qpa = db_construct_qpa ($qpa,
' ((') ;
$qpa = $this->addMatchCondition ($qpa, 'skills_data.title') ;
}
$qpa = db_construct_qpa ($qpa,
')') ;
- if (count ($this->words)) {
- $qpa = db_construct_qpa ($qpa,
- 'AND skills_data.skills_data_id = skills_data_idx.skills_data_id ') ;
- }
+ $qpa = db_construct_qpa ($qpa,
+ 'AND skills_data.skills_data_id = skills_data_idx.skills_data_id ') ;
$qpa = db_construct_qpa ($qpa,
'AND (skills_data.user_id=users.user_id) AND (skills_data.type=skills_data_types.type_id) ') ;
- if (count ($this->words)) {
- $qpa = db_construct_qpa ($qpa,
- 'ORDER BY ts_rank(vectors, q) DESC, finish DESC') ;
- } else {
- $qpa = db_construct_qpa ($qpa,
- 'ORDER BY finish DESC') ;
- }
+ $qpa = db_construct_qpa ($qpa,
+ 'ORDER BY ts_rank(vectors, q) DESC, finish DESC') ;
} else {
$qpa = db_construct_qpa ($qpa,
'SELECT * FROM skills_data, users, skills_data_types WHERE ((') ;
$qpa = db_construct_qpa () ;
if (forge_get_config('use_fti')) {
- if (count ($this->words)) {
- $words = $this->getFormattedWords();
-
- $qpa = db_construct_qpa ($qpa,
- 'SELECT project_task.project_task_id, project_task.percent_complete, ts_headline(project_task.summary, q) AS summary, project_task.start_date,project_task.end_date,users.firstname||$1||users.lastname AS realname, project_group_list.project_name, project_group_list.group_project_id FROM project_task, users, project_group_list, to_tsquery($2) AS q, project_task_idx WHERE project_task.created_by = users.user_id AND project_task.project_task_id = project_task_idx.project_task_id AND project_task.group_project_id = project_group_list.group_project_id AND project_group_list.group_id=$3 ',
- array (' ',
- $words,
- $this->groupId)) ;
- } else {
- $qpa = db_construct_qpa ($qpa,
- 'SELECT project_task.project_task_id, project_task.percent_complete, summary, project_task.start_date,project_task.end_date,users.firstname||$1||users.lastname AS realname, project_group_list.project_name, project_group_list.group_project_id FROM project_task, users, project_group_list WHERE project_task.created_by = users.user_id AND project_task.group_project_id = project_group_list.group_project_id AND project_group_list.group_id = $2 ',
- array (' ',
- $this->groupId)) ;
- }
+ $words = $this->getFTIwords();
+
+ $qpa = db_construct_qpa ($qpa,
+ 'SELECT project_task.project_task_id, project_task.percent_complete, ts_headline(project_task.summary, q) AS summary, project_task.start_date,project_task.end_date,users.firstname||$1||users.lastname AS realname, project_group_list.project_name, project_group_list.group_project_id FROM project_task, users, project_group_list, to_tsquery($2) AS q, project_task_idx WHERE project_task.created_by = users.user_id AND project_task.project_task_id = project_task_idx.project_task_id AND project_task.group_project_id = project_group_list.group_project_id AND project_group_list.group_id=$3 ',
+ array (' ',
+ $words,
+ $this->groupId)) ;
if ($this->sections != SEARCH__ALL_SECTIONS) {
$qpa = db_construct_qpa ($qpa,
'AND project_group_list.group_project_id = ANY ($1) ',
'AND project_group_list.is_public = 1 ') ;
}
if (count($this->phrases)) {
- if (count ($this->words)) {
- $qpa = db_construct_qpa ($qpa,
- 'AND (vectors @@ q AND (') ;
- } else {
- $qpa = db_construct_qpa ($qpa,
- 'AND ((') ;
- }
+ $qpa = db_construct_qpa ($qpa,
+ 'AND (vectors @@ q AND (') ;
$qpa = $this->addMatchCondition($qpa, 'summary');
$qpa = db_construct_qpa ($qpa,
') OR (') ;
$qpa = db_construct_qpa ($qpa,
')) ') ;
}
- if (count ($this->words)) {
- $qpa = db_construct_qpa ($qpa,
- 'ORDER BY project_group_list.project_name, ts_rank(vectors, q) DESC, project_task.project_task_id') ;
- } else {
- $qpa = db_construct_qpa ($qpa,
- 'ORDER BY project_group_list.project_name, project_task.project_task_id') ;
- }
+ $qpa = db_construct_qpa ($qpa,
+ 'ORDER BY project_group_list.project_name, ts_rank(vectors, q) DESC, project_task.project_task_id') ;
} else {
$qpa = db_construct_qpa ($qpa,
'SELECT project_task.project_task_id, project_task.summary, project_task.percent_complete, project_task.start_date, project_task.end_date, users.firstname||$1||users.lastname AS realname, project_group_list.project_name, project_group_list.group_project_id FROM project_task, users, project_group_list WHERE project_task.created_by = users.user_id AND project_task.group_project_id = project_group_list.group_project_id AND project_group_list.group_id = $2 ',
$qpa = db_construct_qpa () ;
if (forge_get_config('use_fti')) {
- if (count ($this->words)) {
- $qpa = db_construct_qpa ($qpa,
- 'SELECT DISTINCT x.* FROM (SELECT artifact.artifact_id, artifact.group_artifact_id, artifact.summary, artifact.open_date, users.realname, artifact_group_list.name, (ts_rank(artifact_idx.vectors, q)+ts_rank(artifact_message_idx.vectors, q)) AS rank FROM artifact LEFT OUTER JOIN artifact_message USING (artifact_id), users, artifact_group_list, to_tsquery($1) q, artifact_idx, artifact_message_idx WHERE users.user_id = artifact.submitted_by AND artifact_idx.artifact_id = artifact.artifact_id AND artifact_message_idx.id = artifact_message.id AND artifact_message_idx.artifact_id = artifact_message_idx.artifact_id AND artifact_group_list.group_artifact_id = artifact.group_artifact_id AND artifact_group_list.group_id = $2 ',
- array ($this->getFormattedWords(),
- $this->groupId)) ;
- $tsmatch = "(artifact_idx.vectors @@ q OR artifact_message_idx.vectors @@ q)";
- $phraseOp = $this->getOperator();
- } else {
- $qpa = db_construct_qpa ($qpa,
- 'SELECT DISTINCT x.* FROM (SELECT artifact.artifact_id, artifact.group_artifact_id, artifact.summary, artifact.open_date, users.realname, artifact_group_list.name FROM artifact LEFT OUTER JOIN artifact_message USING (artifact_id), users, artifact_group_list 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)) ;
-
-
- $tsmatch = "";
- $orderBy = "";
- $phraseOp = "";
- }
+ $qpa = db_construct_qpa ($qpa,
+ 'SELECT DISTINCT x.* FROM (SELECT artifact.artifact_id, artifact.group_artifact_id, artifact.summary, artifact.open_date, users.realname, artifact_group_list.name, (ts_rank(artifact_idx.vectors, q)+ts_rank(artifact_message_idx.vectors, q)) AS rank FROM artifact LEFT OUTER JOIN artifact_message USING (artifact_id), users, artifact_group_list, to_tsquery($1) q, artifact_idx, artifact_message_idx WHERE users.user_id = artifact.submitted_by AND artifact_idx.artifact_id = artifact.artifact_id AND artifact_message_idx.id = artifact_message.id AND artifact_message_idx.artifact_id = artifact_message_idx.artifact_id AND artifact_group_list.group_artifact_id = artifact.group_artifact_id AND artifact_group_list.group_id = $2 ',
+ array ($this->getFTIwords(),
+ $this->groupId)) ;
+ $tsmatch = "(artifact_idx.vectors @@ q OR artifact_message_idx.vectors @@ q)";
+ $phraseOp = $this->getOperator();
if (count($this->phrases)) {
$qpa = db_construct_qpa ($qpa,
}
$qpa = db_construct_qpa ($qpa,
') x') ;
- if (count ($this->words)) {
- $qpa = db_construct_qpa ($qpa,
- 'ORDER BY rank DESC') ;
- }
+ $qpa = db_construct_qpa ($qpa,
+ 'ORDER BY rank DESC') ;
} else {
$qpa = db_construct_qpa ($qpa,
'SELECT DISTINCT artifact.artifact_id, artifact.group_artifact_id, artifact.summary, artifact.open_date, users.realname, artifact_group_list.name FROM artifact LEFT OUTER JOIN artifact_message USING (artifact_id), users, artifact_group_list 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 ',
if (empty($text)) {
return ' ';
}
- $regexp = implode($this->searchQuery->getWords(), '|');
+ $words = $this->searchQuery->getWords();
+ foreach ($this->searchQuery->getPhrases() as $p) {
+ foreach (explode(' ',$p) as $w) {
+ $words[] = $w;
+ }
+ }
+ $regexp = implode('|',$words);
return preg_replace('/('.str_replace('/', '\/', $regexp).')/i','<span class="selected">\1</span>', $text);
}
function testProjectSearch()
{
$this->init();
+ $this->createProject('projectb');
+
$this->open(ROOT) ;
$this->waitForPageToLoad("30000");
$this->type("//input[@name='words']", "XXXXXXXXXXXXXXXXXXXXXXXXXX");
$this->click("//input[@name='Search']");
$this->waitForPageToLoad("30000");
$this->assertTrue($this->isTextPresent("No matches found for"));
+
+ $this->open(ROOT) ;
+ $this->waitForPageToLoad("30000");
$this->type("//input[@name='words']", "projecta");
$this->click("//input[@name='Search']");
$this->waitForPageToLoad("30000");
$this->assertFalse($this->isTextPresent("No matches found for"));
+ $this->assertTrue($this->isTextPresent("public description for ProjectA"));
+ $this->assertFalse($this->isTextPresent("public description for projectb"));
+
+ $this->open(ROOT) ;
+ $this->waitForPageToLoad("30000");
+ $this->type("//input[@name='words']", "description public projecta");
+ $this->click("//input[@name='Search']");
+ $this->waitForPageToLoad("30000");
+ $this->assertFalse($this->isTextPresent("No matches found for"));
+ $this->assertTrue($this->isTextPresent("public description for ProjectA"));
+ $this->assertFalse($this->isTextPresent("public description for projectb"));
+
+ $this->open(ROOT) ;
+ $this->waitForPageToLoad("30000");
+ $this->type("//input[@name='words']", "description 'public projecta'");
+ $this->click("//input[@name='Search']");
+ $this->waitForPageToLoad("30000");
+ $this->assertTrue($this->isTextPresent("No matches found for"));
+ $this->assertFalse($this->isTextPresent("public description for ProjectA"));
+ $this->assertFalse($this->isTextPresent("public description for projectb"));
+
+ $this->open(ROOT) ;
+ $this->waitForPageToLoad("30000");
+ $this->type("//input[@name='words']", "description public");
+ $this->click("//input[@name='Search']");
+ $this->waitForPageToLoad("30000");
+ $this->assertFalse($this->isTextPresent("No matches found for"));
+ $this->assertTrue($this->isTextPresent("public description for ProjectA"));
+ $this->assertTrue($this->isTextPresent("public description for projectb"));
+
+ $this->open(ROOT) ;
+ $this->waitForPageToLoad("30000");
+ $this->type("//input[@name='words']", "'description public'");
+ $this->click("//input[@name='Search']");
+ $this->waitForPageToLoad("30000");
+ $this->assertTrue($this->isTextPresent("No matches found for"));
+ $this->assertFalse($this->isTextPresent("public description for ProjectA"));
+ $this->assertFalse($this->isTextPresent("public description for projectb"));
+
+ $this->open(ROOT) ;
+ $this->waitForPageToLoad("30000");
+ $this->type("//input[@name='words']", "'public description'");
+ $this->click("//input[@name='Search']");
+ $this->waitForPageToLoad("30000");
+ $this->assertFalse($this->isTextPresent("No matches found for"));
+ $this->assertTrue($this->isTextPresent("public description for ProjectA"));
+ $this->assertTrue($this->isTextPresent("public description for projectb"));
+ }
+
+ function testPeopleSearch()
+ {
+ $this->switchUser('admin');
+ $this->createUser('ratatouille');
+ $this->createUser('tartiflette');
+
+ $this->open(ROOT) ;
+ $this->waitForPageToLoad("30000");
+ $this->select("type_of_search", "label=People");
+ $this->type("//input[@name='words']", "tartempion");
+ $this->click("//input[@name='Search']");
+ $this->waitForPageToLoad("30000");
+ $this->assertTrue($this->isTextPresent("No matches found for"));
+ $this->assertFalse($this->isTextPresent("ratatouille Lastname"));
+ $this->assertFalse($this->isTextPresent("tartiflette Lastname"));
+
+ $this->open(ROOT) ;
+ $this->waitForPageToLoad("30000");
+ $this->select("type_of_search", "label=People");
+ $this->type("//input[@name='words']", "ratatouille");
+ $this->click("//input[@name='Search']");
+ $this->waitForPageToLoad("30000");
+ $this->assertFalse($this->isTextPresent("No matches found for"));
+ $this->assertTrue($this->isTextPresent("ratatouille Lastname"));
+ $this->assertFalse($this->isTextPresent("tartiflette Lastname"));
+
+ $this->open(ROOT) ;
+ $this->waitForPageToLoad("30000");
+ $this->select("type_of_search", "label=People");
+ $this->type("//input[@name='words']", "lastname ratatouille");
+ $this->click("//input[@name='Search']");
+ $this->waitForPageToLoad("30000");
+ $this->assertFalse($this->isTextPresent("No matches found for"));
+ $this->assertTrue($this->isTextPresent("ratatouille Lastname"));
+ $this->assertFalse($this->isTextPresent("tartiflette Lastname"));
+
+ $this->open(ROOT) ;
+ $this->waitForPageToLoad("30000");
+ $this->select("type_of_search", "label=People");
+ $this->type("//input[@name='words']", "Lastname");
+ $this->click("//input[@name='Search']");
+ $this->waitForPageToLoad("30000");
+ $this->assertFalse($this->isTextPresent("No matches found for"));
+ $this->assertTrue($this->isTextPresent("ratatouille Lastname"));
+ $this->assertTrue($this->isTextPresent("tartiflette Lastname"));
+
}
}
projects / fusionforge / fusionforge.git / commitdiff