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