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 00181 function SetQueryErrorsToIgnore($ErrorsToIgnore) 00182 { 00183 $this->ErrorsToIgnore = $ErrorsToIgnore; 00184 } 00185 00186 /*@)*/ /* Setup/Initialization */ /*@(*/ 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 /*@)*/ /* Data Manipulation */ /*@(*/ 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 /*@)*/ /* Miscellaneous */ 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 ?>