Search:

# CWIS Developers Documentation

- Main Page
- Classes
- Files

- Search for |

- File List
- File Members

# 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 : 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     /*@)*/ /* Setup/Initialization */ /*@(*/
    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     /*@)*/ /* Data Manipulation */ /*@(*/
    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     /*@)*/ /* Miscellaneous */
    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 ?>