path: root/includes/classes/BitDb.php

<?php
/**
 * ADOdb Library interface Class
 *
 * @package kernel
 * @version $Header$
 *
 * Copyright (c) 2004 bitweaver.org
 * Copyright (c) 2003 tikwiki.org
 * Copyright (c) 2002-2003, Luis Argerich, Garland Foster, Eduardo Polidor, et. al.
 * All Rights Reserved. See below for details and a complete list of authors.
 * Licensed under the GNU LESSER GENERAL PUBLIC LICENSE. See http://www.gnu.org/copyleft/lesser.html for details
 *
 * @author spider <spider@steelsun.com>
 */

 namespace Bitweaver;

/**
 * ensure your AdoDB install is a subdirectory off your include path
 */

define( 'BIT_QUERY_DEFAULT', -1 ); // deprecated constant for no cache time
define( 'BIT_QUERY_CACHE_DISABLE', -1 );
define( 'BIT_MAX_RECORDS', -1 );

// num queries has to be global
global $gNumQueries;
$gNumQueries = 0;

/**
 * This class is used for database access and provides a number of functions to help
 * with database portability.
 *
 * Currently used as a base class, this class should be optional to ensure bitweaver
 * continues to function correctly, without a valid database connection.
 *
 * @package kernel
 */
class BitDb {
	/**
	* Used to store the ADODB db object used to access the database.
	* This is just a pointer to a single global variable used by all classes.
	* This limits database connections to just one per request.
	* @private
	*/
	public $mDb;
	/**
	* Used to identify the ADODB db object
	* @private
	*/
	public $mName;
	/**
	* Used to store the ADODB db object type
	* @private
	*/
	public $mType;
	/**
	* Used to store failed commands
	* @private
	*/
	public $mFailed = [];
	/**
	* Used to store the number of queries executed.
	* @private
	*/
	public $mNumQueries = 0;
	/**
	* Used to store the total query time for this request.
	* @private
	*/
	public $mQueryTime = 0;
	/**
	* Case sensitivity flag used in convertQuery
	* @private
	*/
	public $mCaseSensitive = true;
	/**
	* Used to enable AdoDB caching
	* @private
	*/
	public $mCacheFlag;
	/**
	* Used to determine SQL debug output. BitDbAdodb overrides associated methods to use the debugging mechanisms built into ADODB
	* @private
	*/
	public $mDebug;
	/**
	* Determines if fatal query functions should terminate script execution. Defaults to true. Can be deactived for things like expected duplicate inserts
	* @private
	*/
	public $mFatalActive;
	public $mQueryLap;
	/**
	* During initialisation, database parameters are passed to the class.
	* If these parameters are not valid, class will not be initialised.
	*/
	public function __construct() {
		global $gDebug;
		$this->mDebug = $gDebug;
		$this->mCacheFlag = true;
		$this->mNumQueries = 0;
		$this->mQueryTime = 0;
		$this->setFatalActive();
		global $gBitDbCaseSensitivity;
		$this->setCaseSensitivity( $gBitDbCaseSensitivity );
	}
	/**
	* This function contains any pre-connection work
	* @private
	* @todo investigate if this is the correct way to do it.
	*/
	public function preDBConnection() {
		// Pre connection setup
		if(isset($this->mType)) {
			// we have a db we're gonna try to load
			switch ($this->mType) {
				case "sybase":
				// avoid database change messages
				ini_set("sybct.min_server_severity", "11");
				break;
			}
		} else {
			die("No database type specified");
		}
	}
	/**
	* This function contains any post-connection work
	* @private
	* @todo investigate if this is the correct way to do it.
	* @todo remove the BIT_DB_PREFIX, change to a member variable
	* @todo get spiderr to explain the schema line
	*/
	public function postDBConnection() {
		// Post connection setup
		switch ($this->mType) {
			case "sybase":
			case "mssql":
				$this->mDb->Execute("set quoted_identifier on");
				break;
			case "mysql":
				$version = $this->getDatabaseVersion();
				if( ($version['major'] >= 4 && $version['minor'] >=1) || ($version['major'] >= 5) ) {
					$this->mDb->Execute("set session sql_mode='PIPES_AS_CONCAT'");
				}
				break;
			case "postgres":
				// Do a little prep work for postgres, no break, cause we want default case too
				if (defined("BIT_DB_PREFIX") && preg_match( "/\./", BIT_DB_PREFIX) ) {
					$schema = preg_replace("/[`\.]/", "", BIT_DB_PREFIX);
					// Assume we want to dump in a schema, so set the search path and nuke the prefix here.
					// $result = $this->mDb->Execute( "SET search_path TO $schema,public" );
				}
				break;
		}
	}

