3 # FILE: SPTSearchEngine.php
5 # Part of the Collection Workflow Integration System (CWIS)
6 # Copyright 2011 Edward Almasy and Internet Scout Project
7 # http://scout.wisc.edu/
14 # create a database handle
17 # pass database handle and config values to real search engine object
20 # for each field defined in schema
22 $Fields = $this->Schema->GetFields();
23 foreach ($Fields as $Field)
25 # determine field type for searching
26 switch ($Field->Type())
37 $FieldType = self::FIELDTYPE_TEXT;
42 $FieldType = self::FIELDTYPE_NUMERIC;
46 $FieldType = self::FIELDTYPE_DATERANGE;
50 $FieldType = self::FIELDTYPE_DATE;
58 exit(
"ERROR: unknown field type "
59 .$Field->Type().
" in SPTSearchEngine.php");
63 if ($FieldType !== NULL)
65 # add field to search engine
66 $this->
AddField($Field->Name(), $Field->DBFieldName(), $FieldType,
67 $Field->SearchWeight(), $Field->IncludeInKeywordSearch());
72 # overloaded version of method to retrieve text from DB
78 # retrieve text (including variants) from resource object and return to caller
79 return $Resource->Get($FieldName, FALSE, TRUE);
82 # overloaded version of method to retrieve resource/phrase match list
85 # normalize and escape search phrase for use in SQL query
86 $SearchPhrase = strtolower(addslashes($Phrase));
88 # query DB for matching list based on field type
89 $Field = $this->Schema->GetFieldByName($FieldName);
90 switch ($Field->Type())
96 $QueryString =
"SELECT DISTINCT ResourceId FROM Resources "
97 .
"WHERE POSITION('".$SearchPhrase.
"'"
98 .
" IN LOWER(`".$Field->DBFieldName().
"`)) ";
102 $QueryString =
"SELECT DISTINCT ResourceId FROM Resources "
103 .
"WHERE POSITION('".$SearchPhrase.
"'"
104 .
" IN LOWER(`".$Field->DBFieldName().
"AltText`)) ";
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();
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();
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();
143 $UserId = $this->DB->Query(
"SELECT UserId FROM APUsers "
144 .
"WHERE POSITION('".$SearchPhrase.
"' IN LOWER(UserName)) "
145 .
"OR POSITION('".$SearchPhrase.
"' IN LOWER(RealName))",
"UserId");
148 $QueryString =
"SELECT DISTINCT ResourceId FROM Resources "
149 .
"WHERE `".$Field->DBFieldName().
"` = ".$UserId;
154 if ($SearchPhrase > 0)
156 $QueryString =
"SELECT DISTINCT ResourceId FROM Resources "
157 .
"WHERE `".$Field->DBFieldName().
"` = ".(int)$SearchPhrase;
164 # (these types not yet handled by search engine for phrases)
168 # build match list based on results returned from DB
169 if (isset($QueryString))
171 $this->
DMsg(7,
"Performing phrase search query (<i>".$QueryString.
"</i>)");
172 if ($this->
DebugLevel > 9) { $StartTime = microtime(TRUE); }
173 $this->DB->Query($QueryString);
176 $EndTime = microtime(TRUE);
177 if (($StartTime - $EndTime) > 0.1)
179 printf(
"SE: Query took %.2f seconds<br>\n",
180 ($EndTime - $StartTime));
183 $MatchList = $this->DB->FetchColumn(
"ResourceId");
184 if (isset($SecondQueryString))
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);
192 $EndTime = microtime(TRUE);
193 if (($StartTime - $EndTime) > 0.1)
195 printf(
"SE: query took %.2f seconds<br>\n",
196 ($EndTime - $StartTime));
199 $MatchList = $MatchList + $this->DB->FetchColumn(
"ResourceId");
204 $MatchList = array();
207 # return list of matching resources to caller
211 # search field for records that meet comparison
214 # use SQL keyword appropriate to current search logic for combining operations
217 # for each comparison
218 foreach ($FieldNames as $Index => $FieldName)
220 $Operator = $Operators[$Index];
221 $Value = $Values[$Index];
223 # determine query based on field type
224 $Field = $this->Schema->GetFieldByName($FieldName);
227 switch ($Field->Type())
235 if (isset($Queries[
"Resources"]))
237 $Queries[
"Resources"] .= $CombineWord;
241 $Queries[
"Resources"] =
"SELECT DISTINCT ResourceId FROM Resources WHERE ";
246 $Value = $User->Id();
248 $Queries[
"Resources"] .=
"`".$Field->DBFieldName().
"` ".$Operator.
" '".addslashes($Value).
"' ";
252 $QueryIndex =
"ResourceNameInts".$Field->Id();
253 if (!isset($Queries[$QueryIndex][
"A"]))
255 $Queries[$QueryIndex][
"A"] =
256 "SELECT DISTINCT ResourceId"
257 .
" FROM ResourceNameInts, ControlledNames "
258 .
" WHERE ControlledNames.FieldId = ".$Field->Id()
260 $CloseQuery[$QueryIndex][
"A"] = TRUE;
264 $Queries[$QueryIndex][
"A"] .= $CombineWord;
266 $Queries[$QueryIndex][
"A"] .=
267 "((ResourceNameInts.ControlledNameId"
268 .
" = ControlledNames.ControlledNameId"
269 .
" AND ControlledName "
270 .$Operator.
" '".addslashes($Value).
"'))";
271 if (!isset($Queries[$QueryIndex][
"B"]))
273 $Queries[$QueryIndex][
"B"] =
274 "SELECT DISTINCT ResourceId"
275 .
" FROM ResourceNameInts, ControlledNames,"
277 .
" WHERE ControlledNames.FieldId = ".$Field->Id()
279 $CloseQuery[$QueryIndex][
"B"] = TRUE;
283 $Queries[$QueryIndex][
"B"] .= $CombineWord;
285 $Queries[$QueryIndex][
"B"] .=
286 "((ResourceNameInts.ControlledNameId"
287 .
" = ControlledNames.ControlledNameId"
288 .
" AND ResourceNameInts.ControlledNameId"
289 .
" = VariantNames.ControlledNameId"
291 .$Operator.
" '".addslashes($Value).
"'))";
295 $QueryIndex =
"ResourceNameInts".$Field->Id();
296 if (!isset($Queries[$QueryIndex]))
298 $Queries[$QueryIndex] =
299 "SELECT DISTINCT ResourceId FROM ResourceNameInts, ControlledNames "
300 .
" WHERE ControlledNames.FieldId = ".$Field->Id()
302 $CloseQuery[$QueryIndex] = TRUE;
306 $Queries[$QueryIndex] .= $CombineWord;
308 $Queries[$QueryIndex] .=
"(ResourceNameInts.ControlledNameId = ControlledNames.ControlledNameId"
309 .
" AND ControlledName ".$Operator.
" '".addslashes($Value).
"')";
313 $QueryIndex =
"ResourceClassInts".$Field->Id();
314 if (!isset($Queries[$QueryIndex]))
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;
323 $Queries[$QueryIndex] .= $CombineWord;
325 $Queries[$QueryIndex] .=
" ClassificationName ".$Operator.
" '".addslashes($Value).
"'";
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"))
338 if (isset($Queries[
"Resources"]))
340 $Queries[
"Resources"] .= $CombineWord;
344 $Queries[
"Resources"] =
"SELECT DISTINCT ResourceId"
345 .
" FROM Resources WHERE ";
348 # flip operator if necessary
349 if (strstr($Value,
"ago"))
351 $OperatorFlipMap = array(
357 $Operator = isset($OperatorFlipMap[$Operator])
358 ? $OperatorFlipMap[$Operator] : $Operator;
361 # use strtotime method to build condition
362 $TimestampValue = strtotime($Value);
363 if (($TimestampValue !== FALSE) && ($TimestampValue != -1))
365 if ((date(
"H:i:s", $TimestampValue) ==
"00:00:00")
366 && (strpos($Value,
"00:00") === FALSE)
367 && ($Operator ==
"<="))
370 date(
"Y-m-d", $TimestampValue).
" 23:59:59";
374 $NormalizedValue = date(
"Y-m-d H:i:s", $TimestampValue);
379 $NormalizedValue = addslashes($Value);
381 $Queries[
"Resources"] .=
382 " ( `".$Field->DBFieldName().
"` "
384 .
" '".$NormalizedValue.
"' ) ";
388 # use Date object method to build condition
389 $Date =
new Date($Value);
390 if ($Date->Precision())
392 if (isset($Queries[
"Resources"]))
394 $Queries[
"Resources"] .= $CombineWord;
398 $Queries[
"Resources"] =
"SELECT DISTINCT ResourceId"
399 .
" FROM Resources WHERE ";
401 $Queries[
"Resources"] .=
" ( ".$Date->SqlCondition(
402 $Field->DBFieldName(), NULL, $Operator).
" ) ";
408 $Date =
new Date($Value);
409 if ($Date->Precision())
411 if (isset($Queries[
"Resources"]))
413 $Queries[
"Resources"] .= $CombineWord;
417 $Queries[
"Resources"] =
"SELECT DISTINCT ResourceId"
418 .
" FROM Resources WHERE ";
420 $Queries[
"Resources"] .=
" ( ".$Date->SqlCondition(
421 $Field->DBFieldName().
"Begin",
422 $Field->DBFieldName().
"End", $Operator).
" ) ";
428 # (these types not yet handled by search engine for comparisons)
437 # for each assembled query
438 foreach ($Queries as $QueryIndex => $Query)
440 # if query has multiple parts
441 if (is_array($Query))
443 # for each part of query
444 $ResourceIds = array();
445 foreach ($Query as $PartIndex => $PartQuery)
447 # add closing paren if query was flagged to be closed
448 if (isset($CloseQuery[$QueryIndex][$PartIndex]))
449 { $PartQuery .=
" ) "; }
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");
463 # add closing paren if query was flagged to be closed
464 if (isset($CloseQuery[$QueryIndex])) { $Query .=
" ) "; }
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");
474 # if we already have some results
477 # if search logic is set to AND
480 # remove anything from results that was not returned from query
481 $Results = array_intersect($Results, $ResourceIds);
485 # add values returned from query to results
486 $Results = array_unique(array_merge($Results, $ResourceIds));
491 # set results to values returned from query
492 $Results = $ResourceIds;
498 # initialize results to empty list
502 # return results to caller
509 return $RFactory->GetResourceIdsSortedBy($FieldName, !$SortDescending);
516 $AF->QueueUniqueTask(array(__CLASS__,
"RunUpdateForItem"),
517 array(intval($ItemId)), $Priority);
522 # check that resource still exists
524 if (!$RFactory->ItemExists($ItemId)) {
return; }
526 # update search data for resource
528 $SearchEngine->UpdateForItem($ItemId);
533 # functions for backward compatability w/ old SPT code