Search:

CWIS Developers Documentation

  • Main Page
  • Classes
  • Files
  • 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 ?>
CWIS logo doxygen
Copyright 2009 Internet Scout