	/**
	* Determines if the database connection is valid
	* @return true if DB connection is valid, false if not
	*/
	public function isValid() {
		return !empty( $this->mDb ) && count ($this->mDb->MetaTables() );
	}
	/**
	* Determines if the database connection is valid
	* @return true if DB connection is valid, false if not
	*/
	public function isFatalActive() {
		return $this->mFatalActive;
	}
	/**
	* Determines if the database connection is valid
	* @return true if DB connection is valid, false if not
	*/
	public function setFatalActive( $pActive=true ): void {
		$this->mFatalActive = $pActive;
	}
	/**
	* Used to start query timer if in debug mode
	*/
	public function queryStart() {
		global $gBitTimer;
		if (isset($gBitTimer)) {
			$this->mQueryLap = $gBitTimer->elapsed();
		}
	}
	/** will activate ADODB like native debugging output
	* @param int|bool pLevel debugging level - false is off, true is on, 99 is verbose
	**/
	public function debug( int|bool $pLevel = 99 ): void {
		$this->mDebug = $pLevel;
	}

	/** returns the level of query debugging output
	* @return int|bool pLevel debugging level - false is off, true is on, 99 is verbose
	**/
	public function getDebugLevel(): bool|int {
		return $this->mDebug;
	}
	/**
	* Sets the case sensitivity mode which is used in convertQuery
	* @return true if DB connection is valid, false if not
	*/
	public function setCaseSensitivity( $pSensitivity=true ): void {
		$this->mCaseSensitive = $pSensitivity;
	}
	/**
	* Sets the case sensitivity mode which is used in convertQuery
	* @return true if DB connection is valid, false if not
	*/
	public function getCaseSensitivity( $pSensitivity=true ) {
		switch ($this->mType) {
			case "firebird":
			case "oci8":
			case "oci8po":
			case "pdo":
					// Force Oracle to always be insensitive
				$ret = false;
				break;
			default:
				$ret = $this->mCaseSensitive;
				break;
		}

		return $ret;
	}
	/**
	* Used to stop query tracking and output results if in debug mode
	*/
	public function queryComplete() {
		global $gNumQueries;
		//count the number of queries made
		$gNumQueries++;
		$this->mNumQueries++;
		global $gBitTimer;
		if (!isset($gBitTimer)) {
			$gBitTimer = new BitTimer();
			$gBitTimer->start();
		}
		$interval = $gBitTimer->elapsed() - $this->mQueryLap;
		$this->mQueryTime += $interval;
		if( $this->getDebugLevel() ) {
			$style = ( $interval > .5 ) ? 'color:red;' : (( $interval > .15 ) ? 'color:orange;' : '');
			$querySpeed = ( $interval > .5 ) ? KernelTools::tra( 'VERY SLOW' ): (( $interval > .15 ) ? KernelTools::tra( 'SLOW' ) : 'NORMAL');
			if( ini_get( 'html_errors' ) ) {
				print '<p style="'.$style.'">
						<span style="display:inline-block;width:30%">### Query: <strong>'.$gNumQueries.'</strong> '.$querySpeed.'</span>
						<span style="display:inline-block;width:33%">Start time: '.round( $this->mQueryLap, 5 ).'</span> 
						<span style="display:inline-block;width:33%">### Query run time: '.round( $interval, 5 ).'</span></p>';
			} else {
				print '('.$this->mDb->databaseType."): #$gNumQueries >> Start: ".round( $this->mQueryLap, 5 )."s > $querySpeed ".round( $interval, 5 )."s\n";
			}
			flush();
		}
		$this->mQueryLap = 0;
	}

	/**
	* Used to create tables - most commonly from package/schema_inc.php files
	* @todo remove references to BIT_DB_PREFIX, us a member function
	* @param array pTables an array of tables and creation information in DataDict
	* style
	* @param array pOptions an array of options used while creating the tables
	* @return 
	*/
	public function createTables( array $pTables, array $pOptions = [] ): bool {
		// PURE VIRTUAL
		return false;
	}

	/**
	* Used to check if tables already exists.
	* @todo should be used to confirm tables are already created
	* @param array pTable the table name
	* @return bool true if table already exists
	*/
	public function tableExists( string $pTable): bool {
		// PURE VIRTUAL
		return false;
	}

	/**
	* Used to drop tables
	* @todo remove references to BIT_DB_PREFIX, us a member function
	* @param array pTables an array of table names to drop
	* @return bool
	* true if dropped with no errors |
	* false if errors are stored in $this->mFailed
	*/
	public function dropTables(array $pTables): bool {
		// PURE VIRTUAL
		return false;
	}

	/**
	* Function to set ADODB query caching member variable
	* @param bool pCacheExecute flag to enable or disable ADODB query caching
	* @return void
	*/
	public function setCaching( $pCacheFlag=true ) {
		$this->mCacheFlag = $pCacheFlag;
	}

	/**
	* Function to set ADODB query caching member variable
	* @return bool
	*/
	public function isCachingActive() {
		return $this->mCacheFlag;
	}

	/**
	* Quotes a string to be sent to the database
	* @param string pStr string to be quotes
	* @return string quoted string using AdoDB->qstr()
	*/
	public function qstr( string $pStr): string {
		// PURE VIRTUAL
		return '';
	}

