CWIS Developer Documentation
SPTSearchEngine.php
Go to the documentation of this file.
1 <?PHP
2 #
3 # FILE: SPTSearchEngine.php
4 #
5 # Part of the Collection Workflow Integration System (CWIS)
6 # Copyright 2011 Edward Almasy and Internet Scout Project
7 # http://scout.wisc.edu/
8 #
9 
11 
12  function SPTSearchEngine()
13  {
14  # create a database handle
15  $DB = new Database();
16 
17  # pass database handle and config values to real search engine object
18  $this->SearchEngine($DB, "Resources", "ResourceId");
19 
20  # for each field defined in schema
21  $this->Schema = new MetadataSchema();
22  $Fields = $this->Schema->GetFields();
23  foreach ($Fields as $Field)
24  {
25  # determine field type for searching
26  switch ($Field->Type())
27  {
37  $FieldType = self::FIELDTYPE_TEXT;
38  break;
39 
42  $FieldType = self::FIELDTYPE_NUMERIC;
43  break;
44 
46  $FieldType = self::FIELDTYPE_DATERANGE;
47  break;
48 
50  $FieldType = self::FIELDTYPE_DATE;
51  break;
52 
54  $FieldType = NULL;
55  break;
56 
57  default:
58  exit("ERROR: unknown field type "
59  .$Field->Type()." in SPTSearchEngine.php");
60  break;
61  }
62 
63  if ($FieldType !== NULL)
64  {
65  # add field to search engine
66  $this->AddField($Field->Name(), $Field->DBFieldName(), $FieldType,
67  $Field->SearchWeight(), $Field->IncludeInKeywordSearch());
68  }
69  }
70  }
71 
72  # overloaded version of method to retrieve text from DB
73  function GetFieldContent($ItemId, $FieldName)
74  {
75  # get resource object
76  $Resource = new Resource($ItemId);
77 
78  # retrieve text (including variants) from resource object and return to caller
79  return $Resource->Get($FieldName, FALSE, TRUE);
80  }
81 
82  # overloaded version of method to retrieve resource/phrase match list
83  function SearchFieldForPhrases($FieldName, $Phrase)
84  {
85  # normalize and escape search phrase for use in SQL query
86  $SearchPhrase = strtolower(addslashes($Phrase));
87 
88  # query DB for matching list based on field type
89  $Field = $this->Schema->GetFieldByName($FieldName);
90  switch ($Field->Type())
91  {
96  $QueryString = "SELECT DISTINCT ResourceId FROM Resources "
97  ."WHERE POSITION('".$SearchPhrase."'"
98  ." IN LOWER(`".$Field->DBFieldName()."`)) ";
99  break;
100 
102  $QueryString = "SELECT DISTINCT ResourceId FROM Resources "
103  ."WHERE POSITION('".$SearchPhrase."'"
104  ." IN LOWER(`".$Field->DBFieldName()."AltText`)) ";
105  break;
106 
108  $NameTableSize = $this->DB->Query("SELECT COUNT(*) AS NameCount"
109  ." FROM ControlledNames", "NameCount");
110  $QueryString = "SELECT DISTINCT ResourceNameInts.ResourceId "
111  ."FROM ResourceNameInts, ControlledNames "
112  ."WHERE POSITION('".$SearchPhrase."' IN LOWER(ControlledName)) "
113  ."AND ControlledNames.ControlledNameId"
114  ." = ResourceNameInts.ControlledNameId "
115  ."AND ControlledNames.FieldId = ".$Field->Id();
116  $SecondQueryString = "SELECT DISTINCT ResourceNameInts.ResourceId "
117  ."FROM ResourceNameInts, ControlledNames, VariantNames "
118  ."WHERE POSITION('".$SearchPhrase."' IN LOWER(VariantName)) "
119  ."AND VariantNames.ControlledNameId"
120  ." = ResourceNameInts.ControlledNameId "
121  ."AND ControlledNames.ControlledNameId"
122  ." = ResourceNameInts.ControlledNameId "
123  ."AND ControlledNames.FieldId = ".$Field->Id();
124  break;
125 
127  $QueryString = "SELECT DISTINCT ResourceNameInts.ResourceId "
128  ."FROM ResourceNameInts, ControlledNames "
129  ."WHERE POSITION('".$SearchPhrase."' IN LOWER(ControlledName)) "
130  ."AND ControlledNames.ControlledNameId = ResourceNameInts.ControlledNameId "
131  ."AND ControlledNames.FieldId = ".$Field->Id();
132  break;
133 
135  $QueryString = "SELECT DISTINCT ResourceClassInts.ResourceId "
136  ."FROM ResourceClassInts, Classifications "
137  ."WHERE POSITION('".$SearchPhrase."' IN LOWER(ClassificationName)) "
138  ."AND Classifications.ClassificationId = ResourceClassInts.ClassificationId "
139  ."AND Classifications.FieldId = ".$Field->Id();
140  break;
141 
143  $UserId = $this->DB->Query("SELECT UserId FROM APUsers "
144  ."WHERE POSITION('".$SearchPhrase."' IN LOWER(UserName)) "
145  ."OR POSITION('".$SearchPhrase."' IN LOWER(RealName))", "UserId");
146  if ($UserId != NULL)
147  {
148  $QueryString = "SELECT DISTINCT ResourceId FROM Resources "
149  ."WHERE `".$Field->DBFieldName()."` = ".$UserId;
150  }
151  break;
152 
154  if ($SearchPhrase > 0)
155  {
156  $QueryString = "SELECT DISTINCT ResourceId FROM Resources "
157  ."WHERE `".$Field->DBFieldName()."` = ".(int)$SearchPhrase;
158  }
159  break;
160 
164  # (these types not yet handled by search engine for phrases)
165  break;
166  }
167 
168  # build match list based on results returned from DB
169  if (isset($QueryString))
170  {
171  $this->DMsg(7, "Performing phrase search query (<i>".$QueryString."</i>)");
172  if ($this->DebugLevel > 9) { $StartTime = microtime(TRUE); }
173  $this->DB->Query($QueryString);
174  if ($this->DebugLevel > 9)
175  {
176  $EndTime = microtime(TRUE);
177  if (($StartTime - $EndTime) > 0.1)
178  {
179  printf("SE: Query took %.2f seconds<br>\n",
180  ($EndTime - $StartTime));
181  }
182  }
183  $MatchList = $this->DB->FetchColumn("ResourceId");
184  if (isset($SecondQueryString))
185  {
186  $this->DMsg(7, "Performing second phrase search query"
187  ." (<i>".$SecondQueryString."</i>)");
188  if ($this->DebugLevel > 9) { $StartTime = microtime(TRUE); }
189  $this->DB->Query($SecondQueryString);
190  if ($this->DebugLevel > 9)
191  {
192  $EndTime = microtime(TRUE);
193  if (($StartTime - $EndTime) > 0.1)
194  {
195  printf("SE: query took %.2f seconds<br>\n",
196  ($EndTime - $StartTime));
197  }
198  }
199  $MatchList = $MatchList + $this->DB->FetchColumn("ResourceId");
200  }
201  }
202  else
203  {
204  $MatchList = array();
205  }
206 
207  # return list of matching resources to caller
208  return $MatchList;
209  }
210 
211  # search field for records that meet comparison
212  function SearchFieldsForComparisonMatches($FieldNames, $Operators, $Values)
213  {
214  # use SQL keyword appropriate to current search logic for combining operations
215  $CombineWord = ($this->DefaultSearchLogic == self::LOGIC_AND) ? " AND " : " OR ";
216 
217  # for each comparison
218  foreach ($FieldNames as $Index => $FieldName)
219  {
220  $Operator = $Operators[$Index];
221  $Value = $Values[$Index];
222 
223  # determine query based on field type
224  $Field = $this->Schema->GetFieldByName($FieldName);
225  if ($Field != NULL)
226  {
227  switch ($Field->Type())
228  {
235  if (isset($Queries["Resources"]))
236  {
237  $Queries["Resources"] .= $CombineWord;
238  }
239  else
240  {
241  $Queries["Resources"] = "SELECT DISTINCT ResourceId FROM Resources WHERE ";
242  }
243  if ($Field->Type() == MetadataSchema::MDFTYPE_USER)
244  {
245  $User = new SPTUser($Value);
246  $Value = $User->Id();
247  }
248  $Queries["Resources"] .= "`".$Field->DBFieldName()."` ".$Operator." '".addslashes($Value)."' ";
249  break;
250 
252  $QueryIndex = "ResourceNameInts".$Field->Id();
253  if (!isset($Queries[$QueryIndex]["A"]))
254  {
255  $Queries[$QueryIndex]["A"] =
256  "SELECT DISTINCT ResourceId"
257  ." FROM ResourceNameInts, ControlledNames "
258  ." WHERE ControlledNames.FieldId = ".$Field->Id()
259  ." AND ( ";
260  $CloseQuery[$QueryIndex]["A"] = TRUE;
261  }
262  else
263  {
264  $Queries[$QueryIndex]["A"] .= $CombineWord;
265  }
266  $Queries[$QueryIndex]["A"] .=
267  "((ResourceNameInts.ControlledNameId"
268  ." = ControlledNames.ControlledNameId"
269  ." AND ControlledName "
270  .$Operator." '".addslashes($Value)."'))";
271  if (!isset($Queries[$QueryIndex]["B"]))
272  {
273  $Queries[$QueryIndex]["B"] =
274  "SELECT DISTINCT ResourceId"
275  . " FROM ResourceNameInts, ControlledNames,"
276  ." VariantNames "
277  ." WHERE ControlledNames.FieldId = ".$Field->Id()
278  ." AND ( ";
279  $CloseQuery[$QueryIndex]["B"] = TRUE;
280  }
281  else
282  {
283  $Queries[$QueryIndex]["B"] .= $CombineWord;
284  }
285  $Queries[$QueryIndex]["B"] .=
286  "((ResourceNameInts.ControlledNameId"
287  ." = ControlledNames.ControlledNameId"
288  ." AND ResourceNameInts.ControlledNameId"
289  ." = VariantNames.ControlledNameId"
290  ." AND VariantName "
291  .$Operator." '".addslashes($Value)."'))";
292  break;
293 
295  $QueryIndex = "ResourceNameInts".$Field->Id();
296  if (!isset($Queries[$QueryIndex]))
297  {
298  $Queries[$QueryIndex] =
299  "SELECT DISTINCT ResourceId FROM ResourceNameInts, ControlledNames "
300  ." WHERE ControlledNames.FieldId = ".$Field->Id()
301  ." AND ( ";
302  $CloseQuery[$QueryIndex] = TRUE;
303  }
304  else
305  {
306  $Queries[$QueryIndex] .= $CombineWord;
307  }
308  $Queries[$QueryIndex] .= "(ResourceNameInts.ControlledNameId = ControlledNames.ControlledNameId"
309  ." AND ControlledName ".$Operator." '".addslashes($Value)."')";
310  break;
311 
313  $QueryIndex = "ResourceClassInts".$Field->Id();
314  if (!isset($Queries[$QueryIndex]))
315  {
316  $Queries[$QueryIndex] = "SELECT DISTINCT ResourceId FROM ResourceClassInts, Classifications "
317  ." WHERE ResourceClassInts.ClassificationId = Classifications.ClassificationId"
318  ." AND Classifications.FieldId = ".$Field->Id()." AND ( ";
319  $CloseQuery[$QueryIndex] = TRUE;
320  }
321  else
322  {
323  $Queries[$QueryIndex] .= $CombineWord;
324  }
325  $Queries[$QueryIndex] .= " ClassificationName ".$Operator." '".addslashes($Value)."'";
326  break;
327 
329  # if value appears to have time component or text description
330  if (strpos($Value, ":")
331  || strstr($Value, "day")
332  || strstr($Value, "week")
333  || strstr($Value, "month")
334  || strstr($Value, "year")
335  || strstr($Value, "hour")
336  || strstr($Value, "minute"))
337  {
338  if (isset($Queries["Resources"]))
339  {
340  $Queries["Resources"] .= $CombineWord;
341  }
342  else
343  {
344  $Queries["Resources"] = "SELECT DISTINCT ResourceId"
345  ." FROM Resources WHERE ";
346  }
347 
348  # flip operator if necessary
349  if (strstr($Value, "ago"))
350  {
351  $OperatorFlipMap = array(
352  "<" => ">=",
353  ">" => "<=",
354  "<=" => ">",
355  ">=" => "<",
356  );
357  $Operator = isset($OperatorFlipMap[$Operator])
358  ? $OperatorFlipMap[$Operator] : $Operator;
359  }
360 
361  # use strtotime method to build condition
362  $TimestampValue = strtotime($Value);
363  if (($TimestampValue !== FALSE) && ($TimestampValue != -1))
364  {
365  if ((date("H:i:s", $TimestampValue) == "00:00:00")
366  && (strpos($Value, "00:00") === FALSE)
367  && ($Operator == "<="))
368  {
369  $NormalizedValue =
370  date("Y-m-d", $TimestampValue)." 23:59:59";
371  }
372  else
373  {
374  $NormalizedValue = date("Y-m-d H:i:s", $TimestampValue);
375  }
376  }
377  else
378  {
379  $NormalizedValue = addslashes($Value);
380  }
381  $Queries["Resources"] .=
382  " ( `".$Field->DBFieldName()."` "
383  .$Operator
384  ." '".$NormalizedValue."' ) ";
385  }
386  else
387  {
388  # use Date object method to build condition
389  $Date = new Date($Value);
390  if ($Date->Precision())
391  {
392  if (isset($Queries["Resources"]))
393  {
394  $Queries["Resources"] .= $CombineWord;
395  }
396  else
397  {
398  $Queries["Resources"] = "SELECT DISTINCT ResourceId"
399  ." FROM Resources WHERE ";
400  }
401  $Queries["Resources"] .= " ( ".$Date->SqlCondition(
402  $Field->DBFieldName(), NULL, $Operator)." ) ";
403  }
404  }
405  break;
406 
408  $Date = new Date($Value);
409  if ($Date->Precision())
410  {
411  if (isset($Queries["Resources"]))
412  {
413  $Queries["Resources"] .= $CombineWord;
414  }
415  else
416  {
417  $Queries["Resources"] = "SELECT DISTINCT ResourceId"
418  ." FROM Resources WHERE ";
419  }
420  $Queries["Resources"] .= " ( ".$Date->SqlCondition(
421  $Field->DBFieldName()."Begin",
422  $Field->DBFieldName()."End", $Operator)." ) ";
423  }
424  break;
425 
428  # (these types not yet handled by search engine for comparisons)
429  break;
430  }
431  }
432  }
433 
434  # if queries found
435  if (isset($Queries))
436  {
437  # for each assembled query
438  foreach ($Queries as $QueryIndex => $Query)
439  {
440  # if query has multiple parts
441  if (is_array($Query))
442  {
443  # for each part of query
444  $ResourceIds = array();
445  foreach ($Query as $PartIndex => $PartQuery)
446  {
447  # add closing paren if query was flagged to be closed
448  if (isset($CloseQuery[$QueryIndex][$PartIndex]))
449  { $PartQuery .= " ) "; }
450 
451  # perform query and retrieve IDs
452  $this->DMsg(5, "Performing comparison query (<i>"
453  .$PartQuery."</i>)");
454  $this->DB->Query($PartQuery);
455  $ResourceIds = $ResourceIds
456  + $this->DB->FetchColumn("ResourceId");
457  $this->DMsg(5, "Comparison query produced <i>"
458  .count($ResourceIds)."</i> results");
459  }
460  }
461  else
462  {
463  # add closing paren if query was flagged to be closed
464  if (isset($CloseQuery[$QueryIndex])) { $Query .= " ) "; }
465 
466  # perform query and retrieve IDs
467  $this->DMsg(5, "Performing comparison query (<i>".$Query."</i>)");
468  $this->DB->Query($Query);
469  $ResourceIds = $this->DB->FetchColumn("ResourceId");
470  $this->DMsg(5, "Comparison query produced <i>"
471  .count($ResourceIds)."</i> results");
472  }
473 
474  # if we already have some results
475  if (isset($Results))
476  {
477  # if search logic is set to AND
478  if ($this->DefaultSearchLogic == self::LOGIC_AND)
479  {
480  # remove anything from results that was not returned from query
481  $Results = array_intersect($Results, $ResourceIds);
482  }
483  else
484  {
485  # add values returned from query to results
486  $Results = array_unique(array_merge($Results, $ResourceIds));
487  }
488  }
489  else
490  {
491  # set results to values returned from query
492  $Results = $ResourceIds;
493  }
494  }
495  }
496  else
497  {
498  # initialize results to empty list
499  $Results = array();
500  }
501 
502  # return results to caller
503  return $Results;
504  }
505 
506  static function GetItemIdsSortedByField($FieldName, $SortDescending)
507  {
508  $RFactory = new ResourceFactory();
509  return $RFactory->GetResourceIdsSortedBy($FieldName, !$SortDescending);
510  }
511 
512  static function QueueUpdateForItem($ItemId,
514  {
515  global $AF;
516  $AF->QueueUniqueTask(array(__CLASS__, "RunUpdateForItem"),
517  array(intval($ItemId)), $Priority);
518  }
519 
520  static function RunUpdateForItem($ItemId)
521  {
522  # check that resource still exists
523  $RFactory = new ResourceFactory();
524  if (!$RFactory->ItemExists($ItemId)) { return; }
525 
526  # update search data for resource
527  $SearchEngine = new SPTSearchEngine();
528  $SearchEngine->UpdateForItem($ItemId);
529  }
530 
531  private $Schema;
532 
533  # functions for backward compatability w/ old SPT code
534  function UpdateForResource($ItemId) { $this->UpdateForItem($ItemId); }
535 }