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 # call to this method after an unsuccessful query 00392 if (!is_resource($this->QueryHandle)) 00393 { 00394 return 0; 00395 } 00396 00397 # retrieve number of rows and return to caller 00398 return mysql_num_rows($this->QueryHandle); 00399 } 00400 } 00401 00406 function FetchRow() 00407 { 00408 # if caching is enabled and query was cached 00409 if (self::$CachingFlag && $this->GetResultsFromCache) 00410 { 00411 # if rows left to return 00412 if ($this->RowCounter < $this->NumRows) 00413 { 00414 # retrieve row from cache 00415 $Result = $this->QueryResults[$this->RowCounter]; 00416 00417 # increment row counter 00418 $this->RowCounter++; 00419 } 00420 else 00421 { 00422 # return nothing 00423 $Result = FALSE; 00424 } 00425 } 00426 else 00427 { 00428 # call to this method after successful query 00429 if (is_resource($this->QueryHandle)) 00430 { 00431 $Result = mysql_fetch_assoc($this->QueryHandle); 00432 } 00433 00434 # call to this method after unsuccessful query 00435 else 00436 { 00437 $Result = FALSE; 00438 } 00439 } 00440 00441 # return row to caller 00442 return $Result; 00443 } 00444 00450 function FetchRows($NumberOfRows = NULL) 00451 { 00452 # assume no rows will be returned 00453 $Result = array(); 00454 00455 # for each available row 00456 $RowsFetched = 0; 00457 while ((($RowsFetched < $NumberOfRows) || ($NumberOfRows == NULL)) && ($Row = $this->FetchRow())) 00458 { 00459 # add row to results 00460 $Result[] = $Row; 00461 $RowsFetched++; 00462 } 00463 00464 # return array of rows to caller 00465 return $Result; 00466 } 00467 00481 function FetchColumn($FieldName, $IndexFieldName = NULL) 00482 { 00483 $Array = array(); 00484 while ($Record = $this->FetchRow()) 00485 { 00486 if ($IndexFieldName != NULL) 00487 { 00488 $Array[$Record[$IndexFieldName]] = $Record[$FieldName]; 00489 } 00490 else 00491 { 00492 $Array[] = $Record[$FieldName]; 00493 } 00494 } 00495 return $Array; 00496 } 00497 00506 function FetchField($FieldName) 00507 { 00508 $Record = $this->FetchRow(); 00509 return isset($Record[$FieldName]) ? $Record[$FieldName] : NULL; 00510 } 00511 00519 function LastInsertId($TableName) 00520 { 00521 return (int)$this->Query( 00522 "SELECT LAST_INSERT_ID() AS InsertId FROM ".$TableName, 00523 "InsertId"); 00524 } 00525 00537 function UpdateValue( 00538 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord) 00539 { 00540 # expand condition if supplied 00541 if ($Condition != NULL) { $Condition = " WHERE ".$Condition; } 00542 00543 # read cached record from database if not already loaded 00544 if (!isset($CachedRecord)) 00545 { 00546 $this->Query("SELECT * FROM ".$TableName." ".$Condition); 00547 $CachedRecord = $this->FetchRow(); 00548 } 00549 00550 # if new value supplied 00551 if ($NewValue !== DB_NOVALUE) 00552 { 00553 # update value in database 00554 $this->Query("UPDATE $TableName SET $FieldName = " 00555 .(($NewValue === NULL) ? "NULL" : "'".addslashes($NewValue)."'") 00556 .$Condition); 00557 00558 # update value in cached record 00559 $CachedRecord[$FieldName] = $NewValue; 00560 } 00561 00562 # return value from cached record to caller 00563 return isset($CachedRecord[$FieldName]) 00564 ? $CachedRecord[$FieldName] : NULL; 00565 } 00566 00580 function UpdateIntValue( 00581 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord) 00582 { 00583 return $this->UpdateValue($TableName, $FieldName, 00584 (($NewValue === DB_NOVALUE) ? DB_NOVALUE : (int)$NewValue), 00585 $Condition, $CachedRecord); 00586 } 00587 00601 function UpdateFloatValue( 00602 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord) 00603 { 00604 return $this->UpdateValue($TableName, $FieldName, 00605 (($NewValue === DB_NOVALUE) ? DB_NOVALUE : (float)$NewValue), 00606 $Condition, $CachedRecord); 00607 } 00608 00609 /*@)*/ /* Data Manipulation */ /*@(*/ 00611 00618 function LogComment($String) 00619 { 00620 $this->Query("-- ".$String); 00621 } 00622 00629 function FieldExists($TableName, $FieldName) 00630 { 00631 $this->Query("DESC ".$TableName); 00632 while ($CurrentFieldName = $this->FetchField("Field")) 00633 { 00634 if ($CurrentFieldName == $FieldName) { return TRUE; } 00635 } 00636 return FALSE; 00637 } 00638 00644 static function QueryDebugOutput($NewSetting) 00645 { 00646 self::$QueryDebugOutputFlag = $NewSetting; 00647 } 00648 00654 static function NumQueries() 00655 { 00656 return self::$QueryCounter; 00657 } 00658 00665 static function NumCacheHits() 00666 { 00667 return self::$CachedQueryCounter; 00668 } 00669 00675 static function CacheHitRate() 00676 { 00677 if (self::$QueryCounter) 00678 { 00679 return (self::$CachedQueryCounter / self::$QueryCounter) * 100; 00680 } 00681 else 00682 { 00683 return 0; 00684 } 00685 } 00686 00687 /*@)*/ /* Miscellaneous */ 00688 00689 # ---- PRIVATE INTERFACE ------------------------------------------------- 00690 00691 protected $DBUserName; 00692 protected $DBPassword; 00693 protected $DBHostName; 00694 protected $DBName; 00695 00696 private $Handle; 00697 private $QueryHandle; 00698 private $QueryResults; 00699 private $RowCounter; 00700 private $NumRows; 00701 private $GetResultsFromCache; 00702 private $ErrorsToIgnore = NULL; 00703 private $ErrMsg = NULL; 00704 private $ErrNo = NULL; 00705 00706 private static $DisplayErrors = FALSE; 00707 00708 private static $GlobalDBUserName; 00709 private static $GlobalDBPassword; 00710 private static $GlobalDBHostName; 00711 private static $GlobalDBName; 00712 00713 # debug output flag 00714 private static $QueryDebugOutputFlag = FALSE; 00715 # flag for whether caching is turned on 00716 private static $CachingFlag = TRUE; 00717 # query result advanced caching flag 00718 private static $AdvancedCachingFlag = FALSE; 00719 # global cache for query results 00720 private static $QueryResultCache = array(); 00721 # stats counters 00722 private static $QueryCounter = 0; 00723 private static $CachedQueryCounter = 0; 00724 00725 # determine whether SQL statement is one that modifies data 00726 private function IsReadOnlyStatement($QueryString) 00727 { 00728 return preg_match("/^[ ]*SELECT /i", $QueryString) ? TRUE : FALSE; 00729 } 00730 00731 # try to determine table modified by statement (returns FALSE if unknown) 00732 private function TableModified($QueryString) 00733 { 00734 # assume we're not going to be able to determine table 00735 $TableName = FALSE; 00736 00737 # split query into pieces 00738 $QueryString = trim($QueryString); 00739 $Words = preg_split("/\s+/", $QueryString); 00740 00741 # if INSERT statement 00742 $WordIndex = 1; 00743 if (strtoupper($Words[0]) == "INSERT") 00744 { 00745 # skip over modifying keywords 00746 while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY") 00747 || (strtoupper($Words[$WordIndex]) == "DELAYED") 00748 || (strtoupper($Words[$WordIndex]) == "IGNORE") 00749 || (strtoupper($Words[$WordIndex]) == "INTO")) 00750 { 00751 $WordIndex++; 00752 } 00753 00754 # next word is table name 00755 $TableName = $Words[$WordIndex]; 00756 } 00757 # else if UPDATE statement 00758 elseif (strtoupper($Words[0]) == "UPDATE") 00759 { 00760 # skip over modifying keywords 00761 while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY") 00762 || (strtoupper($Words[$WordIndex]) == "IGNORE")) 00763 { 00764 $WordIndex++; 00765 } 00766 00767 # if word following next word is SET 00768 if (strtoupper($Words[$WordIndex + 1]) == "SET") 00769 { 00770 # next word is table name 00771 $TableName = $Words[$WordIndex]; 00772 } 00773 } 00774 # else if DELETE statement 00775 elseif (strtoupper($Words[0]) == "DELETE") 00776 { 00777 # skip over modifying keywords 00778 while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY") 00779 || (strtoupper($Words[$WordIndex]) == "IGNORE") 00780 || (strtoupper($Words[$WordIndex]) == "QUICK")) 00781 { 00782 $WordIndex++; 00783 } 00784 00785 # if next term is FROM 00786 if (strtoupper($Words[$WordIndex]) == "FROM") 00787 { 00788 # next word is table name 00789 $WordIndex++; 00790 $TableName = $Words[$WordIndex]; 00791 } 00792 } 00793 00794 # discard table name if it looks at all suspicious 00795 if ($TableName) 00796 { 00797 if (!preg_match("/[a-zA-Z0-9]+/", $TableName)) 00798 { 00799 $TableName = FALSE; 00800 } 00801 } 00802 00803 # return table name (or lack thereof) to caller 00804 return $TableName; 00805 } 00806 00807 # try to determine tables accessed by statement (returns FALSE if unknown) 00808 private function TablesAccessed($QueryString) 00809 { 00810 # assume we're not going to be able to determine tables 00811 $TableNames = FALSE; 00812 00813 # split query into pieces 00814 $QueryString = trim($QueryString); 00815 $Words = preg_split("/\s+/", $QueryString); 00816 $UQueryString = strtoupper($QueryString); 00817 $UWords = preg_split("/\s+/", $UQueryString); 00818 00819 # if SELECT statement 00820 if ($UWords[0] == "SELECT") 00821 { 00822 # keep going until we hit FROM or last word 00823 $WordIndex = 1; 00824 while (($UWords[$WordIndex] != "FROM") 00825 && strlen($UWords[$WordIndex])) 00826 { 00827 $WordIndex++; 00828 } 00829 00830 # if we hit FROM 00831 if ($UWords[$WordIndex] == "FROM") 00832 { 00833 # for each word after FROM 00834 $WordIndex++; 00835 while (strlen($UWords[$WordIndex])) 00836 { 00837 # if current word ends with comma 00838 if (preg_match("/,$/", $Words[$WordIndex])) 00839 { 00840 # strip off comma and add word to table name list 00841 $TableNames[] = substr($Words[$WordIndex], 0, -1); 00842 } 00843 else 00844 { 00845 # add word to table name list 00846 $TableNames[] = $Words[$WordIndex]; 00847 00848 # if next word is not comma 00849 $WordIndex++; 00850 if ($Words[$WordIndex] != ",") 00851 { 00852 # if word begins with comma 00853 if (preg_match("/^,/", $Words[$WordIndex])) 00854 { 00855 # strip off comma (NOTE: modifies $Words array!) 00856 $Words[$WordIndex] = substr($Words[$WordIndex], 1); 00857 00858 # decrement index so we start with this word next pass 00859 $WordIndex--; 00860 } 00861 else 00862 { 00863 # stop scanning words (non-basic JOINs not yet handled) 00864 break; 00865 } 00866 } 00867 } 00868 00869 # move to next word 00870 $WordIndex++; 00871 } 00872 } 00873 } 00874 00875 # discard table names if they look at all suspicious 00876 if ($TableNames) 00877 { 00878 foreach ($TableNames as $Name) 00879 { 00880 if (!preg_match("/^[a-zA-Z0-9]+$/", $Name)) 00881 { 00882 $TableNames = FALSE; 00883 break; 00884 } 00885 } 00886 } 00887 00888 # return table name (or lack thereof) to caller 00889 return $TableNames; 00890 } 00891 00892 private function RunQuery($QueryString) 00893 { 00894 if (self::$QueryDebugOutputFlag) { print("DB: $QueryString<br>\n"); } 00895 $this->QueryHandle = mysql_query($QueryString, $this->Handle); 00896 if (($this->QueryHandle === FALSE) && $this->ErrorsToIgnore) 00897 { 00898 foreach ($this->ErrorsToIgnore as $SqlPattern => $ErrMsgPattern) 00899 { 00900 if (preg_match($SqlPattern, $QueryString) 00901 && preg_match($ErrMsgPattern, mysql_error())) 00902 { 00903 $this->QueryHandle = TRUE; 00904 break; 00905 } 00906 } 00907 } 00908 00909 if ($this->QueryHandle === FALSE) 00910 { 00911 $this->ErrMsg = mysql_error(); 00912 $this->ErrNo = mysql_errno(); 00913 $this->NumRows = 0; 00914 if (self::$DisplayErrors) 00915 { 00916 print("<b>SQL Error:</b> <i>".$this->ErrMsg 00917 ."</i> (".$this->ErrNo.")<br/>\n"); 00918 print("<b>SQL Statement:</b> <i>" 00919 .htmlspecialchars($QueryString)."</i><br/>\n"); 00920 } 00921 } 00922 return $this->QueryHandle; 00923 } 00924 } 00925 00926 # define return values (numerical values correspond to MySQL error codes) 00927 define("DB_OKAY", 0); 00928 define("DB_ERROR", 1); 00929 define("DB_ACCESSDENIED", 2); 00930 define("DB_UNKNOWNDB", 3); 00931 define("DB_UNKNOWNTABLE", 4); 00932 define("DB_SYNTAXERROR", 5); 00933 define("DB_DBALREADYEXISTS", 6); 00934 define("DB_DBDOESNOTEXIST", 7); 00935 define("DB_DISKFULL", 8); 00936 00937 # define value to designate omitted arguments (so DB values can be set to NULL) 00938 define("DB_NOVALUE", "!-_-_-DB_NOVALUE-_-_-!"); 00939 00940 # MySQL error code mapping 00941 $APDBErrorCodeMappings = array( 00942 1045 => DB_ACCESSDENIED, 00943 1049 => DB_UNKNOWNDB, 00944 1046 => DB_UNKNOWNTABLE, 00945 1064 => DB_SYNTAXERROR, 00946 1007 => DB_DBALREADYEXISTS, # ? (not sure) 00947 1008 => DB_DBDOESNOTEXIST, # ? (not sure) 00948 1021 => DB_DISKFULL, # ? (not sure) 00949 ); 00950 00951 ?>