00001 <?PHP 00002 00003 # 00004 # FILE: SPT--SearchEngine.php 00005 # 00006 # FUNCTIONS PROVIDED: 00007 # SPTSearchEngine->SPTSearchEngine() 00008 # - constructor 00009 # (see Scout--SearchEngine.php for other public methods) 00010 # 00011 # AUTHOR: Edward Almasy 00012 # 00013 # Part of the Scout Portal Toolkit 00014 # Copyright 2002-2004 Internet Scout Project 00015 # http://scout.wisc.edu 00016 # 00017 00018 00019 class SPTSearchEngine extends SearchEngine { 00020 00021 function SPTSearchEngine() 00022 { 00023 # create a database handle 00024 $DB = new SPTDatabase(); 00025 00026 # pass database handle and config values to real search engine object 00027 $this->SearchEngine($DB, "Resources", "ResourceId"); 00028 00029 # for each field defined in schema 00030 $this->Schema = new MetadataSchema(); 00031 $Fields = $this->Schema->GetFields(); 00032 foreach ($Fields as $Field) 00033 { 00034 # determine field type for searching 00035 switch ($Field->Type()) 00036 { 00037 case MetadataSchema::MDFTYPE_TEXT: 00038 case MetadataSchema::MDFTYPE_PARAGRAPH: 00039 case MetadataSchema::MDFTYPE_USER: 00040 case MetadataSchema::MDFTYPE_TREE: 00041 case MetadataSchema::MDFTYPE_CONTROLLEDNAME: 00042 case MetadataSchema::MDFTYPE_OPTION: 00043 case MetadataSchema::MDFTYPE_IMAGE: 00044 case MetadataSchema::MDFTYPE_FILE: 00045 case MetadataSchema::MDFTYPE_URL: 00046 $FieldType = SEARCHFIELD_TEXT; 00047 break; 00048 00049 case MetadataSchema::MDFTYPE_NUMBER: 00050 case MetadataSchema::MDFTYPE_FLAG: 00051 $FieldType = SEARCHFIELD_NUMERIC; 00052 break; 00053 00054 case MetadataSchema::MDFTYPE_DATE: 00055 $FieldType = SEARCHFIELD_DATERANGE; 00056 break; 00057 00058 case MetadataSchema::MDFTYPE_TIMESTAMP: 00059 $FieldType = SEARCHFIELD_DATE; 00060 break; 00061 00062 case MetadataSchema::MDFTYPE_POINT: 00063 $FieldType = NULL; 00064 break; 00065 00066 default: 00067 exit("ERROR: unknown field type in SPT--SearchEngine.php"); 00068 break; 00069 } 00070 00071 if ($FieldType !== NULL) 00072 { 00073 # add field to search engine 00074 $this->AddField($Field->Name(), $Field->DBFieldName(), $FieldType, 00075 $Field->SearchWeight(), $Field->IncludeInKeywordSearch()); 00076 } 00077 } 00078 } 00079 00080 # overloaded version of method to retrieve text from DB 00081 function GetFieldContent($ItemId, $FieldName) 00082 { 00083 # get resource object 00084 $Item = new Resource($ItemId); 00085 00086 # retrieve text (including variants) from resource object and return to caller 00087 return $Item->Get($FieldName, FALSE, TRUE); 00088 } 00089 00090 # overloaded version of method to retrieve resource/phrase match list 00091 function SearchFieldForPhrases($FieldName, $Phrase) 00092 { 00093 # normalize and escape search phrase for use in SQL query 00094 $SearchPhrase = strtolower(addslashes($Phrase)); 00095 00096 # query DB for matching list based on field type 00097 $Field = $this->Schema->GetFieldByName($FieldName); 00098 switch ($Field->Type()) 00099 { 00100 case MetadataSchema::MDFTYPE_TEXT: 00101 case MetadataSchema::MDFTYPE_PARAGRAPH: 00102 case MetadataSchema::MDFTYPE_FILE: 00103 case MetadataSchema::MDFTYPE_URL: 00104 $QueryString = "SELECT DISTINCT ResourceId FROM Resources " 00105 ."WHERE POSITION('".$SearchPhrase."'" 00106 ." IN LOWER(`".$Field->DBFieldName()."`)) "; 00107 break; 00108 00109 case MetadataSchema::MDFTYPE_IMAGE: 00110 $QueryString = "SELECT DISTINCT ResourceId FROM Resources " 00111 ."WHERE POSITION('".$SearchPhrase."'" 00112 ." IN LOWER(`".$Field->DBFieldName()."AltText`)) "; 00113 break; 00114 00115 case MetadataSchema::MDFTYPE_CONTROLLEDNAME: 00116 $NameTableSize = $this->DB->Query("SELECT COUNT(*) AS NameCount" 00117 ." FROM ControlledNames", "NameCount"); 00118 $QueryString = "SELECT DISTINCT ResourceNameInts.ResourceId " 00119 ."FROM ResourceNameInts, ControlledNames " 00120 ."WHERE POSITION('".$SearchPhrase."' IN LOWER(ControlledName)) " 00121 ."AND ControlledNames.ControlledNameId" 00122 ." = ResourceNameInts.ControlledNameId " 00123 ."AND ControlledNames.FieldId = ".$Field->Id(); 00124 $SecondQueryString = "SELECT DISTINCT ResourceNameInts.ResourceId " 00125 ."FROM ResourceNameInts, ControlledNames, VariantNames " 00126 ."WHERE POSITION('".$SearchPhrase."' IN LOWER(VariantName)) " 00127 ."AND VariantNames.ControlledNameId" 00128 ." = ResourceNameInts.ControlledNameId " 00129 ."AND ControlledNames.ControlledNameId" 00130 ." = ResourceNameInts.ControlledNameId " 00131 ."AND ControlledNames.FieldId = ".$Field->Id(); 00132 break; 00133 00134 case MetadataSchema::MDFTYPE_OPTION: 00135 $QueryString = "SELECT DISTINCT ResourceNameInts.ResourceId " 00136 ."FROM ResourceNameInts, ControlledNames " 00137 ."WHERE POSITION('".$SearchPhrase."' IN LOWER(ControlledName)) " 00138 ."AND ControlledNames.ControlledNameId = ResourceNameInts.ControlledNameId " 00139 ."AND ControlledNames.FieldId = ".$Field->Id(); 00140 break; 00141 00142 case MetadataSchema::MDFTYPE_TREE: 00143 $QueryString = "SELECT DISTINCT ResourceClassInts.ResourceId " 00144 ."FROM ResourceClassInts, Classifications " 00145 ."WHERE POSITION('".$SearchPhrase."' IN LOWER(ClassificationName)) " 00146 ."AND Classifications.ClassificationId = ResourceClassInts.ClassificationId " 00147 ."AND Classifications.FieldId = ".$Field->Id(); 00148 break; 00149 00150 case MetadataSchema::MDFTYPE_USER: 00151 $UserId = $this->DB->Query("SELECT UserId FROM APUsers " 00152 ."WHERE POSITION('".$SearchPhrase."' IN LOWER(UserName)) " 00153 ."OR POSITION('".$SearchPhrase."' IN LOWER(RealName))", "UserId"); 00154 if ($UserId != NULL) 00155 { 00156 $QueryString = "SELECT DISTINCT ResourceId FROM Resources " 00157 ."WHERE `".$Field->DBFieldName()."` = ".$UserId; 00158 } 00159 break; 00160 00161 case MetadataSchema::MDFTYPE_NUMBER: 00162 if ($SearchPhrase > 0) 00163 { 00164 $QueryString = "SELECT DISTINCT ResourceId FROM Resources " 00165 ."WHERE `".$Field->DBFieldName()."` = ".(int)$SearchPhrase; 00166 } 00167 break; 00168 00169 case MetadataSchema::MDFTYPE_FLAG: 00170 case MetadataSchema::MDFTYPE_DATE: 00171 case MetadataSchema::MDFTYPE_TIMESTAMP: 00172 # (these types not yet handled by search engine for phrases) 00173 break; 00174 } 00175 00176 # build match list based on results returned from DB 00177 if (isset($QueryString)) 00178 { 00179 if ($this->DebugLevel > 7) { print("SE: performing phrase search query" 00180 ." (<i>".$QueryString."</i>)<br>\n"); } 00181 if ($this->DebugLevel > 9) { $StartTime = microtime(TRUE); } 00182 $this->DB->Query($QueryString); 00183 if ($this->DebugLevel > 9) 00184 { 00185 $EndTime = microtime(TRUE); 00186 if (($StartTime - $EndTime) > 0.1) 00187 { 00188 printf("SE: query took %.2f seconds<br>\n", 00189 ($EndTime - $StartTime)); 00190 } 00191 } 00192 $MatchList = $this->DB->FetchColumn("ResourceId"); 00193 if (isset($SecondQueryString)) 00194 { 00195 if ($this->DebugLevel > 7) { print("SE: performing second phrase search query" 00196 ." (<i>".$SecondQueryString."</i>)<br>\n"); } 00197 if ($this->DebugLevel > 9) { $StartTime = microtime(TRUE); } 00198 $this->DB->Query($SecondQueryString); 00199 if ($this->DebugLevel > 9) 00200 { 00201 $EndTime = microtime(TRUE); 00202 if (($StartTime - $EndTime) > 0.1) 00203 { 00204 printf("SE: query took %.2f seconds<br>\n", 00205 ($EndTime - $StartTime)); 00206 } 00207 } 00208 $MatchList = $MatchList + $this->DB->FetchColumn("ResourceId"); 00209 } 00210 } 00211 else 00212 { 00213 $MatchList = array(); 00214 } 00215 00216 # return list of matching resources to caller 00217 return $MatchList; 00218 } 00219 00220 # search field for records that meet comparison 00221 function SearchFieldsForComparisonMatches($FieldNames, $Operators, $Values) 00222 { 00223 # use SQL keyword appropriate to current search logic for combining operations 00224 $CombineWord = ($this->DefaultSearchLogic == SEARCHLOGIC_AND) ? " AND " : " OR "; 00225 00226 # for each comparison 00227 foreach ($FieldNames as $Index => $FieldName) 00228 { 00229 $Operator = $Operators[$Index]; 00230 $Value = $Values[$Index]; 00231 00232 # determine query based on field type 00233 $Field = $this->Schema->GetFieldByName($FieldName); 00234 if ($Field != NULL) 00235 { 00236 switch ($Field->Type()) 00237 { 00238 case MetadataSchema::MDFTYPE_TEXT: 00239 case MetadataSchema::MDFTYPE_PARAGRAPH: 00240 case MetadataSchema::MDFTYPE_NUMBER: 00241 case MetadataSchema::MDFTYPE_FLAG: 00242 case MetadataSchema::MDFTYPE_USER: 00243 case MetadataSchema::MDFTYPE_URL: 00244 if (isset($Queries["Resources"])) 00245 { 00246 $Queries["Resources"] .= $CombineWord; 00247 } 00248 else 00249 { 00250 $Queries["Resources"] = "SELECT DISTINCT ResourceId FROM Resources WHERE "; 00251 } 00252 if ($Field->Type() == MetadataSchema::MDFTYPE_USER) 00253 { 00254 $User = new SPTUser($Value); 00255 $Value = $User->Id(); 00256 } 00257 $Queries["Resources"] .= "`".$Field->DBFieldName()."` ".$Operator." '".addslashes($Value)."' "; 00258 break; 00259 00260 case MetadataSchema::MDFTYPE_CONTROLLEDNAME: 00261 $QueryIndex = "ResourceNameInts".$Field->Id(); 00262 if (!isset($Queries[$QueryIndex]["A"])) 00263 { 00264 $Queries[$QueryIndex]["A"] = 00265 "SELECT DISTINCT ResourceId" 00266 ." FROM ResourceNameInts, ControlledNames " 00267 ." WHERE ControlledNames.FieldId = ".$Field->Id() 00268 ." AND ( "; 00269 $CloseQuery[$QueryIndex]["A"] = TRUE; 00270 } 00271 else 00272 { 00273 $Queries[$QueryIndex]["A"] .= $CombineWord; 00274 } 00275 $Queries[$QueryIndex]["A"] .= 00276 "((ResourceNameInts.ControlledNameId" 00277 ." = ControlledNames.ControlledNameId" 00278 ." AND ControlledName " 00279 .$Operator." '".addslashes($Value)."'))"; 00280 if (!isset($Queries[$QueryIndex]["B"])) 00281 { 00282 $Queries[$QueryIndex]["B"] = 00283 "SELECT DISTINCT ResourceId" 00284 . " FROM ResourceNameInts, ControlledNames," 00285 ." VariantNames " 00286 ." WHERE ControlledNames.FieldId = ".$Field->Id() 00287 ." AND ( "; 00288 $CloseQuery[$QueryIndex]["B"] = TRUE; 00289 } 00290 else 00291 { 00292 $Queries[$QueryIndex]["B"] .= $CombineWord; 00293 } 00294 $Queries[$QueryIndex]["B"] .= 00295 "((ResourceNameInts.ControlledNameId" 00296 ." = ControlledNames.ControlledNameId" 00297 ." AND ResourceNameInts.ControlledNameId" 00298 ." = VariantNames.ControlledNameId" 00299 ." AND VariantName " 00300 .$Operator." '".addslashes($Value)."'))"; 00301 break; 00302 00303 case MetadataSchema::MDFTYPE_OPTION: 00304 $QueryIndex = "ResourceNameInts".$Field->Id(); 00305 if (!isset($Queries[$QueryIndex])) 00306 { 00307 $Queries[$QueryIndex] = 00308 "SELECT DISTINCT ResourceId FROM ResourceNameInts, ControlledNames " 00309 ." WHERE ControlledNames.FieldId = ".$Field->Id() 00310 ." AND ( "; 00311 $CloseQuery[$QueryIndex] = TRUE; 00312 } 00313 else 00314 { 00315 $Queries[$QueryIndex] .= $CombineWord; 00316 } 00317 $Queries[$QueryIndex] .= "(ResourceNameInts.ControlledNameId = ControlledNames.ControlledNameId" 00318 ." AND ControlledName ".$Operator." '".addslashes($Value)."')"; 00319 break; 00320 00321 case MetadataSchema::MDFTYPE_TREE: 00322 $QueryIndex = "ResourceClassInts".$Field->Id(); 00323 if (!isset($Queries[$QueryIndex])) 00324 { 00325 $Queries[$QueryIndex] = "SELECT DISTINCT ResourceId FROM ResourceClassInts, Classifications " 00326 ." WHERE ResourceClassInts.ClassificationId = Classifications.ClassificationId" 00327 ." AND Classifications.FieldId = ".$Field->Id()." AND ( "; 00328 $CloseQuery[$QueryIndex] = TRUE; 00329 } 00330 else 00331 { 00332 $Queries[$QueryIndex] .= $CombineWord; 00333 } 00334 $Queries[$QueryIndex] .= " ClassificationName ".$Operator." '".addslashes($Value)."'"; 00335 break; 00336 00337 case MetadataSchema::MDFTYPE_TIMESTAMP: 00338 # if value appears to have time component or text description 00339 if (strpos($Value, ":") 00340 || strstr($Value, "day") 00341 || strstr($Value, "week") 00342 || strstr($Value, "month") 00343 || strstr($Value, "year") 00344 || strstr($Value, "hour") 00345 || strstr($Value, "minute")) 00346 { 00347 if (isset($Queries["Resources"])) 00348 { 00349 $Queries["Resources"] .= $CombineWord; 00350 } 00351 else 00352 { 00353 $Queries["Resources"] = "SELECT DISTINCT ResourceId" 00354 ." FROM Resources WHERE "; 00355 } 00356 00357 # flip operator if necessary 00358 if (strstr($Value, "ago")) 00359 { 00360 $OperatorFlipMap = array( 00361 "<" => ">=", 00362 ">" => "<=", 00363 "<=" => ">", 00364 ">=" => "<", 00365 ); 00366 $Operator = isset($OperatorFlipMap[$Operator]) 00367 ? $OperatorFlipMap[$Operator] : $Operator; 00368 } 00369 00370 # use strtotime method to build condition 00371 $TimestampValue = strtotime($Value); 00372 if (($TimestampValue !== FALSE) && ($TimestampValue != -1)) 00373 { 00374 if ((date("H:i:s", $TimestampValue) == "00:00:00") 00375 && (strpos($Value, "00:00") === FALSE) 00376 && ($Operator == "<=")) 00377 { 00378 $NormalizedValue = 00379 date("Y-m-d", $TimestampValue)." 23:59:59"; 00380 } 00381 else 00382 { 00383 $NormalizedValue = date("Y-m-d H:i:s", $TimestampValue); 00384 } 00385 } 00386 else 00387 { 00388 $NormalizedValue = addslashes($Value); 00389 } 00390 $Queries["Resources"] .= 00391 " ( `".$Field->DBFieldName()."` " 00392 .$Operator 00393 ." '".$NormalizedValue."' ) "; 00394 } 00395 else 00396 { 00397 # use Date object method to build condition 00398 $Date = new Date($Value); 00399 if ($Date->Precision()) 00400 { 00401 if (isset($Queries["Resources"])) 00402 { 00403 $Queries["Resources"] .= $CombineWord; 00404 } 00405 else 00406 { 00407 $Queries["Resources"] = "SELECT DISTINCT ResourceId" 00408 ." FROM Resources WHERE "; 00409 } 00410 $Queries["Resources"] .= " ( ".$Date->SqlCondition( 00411 $Field->DBFieldName(), NULL, $Operator)." ) "; 00412 } 00413 } 00414 break; 00415 00416 case MetadataSchema::MDFTYPE_DATE: 00417 $Date = new Date($Value); 00418 if ($Date->Precision()) 00419 { 00420 if (isset($Queries["Resources"])) 00421 { 00422 $Queries["Resources"] .= $CombineWord; 00423 } 00424 else 00425 { 00426 $Queries["Resources"] = "SELECT DISTINCT ResourceId" 00427 ." FROM Resources WHERE "; 00428 } 00429 $Queries["Resources"] .= " ( ".$Date->SqlCondition( 00430 $Field->DBFieldName()."Begin", 00431 $Field->DBFieldName()."End", $Operator)." ) "; 00432 } 00433 break; 00434 00435 case MetadataSchema::MDFTYPE_IMAGE: 00436 case MetadataSchema::MDFTYPE_FILE: 00437 # (these types not yet handled by search engine for comparisons) 00438 break; 00439 } 00440 } 00441 } 00442 00443 # if queries found 00444 if (isset($Queries)) 00445 { 00446 # for each assembled query 00447 foreach ($Queries as $QueryIndex => $Query) 00448 { 00449 # if query has multiple parts 00450 if (is_array($Query)) 00451 { 00452 # for each part of query 00453 $ResourceIds = array(); 00454 foreach ($Query as $PartIndex => $PartQuery) 00455 { 00456 # add closing paren if query was flagged to be closed 00457 if (isset($CloseQuery[$QueryIndex])) { $PartQuery .= " ) "; } 00458 00459 # perform query and retrieve IDs 00460 if ($this->DebugLevel > 5) { print("SE: " 00461 ." performing comparison query (<i>".$PartQuery 00462 ."</i>)<br>\n"); } 00463 $this->DB->Query($PartQuery); 00464 $ResourceIds = $ResourceIds 00465 + $this->DB->FetchColumn("ResourceId"); 00466 if ($this->DebugLevel > 5) { print("SE: " 00467 ." comparison query produced <i>" 00468 .count($ResourceIds)."</i> results<br>\n"); } 00469 } 00470 } 00471 else 00472 { 00473 # add closing paren if query was flagged to be closed 00474 if (isset($CloseQuery[$QueryIndex])) { $Query .= " ) "; } 00475 00476 # perform query and retrieve IDs 00477 if ($this->DebugLevel > 5) { print("SE: " 00478 ." performing comparison query (<i>".$Query 00479 ."</i>)<br>\n"); } 00480 $this->DB->Query($Query); 00481 $ResourceIds = $this->DB->FetchColumn("ResourceId"); 00482 if ($this->DebugLevel > 5) { print("SE: " 00483 ." comparison query produced <i>" 00484 .count($ResourceIds)."</i> results<br>\n"); } 00485 } 00486 00487 # if we already have some results 00488 if (isset($Results)) 00489 { 00490 # if search logic is set to AND 00491 if ($this->DefaultSearchLogic == SEARCHLOGIC_AND) 00492 { 00493 # remove anything from results that was not returned from query 00494 $Results = array_intersect($Results, $ResourceIds); 00495 } 00496 else 00497 { 00498 # add values returned from query to results 00499 $Results = array_unique(array_merge($Results, $ResourceIds)); 00500 } 00501 } 00502 else 00503 { 00504 # set results to values returned from query 00505 $Results = $ResourceIds; 00506 } 00507 } 00508 } 00509 else 00510 { 00511 # initialize results to empty list 00512 $Results = array(); 00513 } 00514 00515 # return results to caller 00516 return $Results; 00517 } 00518 00519 function GetItemIdsSortedByField($FieldName, $SortDescending) 00520 { 00521 $RFactory = new ResourceFactory(); 00522 return $RFactory->GetResourceIdsSortedBy($FieldName, !$SortDescending); 00523 } 00524 00525 var $Schema; 00526 00527 # functions for backward compatability w/ old SPT code 00528 function UpdateForResource($ItemId) { $this->UpdateForItem($ItemId); } 00529 } 00530 00531 00532 ?>