	/** Queries the database, returning an error if one occurs, rather
	* than exiting while printing the error. -rlpowell
	* @param string $pQuery the SQL query. Use backticks (`) to quote all table
	* and attribute names for AdoDB to quote appropriately.
	* @param string $pError the error string to modify and return
	* @param array $pValues an array of values used in a parameterised query
	* @param int $pNumRows the number of rows (LIMIT) to return in this query
	* @param int $pOffset the row number to begin returning rows from. Used in
	* @return array an AdoDB RecordSet object
	* conjunction with $pNumRows
	* @todo currently not used anywhere.
	*/
	public function queryError( string $pQuery, string &$pError, ?array $pValues = null, int $pNumRows = -1, int $pOffset = -1 ) {
		// PURE VIRTUAL
		return [];
	}

	/** Queries the database reporting an error if detected
	* than exiting while printing the error. -rlpowell
	* @param string pQuery the SQL query. Use backticks (`) to quote all table
	* and attribute names for AdoDB to quote appropriately.
	* @param array pValues an array of values used in a parameterised query
	* @param int pNumRows the number of rows (LIMIT) to return in this query
	* @param int pOffset the row number to begin returning rows from. Used in
	* conjunction with $pNumRows
	* @param int pCacheTime 
	* @return array an AdoDB RecordSet object
	*/
	public function query( string $query, ?array $values = null, int $numrows = BIT_QUERY_DEFAULT, int $offset = BIT_QUERY_DEFAULT, int $pCacheTime=BIT_QUERY_DEFAULT ) {
		// PURE VIRTUAL
		return [];
	}

	/**
	* ADODB compatibility functions for bitcommerce
	*/
	public function Execute($pQuery, $pNumRows=BIT_QUERY_DEFAULT, $offset=BIT_QUERY_DEFAULT, $pCacheTime=BIT_QUERY_DEFAULT) {
		if ( $this->mType == "firebird" || $this->mType == 'pdo') {
			$pQuery = preg_replace("/\\\'/", "''", $pQuery);
			$pQuery = preg_replace("/ NOW/", " 'NOW'", $pQuery);
			$pQuery = preg_replace("/now\(\)/", "'NOW'", $pQuery);
		}
		return $this->query( $pQuery, null, $pNumRows, $offset, $pCacheTime );
	}

	/**
	 * Create a list of tables available in the current database
	 *
	 * @param bool|string ttype can either be 'VIEW' or 'TABLE' or false.
	 * 		If false, both views and tables are returned.
	 *		"VIEW" returns only views
	 *		"TABLE" returns only tables
	 * @param bool showSchema returns the schema/user with the table name, eg. USER.TABLE
	 * @param bool mask  is the input mask - only supported by oci8 and postgresql
	 *
	 * @return array of tables for current database.
	*/
	public function MetaTables( bool|string  $ttype = false, bool $showSchema = false, bool $mask = false ): bool|array {
		// PURE VIRTUAL
		return false;
	}

	/**
	 * List columns in a database as an array of ADOFieldObjects.
	 * See top of file for definition of object.
	 *
	 * @param string tabletable name to query
	 * @param bool upper	uppercase table name (required by some databases)
	 * @param bool schema is optional database schema to use - not supported by all databases.
	 *
	 * @return array of ADOFieldObjects for current table.
	 */
	public function MetaColumns( string $table, bool $normalize=true, bool $schema=false ) {
		// PURE VIRTUAL
		return [];
	}

	/**
	 * List indexes in a database as an array of ADOFieldObjects.
	 * See top of file for definition of object.
	 *
	 * @param string table	table name to query
	 * @param bool primary list primary indexes
	 * @param bool owner list owner of index
	 *
	 * @return  array of ADOFieldObjects for current table.
	 */
	public function MetaIndexes( string $table, bool $primary=false, bool $owner=false) {
		// PURE VIRTUAL
		return [];
	}

	/** Executes the SQL and returns all elements of the first column as a 1-dimensional array. The recordset is discarded for you automatically. If an error occurs, false is returned.
	* See AdoDB GetCol() function for more detail.
	* @param string pQuery the SQL query. Use backticks (`) to quote all table
	* and attribute names for AdoDB to quote appropriately.
	* @param array pValues an array of values used in a parameterised query
	* @param bool pTrim if set to true, when an array is created for each value
	* @return array the associative array, or false if an error occurs
	* @todo not currently used anywhere
	*/

	public function getCol( $pQuery, $pValues=false, $pTrim=false ) {
		// PURE VIRTUAL
		return [];
	}
	/** Returns an associative array for the given query.
	* See AdoDB GetAssoc() function for more detail.
	* @param string pQuery the SQL query. Use backticks (`) to quote all table
	* and attribute names for AdoDB to quote appropriately.
	* @param array pValues an array of values used in a parameterised query
	* @param bool pForceArray if set to true, when an array is created for each value
	* @param bool pFirst2Cols if set to true, only returns the first two columns
	* @return array the associative array, or false if an error occurs
	*/
	public function getArray( $pQuery, $pValues=false, $pForceArray=false, $pFirst2Cols=false, $pCacheTime=BIT_QUERY_DEFAULT ) {
		// PURE VIRTUAL
		return [];
	}

