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 : 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 
00181     function SetQueryErrorsToIgnore($ErrorsToIgnore)
00182     {
00183         $this->ErrorsToIgnore = $ErrorsToIgnore;
00184     }
00185 
00186     /*@)*/ /* Setup/Initialization */ /*@(*/
00188 
00195     function Query($QueryString, $FieldName = "")
00196     {
00197         # if caching is enabled
00198         if (self::$CachingFlag)
00199         {
00200             # if SQL statement is read-only
00201             if ($this->IsReadOnlyStatement($QueryString))
00202             {
00203                 # if we have statement in cache
00204                 if (isset(self::$QueryResultCache[$QueryString]["NumRows"]))
00205                 {
00206                     if (self::$QueryDebugOutputFlag)
00207                             {  print("DB-C: $QueryString<br>\n");  }
00208 
00209                     # make sure query result looks okay
00210                     $this->QueryHandle = TRUE;
00211 
00212                     # increment cache hit counter
00213                     self::$CachedQueryCounter++;
00214 
00215                     # make local copy of results
00216                     $this->QueryResults = self::$QueryResultCache[$QueryString];
00217                     $this->NumRows = self::$QueryResultCache[$QueryString]["NumRows"];
00218 
00219                     # set flag to indicate that results should be retrieved from cache
00220                     $this->GetResultsFromCache = TRUE;
00221                 }
00222                 else
00223                 {
00224                     # execute SQL statement
00225                     $this->QueryHandle = $this->RunQuery($QueryString);
00226                     if ($this->QueryHandle === FALSE) {  return FALSE;  }
00227 
00228                     # save number of rows in result
00229                     $this->NumRows = mysql_num_rows($this->QueryHandle);
00230 
00231                     # if too many rows to cache
00232                     if ($this->NumRows >= 50)
00233                     {
00234                         # set flag to indicate that query results should not be retrieved from cache
00235                         $this->GetResultsFromCache = FALSE;
00236                     }
00237                     else
00238                     {
00239                         # if advanced caching is enabled
00240                         if (self::$AdvancedCachingFlag)
00241                         {
00242                             # save tables accessed by query
00243                             self::$QueryResultCache[$QueryString]["TablesAccessed"] = $this->TablesAccessed($QueryString);
00244                         }
00245 
00246                         # if rows found
00247                         if ($this->NumRows > 0)
00248                         {
00249                             # load query results
00250                             for ($Row = 0;  $Row < $this->NumRows;  $Row++)
00251                             {
00252                                 $this->QueryResults[$Row] = mysql_fetch_assoc($this->QueryHandle);
00253                             }
00254 
00255                             # cache query results
00256                             self::$QueryResultCache[$QueryString] = $this->QueryResults;
00257                         }
00258                         else
00259                         {
00260                             # clear local query results
00261                             unset($this->QueryResults);
00262                         }
00263 
00264                         # cache number of rows
00265                         self::$QueryResultCache[$QueryString]["NumRows"] = $this->NumRows;
00266 
00267                         # set flag to indicate that query results should be retrieved from cache
00268                         $this->GetResultsFromCache = TRUE;
00269                     }
00270                 }
00271             }
00272             else
00273             {
00274                 # if advanced caching is enabled
00275                 if (self::$AdvancedCachingFlag)
00276                 {
00277                     # if table modified by statement is known
00278                     $TableModified = $this->TableModified($QueryString);
00279                     if ($TableModified)
00280                     {
00281                         # for each cached query
00282                         foreach (self::$QueryResultCache as $CachedQueryString => $CachedQueryResult)
00283                         {
00284                             # if we know what tables were accessed
00285                             if ($CachedQueryResult["TablesAccessed"])
00286                             {
00287                                 # if tables accessed include the one we may modify
00288                                 if (in_array($TableModified, $CachedQueryResult["TablesAccessed"]))
00289                                 {
00290                                     # clear cached query results
00291                                     unset($GLOBALS["APDBQueryResultCache"][$CachedQueryString]);
00292                                 }
00293                             }
00294                             else
00295                             {
00296                                 # clear cached query results
00297                                 unset($GLOBALS["APDBQueryResultCache"][$CachedQueryString]);
00298                             }
00299                         }
00300                     }
00301                     else
00302                     {
00303                         # clear entire query result cache
00304                         self::$QueryResultCache = array();
00305                     }
00306                 }
00307                 else
00308                 {
00309                     # clear entire query result cache
00310                     self::$QueryResultCache = array();
00311                 }
00312 
00313                 # execute SQL statement
00314                 $this->QueryHandle = $this->RunQuery($QueryString);
00315                 if ($this->QueryHandle === FALSE) {  return FALSE;  }
00316 
00317                 # set flag to indicate that query results should not be retrieved from cache
00318                 $this->GetResultsFromCache = FALSE;
00319             }
00320 
00321             # reset row counter
00322             $this->RowCounter = 0;
00323 
00324             # increment query counter
00325             self::$QueryCounter++;
00326         }
00327         else
00328         {
00329             # execute SQL statement
00330             $this->QueryHandle = $this->RunQuery($QueryString);
00331             if ($this->QueryHandle === FALSE) {  return FALSE;  }
00332         }
00333 
00334         if (($FieldName != "") && ($this->QueryHandle != FALSE))
00335         {
00336             return $this->FetchField($FieldName);
00337         }
00338         else
00339         {
00340             return $this->QueryHandle;
00341         }
00342     }
00343 
00349     function QueryErrMsg()
00350     {
00351         return $this->ErrMsg;
00352     }
00353 
00359     function QueryErrNo()
00360     {
00361         return $this->ErrNo;
00362     }
00363 
00370     static function DisplayQueryErrors($NewValue = NULL)
00371     {
00372         if ($NewValue !== NULL) {  self::$DisplayErrors = $NewValue;  }
00373         return self::$DisplayErrors;
00374     }
00375 
00380     function NumRowsSelected()
00381     {
00382         # if caching is enabled and query was cached
00383         if (self::$CachingFlag && $this->GetResultsFromCache)
00384         {
00385             # return cached number of rows to caller
00386             return $this->NumRows;
00387         }
00388         else
00389         {
00390             # retrieve number of rows and return to caller
00391             return mysql_num_rows($this->QueryHandle);
00392         }
00393     }
00394 
00399     function FetchRow()
00400     {
00401         # if caching is enabled and query was cached
00402         if (self::$CachingFlag && $this->GetResultsFromCache)
00403         {
00404             # if rows left to return
00405             if ($this->RowCounter < $this->NumRows)
00406             {
00407                 # retrieve row from cache
00408                 $Result = $this->QueryResults[$this->RowCounter];
00409 
00410                 # increment row counter
00411                 $this->RowCounter++;
00412             }
00413             else
00414             {
00415                 # return nothing
00416                 $Result = FALSE;
00417             }
00418         }
00419         else
00420         {
00421             # retrieve row from DB
00422             $Result = mysql_fetch_assoc($this->QueryHandle);
00423         }
00424 
00425         # return row to caller
00426         return $Result;
00427     }
00428 
00434     function FetchRows($NumberOfRows = NULL)
00435     {
00436         # assume no rows will be returned
00437         $Result = array();
00438 
00439         # for each available row
00440         $RowsFetched = 0;
00441         while ((($RowsFetched < $NumberOfRows) || ($NumberOfRows == NULL)) && ($Row = $this->FetchRow()))
00442         {
00443             # add row to results
00444             $Result[] = $Row;
00445             $RowsFetched++;
00446         }
00447 
00448         # return array of rows to caller
00449         return $Result;
00450     }
00451 
00465     function FetchColumn($FieldName, $IndexFieldName = NULL)
00466     {
00467         $Array = array();
00468         while ($Record = $this->FetchRow())
00469         {
00470             if ($IndexFieldName != NULL)
00471             {
00472                 $Array[$Record[$IndexFieldName]] = $Record[$FieldName];
00473             }
00474             else
00475             {
00476                 $Array[] = $Record[$FieldName];
00477             }
00478         }
00479         return $Array;
00480     }
00481 
00490     function FetchField($FieldName)
00491     {
00492         $Record = $this->FetchRow();
00493         return isset($Record[$FieldName]) ? $Record[$FieldName] : NULL;
00494     }
00495 
00503     function LastInsertId($TableName)
00504     {
00505         return (int)$this->Query(
00506                 "SELECT LAST_INSERT_ID() AS InsertId FROM ".$TableName,
00507                 "InsertId");
00508     }
00509 
00521     function UpdateValue(
00522             $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
00523     {
00524         # expand condition if supplied
00525         if ($Condition != NULL) {  $Condition = " WHERE ".$Condition;  }
00526 
00527         # read cached record from database if not already loaded
00528         if (!isset($CachedRecord))
00529         {
00530             $this->Query("SELECT * FROM ".$TableName." ".$Condition);
00531             $CachedRecord = $this->FetchRow();
00532         }
00533 
00534         # if new value supplied
00535         if ($NewValue !== DB_NOVALUE)
00536         {
00537             # update value in database
00538             $this->Query("UPDATE $TableName SET $FieldName = "
00539                     .(($NewValue === NULL) ? "NULL" : "'".addslashes($NewValue)."'")
00540                     .$Condition);
00541 
00542             # update value in cached record
00543             $CachedRecord[$FieldName] = $NewValue;
00544         }
00545 
00546         # return value from cached record to caller
00547         return isset($CachedRecord[$FieldName])
00548                 ? $CachedRecord[$FieldName] : NULL;
00549     }
00550 
00551     /*@)*/ /* Data Manipulation */ /*@(*/
00553 
00560     function LogComment($String)
00561     {
00562         $this->Query("-- ".$String);
00563     }
00564 
00571     function FieldExists($TableName, $FieldName)
00572     {
00573         $this->Query("DESC ".$TableName);
00574         while ($CurrentFieldName = $this->FetchField("Field"))
00575         {
00576             if ($CurrentFieldName == $FieldName) {  return TRUE;  }
00577         }
00578         return FALSE;
00579     }
00580 
00586     static function QueryDebugOutput($NewSetting)
00587     {
00588         self::$QueryDebugOutputFlag = $NewSetting;
00589     }
00590 
00596     static function NumQueries()
00597     {
00598         return self::$QueryCounter;
00599     }
00600 
00607     static function NumCacheHits()
00608     {
00609         return self::$CachedQueryCounter;
00610     }
00611 
00617     static function CacheHitRate()
00618     {
00619         if (self::$QueryCounter)
00620         {
00621             return (self::$CachedQueryCounter / self::$QueryCounter) * 100;
00622         }
00623         else
00624         {
00625             return 0;
00626         }
00627     }
00628 
00629     /*@)*/ /* Miscellaneous */
00630 
00631     # ---- PRIVATE INTERFACE -------------------------------------------------
00632 
00633     protected $DBUserName;
00634     protected $DBPassword;
00635     protected $DBHostName;
00636     protected $DBName;
00637 
00638     private $Handle;
00639     private $QueryHandle;
00640     private $QueryResults;
00641     private $RowCounter;
00642     private $NumRows;
00643     private $GetResultsFromCache;
00644     private $ErrorsToIgnore = NULL;
00645     private $ErrMsg = NULL;
00646     private $ErrNo = NULL;
00647 
00648     private static $DisplayErrors = FALSE;
00649 
00650     private static $GlobalDBUserName;
00651     private static $GlobalDBPassword;
00652     private static $GlobalDBHostName;
00653     private static $GlobalDBName;
00654 
00655     # debug output flag
00656     private static $QueryDebugOutputFlag = FALSE;
00657     # flag for whether caching is turned on
00658     private static $CachingFlag = TRUE;
00659     # query result advanced caching flag
00660     private static $AdvancedCachingFlag = FALSE;
00661     # global cache for query results
00662     private static $QueryResultCache = array();
00663     # stats counters
00664     private static $QueryCounter = 0;
00665     private static $CachedQueryCounter = 0;
00666 
00667     # determine whether SQL statement is one that modifies data
00668     private function IsReadOnlyStatement($QueryString)
00669     {
00670         return preg_match("/^[ ]*SELECT /i", $QueryString) ? TRUE : FALSE;
00671     }
00672 
00673     # try to determine table modified by statement (returns FALSE if unknown)
00674     private function TableModified($QueryString)
00675     {
00676         # assume we're not going to be able to determine table
00677         $TableName = FALSE;
00678 
00679         # split query into pieces
00680         $QueryString = trim($QueryString);
00681         $Words = preg_split("/\s+/", $QueryString);
00682 
00683         # if INSERT statement
00684         $WordIndex = 1;
00685         if (strtoupper($Words[0]) == "INSERT")
00686         {
00687             # skip over modifying keywords
00688             while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
00689                     || (strtoupper($Words[$WordIndex]) == "DELAYED")
00690                     || (strtoupper($Words[$WordIndex]) == "IGNORE")
00691                     || (strtoupper($Words[$WordIndex]) == "INTO"))
00692             {
00693                 $WordIndex++;
00694             }
00695 
00696             # next word is table name
00697             $TableName = $Words[$WordIndex];
00698         }
00699         # else if UPDATE statement
00700         elseif (strtoupper($Words[0]) == "UPDATE")
00701         {
00702             # skip over modifying keywords
00703             while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
00704                     || (strtoupper($Words[$WordIndex]) == "IGNORE"))
00705             {
00706                 $WordIndex++;
00707             }
00708 
00709             # if word following next word is SET
00710             if (strtoupper($Words[$WordIndex + 1]) == "SET")
00711             {
00712                 # next word is table name
00713                 $TableName = $Words[$WordIndex];
00714             }
00715         }
00716         # else if DELETE statement
00717         elseif (strtoupper($Words[0]) == "DELETE")
00718         {
00719             # skip over modifying keywords
00720             while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
00721                     || (strtoupper($Words[$WordIndex]) == "IGNORE")
00722                     || (strtoupper($Words[$WordIndex]) == "QUICK"))
00723             {
00724                 $WordIndex++;
00725             }
00726 
00727             # if next term is FROM
00728             if (strtoupper($Words[$WordIndex]) == "FROM")
00729             {
00730                 # next word is table name
00731                 $WordIndex++;
00732                 $TableName = $Words[$WordIndex];
00733             }
00734         }
00735 
00736         # discard table name if it looks at all suspicious
00737         if ($TableName)
00738         {
00739             if (!preg_match("/[a-zA-Z0-9]+/", $TableName))
00740             {
00741                 $TableName = FALSE;
00742             }
00743         }
00744 
00745         # return table name (or lack thereof) to caller
00746         return $TableName;
00747     }
00748 
00749     # try to determine tables accessed by statement (returns FALSE if unknown)
00750     private function TablesAccessed($QueryString)
00751     {
00752         # assume we're not going to be able to determine tables
00753         $TableNames = FALSE;
00754 
00755         # split query into pieces
00756         $QueryString = trim($QueryString);
00757         $Words = preg_split("/\s+/", $QueryString);
00758         $UQueryString = strtoupper($QueryString);
00759         $UWords = preg_split("/\s+/", $UQueryString);
00760 
00761         # if SELECT statement
00762         if ($UWords[0] == "SELECT")
00763         {
00764             # keep going until we hit FROM or last word
00765             $WordIndex = 1;
00766             while (($UWords[$WordIndex] != "FROM")
00767                     && strlen($UWords[$WordIndex]))
00768             {
00769                 $WordIndex++;
00770             }
00771 
00772             # if we hit FROM
00773             if ($UWords[$WordIndex] == "FROM")
00774             {
00775                 # for each word after FROM
00776                 $WordIndex++;
00777                 while (strlen($UWords[$WordIndex]))
00778                 {
00779                     # if current word ends with comma
00780                     if (preg_match("/,$/", $Words[$WordIndex]))
00781                     {
00782                         # strip off comma and add word to table name list
00783                         $TableNames[] = substr($Words[$WordIndex], 0, -1);
00784                     }
00785                     else
00786                     {
00787                         # add word to table name list
00788                         $TableNames[] = $Words[$WordIndex];
00789 
00790                         # if next word is not comma
00791                         $WordIndex++;
00792                         if ($Words[$WordIndex] != ",")
00793                         {
00794                             # if word begins with comma
00795                             if (preg_match("/^,/", $Words[$WordIndex]))
00796                             {
00797                                 # strip off comma (NOTE: modifies $Words array!)
00798                                 $Words[$WordIndex] = substr($Words[$WordIndex], 1);
00799 
00800                                 # decrement index so we start with this word next pass
00801                                 $WordIndex--;
00802                             }
00803                             else
00804                             {
00805                                 # stop scanning words (non-basic JOINs not yet handled)
00806                                 break;
00807                             }
00808                         }
00809                     }
00810 
00811                     # move to next word
00812                     $WordIndex++;
00813                 }
00814             }
00815         }
00816 
00817         # discard table names if they look at all suspicious
00818         if ($TableNames)
00819         {
00820             foreach ($TableNames as $Name)
00821             {
00822                 if (!preg_match("/^[a-zA-Z0-9]+$/", $Name))
00823                 {
00824                     $TableNames = FALSE;
00825                     break;
00826                 }
00827             }
00828         }
00829 
00830         # return table name (or lack thereof) to caller
00831         return $TableNames;
00832     }
00833 
00834     private function RunQuery($QueryString)
00835     {
00836         if (self::$QueryDebugOutputFlag) {  print("DB: $QueryString<br>\n");  }
00837         $this->QueryHandle = mysql_query($QueryString, $this->Handle);
00838         if (($this->QueryHandle === FALSE) && $this->ErrorsToIgnore)
00839         {
00840             foreach ($this->ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
00841             {
00842                 if (preg_match($SqlPattern, $QueryString)
00843                         && preg_match($ErrMsgPattern, mysql_error()))
00844                 {
00845                     $this->QueryHandle = TRUE;
00846                     break;
00847                 }
00848             }
00849         }
00850 
00851         if ($this->QueryHandle === FALSE)
00852         {
00853             $this->ErrMsg = mysql_error();
00854             $this->ErrNo = mysql_errno();
00855             $this->NumRows = 0;
00856             if (self::$DisplayErrors)
00857             {
00858                 print("<b>SQL Error:</b> <i>".$this->ErrMsg
00859                         ."</i> (".$this->ErrNo.")<br/>\n");
00860                 print("<b>SQL Statement:</b> <i>"
00861                         .htmlspecialchars($QueryString)."</i><br/>\n");
00862             }
00863         }
00864         return $this->QueryHandle;
00865     }
00866 }
00867 
00868 # define return values  (numerical values correspond to MySQL error codes)
00869 define("DB_OKAY",               0);
00870 define("DB_ERROR",              1);
00871 define("DB_ACCESSDENIED",       2);
00872 define("DB_UNKNOWNDB",          3);
00873 define("DB_UNKNOWNTABLE",       4);
00874 define("DB_SYNTAXERROR",        5);
00875 define("DB_DBALREADYEXISTS",    6);
00876 define("DB_DBDOESNOTEXIST",     7);
00877 define("DB_DISKFULL",           8);
00878 
00879 # define value to designate omitted arguments (so DB values can be set to NULL)
00880 define("DB_NOVALUE", "!-_-_-DB_NOVALUE-_-_-!");
00881 
00882 # MySQL error code mapping
00883 $APDBErrorCodeMappings = array(
00884         1045    => DB_ACCESSDENIED,
00885         1049    => DB_UNKNOWNDB,
00886         1046    => DB_UNKNOWNTABLE,
00887         1064    => DB_SYNTAXERROR,
00888         1007    => DB_DBALREADYEXISTS,  # ?  (not sure)
00889         1008    => DB_DBDOESNOTEXIST,   # ?  (not sure)
00890         1021    => DB_DISKFULL,         # ?  (not sure)
00891         );
00892 
00893 ?>

CWIS logo doxygen
Copyright 2010 Internet Scout