CWIS Developer Documentation
Axis--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  # ---- PUBLIC INTERFACE --------------------------------------------------
25  /*@(*/
27 
40  function Database(
41  $UserName = NULL, $Password = NULL, $DatabaseName = NULL, $HostName = NULL)
42  {
43  # save DB access parameter values
44  $this->DBUserName = $UserName ? $UserName : self::$GlobalDBUserName;
45  $this->DBPassword = $Password ? $Password : self::$GlobalDBPassword;
46  $this->DBHostName = $HostName ? $HostName :
47  (isset(self::$GlobalDBHostName) ? self::$GlobalDBHostName
48  : "localhost");
49  $this->DBName = $DatabaseName ? $DatabaseName : self::$GlobalDBName;
50 
51  # if we don't already have a connection or DB access parameters were supplied
52  $HandleIndex = $this->DBHostName.":".$this->DBName;
53  if (!array_key_exists($HandleIndex, self::$ConnectionHandles)
54  || $UserName || $Password || $DatabaseName || $HostName)
55  {
56  # open connection to DB server
57  self::$ConnectionHandles[$HandleIndex] = mysqli_connect(
58  $this->DBHostName, $this->DBUserName,
59  $this->DBPassword)
60  or die("Could not connect to database: ".mysqli_connect_error());
61 
62  # set local connection handle
63  $this->Handle = self::$ConnectionHandles[$HandleIndex];
64 
65  # select DB
66  mysqli_select_db($this->Handle, $this->DBName)
67  or die(mysqli_error($this->Handle));
68  }
69  else
70  {
71  # set local connection handle
72  $this->Handle = self::$ConnectionHandles[$HandleIndex];
73  }
74  }
75 
80  function __sleep()
81  {
82  return array("DBUserName", "DBPassword", "DBHostName", "DBName");
83  }
87  function __wakeup()
88  {
89  # open connection to DB server
90  $this->Handle = mysqli_connect(
91  $this->DBHostName, $this->DBUserName, $this->DBPassword)
92  or die("could not connect to database");
93 
94  # select DB
95  mysqli_select_db($this->Handle, $this->DBName)
96  or die(mysqli_error($this->Handle));
97  }
107  static function SetGlobalServerInfo($UserName, $Password, $HostName = "localhost")
108  {
109  # save default DB access parameters
110  self::$GlobalDBUserName = $UserName;
111  self::$GlobalDBPassword = $Password;
112  self::$GlobalDBHostName = $HostName;
113 
114  # clear any existing DB connection handles
115  self::$ConnectionHandles = array();
116  }
117 
122  static function SetGlobalDatabaseName($DatabaseName)
123  {
124  # save new default DB name
125  self::$GlobalDBName = $DatabaseName;
126 
127  # clear any existing DB connection handles
128  self::$ConnectionHandles = array();
129  }
130 
135  function SetDefaultStorageEngine($Engine)
136  {
137  # choose config variable to use based on server version number
138  $ConfigVar = version_compare($this->GetServerVersion(), "5.5", "<")
139  ? "storage_engine" : "default_storage_engine";
140 
141  # set storage engine in database
142  $this->Query("SET ".$ConfigVar." = ".$Engine);
143  }
144 
151  function GetServerVersion($FullVersion=FALSE)
152  {
153  # retrieve version string
154  $Version = $this->Query("SELECT VERSION() AS ServerVer", "ServerVer");
155 
156  if (!$FullVersion)
157  {
158  # strip off any build/config suffix
159  $Pieces = explode("-", $Version);
160  $Version = array_shift($Pieces);
161  }
162 
163  # return version number to caller
164  return $Version;
165  }
166 
175  function GetClientVersion()
176  {
177  return mysqli_get_client_info();
178  }
179 
185  function GetHostInfo()
186  {
187  return mysqli_get_host_info($this->Handle);
188  }
189 
195  function DBHostName() { return $this->DBHostName; }
196 
202  function DBName() { return $this->DBName; }
203 
209  function DBUserName() { return $this->DBUserName; }
210 
218  static function Caching($NewSetting = NULL)
219  {
220  # if cache setting has changed
221  if (($NewSetting !== NULL) && ($NewSetting != self::$CachingFlag))
222  {
223  # save new setting
224  self::$CachingFlag = $NewSetting;
225 
226  # clear any existing cached results
227  self::$QueryResultCache = array();
228  }
229 
230  # return current setting to caller
231  return self::$CachingFlag;
232  }
233 
244  static function AdvancedCaching($NewSetting = NULL)
245  {
246  if ($NewSetting !== NULL)
247  {
248  self::$AdvancedCachingFlag = $NewSetting;
249  }
250  return self::$AdvancedCachingFlag;
251  }
252 
272  function SetQueryErrorsToIgnore($ErrorsToIgnore, $NormalizeWhitespace = TRUE)
273  {
274  if ($NormalizeWhitespace && ($ErrorsToIgnore !== NULL))
275  {
276  $RevisedErrorsToIgnore = array();
277  foreach ($ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
278  {
279  $SqlPattern = preg_replace("/\\s+/", "\\s+", $SqlPattern);
280  $RevisedErrorsToIgnore[$SqlPattern] = $ErrMsgPattern;
281  }
282  $ErrorsToIgnore = $RevisedErrorsToIgnore;
283  }
284  $this->ErrorsToIgnore = $ErrorsToIgnore;
285  }
286 
292  function IgnoredError()
293  {
294  return $this->ErrorIgnored;
295  }
296 
297  /*@)*/ /* Setup/Initialization */ /*@(*/
299 
307  function Query($QueryString, $FieldName = "")
308  {
309  # clear flag that indicates whether query error was ignored
310  $this->ErrorIgnored = FALSE;
311 
312  # if caching is enabled
313  if (self::$CachingFlag)
314  {
315  # if SQL statement is read-only
316  if ($this->IsReadOnlyStatement($QueryString))
317  {
318  # if we have statement in cache
319  if (isset(self::$QueryResultCache[$QueryString]["NumRows"]))
320  {
321  if (self::$QueryDebugOutputFlag)
322  { print("DB-C: $QueryString<br>\n"); }
323 
324  # make sure query result looks okay
325  $this->QueryHandle = TRUE;
326 
327  # increment cache hit counter
328  self::$CachedQueryCounter++;
329 
330  # make local copy of results
331  $this->QueryResults = self::$QueryResultCache[$QueryString];
332  $this->NumRows = self::$QueryResultCache[$QueryString]["NumRows"];
333 
334  # set flag to indicate that results should be retrieved from cache
335  $this->GetResultsFromCache = TRUE;
336  }
337  else
338  {
339  # execute SQL statement
340  $this->QueryHandle = $this->RunQuery($QueryString);
341  if (!$this->QueryHandle instanceof mysqli_result) { return FALSE; }
342 
343  # save number of rows in result
344  $this->NumRows = mysqli_num_rows($this->QueryHandle);
345 
346  # if too many rows to cache
347  if ($this->NumRows >= 50)
348  {
349  # set flag to indicate that query results should not
350  # be retrieved from cache
351  $this->GetResultsFromCache = FALSE;
352  }
353  else
354  {
355  # if advanced caching is enabled
356  if (self::$AdvancedCachingFlag)
357  {
358  # save tables accessed by query
359  self::$QueryResultCache[$QueryString]["TablesAccessed"] =
360  $this->TablesAccessed($QueryString);
361  }
362 
363  # if rows found
364  if ($this->NumRows > 0)
365  {
366  # load query results
367  for ($Row = 0; $Row < $this->NumRows; $Row++)
368  {
369  $this->QueryResults[$Row] =
370  mysqli_fetch_assoc($this->QueryHandle);
371  }
372 
373  # cache query results
374  self::$QueryResultCache[$QueryString] = $this->QueryResults;
375  }
376  else
377  {
378  # clear local query results
379  unset($this->QueryResults);
380  }
381 
382  # cache number of rows
383  self::$QueryResultCache[$QueryString]["NumRows"] = $this->NumRows;
384 
385  # set flag to indicate that query results should be retrieved from cache
386  $this->GetResultsFromCache = TRUE;
387  }
388  }
389  }
390  else
391  {
392  # if advanced caching is enabled
393  if (self::$AdvancedCachingFlag)
394  {
395  # if table modified by statement is known
396  $TableModified = $this->TableModified($QueryString);
397  if ($TableModified)
398  {
399  # for each cached query
400  foreach (self::$QueryResultCache
401  as $CachedQueryString => $CachedQueryResult)
402  {
403  # if we know what tables were accessed
404  if ($CachedQueryResult["TablesAccessed"])
405  {
406  # if tables accessed include the one we may modify
407  if (in_array($TableModified, $CachedQueryResult["TablesAccessed"]))
408  {
409  # clear cached query results
410  unset($GLOBALS["APDBQueryResultCache"][$CachedQueryString]);
411  }
412  }
413  else
414  {
415  # clear cached query results
416  unset($GLOBALS["APDBQueryResultCache"][$CachedQueryString]);
417  }
418  }
419  }
420  else
421  {
422  # clear entire query result cache
423  self::$QueryResultCache = array();
424  }
425  }
426  else
427  {
428  # clear entire query result cache
429  self::$QueryResultCache = array();
430  }
431 
432  # execute SQL statement
433  $this->QueryHandle = $this->RunQuery($QueryString);
434  if ($this->QueryHandle === FALSE) { return FALSE; }
435 
436  # set flag to indicate that query results should not be retrieved from cache
437  $this->GetResultsFromCache = FALSE;
438  }
439 
440  # reset row counter
441  $this->RowCounter = 0;
442 
443  # increment query counter
444  self::$QueryCounter++;
445  }
446  else
447  {
448  # execute SQL statement
449  $this->QueryHandle = $this->RunQuery($QueryString);
450  if ($this->QueryHandle === FALSE) { return FALSE; }
451  }
452 
453  if (($FieldName != "") && ($this->QueryHandle != FALSE))
454  {
455  return $this->FetchField($FieldName);
456  }
457  else
458  {
459  return $this->QueryHandle;
460  }
461  }
462 
475  function ExecuteQueriesFromFile($FileName)
476  {
477  # open file
478  $FHandle = fopen($FileName, "r");
479 
480  # if file open succeeded
481  if ($FHandle !== FALSE)
482  {
483  # while lines left in file
484  $Query = "";
485  $QueryCount = 0;
486  while (!feof($FHandle))
487  {
488  # read in line from file
489  $Line = fgets($FHandle, 32767);
490 
491  # trim whitespace from line
492  $Line = trim($Line);
493 
494  # if line is not empty and not a comment
495  if (!preg_match("/^#/", $Line)
496  && !preg_match("/^--/", $Line)
497  && strlen($Line))
498  {
499  # add line to current query
500  $Query .= " ".$Line;
501 
502  # if line completes a query
503  if (preg_match("/;$/", $Line))
504  {
505  # run query
506  $QueryCount++;
507  $Result = $this->Query($Query);
508  $Query = "";
509 
510  # if query resulted in an error that is not ignorable
511  if ($Result === FALSE)
512  {
513  # stop processing queries and set error code
514  $QueryCount = NULL;
515  break;
516  }
517  }
518  }
519  }
520 
521  # close file
522  fclose($FHandle);
523  }
524 
525  # return number of executed queries to caller
526  return $QueryCount;
527  }
528 
534  function QueryErrMsg()
535  {
536  return $this->ErrMsg;
537  }
538 
544  function QueryErrNo()
545  {
546  return $this->ErrNo;
547  }
548 
555  static function DisplayQueryErrors($NewValue = NULL)
556  {
557  if ($NewValue !== NULL) { self::$DisplayErrors = $NewValue; }
558  return self::$DisplayErrors;
559  }
560 
565  function NumRowsSelected()
566  {
567  # if caching is enabled and query was cached
568  if (self::$CachingFlag && $this->GetResultsFromCache)
569  {
570  # return cached number of rows to caller
571  return $this->NumRows;
572  }
573  else
574  {
575  # call to this method after an unsuccessful query
576  if (!$this->QueryHandle instanceof mysqli_result)
577  {
578  return 0;
579  }
580 
581  # retrieve number of rows and return to caller
582  return mysqli_num_rows($this->QueryHandle);
583  }
584  }
585 
591  function NumRowsAffected()
592  {
593  # call to this method after an unsuccessful query
594  if (!$this->QueryHandle instanceof mysqli_result)
595  {
596  return 0;
597  }
598 
599  # retrieve number of rows and return to caller
600  return mysqli_affected_rows($this->Handle);
601  }
602 
608  function FetchRow()
609  {
610  # if caching is enabled and query was cached
611  if (self::$CachingFlag && $this->GetResultsFromCache)
612  {
613  # if rows left to return
614  if ($this->RowCounter < $this->NumRows)
615  {
616  # retrieve row from cache
617  $Result = $this->QueryResults[$this->RowCounter];
618 
619  # increment row counter
620  $this->RowCounter++;
621  }
622  else
623  {
624  # return nothing
625  $Result = FALSE;
626  }
627  }
628  else
629  {
630  # call to this method after successful query
631  if ($this->QueryHandle instanceof mysqli_result)
632  {
633  $Result = mysqli_fetch_assoc($this->QueryHandle);
634  if ($Result === NULL) { $Result = FALSE; }
635  }
636 
637  # call to this method after unsuccessful query
638  else
639  {
640  $Result = FALSE;
641  }
642  }
643 
644  # return row to caller
645  return $Result;
646  }
647 
654  function FetchRows($NumberOfRows = NULL)
655  {
656  # assume no rows will be returned
657  $Result = array();
658 
659  # for each available row
660  $RowsFetched = 0;
661  while ((($RowsFetched < $NumberOfRows) || ($NumberOfRows == NULL))
662  && ($Row = $this->FetchRow()))
663  {
664  # add row to results
665  $Result[] = $Row;
666  $RowsFetched++;
667  }
668 
669  # return array of rows to caller
670  return $Result;
671  }
672 
689  function FetchColumn($FieldName, $IndexFieldName = NULL)
690  {
691  $Array = array();
692  while ($Record = $this->FetchRow())
693  {
694  if ($IndexFieldName != NULL)
695  {
696  $Array[$Record[$IndexFieldName]] = $Record[$FieldName];
697  }
698  else
699  {
700  $Array[] = $Record[$FieldName];
701  }
702  }
703  return $Array;
704  }
705 
714  function FetchField($FieldName)
715  {
716  $Record = $this->FetchRow();
717  return isset($Record[$FieldName]) ? $Record[$FieldName] : NULL;
718  }
719 
726  function LastInsertId()
727  {
728  return (int)$this->Query(
729  "SELECT LAST_INSERT_ID() AS InsertId",
730  "InsertId");
731  }
732 
747  function UpdateValue(
748  $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
749  {
750  # expand condition if supplied
751  if ($Condition != NULL) { $Condition = " WHERE ".$Condition; }
752 
753  # read cached record from database if not already loaded
754  if (!isset($CachedRecord))
755  {
756  $this->Query("SELECT * FROM `".$TableName."` ".$Condition);
757  $CachedRecord = $this->FetchRow();
758  }
759 
760  # if new value supplied
761  if ($NewValue !== DB_NOVALUE)
762  {
763  # update value in database
764  $this->Query("UPDATE `".$TableName."` SET `".$FieldName."` = "
765  .(($NewValue === NULL) ? "NULL" : "'"
766  .mysqli_real_escape_string($this->Handle, $NewValue)."'")
767  .$Condition);
768 
769  # update value in cached record
770  $CachedRecord[$FieldName] = $NewValue;
771  }
772 
773  # return value from cached record to caller
774  return isset($CachedRecord[$FieldName])
775  ? $CachedRecord[$FieldName] : NULL;
776  }
777 
794  function UpdateIntValue(
795  $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
796  {
797  return $this->UpdateValue($TableName, $FieldName,
798  (($NewValue === DB_NOVALUE) ? DB_NOVALUE : (int)$NewValue),
799  $Condition, $CachedRecord);
800  }
801 
819  $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
820  {
821  return $this->UpdateValue($TableName, $FieldName,
822  (($NewValue === DB_NOVALUE) ? DB_NOVALUE : (float)$NewValue),
823  $Condition, $CachedRecord);
824  }
825 
826  /*@)*/ /* Data Manipulation */ /*@(*/
828 
837  function EscapeString($String)
838  {
839  return mysqli_real_escape_string($this->Handle, $String);
840  }
841 
848  function LogComment($String)
849  {
850  $this->Query("-- ".$String);
851  }
852 
858  function TableExists($TableName)
859  {
860  $this->Query("SHOW TABLES LIKE '".addslashes($TableName)."'");
861  return $this->NumRowsSelected() ? TRUE : FALSE;
862  }
863 
870  function FieldExists($TableName, $FieldName)
871  {
872  $this->Query("DESC ".$TableName);
873  while ($CurrentFieldName = $this->FetchField("Field"))
874  {
875  if ($CurrentFieldName == $FieldName) { return TRUE; }
876  }
877  return FALSE;
878  }
879 
886  function GetFieldType($TableName, $FieldName)
887  {
888  $this->Query("DESC ".$TableName);
889  $AllTypes = $this->FetchColumn("Type", "Field");
890  return $AllTypes[$FieldName];
891  }
892 
898  static function QueryDebugOutput($NewSetting)
899  {
900  self::$QueryDebugOutputFlag = $NewSetting;
901  }
902 
908  static function NumQueries()
909  {
910  return self::$QueryCounter;
911  }
912 
919  static function NumCacheHits()
920  {
921  return self::$CachedQueryCounter;
922  }
923 
929  static function CacheHitRate()
930  {
931  if (self::$QueryCounter)
932  {
933  return (self::$CachedQueryCounter / self::$QueryCounter) * 100;
934  }
935  else
936  {
937  return 0;
938  }
939  }
940 
941  /*@)*/ /* Miscellaneous */
942 
943  # ---- PRIVATE INTERFACE -------------------------------------------------
944 
945  protected $DBUserName;
946  protected $DBPassword;
947  protected $DBHostName;
948  protected $DBName;
949 
950  private $Handle;
951  private $QueryHandle;
952  private $QueryResults;
953  private $RowCounter;
954  private $NumRows;
955  private $GetResultsFromCache;
956  private $ErrorIgnored = FALSE;
957  private $ErrorsToIgnore = NULL;
958  private $ErrMsg = NULL;
959  private $ErrNo = NULL;
960 
961  private static $DisplayErrors = FALSE;
962 
963  private static $GlobalDBUserName;
964  private static $GlobalDBPassword;
965  private static $GlobalDBHostName;
966  private static $GlobalDBName;
967 
968  # debug output flag
969  private static $QueryDebugOutputFlag = FALSE;
970  # flag for whether caching is turned on
971  private static $CachingFlag = TRUE;
972  # query result advanced caching flag
973  private static $AdvancedCachingFlag = FALSE;
974  # global cache for query results
975  private static $QueryResultCache = array();
976  # stats counters
977  private static $QueryCounter = 0;
978  private static $CachedQueryCounter = 0;
979  # database connection link handles
980  private static $ConnectionHandles = array();
981 
987  private function IsReadOnlyStatement($QueryString)
988  {
989  return preg_match("/^[ ]*SELECT /i", $QueryString) ? TRUE : FALSE;
990  }
991 
998  private function TableModified($QueryString)
999  {
1000  # assume we're not going to be able to determine table
1001  $TableName = FALSE;
1002 
1003  # split query into pieces
1004  $QueryString = trim($QueryString);
1005  $Words = preg_split("/\s+/", $QueryString);
1006 
1007  # if INSERT statement
1008  $WordIndex = 1;
1009  if (strtoupper($Words[0]) == "INSERT")
1010  {
1011  # skip over modifying keywords
1012  while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
1013  || (strtoupper($Words[$WordIndex]) == "DELAYED")
1014  || (strtoupper($Words[$WordIndex]) == "IGNORE")
1015  || (strtoupper($Words[$WordIndex]) == "INTO"))
1016  {
1017  $WordIndex++;
1018  }
1019 
1020  # next word is table name
1021  $TableName = $Words[$WordIndex];
1022  }
1023  # else if UPDATE statement
1024  elseif (strtoupper($Words[0]) == "UPDATE")
1025  {
1026  # skip over modifying keywords
1027  while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
1028  || (strtoupper($Words[$WordIndex]) == "IGNORE"))
1029  {
1030  $WordIndex++;
1031  }
1032 
1033  # if word following next word is SET
1034  if (strtoupper($Words[$WordIndex + 1]) == "SET")
1035  {
1036  # next word is table name
1037  $TableName = $Words[$WordIndex];
1038  }
1039  }
1040  # else if DELETE statement
1041  elseif (strtoupper($Words[0]) == "DELETE")
1042  {
1043  # skip over modifying keywords
1044  while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
1045  || (strtoupper($Words[$WordIndex]) == "IGNORE")
1046  || (strtoupper($Words[$WordIndex]) == "QUICK"))
1047  {
1048  $WordIndex++;
1049  }
1050 
1051  # if next term is FROM
1052  if (strtoupper($Words[$WordIndex]) == "FROM")
1053  {
1054  # next word is table name
1055  $WordIndex++;
1056  $TableName = $Words[$WordIndex];
1057  }
1058  }
1059 
1060  # discard table name if it looks at all suspicious
1061  if ($TableName)
1062  {
1063  if (!preg_match("/[a-zA-Z0-9]+/", $TableName))
1064  {
1065  $TableName = FALSE;
1066  }
1067  }
1068 
1069  # return table name (or lack thereof) to caller
1070  return $TableName;
1071  }
1072 
1079  private function TablesAccessed($QueryString)
1080  {
1081  # assume we're not going to be able to determine tables
1082  $TableNames = FALSE;
1083 
1084  # split query into pieces
1085  $QueryString = trim($QueryString);
1086  $Words = preg_split("/\s+/", $QueryString);
1087  $UQueryString = strtoupper($QueryString);
1088  $UWords = preg_split("/\s+/", $UQueryString);
1089 
1090  # if SELECT statement
1091  if ($UWords[0] == "SELECT")
1092  {
1093  # keep going until we hit FROM or last word
1094  $WordIndex = 1;
1095  while (($UWords[$WordIndex] != "FROM")
1096  && strlen($UWords[$WordIndex]))
1097  {
1098  $WordIndex++;
1099  }
1100 
1101  # if we hit FROM
1102  if ($UWords[$WordIndex] == "FROM")
1103  {
1104  # for each word after FROM
1105  $WordIndex++;
1106  while (strlen($UWords[$WordIndex]))
1107  {
1108  # if current word ends with comma
1109  if (preg_match("/,$/", $Words[$WordIndex]))
1110  {
1111  # strip off comma and add word to table name list
1112  $TableNames[] = substr($Words[$WordIndex], 0, -1);
1113  }
1114  else
1115  {
1116  # add word to table name list
1117  $TableNames[] = $Words[$WordIndex];
1118 
1119  # if next word is not comma
1120  $WordIndex++;
1121  if ($Words[$WordIndex] != ",")
1122  {
1123  # if word begins with comma
1124  if (preg_match("/^,/", $Words[$WordIndex]))
1125  {
1126  # strip off comma (NOTE: modifies $Words array!)
1127  $Words[$WordIndex] = substr($Words[$WordIndex], 1);
1128 
1129  # decrement index so we start with this word next pass
1130  $WordIndex--;
1131  }
1132  else
1133  {
1134  # stop scanning words (non-basic JOINs not yet handled)
1135  break;
1136  }
1137  }
1138  }
1139 
1140  # move to next word
1141  $WordIndex++;
1142  }
1143  }
1144  }
1145 
1146  # discard table names if they look at all suspicious
1147  if ($TableNames)
1148  {
1149  foreach ($TableNames as $Name)
1150  {
1151  if (!preg_match("/^[a-zA-Z0-9]+$/", $Name))
1152  {
1153  $TableNames = FALSE;
1154  break;
1155  }
1156  }
1157  }
1158 
1159  # return table name (or lack thereof) to caller
1160  return $TableNames;
1161  }
1162 
1169  private function RunQuery($QueryString)
1170  {
1171  # log query start time if debugging output is enabled
1172  if (self::$QueryDebugOutputFlag) { $QueryStartTime = microtime(TRUE); }
1173 
1174  # run query against database
1175  $this->QueryHandle = mysqli_query($this->Handle, $QueryString) ;
1176 
1177  # print query and execution time if debugging output is enabled
1178  if (self::$QueryDebugOutputFlag)
1179  {
1180  print "DB: ".$QueryString." ["
1181  .sprintf("%.2f", microtime(TRUE) - $QueryStartTime)
1182  ."s]"."<br>\n";
1183  }
1184 
1185  # if query failed and there are errors that we can ignore
1186  if (($this->QueryHandle === FALSE) && $this->ErrorsToIgnore)
1187  {
1188  # for each pattern for an error that we can ignore
1189  foreach ($this->ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
1190  {
1191  # if error matches pattern
1192  $ErrorMsg = mysqli_error($this->Handle);
1193  if (preg_match($SqlPattern, $QueryString)
1194  && preg_match($ErrMsgPattern, $ErrorMsg))
1195  {
1196  # set return value to indicate error was ignored
1197  $this->QueryHandle = TRUE;
1198 
1199  # set internal flag to indicate that an error was ignored
1200  $this->ErrorIgnored = $ErrorMsg;
1201 
1202  # stop looking at patterns
1203  break;
1204  }
1205  }
1206  }
1207 
1208  # if query failed
1209  if ($this->QueryHandle === FALSE)
1210  {
1211  # clear stored value for number of rows retrieved
1212  $this->NumRows = 0;
1213 
1214  # retrieve error info
1215  $this->ErrMsg = mysqli_error($this->Handle);
1216  $this->ErrNo = mysqli_errno($this->Handle);
1217 
1218  # if we are supposed to be displaying errors
1219  if (self::$DisplayErrors)
1220  {
1221  # print error info
1222  print("<b>SQL Error:</b> <i>".$this->ErrMsg
1223  ."</i> (".$this->ErrNo.")<br/>\n");
1224  print("<b>SQL Statement:</b> <i>"
1225  .htmlspecialchars($QueryString)."</i><br/>\n");
1226 
1227  # retrieve execution trace that got us to this point
1228  $Trace = debug_backtrace();
1229 
1230  # remove current context from trace
1231  array_shift($Trace);
1232 
1233  # make sure file name and line number are available
1234  foreach ($Trace as $Index => $Loc)
1235  {
1236  if (!array_key_exists("file", $Loc))
1237  {
1238  $Trace[$Index]["file"] = "UNKNOWN";
1239  }
1240  if (!array_key_exists("line", $Loc))
1241  {
1242  $Trace[$Index]["line"] = "??";
1243  }
1244  }
1245 
1246  # determine length of leading path common to all file names in trace
1247  $LocString = "";
1248  $OurFile = __FILE__;
1249  $PrefixLen = 9999;
1250  foreach ($Trace as $Loc)
1251  {
1252  if ($Loc["file"] != "UNKNOWN")
1253  {
1254  $Index = 0;
1255  $FNameLength = strlen($Loc["file"]);
1256  while ($Index < $FNameLength &&
1257  $Loc["file"][$Index] == $OurFile[$Index])
1258  { $Index++; }
1259  $PrefixLen = min($PrefixLen, $Index);
1260  }
1261  }
1262 
1263  foreach ($Trace as $Loc)
1264  {
1265  $Sep = "";
1266  $ArgString = "";
1267  foreach ($Loc["args"] as $Arg)
1268  {
1269  $ArgString .= $Sep;
1270  switch (gettype($Arg))
1271  {
1272  case "boolean":
1273  $ArgString .= $Arg ? "TRUE" : "FALSE";
1274  break;
1275 
1276  case "integer":
1277  case "double":
1278  $ArgString .= $Arg;
1279  break;
1280 
1281  case "string":
1282  $ArgString .= '"<i>'.htmlspecialchars(substr($Arg, 0, 40))
1283  .((strlen($Arg) > 40) ? "..." : "").'</i>"';
1284  break;
1285 
1286  case "array":
1287  case "resource":
1288  case "NULL":
1289  $ArgString .= strtoupper(gettype($Arg));
1290  break;
1291 
1292  case "object":
1293  $ArgString .= get_class($Arg);
1294  break;
1295 
1296  case "unknown type":
1297  $ArgString .= "UNKNOWN";
1298  break;
1299  }
1300  $Sep = ",";
1301  }
1302  $Loc["file"] = substr($Loc["file"], $PrefixLen);
1303  $LocString .= "&nbsp;&nbsp;";
1304  if (array_key_exists("class", $Loc))
1305  { $LocString .= $Loc["class"]."::"; }
1306  $LocString .= $Loc["function"]."(".$ArgString.")"
1307  ." - ".$Loc["file"].":".$Loc["line"]
1308  ."<br>\n";
1309  }
1310  print("<b>Trace:</b><br>\n".$LocString);
1311  }
1312  }
1313  return $this->QueryHandle;
1314  }
1315 }
1316 
1317 # define return values (numerical values correspond to MySQL error codes)
1318 // @codingStandardsIgnoreStart (to silence warning about multiple spaces)
1319 define("DB_OKAY", 0);
1320 define("DB_ERROR", 1);
1321 define("DB_ACCESSDENIED", 2);
1322 define("DB_UNKNOWNDB", 3);
1323 define("DB_UNKNOWNTABLE", 4);
1324 define("DB_SYNTAXERROR", 5);
1325 define("DB_DBALREADYEXISTS", 6);
1326 define("DB_DBDOESNOTEXIST", 7);
1327 define("DB_DISKFULL", 8);
1328 // @codingStandardsIgnoreEnd
1329 
1330 # define value to designate omitted arguments (so DB values can be set to NULL)
1331 define("DB_NOVALUE", "!-_-_-DB_NOVALUE-_-_-!");
1332 
1333 # MySQL error code mapping
1335  1045 => DB_ACCESSDENIED,
1336  1049 => DB_UNKNOWNDB,
1337  1046 => DB_UNKNOWNTABLE,
1338  1064 => DB_SYNTAXERROR,
1339  1007 => DB_DBALREADYEXISTS, # ? (not sure)
1340  1008 => DB_DBDOESNOTEXIST, # ? (not sure)
1341  1021 => DB_DISKFULL, # ? (not sure)
1342  );
1343 
1344 # date() format for SQL dates
1345 define("DATE_SQL", "Y-m-d H:i:s");
QueryErrMsg()
Get most recent error message text set by Query().
static Caching($NewSetting=NULL)
Get or set whether query result caching is currently enabled.
const DB_UNKNOWNDB
GetServerVersion($FullVersion=FALSE)
Get database server version number.
static SetGlobalDatabaseName($DatabaseName)
Set default database name.
SetDefaultStorageEngine($Engine)
Set default database storage engine.
ExecuteQueriesFromFile($FileName)
Execute queries from specified file.
UpdateIntValue($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
A convenience function to get or set an integer value in the database.
const DB_SYNTAXERROR
SQL database abstraction object with smart query caching.
SetQueryErrorsToIgnore($ErrorsToIgnore, $NormalizeWhitespace=TRUE)
Set query errors to ignore.
const DB_NOVALUE
DBUserName()
Get name used to connect with database server.
Database($UserName=NULL, $Password=NULL, $DatabaseName=NULL, $HostName=NULL)
Object constructor.
EscapeString($String)
Escape a string that may contain null bytes.
FetchRow()
Get next database row retrieved by most recent query.
LastInsertId()
Get ID of row added by the last SQL "INSERT" statement.
TableExists($TableName)
Get whether specified table exists.
static SetGlobalServerInfo($UserName, $Password, $HostName="localhost")
const DB_DBALREADYEXISTS
GetClientVersion()
Get version number of the client libraries being used to connect to the database server (Currently th...
GetFieldType($TableName, $FieldName)
Get field (column) type.
NumRowsSelected()
Get number of rows returned by last SELECT or SHOW query.
FetchRows($NumberOfRows=NULL)
Get specified number of database rows retrieved by most recent query.
static QueryDebugOutput($NewSetting)
Enable or disable debugging output for queries.
Query($QueryString, $FieldName="")
Query database (with caching if enabled).
FetchField($FieldName)
Pull next row from last DB query and get a specific value from that row.
FieldExists($TableName, $FieldName)
Get whether specified field exists in specified table.
static NumCacheHits()
Get the number of queries that have resulted in cache hits since program execution began...
FetchColumn($FieldName, $IndexFieldName=NULL)
Get all available values for specified database field retrieved by most recent query.
DBHostName()
Get host name of system on which database server resides.
NumRowsAffected()
Get number of rows affected by last INSERT, UPDATE, REPLACE, or DELETE query.
const DB_ACCESSDENIED
UpdateFloatValue($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
A convenience function to get or set a float value in the database.
QueryErrNo()
Get most recent error code set by Query().
$APDBErrorCodeMappings
GetHostInfo()
Get database connection type and hostname.
UpdateValue($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
A convenience function to get or set a value in the database.
static AdvancedCaching($NewSetting=NULL)
Get or set whether advanced query result cachine is currently enabled.
static DisplayQueryErrors($NewValue=NULL)
Get/set whether Query() errors will be displayed.
static CacheHitRate()
Get the ratio of query cache hits to queries as a percentage.
IgnoredError()
Check whether an error was ignored by the most recent query.
DBName()
Get current database name.
LogComment($String)
Peform query that consists of SQL comment statement.
__wakeup()
Restore database connection when unserialized.
static NumQueries()
Get the number of queries that have been run since program execution began.
const DB_DBDOESNOTEXIST
const DB_UNKNOWNTABLE
const DB_DISKFULL