	/** Returns an associative array for the given query.
	* See AdoDB GetAssoc() function for more detail.
	* @param string pQuery the SQL query. Use backticks (`) to quote all table
	* and attribute names for AdoDB to quote appropriately.
	* @param array pValues an array of values used in a parameterised query
	* @param bool pForceArray if set to true, when an array is created for each value
	* @param bool pFirst2Cols if set to true, only returns the first two columns
	* @return array the associative array, or false if an error occurs
	*/
	public function getAssoc( $pQuery, $pValues=false, $pForceArray=false, $pFirst2Cols=false, $pCacheTime=BIT_QUERY_DEFAULT ) {
		// PURE VIRTUAL
		return [];
	}

	/** Executes the SQL and returns the first row as an array. The recordset and remaining rows are discarded for you automatically. If an error occurs, false is returned.
	* See AdoDB GetRow() function for more detail.
	* @param string pQuery the SQL query. Use backticks (`) to quote all table
	* and attribute names for AdoDB to quote appropriately.
	* @param array pValues an array of values used in a parameterised query
	* @return array the first row as an array, or false if an error occurs
	*/
	public function getRow( $pQuery, $pValues=false, $pCacheTime=BIT_QUERY_DEFAULT ) {
		// PURE VIRTUAL
		return [];
	}

	/** Returns a single column value from the database.
	* @param string pQuery the SQL query. Use backticks (`) to quote all table
	* and attribute names for AdoDB to quote appropriately.
	* @param array pValues an array of values used in a parameterised query
	* @param int pNumRows
	* @param int pOffset the row number to begin returning rows from.
	* @return array the associative array, or false if an error occurs
	*/
	public function getOne($pQuery, $pValues=null, $pNumRows=null, $pOffset=null, $pCacheTime = BIT_QUERY_DEFAULT ) {
		// PURE VIRTUAL
		return [];
	}

	/**
	* This function will take a set of fields identified by an associative array - $insertData
	* generate a suitable SQL script
	* and insert the data into the specified table - $insertTable
	* @param string insertTable Name of the table to be inserted into
	* @param array insertData Array of data to be inserted. Array keys provide the field names
	* @return array Error status of the insert
	*/
	public function associateInsert( $insertTable, $insertData ) {
		$setSql = '`'.implode( '`, `', array_keys( $insertData ) ).'`';
		//stupid little loop to generate question marks. Start at one, and tack at the end to ease dealing with comma
		$valueSql = '';
		for( $i = 1; $i < count( $insertData ); $i++ ) {
			$valueSql .= '?, ';
		}
		$valueSql .= '?';

		if( $insertTable[0] != '`' ) {
			$insertTable = '`'.$insertTable.'`';
		}

		$query = "INSERT INTO $insertTable ( $setSql ) VALUES ( $valueSql )";

		$result = $this->query( $query, array_values( $insertData ) );

		return $result;
	}

	/**
	* This function will take a set of fields identified by an associative array - $updateData
	* generate a suitable SQL script
	* update the data into the specified table
	* at the location identified in updateId which holds a name and value entry
	* @param string updateTable Name of the table to be updated
	* @param array updateData Array of data to be changed. Array keys provide the field names
	* If an array key contains an '=' it will assumed to already be properly quoted.
	* This allows use of keys like this: `column_name` = `column_name` + ?
	* @param array updateId Array identifying the record to update.
	*		Array key 'name' provide the field name, and 'value' the record key
	* @return array Error status of the insert
	*/
	public function associateUpdate( $updateTable, $updateData, $updateId ) {
		$setSql = '';
		foreach( $updateData as $key=>$value ) {
			if (strpos($key,'=') === false) {
				$setSql .= ", `$key` = ?";
			}
			else
				$setSql .= ', ' . $key;
			}
		$setSql = 	substr($setSql,1);
		$bindVars = array_values( $updateData );
		$keyNames = '`'.implode( '`=? AND `', array_keys( $updateId ) ).'`=?';
		$keyVars = array_values( $updateId );
		$bindVars = array_merge( $bindVars, $keyVars );
		if( $updateTable[0] != '`' ) {
			$updateTable = '`'.$updateTable.'`';
		}

		$query = "UPDATE $updateTable SET $setSql WHERE $keyNames";
		$result = $this->query( $query, $bindVars );

		return $result;
	}

	/**
	* A database portable Sequence management function.
	*
	* @param string pSequenceName Name of the sequence to be used
	*		It will be created if it does not already exist
	* @return		0 if not supported, otherwise a sequence id
	*/
	public function GenID( $pSequenceName, $pUseDbPrefix = true ) {
		// PURE VIRTUAL
	}

	/**
	* A database portable Sequence management function.
	*
	* @param string pSequenceName Name of the sequence to be used
	*		It will be created if it does not already exist
	* @param int pStartID Allows setting the initial value of the sequence
	* @return		0 if not supported, otherwise a sequence id
	* @todo	To be combined with GenID
	*/
	public function CreateSequence($seqname='adodbseq',$startID=1) {
		// PURE VIRTUAL
	}

	/**
	* A database portable IFnull function.
	*
	* @param string pField argument to compare to null
	* @param string pNullRepl the null replacement value
	* @return string that represents the function that checks whether
	* $pField is null for the given database, and if null, change the
	* value returned to $pNullRepl.
	*/
	public function ifNull($pField, $pNullRepl): string {
		// PURE VIRTUAL
		return '';
	}

	/**
	 * A database portable RANDOM() function.
	 * Adodb overrides it anyway with it's $rand property.
	 *
	 * @return string with RANDOM() function.
	 */
	public function random() {

		switch( $this->mType ) {
			case "postgres":
			case "pgsql":
				return "RANDOM()";

			case "mssql":
				return "NEWID()";

			default:
				return "RAND()";

		}
	}

	/** Format the timestamp in the format the database accepts.
	* @param string pDate a Unix integer timestamp or an ISO format Y-m-d H:i:s
	* @return string the timestamp as a quoted string.
	* @todo could be used to later convert all int timestamps into db
	* timestamps. Currently not used anywhere.
	*/
	public function ls($pDate) {
		// PURE VIRTUAL
		return '';
;	}

	/**
	 * Return the current timestamp literal relevent to the database type
	 * @todo This needs extending to allow the use of GMT timestamp
	 *		rather then the current server time
	 */
	public function NOW() {
		global $gBitDbType, $gBitSystem;
		switch( $gBitDbType ) {
			case "firebird":
			case "pdo":
					$ret = $gBitSystem->getUTCTimestamp(); // UTC time to get round server offsets
				break;
			default:
				$ret = 'now()';
		}
		return $ret;
	}

	/**
	 * Return the current timestamp literal relevent to the database type
	 * @todo This needs extending to allow the use of GMT timestamp
	 *		rather then the current server time
	 */
	public function qtNOW() {
		global $gBitDbType, $gBitSystem;
		switch( $gBitDbType ) {
			case "firebird":
			case "pdo":
					$ret = "'".$gBitSystem->getUTCTimestamp()."'"; // UTC time to get round server offsets
				break;
			default:
				$ret = 'now()';
		}
		return $ret;
	}

	/** Return the sql to cast the given column from a time stamp to a Unix epoch
	* this is most useful for the many places bitweaver stores time as epoch integers
	* ADODB has no native support for this, see http://phplens.com/lens/lensforum/msgs.php?id=13661&x=1
	* @param string pColumn name of an integer, or long integer column
	* @return string the timestamp as a quoted string.
	* @todo could be used to later convert all int timestamps into db
	* timestamps. Currently not used anywhere.
	*/
	public function SQLTimestampToInt( $pColumn ) {
		global $gBitDbType;
		switch( $gBitDbType ) {
			case "firebird":
			case "pdo_firebird":
					$ret = "CAST `$pColumn` AS TIMESTAMP";
				break;
			case "mysql":
			case "mysqli":
				$ret = "UNIX_TIMESTAMP( `$pColumn` )";
				break;
			case "pgsql":
			case "postgres":
			case "postgres7":
				$ret = $pColumn.'::abstime::integer';
				break;
			default:
				$ret = $pColumn;
		}
		return $ret;
	}

	/** Return the sql to cast the given column from an long integer to a time stamp.
	* this is most useful for the many places bitweaver stores time as epoch integers
	* ADODB has no native support for this, see http://phplens.com/lens/lensforum/msgs.php?id=13661&x=1
	* @param string pColumn name of an integer, or long integer column
	* @return string the timestamp as a quoted string.
	* @todo could be used to later convert all int timestamps into db
	* timestamps. Currently not used anywhere.
	*/
	public function SQLIntToTimestamp( $pColumn ) {
		global $gBitDbType;
		switch( $gBitDbType ) {
			case "firebird":
			case "pdo_firebird":
					$ret = "(`$pColumn` / 86400.000000) + CAST ( '01/01/1970' AS TIMESTAMP )";
				break;
			case "mysql":
			case "mysqli":
				$ret = "CAST( `$pColumn` AS DATETIME )";
				break;
			case "pgsql":
			case "postgres":
			case "postgres7":
				$ret = $pColumn.'::integer::abstime::timestamptz';
				break;
			default:
				$ret = $pColumn;
		}
		return $ret;
	}

	public static function getPeriodFormat( $pPeriod ) {
		switch( $pPeriod ) {
			case 'year':
				$format = 'Y';
				break;
			case 'quarter':
				$format = 'Y-\QQ';
				break;
			case 'day':
				$format = 'Y-m-d';
				break;
			case 'week':
				$format = 'Y \Week W';
				break;
			case 'month':
			default:
				$format = 'Y-m';
				break;
		}
		return $format;
	}

	/** Return the sql to lock selected rows for updating.
	* ADODB has no native support for this, see http://phplens.com/lens/lensforum/msgs.php?id=13661&x=1
	* @param string pColumn name of an integer, or long integer column
	* @return string the timestamp as a quoted string.
	* @todo could be used to later convert all int timestamps into db
	* timestamps. Currently not used anywhere.
	*/
	public function SQLForUpdate() {
		global $gBitDbType;
		switch( $gBitDbType ) {
			case "firebird":
			case "pdo_firebird":
			case "pgsql":
			case "postgres":
			case "postgres7":
				$ret = ' FOR UPDATE ';
				break;
			default:
				$ret = '';
		}
		return $ret;
	}

	/**
	 * Format date column in sql string given an input format that understands Y M D
	 */
	public function SQLDate($pDateFormat, $pBaseDate=false) {
		// PURE VIRTUAL
	}

	/**
	 * Calculate the offset of a date for a particular database and generate
	 * appropriate SQL. Useful for calculating future/past dates and storing
	 * in a database.
	 * @param int pDays Number of days to offset by
	 *		If dayFraction=1.5 means 1.5 days from now, 1.0/24 for 1 hour.
	 * @param string pColumn Value to be offset
	 *		If null an offset from the current time is supplied
	 * @return void New number of days
	 *
	 * @todo Not currently used - this is database specific and uses TIMESTAMP
	 * rather than unix seconds
	 */
	public function OffsetDate( $pDays, $pColumn=null ) {
		// PURE VIRTUAL

	}

	/** Converts backtick (`) quotes to the appropriate quote for the
	 * database.
	 * @param string pQuery the SQL query using backticks (`)
	 * @return void the correctly quoted SQL statement in pQuery
	 * @todo investigate replacement by AdoDB NameQuote() function
	 */
	public function convertQuery( string &$pQuery ) {
		$pQuery = preg_replace( "!(^\s+)|(\s+$)!s", "", $pQuery );
		if( !empty( $this->mType ) ) {
			switch( $this->mType ) {
				case "oci8":
				case "oci8po":
					// Force Oracle to always be insensitive
					$pQuery = str_replace( '`', '', $pQuery );
					break;
				case "pgsql":
				case "postgres":	// For PEAR
				case "postgres7":	// Deprecated ADODB
				case "mssql":
				case "sybase":
				case "firebird":
				case "pdo":
					$pQuery = $this->getCaseSensitivity()
						? str_replace( '`', '"', $pQuery )
						: str_replace( '`', '', $pQuery );
					break;
				case "sqlite":
					$pQuery = str_replace( '`', '', $pQuery );
					break;
			}
		}
	}

	/**
	 * Converts field sorting abbreviation to SQL - you can pass in a single string or an entire array of sortmodes
	 *
	 * @param string or array $pSortMode fieldname and sort order string (eg name_asc)
	 * @access public
	 * @return string the correctly quoted SQL ORDER statement
	 */
	public function convertSortmode( $pSortMode ) {
		if( is_array( $pSortMode ) ) {
			$sql = '';
			foreach( $pSortMode as $sortMode ) {
				if( !empty( $sql ) ) {
					$sql .= ',';
				}
				$sql .= $this->convertSortmodeOneItem( $sortMode );
			}
			return $sql;
		}
			return $this->convertSortmodeOneItem( $pSortMode );

	}

	/**
	 * Converts field sorting abbreviation to SQL and it also allows us to do things like sort by random rows.
	 *
	 * @param string $pSortMode If pSortMode is 'random' it will insert the properly named db-specific function to achieve this.
	 * @access public
	 * @return string valid, database-specific sortmode - if sortmode is not valid, null is returned
	 */
	public function convertSortmodeOneItem( $pSortMode ) {
		// check $sort_mode for evil stuff
		if( $pSortMode = preg_replace('/[^.0-9A-Za-z_,]/', '', $pSortMode) ) {
			if( $sep = strrpos( $pSortMode, '_' ) ) {
				$order = substr( $pSortMode, $sep );
				// force ending to neither _asc or _desc
				if ( $order !='_asc' && $order != '_desc' ) {
					$pSortMode = substr( $pSortMode, 0, $sep ) . '_desc';
				}
			} elseif( $pSortMode != 'random' ) {
				$pSortMode .= '_desc';
			}

			$pSortMode = preg_replace( '/lastModif/', 'last_modified', $pSortMode );
			$pSortMode = preg_replace( '/pageName/', 'title', $pSortMode );
			$pSortMode = preg_replace( '/^user_(asc|desc)/', 'login_\1', $pSortMode );

			$bIsFunction = false;

			//Use random() of BitDb. BitDbAdodb will override it with its implementation.
			if( $pSortMode == "random" ) {
				$pSortMode = $this->random ();
				$bIsFunction = true;
			}

			if( !$bIsFunction ) {
				switch( $this->mType ) {
					case "oci8po":
						$pSortMode = preg_replace( "/_asc$/", "` ASC nullS LAST", $pSortMode );
						$pSortMode = preg_replace( "/_desc$/", "` DESC nullS LAST", $pSortMode );
						break;
					case "firebird":
					case "pdo":
							// Use of alias in order by is not supported because of optimizer processing
						if ( $pSortMode == 'page_name_asc' )           $pSortMode = 'title_asc';
						if ( $pSortMode == 'page_name_desc' )          $pSortMode = 'title_desc';
						if ( $pSortMode == 'content_id_asc' )          $pSortMode = 'lc.content_id_asc';
						if ( $pSortMode == 'content_id_desc' )         $pSortMode = 'lc.content_id_desc';
						if ( $pSortMode == 'item_position_asc' )       $pSortMode = 'tfgim2.item_position_asc';
						if ( $pSortMode == 'item_position_desc' )      $pSortMode = 'tfgim2.item_position_desc';
						if ( $pSortMode == 'creator_user_asc' )        $pSortMode = 'uuc.login_asc';
						if ( $pSortMode == 'creator_user_desc' )       $pSortMode = 'uuc.login_desc';
						if ( $pSortMode == 'creator_real_name_asc' )   $pSortMode = 'uuc.real_name_asc';
						if ( $pSortMode == 'creator_real_name_desc' )  $pSortMode = 'uuc.real_name_desc';
						if ( $pSortMode == 'modifier_user_asc' )       $pSortMode = 'uue.login_asc';
						if ( $pSortMode == 'modifier_user_desc' )      $pSortMode = 'uue.login_desc';
						if ( $pSortMode == 'modifier_real_name_asc' )  $pSortMode = 'uue.real_name_asc';
						if ( $pSortMode == 'modifier_real_name_desc' ) $pSortMode = 'uue.real_name_desc';
					case "oci8":
					case "sybase":
					case "mssql":
					case "sqlite":
					case "mysql3":
					case "postgres":
					case "mysql":
					default:
						$pSortMode = preg_replace( "/_asc$/", "` ASC", $pSortMode );
						$pSortMode = preg_replace( "/_desc$/", "` DESC", $pSortMode );
						break;
				}
				$pSortMode = str_replace( ",", "`,`",$pSortMode );
				$pSortMode = strpos( $pSortMode, '.' )
					? str_replace( ".", ".`",$pSortMode )
					: "`" . $pSortMode;
			}
		} else {
			$pSortMode = '';
		}
		return $pSortMode;
	}

