History: MySQL4searchHack

Despite the advertised ability, I was unable to get working searches
on quoted phrases or booleans using MySQL 4.0.15.

Unless I'm missing something, the TikiWiki.org search engine doesn't work
as expected either. Quoted strings just act like simple ORs and boolean +/-
prefixes cause lots of error messages. I had the same symptoms on my
1.8 implementation until I made the following changes in searchlib.

Basically, I just restore some of the 1.7 query syntax, then add
the MySQL 'IN BOOLEAN MODE' qualifier.

Granted, at this point I'm just poking and prodding at an alien technology. I realize this may break other database servers... but it's worked for me.

// MySQL 4.0 boolean search hack to _find function in lib/searchlib.php:
//
//**************** original - v1.8 release *******************************
function _find($h, $words = '', $offset = 0, $maxRecords = -1, $fulltext = false) {
$words = trim($words);
$sql = sprintf(
'SELECT %s AS name, LEFT(%s, 240) AS data, %s AS hits, %s AS lastModif, %s AS pageName',
$h'name', $h'data', $h'hits', $h'lastModif', $h'pageName');

$id = $h'id';
for ($i = 0; $i < count($id); ++$i)
$sql .= ',' . $id$i . ' AS id' . ($i + 1);
if (count($id) < 2)
$sql .= ',1 AS id2';

$sql2 = ' FROM ' . $h'from' . ' WHERE 1';
$search_fields = array($h'name');
if ($h'data' && $h'name' != $h'data')
array_push($search_fields, $h'data');
$orderby = (isset($h'orderby') ? $h'orderby' : $h'hits');
if ($fulltext) {
if 'search'" class="wiki wikinew text-danger tips count">$h'search'
if (!preg_match('/\./', $h'search'0))
$search_fields = array_merge($search_fields, $h'search');
$qwords = $this->db->quote($words);
$sqlft = 'MATCH(' . join(',', $search_fields) . ') AGAINST (' . $qwords . ')';
$sql2 .= ' AND ' . $sqlft . ' >= 0';
$sql .= ', ' . $sqlft . ' AS relevance';
$orderby = 'relevance desc, ' . $orderby;

} else if ($words) {
$sql .= ', -1 AS relevance';
$vwords = split(' ',$words);
foreach ($vwords as $aword) {
//$aword = $this->db->quote('[[:<:]]' . strtoupper($aword) . '[[:>:]]');
$aword = $this->db->quote('.*'.strtoupper($aword).'.*');
$sql2 .= ' AND (';
for ($i = 0; $i < count($search_fields); ++$i) {
if ($i)
$sql2 .= ' OR ';
$sql2 .= 'UPPER(' . $search_fields$i . ') REGEXP ' . $aword;
}
$sql2 .= ')';
}
} else {
$sql .= ', -1 AS relevance';
}

$cant = $this->getOne('SELECT COUNT(*)' . $sql2);

if (!$cant) {
return array('data' => array(), 'cant' => 0);
}
$sql .= $sql2 . ' ORDER BY ' . $orderby . ' DESC LIMIT ' . $offset . ',' . $maxRecords;
$result = $this->query($sql);
$ret = Array();
while ($res = $result->fetchRow(DB_FETCHMODE_ASSOC)) {
$href = sprintf(urldecode($h'href'), $res'id1', $res'id2');
$ret[] = array(
'pageName' => $res"pageName",
'data' => $res"data",
'hits' => $res"hits",
'lastModif' => $res"lastModif",
'href' => $href,
'relevance' => round($res"relevance", 3),
);
}
return array('data' => $ret, 'cant' => $cant);
}
//*************************************************************

//***** revision by kevinB for MySQL 4.0.15 fulltext and boolean ************
function _find($h, $words = '', $offset = 0, $maxRecords = -1, $fulltext = false) {
$words = trim($words);
$sql = sprintf(
'SELECT %s AS name, LEFT(%s, 240) AS data, %s AS hits, %s AS lastModif, %s AS pageName',
$h'name', $h'data', $h'hits', $h'lastModif', $h'pageName');

$id = $h'id';
for ($i = 0; $i < count($id); ++$i)
$sql .= ',' . $id$i . ' AS id' . ($i + 1);
if (count($id) < 2)
$sql .= ',1 AS id2';

$sql2 = ' FROM ' . $h'from' . ' WHERE ';
$sql2 .= 'filter'" class="wiki wikinew text-danger tips isset">$h'filter'? $h'filter' : '1';

$orderby = (isset($h'orderby') ? $h'orderby' : $h'hits');

if ($fulltext) {
$qwords = $this->db->quote($words);
$sqlft = 'MATCH(' . join(',', $h'search'). ')
AGAINST (' . $qwords . ' IN BOOLEAN MODE)';

$sql2 .= ' AND ' . $sqlft ;
$sql .= ', ' . $sqlft . ' AS relevance';
$orderby = 'relevance desc, ' . $orderby;

} else if ($words) {
$sql .= ', -1 AS relevance';
$vwords = split(' ',$words);
foreach ($vwords as $aword) {
//$aword = $this->db->quote('[[:<:]]' . strtoupper($aword) . '[[:>:]]');
$aword = $this->db->quote('.*'.strtoupper($aword).'.*');
$sql2 .= ' AND (';
for ($i = 0; $i < count($search_fields); ++$i) {
if ($i)
$sql2 .= ' OR ';
$sql2 .= 'UPPER(' . $search_fields$i . ') REGEXP ' . $aword;
}
$sql2 .= ')';
}
} else {
$sql .= ', -1 AS relevance';
}

$cant = $this->getOne('SELECT COUNT(*)' . $sql2);

//if (!$cant) { // no result
// if ($fulltext && $words) // try a simple search
// return $this->_find($h, $words, $offset, $maxRecords, false);
// else
// return array(
// 'data' => array(),
// 'cant' => 0
// );
//}

$sql .= $sql2 . ' ORDER BY ' . $orderby . ' DESC LIMIT ' . $offset . ',' . $maxRecords;

$result = $this->query($sql);
$ret = array();

while ($res = $result->fetchRow(DB_FETCHMODE_ASSOC)) {
$href = sprintf(urldecode($h'href'), $res'id1', $res'id2');

$ret[] = array(
'pageName' => $res"pageName",
'data' => $res"data",
'hits' => $res"hits",
'lastModif' => $res"lastModif",
'href' => $href,
'relevance' => round($res"relevance", 3),
);
}

return array('data' => $ret, 'cant' => $cant);
}

//*********************************************************

//**************** Unix diff file contents *********************
5a6
>
12,15c13,15
< $sql2 = ' FROM ' . $h'from' . ' WHERE 1';
< $search_fields = array($h'name');
< if ($h'data' && $h'name' != $h'data')
< array_push($search_fields, $h'data');

> $sql2 = ' FROM ' . $h'from' . ' WHERE ';
> $sql2 .= 'filter'" class="wiki wikinew text-danger tips isset">$h'filter'? $h'filter' : '1';
>
16a17
>
18,20d18
< if 'search'" class="wiki wikinew text-danger tips count">$h'search'
< if (!preg_match('/\./', $h'search'0))
< $search_fields = array_merge($search_fields, $h'search');
22,23c20,21
< $sqlft = 'MATCH(' . join(',', $search_fields) . ') AGAINST (' . $qwords . ')';
< $sql2 .= ' AND ' . $sqlft . ' >= 0';

> $sqlft = 'MATCH(' . join(',', $h'search'). ')
AGAINST (' . $qwords . ' IN BOOLEAN MODE)';
> $sql2 .= ' AND ' . $sqlft ;
44a43
>
47,49c46,56
< if (!$cant) {
< return array('data' => array(), 'cant' => 0);
< }

> //if (!$cant) { // no result
> // if ($fulltext && $words) // try a simple search
> // return $this->_find($h, $words, $offset, $maxRecords, false);
> // else
> // return array(
> // 'data' => array(),
> // 'cant' => 0
> // );
> //}
>
>
50a58
>
52a61,62
>
>
54a65
>
63a75
>
//*********************************************************