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 # 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
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
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) { $QueryStartTime = microtime(TRUE); }
00895 $this->QueryHandle = mysql_query($QueryString, $this->Handle);
00896 if (self::$QueryDebugOutputFlag)
00897 {
00898 print "DB: ".$QueryString." ["
00899 .sprintf("%.2f", microtime(TRUE) - $QueryStartTime)
00900 ."s]"."<br>\n";
00901 }
00902 if (($this->QueryHandle === FALSE) && $this->ErrorsToIgnore)
00903 {
00904 foreach ($this->ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
00905 {
00906 if (preg_match($SqlPattern, $QueryString)
00907 && preg_match($ErrMsgPattern, mysql_error()))
00908 {
00909 $this->QueryHandle = TRUE;
00910 break;
00911 }
00912 }
00913 }
00914
00915 if ($this->QueryHandle === FALSE)
00916 {
00917 $this->ErrMsg = mysql_error();
00918 $this->ErrNo = mysql_errno();
00919 $this->NumRows = 0;
00920 if (self::$DisplayErrors)
00921 {
00922 print("<b>SQL Error:</b> <i>".$this->ErrMsg
00923 ."</i> (".$this->ErrNo.")<br/>\n");
00924 print("<b>SQL Statement:</b> <i>"
00925 .htmlspecialchars($QueryString)."</i><br/>\n");
00926 }
00927 }
00928 return $this->QueryHandle;
00929 }
00930 }
00931
00932 # define return values (numerical values correspond to MySQL error codes)
00933 define("DB_OKAY", 0);
00934 define("DB_ERROR", 1);
00935 define("DB_ACCESSDENIED", 2);
00936 define("DB_UNKNOWNDB", 3);
00937 define("DB_UNKNOWNTABLE", 4);
00938 define("DB_SYNTAXERROR", 5);
00939 define("DB_DBALREADYEXISTS", 6);
00940 define("DB_DBDOESNOTEXIST", 7);
00941 define("DB_DISKFULL", 8);
00942
00943 # define value to designate omitted arguments (so DB values can be set to NULL)
00944 define("DB_NOVALUE", "!-_-_-DB_NOVALUE-_-_-!");
00945
00946 # MySQL error code mapping
00947 $APDBErrorCodeMappings = array(
00948 1045 => DB_ACCESSDENIED,
00949 1049 => DB_UNKNOWNDB,
00950 1046 => DB_UNKNOWNTABLE,
00951 1064 => DB_SYNTAXERROR,
00952 1007 => DB_DBALREADYEXISTS, # ? (not sure)
00953 1008 => DB_DBDOESNOTEXIST, # ? (not sure)
00954 1021 => DB_DISKFULL, # ? (not sure)
00955 );
00956
00957 ?>