<?PHP

#
#   Axis--Database.php
#   A Simple SQL Database Abstraction Object
#
#   Copyright 1999-2002 Axis Data
#   This code is free software that can be used or redistributed under the
#   terms of Version 2 of the GNU General Public License, as published by the
#   Free Software Foundation (http://www.fsf.org).
#
#   Author:  Edward Almasy (almasy@axisdata.com)
#
#   Part of the AxisPHP library v1.2.5
#   For more information see http://www.axisdata.com/AxisPHP/
#

/**
* SQL database abstraction object with smart query caching.
* \nosubgrouping
*/
class Database {

    # ---- PUBLIC INTERFACE --------------------------------------------------

    /** @name Setup/Initialization */ /*@(*/

    /**
    * Object constructor.  If user name, password, or database name are omitted
    * they must have been set earlier with SetGlobalServerInfo() and
    * SetGlobalDatabaseName().
    * @param UserName User name to use to log in to database server.  (OPTIONAL)
    * @param Password Password to use to log in to database server.  (OPTIONAL)
    * @param DatabaseName Name of database to use once logged in.  (OPTIONAL)
    * @param HostName Host name of system on which database server resides.  (OPTIONAL, defaults to "localhost")
    * @see SetGlobalServerInfo()
    * @see SetGlobalDatabaseName()
    */
    function Database(
            $UserName = NULL, $Password = NULL, $DatabaseName = NULL, $HostName = NULL)
    {
        # save DB access values
        $this->DBUserName = $UserName ? $UserName : Database::$GlobalDBUserName;
        $this->DBPassword = $Password ? $Password : Database::$GlobalDBPassword;
        $this->DBHostName = $HostName ? $HostName : 
                (isset(Database::$GlobalDBHostName) ? Database::$GlobalDBHostName 
                    : "localhost");
        $this->DBName = $DatabaseName ? $DatabaseName : Database::$GlobalDBName;

        # open connection to DB server
        $this->Handle = mysql_connect(
                $this->DBHostName, $this->DBUserName, $this->DBPassword)
                or die("could not connect to database");

        # select DB
        mysql_select_db($this->DBName, $this->Handle)
                or die(mysql_error($this->Handle));
    }

    /** @cond */
    /**
    * Specify variables to be saved when serialized.
    */
    function __sleep()
    {
        return array("DBUserName", "DBPassword", "DBHostName", "DBName");
    }
    /**
    * Restore database connection when unserialized.
    */
    function __wakeup()
    {
        # open connection to DB server
        $this->Handle = mysql_connect(
                $this->DBHostName, $this->DBUserName, $this->DBPassword)
                or die("could not connect to database");

        # select DB
        mysql_select_db($this->DBName, $this->Handle)
                or die(mysql_error($this->Handle));
    }
    /** @endcond */

    /**
    * Set default login and host info for database server.
    * @param UserName User name to use to log in to database server.
    * @param Password Password to use to log in to database server.
    * @param HostName Host name of system on which database server resides.  (OPTIONAL, defaults to "localhost")
    */
    static function SetGlobalServerInfo($UserName, $Password, $HostName = "localhost")
    {
        Database::$GlobalDBUserName = $UserName;
        Database::$GlobalDBPassword = $Password;
        Database::$GlobalDBHostName = $HostName;
    }

    /**
    * Set default database name.
    * @param DatabaseName Name of database to use once logged in.
    */
    static function SetGlobalDatabaseName($DatabaseName)
    {
        Database::$GlobalDBName = $DatabaseName;
    }

    /**
    * Get host name of system on which database server resides.
    * @return Host name of database server.
    * @see SetGlobalServerInfo()
    */
    function DBHostName() {  return $this->DBHostName;  }

    /**
    * Get current database name.
    * @return Database name.
    * @see SetGlobalDatabaseName()
    */
    function DBName() {  return $this->DBName;  }

    /**
    * Get name used to connect with database server.
    * @return Login name.
    * @see SetGlobalServerInfo()
    */
    function DBUserName() {  return $this->DBUserName;  }

