CWIS Developer Documentation
List of all members
Database Class Reference

SQL database abstraction object with smart query caching. More...

Setup/Initialization

 __construct ($UserName=NULL, $Password=NULL, $DatabaseName=NULL, $HostName=NULL)
 Object constructor. More...
 
 __sleep ()
 
 __wakeup ()
 Restore database connection when unserialized. More...
 
 SetDefaultStorageEngine ($Engine)
 Set default database storage engine. More...
 
 GetServerVersion ($FullVersion=FALSE)
 Get database server version number. More...
 
 GetClientVersion ()
 Get version number of the client libraries being used to connect to the database server (Currently the mysql library version number). More...
 
 GetHostInfo ()
 Get database connection type and hostname. More...
 
 DBHostName ()
 Get host name of system on which database server resides. More...
 
 DBName ()
 Get current database name. More...
 
 DBUserName ()
 Get name used to connect with database server. More...
 
 SetQueryErrorsToIgnore ($ErrorsToIgnore, $NormalizeWhitespace=TRUE)
 Set query errors to ignore. More...
 
 IgnoredError ()
 Check whether an error was ignored by the most recent query. More...
 
static SetGlobalServerInfo ($UserName, $Password, $HostName="localhost")
 Set default login and host info for database server. More...
 
static SetGlobalDatabaseName ($DatabaseName)
 Set default database name. More...
 
static Caching ($NewSetting=NULL)
 Get or set whether query result caching is currently enabled. More...
 
static AdvancedCaching ($NewSetting=NULL)
 Get or set whether advanced query result cachine is currently enabled. More...
 

