CWIS Developer Documentation

- Home
- Main Page
- Namespaces
- Classes
- Files

- File List
- File Members

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
10 class SPTSearchEngine extends SearchEngine {
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 {
28 case MetadataSchema::MDFTYPE_TEXT:
29 case MetadataSchema::MDFTYPE_PARAGRAPH:
30 case MetadataSchema::MDFTYPE_USER:
31 case MetadataSchema::MDFTYPE_TREE:
32 case MetadataSchema::MDFTYPE_CONTROLLEDNAME:
33 case MetadataSchema::MDFTYPE_OPTION:
34 case MetadataSchema::MDFTYPE_IMAGE:
35 case MetadataSchema::MDFTYPE_FILE:
36 case MetadataSchema::MDFTYPE_URL:
37 $FieldType = self::FIELDTYPE_TEXT;
38 break;
39
40 case MetadataSchema::MDFTYPE_NUMBER:
41 case MetadataSchema::MDFTYPE_FLAG:
42 $FieldType = self::FIELDTYPE_NUMERIC;
43 break;
44
45 case MetadataSchema::MDFTYPE_DATE:
46 $FieldType = self::FIELDTYPE_DATERANGE;
47 break;
48
49 case MetadataSchema::MDFTYPE_TIMESTAMP:
50 $FieldType = self::FIELDTYPE_DATE;
51 break;
52
53 case MetadataSchema::MDFTYPE_POINT:
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 {
92 case MetadataSchema::MDFTYPE_TEXT:
93 case MetadataSchema::MDFTYPE_PARAGRAPH:
94 case MetadataSchema::MDFTYPE_FILE:
95 case MetadataSchema::MDFTYPE_URL:
96 $QueryString = "SELECT DISTINCT ResourceId FROM Resources "
97 ."WHERE POSITION('".$SearchPhrase."'"
98 ." IN LOWER(`".$Field->DBFieldName()."`)) ";
99 break;
100
101 case MetadataSchema::MDFTYPE_IMAGE:
102 $QueryString = "SELECT DISTINCT ResourceId FROM Resources "
103 ."WHERE POSITION('".$SearchPhrase."'"
104 ." IN LOWER(`".$Field->DBFieldName()."AltText`)) ";
105 break;
106
107 case MetadataSchema::MDFTYPE_CONTROLLEDNAME:
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
126 case MetadataSchema::MDFTYPE_OPTION:
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
134 case MetadataSchema::MDFTYPE_TREE:
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
142 case MetadataSchema::MDFTYPE_USER:
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
153 case MetadataSchema::MDFTYPE_NUMBER:
154 if ($SearchPhrase > 0)
155 {
156 $QueryString = "SELECT DISTINCT ResourceId FROM Resources "
157 ."WHERE `".$Field->DBFieldName()."` = ".(int)$SearchPhrase;
158 }
159 break;
160
161 case MetadataSchema::MDFTYPE_FLAG:
162 case MetadataSchema::MDFTYPE_DATE:
163 case MetadataSchema::MDFTYPE_TIMESTAMP:
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 {
229 case MetadataSchema::MDFTYPE_TEXT:
230 case MetadataSchema::MDFTYPE_PARAGRAPH:
231 case MetadataSchema::MDFTYPE_NUMBER:
232 case MetadataSchema::MDFTYPE_FLAG:
233 case MetadataSchema::MDFTYPE_USER:
234 case MetadataSchema::MDFTYPE_URL:
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
251 case MetadataSchema::MDFTYPE_CONTROLLEDNAME:
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
294 case MetadataSchema::MDFTYPE_OPTION:
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
312 case MetadataSchema::MDFTYPE_TREE:
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
328 case MetadataSchema::MDFTYPE_TIMESTAMP:
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
407 case MetadataSchema::MDFTYPE_DATE:
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
426 case MetadataSchema::MDFTYPE_IMAGE:
427 case MetadataSchema::MDFTYPE_FILE:
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,
513 $Priority = ApplicationFramework::PRIORITY_LOW)
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 }