    /**
    * Get or set whether query result caching is currently enabled.
    * Caching is <b>enabled</b> by default.
    * This setting applies to <b>all</b> instances of the Database class.
    * @param NewSetting TRUE to enable caching or FALSE to disable.  (OPTIONAL)
    * @return Current caching setting.
    */
    static function Caching($NewSetting = NULL)
    {
        # if cache setting has changed
        if (($NewSetting !== NULL) && ($NewSetting != Database::$CachingFlag))
        {
            # save new setting
            Database::$CachingFlag = $NewSetting;

            # clear any existing cached results
            Database::$QueryResultCache = array();
        }

        # return current setting to caller
        return Database::$CachingFlag;
    }

    /**
    * Get or set whether advanced query result cachine is currently enabled.
    * Advanced caching attempts to determine whether a query has modified any
    * of the referenced tables since the data was last cached.
    * Advanced caching is <b>disabled</b> by default.
    * This setting applies to <b>all</b> instances of the Database class.
    * @param NewSetting TRUE to enable advanced caching or FALSE to disable.  (OPTIONAL)
    * @return Current advanced caching setting.
    */
    static function AdvancedCaching($NewSetting = NULL)
    {
        if ($NewSetting !== NULL)
        {
            Database::$AdvancedCachingFlag = $NewSetting;
        }
        return Database::$AdvancedCachingFlag;
    }

    /*@)*/ /* Setup/Initialization */
    /** @name Data Manipulation */ /*@(*/

    /**
    * Query database (with caching if enabled).
    * @param QueryString SQL query string.
    * @param FieldName Name of field for which to return value to caller.  (OPTIONAL)
    * @return Query handle or retrieved value if FieldName supplied.  Returns FALSE on error.
    */
    function Query($QueryString, $FieldName = "")
    {
        # if caching is enabled
        if (Database::$CachingFlag)
        {
            # if SQL statement is read-only
            if ($this->IsReadOnlyStatement($QueryString))
            {
                # if we have statement in cache
                if (isset(Database::$QueryResultCache[$QueryString]["NumRows"]))
                {
                    if (Database::$QueryDebugOutputFlag) {  print("DB-C: $QueryString<br>\n");  }

                    # make sure query result looks okay
                    $this->QueryHandle = TRUE;

                    # increment cache hit counter
                    Database::$CachedQueryCounter++;

                    # make local copy of results
                    $this->QueryResults = Database::$QueryResultCache[$QueryString];
                    $this->NumRows = Database::$QueryResultCache[$QueryString]["NumRows"];

                    # set flag to indicate that query results should be retrieved from cache
                    $this->GetResultsFromCache = TRUE;
                }
                else
                {
                    # execute SQL statement
                    if (Database::$QueryDebugOutputFlag) {  print("DB: $QueryString<br>\n");  }
                    $this->QueryHandle = mysql_query($QueryString, $this->Handle)
                            or die("<br>SQL Statement: <i>".$QueryString."</i><br>\nSQL Error: <i>".mysql_error($this->Handle)."</i>\n");

                    # save number of rows in result
                    $this->NumRows = mysql_num_rows($this->QueryHandle);

                    # if too many rows to cache
                    if ($this->NumRows >= 50)
                    {
                        # set flag to indicate that query results should not be retrieved from cache
                        $this->GetResultsFromCache = FALSE;
                    }
                    else
                    {
                        # if advanced caching is enabled
                        if (Database::$AdvancedCachingFlag)
                        {
                            # save tables accessed by query
                            Database::$QueryResultCache[$QueryString]["TablesAccessed"] = $this->TablesAccessed($QueryString);
                        }

                        # if rows found
                        if ($this->NumRows > 0)
                        {
                            # load query results
                            for ($Row = 0;  $Row < $this->NumRows;  $Row++)
                            {
                                $this->QueryResults[$Row] = mysql_fetch_assoc($this->QueryHandle);
                            }

                            # cache query results
                            Database::$QueryResultCache[$QueryString] = $this->QueryResults;
                        }
                        else
                        {
                            # clear local query results
                            unset($this->QueryResults);
                        }

                        # cache number of rows
                        Database::$QueryResultCache[$QueryString]["NumRows"] = $this->NumRows;

                        # set flag to indicate that query results should be retrieved from cache
                        $this->GetResultsFromCache = TRUE;
                    }
                }
            }
            else
            {
                # if advanced caching is enabled
                if (Database::$AdvancedCachingFlag)
                {
                    # if table modified by statement is known
                    $TableModified = $this->TableModified($QueryString);
                    if ($TableModified)
                    {
                        # for each cached query
                        foreach (Database::$QueryResultCache as $CachedQueryString => $CachedQueryResult)
                        {
                            # if we know what tables were accessed
                            if ($CachedQueryResult["TablesAccessed"])
                            {
                                # if tables accessed include the one we may modify
                                if (in_array($TableModified, $CachedQueryResult["TablesAccessed"]))
                                {
                                    # clear cached query results
                                    unset($GLOBALS["APDBQueryResultCache"][$CachedQueryString]);
                                }
                            }
                            else
                            {
                                # clear cached query results
                                unset($GLOBALS["APDBQueryResultCache"][$CachedQueryString]);
                            }
                        }
                    }
                    else
                    {
                        # clear entire query result cache
                        Database::$QueryResultCache = array();
                    }
                }
                else
                {
                    # clear entire query result cache
                    Database::$QueryResultCache = array();
                }

                # execute SQL statement
                if (Database::$QueryDebugOutputFlag) {  print("DB: $QueryString<br>\n");  }
                $this->QueryHandle = mysql_query($QueryString, $this->Handle)
                        or die("<br>SQL Statement: <i>".$QueryString."</i><br>\nSQL Error: <i>".mysql_error($this->Handle)."</i>\n");

                # set flag to indicate that query results should not be retrieved from cache
                $this->GetResultsFromCache = FALSE;
            }

            # reset row counter
            $this->RowCounter = 0;

            # increment query counter
            Database::$QueryCounter++;
        }
        else
        {
            # execute SQL statement
            if (Database::$QueryDebugOutputFlag) {  print("DB: $QueryString<br>\n");  }
            $this->QueryHandle = mysql_query($QueryString, $this->Handle)
                    or die(mysql_error($this->Handle));
        }

        if (($FieldName != "") && ($this->QueryHandle != FALSE)) 
        {
            return $this->FetchField($FieldName);
        }
        else
        {
            return $this->QueryHandle;
        }
    }