	/** Returns the keyword to force a column comparison to be case sensitive
	* for none case-sensitive databases (eg MySQL)
	* @return string the SQL keyword
	* @todo only used in gBitSystem and users_lib to compare login names
	*/
	public function convertBinary() {
		switch ($this->mType) {
			case "oci8":
			case "firebird":
			case "sqlite":
			break;
			case "mysql3":
			case "mysql":
			return "BINARY";
		}
		return '';
	}

	/** Used to cast variable types for certain databases (ie SyBase & MSSQL)
	* @param string pVar the variable value to cast
	* @param string pType the current variable type
	* @return string the SQL casting statement
	*/
	public function sqlCast($pVar,$pType) {
		switch ($this->mType) {
			case "sybase":
			case "mssql":
			switch ($pType) {
				case "int":
				return " CONVERT(numeric(14,0),$pVar) ";
				case "string":
				return " CONVERT(varchar(255),$pVar) ";
				case "float":
				return " CONVERT(numeric(10,5),$pVar) ";
			}
			break;
			default:
		}
		return $pVar;
	}
	/**
	* Used to encode blob data (eg PostgreSQL). Can be called statically
	* @todo had a lot of trouble with AdoDB BlobEncode and BlobDecode
	* the code works but will need work for dbs other than PgSQL
	* @param string pData a string of raw blob data
	* @return string escaped blob data
	*/
	public function dbByteEncode( &$pData ) {
		// need to use this global so as not to break static calls
		global $gBitDbType;
		switch ( $gBitDbType ) {
			case "postgres":
				$search = [chr(92), chr(0), chr(39)];
				$replace = ['\\\134', '\\\000', '\\\047'];
				$ret = str_replace($search, $replace, $pData);
				break;
			default:
				$ret = &$pData;
				break;
		}
		return $ret;
	}

	/**
	* Used to decode blob data (eg PostgreSQL)
	* @todo had a lot of trouble with AdoDB BlobEncode and BlobDecode
	* the code works but will need work for dbs other than PgSQL
	* @param string pData escaped blob data
	* @return string a string of raw blob data
	*/
	public function dbByteDecode( &$pData ) {
		switch ($this->mType) {
			case "postgres":
				$ret = stripcslashes( $pData );
				break;
			default:
				$ret = &$pData;
				break;
		}
		return $ret;
	}

	/**
	 *	Improved method of initiating a transaction. Used together with CompleteTrans().
	 *	Advantages include:
	 *
	 *	a. StartTrans/CompleteTrans is nestable, unlike BeginTrans/CommitTrans/RollbackTrans.
	 *	   Only the outermost block is treated as a transaction.<br>
	 *	b. CompleteTrans auto-detects SQL errors, and will rollback on errors, commit otherwise.<br>
	 *	c. All BeginTrans/CommitTrans/RollbackTrans inside a StartTrans/CompleteTrans block
	 *	   are disabled, making it backward compatible.
	 */
	public function StartTrans() {
		// PURE VIRTUAL
	}

