function createTextQuery($searchFields, $additionalFields)
{
SpotTiming::start(__CLASS__ . '::' . __FUNCTION__);
/*
* Initialize some basic values which are used as return values to
* make sure always return a valid set
*/
$filterValueSql = array('(idx_fts_spots.rowid = s.rowid)');
$additionalTables = array('idx_fts_spots');
$matchList = array();
/*
* sqlite can only use one WHERE clause for all textstring matches,
* if you exceed this it throws an unrelated error and refuses the query
* so we have to collapse all textqueries into one query
*/
foreach ($searchFields as $searchItem) {
$searchValue = trim($searchItem['value']);
/*
* The caller usually provides an expiciet table.fieldname
* for the select, but sqlite doesn't recgnize this in its
* MATCH statement so we remove it and hope there is no
* ambiguity
*/
$tmpField = explode('.', $searchItem['fieldname']);
$field = $tmpField[1];
$matchList[] = $field . ':' . $this->_db->safe($searchValue);
}
# foreach
# add one WHERE MATCH conditions with all conditions
$filterValueSql[] = " (idx_fts_spots MATCH '" . implode(' ', $matchList) . "') ";
SpotTiming::stop(__CLASS__ . '::' . __FUNCTION__, array($filterValueSql, $additionalTables));
return array('filterValueSql' => $filterValueSql, 'additionalTables' => $additionalTables, 'additionalFields' => $additionalFields, 'sortFields' => array());
}
private function addToSpotStateList($list, $messageId, $ourUserId)
{
SpotTiming::start(__FUNCTION__);
$stamp = time();
$this->_conn->modify("INSERT INTO spotstatelist (messageid, ouruserid, " . $list . ") VALUES ('%s', %d, %d) ON DUPLICATE KEY UPDATE " . $list . " = %d", array($messageId, (int) $ourUserId, $stamp, $stamp));
SpotTiming::stop(__FUNCTION__, array($list, $messageId, $ourUserId, $stamp));
}
function createTextQuery($searchFields)
{
SpotTiming::start(__FUNCTION__);
# Initialiseer een aantal arrays welke we terug moeten geven aan
# aanroeper
$filterValueSql = array('(idx_fts_spots.rowid = s.rowid)');
$additionalTables = array('idx_fts_spots');
$matchList = array();
# sqlite kan maar 1 where clausule gebruiken voor alle textstrng
# matches (anders krijg je een vage error), dus we plakken hier
# gewoon alle textmatches samen
foreach ($searchFields as $searchItem) {
$searchValue = trim($searchItem['value']);
# omdat we de fieldname in tabel.fieldname krijgen, maar sqlite dat niet
# snapt, halen we de tabelnaam weg
$tmpField = explode('.', $searchItem['fieldname']);
$field = $tmpField[1];
$matchList[] = $field . ':' . $this->safe($searchValue);
}
# foreach
# en voeg nu 1 WHERE filter toe met alle condities hierin
$filterValueSql[] = " (idx_fts_spots MATCH '" . implode(' ', $matchList) . "') ";
SpotTiming::stop(__FUNCTION__, array($filterValueSql, $additionalTables));
return array('filterValueSql' => $filterValueSql, 'additionalTables' => $additionalTables, 'additionalFields' => array(), 'sortFields' => array());
}
function getUserListForDisplay()
{
SpotTiming::start(__CLASS__ . '::' . __FUNCTION__);
$tmpResult = $this->_conn->arrayQuery("SELECT u.id AS userid,\n\t\t\t\t\t\t\t\tu.username AS username,\n\t\t\t\t\t\t\t\tMAX(u.firstname) AS firstname,\n\t\t\t\t\t\t\t\tMAX(u.lastname) AS lastname,\n\t\t\t\t\t\t\t\tMAX(u.mail) AS mail,\n\t\t\t\t\t\t\t\tMAX(u.lastlogin) AS lastlogin,\n\t\t\t\t\t\t\t\tCOALESCE(MAX(ss.lasthit), MAX(u.lastvisit)) AS lastvisit,\n\t\t\t\t\t\t\t\tMAX(ipaddr) AS lastipaddr\n\t\t\t\t\t\t\tFROM users AS u\n\t\t\t\t\t\t\tLEFT JOIN (SELECT userid, lasthit, ipaddr, devicetype FROM sessions WHERE sessions.userid = userid ORDER BY lasthit) AS ss ON (u.id = ss.userid)\n\t\t\t\t\t\t\tWHERE (deleted = :isdeleted)\n\t\t\t\t\t\t\tGROUP BY u.id, u.username", array(':isdeleted' => array(false, PDO::PARAM_BOOL)));
SpotTiming::stop(__CLASS__ . '::' . __FUNCTION__, array());
return $tmpResult;
}
public function addToSpotStateList($list, $messageId, $ourUserId)
{
SpotTiming::start(__CLASS__ . '::' . __FUNCTION__);
$stamp = time();
$this->_conn->modify("INSERT INTO spotstatelist (messageid, ouruserid, " . $list . ")\n\t\t VALUES (:messageid, :ouruserid, :stamp1) ON DUPLICATE KEY UPDATE " . $list . " = :stamp2", array(':messageid' => array($messageId, PDO::PARAM_STR), ':ouruserid' => array($ourUserId, PDO::PARAM_INT), ':stamp1' => array($stamp, PDO::PARAM_INT), ':stamp2' => array($stamp, PDO::PARAM_INT)));
SpotTiming::stop(__CLASS__ . '::' . __FUNCTION__, array($list, $messageId, $ourUserId, $stamp));
}
function fetchFullSpot($msgId, $ourUserId)
{
SpotTiming::start(__CLASS__ . '::' . __FUNCTION__);
/*
* First try the database for the spot, because if it
* is already cached in the database we don't need
* anything else
*/
$fullSpot = $this->_spotDao->getFullSpot($msgId, $ourUserId);
if (empty($fullSpot)) {
/*
* When we retrieve a fullspot entry but there is no spot entry the join in our DB query
* causes us to never get the spot, hence we throw this exception
*/
$spotHeader = $this->_spotDao->getSpotHeader($msgId);
if (empty($spotHeader)) {
throw new Exception("Spot is not in our Spotweb database");
}
# if
/*
* Retrieve a full loaded spot from the NNTP server
*/
$newFullSpot = $this->_nntpSpotReading->readFullSpot($msgId);
if (!empty($newFullSpot)) {
$this->_spotDao->addFullSpots(array($newFullSpot));
} else {
SpotTiming::stop(__CLASS__ . '::' . __FUNCTION__, array($msgId, $ourUserId, $fullSpot));
return false;
}
// else
/*
* If the current spotterid is empty, we probably now have a spotterid because
* we have the fullspot.
*
* We now update the 'basic' spot information, like the spotterid but also the
* title. This is necessary because the XML contains better encoding.
*
* For example take the title from spot bdZZdJ3gPxTAmSE@spot.net.
*
* We cannot use all information from the XML because because some information just
* isn't present in the XML file
*/
$this->_spotDao->updateSpotInfoFromFull($newFullSpot);
/*
* We ask our DB to retrieve the fullspot again, this ensures
* us all information is present and in always the same format
*/
$fullSpot = $this->_spotDao->getFullSpot($msgId, $ourUserId);
}
# if
/*
* We always have to parse the full spot because the database
* does not contain all information
*/
$spotParser = new Services_Format_Parsing();
$parsedXml = $spotParser->parseFull($fullSpot['fullxml']);
$fullSpot = array_merge($parsedXml, $fullSpot);
SpotTiming::stop(__CLASS__ . '::' . __FUNCTION__, array($msgId, $ourUserId, $fullSpot));
return $fullSpot;
}
function createTextQuery($searchFields)
{
SpotTiming::start(__FUNCTION__);
# Initialiseer een aantal arrays welke we terug moeten geven aan
# aanroeper
$filterValueSql = array();
$additionalFields = array();
$sortFields = array();
foreach ($searchFields as $searchItem) {
$searchValue = trim($searchItem['value']);
$field = $searchItem['fieldname'];
# We zouden in theorie meerdere van deze textsearches kunnen hebben, dan
# sorteren we ze in de volgorde waarop ze binnenkwamen
$tmpSortCounter = count($additionalFields);
# prepareer de to_tsvector en de to_tsquery strings
$ts_vector = "to_tsvector('Dutch', " . $field . ")";
$ts_query = "to_tsquery('" . $this->safe(strtolower($searchValue)) . "')";
$filterValueSql[] = " " . $ts_vector . " @@ " . $ts_query;
$additionalFields[] = " ts_rank(" . $ts_vector . ", " . $ts_query . ") AS searchrelevancy" . $tmpSortCounter;
$sortFields[] = array('field' => 'searchrelevancy' . $tmpSortCounter, 'direction' => 'DESC', 'autoadded' => true, 'friendlyname' => null);
}
# foreach
SpotTiming::stop(__FUNCTION__, array($filterValueSql, $additionalFields, $sortFields));
return array('filterValueSql' => $filterValueSql, 'additionalTables' => array(), 'additionalFields' => $additionalFields, 'sortFields' => $sortFields);
}
function createTextQuery($searchFields, $additionalFields)
{
SpotTiming::start(__FUNCTION__);
/*
* Initialize some basic values which are used as return values to
* make sure always return a valid set
*/
$filterValueSql = array();
$sortFields = array();
foreach ($searchFields as $searchItem) {
$searchValue = trim($searchItem['value']);
$field = $searchItem['fieldname'];
/*
* if we get multiple textsearches, we sort them per order
* in the system
*/
$tmpSortCounter = count($additionalFields);
# Prepare the to_tsvector and to_tsquery strings
$ts_vector = "to_tsvector('Dutch', " . $field . ")";
$ts_query = "plainto_tsquery('Dutch', '" . $this->_db->safe(strtolower($searchValue)) . "')";
$filterValueSql[] = " " . $ts_vector . " @@ " . $ts_query;
$additionalFields[] = " ts_rank(" . $ts_vector . ", " . $ts_query . ") AS searchrelevancy" . $tmpSortCounter;
$sortFields[] = array('field' => 'searchrelevancy' . $tmpSortCounter, 'direction' => 'DESC', 'autoadded' => true, 'friendlyname' => null);
}
# foreach
SpotTiming::stop(__FUNCTION__, array($filterValueSql, $additionalFields, $sortFields));
return array('filterValueSql' => $filterValueSql, 'additionalTables' => array(), 'additionalFields' => $additionalFields, 'sortFields' => $sortFields);
}
private function sendRequest($method, $args)
{
SpotTiming::start(__CLASS__ . '::' . __FUNCTION__);
$reqarr = array('version' => '1.1', 'method' => $method, 'params' => $args);
$content = json_encode($reqarr);
/*
* Actually perform the HTTP POST
*/
$svcProvHttp = new Services_Providers_Http(null);
$svcProvHttp->setUsername($this->_username);
$svcProvHttp->setPassword($this->_password);
$svcProvHttp->setMethod('POST');
$svcProvHttp->setContentType('application/json');
$svcProvHttp->setRawPostData($content);
$output = $svcProvHttp->perform($this->_url, null);
if ($output['successful'] === false) {
$errorStr = "ERROR: Could not decode json-data for NZBGet method '" . $method . "', " . $output['errorstr'];
error_log($errorStr);
throw new Exception($errorStr);
}
# if
$response = json_decode($output['data'], true);
if (is_array($response) && isset($response['error']) && isset($response['error']['code'])) {
error_log("NZBGet RPC: Method '" . $method . "', " . $response['error']['message'] . " (" . $response['error']['code'] . ")");
throw new Exception("NZBGet RPC: Method '" . $method . "', " . $response['error']['message'] . " (" . $response['error']['code'] . ")");
} elseif (is_array($response) && isset($response['result'])) {
$response = $response['result'];
}
SpotTiming::stop(__CLASS__ . '::' . __FUNCTION__, array($method, $args));
return $response;
}
function render()
{
SpotTiming::start(__FUNCTION__);
# Controleer de users' rechten
$this->_spotSec->fatalPermCheck(SpotSecurity::spotsec_view_spots_index, '');
# als een zoekopdracht is meegegevne, moeten er ook rechten zijn om te mogen zoeken
if (!empty($this->_params['search'])) {
$this->_spotSec->fatalPermCheck(SpotSecurity::spotsec_perform_search, '');
}
# if
$spotsOverview = new SpotsOverview($this->_db, $this->_settings);
# Zet the query parameters om naar een lijst met filters, velden,
# en sorteringen etc
$spotUserSystem = new SpotUserSystem($this->_db, $this->_settings);
$parsedSearch = $spotsOverview->filterToQuery($this->_params['search'], array('field' => $this->_params['sortby'], 'direction' => $this->_params['sortdir']), $this->_currentSession, $spotUserSystem->getIndexFilter($this->_currentSession['user']['userid']));
# Haal de offset uit de URL en zet deze als startid voor de volgende zoektocht
# Als de offset niet in de url staat, zet de waarde als 0, het is de eerste keer
# dat de index pagina wordt aangeroepen
$pageNr = $this->_params['pagenr'];
$nextPage = $pageNr + 1;
if ($nextPage == 1) {
$prevPage = -1;
} else {
$prevPage = max($pageNr - 1, 0);
}
# else
# afhankelijk van wat er gekozen is, voer het uit
if (isset($parsedSearch['filterValueList'][0]['fieldname']) && $parsedSearch['filterValueList'][0]['fieldname'] == "Watch") {
# Controleer de users' rechten
$this->_spotSec->fatalPermCheck(SpotSecurity::spotsec_keep_own_watchlist, '');
switch ($this->_action) {
case 'remove':
$this->_db->removeFromSpotStateList(SpotDb::spotstate_Watch, $this->_params['messageid'], $this->_currentSession['user']['userid']);
$spotsNotifications = new SpotNotifications($this->_db, $this->_settings, $this->_currentSession);
$spotsNotifications->sendWatchlistHandled($this->_action, $this->_params['messageid']);
break;
case 'add':
$this->_db->addToSpotStateList(SpotDb::spotstate_Watch, $this->_params['messageid'], $this->_currentSession['user']['userid'], '');
$spotsNotifications = new SpotNotifications($this->_db, $this->_settings, $this->_currentSession);
$spotsNotifications->sendWatchlistHandled($this->_action, $this->_params['messageid']);
break;
default:
}
# switch
}
# if
# laad de spots
$spotsTmp = $spotsOverview->loadSpots($this->_currentSession['user']['userid'], $pageNr, $this->_currentSession['user']['prefs']['perpage'], $parsedSearch);
# als er geen volgende pagina is, ook niet tonen
if (!$spotsTmp['hasmore']) {
$nextPage = -1;
}
# if
# zet de page title
$this->_pageTitle = "overzicht";
#- display stuff -#
$this->template('spots', array('spots' => $spotsTmp['list'], 'quicklinks' => $this->_settings->get('quicklinks'), 'filters' => $this->_db->getFilterList($this->_currentSession['user']['userid'], 'filter'), 'nextPage' => $nextPage, 'prevPage' => $prevPage, 'parsedsearch' => $parsedSearch, 'data' => $this->_params['data']));
SpotTiming::stop(__FUNCTION__);
}
function fetchSpotList($ourUserId, $start, $limit, $parsedSearch)
{
SpotTiming::start(__CLASS__ . '::' . __FUNCTION__);
/*
* Actually fetch the spots from the database
*/
$spotResults = $this->_spotDao->getSpots($ourUserId, $start, $limit, $parsedSearch);
SpotTiming::stop(__CLASS__ . '::' . __FUNCTION__, array());
return $spotResults;
}
private function getAvatarImage($md5, $size, $default, $rating)
{
SpotTiming::start(__CLASS__ . '::' . __FUNCTION__);
$url = 'http://www.gravatar.com/avatar/' . $md5 . "?s=" . $size . "&d=" . $default . "&r=" . $rating;
list($return_code, $data) = $this->_serviceHttp->performCachedGet($url, true, 60 * 60);
$dimensions = $this->_svc_ImageUtil->getImageDimensions($data);
$data = array('content' => $data);
$data['metadata'] = $dimensions;
$data['ttl'] = 24 * 7 * 60 * 60;
SpotTiming::stop(__CLASS__ . '::' . __FUNCTION__, array($md5, $size, $default, $rating));
return $data;
}
function createTextQuery($field, $searchValue) {
SpotTiming::start(__FUNCTION__);
//
// FIXME
// Sorteeren op rank, zie http://www.postgresql.org/docs/8.3/static/textsearch-controls.html
//
$queryPart = " to_tsvector('Dutch', " . $field . ") @@ '" . $this->safe(strtolower($searchValue)) . "' ";
SpotTiming::stop(__FUNCTION__, array($field,$searchValue));
return array('filter' => $queryPart,
'sortable' => false);
} # createTextQuery()
function fetchNzb($fullSpot)
{
SpotTiming::start(__CLASS__ . '::' . __FUNCTION__);
/*
* Retrieve the NZB from the cache
*/
$nzb = $this->_cacheDao->getCachedNzb($fullSpot['messageid']);
if (!empty($nzb)) {
/*
* NZB file is alread in the cache, update the cache timestamp
*/
$nzb = $nzb['content'];
$this->_cacheDao->updateNzbCacheStamp($fullSpot['messageid']);
} else {
SpotTiming::start(__FUNCTION__ . '::cacheMiss');
/*
* File is not in the cache yet, retrieve it from the appropriate store, and
* store it in the cache
*/
$nzb = null;
// Search for alternate download urls
$alternateDownload = new Services_Providers_HttpNzb($fullSpot, $this->_cacheDao);
// Only return an alternate if there is one.
if ($alternateDownload->hasNzb()) {
$nzb = $alternateDownload->getNzb();
} else {
$nzb = $this->_nntpSpotReading->readBinary($fullSpot['nzb'], true);
}
# else
/*
* If the returned NZB is empty, lets create a dummy (invalid) NZB file
* we can store. This way, we prevent hitting the usenet or HTTP server
* over and over again for invalid NZB files.
*/
$mustExpire = false;
if (empty($nzb)) {
$nzb = '<xml><error>Invalid NZB file, unable to retrieve correct NZB file</error></xml>';
$mustExpire = true;
}
# if
if (!$this->_cacheDao->saveNzbCache($fullSpot['messageid'], $nzb, $mustExpire)) {
error_log('Spotweb: Unable to save NZB file to cache, is cache directory writable?');
}
# if
SpotTiming::stop(__CLASS__ . '::' . __FUNCTION__ . '::cacheMiss');
}
# else
SpotTiming::stop(__CLASS__ . '::' . __FUNCTION__, array($fullSpot));
return $nzb;
}
function deleteSpotsRetention($retention)
{
SpotTiming::start(__CLASS__ . '::' . __FUNCTION__);
$retention = $retention * 24 * 60 * 60;
// omzetten in seconden
$this->_conn->modify("DELETE FROM spots WHERE spots.stamp < :time", array(':time' => array(time() - $retention, PDO::PARAM_INT)));
$this->_conn->modify("DELETE FROM spotsfull WHERE NOT EXISTS\n\t\t\t\t\t\t\t(SELECT 1 FROM spots WHERE spots.messageid = spotsfull.messageid)");
$this->_conn->modify("DELETE FROM commentsfull WHERE NOT EXISTS\n\t\t\t\t\t\t\t(SELECT 1 FROM commentsxover WHERE commentsxover.messageid = commentsfull.messageid)");
$this->_conn->modify("DELETE FROM commentsxover WHERE NOT EXISTS\n\t\t\t\t\t\t\t(SELECT 1 FROM spots WHERE spots.messageid = commentsxover.nntpref)");
$this->_conn->modify("DELETE FROM reportsxover WHERE NOT EXISTS\n\t\t\t\t\t\t\t(SELECT 1 FROM spots WHERE spots.messageid = reportsxover.nntpref)");
$this->_conn->modify("DELETE FROM spotstatelist WHERE NOT EXISTS\n\t\t\t\t\t\t\t(SELECT 1 FROM spots WHERE spots.messageid = spotstatelist.messageid)");
$this->_conn->modify("DELETE FROM reportsposted WHERE NOT EXISTS\n\t\t\t\t\t\t\t (SELECT 1 FROM spots WHERE spots.messageid = reportsposted.inreplyto)");
SpotTiming::stop(__CLASS__ . '::' . __FUNCTION__, array($retention));
}
function template($tpl, $params = array())
{
SpotTiming::start(__FUNCTION__ . ':' . $tpl);
extract($params, EXTR_REFS);
$settings = $this->_settings;
$pagetitle = 'SpotWeb v. ' . SPOTWEB_VERSION . ' - ' . $this->_pageTitle;
# update the template helper variables
$this->_tplHelper->setParams($params);
# We maken een aantal variabelen / objecten standaard beschikbaar in de template.
$tplHelper = $this->_tplHelper;
$currentSession = $this->_currentSession;
$spotSec = $this->_currentSession['security'];
# stuur de expire headers
$this->sendExpireHeaders(true);
$this->sendContentTypeHeader();
# en we spelen de template af
require_once 'templates/' . $settings->get('tpl_name') . '/' . $tpl . '.inc.php';
SpotTiming::stop(__FUNCTION__ . ':' . $tpl, array($params));
}
function template($tpl, $params = array())
{
SpotTiming::start(__FUNCTION__ . ':notifications:' . $tpl);
extract($params, EXTR_REFS);
$settings = $this->_settings;
# We maken een aantal variabelen / objecten standaard beschikbaar in de template.
$currentSession = $this->_currentSession;
$spotSec = $this->_currentSession['security'];
# start output buffering
ob_start();
# en we spelen de template af
require 'templates/notifications/' . $tpl . '.inc.php';
# nu vraag de inhoud van de output buffer op
$notificationContent = ob_get_contents();
ob_end_clean();
# de eerste regel is het onderwerp, de tweede regel is een spatie,
# en de rest is daadwerkelijke buffer
$notificationArray = explode("\n", $notificationContent);
SpotTiming::stop(__CLASS__ . '::' . __FUNCTION__ . ':notifications:' . $tpl, array($params));
return array('title' => $notificationArray[0], 'body' => array_slice($notificationArray, 2));
}
public function filterToQuery($search, $sort, $currentSession, $indexFilter)
{
SpotTiming::start(__CLASS__ . '::' . __FUNCTION__);
$isUnfiltered = false;
$categoryList = array();
$categorySql = array();
$strongNotList = array();
$strongNotSql = array();
$filterValueList = array();
$filterValueSql = array();
$additionalFields = array();
$additionalTables = array();
$additionalJoins = array();
$sortFields = array();
# Take the easy way out of no filters have been given
if (empty($search)) {
return array('filter' => '', 'search' => array(), 'additionalFields' => array(), 'additionalTables' => array(), 'additionalJoins' => array(), 'categoryList' => array(), 'strongNotList' => array(), 'filterValueList' => array(), 'unfiltered' => false, 'sortFields' => array(array('field' => 'stamp', 'direction' => 'DESC', 'autoadded' => true, 'friendlyname' => null)));
}
# if
/*
* Process the parameters in $search, legacy parameters are converted
* to a common format by prepareFilterValues, this list is then
* converted to SQL
*/
$filterValueList = $this->prepareFilterValues($search);
list($filterValueSql, $additionalFields, $additionalTables, $additionalJoins, $sortFields) = $this->filterValuesToSql($filterValueList, $currentSession);
/*
* When asked to forget all category filters (and only search for a word/typefilter)
* we simply reset the filter by overwriting $search with $indexfilter
*/
if (isset($search['unfiltered']) && $search['unfiltered'] === 'true') {
$search = array_merge($search, $indexFilter);
$isUnfiltered = true;
}
# if
/*
* If a tree was given, convert it to subcategories etc.
* prepareCategorySelection() makes sure all categories eventually
* are in a common format
*/
if (!empty($search['tree'])) {
# explode the dynaList
$dynaList = explode(',', $search['tree']);
list($categoryList, $strongNotList) = $this->prepareCategorySelection($dynaList);
# and convert to SQL
$categorySql = $this->categoryListToSql($categoryList);
$strongNotSql = $this->strongNotListToSql($strongNotList);
}
# if
# Check for an explicit sorting convention
$sortFields = $this->prepareSortFields($sort, $sortFields);
$endFilter = array();
if (!empty($categorySql)) {
$endFilter[] = '(' . join(' OR ', $categorySql) . ') ';
}
# if
if (!empty($filterValueSql['AND'])) {
$endFilter[] = '(' . join(' AND ', $filterValueSql['AND']) . ') ';
}
# if
if (!empty($filterValueSql['OR'])) {
$endFilter[] = '(' . join(' OR ', $filterValueSql['OR']) . ') ';
}
# if
$endFilter[] = join(' AND ', $strongNotSql);
$endFilter = array_filter($endFilter);
SpotTiming::stop(__CLASS__ . '::' . __FUNCTION__, array(join(" AND ", $endFilter)));
return array('filter' => join(" AND ", $endFilter), 'categoryList' => $categoryList, 'unfiltered' => $isUnfiltered, 'strongNotList' => $strongNotList, 'filterValueList' => $filterValueList, 'additionalFields' => $additionalFields, 'additionalTables' => $additionalTables, 'additionalJoins' => $additionalJoins, 'sortFields' => $sortFields);
}
function template($tpl, $params = array())
{
SpotTiming::start(__FUNCTION__ . ':' . $tpl);
extract($params, EXTR_REFS);
$settings = $this->_settings;
$pagetitle = $this->_pageTitle;
# update the template helper variables
$this->_tplHelper->setParams($params);
# Expose some variables to the template script in its local scope
$tplHelper = $this->_tplHelper;
$currentSession = $this->_currentSession;
$spotSec = $this->_currentSession['security'];
# send any expire headers
$this->sendExpireHeaders(true);
$this->sendContentTypeHeader('html');
# and include the template
foreach ($this->_templatePaths as $tplPath) {
if (file_exists($tplPath . $tpl . '.inc.php')) {
require_once $tplPath . $tpl . '.inc.php';
break;
}
# if
}
# foreach
SpotTiming::stop(__CLASS__ . '::' . __FUNCTION__ . ':' . $tpl, array($params));
}
function getMaxMessageId($headers)
{
SpotTiming::start(__FUNCTION__);
if ($headers == 'headers') {
$msgIds = $this->_conn->arrayQuery("SELECT messageid FROM spots ORDER BY id DESC LIMIT 5000");
} elseif ($headers == 'comments') {
$msgIds = $this->_conn->arrayQuery("SELECT messageid FROM commentsxover ORDER BY id DESC LIMIT 5000");
} elseif ($headers == 'reports') {
$msgIds = $this->_conn->arrayQuery("SELECT messageid FROM reportsxover ORDER BY id DESC LIMIT 5000");
} else {
throw new Exception("getMaxMessageId() header-type value is unknown");
}
# else
if ($msgIds == null) {
return array();
}
# if
$tempMsgIdList = array();
$msgIdCount = count($msgIds);
for ($i = 0; $i < $msgIdCount; $i++) {
$tempMsgIdList['<' . $msgIds[$i]['messageid'] . '>'] = 1;
}
# for
SpotTiming::stop(__FUNCTION__, array($headers));
return $tempMsgIdList;
}
}
}
if ($tplHelper->allowed(SpotSecurity::spotsec_keep_own_downloadlist, '') && $tplHelper->allowed(SpotSecurity::spotsec_keep_own_downloadlist, 'erasedls')) {
?>
<li><a href="<?php
echo $tplHelper->getPageUrl('erasedls');
?>
" onclick="eraseDownloads()" class="greyButton erasedownloads"><?php
echo _('Erase downloadhistory');
?>
</a></li>
<?php
}
if ($tplHelper->allowed(SpotSecurity::spotsec_keep_own_seenlist, '')) {
?>
<li><a href="<?php
echo $tplHelper->getPageUrl('markallasread');
?>
" onclick="markAsRead()" class="greyButton markasread"><?php
echo _('Mark everything as read');
?>
</a></li>
<?php
}
?>
</ul>
</div>
<?php
SpotTiming::stop('tpl:filters');
function updateCacheStamp($resourceid, $cachetype)
{
SpotTiming::start(__FUNCTION__);
$this->_db->updateCacheStamp($resourceid, $cachetype);
SpotTiming::stop(__FUNCTION__, array($resourceid, $cachetype));
}
function getCommentsFull($userId, $nntpRef)
{
SpotTiming::start(__CLASS__ . '::' . __FUNCTION__);
# eactually retrieve the comment
$commentList = $this->_conn->arrayQuery("SELECT c.messageid AS messageid, \n\t\t\t\t\t\t\t\t\t\t\t\t\t\t(f.messageid IS NOT NULL) AS havefull,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tf.fromhdr AS fromhdr, \n\t\t\t\t\t\t\t\t\t\t\t\t\t\tf.stamp AS stamp, \n\t\t\t\t\t\t\t\t\t\t\t\t\t\tf.usersignature AS \"user-signature\", \n\t\t\t\t\t\t\t\t\t\t\t\t\t\tf.userkey AS \"user-key\", \n\t\t\t\t\t\t\t\t\t\t\t\t\t\tf.spotterid AS spotterid, \n\t\t\t\t\t\t\t\t\t\t\t\t\t\tf.body AS body, \n\t\t\t\t\t\t\t\t\t\t\t\t\t\tf.verified AS verified,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tc.spotrating AS spotrating,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tc.moderated AS moderated,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tf.avatar as \"user-avatar\",\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tbl.idtype AS idtype\n\t\t\t\t\t\t\t\t\t\t\t\t\tFROM commentsfull f \n\t\t\t\t\t\t\t\t\t\t\t\t\tRIGHT JOIN commentsxover c on (f.messageid = c.messageid)\n\t\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN spotteridblacklist as bl ON ((bl.spotterid = f.spotterid) AND (bl.doubled = :doubled))\n\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE c.nntpref = :nntpref AND ((bl.spotterid IS NULL) OR (((bl.ouruserid = :ouruserid) OR (bl.ouruserid = -1)) AND (bl.idtype = 2)))\n\t\t\t\t\t\t\t\t\t\t\t\t\tORDER BY c.id", array(':doubled' => array(false, PDO::PARAM_BOOL), ':nntpref' => array($nntpRef, PDO::PARAM_STR), ':ouruserid' => array($userId, PDO::PARAM_INT)));
$commentListCount = count($commentList);
for ($i = 0; $i < $commentListCount; $i++) {
if ($commentList[$i]['havefull']) {
$commentList[$i]['user-key'] = unserialize($commentList[$i]['user-key']);
}
# if
}
# for
SpotTiming::stop(__CLASS__ . '::' . __FUNCTION__);
return $commentList;
}
SpotTiming::start('renderpage->case-default');
if (@$_SERVER['HTTP_X_PURPOSE'] == 'preview') {
$page = new SpotPage_getimage($db, $settings, $currentSession, array('messageid' => $req->getDef('messageid', ''), 'image' => array('type' => 'speeddial')));
} else {
$page = new SpotPage_index($db, $settings, $currentSession, array('search' => $req->getDef('search', $spotUserSystem->getIndexFilter($currentSession['user']['userid'])), 'pagenr' => $req->getDef('pagenr', 0), 'sortby' => $req->getDef('sortby', ''), 'sortdir' => $req->getDef('sortdir', ''), 'messageid' => $req->getDef('messageid', ''), 'action' => $req->getDef('action', ''), 'data' => $req->getDef('data', array())));
}
# if
SpotTiming::stop('renderpage->case-default');
$page->render();
break;
# default
}
# switch
SpotTiming::stop('renderpage');
# timing
SpotTiming::stop('total');
# enable of disable de timer
if ($settings->get('enable_timing') && !in_array($req->getDef('page', ''), array('catsjson', 'statics', 'getnzb', 'getnzbmobile', 'markallasread', 'rss', 'newznabapi'))) {
SpotTiming::display();
}
# if
} catch (PermissionDeniedException $x) {
/*
* We try to render a permission denied error using the already created
* renderer first. We do this, so pages which are supposed to output
* XML, can also output their errors using XML.
*
* If no page is initiated just yet, we create an basic renderer object
* to render an error page
*/
if (!$page instanceof SpotPage_Abs) {
/**
* Retrieves an URL from the web and caches it when so required
*/
function performCachedGet($url, $storeWhenRedirected, $ttl = 900, $forceCacheRefresh = false)
{
SpotTiming::start(__CLASS__ . '::' . __FUNCTION__);
$url_md5 = md5($url);
/*
* Is this URL stored in the cache and is it still valid?
*/
if ($forceCacheRefresh) {
$content = false;
} else {
$content = $this->_cacheDao->getCachedHttp($url_md5);
}
// else
if (!$content || time() - (int) $content['stamp'] > $ttl) {
if (isset($content['stamp'])) {
$tmpData = $this->perform($url, $content['stamp']);
} else {
$tmpData = $this->perform($url);
}
// else
$data = $tmpData['data'];
$http_code = $tmpData['http_code'];
$curl_info = $tmpData['curl_info'];
/*
* HTTP return code is other than 200 (OK) and
* other than 304 (Resource not modified),
* we have no use for the result
*/
if ($http_code != 200 && $http_code != 304) {
return array($http_code, false);
}
# if
/*
* A ttl > 0 is specified, meaning we are allowed to
* store resources in the cache
*/
if ($ttl > 0) {
switch ($http_code) {
case 304:
/*
* Update the timestamp in the database to refresh this
* cached resource.
*/
$this->_cacheDao->updateHttpCacheStamp($url_md5);
break;
# 304 (resource not modified)
# 304 (resource not modified)
default:
/*
* Store the retrieved information in the cache
*/
if ($storeWhenRedirected || $curl_info['redirect_count'] == 0) {
$this->_cacheDao->saveHttpCache($url_md5, $data);
}
# if
# if
}
# switch
}
# else
} else {
$http_code = 304;
$data = $content['content'];
}
# else
SpotTiming::stop(__CLASS__ . '::' . __FUNCTION__, array($url, $storeWhenRedirected, $ttl));
return array($http_code, $data);
}
function createTextQuery($searchFields, $additionalFields)
{
SpotTiming::start(__FUNCTION__);
/*
* Initialize some basic values which are used as return values to
* make sure always return a valid set
*/
$filterValueSql = array();
$sortFields = array();
/*
* MySQL's fultxt search has a minimum length of words for indexes. Per default this is
* a minimum word length of 4. This means that a searchstring like 'Top 40' will not
* be found because both 'Top' and '40' are shorter than 4 characters.
*
* We query the server setting, and if this is the case, we fall back to a basic LIKE
* search because it has no such limitation
*/
$serverSetting = $this->_db->arrayQuery("SHOW VARIABLES WHERE variable_name = 'ft_min_word_len'");
$minWordLen = $serverSetting[0]['Value'];
foreach ($searchFields as $searchItem) {
$hasTooShortWords = false;
$hasLongEnoughWords = false;
$hasStopWords = false;
$hasNoStopWords = false;
$hasSearchOpAsTerm = false;
$searchMode = "match-natural";
$searchValue = trim($searchItem['value']);
$field = $searchItem['fieldname'];
$tempSearchValue = str_replace(array('+', '-', 'AND', 'NOT', 'OR'), '', $searchValue);
/*
* Look at each individual word. If it is shorter than $minWordLen, we have to perform
* a LIKE search as well
*/
$termList = explode(' ', $tempSearchValue);
foreach ($termList as $term) {
if (strlen($term) < $minWordLen && strlen($term) > 0) {
$hasTooShortWords = true;
}
# if
if (strlen($term) >= $minWordLen) {
$hasLongEnoughWords = true;
}
# if
}
# foreach
/*
* remove any double whitespace because else the MySQL matcher will never
* find anything
*/
$searchValue = str_replace(' ', ' ', $searchValue);
/*
* MySQL has several types of searches - both boolean and natural matching for
* FTS is possible.
*
* We try some heuristics to select the most appropriate type of search.
* If a word starts with either an '+' or an '-', we switch to boolean match
*/
$termList = explode(' ', $searchValue);
foreach ($termList as $term) {
/*
* We strip some characters because these are valid, but
* can cause the system to not regocnize them as operators.
*
* The string: "(<test)" is such an example -- we only check
* the first character so we need to remove the parenthesis
*/
$strippedTerm = trim($term, "()'\"");
/*
* If after stripping the term of these characters, no string
* is left, make sure we juts abort the matching
*/
if (strlen($strippedTerm) < 1) {
continue;
}
# if
/*
* + and - are only allowed at the beginning of the search to
* enforce it as an search operator. If they are in the
* words themselves, we fall back to LIKE
*/
if (strpos($strippedTerm, '-') > 0 || strpos($strippedTerm, '+') > 0 || strpos($strippedTerm, '/') > 0) {
$hasSearchOpAsTerm = true;
}
# if
/*
* When there are boolean operators in the string, it's an
* boolean search
*/
if (strpos('+-~<>', $strippedTerm[0]) !== false) {
$searchMode = 'match-boolean';
}
# if
if (strpos('*', substr($strippedTerm, -1)) !== false) {
$searchMode = 'match-boolean';
}
# if
if (strpos('"', substr($term, -1)) !== false) {
$searchMode = 'match-boolean';
}
# if
/*
* If the term is a stopword (things like: the, it, ...) we have to
* fallback to a like search as well.
*/
if (in_array(strtolower($strippedTerm), $this->stop_words) !== false) {
$hasStopWords = true;
} else {
/*
* This extra chcek is necessary because when a query was to be done
* for only short of stopwords (eg: "The Top") , we should fall back to
* a like anyway
*/
if (strlen($term) >= $minWordLen) {
$hasNoStopWords = true;
}
# if
}
# else
}
# foreach
# Actually determine the searchmode
/*
* Test cases:
*
* 9th Company
* Ubuntu 9
* Top 40
* South Park
* Sex and the city
* Rio
* "sex and the city 2"
* Just Go With It (fallback naar like, enkel stopwoorden of te kort)
* "Just Go With It" (fallback naar like, en quotes gestripped)
* +empire +sun
* x-art (like search because it contains an -)
* 50/50 (like search because it contains an /)
*/
/*
echo 'HasTooShortWords : ' . (int) $hasTooShortWords . '<br>';
echo 'hasStopWords : ' . (int) $hasStopWords . '<br>';
echo 'hasLongEnoughWords: ' . (int) $hasLongEnoughWords . '<br>';
echo 'hasNoStopWords : ' . (int) $hasNoStopWords . '<br>';
echo 'hasSearchOpAsTerm : ' . (int) $hasSearchOpAsTerm . '<br>';
echo 'searchmode : ' . $searchMode . '<br>';
die();
*/
if (($hasTooShortWords || $hasStopWords) && ($hasLongEnoughWords || $hasNoStopWords) && !$hasSearchOpAsTerm) {
if ($hasStopWords && !$hasNoStopWords) {
$searchMode = 'normal';
} else {
$searchMode = 'both-' . $searchMode;
}
# else
} elseif (($hasTooShortWords || $hasStopWords) && (!$hasLongEnoughWords && !$hasNoStopWords) || $hasSearchOpAsTerm) {
$searchMode = 'normal';
}
# else
/*
* Start constructing the query. Sometimes we construct the quer
* both with a LIKE and with a MATCh statement
*/
$queryPart = '';
if ($searchMode == 'normal' || $searchMode == 'both-match-natural') {
$filterValueSql[] = ' ' . $field . " LIKE '%" . $this->_db->safe(trim($searchValue, "\"'")) . "%'";
}
# if
if ($searchMode == 'match-natural' || $searchMode == 'both-match-natural') {
/* Natural language mode altijd default in MySQL 5.0 en 5.1, but cannot be explicitly defined in MySQL 5.0 */
$queryPart = " MATCH(" . $field . ") AGAINST ('" . $this->_db->safe($searchValue) . "')";
$filterValueSql[] = $queryPart;
}
# if
if ($searchMode == 'match-boolean' || $searchMode == 'both-match-boolean') {
$queryPart = " MATCH(" . $field . ") AGAINST ('" . $this->_db->safe($searchValue) . "' IN BOOLEAN MODE)";
$filterValueSql[] = $queryPart;
}
# if
/*
* We add these extended textqueries as a column to the filterlist
* and use it as a relevance column. This allows us to sort on
* relevance
*/
if ($searchMode != 'normal') {
/*
* if we get multiple textsearches, we sort them per order
* in the system
*/
$tmpSortCounter = count($additionalFields);
$additionalFields[] = $queryPart . ' AS searchrelevancy' . $tmpSortCounter;
$sortFields[] = array('field' => 'searchrelevancy' . $tmpSortCounter, 'direction' => 'DESC', 'autoadded' => true, 'friendlyname' => null);
}
# if
}
# foreach
SpotTiming::stop(__FUNCTION__, array($filterValueSql, $additionalFields, $sortFields));
return array('filterValueSql' => $filterValueSql, 'additionalTables' => array(), 'additionalFields' => $additionalFields, 'sortFields' => $sortFields);
}
public function fetchSpotImage($fullSpot)
{
SpotTiming::start(__CLASS__ . '::' . __FUNCTION__);
$return_code = 0;
$validImage = false;
$imageString = '';
$data = $this->_cacheDao->getCachedSpotImage($fullSpot['messageid']);
if ($data !== false) {
$this->_cacheDao->updateSpotImageCacheStamp($fullSpot['messageid'], $data);
SpotTiming::stop(__CLASS__ . '::' . __FUNCTION__);
return $data;
}
# if
/*
* Determine whether the spot is stored on an NNTP server or a web resource,
* older spots are stored on an HTTP server
*/
if (is_array($fullSpot['image'])) {
try {
/*
* Convert the list of segments to a format
* usable for readBinary()
*/
$segmentList = array();
foreach ($fullSpot['image']['segment'] as $seg) {
$segmentList[] = $seg;
}
# foreach
SpotTiming::start('fetchSpotImage::readBinary()');
$imageString = $this->_nntpSpotReading->readBinary($segmentList, false);
SpotTiming::stop('fetchSpotImage::readBinary()');
$validImage = true;
} catch (Exception $x) {
$validImage = false;
$return_code = $x->getCode();
}
# catch
} elseif (empty($fullSpot['image'])) {
/*
* Spot did not contain an image (this is illegal?),
* create a dummy error message
*/
$validImage = false;
$return_code = 901;
} elseif (!empty($fullSpot['image'])) {
/*
* We don't want the HTTP layer of this code to cache the image, because
* we want to cache / store additional information in the cache for images
*/
$tmpPerform = $this->_serviceHttp->perform($fullSpot['image'], null, 0);
$return_code = $tmpPerform['http_code'];
$imageString = $tmpPerform['data'];
if ($return_code == 200 || $return_code == 304) {
$validImage = true;
}
# else
}
# elseif
/*
* Now validate the resource we have retrieved from the server
*/
if ($validImage) {
SpotTiming::start('fetchSpotImage::getImageDimensions()');
$svc_ImageUtil = new Services_Image_Util();
$dimensions = $svc_ImageUtil->getImageDimensions($imageString);
SpotTiming::stop('fetchSpotImage::getImageDimensions()', array());
/*
* If this is not a valid image, create a dummy error code,
* else we save it in the cache
*/
if ($dimensions !== false) {
/*
* If the current image is an BMP file, convert it to
* JPEG
*/
if ($dimensions['isbmp']) {
SpotTiming::start('fetchSpotImage::convertToBmp()');
$svc_ImageBmpConverter = new Services_Image_BmpConverter();
$imageString = $svc_ImageBmpConverter->convertBmpImageStringToJpeg($imageString, $dimensions);
$dimensions = $svc_ImageUtil->getImageDimensions($imageString);
$validImage = $dimensions !== false;
SpotTiming::stop('fetchSpotImage::convertToBmp()', serialize($validImage));
}
# if
/*
* and store the file in the cache
*/
if ($validImage) {
/*
* This is an actual SpotImage
*/
$dimensions['is_tempimage'] = false;
SpotTiming::start('fetchSpotImage::savingToCache()');
if (!$this->_cacheDao->saveSpotImageCache($fullSpot['messageid'], $dimensions, $imageString, false)) {
$validImage = false;
$return_code = 997;
}
# if
SpotTiming::stop('fetchSpotImage::savingToCache()', serialize($validImage));
}
# if
} else {
$validImage = false;
$return_code = 998;
}
# if
}
# if
/*
* Did we get a return code other than 200 OK and
* other than 304 (Resource Not modified), create
* an error code image
*/
if (!$validImage) {
SpotTiming::start('fetchSpotImage::createErrorImage()');
$svc_ImageError = new Services_Image_Error();
$errorImage = $svc_ImageError->createErrorImage($return_code);
SpotTiming::stop('fetchSpotImage::createErrorImage()');
$imageString = $errorImage['content'];
$dimensions = $errorImage['metadata'];
/*
* Store a copy of the error image so we don't request
* the same image over and over.
*/
$this->_cacheDao->saveSpotImageCache($fullSpot['messageid'], $dimensions, $imageString, true);
}
# if
SpotTiming::stop(__CLASS__ . '::' . __FUNCTION__, array($fullSpot));
return array('content' => $imageString, 'metadata' => $dimensions);
}
function createTextQuery($field, $searchValue) {
SpotTiming::start(__FUNCTION__);
$searchMode = "match-natural";
$searchValue = trim($searchValue);
$tempSearchValue = str_replace(array('+', '-', 'AND', 'NOT', 'OR'), '', $searchValue);
# MySQL fulltext search kent een minimum aan lengte voor woorden dat het indexeert,
# standaard staat dit op 4 en dat betekent bv. dat een zoekstring als 'Top 40' niet gevonden
# zal worden omdat zowel Top als 40 onder de 4 karakters zijn. We kijken hier wat de server
# instelling is, en vallen eventueel terug op een normale 'LIKE' zoekopdracht.
$serverSetting = $this->arrayQuery("SHOW VARIABLES WHERE variable_name = 'ft_min_word_len'");
$minWordLen = $serverSetting[0]['Value'];
# bekijk elk woord individueel, is het korter dan $minWordLen, gaan we terug naar normale
# LIKE modus
$termList = explode(' ', $tempSearchValue);
foreach($termList as $term) {
if ((strlen($term) < $minWordLen) && (strlen($term) > 0)) {
$searchValue = $tempSearchValue;
$searchMode = "normal";
break;
} # if
} # foreach
# bekijk elk woord opnieuw individueel, als we een + of - sign aan het begin van een woord
# vinden, schakelen we over naar boolean match
$termList = explode(' ', $searchValue);
foreach($termList as $term) {
# We strippen een aantal karakters omdat dat niet de search
# methode mag beinvloeden, bv. (<test) oid.
$strippedTerm = trim($term, "()'\"");
# als er boolean phrases in zitten, is het een boolean search
if (strpos('+-~<>', $strippedTerm[0]) !== false) {
$searchMode = 'match-boolean';
break;
} # if
if (strpos('*', substr($strippedTerm, -1)) !== false) {
$searchMode = 'match-boolean';
break;
} # if
if (strpos('"', substr($term, -1)) !== false) {
$searchMode = 'match-boolean';
break;
} # if
# als het een stop word is, dan vallen we ook terug naar de like search
if (in_array($strippedTerm, $this->stop_words) !== false) {
$searchMode = 'normal';
break;
} # if
} # foreach
switch($searchMode) {
case 'normal' : $queryPart = ' ' . $field . " LIKE '%" . $this->safe($searchValue) . "%'"; break;
/* Natural language mode is altijd het default in MySQL 5.0 en 5.1, maar kan in 5.0 niet expliciet opgegeven worden */
case 'match-natural' : $queryPart = " MATCH(" . $field . ") AGAINST ('" . $this->safe($searchValue) . "')"; break;
case 'match-boolean' : $queryPart = " MATCH(" . $field . ") AGAINST ('" . $this->safe($searchValue) . "' IN BOOLEAN MODE)"; break;
} # else
SpotTiming::stop(__FUNCTION__, array($field,$searchValue));
return array('filter' => $queryPart,
'sortable' => ($searchMode != 'normal') );
} # createTextQuery()
/**
* Returns the DAO factory used by all of
* Spotweb
*
* @throws DatabaseConnectionException
* @return Dao_Base_Factory
*/
public function getDaoFactory()
{
SpotTiming::start(__CLASS__ . '::' . __FUNCTION__);
@(include "dbsettings.inc.php");
if (empty($dbsettings)) {
throw new DatabaseConnectionException("No database settings have been entered, please use the 'install.php' wizard to install and configure Spotweb." . PHP_EOL . "If you are upgrading from an earlier version of Spotweb, please consult https://github.com/spotweb/spotweb/wiki/Frequently-asked-questions/ first");
}
# if
/*
* Store the DB settings so we can retrieve them later, if so desired,
* we do overwrite the password to make sure it doesn't show up in a
* stacktrace.
*/
$this->_dbSettings = $dbsettings;
$this->_dbSettings['pass'] = '******';
$this->_dbSettings['user'] = '******';
$dbCon = dbeng_abs::getDbFactory($dbsettings['engine']);
$dbCon->connect($dbsettings['host'], $dbsettings['user'], $dbsettings['pass'], $dbsettings['dbname']);
$daoFactory = Dao_Factory::getDAOFactory($dbsettings['engine']);
$daoFactory->setConnection($dbCon);
SpotTiming::stop(__CLASS__ . '::' . __FUNCTION__);
return $daoFactory;
}
function createTextQuery($searchFields, $additionalFields)
{
SpotTiming::start(__CLASS__ . '::' . __FUNCTION__);
/*
* Initialize some basic values which are used as return values to
* make sure always return a valid set
*/
$filterValueSql = array();
$sortFields = array();
/*
* MySQL's fultxt search has a minimum length of words for indexes. Per default this is
* a minimum word length of 4. This means that a searchstring like 'Top 40' will not
* be found because both 'Top' and '40' are shorter than 4 characters.
*
* We query the server setting, and if this is the case, we fall back to a basic LIKE
* search because it has no such limitation
*/
$serverSetting = $this->_db->arrayQuery("SHOW VARIABLES WHERE variable_name = 'ft_min_word_len'");
$minWordLen = $serverSetting[0]['Value'];
// var_dump($searchFields);
foreach ($searchFields as $searchItem) {
$hasTooShortWords = false;
$hasLongEnoughWords = false;
$hasStopWords = false;
$hasNoStopWords = false;
$hasSearchOpAsTerm = false;
$hasPhraseWithOnlyInvalids = false;
$searchMode = "match-natural";
$searchValue = trim($searchItem['value']);
$field = $searchItem['fieldname'];
/*
* Look at each individual word. If it is shorter than $minWordLen, we have to perform
* a LIKE search as well
*
* We do not use splitWords() here, because we need to have the lengths of the
* individual words not of the phrase search.
*
* There is one exception, if a phrase search contains ONLY stop words, we cannot
* use the FTS at all so we should be aware of that.
*/
$tempSearchValue = str_replace(array('+', '-', '"', '(', ')', 'AND', 'NOT', 'OR'), '', $searchValue);
$termList = explode(' ', $tempSearchValue);
foreach ($termList as $term) {
if (strlen($term) < $minWordLen && strlen($term) > 0) {
$hasTooShortWords = true;
}
# if
if (strlen($term) >= $minWordLen) {
$hasLongEnoughWords = true;
}
# if
/*
* If the term is a stopword (things like: the, it, ...) we have to
* fallback to a like search as well.
*/
if (in_array(strtolower($term), $this->stop_words) !== false) {
$hasStopWords = true;
} else {
$hasNoStopWords = true;
}
# if
}
# foreach
/*
* remove any double whitespace because else the MySQL matcher will never
* find anything
*/
$searchValue = str_replace(' ', ' ', $searchValue);
/*
* MySQL has several types of searches - both boolean and natural matching for
* FTS is possible.
*
* We try some heuristics to select the most appropriate type of search.
* If a word starts with either an '+' or an '-', we switch to boolean match
*/
$termList = $this->splitWords($searchValue);
foreach ($termList as $term) {
/*
* We strip some characters because these are valid, but
* can cause the system to not recognize them as operators.
*
* The string: "(<test)" is such an example -- we only check
* the first character so we need to remove the parenthesis.
*/
$strippedTerm = trim($term, "()'\"");
/*
* If after stripping the term of these characters, no string
* is left, make sure we just abort the matching
*/
if (strlen($strippedTerm) < 1) {
continue;
}
# if
/*
* + and - are only allowed at the beginning of the search to
* enforce it as an search operator. If they are in the
* words themselves, we fall back to LIKE
*/
if (strpos($strippedTerm, '-') > 0 || strpos($strippedTerm, '+') > 0 || strpos($strippedTerm, '/') > 0) {
$hasSearchOpAsTerm = true;
}
# if
/*
* When there are boolean operators in the string, it's an
* boolean search
*/
if (strlen($strippedTerm[0]) > 0) {
if (strpos('+-~<>', $strippedTerm[0]) !== false) {
$searchMode = 'match-boolean';
$strippedTerm = trim($strippedTerm, "+-");
}
# if
}
if (strlen(substr($strippedTerm, -1)) > 0) {
if (strpos('*', substr($strippedTerm, -1)) !== false) {
$searchMode = 'match-boolean';
$strippedTerm = trim($strippedTerm, "*");
}
# if
}
if (strlen(substr($term, -1)) > 0) {
if (strpos('"', substr($term, -1)) !== false) {
$searchMode = 'match-boolean';
}
# if
}
/*
* We get the complete phrase here, we need to look into
* the phrase terms, because if it only contains invalid terms
* (eg: only stopwords, only shortwords, or a combination thereof),
* we must disable the FTS completely.
*
*/
if (!$hasPhraseWithOnlyInvalids && $term[0] == '"') {
$tmpFoundValidTerms = false;
$tmpTermList = explode(' ', $strippedTerm);
foreach ($tmpTermList as $tmpTerm) {
if (strlen($tmpTerm) >= $minWordLen) {
if (in_array(strtolower($tmpTerm), $this->stop_words) === false) {
$tmpFoundValidTerms = true;
}
# if
}
# if
}
# foreach
if (!$tmpFoundValidTerms) {
$hasPhraseWithOnlyInvalids = true;
}
# if
}
# if
}
# foreach
# Actually determine the searchmode
/*
* Test cases:
*
* 9th Company
* Ubuntu 11
* Top 40
* "Top 40"
* South Park
* Sex and the city
* Rio
* "sex and the city 2"
* Just Go With It (fallback naar like, enkel stopwoorden of te kort)
* "Just Go With It" (fallback naar like, en quotes gestripped)
* +"taken 2" +(2012) (fallback naar like, en quotes gestripped - enkel stop woorden maar operators)
* +empire +sun
* x-art (like search because it contains an -)
* 50/50 (like search because it contains an /)
* Arvo -Lamentate (natural without like)
* +"Phantom" +(2013) <- Shouldn't use a LIKE per se
* "The Top" <- Should use a LIKE as its only keywords
* +"Warehouse 13" +S04 <- Shouldn't use a LIKE per se
*/
if ($hasPhraseWithOnlyInvalids) {
$searchMode = 'normal';
} elseif (($hasTooShortWords || $hasStopWords) && ($hasLongEnoughWords || $hasNoStopWords) && !$hasSearchOpAsTerm) {
if ($hasStopWords && !$hasNoStopWords || $hasTooShortWords && !$hasLongEnoughWords) {
$searchMode = 'normal';
} else {
$searchMode = 'both-' . $searchMode;
}
# else
} elseif (($hasTooShortWords || $hasStopWords) && (!$hasLongEnoughWords && !$hasNoStopWords) || $hasSearchOpAsTerm) {
$searchMode = 'normal';
}
# else
/*
echo 'hasStopWords : ' . (int) $hasStopWords . '<br>';
echo 'hasLongEnoughWords : ' . (int) $hasLongEnoughWords . '<br>';
echo 'hasTooShortWords : ' . (int) $hasTooShortWords . '<br>';
echo 'hasNoStopWords : ' . (int) $hasNoStopWords . '<br>';
echo 'hasSearchOpAsTerm : ' . (int) $hasSearchOpAsTerm . '<br>';
echo 'hasPhraseWithOnlyInvalids : ' . (int) $hasPhraseWithOnlyInvalids . '<br>';
echo 'searchmode : ' . $searchMode . '<br>';
die();
*/
/*
* Start constructing the query. Sometimes we construct the query
* both with a LIKE and with a MATCH statement
*/
$queryPart = array();
$matchPart = '';
if ($searchMode == 'normal' || $searchMode == 'both-match-natural' || $searchMode == 'both-match-boolean') {
foreach ($this->splitWords($searchValue) as $splittedTerm) {
/*
* If the term contains an boolean operator in the beginning,
* strip it
*/
$filteredTerm = trim($splittedTerm, "\"");
$filteredTerm = ltrim($filteredTerm, "+-~<>");
$filteredTerm = rtrim($filteredTerm, "*");
if (!empty($filteredTerm)) {
$queryPart[] = ' ' . $field . " LIKE " . $this->_db->safe('%' . $filteredTerm . '%');
}
# if
}
# foreach
}
# if
if ($searchMode == 'match-natural' || $searchMode == 'both-match-natural') {
/* Natural language mode always defaults in MySQL 5.0 en 5.1, but cannot be explicitly defined in MySQL 5.0 */
$matchPart = " MATCH(" . $field . ") AGAINST (" . $this->_db->safe($searchValue) . ")";
$queryPart[] = $matchPart;
}
# if
/*
* Boolean searches with required or missing terms, will never match if the terms are
* stopwords because stopwords are not in the index and cannot be found
*/
if ($searchMode == 'match-boolean' || $searchMode == 'both-match-boolean') {
$matchPart = " MATCH(" . $field . ") AGAINST (" . $this->_db->safe($searchValue) . " IN BOOLEAN MODE)";
$queryPart[] = $matchPart;
}
# if
/*
* Add the textqueries with an AND per search term
*/
$filterValueSql[] = ' (' . implode(' AND ', $queryPart) . ') ';
/*
* We add these extended textqueries as a column to the filterlist
* and use it as a relevance column. This allows us to sort on
* relevance
*/
if ($searchMode != 'normal') {
/*
* if we get multiple textsearches, we sort them per order
* in the system
*/
$tmpSortCounter = count($additionalFields);
$additionalFields[] = $matchPart . ' AS searchrelevancy' . $tmpSortCounter;
$sortFields[] = array('field' => 'searchrelevancy' . $tmpSortCounter, 'direction' => 'DESC', 'autoadded' => true, 'friendlyname' => null);
}
# if
}
# foreach
SpotTiming::stop(__CLASS__ . '::' . __FUNCTION__, array($filterValueSql, $additionalFields, $sortFields));
// var_dump($filterValueSql);
// die();
return array('filterValueSql' => $filterValueSql, 'additionalTables' => array(), 'additionalFields' => $additionalFields, 'sortFields' => $sortFields);
}