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 : Database::$GlobalDBUserName;
00044 $this->DBPassword = $Password ? $Password : Database::$GlobalDBPassword;
00045 $this->DBHostName = $HostName ? $HostName :
00046 (isset(Database::$GlobalDBHostName) ? Database::$GlobalDBHostName
00047 : "localhost");
00048 $this->DBName = $DatabaseName ? $DatabaseName : Database::$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");
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 Database::$GlobalDBUserName = $UserName;
00093 Database::$GlobalDBPassword = $Password;
00094 Database::$GlobalDBHostName = $HostName;
00095 }
00096
00101 static function SetGlobalDatabaseName($DatabaseName)
00102 {
00103 Database::$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 != Database::$CachingFlag))
00138 {
00139 # save new setting
00140 Database::$CachingFlag = $NewSetting;
00141
00142 # clear any existing cached results
00143 Database::$QueryResultCache = array();
00144 }
00145
00146 # return current setting to caller
00147 return Database::$CachingFlag;
00148 }
00149
00159 static function AdvancedCaching($NewSetting = NULL)
00160 {
00161 if ($NewSetting !== NULL)
00162 {
00163 Database::$AdvancedCachingFlag = $NewSetting;
00164 }
00165 return Database::$AdvancedCachingFlag;
00166 }
00167
00168
00170
00177 function Query($QueryString, $FieldName = "")
00178 {
00179 # if caching is enabled
00180 if (Database::$CachingFlag)
00181 {
00182 # if SQL statement is read-only
00183 if ($this->IsReadOnlyStatement($QueryString))
00184 {
00185 # if we have statement in cache
00186 if (isset(Database::$QueryResultCache[$QueryString]["NumRows"]))
00187 {
00188 if (Database::$QueryDebugOutputFlag) { print("DB-C: $QueryString<br>\n"); }
00189
00190 # make sure query result looks okay
00191 $this->QueryHandle = TRUE;
00192
00193 # increment cache hit counter
00194 Database::$CachedQueryCounter++;
00195
00196 # make local copy of results
00197 $this->QueryResults = Database::$QueryResultCache[$QueryString];
00198 $this->NumRows = Database::$QueryResultCache[$QueryString]["NumRows"];
00199
00200 # set flag to indicate that query results should be retrieved from cache
00201 $this->GetResultsFromCache = TRUE;
00202 }
00203 else
00204 {
00205 # execute SQL statement
00206 if (Database::$QueryDebugOutputFlag) { print("DB: $QueryString<br>\n"); }
00207 $this->QueryHandle = mysql_query($QueryString, $this->Handle)
00208 or die("<br>SQL Statement: <i>".$QueryString."</i><br>\nSQL Error: <i>".mysql_error($this->Handle)."</i>\n");
00209
00210 # save number of rows in result
00211 $this->NumRows = mysql_num_rows($this->QueryHandle);
00212
00213 # if too many rows to cache
00214 if ($this->NumRows >= 50)
00215 {
00216 # set flag to indicate that query results should not be retrieved from cache
00217 $this->GetResultsFromCache = FALSE;
00218 }
00219 else
00220 {
00221 # if advanced caching is enabled
00222 if (Database::$AdvancedCachingFlag)
00223 {
00224 # save tables accessed by query
00225 Database::$QueryResultCache[$QueryString]["TablesAccessed"] = $this->TablesAccessed($QueryString);
00226 }
00227
00228 # if rows found
00229 if ($this->NumRows > 0)
00230 {
00231 # load query results
00232 for ($Row = 0; $Row < $this->NumRows; $Row++)
00233 {
00234 $this->QueryResults[$Row] = mysql_fetch_assoc($this->QueryHandle);
00235 }
00236
00237 # cache query results
00238 Database::$QueryResultCache[$QueryString] = $this->QueryResults;
00239 }
00240 else
00241 {
00242 # clear local query results
00243 unset($this->QueryResults);
00244 }
00245
00246 # cache number of rows
00247 Database::$QueryResultCache[$QueryString]["NumRows"] = $this->NumRows;
00248
00249 # set flag to indicate that query results should be retrieved from cache
00250 $this->GetResultsFromCache = TRUE;
00251 }
00252 }
00253 }
00254 else
00255 {
00256 # if advanced caching is enabled
00257 if (Database::$AdvancedCachingFlag)
00258 {
00259 # if table modified by statement is known
00260 $TableModified = $this->TableModified($QueryString);
00261 if ($TableModified)
00262 {
00263 # for each cached query
00264 foreach (Database::$QueryResultCache as $CachedQueryString => $CachedQueryResult)
00265 {
00266 # if we know what tables were accessed
00267 if ($CachedQueryResult["TablesAccessed"])
00268 {
00269 # if tables accessed include the one we may modify
00270 if (in_array($TableModified, $CachedQueryResult["TablesAccessed"]))
00271 {
00272 # clear cached query results
00273 unset($GLOBALS["APDBQueryResultCache"][$CachedQueryString]);
00274 }
00275 }
00276 else
00277 {
00278 # clear cached query results
00279 unset($GLOBALS["APDBQueryResultCache"][$CachedQueryString]);
00280 }
00281 }
00282 }
00283 else
00284 {
00285 # clear entire query result cache
00286 Database::$QueryResultCache = array();
00287 }
00288 }
00289 else
00290 {
00291 # clear entire query result cache
00292 Database::$QueryResultCache = array();
00293 }
00294
00295 # execute SQL statement
00296 if (Database::$QueryDebugOutputFlag) { print("DB: $QueryString<br>\n"); }
00297 $this->QueryHandle = mysql_query($QueryString, $this->Handle)
00298 or die("<br>SQL Statement: <i>".$QueryString."</i><br>\nSQL Error: <i>".mysql_error($this->Handle)."</i>\n");
00299
00300 # set flag to indicate that query results should not be retrieved from cache
00301 $this->GetResultsFromCache = FALSE;
00302 }
00303
00304 # reset row counter
00305 $this->RowCounter = 0;
00306
00307 # increment query counter
00308 Database::$QueryCounter++;
00309 }
00310 else
00311 {
00312 # execute SQL statement
00313 if (Database::$QueryDebugOutputFlag) { print("DB: $QueryString<br>\n"); }
00314 $this->QueryHandle = mysql_query($QueryString, $this->Handle)
00315 or die(mysql_error($this->Handle));
00316 }
00317
00318 if (($FieldName != "") && ($this->QueryHandle != FALSE))
00319 {
00320 return $this->FetchField($FieldName);
00321 }
00322 else
00323 {
00324 return $this->QueryHandle;
00325 }
00326 }
00327
00332 function NumRowsSelected()
00333 {
00334 # if caching is enabled and query was cached
00335 if (Database::$CachingFlag && $this->GetResultsFromCache)
00336 {
00337 # return cached number of rows to caller
00338 return $this->NumRows;
00339 }
00340 else
00341 {
00342 # retrieve number of rows and return to caller
00343 return mysql_num_rows($this->QueryHandle);
00344 }
00345 }
00346
00351 function FetchRow()
00352 {
00353 # if caching is enabled and query was cached
00354 if (Database::$CachingFlag && $this->GetResultsFromCache)
00355 {
00356 # if rows left to return
00357 if ($this->RowCounter < $this->NumRows)
00358 {
00359 # retrieve row from cache
00360 $Result = $this->QueryResults[$this->RowCounter];
00361
00362 # increment row counter
00363 $this->RowCounter++;
00364 }
00365 else
00366 {
00367 # return nothing
00368 $Result = FALSE;
00369 }
00370 }
00371 else
00372 {
00373 # retrieve row from DB
00374 $Result = mysql_fetch_assoc($this->QueryHandle);
00375 }
00376
00377 # return row to caller
00378 return $Result;
00379 }
00380
00386 function FetchRows($NumberOfRows = NULL)
00387 {
00388 # assume no rows will be returned
00389 $Result = array();
00390
00391 # for each available row
00392 $RowsFetched = 0;
00393 while ((($RowsFetched < $NumberOfRows) || ($NumberOfRows == NULL)) && ($Row = $this->FetchRow()))
00394 {
00395 # add row to results
00396 $Result[] = $Row;
00397 $RowsFetched++;
00398 }
00399
00400 # return array of rows to caller
00401 return $Result;
00402 }
00403
00417 function FetchColumn($FieldName, $IndexFieldName = NULL)
00418 {
00419 $Array = array();
00420 while ($Record = $this->FetchRow())
00421 {
00422 if ($IndexFieldName != NULL)
00423 {
00424 $Array[$Record[$IndexFieldName]] = $Record[$FieldName];
00425 }
00426 else
00427 {
00428 $Array[] = $Record[$FieldName];
00429 }
00430 }
00431 return $Array;
00432 }
00433
00442 function FetchField($FieldName)
00443 {
00444 $Record = $this->FetchRow();
00445 return isset($Record[$FieldName]) ? $Record[$FieldName] : NULL;
00446 }
00447
00455 function LastInsertId($TableName)
00456 {
00457 return (int)$this->Query(
00458 "SELECT LAST_INSERT_ID() AS InsertId FROM ".$TableName,
00459 "InsertId");
00460 }
00461
00473 function UpdateValue(
00474 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
00475 {
00476 # expand condition if supplied
00477 if ($Condition != NULL) { $Condition = " WHERE ".$Condition; }
00478
00479 # read cached record from database if not already loaded
00480 if (!isset($CachedRecord))
00481 {
00482 $this->Query("SELECT * FROM ".$TableName." ".$Condition);
00483 $CachedRecord = $this->FetchRow();
00484 }
00485
00486 # if new value supplied
00487 if ($NewValue !== DB_NOVALUE)
00488 {
00489 # update value in database
00490 $this->Query("UPDATE $TableName SET $FieldName = "
00491 .(($NewValue === NULL) ? "NULL" : "'".addslashes($NewValue)."'")
00492 .$Condition);
00493
00494 # update value in cached record
00495 $CachedRecord[$FieldName] = $NewValue;
00496 }
00497
00498 # return value from cached record to caller
00499 return isset($CachedRecord[$FieldName])
00500 ? $CachedRecord[$FieldName] : NULL;
00501 }
00502
00503
00505
00512 function LogComment($String)
00513 {
00514 $this->Query("-- ".$String);
00515 }
00516
00523 function FieldExists($TableName, $FieldName)
00524 {
00525 $this->Query("DESC ".$TableName);
00526 while ($CurrentFieldName = $this->FetchField("Field"))
00527 {
00528 if ($CurrentFieldName == $FieldName) { return TRUE; }
00529 }
00530 return FALSE;
00531 }
00532
00538 static function QueryDebugOutput($NewSetting)
00539 {
00540 Database::$QueryDebugOutputFlag = $NewSetting;
00541 }
00542
00548 static function NumQueries()
00549 {
00550 return Database::$QueryCounter;
00551 }
00552
00559 static function NumCacheHits()
00560 {
00561 return Database::$CachedQueryCounter;
00562 }
00563
00569 static function CacheHitRate()
00570 {
00571 if (Database::$QueryCounter)
00572 {
00573 return (Database::$CachedQueryCounter / Database::$QueryCounter) * 100;
00574 }
00575 else
00576 {
00577 return 0;
00578 }
00579 }
00580
00581
00582
00583 # ---- PRIVATE INTERFACE -------------------------------------------------
00584
00585 protected $DBUserName;
00586 protected $DBPassword;
00587 protected $DBHostName;
00588 protected $DBName;
00589
00590 private $Handle;
00591 private $QueryHandle;
00592 private $QueryResults;
00593 private $RowCounter;
00594 private $NumRows;
00595 private $GetResultsFromCache;
00596
00597 private static $GlobalDBUserName;
00598 private static $GlobalDBPassword;
00599 private static $GlobalDBHostName;
00600 private static $GlobalDBName;
00601
00602 # debug output flag
00603 private static $QueryDebugOutputFlag = FALSE;
00604 # flag for whether caching is turned on
00605 private static $CachingFlag = TRUE;
00606 # query result advanced caching flag
00607 private static $AdvancedCachingFlag = FALSE;
00608 # global cache for query results
00609 private static $QueryResultCache = array();
00610 # stats counters
00611 private static $QueryCounter = 0;
00612 private static $CachedQueryCounter = 0;
00613
00614 # determine whether SQL statement is one that modifies data
00615 private function IsReadOnlyStatement($QueryString)
00616 {
00617 return preg_match("/^[ ]*SELECT /i", $QueryString) ? TRUE : FALSE;
00618 }
00619
00620 # try to determine table modified by statement (returns FALSE if unknown)
00621 private function TableModified($QueryString)
00622 {
00623 # assume we're not going to be able to determine table
00624 $TableName = FALSE;
00625
00626 # split query into pieces
00627 $QueryString = trim($QueryString);
00628 $Words = preg_split("/\s+/", $QueryString);
00629
00630 # if INSERT statement
00631 $WordIndex = 1;
00632 if (strtoupper($Words[0]) == "INSERT")
00633 {
00634 # skip over modifying keywords
00635 while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
00636 || (strtoupper($Words[$WordIndex]) == "DELAYED")
00637 || (strtoupper($Words[$WordIndex]) == "IGNORE")
00638 || (strtoupper($Words[$WordIndex]) == "INTO"))
00639 {
00640 $WordIndex++;
00641 }
00642
00643 # next word is table name
00644 $TableName = $Words[$WordIndex];
00645 }
00646 # else if UPDATE statement
00647 elseif (strtoupper($Words[0]) == "UPDATE")
00648 {
00649 # skip over modifying keywords
00650 while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
00651 || (strtoupper($Words[$WordIndex]) == "IGNORE"))
00652 {
00653 $WordIndex++;
00654 }
00655
00656 # if word following next word is SET
00657 if (strtoupper($Words[$WordIndex + 1]) == "SET")
00658 {
00659 # next word is table name
00660 $TableName = $Words[$WordIndex];
00661 }
00662 }
00663 # else if DELETE statement
00664 elseif (strtoupper($Words[0]) == "DELETE")
00665 {
00666 # skip over modifying keywords
00667 while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
00668 || (strtoupper($Words[$WordIndex]) == "IGNORE")
00669 || (strtoupper($Words[$WordIndex]) == "QUICK"))
00670 {
00671 $WordIndex++;
00672 }
00673
00674 # if next term is FROM
00675 if (strtoupper($Words[$WordIndex]) == "FROM")
00676 {
00677 # next word is table name
00678 $WordIndex++;
00679 $TableName = $Words[$WordIndex];
00680 }
00681 }
00682
00683 # discard table name if it looks at all suspicious
00684 if ($TableName)
00685 {
00686 if (!preg_match("/[a-zA-Z0-9]+/", $TableName))
00687 {
00688 $TableName = FALSE;
00689 }
00690 }
00691
00692 # return table name (or lack thereof) to caller
00693 return $TableName;
00694 }
00695
00696 # try to determine tables accessed by statement (returns FALSE if unknown)
00697 private function TablesAccessed($QueryString)
00698 {
00699 # assume we're not going to be able to determine tables
00700 $TableNames = FALSE;
00701
00702 # split query into pieces
00703 $QueryString = trim($QueryString);
00704 $Words = preg_split("/\s+/", $QueryString);
00705 $UQueryString = strtoupper($QueryString);
00706 $UWords = preg_split("/\s+/", $UQueryString);
00707
00708 # if SELECT statement
00709 if ($UWords[0] == "SELECT")
00710 {
00711 # keep going until we hit FROM or last word
00712 $WordIndex = 1;
00713 while (($UWords[$WordIndex] != "FROM")
00714 && strlen($UWords[$WordIndex]))
00715 {
00716 $WordIndex++;
00717 }
00718
00719 # if we hit FROM
00720 if ($UWords[$WordIndex] == "FROM")
00721 {
00722 # for each word after FROM
00723 $WordIndex++;
00724 while (strlen($UWords[$WordIndex]))
00725 {
00726 # if current word ends with comma
00727 if (preg_match("/,$/", $Words[$WordIndex]))
00728 {
00729 # strip off comma and add word to table name list
00730 $TableNames[] = substr($Words[$WordIndex], 0, -1);
00731 }
00732 else
00733 {
00734 # add word to table name list
00735 $TableNames[] = $Words[$WordIndex];
00736
00737 # if next word is not comma
00738 $WordIndex++;
00739 if ($Words[$WordIndex] != ",")
00740 {
00741 # if word begins with comma
00742 if (preg_match("/^,/", $Words[$WordIndex]))
00743 {
00744 # strip off comma (NOTE: modifies $Words array!)
00745 $Words[$WordIndex] = substr($Words[$WordIndex], 1);
00746
00747 # decrement index so we start with this word next pass
00748 $WordIndex--;
00749 }
00750 else
00751 {
00752 # stop scanning words (non-basic JOINs not yet handled)
00753 break;
00754 }
00755 }
00756 }
00757
00758 # move to next word
00759 $WordIndex++;
00760 }
00761 }
00762 }
00763
00764 # discard table names if they look at all suspicious
00765 if ($TableNames)
00766 {
00767 foreach ($TableNames as $Name)
00768 {
00769 if (!preg_match("/^[a-zA-Z0-9]+$/", $Name))
00770 {
00771 $TableNames = FALSE;
00772 break;
00773 }
00774 }
00775 }
00776
00777 # return table name (or lack thereof) to caller
00778 return $TableNames;
00779 }
00780 }
00781
00782 # define return values (numerical values correspond to MySQL error codes)
00783 define("DB_OKAY", 0);
00784 define("DB_ERROR", 1);
00785 define("DB_ACCESSDENIED", 2);
00786 define("DB_UNKNOWNDB", 3);
00787 define("DB_UNKNOWNTABLE", 4);
00788 define("DB_SYNTAXERROR", 5);
00789 define("DB_DBALREADYEXISTS", 6);
00790 define("DB_DBDOESNOTEXIST", 7);
00791 define("DB_DISKFULL", 8);
00792
00793 # define value to designate omitted arguments (so DB values can be set to NULL)
00794 define("DB_NOVALUE", "!-_-_-DB_NOVALUE-_-_-!");
00795
00796 # MySQL error code mapping
00797 $APDBErrorCodeMappings = array(
00798 1045 => DB_ACCESSDENIED,
00799 1049 => DB_UNKNOWNDB,
00800 1046 => DB_UNKNOWNTABLE,
00801 1064 => DB_SYNTAXERROR,
00802 1007 => DB_DBALREADYEXISTS, # ? (not sure)
00803 1008 => DB_DBDOESNOTEXIST, # ? (not sure)
00804 1021 => DB_DISKFULL, # ? (not sure)
00805 );
00806
00807 ?>