	/**
	 *	Used together with StartTrans() to end a transaction. Monitors connection
	 *	for sql errors, and will commit or rollback as appropriate.
	 *
	 *	autoComplete if true, monitor sql errors and commit and rollback as appropriate,
	 *	and if set to false force rollback even if no SQL error detected.
	 *	@return bool true on commit, false on rollback.
	 */
	public function CompleteTrans() {
		// PURE VIRTUAL
		return false;
	}

	/**
	 * If database does not support transactions, rollbacks always fail, so return false
	 * otherwise returns true if the Rollback was successful
	 *
	 * @return bool true/false.
	 */
	public function RollbackTrans() {
		// PURE VIRTUAL
		return false;
	}

	/**
	* @return # rows affected by UPDATE/DELETE
	*/
	public function Affected_Rows() {
		// PURE VIRTUAL
	}

	/**
	 * Check for Postgres specific extensions
	 */
	public function isAdvancedPostgresEnabled() {
		// This code makes use of the badass /usr/share/pgsql/contrib/tablefunc.sql
		// contribution that you have to install like: psql foo < /usr/share/pgsql/contrib/tablefunc.sql
		return defined( 'ADVANCED_PGSQL' );
	}

	/**
	 * determine current version of the databse
	 * @return # hash including 'description', 'version' full string, 'major', 'minor', and 'revsion'
	 */
	public function getDatabaseVersion() {
		$ret = $this->mDb->ServerInfo();
		$versionHash = explode( '.', $ret['version'] );
		$ret['major'] = !empty( $versionHash[0] ) ? $versionHash[0] : 0;
		$ret['minor'] = !empty( $versionHash[1] ) ? $versionHash[1] : 0;
		$ret['revision'] = !empty( $versionHash[2] ) ? $versionHash[2] : 0;
		return $ret;
	}

	/**
	 * Compatibility function for DBs with case insensitive searches
	 * (like MySQL, see: http://dev.mysql.com/doc/refman/5.1/en/case-sensitivity.html)
	 * How to use:
	 * 	AND ".$this->mDb->getCaseLessColumn('lc.title')." = 'page title'
	 * The reason all this matters is that huge performane difference between:
	 *   where title = 'PAGE TITLE'
	 * and
	 *   where UPPER(tittle) = 'PAGE TITTLE'
	 * The latter version will not make use of the index on page title (at least for MySQl)
	 * while the first vesion will use the index.  In a case insensitive search DB (MySQL) both
	 * forms of the query will give the same results, the only difference being the preformance.
	 * Spiderr suggested this solution and suppled the code below
	 */
	public function getCaselessColumn( $pColumn ) {
		global $gBitDbType;
		switch( $gBitDbType ) {
			case "mysql":
			case "mysqli":
				$ret = $pColumn;
				break;
			default:
				$ret = " UPPER($pColumn) ";
				break;
		}
		return $ret;
	}

	public function sanitizeColumnString( $pColumn ) {
		return preg_replace( "/[^a-z0-9_\.]+/i", "-", strtolower( $pColumn ) );
	}

	/**
	 * Renamed a few functions - these are the temporary backward compatability calls with the deprecated note
	 * These funcitons will be removed in due course
	 */
	/**
	 * @deprecated deprecated since version 2.0.0
	 */
	public function convert_sortmode( $pSortMode ) {
		KernelTools::deprecated( $this->depText( 'convert_sortmode', 'convertSortmode' ) );
		return $this->convertSortmode( $pSortMode );
	}
	/**
	 * @deprecated deprecated since version 2.0.0
	 */
	public function convert_sortmode_one_item( $pSortMode ) {
		KernelTools::deprecated( $this->depText( 'convert_sortmode_one_item', 'convertSortmodeOneItem' ) );
		return $this->convertSortmode( $pSortMode );
	}
	/**
	 * @deprecated deprecated since version 2.0.0
	 */
	public function convert_binary() {
		KernelTools::deprecated( $this->depText( 'convert_binary', 'convertBinary' ) );
		return $this->convertBinary();
	}
	/**
	 * @deprecated deprecated since version 2.0.0
	 */
	public function sql_cast( $pVar, $pType ) {
		KernelTools::deprecated( $this->depText( 'sql_cast', 'sqlCast' ) );
		return $this->sqlCast( $pVar, $pType );
	}
	/**
	 * @deprecated deprecated since version 2.0.0
	 */
	public function db_byte_encode( &$pData ) {
		KernelTools::deprecated( $this->depText( 'db_byte_encode', 'dbByteEncode' ) );
		return $this->dbByteEncode( $pData );
	}
	/**
	 * @deprecated deprecated since version 2.0.0
	 */
	public function db_byte_decode( &$pData ) {
		KernelTools::deprecated( $this->depText( 'db_byte_decode', 'dbByteDecode' ) );
		return $this->dbByteDecode( $pData );
	}
	public function depText( $pFrom, $pTo ) {
		return "We have changed this method to BitDb::{$pTo}().
	Please update your code accordingly - you can try using the following (please back up your code before applying this):
	find <your package>/ -name \"*.php\" -exec perl -i -wpe 's/\b{$pFrom}\b/{$pTo}/g' {} \;";
	}
}