    /**
    * Get number of rows returned by last query.
    * @return Number of database rows selected by last query.
    */
    function NumRowsSelected()
    {
        # if caching is enabled and query was cached
        if (Database::$CachingFlag && $this->GetResultsFromCache)
        {
            # return cached number of rows to caller
            return $this->NumRows;
        }
        else
        {
            # retrieve number of rows and return to caller
            return mysql_num_rows($this->QueryHandle);
        }
    }

    /**
    * Get next database row retrieved by most recent query.
    * @return Array of database values with field names for indexes.  Returns FALSE if no more rows are available.
    */
    function FetchRow()
    {
        # if caching is enabled and query was cached
        if (Database::$CachingFlag && $this->GetResultsFromCache)
        {
            # if rows left to return
            if ($this->RowCounter < $this->NumRows)
            {
                # retrieve row from cache
                $Result = $this->QueryResults[$this->RowCounter];

                # increment row counter
                $this->RowCounter++;
            }
            else
            {
                # return nothing
                $Result = FALSE;
            }
        }
        else
        {
            # retrieve row from DB
            $Result = mysql_fetch_assoc($this->QueryHandle);
        }
        
        # return row to caller
        return $Result;
    }

    /**
    * Get specified number of database rows retrieved by most recent query.
    * @param NumberOfRows Maximum number of rows to return.  (OPTIONAL -- if not specified then all available rows are returned)
    * @return Array of rows.  Each row is an associative array indexed by field name.
    */
    function FetchRows($NumberOfRows = NULL)
    {
        # assume no rows will be returned
        $Result = array();

        # for each available row
        $RowsFetched = 0;
        while ((($RowsFetched < $NumberOfRows) || ($NumberOfRows == NULL)) && ($Row = $this->FetchRow()))
        {
            # add row to results
            $Result[] = $Row;
            $RowsFetched++;
        }

        # return array of rows to caller
        return $Result;
    }
    
