3 # FILE: ItemFactory.php 5 # Part of the ScoutLib application support library 6 # Copyright 2007-2016 Edward Almasy and Internet Scout Research Group 7 # http://scout.wisc.edu 20 # ---- PUBLIC INTERFACE -------------------------------------------------- 37 public function __construct($ItemClassName, $ItemTableName, $ItemIdFieldName,
38 $ItemNameFieldName = NULL, $OrderOpsAllowed = FALSE, $SqlCondition = NULL)
40 # save item access names 41 $this->ItemClassName = $ItemClassName;
42 $this->ItemTableName = $ItemTableName;
43 $this->ItemIdFieldName = $ItemIdFieldName;
44 $this->ItemNameFieldName = $ItemNameFieldName;
46 # save flag indicating whether item type allows ordering operations 47 $this->OrderOpsAllowed = $OrderOpsAllowed;
51 $ItemTableName, $ItemIdFieldName);
55 # save database operation conditional 56 $this->SqlCondition = $SqlCondition;
58 # grab our own database handle 68 return $this->ItemClassName;
80 # load array of stale items 81 $MinutesUntilStale = max($MinutesUntilStale, 1);
82 $this->DB->Query(
"SELECT ".$this->ItemIdFieldName.
" FROM ".$this->ItemTableName
83 .
" WHERE ".$this->ItemIdFieldName.
" < 0" 84 .
" AND DateLastModified < DATE_SUB(NOW(), " 85 .
" INTERVAL ".intval($MinutesUntilStale).
" MINUTE)" 86 .($this->SqlCondition ?
" AND ".$this->SqlCondition :
""));
87 $ItemIds = $this->DB->FetchColumn($this->ItemIdFieldName);
90 foreach ($ItemIds as $ItemId)
92 $Item =
new $this->ItemClassName($ItemId);
96 # report number of items deleted to caller 97 return count($ItemIds);
107 # if no highest item ID found 109 if ($HighestItemId <= 0)
111 # start with item ID 1 116 # else use next ID available after highest 117 $ItemId = $HighestItemId + 1;
120 # return next ID to caller 133 # use class-wide condition if set 134 $ConditionString = ($this->SqlCondition && !$IgnoreSqlCondition)
135 ?
" WHERE ".$this->SqlCondition :
"";
137 # return highest item ID to caller 138 return $this->DB->Query(
"SELECT ".$this->ItemIdFieldName
139 .
" FROM ".$this->ItemTableName
141 .
" ORDER BY ".$this->ItemIdFieldName
143 $this->ItemIdFieldName);
152 $LowestItemId = $this->DB->Query(
"SELECT ".$this->ItemIdFieldName
153 .
" FROM ".$this->ItemTableName
154 .
" ORDER BY ".$this->ItemIdFieldName
156 $this->ItemIdFieldName);
157 if ($LowestItemId > 0)
163 $ItemId = $LowestItemId - 1;
176 public function GetItemCount($Condition = NULL, $IncludeTempItems = FALSE)
178 # use condition if supplied 179 $ConditionString = ($Condition != NULL) ?
" WHERE ".$Condition :
"";
181 # if temp items are to be excluded 182 if (!$IncludeTempItems)
184 # if a condition was previously set 185 if (strlen($ConditionString))
187 # add in condition to exclude temp items 188 $ConditionString .=
" AND (".$this->ItemIdFieldName.
" >= 0)";
192 # use condition to exclude temp items 193 $ConditionString =
" WHERE ".$this->ItemIdFieldName.
" >= 0";
197 # add class-wide condition if set 198 if ($this->SqlCondition)
200 if (strlen($ConditionString))
202 $ConditionString .=
" AND ".$this->SqlCondition;
206 $ConditionString =
" WHERE ".$this->SqlCondition;
210 # retrieve item count 211 $Count = $this->DB->Query(
"SELECT COUNT(*) AS RecordCount" 212 .
" FROM ".$this->ItemTableName
216 # return count to caller 217 return intval($Count);
233 public function GetItemIds($Condition = NULL, $IncludeTempItems = FALSE,
234 $SortField = NULL, $SortAscending = TRUE)
236 # if temp items are supposed to be included 237 if ($IncludeTempItems)
239 # condition is only as supplied 240 $ConditionString = ($Condition == NULL) ?
"" :
" WHERE ".$Condition;
244 # condition is non-negative IDs plus supplied condition 245 $ConditionString =
" WHERE ".$this->ItemIdFieldName.
" >= 0" 246 .(($Condition == NULL) ?
"" :
" AND ".$Condition);
249 # add class-wide condition if set 250 if ($this->SqlCondition)
252 if (strlen($ConditionString))
254 $ConditionString .=
" AND ".$this->SqlCondition;
258 $ConditionString =
" WHERE ".$this->SqlCondition;
262 # add sorting if specified 263 if ($SortField !== NULL)
265 $ConditionString .=
" ORDER BY `".addslashes($SortField).
"` " 266 .($SortAscending ?
"ASC" :
"DESC");
270 $this->DB->Query(
"SELECT ".$this->ItemIdFieldName
271 .
" FROM ".$this->ItemTableName
273 $ItemIds = $this->DB->FetchColumn($this->ItemIdFieldName);
275 # return IDs to caller 288 # set up SQL condition if supplied 289 $ConditionString = ($Condition == NULL) ?
"" :
" WHERE ".$Condition;
291 # add class-wide condition if set 292 if ($this->SqlCondition)
294 if (strlen($ConditionString))
296 $ConditionString .=
" AND ".$this->SqlCondition;
300 $ConditionString =
" WHERE ".$this->SqlCondition;
304 # return modification date for item most recently changed 305 return $this->DB->Query(
"SELECT MAX(DateLastModified) AS LastChangeDate" 306 .
" FROM ".$this->ItemTableName.$ConditionString,
318 return new $this->ItemClassName($ItemId);
328 public function ItemExists($ItemId, $IgnoreSqlCondition = FALSE)
330 if (!is_numeric($ItemId))
334 $Condition = $IgnoreSqlCondition ?
"" 335 : ($this->SqlCondition ?
" AND ".$this->SqlCondition :
"");
336 $ItemCount = $this->DB->Query(
"SELECT COUNT(*) AS ItemCount" 337 .
" FROM ".$this->ItemTableName
338 .
" WHERE ".$this->ItemIdFieldName.
" = ".intval($ItemId)
339 .$Condition,
"ItemCount");
340 return ($ItemCount > 0) ? TRUE : FALSE;
356 if ($ItemId === FALSE)
358 # report error to caller 363 # load object and return to caller 364 return $this->
GetItem($ItemId);
378 # error out if this is an illegal operation for this item type 379 if ($this->ItemNameFieldName == NULL)
381 throw new Exception(
"Attempt to get item ID by name on item type" 382 .
"(".$this->ItemClassName.
") that has no name field specified.");
385 # if caching is off or item ID is already loaded 387 || !isset($this->ItemIdByNameCache[$this->SqlCondition][$Name]))
389 # query database for item ID 390 $Comparison = $IgnoreCase
391 ?
"LOWER(".$this->ItemNameFieldName.
") = '" 392 .addslashes(strtolower($Name)).
"'" 393 : $this->ItemNameFieldName.
" = '" .addslashes($Name).
"'";
394 $ItemId = $this->DB->Query(
"SELECT ".$this->ItemIdFieldName
395 .
" FROM ".$this->ItemTableName
396 .
" WHERE ".$Comparison
397 .($this->SqlCondition
398 ?
" AND ".$this->SqlCondition
400 $this->ItemIdFieldName);
401 $this->ItemIdByNameCache[$this->SqlCondition][$Name] =
402 ($this->DB->NumRowsSelected() == 0) ? FALSE : $ItemId;
405 # return ID or error indicator to caller 406 return $this->ItemIdByNameCache[$this->SqlCondition][$Name];
417 # error out if this is an illegal operation for this item type 418 if ($this->ItemNameFieldName == NULL)
420 throw new Exception(
"Attempt to get array of item names" 421 .
" on item type (".$this->ItemClassName.
") that has no" 422 .
" name field specified.");
425 # query database for item names 429 $Condition =
"WHERE ".$SqlCondition;
431 if ($this->SqlCondition)
433 if (strlen($Condition))
435 $Condition .=
" AND ".$this->SqlCondition;
439 $Condition =
" WHERE ".$this->SqlCondition;
442 $this->DB->Query(
"SELECT ".$this->ItemIdFieldName
443 .
", ".$this->ItemNameFieldName
444 .
" FROM ".$this->ItemTableName.
" " 446 .
" ORDER BY ".$this->ItemNameFieldName);
447 $Names = $this->DB->FetchColumn(
448 $this->ItemNameFieldName, $this->ItemIdFieldName);
450 # return item names to caller 464 foreach ($Ids as $Id)
488 $OptionListName, $SelectedItemId = NULL, $SqlCondition = NULL,
489 $DisplaySize = 1, $SubmitOnChange = FALSE, $Disabled = FALSE)
491 # retrieve requested fields 496 $OptionListName, $ItemNames, $SelectedItemId);
498 # set list attributes 499 $OptList->SubmitOnChange($SubmitOnChange);
500 $OptList->Size($DisplaySize);
501 $OptList->Disabled($Disabled);
503 # return generated HTML for list to caller 504 return $OptList->GetHtml();
515 $Condition = $IgnoreCase
516 ?
"LOWER(".$this->ItemNameFieldName.
")" 517 .
" = '".addslashes(strtolower($Name)).
"'" 518 : $this->ItemNameFieldName.
" = '".addslashes($Name).
"'";
519 if ($this->SqlCondition)
521 $Condition .=
" AND ".$this->SqlCondition;
523 $NameCount = $this->DB->Query(
"SELECT COUNT(*) AS RecordCount FROM " 524 .$this->ItemTableName.
" WHERE ".$Condition,
"RecordCount");
525 return ($NameCount > 0) ? TRUE : FALSE;
546 $IncludeVariants = FALSE, $UseBooleanMode = TRUE, $Offset=0,
547 $IdExclusions = array(), $ValueExclusions=array())
549 # error out if this is an illegal operation for this item type 550 if ($this->ItemNameFieldName == NULL)
552 throw new Exception(
"Attempt to search for item names on item type" 553 .
"(".$this->ItemClassName.
") that has no name field specified.");
556 # return no results if empty search string passed in 557 if (!strlen(trim($SearchString))) {
return array(); }
559 # construct SQL query 561 $QueryString =
"SELECT ".$this->ItemIdFieldName.
",".$this->ItemNameFieldName
562 .
" FROM ".$this->ItemTableName.
" WHERE " 563 .$this->ConstructSqlConditionsForSearch(
564 $SearchString, $IncludeVariants, $UseBooleanMode, $IdExclusions,
568 $QueryString .=
" LIMIT ".intval($NumberOfResults).
" OFFSET " 571 # perform query and retrieve names and IDs of items found by query 572 $DB->Query($QueryString);
573 $Names =
$DB->FetchColumn($this->ItemNameFieldName, $this->ItemIdFieldName);
575 # remove excluded words that were shorter than the MinWordLength 576 # (these will always be returned as mysql effectively ignores them) 577 $MinWordLen =
$DB->Query(
578 "SHOW VARIABLES WHERE variable_name='ft_min_word_len'",
"Value");
580 # explode the search string into whitespace delimited tokens, 581 # iterate over each token 582 $Words = preg_split(
"/[\s]+/", trim($SearchString));
583 foreach ($Words as $Word)
585 # if this token was an exclusion 588 # remove the - prefix to get the TgtWord 589 $TgtWord = substr($Word, 1);
591 # if this token was an exclusion shorter than MindWordLen 592 if (strlen($TgtWord) < $MinWordLen)
594 # filter names that match this exclusion from results 596 foreach ($Names as $Id => $Name)
598 if (! preg_match(
'/\b'.$TgtWord.
'/i', $Name))
600 $NewNames[$Id] = $Name;
608 # return names to caller 626 $UseBooleanMode = TRUE, $IdExclusions = array(), $ValueExclusions=array())
628 # return no results if empty search string passed in 629 if (!strlen(trim($SearchString))) {
return 0; }
631 # construct SQL query 633 $QueryString =
"SELECT COUNT(*) as ItemCount FROM " 634 .$this->ItemTableName.
" WHERE " 635 .$this->ConstructSqlConditionsForSearch(
636 $SearchString, $IncludeVariants, $UseBooleanMode, $IdExclusions,
639 # perform query and retrieve names and IDs of items found by query 640 $DB->Query($QueryString);
641 return intval(
$DB->FetchField(
"ItemCount"));
652 if ($NewValue !== NULL)
656 return $this->CachingEnabled;
665 unset($this->ItemIdByNameCache);
669 # ---- Ordering Operations ----------------------------------------------- 679 # condition is non-negative IDs (non-temp items) plus supplied condition 680 $NewCondition = $this->ItemIdFieldName.
" >= 0" 681 .($Condition ?
" AND ".$Condition :
"")
682 .($this->SqlCondition ?
" AND ".$this->SqlCondition :
"");
683 $this->OrderList->SqlCondition($NewCondition);
695 # error out if ordering operations are not allowed for this item type 696 if (!$this->OrderOpsAllowed)
698 throw new Exception(
"Attempt to perform order operation on item" 699 .
" type (".$this->ItemClassName.
") that does not support" 704 $this->OrderList->InsertBefore($TargetItem, $NewItem);
716 # error out if ordering operations are not allowed for this item type 717 if (!$this->OrderOpsAllowed)
719 throw new Exception(
"Attempt to perform order operation on item" 720 .
" type (".$this->ItemClassName.
") that does not support" 725 $this->OrderList->InsertAfter($TargetItem, $NewItem);
735 # error out if ordering operations are not allowed for this item type 736 if (!$this->OrderOpsAllowed)
738 throw new Exception(
"Attempt to perform order operation on item" 739 .
" type (".$this->ItemClassName.
") that does not support" 744 $this->OrderList->Prepend($Item);
754 # error out if ordering operations are not allowed for this item type 755 if (!$this->OrderOpsAllowed)
757 throw new Exception(
"Attempt to perform order operation on item" 758 .
" type (".$this->ItemClassName.
") that does not support" 763 $this->OrderList->Append($Item);
772 # error out if ordering operations are not allowed for this item type 773 if (!$this->OrderOpsAllowed)
775 throw new Exception(
"Attempt to perform order operation on item" 776 .
" type (".$this->ItemClassName.
") that does not support" 780 # retrieve list of IDs 781 return $this->OrderList->GetIds();
792 # error out if ordering operations are not allowed for this item type 793 if (!$this->OrderOpsAllowed)
795 throw new Exception(
"Attempt to perform order operation on item" 796 .
" type (".$this->ItemClassName.
") that does not support" 801 $this->OrderList->Remove($ItemId);
805 # ---- PRIVATE INTERFACE ------------------------------------------------- 822 private function ConstructSqlConditionsForSearch(
823 $SearchString, $IncludeVariants = FALSE,
824 $UseBooleanMode = TRUE, $IdExclusions = array(), $ValueExclusions=array() )
828 # If the search string is valid but shorter than the minimum word length 829 # indexed by the FTS, just do a normal equality test instead of using 830 # the index. Otherwise, FTS away. 832 $MinWordLen =
$DB->Query(
833 "SHOW VARIABLES WHERE variable_name='ft_min_word_len'",
"Value");
834 if (strlen($SearchString) < $MinWordLen)
836 $QueryString .=
" ".$this->ItemNameFieldName
837 .
"='".addslashes($SearchString).
"'";
839 else if ($UseBooleanMode)
841 # When we're in boolean mode, construct a search string to use in our 842 # query. Include quoted strings verbatim. Make sure that each 843 # non-quoted word is prefixed with either + or -, so that it is 844 # either explicitly included or explicitily excluded. 845 # Keep track of stopwords in the search query (these will not 846 # match in the boolean search because FTS indexes ignores them). 847 # Append 'REGEXP' queries to match, so that our search results 848 # pay *some* attention to stopwords. 849 $SearchString = preg_replace(
"/[)\(><]+/",
"", $SearchString);
850 $Words = preg_split(
"/[\s]+/", trim($SearchString));
851 $NewSearchString =
"";
852 $SearchedStopwords = array();
853 $InQuotedString = FALSE;
855 $StopWordList = $SqlVarObj->GetStopWords();
856 $MinWordLen = $SqlVarObj->Get(
"ft_min_word_len");
857 foreach ($Words as $Word)
859 # remove any query-specific terms, punctuation, etc. 860 $JustTheWord = preg_replace(
"/[^a-zA-Z-]/",
"", $Word);
862 # require (boolean AND) certain words 863 if ($InQuotedString == FALSE
864 && !in_array($JustTheWord, $StopWordList)
865 && strlen($JustTheWord) >= $MinWordLen
869 $NewSearchString .=
"+";
872 if (preg_match(
"/^\"/", $Word)) { $InQuotedString = TRUE; }
873 if (preg_match(
"/\"$/", $Word)) { $InQuotedString = FALSE; }
874 $NewSearchString .= $Word.
" ";
876 if (in_array($JustTheWord, $StopWordList))
878 $SearchedStopwords[]= $JustTheWord;
882 # Build onto our query string by appending the boolean search 884 $QueryString .=
" MATCH (".$this->ItemNameFieldName.
")" 885 .
" AGAINST ('".addslashes(trim($NewSearchString)).
"'" 886 .
" IN BOOLEAN MODE)";
888 # If there were any stopwords included in the search string, 889 # append REGEXP conditions to match those. 890 foreach ($SearchedStopwords as $Stopword)
892 $QueryString .=
" AND ".$this->ItemNameFieldName
893 .
" REGEXP '".addslashes(preg_quote($Stopword)).
"'";
898 # If we weren't in boolean mode, just include the search 899 # string verbatim as a match condition: 900 $QueryString .=
" MATCH (".$this->ItemNameFieldName.
")" 901 .
" AGAINST ('".addslashes(trim($SearchString)).
"')";
904 # add each ID exclusion 905 foreach ($IdExclusions as $IdExclusion)
907 $QueryString .=
" AND ".$this->ItemIdFieldName.
" != '" 908 .addslashes($IdExclusion).
"' ";
911 # add each value exclusion 912 foreach ($ValueExclusions as $ValueExclusion)
914 $QueryString .=
" AND ".$this->ItemNameFieldName.
" != '" 915 .addslashes($ValueExclusion).
"' ";
918 # add class-wide condition if set 919 if ($this->SqlCondition)
921 $QueryString .=
" AND ".$this->SqlCondition;
929 private $CachingEnabled = TRUE;
930 private $ItemClassName;
931 private $ItemTableName;
932 private $ItemIdByNameCache;
933 private $ItemIdFieldName;
934 private $ItemNameFieldName;
935 private $OrderOpsAllowed;
937 private $SqlCondition;
GetHighestItemId($IgnoreSqlCondition=FALSE)
Retrieve highest item ID in use.
GetItemIdsInOrder()
Retrieve list of item IDs in order.
RemoveItemFromOrder($ItemId)
Remove item from existing order.
Prepend($Item)
Add item to beginning of order.
GetItemClassName()
Get class name of items manipulated by factory.
GetItemIdByName($Name, $IgnoreCase=FALSE)
Retrieve item ID by name.
SQL database abstraction object with smart query caching.
GetItemNames($SqlCondition=NULL)
Retrieve item names.
GetItemCount($Condition=NULL, $IncludeTempItems=FALSE)
Get count of items.
SearchForItemNames($SearchString, $NumberOfResults=100, $IncludeVariants=FALSE, $UseBooleanMode=TRUE, $Offset=0, $IdExclusions=array(), $ValueExclusions=array())
Retrieve items with names matching search string.
GetItemsAsOptionList($OptionListName, $SelectedItemId=NULL, $SqlCondition=NULL, $DisplaySize=1, $SubmitOnChange=FALSE, $Disabled=FALSE)
Retrieve items of specified type as HTML option list with item names as labels and item IDs as value ...
GetItem($ItemId)
Retrieve item by item ID.
GetItemByName($Name, $IgnoreCase=FALSE)
Retrieve item by name.
GetLatestModificationDate($Condition=NULL)
Get newest modification date (based on values in "DateLastModified" column in database table)...
GetCountForItemNames($SearchString, $IncludeVariants=FALSE, $UseBooleanMode=TRUE, $IdExclusions=array(), $ValueExclusions=array())
Retrieve count of items with names matching search string.
CleanOutStaleTempItems($MinutesUntilStale=10080)
Clear out (call the Delete() method) for any temp items more than specified number of minutes old...
ItemExists($ItemId, $IgnoreSqlCondition=FALSE)
Check that item exists with specified ID.
Class that allows permits easier access to MySQL system variables.
InsertBefore($TargetItem, $NewItem)
Insert item into order before specified item.
CachingEnabled($NewValue=NULL)
Enable/disable caching of item information.
InsertAfter($TargetItem, $NewItem)
Insert item into order after specified item.
ClearCaches()
Clear item information caches.
Persistent doubly-linked-list data structure, with its data stored in a specified database table...
GetItems($SqlCondition=NULL)
Retrieve items.
GetNextTempItemId()
Return next available temporary item ID.
Append($Item)
Add item to end of order.
SetOrderOpsCondition($Condition)
Set SQL condition (added to WHERE clause) used to select items for ordering operations.
Common factory class for item manipulation.
__construct($ItemClassName, $ItemTableName, $ItemIdFieldName, $ItemNameFieldName=NULL, $OrderOpsAllowed=FALSE, $SqlCondition=NULL)
Class constructor.
GetItemIds($Condition=NULL, $IncludeTempItems=FALSE, $SortField=NULL, $SortAscending=TRUE)
Return array of item IDs.
NameIsInUse($Name, $IgnoreCase=FALSE)
Check whether item name is currently in use.
Convenience class for generating an HTML select/option form element.
GetNextItemId()
Retrieve next available (non-temp) item ID.