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

CWIS logo doxygen
Copyright 2010 Internet Scout