CWIS Developer Documentation
Axis--Database.php
Go to the documentation of this file.
1 <?PHP
2 
3 #
4 # Axis--Database.php
5 # A Simple SQL Database Abstraction Object
6 #
7 # Copyright 1999-2002 Axis Data
8 # This code is free software that can be used or redistributed under the
9 # terms of Version 2 of the GNU General Public License, as published by the
10 # Free Software Foundation (http://www.fsf.org).
11 #
12 # Author: Edward Almasy (almasy@axisdata.com)
13 #
14 # Part of the AxisPHP library v1.2.5
15 # For more information see http://www.axisdata.com/AxisPHP/
16 #
17 
22 class Database {
23 
24  # ---- PUBLIC INTERFACE --------------------------------------------------
25  /*@(*/
27 
40  function Database(
41  $UserName = NULL, $Password = NULL, $DatabaseName = NULL, $HostName = NULL)
42  {
43  # save DB access parameter values
44  $this->DBUserName = $UserName ? $UserName : self::$GlobalDBUserName;
45  $this->DBPassword = $Password ? $Password : self::$GlobalDBPassword;
46  $this->DBHostName = $HostName ? $HostName :
47  (isset(self::$GlobalDBHostName) ? self::$GlobalDBHostName
48  : "localhost");
49  $this->DBName = $DatabaseName ? $DatabaseName : self::$GlobalDBName;
50 
51  # if we don't already have a connection or DB access parameters were supplied
52  $HandleIndex = $this->DBHostName.":".$this->DBName;
53  if (!array_key_exists($HandleIndex, self::$ConnectionHandles)
54  || $UserName || $Password || $DatabaseName || $HostName)
55  {
56  # open connection to DB server
57  self::$ConnectionHandles[$HandleIndex] = mysql_connect(
58  $this->DBHostName, $this->DBUserName,
59  $this->DBPassword, TRUE)
60  or die("Could not connect to database: ".mysql_error());
61 
62  # set local connection handle
63  $this->Handle = self::$ConnectionHandles[$HandleIndex];
64 
65  # select DB
66  mysql_select_db($this->DBName, $this->Handle)
67  or die(mysql_error($this->Handle));
68  }
69  else
70  {
71  # set local connection handle
72  $this->Handle = self::$ConnectionHandles[$HandleIndex];
73  }
74  }
75 
80  function __sleep()
81  {
82  return array("DBUserName", "DBPassword", "DBHostName", "DBName");
83  }
87  function __wakeup()
88  {
89  # open connection to DB server
90  $this->Handle = mysql_connect(
91  $this->DBHostName, $this->DBUserName, $this->DBPassword)
92  or die("could not connect to database");
93 
94  # select DB
95  mysql_select_db($this->DBName, $this->Handle)
96  or die(mysql_error($this->Handle));
97  }
107  static function SetGlobalServerInfo($UserName, $Password, $HostName = "localhost")
108  {
109  # save default DB access parameters
110  self::$GlobalDBUserName = $UserName;
111  self::$GlobalDBPassword = $Password;
112  self::$GlobalDBHostName = $HostName;
113 
114  # clear any existing DB connection handles
115  self::$ConnectionHandles = array();
116  }
117 
122  static function SetGlobalDatabaseName($DatabaseName)
123  {
124  # save new default DB name
125  self::$GlobalDBName = $DatabaseName;
126 
127  # clear any existing DB connection handles
128  self::$ConnectionHandles = array();
129  }
130 
136  function DBHostName() { return $this->DBHostName; }
137 
143  function DBName() { return $this->DBName; }
144 
150  function DBUserName() { return $this->DBUserName; }
151 
159  static function Caching($NewSetting = NULL)
160  {
161  # if cache setting has changed
162  if (($NewSetting !== NULL) && ($NewSetting != self::$CachingFlag))
163  {
164  # save new setting
165  self::$CachingFlag = $NewSetting;
166 
167  # clear any existing cached results
168  self::$QueryResultCache = array();
169  }
170 
171  # return current setting to caller
172  return self::$CachingFlag;
173  }
174 
184  static function AdvancedCaching($NewSetting = NULL)
185  {
186  if ($NewSetting !== NULL)
187  {
188  self::$AdvancedCachingFlag = $NewSetting;
189  }
190  return self::$AdvancedCachingFlag;
191  }
192 
207  function SetQueryErrorsToIgnore($ErrorsToIgnore)
208  {
209  $this->ErrorsToIgnore = $ErrorsToIgnore;
210  }
211 
212  /*@)*/ /* Setup/Initialization */ /*@(*/
214 
221  function Query($QueryString, $FieldName = "")
222  {
223  # if caching is enabled
224  if (self::$CachingFlag)
225  {
226  # if SQL statement is read-only
227  if ($this->IsReadOnlyStatement($QueryString))
228  {
229  # if we have statement in cache
230  if (isset(self::$QueryResultCache[$QueryString]["NumRows"]))
231  {
232  if (self::$QueryDebugOutputFlag)
233  { print("DB-C: $QueryString<br>\n"); }
234 
235  # make sure query result looks okay
236  $this->QueryHandle = TRUE;
237 
238  # increment cache hit counter
239  self::$CachedQueryCounter++;
240 
241  # make local copy of results
242  $this->QueryResults = self::$QueryResultCache[$QueryString];
243  $this->NumRows = self::$QueryResultCache[$QueryString]["NumRows"];
244 
245  # set flag to indicate that results should be retrieved from cache
246  $this->GetResultsFromCache = TRUE;
247  }
248  else
249  {
250  # execute SQL statement
251  $this->QueryHandle = $this->RunQuery($QueryString);
252  if ($this->QueryHandle === FALSE) { return FALSE; }
253 
254  # save number of rows in result
255  $this->NumRows = mysql_num_rows($this->QueryHandle);
256 
257  # if too many rows to cache
258  if ($this->NumRows >= 50)
259  {
260  # set flag to indicate that query results should not be retrieved from cache
261  $this->GetResultsFromCache = FALSE;
262  }
263  else
264  {
265  # if advanced caching is enabled
266  if (self::$AdvancedCachingFlag)
267  {
268  # save tables accessed by query
269  self::$QueryResultCache[$QueryString]["TablesAccessed"] =
270  $this->TablesAccessed($QueryString);
271  }
272 
273  # if rows found
274  if ($this->NumRows > 0)
275  {
276  # load query results
277  for ($Row = 0; $Row < $this->NumRows; $Row++)
278  {
279  $this->QueryResults[$Row] =
280  mysql_fetch_assoc($this->QueryHandle);
281  }
282 
283  # cache query results
284  self::$QueryResultCache[$QueryString] = $this->QueryResults;
285  }
286  else
287  {
288  # clear local query results
289  unset($this->QueryResults);
290  }
291 
292  # cache number of rows
293  self::$QueryResultCache[$QueryString]["NumRows"] = $this->NumRows;
294 
295  # set flag to indicate that query results should be retrieved from cache
296  $this->GetResultsFromCache = TRUE;
297  }
298  }
299  }
300  else
301  {
302  # if advanced caching is enabled
303  if (self::$AdvancedCachingFlag)
304  {
305  # if table modified by statement is known
306  $TableModified = $this->TableModified($QueryString);
307  if ($TableModified)
308  {
309  # for each cached query
310  foreach (self::$QueryResultCache as $CachedQueryString => $CachedQueryResult)
311  {
312  # if we know what tables were accessed
313  if ($CachedQueryResult["TablesAccessed"])
314  {
315  # if tables accessed include the one we may modify
316  if (in_array($TableModified, $CachedQueryResult["TablesAccessed"]))
317  {
318  # clear cached query results
319  unset($GLOBALS["APDBQueryResultCache"][$CachedQueryString]);
320  }
321  }
322  else
323  {
324  # clear cached query results
325  unset($GLOBALS["APDBQueryResultCache"][$CachedQueryString]);
326  }
327  }
328  }
329  else
330  {
331  # clear entire query result cache
332  self::$QueryResultCache = array();
333  }
334  }
335  else
336  {
337  # clear entire query result cache
338  self::$QueryResultCache = array();
339  }
340 
341  # execute SQL statement
342  $this->QueryHandle = $this->RunQuery($QueryString);
343  if ($this->QueryHandle === FALSE) { return FALSE; }
344 
345  # set flag to indicate that query results should not be retrieved from cache
346  $this->GetResultsFromCache = FALSE;
347  }
348 
349  # reset row counter
350  $this->RowCounter = 0;
351 
352  # increment query counter
353  self::$QueryCounter++;
354  }
355  else
356  {
357  # execute SQL statement
358  $this->QueryHandle = $this->RunQuery($QueryString);
359  if ($this->QueryHandle === FALSE) { return FALSE; }
360  }
361 
362  if (($FieldName != "") && ($this->QueryHandle != FALSE))
363  {
364  return $this->FetchField($FieldName);
365  }
366  else
367  {
368  return $this->QueryHandle;
369  }
370  }
371 
377  function QueryErrMsg()
378  {
379  return $this->ErrMsg;
380  }
381 
387  function QueryErrNo()
388  {
389  return $this->ErrNo;
390  }
391 
398  static function DisplayQueryErrors($NewValue = NULL)
399  {
400  if ($NewValue !== NULL) { self::$DisplayErrors = $NewValue; }
401  return self::$DisplayErrors;
402  }
403 
408  function NumRowsSelected()
409  {
410  # if caching is enabled and query was cached
411  if (self::$CachingFlag && $this->GetResultsFromCache)
412  {
413  # return cached number of rows to caller
414  return $this->NumRows;
415  }
416  else
417  {
418  # call to this method after an unsuccessful query
419  if (!is_resource($this->QueryHandle))
420  {
421  return 0;
422  }
423 
424  # retrieve number of rows and return to caller
425  return mysql_num_rows($this->QueryHandle);
426  }
427  }
428 
433  function FetchRow()
434  {
435  # if caching is enabled and query was cached
436  if (self::$CachingFlag && $this->GetResultsFromCache)
437  {
438  # if rows left to return
439  if ($this->RowCounter < $this->NumRows)
440  {
441  # retrieve row from cache
442  $Result = $this->QueryResults[$this->RowCounter];
443 
444  # increment row counter
445  $this->RowCounter++;
446  }
447  else
448  {
449  # return nothing
450  $Result = FALSE;
451  }
452  }
453  else
454  {
455  # call to this method after successful query
456  if (is_resource($this->QueryHandle))
457  {
458  $Result = mysql_fetch_assoc($this->QueryHandle);
459  }
460 
461  # call to this method after unsuccessful query
462  else
463  {
464  $Result = FALSE;
465  }
466  }
467 
468  # return row to caller
469  return $Result;
470  }
471 
477  function FetchRows($NumberOfRows = NULL)
478  {
479  # assume no rows will be returned
480  $Result = array();
481 
482  # for each available row
483  $RowsFetched = 0;
484  while ((($RowsFetched < $NumberOfRows) || ($NumberOfRows == NULL)) && ($Row = $this->FetchRow()))
485  {
486  # add row to results
487  $Result[] = $Row;
488  $RowsFetched++;
489  }
490 
491  # return array of rows to caller
492  return $Result;
493  }
494 
508  function FetchColumn($FieldName, $IndexFieldName = NULL)
509  {
510  $Array = array();
511  while ($Record = $this->FetchRow())
512  {
513  if ($IndexFieldName != NULL)
514  {
515  $Array[$Record[$IndexFieldName]] = $Record[$FieldName];
516  }
517  else
518  {
519  $Array[] = $Record[$FieldName];
520  }
521  }
522  return $Array;
523  }
524 
533  function FetchField($FieldName)
534  {
535  $Record = $this->FetchRow();
536  return isset($Record[$FieldName]) ? $Record[$FieldName] : NULL;
537  }
538 
546  function LastInsertId($TableName)
547  {
548  return (int)$this->Query(
549  "SELECT LAST_INSERT_ID() AS InsertId FROM ".$TableName,
550  "InsertId");
551  }
552 
564  function UpdateValue(
565  $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
566  {
567  # expand condition if supplied
568  if ($Condition != NULL) { $Condition = " WHERE ".$Condition; }
569 
570  # read cached record from database if not already loaded
571  if (!isset($CachedRecord))
572  {
573  $this->Query("SELECT * FROM ".$TableName." ".$Condition);
574  $CachedRecord = $this->FetchRow();
575  }
576 
577  # if new value supplied
578  if ($NewValue !== DB_NOVALUE)
579  {
580  # update value in database
581  $this->Query("UPDATE $TableName SET $FieldName = "
582  .(($NewValue === NULL) ? "NULL" : "'".addslashes($NewValue)."'")
583  .$Condition);
584 
585  # update value in cached record
586  $CachedRecord[$FieldName] = $NewValue;
587  }
588 
589  # return value from cached record to caller
590  return isset($CachedRecord[$FieldName])
591  ? $CachedRecord[$FieldName] : NULL;
592  }
593 
607  function UpdateIntValue(
608  $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
609  {
610  return $this->UpdateValue($TableName, $FieldName,
611  (($NewValue === DB_NOVALUE) ? DB_NOVALUE : (int)$NewValue),
612  $Condition, $CachedRecord);
613  }
614 
629  $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
630  {
631  return $this->UpdateValue($TableName, $FieldName,
632  (($NewValue === DB_NOVALUE) ? DB_NOVALUE : (float)$NewValue),
633  $Condition, $CachedRecord);
634  }
635 
636  /*@)*/ /* Data Manipulation */ /*@(*/
638 
645  function LogComment($String)
646  {
647  $this->Query("-- ".$String);
648  }
649 
656  function FieldExists($TableName, $FieldName)
657  {
658  $this->Query("DESC ".$TableName);
659  while ($CurrentFieldName = $this->FetchField("Field"))
660  {
661  if ($CurrentFieldName == $FieldName) { return TRUE; }
662  }
663  return FALSE;
664  }
665 
671  static function QueryDebugOutput($NewSetting)
672  {
673  self::$QueryDebugOutputFlag = $NewSetting;
674  }
675 
681  static function NumQueries()
682  {
683  return self::$QueryCounter;
684  }
685 
692  static function NumCacheHits()
693  {
694  return self::$CachedQueryCounter;
695  }
696 
702  static function CacheHitRate()
703  {
704  if (self::$QueryCounter)
705  {
706  return (self::$CachedQueryCounter / self::$QueryCounter) * 100;
707  }
708  else
709  {
710  return 0;
711  }
712  }
713 
714  /*@)*/ /* Miscellaneous */
715 
716  # ---- PRIVATE INTERFACE -------------------------------------------------
717 
718  protected $DBUserName;
719  protected $DBPassword;
720  protected $DBHostName;
721  protected $DBName;
722 
723  private $Handle;
724  private $QueryHandle;
725  private $QueryResults;
726  private $RowCounter;
727  private $NumRows;
728  private $GetResultsFromCache;
729  private $ErrorsToIgnore = NULL;
730  private $ErrMsg = NULL;
731  private $ErrNo = NULL;
732 
733  private static $DisplayErrors = FALSE;
734 
735  private static $GlobalDBUserName;
736  private static $GlobalDBPassword;
737  private static $GlobalDBHostName;
738  private static $GlobalDBName;
739 
740  # debug output flag
741  private static $QueryDebugOutputFlag = FALSE;
742  # flag for whether caching is turned on
743  private static $CachingFlag = TRUE;
744  # query result advanced caching flag
745  private static $AdvancedCachingFlag = FALSE;
746  # global cache for query results
747  private static $QueryResultCache = array();
748  # stats counters
749  private static $QueryCounter = 0;
750  private static $CachedQueryCounter = 0;
751  # database connection link handles
752  private static $ConnectionHandles = array();
753 
754  # determine whether SQL statement is one that modifies data
755  private function IsReadOnlyStatement($QueryString)
756  {
757  return preg_match("/^[ ]*SELECT /i", $QueryString) ? TRUE : FALSE;
758  }
759 
760  # try to determine table modified by statement (returns FALSE if unknown)
761  private function TableModified($QueryString)
762  {
763  # assume we're not going to be able to determine table
764  $TableName = FALSE;
765 
766  # split query into pieces
767  $QueryString = trim($QueryString);
768  $Words = preg_split("/\s+/", $QueryString);
769 
770  # if INSERT statement
771  $WordIndex = 1;
772  if (strtoupper($Words[0]) == "INSERT")
773  {
774  # skip over modifying keywords
775  while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
776  || (strtoupper($Words[$WordIndex]) == "DELAYED")
777  || (strtoupper($Words[$WordIndex]) == "IGNORE")
778  || (strtoupper($Words[$WordIndex]) == "INTO"))
779  {
780  $WordIndex++;
781  }
782 
783  # next word is table name
784  $TableName = $Words[$WordIndex];
785  }
786  # else if UPDATE statement
787  elseif (strtoupper($Words[0]) == "UPDATE")
788  {
789  # skip over modifying keywords
790  while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
791  || (strtoupper($Words[$WordIndex]) == "IGNORE"))
792  {
793  $WordIndex++;
794  }
795 
796  # if word following next word is SET
797  if (strtoupper($Words[$WordIndex + 1]) == "SET")
798  {
799  # next word is table name
800  $TableName = $Words[$WordIndex];
801  }
802  }
803  # else if DELETE statement
804  elseif (strtoupper($Words[0]) == "DELETE")
805  {
806  # skip over modifying keywords
807  while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
808  || (strtoupper($Words[$WordIndex]) == "IGNORE")
809  || (strtoupper($Words[$WordIndex]) == "QUICK"))
810  {
811  $WordIndex++;
812  }
813 
814  # if next term is FROM
815  if (strtoupper($Words[$WordIndex]) == "FROM")
816  {
817  # next word is table name
818  $WordIndex++;
819  $TableName = $Words[$WordIndex];
820  }
821  }
822 
823  # discard table name if it looks at all suspicious
824  if ($TableName)
825  {
826  if (!preg_match("/[a-zA-Z0-9]+/", $TableName))
827  {
828  $TableName = FALSE;
829  }
830  }
831 
832  # return table name (or lack thereof) to caller
833  return $TableName;
834  }
835 
836  # try to determine tables accessed by statement (returns FALSE if unknown)
837  private function TablesAccessed($QueryString)
838  {
839  # assume we're not going to be able to determine tables
840  $TableNames = FALSE;
841 
842  # split query into pieces
843  $QueryString = trim($QueryString);
844  $Words = preg_split("/\s+/", $QueryString);
845  $UQueryString = strtoupper($QueryString);
846  $UWords = preg_split("/\s+/", $UQueryString);
847 
848  # if SELECT statement
849  if ($UWords[0] == "SELECT")
850  {
851  # keep going until we hit FROM or last word
852  $WordIndex = 1;
853  while (($UWords[$WordIndex] != "FROM")
854  && strlen($UWords[$WordIndex]))
855  {
856  $WordIndex++;
857  }
858 
859  # if we hit FROM
860  if ($UWords[$WordIndex] == "FROM")
861  {
862  # for each word after FROM
863  $WordIndex++;
864  while (strlen($UWords[$WordIndex]))
865  {
866  # if current word ends with comma
867  if (preg_match("/,$/", $Words[$WordIndex]))
868  {
869  # strip off comma and add word to table name list
870  $TableNames[] = substr($Words[$WordIndex], 0, -1);
871  }
872  else
873  {
874  # add word to table name list
875  $TableNames[] = $Words[$WordIndex];
876 
877  # if next word is not comma
878  $WordIndex++;
879  if ($Words[$WordIndex] != ",")
880  {
881  # if word begins with comma
882  if (preg_match("/^,/", $Words[$WordIndex]))
883  {
884  # strip off comma (NOTE: modifies $Words array!)
885  $Words[$WordIndex] = substr($Words[$WordIndex], 1);
886 
887  # decrement index so we start with this word next pass
888  $WordIndex--;
889  }
890  else
891  {
892  # stop scanning words (non-basic JOINs not yet handled)
893  break;
894  }
895  }
896  }
897 
898  # move to next word
899  $WordIndex++;
900  }
901  }
902  }
903 
904  # discard table names if they look at all suspicious
905  if ($TableNames)
906  {
907  foreach ($TableNames as $Name)
908  {
909  if (!preg_match("/^[a-zA-Z0-9]+$/", $Name))
910  {
911  $TableNames = FALSE;
912  break;
913  }
914  }
915  }
916 
917  # return table name (or lack thereof) to caller
918  return $TableNames;
919  }
920 
921  private function RunQuery($QueryString)
922  {
923  if (self::$QueryDebugOutputFlag) { $QueryStartTime = microtime(TRUE); }
924  $this->QueryHandle = mysql_query($QueryString, $this->Handle);
925  if (self::$QueryDebugOutputFlag)
926  {
927  print "DB: ".$QueryString." ["
928  .sprintf("%.2f", microtime(TRUE) - $QueryStartTime)
929  ."s]"."<br>\n";
930  }
931  if (($this->QueryHandle === FALSE) && $this->ErrorsToIgnore)
932  {
933  foreach ($this->ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
934  {
935  if (preg_match($SqlPattern, $QueryString)
936  && preg_match($ErrMsgPattern, mysql_error($this->Handle)))
937  {
938  $this->QueryHandle = TRUE;
939  break;
940  }
941  }
942  }
943 
944  if ($this->QueryHandle === FALSE)
945  {
946  $this->ErrMsg = mysql_error($this->Handle);
947  $this->ErrNo = mysql_errno($this->Handle);
948  $this->NumRows = 0;
949  if (self::$DisplayErrors)
950  {
951  print("<b>SQL Error:</b> <i>".$this->ErrMsg
952  ."</i> (".$this->ErrNo.")<br/>\n");
953  print("<b>SQL Statement:</b> <i>"
954  .htmlspecialchars($QueryString)."</i><br/>\n");
955  }
956  }
957  return $this->QueryHandle;
958  }
959 }
960 
961 # define return values (numerical values correspond to MySQL error codes)
962 define("DB_OKAY", 0);
963 define("DB_ERROR", 1);
964 define("DB_ACCESSDENIED", 2);
965 define("DB_UNKNOWNDB", 3);
966 define("DB_UNKNOWNTABLE", 4);
967 define("DB_SYNTAXERROR", 5);
968 define("DB_DBALREADYEXISTS", 6);
969 define("DB_DBDOESNOTEXIST", 7);
970 define("DB_DISKFULL", 8);
971 
972 # define value to designate omitted arguments (so DB values can be set to NULL)
973 define("DB_NOVALUE", "!-_-_-DB_NOVALUE-_-_-!");
974 
975 # MySQL error code mapping
977  1045 => DB_ACCESSDENIED,
978  1049 => DB_UNKNOWNDB,
979  1046 => DB_UNKNOWNTABLE,
980  1064 => DB_SYNTAXERROR,
981  1007 => DB_DBALREADYEXISTS, # ? (not sure)
982  1008 => DB_DBDOESNOTEXIST, # ? (not sure)
983  1021 => DB_DISKFULL, # ? (not sure)
984  );
985 
986 ?>