CWIS Developer Documentation

- Home
- Main Page
- Namespaces
- Classes
- Files

- File List
- File Members

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(
1110 $DBHostName, $DBUserName, $DBPassword, $DBName)
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
1544 $APDBErrorCodeMappings = array(
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");
Database\QueryErrMsg
QueryErrMsg()
Get most recent error message text set by Query().
Definition: Database.php:568
Database\$DBName
$DBName
Definition: Database.php:1043
Database\Caching
static Caching($NewSetting=NULL)
Get or set whether query result caching is currently enabled.
Definition: Database.php:237
Database\__sleep
__sleep()
Definition: Database.php:81
Database\GetServerVersion
GetServerVersion($FullVersion=FALSE)
Get database server version number.
Definition: Database.php:161
Database\SetGlobalDatabaseName
static SetGlobalDatabaseName($DatabaseName)
Set default database name.
Definition: Database.php:132
Database\CR_CONNECTION_ERROR
const CR_CONNECTION_ERROR
Definition: Database.php:1088
DB_ACCESSDENIED
const DB_ACCESSDENIED
Definition: Database.php:1531
Database\SetDefaultStorageEngine
SetDefaultStorageEngine($Engine)
Set default database storage engine.
Definition: Database.php:145
Database\ExecuteQueriesFromFile
ExecuteQueriesFromFile($FileName)
Execute queries from specified file.
Definition: Database.php:509
Database\$DBPassword
$DBPassword
Definition: Database.php:1041
Database\$DBUserName
$DBUserName
Definition: Database.php:1040
Database\UpdateIntValue
UpdateIntValue($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
A convenience function to get or set an integer value in the database.
Definition: Database.php:828
Database
SQL database abstraction object with smart query caching.
Definition: Database.php:22
Database\SetQueryErrorsToIgnore
SetQueryErrorsToIgnore($ErrorsToIgnore, $NormalizeWhitespace=TRUE)
Set query errors to ignore.
Definition: Database.php:291
Database\CR_SERVER_GONE_ERROR
const CR_SERVER_GONE_ERROR
Definition: Database.php:1091
Database\DBUserName
DBUserName()
Get name used to connect with database server.
Definition: Database.php:225
Database\EscapeString
EscapeString($String)
Escape a string that may contain null bytes.
Definition: Database.php:919
Database\FetchRow
FetchRow()
Get next database row retrieved by most recent query.
Definition: Database.php:642
Database\LastInsertId
LastInsertId()
Get ID of row added by the last SQL "INSERT" statement.
Definition: Database.php:760
Database\CR_SERVER_LOST
const CR_SERVER_LOST
Definition: Database.php:1092
DB_SYNTAXERROR
const DB_SYNTAXERROR
Definition: Database.php:1534
Database\__construct
__construct($UserName=NULL, $Password=NULL, $DatabaseName=NULL, $HostName=NULL)
Object constructor.
Definition: Database.php:43
Database\TableExists
TableExists($TableName)
Get whether specified table exists.
Definition: Database.php:940
DB_DISKFULL
const DB_DISKFULL
Definition: Database.php:1537
Database\SetGlobalServerInfo
static SetGlobalServerInfo($UserName, $Password, $HostName="localhost")
Set default login and host info for database server.
Definition: Database.php:116
$APDBErrorCodeMappings
$APDBErrorCodeMappings
Definition: Database.php:1544
Database\$DBHostName
$DBHostName
Definition: Database.php:1042
PHP
PHP
Definition: RSSClient--Test.php:12
Database\GetClientVersion
GetClientVersion()
Get version number of the client libraries being used to connect to the database server (Currently th...
Definition: Database.php:185
Database\GetFieldType
GetFieldType($TableName, $FieldName)
Get field (column) type.
Definition: Database.php:968
DB_NOVALUE
const DB_NOVALUE
Definition: Database.php:1541
Database\NumRowsSelected
NumRowsSelected()
Get number of rows returned by last SELECT or SHOW query.
Definition: Database.php:599
Database\FetchRows
FetchRows($NumberOfRows=NULL)
Get specified number of database rows retrieved by most recent query.
Definition: Database.php:688
Database\QueryDebugOutput
static QueryDebugOutput($NewSetting)
Enable or disable debugging output for queries.
Definition: Database.php:993
Database\Query
Query($QueryString, $FieldName="")
Query database (with caching if enabled).
Definition: Database.php:329
Database\GetColumns
GetColumns($TableName)
Get column (database field) names.
Definition: Database.php:980
Database\FetchField
FetchField($FieldName)
Pull next row from last DB query and get a specific value from that row.
Definition: Database.php:748
Database\FieldExists
FieldExists($TableName, $FieldName)
Get whether specified field exists in specified table.
Definition: Database.php:952
Database\NumCacheHits
static NumCacheHits()
Get the number of queries that have resulted in cache hits since program execution began...
Definition: Database.php:1014
DB_UNKNOWNTABLE
const DB_UNKNOWNTABLE
Definition: Database.php:1533
Database\FetchColumn
FetchColumn($FieldName, $IndexFieldName=NULL)
Get all available values for specified database field retrieved by most recent query.
Definition: Database.php:723
Database\DBHostName
DBHostName()
Get host name of system on which database server resides.
Definition: Database.php:205
DB_DBDOESNOTEXIST
const DB_DBDOESNOTEXIST
Definition: Database.php:1536
Database\NumRowsAffected
NumRowsAffected()
Get number of rows affected by last INSERT, UPDATE, REPLACE, or DELETE query.
Definition: Database.php:625
Database\UpdateFloatValue
UpdateFloatValue($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
A convenience function to get or set a float value in the database.
Definition: Database.php:852
DB_DBALREADYEXISTS
const DB_DBALREADYEXISTS
Definition: Database.php:1535
Database\QueryErrNo
QueryErrNo()
Get most recent error code set by Query().
Definition: Database.php:578
DB_UNKNOWNDB
const DB_UNKNOWNDB
Definition: Database.php:1532
Database\GetHostInfo
GetHostInfo()
Get database connection type and hostname.
Definition: Database.php:195
Database\UpdateValue
UpdateValue($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
A convenience function to get or set a value in the database.
Definition: Database.php:781
Database\AdvancedCaching
static AdvancedCaching($NewSetting=NULL)
Get or set whether advanced query result cachine is currently enabled.
Definition: Database.php:263
Database\DisplayQueryErrors
static DisplayQueryErrors($NewValue=NULL)
Get/set whether Query() errors will be displayed.
Definition: Database.php:589
Database\CacheHitRate
static CacheHitRate()
Get the ratio of query cache hits to queries as a percentage.
Definition: Database.php:1024
Database\IgnoredError
IgnoredError()
Check whether an error was ignored by the most recent query.
Definition: Database.php:311
Database\DBName
DBName()
Get current database name.
Definition: Database.php:215
Database\CopyValues
CopyValues($TableName, $IdColumn, $SrcId, $DstId, $ColumnsToExclude=array())
A convenience function to copy values from one row to another.
Definition: Database.php:871
Database\LogComment
LogComment($String)
Peform query that consists of SQL comment statement.
Definition: Database.php:930
Database\__wakeup
__wakeup()
Restore database connection when unserialized.
Definition: Database.php:90
Database\NumQueries
static NumQueries()
Get the number of queries that have been run since program execution began.
Definition: Database.php:1003