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  $this->Handle = self::ConnectAndSelectDB($this->DBHostName,
98  $this->DBUserName, $this->DBPassword, $this->DBName);
99  self::$ConnectionHandles[$HandleIndex] = $this->Handle;
100  }
101  else
102  {
103  # set local connection handle
104  $this->Handle = self::$ConnectionHandles[$HandleIndex];
105  }
106  }
116  public static function SetGlobalServerInfo(
117  $UserName, $Password, $HostName = "localhost")
118  {
119  # save default DB access parameters
120  self::$GlobalDBUserName = $UserName;
121  self::$GlobalDBPassword = $Password;
122  self::$GlobalDBHostName = $HostName;
123 
124  # clear any existing DB connection handles
125  self::$ConnectionHandles = array();
126  }
127 
132  public static function SetGlobalDatabaseName($DatabaseName)
133  {
134  # save new default DB name
135  self::$GlobalDBName = $DatabaseName;
136 
137  # clear any existing DB connection handles
138  self::$ConnectionHandles = array();
139  }
140 
145  public function SetDefaultStorageEngine($Engine)
146  {
147  # choose config variable to use based on server version number
148  $ConfigVar = version_compare($this->GetServerVersion(), "5.5", "<")
149  ? "storage_engine" : "default_storage_engine";
150 
151  # set storage engine in database
152  $this->Query("SET ".$ConfigVar." = ".$Engine);
153  }
154 
161  public function GetServerVersion($FullVersion=FALSE)
162  {
163  # retrieve version string
164  $Version = $this->Query("SELECT VERSION() AS ServerVer", "ServerVer");
165 
166  if (!$FullVersion)
167  {
168  # strip off any build/config suffix
169  $Pieces = explode("-", $Version);
170  $Version = array_shift($Pieces);
171  }
172 
173  # return version number to caller
174  return $Version;
175  }
176 
185  public function GetClientVersion()
186  {
187  return mysqli_get_client_info();
188  }
189 
195  public function GetHostInfo()
196  {
197  return mysqli_get_host_info($this->Handle);
198  }
199 
205  public function DBHostName()
206  {
207  return $this->DBHostName;
208  }
209 
215  public function DBName()
216  {
217  return $this->DBName;
218  }
219 
225  public function DBUserName()
226  {
227  return $this->DBUserName;
228  }
229 
237  public static function Caching($NewSetting = NULL)
238  {
239  # if cache setting has changed
240  if (($NewSetting !== NULL) && ($NewSetting != self::$CachingFlag))
241  {
242  # save new setting
243  self::$CachingFlag = $NewSetting;
244 
245  # clear any existing cached results
246  self::$QueryResultCache = array();
247  }
248 
249  # return current setting to caller
250  return self::$CachingFlag;
251  }
252 
263  public static function AdvancedCaching($NewSetting = NULL)
264  {
265  if ($NewSetting !== NULL)
266  {
267  self::$AdvancedCachingFlag = $NewSetting;
268  }
269  return self::$AdvancedCachingFlag;
270  }
271 
291  public function SetQueryErrorsToIgnore($ErrorsToIgnore, $NormalizeWhitespace = TRUE)
292  {
293  if ($NormalizeWhitespace && ($ErrorsToIgnore !== NULL))
294  {
295  $RevisedErrorsToIgnore = array();
296  foreach ($ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
297  {
298  $SqlPattern = preg_replace("/\\s+/", "\\s+", $SqlPattern);
299  $RevisedErrorsToIgnore[$SqlPattern] = $ErrMsgPattern;
300  }
301  $ErrorsToIgnore = $RevisedErrorsToIgnore;
302  }
303  $this->ErrorsToIgnore = $ErrorsToIgnore;
304  }
305 
311  public function IgnoredError()
312  {
313  return $this->ErrorIgnored;
314  }
315 
316  /*@)*/ /* Setup/Initialization */ /*@(*/
318 
329  public function Query($QueryString, $FieldName = "")
330  {
331  # clear flag that indicates whether query error was ignored
332  $this->ErrorIgnored = FALSE;
333 
334  # if caching is enabled
335  if (self::$CachingFlag)
336  {
337  # if SQL statement is read-only
338  if ($this->IsReadOnlyStatement($QueryString))
339  {
340  # if we have statement in cache
341  if (isset(self::$QueryResultCache[$QueryString]["NumRows"]))
342  {
343  if (self::$QueryDebugOutputFlag)
344  { print("DB-C: $QueryString<br>\n"); }
345 
346  # make sure query result looks okay
347  $this->QueryHandle = TRUE;
348 
349  # increment cache hit counter
350  self::$CachedQueryCounter++;
351 
352  # make local copy of results
353  $this->QueryResults = self::$QueryResultCache[$QueryString];
354  $this->NumRows = self::$QueryResultCache[$QueryString]["NumRows"];
355 
356  # set flag to indicate that results should be retrieved from cache
357  $this->GetResultsFromCache = TRUE;
358  }
359  else
360  {
361  # execute SQL statement
362  $this->QueryHandle = $this->RunQuery($QueryString);
363  if (!$this->QueryHandle instanceof mysqli_result) { return FALSE; }
364 
365  # save number of rows in result
366  $this->NumRows = mysqli_num_rows($this->QueryHandle);
367 
368  # if too many rows to cache
369  if ($this->NumRows >= self::$CacheRowsThreshold)
370  {
371  # set flag to indicate that query results should not
372  # be retrieved from cache
373  $this->GetResultsFromCache = FALSE;
374  }
375  else
376  {
377  # if we are low on memory
378  if (self::GetFreeMemory() < self::$CacheMemoryThreshold)
379  {
380  # clear out all but last few rows from cache
381  self::$QueryResultCache = array_slice(
382  self::$QueryResultCache,
383  (0 - self::$CacheRowsToLeave));
384  }
385 
386  # if advanced caching is enabled
387  if (self::$AdvancedCachingFlag)
388  {
389  # save tables accessed by query
390  self::$QueryResultCache[$QueryString]["TablesAccessed"] =
391  $this->TablesAccessed($QueryString);
392  }
393 
394  # if rows found
395  if ($this->NumRows > 0)
396  {
397  # load query results
398  for ($Row = 0; $Row < $this->NumRows; $Row++)
399  {
400  $this->QueryResults[$Row] =
401  mysqli_fetch_assoc($this->QueryHandle);
402  }
403 
404  # cache query results
405  self::$QueryResultCache[$QueryString] = $this->QueryResults;
406  }
407  else
408  {
409  # clear local query results
410  unset($this->QueryResults);
411  }
412 
413  # cache number of rows
414  self::$QueryResultCache[$QueryString]["NumRows"] = $this->NumRows;
415 
416  # set flag to indicate that query results should be
417  # retrieved from cache
418  $this->GetResultsFromCache = TRUE;
419  }
420  }
421  }
422  else
423  {
424  # if advanced caching is enabled
425  if (self::$AdvancedCachingFlag)
426  {
427  # if table modified by statement is known
428  $TableModified = $this->TableModified($QueryString);
429  if ($TableModified)
430  {
431  # for each cached query
432  foreach (self::$QueryResultCache
433  as $CachedQueryString => $CachedQueryResult)
434  {
435  # if we know what tables were accessed
436  if ($CachedQueryResult["TablesAccessed"])
437  {
438  # if tables accessed include the one we may modify
439  if (in_array($TableModified,
440  $CachedQueryResult["TablesAccessed"]))
441  {
442  # clear cached query results
443  unset(self::$QueryResultCache[$CachedQueryString]);
444  }
445  }
446  else
447  {
448  # clear cached query results
449  unset(self::$QueryResultCache[$CachedQueryString]);
450  }
451  }
452  }
453  else
454  {
455  # clear entire query result cache
456  self::$QueryResultCache = array();
457  }
458  }
459  else
460  {
461  # clear entire query result cache
462  self::$QueryResultCache = array();
463  }
464 
465  # execute SQL statement
466  $this->QueryHandle = $this->RunQuery($QueryString);
467  if ($this->QueryHandle === FALSE) { return FALSE; }
468 
469  # set flag to indicate that query results should not be
470  # retrieved from cache
471  $this->GetResultsFromCache = FALSE;
472  }
473 
474  # reset row counter
475  $this->RowCounter = 0;
476 
477  # increment query counter
478  self::$QueryCounter++;
479  }
480  else
481  {
482  # execute SQL statement
483  $this->QueryHandle = $this->RunQuery($QueryString);
484  if ($this->QueryHandle === FALSE) { return FALSE; }
485  }
486 
487  if (($FieldName != "") && ($this->QueryHandle !== FALSE))
488  {
489  return $this->FetchField($FieldName);
490  }
491  else
492  {
493  return $this->QueryHandle;
494  }
495  }
496 
509  public function ExecuteQueriesFromFile($FileName)
510  {
511  # open file
512  $FHandle = fopen($FileName, "r");
513 
514  # if file open succeeded
515  if ($FHandle !== FALSE)
516  {
517  # while lines left in file
518  $Query = "";
519  $QueryCount = 0;
520  while (!feof($FHandle))
521  {
522  # read in line from file
523  $Line = fgets($FHandle, 32767);
524 
525  # trim whitespace from line
526  $Line = trim($Line);
527 
528  # if line is not empty and not a comment
529  if (!preg_match("/^#/", $Line)
530  && !preg_match("/^--/", $Line)
531  && strlen($Line))
532  {
533  # add line to current query
534  $Query .= " ".$Line;
535 
536  # if line completes a query
537  if (preg_match("/;$/", $Line))
538  {
539  # run query
540  $QueryCount++;
541  $Result = $this->Query($Query);
542  $Query = "";
543 
544  # if query resulted in an error that is not ignorable
545  if ($Result === FALSE)
546  {
547  # stop processing queries and set error code
548  $QueryCount = NULL;
549  break;
550  }
551  }
552  }
553  }
554 
555  # close file
556  fclose($FHandle);
557  }
558 
559  # return number of executed queries to caller
560  return $QueryCount;
561  }
562 
568  public function QueryErrMsg()
569  {
570  return $this->ErrMsg;
571  }
572 
578  public function QueryErrNo()
579  {
580  return $this->ErrNo;
581  }
582 
589  public static function DisplayQueryErrors($NewValue = NULL)
590  {
591  if ($NewValue !== NULL) { self::$DisplayErrors = $NewValue; }
592  return self::$DisplayErrors;
593  }
594 
599  public function NumRowsSelected()
600  {
601  # if caching is enabled and query was cached
602  if (self::$CachingFlag && $this->GetResultsFromCache)
603  {
604  # return cached number of rows to caller
605  return $this->NumRows;
606  }
607  else
608  {
609  # call to this method after an unsuccessful query
610  if (!$this->QueryHandle instanceof mysqli_result)
611  {
612  return 0;
613  }
614 
615  # retrieve number of rows and return to caller
616  return mysqli_num_rows($this->QueryHandle);
617  }
618  }
619 
625  public function NumRowsAffected()
626  {
627  # call to this method after an unsuccessful query
628  if (!$this->QueryHandle instanceof mysqli_result)
629  {
630  return 0;
631  }
632 
633  # retrieve number of rows and return to caller
634  return mysqli_affected_rows($this->Handle);
635  }
636 
642  public function FetchRow()
643  {
644  # if caching is enabled and query was cached
645  if (self::$CachingFlag && $this->GetResultsFromCache)
646  {
647  # if rows left to return
648  if ($this->RowCounter < $this->NumRows)
649  {
650  # retrieve row from cache
651  $Result = $this->QueryResults[$this->RowCounter];
652 
653  # increment row counter
654  $this->RowCounter++;
655  }
656  else
657  {
658  # return nothing
659  $Result = FALSE;
660  }
661  }
662  else
663  {
664  # call to this method after successful query
665  if ($this->QueryHandle instanceof mysqli_result)
666  {
667  $Result = mysqli_fetch_assoc($this->QueryHandle);
668  if ($Result === NULL) { $Result = FALSE; }
669  }
670 
671  # call to this method after unsuccessful query
672  else
673  {
674  $Result = FALSE;
675  }
676  }
677 
678  # return row to caller
679  return $Result;
680  }
681 
688  public function FetchRows($NumberOfRows = NULL)
689  {
690  # assume no rows will be returned
691  $Result = array();
692 
693  # for each available row
694  $RowsFetched = 0;
695  while ((($RowsFetched < $NumberOfRows) || ($NumberOfRows == NULL))
696  && ($Row = $this->FetchRow()))
697  {
698  # add row to results
699  $Result[] = $Row;
700  $RowsFetched++;
701  }
702 
703  # return array of rows to caller
704  return $Result;
705  }
706 
723  public function FetchColumn($FieldName, $IndexFieldName = NULL)
724  {
725  $Array = array();
726  while ($Record = $this->FetchRow())
727  {
728  if ($IndexFieldName != NULL)
729  {
730  $Array[$Record[$IndexFieldName]] = $Record[$FieldName];
731  }
732  else
733  {
734  $Array[] = $Record[$FieldName];
735  }
736  }
737  return $Array;
738  }
739 
748  public function FetchField($FieldName)
749  {
750  $Record = $this->FetchRow();
751  return isset($Record[$FieldName]) ? $Record[$FieldName] : NULL;
752  }
753 
760  public function LastInsertId()
761  {
762  return (int)$this->Query(
763  "SELECT LAST_INSERT_ID() AS InsertId",
764  "InsertId");
765  }
766 
781  public function UpdateValue(
782  $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
783  {
784  # expand condition if supplied
785  if ($Condition != NULL) { $Condition = " WHERE ".$Condition; }
786 
787  # read cached record from database if not already loaded
788  if (!isset($CachedRecord))
789  {
790  $this->Query("SELECT * FROM `".$TableName."` ".$Condition);
791  $CachedRecord = $this->FetchRow();
792  }
793 
794  # if new value supplied
795  if ($NewValue !== DB_NOVALUE)
796  {
797  # update value in database
798  $this->Query("UPDATE `".$TableName."` SET `".$FieldName."` = "
799  .(($NewValue === NULL) ? "NULL" : "'"
800  .mysqli_real_escape_string($this->Handle, $NewValue)."'")
801  .$Condition);
802 
803  # update value in cached record
804  $CachedRecord[$FieldName] = $NewValue;
805  }
806 
807  # return value from cached record to caller
808  return isset($CachedRecord[$FieldName])
809  ? $CachedRecord[$FieldName] : NULL;
810  }
811 
828  public function UpdateIntValue(
829  $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
830  {
831  return $this->UpdateValue($TableName, $FieldName,
832  (($NewValue === DB_NOVALUE) ? DB_NOVALUE : (int)$NewValue),
833  $Condition, $CachedRecord);
834  }
835 
852  public function UpdateFloatValue(
853  $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
854  {
855  return $this->UpdateValue($TableName, $FieldName,
856  (($NewValue === DB_NOVALUE) ? DB_NOVALUE : (float)$NewValue),
857  $Condition, $CachedRecord);
858  }
859 
871  public function CopyValues($TableName, $IdColumn, $SrcId, $DstId,
872  $ColumnsToExclude = array())
873  {
874  # retrieve names of all columns in table
875  $AllColumns = $this->GetColumns($TableName);
876 
877  # remove columns to be excluded from copy
878  $ColumnsToExclude[] = $IdColumn;
879  $ColumnsToCopy = array_diff($AllColumns, $ColumnsToExclude);
880 
881  # normalize destination IDs
882  $DstIds = is_array($DstId) ? $DstId : array($DstId);
883  $DstIds = array_diff($DstIds, array($SrcId));
884 
885  # if there are columns to copy and we have destinations
886  if (count($ColumnsToCopy) && count($DstIds))
887  {
888  # construct and execute query to perform copy
889  $Query = "UPDATE `".$TableName."` AS Target"
890  ." LEFT JOIN `".$TableName."` AS Source"
891  ." ON Source.`".$IdColumn."` = '".addslashes($SrcId)."'";
892  $QuerySets = array();
893  foreach ($ColumnsToCopy as $ColumnName)
894  {
895  $QuerySets[] = "Target.`".$ColumnName."` = Source.`".$ColumnName."`";
896  }
897  $Query .= " SET ".implode(", ", $QuerySets);
898  $QueryConditions = array();
899  foreach ($DstIds as $Id)
900  {
901  $QueryConditions[] = "Target.`".$IdColumn."` = '".addslashes($DstId)."'";
902  }
903  $Query .= " WHERE ".implode(" OR ", $QueryConditions);
904  $this->Query($Query);
905  }
906  }
907 
908  /*@)*/ /* Data Manipulation */ /*@(*/
910 
919  public function EscapeString($String)
920  {
921  return mysqli_real_escape_string($this->Handle, $String);
922  }
923 
930  public function LogComment($String)
931  {
932  $this->Query("-- ".$String);
933  }
934 
940  public function TableExists($TableName)
941  {
942  $this->Query("SHOW TABLES LIKE '".addslashes($TableName)."'");
943  return $this->NumRowsSelected() ? TRUE : FALSE;
944  }
945 
952  public function FieldExists($TableName, $FieldName)
953  {
954  $this->Query("DESC ".$TableName);
955  while ($CurrentFieldName = $this->FetchField("Field"))
956  {
957  if ($CurrentFieldName == $FieldName) { return TRUE; }
958  }
959  return FALSE;
960  }
961 
968  public function GetFieldType($TableName, $FieldName)
969  {
970  $this->Query("DESC ".$TableName);
971  $AllTypes = $this->FetchColumn("Type", "Field");
972  return $AllTypes[$FieldName];
973  }
974 
980  public function GetColumns($TableName)
981  {
982  $this->Query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS"
983  ." WHERE TABLE_SCHEMA = '".addslashes($this->DBName)
984  ."' AND TABLE_NAME = '".addslashes($TableName)."'");
985  return $this->FetchColumn("COLUMN_NAME");
986  }
987 
993  public static function QueryDebugOutput($NewSetting)
994  {
995  self::$QueryDebugOutputFlag = $NewSetting;
996  }
997 
1003  public static function NumQueries()
1004  {
1005  return self::$QueryCounter;
1006  }
1007 
1014  public static function NumCacheHits()
1015  {
1016  return self::$CachedQueryCounter;
1017  }
1018 
1024  public static function CacheHitRate()
1025  {
1026  if (self::$QueryCounter)
1027  {
1028  return (self::$CachedQueryCounter / self::$QueryCounter) * 100;
1029  }
1030  else
1031  {
1032  return 0;
1033  }
1034  }
1035 
1036  /*@)*/ /* Miscellaneous */
1037 
1038  # ---- PRIVATE INTERFACE -------------------------------------------------
1039 
1040  protected $DBUserName;
1041  protected $DBPassword;
1042  protected $DBHostName;
1043  protected $DBName;
1044 
1045  private $Handle;
1046  private $QueryHandle;
1047  private $QueryResults;
1048  private $RowCounter;
1049  private $NumRows;
1050  private $GetResultsFromCache;
1051  private $ErrorIgnored = FALSE;
1052  private $ErrorsToIgnore = NULL;
1053  private $ErrMsg = NULL;
1054  private $ErrNo = NULL;
1055 
1056  private static $DisplayErrors = FALSE;
1057 
1058  private static $GlobalDBUserName;
1059  private static $GlobalDBPassword;
1060  private static $GlobalDBHostName;
1061  private static $GlobalDBName;
1062 
1063  # debug output flag
1064  private static $QueryDebugOutputFlag = FALSE;
1065  # flag for whether caching is turned on
1066  private static $CachingFlag = TRUE;
1067  # query result advanced caching flag
1068  private static $AdvancedCachingFlag = FALSE;
1069  # global cache for query results
1070  private static $QueryResultCache = array();
1071  # stats counters
1072  private static $QueryCounter = 0;
1073  private static $CachedQueryCounter = 0;
1074  # database connection link handles
1075  private static $ConnectionHandles = array();
1076  # do not cache queries that return more than this number of rows
1077  private static $CacheRowsThreshold = 250;
1078  # prune the query cache if there is less than this amount of memory free
1079  private static $CacheMemoryThreshold;
1080  # number of rows to leave in cache when pruning
1081  private static $CacheRowsToLeave = 10;
1082  # number of retry attempts to make to connect to database
1083  private static $ConnectRetryAttempts = 3;
1084  # number of seconds to wait between connection retry attempts
1085  private static $ConnectRetryInterval = 5;
1086 
1087  # server connection error codes
1088  const CR_CONNECTION_ERROR = 2002; # Can't connect to local MySQL server
1089  # through socket '%s' (%d)
1090  const CR_CONN_HOST_ERROR = 2003; # Can't connect to MySQL server on '%s' (%d)
1091  const CR_SERVER_GONE_ERROR = 2006; # MySQL server has gone away
1092  const CR_SERVER_LOST = 2013; # Lost connection to MySQL server during query
1093 
1094  # connection error codes that may be recoverable
1095  private static $RecoverableConnectionErrors = array(
1096  self::CR_CONNECTION_ERROR,
1097  );
1098 
1109  private static function ConnectAndSelectDB(
1111  {
1112  $ConnectAttemptsLeft = self::$ConnectRetryAttempts + 1;
1113  do
1114  {
1115  # if this is not our first connection attempt
1116  if (isset($Handle))
1117  {
1118  # wait for the retry interval
1119  sleep(self::$ConnectRetryInterval);
1120  }
1121 
1122  # attempt to connect to server
1123  $Handle = @mysqli_connect($DBHostName, $DBUserName, $DBPassword);
1124  $ConnectAttemptsLeft--;
1125  }
1126  # repeat if we do not have a connection and there are retry attempts
1127  # left and the connection error code indicates a retry may succeed
1128  // @codingStandardsIgnoreStart
1129  // (because phpcs apparently doesn't know how to handle do-while loops)
1130  while (!$Handle && $ConnectAttemptsLeft
1131  && in_array(mysqli_connect_errno(),
1132  self::$RecoverableConnectionErrors));
1133  // @codingStandardsIgnoreEnd
1134 
1135  # throw exception if connection attempts failed
1136  if (!$Handle)
1137  {
1138  throw new Exception("Could not connect to database: "
1139  .mysqli_connect_error()." (errno: ".mysqli_connect_errno().")");
1140  }
1141 
1142  # select DB
1143  $Result = mysqli_select_db($Handle, $DBName);
1144  if ($Result !== TRUE)
1145  {
1146  throw new Exception("Could not select database: "
1147  .mysqli_error($Handle)." (errno: ".mysqli_errno($Handle).")");
1148  }
1149 
1150  # return new connection to caller
1151  return $Handle;
1152  }
1153 
1159  private function IsReadOnlyStatement($QueryString)
1160  {
1161  return preg_match("/^[ ]*SELECT /i", $QueryString) ? TRUE : FALSE;
1162  }
1163 
1170  private function TableModified($QueryString)
1171  {
1172  # assume we're not going to be able to determine table
1173  $TableName = FALSE;
1174 
1175  # split query into pieces
1176  $QueryString = trim($QueryString);
1177  $Words = preg_split("/\s+/", $QueryString);
1178 
1179  # if INSERT statement
1180  $WordIndex = 1;
1181  if (strtoupper($Words[0]) == "INSERT")
1182  {
1183  # skip over modifying keywords
1184  while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
1185  || (strtoupper($Words[$WordIndex]) == "DELAYED")
1186  || (strtoupper($Words[$WordIndex]) == "IGNORE")
1187  || (strtoupper($Words[$WordIndex]) == "INTO"))
1188  {
1189  $WordIndex++;
1190  }
1191 
1192  # next word is table name
1193  $TableName = $Words[$WordIndex];
1194  }
1195  # else if UPDATE statement
1196  elseif (strtoupper($Words[0]) == "UPDATE")
1197  {
1198  # skip over modifying keywords
1199  while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
1200  || (strtoupper($Words[$WordIndex]) == "IGNORE"))
1201  {
1202  $WordIndex++;
1203  }
1204 
1205  # if word following next word is SET
1206  if (strtoupper($Words[$WordIndex + 1]) == "SET")
1207  {
1208  # next word is table name
1209  $TableName = $Words[$WordIndex];
1210  }
1211  }
1212  # else if DELETE statement
1213  elseif (strtoupper($Words[0]) == "DELETE")
1214  {
1215  # skip over modifying keywords
1216  while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
1217  || (strtoupper($Words[$WordIndex]) == "IGNORE")
1218  || (strtoupper($Words[$WordIndex]) == "QUICK"))
1219  {
1220  $WordIndex++;
1221  }
1222 
1223  # if next term is FROM
1224  if (strtoupper($Words[$WordIndex]) == "FROM")
1225  {
1226  # next word is table name
1227  $WordIndex++;
1228  $TableName = $Words[$WordIndex];
1229  }
1230  }
1231 
1232  # discard table name if it looks at all suspicious
1233  if ($TableName)
1234  {
1235  if (!preg_match("/[a-zA-Z0-9]+/", $TableName))
1236  {
1237  $TableName = FALSE;
1238  }
1239  }
1240 
1241  # return table name (or lack thereof) to caller
1242  return $TableName;
1243  }
1244 
1251  private function TablesAccessed($QueryString)
1252  {
1253  # assume we're not going to be able to determine tables
1254  $TableNames = FALSE;
1255 
1256  # split query into pieces
1257  $QueryString = trim($QueryString);
1258  $Words = preg_split("/\s+/", $QueryString);
1259  $UQueryString = strtoupper($QueryString);
1260  $UWords = preg_split("/\s+/", $UQueryString);
1261 
1262  # if SELECT statement
1263  if ($UWords[0] == "SELECT")
1264  {
1265  # keep going until we hit FROM or last word
1266  $WordIndex = 1;
1267  while (($UWords[$WordIndex] != "FROM")
1268  && strlen($UWords[$WordIndex]))
1269  {
1270  $WordIndex++;
1271  }
1272 
1273  # if we hit FROM
1274  if ($UWords[$WordIndex] == "FROM")
1275  {
1276  # for each word after FROM
1277  $WordIndex++;
1278  while (strlen($UWords[$WordIndex]))
1279  {
1280  # if current word ends with comma
1281  if (preg_match("/,$/", $Words[$WordIndex]))
1282  {
1283  # strip off comma and add word to table name list
1284  $TableNames[] = substr($Words[$WordIndex], 0, -1);
1285  }
1286  else
1287  {
1288  # add word to table name list
1289  $TableNames[] = $Words[$WordIndex];
1290 
1291  # if next word is not comma
1292  $WordIndex++;
1293  if ($Words[$WordIndex] != ",")
1294  {
1295  # if word begins with comma
1296  if (preg_match("/^,/", $Words[$WordIndex]))
1297  {
1298  # strip off comma (NOTE: modifies $Words array!)
1299  $Words[$WordIndex] = substr($Words[$WordIndex], 1);
1300 
1301  # decrement index so we start with this word next pass
1302  $WordIndex--;
1303  }
1304  else
1305  {
1306  # stop scanning words (non-basic JOINs not yet handled)
1307  break;
1308  }
1309  }
1310  }
1311 
1312  # move to next word
1313  $WordIndex++;
1314  }
1315  }
1316  }
1317 
1318  # discard table names if they look at all suspicious
1319  if ($TableNames)
1320  {
1321  foreach ($TableNames as $Name)
1322  {
1323  if (!preg_match("/^[a-zA-Z0-9]+$/", $Name))
1324  {
1325  $TableNames = FALSE;
1326  break;
1327  }
1328  }
1329  }
1330 
1331  # return table name (or lack thereof) to caller
1332  return $TableNames;
1333  }
1334 
1341  private function RunQuery($QueryString)
1342  {
1343  # log query start time if debugging output is enabled
1344  if (self::$QueryDebugOutputFlag) { $QueryStartTime = microtime(TRUE); }
1345 
1346  # run query against database
1347  $this->QueryHandle = mysqli_query($this->Handle, $QueryString) ;
1348 
1349  # print query and execution time if debugging output is enabled
1350  if (self::$QueryDebugOutputFlag)
1351  {
1352  print "DB: ".$QueryString." ["
1353  .sprintf("%.2f", microtime(TRUE) - $QueryStartTime)
1354  ."s]"."<br>\n";
1355  }
1356 
1357  # if query failed and there are errors that we can ignore
1358  if (($this->QueryHandle === FALSE) && $this->ErrorsToIgnore)
1359  {
1360  # for each pattern for an error that we can ignore
1361  foreach ($this->ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
1362  {
1363  # if error matches pattern
1364  $ErrorMsg = mysqli_error($this->Handle);
1365  if (preg_match($SqlPattern, $QueryString)
1366  && preg_match($ErrMsgPattern, $ErrorMsg))
1367  {
1368  # set return value to indicate error was ignored
1369  $this->QueryHandle = TRUE;
1370 
1371  # set internal flag to indicate that an error was ignored
1372  $this->ErrorIgnored = $ErrorMsg;
1373 
1374  # stop looking at patterns
1375  break;
1376  }
1377  }
1378  }
1379 
1380  # if query failed
1381  if ($this->QueryHandle === FALSE)
1382  {
1383  # clear stored value for number of rows retrieved
1384  $this->NumRows = 0;
1385 
1386  # retrieve error info
1387  $this->ErrMsg = mysqli_error($this->Handle);
1388  $this->ErrNo = mysqli_errno($this->Handle);
1389 
1390  # if we are supposed to be displaying errors
1391  if (self::$DisplayErrors)
1392  {
1393  # print error info
1394  print("<b>SQL Error:</b> <i>".$this->ErrMsg
1395  ."</i> (".$this->ErrNo.")<br/>\n");
1396  print("<b>SQL Statement:</b> <i>"
1397  .htmlspecialchars($QueryString)."</i><br/>\n");
1398 
1399  # retrieve execution trace that got us to this point
1400  $Trace = debug_backtrace();
1401 
1402  # remove current context from trace
1403  array_shift($Trace);
1404 
1405  # make sure file name and line number are available
1406  foreach ($Trace as $Index => $Loc)
1407  {
1408  if (!array_key_exists("file", $Loc))
1409  {
1410  $Trace[$Index]["file"] = "UNKNOWN";
1411  }
1412  if (!array_key_exists("line", $Loc))
1413  {
1414  $Trace[$Index]["line"] = "??";
1415  }
1416  }
1417 
1418  # determine length of leading path common to all file names in trace
1419  $LocString = "";
1420  $OurFile = __FILE__;
1421  $PrefixLen = 9999;
1422  foreach ($Trace as $Loc)
1423  {
1424  if ($Loc["file"] != "UNKNOWN")
1425  {
1426  $Index = 0;
1427  $FNameLength = strlen($Loc["file"]);
1428  while ($Index < $FNameLength &&
1429  $Loc["file"][$Index] == $OurFile[$Index])
1430  { $Index++; }
1431  $PrefixLen = min($PrefixLen, $Index);
1432  }
1433  }
1434 
1435  foreach ($Trace as $Loc)
1436  {
1437  $Sep = "";
1438  $ArgString = "";
1439  foreach ($Loc["args"] as $Arg)
1440  {
1441  $ArgString .= $Sep;
1442  switch (gettype($Arg))
1443  {
1444  case "boolean":
1445  $ArgString .= $Arg ? "TRUE" : "FALSE";
1446  break;
1447 
1448  case "integer":
1449  case "double":
1450  $ArgString .= $Arg;
1451  break;
1452 
1453  case "string":
1454  $ArgString .= '"<i>'.htmlspecialchars(substr($Arg, 0, 40))
1455  .((strlen($Arg) > 40) ? "..." : "").'</i>"';
1456  break;
1457 
1458  case "array":
1459  case "resource":
1460  case "NULL":
1461  $ArgString .= strtoupper(gettype($Arg));
1462  break;
1463 
1464  case "object":
1465  $ArgString .= get_class($Arg);
1466  break;
1467 
1468  case "unknown type":
1469  $ArgString .= "UNKNOWN";
1470  break;
1471  }
1472  $Sep = ",";
1473  }
1474  $Loc["file"] = substr($Loc["file"], $PrefixLen);
1475  $LocString .= "&nbsp;&nbsp;";
1476  if (array_key_exists("class", $Loc))
1477  { $LocString .= $Loc["class"]."::"; }
1478  $LocString .= $Loc["function"]."(".$ArgString.")"
1479  ." - ".$Loc["file"].":".$Loc["line"]
1480  ."<br>\n";
1481  }
1482  print("<b>Trace:</b><br>\n".$LocString);
1483  }
1484  }
1485  return $this->QueryHandle;
1486  }
1487 
1492  static private function GetPhpMemoryLimit()
1493  {
1494  $Str = strtoupper(ini_get("memory_limit"));
1495  if (substr($Str, -1) == "B") { $Str = substr($Str, 0, strlen($Str) - 1); }
1496  switch (substr($Str, -1))
1497  {
1498  case "K":
1499  $MemoryLimit = (int)$Str * 1024;
1500  break;
1501 
1502  case "M":
1503  $MemoryLimit = (int)$Str * 1048576;
1504  break;
1505 
1506  case "G":
1507  $MemoryLimit = (int)$Str * 1073741824;
1508  break;
1509 
1510  default:
1511  $MemoryLimit = (int)$Str;
1512  break;
1513  }
1514  return $MemoryLimit;
1515  }
1516 
1521  static private function GetFreeMemory()
1522  {
1523  return self::GetPhpMemoryLimit() - memory_get_usage();
1524  }
1525 }
1526 
1527 # define return values (numerical values correspond to MySQL error codes)
1528 // @codingStandardsIgnoreStart (to silence warning about multiple spaces)
1529 define("DB_OKAY", 0);
1530 define("DB_ERROR", 1);
1531 define("DB_ACCESSDENIED", 2);
1532 define("DB_UNKNOWNDB", 3);
1533 define("DB_UNKNOWNTABLE", 4);
1534 define("DB_SYNTAXERROR", 5);
1535 define("DB_DBALREADYEXISTS", 6);
1536 define("DB_DBDOESNOTEXIST", 7);
1537 define("DB_DISKFULL", 8);
1538 // @codingStandardsIgnoreEnd
1539 
1540 # define value to designate omitted arguments (so DB values can be set to NULL)
1541 define("DB_NOVALUE", "!-_-_-DB_NOVALUE-_-_-!");
1542 
1543 # MySQL error code mapping
1545  1045 => DB_ACCESSDENIED,
1546  1049 => DB_UNKNOWNDB,
1547  1046 => DB_UNKNOWNTABLE,
1548  1064 => DB_SYNTAXERROR,
1549  1007 => DB_DBALREADYEXISTS, # ? (not sure)
1550  1008 => DB_DBDOESNOTEXIST, # ? (not sure)
1551  1021 => DB_DISKFULL, # ? (not sure)
1552  );
1553 
1554 # date() format for SQL dates
1555 define("DATE_SQL", "Y-m-d H:i:s");
QueryErrMsg()
Get most recent error message text set by Query().
Definition: Database.php:568
static Caching($NewSetting=NULL)
Get or set whether query result caching is currently enabled.
Definition: Database.php:237
__sleep()
Definition: Database.php:81
GetServerVersion($FullVersion=FALSE)
Get database server version number.
Definition: Database.php:161
static SetGlobalDatabaseName($DatabaseName)
Set default database name.
Definition: Database.php:132
const CR_CONNECTION_ERROR
Definition: Database.php:1088
const DB_ACCESSDENIED
Definition: Database.php:1531
SetDefaultStorageEngine($Engine)
Set default database storage engine.
Definition: Database.php:145
ExecuteQueriesFromFile($FileName)
Execute queries from specified file.
Definition: Database.php:509
UpdateIntValue($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
A convenience function to get or set an integer value in the database.
Definition: Database.php:828
SQL database abstraction object with smart query caching.
Definition: Database.php:22
SetQueryErrorsToIgnore($ErrorsToIgnore, $NormalizeWhitespace=TRUE)
Set query errors to ignore.
Definition: Database.php:291
const CR_SERVER_GONE_ERROR
Definition: Database.php:1091
DBUserName()
Get name used to connect with database server.
Definition: Database.php:225
EscapeString($String)
Escape a string that may contain null bytes.
Definition: Database.php:919
FetchRow()
Get next database row retrieved by most recent query.
Definition: Database.php:642
LastInsertId()
Get ID of row added by the last SQL "INSERT" statement.
Definition: Database.php:760
const CR_SERVER_LOST
Definition: Database.php:1092
const DB_SYNTAXERROR
Definition: Database.php:1534
__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:940
const DB_DISKFULL
Definition: Database.php:1537
static SetGlobalServerInfo($UserName, $Password, $HostName="localhost")
Set default login and host info for database server.
Definition: Database.php:116
$APDBErrorCodeMappings
Definition: Database.php:1544
GetClientVersion()
Get version number of the client libraries being used to connect to the database server (Currently th...
Definition: Database.php:185
GetFieldType($TableName, $FieldName)
Get field (column) type.
Definition: Database.php:968
const DB_NOVALUE
Definition: Database.php:1541
NumRowsSelected()
Get number of rows returned by last SELECT or SHOW query.
Definition: Database.php:599
FetchRows($NumberOfRows=NULL)
Get specified number of database rows retrieved by most recent query.
Definition: Database.php:688
static QueryDebugOutput($NewSetting)
Enable or disable debugging output for queries.
Definition: Database.php:993
Query($QueryString, $FieldName="")
Query database (with caching if enabled).
Definition: Database.php:329
GetColumns($TableName)
Get column (database field) names.
Definition: Database.php:980
FetchField($FieldName)
Pull next row from last DB query and get a specific value from that row.
Definition: Database.php:748
FieldExists($TableName, $FieldName)
Get whether specified field exists in specified table.
Definition: Database.php:952
static NumCacheHits()
Get the number of queries that have resulted in cache hits since program execution began...
Definition: Database.php:1014
const DB_UNKNOWNTABLE
Definition: Database.php:1533
FetchColumn($FieldName, $IndexFieldName=NULL)
Get all available values for specified database field retrieved by most recent query.
Definition: Database.php:723
DBHostName()
Get host name of system on which database server resides.
Definition: Database.php:205
const DB_DBDOESNOTEXIST
Definition: Database.php:1536
NumRowsAffected()
Get number of rows affected by last INSERT, UPDATE, REPLACE, or DELETE query.
Definition: Database.php:625
UpdateFloatValue($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
A convenience function to get or set a float value in the database.
Definition: Database.php:852
const DB_DBALREADYEXISTS
Definition: Database.php:1535
QueryErrNo()
Get most recent error code set by Query().
Definition: Database.php:578
const DB_UNKNOWNDB
Definition: Database.php:1532
GetHostInfo()
Get database connection type and hostname.
Definition: Database.php:195
UpdateValue($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
A convenience function to get or set a value in the database.
Definition: Database.php:781
static AdvancedCaching($NewSetting=NULL)
Get or set whether advanced query result cachine is currently enabled.
Definition: Database.php:263
static DisplayQueryErrors($NewValue=NULL)
Get/set whether Query() errors will be displayed.
Definition: Database.php:589
static CacheHitRate()
Get the ratio of query cache hits to queries as a percentage.
Definition: Database.php:1024
IgnoredError()
Check whether an error was ignored by the most recent query.
Definition: Database.php:311
DBName()
Get current database name.
Definition: Database.php:215
CopyValues($TableName, $IdColumn, $SrcId, $DstId, $ColumnsToExclude=array())
A convenience function to copy values from one row to another.
Definition: Database.php:871
LogComment($String)
Peform query that consists of SQL comment statement.
Definition: Database.php:930
__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:1003