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 # call to this method after an unsuccessful query
566 if (!is_resource($this->QueryHandle))
571 # retrieve number of rows and return to caller
572 return mysql_affected_rows($this->Handle);
582 # if caching is enabled and query was cached
583 if (self::$CachingFlag && $this->GetResultsFromCache)
585 # if rows left to return
586 if ($this->RowCounter < $this->NumRows)
588 # retrieve row from cache
589 $Result = $this->QueryResults[$this->RowCounter];
591 # increment row counter
602 # call to this method after successful query
603 if (is_resource($this->QueryHandle))
605 $Result = mysql_fetch_assoc($this->QueryHandle);
608 # call to this method after unsuccessful query
615 # return row to caller
627 # assume no rows will be returned
630 # for each available row
632 while ((($RowsFetched < $NumberOfRows) || ($NumberOfRows == NULL))
640 # return array of rows to caller
665 if ($IndexFieldName != NULL)
667 $Array[$Record[$IndexFieldName]] = $Record[$FieldName];
671 $Array[] = $Record[$FieldName];
688 return isset($Record[$FieldName]) ? $Record[$FieldName] : NULL;
699 return (
int)$this->
Query(
700 "SELECT LAST_INSERT_ID() AS InsertId",
719 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
721 # expand condition if supplied
722 if ($Condition != NULL) { $Condition =
" WHERE ".$Condition; }
724 # read cached record from database if not already loaded
725 if (!isset($CachedRecord))
727 $this->
Query(
"SELECT * FROM `".$TableName.
"` ".$Condition);
731 # if new value supplied
734 # update value in database
735 $this->
Query(
"UPDATE `".$TableName.
"` SET `".$FieldName.
"` = "
736 .(($NewValue === NULL) ?
"NULL" :
"'"
737 .mysql_real_escape_string($NewValue).
"'")
740 # update value in cached record
741 $CachedRecord[$FieldName] = $NewValue;
744 # return value from cached record to caller
745 return isset($CachedRecord[$FieldName])
746 ? $CachedRecord[$FieldName] : NULL;
766 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
770 $Condition, $CachedRecord);
790 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
794 $Condition, $CachedRecord);
808 $this->
Query(
"-- ".$String);
818 $this->
Query(
"SHOW TABLES LIKE '".addslashes($TableName).
"'");
830 $this->
Query(
"DESC ".$TableName);
831 while ($CurrentFieldName = $this->
FetchField(
"Field"))
833 if ($CurrentFieldName == $FieldName) {
return TRUE; }
846 $this->
Query(
"DESC ".$TableName);
857 self::$QueryDebugOutputFlag = $NewSetting;
867 return self::$QueryCounter;
878 return self::$CachedQueryCounter;
888 if (self::$QueryCounter)
890 return (self::$CachedQueryCounter / self::$QueryCounter) * 100;
900 # ---- PRIVATE INTERFACE -------------------------------------------------
908 private $QueryHandle;
909 private $QueryResults;
912 private $GetResultsFromCache;
913 private $ErrorIgnored = FALSE;
914 private $ErrorsToIgnore = NULL;
915 private $ErrMsg = NULL;
916 private $ErrNo = NULL;
918 private static $DisplayErrors = FALSE;
920 private static $GlobalDBUserName;
921 private static $GlobalDBPassword;
922 private static $GlobalDBHostName;
923 private static $GlobalDBName;
926 private static $QueryDebugOutputFlag = FALSE;
927 # flag for whether caching is turned on
928 private static $CachingFlag = TRUE;
929 # query result advanced caching flag
930 private static $AdvancedCachingFlag = FALSE;
931 # global cache for query results
932 private static $QueryResultCache = array();
934 private static $QueryCounter = 0;
935 private static $CachedQueryCounter = 0;
936 # database connection link handles
937 private static $ConnectionHandles = array();
944 private function IsReadOnlyStatement($QueryString)
946 return preg_match(
"/^[ ]*SELECT /i", $QueryString) ? TRUE : FALSE;
955 private function TableModified($QueryString)
957 # assume we're not going to be able to determine table
960 # split query into pieces
961 $QueryString = trim($QueryString);
962 $Words = preg_split(
"/\s+/", $QueryString);
964 # if INSERT statement
966 if (strtoupper($Words[0]) ==
"INSERT")
968 # skip over modifying keywords
969 while ((strtoupper($Words[$WordIndex]) ==
"LOW_PRIORITY")
970 || (strtoupper($Words[$WordIndex]) ==
"DELAYED")
971 || (strtoupper($Words[$WordIndex]) ==
"IGNORE")
972 || (strtoupper($Words[$WordIndex]) ==
"INTO"))
977 # next word is table name
978 $TableName = $Words[$WordIndex];
980 # else if UPDATE statement
981 elseif (strtoupper($Words[0]) ==
"UPDATE")
983 # skip over modifying keywords
984 while ((strtoupper($Words[$WordIndex]) ==
"LOW_PRIORITY")
985 || (strtoupper($Words[$WordIndex]) ==
"IGNORE"))
990 # if word following next word is SET
991 if (strtoupper($Words[$WordIndex + 1]) ==
"SET")
993 # next word is table name
994 $TableName = $Words[$WordIndex];
997 # else if DELETE statement
998 elseif (strtoupper($Words[0]) ==
"DELETE")
1000 # skip over modifying keywords
1001 while ((strtoupper($Words[$WordIndex]) ==
"LOW_PRIORITY")
1002 || (strtoupper($Words[$WordIndex]) ==
"IGNORE")
1003 || (strtoupper($Words[$WordIndex]) ==
"QUICK"))
1008 # if next term is FROM
1009 if (strtoupper($Words[$WordIndex]) ==
"FROM")
1011 # next word is table name
1013 $TableName = $Words[$WordIndex];
1017 # discard table name if it looks at all suspicious
1020 if (!preg_match(
"/[a-zA-Z0-9]+/", $TableName))
1026 # return table name (or lack thereof) to caller
1036 private function TablesAccessed($QueryString)
1038 # assume we're not going to be able to determine tables
1039 $TableNames = FALSE;
1041 # split query into pieces
1042 $QueryString = trim($QueryString);
1043 $Words = preg_split(
"/\s+/", $QueryString);
1044 $UQueryString = strtoupper($QueryString);
1045 $UWords = preg_split(
"/\s+/", $UQueryString);
1047 # if SELECT statement
1048 if ($UWords[0] ==
"SELECT")
1050 # keep going until we hit FROM or last word
1052 while (($UWords[$WordIndex] !=
"FROM")
1053 && strlen($UWords[$WordIndex]))
1059 if ($UWords[$WordIndex] ==
"FROM")
1061 # for each word after FROM
1063 while (strlen($UWords[$WordIndex]))
1065 # if current word ends with comma
1066 if (preg_match(
"/,$/", $Words[$WordIndex]))
1068 # strip off comma and add word to table name list
1069 $TableNames[] = substr($Words[$WordIndex], 0, -1);
1073 # add word to table name list
1074 $TableNames[] = $Words[$WordIndex];
1076 # if next word is not comma
1078 if ($Words[$WordIndex] !=
",")
1080 # if word begins with comma
1081 if (preg_match(
"/^,/", $Words[$WordIndex]))
1083 # strip off comma (NOTE: modifies $Words array!)
1084 $Words[$WordIndex] = substr($Words[$WordIndex], 1);
1086 # decrement index so we start with this word next pass
1091 # stop scanning words (non-basic JOINs not yet handled)
1103 # discard table names if they look at all suspicious
1106 foreach ($TableNames as $Name)
1108 if (!preg_match(
"/^[a-zA-Z0-9]+$/", $Name))
1110 $TableNames = FALSE;
1116 # return table name (or lack thereof) to caller
1126 private function RunQuery($QueryString)
1128 # log query start time if debugging output is enabled
1129 if (self::$QueryDebugOutputFlag) { $QueryStartTime = microtime(TRUE); }
1131 # run query against database
1132 $this->QueryHandle = mysql_query($QueryString, $this->Handle);
1134 # print query and execution time if debugging output is enabled
1135 if (self::$QueryDebugOutputFlag)
1137 print
"DB: ".$QueryString.
" ["
1138 .sprintf(
"%.2f", microtime(TRUE) - $QueryStartTime)
1142 # if query failed and there are errors that we can ignore
1143 if (($this->QueryHandle === FALSE) && $this->ErrorsToIgnore)
1145 # for each pattern for an error that we can ignore
1146 foreach ($this->ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
1148 # if error matches pattern
1149 $ErrorMsg = mysql_error($this->Handle);
1150 if (preg_match($SqlPattern, $QueryString)
1151 && preg_match($ErrMsgPattern, $ErrorMsg))
1153 # set return value to indicate error was ignored
1154 $this->QueryHandle = TRUE;
1156 # set internal flag to indicate that an error was ignored
1157 $this->ErrorIgnored = $ErrorMsg;
1159 # stop looking at patterns
1166 if ($this->QueryHandle === FALSE)
1168 # clear stored value for number of rows retrieved
1171 # retrieve error info
1172 $this->ErrMsg = mysql_error($this->Handle);
1173 $this->ErrNo = mysql_errno($this->Handle);
1175 # if we are supposed to be displaying errors
1176 if (self::$DisplayErrors)
1179 print(
"<b>SQL Error:</b> <i>".$this->ErrMsg
1180 .
"</i> (".$this->ErrNo.
")<br/>\n");
1181 print(
"<b>SQL Statement:</b> <i>"
1182 .htmlspecialchars($QueryString).
"</i><br/>\n");
1184 # retrieve execution trace that got us to this point
1185 $Trace = debug_backtrace();
1187 # remove current context from trace
1188 array_shift($Trace);
1190 # make sure file name and line number are available
1191 foreach ($Trace as $Index => $Loc)
1193 if (!array_key_exists(
"file", $Loc))
1195 $Trace[$Index][
"file"] =
"UNKNOWN";
1197 if (!array_key_exists(
"line", $Loc))
1199 $Trace[$Index][
"line"] =
"??";
1203 # determine length of leading path common to all file names in trace
1205 $OurFile = __FILE__;
1207 foreach ($Trace as $Loc)
1209 if ($Loc[
"file"] !=
"UNKNOWN")
1212 while ($Loc[
"file"][$Index] == $OurFile[$Index])
1214 $PrefixLen = min($PrefixLen, $Index);
1218 foreach ($Trace as $Loc)
1222 foreach ($Loc[
"args"] as $Arg)
1225 switch (gettype($Arg))
1228 $ArgString .= $Arg ?
"TRUE" :
"FALSE";
1237 $ArgString .=
'"<i>'.htmlspecialchars(substr($Arg, 0, 40))
1238 .((strlen($Arg) > 40) ?
"..." :
"").
'</i>"';
1244 $ArgString .= strtoupper(gettype($Arg));
1248 $ArgString .= get_class($Arg);
1251 case "unknown type":
1252 $ArgString .=
"UNKNOWN";
1257 $Loc[
"file"] = substr($Loc[
"file"], $PrefixLen);
1258 $LocString .=
" ";
1259 if (array_key_exists(
"class", $Loc))
1260 { $LocString .= $Loc[
"class"].
"::"; }
1261 $LocString .= $Loc[
"function"].
"(".$ArgString.
")"
1262 .
" - ".$Loc[
"file"].
":".$Loc[
"line"]
1265 print(
"<b>Trace:</b><br>\n".$LocString);
1268 return $this->QueryHandle;
1272 # define return values (numerical values correspond to MySQL error codes)
1273 define(
"DB_OKAY", 0);
1274 define(
"DB_ERROR", 1);
1275 define(
"DB_ACCESSDENIED", 2);
1276 define(
"DB_UNKNOWNDB", 3);
1277 define(
"DB_UNKNOWNTABLE", 4);
1278 define(
"DB_SYNTAXERROR", 5);
1279 define(
"DB_DBALREADYEXISTS", 6);
1280 define(
"DB_DBDOESNOTEXIST", 7);
1281 define(
"DB_DISKFULL", 8);
1283 # define value to designate omitted arguments (so DB values can be set to NULL)
1284 define(
"DB_NOVALUE",
"!-_-_-DB_NOVALUE-_-_-!");
1286 # MySQL error code mapping
1297 # date() format for SQL dates
1298 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 SELECT or SHOW query.
FetchRows($NumberOfRows=NULL)
Get specified number of database rows retrieved by most recent query.
static QueryDebugOutput($NewSetting)
Enable or disable debugging output for queries.
Query($QueryString, $FieldName="")
Query database (with caching if enabled).
FetchField($FieldName)
Pull next row from last DB query and get a specific value from that row.
FieldExists($TableName, $FieldName)
Get whether specified field exists in specified table.
static NumCacheHits()
Get the number of queries that have resulted in cache hits since program execution began...
FetchColumn($FieldName, $IndexFieldName=NULL)
Get all available values for specified database field retrieved by most recent query.
DBHostName()
Get host name of system on which database server resides.
NumRowsAffected()
Get number of rows affected by last INSERT, UPDATE, REPLACE, or DELETE query.
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.