PageRenderTime 4ms CodeModel.GetById 5ms app.highlight 59ms RepoModel.GetById 13ms app.codeStats 0ms

/lib/services/Search/Services_Search_QueryParser.php

https://github.com/elstenaar86/spotweb
PHP | 986 lines | 505 code | 106 blank | 375 comment | 121 complexity | 965240588f0d0e8f3291645c23172ee6 MD5 | raw file
  1<?php
  2
  3class Services_Search_QueryParser {
  4	/*
  5	 * We need a databse class to be able to properly escape
  6	 * data in queries
  7	 */
  8	private $_dbEng = null;
  9
 10	/*
 11	 * constructor 
 12	 */
 13	function __construct(dbeng_abs $db) {
 14		$this->_dbEng = $db;
 15	} # ctor
 16
 17	/*
 18	 * When passed an array with categories, this array is expanded \
 19	 * to contain the fully qualified categories and subcategories.
 20	 */
 21	public function prepareCategorySelection($dynaList) {
 22		SpotTiming::start(__FUNCTION__);
 23
 24		$strongNotList = array();
 25		$categoryList = array();
 26		
 27		/*
 28		 * The Dynatree jquery widget which we use, retrieves its data from ?page=catsjson,
 29		 * for each node in the dynatree we provide a key. The tree could be seen as follows,
 30		 * with the unique key within parenthesis.
 31		 *
 32		 * - Image (cat0)
 33		 * +-- Film (cat0_z0)
 34		 * +--- Format (cat0_z0_a)
 35		 * +----- DivX (cat0_z0_a0)
 36		 * +----- WMV (cat0_z0_a1)
 37		 * +-- Series (cat0_z1)
 38		 * +--- Format (cat0_z1_a)
 39		 *+----- DivX (cat0_z1_a0)
 40		 * +----- WMV (cat0_z1_a1)
 41		 * +--- Source (cat0_z1_b)
 42		 * - Applications (cat3)
 43		 * +-- Format (cat1_zz_a / cat1_a)
 44		 *
 45		 * Basially, you have a headcategory number, then you have a categorytype, then a subcategorytype (a,b,c,d, ...)
 46		 * then the subcategorynumber follows.
 47		 *
 48		 * When you want to select, in above example, a Film in DivX, the keyvalue is simply cat0_z0_a0.
 49		 * However, when you want to select the whole of 'Image', keyvalue 'cat0' would suffice. 
 50		 *
 51		 * If users would select categories manually (for example a manually constructed search), it would
 52		 * be more convienent for them to be able to provide shorthands, allowing one to select common category
 53		 * groups more easily. Spotweb wil expand those category selection items to contain the full selection.
 54		 *
 55		 * The following shorthands are allowed:
 56		 *		 
 57		 * cat0						- Will be expanded to all subcategoies of category 0
 58		 * cat0_z0_a				- Will be expanded to subcategory A of category 0, but the type must be z0
 59		 * !cat0_z0_a1				- Will remove cat0_z0_a1 from the list (order in the list is important)
 60		 * ~cat0_z0_a1				- 'Forbids' cat0_z0_a1 to be in the list (a NOT will be applied to it)
 61		 * cat0_a					- Select everything from subcategory A in category 0 (all z-types)
 62		 *
 63		 */
 64		$newTreeQuery = '';
 65		
 66		/*
 67		 * Process each item in the list, and expand it where necessary
 68		 */
 69		$dynaListCount = count($dynaList);
 70		for($i = 0; $i < $dynaListCount; $i++) {
 71			/*
 72			 * The given category can be one of the following four types:
 73			 *    cat1_z0_a			==> Everything of cat1, type z0, and then everything of subcategory a
 74			 *    cat1_z0			==> Everything of cat1, type z0
 75			 * 	  cat1_a			==> Everything of cat1 which is of 'subcategory a'
 76			 * 	  cat1				==> Select the whole of cat1
 77			 *
 78			 */
 79			if ((strlen($dynaList[$i]) > 0) && ($dynaList[$i][0] == 'c')) {
 80				$hCat = (int) substr($dynaList[$i], 3, 1);
 81				
 82				# Was a type + global subcategory selected? (cat1_z0_a)
 83				if (strlen($dynaList[$i]) == 9) {
 84					$typeSelected = substr($dynaList[$i], 5, 2);
 85					$subCatSelected = substr($dynaList[$i], 8);
 86				# Was only as category selected (cat1)
 87				} elseif (strlen($dynaList[$i]) == 4) {
 88					$typeSelected = '*';
 89					$subCatSelected = '*';
 90				# Was a category and type selected (cat1_z0)
 91				} elseif ((strlen($dynaList[$i]) == 7) && ($dynaList[$i][5] === 'z')) {
 92					$typeSelected = substr($dynaList[$i], 5, 2);
 93					$subCatSelected = '*';
 94				# Was a category and subcateory specified, old stype? (cat1_a3)
 95				} elseif (((strlen($dynaList[$i]) == 7) || (strlen($dynaList[$i]) == 8)) && ($dynaList[$i][5] !== 'z')) {
 96					# Convert the old style to explicit categories (cat1_z0_a3, cat1_z1_a3, cat1_z2_a3, ... )
 97					foreach(SpotCategories::$_categories[$hCat]['z'] as $typeKey => $typeValue) {
 98						$newTreeQuery .= "," . substr($dynaList[$i], 0, 4) . '_z' . $typeKey . '_' . substr($dynaList[$i], 5);
 99					} # foreach
100					
101					$typeSelected = '';
102					$subCatSelected = '';
103				# was a subcategory specified? (cat1_a)
104				} elseif (strlen($dynaList[$i]) == 6) {
105					$typeSelected = '*';
106					$subCatSelected = substr($dynaList[$i], 5, 1);
107				} else {
108					$newTreeQuery .= "," . $dynaList[$i];
109					
110					$typeSelected = '';
111					$subCatSelected = '';
112				} # else
113
114				/*
115				 * Createa a string containing all subcategories.
116				 *
117				 * We always loop through all subcategories so we can reuse this bit of code
118				 * both for complete category selection as subcategory selection.
119				 */
120				$tmpStr = '';
121				foreach(SpotCategories::$_categories[$hCat] as $subCat => $subcatValues) {
122
123					/*
124					 * There are four possible cases:
125					 *
126					 *   $subcatSelected contains an empty string, it matches to nothing.
127					 *   $subcatSelected contains an asterisk, it matches all subcategories.
128					 *   $typeSelected contains an empty string, it matches nothing.
129					 *   $typeSelected contains an asterisk, it matches all types.
130					 */				
131					if ($subCatSelected == '*') {
132						foreach(SpotCategories::$_categories[$hCat]['z'] as $typeKey => $typeValue) {
133							$typeKey = 'z' . $typeKey;
134							if (($typeKey == $typeSelected) || ($typeSelected == '*')) {
135								$tmpStr .= ',sub' . $hCat . '_' . $typeKey;
136							} # if
137						} # foreach
138					} elseif (($subCat == $subCatSelected) && ($subCat !== 'z')) {
139						foreach(SpotCategories::$_categories[$hCat]['z'] as $typeKey => $typeValue) {
140							$typeKey = 'z' . $typeKey;
141							if (($typeKey == $typeSelected) || ($typeSelected == '*')) {
142							
143								foreach(SpotCategories::$_categories[$hCat][$subCat] as $x => $y) {
144									if (in_array($typeKey, $y[2])) {
145										$tmpStr .= ",cat" . $hCat . "_" . $typeKey . '_' . $subCat . $x;
146									} # if
147								} # foreach
148							} # if
149						} # foreach
150					} # if
151				} # foreach
152
153				$newTreeQuery .= $tmpStr;
154			} elseif (substr($dynaList[$i], 0, 1) == '!') {
155				# For a not, we just remove / exclude it from the list.
156				$newTreeQuery = str_replace(',' . substr($dynaList[$i], 1), "", $newTreeQuery);
157			} elseif (substr($dynaList[$i], 0, 1) == '~') {
158				/*
159				 * For a STRONG NOT, we cannot remove it from the list because want to explicitly
160				 * remove those results from the query and we have to pass it in other URL's and the 
161				 * likes
162				 */
163				$newTreeQuery .= "," . $dynaList[$i];
164				
165				# and add it to the strongNotList array for usage later on
166				$strongNotTmp = explode("_", $dynaList[$i], 2);
167
168				/* To deny a whole category, we have to take an other shortcut */
169				if (count($strongNotTmp) == 1) {
170					$strongNotList[(int) substr($strongNotTmp[0], 4)][] = '';
171				} else {
172					$strongNotList[(int) substr($strongNotTmp[0], 4)][] = $strongNotTmp[1];
173				} # else
174			} else {
175				$newTreeQuery .= "," . $dynaList[$i];
176			} # else
177		} # for
178		if ((!empty($newTreeQuery)) && ($newTreeQuery[0] == ",")) { 
179			$newTreeQuery = substr($newTreeQuery, 1); 
180		} # if
181
182		/*
183		 * 
184		 * Starting from here, we have a prepared list - meaning, a list with all
185		 * categories fully expanded.
186		 *
187		 * We now translate this list to an nested list of elements which is easier
188		 * to convert to SQL. The format of the array is fairly typical:
189		 *
190		 * list['cat']
191		 *            [cat]							 -> Head category, eg: 0 for Images
192		 *                 [type]					 -> Type, eg: 0 for z0 
193		 *                       [subcattype]		 -> Subcategory type, eg: a 
194		 *                                   = value -> eg 1 for in total cat0_z0_a1
195		 */     
196		$dynaList = explode(',', $newTreeQuery);
197
198		foreach($dynaList as $val) {
199			if (substr($val, 0, 3) == 'cat') {
200				# 0 element is headcategory
201				# 1st element is type
202				# 2ndelement is category
203				$val = explode('_', (substr($val, 3) . '_'));
204
205				$catVal = $val[0];
206				$typeVal = $val[1];
207				$subCatIdx = substr($val[2], 0, 1);
208				$subCatVal = substr($val[2], 1);
209
210				if (count($val) >= 4) {
211					$categoryList['cat'][$catVal][$typeVal][$subCatIdx][] = $subCatVal;
212				} # if
213			} elseif (substr($val, 0, 3) == 'sub') {
214				# 0 element is headcategory
215				# 1st element is type
216				$val = explode('_', (substr($val, 3) . '_'));
217
218				$catVal = $val[0];
219				$typeVal = $val[1];
220
221				# Create the z-category in the categorylist
222				if (count($val) == 3) {
223					if (!isset($categoryList['cat'][$catVal][$typeVal])) {
224						$categoryList['cat'][$catVal][$typeVal] = array();
225					} # if
226				} # if
227			} # elseif
228		} # foreach
229
230		SpotTiming::stop(__FUNCTION__, array($categoryList, $strongNotList));
231		
232		return array($categoryList, $strongNotList);
233	} # prepareCategorySelection
234
235	/*
236	 * Converts a list of categories to an SQL filter
237	 */
238	private function categoryListToSql($categoryList) {
239		SpotTiming::start(__FUNCTION__);
240		$categorySql = array();
241
242		# Make sure we were passed a valid filter
243		if ((!isset($categoryList['cat'])) || (!is_array($categoryList['cat']))) {
244			return $categorySql;
245		} # if
246
247		/*
248		 * We have to translate the list of sub- and headcategories to an SQL WHERE statement in 
249		 * multiple steps, where the 'category' is the basis for our filter.
250		 *
251		 * A testste for filters could be the following:
252		 *   cat0_z0_a9,cat0_z1_a9,cat0_z3_a9, ==> HD beeld
253		 *   cat0_z0_a9,cat0_z0_b3,cat0_z0_c1,cat0_z0_c2,cat0_z0_c6,cat0_z0_c11,~cat0_z1,~cat0_z2,~cat0_z3 ==> Nederlands ondertitelde films
254		 *   cat0_a9 ==> Alles in x264HD
255		 *   cat1_z0,cat1_z1,cat1_z2,cat1_z3 ==> Alle muziek, maar soms heeft muziek geen genre ingevuld!
256		 * 
257		 * The category list structure is:
258		 *
259		 *	array(1) {
260		 *	  ["cat"]=>
261		 *	  array(1) {								
262		 *		[1]=>									<== Headcategory number (cat1)
263		 *		array(4) {
264		 *		  ["z0"]=>								<== Type (subcatz) number (cat1_z0)
265		 *		  array(4) {
266		 *			["a"]=>								<== Subcategorylist (cat1_z0_a)
267		 *			array(9) {
268		 *			  [0]=>								
269		 *			  string(1) "0"						<== Selected subcategory (so: cat1_z0_a0)
270		 *			}
271		 *			["b"]=>
272		 *			array(7) {
273		 *			  [0]=>
274		 *			  string(1) "0"
275		 *
276		 */
277
278		foreach($categoryList['cat'] as $catid => $cat) {
279			/*
280			 * Each category we have, we try to procss all subcategories
281			 * and convert it to a filter
282			 */
283			if ((is_array($cat)) && (!empty($cat))) {
284
285				foreach($cat as $type => $typeValues) {
286					$catid = (int) $catid;
287					$tmpStr = "((s.category = " . (int) $catid . ")";
288					
289					# dont filter the zz types (games/apps)
290					if ($type[1] !== 'z') {
291						$tmpStr .= " AND (s.subcatz = '" . $type . "|')";
292					} # if
293
294					$subcatItems = array();
295					foreach($typeValues as $subcat => $subcatItem) {
296						$subcatValues = array();
297						
298						foreach($subcatItem as $subcatValue) {
299							/*
300							 * A spot can only contain one 'A' and 'Z' subcategory value, so we
301							 * can perform an equality filter instead of a LIKE
302							 */
303							if ($subcat == 'a')  {
304								$subcatValues[] = "(s.subcata = '" . $subcat . $subcatValue . "|') ";
305							} elseif (in_array($subcat, array('b', 'c', 'd'))) {
306								$subcatValues[] = "(s.subcat" . $subcat . " LIKE '%" . $subcat . $subcatValue . "|%') ";
307							} # if
308						} # foreach
309
310						/*
311						 *
312						 * We add all subactegories within the same subcategory together (for example all
313						 * formats of a movie) with an OR. This means you can pick between DivX and WMV as 
314						 * a format
315						 *
316						 */
317						if (count($subcatValues) > 0) {
318							$subcatItems[] = " (" . join(" OR ", $subcatValues) . ") ";
319						} # if
320					} # foreach subcat
321
322					/*
323					 * After this, same headcategory and type (Image + Movie, Sound) filters for
324					 * subcategories are merged together with an AND.
325					 * 
326					 * This results in a filter like:
327					 * 
328					 * (((category = 0) AND ( ((subcata = 'a0|') ) AND ((subcatd LIKE '%d0|%')
329					 *
330					 * This makes sure you are able to pick multiple genres within the same category/subcategory,
331					 * but you will not get unpredictable results by getting an 'Action' game for Linux when you
332					 * accidentally asked for either 'Action' or 'Romance'.
333					 */
334					if (count($subcatItems) > 0) {
335						$tmpStr .= " AND (" . join(" AND ", $subcatItems) . ") ";
336					} # if
337					
338					# Finish of the query
339					$tmpStr .= ")";
340					$categorySql[] = $tmpStr;
341				} # foreach type
342
343			} # if
344		} # foreach
345
346		SpotTiming::stop(__FUNCTION__, array($categorySql));
347
348		return $categorySql;
349	} # categoryListToSql 
350	
351	/*
352	 * Converts a list of "strong nots" to the corresponding
353	 * SQL statements
354	 */
355	private function strongNotListToSql($strongNotList) {
356		SpotTiming::start(__FUNCTION__);
357		$strongNotSql = array();
358		
359		if (empty($strongNotList)) {
360			return array();
361		} # if
362
363		/*
364		 * Each STRONG NOT is to be converted individually to a NOT 
365		 * SQL WHERE filter
366		 */
367		foreach(array_keys($strongNotList) as $strongNotCat) {
368			foreach($strongNotList[$strongNotCat] as $strongNotSubcat) {
369				/*
370				 * When the strongnot is for a whole category (eg: cat0), we can
371				 * make the NOT even simpler
372				 */
373				if (empty($strongNotSubcat)) {
374					$strongNotSql[] = "(NOT (s.Category = " . (int) $strongNotCat . "))";
375				} else {
376					$subcats = explode('_', $strongNotSubcat);
377
378					/*
379					 * A spot can only contain one 'A' and 'Z' subcategory value, so we
380					 * can perform an equality filter instead of a LIKE
381					 */
382					if (count($subcats) == 1) {
383						if (in_array($subcats[0][0], array('a', 'z'))) { 
384							$strongNotSql[] = "(NOT ((s.Category = " . (int) $strongNotCat . ") AND (s.subcat" . $subcats[0][0] . " = '" . $this->_dbEng->safe($subcats[0]) . "|')))";
385						} elseif (in_array($subcats[0][0], array('b', 'c', 'd'))) { 
386							$strongNotSql[] = "(NOT ((s.Category = " . (int) $strongNotCat . ") AND (s.subcat" . $subcats[0][0] . " LIKE '%" . $this->_dbEng->safe($subcats[0]) . "|%')))";
387						} # if
388					} elseif (count($subcats) == 2) {
389						if (in_array($subcats[1][0], array('a', 'z'))) { 
390							$strongNotSql[] = "(NOT ((s.Category = " . (int) $strongNotCat . ") AND (s.subcatz = '" . $subcats[0] . "|') AND (subcat" . $subcats[1][0] . " = '" . $this->_dbEng->safe($subcats[1]) . "|')))";
391						} elseif (in_array($subcats[1][0], array('b', 'c', 'd'))) { 
392							$strongNotSql[] = "(NOT ((s.Category = " . (int) $strongNotCat . ") AND (s.subcatz = '" . $subcats[0] . "|') AND (subcat" . $subcats[1][0] . " LIKE '%" . $this->_dbEng->safe($subcats[1]) . "|%')))";
393						} # if
394					} # else
395				} # else not whole subcat
396			} # foreach				
397		} # forEach
398
399		SpotTiming::stop(__FUNCTION__, array($strongNotSql));
400
401		return $strongNotSql;
402	} # strongNotListToSql
403
404	/*
405	 * Prepareert de filter values naar een altijd juist formaat 
406	 */
407	private function prepareFilterValues($search) {
408		SpotTiming::start(__FUNCTION__);
409		$filterValueList = array();
410
411		/*
412		 * We have drie kinds of filters:
413		 *		- Old type where you have a search[type] with the values stamp,title,tag and an search[text]
414		 *		  containing the value to search for. This limits you to a maximum of one filter which is not
415		 *		  sufficient.
416		 *
417		 *		  We automatically convert these kind of searches to the new type.
418		 *
419		 *
420		 *		- New type where there is a search[value] array, which contain values in the following shape:
421		 *		  type:operator:value. 
422		 *        For example, tag:=:spotweb. A shorthand is also available when the operator is left out (eg: tag:spotweb),
423		 *		  we assume the EQ operator was intended.
424		 *
425		 *		- Special kind of lists, there are a few values with a special meaning:
426		 * 				New:0 					(new spots)
427		 * 				Downloaded:0 			(spots which are downloaded by this account)
428		 * 				Watch:0 				(spots on the watchlist of this account)
429		 * 				Seen:0 					(spots which have already been opened by this account)
430		 * 				MyPostedSpots:0 		(spots posted by this account)
431		 * 				WhitelistedSpotters:0   (spots posted by a whitelisted spotter)
432		 * 				
433		 */
434		if (isset($search['type'])) {
435			if (!isset($search['text'])) {
436				$search['text'] = '';
437			} # if
438		
439			/*
440			 * We can be provided a set of old and new filters, we don't want to
441			 * overwrite the regular filters, so we take care to append to them
442			 */	
443			if ((!isset($search['value'])) || (!is_array($search['value']))) {
444				$search['value'] = array();
445			} # if
446			$search['value'][] = $search['type'] . ':=:' . $search['text'];
447			unset($search['type']);
448		} # if
449
450		# Make sure that we always have something to iterate through
451		if ((!isset($search['value'])) || (!is_array($search['value']))) {
452			$search['value'] = array();
453		} # if
454
455		# Now we transform the new query (field:operator:value pair) to an exploded array for easier iteration
456		foreach($search['value'] as $value) {
457			if (!empty($value)) {
458				$tmpFilter = explode(':', $value);
459
460				# Default to an '=' operator when none is given				
461				if (count($tmpFilter) < 3) {
462					$tmpFilter = array($tmpFilter[0],
463									   '=',
464									   $tmpFilter[1]);
465				} # if
466				
467				/*
468				 * Create the actual filter, we add the array_slice part to
469				 * allow for an ':' in the actual search value.
470				 */
471				$filterValueTemp = Array('fieldname' => $tmpFilter[0],
472										 'operator' => $tmpFilter[1],
473										 'value' => join(":", array_slice($tmpFilter, 2)));
474										 
475				/*
476				 * and create the actual filter list. Before appending it,
477				 * we want to make sure no identical filter is already
478				 * in the list, because this might make MySQL very slow.
479				 */
480				if (!in_array($filterValueTemp, $filterValueList)) {
481					$filterValueList[] = $filterValueTemp;
482				} # if
483			} # if
484		} # for
485		
486		SpotTiming::stop(__FUNCTION__, array($filterValueList));
487
488		return $filterValueList;
489	} # prepareFilterValues
490
491	/*
492	 * Converts one or multiple userprovided txt filters to SQL statements
493	 */
494	private function filterValuesToSql($filterValueList, $currentSession) {
495		SpotTiming::start(__FUNCTION__);
496
497		# Add a list of possible text searches
498		$filterValueSql = array('OR' => array(), 'AND' => array());
499		$additionalFields = array();
500		$additionalTables = array();
501		$additionalJoins = array();
502		
503		$sortFields = array();
504		$textSearchFields = array();
505		
506		# Lookp table from 'friendly' name to fully qualified one
507		$filterFieldMapping = array('filesize' => 's.filesize',
508								  'date' => 's.stamp',
509								  'stamp' => 's.stamp',
510								  'userid' => 's.spotterid',
511								  'spotterid' => 's.spotterid',
512								  'moderated' => 's.moderated',
513								  'poster' => 's.poster',
514								  'titel' => 's.title',
515								  'title' => 's.title',
516								  'tag' => 's.tag',
517								  'new' => 'new',
518								  'reportcount' => 's.reportcount',
519								  'commentcount' => 's.commentcount',
520								  'downloaded' => 'downloaded', 
521								  'mypostedspots' => 'mypostedspots',
522								  'whitelistedspotters' => 'whitelistedspotters',
523								  'watch' => 'watch', 
524								  'seen' => 'seen');
525
526		foreach($filterValueList as $filterRecord) {
527			$tmpFilterFieldname = strtolower($filterRecord['fieldname']);
528			$tmpFilterOperator = $filterRecord['operator'];
529			$tmpFilterValue = $filterRecord['value'];
530
531			# When no match for friendly name -> column name is found, ignore the search
532			if (!isset($filterFieldMapping[$tmpFilterFieldname])) {
533				break;
534			} # if
535
536			# make sure the operators are valid
537			if (!in_array($tmpFilterOperator, array('>', '<', '>=', '<=', '=', '!='))) {
538				break;
539			} # if
540
541			/* 
542			 * Ignore empty searches. We cannot use the empty() operator, 
543			 * because empty(0) evaluates to true but is an valid 
544			 * value to search for
545			 */
546			if (strlen($tmpFilterValue) == 0) {
547				continue;
548			} # if
549
550			/*
551			 * When the search is pure textsearch, it might be able to be optimized
552			 * by utilizing the fulltext search (engine). If so, we take this path
553			 * to gain the most performance.
554			 */
555			if (in_array($tmpFilterFieldname, array('tag', 'poster', 'titel'))) {
556				/*
557				 * Some databases (sqlite for example), want to have all their fulltext
558				 * searches available in one SQL function call. 
559				 *
560				 * To be able to do this, we append all fulltext searches for now, so we
561				 * can create the actual fulltext search later on.
562				 */
563				if (!isset($textSearchFields[$filterFieldMapping[$tmpFilterFieldname]])) {
564					$textSearchFields[$filterFieldMapping[$tmpFilterFieldname]] = array();
565				} # if
566				$textSearchFields[$filterFieldMapping[$tmpFilterFieldname]][] = array('fieldname' => $filterFieldMapping[$tmpFilterFieldname], 'value' => $tmpFilterValue);
567			} elseif (in_array($tmpFilterFieldname, array('new', 'downloaded', 'watch', 'seen', 'mypostedspots', 'whitelistedspotters'))) {
568				/*
569				 * Some fieldnames are mere dummy fields which map to actual
570				 * functionality. Those dummiefields are processed below
571				 */
572				switch($tmpFilterFieldname) {
573					case 'new' : {
574							$tmpFilterValue = ' ((s.stamp > ' . (int) $this->_dbEng->safe($currentSession['user']['lastread']) . ')';
575							$tmpFilterValue .= ' AND (l.seen IS NULL))';
576							
577							break;
578					} # case 'new' 
579					case 'whitelistedspotters' : {
580						$tmpFilterValue = ' (wl.spotterid IS NOT NULL)';
581
582						break;
583					} # case 'whitelistedspotters'
584					case 'mypostedspots' : {
585						$additionalFields[] = '1 AS mypostedspot';
586						$additionalJoins[] = array('tablename' => 'spotsposted',
587												   'tablealias' => 'spost',
588												   'jointype' => 'LEFT',
589												   'joincondition' => 'spost.messageid = s.messageid');
590						$tmpFilterValue = ' (spost.ouruserid = ' . (int) $this->_dbEng->safe($currentSession['user']['userid']) . ') '; 	
591						$sortFields[] = array('field' => 'spost.stamp',
592											  'direction' => 'DESC',
593											  'autoadded' => true,
594											  'friendlyname' => null);
595						break;
596					} # case 'mypostedspots'
597					case 'downloaded' : { 
598						$tmpFilterValue = ' (l.download IS NOT NULL)'; 	
599						$sortFields[] = array('field' => 'downloadstamp',
600											  'direction' => 'DESC',
601											  'autoadded' => true,
602											  'friendlyname' => null);
603						break;
604					} # case 'downloaded'
605					case 'watch' 	  : { 
606						$additionalFields[] = '1 AS mywatchedspot';
607						$tmpFilterValue = ' (l.watch IS NOT NULL)'; 
608						$sortFields[] = array('field' => 'watchstamp',
609											  'direction' => 'DESC',
610											  'autoadded' => true,
611											  'friendlyname' => null);
612						break;
613					} # case 'watch'
614					case 'seen' 	  : {
615						$additionalFields[] = '1 AS myseenspot';
616						$tmpFilterValue = ' (l.seen IS NOT NULL)'; 	
617						$sortFields[] = array('field' => 'seenstamp',
618											  'direction' => 'DESC',
619											  'autoadded' => true,
620											  'friendlyname' => null);
621						break;
622					} # case 'seen'
623				} # switch
624				
625				# append the created query string to be an AND filter
626				$filterValueSql['AND'][] = $tmpFilterValue;
627			} else {
628				/*
629				 * No FTS, no dummyfield, it must be some sort of comparison then.
630				 *
631				 * First we want to extract the field we are filtering on.
632				 */
633				if ($tmpFilterFieldname == 'date') {
634					$tmpFilterValue = date("U",  strtotime($tmpFilterValue));
635				} elseif ($tmpFilterFieldname == 'stamp') {
636					$tmpFilterValue = (int) $tmpFilterValue;
637				} elseif (($tmpFilterFieldname == 'filesize') && (is_numeric($tmpFilterValue) === false)) {
638					# Explicitly cast to float to workaroun a rounding bug in PHP on x86
639					$val = (float) trim(substr($tmpFilterValue, 0, -1));
640					$last = strtolower($tmpFilterValue[strlen($tmpFilterValue) - 1]);
641					switch($last) {
642						case 'g': $val *= (float) 1024;
643						case 'm': $val *= (float) 1024;
644						case 'k': $val *= (float) 1024;
645					} # switch
646					$tmpFilterValue = round($val, 0);
647				} # if
648					
649				/*
650				 * add quotes around it when not numeric. We cannot blankly always add quotes
651				 * as postgresql doesn't like that of course
652				 */
653				if (!is_numeric($tmpFilterValue)) {
654					$tmpFilterValue = "'" . $this->_dbEng->safe($tmpFilterValue) . "'";
655				} else {
656					$tmpFilterValue = $this->_dbEng->safe($tmpFilterValue);
657				} # if
658
659				# depending on the type of search, we either add the filter as an AND or an OR
660				if (in_array($tmpFilterFieldname, array('spotterid', 'userid'))) {
661					$filterValueSql['OR'][] = ' (' . $filterFieldMapping[$tmpFilterFieldname] . ' ' . $tmpFilterOperator . ' '  . $tmpFilterValue . ') ';
662				} else {
663					$filterValueSql['AND'][] = ' (' . $filterFieldMapping[$tmpFilterFieldname] . ' ' . $tmpFilterOperator . ' '  . $tmpFilterValue . ') ';
664				} # else
665			} # if
666		} # foreach
667
668		/*
669		 * When all filters are processed, we want to check wether we actually
670		 * have to process any of the $textSearchFields for which we could run
671		 * the db specific FTS engine.
672		 *
673		 * If so, ask the FTS engin to process the query.
674		 */
675		if (!empty($textSearchFields)) {
676			/*
677 			 * We group searches per search type, but this means
678			 * we need to pass several the total amount of added
679			 * fields to the FTS so it can create unique relevancy
680			 * field names.
681			 * 
682			 * For example: search for poster AA and Title BB
683			 */
684			foreach($textSearchFields as $searchField => $searches) {
685				$ftsEng = dbfts_abs::Factory($this->_dbEng);
686				$parsedTextQueryResult = $ftsEng->createTextQuery($searches, $additionalFields);
687
688				if (in_array($searches[0]['fieldname'], array('s.poster', 's.tag', 's.title'))) {
689					$filterValueSql['AND'][] = ' (' . implode(' OR ', $parsedTextQueryResult['filterValueSql']) . ') ';
690				} else {
691					$filterValueSql['AND'][] = ' (' . implode(' AND ', $parsedTextQueryResult['filterValueSql']) . ') ';
692				} # if
693
694				$additionalTables = array_merge($additionalTables, $parsedTextQueryResult['additionalTables']);
695				$additionalFields = array_merge($additionalFields, $parsedTextQueryResult['additionalFields']);
696				$sortFields = array_merge($sortFields, $parsedTextQueryResult['sortFields']);
697			} # foreach
698		} # if
699
700		
701		SpotTiming::stop(__FUNCTION__, array($filterValueSql, $additionalFields, $additionalTables, $additionalJoins, $sortFields));
702
703		return array($filterValueSql, $additionalFields, $additionalTables, $additionalJoins, $sortFields);
704	} # filterValuesToSql
705
706	/*
707	 * Converts the sorting as asked to an intermediate format ready for processing
708	 */
709	private function prepareSortFields($sort, $sortFields) {
710		SpotTiming::start(__FUNCTION__);
711		$VALID_SORT_FIELDS = array('category' => 1, 
712								   'poster' => 1, 
713								   'title' => 1, 
714								   'filesize' => 1, 
715								   'stamp' => 1, 
716								   'subcata' => 1, 
717								   'spotrating' => 1, 
718								   'commentcount' => 1);
719
720		if ((!isset($sort['field'])) || (!isset($VALID_SORT_FIELDS[$sort['field']]))) {
721			/*
722			 * Add an extra sort on stamp. It might be that a FTS engine or something else,
723			 * has added a requested sorting as well, so make sure we add it to the end of
724			 * sortfields.
725			 */
726			$sortFields[] = array('field' => 's.stamp', 'direction' => 'DESC', 'autoadded' => true, 'friendlyname' => null);
727		} else {
728			if (strtoupper($sort['direction']) != 'ASC') {
729				$sort['direction'] = 'DESC';
730			} # if
731
732			/*
733			 * Explicit requested sorts, are prepended to the beginning of the array, so
734			 * the user requested sorting always is preferred above any other sorting
735			 */			
736			array_unshift($sortFields, array('field' => 's.' . $sort['field'], 
737											 'direction' => $sort['direction'], 
738											 'autoadded' => false, 
739											 'friendlyname' => $sort['field']));
740		} # else
741		
742		SpotTiming::stop(__FUNCTION__, array($sortFields));
743		return $sortFields;
744	} # prepareSortFields
745	
746	
747	/*
748	 * "Compresses" an expanded category list. It tries to search for the smallest
749	 * (in string length) match which contains the same information.
750	 *
751	 * This function, for example, will translate cat0_z0_a1,cat0_z0_a2,... to a 
752	 * simple cat0_z0_a string and other nifty tricks.
753	 *
754	 * This is wanted to get cleaner urls, to be more efficient when parsing and
755	 * to be able to lessen the change we will hit the GET HTTP url limit.
756	 *
757	 */
758	public function compressCategorySelection($categoryList, $strongNotList) {
759		SpotTiming::start(__FUNCTION__);
760		$compressedList = '';
761
762		/*
763		 * We process each category, and the matching subcategories, to make sure all
764		 * required elments are set. If so, we remove the individual elements and
765		 * add the shorthand for it.
766		 */
767		foreach(SpotCategories::$_head_categories as $headCatNumber => $headCatValue) {
768			$subcatsMissing = array();
769
770			# match each subcategory
771			if (isset($categoryList['cat'][$headCatNumber])) {
772				$subcatsMissing[$headCatNumber] = array();
773
774				foreach($categoryList['cat'][$headCatNumber] as $subCatType => $subCatValues) {
775					$subcatsMissing[$headCatNumber][$subCatType] = array();
776	
777					foreach(SpotCategories::$_categories[$headCatNumber] as $subCat => $subcatValues) {
778						if ($subCat !== 'z') {
779							if (isset($categoryList['cat'][$headCatNumber][$subCatType][$subCat])) {
780								# process all subcategory values to see if any are missing
781								foreach(SpotCategories::$_categories[$headCatNumber][$subCat] as $subcatValue => $subcatDescription) {
782									# Make sure the subcategory is actually avaialble for this type
783									if (in_array($subCatType, $subcatDescription[2])) {
784										# and if the subcat element is missing, add it to the missing list
785										if (array_search($subcatValue, $categoryList['cat'][$headCatNumber][$subCatType][$subCat]) === false) {
786											$subcatsMissing[$headCatNumber][$subCatType][$subCat][$subcatValue] = 1;
787										} # if
788									} # if
789								} # foreach
790							} else {
791								// $subcatsMissing[$headCatNumber][$subCatType][$subCat] = array();
792							} # if
793						} # if
794					} # foreach
795					
796				} # foreach
797
798//var_dump($categoryList);
799//var_dump(expression)($subcatsMissing);
800//die();
801
802				/*
803				 * If not the complete headcategory has been selected, we have to
804				 * do a tiny bit more work to get the exact match
805				 */
806				if (!empty($subcatsMissing[$headCatNumber])) {
807					/*
808					 * There are three possible situations:
809					 *
810					 * - the subcategory does not exist at all, we select the complete subcategory
811					 * - the subcategory exists, but is empty. It means we do not want anything out of it
812					 * - the subcategory exists, and is not empty. The items in it, are the items we do not want
813					 */
814					foreach($categoryList['cat'][$headCatNumber] as $subType => $subTypeValue) {
815						/*
816						 * Check wether the complete headcat+subtype (cat0_z0, cat0_z1) is selected
817						 */
818						if (!empty($subcatsMissing[$headCatNumber][$subType])) {
819							foreach(SpotCategories::$_subcat_descriptions[$headCatNumber] as $subCatKey => $subCatValue) {
820								if ($subCatKey !== 'z') {
821									if (!isset($subcatsMissing[$headCatNumber][$subType][$subCatKey])) {
822										// $compressedList .= 'cat' . $headCatNumber . '_' . $subType . '_' . $subCatKey . ',';
823									} elseif (empty($subcatsMissing[$headCatNumber][$subType][$subCatKey])) {
824										/*
825										 * If the subcategory is completely empty, the user doesn't
826										 * want anything from it
827										 */
828									} else {
829										/*
830										 * The subcategory does exist, but contains only items
831										 * the user doesn't want or need. We deselected them here.
832										 *
833										 * We can either add the whole category, and add a few 
834										 * "NOT"'s (!cat0_z0_a1) or just selected the individual 
835										 * items. We determine this whether the majority is 
836										 * selected or excluded.
837										 */
838										$moreFalseThanTrue = (count(@$subcatsMissing[$headCatNumber][$subType][$subCatKey]) > (count(@SpotCategories::$_categories[$headCatNumber][$subCatKey][$subCatValue]) / 2));
839										foreach(SpotCategories::$_categories[$headCatNumber][$subCatKey] as $subCatValue => $subCatDesc) {
840											if (in_array($subType, $subCatDesc[2])) {
841												if ($moreFalseThanTrue) {
842													if (!isset($subcatsMissing[$headCatNumber][$subType][$subCatKey][$subCatValue])) {
843														$compressedList .= 'cat' . $headCatNumber . '_' . $subType . '_' . $subCatKey . $subCatValue . ',';
844													} # if
845												} else {
846													if (isset($subcatsMissing[$headCatNumber][$subType][$subCatKey][$subCatValue])) {
847														/*
848														 * We have to make sure the whole category is selected, so we perform an
849														 * extra check for it
850														 */
851														if (strpos(',' . $compressedList . ',', ',cat' . $headCatNumber . '_' . $subType . '_' . $subCatKey . ',') === false) {
852															$compressedList .= 'cat' . $headCatNumber . '_' . $subType . '_' . $subCatKey . ',';
853														} # if
854														
855														# and start deselecting the subcategories
856														$compressedList .= '!cat' . $headCatNumber . '_' . $subType . '_' . $subCatKey . $subCatValue . ',';
857													} # if
858												} # if
859											} # if
860										} # foreach
861									} # else
862								} # if
863								
864							} # foreach
865						} else {
866							$compressedList .= 'cat' . $headCatNumber . '_' . $subType . ',';
867						} # if
868					} # foreach
869				} else {
870					$compressedList .= 'cat' . $headCatNumber . ',';
871				} # else
872			} # if
873		} # foreach
874
875		# and of course, add the strong not list
876		if (!empty($strongNotList)) {
877			foreach($strongNotList as $headCat => $subcatList) {
878				foreach($subcatList as $subcatValue) {
879					$compressedList .= '~cat' . $headCat . '_' . $subcatValue . ',';
880				} # foreach
881			} # foreach
882		} # if
883
884		SpotTiming::stop(__FUNCTION__, array($compressedList));
885
886		return $compressedList;
887	} # compressCategorySelection
888
889	/*
890	 * Converts an array with search terms (tree, type, valus) to an SQL statement
891	 * to be glued to an SQL WHERE query
892	 */
893	public function filterToQuery($search, $sort, $currentSession, $indexFilter) {
894		SpotTiming::start(__FUNCTION__);
895		
896		$isUnfiltered = false;
897		
898		$categoryList = array();
899		$categorySql = array();
900		
901		$strongNotList = array();
902		$strongNotSql = array();
903		
904		$filterValueList = array();
905		$filterValueSql = array();
906		
907		$additionalFields = array();
908		$additionalTables = array();
909		$additionalJoins = array();
910		$sortFields = array();
911		
912		# Take the easy way out of no filters have been given
913		if (empty($search)) {
914			return array('filter' => '',
915						 'search' => array(),
916					     'additionalFields' => array(),
917						 'additionalTables' => array(),
918						 'additionalJoins' => array(),
919						 'categoryList' => array(),
920						 'strongNotList' => array(),
921					     'filterValueList' => array(),
922						 'unfiltered' => false,
923					     'sortFields' => array(array('field' => 'stamp', 'direction' => 'DESC', 'autoadded' => true, 'friendlyname' => null)));
924		} # if
925
926		/*
927		 * Process the parameters in $search, legacy parameters are converted
928		 * to a common format by prepareFilterValues, this list is then
929		 * converted to SQL
930		 */
931		$filterValueList = $this->prepareFilterValues($search);
932		list($filterValueSql, $additionalFields, $additionalTables, $additionalJoins, $sortFields) = $this->filterValuesToSql($filterValueList, $currentSession);
933
934		/*
935		 * When asked to forget all category filters (and only search for a word/typefilter)
936		 * we simply reset the filter by overwriting $search with $indexfilter
937		 */
938		if ((isset($search['unfiltered'])) && (($search['unfiltered'] === 'true'))) {
939			$search = array_merge($search, $indexFilter);
940			$isUnfiltered = true;
941		} # if
942
943		/*
944		 * If a tree was given, convert it to subcategories etc. 
945		 * prepareCategorySelection() makes sure all categories eventually
946		 * are in a common format
947		 */		
948		if (!empty($search['tree'])) {
949			# explode the dynaList
950			$dynaList = explode(',', $search['tree']);
951			list($categoryList, $strongNotList) = $this->prepareCategorySelection($dynaList);
952
953			# and convert to SQL
954			$categorySql = $this->categoryListToSql($categoryList);
955			$strongNotSql = $this->strongNotListToSql($strongNotList);
956		} # if
957
958		# Check for an explicit sorting convention
959		$sortFields = $this->prepareSortFields($sort, $sortFields);
960
961		$endFilter = array();
962		if (!empty($categorySql)) { 
963			$endFilter[] = '(' . join(' OR ', $categorySql) . ') ';
964		} # if
965		if (!empty($filterValueSql['AND'])) {
966			$endFilter[] = '(' . join(' AND ', $filterValueSql['AND']) . ') ';
967		} # if
968		if (!empty($filterValueSql['OR'])) {
969			$endFilter[] = '(' . join(' OR ', $filterValueSql['OR']) . ') ';
970		} # if
971		$endFilter[] = join(' AND ', $strongNotSql);
972		$endFilter = array_filter($endFilter);
973
974		SpotTiming::stop(__FUNCTION__, array(join(" AND ", $endFilter)));
975		return array('filter' => join(" AND ", $endFilter),
976					 'categoryList' => $categoryList,
977					 'unfiltered' => $isUnfiltered,
978					 'strongNotList' => $strongNotList,
979					 'filterValueList' => $filterValueList,
980					 'additionalFields' => $additionalFields,
981					 'additionalTables' => $additionalTables,
982					 'additionalJoins' => $additionalJoins,
983					 'sortFields' => $sortFields);
984	} # filterToQuery
985
986} # Services_Search_QueryParser