    /**
    * Get all available values for specified database field retrieved by most
    * recent query.  If a second database field name is specified then the array
    * returned will be indexed by the values from that field.  If all index field
    * values are not unique then some values will be overwritten.
    *
    * A common use for this method is to retrieve a set of values with an ID field
    * specified for the index:<br>
    *  <code>$CNames = $DB->FetchColumn("ControlledName", "ControlledNameId");</code>
    * @param FieldName Name of database field.
    * @param IndexFieldName Name of second database field to use for array index.  (OPTIONAL)
    * @return Array of values from specified field, indexed numerically.  If IndexFieldName is supplied then array will be indexed by corresponding values from that field.
    */
    function FetchColumn($FieldName, $IndexFieldName = NULL)
    {
        $Array = array();
        while ($Record = $this->FetchRow())
        {
            if ($IndexFieldName != NULL)
            {
                $Array[$Record[$IndexFieldName]] = $Record[$FieldName];
            }
            else
            {
                $Array[] = $Record[$FieldName];
            }
        }
        return $Array;
    }
 
    /**
    * Pull next row from last DB query and get a specific value from that row.
    * This is a convenience method that in effect combines a FetchRow() with getting
    * a value from the array returned.  This method <b>does</b> advance the pointer
    * to the next row returned by the query each time it is called.
    * @param FieldName Name of field.
    * @return Value from specified field.
    */
    function FetchField($FieldName)
    {
        $Record = $this->FetchRow();
        return isset($Record[$FieldName]) ? $Record[$FieldName] : NULL;
    }

    /**
    * Get ID of row added by the last SQL "INSERT" statement.  It should be
    * called immediately after the INSERT statement query.  This method uses the
    * SQL "LAST_INSERT_ID()" function.
    * @param TableName Name of SQL table into which row was inserted.
    * @return Numerical ID value.
    */
    function LastInsertId($TableName)
    {
        return (int)$this->Query(
                "SELECT LAST_INSERT_ID() AS InsertId FROM ".$TableName, 
                "InsertId");
    }

