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,
45  $Password = NULL,
46  $DatabaseName = NULL,
47  $HostName = NULL)
48  {
49  # save DB access parameter values
50  $this->DBUserName = $UserName ? $UserName : self::$GlobalDBUserName;
51  $this->DBPassword = $Password ? $Password : self::$GlobalDBPassword;
52  $this->DBHostName = $HostName ? $HostName :
53  (isset(self::$GlobalDBHostName) ? self::$GlobalDBHostName
54  : "localhost");
55  $this->DBName = $DatabaseName ? $DatabaseName : self::$GlobalDBName;
56 
57  # set memory threshold for cache clearing
58  if (!isset(self::$CacheMemoryThreshold))
59  {
60  self::$CacheMemoryThreshold = self::GetPhpMemoryLimit() / 4;
61  }
62 
63  # if we don't already have a connection or DB access parameters were supplied
64  $HandleIndex = $this->DBHostName.":".$this->DBName;
65  if (!array_key_exists($HandleIndex, self::$ConnectionHandles)
66  || $UserName || $Password || $DatabaseName || $HostName)
67  {
68  # open connection to DB server and select database
69  $this->Handle = self::ConnectAndSelectDB($this->DBHostName,
70  $this->DBUserName, $this->DBPassword, $this->DBName);
71  self::$ConnectionHandles[$HandleIndex] = $this->Handle;
72  }
73  else
74  {
75  # set local connection handle
76  $this->Handle = self::$ConnectionHandles[$HandleIndex];
77  }
78  }
79 
84  public function __sleep()
85  {
86  return array("DBUserName", "DBPassword", "DBHostName", "DBName");
87  }
93  public function __wakeup()
94  {
95  # if we don't already have a database server connection
96  $HandleIndex = $this->DBHostName.":".$this->DBName;
97  if (!array_key_exists($HandleIndex, self::$ConnectionHandles))
98  {
99  # open connection to DB server and select database
100  try
101  {
102  $this->Handle = self::ConnectAndSelectDB($this->DBHostName,
103  $this->DBUserName, $this->DBPassword, $this->DBName);
104  }
105  catch (Exception $Exception)
106  {
107  if (isset(self::$GlobalDBUserName)
108  && isset(self::$GlobalDBPassword)
109  && isset(self::$GlobalDBName))
110  {
111  $this->DBUserName = self::$GlobalDBUserName;
112  $this->DBPassword = self::$GlobalDBPassword;
113  $this->DBName = self::$GlobalDBName;
114  $this->DBHostName = isset(self::$GlobalDBHostName)
115  ? self::$GlobalDBHostName : "localhost";
116  $this->Handle = self::ConnectAndSelectDB($this->DBHostName,
117  $this->DBUserName, $this->DBPassword, $this->DBName);
118  }
119  else
120  {
121  throw $Exception;
122  }
123  }
124  self::$ConnectionHandles[$HandleIndex] = $this->Handle;
125  }
126  else
127  {
128  # set local connection handle
129  $this->Handle = self::$ConnectionHandles[$HandleIndex];
130  }
131  }
141  public static function SetGlobalServerInfo(
142  $UserName, $Password, $HostName = "localhost")
143  {
144  # save default DB access parameters
145  self::$GlobalDBUserName = $UserName;
146  self::$GlobalDBPassword = $Password;
147  self::$GlobalDBHostName = $HostName;
148 
149  # clear any existing DB connection handles
150  self::$ConnectionHandles = array();
151  }
152 
157  public static function SetGlobalDatabaseName($DatabaseName)
158  {
159  # save new default DB name
160  self::$GlobalDBName = $DatabaseName;
161 
162  # clear any existing DB connection handles
163  self::$ConnectionHandles = array();
164  }
165 
170  public function SetDefaultStorageEngine($Engine)
171  {
172  # choose config variable to use based on server version number
173  $ConfigVar = version_compare($this->GetServerVersion(), "5.5", "<")
174  ? "storage_engine" : "default_storage_engine";
175 
176  # set storage engine in database
177  $this->Query("SET ".$ConfigVar." = ".$Engine);
178  }
179 
186  public function GetServerVersion($FullVersion=FALSE)
187  {
188  # retrieve version string
189  $Version = $this->Query("SELECT VERSION() AS ServerVer", "ServerVer");
190 
191  if (!$FullVersion)
192  {
193  # strip off any build/config suffix
194  $Pieces = explode("-", $Version);
195  $Version = array_shift($Pieces);
196  }
197 
198  # return version number to caller
199  return $Version;
200  }
201 
210  public function GetClientVersion()
211  {
212  return mysqli_get_client_info();
213  }
214 
220  public function GetHostInfo()
221  {
222  return mysqli_get_host_info($this->Handle);
223  }
224 
230  public function DBHostName()
231  {
232  return $this->DBHostName;
233  }
234 
240  public function DBName()
241  {
242  return $this->DBName;
243  }
244 
250  public function DBUserName()
251  {
252  return $this->DBUserName;
253  }
254 
262  public static function Caching($NewSetting = NULL)
263  {
264  # if cache setting has changed
265  if (($NewSetting !== NULL) && ($NewSetting != self::$CachingFlag))
266  {
267  # save new setting
268  self::$CachingFlag = $NewSetting;
269 
270  # clear any existing cached results
271  self::$QueryResultCache = array();
272  }
273 
274  # return current setting to caller
275  return self::$CachingFlag;
276  }
277 
288  public static function AdvancedCaching($NewSetting = NULL)
289  {
290  if ($NewSetting !== NULL)
291  {
292  self::$AdvancedCachingFlag = $NewSetting;
293  }
294  return self::$AdvancedCachingFlag;
295  }
296 
316  public function SetQueryErrorsToIgnore(
317  $ErrorsToIgnore, $NormalizeWhitespace = TRUE)
318  {
319  if ($NormalizeWhitespace && ($ErrorsToIgnore !== NULL))
320  {
321  $RevisedErrorsToIgnore = array();
322  foreach ($ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
323  {
324  $SqlPattern = preg_replace("/\\s+/", "\\s+", $SqlPattern);
325  $RevisedErrorsToIgnore[$SqlPattern] = $ErrMsgPattern;
326  }
327  $ErrorsToIgnore = $RevisedErrorsToIgnore;
328  }
329  $this->ErrorsToIgnore = $ErrorsToIgnore;
330  }
331 
337  public function IgnoredError()
338  {
339  return $this->ErrorIgnored;
340  }
341 
342  /*@)*/ /* Setup/Initialization */ /*@(*/
344 
355  public function Query($QueryString, $FieldName = "")
356  {
357  # clear flag that indicates whether query error was ignored
358  $this->ErrorIgnored = FALSE;
359 
360  # if caching is enabled
361  if (self::$CachingFlag)
362  {
363  # if SQL statement is read-only
364  if ($this->IsReadOnlyStatement($QueryString))
365  {
366  # if we have statement in cache
367  if (isset(self::$QueryResultCache[$QueryString]["NumRows"]))
368  {
369  if (self::$QueryDebugOutputFlag)
370  { print("DB-C: $QueryString<br>\n"); }
371 
372  # make sure query result looks okay
373  $this->QueryHandle = TRUE;
374 
375  # increment cache hit counter
376  self::$CachedQueryCounter++;
377 
378  # make local copy of results
379  $this->QueryResults = self::$QueryResultCache[$QueryString];
380  $this->NumRows = self::$QueryResultCache[$QueryString]["NumRows"];
381 
382  # set flag to indicate that results should be retrieved from cache
383  $this->GetResultsFromCache = TRUE;
384  }
385  else
386  {
387  # execute SQL statement
388  $this->QueryHandle = $this->RunQuery($QueryString);
389  if (!$this->QueryHandle instanceof mysqli_result) { return FALSE; }
390 
391  # save number of rows in result
392  $this->NumRows = mysqli_num_rows($this->QueryHandle);
393 
394  # if too many rows to cache
395  if ($this->NumRows >= self::$CacheRowsThreshold)
396  {
397  # set flag to indicate that query results should not
398  # be retrieved from cache
399  $this->GetResultsFromCache = FALSE;
400  }
401  else
402  {
403  # if we are low on memory
404  if (self::GetFreeMemory() < self::$CacheMemoryThreshold)
405  {
406  # clear out all but last few rows from cache
407  self::$QueryResultCache = array_slice(
408  self::$QueryResultCache,
409  (0 - self::$CacheRowsToLeave));
410  }
411 
412  # if advanced caching is enabled
413  if (self::$AdvancedCachingFlag)
414  {
415  # save tables accessed by query
416  self::$QueryResultCache[$QueryString]["TablesAccessed"] =
417  $this->TablesAccessed($QueryString);
418  }
419 
420  # if rows found
421  if ($this->NumRows > 0)
422  {
423  # load query results
424  for ($Row = 0; $Row < $this->NumRows; $Row++)
425  {
426  $this->QueryResults[$Row] =
427  mysqli_fetch_assoc($this->QueryHandle);
428  }
429 
430  # cache query results
431  self::$QueryResultCache[$QueryString] = $this->QueryResults;
432  }
433  else
434  {
435  # clear local query results
436  unset($this->QueryResults);
437  }
438 
439  # cache number of rows
440  self::$QueryResultCache[$QueryString]["NumRows"] = $this->NumRows;
441 
442  # set flag to indicate that query results should be
443  # retrieved from cache
444  $this->GetResultsFromCache = TRUE;
445  }
446  }
447  }
448  else
449  {
450  # if advanced caching is enabled
451  if (self::$AdvancedCachingFlag)
452  {
453  # if table modified by statement is known
454  $TableModified = $this->TableModified($QueryString);
455  if ($TableModified)
456  {
457  # for each cached query
458  foreach (self::$QueryResultCache
459  as $CachedQueryString => $CachedQueryResult)
460  {
461  # if we know what tables were accessed
462  if ($CachedQueryResult["TablesAccessed"])
463  {
464  # if tables accessed include the one we may modify
465  if (in_array($TableModified,
466  $CachedQueryResult["TablesAccessed"]))
467  {
468  # clear cached query results
469  unset(self::$QueryResultCache[$CachedQueryString]);
470  }
471  }
472  else
473  {
474  # clear cached query results
475  unset(self::$QueryResultCache[$CachedQueryString]);
476  }
477  }
478  }
479  else
480  {
481  # clear entire query result cache
482  self::$QueryResultCache = array();
483  }
484  }
485  else
486  {
487  # clear entire query result cache
488  self::$QueryResultCache = array();
489  }
490 
491  # execute SQL statement
492  $this->QueryHandle = $this->RunQuery($QueryString);
493  if ($this->QueryHandle === FALSE) { return FALSE; }
494 
495  # set flag to indicate that query results should not be
496  # retrieved from cache
497  $this->GetResultsFromCache = FALSE;
498  }
499 
500  # reset row counter
501  $this->RowCounter = 0;
502 
503  # increment query counter
504  self::$QueryCounter++;
505  }
506  else
507  {
508  # execute SQL statement
509  $this->QueryHandle = $this->RunQuery($QueryString);
510  if ($this->QueryHandle === FALSE) { return FALSE; }
511  }
512 
513  if (($FieldName != "") && ($this->QueryHandle !== FALSE))
514  {
515  return $this->FetchField($FieldName);
516  }
517  else
518  {
519  return $this->QueryHandle;
520  }
521  }
522 
535  public function ExecuteQueriesFromFile($FileName)
536  {
537  $QueryCount = 0;
538 
539  # open file
540  $FHandle = fopen($FileName, "r");
541 
542  # if file open succeeded
543  if ($FHandle !== FALSE)
544  {
545  # while lines left in file
546  $Query = "";
547  while (!feof($FHandle))
548  {
549  # read in line from file
550  $Line = fgets($FHandle, 32767);
551 
552  # trim whitespace from line
553  $Line = trim($Line);
554 
555  # if line is not empty and not a comment
556  if (!preg_match("/^#/", $Line)
557  && !preg_match("/^--/", $Line)
558  && strlen($Line))
559  {
560  # add line to current query
561  $Query .= " ".$Line;
562 
563  # if line completes a query
564  if (preg_match("/;$/", $Line))
565  {
566  # run query
567  $QueryCount++;
568  $Result = $this->Query($Query);
569  $Query = "";
570 
571  # if query resulted in an error that is not ignorable
572  if ($Result === FALSE)
573  {
574  # stop processing queries and set error code
575  $QueryCount = NULL;
576  break;
577  }
578  }
579  }
580  }
581 
582  # close file
583  fclose($FHandle);
584  }
585 
586  # return number of executed queries to caller
587  return $QueryCount;
588  }
589 
595  public function QueryErrMsg()
596  {
597  return $this->ErrMsg;
598  }
599 
605  public function QueryErrNo()
606  {
607  return $this->ErrNo;
608  }
609 
616  public static function DisplayQueryErrors($NewValue = NULL)
617  {
618  if ($NewValue !== NULL) { self::$DisplayErrors = $NewValue; }
619  return self::$DisplayErrors;
620  }
621 
626  public function NumRowsSelected()
627  {
628  # if caching is enabled and query was cached
629  if (self::$CachingFlag && $this->GetResultsFromCache)
630  {
631  # return cached number of rows to caller
632  return $this->NumRows;
633  }
634  else
635  {
636  # call to this method after an unsuccessful query
637  if (!$this->QueryHandle instanceof mysqli_result)
638  {
639  return 0;
640  }
641 
642  # retrieve number of rows and return to caller
643  return mysqli_num_rows($this->QueryHandle);
644  }
645  }
646 
652  public function NumRowsAffected()
653  {
654  # call to this method after an unsuccessful query
655  if ($this->QueryHandle === FALSE)
656  {
657  return 0;
658  }
659 
660  # retrieve number of rows and return to caller
661  return mysqli_affected_rows($this->Handle);
662  }
663 
669  public function FetchRow()
670  {
671  # if caching is enabled and query was cached
672  if (self::$CachingFlag && $this->GetResultsFromCache)
673  {
674  # if rows left to return
675  if ($this->RowCounter < $this->NumRows)
676  {
677  # retrieve row from cache
678  $Result = $this->QueryResults[$this->RowCounter];
679 
680  # increment row counter
681  $this->RowCounter++;
682  }
683  else
684  {
685  # return nothing
686  $Result = FALSE;
687  }
688  }
689  else
690  {
691  # call to this method after successful query
692  if ($this->QueryHandle instanceof mysqli_result)
693  {
694  $Result = mysqli_fetch_assoc($this->QueryHandle);
695  if ($Result === NULL) { $Result = FALSE; }
696  }
697 
698  # call to this method after unsuccessful query
699  else
700  {
701  $Result = FALSE;
702  }
703  }
704 
705  # return row to caller
706  return $Result;
707  }
708 
716  public function FetchRows($NumberOfRows = NULL)
717  {
718  # assume no rows will be returned
719  $Result = array();
720 
721  # for each available row
722  $RowsFetched = 0;
723  while ((($RowsFetched < $NumberOfRows) || ($NumberOfRows == NULL))
724  && ($Row = $this->FetchRow()))
725  {
726  # add row to results
727  $Result[] = $Row;
728  $RowsFetched++;
729  }
730 
731  # return array of rows to caller
732  return $Result;
733  }
734 
751  public function FetchColumn($FieldName, $IndexFieldName = NULL)
752  {
753  $Array = array();
754  while ($Record = $this->FetchRow())
755  {
756  if ($IndexFieldName != NULL)
757  {
758  $Array[$Record[$IndexFieldName]] = $Record[$FieldName];
759  }
760  else
761  {
762  $Array[] = $Record[$FieldName];
763  }
764  }
765  return $Array;
766  }
767 
776  public function FetchField($FieldName)
777  {
778  $Record = $this->FetchRow();
779  return isset($Record[$FieldName]) ? $Record[$FieldName] : NULL;
780  }
781 
788  public function LastInsertId()
789  {
790  return (int)$this->Query(
791  "SELECT LAST_INSERT_ID() AS InsertId",
792  "InsertId");
793  }
794 
805  public function GetNextInsertId($TableName)
806  {
807  if (!$this->TableExists($TableName))
808  {
809  throw new Exception(
810  "Table ".$TableName." does not exist");
811  }
812 
813  return (int)$this->Query(
814  "SELECT `AUTO_INCREMENT` AS Id FROM INFORMATION_SCHEMA.TABLES "
815  ."WHERE TABLE_SCHEMA='".addslashes($this->DBName())."' "
816  ."AND TABLE_NAME = '".addslashes($TableName)."'", "Id");
817  }
818 
833  public function UpdateValue(
834  $TableName,
835  $FieldName,
836  $NewValue,
837  $Condition,
838  &$CachedRecord)
839  {
840  # expand condition if supplied
841  if ($Condition != NULL) { $Condition = " WHERE ".$Condition; }
842 
843  # if cached values not already loaded
844  if (!isset($CachedRecord))
845  {
846  # read cached record from database
847  $this->Query("SELECT * FROM `".$TableName."` ".$Condition);
848  $CachedRecord = $this->FetchRow();
849 
850  # error out if requested column does not exist in specified table
851  if ($NewValue === DB_NOVALUE)
852  {
853  $RowsFound = ($this->NumRowsSelected() > 0);
854  if (($RowsFound && !array_key_exists($FieldName, $CachedRecord))
855  || (!$RowsFound && !$this->FieldExists($TableName, $FieldName)))
856  {
857  throw new Exception("Column '".$FieldName
858  ."' not found in table '".$TableName."'.");
859  }
860  }
861  }
862 
863  # if new value supplied
864  if ($NewValue !== DB_NOVALUE)
865  {
866  # error out if we are trying to update a nonexistent record or field
867  if (!count($CachedRecord))
868  {
869  throw new Exception("No records found when attempting to update"
870  ." column '".$FieldName."' in table '".$TableName."'"
871  .(($Condition != NULL)
872  ? " using condition '".$Condition."'" : "")
873  .".");
874  }
875  else
876  {
877  if (!array_key_exists($FieldName, $CachedRecord))
878  {
879  throw new Exception("Attempting to update column '".$FieldName
880  ."', which does not exist in table '".$TableName."'.");
881  }
882  }
883 
884  # update value in database
885  $this->Query("UPDATE `".$TableName."` SET `".$FieldName."` = "
886  .(($NewValue === NULL) ? "NULL" : "'"
887  .mysqli_real_escape_string($this->Handle, $NewValue)."'")
888  .$Condition);
889 
890  # update value in cached record
891  $CachedRecord[$FieldName] = $NewValue;
892  }
893 
894  # return value from cached record to caller
895  return isset($CachedRecord[$FieldName])
896  ? $CachedRecord[$FieldName] : NULL;
897  }
898 
915  public function UpdateIntValue(
916  $TableName,
917  $FieldName,
918  $NewValue,
919  $Condition,
920  &$CachedRecord)
921  {
922  return $this->UpdateValue($TableName, $FieldName,
923  (($NewValue === DB_NOVALUE) ? DB_NOVALUE : (int)$NewValue),
924  $Condition, $CachedRecord);
925  }
926 
943  public function UpdateFloatValue(
944  $TableName,
945  $FieldName,
946  $NewValue,
947  $Condition,
948  &$CachedRecord)
949  {
950  return $this->UpdateValue($TableName, $FieldName,
951  (($NewValue === DB_NOVALUE) ? DB_NOVALUE : (float)$NewValue),
952  $Condition, $CachedRecord);
953  }
954 
966  public function CopyValues($TableName, $IdColumn, $SrcId, $DstId,
967  $ColumnsToExclude = array())
968  {
969  # retrieve names of all columns in table
970  $AllColumns = $this->GetColumns($TableName);
971 
972  # remove columns to be excluded from copy
973  $ColumnsToExclude[] = $IdColumn;
974  $ColumnsToCopy = array_diff($AllColumns, $ColumnsToExclude);
975 
976  # normalize destination IDs
977  $DstIds = is_array($DstId) ? $DstId : array($DstId);
978  $DstIds = array_diff($DstIds, array($SrcId));
979 
980  # if there are columns to copy and we have destinations
981  if (count($ColumnsToCopy) && count($DstIds))
982  {
983  # construct and execute query to perform copy
984  $Query = "UPDATE `".$TableName."` AS Target"
985  ." LEFT JOIN `".$TableName."` AS Source"
986  ." ON Source.`".$IdColumn."` = '".addslashes($SrcId)."'";
987  $QuerySets = array();
988  foreach ($ColumnsToCopy as $ColumnName)
989  {
990  $QuerySets[] = "Target.`".$ColumnName."` = Source.`".$ColumnName."`";
991  }
992  $Query .= " SET ".implode(", ", $QuerySets);
993  $QueryConditions = array();
994  foreach ($DstIds as $Id)
995  {
996  $QueryConditions[] = "Target.`".$IdColumn."` = '".addslashes($DstId)."'";
997  }
998  $Query .= " WHERE ".implode(" OR ", $QueryConditions);
999  $this->Query($Query);
1000  }
1001  }
1002 
1016  public function InsertArray($Table, $ValueField, $Values,
1017  $KeyField = NULL, $AvgDataLength = 20)
1018  {
1019  # pick some ballpark values
1020  $ChunkSizeAssumedSafe = 100;
1021  $QueryLengthAssumedSafe = 10486576; # (1 MB)
1022 
1023  # exit without doing anything if there are no values
1024  $ValueCount = count($Values);
1025  if ($ValueCount == 0)
1026  {
1027  return;
1028  }
1029 
1030  # determine size of array chunk per INSERT statement
1031  $NonValueCharCount = 100;
1032  if ($ValueCount > $ChunkSizeAssumedSafe)
1033  {
1034  $MaxQueryLen = $this->GetMaxQueryLength();
1035  $ValueSegmentLen = $AvgDataLength + 6;
1036  if ($KeyField !== NULL)
1037  {
1038  $ValueSegmentLen = $ValueSegmentLen * 2;
1039  }
1040  $ValueChunkSize = $MaxQueryLen / $ValueSegmentLen;
1041  }
1042  else
1043  {
1044  $ValueChunkSize = $ChunkSizeAssumedSafe;
1045  }
1046 
1047  # for each chunk of values
1048  foreach (array_chunk($Values, $ValueChunkSize, TRUE) as $ValueChunk)
1049  {
1050  # begin building query
1051  $Query = "INSERT INTO `".$Table."` (`".$ValueField."`";
1052 
1053  # if key field was specified
1054  if ($KeyField !== NULL)
1055  {
1056  # add key field to query
1057  $Query .= ", `".$KeyField."`";
1058 
1059  # assemble value segment with keys
1060  $ValueSegFunc = function($Carry, $Key) use ($ValueChunk)
1061  {
1062  $Carry .= "('".addslashes($ValueChunk[$Key])."','"
1063  .addslashes($Key)."'),";
1064  return $Carry;
1065  };
1066  $ValueSegment = array_reduce(array_keys($ValueChunk), $ValueSegFunc);
1067  }
1068  else
1069  {
1070  # assemble value segment
1071  $ValueSegFunc = function($Carry, $Value)
1072  {
1073  $Carry .= "('".addslashes($Value)."'),";
1074  return $Carry;
1075  };
1076  $ValueSegment = array_reduce($ValueChunk, $ValueSegFunc);
1077  }
1078 
1079  # trim extraneous comma off of value segment
1080  $ValueSegment = substr($ValueSegment, 0, -1);
1081 
1082  # add value segment to query
1083  $Query .= ") VALUES ".$ValueSegment;
1084 
1085  # double check to make sure query isn't too long
1086  $QueryLen = strlen($Query);
1087  if ($QueryLen > $QueryLengthAssumedSafe)
1088  {
1089  if (!isset($MaxQueryLen))
1090  {
1091  $MaxQueryLen = $this->GetMaxQueryLength();
1092  }
1093  if ($QueryLen > $MaxQueryLen)
1094  {
1095  throw new Exception("Maximum query length ("
1096  .$MaxQueryLen.") exceeded (".$QueryLen.").");
1097  }
1098  }
1099 
1100  # run query
1101  $this->Query($Query);
1102  }
1103  }
1104 
1105  /*@)*/ /* Data Manipulation */ /*@(*/
1107 
1116  public function EscapeString($String)
1117  {
1118  return mysqli_real_escape_string($this->Handle, $String);
1119  }
1120 
1127  public function LogComment($String)
1128  {
1129  $this->Query("-- ".$String);
1130  }
1131 
1137  public function TableExists($TableName)
1138  {
1139  $this->Query("SHOW TABLES LIKE '".addslashes($TableName)."'");
1140  return $this->NumRowsSelected() ? TRUE : FALSE;
1141  }
1142 
1149  public function FieldExists($TableName, $FieldName)
1150  {
1151  $this->Query("DESC ".$TableName);
1152  while ($CurrentFieldName = $this->FetchField("Field"))
1153  {
1154  if ($CurrentFieldName == $FieldName) { return TRUE; }
1155  }
1156  return FALSE;
1157  }
1158 
1165  public function GetFieldType($TableName, $FieldName)
1166  {
1167  $this->Query("DESC ".$TableName);
1168  $AllTypes = $this->FetchColumn("Type", "Field");
1169  return $AllTypes[$FieldName];
1170  }
1171 
1177  public function GetColumns($TableName)
1178  {
1179  $this->Query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS"
1180  ." WHERE TABLE_SCHEMA = '".addslashes($this->DBName)
1181  ."' AND TABLE_NAME = '".addslashes($TableName)."'");
1182  return $this->FetchColumn("COLUMN_NAME");
1183  }
1184 
1189  public function GetMaxQueryLength()
1190  {
1191  return (int)static::ServerSystemVariable("max_allowed_packet");
1192  }
1193 
1199  public static function QueryDebugOutput($NewSetting)
1200  {
1201  self::$QueryDebugOutputFlag = $NewSetting;
1202  }
1203 
1209  public static function NumQueries()
1210  {
1211  return self::$QueryCounter;
1212  }
1213 
1220  public static function NumCacheHits()
1221  {
1222  return self::$CachedQueryCounter;
1223  }
1224 
1230  public static function CacheHitRate()
1231  {
1232  if (self::$QueryCounter)
1233  {
1234  return (self::$CachedQueryCounter / self::$QueryCounter) * 100;
1235  }
1236  else
1237  {
1238  return 0;
1239  }
1240  }
1241 
1248  public static function SlowQueryThreshold($NewValue = NULL)
1249  {
1250  return (int)static::ServerSystemVariable("long_query_time", intval($NewValue));
1251  }
1252 
1253  /*@)*/ /* Miscellaneous */
1254 
1255  # ---- PRIVATE INTERFACE -------------------------------------------------
1256 
1257  protected $DBUserName;
1258  protected $DBPassword;
1259  protected $DBHostName;
1260  protected $DBName;
1261 
1262  private $Handle;
1263  private $QueryHandle;
1264  private $QueryResults;
1265  private $RowCounter;
1266  private $NumRows;
1267  private $GetResultsFromCache;
1268  private $ErrorIgnored = FALSE;
1269  private $ErrorsToIgnore = NULL;
1270  private $ErrMsg = NULL;
1271  private $ErrNo = NULL;
1272 
1273  private static $DisplayErrors = FALSE;
1274 
1275  private static $GlobalDBUserName;
1276  private static $GlobalDBPassword;
1277  private static $GlobalDBHostName;
1278  private static $GlobalDBName;
1279 
1280  # debug output flag
1281  private static $QueryDebugOutputFlag = FALSE;
1282  # flag for whether caching is turned on
1283  private static $CachingFlag = TRUE;
1284  # query result advanced caching flag
1285  private static $AdvancedCachingFlag = FALSE;
1286  # global cache for query results
1287  private static $QueryResultCache = array();
1288  # stats counters
1289  private static $QueryCounter = 0;
1290  private static $CachedQueryCounter = 0;
1291  # database connection link handles
1292  private static $ConnectionHandles = array();
1293  # do not cache queries that return more than this number of rows
1294  private static $CacheRowsThreshold = 250;
1295  # prune the query cache if there is less than this amount of memory free
1296  private static $CacheMemoryThreshold;
1297  # number of rows to leave in cache when pruning
1298  private static $CacheRowsToLeave = 10;
1299  # number of retry attempts to make to connect to database
1300  private static $ConnectRetryAttempts = 3;
1301  # number of seconds to wait between connection retry attempts
1302  private static $ConnectRetryInterval = 5;
1303 
1304  # server connection error codes
1305  const CR_CONNECTION_ERROR = 2002; # Can't connect to local MySQL server
1306  # through socket '%s' (%d)
1307  const CR_CONN_HOST_ERROR = 2003; # Can't connect to MySQL server on '%s' (%d)
1308  const CR_SERVER_GONE_ERROR = 2006; # MySQL server has gone away
1309  const CR_SERVER_LOST = 2013; # Lost connection to MySQL server during query
1310 
1311  # limits on int variables
1312  # https://dev.mysql.com/doc/refman/5.7/en/integer-types.html
1313  const TINYINT_MAX_VALUE = 127;
1314  const SMALLINT_MAX_VALUE = 32767;
1315  const MEDIUMINT_MAX_VALUE = 8388607;
1316  const INT_MAX_VALUE = 2147483647;
1317  const BIGINT_MAX_VALUE = 9223372036854775807;
1318 
1319  # connection error codes that may be recoverable
1320  private static $RecoverableConnectionErrors = array(
1321  self::CR_CONNECTION_ERROR,
1322  );
1323 
1334  private static function ConnectAndSelectDB(
1336  {
1337  $ConnectAttemptsLeft = self::$ConnectRetryAttempts + 1;
1338  do
1339  {
1340  # if this is not our first connection attempt
1341  if (isset($Handle))
1342  {
1343  # wait for the retry interval
1344  sleep(self::$ConnectRetryInterval);
1345  }
1346 
1347  # attempt to connect to server
1348  $Handle = @mysqli_connect($DBHostName, $DBUserName, $DBPassword);
1349  $ConnectAttemptsLeft--;
1350  }
1351  # repeat if we do not have a connection and there are retry attempts
1352  # left and the connection error code indicates a retry may succeed
1353  // @codingStandardsIgnoreStart
1354  // (because phpcs apparently doesn't know how to handle do-while loops)
1355  while (!$Handle && $ConnectAttemptsLeft
1356  && in_array(mysqli_connect_errno(),
1357  self::$RecoverableConnectionErrors));
1358  // @codingStandardsIgnoreEnd
1359 
1360  # throw exception if connection attempts failed
1361  if (!$Handle)
1362  {
1363  throw new Exception("Could not connect to database: "
1364  .mysqli_connect_error()." (errno: ".mysqli_connect_errno().")");
1365  }
1366 
1367  # select DB
1368  $Result = mysqli_select_db($Handle, $DBName);
1369  if ($Result !== TRUE)
1370  {
1371  throw new Exception("Could not select database: "
1372  .mysqli_error($Handle)." (errno: ".mysqli_errno($Handle).")");
1373  }
1374 
1375  # return new connection to caller
1376  return $Handle;
1377  }
1378 
1384  private function IsReadOnlyStatement($QueryString)
1385  {
1386  return preg_match("/^[ ]*SELECT /i", $QueryString) ? TRUE : FALSE;
1387  }
1388 
1395  private function TableModified($QueryString)
1396  {
1397  # assume we're not going to be able to determine table
1398  $TableName = FALSE;
1399 
1400  # split query into pieces
1401  $QueryString = trim($QueryString);
1402  $Words = preg_split("/\s+/", $QueryString);
1403 
1404  # if INSERT statement
1405  $WordIndex = 1;
1406  if (strtoupper($Words[0]) == "INSERT")
1407  {
1408  # skip over modifying keywords
1409  while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
1410  || (strtoupper($Words[$WordIndex]) == "DELAYED")
1411  || (strtoupper($Words[$WordIndex]) == "IGNORE")
1412  || (strtoupper($Words[$WordIndex]) == "INTO"))
1413  {
1414  $WordIndex++;
1415  }
1416 
1417  # next word is table name
1418  $TableName = $Words[$WordIndex];
1419  }
1420  # else if UPDATE statement
1421  elseif (strtoupper($Words[0]) == "UPDATE")
1422  {
1423  # skip over modifying keywords
1424  while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
1425  || (strtoupper($Words[$WordIndex]) == "IGNORE"))
1426  {
1427  $WordIndex++;
1428  }
1429 
1430  # if word following next word is SET
1431  if (strtoupper($Words[$WordIndex + 1]) == "SET")
1432  {
1433  # next word is table name
1434  $TableName = $Words[$WordIndex];
1435  }
1436  }
1437  # else if DELETE statement
1438  elseif (strtoupper($Words[0]) == "DELETE")
1439  {
1440  # skip over modifying keywords
1441  while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
1442  || (strtoupper($Words[$WordIndex]) == "IGNORE")
1443  || (strtoupper($Words[$WordIndex]) == "QUICK"))
1444  {
1445  $WordIndex++;
1446  }
1447 
1448  # if next term is FROM
1449  if (strtoupper($Words[$WordIndex]) == "FROM")
1450  {
1451  # next word is table name
1452  $WordIndex++;
1453  $TableName = $Words[$WordIndex];
1454  }
1455  }
1456 
1457  # discard table name if it looks at all suspicious
1458  if ($TableName)
1459  {
1460  if (!preg_match("/[a-zA-Z0-9]+/", $TableName))
1461  {
1462  $TableName = FALSE;
1463  }
1464  }
1465 
1466  # return table name (or lack thereof) to caller
1467  return $TableName;
1468  }
1469 
1476  private function TablesAccessed($QueryString)
1477  {
1478  # assume we're not going to be able to determine tables
1479  $TableNames = FALSE;
1480 
1481  # split query into pieces
1482  $QueryString = trim($QueryString);
1483  $Words = preg_split("/\s+/", $QueryString);
1484  $UQueryString = strtoupper($QueryString);
1485  $UWords = preg_split("/\s+/", $UQueryString);
1486 
1487  # if SELECT statement
1488  if ($UWords[0] == "SELECT")
1489  {
1490  # keep going until we hit FROM or last word
1491  $WordIndex = 1;
1492  while (($UWords[$WordIndex] != "FROM")
1493  && strlen($UWords[$WordIndex]))
1494  {
1495  $WordIndex++;
1496  }
1497 
1498  # if we hit FROM
1499  if ($UWords[$WordIndex] == "FROM")
1500  {
1501  # for each word after FROM
1502  $WordIndex++;
1503  while (strlen($UWords[$WordIndex]))
1504  {
1505  # if current word ends with comma
1506  if (preg_match("/,$/", $Words[$WordIndex]))
1507  {
1508  # strip off comma and add word to table name list
1509  $TableNames[] = substr($Words[$WordIndex], 0, -1);
1510  }
1511  else
1512  {
1513  # add word to table name list
1514  $TableNames[] = $Words[$WordIndex];
1515 
1516  # if next word is not comma
1517  $WordIndex++;
1518  if ($Words[$WordIndex] != ",")
1519  {
1520  # if word begins with comma
1521  if (preg_match("/^,/", $Words[$WordIndex]))
1522  {
1523  # strip off comma (NOTE: modifies $Words array!)
1524  $Words[$WordIndex] = substr($Words[$WordIndex], 1);
1525 
1526  # decrement index so we start with this word next pass
1527  $WordIndex--;
1528  }
1529  else
1530  {
1531  # stop scanning words (non-basic JOINs not yet handled)
1532  break;
1533  }
1534  }
1535  }
1536 
1537  # move to next word
1538  $WordIndex++;
1539  }
1540  }
1541  }
1542 
1543  # discard table names if they look at all suspicious
1544  if ($TableNames)
1545  {
1546  foreach ($TableNames as $Name)
1547  {
1548  if (!preg_match("/^[a-zA-Z0-9]+$/", $Name))
1549  {
1550  $TableNames = FALSE;
1551  break;
1552  }
1553  }
1554  }
1555 
1556  # return table name (or lack thereof) to caller
1557  return $TableNames;
1558  }
1559 
1566  private function RunQuery($QueryString)
1567  {
1568  # log query start time if debugging output is enabled
1569  if (self::$QueryDebugOutputFlag) { $QueryStartTime = microtime(TRUE); }
1570 
1571  # run query against database
1572  $this->QueryHandle = mysqli_query($this->Handle, $QueryString) ;
1573 
1574  # print query and execution time if debugging output is enabled
1575  if (self::$QueryDebugOutputFlag)
1576  {
1577  print "DB: ".$QueryString." ["
1578  .sprintf("%.2f", microtime(TRUE) - $QueryStartTime)
1579  ."s]"."<br>\n";
1580  }
1581 
1582  # if query failed and there are errors that we can ignore
1583  if (($this->QueryHandle === FALSE) && $this->ErrorsToIgnore)
1584  {
1585  # for each pattern for an error that we can ignore
1586  foreach ($this->ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
1587  {
1588  # if error matches pattern
1589  $ErrorMsg = mysqli_error($this->Handle);
1590  if (preg_match($SqlPattern, $QueryString)
1591  && preg_match($ErrMsgPattern, $ErrorMsg))
1592  {
1593  # set return value to indicate error was ignored
1594  $this->QueryHandle = TRUE;
1595 
1596  # set internal flag to indicate that an error was ignored
1597  $this->ErrorIgnored = $ErrorMsg;
1598 
1599  # stop looking at patterns
1600  break;
1601  }
1602  }
1603  }
1604 
1605  # if query failed
1606  if ($this->QueryHandle === FALSE)
1607  {
1608  # clear stored value for number of rows retrieved
1609  $this->NumRows = 0;
1610 
1611  # retrieve error info
1612  $this->ErrMsg = mysqli_error($this->Handle);
1613  $this->ErrNo = mysqli_errno($this->Handle);
1614 
1615  # if we are supposed to be displaying errors
1616  if (self::$DisplayErrors)
1617  {
1618  # print error info
1619  print("<b>SQL Error:</b> <i>".$this->ErrMsg
1620  ."</i> (".$this->ErrNo.")<br/>\n");
1621  print("<b>SQL Statement:</b> <i>"
1622  .htmlspecialchars($QueryString)."</i><br/>\n");
1623 
1624  # retrieve execution trace that got us to this point
1625  $Trace = debug_backtrace();
1626 
1627  # remove current context from trace
1628  array_shift($Trace);
1629 
1630  # make sure file name and line number are available
1631  foreach ($Trace as $Index => $Loc)
1632  {
1633  if (!array_key_exists("file", $Loc))
1634  {
1635  $Trace[$Index]["file"] = "UNKNOWN";
1636  }
1637  if (!array_key_exists("line", $Loc))
1638  {
1639  $Trace[$Index]["line"] = "??";
1640  }
1641  }
1642 
1643  # determine length of leading path common to all file names in trace
1644  $LocString = "";
1645  $OurFile = __FILE__;
1646  $PrefixLen = 9999;
1647  foreach ($Trace as $Loc)
1648  {
1649  if ($Loc["file"] != "UNKNOWN")
1650  {
1651  $Index = 0;
1652  $FNameLength = strlen($Loc["file"]);
1653  while ($Index < $FNameLength &&
1654  $Loc["file"][$Index] == $OurFile[$Index])
1655  { $Index++; }
1656  $PrefixLen = min($PrefixLen, $Index);
1657  }
1658  }
1659 
1660  foreach ($Trace as $Loc)
1661  {
1662  $Sep = "";
1663  $ArgString = "";
1664  foreach ($Loc["args"] as $Arg)
1665  {
1666  $ArgString .= $Sep;
1667  switch (gettype($Arg))
1668  {
1669  case "boolean":
1670  $ArgString .= $Arg ? "TRUE" : "FALSE";
1671  break;
1672 
1673  case "integer":
1674  case "double":
1675  $ArgString .= $Arg;
1676  break;
1677 
1678  case "string":
1679  $ArgString .= '"<i>'.htmlspecialchars(substr($Arg, 0, 40))
1680  .((strlen($Arg) > 40) ? "..." : "").'</i>"';
1681  break;
1682 
1683  case "array":
1684  case "resource":
1685  case "NULL":
1686  $ArgString .= strtoupper(gettype($Arg));
1687  break;
1688 
1689  case "object":
1690  $ArgString .= get_class($Arg);
1691  break;
1692 
1693  case "unknown type":
1694  $ArgString .= "UNKNOWN";
1695  break;
1696  }
1697  $Sep = ",";
1698  }
1699  $Loc["file"] = substr($Loc["file"], $PrefixLen);
1700  $LocString .= "&nbsp;&nbsp;";
1701  if (array_key_exists("class", $Loc))
1702  { $LocString .= $Loc["class"]."::"; }
1703  $LocString .= $Loc["function"]."(".$ArgString.")"
1704  ." - ".$Loc["file"].":".$Loc["line"]
1705  ."<br>\n";
1706  }
1707  print("<b>Trace:</b><br>\n".$LocString);
1708  }
1709  }
1710  return $this->QueryHandle;
1711  }
1712 
1717  static private function GetPhpMemoryLimit()
1718  {
1719  $Str = strtoupper(ini_get("memory_limit"));
1720  if (substr($Str, -1) == "B") { $Str = substr($Str, 0, strlen($Str) - 1); }
1721  switch (substr($Str, -1))
1722  {
1723  case "K":
1724  $MemoryLimit = (int)$Str * 1024;
1725  break;
1726 
1727  case "M":
1728  $MemoryLimit = (int)$Str * 1048576;
1729  break;
1730 
1731  case "G":
1732  $MemoryLimit = (int)$Str * 1073741824;
1733  break;
1734 
1735  default:
1736  $MemoryLimit = (int)$Str;
1737  break;
1738  }
1739  return $MemoryLimit;
1740  }
1741 
1746  static private function GetFreeMemory()
1747  {
1748  return self::GetPhpMemoryLimit() - memory_get_usage();
1749  }
1750 
1760  static private function ServerSystemVariable($VarName, $NewValue = NULL)
1761  {
1762  static $DB;
1763 
1764  if (!isset($DB))
1765  {
1766  $DB = new self();
1767  }
1768 
1769  if ($NewValue !== NULL)
1770  {
1771  if (is_string($NewValue))
1772  {
1773  $NewValue = "'".addslashes($NewValue)."'";
1774  }
1775  $DB->Query("SET ".$VarName." = ".$NewValue);
1776  }
1777 
1778  return $DB->Query("SHOW VARIABLES LIKE '".addslashes($VarName)."'",
1779  "Value");
1780  }
1781 }
1782 
1783 # define value to designate omitted arguments (so DB values can be set to NULL)
1784 define("DB_NOVALUE", "!-_-_-DB_NOVALUE-_-_-!");
1785 
QueryErrMsg()
Get most recent error message text set by Query().
Definition: Database.php:595
static Caching($NewSetting=NULL)
Get or set whether query result caching is currently enabled.
Definition: Database.php:262
__sleep()
Definition: Database.php:84
GetServerVersion($FullVersion=FALSE)
Get database server version number.
Definition: Database.php:186
static SetGlobalDatabaseName($DatabaseName)
Set default database name.
Definition: Database.php:157
const CR_CONNECTION_ERROR
Definition: Database.php:1305
SetDefaultStorageEngine($Engine)
Set default database storage engine.
Definition: Database.php:170
ExecuteQueriesFromFile($FileName)
Execute queries from specified file.
Definition: Database.php:535
UpdateIntValue($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
A convenience function to get or set an integer value in the database.
Definition: Database.php:915
SQL database abstraction object with smart query caching.
Definition: Database.php:22
SetQueryErrorsToIgnore($ErrorsToIgnore, $NormalizeWhitespace=TRUE)
Set query errors to ignore.
Definition: Database.php:316
const CR_SERVER_GONE_ERROR
Definition: Database.php:1308
DBUserName()
Get name used to connect with database server.
Definition: Database.php:250
GetNextInsertId($TableName)
For tables that have an AUTO_INCREMENT column, get the next value that will be assigned.
Definition: Database.php:805
EscapeString($String)
Escape a string that may contain null bytes.
Definition: Database.php:1116
FetchRow()
Get next database row retrieved by most recent query.
Definition: Database.php:669
LastInsertId()
Get ID of row added by the last SQL "INSERT" statement.
Definition: Database.php:788
const CR_SERVER_LOST
Definition: Database.php:1309
__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:1137
const INT_MAX_VALUE
Definition: Database.php:1316
static SlowQueryThreshold($NewValue=NULL)
Get/set current threshold for what is considered a "slow" SQL query.
Definition: Database.php:1248
static SetGlobalServerInfo($UserName, $Password, $HostName="localhost")
Set default login and host info for database server.
Definition: Database.php:141
GetClientVersion()
Get version number of the client libraries being used to connect to the database server (Currently th...
Definition: Database.php:210
GetFieldType($TableName, $FieldName)
Get field (column) type.
Definition: Database.php:1165
const DB_NOVALUE
Definition: Database.php:1784
const MEDIUMINT_MAX_VALUE
Definition: Database.php:1315
NumRowsSelected()
Get number of rows returned by last SELECT or SHOW query.
Definition: Database.php:626
const SMALLINT_MAX_VALUE
Definition: Database.php:1314
FetchRows($NumberOfRows=NULL)
Get specified number of database rows retrieved by most recent query.
Definition: Database.php:716
static QueryDebugOutput($NewSetting)
Enable or disable debugging output for queries.
Definition: Database.php:1199
Query($QueryString, $FieldName="")
Query database (with caching if enabled).
Definition: Database.php:355
GetColumns($TableName)
Get column (database field) names.
Definition: Database.php:1177
FetchField($FieldName)
Pull next row from last DB query and get a specific value from that row.
Definition: Database.php:776
FieldExists($TableName, $FieldName)
Get whether specified field exists in specified table.
Definition: Database.php:1149
static NumCacheHits()
Get the number of queries that have resulted in cache hits since program execution began...
Definition: Database.php:1220
FetchColumn($FieldName, $IndexFieldName=NULL)
Get all available values for specified database field retrieved by most recent query.
Definition: Database.php:751
DBHostName()
Get host name of system on which database server resides.
Definition: Database.php:230
NumRowsAffected()
Get number of rows affected by last INSERT, UPDATE, REPLACE, or DELETE query.
Definition: Database.php:652
UpdateFloatValue($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
A convenience function to get or set a float value in the database.
Definition: Database.php:943
InsertArray($Table, $ValueField, $Values, $KeyField=NULL, $AvgDataLength=20)
Insert an array of values with a minimum number of INSERT statements.
Definition: Database.php:1016
QueryErrNo()
Get most recent error code set by Query().
Definition: Database.php:605
GetHostInfo()
Get database connection type and hostname.
Definition: Database.php:220
UpdateValue($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
A convenience function to get or set a value in the database.
Definition: Database.php:833
static AdvancedCaching($NewSetting=NULL)
Get or set whether advanced query result cachine is currently enabled.
Definition: Database.php:288
const BIGINT_MAX_VALUE
Definition: Database.php:1317
const TINYINT_MAX_VALUE
Definition: Database.php:1313
static DisplayQueryErrors($NewValue=NULL)
Get/set whether Query() errors will be displayed.
Definition: Database.php:616
static CacheHitRate()
Get the ratio of query cache hits to queries as a percentage.
Definition: Database.php:1230
GetMaxQueryLength()
Get maximum size for query string.
Definition: Database.php:1189
IgnoredError()
Check whether an error was ignored by the most recent query.
Definition: Database.php:337
DBName()
Get current database name.
Definition: Database.php:240
CopyValues($TableName, $IdColumn, $SrcId, $DstId, $ColumnsToExclude=array())
A convenience function to copy values from one row to another.
Definition: Database.php:966
LogComment($String)
Peform query that consists of SQL comment statement.
Definition: Database.php:1127
__wakeup()
Restore database connection when unserialized.
Definition: Database.php:93
static NumQueries()
Get the number of queries that have been run since program execution began.
Definition: Database.php:1209