Axis--Database.php
Go to the documentation of this file.
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 /*@)*/ /* Setup/Initialization */ /*@(*/ 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 /*@)*/ /* Data Manipulation */ /*@(*/ 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 /*@)*/ /* Miscellaneous */ 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 ?>