    /**
    * A convenience function to get or set a value in the database.  This will
    * typically be called from inside a private convenience method within an object
    * that supplies the table name, condition, and cache.
    * @param TableName Name of database table.
    * @param FieldName Name of database field.
    * @param NewValue New value to set.  Use DB_NOVALUE to not set a new value (i.e. when just getting a value).
    * @param Condition SQL query conditional to use in SELECT or UPDATE statements (should not include "WHERE").  Use NULL if no conditional is needed.
    * @param CachedRecord Variable to use to cache values.  Accessed by reference.
    * @return Requested value.
    */
    function UpdateValue(
            $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
    {
        # expand condition if supplied
        if ($Condition != NULL) {  $Condition = " WHERE ".$Condition;  }

        # read cached record from database if not already loaded
        if (!isset($CachedRecord))
        {
            $this->Query("SELECT * FROM ".$TableName." ".$Condition);
            $CachedRecord = $this->FetchRow();
        }

        # if new value supplied
        if ($NewValue !== DB_NOVALUE)
        {
            # update value in database
            $this->Query("UPDATE $TableName SET $FieldName = "
                    .(($NewValue === NULL) ? "NULL" : "'".addslashes($NewValue)."'")
                    .$Condition);

            # update value in cached record
            $CachedRecord[$FieldName] = $NewValue;
        }

        # return value from cached record to caller
        return isset($CachedRecord[$FieldName]) 
                ? $CachedRecord[$FieldName] : NULL;
    }
    
    /*@)*/ /* Data Manipulation */
    /** @name Miscellaneous */ /*@(*/

    /**
    * Peform query that consists of SQL comment statement.  This is used primarily
    * when query debug output is turned on, to insert additional information into
    * the query stream.
    * @param String Debug string.
    */
    function LogComment($String)
    {
        $this->Query("-- ".$String);
    }
 
    /**
    * Get whether specified field exists in specified table.
    * @param TableName Name of database table.
    * @param FieldName Name of database field.
    * @return TRUE if table and field exist, or FALSE otherwise.
    */
    function FieldExists($TableName, $FieldName)
    {
        $this->Query("DESC ".$TableName);
        while ($CurrentFieldName = $this->FetchField("Field"))
        {
            if ($CurrentFieldName == $FieldName) {  return TRUE;  }
        }
        return FALSE;
    }

    /**
    * Enable or disable debugging output for queries.  Output is disabled by default.
    * This setting applies to <b>all</b> instances of the Database class.
    * @param NewSetting TRUE to enable output or FALSE to disable output.
    */
    static function QueryDebugOutput($NewSetting)
    {
        Database::$QueryDebugOutputFlag = $NewSetting;
    }

    /**
    * Get the number of queries that have been run since program execution began.
    * The value returned is for <b>all</b> instances of the Database class.
    * @return Number of queries.
    */
    static function NumQueries()
    {
        return Database::$QueryCounter;
    }

    /**
    * Get the number of queries that have resulted in cache hits since program
    * execution began.
    * The value returned is for <b>all</b> instances of the Database class.
    * @return Number of queries that resulted in cache hits.
    */
    static function NumCacheHits()
    {
        return Database::$CachedQueryCounter;
    }

    /**
    * Get the ratio of query cache hits to queries as a percentage.
    * The value returned is for <b>all</b> instances of the Database class.
    * @return Percentage of queries that resulted in hits.
    */
    static function CacheHitRate()
    {
        if (Database::$QueryCounter)
        {
            return (Database::$CachedQueryCounter / Database::$QueryCounter) * 100;
        }
        else
        {
            return 0;
        }
    }

    /*@)*/ /* Miscellaneous */

    # ---- PRIVATE INTERFACE -------------------------------------------------

    protected $DBUserName;
    protected $DBPassword;
    protected $DBHostName;
    protected $DBName;

    private $Handle;
    private $QueryHandle;
    private $QueryResults;
    private $RowCounter;
    private $NumRows;
    private $GetResultsFromCache;

    private static $GlobalDBUserName;
    private static $GlobalDBPassword;
    private static $GlobalDBHostName;
    private static $GlobalDBName;

    # debug output flag
    private static $QueryDebugOutputFlag = FALSE;
    # flag for whether caching is turned on
    private static $CachingFlag = TRUE;
    # query result advanced caching flag
    private static $AdvancedCachingFlag = FALSE;
    # global cache for query results
    private static $QueryResultCache = array();
    # stats counters
    private static $QueryCounter = 0;
    private static $CachedQueryCounter = 0;

    # determine whether SQL statement is one that modifies data
    private function IsReadOnlyStatement($QueryString)
    {
        return preg_match("/^[ ]*SELECT /i", $QueryString) ? TRUE : FALSE;
    }

    # try to determine table modified by statement (returns FALSE if unknown)
    private function TableModified($QueryString)
    {
        # assume we're not going to be able to determine table
        $TableName = FALSE;

        # split query into pieces
        $QueryString = trim($QueryString);
        $Words = preg_split("/\s+/", $QueryString);

        # if INSERT statement
        $WordIndex = 1;
        if (strtoupper($Words[0]) == "INSERT")
        {
            # skip over modifying keywords
            while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
                    || (strtoupper($Words[$WordIndex]) == "DELAYED")
                    || (strtoupper($Words[$WordIndex]) == "IGNORE")
                    || (strtoupper($Words[$WordIndex]) == "INTO"))
            {
                $WordIndex++;
            }

            # next word is table name
            $TableName = $Words[$WordIndex];
        }
        # else if UPDATE statement
        elseif (strtoupper($Words[0]) == "UPDATE")
        {
            # skip over modifying keywords
            while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
                    || (strtoupper($Words[$WordIndex]) == "IGNORE"))
            {
                $WordIndex++;
            }

            # if word following next word is SET
            if (strtoupper($Words[$WordIndex + 1]) == "SET")
            {
                # next word is table name
                $TableName = $Words[$WordIndex];
            }
        }
        # else if DELETE statement
        elseif (strtoupper($Words[0]) == "DELETE")
        {
            # skip over modifying keywords
            while ((strtoupper($Words[$WordIndex]) == "LOW_PRIORITY")
                    || (strtoupper($Words[$WordIndex]) == "IGNORE")
                    || (strtoupper($Words[$WordIndex]) == "QUICK"))
            {
                $WordIndex++;
            }

            # if next term is FROM
            if (strtoupper($Words[$WordIndex]) == "FROM")
            {
                # next word is table name
                $WordIndex++;
                $TableName = $Words[$WordIndex];
            }
        }

