CWIS Developer Documentation
|
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... | |
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 |
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... | |
SQL database abstraction object with smart query caching.
Definition at line 22 of file Database.php.
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().
string | $UserName | User name to use to log in to database server. (OPTIONAL) |
string | $Password | Password to use to log in to database server. (OPTIONAL) |
string | $DatabaseName | Name of database to use once logged in. (OPTIONAL) |
string | $HostName | Host name of system on which database server resides. (OPTIONAL, defaults to "localhost") |
Exception | When unable to connect to database server or select specified database. |
Definition at line 43 of file Database.php.
References $DBName, DBHostName(), DBName(), and DBUserName().
Database::__sleep | ( | ) |
Specify variables to be saved when serialized.
Definition at line 81 of file Database.php.
Database::__wakeup | ( | ) |
Restore database connection when unserialized.
Exception | When 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 |
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.
bool | $NewSetting | TRUE to enable advanced caching or FALSE to disable. (OPTIONAL) |
Definition at line 285 of file Database.php.
|
static |
Get the ratio of query cache hits to queries as a percentage.
The value returned is for all instances of the Database class.
Definition at line 1189 of file Database.php.
|
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.
bool | $NewSetting | TRUE to enable caching or FALSE to disable. (OPTIONAL) |
Definition at line 259 of file Database.php.
Referenced by SPTRecommender\RunUpdateForItem().
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.
string | $TableName | Name of table. |
string | $IdColumn | Name of column containing ID value. |
string | $SrcId | Value of ID column in source row. |
mixed | $DstId | Value of ID column or array of values of ID columns in destination row(s). |
array | $ColumnsToExclude | Names of additional columns to exclude from copy. (OPTIONAL) |
Definition at line 924 of file Database.php.
References GetColumns(), and Query().
Database::DBHostName | ( | ) |
Get host name of system on which database server resides.
Definition at line 227 of file Database.php.
References $DBHostName.
Referenced by __construct(), and __wakeup().
Database::DBName | ( | ) |
Get current database name.
Definition at line 237 of file Database.php.
References $DBName.
Referenced by __construct(), __wakeup(), and GetColumns().
Database::DBUserName | ( | ) |
Get name used to connect with database server.
Definition at line 247 of file Database.php.
References $DBUserName.
Referenced by __construct(), and __wakeup().
|
static |
Get/set whether Query() errors will be displayed.
By default errors are not displayed.
bool | $NewValue | TRUE to display errors or FALSE to not display. (OPTIONAL) |
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.
string | $String | String to escape. |
Definition at line 1074 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().
string | $FileName | Name of file to load queries from. |
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");
string | $FieldName | Name of database field. |
string | $IndexFieldName | Name of second database field to use for array index. (OPTIONAL) |
Definition at line 745 of file Database.php.
References FetchRow().
Referenced by GetColumns(), and GetFieldType().
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.
string | $FieldName | Name of field. |
Definition at line 770 of file Database.php.
References FetchRow().
Referenced by FieldExists(), and Query().
Database::FetchRow | ( | ) |
Get next database row retrieved by most recent query.
Definition at line 664 of file Database.php.
Referenced by MysqlSystemVariables\__construct(), MetadataFieldOrder\__construct(), FetchColumn(), FetchField(), FetchRows(), and UpdateValue().
Database::FetchRows | ( | $NumberOfRows = NULL | ) |
Get specified number of database rows retrieved by most recent query.
int | $NumberOfRows | Maximum number of rows to return. (OPTIONAL – if not specified then all available rows are returned) |
Definition at line 710 of file Database.php.
References FetchRow().
Database::FieldExists | ( | $TableName, | |
$FieldName | |||
) |
Get whether specified field exists in specified table.
string | $TableName | Name of database table. |
string | $FieldName | Name of database field. |
Definition at line 1107 of file Database.php.
References FetchField(), and Query().
Referenced by UpdateValue().
Database::GetClientVersion | ( | ) |
Get version number of the client libraries being used to connect to the database server (Currently the mysql library version number).
Definition at line 207 of file Database.php.
Database::GetColumns | ( | $TableName | ) |
Get column (database field) names.
string | $TableName | Name of database table. |
Definition at line 1135 of file Database.php.
References DBName(), FetchColumn(), and Query().
Referenced by CopyValues().
Database::GetFieldType | ( | $TableName, | |
$FieldName | |||
) |
Get field (column) type.
string | $TableName | Name of database table. |
string | $FieldName | Name of database field. |
Definition at line 1123 of file Database.php.
References FetchColumn(), and Query().
Database::GetHostInfo | ( | ) |
Get database connection type and hostname.
Definition at line 217 of file Database.php.
Database::GetMaxQueryLength | ( | ) |
Get maximum size for query string.
Definition at line 1147 of file Database.php.
References Query().
Referenced by InsertArray().
Database::GetServerVersion | ( | $FullVersion = FALSE | ) |
Get database server version number.
bool | $FullVersion | TRUE for the whole version string or FALSE for just the version number (OPTIONAL, default FALSE). |
Definition at line 183 of file Database.php.
References Query().
Referenced by SetDefaultStorageEngine().
Database::IgnoredError | ( | ) |
Check whether an error was ignored by the most recent query.
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.
string | $Table | Name of the table to insert into. |
string | $ValueField | Name of column to insert array values into. |
array | $Values | Array values. |
string | $KeyField | Name of column to insert array keys into. (OPTIONAL) |
int | $AvgDataLength | Average length of value (and keys, if key column name supplied) in characters. (OPTIONAL, defaults to 20) |
Definition at line 974 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.
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.
string | $String | Debug string. |
Definition at line 1085 of file Database.php.
References Query().
|
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.
Definition at line 1179 of file Database.php.
|
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.
Definition at line 1168 of file Database.php.
Database::NumRowsAffected | ( | ) |
Get number of rows affected by last INSERT, UPDATE, REPLACE, or DELETE query.
Definition at line 647 of file Database.php.
Database::NumRowsSelected | ( | ) |
Get number of rows returned by last SELECT or SHOW query.
Definition at line 621 of file Database.php.
Referenced by MetadataFieldOrder\__construct(), TableExists(), and UpdateValue().
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.
string | $QueryString | SQL query string. |
string | $FieldName | Name of field for which to return value to caller. (OPTIONAL) |
Definition at line 351 of file Database.php.
References FetchField().
Referenced by MysqlSystemVariables\__construct(), MetadataFieldOrder\__construct(), CopyValues(), MetadataFieldOrder\Delete(), ExecuteQueriesFromFile(), FieldExists(), GetColumns(), GetFieldType(), GetMaxQueryLength(), GetServerVersion(), InsertArray(), LastInsertId(), LogComment(), SetDefaultStorageEngine(), TableExists(), and UpdateValue().
|
static |
Enable or disable debugging output for queries.
Output is disabled by default. This setting applies to all instances of the Database class.
bool | $NewSetting | TRUE to enable output or FALSE to disable output. |
Definition at line 1158 of file Database.php.
Database::QueryErrMsg | ( | ) |
Get most recent error message text set by Query().
Definition at line 590 of file Database.php.
Database::QueryErrNo | ( | ) |
Get most recent error code set by Query().
Definition at line 600 of file Database.php.
Database::SetDefaultStorageEngine | ( | $Engine | ) |
Set default database storage engine.
string | $Engine | New default storage engine. |
Definition at line 167 of file Database.php.
References GetServerVersion(), and Query().
|
static |
Set default database name.
string | $DatabaseName | Name of database to use once logged in. |
Definition at line 154 of file Database.php.
|
static |
Set default login and host info for database server.
string | $UserName | User name to use to log in to database server. |
string | $Password | Password to use to log in to database server. |
string | $HostName | Host 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:
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. |
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) |
Definition at line 313 of file Database.php.
Database::TableExists | ( | $TableName | ) |
Get whether specified table exists.
string | $TableName | Name of database table. |
Definition at line 1095 of file Database.php.
References NumRowsSelected(), and Query().
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.
string | $TableName | Name of database table. |
string | $FieldName | Name of database field. |
float | $NewValue | New value to set. Use DB_NOVALUE to not set a new value (i.e. when just getting a value). |
string | $Condition | SQL query conditional to use in SELECT or UPDATE statements (should not include "WHERE"). Use NULL if no conditional is needed. |
array | $CachedRecord | Variable to use to cache values. Accessed by reference. |
Definition at line 905 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.
string | $TableName | Name of database table. |
string | $FieldName | Name of database field. |
int | $NewValue | New value to set. Use DB_NOVALUE to not set a new value (i.e. when just getting a value). |
string | $Condition | SQL query conditional to use in SELECT or UPDATE statements (should not include "WHERE"). Use NULL if no conditional is needed. |
array | $CachedRecord | Variable to use to cache values. Accessed by reference. |
Definition at line 881 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.
string | $TableName | Name of database table. |
string | $FieldName | Name of database field. |
mixed | $NewValue | New value to set. Use DB_NOVALUE to not set a new value (i.e. when just getting a value). |
string | $Condition | SQL query conditional to use in SELECT or UPDATE statements (should not include "WHERE"). Use NULL if no conditional is needed. |
array | $CachedRecord | Variable to use to cache values. Accessed by reference. |
Definition at line 803 of file Database.php.
References DB_NOVALUE, FetchRow(), FieldExists(), NumRowsSelected(), and Query().
Referenced by UpdateFloatValue(), and UpdateIntValue().
|
protected |
Definition at line 1207 of file Database.php.
Referenced by DBHostName().
|
protected |
Definition at line 1208 of file Database.php.
Referenced by __construct(), __wakeup(), and DBName().
|
protected |
Definition at line 1206 of file Database.php.
|
protected |
Definition at line 1205 of file Database.php.
Referenced by DBUserName().
const Database::CR_CONN_HOST_ERROR = 2003 |
Definition at line 1255 of file Database.php.
const Database::CR_CONNECTION_ERROR = 2002 |
Definition at line 1253 of file Database.php.
const Database::CR_SERVER_GONE_ERROR = 2006 |
Definition at line 1256 of file Database.php.
const Database::CR_SERVER_LOST = 2013 |
Definition at line 1257 of file Database.php.