CWIS Developer Documentation
Database.php
Go to the documentation of this file.
1 <?PHP
2 
3 #
4 # Axis--Database.php
5 # A Simple SQL Database Abstraction Object
6 #
7 # Copyright 1999-2002 Axis Data
8 # This code is free software that can be used or redistributed under the
9 # terms of Version 2 of the GNU General Public License, as published by the
10 # Free Software Foundation (http://www.fsf.org).
11 #
12 # Author: Edward Almasy (almasy@axisdata.com)
13 #
14 # Part of the AxisPHP library v1.2.5
15 # For more information see http://www.axisdata.com/AxisPHP/
16 #
17 
22 class Database
23 {
24 
25  # ---- PUBLIC INTERFACE --------------------------------------------------
26  /*@(*/
28 
43  public function __construct(
44  $UserName = NULL, $Password = NULL, $DatabaseName = NULL, $HostName = NULL)
45  {
46  # save DB access parameter values
47  $this->DBUserName = $UserName ? $UserName : self::$GlobalDBUserName;
48  $this->DBPassword = $Password ? $Password : self::$GlobalDBPassword;
49  $this->DBHostName = $HostName ? $HostName :
50  (isset(self::$GlobalDBHostName) ? self::$GlobalDBHostName
51  : "localhost");
52  $this->DBName = $DatabaseName ? $DatabaseName : self::$GlobalDBName;
53 
54  # set memory threshold for cache clearing
55  if (!isset(self::$CacheMemoryThreshold))
56  {
57  self::$CacheMemoryThreshold = self::GetPhpMemoryLimit() / 4;
58  }
59 
60  # if we don't already have a connection or DB access parameters were supplied
61  $HandleIndex = $this->DBHostName.":".$this->DBName;
62  if (!array_key_exists($HandleIndex, self::$ConnectionHandles)
63  || $UserName || $Password || $DatabaseName || $HostName)
64  {
65  # open connection to DB server and select database
66  $this->Handle = self::ConnectAndSelectDB($this->DBHostName,
67  $this->DBUserName, $this->DBPassword, $this->DBName);
68  self::$ConnectionHandles[$HandleIndex] = $this->Handle;
69  }
70  else
71  {
72  # set local connection handle
73  $this->Handle = self::$ConnectionHandles[$HandleIndex];
74  }
75  }
76 
81  public function __sleep()
82  {
83  return array("DBUserName", "DBPassword", "DBHostName", "DBName");
84  }
90  public function __wakeup()
91  {
92  # if we don't already have a database server connection
93  $HandleIndex = $this->DBHostName.":".$this->DBName;
94  if (!array_key_exists($HandleIndex, self::$ConnectionHandles))
95  {
96  # open connection to DB server and select database
97  try
98  {
99  $this->Handle = self::ConnectAndSelectDB($this->DBHostName,
100  $this->DBUserName, $this->DBPassword, $this->DBName);
101  }
102  catch (Exception $Exception)
103  {
104  if (isset(self::$GlobalDBUserName)
105  && isset(self::$GlobalDBPassword)
106  && isset(self::$GlobalDBName))
107  {
108  $this->DBUserName = self::$GlobalDBUserName;
109  $this->DBPassword = self::$GlobalDBPassword;
110  $this->DBName = self::$GlobalDBName;
111  $this->DBHostName = isset(self::$GlobalDBHostName)
112  ? self::$GlobalDBHostName : "localhost";
113  $this->Handle = self::ConnectAndSelectDB($this->DBHostName,
114  $this->DBUserName, $this->DBPassword, $this->DBName);
115  }
116  else
117  {
118  throw $Exception;
119  }
120  }
121  self::$ConnectionHandles[$HandleIndex] = $this->Handle;
122  }
123  else
124  {
125  # set local connection handle
126  $this->Handle = self::$ConnectionHandles[$HandleIndex];
127  }
128  }
138  public static function SetGlobalServerInfo(
139  $UserName, $Password, $HostName = "localhost")
140  {
141  # save default DB access parameters
142  self::$GlobalDBUserName = $UserName;
143  self::$GlobalDBPassword = $Password;
144  self::$GlobalDBHostName = $HostName;
145 
146  # clear any existing DB connection handles
147  self::$ConnectionHandles = array();
148  }
149 
154  public static function SetGlobalDatabaseName($DatabaseName)
155  {
156  # save new default DB name
157  self::$GlobalDBName = $DatabaseName;
158 
159  # clear any existing DB connection handles
160  self::$ConnectionHandles = array();
161  }
162 
167  public function SetDefaultStorageEngine($Engine)
168  {
169  # choose config variable to use based on server version number
170  $ConfigVar = version_compare($this->GetServerVersion(), "5.5", "<")
171  ? "storage_engine" : "default_storage_engine";
172 
173  # set storage engine in database
174  $this->Query("SET ".$ConfigVar." = ".$Engine);
175  }
176 
183  public function GetServerVersion($FullVersion=FALSE)
184  {
185  # retrieve version string
186  $Version = $this->Query("SELECT VERSION() AS ServerVer", "ServerVer");
187 
188  if (!$FullVersion)
189  {
190  # strip off any build/config suffix
191  $Pieces = explode("-", $Version);
192  $Version = array_shift($Pieces);
193  }
194 
195  # return version number to caller
196  return $Version;
197  }
198 
207  public function GetClientVersion()
208  {
209  return mysqli_get_client_info();
210  }
211 
217  public function GetHostInfo()
218  {
219  return mysqli_get_host_info($this->Handle);
220  }
221 
227  public function DBHostName()
228  {
229  return $this->DBHostName;
230  }
231 
237  public function DBName()
238  {
239  return $this->DBName;
240  }
241 
247  public function DBUserName()
248  {
249  return $this->DBUserName;
250  }
251 
259  public static function Caching($NewSetting = NULL)
260  {
261  # if cache setting has changed
262  if (($NewSetting !== NULL) && ($NewSetting != self::$CachingFlag))
263  {
264  # save new setting
265  self::$CachingFlag = $NewSetting;
266 
267  # clear any existing cached results
268  self::$QueryResultCache = array();
269  }
270 
271  # return current setting to caller
272  return self::$CachingFlag;
273  }
274 
285  public static function AdvancedCaching($NewSetting = NULL)
286  {
287  if ($NewSetting !== NULL)
288  {
289  self::$AdvancedCachingFlag = $NewSetting;
290  }
291  return self::$AdvancedCachingFlag;
292  }
293 
313  public function SetQueryErrorsToIgnore($ErrorsToIgnore, $NormalizeWhitespace = TRUE)
314  {
315  if ($NormalizeWhitespace && ($ErrorsToIgnore !== NULL))
316  {
317  $RevisedErrorsToIgnore = array();
318  foreach ($ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
319  {
320  $SqlPattern = preg_replace("/\\s+/", "\\s+", $SqlPattern);
321  $RevisedErrorsToIgnore[$SqlPattern] = $ErrMsgPattern;
322  }
323  $ErrorsToIgnore = $RevisedErrorsToIgnore;
324  }
325  $this->ErrorsToIgnore = $ErrorsToIgnore;
326  }
327 
333  public function IgnoredError()
334  {
335  return $this->ErrorIgnored;
336  }
337 
338  /*@)*/ /* Setup/Initialization */ /*@(*/
340 
351  public function Query($QueryString, $FieldName = "")
352  {
353  # clear flag that indicates whether query error was ignored
354  $this->ErrorIgnored = FALSE;
355 
356  # if caching is enabled
357  if (self::$CachingFlag)
358  {
359  # if SQL statement is read-only
360  if ($this->IsReadOnlyStatement($QueryString))
361  {
362  # if we have statement in cache
363  if (isset(self::$QueryResultCache[$QueryString]["NumRows"]))
364  {
365  if (self::$QueryDebugOutputFlag)
366  { print("DB-C: $QueryString<br>\n"); }
367 
368  # make sure query result looks okay
369  $this->QueryHandle = TRUE;
370 
371  # increment cache hit counter
372  self::$CachedQueryCounter++;
373 
374  # make local copy of results
375  $this->QueryResults = self::$QueryResultCache[$QueryString];
376  $this->NumRows = self::$QueryResultCache[$QueryString]["NumRows"];
377 
378  # set flag to indicate that results should be retrieved from cache
379  $this->GetResultsFromCache = TRUE;
380  }
381  else
382  {
383  # execute SQL statement
384  $this->QueryHandle = $this->RunQuery($QueryString);
385  if (!$this->QueryHandle instanceof mysqli_result) { return FALSE; }
386 
387  # save number of rows in result
388  $this->NumRows = mysqli_num_rows($this->QueryHandle);
389 
390  # if too many rows to cache
391  if ($this->NumRows >= self::$CacheRowsThreshold)
392  {
393  # set flag to indicate that query results should not
394  # be retrieved from cache
395  $this->GetResultsFromCache = FALSE;
396  }
397  else
398  {
399  # if we are low on memory
400  if (self::GetFreeMemory() < self::$CacheMemoryThreshold)
401  {
402  # clear out all but last few rows from cache
403  self::$QueryResultCache = array_slice(
404  self::$QueryResultCache,
405  (0 - self::$CacheRowsToLeave));
406  }
407 
408  # if advanced caching is enabled
409  if (self::$AdvancedCachingFlag)
410  {
411  # save tables accessed by query
412  self::$QueryResultCache[$QueryString]["TablesAccessed"] =
413  $this->TablesAccessed($QueryString);
414  }
415 
416  # if rows found
417  if ($this->NumRows > 0)
418  {
419  # load query results
420  for ($Row = 0; $Row < $this->NumRows; $Row++)
421  {
422  $this->QueryResults[$Row] =
423  mysqli_fetch_assoc($this->QueryHandle);
424  }
425 
426  # cache query results
427  self::$QueryResultCache[$QueryString] = $this->QueryResults;
428  }
429  else
430  {
431  # clear local query results
432  unset($this->QueryResults);
433  }
434 
435  # cache number of rows
436  self::$QueryResultCache[$QueryString]["NumRows"] = $this->NumRows;
437 
438  # set flag to indicate that query results should be
439  # retrieved from cache
440  $this->GetResultsFromCache = TRUE;
441  }
442  }
443  }
444  else
445  {
446  # if advanced caching is enabled
447  if (self::$AdvancedCachingFlag)
448  {
449  # if table modified by statement is known
450  $TableModified = $this->TableModified($QueryString);
451  if ($TableModified)
452  {
453  # for each cached query
454  foreach (self::$QueryResultCache
455  as $CachedQueryString => $CachedQueryResult)
456  {
457  # if we know what tables were accessed
458  if ($CachedQueryResult["TablesAccessed"])
459  {
460  # if tables accessed include the one we may modify
461  if (in_array($TableModified,
462  $CachedQueryResult["TablesAccessed"]))
463  {
464  # clear cached query results
465  unset(self::$QueryResultCache[$CachedQueryString]);
466  }
467  }
468  else
469  {
470  # clear cached query results
471  unset(self::$QueryResultCache[$CachedQueryString]);
472  }
473  }
474  }
475  else
476  {
477  # clear entire query result cache
478  self::$QueryResultCache = array();
479  }
480  }
481  else
482  {
483  # clear entire query result cache
484  self::$QueryResultCache = array();
485  }
486 
487  # execute SQL statement
488  $this->QueryHandle = $this->RunQuery($QueryString);
489  if ($this->QueryHandle === FALSE) { return FALSE; }
490 
491  # set flag to indicate that query results should not be
492  # retrieved from cache
493  $this->GetResultsFromCache = FALSE;
494  }
495 
496  # reset row counter
497  $this->RowCounter = 0;
498 
499  # increment query counter
500  self::$QueryCounter++;
501  }
502  else
503  {
504  # execute SQL statement
505  $this->QueryHandle = $this->RunQuery($QueryString);
506  if ($this->QueryHandle === FALSE) { return FALSE; }
507  }
508 
509  if (($FieldName != "") && ($this->QueryHandle !== FALSE))
510  {
511  return $this->FetchField($FieldName);
512  }
513  else
514  {
515  return $this->QueryHandle;
516  }
517  }
518 
531  public function ExecuteQueriesFromFile($FileName)
532  {
533  # open file
534  $FHandle = fopen($FileName, "r");
535 
536  # if file open succeeded
537  if ($FHandle !== FALSE)
538  {
539  # while lines left in file
540  $Query = "";
541  $QueryCount = 0;
542  while (!feof($FHandle))
543  {
544  # read in line from file
545  $Line = fgets($FHandle, 32767);
546 
547  # trim whitespace from line
548  $Line = trim($Line);
549 
550  # if line is not empty and not a comment
551  if (!preg_match("/^#/", $Line)
552  && !preg_match("/^--/", $Line)
553  && strlen($Line))
554  {
555  # add line to current query
556  $Query .= " ".$Line;
557 
558  # if line completes a query
559  if (preg_match("/;$/", $Line))
560  {
561  # run query
562  $QueryCount++;
563  $Result = $this->Query($Query);
564  $Query = "";
565 
566  # if query resulted in an error that is not ignorable
567  if ($Result === FALSE)
568  {
569  # stop processing queries and set error code
570  $QueryCount = NULL;
571  break;
572  }
573  }
574  }
575  }
576 
577  # close file
578  fclose($FHandle);
579  }
580 
581  # return number of executed queries to caller
582  return $QueryCount;
583  }
584 
590  public function QueryErrMsg()
591  {
592  return $this->ErrMsg;
593  }
594 
600  public function QueryErrNo()
601  {
602  return $this->ErrNo;
603  }
604 
611  public static function DisplayQueryErrors($NewValue = NULL)
612  {
613  if ($NewValue !== NULL) { self::$DisplayErrors = $NewValue; }
614  return self::$DisplayErrors;
615  }
616 
621  public function NumRowsSelected()
622  {
623  # if caching is enabled and query was cached
624  if (self::$CachingFlag && $this->GetResultsFromCache)
625  {
626  # return cached number of rows to caller
627  return $this->NumRows;
628  }
629  else
630  {
631  # call to this method after an unsuccessful query
632  if (!$this->QueryHandle instanceof mysqli_result)
633  {
634  return 0;
635  }
636 
637  # retrieve number of rows and return to caller
638  return mysqli_num_rows($this->QueryHandle);
639  }
640  }
641 
647  public function NumRowsAffected()
648  {
649  # call to this method after an unsuccessful query
650  if ($this->QueryHandle === FALSE)
651  {
652  return 0;
653  }
654 
655  # retrieve number of rows and return to caller
656  return mysqli_affected_rows($this->Handle);
657  }
658 
664  public function FetchRow()
665  {
666  # if caching is enabled and query was cached
667  if (self::$CachingFlag && $this->GetResultsFromCache)
668  {
669  # if rows left to return
670  if ($this->RowCounter < $this->NumRows)
671  {
672  # retrieve row from cache
673  $Result = $this->QueryResults[$this->RowCounter];
674 
675  # increment row counter
676  $this->RowCounter++;
677  }
678  else
679  {
680  # return nothing
681  $Result = FALSE;
682  }
683  }
684  else
685  {
686  # call to this method after successful query
687  if ($this->QueryHandle instanceof mysqli_result)
688  {
689  $Result = mysqli_fetch_assoc($this->QueryHandle);
690  if ($Result === NULL) { $Result = FALSE; }
691  }
692 
693  # call to this method after unsuccessful query
694  else
695  {
696  $Result = FALSE;
697  }
698  }
699 
700  # return row to caller
701  return $Result;
702  }
703 
710  public function FetchRows($NumberOfRows = NULL)
711  {
712  # assume no rows will be returned
713  $Result = array();
714 
715  # for each available row
716  $RowsFetched = 0;
717  while ((($RowsFetched < $NumberOfRows) || ($NumberOfRows == NULL))
718  && ($Row = $this->FetchRow()))
719  {
720  # add row to results
721  $Result[] = $Row;
722  $RowsFetched++;
723  }
724 
725  # return array of rows to caller
726  return $Result;
727  }
728 
745  public function FetchColumn($FieldName, $IndexFieldName = NULL)
746  {
747  $Array = array();
748  while ($Record = $this->FetchRow())
749  {
750  if ($IndexFieldName != NULL)
751  {
752  $Array[$Record[$IndexFieldName]] = $Record[$FieldName];
753  }
754  else
755  {
756  $Array[] = $Record[$FieldName];
757  }
758  }
759  return $Array;
760  }
761 
770  public function FetchField($FieldName)
771  {
772  $Record = $this->FetchRow();
773  return isset($Record[$FieldName]) ? $Record[$FieldName] : NULL;
774  }
775 
782  public function LastInsertId()
783  {
784  return (int)$this->Query(
785  "SELECT LAST_INSERT_ID() AS InsertId",
786  "InsertId");
787  }
788 
803  public function UpdateValue(
804  $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
805  {
806  # expand condition if supplied
807  if ($Condition != NULL) { $Condition = " WHERE ".$Condition; }
808 
809  # read cached record from database if not already loaded
810  if (!isset($CachedRecord))
811  {
812  $this->Query("SELECT * FROM `".$TableName."` ".$Condition);
813  $CachedRecord = $this->FetchRow();
814  }
815 
816  # if new value supplied
817  if ($NewValue !== DB_NOVALUE)
818  {
819  # update value in database
820  $this->Query("UPDATE `".$TableName."` SET `".$FieldName."` = "
821  .(($NewValue === NULL) ? "NULL" : "'"
822  .mysqli_real_escape_string($this->Handle, $NewValue)."'")
823  .$Condition);
824 
825  # update value in cached record
826  $CachedRecord[$FieldName] = $NewValue;
827  }
828 
829  # return value from cached record to caller
830  return isset($CachedRecord[$FieldName])
831  ? $CachedRecord[$FieldName] : NULL;
832  }
833 
850  public function UpdateIntValue(
851  $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
852  {
853  return $this->UpdateValue($TableName, $FieldName,
854  (($NewValue === DB_NOVALUE) ? DB_NOVALUE : (int)$NewValue),
855  $Condition, $CachedRecord);
856  }
857 
874  public function UpdateFloatValue(
875  $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
876  {
877  return $this->UpdateValue($TableName, $FieldName,
878  (($NewValue === DB_NOVALUE) ? DB_NOVALUE : (float)$NewValue),
879  $Condition, $CachedRecord);
880  }
881 
893  public function CopyValues($TableName, $IdColumn, $SrcId, $DstId,
894  $ColumnsToExclude = array())
895  {
896  # retrieve names of all columns in table
897  $AllColumns = $this->GetColumns($TableName);
898 
899  # remove columns to be excluded from copy
900  $ColumnsToExclude[] = $IdColumn;
901  $ColumnsToCopy = array_diff($AllColumns, $ColumnsToExclude);
902 
903  # normalize destination IDs
904  $DstIds = is_array($DstId) ? $DstId : array($DstId);
905  $DstIds = array_diff($DstIds, array($SrcId));
906 
907  # if there are columns to copy and we have destinations
908  if (count($ColumnsToCopy) && count($DstIds))
909  {
910  # construct and execute query to perform copy
911  $Query = "UPDATE `".$TableName."` AS Target"
912  ." LEFT JOIN `".$TableName."` AS Source"
913  ." ON Source.`".$IdColumn."` = '".addslashes($SrcId)."'";
914  $QuerySets = array();
915  foreach ($ColumnsToCopy as $ColumnName)
916  {
917  $QuerySets[] = "Target.`".$ColumnName."` = Source.`".$ColumnName."`";
918  }
919  $Query .= " SET ".implode(", ", $QuerySets);
920  $QueryConditions = array();
921  foreach ($DstIds as $Id)
922  {
923  $QueryConditions[] = "Target.`".$IdColumn."` = '".addslashes($DstId)."'";
924  }
925  $Query .= " WHERE ".implode(" OR ", $QueryConditions);
926  $this->Query($Query);
927  }
928  }
929 
943  public function InsertArray($Table, $ValueField, $Values,
944  $KeyField = NULL, $AvgDataLength = 20)
945  {
946  # pick some ballpark values
947  $ChunkSizeAssumedSafe = 100;
948  $QueryLengthAssumedSafe = 10486576; # (1 MB)
949 
950  # exit without doing anything if there are no values
951  $ValueCount = count($Values);
952  if ($ValueCount == 0)
953  {
954  return;
955  }
956 
957  # determine size of array chunk per INSERT statement
958  $NonValueCharCount = 100;
959  if ($ValueCount > $ChunkSizeAssumedSafe)
960  {
961  $MaxQueryLen = $this->GetMaxQueryLength();
962  $ValueSegmentLen = $AvgDataLength + 6;
963  if ($KeyField !== NULL)
964  {
965  $ValueSegmentLen = $ValueSegmentLen * 2;
966  }
967  $ValueChunkSize = $MaxQueryLen / $ValueSegmentLen;
968  }
969  else
970  {
971  $ValueChunkSize = $ChunkSizeAssumedSafe;
972  }
973 
974  # for each chunk of values
975  foreach (array_chunk($Values, $ValueChunkSize, TRUE) as $ValueChunk)
976  {
977  # begin building query
978  $Query = "INSERT INTO `".$Table."` (`".$ValueField."`";
979 
980  # if key field was specified
981  if ($KeyField !== NULL)
982  {
983  # add key field to query
984  $Query .= ", `".$KeyField."`";
985 
986  # assemble value segment with keys
987  $ValueSegFunc = function($Carry, $Key) use ($ValueChunk)
988  {
989  $Carry .= "('".addslashes($ValueChunk[$Key])."','"
990  .addslashes($Key)."'),";
991  return $Carry;
992  };
993  $ValueSegment = array_reduce(array_keys($ValueChunk), $ValueSegFunc);
994  }
995  else
996  {
997  # assemble value segment
998  $ValueSegFunc = function($Carry, $Value)
999  {
1000  $Carry .= "('".addslashes($Value)."'),";
1001  return $Carry;
1002  };
1003  $ValueSegment = array_reduce($ValueChunk, $ValueSegFunc);
1004  }
1005 
1006  # trim extraneous comma off of value segment
1007  $ValueSegment = substr($ValueSegment, 0, -1);
1008 
1009  # add value segment to query
1010  $Query .= ") VALUES ".$ValueSegment;
1011 
1012  # double check to make sure query isn't too long
1013  $QueryLen = strlen($Query);
1014  if ($QueryLen > $QueryLengthAssumedSafe)
1015  {
1016  if (!isset($MaxQueryLen))
1017  {
1018  $MaxQueryLen = $this->GetMaxQueryLength();
1019  }
1020  if ($QueryLen > $MaxQueryLen)
1021  {
1022  throw new Exception("Maximum query length ("
1023  .$MaxQueryLen.") exceeded (".$QueryLen.").");
1024  }
1025  }
1026 
1027  # run query
1028  $this->Query($Query);
1029  }
1030  }
1031 
1032  /*@)*/ /* Data Manipulation */ /*@(*/
1034 
1043  public function EscapeString($String)
1044  {
1045  return mysqli_real_escape_string($this->Handle, $String);
1046  }
1047 
1054  public function LogComment($String)
1055  {
1056  $this->Query("-- ".$String);
1057  }
1058 
1064  public function TableExists($TableName)
1065  {
1066  $this->Query("SHOW TABLES LIKE '".addslashes($TableName)."'");
1067  return $this->NumRowsSelected() ? TRUE : FALSE;
1068  }
1069 
1076  public function FieldExists($TableName, $FieldName)
1077  {
1078  $this->Query("DESC ".$TableName);
1079  while ($CurrentFieldName = $this->FetchField("Field"))
1080  {
1081  if ($CurrentFieldName == $FieldName) { return TRUE; }
1082  }
1083  return FALSE;
1084  }
1085 
1092  public function GetFieldType($TableName, $FieldName)
1093  {
1094  $this->Query("DESC ".$TableName);
1095  $AllTypes = $this->FetchColumn("Type", "Field");
1096  return $AllTypes[$FieldName];
1097  }
1098 
1104  public function GetColumns($TableName)
1105  {
1106  $this->Query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS"
1107  ." WHERE TABLE_SCHEMA = '".addslashes($this->DBName)
1108  ."' AND TABLE_NAME = '".addslashes($TableName)."'");
1109  return $this->FetchColumn("COLUMN_NAME");
1110  }
1111 
1116  public function GetMaxQueryLength()
1117  {
1118  return $this->Query("SHOW VARIABLES LIKE 'max_allowed_packet'",
1119  "Value");
1120  }
1121 
1127  public static function QueryDebugOutput($NewSetting)
1128  {
1129  self::$QueryDebugOutputFlag = $NewSetting;
1130  }
1131 
1137  public static function NumQueries()
1138  {
1139  return self::$QueryCounter;
1140  }
1141 
1148  public static function NumCacheHits()
1149  {
1150  return self::$CachedQueryCounter;
1151  }
1152 
1158  public static function CacheHitRate()
1159  {
1160  if (self::$QueryCounter)
1161  {
1162  return (self::$CachedQueryCounter / self::$QueryCounter) * 100;
1163  }
1164  else
1165  {
1166  return 0;
1167  }
1168  }
1169 
1170  /*@)*/ /* Miscellaneous */
1171 
1172  # ---- PRIVATE INTERFACE -------------------------------------------------
1173 
1174  protected $DBUserName;
1175  protected $DBPassword;
1176  protected $DBHostName;
1177  protected $DBName;
1178 
1179  private $Handle;
1180  private $QueryHandle;
1181  private $QueryResults;
1182  private $RowCounter;
1183  private $NumRows;
1184  private $GetResultsFromCache;
1185  private $ErrorIgnored = FALSE;
1186  private $ErrorsToIgnore = NULL;
1187  private $ErrMsg = NULL;
1188  private $ErrNo = NULL;
1189 
1190  private static $DisplayErrors = FALSE;
1191 
1192  private static $GlobalDBUserName;
1193  private static $GlobalDBPassword;
1194  private static $GlobalDBHostName;
1195  private static $GlobalDBName;
1196 
1197  # debug output flag
1198  private static $QueryDebugOutputFlag = FALSE;
1199  # flag for whether caching is turned on
1200  private static $CachingFlag = TRUE;
1201  # query result advanced caching flag
1202  private static $AdvancedCachingFlag = FALSE;
1203  # global cache for query results
1204  private static $QueryResultCache = array();
1205  # stats counters
1206  private static $QueryCounter = 0;
1207  private static $CachedQueryCounter = 0;
1208  # database connection link handles
1209  private static $ConnectionHandles = array();
1210  # do not cache queries that return more than this number of rows
1211  private static $CacheRowsThreshold = 250;
1212  # prune the query cache if there is less than this amount of memory free
1213  private static $CacheMemoryThreshold;
1214  # number of rows to leave in cache when pruning
1215  private static $CacheRowsToLeave = 10;
1216  # number of retry attempts to make to connect to database
1217  private static $ConnectRetryAttempts = 3;
1218  # number of seconds to wait between connection retry attempts
1219  private static $ConnectRetryInterval = 5;
1220 
1221  # server connection error codes
1222  const CR_CONNECTION_ERROR = 2002; # Can't connect to local MySQL server
1223  # through socket '%s' (%d)
1224  const CR_CONN_HOST_ERROR = 2003; # Can't connect to MySQL server on '%s' (%d)
1225  const CR_SERVER_GONE_ERROR = 2006; # MySQL server has gone away
1226  const CR_SERVER_LOST = 2013; # Lost connection to MySQL server during query
1227 
1228  # connection error codes that may be recoverable
1229  private static $RecoverableConnectionErrors = array(
1230  self::CR_CONNECTION_ERROR,
1231  );
1232 
1243  private static function ConnectAndSelectDB(
1245  {
1246  $ConnectAttemptsLeft = self::$ConnectRetryAttempts + 1;
1247  do
1248  {
1249  # if this is not our first connection attempt
1250  if (isset($Handle))
1251  {
1252  # wait for the retry interval
1253  sleep(self::$ConnectRetryInterval);
1254  }
1255 
1256  # attempt to connect to server
1257  $Handle = @mysqli_connect($DBHostName, $DBUserName, $DBPassword);
1258  $ConnectAttemptsLeft--;
1259  }
1260  # repeat if we do not have a connection and there are retry attempts
1261  # left and the connection error code indicates a retry may succeed
1262  // @codingStandardsIgnoreStart
1263  // (because phpcs apparently doesn't know how to handle do-while loops)
1264  while (!$Handle && $ConnectAttemptsLeft
1265  && in_array(mysqli_connect_errno(),
1266  self::$RecoverableConnectionErrors));
1267  // @codingStandardsIgnoreEnd
1268 
1269  # throw exception if connection attempts failed
1270  if (!$Handle)
1271  {
1272  throw new Exception("Could not connect to database: "
1273  .mysqli_connect_error()." (errno: ".mysqli_connect_errno().")");
1274  }
1275 
1276  # select DB
1277  $Result = mysqli_select_db($Handle, $DBName);
1278  if ($Result !== TRUE)
1279  {
1280  throw new Exception("Could not select database: "
1281  .mysqli_error($Handle)." (errno: ".mysqli_errno($Handle).")");
1282  }
1283 
1284  # return new connection to caller
1285  return $Handle;
1286  }
1287 
1293  private function IsReadOnlyStatement($QueryString)
1294  {
1295  return preg_match("/^[ ]*SELECT /i", $QueryString) ? TRUE : FALSE;
1296  }
1297 
1304  private function TableModified($QueryString)
1305  {
1306  # assume we're not going to be able to determine table
1307  $TableName = FALSE;
1308 
1309  # split query into pieces
1310  $QueryString = trim($QueryString);
1311  $Words = preg_split("/\s+/", $QueryString);
1312 
1313  # if INSERT statement
1314  $WordIndex = 1;
1315  if (strtoupper($Words[0]) == "INSERT")
1316  {
1317  # skip over modifying keywords
1318  while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
1319  || (strtoupper($Words[$WordIndex]) == "DELAYED")
1320  || (strtoupper($Words[$WordIndex]) == "IGNORE")
1321  || (strtoupper($Words[$WordIndex]) == "INTO"))
1322  {
1323  $WordIndex++;
1324  }
1325 
1326  # next word is table name
1327  $TableName = $Words[$WordIndex];
1328  }
1329  # else if UPDATE statement
1330  elseif (strtoupper($Words[0]) == "UPDATE")
1331  {
1332  # skip over modifying keywords
1333  while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
1334  || (strtoupper($Words[$WordIndex]) == "IGNORE"))
1335  {
1336  $WordIndex++;
1337  }
1338 
1339  # if word following next word is SET
1340  if (strtoupper($Words[$WordIndex + 1]) == "SET")
1341  {
1342  # next word is table name
1343  $TableName = $Words[$WordIndex];
1344  }
1345  }
1346  # else if DELETE statement
1347  elseif (strtoupper($Words[0]) == "DELETE")
1348  {
1349  # skip over modifying keywords
1350  while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
1351  || (strtoupper($Words[$WordIndex]) == "IGNORE")
1352  || (strtoupper($Words[$WordIndex]) == "QUICK"))
1353  {
1354  $WordIndex++;
1355  }
1356 
1357  # if next term is FROM
1358  if (strtoupper($Words[$WordIndex]) == "FROM")
1359  {
1360  # next word is table name
1361  $WordIndex++;
1362  $TableName = $Words[$WordIndex];
1363  }
1364  }
1365 
1366  # discard table name if it looks at all suspicious
1367  if ($TableName)
1368  {
1369  if (!preg_match("/[a-zA-Z0-9]+/", $TableName))
1370  {
1371  $TableName = FALSE;
1372  }
1373  }
1374 
1375  # return table name (or lack thereof) to caller
1376  return $TableName;
1377  }
1378 
1385  private function TablesAccessed($QueryString)
1386  {
1387  # assume we're not going to be able to determine tables
1388  $TableNames = FALSE;
1389 
1390  # split query into pieces
1391  $QueryString = trim($QueryString);
1392  $Words = preg_split("/\s+/", $QueryString);
1393  $UQueryString = strtoupper($QueryString);
1394  $UWords = preg_split("/\s+/", $UQueryString);
1395 
1396  # if SELECT statement
1397  if ($UWords[0] == "SELECT")
1398  {
1399  # keep going until we hit FROM or last word
1400  $WordIndex = 1;
1401  while (($UWords[$WordIndex] != "FROM")
1402  && strlen($UWords[$WordIndex]))
1403  {
1404  $WordIndex++;
1405  }
1406 
1407  # if we hit FROM
1408  if ($UWords[$WordIndex] == "FROM")
1409  {
1410  # for each word after FROM
1411  $WordIndex++;
1412  while (strlen($UWords[$WordIndex]))
1413  {
1414  # if current word ends with comma
1415  if (preg_match("/,$/", $Words[$WordIndex]))
1416  {
1417  # strip off comma and add word to table name list
1418  $TableNames[] = substr($Words[$WordIndex], 0, -1);
1419  }
1420  else
1421  {
1422  # add word to table name list
1423  $TableNames[] = $Words[$WordIndex];
1424 
1425  # if next word is not comma
1426  $WordIndex++;
1427  if ($Words[$WordIndex] != ",")
1428  {
1429  # if word begins with comma
1430  if (preg_match("/^,/", $Words[$WordIndex]))
1431  {
1432  # strip off comma (NOTE: modifies $Words array!)
1433  $Words[$WordIndex] = substr($Words[$WordIndex], 1);
1434 
1435  # decrement index so we start with this word next pass
1436  $WordIndex--;
1437  }
1438  else
1439  {
1440  # stop scanning words (non-basic JOINs not yet handled)
1441  break;
1442  }
1443  }
1444  }
1445 
1446  # move to next word
1447  $WordIndex++;
1448  }
1449  }
1450  }
1451 
1452  # discard table names if they look at all suspicious
1453  if ($TableNames)
1454  {
1455  foreach ($TableNames as $Name)
1456  {
1457  if (!preg_match("/^[a-zA-Z0-9]+$/", $Name))
1458  {
1459  $TableNames = FALSE;
1460  break;
1461  }
1462  }
1463  }
1464 
1465  # return table name (or lack thereof) to caller
1466  return $TableNames;
1467  }
1468 
1475  private function RunQuery($QueryString)
1476  {
1477  # log query start time if debugging output is enabled
1478  if (self::$QueryDebugOutputFlag) { $QueryStartTime = microtime(TRUE); }
1479 
1480  # run query against database
1481  $this->QueryHandle = mysqli_query($this->Handle, $QueryString) ;
1482 
1483  # print query and execution time if debugging output is enabled
1484  if (self::$QueryDebugOutputFlag)
1485  {
1486  print "DB: ".$QueryString." ["
1487  .sprintf("%.2f", microtime(TRUE) - $QueryStartTime)
1488  ."s]"."<br>\n";
1489  }
1490 
1491  # if query failed and there are errors that we can ignore
1492  if (($this->QueryHandle === FALSE) && $this->ErrorsToIgnore)
1493  {
1494  # for each pattern for an error that we can ignore
1495  foreach ($this->ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
1496  {
1497  # if error matches pattern
1498  $ErrorMsg = mysqli_error($this->Handle);
1499  if (preg_match($SqlPattern, $QueryString)
1500  && preg_match($ErrMsgPattern, $ErrorMsg))
1501  {
1502  # set return value to indicate error was ignored
1503  $this->QueryHandle = TRUE;
1504 
1505  # set internal flag to indicate that an error was ignored
1506  $this->ErrorIgnored = $ErrorMsg;
1507 
1508  # stop looking at patterns
1509  break;
1510  }
1511  }
1512  }
1513 
1514  # if query failed
1515  if ($this->QueryHandle === FALSE)
1516  {
1517  # clear stored value for number of rows retrieved
1518  $this->NumRows = 0;
1519 
1520  # retrieve error info
1521  $this->ErrMsg = mysqli_error($this->Handle);
1522  $this->ErrNo = mysqli_errno($this->Handle);
1523 
1524  # if we are supposed to be displaying errors
1525  if (self::$DisplayErrors)
1526  {
1527  # print error info
1528  print("<b>SQL Error:</b> <i>".$this->ErrMsg
1529  ."</i> (".$this->ErrNo.")<br/>\n");
1530  print("<b>SQL Statement:</b> <i>"
1531  .htmlspecialchars($QueryString)."</i><br/>\n");
1532 
1533  # retrieve execution trace that got us to this point
1534  $Trace = debug_backtrace();
1535 
1536  # remove current context from trace
1537  array_shift($Trace);
1538 
1539  # make sure file name and line number are available
1540  foreach ($Trace as $Index => $Loc)
1541  {
1542  if (!array_key_exists("file", $Loc))
1543  {
1544  $Trace[$Index]["file"] = "UNKNOWN";
1545  }
1546  if (!array_key_exists("line", $Loc))
1547  {
1548  $Trace[$Index]["line"] = "??";
1549  }
1550  }
1551 
1552  # determine length of leading path common to all file names in trace
1553  $LocString = "";
1554  $OurFile = __FILE__;
1555  $PrefixLen = 9999;
1556  foreach ($Trace as $Loc)
1557  {
1558  if ($Loc["file"] != "UNKNOWN")
1559  {
1560  $Index = 0;
1561  $FNameLength = strlen($Loc["file"]);
1562  while ($Index < $FNameLength &&
1563  $Loc["file"][$Index] == $OurFile[$Index])
1564  { $Index++; }
1565  $PrefixLen = min($PrefixLen, $Index);
1566  }
1567  }
1568 
1569  foreach ($Trace as $Loc)
1570  {
1571  $Sep = "";
1572  $ArgString = "";
1573  foreach ($Loc["args"] as $Arg)
1574  {
1575  $ArgString .= $Sep;
1576  switch (gettype($Arg))
1577  {
1578  case "boolean":
1579  $ArgString .= $Arg ? "TRUE" : "FALSE";
1580  break;
1581 
1582  case "integer":
1583  case "double":
1584  $ArgString .= $Arg;
1585  break;
1586 
1587  case "string":
1588  $ArgString .= '"<i>'.htmlspecialchars(substr($Arg, 0, 40))
1589  .((strlen($Arg) > 40) ? "..." : "").'</i>"';
1590  break;
1591 
1592  case "array":
1593  case "resource":
1594  case "NULL":
1595  $ArgString .= strtoupper(gettype($Arg));
1596  break;
1597 
1598  case "object":
1599  $ArgString .= get_class($Arg);
1600  break;
1601 
1602  case "unknown type":
1603  $ArgString .= "UNKNOWN";
1604  break;
1605  }
1606  $Sep = ",";
1607  }
1608  $Loc["file"] = substr($Loc["file"], $PrefixLen);
1609  $LocString .= "&nbsp;&nbsp;";
1610  if (array_key_exists("class", $Loc))
1611  { $LocString .= $Loc["class"]."::"; }
1612  $LocString .= $Loc["function"]."(".$ArgString.")"
1613  ." - ".$Loc["file"].":".$Loc["line"]
1614  ."<br>\n";
1615  }
1616  print("<b>Trace:</b><br>\n".$LocString);
1617  }
1618  }
1619  return $this->QueryHandle;
1620  }
1621 
1626  static private function GetPhpMemoryLimit()
1627  {
1628  $Str = strtoupper(ini_get("memory_limit"));
1629  if (substr($Str, -1) == "B") { $Str = substr($Str, 0, strlen($Str) - 1); }
1630  switch (substr($Str, -1))
1631  {
1632  case "K":
1633  $MemoryLimit = (int)$Str * 1024;
1634  break;
1635 
1636  case "M":
1637  $MemoryLimit = (int)$Str * 1048576;
1638  break;
1639 
1640  case "G":
1641  $MemoryLimit = (int)$Str * 1073741824;
1642  break;
1643 
1644  default:
1645  $MemoryLimit = (int)$Str;
1646  break;
1647  }
1648  return $MemoryLimit;
1649  }
1650 
1655  static private function GetFreeMemory()
1656  {
1657  return self::GetPhpMemoryLimit() - memory_get_usage();
1658  }
1659 }
1660 
1661 # define return values (numerical values correspond to MySQL error codes)
1662 // @codingStandardsIgnoreStart (to silence warning about multiple spaces)
1663 define("DB_OKAY", 0);
1664 define("DB_ERROR", 1);
1665 define("DB_ACCESSDENIED", 2);
1666 define("DB_UNKNOWNDB", 3);
1667 define("DB_UNKNOWNTABLE", 4);
1668 define("DB_SYNTAXERROR", 5);
1669 define("DB_DBALREADYEXISTS", 6);
1670 define("DB_DBDOESNOTEXIST", 7);
1671 define("DB_DISKFULL", 8);
1672 // @codingStandardsIgnoreEnd
1673 
1674 # define value to designate omitted arguments (so DB values can be set to NULL)
1675 define("DB_NOVALUE", "!-_-_-DB_NOVALUE-_-_-!");
1676 
1677 # MySQL error code mapping
1679  1045 => DB_ACCESSDENIED,
1680  1049 => DB_UNKNOWNDB,
1681  1046 => DB_UNKNOWNTABLE,
1682  1064 => DB_SYNTAXERROR,
1683  1007 => DB_DBALREADYEXISTS, # ? (not sure)
1684  1008 => DB_DBDOESNOTEXIST, # ? (not sure)
1685  1021 => DB_DISKFULL, # ? (not sure)
1686  );
1687 
1688 # date() format for SQL dates
1689 define("DATE_SQL", "Y-m-d H:i:s");
QueryErrMsg()
Get most recent error message text set by Query().
Definition: Database.php:590
static Caching($NewSetting=NULL)
Get or set whether query result caching is currently enabled.
Definition: Database.php:259
__sleep()
Definition: Database.php:81
GetServerVersion($FullVersion=FALSE)
Get database server version number.
Definition: Database.php:183
static SetGlobalDatabaseName($DatabaseName)
Set default database name.
Definition: Database.php:154
const CR_CONNECTION_ERROR
Definition: Database.php:1222
const DB_ACCESSDENIED
Definition: Database.php:1665
SetDefaultStorageEngine($Engine)
Set default database storage engine.
Definition: Database.php:167
ExecuteQueriesFromFile($FileName)
Execute queries from specified file.
Definition: Database.php:531
UpdateIntValue($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
A convenience function to get or set an integer value in the database.
Definition: Database.php:850
SQL database abstraction object with smart query caching.
Definition: Database.php:22
SetQueryErrorsToIgnore($ErrorsToIgnore, $NormalizeWhitespace=TRUE)
Set query errors to ignore.
Definition: Database.php:313
const CR_SERVER_GONE_ERROR
Definition: Database.php:1225
DBUserName()
Get name used to connect with database server.
Definition: Database.php:247
EscapeString($String)
Escape a string that may contain null bytes.
Definition: Database.php:1043
FetchRow()
Get next database row retrieved by most recent query.
Definition: Database.php:664
LastInsertId()
Get ID of row added by the last SQL "INSERT" statement.
Definition: Database.php:782
const CR_SERVER_LOST
Definition: Database.php:1226
const DB_SYNTAXERROR
Definition: Database.php:1668
__construct($UserName=NULL, $Password=NULL, $DatabaseName=NULL, $HostName=NULL)
Object constructor.
Definition: Database.php:43
TableExists($TableName)
Get whether specified table exists.
Definition: Database.php:1064
const DB_DISKFULL
Definition: Database.php:1671
static SetGlobalServerInfo($UserName, $Password, $HostName="localhost")
Set default login and host info for database server.
Definition: Database.php:138
$APDBErrorCodeMappings
Definition: Database.php:1678
GetClientVersion()
Get version number of the client libraries being used to connect to the database server (Currently th...
Definition: Database.php:207
GetFieldType($TableName, $FieldName)
Get field (column) type.
Definition: Database.php:1092
const DB_NOVALUE
Definition: Database.php:1675
NumRowsSelected()
Get number of rows returned by last SELECT or SHOW query.
Definition: Database.php:621
FetchRows($NumberOfRows=NULL)
Get specified number of database rows retrieved by most recent query.
Definition: Database.php:710
static QueryDebugOutput($NewSetting)
Enable or disable debugging output for queries.
Definition: Database.php:1127
Query($QueryString, $FieldName="")
Query database (with caching if enabled).
Definition: Database.php:351
GetColumns($TableName)
Get column (database field) names.
Definition: Database.php:1104
FetchField($FieldName)
Pull next row from last DB query and get a specific value from that row.
Definition: Database.php:770
FieldExists($TableName, $FieldName)
Get whether specified field exists in specified table.
Definition: Database.php:1076
static NumCacheHits()
Get the number of queries that have resulted in cache hits since program execution began...
Definition: Database.php:1148
const DB_UNKNOWNTABLE
Definition: Database.php:1667
FetchColumn($FieldName, $IndexFieldName=NULL)
Get all available values for specified database field retrieved by most recent query.
Definition: Database.php:745
DBHostName()
Get host name of system on which database server resides.
Definition: Database.php:227
const DB_DBDOESNOTEXIST
Definition: Database.php:1670
NumRowsAffected()
Get number of rows affected by last INSERT, UPDATE, REPLACE, or DELETE query.
Definition: Database.php:647
UpdateFloatValue($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
A convenience function to get or set a float value in the database.
Definition: Database.php:874
const DB_DBALREADYEXISTS
Definition: Database.php:1669
InsertArray($Table, $ValueField, $Values, $KeyField=NULL, $AvgDataLength=20)
Insert an array of values with a minimum number of INSERT statements.
Definition: Database.php:943
QueryErrNo()
Get most recent error code set by Query().
Definition: Database.php:600
const DB_UNKNOWNDB
Definition: Database.php:1666
GetHostInfo()
Get database connection type and hostname.
Definition: Database.php:217
UpdateValue($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
A convenience function to get or set a value in the database.
Definition: Database.php:803
static AdvancedCaching($NewSetting=NULL)
Get or set whether advanced query result cachine is currently enabled.
Definition: Database.php:285
static DisplayQueryErrors($NewValue=NULL)
Get/set whether Query() errors will be displayed.
Definition: Database.php:611
static CacheHitRate()
Get the ratio of query cache hits to queries as a percentage.
Definition: Database.php:1158
GetMaxQueryLength()
Get maximum size for query string.
Definition: Database.php:1116
IgnoredError()
Check whether an error was ignored by the most recent query.
Definition: Database.php:333
DBName()
Get current database name.
Definition: Database.php:237
CopyValues($TableName, $IdColumn, $SrcId, $DstId, $ColumnsToExclude=array())
A convenience function to copy values from one row to another.
Definition: Database.php:893
LogComment($String)
Peform query that consists of SQL comment statement.
Definition: Database.php:1054
__wakeup()
Restore database connection when unserialized.
Definition: Database.php:90
static NumQueries()
Get the number of queries that have been run since program execution began.
Definition: Database.php:1137