        # discard table name if it looks at all suspicious
        if ($TableName)
        {
            if (!preg_match("/[a-zA-Z0-9]+/", $TableName))
            {
                $TableName = FALSE;
            }
        }

        # return table name (or lack thereof) to caller
        return $TableName;
    }

    # try to determine tables accessed by statement (returns FALSE if unknown)
    private function TablesAccessed($QueryString)
    {
        # assume we're not going to be able to determine tables
        $TableNames = FALSE;

        # split query into pieces
        $QueryString = trim($QueryString);
        $Words = preg_split("/\s+/", $QueryString);
        $UQueryString = strtoupper($QueryString);
        $UWords = preg_split("/\s+/", $UQueryString);

        # if SELECT statement
        if ($UWords[0] == "SELECT")
        {
            # keep going until we hit FROM or last word
            $WordIndex = 1;
            while (($UWords[$WordIndex] != "FROM") 
                    && strlen($UWords[$WordIndex]))
            {
                $WordIndex++;
            }

            # if we hit FROM
            if ($UWords[$WordIndex] == "FROM")
            {
                # for each word after FROM
                $WordIndex++;
                while (strlen($UWords[$WordIndex]))
                {
                    # if current word ends with comma
                    if (preg_match("/,$/", $Words[$WordIndex]))
                    {
                        # strip off comma and add word to table name list
                        $TableNames[] = substr($Words[$WordIndex], 0, -1);
                    }
                    else
                    {
                        # add word to table name list
                        $TableNames[] = $Words[$WordIndex];

                        # if next word is not comma
                        $WordIndex++;
                        if ($Words[$WordIndex] != ",")
                        {
                            # if word begins with comma
                            if (preg_match("/^,/", $Words[$WordIndex]))
                            {
                                # strip off comma (NOTE: modifies $Words array!)
                                $Words[$WordIndex] = substr($Words[$WordIndex], 1);

                                # decrement index so we start with this word next pass
                                $WordIndex--;
                            }
                            else
                            {
                                # stop scanning words (non-basic JOINs not yet handled)
                                break;
                            }
                        }
                    }

                    # move to next word
                    $WordIndex++;
                }
            }
        }

        # discard table names if they look at all suspicious
        if ($TableNames)
        {
            foreach ($TableNames as $Name)
            {
                if (!preg_match("/^[a-zA-Z0-9]+$/", $Name))
                {
                    $TableNames = FALSE;
                    break;
                }
            }
        }

        # return table name (or lack thereof) to caller
        return $TableNames;
    }
}

# define return values  (numerical values correspond to MySQL error codes)
define("DB_OKAY",               0);
define("DB_ERROR",              1);
define("DB_ACCESSDENIED",       2);
define("DB_UNKNOWNDB",          3);
define("DB_UNKNOWNTABLE",       4);
define("DB_SYNTAXERROR",        5);
define("DB_DBALREADYEXISTS",    6);
define("DB_DBDOESNOTEXIST",     7);
define("DB_DISKFULL",           8);

# define value to designate omitted arguments (so DB values can be set to NULL)
define("DB_NOVALUE", "!-_-_-DB_NOVALUE-_-_-!");

# MySQL error code mapping
$APDBErrorCodeMappings = array(
        1045    => DB_ACCESSDENIED,
        1049    => DB_UNKNOWNDB,
        1046    => DB_UNKNOWNTABLE,
        1064    => DB_SYNTAXERROR,
        1007    => DB_DBALREADYEXISTS,  # ?  (not sure)
        1008    => DB_DBDOESNOTEXIST,   # ?  (not sure)
        1021    => DB_DISKFULL,         # ?  (not sure)
        );

?>
