<?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 string $UserName User name to use to log in to database server.  (OPTIONAL)
    * @param string $Password Password to use to log in to database server.  (OPTIONAL)
    * @param string $DatabaseName Name of database to use once logged in.  (OPTIONAL)
    * @param string $HostName Host name of system on which database server resides.
    *       (OPTIONAL, defaults to "localhost")
    * @throws Exception When unable to connect to database server or select
    *       specified database.
    * @see Database::SetGlobalServerInfo()
    * @see Database::SetGlobalDatabaseName()
    */
    public function __construct(
            $UserName = NULL, $Password = NULL, $DatabaseName = NULL, $HostName = NULL)
    {
        # save DB access parameter values
        $this->DBUserName = $UserName ? $UserName : self::$GlobalDBUserName;
        $this->DBPassword = $Password ? $Password : self::$GlobalDBPassword;
        $this->DBHostName = $HostName ? $HostName :
                (isset(self::$GlobalDBHostName) ? self::$GlobalDBHostName
                    : "localhost");
        $this->DBName = $DatabaseName ? $DatabaseName : self::$GlobalDBName;

        # set memory threshold for cache clearing
        if (!isset(self::$CacheMemoryThreshold))
        {
            self::$CacheMemoryThreshold = self::GetPhpMemoryLimit() / 4;
        }

        # if we don't already have a connection or DB access parameters were supplied
        $HandleIndex = $this->DBHostName.":".$this->DBName;
        if (!array_key_exists($HandleIndex, self::$ConnectionHandles)
                || $UserName || $Password || $DatabaseName || $HostName)
        {
            # open connection to DB server and select database
            $this->Handle = self::ConnectAndSelectDB($this->DBHostName,
                    $this->DBUserName, $this->DBPassword, $this->DBName);
            self::$ConnectionHandles[$HandleIndex] = $this->Handle;
        }
        else
        {
            # set local connection handle
            $this->Handle = self::$ConnectionHandles[$HandleIndex];
        }
    }

    /** @cond */
    /**
    * Specify variables to be saved when serialized.
    */
    public function __sleep()
    {
        return array("DBUserName", "DBPassword", "DBHostName", "DBName");
    }
    /**
    * Restore database connection when unserialized.
    * @throws Exception When unable to connect to database server or select
    *       specified database.
    */
    public function __wakeup()
    {
        # if we don't already have a database server connection
        $HandleIndex = $this->DBHostName.":".$this->DBName;
        if (!array_key_exists($HandleIndex, self::$ConnectionHandles))
        {
            # open connection to DB server and select database
            $this->Handle = self::ConnectAndSelectDB($this->DBHostName,
                    $this->DBUserName, $this->DBPassword, $this->DBName);
            self::$ConnectionHandles[$HandleIndex] = $this->Handle;
        }
        else
        {
            # set local connection handle
            $this->Handle = self::$ConnectionHandles[$HandleIndex];
        }
    }
    /** @endcond */

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

        # clear any existing DB connection handles
        self::$ConnectionHandles = array();
    }

    /**
    * Set default database name.
    * @param string $DatabaseName Name of database to use once logged in.
    */
    public static function SetGlobalDatabaseName($DatabaseName)
    {
        # save new default DB name
        self::$GlobalDBName = $DatabaseName;

        # clear any existing DB connection handles
        self::$ConnectionHandles = array();
    }

    /**
    * Set default database storage engine.
    * @param string $Engine New default storage engine.
    */
    public function SetDefaultStorageEngine($Engine)
    {
        # choose config variable to use based on server version number
        $ConfigVar = version_compare($this->GetServerVersion(), "5.5", "<")
                ? "storage_engine" : "default_storage_engine";

        # set storage engine in database
        $this->Query("SET ".$ConfigVar." = ".$Engine);
    }

    /**
    * Get database server version number.
    * @param bool $FullVersion TRUE for the whole version string or
    *     FALSE for just the version number (OPTIONAL, default FALSE).
    * @return string Version number.
    */
    public function GetServerVersion($FullVersion=FALSE)
    {
        # retrieve version string
        $Version = $this->Query("SELECT VERSION() AS ServerVer", "ServerVer");

        if (!$FullVersion)
        {
            # strip off any build/config suffix
            $Pieces = explode("-", $Version);
            $Version = array_shift($Pieces);
        }

        # return version number to caller
        return $Version;
    }

    /**
    * Get version number of the client libraries being used to connect
    * to the database server (Currently the mysql library version
    * number).
    * @return string client library version number (e.g., 5.1.73)
    *   should be version_compare()-able, as long as mysql doesn't
    *   change their version numbering.
    */
    public function GetClientVersion()
    {
        return mysqli_get_client_info();
    }

    /**
    * Get database connection type and hostname.
    * @return string describing the database connection
    *   (e.g. "Locahost via UNIX socket").
    */
    public function GetHostInfo()
    {
        return mysqli_get_host_info($this->Handle);
    }

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

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

    /**
    * Get name used to connect with database server.
    * @return Login name.
    * @see SetGlobalServerInfo()
    */
    public 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 bool $NewSetting TRUE to enable caching or FALSE to disable.  (OPTIONAL)
    * @return Current caching setting.
    */
    public static function Caching($NewSetting = NULL)
    {
        # if cache setting has changed
        if (($NewSetting !== NULL) && ($NewSetting != self::$CachingFlag))
        {
            # save new setting
            self::$CachingFlag = $NewSetting;

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

        # return current setting to caller
        return self::$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 bool $NewSetting TRUE to enable advanced caching or FALSE to
    *       disable.  (OPTIONAL)
    * @return Current advanced caching setting.
    */
    public static function AdvancedCaching($NewSetting = NULL)
    {
        if ($NewSetting !== NULL)
        {
            self::$AdvancedCachingFlag = $NewSetting;
        }
        return self::$AdvancedCachingFlag;
    }

    /**
    * Set query errors to ignore.  The command and error message patterns should be
    *       formatted for preg_match().  For example:
    * @code
    * $SqlErrorsWeCanIgnore = array(
    *        "/ALTER TABLE [a-z]+ ADD COLUMN/i" => "/Duplicate column name/i",
    *        "/CREATE TABLE /i" => "/Table '[a-z0-9_]+' already exists/i",
    *        );
    * @endcode
    * @param array $ErrorsToIgnore Associative array containing errors to ignore when
    *       running queries, with patterns for SQL commands as the indexes and the
    *       patterns for the SQL error messages as the values.  Pass in NULL to
    *       clear list of errors to ignore.
    * @param bool $NormalizeWhitespace If TRUE, incoming SQL patterns have any
    *       whitespace within them replaced with "\s+" so that variations in
    *       whitespace within SQL will not cause the pattern to fail.
    *       (OPTIONAL, defaults to TRUE)
    * @see Database::IgnoredError()
    */
    public function SetQueryErrorsToIgnore($ErrorsToIgnore, $NormalizeWhitespace = TRUE)
    {
        if ($NormalizeWhitespace && ($ErrorsToIgnore !== NULL))
        {
            $RevisedErrorsToIgnore = array();
            foreach ($ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
            {
                $SqlPattern = preg_replace("/\\s+/", "\\s+", $SqlPattern);
                $RevisedErrorsToIgnore[$SqlPattern] = $ErrMsgPattern;
            }
            $ErrorsToIgnore = $RevisedErrorsToIgnore;
        }
        $this->ErrorsToIgnore = $ErrorsToIgnore;
    }

    /**
    * Check whether an error was ignored by the most recent query.
    * @return mixed Error message if an error was ignored, otherwise FALSE.
    * @see Database::SetQueryErrorsToIgnore()
    */
    public function IgnoredError()
    {
        return $this->ErrorIgnored;
    }

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

    /**
    * Query database (with caching if enabled).  It's important to keep in
    * mind that a query that returns no results is NOT the same as a query
    * that generates an error.
    * @param string $QueryString SQL query string.
    * @param string $FieldName Name of field for which to return value to
    *       caller.  (OPTIONAL)
    * @return Query handle, retrieved value (if FieldName supplied), or
    *       FALSE on error.
    */
    public function Query($QueryString, $FieldName = "")
    {
        # clear flag that indicates whether query error was ignored
        $this->ErrorIgnored = FALSE;

        # if caching is enabled
        if (self::$CachingFlag)
        {
            # if SQL statement is read-only
            if ($this->IsReadOnlyStatement($QueryString))
            {
                # if we have statement in cache
                if (isset(self::$QueryResultCache[$QueryString]["NumRows"]))
                {
                    if (self::$QueryDebugOutputFlag)
                            {  print("DB-C: $QueryString<br>\n");  }

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

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

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

                    # set flag to indicate that results should be retrieved from cache
                    $this->GetResultsFromCache = TRUE;
                }
                else
                {
                    # execute SQL statement
                    $this->QueryHandle = $this->RunQuery($QueryString);
                    if (!$this->QueryHandle instanceof mysqli_result) {  return FALSE;  }

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

                    # if too many rows to cache
                    if ($this->NumRows >= self::$CacheRowsThreshold)
                    {
                        # set flag to indicate that query results should not
                        #       be retrieved from cache
                        $this->GetResultsFromCache = FALSE;
                    }
                    else
                    {
                        # if we are low on memory
                        if (self::GetFreeMemory() < self::$CacheMemoryThreshold)
                        {
                            # clear out all but last few rows from cache
                            self::$QueryResultCache = array_slice(
                                    self::$QueryResultCache,
                                    (0 - self::$CacheRowsToLeave));
                        }

                        # if advanced caching is enabled
                        if (self::$AdvancedCachingFlag)
                        {
                            # save tables accessed by query
                            self::$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] =
                                        mysqli_fetch_assoc($this->QueryHandle);
                            }

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

                        # cache number of rows
                        self::$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 (self::$AdvancedCachingFlag)
                {
                    # if table modified by statement is known
                    $TableModified = $this->TableModified($QueryString);
                    if ($TableModified)
                    {
                        # for each cached query
                        foreach (self::$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(self::$QueryResultCache[$CachedQueryString]);
                                }
                            }
                            else
                            {
                                # clear cached query results
                                unset(self::$QueryResultCache[$CachedQueryString]);
                            }
                        }
                    }
                    else
                    {
                        # clear entire query result cache
                        self::$QueryResultCache = array();
                    }
                }
                else
                {
                    # clear entire query result cache
                    self::$QueryResultCache = array();
                }

                # execute SQL statement
                $this->QueryHandle = $this->RunQuery($QueryString);
                if ($this->QueryHandle === FALSE) {  return FALSE;  }

                # 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
            self::$QueryCounter++;
        }
        else
        {
            # execute SQL statement
            $this->QueryHandle = $this->RunQuery($QueryString);
            if ($this->QueryHandle === FALSE) {  return FALSE;  }
        }

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

    /**
    * Execute queries from specified file.  Comment lines are ignored.
    * Multiple queries on a single line are not handled.  Execution continues
    * until all queries are run or an error occurs that has not been
    * previously specified to be ignored.  If a query fails, information
    * about the failure can be retrieved with QueryErrMsg() and QueryErrNo().
    * @param string $FileName Name of file to load queries from.
    * @return Number of queries executed or NULL if query failed.
    * @see Database::SetQueryErrorsToIgnore()
    * @see Database::QueryErrMsg()
    * @see Database::QueryErrNo()
    */
    public function ExecuteQueriesFromFile($FileName)
    {
        # open file
        $FHandle = fopen($FileName, "r");

        # if file open succeeded
        if ($FHandle !== FALSE)
        {
            # while lines left in file
            $Query = "";
            $QueryCount = 0;
            while (!feof($FHandle))
            {
                # read in line from file
                $Line = fgets($FHandle, 32767);

                # trim whitespace from line
                $Line = trim($Line);

                # if line is not empty and not a comment
                if (!preg_match("/^#/", $Line)
                        && !preg_match("/^--/", $Line)
                        && strlen($Line))
                {
                    # add line to current query
                    $Query .= " ".$Line;

                    # if line completes a query
                    if (preg_match("/;$/", $Line))
                    {
                        # run query
                        $QueryCount++;
                        $Result = $this->Query($Query);
                        $Query = "";

                        # if query resulted in an error that is not ignorable
                        if ($Result === FALSE)
                        {
                            # stop processing queries and set error code
                            $QueryCount = NULL;
                            break;
                        }
                    }
                }
            }

            # close file
            fclose($FHandle);
        }

        # return number of executed queries to caller
        return $QueryCount;
    }

    /**
    * Get most recent error message text set by Query().
    * @return Error message text from database server.
    * @see QueryErrNo()
    */
    public function QueryErrMsg()
    {
        return $this->ErrMsg;
    }

    /**
    * Get most recent error code set by Query().
    * @return Error code from database server.
    * @see QueryErrMsg()
    */
    public function QueryErrNo()
    {
        return $this->ErrNo;
    }

    /**
    * Get/set whether Query() errors will be displayed.  By default errors
    *       are not displayed.
    * @param bool $NewValue TRUE to display errors or FALSE to not display.  (OPTIONAL)
    * @return Current value of whether Query() errors will be displayed.
    */
    public static function DisplayQueryErrors($NewValue = NULL)
    {
        if ($NewValue !== NULL) {  self::$DisplayErrors = $NewValue;  }
        return self::$DisplayErrors;
    }

    /**
    * Get number of rows returned by last SELECT or SHOW query.
    * @return Number of database rows selected by last query.
    */
    public function NumRowsSelected()
    {
        # if caching is enabled and query was cached
        if (self::$CachingFlag && $this->GetResultsFromCache)
        {
            # return cached number of rows to caller
            return $this->NumRows;
        }
        else
        {
            # call to this method after an unsuccessful query
            if (!$this->QueryHandle instanceof mysqli_result)
            {
                return 0;
            }

            # retrieve number of rows and return to caller
            return mysqli_num_rows($this->QueryHandle);
        }
    }

    /**
    * Get number of rows affected by last INSERT, UPDATE, REPLACE,
    * or DELETE query.
    * @return Number of database rows affected by last query.
    */
    public function NumRowsAffected()
    {
        # call to this method after an unsuccessful query
        if (!$this->QueryHandle instanceof mysqli_result)
        {
            return 0;
        }

        # retrieve number of rows and return to caller
        return mysqli_affected_rows($this->Handle);
    }

    /**
    * 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.
    */
    public function FetchRow()
    {
        # if caching is enabled and query was cached
        if (self::$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
        {
            # call to this method after successful query
            if ($this->QueryHandle instanceof mysqli_result)
            {
                $Result = mysqli_fetch_assoc($this->QueryHandle);
                if ($Result === NULL) { $Result = FALSE; }
            }

            # call to this method after unsuccessful query
            else
            {
                $Result = FALSE;
            }
        }

        # return row to caller
        return $Result;
    }

    /**
    * Get specified number of database rows retrieved by most recent query.
    * @param int $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.
    */
    public 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 string $FieldName Name of database field.
    * @param string $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.
    */
    public 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 string $FieldName Name of field.
    * @return Value from specified field.
    */
    public 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.
    * @return Numerical ID value.
    */
    public function LastInsertId()
    {
        return (int)$this->Query(
                "SELECT LAST_INSERT_ID() AS InsertId",
                "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 string $TableName Name of database table.
    * @param string $FieldName Name of database field.
    * @param mixed $NewValue New value to set.  Use DB_NOVALUE to not set a
    *       new value (i.e. when just getting a value).
    * @param string $Condition SQL query conditional to use in SELECT or UPDATE
    *       statements (should not include "WHERE").  Use NULL if no conditional
    *       is needed.
    * @param array $CachedRecord Variable to use to cache values.  Accessed by reference.
    * @return Requested value.
    */
    public 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" : "'"
                            .mysqli_real_escape_string($this->Handle, $NewValue)."'")
                    .$Condition);

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

        # return value from cached record to caller
        return isset($CachedRecord[$FieldName])
                ? $CachedRecord[$FieldName] : NULL;
    }

    /**
    * A convenience function to get or set an integer 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.  This method should be used
    * instead of UpdateValue() in situations where the incoming value specifically
    * needs to be forced to an int.
    * @param string $TableName Name of database table.
    * @param string $FieldName Name of database field.
    * @param int $NewValue New value to set.  Use DB_NOVALUE to not set a new
    *       value (i.e. when just getting a value).
    * @param string $Condition SQL query conditional to use in SELECT or UPDATE
    *       statements (should not include "WHERE").  Use NULL if no conditional
    *       is needed.
    * @param array $CachedRecord Variable to use to cache values.  Accessed by reference.
    * @return Requested value.
    */
    public function UpdateIntValue(
            $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
    {
        return $this->UpdateValue($TableName, $FieldName,
                (($NewValue === DB_NOVALUE) ? DB_NOVALUE : (int)$NewValue),
                $Condition, $CachedRecord);
    }

    /**
    * A convenience function to get or set a float 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.  This method should be used
    * instead of UpdateValue() in situations where the incoming value specifically
    * needs to be forced to an float.
    * @param string $TableName Name of database table.
    * @param string $FieldName Name of database field.
    * @param float $NewValue New value to set.  Use DB_NOVALUE to not set a
    *       new value (i.e. when just getting a value).
    * @param string $Condition SQL query conditional to use in SELECT or
    *       UPDATE statements (should not include "WHERE").  Use NULL if no
    *       conditional is needed.
    * @param array $CachedRecord Variable to use to cache values.  Accessed by reference.
    * @return Requested value.
    */
    public function UpdateFloatValue(
            $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
    {
        return $this->UpdateValue($TableName, $FieldName,
                (($NewValue === DB_NOVALUE) ? DB_NOVALUE : (float)$NewValue),
                $Condition, $CachedRecord);
    }

    /**
    * A convenience function to copy values from one row to another.  The ID
    * column value will not be copied.
    * @param string $TableName Name of table.
    * @param string $IdColumn Name of column containing ID value.
    * @param string $SrcId Value of ID column in source row.
    * @param mixed $DstId Value of ID column or array of values of ID columns
    *       in destination row(s).
    * @param array $ColumnsToExclude Names of additional columns to exclude
    *       from copy.  (OPTIONAL)
    */
    public function CopyValues($TableName, $IdColumn, $SrcId, $DstId,
            $ColumnsToExclude = array())
    {
        # retrieve names of all columns in table
        $AllColumns = $this->GetColumns($TableName);

        # remove columns to be excluded from copy
        $ColumnsToExclude[] = $IdColumn;
        $ColumnsToCopy = array_diff($AllColumns, $ColumnsToExclude);

        # normalize destination IDs
        $DstIds = is_array($DstId) ? $DstId : array($DstId);
        $DstIds = array_diff($DstIds, array($SrcId));

        # if there are columns to copy and we have destinations
        if (count($ColumnsToCopy) && count($DstIds))
        {
            # construct and execute query to perform copy
            $Query = "UPDATE `".$TableName."` AS Target"
                    ." LEFT JOIN `".$TableName."` AS Source"
                    ." ON Source.`".$IdColumn."` = '".addslashes($SrcId)."'";
            $QuerySets = array();
            foreach ($ColumnsToCopy as $ColumnName)
            {
                $QuerySets[] = "Target.`".$ColumnName."` = Source.`".$ColumnName."`";
            }
            $Query .= " SET ".implode(", ", $QuerySets);
            $QueryConditions = array();
            foreach ($DstIds as $Id)
            {
                $QueryConditions[] = "Target.`".$IdColumn."` = '".addslashes($DstId)."'";
            }
            $Query .= " WHERE ".implode(" OR ", $QueryConditions);
            $this->Query($Query);
        }
    }

    /*@)*/ /* Data Manipulation */
    /** @name Miscellaneous */ /*@(*/

    /**
    * Escape a string that may contain null bytes.  Normally,
    * addslashes() should be used for escaping.  However, addslashes()
    * does not correctly handle null bytes which can come up when
    * serializing PHP objects or dealing with binary data.
    * @param string $String String to escape.
    * @return string Escaped data
    */
    public function EscapeString($String)
    {
        return mysqli_real_escape_string($this->Handle, $String);
    }

    /**
    * 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 $String Debug string.
    */
    public function LogComment($String)
    {
        $this->Query("-- ".$String);
    }

    /**
    * Get whether specified table exists.
    * @param string $TableName Name of database table.
    * @return bool TRUE if table exists, or FALSE otherwise.
    */
    public function TableExists($TableName)
    {
        $this->Query("SHOW TABLES LIKE '".addslashes($TableName)."'");
        return $this->NumRowsSelected() ? TRUE : FALSE;
    }

    /**
    * Get whether specified field exists in specified table.
    * @param string $TableName Name of database table.
    * @param string $FieldName Name of database field.
    * @return bool TRUE if table and field exist, or FALSE otherwise.
    */
    public function FieldExists($TableName, $FieldName)
    {
        $this->Query("DESC ".$TableName);
        while ($CurrentFieldName = $this->FetchField("Field"))
        {
            if ($CurrentFieldName == $FieldName) {  return TRUE;  }
        }
        return FALSE;
    }

    /**
    * Get field (column) type.
    * @param string $TableName Name of database table.
    * @param string $FieldName Name of database field.
    * @return string Field type or NULL if field was not found.
    */
    public function GetFieldType($TableName, $FieldName)
    {
        $this->Query("DESC ".$TableName);
        $AllTypes = $this->FetchColumn("Type", "Field");
        return $AllTypes[$FieldName];
    }

    /**
    * Get column (database field) names.
    * @param string $TableName Name of database table.
    * @return array Field names.
    */
    public function GetColumns($TableName)
    {
        $this->Query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS"
                ." WHERE TABLE_SCHEMA = '".addslashes($this->DBName)
                ."' AND TABLE_NAME = '".addslashes($TableName)."'");
        return $this->FetchColumn("COLUMN_NAME");
    }

    /**
    * 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 bool $NewSetting TRUE to enable output or FALSE to disable output.
    */
    public static function QueryDebugOutput($NewSetting)
    {
        self::$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.
    */
    public static function NumQueries()
    {
        return self::$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.
    */
    public static function NumCacheHits()
    {
        return self::$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.
    */
    public static function CacheHitRate()
    {
        if (self::$QueryCounter)
        {
            return (self::$CachedQueryCounter / self::$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 $ErrorIgnored = FALSE;
    private $ErrorsToIgnore = NULL;
    private $ErrMsg = NULL;
    private $ErrNo = NULL;

    private static $DisplayErrors = FALSE;

    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;
    # database connection link handles
    private static $ConnectionHandles = array();
    # do not cache queries that return more than this number of rows
    private static $CacheRowsThreshold = 250;
    # prune the query cache if there is less than this amount of memory free
    private static $CacheMemoryThreshold;
    # number of rows to leave in cache when pruning
    private static $CacheRowsToLeave = 10;
    # number of retry attempts to make to connect to database
    private static $ConnectRetryAttempts = 3;
    # number of seconds to wait between connection retry attempts
    private static $ConnectRetryInterval = 5;

    # server connection error codes
    const CR_CONNECTION_ERROR = 2002;   # Can't connect to local MySQL server
                                        #   through socket '%s' (%d)
    const CR_CONN_HOST_ERROR = 2003;    # Can't connect to MySQL server on '%s' (%d)
    const CR_SERVER_GONE_ERROR = 2006;  # MySQL server has gone away
    const CR_SERVER_LOST = 2013;        # Lost connection to MySQL server during query

    # connection error codes that may be recoverable
    private static $RecoverableConnectionErrors = array(
            self::CR_CONNECTION_ERROR,
            );

    /**
    * Connect to database server and select database.
    * @param string $DBHostName Name of host database server is on.
    * @param string $DBUserName User name for logging in to server.
    * @param string $DBPassword Password for logging in to server.
    * @param string $DBName Name of database to select.
    * @return object Handle for database server connection.
    * @throws Exception When unable to connect to database server or select
    *       specified database.
    */
    private static function ConnectAndSelectDB(
            $DBHostName, $DBUserName, $DBPassword, $DBName)
    {
        $ConnectAttemptsLeft = self::$ConnectRetryAttempts + 1;
        do
        {
            # if this is not our first connection attempt
            if (isset($Handle))
            {
                # wait for the retry interval
                sleep(self::$ConnectRetryInterval);
            }

            # attempt to connect to server
            $Handle = @mysqli_connect($DBHostName, $DBUserName, $DBPassword);
            $ConnectAttemptsLeft--;
        }
        # repeat if we do not have a connection and there are retry attempts
        #       left and the connection error code indicates a retry may succeed
        // @codingStandardsIgnoreStart
        // (because phpcs apparently doesn't know how to handle do-while loops)
        while (!$Handle && $ConnectAttemptsLeft
                && in_array(mysqli_connect_errno(),
                        self::$RecoverableConnectionErrors));
        // @codingStandardsIgnoreEnd

        # throw exception if connection attempts failed
        if (!$Handle)
        {
            throw new Exception("Could not connect to database: "
                    .mysqli_connect_error()." (errno: ".mysqli_connect_errno().")");
        }

        # select DB
        $Result = mysqli_select_db($Handle, $DBName);
        if ($Result !== TRUE)
        {
            throw new Exception("Could not select database: "
                    .mysqli_error($Handle)." (errno: ".mysqli_errno($Handle).")");
        }

        # return new connection to caller
        return $Handle;
    }

    /**
    * Attempt to determine whether a specified SQL statement may modify data.
    * @param string $QueryString SQL query string to examine.
    * @return bool TRUE if statement is unlikely to modify data, otherwise FALSE.
    */
    private function IsReadOnlyStatement($QueryString)
    {
        return preg_match("/^[ ]*SELECT /i", $QueryString) ? TRUE : FALSE;
    }

    /**
    * Attempt to determine which tables might be modified by an SQL statement.
    * @param string $QueryString SQL query string to examine.
    * @return string Table name that will be modified, or FALSE if no table is
    *       modified or it is unclear what table will be modified.
    */
    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;
    }

    /**
    * Attempt to determine which tables might be accessed by an SQL statement.
    * @param string $QueryString SQL query string to examine.
    * @return array Array of table name that may be accessed, or FALSE if no
    *       table is accessed or it is unclear what tables may be accessed.
    */
    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;
    }

    /**
    * Run SQL query, ignoring or reporting errors as appropriate.
    * @param string $QueryString SQL query to run.
    * @return mixed SQL query handle or FALSE if query failed or TRUE if query
    *       failed but error was ignored.
    */
    private function RunQuery($QueryString)
    {
        # log query start time if debugging output is enabled
        if (self::$QueryDebugOutputFlag) {  $QueryStartTime = microtime(TRUE);  }

        # run query against database
        $this->QueryHandle = mysqli_query($this->Handle, $QueryString) ;

        # print query and execution time if debugging output is enabled
        if (self::$QueryDebugOutputFlag)
        {
            print "DB: ".$QueryString." ["
                    .sprintf("%.2f", microtime(TRUE) - $QueryStartTime)
                    ."s]"."<br>\n";
        }

        # if query failed and there are errors that we can ignore
        if (($this->QueryHandle === FALSE) && $this->ErrorsToIgnore)
        {
            # for each pattern for an error that we can ignore
            foreach ($this->ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
            {
                # if error matches pattern
                $ErrorMsg = mysqli_error($this->Handle);
                if (preg_match($SqlPattern, $QueryString)
                        && preg_match($ErrMsgPattern, $ErrorMsg))
                {
                    # set return value to indicate error was ignored
                    $this->QueryHandle = TRUE;

                    # set internal flag to indicate that an error was ignored
                    $this->ErrorIgnored = $ErrorMsg;

                    # stop looking at patterns
                    break;
                }
            }
        }

        # if query failed
        if ($this->QueryHandle === FALSE)
        {
            # clear stored value for number of rows retrieved
            $this->NumRows = 0;

            # retrieve error info
            $this->ErrMsg = mysqli_error($this->Handle);
            $this->ErrNo = mysqli_errno($this->Handle);

            # if we are supposed to be displaying errors
            if (self::$DisplayErrors)
            {
                # print error info
                print("<b>SQL Error:</b> <i>".$this->ErrMsg
                        ."</i> (".$this->ErrNo.")<br/>\n");
                print("<b>SQL Statement:</b> <i>"
                        .htmlspecialchars($QueryString)."</i><br/>\n");

                # retrieve execution trace that got us to this point
                $Trace = debug_backtrace();

                # remove current context from trace
                array_shift($Trace);

                # make sure file name and line number are available
                foreach ($Trace as $Index => $Loc)
                {
                    if (!array_key_exists("file", $Loc))
                    {
                        $Trace[$Index]["file"] = "UNKNOWN";
                    }
                    if (!array_key_exists("line", $Loc))
                    {
                        $Trace[$Index]["line"] = "??";
                    }
                }

                # determine length of leading path common to all file names in trace
                $LocString = "";
                $OurFile = __FILE__;
                $PrefixLen = 9999;
                foreach ($Trace as $Loc)
                {
                    if ($Loc["file"] != "UNKNOWN")
                    {
                        $Index = 0;
                        $FNameLength = strlen($Loc["file"]);
                        while ($Index < $FNameLength &&
                               $Loc["file"][$Index] == $OurFile[$Index])
                                {  $Index++;  }
                        $PrefixLen = min($PrefixLen, $Index);
                    }
                }

                foreach ($Trace as $Loc)
                {
                    $Sep = "";
                    $ArgString = "";
                    foreach ($Loc["args"] as $Arg)
                    {
                        $ArgString .= $Sep;
                        switch (gettype($Arg))
                        {
                            case "boolean":
                                $ArgString .= $Arg ? "TRUE" : "FALSE";
                                break;

                            case "integer":
                            case "double":
                                $ArgString .= $Arg;
                                break;

                            case "string":
                                $ArgString .= '"<i>'.htmlspecialchars(substr($Arg, 0, 40))
                                        .((strlen($Arg) > 40) ? "..." : "").'</i>"';
                                break;

                            case "array":
                            case "resource":
                            case "NULL":
                                $ArgString .= strtoupper(gettype($Arg));
                                break;

                            case "object":
                                $ArgString .= get_class($Arg);
                                break;

                            case "unknown type":
                                $ArgString .= "UNKNOWN";
                                break;
                        }
                        $Sep = ",";
                    }
                    $Loc["file"] = substr($Loc["file"], $PrefixLen);
                    $LocString .= "&nbsp;&nbsp;";
                    if (array_key_exists("class", $Loc))
                            {  $LocString .= $Loc["class"]."::";  }
                    $LocString .= $Loc["function"]."(".$ArgString.")"
                            ." - ".$Loc["file"].":".$Loc["line"]
                            ."<br>\n";
                }
                print("<b>Trace:</b><br>\n".$LocString);
            }
        }
        return $this->QueryHandle;
    }

    /**
    * Get memory limit.
    * @return int PHP memory limit in bytes.
    */
    static private function GetPhpMemoryLimit()
    {
        $Str = strtoupper(ini_get("memory_limit"));
        if (substr($Str, -1) == "B") {  $Str = substr($Str, 0, strlen($Str) - 1);  }
        switch (substr($Str, -1))
        {
            case "K":
                $MemoryLimit = (int)$Str * 1024;
                break;

            case "M":
                $MemoryLimit = (int)$Str * 1048576;
                break;

            case "G":
                $MemoryLimit = (int)$Str * 1073741824;
                break;

            default:
                $MemoryLimit = (int)$Str;
                break;
        }
        return $MemoryLimit;
    }

    /**
    * Get current amount of free memory.
    * @return int Memory available in bytes.
    */
    static private function GetFreeMemory()
    {
        return self::GetPhpMemoryLimit() - memory_get_usage();
    }
}

# define return values  (numerical values correspond to MySQL error codes)
// @codingStandardsIgnoreStart (to silence warning about multiple spaces)
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);
// @codingStandardsIgnoreEnd

# 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)
        );

# date() format for SQL dates
define("DATE_SQL", "Y-m-d H:i:s");
