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 -------------------------------------------------- 41 $ItemNameColumnName = NULL,
42 $OrderOpsAllowed = FALSE,
45 # save item access names 46 $this->ItemClassName = $ItemClassName;
47 $this->ItemTableName = $ItemTableName;
48 $this->ItemIdColumnName = $ItemIdColumnName;
49 $this->ItemNameColumnName = $ItemNameColumnName;
51 # save flag indicating whether item type allows ordering operations 52 $this->OrderOpsAllowed = $OrderOpsAllowed;
56 $ItemTableName, $ItemIdColumnName);
60 # save database operation conditional 61 $this->SqlCondition = $SqlCondition;
63 # grab our own database handle 73 return $this->ItemClassName;
85 # load array of stale items 86 $MinutesUntilStale = max($MinutesUntilStale, 1);
87 $this->DB->Query(
"SELECT ".$this->ItemIdColumnName.
" FROM ".$this->ItemTableName
88 .
" WHERE ".$this->ItemIdColumnName.
" < 0" 89 .
" AND DateLastModified < DATE_SUB(NOW(), " 90 .
" INTERVAL ".intval($MinutesUntilStale).
" MINUTE)" 91 .($this->SqlCondition ?
" AND ".$this->SqlCondition :
""));
92 $ItemIds = $this->DB->FetchColumn($this->ItemIdColumnName);
95 foreach ($ItemIds as $ItemId)
97 $Item =
new $this->ItemClassName($ItemId);
101 # report number of items deleted to caller 102 return count($ItemIds);
112 # if no highest item ID found 114 if ($HighestItemId <= 0)
116 # start with item ID 1 121 # else use next ID available after highest 122 $ItemId = $HighestItemId + 1;
125 # return next ID to caller 138 # use class-wide condition if set 139 $ConditionString = ($this->SqlCondition && !$IgnoreSqlCondition)
140 ?
" WHERE ".$this->SqlCondition :
"";
142 # return highest item ID to caller 143 return $this->DB->Query(
"SELECT ".$this->ItemIdColumnName
144 .
" FROM ".$this->ItemTableName
146 .
" ORDER BY ".$this->ItemIdColumnName
148 $this->ItemIdColumnName);
157 $LowestItemId = $this->DB->Query(
"SELECT ".$this->ItemIdColumnName
158 .
" FROM ".$this->ItemTableName
159 .
" ORDER BY ".$this->ItemIdColumnName
161 $this->ItemIdColumnName);
162 if ($LowestItemId > 0)
168 $ItemId = $LowestItemId - 1;
181 public function GetItemCount($Condition = NULL, $IncludeTempItems = FALSE)
183 # use condition if supplied 184 $ConditionString = ($Condition != NULL) ?
" WHERE ".$Condition :
"";
186 # if temp items are to be excluded 187 if (!$IncludeTempItems)
189 # if a condition was previously set 190 if (strlen($ConditionString))
192 # add in condition to exclude temp items 193 $ConditionString .=
" AND (".$this->ItemIdColumnName.
" >= 0)";
197 # use condition to exclude temp items 198 $ConditionString =
" WHERE ".$this->ItemIdColumnName.
" >= 0";
202 # add class-wide condition if set 203 if ($this->SqlCondition)
205 if (strlen($ConditionString))
207 $ConditionString .=
" AND ".$this->SqlCondition;
211 $ConditionString =
" WHERE ".$this->SqlCondition;
215 # retrieve item count 216 $Count = $this->DB->Query(
"SELECT COUNT(*) AS RecordCount" 217 .
" FROM ".$this->ItemTableName
221 # return count to caller 222 return intval($Count);
238 public function GetItemIds($Condition = NULL, $IncludeTempItems = FALSE,
239 $SortField = NULL, $SortAscending = TRUE)
241 # if temp items are supposed to be included 242 if ($IncludeTempItems)
244 # condition is only as supplied 245 $ConditionString = ($Condition == NULL) ?
"" :
" WHERE ".$Condition;
249 # condition is non-negative IDs plus supplied condition 250 $ConditionString =
" WHERE ".$this->ItemIdColumnName.
" >= 0" 251 .(($Condition == NULL) ?
"" :
" AND ".$Condition);
254 # add class-wide condition if set 255 if ($this->SqlCondition)
257 if (strlen($ConditionString))
259 $ConditionString .=
" AND ".$this->SqlCondition;
263 $ConditionString =
" WHERE ".$this->SqlCondition;
267 # add sorting if specified 268 if ($SortField !== NULL)
270 $ConditionString .=
" ORDER BY `".addslashes($SortField).
"` " 271 .($SortAscending ?
"ASC" :
"DESC");
275 $this->DB->Query(
"SELECT ".$this->ItemIdColumnName
276 .
" FROM ".$this->ItemTableName
278 $ItemIds = $this->DB->FetchColumn($this->ItemIdColumnName);
280 # return IDs to caller 293 # set up SQL condition if supplied 294 $ConditionString = ($Condition == NULL) ?
"" :
" WHERE ".$Condition;
296 # add class-wide condition if set 297 if ($this->SqlCondition)
299 if (strlen($ConditionString))
301 $ConditionString .=
" AND ".$this->SqlCondition;
305 $ConditionString =
" WHERE ".$this->SqlCondition;
309 # return modification date for item most recently changed 310 return $this->DB->Query(
"SELECT MAX(DateLastModified) AS LastChangeDate" 311 .
" FROM ".$this->ItemTableName.$ConditionString,
323 return new $this->ItemClassName($ItemId);
333 public function ItemExists($ItemId, $IgnoreSqlCondition = FALSE)
335 if (!is_numeric($ItemId))
339 $Condition = $IgnoreSqlCondition ?
"" 340 : ($this->SqlCondition ?
" AND ".$this->SqlCondition :
"");
341 $ItemCount = $this->DB->Query(
"SELECT COUNT(*) AS ItemCount" 342 .
" FROM ".$this->ItemTableName
343 .
" WHERE ".$this->ItemIdColumnName.
" = ".intval($ItemId)
344 .$Condition,
"ItemCount");
345 return ($ItemCount > 0) ? TRUE : FALSE;
361 if ($ItemId === FALSE)
363 # report error to caller 368 # load object and return to caller 369 return $this->
GetItem($ItemId);
383 # error out if this is an illegal operation for this item type 384 if ($this->ItemNameColumnName == NULL)
386 throw new Exception(
"Attempt to get item ID by name on item type" 387 .
"(".$this->ItemClassName.
") that has no name field specified.");
390 # if caching is off or item ID is already loaded 392 || !isset($this->ItemIdByNameCache[$this->SqlCondition][$Name]))
394 # query database for item ID 395 $Comparison = $IgnoreCase
396 ?
"LOWER(".$this->ItemNameColumnName.
") = '" 397 .addslashes(strtolower($Name)).
"'" 398 : $this->ItemNameColumnName.
" = '" .addslashes($Name).
"'";
399 $ItemId = $this->DB->Query(
"SELECT ".$this->ItemIdColumnName
400 .
" FROM ".$this->ItemTableName
401 .
" WHERE ".$Comparison
402 .($this->SqlCondition
403 ?
" AND ".$this->SqlCondition
405 $this->ItemIdColumnName);
406 $this->ItemIdByNameCache[$this->SqlCondition][$Name] =
407 ($this->DB->NumRowsSelected() == 0) ? FALSE : $ItemId;
410 # return ID or error indicator to caller 411 return $this->ItemIdByNameCache[$this->SqlCondition][$Name];
423 foreach ($Names as $Name)
438 # error out if this is an illegal operation for this item type 439 if ($this->ItemNameColumnName == NULL)
441 throw new Exception(
"Attempt to get array of item names" 442 .
" on item type (".$this->ItemClassName.
") that has no" 443 .
" name field specified.");
446 # query database for item names 450 $Condition =
"WHERE ".$SqlCondition;
452 if ($this->SqlCondition)
454 if (strlen($Condition))
456 $Condition .=
" AND ".$this->SqlCondition;
460 $Condition =
" WHERE ".$this->SqlCondition;
463 $this->DB->Query(
"SELECT ".$this->ItemIdColumnName
464 .
", ".$this->ItemNameColumnName
465 .
" FROM ".$this->ItemTableName.
" " 467 .
" ORDER BY ".$this->ItemNameColumnName);
468 $Names = $this->DB->FetchColumn(
469 $this->ItemNameColumnName, $this->ItemIdColumnName);
471 # return item names to caller 485 foreach ($Ids as $Id)
510 $SelectedItemId = NULL,
511 $SqlCondition = NULL,
513 $SubmitOnChange = FALSE,
516 # retrieve requested fields 521 $OptionListName, $ItemNames, $SelectedItemId);
523 # set list attributes 524 $OptList->SubmitOnChange($SubmitOnChange);
525 $OptList->Size($DisplaySize);
526 $OptList->Disabled($Disabled);
528 # return generated HTML for list to caller 529 return $OptList->GetHtml();
540 $Condition = $IgnoreCase
541 ?
"LOWER(".$this->ItemNameColumnName.
")" 542 .
" = '".addslashes(strtolower($Name)).
"'" 543 : $this->ItemNameColumnName.
" = '".addslashes($Name).
"'";
544 if ($this->SqlCondition)
546 $Condition .=
" AND ".$this->SqlCondition;
548 $NameCount = $this->DB->Query(
"SELECT COUNT(*) AS RecordCount FROM " 549 .$this->ItemTableName.
" WHERE ".$Condition,
"RecordCount");
550 return ($NameCount > 0) ? TRUE : FALSE;
572 $NumberOfResults = 100,
573 $IncludeVariants = FALSE,
574 $UseBooleanMode = TRUE,
576 $IdExclusions = array(),
577 $NameExclusions = array())
579 # error out if this is an illegal operation for this item type 580 if ($this->ItemNameColumnName == NULL)
582 throw new Exception(
"Attempt to search for item names on item type" 583 .
"(".$this->ItemClassName.
") that has no name field specified.");
586 # return no results if empty search string passed in 587 if (!strlen(trim($SearchString))) {
return array(); }
589 # construct SQL query 591 $QueryString =
"SELECT ".$this->ItemIdColumnName.
",".$this->ItemNameColumnName
592 .
" FROM ".$this->ItemTableName.
" WHERE " 593 .$this->ConstructSqlConditionsForSearch(
594 $SearchString, $IncludeVariants, $UseBooleanMode, $IdExclusions,
598 $QueryString .=
" LIMIT ".intval($NumberOfResults).
" OFFSET " 601 # perform query and retrieve names and IDs of items found by query 602 $DB->Query($QueryString);
603 $Names =
$DB->FetchColumn($this->ItemNameColumnName, $this->ItemIdColumnName);
605 # remove excluded words that were shorter than the MinWordLength 606 # (these will always be returned as mysql effectively ignores them) 607 $MinWordLen =
$DB->Query(
608 "SHOW VARIABLES WHERE variable_name='ft_min_word_len'",
"Value");
610 # explode the search string into whitespace delimited tokens, 611 # iterate over each token 612 $Words = preg_split(
"/[\s]+/", trim($SearchString));
613 foreach ($Words as $Word)
615 # if this token was an exclusion 618 # remove the - prefix to get the TgtWord 619 $TgtWord = substr($Word, 1);
621 # if this token was an exclusion shorter than MindWordLen 622 if (strlen($TgtWord) < $MinWordLen)
624 # filter names that match this exclusion from results 626 foreach ($Names as $Id => $Name)
628 if (! preg_match(
'/\b'.$TgtWord.
'/i', $Name))
630 $NewNames[$Id] = $Name;
638 # return names to caller 657 $IncludeVariants = FALSE,
658 $UseBooleanMode = TRUE,
659 $IdExclusions = array(),
660 $NameExclusions=array())
662 # return no results if empty search string passed in 663 if (!strlen(trim($SearchString))) {
return 0; }
665 # construct SQL query 667 $QueryString =
"SELECT COUNT(*) as ItemCount FROM " 668 .$this->ItemTableName.
" WHERE " 669 .$this->ConstructSqlConditionsForSearch(
670 $SearchString, $IncludeVariants, $UseBooleanMode, $IdExclusions,
673 # perform query and retrieve names and IDs of items found by query 674 $DB->Query($QueryString);
675 return intval(
$DB->FetchField(
"ItemCount"));
687 foreach ($Array as $Key => $Value)
692 throw new InvalidArgumentException(
693 "Unknown name found (\"".$Key.
"\").");
695 $NewArray[$Id] = $Value;
708 if ($NewValue !== NULL)
712 return $this->CachingEnabled;
721 unset($this->ItemIdByNameCache);
725 # ---- Ordering Operations ----------------------------------------------- 735 # condition is non-negative IDs (non-temp items) plus supplied condition 736 $NewCondition = $this->ItemIdColumnName.
" >= 0" 737 .($Condition ?
" AND ".$Condition :
"")
738 .($this->SqlCondition ?
" AND ".$this->SqlCondition :
"");
739 $this->OrderList->SqlCondition($NewCondition);
751 # error out if ordering operations are not allowed for this item type 752 if (!$this->OrderOpsAllowed)
754 throw new Exception(
"Attempt to perform order operation on item" 755 .
" type (".$this->ItemClassName.
") that does not support" 760 $this->OrderList->InsertBefore($TargetItem, $NewItem);
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" 781 $this->OrderList->InsertAfter($TargetItem, $NewItem);
791 # error out if ordering operations are not allowed for this item type 792 if (!$this->OrderOpsAllowed)
794 throw new Exception(
"Attempt to perform order operation on item" 795 .
" type (".$this->ItemClassName.
") that does not support" 800 $this->OrderList->Prepend($Item);
810 # error out if ordering operations are not allowed for this item type 811 if (!$this->OrderOpsAllowed)
813 throw new Exception(
"Attempt to perform order operation on item" 814 .
" type (".$this->ItemClassName.
") that does not support" 819 $this->OrderList->Append($Item);
828 # error out if ordering operations are not allowed for this item type 829 if (!$this->OrderOpsAllowed)
831 throw new Exception(
"Attempt to perform order operation on item" 832 .
" type (".$this->ItemClassName.
") that does not support" 836 # retrieve list of IDs 837 return $this->OrderList->GetIds();
848 # error out if ordering operations are not allowed for this item type 849 if (!$this->OrderOpsAllowed)
851 throw new Exception(
"Attempt to perform order operation on item" 852 .
" type (".$this->ItemClassName.
") that does not support" 857 $this->OrderList->Remove($ItemId);
861 # ---- PRIVATE INTERFACE ------------------------------------------------- 878 private function ConstructSqlConditionsForSearch(
879 $SearchString, $IncludeVariants = FALSE,
880 $UseBooleanMode = TRUE, $IdExclusions = array(), $NameExclusions=array() )
884 $MinWordLen = $SqlVarObj->Get(
"ft_min_word_len");
887 # if the search string is valid but shorter than the minimum word length 888 # indexed by the FTS, just do a normal equality test instead of using 889 # the index. Otherwise, FTS away. 890 if (strlen($SearchString) < $MinWordLen)
892 $QueryString .=
" ".$this->ItemNameColumnName
893 .
"='".addslashes($SearchString).
"'";
895 else if ($UseBooleanMode)
897 # When we're in boolean mode, construct a search string to use in our 898 # query. Include quoted strings verbatim. Make sure that each 899 # non-quoted word is prefixed with either + or -, so that it is 900 # either explicitly included or explicitly excluded. 901 # Keep track of stopwords in the search query (these will not 902 # match in the boolean search because FTS indexes ignores them). 903 # Append 'REGEXP' queries to match, so that our search results 904 # pay *some* attention to stopwords. 905 $StopWordList = $SqlVarObj->GetStopWords();
907 # strip out characters with special meaning in an SQL MATCH () AGAINST(). 908 $SearchString = trim(preg_replace(
"/[)\(><]+/",
"", $SearchString));
909 $Tokens = preg_split(
'/\s+/', $SearchString);
911 $NewSearchString =
"";
912 $SearchedStopwords = array();
913 $InQuotedString = FALSE;
914 foreach ($Tokens as $Token)
916 # if this is the beginning of a quoted string 917 # " -> quoted string implicitly reqiured 918 # +" -> quoted string explicitly required 919 # -" -> quoted string explicitly forbidden 920 $InQuotedString |= preg_match(
'/^[+-]?"/', $Token);
923 $NewSearchString .= $Token.
" ";
924 # we're still in a quoted string when our token 925 # doesn't end with a quote 926 $InQuotedString &= (substr($Token, -1) !=
'"');
930 # extract just the 'word' part of the token to 931 # check against our stopword list (alphabetic 932 # characters and apostrophes) 933 $Word = preg_replace(
"/[^a-zA-Z']/",
"", $Token);
934 if (in_array(strtolower($Word), $StopWordList))
936 $SearchedStopwords[]= $Word;
938 elseif (strlen($Word) >= $MinWordLen)
940 # if our token isn't explicitly required or 941 # excluded, mark it required 942 if ($Token{0} !=
"+" && $Token{0} !=
"-")
947 $NewSearchString .= $Token.
" ";
952 # trim trailing whitespace, close any open quotes 953 $NewSearchString = trim($NewSearchString);
956 $NewSearchString .=
'"';
959 # build onto our query string by appending the boolean search 961 $QueryString .=
" MATCH (".$this->ItemNameColumnName.
")" 962 .
" AGAINST ('".addslashes(trim($NewSearchString)).
"'" 963 .
" IN BOOLEAN MODE)";
965 # if there were any stopwords included in the search string, 966 # append REGEXP conditions to match those 967 foreach ($SearchedStopwords as $Stopword)
969 $QueryString .=
" AND ".$this->ItemNameColumnName
970 .
" REGEXP '".addslashes(preg_quote($Stopword)).
"'";
975 # if we weren't in boolean mode, just include the search 976 # string verbatim as a match condition 977 $QueryString .=
" MATCH (".$this->ItemNameColumnName.
")" 978 .
" AGAINST ('".addslashes(trim($SearchString)).
"')";
981 # add each ID exclusion 982 foreach ($IdExclusions as $IdExclusion)
984 $QueryString .=
" AND ".$this->ItemIdColumnName.
" != '" 985 .addslashes($IdExclusion).
"' ";
988 # add each value exclusion 989 foreach ($NameExclusions as $NameExclusion)
991 $QueryString .=
" AND ".$this->ItemNameColumnName.
" != '" 992 .addslashes($NameExclusion).
"' ";
995 # add class-wide condition if set 996 if ($this->SqlCondition)
998 $QueryString .=
" AND ".$this->SqlCondition;
1001 return $QueryString;
1006 private $CachingEnabled = TRUE;
1007 private $ItemClassName;
1008 private $ItemTableName;
1009 private $ItemIdByNameCache;
1010 private $ItemIdColumnName;
1011 private $ItemNameColumnName;
1012 private $OrderOpsAllowed;
1014 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.
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)...
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.
GetCountForItemNames($SearchString, $IncludeVariants=FALSE, $UseBooleanMode=TRUE, $IdExclusions=array(), $NameExclusions=array())
Retrieve count of items with names matching search string.
ClearCaches()
Clear item information caches.
ReindexByItemIds($Array)
Reindex supplied associative array, by replacing item names with item IDs.
Persistent doubly-linked-list data structure, with its data stored in a specified database table...
GetItems($SqlCondition=NULL)
Retrieve items.
SearchForItemNames($SearchString, $NumberOfResults=100, $IncludeVariants=FALSE, $UseBooleanMode=TRUE, $Offset=0, $IdExclusions=array(), $NameExclusions=array())
Retrieve items with names matching search string.
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.
GetItemIdsByNames($Names)
Look up IDs for specified names.
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.
__construct($ItemClassName, $ItemTableName, $ItemIdColumnName, $ItemNameColumnName=NULL, $OrderOpsAllowed=FALSE, $SqlCondition=NULL)
Class constructor.
GetNextItemId()
Retrieve next available (non-temp) item ID.