00001 <?PHP
00002
00003 #
00004 # Axis--Database.php
00005 # A Simple SQL Database Abstraction Object
00006 #
00007 # Copyright 1999-2002 Axis Data
00008 # This code is free software that can be used or redistributed under the
00009 # terms of Version 2 of the GNU General Public License, as published by the
00010 # Free Software Foundation (http://www.fsf.org).
00011 #
00012 # Author: Edward Almasy (almasy@axisdata.com)
00013 #
00014 # Part of the AxisPHP library v1.2.5
00015 # For more information see http://www.axisdata.com/AxisPHP/
00016 #
00017
00022 class Database {
00023
00024 # ---- PUBLIC INTERFACE --------------------------------------------------
00025
00027
00039 function Database(
00040 $UserName = NULL, $Password = NULL, $DatabaseName = NULL, $HostName = NULL)
00041 {
00042 # save DB access values
00043 $this->DBUserName = $UserName ? $UserName : self::$GlobalDBUserName;
00044 $this->DBPassword = $Password ? $Password : self::$GlobalDBPassword;
00045 $this->DBHostName = $HostName ? $HostName :
00046 (isset(self::$GlobalDBHostName) ? self::$GlobalDBHostName
00047 : "localhost");
00048 $this->DBName = $DatabaseName ? $DatabaseName : self::$GlobalDBName;
00049
00050 # open connection to DB server
00051 $this->Handle = mysql_connect(
00052 $this->DBHostName, $this->DBUserName, $this->DBPassword)
00053 or die("Could not connect to database: ".mysql_error());
00054
00055 # select DB
00056 mysql_select_db($this->DBName, $this->Handle)
00057 or die(mysql_error($this->Handle));
00058 }
00059
00064 function __sleep()
00065 {
00066 return array("DBUserName", "DBPassword", "DBHostName", "DBName");
00067 }
00071 function __wakeup()
00072 {
00073 # open connection to DB server
00074 $this->Handle = mysql_connect(
00075 $this->DBHostName, $this->DBUserName, $this->DBPassword)
00076 or die("could not connect to database");
00077
00078 # select DB
00079 mysql_select_db($this->DBName, $this->Handle)
00080 or die(mysql_error($this->Handle));
00081 }
00090 static function SetGlobalServerInfo($UserName, $Password, $HostName = "localhost")
00091 {
00092 self::$GlobalDBUserName = $UserName;
00093 self::$GlobalDBPassword = $Password;
00094 self::$GlobalDBHostName = $HostName;
00095 }
00096
00101 static function SetGlobalDatabaseName($DatabaseName)
00102 {
00103 self::$GlobalDBName = $DatabaseName;
00104 }
00105
00111 function DBHostName() { return $this->DBHostName; }
00112
00118 function DBName() { return $this->DBName; }
00119
00125 function DBUserName() { return $this->DBUserName; }
00126
00134 static function Caching($NewSetting = NULL)
00135 {
00136 # if cache setting has changed
00137 if (($NewSetting !== NULL) && ($NewSetting != self::$CachingFlag))
00138 {
00139 # save new setting
00140 self::$CachingFlag = $NewSetting;
00141
00142 # clear any existing cached results
00143 self::$QueryResultCache = array();
00144 }
00145
00146 # return current setting to caller
00147 return self::$CachingFlag;
00148 }
00149
00159 static function AdvancedCaching($NewSetting = NULL)
00160 {
00161 if ($NewSetting !== NULL)
00162 {
00163 self::$AdvancedCachingFlag = $NewSetting;
00164 }
00165 return self::$AdvancedCachingFlag;
00166 }
00167
00181 function SetQueryErrorsToIgnore($ErrorsToIgnore)
00182 {
00183 $this->ErrorsToIgnore = $ErrorsToIgnore;
00184 }
00185
00186
00188
00195 function Query($QueryString, $FieldName = "")
00196 {
00197 # if caching is enabled
00198 if (self::$CachingFlag)
00199 {
00200 # if SQL statement is read-only
00201 if ($this->IsReadOnlyStatement($QueryString))
00202 {
00203 # if we have statement in cache
00204 if (isset(self::$QueryResultCache[$QueryString]["NumRows"]))
00205 {
00206 if (self::$QueryDebugOutputFlag)
00207 { print("DB-C: $QueryString<br>\n"); }
00208
00209 # make sure query result looks okay
00210 $this->QueryHandle = TRUE;
00211
00212 # increment cache hit counter
00213 self::$CachedQueryCounter++;
00214
00215 # make local copy of results
00216 $this->QueryResults = self::$QueryResultCache[$QueryString];
00217 $this->NumRows = self::$QueryResultCache[$QueryString]["NumRows"];
00218
00219 # set flag to indicate that results should be retrieved from cache
00220 $this->GetResultsFromCache = TRUE;
00221 }
00222 else
00223 {
00224 # execute SQL statement
00225 $this->QueryHandle = $this->RunQuery($QueryString);
00226 if ($this->QueryHandle === FALSE) { return FALSE; }
00227
00228 # save number of rows in result
00229 $this->NumRows = mysql_num_rows($this->QueryHandle);
00230
00231 # if too many rows to cache
00232 if ($this->NumRows >= 50)
00233 {
00234 # set flag to indicate that query results should not be retrieved from cache
00235 $this->GetResultsFromCache = FALSE;
00236 }
00237 else
00238 {
00239 # if advanced caching is enabled
00240 if (self::$AdvancedCachingFlag)
00241 {
00242 # save tables accessed by query
00243 self::$QueryResultCache[$QueryString]["TablesAccessed"] = $this->TablesAccessed($QueryString);
00244 }
00245
00246 # if rows found
00247 if ($this->NumRows > 0)
00248 {
00249 # load query results
00250 for ($Row = 0; $Row < $this->NumRows; $Row++)
00251 {
00252 $this->QueryResults[$Row] = mysql_fetch_assoc($this->QueryHandle);
00253 }
00254
00255 # cache query results
00256 self::$QueryResultCache[$QueryString] = $this->QueryResults;
00257 }
00258 else
00259 {
00260 # clear local query results
00261 unset($this->QueryResults);
00262 }
00263
00264 # cache number of rows
00265 self::$QueryResultCache[$QueryString]["NumRows"] = $this->NumRows;
00266
00267 # set flag to indicate that query results should be retrieved from cache
00268 $this->GetResultsFromCache = TRUE;
00269 }
00270 }
00271 }
00272 else
00273 {
00274 # if advanced caching is enabled
00275 if (self::$AdvancedCachingFlag)
00276 {
00277 # if table modified by statement is known
00278 $TableModified = $this->TableModified($QueryString);
00279 if ($TableModified)
00280 {
00281 # for each cached query
00282 foreach (self::$QueryResultCache as $CachedQueryString => $CachedQueryResult)
00283 {
00284 # if we know what tables were accessed
00285 if ($CachedQueryResult["TablesAccessed"])
00286 {
00287 # if tables accessed include the one we may modify
00288 if (in_array($TableModified, $CachedQueryResult["TablesAccessed"]))
00289 {
00290 # clear cached query results
00291 unset($GLOBALS["APDBQueryResultCache"][$CachedQueryString]);
00292 }
00293 }
00294 else
00295 {
00296 # clear cached query results
00297 unset($GLOBALS["APDBQueryResultCache"][$CachedQueryString]);
00298 }
00299 }
00300 }
00301 else
00302 {
00303 # clear entire query result cache
00304 self::$QueryResultCache = array();
00305 }
00306 }
00307 else
00308 {
00309 # clear entire query result cache
00310 self::$QueryResultCache = array();
00311 }
00312
00313 # execute SQL statement
00314 $this->QueryHandle = $this->RunQuery($QueryString);
00315 if ($this->QueryHandle === FALSE) { return FALSE; }
00316
00317 # set flag to indicate that query results should not be retrieved from cache
00318 $this->GetResultsFromCache = FALSE;
00319 }
00320
00321 # reset row counter
00322 $this->RowCounter = 0;
00323
00324 # increment query counter
00325 self::$QueryCounter++;
00326 }
00327 else
00328 {
00329 # execute SQL statement
00330 $this->QueryHandle = $this->RunQuery($QueryString);
00331 if ($this->QueryHandle === FALSE) { return FALSE; }
00332 }
00333
00334 if (($FieldName != "") && ($this->QueryHandle != FALSE))
00335 {
00336 return $this->FetchField($FieldName);
00337 }
00338 else
00339 {
00340 return $this->QueryHandle;
00341 }
00342 }
00343
00349 function QueryErrMsg()
00350 {
00351 return $this->ErrMsg;
00352 }
00353
00359 function QueryErrNo()
00360 {
00361 return $this->ErrNo;
00362 }
00363
00370 static function DisplayQueryErrors($NewValue = NULL)
00371 {
00372 if ($NewValue !== NULL) { self::$DisplayErrors = $NewValue; }
00373 return self::$DisplayErrors;
00374 }
00375
00380 function NumRowsSelected()
00381 {
00382 # if caching is enabled and query was cached
00383 if (self::$CachingFlag && $this->GetResultsFromCache)
00384 {
00385 # return cached number of rows to caller
00386 return $this->NumRows;
00387 }
00388 else
00389 {
00390 # retrieve number of rows and return to caller
00391 return mysql_num_rows($this->QueryHandle);
00392 }
00393 }
00394
00399 function FetchRow()
00400 {
00401 # if caching is enabled and query was cached
00402 if (self::$CachingFlag && $this->GetResultsFromCache)
00403 {
00404 # if rows left to return
00405 if ($this->RowCounter < $this->NumRows)
00406 {
00407 # retrieve row from cache
00408 $Result = $this->QueryResults[$this->RowCounter];
00409
00410 # increment row counter
00411 $this->RowCounter++;
00412 }
00413 else
00414 {
00415 # return nothing
00416 $Result = FALSE;
00417 }
00418 }
00419 else
00420 {
00421 # retrieve row from DB
00422 $Result = mysql_fetch_assoc($this->QueryHandle);
00423 }
00424
00425 # return row to caller
00426 return $Result;
00427 }
00428
00434 function FetchRows($NumberOfRows = NULL)
00435 {
00436 # assume no rows will be returned
00437 $Result = array();
00438
00439 # for each available row
00440 $RowsFetched = 0;
00441 while ((($RowsFetched < $NumberOfRows) || ($NumberOfRows == NULL)) && ($Row = $this->FetchRow()))
00442 {
00443 # add row to results
00444 $Result[] = $Row;
00445 $RowsFetched++;
00446 }
00447
00448 # return array of rows to caller
00449 return $Result;
00450 }
00451
00465 function FetchColumn($FieldName, $IndexFieldName = NULL)
00466 {
00467 $Array = array();
00468 while ($Record = $this->FetchRow())
00469 {
00470 if ($IndexFieldName != NULL)
00471 {
00472 $Array[$Record[$IndexFieldName]] = $Record[$FieldName];
00473 }
00474 else
00475 {
00476 $Array[] = $Record[$FieldName];
00477 }
00478 }
00479 return $Array;
00480 }
00481
00490 function FetchField($FieldName)
00491 {
00492 $Record = $this->FetchRow();
00493 return isset($Record[$FieldName]) ? $Record[$FieldName] : NULL;
00494 }
00495
00503 function LastInsertId($TableName)
00504 {
00505 return (int)$this->Query(
00506 "SELECT LAST_INSERT_ID() AS InsertId FROM ".$TableName,
00507 "InsertId");
00508 }
00509
00521 function UpdateValue(
00522 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
00523 {
00524 # expand condition if supplied
00525 if ($Condition != NULL) { $Condition = " WHERE ".$Condition; }
00526
00527 # read cached record from database if not already loaded
00528 if (!isset($CachedRecord))
00529 {
00530 $this->Query("SELECT * FROM ".$TableName." ".$Condition);
00531 $CachedRecord = $this->FetchRow();
00532 }
00533
00534 # if new value supplied
00535 if ($NewValue !== DB_NOVALUE)
00536 {
00537 # update value in database
00538 $this->Query("UPDATE $TableName SET $FieldName = "
00539 .(($NewValue === NULL) ? "NULL" : "'".addslashes($NewValue)."'")
00540 .$Condition);
00541
00542 # update value in cached record
00543 $CachedRecord[$FieldName] = $NewValue;
00544 }
00545
00546 # return value from cached record to caller
00547 return isset($CachedRecord[$FieldName])
00548 ? $CachedRecord[$FieldName] : NULL;
00549 }
00550
00551
00553
00560 function LogComment($String)
00561 {
00562 $this->Query("-- ".$String);
00563 }
00564
00571 function FieldExists($TableName, $FieldName)
00572 {
00573 $this->Query("DESC ".$TableName);
00574 while ($CurrentFieldName = $this->FetchField("Field"))
00575 {
00576 if ($CurrentFieldName == $FieldName) { return TRUE; }
00577 }
00578 return FALSE;
00579 }
00580
00586 static function QueryDebugOutput($NewSetting)
00587 {
00588 self::$QueryDebugOutputFlag = $NewSetting;
00589 }
00590
00596 static function NumQueries()
00597 {
00598 return self::$QueryCounter;
00599 }
00600
00607 static function NumCacheHits()
00608 {
00609 return self::$CachedQueryCounter;
00610 }
00611
00617 static function CacheHitRate()
00618 {
00619 if (self::$QueryCounter)
00620 {
00621 return (self::$CachedQueryCounter / self::$QueryCounter) * 100;
00622 }
00623 else
00624 {
00625 return 0;
00626 }
00627 }
00628
00629
00630
00631 # ---- PRIVATE INTERFACE -------------------------------------------------
00632
00633 protected $DBUserName;
00634 protected $DBPassword;
00635 protected $DBHostName;
00636 protected $DBName;
00637
00638 private $Handle;
00639 private $QueryHandle;
00640 private $QueryResults;
00641 private $RowCounter;
00642 private $NumRows;
00643 private $GetResultsFromCache;
00644 private $ErrorsToIgnore = NULL;
00645 private $ErrMsg = NULL;
00646 private $ErrNo = NULL;
00647
00648 private static $DisplayErrors = FALSE;
00649
00650 private static $GlobalDBUserName;
00651 private static $GlobalDBPassword;
00652 private static $GlobalDBHostName;
00653 private static $GlobalDBName;
00654
00655 # debug output flag
00656 private static $QueryDebugOutputFlag = FALSE;
00657 # flag for whether caching is turned on
00658 private static $CachingFlag = TRUE;
00659 # query result advanced caching flag
00660 private static $AdvancedCachingFlag = FALSE;
00661 # global cache for query results
00662 private static $QueryResultCache = array();
00663 # stats counters
00664 private static $QueryCounter = 0;
00665 private static $CachedQueryCounter = 0;
00666
00667 # determine whether SQL statement is one that modifies data
00668 private function IsReadOnlyStatement($QueryString)
00669 {
00670 return preg_match("/^[ ]*SELECT /i", $QueryString) ? TRUE : FALSE;
00671 }
00672
00673 # try to determine table modified by statement (returns FALSE if unknown)
00674 private function TableModified($QueryString)
00675 {
00676 # assume we're not going to be able to determine table
00677 $TableName = FALSE;
00678
00679 # split query into pieces
00680 $QueryString = trim($QueryString);
00681 $Words = preg_split("/\s+/", $QueryString);
00682
00683 # if INSERT statement
00684 $WordIndex = 1;
00685 if (strtoupper($Words[0]) == "INSERT")
00686 {
00687 # skip over modifying keywords
00688 while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
00689 || (strtoupper($Words[$WordIndex]) == "DELAYED")
00690 || (strtoupper($Words[$WordIndex]) == "IGNORE")
00691 || (strtoupper($Words[$WordIndex]) == "INTO"))
00692 {
00693 $WordIndex++;
00694 }
00695
00696 # next word is table name
00697 $TableName = $Words[$WordIndex];
00698 }
00699 # else if UPDATE statement
00700 elseif (strtoupper($Words[0]) == "UPDATE")
00701 {
00702 # skip over modifying keywords
00703 while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
00704 || (strtoupper($Words[$WordIndex]) == "IGNORE"))
00705 {
00706 $WordIndex++;
00707 }
00708
00709 # if word following next word is SET
00710 if (strtoupper($Words[$WordIndex + 1]) == "SET")
00711 {
00712 # next word is table name
00713 $TableName = $Words[$WordIndex];
00714 }
00715 }
00716 # else if DELETE statement
00717 elseif (strtoupper($Words[0]) == "DELETE")
00718 {
00719 # skip over modifying keywords
00720 while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
00721 || (strtoupper($Words[$WordIndex]) == "IGNORE")
00722 || (strtoupper($Words[$WordIndex]) == "QUICK"))
00723 {
00724 $WordIndex++;
00725 }
00726
00727 # if next term is FROM
00728 if (strtoupper($Words[$WordIndex]) == "FROM")
00729 {
00730 # next word is table name
00731 $WordIndex++;
00732 $TableName = $Words[$WordIndex];
00733 }
00734 }
00735
00736 # discard table name if it looks at all suspicious
00737 if ($TableName)
00738 {
00739 if (!preg_match("/[a-zA-Z0-9]+/", $TableName))
00740 {
00741 $TableName = FALSE;
00742 }
00743 }
00744
00745 # return table name (or lack thereof) to caller
00746 return $TableName;
00747 }
00748
00749 # try to determine tables accessed by statement (returns FALSE if unknown)
00750 private function TablesAccessed($QueryString)
00751 {
00752 # assume we're not going to be able to determine tables
00753 $TableNames = FALSE;
00754
00755 # split query into pieces
00756 $QueryString = trim($QueryString);
00757 $Words = preg_split("/\s+/", $QueryString);
00758 $UQueryString = strtoupper($QueryString);
00759 $UWords = preg_split("/\s+/", $UQueryString);
00760
00761 # if SELECT statement
00762 if ($UWords[0] == "SELECT")
00763 {
00764 # keep going until we hit FROM or last word
00765 $WordIndex = 1;
00766 while (($UWords[$WordIndex] != "FROM")
00767 && strlen($UWords[$WordIndex]))
00768 {
00769 $WordIndex++;
00770 }
00771
00772 # if we hit FROM
00773 if ($UWords[$WordIndex] == "FROM")
00774 {
00775 # for each word after FROM
00776 $WordIndex++;
00777 while (strlen($UWords[$WordIndex]))
00778 {
00779 # if current word ends with comma
00780 if (preg_match("/,$/", $Words[$WordIndex]))
00781 {
00782 # strip off comma and add word to table name list
00783 $TableNames[] = substr($Words[$WordIndex], 0, -1);
00784 }
00785 else
00786 {
00787 # add word to table name list
00788 $TableNames[] = $Words[$WordIndex];
00789
00790 # if next word is not comma
00791 $WordIndex++;
00792 if ($Words[$WordIndex] != ",")
00793 {
00794 # if word begins with comma
00795 if (preg_match("/^,/", $Words[$WordIndex]))
00796 {
00797 # strip off comma (NOTE: modifies $Words array!)
00798 $Words[$WordIndex] = substr($Words[$WordIndex], 1);
00799
00800 # decrement index so we start with this word next pass
00801 $WordIndex--;
00802 }
00803 else
00804 {
00805 # stop scanning words (non-basic JOINs not yet handled)
00806 break;
00807 }
00808 }
00809 }
00810
00811 # move to next word
00812 $WordIndex++;
00813 }
00814 }
00815 }
00816
00817 # discard table names if they look at all suspicious
00818 if ($TableNames)
00819 {
00820 foreach ($TableNames as $Name)
00821 {
00822 if (!preg_match("/^[a-zA-Z0-9]+$/", $Name))
00823 {
00824 $TableNames = FALSE;
00825 break;
00826 }
00827 }
00828 }
00829
00830 # return table name (or lack thereof) to caller
00831 return $TableNames;
00832 }
00833
00834 private function RunQuery($QueryString)
00835 {
00836 if (self::$QueryDebugOutputFlag) { print("DB: $QueryString<br>\n"); }
00837 $this->QueryHandle = mysql_query($QueryString, $this->Handle);
00838 if (($this->QueryHandle === FALSE) && $this->ErrorsToIgnore)
00839 {
00840 foreach ($this->ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
00841 {
00842 if (preg_match($SqlPattern, $QueryString)
00843 && preg_match($ErrMsgPattern, mysql_error()))
00844 {
00845 $this->QueryHandle = TRUE;
00846 break;
00847 }
00848 }
00849 }
00850
00851 if ($this->QueryHandle === FALSE)
00852 {
00853 $this->ErrMsg = mysql_error();
00854 $this->ErrNo = mysql_errno();
00855 $this->NumRows = 0;
00856 if (self::$DisplayErrors)
00857 {
00858 print("<b>SQL Error:</b> <i>".$this->ErrMsg
00859 ."</i> (".$this->ErrNo.")<br/>\n");
00860 print("<b>SQL Statement:</b> <i>"
00861 .htmlspecialchars($QueryString)."</i><br/>\n");
00862 }
00863 }
00864 return $this->QueryHandle;
00865 }
00866 }
00867
00868 # define return values (numerical values correspond to MySQL error codes)
00869 define("DB_OKAY", 0);
00870 define("DB_ERROR", 1);
00871 define("DB_ACCESSDENIED", 2);
00872 define("DB_UNKNOWNDB", 3);
00873 define("DB_UNKNOWNTABLE", 4);
00874 define("DB_SYNTAXERROR", 5);
00875 define("DB_DBALREADYEXISTS", 6);
00876 define("DB_DBDOESNOTEXIST", 7);
00877 define("DB_DISKFULL", 8);
00878
00879 # define value to designate omitted arguments (so DB values can be set to NULL)
00880 define("DB_NOVALUE", "!-_-_-DB_NOVALUE-_-_-!");
00881
00882 # MySQL error code mapping
00883 $APDBErrorCodeMappings = array(
00884 1045 => DB_ACCESSDENIED,
00885 1049 => DB_UNKNOWNDB,
00886 1046 => DB_UNKNOWNTABLE,
00887 1064 => DB_SYNTAXERROR,
00888 1007 => DB_DBALREADYEXISTS, # ? (not sure)
00889 1008 => DB_DBDOESNOTEXIST, # ? (not sure)
00890 1021 => DB_DISKFULL, # ? (not sure)
00891 );
00892
00893 ?>