5 # A Simple SQL Database Abstraction Object
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).
12 # Author: Edward Almasy (almasy@axisdata.com)
14 # Part of the AxisPHP library v1.2.5
15 # For more information see http://www.axisdata.com/AxisPHP/
24 # ---- PUBLIC INTERFACE --------------------------------------------------
41 $UserName = NULL, $Password = NULL, $DatabaseName = NULL, $HostName = NULL)
43 # save DB access parameter values
44 $this->
DBUserName = $UserName ? $UserName : self::$GlobalDBUserName;
45 $this->DBPassword = $Password ? $Password : self::$GlobalDBPassword;
47 (isset(self::$GlobalDBHostName) ? self::$GlobalDBHostName
49 $this->
DBName = $DatabaseName ? $DatabaseName : self::$GlobalDBName;
51 # if we don't already have a connection or DB access parameters were supplied
53 if (!array_key_exists($HandleIndex, self::$ConnectionHandles)
54 || $UserName || $Password || $DatabaseName || $HostName)
56 # open connection to DB server
57 self::$ConnectionHandles[$HandleIndex] = mysql_connect(
59 $this->DBPassword, TRUE)
60 or die(
"Could not connect to database: ".mysql_error());
62 # set local connection handle
63 $this->Handle = self::$ConnectionHandles[$HandleIndex];
66 mysql_select_db($this->
DBName, $this->Handle)
67 or die(mysql_error($this->Handle));
71 # set local connection handle
72 $this->Handle = self::$ConnectionHandles[$HandleIndex];
82 return array(
"DBUserName",
"DBPassword",
"DBHostName",
"DBName");
89 # open connection to DB server
90 $this->Handle = mysql_connect(
92 or die(
"could not connect to database");
95 mysql_select_db($this->
DBName, $this->Handle)
96 or die(mysql_error($this->Handle));
109 # save default DB access parameters
110 self::$GlobalDBUserName = $UserName;
111 self::$GlobalDBPassword = $Password;
112 self::$GlobalDBHostName = $HostName;
114 # clear any existing DB connection handles
115 self::$ConnectionHandles = array();
124 # save new default DB name
125 self::$GlobalDBName = $DatabaseName;
127 # clear any existing DB connection handles
128 self::$ConnectionHandles = array();
137 # choose config variable to use based on server version number
139 ?
"storage_engine" :
"default_storage_engine";
141 # set storage engine in database
142 $this->
Query(
"SET ".$ConfigVar.
" = ".$Engine);
151 # retrieve version string
152 $Version = $this->
Query(
"SELECT VERSION() AS ServerVer",
"ServerVer");
154 # strip off any build/config suffix
155 $Pieces = explode(
"-", $Version);
156 $Version = array_shift($Pieces);
158 # return version number to caller
192 # if cache setting has changed
193 if (($NewSetting !== NULL) && ($NewSetting != self::$CachingFlag))
196 self::$CachingFlag = $NewSetting;
198 # clear any existing cached results
199 self::$QueryResultCache = array();
202 # return current setting to caller
203 return self::$CachingFlag;
218 if ($NewSetting !== NULL)
220 self::$AdvancedCachingFlag = $NewSetting;
222 return self::$AdvancedCachingFlag;
246 if ($NormalizeWhitespace && ($ErrorsToIgnore !== NULL))
248 $RevisedErrorsToIgnore = array();
249 foreach ($ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
251 $SqlPattern = preg_replace(
"/\\s+/",
"\\s+", $SqlPattern);
252 $RevisedErrorsToIgnore[$SqlPattern] = $ErrMsgPattern;
254 $ErrorsToIgnore = $RevisedErrorsToIgnore;
256 $this->ErrorsToIgnore = $ErrorsToIgnore;
266 return $this->ErrorIgnored;
279 function Query($QueryString, $FieldName =
"")
281 # clear flag that indicates whether query error was ignored
282 $this->ErrorIgnored = FALSE;
284 # if caching is enabled
285 if (self::$CachingFlag)
287 # if SQL statement is read-only
288 if ($this->IsReadOnlyStatement($QueryString))
290 # if we have statement in cache
291 if (isset(self::$QueryResultCache[$QueryString][
"NumRows"]))
293 if (self::$QueryDebugOutputFlag)
294 { print(
"DB-C: $QueryString<br>\n"); }
296 # make sure query result looks okay
297 $this->QueryHandle = TRUE;
299 # increment cache hit counter
300 self::$CachedQueryCounter++;
302 # make local copy of results
303 $this->QueryResults = self::$QueryResultCache[$QueryString];
304 $this->NumRows = self::$QueryResultCache[$QueryString][
"NumRows"];
306 # set flag to indicate that results should be retrieved from cache
307 $this->GetResultsFromCache = TRUE;
311 # execute SQL statement
312 $this->QueryHandle = $this->RunQuery($QueryString);
313 if (!is_resource($this->QueryHandle)) {
return FALSE; }
315 # save number of rows in result
316 $this->NumRows = mysql_num_rows($this->QueryHandle);
318 # if too many rows to cache
319 if ($this->NumRows >= 50)
321 # set flag to indicate that query results should not
322 # be retrieved from cache
323 $this->GetResultsFromCache = FALSE;
327 # if advanced caching is enabled
328 if (self::$AdvancedCachingFlag)
330 # save tables accessed by query
331 self::$QueryResultCache[$QueryString][
"TablesAccessed"] =
332 $this->TablesAccessed($QueryString);
336 if ($this->NumRows > 0)
339 for ($Row = 0; $Row < $this->NumRows; $Row++)
341 $this->QueryResults[$Row] =
342 mysql_fetch_assoc($this->QueryHandle);
345 # cache query results
346 self::$QueryResultCache[$QueryString] = $this->QueryResults;
350 # clear local query results
351 unset($this->QueryResults);
354 # cache number of rows
355 self::$QueryResultCache[$QueryString][
"NumRows"] = $this->NumRows;
357 # set flag to indicate that query results should be retrieved from cache
358 $this->GetResultsFromCache = TRUE;
364 # if advanced caching is enabled
365 if (self::$AdvancedCachingFlag)
367 # if table modified by statement is known
368 $TableModified = $this->TableModified($QueryString);
371 # for each cached query
372 foreach (self::$QueryResultCache
373 as $CachedQueryString => $CachedQueryResult)
375 # if we know what tables were accessed
376 if ($CachedQueryResult[
"TablesAccessed"])
378 # if tables accessed include the one we may modify
379 if (in_array($TableModified, $CachedQueryResult[
"TablesAccessed"]))
381 # clear cached query results
382 unset($GLOBALS[
"APDBQueryResultCache"][$CachedQueryString]);
387 # clear cached query results
388 unset($GLOBALS[
"APDBQueryResultCache"][$CachedQueryString]);
394 # clear entire query result cache
395 self::$QueryResultCache = array();
400 # clear entire query result cache
401 self::$QueryResultCache = array();
404 # execute SQL statement
405 $this->QueryHandle = $this->RunQuery($QueryString);
406 if ($this->QueryHandle === FALSE) {
return FALSE; }
408 # set flag to indicate that query results should not be retrieved from cache
409 $this->GetResultsFromCache = FALSE;
413 $this->RowCounter = 0;
415 # increment query counter
416 self::$QueryCounter++;
420 # execute SQL statement
421 $this->QueryHandle = $this->RunQuery($QueryString);
422 if ($this->QueryHandle === FALSE) {
return FALSE; }
425 if (($FieldName !=
"") && ($this->QueryHandle != FALSE))
431 return $this->QueryHandle;
450 $FHandle = fopen($FileName,
"r");
452 # if file open succeeded
453 if ($FHandle !== FALSE)
455 # while lines left in file
458 while (!feof($FHandle))
460 # read in line from file
461 $Line = fgets($FHandle, 32767);
463 # trim whitespace from line
466 # if line is not empty and not a comment
467 if (!preg_match(
"/^#/", $Line)
468 && !preg_match(
"/^--/", $Line)
471 # add line to current query
474 # if line completes a query
475 if (preg_match(
"/;$/", $Line))
479 $Result = $this->
Query($Query);
482 # if query resulted in an error that is not ignorable
483 if ($Result === FALSE)
485 # stop processing queries and set error code
497 # return number of executed queries to caller
508 return $this->ErrMsg;
529 if ($NewValue !== NULL) { self::$DisplayErrors = $NewValue; }
530 return self::$DisplayErrors;
539 # if caching is enabled and query was cached
540 if (self::$CachingFlag && $this->GetResultsFromCache)
542 # return cached number of rows to caller
543 return $this->NumRows;
547 # call to this method after an unsuccessful query
548 if (!is_resource($this->QueryHandle))
553 # retrieve number of rows and return to caller
554 return mysql_num_rows($this->QueryHandle);
565 # if caching is enabled and query was cached
566 if (self::$CachingFlag && $this->GetResultsFromCache)
568 # if rows left to return
569 if ($this->RowCounter < $this->NumRows)
571 # retrieve row from cache
572 $Result = $this->QueryResults[$this->RowCounter];
574 # increment row counter
585 # call to this method after successful query
586 if (is_resource($this->QueryHandle))
588 $Result = mysql_fetch_assoc($this->QueryHandle);
591 # call to this method after unsuccessful query
598 # return row to caller
610 # assume no rows will be returned
613 # for each available row
615 while ((($RowsFetched < $NumberOfRows) || ($NumberOfRows == NULL))
623 # return array of rows to caller
648 if ($IndexFieldName != NULL)
650 $Array[$Record[$IndexFieldName]] = $Record[$FieldName];
654 $Array[] = $Record[$FieldName];
671 return isset($Record[$FieldName]) ? $Record[$FieldName] : NULL;
682 return (
int)$this->
Query(
683 "SELECT LAST_INSERT_ID() AS InsertId",
702 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
704 # expand condition if supplied
705 if ($Condition != NULL) { $Condition =
" WHERE ".$Condition; }
707 # read cached record from database if not already loaded
708 if (!isset($CachedRecord))
710 $this->
Query(
"SELECT * FROM `".$TableName.
"` ".$Condition);
714 # if new value supplied
717 # update value in database
718 $this->
Query(
"UPDATE `".$TableName.
"` SET `".$FieldName.
"` = "
719 .(($NewValue === NULL) ?
"NULL" :
"'"
720 .mysql_real_escape_string($NewValue).
"'")
723 # update value in cached record
724 $CachedRecord[$FieldName] = $NewValue;
727 # return value from cached record to caller
728 return isset($CachedRecord[$FieldName])
729 ? $CachedRecord[$FieldName] : NULL;
749 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
753 $Condition, $CachedRecord);
773 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
777 $Condition, $CachedRecord);
791 $this->
Query(
"-- ".$String);
801 $this->
Query(
"SHOW TABLES LIKE '".addslashes($TableName).
"'");
813 $this->
Query(
"DESC ".$TableName);
814 while ($CurrentFieldName = $this->
FetchField(
"Field"))
816 if ($CurrentFieldName == $FieldName) {
return TRUE; }
829 $this->
Query(
"DESC ".$TableName);
840 self::$QueryDebugOutputFlag = $NewSetting;
850 return self::$QueryCounter;
861 return self::$CachedQueryCounter;
871 if (self::$QueryCounter)
873 return (self::$CachedQueryCounter / self::$QueryCounter) * 100;
883 # ---- PRIVATE INTERFACE -------------------------------------------------
891 private $QueryHandle;
892 private $QueryResults;
895 private $GetResultsFromCache;
896 private $ErrorIgnored = FALSE;
897 private $ErrorsToIgnore = NULL;
898 private $ErrMsg = NULL;
899 private $ErrNo = NULL;
901 private static $DisplayErrors = FALSE;
903 private static $GlobalDBUserName;
904 private static $GlobalDBPassword;
905 private static $GlobalDBHostName;
906 private static $GlobalDBName;
909 private static $QueryDebugOutputFlag = FALSE;
910 # flag for whether caching is turned on
911 private static $CachingFlag = TRUE;
912 # query result advanced caching flag
913 private static $AdvancedCachingFlag = FALSE;
914 # global cache for query results
915 private static $QueryResultCache = array();
917 private static $QueryCounter = 0;
918 private static $CachedQueryCounter = 0;
919 # database connection link handles
920 private static $ConnectionHandles = array();
927 private function IsReadOnlyStatement($QueryString)
929 return preg_match(
"/^[ ]*SELECT /i", $QueryString) ? TRUE : FALSE;
938 private function TableModified($QueryString)
940 # assume we're not going to be able to determine table
943 # split query into pieces
944 $QueryString = trim($QueryString);
945 $Words = preg_split(
"/\s+/", $QueryString);
947 # if INSERT statement
949 if (strtoupper($Words[0]) ==
"INSERT")
951 # skip over modifying keywords
952 while ((strtoupper($Words[$WordIndex]) ==
"LOW_PRIORITY")
953 || (strtoupper($Words[$WordIndex]) ==
"DELAYED")
954 || (strtoupper($Words[$WordIndex]) ==
"IGNORE")
955 || (strtoupper($Words[$WordIndex]) ==
"INTO"))
960 # next word is table name
961 $TableName = $Words[$WordIndex];
963 # else if UPDATE statement
964 elseif (strtoupper($Words[0]) ==
"UPDATE")
966 # skip over modifying keywords
967 while ((strtoupper($Words[$WordIndex]) ==
"LOW_PRIORITY")
968 || (strtoupper($Words[$WordIndex]) ==
"IGNORE"))
973 # if word following next word is SET
974 if (strtoupper($Words[$WordIndex + 1]) ==
"SET")
976 # next word is table name
977 $TableName = $Words[$WordIndex];
980 # else if DELETE statement
981 elseif (strtoupper($Words[0]) ==
"DELETE")
983 # skip over modifying keywords
984 while ((strtoupper($Words[$WordIndex]) ==
"LOW_PRIORITY")
985 || (strtoupper($Words[$WordIndex]) ==
"IGNORE")
986 || (strtoupper($Words[$WordIndex]) ==
"QUICK"))
991 # if next term is FROM
992 if (strtoupper($Words[$WordIndex]) ==
"FROM")
994 # next word is table name
996 $TableName = $Words[$WordIndex];
1000 # discard table name if it looks at all suspicious
1003 if (!preg_match(
"/[a-zA-Z0-9]+/", $TableName))
1009 # return table name (or lack thereof) to caller
1019 private function TablesAccessed($QueryString)
1021 # assume we're not going to be able to determine tables
1022 $TableNames = FALSE;
1024 # split query into pieces
1025 $QueryString = trim($QueryString);
1026 $Words = preg_split(
"/\s+/", $QueryString);
1027 $UQueryString = strtoupper($QueryString);
1028 $UWords = preg_split(
"/\s+/", $UQueryString);
1030 # if SELECT statement
1031 if ($UWords[0] ==
"SELECT")
1033 # keep going until we hit FROM or last word
1035 while (($UWords[$WordIndex] !=
"FROM")
1036 && strlen($UWords[$WordIndex]))
1042 if ($UWords[$WordIndex] ==
"FROM")
1044 # for each word after FROM
1046 while (strlen($UWords[$WordIndex]))
1048 # if current word ends with comma
1049 if (preg_match(
"/,$/", $Words[$WordIndex]))
1051 # strip off comma and add word to table name list
1052 $TableNames[] = substr($Words[$WordIndex], 0, -1);
1056 # add word to table name list
1057 $TableNames[] = $Words[$WordIndex];
1059 # if next word is not comma
1061 if ($Words[$WordIndex] !=
",")
1063 # if word begins with comma
1064 if (preg_match(
"/^,/", $Words[$WordIndex]))
1066 # strip off comma (NOTE: modifies $Words array!)
1067 $Words[$WordIndex] = substr($Words[$WordIndex], 1);
1069 # decrement index so we start with this word next pass
1074 # stop scanning words (non-basic JOINs not yet handled)
1086 # discard table names if they look at all suspicious
1089 foreach ($TableNames as $Name)
1091 if (!preg_match(
"/^[a-zA-Z0-9]+$/", $Name))
1093 $TableNames = FALSE;
1099 # return table name (or lack thereof) to caller
1109 private function RunQuery($QueryString)
1111 # log query start time if debugging output is enabled
1112 if (self::$QueryDebugOutputFlag) { $QueryStartTime = microtime(TRUE); }
1114 # run query against database
1115 $this->QueryHandle = mysql_query($QueryString, $this->Handle);
1117 # print query and execution time if debugging output is enabled
1118 if (self::$QueryDebugOutputFlag)
1120 print
"DB: ".$QueryString.
" ["
1121 .sprintf(
"%.2f", microtime(TRUE) - $QueryStartTime)
1125 # if query failed and there are errors that we can ignore
1126 if (($this->QueryHandle === FALSE) && $this->ErrorsToIgnore)
1128 # for each pattern for an error that we can ignore
1129 foreach ($this->ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
1131 # if error matches pattern
1132 $ErrorMsg = mysql_error($this->Handle);
1133 if (preg_match($SqlPattern, $QueryString)
1134 && preg_match($ErrMsgPattern, $ErrorMsg))
1136 # set return value to indicate error was ignored
1137 $this->QueryHandle = TRUE;
1139 # set internal flag to indicate that an error was ignored
1140 $this->ErrorIgnored = $ErrorMsg;
1142 # stop looking at patterns
1149 if ($this->QueryHandle === FALSE)
1151 # clear stored value for number of rows retrieved
1154 # retrieve error info
1155 $this->ErrMsg = mysql_error($this->Handle);
1156 $this->ErrNo = mysql_errno($this->Handle);
1158 # if we are supposed to be displaying errors
1159 if (self::$DisplayErrors)
1162 print(
"<b>SQL Error:</b> <i>".$this->ErrMsg
1163 .
"</i> (".$this->ErrNo.
")<br/>\n");
1164 print(
"<b>SQL Statement:</b> <i>"
1165 .htmlspecialchars($QueryString).
"</i><br/>\n");
1167 # retrieve execution trace that got us to this point
1168 $Trace = debug_backtrace();
1170 # remove current context from trace
1171 array_shift($Trace);
1173 # make sure file name and line number are available
1174 foreach ($Trace as $Index => $Loc)
1176 if (!array_key_exists(
"file", $Loc))
1178 $Trace[$Index][
"file"] =
"UNKNOWN";
1180 if (!array_key_exists(
"line", $Loc))
1182 $Trace[$Index][
"line"] =
"??";
1186 # determine length of leading path common to all file names in trace
1188 $OurFile = __FILE__;
1190 foreach ($Trace as $Loc)
1192 if ($Loc[
"file"] !=
"UNKNOWN")
1195 while ($Loc[
"file"][$Index] == $OurFile[$Index])
1197 $PrefixLen = min($PrefixLen, $Index);
1201 foreach ($Trace as $Loc)
1205 foreach ($Loc[
"args"] as $Arg)
1208 switch (gettype($Arg))
1211 $ArgString .= $Arg ?
"TRUE" :
"FALSE";
1220 $ArgString .=
'"<i>'.htmlspecialchars(substr($Arg, 0, 40))
1221 .((strlen($Arg) > 40) ?
"..." :
"").
'</i>"';
1227 $ArgString .= strtoupper(gettype($Arg));
1231 $ArgString .= get_class($Arg);
1234 case "unknown type":
1235 $ArgString .=
"UNKNOWN";
1240 $Loc[
"file"] = substr($Loc[
"file"], $PrefixLen);
1241 $LocString .=
" ";
1242 if (array_key_exists(
"class", $Loc))
1243 { $LocString .= $Loc[
"class"].
"::"; }
1244 $LocString .= $Loc[
"function"].
"(".$ArgString.
")"
1245 .
" - ".$Loc[
"file"].
":".$Loc[
"line"]
1248 print(
"<b>Trace:</b><br>\n".$LocString);
1251 return $this->QueryHandle;
1255 # define return values (numerical values correspond to MySQL error codes)
1256 define(
"DB_OKAY", 0);
1257 define(
"DB_ERROR", 1);
1258 define(
"DB_ACCESSDENIED", 2);
1259 define(
"DB_UNKNOWNDB", 3);
1260 define(
"DB_UNKNOWNTABLE", 4);
1261 define(
"DB_SYNTAXERROR", 5);
1262 define(
"DB_DBALREADYEXISTS", 6);
1263 define(
"DB_DBDOESNOTEXIST", 7);
1264 define(
"DB_DISKFULL", 8);
1266 # define value to designate omitted arguments (so DB values can be set to NULL)
1267 define(
"DB_NOVALUE",
"!-_-_-DB_NOVALUE-_-_-!");
1269 # MySQL error code mapping
1280 # date() format for SQL dates
1281 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.
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.
SQL database abstraction object with smart query caching.
GetServerVersion()
Get database server version number.
SetQueryErrorsToIgnore($ErrorsToIgnore, $NormalizeWhitespace=TRUE)
Set query errors to ignore.
DBUserName()
Get name used to connect with database server.
Database($UserName=NULL, $Password=NULL, $DatabaseName=NULL, $HostName=NULL)
Object constructor.
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")
GetFieldType($TableName, $FieldName)
Get field (column) type.
NumRowsSelected()
Get number of rows returned by last 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.
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().
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.