CWIS Developer Documentation

- Home
- Main Page
- Namespaces
- Classes
- Files

- File List
- File Members

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
628 function UpdateFloatValue(
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
976 $APDBErrorCodeMappings = array(
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 ?>