Data Manipulation

 Query ($QueryString, $FieldName="")
 Query database (with caching if enabled). More...
 
 ExecuteQueriesFromFile ($FileName)
 Execute queries from specified file. More...
 
 QueryErrMsg ()
 Get most recent error message text set by Query(). More...
 
 QueryErrNo ()
 Get most recent error code set by Query(). More...
 
 NumRowsSelected ()
 Get number of rows returned by last SELECT or SHOW query. More...
 
 NumRowsAffected ()
 Get number of rows affected by last INSERT, UPDATE, REPLACE, or DELETE query. More...
 
 FetchRow ()
 Get next database row retrieved by most recent query. More...
 
 FetchRows ($NumberOfRows=NULL)
 Get specified number of database rows retrieved by most recent query. More...
 
 FetchColumn ($FieldName, $IndexFieldName=NULL)
 Get all available values for specified database field retrieved by most recent query. More...
 
 FetchField ($FieldName)
 Pull next row from last DB query and get a specific value from that row. More...
 
 LastInsertId ()
 Get ID of row added by the last SQL "INSERT" statement. More...
 
 GetNextInsertId ($TableName)
 For tables that have an AUTO_INCREMENT column, get the next value that will be assigned. More...
 
 UpdateValue ($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
 A convenience function to get or set a value in the database. More...
 
 UpdateIntValue ($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
 A convenience function to get or set an integer value in the database. More...
 
 UpdateFloatValue ($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
 A convenience function to get or set a float value in the database. More...
 
 CopyValues ($TableName, $IdColumn, $SrcId, $DstId, $ColumnsToExclude=array())
 A convenience function to copy values from one row to another. More...
 
 InsertArray ($Table, $ValueField, $Values, $KeyField=NULL, $AvgDataLength=20)
 Insert an array of values with a minimum number of INSERT statements. More...
 
static DisplayQueryErrors ($NewValue=NULL)
 Get/set whether Query() errors will be displayed. More...
 

Miscellaneous

 $DBUserName
 
 $DBPassword
 
 $DBHostName
 
 $DBName
 
const CR_CONNECTION_ERROR = 2002
 
const CR_CONN_HOST_ERROR = 2003
 
const CR_SERVER_GONE_ERROR = 2006
 
const CR_SERVER_LOST = 2013
 
const TINYINT_MAX_VALUE = 127
 
const SMALLINT_MAX_VALUE = 32767
 
const MEDIUMINT_MAX_VALUE = 8388607
 
const INT_MAX_VALUE = 2147483647
 
const BIGINT_MAX_VALUE = 9223372036854775807
 
 EscapeString ($String)
 Escape a string that may contain null bytes. More...
 
 LogComment ($String)
 Peform query that consists of SQL comment statement. More...
 
 TableExists ($TableName)
 Get whether specified table exists. More...
 
 FieldExists ($TableName, $FieldName)
 Get whether specified field exists in specified table. More...
 
 GetFieldType ($TableName, $FieldName)
 Get field (column) type. More...
 
 GetColumns ($TableName)
 Get column (database field) names. More...
 
 GetMaxQueryLength ()
 Get maximum size for query string. More...
 
static QueryDebugOutput ($NewSetting)
 Enable or disable debugging output for queries. More...
 
static NumQueries ()
 Get the number of queries that have been run since program execution began. More...
 
static NumCacheHits ()
 Get the number of queries that have resulted in cache hits since program execution began. More...
 
static CacheHitRate ()
 Get the ratio of query cache hits to queries as a percentage. More...
 

Detailed Description

SQL database abstraction object with smart query caching.

Definition at line 22 of file Database.php.

Constructor & Destructor Documentation

Database::__construct (   $UserName = NULL,
  $Password = NULL,
  $DatabaseName = NULL,
  $HostName = NULL 
)

Object constructor.

If user name, password, or database name are omitted they must have been set earlier with SetGlobalServerInfo() and SetGlobalDatabaseName().

Parameters
string$UserNameUser name to use to log in to database server. (OPTIONAL)
string$PasswordPassword to use to log in to database server. (OPTIONAL)
string$DatabaseNameName of database to use once logged in. (OPTIONAL)
string$HostNameHost name of system on which database server resides. (OPTIONAL, defaults to "localhost")
Exceptions
ExceptionWhen unable to connect to database server or select specified database.
See also
Database::SetGlobalServerInfo()
Database::SetGlobalDatabaseName()

Definition at line 43 of file Database.php.

References $DBName, DBHostName(), DBName(), and DBUserName().

Member Function Documentation

Database::__sleep ( )

Specify variables to be saved when serialized.

Definition at line 81 of file Database.php.

Database::__wakeup ( )

Restore database connection when unserialized.

Exceptions
ExceptionWhen unable to connect to database server or select specified database.

Definition at line 90 of file Database.php.

References $DBName, DBHostName(), DBName(), and DBUserName().

static Database::AdvancedCaching (   $NewSetting = NULL)
static

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 disabled by default. This setting applies to all instances of the Database class.

Parameters
bool$NewSettingTRUE to enable advanced caching or FALSE to disable. (OPTIONAL)
Returns
Current advanced caching setting.

Definition at line 285 of file Database.php.

static Database::CacheHitRate ( )
static

Get the ratio of query cache hits to queries as a percentage.

The value returned is for all instances of the Database class.

Returns
Percentage of queries that resulted in hits.

Definition at line 1213 of file Database.php.

static Database::Caching (   $NewSetting = NULL)
static

Get or set whether query result caching is currently enabled.

Caching is enabled by default. This setting applies to all instances of the Database class.

Parameters
bool$NewSettingTRUE to enable caching or FALSE to disable. (OPTIONAL)
Returns
Current caching setting.

Definition at line 259 of file Database.php.

Referenced by SPTRecommender\RunUpdateForItem().

Here is the caller graph for this function:

Database::CopyValues (   $TableName,
  $IdColumn,
  $SrcId,
  $DstId,
  $ColumnsToExclude = array() 
)

A convenience function to copy values from one row to another.

The ID column value will not be copied.

Parameters
string$TableNameName of table.
string$IdColumnName of column containing ID value.
string$SrcIdValue of ID column in source row.
mixed$DstIdValue of ID column or array of values of ID columns in destination row(s).
array$ColumnsToExcludeNames of additional columns to exclude from copy. (OPTIONAL)

Definition at line 948 of file Database.php.

References GetColumns(), and Query().

Database::DBHostName ( )

Get host name of system on which database server resides.

Returns
Host name of database server.
See also
SetGlobalServerInfo()

Definition at line 227 of file Database.php.

References $DBHostName.

Referenced by __construct(), and __wakeup().

Here is the caller graph for this function:

Database::DBName ( )

Get current database name.

Returns
Database name.
See also
SetGlobalDatabaseName()

Definition at line 237 of file Database.php.

References $DBName.

Referenced by __construct(), __wakeup(), GetColumns(), and GetNextInsertId().

Here is the caller graph for this function:

Database::DBUserName ( )

Get name used to connect with database server.

Returns
Login name.
See also
SetGlobalServerInfo()

Definition at line 247 of file Database.php.

References $DBUserName.

Referenced by __construct(), and __wakeup().

Here is the caller graph for this function:

static Database::DisplayQueryErrors (   $NewValue = NULL)
static

Get/set whether Query() errors will be displayed.

By default errors are not displayed.

Parameters
bool$NewValueTRUE to display errors or FALSE to not display. (OPTIONAL)
Returns
Current value of whether Query() errors will be displayed.

Definition at line 611 of file Database.php.

Database::EscapeString (   $String)

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.

Parameters
string$StringString to escape.
Returns
string Escaped data

Definition at line 1098 of file Database.php.

Database::ExecuteQueriesFromFile (   $FileName)

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().

Parameters
string$FileNameName of file to load queries from.
Returns
Number of queries executed or NULL if query failed.
See also
Database::SetQueryErrorsToIgnore()
Database::QueryErrMsg()
Database::QueryErrNo()

Definition at line 531 of file Database.php.

References Query().

Database::FetchColumn (   $FieldName,
  $IndexFieldName = NULL 
)

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:
$CNames = $DB->FetchColumn("ControlledName", "ControlledNameId");

Parameters
string$FieldNameName of database field.
string$IndexFieldNameName of second database field to use for array index. (OPTIONAL)
Returns
Array of values from specified field, indexed numerically. If IndexFieldName is supplied then array will be indexed by corresponding values from that field.

Definition at line 745 of file Database.php.

References FetchRow().

Referenced by GetColumns(), and GetFieldType().

Here is the caller graph for this function:

Database::FetchField (   $FieldName)

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 does advance the pointer to the next row returned by the query each time it is called.

Parameters
string$FieldNameName of field.
Returns
Value from specified field.

Definition at line 770 of file Database.php.

References FetchRow().

Referenced by FieldExists(), and Query().

Here is the caller graph for this function:

Database::FetchRow ( )

Get next database row retrieved by most recent query.

Returns
Array of database values with field names for indexes. Returns FALSE if no more rows are available.

Definition at line 664 of file Database.php.

Referenced by MysqlSystemVariables\__construct(), MetadataFieldOrder\__construct(), FetchColumn(), FetchField(), FetchRows(), and UpdateValue().

Here is the caller graph for this function:

Database::FetchRows (   $NumberOfRows = NULL)

Get specified number of database rows retrieved by most recent query.

Parameters
int$NumberOfRowsMaximum number of rows to return. (OPTIONAL – if not specified then all available rows are returned)
Returns
Array of rows. Each row is an associative array indexed by field name.

Definition at line 710 of file Database.php.

References FetchRow().

Database::FieldExists (   $TableName,
  $FieldName 
)

Get whether specified field exists in specified table.

Parameters
string$TableNameName of database table.
string$FieldNameName of database field.
Returns
bool TRUE if table and field exist, or FALSE otherwise.

Definition at line 1131 of file Database.php.

References FetchField(), and Query().

Referenced by UpdateValue().

Here is the caller graph for this function:

Database::GetClientVersion ( )

Get version number of the client libraries being used to connect to the database server (Currently the mysql library version number).

Returns
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.

Definition at line 207 of file Database.php.

Database::GetColumns (   $TableName)

Get column (database field) names.

Parameters
string$TableNameName of database table.
Returns
array Field names.

Definition at line 1159 of file Database.php.

References DBName(), FetchColumn(), and Query().

Referenced by CopyValues().

Here is the caller graph for this function:

Database::GetFieldType (   $TableName,
  $FieldName 
)

Get field (column) type.

Parameters
string$TableNameName of database table.
string$FieldNameName of database field.
Returns
string Field type or NULL if field was not found.

Definition at line 1147 of file Database.php.

References FetchColumn(), and Query().

Database::GetHostInfo ( )

Get database connection type and hostname.

Returns
string describing the database connection (e.g. "Locahost via UNIX socket").

Definition at line 217 of file Database.php.

Database::GetMaxQueryLength ( )

Get maximum size for query string.

Returns
int Maximum size in bytes.

Definition at line 1171 of file Database.php.

References Query().

Referenced by InsertArray().

Here is the caller graph for this function:

Database::GetNextInsertId (   $TableName)

For tables that have an AUTO_INCREMENT column, get the next value that will be assigned.

Callers are likely to want to LOCK the table to make sure that new rows are not inserted after this function is called.

Parameters
string$TableNameTable to examine.
Returns
Next insert id (always zero for tables with no AUTO_INCREMENT column).
Exceptions
Exceptionif table does not exist.

Definition at line 799 of file Database.php.

References DBName(), Query(), and TableExists().

Database::GetServerVersion (   $FullVersion = FALSE)

Get database server version number.

Parameters
bool$FullVersionTRUE for the whole version string or FALSE for just the version number (OPTIONAL, default FALSE).
Returns
string Version number.

Definition at line 183 of file Database.php.

References Query().

Referenced by SetDefaultStorageEngine().

Here is the caller graph for this function:

Database::IgnoredError ( )

Check whether an error was ignored by the most recent query.

Returns
mixed Error message if an error was ignored, otherwise FALSE.
See also
Database::SetQueryErrorsToIgnore()

Definition at line 333 of file Database.php.

Database::InsertArray (   $Table,
  $ValueField,
  $Values,
  $KeyField = NULL,
  $AvgDataLength = 20 
)

Insert an array of values with a minimum number of INSERT statements.

If a key column name is specified, then the array keys will be set in each row, along with the array values.

Parameters
string$TableName of the table to insert into.
string$ValueFieldName of column to insert array values into.
array$ValuesArray values.
string$KeyFieldName of column to insert array keys into. (OPTIONAL)
int$AvgDataLengthAverage length of value (and keys, if key column name supplied) in characters. (OPTIONAL, defaults to 20)

Definition at line 998 of file Database.php.

References GetMaxQueryLength(), and Query().

Database::LastInsertId ( )

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.

Returns
Numerical ID value.

Definition at line 782 of file Database.php.

References Query().

Database::LogComment (   $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.

Parameters
string$StringDebug string.

Definition at line 1109 of file Database.php.

References Query().

static Database::NumCacheHits ( )
static

Get the number of queries that have resulted in cache hits since program execution began.

The value returned is for all instances of the Database class.

Returns
Number of queries that resulted in cache hits.

Definition at line 1203 of file Database.php.

static Database::NumQueries ( )
static

Get the number of queries that have been run since program execution began.

The value returned is for all instances of the Database class.

Returns
Number of queries.

Definition at line 1192 of file Database.php.

Database::NumRowsAffected ( )

Get number of rows affected by last INSERT, UPDATE, REPLACE, or DELETE query.

Returns
Number of database rows affected by last query.

Definition at line 647 of file Database.php.

Database::NumRowsSelected ( )

Get number of rows returned by last SELECT or SHOW query.

Returns
Number of database rows selected by last query.

Definition at line 621 of file Database.php.

Referenced by MetadataFieldOrder\__construct(), TableExists(), and UpdateValue().

Here is the caller graph for this function:

Database::Query (   $QueryString,
  $FieldName = "" 
)

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.

Parameters
string$QueryStringSQL query string.
string$FieldNameName of field for which to return value to caller. (OPTIONAL)
Returns
Query handle, retrieved value (if FieldName supplied), or FALSE on error.

Definition at line 351 of file Database.php.

References FetchField().

Referenced by MysqlSystemVariables\__construct(), MetadataFieldOrder\__construct(), CopyValues(), MetadataFieldOrder\Delete(), ExecuteQueriesFromFile(), FieldExists(), GetColumns(), GetFieldType(), GetMaxQueryLength(), GetNextInsertId(), GetServerVersion(), InsertArray(), LastInsertId(), LogComment(), SetDefaultStorageEngine(), TableExists(), and UpdateValue().

Here is the caller graph for this function:

static Database::QueryDebugOutput (   $NewSetting)
static

Enable or disable debugging output for queries.

Output is disabled by default. This setting applies to all instances of the Database class.

Parameters
bool$NewSettingTRUE to enable output or FALSE to disable output.

Definition at line 1182 of file Database.php.

Database::QueryErrMsg ( )

Get most recent error message text set by Query().

Returns
Error message text from database server.
See also
QueryErrNo()

Definition at line 590 of file Database.php.

Database::QueryErrNo ( )

Get most recent error code set by Query().

Returns
Error code from database server.
See also
QueryErrMsg()

Definition at line 600 of file Database.php.

Database::SetDefaultStorageEngine (   $Engine)

Set default database storage engine.

Parameters
string$EngineNew default storage engine.

Definition at line 167 of file Database.php.

References GetServerVersion(), and Query().

static Database::SetGlobalDatabaseName (   $DatabaseName)
static

Set default database name.

Parameters
string$DatabaseNameName of database to use once logged in.

Definition at line 154 of file Database.php.

static Database::SetGlobalServerInfo (   $UserName,
  $Password,
  $HostName = "localhost" 
)
static

Set default login and host info for database server.

Parameters
string$UserNameUser name to use to log in to database server.
string$PasswordPassword to use to log in to database server.
string$HostNameHost name of system on which database server resides. (OPTIONAL, defaults to "localhost")

Definition at line 138 of file Database.php.

Database::SetQueryErrorsToIgnore (   $ErrorsToIgnore,
  $NormalizeWhitespace = TRUE 
)

Set query errors to ignore.

The command and error message patterns should be formatted for preg_match(). For example:

$SqlErrorsWeCanIgnore = array(
"/ALTER TABLE [a-z]+ ADD COLUMN/i" => "/Duplicate column name/i",
"/CREATE TABLE /i" => "/Table '[a-z0-9_]+' already exists/i",
);
Parameters
array$ErrorsToIgnoreAssociative 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.
bool$NormalizeWhitespaceIf 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 also
Database::IgnoredError()

Definition at line 313 of file Database.php.

Database::TableExists (   $TableName)

Get whether specified table exists.

Parameters
string$TableNameName of database table.
Returns
bool TRUE if table exists, or FALSE otherwise.

Definition at line 1119 of file Database.php.

References NumRowsSelected(), and Query().

Referenced by GetNextInsertId().

Here is the caller graph for this function:

Database::UpdateFloatValue (   $TableName,
  $FieldName,
  $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.

Parameters
string$TableNameName of database table.
string$FieldNameName of database field.
float$NewValueNew value to set. Use DB_NOVALUE to not set a new value (i.e. when just getting a value).
string$ConditionSQL query conditional to use in SELECT or UPDATE statements (should not include "WHERE"). Use NULL if no conditional is needed.
array$CachedRecordVariable to use to cache values. Accessed by reference.
Returns
Requested value.

Definition at line 929 of file Database.php.

References DB_NOVALUE, and UpdateValue().

Database::UpdateIntValue (   $TableName,
  $FieldName,
  $NewValue,
  $Condition,
$CachedRecord 
)

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.

Parameters
string$TableNameName of database table.
string$FieldNameName of database field.
int$NewValueNew value to set. Use DB_NOVALUE to not set a new value (i.e. when just getting a value).
string$ConditionSQL query conditional to use in SELECT or UPDATE statements (should not include "WHERE"). Use NULL if no conditional is needed.
array$CachedRecordVariable to use to cache values. Accessed by reference.
Returns
Requested value.

Definition at line 905 of file Database.php.

References DB_NOVALUE, and UpdateValue().

Database::UpdateValue (   $TableName,
  $FieldName,
  $NewValue,
  $Condition,
$CachedRecord 
)

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.

Parameters
string$TableNameName of database table.
string$FieldNameName of database field.
mixed$NewValueNew value to set. Use DB_NOVALUE to not set a new value (i.e. when just getting a value).
string$ConditionSQL query conditional to use in SELECT or UPDATE statements (should not include "WHERE"). Use NULL if no conditional is needed.
array$CachedRecordVariable to use to cache values. Accessed by reference.
Returns
Requested value.

Definition at line 827 of file Database.php.

References DB_NOVALUE, FetchRow(), FieldExists(), NumRowsSelected(), and Query().

Referenced by UpdateFloatValue(), and UpdateIntValue().

Here is the caller graph for this function:

Member Data Documentation

Database::$DBHostName
protected

Definition at line 1231 of file Database.php.

Referenced by DBHostName().

Database::$DBName
protected

Definition at line 1232 of file Database.php.

Referenced by __construct(), __wakeup(), and DBName().

Database::$DBPassword
protected

Definition at line 1230 of file Database.php.

Database::$DBUserName
protected

Definition at line 1229 of file Database.php.

Referenced by DBUserName().

const Database::BIGINT_MAX_VALUE = 9223372036854775807

Definition at line 1289 of file Database.php.

const Database::CR_CONN_HOST_ERROR = 2003

Definition at line 1279 of file Database.php.

const Database::CR_CONNECTION_ERROR = 2002

Definition at line 1277 of file Database.php.

const Database::CR_SERVER_GONE_ERROR = 2006

Definition at line 1280 of file Database.php.

const Database::CR_SERVER_LOST = 2013

Definition at line 1281 of file Database.php.

const Database::INT_MAX_VALUE = 2147483647

Definition at line 1288 of file Database.php.

const Database::MEDIUMINT_MAX_VALUE = 8388607

Definition at line 1287 of file Database.php.

const Database::SMALLINT_MAX_VALUE = 32767

Definition at line 1286 of file Database.php.

const Database::TINYINT_MAX_VALUE = 127

Definition at line 1285 of file Database.php.


The documentation for this class was generated from the following file: