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
41 public function __construct(
42 $UserName = NULL, $Password = NULL, $DatabaseName = NULL, $HostName = NULL)
43 {
44 # save DB access parameter values
45 $this->DBUserName = $UserName ? $UserName : self::$GlobalDBUserName;
46 $this->DBPassword = $Password ? $Password : self::$GlobalDBPassword;
47 $this->DBHostName = $HostName ? $HostName :
48 (isset(self::$GlobalDBHostName) ? self::$GlobalDBHostName
49 : "localhost");
50 $this->DBName = $DatabaseName ? $DatabaseName : self::$GlobalDBName;
51
52 # set memory threshold for cache clearing
53 if (!isset(self::$CacheMemoryThreshold))
54 {
55 self::$CacheMemoryThreshold = self::GetPhpMemoryLimit() / 4;
56 }
57
58 # if we don't already have a connection or DB access parameters were supplied
59 $HandleIndex = $this->DBHostName.":".$this->DBName;
60 if (!array_key_exists($HandleIndex, self::$ConnectionHandles)
61 || $UserName || $Password || $DatabaseName || $HostName)
62 {
63 # open connection to DB server
64 self::$ConnectionHandles[$HandleIndex] = mysqli_connect(
65 $this->DBHostName, $this->DBUserName,
66 $this->DBPassword)
67 or die("Could not connect to database: ".mysqli_connect_error());
68
69 # set local connection handle
70 $this->Handle = self::$ConnectionHandles[$HandleIndex];
71
72 # select DB
73 mysqli_select_db($this->Handle, $this->DBName)
74 or die(mysqli_error($this->Handle));
75 }
76 else
77 {
78 # set local connection handle
79 $this->Handle = self::$ConnectionHandles[$HandleIndex];
80 }
81 }
82
87 public function __sleep()
88 {
89 return array("DBUserName", "DBPassword", "DBHostName", "DBName");
90 }
94 public function __wakeup()
95 {
96 # open connection to DB server
97 $this->Handle = mysqli_connect(
98 $this->DBHostName, $this->DBUserName, $this->DBPassword)
99 or die("could not connect to database");
100
101 # select DB
102 mysqli_select_db($this->Handle, $this->DBName)
103 or die(mysqli_error($this->Handle));
104 }
114 public static function SetGlobalServerInfo(
115 $UserName, $Password, $HostName = "localhost")
116 {
117 # save default DB access parameters
118 self::$GlobalDBUserName = $UserName;
119 self::$GlobalDBPassword = $Password;
120 self::$GlobalDBHostName = $HostName;
121
122 # clear any existing DB connection handles
123 self::$ConnectionHandles = array();
124 }
125
130 public static function SetGlobalDatabaseName($DatabaseName)
131 {
132 # save new default DB name
133 self::$GlobalDBName = $DatabaseName;
134
135 # clear any existing DB connection handles
136 self::$ConnectionHandles = array();
137 }
138
143 public function SetDefaultStorageEngine($Engine)
144 {
145 # choose config variable to use based on server version number
146 $ConfigVar = version_compare($this->GetServerVersion(), "5.5", "<")
147 ? "storage_engine" : "default_storage_engine";
148
149 # set storage engine in database
150 $this->Query("SET ".$ConfigVar." = ".$Engine);
151 }
152
159 public function GetServerVersion($FullVersion=FALSE)
160 {
161 # retrieve version string
162 $Version = $this->Query("SELECT VERSION() AS ServerVer", "ServerVer");
163
164 if (!$FullVersion)
165 {
166 # strip off any build/config suffix
167 $Pieces = explode("-", $Version);
168 $Version = array_shift($Pieces);
169 }
170
171 # return version number to caller
172 return $Version;
173 }
174
183 public function GetClientVersion()
184 {
185 return mysqli_get_client_info();
186 }
187
193 public function GetHostInfo()
194 {
195 return mysqli_get_host_info($this->Handle);
196 }
197
203 public function DBHostName()
204 {
205 return $this->DBHostName;
206 }
207
213 public function DBName()
214 {
215 return $this->DBName;
216 }
217
223 public function DBUserName()
224 {
225 return $this->DBUserName;
226 }
227
235 public static function Caching($NewSetting = NULL)
236 {
237 # if cache setting has changed
238 if (($NewSetting !== NULL) && ($NewSetting != self::$CachingFlag))
239 {
240 # save new setting
241 self::$CachingFlag = $NewSetting;
242
243 # clear any existing cached results
244 self::$QueryResultCache = array();
245 }
246
247 # return current setting to caller
248 return self::$CachingFlag;
249 }
250
261 public static function AdvancedCaching($NewSetting = NULL)
262 {
263 if ($NewSetting !== NULL)
264 {
265 self::$AdvancedCachingFlag = $NewSetting;
266 }
267 return self::$AdvancedCachingFlag;
268 }
269
289 public function SetQueryErrorsToIgnore($ErrorsToIgnore, $NormalizeWhitespace = TRUE)
290 {
291 if ($NormalizeWhitespace && ($ErrorsToIgnore !== NULL))
292 {
293 $RevisedErrorsToIgnore = array();
294 foreach ($ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
295 {
296 $SqlPattern = preg_replace("/\\s+/", "\\s+", $SqlPattern);
297 $RevisedErrorsToIgnore[$SqlPattern] = $ErrMsgPattern;
298 }
299 $ErrorsToIgnore = $RevisedErrorsToIgnore;
300 }
301 $this->ErrorsToIgnore = $ErrorsToIgnore;
302 }
303
309 public function IgnoredError()
310 {
311 return $this->ErrorIgnored;
312 }
313
314 /*@)*/ /* Setup/Initialization */ /*@(*/
316
327 public function Query($QueryString, $FieldName = "")
328 {
329 # clear flag that indicates whether query error was ignored
330 $this->ErrorIgnored = FALSE;
331
332 # if caching is enabled
333 if (self::$CachingFlag)
334 {
335 # if SQL statement is read-only
336 if ($this->IsReadOnlyStatement($QueryString))
337 {
338 # if we have statement in cache
339 if (isset(self::$QueryResultCache[$QueryString]["NumRows"]))
340 {
341 if (self::$QueryDebugOutputFlag)
342 { print("DB-C: $QueryString<br>\n"); }
343
344 # make sure query result looks okay
345 $this->QueryHandle = TRUE;
346
347 # increment cache hit counter
348 self::$CachedQueryCounter++;
349
350 # make local copy of results
351 $this->QueryResults = self::$QueryResultCache[$QueryString];
352 $this->NumRows = self::$QueryResultCache[$QueryString]["NumRows"];
353
354 # set flag to indicate that results should be retrieved from cache
355 $this->GetResultsFromCache = TRUE;
356 }
357 else
358 {
359 # execute SQL statement
360 $this->QueryHandle = $this->RunQuery($QueryString);
361 if (!$this->QueryHandle instanceof mysqli_result) { return FALSE; }
362
363 # save number of rows in result
364 $this->NumRows = mysqli_num_rows($this->QueryHandle);
365
366 # if too many rows to cache
367 if ($this->NumRows >= self::$CacheRowsThreshold)
368 {
369 # set flag to indicate that query results should not
370 # be retrieved from cache
371 $this->GetResultsFromCache = FALSE;
372 }
373 else
374 {
375 # if we are low on memory
376 if (self::GetFreeMemory() < self::$CacheMemoryThreshold)
377 {
378 # clear out all but last few rows from cache
379 self::$QueryResultCache = array_slice(
380 self::$QueryResultCache,
381 (0 - self::$CacheRowsToLeave));
382 }
383
384 # if advanced caching is enabled
385 if (self::$AdvancedCachingFlag)
386 {
387 # save tables accessed by query
388 self::$QueryResultCache[$QueryString]["TablesAccessed"] =
389 $this->TablesAccessed($QueryString);
390 }
391
392 # if rows found
393 if ($this->NumRows > 0)
394 {
395 # load query results
396 for ($Row = 0; $Row < $this->NumRows; $Row++)
397 {
398 $this->QueryResults[$Row] =
399 mysqli_fetch_assoc($this->QueryHandle);
400 }
401
402 # cache query results
403 self::$QueryResultCache[$QueryString] = $this->QueryResults;
404 }
405 else
406 {
407 # clear local query results
408 unset($this->QueryResults);
409 }
410
411 # cache number of rows
412 self::$QueryResultCache[$QueryString]["NumRows"] = $this->NumRows;
413
414 # set flag to indicate that query results should be
415 # retrieved from cache
416 $this->GetResultsFromCache = TRUE;
417 }
418 }
419 }
420 else
421 {
422 # if advanced caching is enabled
423 if (self::$AdvancedCachingFlag)
424 {
425 # if table modified by statement is known
426 $TableModified = $this->TableModified($QueryString);
427 if ($TableModified)
428 {
429 # for each cached query
430 foreach (self::$QueryResultCache
431 as $CachedQueryString => $CachedQueryResult)
432 {
433 # if we know what tables were accessed
434 if ($CachedQueryResult["TablesAccessed"])
435 {
436 # if tables accessed include the one we may modify
437 if (in_array($TableModified,
438 $CachedQueryResult["TablesAccessed"]))
439 {
440 # clear cached query results
441 unset($GLOBALS["APDBQueryResultCache"][$CachedQueryString]);
442 }
443 }
444 else
445 {
446 # clear cached query results
447 unset($GLOBALS["APDBQueryResultCache"][$CachedQueryString]);
448 }
449 }
450 }
451 else
452 {
453 # clear entire query result cache
454 self::$QueryResultCache = array();
455 }
456 }
457 else
458 {
459 # clear entire query result cache
460 self::$QueryResultCache = array();
461 }
462
463 # execute SQL statement
464 $this->QueryHandle = $this->RunQuery($QueryString);
465 if ($this->QueryHandle === FALSE) { return FALSE; }
466
467 # set flag to indicate that query results should not be
468 # retrieved from cache
469 $this->GetResultsFromCache = FALSE;
470 }
471
472 # reset row counter
473 $this->RowCounter = 0;
474
475 # increment query counter
476 self::$QueryCounter++;
477 }
478 else
479 {
480 # execute SQL statement
481 $this->QueryHandle = $this->RunQuery($QueryString);
482 if ($this->QueryHandle === FALSE) { return FALSE; }
483 }
484
485 if (($FieldName != "") && ($this->QueryHandle != FALSE))
486 {
487 return $this->FetchField($FieldName);
488 }
489 else
490 {
491 return $this->QueryHandle;
492 }
493 }
494
507 public function ExecuteQueriesFromFile($FileName)
508 {
509 # open file
510 $FHandle = fopen($FileName, "r");
511
512 # if file open succeeded
513 if ($FHandle !== FALSE)
514 {
515 # while lines left in file
516 $Query = "";
517 $QueryCount = 0;
518 while (!feof($FHandle))
519 {
520 # read in line from file
521 $Line = fgets($FHandle, 32767);
522
523 # trim whitespace from line
524 $Line = trim($Line);
525
526 # if line is not empty and not a comment
527 if (!preg_match("/^#/", $Line)
528 && !preg_match("/^--/", $Line)
529 && strlen($Line))
530 {
531 # add line to current query
532 $Query .= " ".$Line;
533
534 # if line completes a query
535 if (preg_match("/;$/", $Line))
536 {
537 # run query
538 $QueryCount++;
539 $Result = $this->Query($Query);
540 $Query = "";
541
542 # if query resulted in an error that is not ignorable
543 if ($Result === FALSE)
544 {
545 # stop processing queries and set error code
546 $QueryCount = NULL;
547 break;
548 }
549 }
550 }
551 }
552
553 # close file
554 fclose($FHandle);
555 }
556
557 # return number of executed queries to caller
558 return $QueryCount;
559 }
560
566 public function QueryErrMsg()
567 {
568 return $this->ErrMsg;
569 }
570
576 public function QueryErrNo()
577 {
578 return $this->ErrNo;
579 }
580
587 public static function DisplayQueryErrors($NewValue = NULL)
588 {
589 if ($NewValue !== NULL) { self::$DisplayErrors = $NewValue; }
590 return self::$DisplayErrors;
591 }
592
597 public function NumRowsSelected()
598 {
599 # if caching is enabled and query was cached
600 if (self::$CachingFlag && $this->GetResultsFromCache)
601 {
602 # return cached number of rows to caller
603 return $this->NumRows;
604 }
605 else
606 {
607 # call to this method after an unsuccessful query
608 if (!$this->QueryHandle instanceof mysqli_result)
609 {
610 return 0;
611 }
612
613 # retrieve number of rows and return to caller
614 return mysqli_num_rows($this->QueryHandle);
615 }
616 }
617
623 public function NumRowsAffected()
624 {
625 # call to this method after an unsuccessful query
626 if (!$this->QueryHandle instanceof mysqli_result)
627 {
628 return 0;
629 }
630
631 # retrieve number of rows and return to caller
632 return mysqli_affected_rows($this->Handle);
633 }
634
640 public function FetchRow()
641 {
642 # if caching is enabled and query was cached
643 if (self::$CachingFlag && $this->GetResultsFromCache)
644 {
645 # if rows left to return
646 if ($this->RowCounter < $this->NumRows)
647 {
648 # retrieve row from cache
649 $Result = $this->QueryResults[$this->RowCounter];
650
651 # increment row counter
652 $this->RowCounter++;
653 }
654 else
655 {
656 # return nothing
657 $Result = FALSE;
658 }
659 }
660 else
661 {
662 # call to this method after successful query
663 if ($this->QueryHandle instanceof mysqli_result)
664 {
665 $Result = mysqli_fetch_assoc($this->QueryHandle);
666 if ($Result === NULL) { $Result = FALSE; }
667 }
668
669 # call to this method after unsuccessful query
670 else
671 {
672 $Result = FALSE;
673 }
674 }
675
676 # return row to caller
677 return $Result;
678 }
679
686 public function FetchRows($NumberOfRows = NULL)
687 {
688 # assume no rows will be returned
689 $Result = array();
690
691 # for each available row
692 $RowsFetched = 0;
693 while ((($RowsFetched < $NumberOfRows) || ($NumberOfRows == NULL))
694 && ($Row = $this->FetchRow()))
695 {
696 # add row to results
697 $Result[] = $Row;
698 $RowsFetched++;
699 }
700
701 # return array of rows to caller
702 return $Result;
703 }
704
721 public function FetchColumn($FieldName, $IndexFieldName = NULL)
722 {
723 $Array = array();
724 while ($Record = $this->FetchRow())
725 {
726 if ($IndexFieldName != NULL)
727 {
728 $Array[$Record[$IndexFieldName]] = $Record[$FieldName];
729 }
730 else
731 {
732 $Array[] = $Record[$FieldName];
733 }
734 }
735 return $Array;
736 }
737
746 public function FetchField($FieldName)
747 {
748 $Record = $this->FetchRow();
749 return isset($Record[$FieldName]) ? $Record[$FieldName] : NULL;
750 }
751
758 public function LastInsertId()
759 {
760 return (int)$this->Query(
761 "SELECT LAST_INSERT_ID() AS InsertId",
762 "InsertId");
763 }
764
779 public function UpdateValue(
780 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
781 {
782 # expand condition if supplied
783 if ($Condition != NULL) { $Condition = " WHERE ".$Condition; }
784
785 # read cached record from database if not already loaded
786 if (!isset($CachedRecord))
787 {
788 $this->Query("SELECT * FROM `".$TableName."` ".$Condition);
789 $CachedRecord = $this->FetchRow();
790 }
791
792 # if new value supplied
793 if ($NewValue !== DB_NOVALUE)
794 {
795 # update value in database
796 $this->Query("UPDATE `".$TableName."` SET `".$FieldName."` = "
797 .(($NewValue === NULL) ? "NULL" : "'"
798 .mysqli_real_escape_string($this->Handle, $NewValue)."'")
799 .$Condition);
800
801 # update value in cached record
802 $CachedRecord[$FieldName] = $NewValue;
803 }
804
805 # return value from cached record to caller
806 return isset($CachedRecord[$FieldName])
807 ? $CachedRecord[$FieldName] : NULL;
808 }
809
826 public function UpdateIntValue(
827 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
828 {
829 return $this->UpdateValue($TableName, $FieldName,
830 (($NewValue === DB_NOVALUE) ? DB_NOVALUE : (int)$NewValue),
831 $Condition, $CachedRecord);
832 }
833
850 public function UpdateFloatValue(
851 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
852 {
853 return $this->UpdateValue($TableName, $FieldName,
854 (($NewValue === DB_NOVALUE) ? DB_NOVALUE : (float)$NewValue),
855 $Condition, $CachedRecord);
856 }
857
858 /*@)*/ /* Data Manipulation */ /*@(*/
860
869 public function EscapeString($String)
870 {
871 return mysqli_real_escape_string($this->Handle, $String);
872 }
873
880 public function LogComment($String)
881 {
882 $this->Query("-- ".$String);
883 }
884
890 public function TableExists($TableName)
891 {
892 $this->Query("SHOW TABLES LIKE '".addslashes($TableName)."'");
893 return $this->NumRowsSelected() ? TRUE : FALSE;
894 }
895
902 public function FieldExists($TableName, $FieldName)
903 {
904 $this->Query("DESC ".$TableName);
905 while ($CurrentFieldName = $this->FetchField("Field"))
906 {
907 if ($CurrentFieldName == $FieldName) { return TRUE; }
908 }
909 return FALSE;
910 }
911
918 public function GetFieldType($TableName, $FieldName)
919 {
920 $this->Query("DESC ".$TableName);
921 $AllTypes = $this->FetchColumn("Type", "Field");
922 return $AllTypes[$FieldName];
923 }
924
930 public static function QueryDebugOutput($NewSetting)
931 {
932 self::$QueryDebugOutputFlag = $NewSetting;
933 }
934
940 public static function NumQueries()
941 {
942 return self::$QueryCounter;
943 }
944
951 public static function NumCacheHits()
952 {
953 return self::$CachedQueryCounter;
954 }
955
961 public static function CacheHitRate()
962 {
963 if (self::$QueryCounter)
964 {
965 return (self::$CachedQueryCounter / self::$QueryCounter) * 100;
966 }
967 else
968 {
969 return 0;
970 }
971 }
972
973 /*@)*/ /* Miscellaneous */
974
975 # ---- PRIVATE INTERFACE -------------------------------------------------
976
977 protected $DBUserName;
978 protected $DBPassword;
979 protected $DBHostName;
980 protected $DBName;
981
982 private $Handle;
983 private $QueryHandle;
984 private $QueryResults;
985 private $RowCounter;
986 private $NumRows;
987 private $GetResultsFromCache;
988 private $ErrorIgnored = FALSE;
989 private $ErrorsToIgnore = NULL;
990 private $ErrMsg = NULL;
991 private $ErrNo = NULL;
992
993 private static $DisplayErrors = FALSE;
994
995 private static $GlobalDBUserName;
996 private static $GlobalDBPassword;
997 private static $GlobalDBHostName;
998 private static $GlobalDBName;
999
1000 # debug output flag
1001 private static $QueryDebugOutputFlag = FALSE;
1002 # flag for whether caching is turned on
1003 private static $CachingFlag = TRUE;
1004 # query result advanced caching flag
1005 private static $AdvancedCachingFlag = FALSE;
1006 # global cache for query results
1007 private static $QueryResultCache = array();
1008 # stats counters
1009 private static $QueryCounter = 0;
1010 private static $CachedQueryCounter = 0;
1011 # database connection link handles
1012 private static $ConnectionHandles = array();
1013 # do not cache queries that return more than this number of rows
1014 private static $CacheRowsThreshold = 250;
1015 # prune the query cache if there is less than this amount of memory free
1016 private static $CacheMemoryThreshold;
1017 # number of rows to leave in cache when pruning
1018 private static $CacheRowsToLeave = 10;
1019
1025 private function IsReadOnlyStatement($QueryString)
1026 {
1027 return preg_match("/^[ ]*SELECT /i", $QueryString) ? TRUE : FALSE;
1028 }
1029
1036 private function TableModified($QueryString)
1037 {
1038 # assume we're not going to be able to determine table
1039 $TableName = FALSE;
1040
1041 # split query into pieces
1042 $QueryString = trim($QueryString);
1043 $Words = preg_split("/\s+/", $QueryString);
1044
1045 # if INSERT statement
1046 $WordIndex = 1;
1047 if (strtoupper($Words[0]) == "INSERT")
1048 {
1049 # skip over modifying keywords
1050 while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
1051 || (strtoupper($Words[$WordIndex]) == "DELAYED")
1052 || (strtoupper($Words[$WordIndex]) == "IGNORE")
1053 || (strtoupper($Words[$WordIndex]) == "INTO"))
1054 {
1055 $WordIndex++;
1056 }
1057
1058 # next word is table name
1059 $TableName = $Words[$WordIndex];
1060 }
1061 # else if UPDATE statement
1062 elseif (strtoupper($Words[0]) == "UPDATE")
1063 {
1064 # skip over modifying keywords
1065 while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
1066 || (strtoupper($Words[$WordIndex]) == "IGNORE"))
1067 {
1068 $WordIndex++;
1069 }
1070
1071 # if word following next word is SET
1072 if (strtoupper($Words[$WordIndex + 1]) == "SET")
1073 {
1074 # next word is table name
1075 $TableName = $Words[$WordIndex];
1076 }
1077 }
1078 # else if DELETE statement
1079 elseif (strtoupper($Words[0]) == "DELETE")
1080 {
1081 # skip over modifying keywords
1082 while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
1083 || (strtoupper($Words[$WordIndex]) == "IGNORE")
1084 || (strtoupper($Words[$WordIndex]) == "QUICK"))
1085 {
1086 $WordIndex++;
1087 }
1088
1089 # if next term is FROM
1090 if (strtoupper($Words[$WordIndex]) == "FROM")
1091 {
1092 # next word is table name
1093 $WordIndex++;
1094 $TableName = $Words[$WordIndex];
1095 }
1096 }
1097
1098 # discard table name if it looks at all suspicious
1099 if ($TableName)
1100 {
1101 if (!preg_match("/[a-zA-Z0-9]+/", $TableName))
1102 {
1103 $TableName = FALSE;
1104 }
1105 }
1106
1107 # return table name (or lack thereof) to caller
1108 return $TableName;
1109 }
1110
1117 private function TablesAccessed($QueryString)
1118 {
1119 # assume we're not going to be able to determine tables
1120 $TableNames = FALSE;
1121
1122 # split query into pieces
1123 $QueryString = trim($QueryString);
1124 $Words = preg_split("/\s+/", $QueryString);
1125 $UQueryString = strtoupper($QueryString);
1126 $UWords = preg_split("/\s+/", $UQueryString);
1127
1128 # if SELECT statement
1129 if ($UWords[0] == "SELECT")
1130 {
1131 # keep going until we hit FROM or last word
1132 $WordIndex = 1;
1133 while (($UWords[$WordIndex] != "FROM")
1134 && strlen($UWords[$WordIndex]))
1135 {
1136 $WordIndex++;
1137 }
1138
1139 # if we hit FROM
1140 if ($UWords[$WordIndex] == "FROM")
1141 {
1142 # for each word after FROM
1143 $WordIndex++;
1144 while (strlen($UWords[$WordIndex]))
1145 {
1146 # if current word ends with comma
1147 if (preg_match("/,$/", $Words[$WordIndex]))
1148 {
1149 # strip off comma and add word to table name list
1150 $TableNames[] = substr($Words[$WordIndex], 0, -1);
1151 }
1152 else
1153 {
1154 # add word to table name list
1155 $TableNames[] = $Words[$WordIndex];
1156
1157 # if next word is not comma
1158 $WordIndex++;
1159 if ($Words[$WordIndex] != ",")
1160 {
1161 # if word begins with comma
1162 if (preg_match("/^,/", $Words[$WordIndex]))
1163 {
1164 # strip off comma (NOTE: modifies $Words array!)
1165 $Words[$WordIndex] = substr($Words[$WordIndex], 1);
1166
1167 # decrement index so we start with this word next pass
1168 $WordIndex--;
1169 }
1170 else
1171 {
1172 # stop scanning words (non-basic JOINs not yet handled)
1173 break;
1174 }
1175 }
1176 }
1177
1178 # move to next word
1179 $WordIndex++;
1180 }
1181 }
1182 }
1183
1184 # discard table names if they look at all suspicious
1185 if ($TableNames)
1186 {
1187 foreach ($TableNames as $Name)
1188 {
1189 if (!preg_match("/^[a-zA-Z0-9]+$/", $Name))
1190 {
1191 $TableNames = FALSE;
1192 break;
1193 }
1194 }
1195 }
1196
1197 # return table name (or lack thereof) to caller
1198 return $TableNames;
1199 }
1200
1207 private function RunQuery($QueryString)
1208 {
1209 # log query start time if debugging output is enabled
1210 if (self::$QueryDebugOutputFlag) { $QueryStartTime = microtime(TRUE); }
1211
1212 # run query against database
1213 $this->QueryHandle = mysqli_query($this->Handle, $QueryString) ;
1214
1215 # print query and execution time if debugging output is enabled
1216 if (self::$QueryDebugOutputFlag)
1217 {
1218 print "DB: ".$QueryString." ["
1219 .sprintf("%.2f", microtime(TRUE) - $QueryStartTime)
1220 ."s]"."<br>\n";
1221 }
1222
1223 # if query failed and there are errors that we can ignore
1224 if (($this->QueryHandle === FALSE) && $this->ErrorsToIgnore)
1225 {
1226 # for each pattern for an error that we can ignore
1227 foreach ($this->ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
1228 {
1229 # if error matches pattern
1230 $ErrorMsg = mysqli_error($this->Handle);
1231 if (preg_match($SqlPattern, $QueryString)
1232 && preg_match($ErrMsgPattern, $ErrorMsg))
1233 {
1234 # set return value to indicate error was ignored
1235 $this->QueryHandle = TRUE;
1236
1237 # set internal flag to indicate that an error was ignored
1238 $this->ErrorIgnored = $ErrorMsg;
1239
1240 # stop looking at patterns
1241 break;
1242 }
1243 }
1244 }
1245
1246 # if query failed
1247 if ($this->QueryHandle === FALSE)
1248 {
1249 # clear stored value for number of rows retrieved
1250 $this->NumRows = 0;
1251
1252 # retrieve error info
1253 $this->ErrMsg = mysqli_error($this->Handle);
1254 $this->ErrNo = mysqli_errno($this->Handle);
1255
1256 # if we are supposed to be displaying errors
1257 if (self::$DisplayErrors)
1258 {
1259 # print error info
1260 print("<b>SQL Error:</b> <i>".$this->ErrMsg
1261 ."</i> (".$this->ErrNo.")<br/>\n");
1262 print("<b>SQL Statement:</b> <i>"
1263 .htmlspecialchars($QueryString)."</i><br/>\n");
1264
1265 # retrieve execution trace that got us to this point
1266 $Trace = debug_backtrace();
1267
1268 # remove current context from trace
1269 array_shift($Trace);
1270
1271 # make sure file name and line number are available
1272 foreach ($Trace as $Index => $Loc)
1273 {
1274 if (!array_key_exists("file", $Loc))
1275 {
1276 $Trace[$Index]["file"] = "UNKNOWN";
1277 }
1278 if (!array_key_exists("line", $Loc))
1279 {
1280 $Trace[$Index]["line"] = "??";
1281 }
1282 }
1283
1284 # determine length of leading path common to all file names in trace
1285 $LocString = "";
1286 $OurFile = __FILE__;
1287 $PrefixLen = 9999;
1288 foreach ($Trace as $Loc)
1289 {
1290 if ($Loc["file"] != "UNKNOWN")
1291 {
1292 $Index = 0;
1293 $FNameLength = strlen($Loc["file"]);
1294 while ($Index < $FNameLength &&
1295 $Loc["file"][$Index] == $OurFile[$Index])
1296 { $Index++; }
1297 $PrefixLen = min($PrefixLen, $Index);
1298 }
1299 }
1300
1301 foreach ($Trace as $Loc)
1302 {
1303 $Sep = "";
1304 $ArgString = "";
1305 foreach ($Loc["args"] as $Arg)
1306 {
1307 $ArgString .= $Sep;
1308 switch (gettype($Arg))
1309 {
1310 case "boolean":
1311 $ArgString .= $Arg ? "TRUE" : "FALSE";
1312 break;
1313
1314 case "integer":
1315 case "double":
1316 $ArgString .= $Arg;
1317 break;
1318
1319 case "string":
1320 $ArgString .= '"<i>'.htmlspecialchars(substr($Arg, 0, 40))
1321 .((strlen($Arg) > 40) ? "..." : "").'</i>"';
1322 break;
1323
1324 case "array":
1325 case "resource":
1326 case "NULL":
1327 $ArgString .= strtoupper(gettype($Arg));
1328 break;
1329
1330 case "object":
1331 $ArgString .= get_class($Arg);
1332 break;
1333
1334 case "unknown type":
1335 $ArgString .= "UNKNOWN";
1336 break;
1337 }
1338 $Sep = ",";
1339 }
1340 $Loc["file"] = substr($Loc["file"], $PrefixLen);
1341 $LocString .= "&nbsp;&nbsp;";
1342 if (array_key_exists("class", $Loc))
1343 { $LocString .= $Loc["class"]."::"; }
1344 $LocString .= $Loc["function"]."(".$ArgString.")"
1345 ." - ".$Loc["file"].":".$Loc["line"]
1346 ."<br>\n";
1347 }
1348 print("<b>Trace:</b><br>\n".$LocString);
1349 }
1350 }
1351 return $this->QueryHandle;
1352 }
1353
1358 static private function GetPhpMemoryLimit()
1359 {
1360 $Str = strtoupper(ini_get("memory_limit"));
1361 if (substr($Str, -1) == "B") { $Str = substr($Str, 0, strlen($Str) - 1); }
1362 switch (substr($Str, -1))
1363 {
1364 case "K": $MemoryLimit = (int)$Str * 1024; break;
1365 case "M": $MemoryLimit = (int)$Str * 1048576; break;
1366 case "G": $MemoryLimit = (int)$Str * 1073741824; break;
1367 default: $MemoryLimit = (int)$Str; break;
1368 }
1369 return $MemoryLimit;
1370 }
1371
1376 static private function GetFreeMemory()
1377 {
1378 return self::GetPhpMemoryLimit() - memory_get_usage();
1379 }
1380 }
1381
1382 # define return values (numerical values correspond to MySQL error codes)
1383 // @codingStandardsIgnoreStart (to silence warning about multiple spaces)
1384 define("DB_OKAY", 0);
1385 define("DB_ERROR", 1);
1386 define("DB_ACCESSDENIED", 2);
1387 define("DB_UNKNOWNDB", 3);
1388 define("DB_UNKNOWNTABLE", 4);
1389 define("DB_SYNTAXERROR", 5);
1390 define("DB_DBALREADYEXISTS", 6);
1391 define("DB_DBDOESNOTEXIST", 7);
1392 define("DB_DISKFULL", 8);
1393 // @codingStandardsIgnoreEnd
1394
1395 # define value to designate omitted arguments (so DB values can be set to NULL)
1396 define("DB_NOVALUE", "!-_-_-DB_NOVALUE-_-_-!");
1397
1398 # MySQL error code mapping
1399 $APDBErrorCodeMappings = array(
1400 1045 => DB_ACCESSDENIED,
1401 1049 => DB_UNKNOWNDB,
1402 1046 => DB_UNKNOWNTABLE,
1403 1064 => DB_SYNTAXERROR,
1404 1007 => DB_DBALREADYEXISTS, # ? (not sure)
1405 1008 => DB_DBDOESNOTEXIST, # ? (not sure)
1406 1021 => DB_DISKFULL, # ? (not sure)
1407 );
1408
1409 # date() format for SQL dates
1410 define("DATE_SQL", "Y-m-d H:i:s");
Database\QueryErrMsg
QueryErrMsg()
Get most recent error message text set by Query().
Definition: Database.php:566
Database\$DBName
$DBName
Definition: Database.php:980
Database\Caching
static Caching($NewSetting=NULL)
Get or set whether query result caching is currently enabled.
Definition: Database.php:235
Database\__sleep
__sleep()
Definition: Database.php:87
Database\GetServerVersion
GetServerVersion($FullVersion=FALSE)
Get database server version number.
Definition: Database.php:159
Database\SetGlobalDatabaseName
static SetGlobalDatabaseName($DatabaseName)
Set default database name.
Definition: Database.php:130
DB_ACCESSDENIED
const DB_ACCESSDENIED
Definition: Database.php:1386
Database\SetDefaultStorageEngine
SetDefaultStorageEngine($Engine)
Set default database storage engine.
Definition: Database.php:143
Database\ExecuteQueriesFromFile
ExecuteQueriesFromFile($FileName)
Execute queries from specified file.
Definition: Database.php:507
Database\$DBPassword
$DBPassword
Definition: Database.php:978
Database\$DBUserName
$DBUserName
Definition: Database.php:977
Database\UpdateIntValue
UpdateIntValue($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
A convenience function to get or set an integer value in the database.
Definition: Database.php:826
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:289
Database\DBUserName
DBUserName()
Get name used to connect with database server.
Definition: Database.php:223
Database\EscapeString
EscapeString($String)
Escape a string that may contain null bytes.
Definition: Database.php:869
Database\FetchRow
FetchRow()
Get next database row retrieved by most recent query.
Definition: Database.php:640
Database\LastInsertId
LastInsertId()
Get ID of row added by the last SQL "INSERT" statement.
Definition: Database.php:758
DB_SYNTAXERROR
const DB_SYNTAXERROR
Definition: Database.php:1389
Database\__construct
__construct($UserName=NULL, $Password=NULL, $DatabaseName=NULL, $HostName=NULL)
Object constructor.
Definition: Database.php:41
Database\TableExists
TableExists($TableName)
Get whether specified table exists.
Definition: Database.php:890
DB_DISKFULL
const DB_DISKFULL
Definition: Database.php:1392
Database\SetGlobalServerInfo
static SetGlobalServerInfo($UserName, $Password, $HostName="localhost")
Definition: Database.php:114
$APDBErrorCodeMappings
$APDBErrorCodeMappings
Definition: Database.php:1399
Database\$DBHostName
$DBHostName
Definition: Database.php:979
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:183
Database\GetFieldType
GetFieldType($TableName, $FieldName)
Get field (column) type.
Definition: Database.php:918
DB_NOVALUE
const DB_NOVALUE
Definition: Database.php:1396
Database\NumRowsSelected
NumRowsSelected()
Get number of rows returned by last SELECT or SHOW query.
Definition: Database.php:597
Database\FetchRows
FetchRows($NumberOfRows=NULL)
Get specified number of database rows retrieved by most recent query.
Definition: Database.php:686
Database\QueryDebugOutput
static QueryDebugOutput($NewSetting)
Enable or disable debugging output for queries.
Definition: Database.php:930
Database\Query
Query($QueryString, $FieldName="")
Query database (with caching if enabled).
Definition: Database.php:327
Database\FetchField
FetchField($FieldName)
Pull next row from last DB query and get a specific value from that row.
Definition: Database.php:746
Database\FieldExists
FieldExists($TableName, $FieldName)
Get whether specified field exists in specified table.
Definition: Database.php:902
Database\NumCacheHits
static NumCacheHits()
Get the number of queries that have resulted in cache hits since program execution began...
Definition: Database.php:951
DB_UNKNOWNTABLE
const DB_UNKNOWNTABLE
Definition: Database.php:1388
Database\FetchColumn
FetchColumn($FieldName, $IndexFieldName=NULL)
Get all available values for specified database field retrieved by most recent query.
Definition: Database.php:721
Database\DBHostName
DBHostName()
Get host name of system on which database server resides.
Definition: Database.php:203
DB_DBDOESNOTEXIST
const DB_DBDOESNOTEXIST
Definition: Database.php:1391
Database\NumRowsAffected
NumRowsAffected()
Get number of rows affected by last INSERT, UPDATE, REPLACE, or DELETE query.
Definition: Database.php:623
Database\UpdateFloatValue
UpdateFloatValue($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
A convenience function to get or set a float value in the database.
Definition: Database.php:850
DB_DBALREADYEXISTS
const DB_DBALREADYEXISTS
Definition: Database.php:1390
Database\QueryErrNo
QueryErrNo()
Get most recent error code set by Query().
Definition: Database.php:576
DB_UNKNOWNDB
const DB_UNKNOWNDB
Definition: Database.php:1387
Database\GetHostInfo
GetHostInfo()
Get database connection type and hostname.
Definition: Database.php:193
Database\UpdateValue
UpdateValue($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
A convenience function to get or set a value in the database.
Definition: Database.php:779
Database\AdvancedCaching
static AdvancedCaching($NewSetting=NULL)
Get or set whether advanced query result cachine is currently enabled.
Definition: Database.php:261
Database\DisplayQueryErrors
static DisplayQueryErrors($NewValue=NULL)
Get/set whether Query() errors will be displayed.
Definition: Database.php:587
Database\CacheHitRate
static CacheHitRate()
Get the ratio of query cache hits to queries as a percentage.
Definition: Database.php:961
Database\IgnoredError
IgnoredError()
Check whether an error was ignored by the most recent query.
Definition: Database.php:309
Database\DBName
DBName()
Get current database name.
Definition: Database.php:213
Database\LogComment
LogComment($String)
Peform query that consists of SQL comment statement.
Definition: Database.php:880
Database\__wakeup
__wakeup()
Restore database connection when unserialized.
Definition: Database.php:94
Database\NumQueries
static NumQueries()
Get the number of queries that have been run since program execution began.
Definition: Database.php:940