PHP Class Gdn_SQLDriver, vanilla

This class is HEAVILY inspired by and, in places, flat out copied from CodeIgniter (http://www.codeigniter.com). My hat is off to them.

Author: Mark O'Sullivan

Show file Open project: vanilla/vanilla Class Usage Examples

Public Properties

Property	Type	Description
$CaptureModifications		Whether or not to capture (not execute) DML statements.
$ClassName		The name of the class that has been instantiated.
$Database		The connection and engine information for the database.

Protected Properties

Property	Type	Description
$_AliasMap		An associative array of table alias => table name pairs.
$_CacheKey		The name of the cache key associated with this query.
$_CacheOperation		Cache op.
$_CacheOptions		Cache options.
$_DatabaseInfo		An associative array of information about the database to which the application is connected. Values include: Engine, Version, DatabaseName.
$_Distinct		A boolean value indicating if this is a distinct query.
$_Froms		A collection of tables from which data is being selected.
$_GroupBys		A collection of group by clauses.
$_Havings		A collection of having clauses.
$_Joins		A collection of tables which have been joined to.
$_Limit		The number of records to limit the query to. FALSE by default.
$_NamedParameters		An associative array of parameter_name => parameter_value pairs to be inserted into the prepared $this->_PDOStatement.
$_NoReset		Whether or not to reset the properties when a query is executed. 0 = The object will reset after query execution. 1 = The object will not reset after the NEXT query execution. 2 = The object will not reset after ALL query executions.
$_Offset		The offset from which data should be returned. FALSE by default.
$_OpenWhereGroupCount		The number of where groups currently open.
$_Options		Extended options for a statement, usable by the driver.
$_OrderBys		A collection of order by statements.
$_Selects		A collection of fields that are being selected.
$_Sets		An associative array of Field Name => Value pairs to be saved to the database.
$_WhereConcat		The logical operator used to concatenate where clauses.
$_WhereConcatDefault		The default $_WhereConcat that will be reverted back to after every where clause is appended.
$_WhereGroupConcat		The logical operator used to concatenate where group clauses.
$_WhereGroupConcatDefault		The default $_WhereGroupConcat that will be reverted back to after every where or where group clause is appended.
$_WhereGroupCount		The number of where groups to open.
$_Wheres		A collection of where clauses.

Public Methods

Method	Description
GetLimit ( string $Query, integer $Limit, integer $Offset )	Adds a limit clause to the provided query for this database engine.
__construct ( )
_whereIn ( string $Field, array $Values, string $Op = 'in', string $Escape = true ) : Gdn_SQLDriver	Adds to the $this->_WhereIns collection. Used to generate a "where field in (1,2,3)" query. Called by $this->WhereIn(), $this->OrWhereIn(), $this->WhereNotIn(), and $this->OrWhereNotIn().
andOp ( boolean $SetDefault = false ) : Gdn_SQLDriver	Concat the next where expression with an 'and' operator.
applyParameters ( $Sql, null $Parameters = null ) : mixed
beginWhereGroup ( ) : Gdn_SQLDriver	Begin bracketed group in the where clause to group logical expressions together.
cache ( string \| array $Key, string $Operation = null, array $Options = null ) : Gdn_SQLDriver	Set the cache key for this transaction
conditionExpr ( string $Field, mixed $Value, $EscapeFieldSql = true, $EscapeValueSql = true ) : string	Returns a single Condition Expression for use in a 'where' or an 'on' clause.
databaseName ( )	Returns the name of the database currently connected to.
delete ( mixed $Table = '', mixed $Where = '', integer $Limit = false )	Builds and executes a delete from query.
distinct ( boolean $Bool = true ) : Gdn_SQLDriver	Specifies that the query should be run as a distinct so that duplicate columns are grouped together. Returns this object for chaining purposes.
emptyTable ( string $Table = '' )	Removes all data from a table.
endWhereGroup ( ) : Gdn_SQLDriver	End a bracketed group in the where clause.
fetchColumnSql ( string $Table )	Returns a platform-specific query to fetch column data from $Table.
fetchColumns ( string $Table )	Returns an array containing column names from $Table.
fetchTableSchema ( string $Table )	Returns an array of schema data objects for each field in the specified table. The returned array of objects contains the following properties: Name, PrimaryKey, Type, AllowNull, Default, Length, Enum.
fetchTableSql ( mixed $LimitToPrefix = false )	Returns a platform-specific query to fetch table names.
fetchTables ( mixed $LimitToPrefix = false ) : array	Returns an array containing table names in the database.
fetchVersionSql ( )	Returns a string of SQL that retrieves the database engine version in the fieldname "version".
formatTableName ( string $Table )	Takes a table name and makes sure it is formatted for this database engine.
from ( mixed $From ) : Gdn_SQLDriver	The table(s) from which to select values. Returns this object for chaining purposes.
get ( string $Table = '', string $OrderFields = '', string $OrderDirection = 'asc', integer $Limit = false, integer $PageNumber = false ) : Gdn_DataSet	Builds the select statement and runs the query, returning a result object.
getCount ( string $Table = '', mixed $Where = false )	Returns the total number of records in the specified table.
getCountLike ( string $Table = '', mixed $Like = false )	Returns the total number of records in the specified table.
getDelete ( string $TableName, array $Wheres = [] )	Returns a delete statement for the specified table and the supplied conditions.
getInsert ( string $Table, string $Data )	Returns an insert statement for the specified $Table with the provided $Data.
getSelect ( )	Builds the select statement based on the various collections in this object. This method should not be called directly; it is called by $this->Get() and $this->GetWhere().
getTruncate ( string $Table )	Returns a truncate statement for this database engine.
getUpdate ( array $Tables, array $Data, mixed $Where )	Returns an update statement for the specified table with the provided $Data.
getWhere ( string $Table = '', mixed $Where = false, string $OrderFields = '', string $OrderDirection = 'asc', integer $Limit = false, integer $Offset ) : Gdn_DataSet	Builds the select statement and runs the query, returning a result object. Allows a where clause, limit, and offset to be added directly.
getWhereLike ( string $Table = '', mixed $Like = false, string $OrderFields = '', string $OrderDirection = 'asc', integer $Limit = false, integer $PageNumber = false )	Builds the select statement and runs the query, returning a result object. Allows a like clause, limit, and offset to be added directly.
groupBy ( mixed $Fields = null ) : Gdn_SQLDriver	Adds to the $this->_GroupBys collection.
having ( mixed $Field, string $Value = '', $EscapeField = true, $EscapeValue = true ) : Gdn_SQLDriver	Adds to the $this->_Havings collection. Called by $this->Having() and $this->OrHaving().
history ( $UpdateFields = true, $InsertFields = false ) : Gdn_SQLDriver
identity ( )	Returns the last identity to be inserted into the database at $this->_Connection.
information ( string $Request )	Returns information about the database. Values include: Engine, Version, DatabaseName.
insert ( string $Table = '', mixed $Set = null, string $Select = '' )	Builds the insert statement and runs the query, returning a result object.
join ( string $TableName, string $On, string $Join = '' ) : Gdn_SQLDriver	The table(s) to which this query should join. Returns this object for chaining purposes.
leftJoin ( $TableName, $On )	A convenience method for Gdn_DatabaseDriver::Join that makes the join type 'left.'
like ( mixed $Field, string $Match = '', string $Side = 'both', string $Op = 'like' ) : Gdn_SQLDriver	Adds to the $this->_Wheres collection. Used to generate the LIKE portion of a query. Called by $this->Like(), $this->NotLike()
limit ( integer $Limit, integer $Offset = false ) : Gdn_SQLDriver	Sets the limit (and offset optionally) for the query.
mapAliases ( string $TableString ) : string	Takes a provided table specification and parses out any table aliases provided, placing them in an alias mapping array. Returns the table specification with any table prefix prepended.
namedParameter ( string $Name, boolean $CreateNew = false, $Value = null ) : string	Takes a parameter name and makes sure it is cleaned up to be used as a named parameter in a pdo prepared statement.
namedParameters ( $NewValue = null )
noReset ( $NoReset = true, boolean $OneTime = true ) : Gdn_SQLDriver	Allows a query to be called without resetting the object.
notLike ( $Field, $Match = '', $Side = 'both' )	A convenience method for Gdn_DatabaseDriver::Like that changes the operator to 'not like.'
offset ( integer $Offset ) : Gdn_SQLDriver	Sets the offset for the query.
options ( string $Key, mixed $Value = null ) : mixed	Gets/sets an option on the object.
orBeginWhereGroup ( ) : Gdn_SQLDriver	A convenience method that calls Gdn_DatabaseDriver::BeginWhereGroup with concatenated with an 'or.'
orHaving ( mixed $Field, string $Value = '', boolean $EscapeField = true, boolean $EscapeValue = true ) : Gdn_SQLDriver	Adds to the $this->_Havings collection. Concatenates multiple calls with OR.
orLike ( $Field, $Match = '', $Side = 'both', $Op = 'like' ) : Gdn_SQLDriver	A convenience method that calls Gdn_DatabaseDriver::Like with concatenated with an 'or.'
orNotLike ( $Field, $Match = '', $Side = 'both' )	A convenience method for Gdn_DatabaseDriver::Like that changes the operator to 'not like,' and is concatenated with an 'or.'
orOp ( boolean $SetDefault = false ) : Gdn_SQLDriver	Concat the next where expression with an 'or' operator.
orWhere ( $Field, $Value = null, $EscapeFieldSql = true, $EscapeValueSql = true )
orWhereExists ( $SqlDriver, $Op = 'exists' )	A convienience method for Gdn_DatabaseDriver::WhereExists() concatenates with an 'or.'
orWhereIn ( $Field, $Values )
orWhereNotExists ( $SqlDriver )	A convienience method for Gdn_DatabaseDriver::WhereExists() that changes the operator to 'not exists,' and concatenates with an 'or.'
orWhereNotIn ( $Field, $Values )	A convenience method for Gdn_DatabaseDriver::WhereIn() that changes the operator to 'not in,' and concatenates with an 'or.'
orderBy ( string $Fields, string $Direction = 'asc' ) : Gdn_SQLDriver	Adds to the $this->_OrderBys collection.
permission ( mixed $Permission, string $ForeignAlias, string $ForeignColumn, string $JunctionTable = '', string $JunctionColumn = '' ) : Gdn_SQLDriver	Joins the query to a permission junction table and limits the results accordingly.
prefixTable ( string $Table )	Prefixes a table with the database prefix if it is not already there.
put ( string $Table = '', mixed $Set = null, string $Where = false, integer $Limit = false )	Builds the update statement and runs the query, returning a result object.
query ( $Sql, $Type = 'select' )
quoteIdentifier ( $String )
replace ( string $Table = '', array $Set = null, array $Where, $CheckExisting = false )	Inserts or updates values in the table depending on whether they are already there.
reset ( ) : Gdn_SQLDriver	Resets properties of this object that relate to building a select statement back to their default values. Called by $this->Get() and $this->GetWhere().
select ( mixed $Select = '*', string $Function = '', string $Alias = '' ) : Gdn_SQLDriver	Allows the specification of columns to be selected in a database query.
selectCase ( string $Field, array $Options, string $Alias ) : Gdn_SQLDriver	Allows the specification of a case statement in the select list.
set ( mixed $Field, string $Value = '', boolean $EscapeString = true, boolean $CreateNewNamedParameter = true ) : Gdn_SQLDriver	Adds values to the $this->_Sets collection. Allows for the inserting and updating of values to the db.
setEncoding ( $Encoding )	Sets the character encoding for this database engine.
setOverwrite ( mixed $Field, string $Value = '', boolean $EscapeString = true )	Similar to $this->Set() in every way except that if a named parameter is used in place of $Value, it will overwrite any existing value associated with that name as opposed to adding a new name/value (which is the default way that $this->Set() works).
truncate ( string $Table = '' )	Truncates all data from a table (will delete from the table if database does not support truncate).
update ( string $Table, mixed $Set = null, string $Where = false, integer $Limit = false ) : Gdn_SQLDriver	Allows the specification of a table to be updated in a database query.
version ( )	Returns a plain-english string containing the version of the database engine.
where ( mixed $Field, mixed $Value = null, boolean $EscapeFieldSql = true, $EscapeValueSql = true ) : Gdn_SQLDriver	Adds to the $this->_Wheres collection. Called by $this->Where() and $this->OrWhere();
whereCount ( ) : integer	Get the number of items in the where array.
whereExists ( Gdn_DatabaseDriver $SqlDriver, string $Op = 'exists' ) : Gdn_DatabaseDriver	Adds an Sql exists expression to the $this->_Wheres collection.
whereIn ( string $Field, array $Values, $Escape = true ) : Gdn_SQLDriver	Adds to the $this->_WhereIns collection. Used to generate a "where field in (1,2,3)" query. Concatenated with AND.
whereNotExists ( $SqlDriver )	A convienience method for Gdn_DatabaseDriver::WhereExists() that changes the operator to 'not exists'.
whereNotIn ( $Field, $Values, $Escape = true ) : Gdn_SQLDriver	A convenience method for Gdn_DatabaseDriver::WhereIn() that changes the operator to 'not in.'

Protected Methods

Method	Description
_endQuery ( )	Closes off any open elements in the query before execution.
_fromTables ( mixed $Tables )	Returns a string of comma delimited table names to select from.
_getIdentifierTokens ( $Sql ) : array	A helper function for escaping sql identifiers.
_having ( string $Sql ) : Gdn_SQLDriver	Adds to the $this->_Havings collection.
_parseExpr ( string $Expr, string $Name = null, $EscapeExpr = false ) : string	Parses an expression for use in where clauses.
_where ( string $Sql ) : Gdn_SQLDriver	Adds to the $this->_Wheres collection. This is the most basic where that adds a freeform string of text.
escapeIdentifier ( string $RefExpr ) : string	Takes a string formatted as an SQL field reference and escapes it for the defined database engine.
escapeSql ( mixed $String, boolean $FirstWordOnly = false )	Takes a string of SQL and escapes it for the defined database engine.

Method Details

GetLimit() public method

Adds a limit clause to the provided query for this database engine.

public GetLimit ( string $Query, integer $Limit, integer $Offset )
$Query	string	The SQL string to which the limit statement should be appended.
$Limit	integer	The number of records to limit the query to.
$Offset	integer	The number of records to offset the query from.

__construct() public method

public __construct ( )

_endQuery() protected method

Ideally, the programmer should have everything closed off so this method will do nothing.

protected _endQuery ( )

_fromTables() protected method

Returns a string of comma delimited table names to select from.

protected _fromTables ( mixed $Tables )
$Tables	mixed	The name of a table (or an array of table names) to be added in the from clause of a query.

_getIdentifierTokens() protected method

A helper function for escaping sql identifiers.

protected _getIdentifierTokens ( $Sql ) : array
return	array	All of the tokens in the sql. The tokens that require escaping will still have back ticks.

_having() protected method

This is the most basic having that adds a freeform string of text. It should be used only in conjunction with methods that properly escape the sql.

protected _having ( string $Sql ) : Gdn_SQLDriver
$Sql	string	The condition to add.
return	Gdn_SQLDriver	$this

_parseExpr() protected method

Parses an expression for use in where clauses.

protected _parseExpr ( string $Expr, string $Name = null, $EscapeExpr = false ) : string
$Expr	string	The expression to parse.
$Name	string	A name to give the parameter if $Expr becomes a named parameter.
return	string	The parsed expression.

_where() protected method

It should be used only in conjunction with methods that properly escape the sql.

protected _where ( string $Sql ) : Gdn_SQLDriver
$Sql	string	The condition to add.
return	Gdn_SQLDriver	$this

_whereIn() public method

Adds to the $this->_WhereIns collection. Used to generate a "where field in (1,2,3)" query. Called by $this->WhereIn(), $this->OrWhereIn(), $this->WhereNotIn(), and $this->OrWhereNotIn().

public _whereIn ( string $Field, array $Values, string $Op = 'in', string $Escape = true ) : Gdn_SQLDriver
$Field	string	The field to search in for $Values.
$Values	array	An array of values to look for in $Field.
$Op	string	Either 'in' or 'not in' for the respective operation.
$Escape	string	Whether or not to escape the items in $Values. clause.
return	Gdn_SQLDriver	$this

andOp() public method

Note: Since 'and' is the default operator to begin with this method doesn't usually have to be called, unless Gdn_DatabaseDriver::Or(FALSE) has previously been called.

applyParameters() public method

public applyParameters ( $Sql, null $Parameters = null ) : mixed
$Sql
$Parameters	null
return	mixed

beginWhereGroup() public method

Begin bracketed group in the where clause to group logical expressions together.

public beginWhereGroup ( ) : Gdn_SQLDriver
return	Gdn_SQLDriver	$this

cache() public method

Set the cache key for this transaction

public cache ( string \| array $Key, string $Operation = null, array $Options = null ) : Gdn_SQLDriver
$Key	string \| array	The cache key (or array of keys) that this query will save into.
$Operation	string	The cache operation as a hint to the db.
$Options	array	The cache options as passed into Gdn_Cache::Store().
return	Gdn_SQLDriver	$this

conditionExpr() public method

Returns a single Condition Expression for use in a 'where' or an 'on' clause.

public conditionExpr ( string $Field, mixed $Value, $EscapeFieldSql = true, $EscapeValueSql = true ) : string
$Field	string	The name of the field on the left hand side of the expression. If $Field ends with an operator, then it used for the comparison. Otherwise '=' will be used.
$Value	mixed	The value on the right side of the expression. If $EscapeValueSql is true then it will end up in a parameter. Syntax The $Field and Value expressions can begin with special characters to do certain things. =: This means that the argument is a function call. If you want to pass field reference arguments into the function then enclose them in square brackets. ex. `'=LEFT([u.Name], 4)'` will call the LEFT database function on the u.Name column. @: This means that the argument is a literal. This is useful for passing in literal numbers. no prefix>: This will treat the argument differently depending on the argument. - $Field - The argument is a column reference. - $Value - The argument will become a named parameter.
return	string	The single expression.

databaseName() public method

Returns the name of the database currently connected to.

public databaseName ( )

delete() public method

Builds and executes a delete from query.

public delete ( mixed $Table = '', mixed $Where = '', integer $Limit = false )
$Table	mixed	The table (or array of table names) to delete from.
$Where	mixed	The string on the left side of the where comparison, or an associative array of Field => Value items to compare.
$Limit	integer	The number of records to limit the query to.

distinct() public method

Specifies that the query should be run as a distinct so that duplicate columns are grouped together. Returns this object for chaining purposes.

public distinct ( boolean $Bool = true ) : Gdn_SQLDriver
$Bool	boolean	A boolean value indicating if the query should be distinct or not.
return	Gdn_SQLDriver	$this

emptyTable() public method

Removes all data from a table.

public emptyTable ( string $Table = '' )
$Table	string	The table to empty.

endWhereGroup() public method

Note: If no items where added to the group then no barackets will appear in the final statement.

public endWhereGroup ( ) : Gdn_SQLDriver
return	Gdn_SQLDriver	$this

escapeIdentifier() protected method

Takes a string formatted as an SQL field reference and escapes it for the defined database engine.

protected escapeIdentifier ( string $RefExpr ) : string
$RefExpr	string	The reference expression to be escaped. The reference should be in the form of alias.column.
return	string	Returns the escaped string.

escapeSql() protected method

ie. adds backticks or any other database-specific formatting.

protected escapeSql ( mixed $String, boolean $FirstWordOnly = false )
$String	mixed	The string (or array of strings) of SQL to be escaped.
$FirstWordOnly	boolean	A boolean value indicating if the first word should be escaped only.

fetchColumnSql() public method

Returns a platform-specific query to fetch column data from $Table.

public fetchColumnSql ( string $Table )
$Table	string	The name of the table to fetch column data from.

fetchColumns() public method

Returns an array containing column names from $Table.

public fetchColumns ( string $Table )
$Table	string	The name of the table to fetch column data from.

fetchTableSchema() public method

Returns an array of schema data objects for each field in the specified table. The returned array of objects contains the following properties: Name, PrimaryKey, Type, AllowNull, Default, Length, Enum.

public fetchTableSchema ( string $Table )
$Table	string	The name of the table to get schema data for.

fetchTableSql() public method

Returns a platform-specific query to fetch table names.

public fetchTableSql ( mixed $LimitToPrefix = false )
$LimitToPrefix	mixed	Whether or not to limit the search to tables with the database prefix or a specific table name. The following types can be given for this parameter: - TRUE: The search will be limited to the database prefix. - FALSE: All tables will be fetched. Default. - string: The search will be limited to a like clause. The ':_' will be replaced with the database prefix.

fetchTables() public method

Returns an array containing table names in the database.

public fetchTables ( mixed $LimitToPrefix = false ) : array
$LimitToPrefix	mixed	Whether or not to limit the search to tables with the database prefix or a specific table name. The following types can be given for this parameter: - TRUE: The search will be limited to the database prefix. - FALSE: All tables will be fetched. Default. - string: The search will be limited to a like clause. The ':_' will be replaced with the database prefix.
return	array

fetchVersionSql() public method

Returns a string of SQL that retrieves the database engine version in the fieldname "version".

public fetchVersionSql ( )

formatTableName() public method

Takes a table name and makes sure it is formatted for this database engine.

public formatTableName ( string $Table )
$Table	string	The name of the table name to format.

from() public method

The table(s) from which to select values. Returns this object for chaining purposes.

public from ( mixed $From ) : Gdn_SQLDriver
$From	mixed	A string or array of table names/aliases from which to select data. Accepted formats include: user user, user u2, role array("user u", "user u2", "role")
return	Gdn_SQLDriver	$this

get() public method

Builds the select statement and runs the query, returning a result object.

public get ( string $Table = '', string $OrderFields = '', string $OrderDirection = 'asc', integer $Limit = false, integer $PageNumber = false ) : Gdn_DataSet
$Table	string	The table from which to select data. Adds to the $this->_Froms collection.
$OrderFields	string	A string of fields to be ordered.
$OrderDirection	string	The direction of the sort.
$Limit	integer	Adds a limit to the query.
$PageNumber	integer	The page of data to retrieve.
return	Gdn_DataSet

getCount() public method

Returns the total number of records in the specified table.

public getCount ( string $Table = '', mixed $Where = false )
$Table	string	The table from which to count rows of data.
$Where	mixed	Adds to the $this->_Wheres collection using $this->Where();

getCountLike() public method

Returns the total number of records in the specified table.

public getCountLike ( string $Table = '', mixed $Like = false )
$Table	string	The table from which to count rows of data.
$Like	mixed	Adds to the $this->_Wheres collection using $this->Like();

getDelete() public method

Returns a delete statement for the specified table and the supplied conditions.

public getDelete ( string $TableName, array $Wheres = [] )
$TableName	string	The name of the table to delete from.
$Wheres	array	An array of where conditions.

getInsert() public method

Returns an insert statement for the specified $Table with the provided $Data.

public getInsert ( string $Table, string $Data )
$Table	string	The name of the table to insert data into.
$Data	string	An associative array of FieldName => Value pairs that should be inserted $Table.

getSelect() public method

Builds the select statement based on the various collections in this object. This method should not be called directly; it is called by $this->Get() and $this->GetWhere().

public getSelect ( )

getTruncate() public method

Returns a truncate statement for this database engine.

public getTruncate ( string $Table )
$Table	string	The name of the table to updated data in.

getUpdate() public method

Returns an update statement for the specified table with the provided $Data.

public getUpdate ( array $Tables, array $Data, mixed $Where )
$Tables	array	The names of the tables to updated data in.
$Data	array	An associative array of FieldName => Value pairs that should be inserted $Table.
$Where	mixed	A where clause (or array containing multiple where clauses) to be applied to the where portion of the update statement.

getWhere() public method

Builds the select statement and runs the query, returning a result object. Allows a where clause, limit, and offset to be added directly.

public getWhere ( string $Table = '', mixed $Where = false, string $OrderFields = '', string $OrderDirection = 'asc', integer $Limit = false, integer $Offset ) : Gdn_DataSet
$Table	string	The table from which to select data. Adds to the $this->_Froms collection.
$Where	mixed	Adds to the $this->_Wheres collection using $this->Where();
$OrderFields	string	A string of fields to be ordered.
$OrderDirection	string	The direction of the sort.
$Limit	integer	The number of records to limit the query to.
$Offset	integer	The offset where the query results should begin.
return	Gdn_DataSet	The data returned by the query.

getWhereLike() public method

Builds the select statement and runs the query, returning a result object. Allows a like clause, limit, and offset to be added directly.

public getWhereLike ( string $Table = '', mixed $Like = false, string $OrderFields = '', string $OrderDirection = 'asc', integer $Limit = false, integer $PageNumber = false )
$Table	string	The table from which to select data. Adds to the $this->_Froms collection.
$Like	mixed	Adds to the $this->_Wheres collection using $this->Like();
$OrderFields	string	A string of fields to be ordered.
$OrderDirection	string	The direction of the sort.
$Limit	integer	The number of records to limit the query to.
$PageNumber	integer	The offset where the query results should begin.

groupBy() public method

Adds to the $this->_GroupBys collection.

public groupBy ( mixed $Fields = null ) : Gdn_SQLDriver
$Fields	mixed	An array of field names (or a comma-delimited list of field names) to be grouped by.
return	Gdn_SQLDriver	$this

having() public method

Adds to the $this->_Havings collection. Called by $this->Having() and $this->OrHaving().

public having ( mixed $Field, string $Value = '', $EscapeField = true, $EscapeValue = true ) : Gdn_SQLDriver
$Field	mixed	The name of the field (or array of field names) in the having clause.
$Value	string	The string on the right side of the having comparison.
return	Gdn_SQLDriver	$this

history() public method

public history ( $UpdateFields = true, $InsertFields = false ) : Gdn_SQLDriver
return	Gdn_SQLDriver	$this

identity() public method

Returns the last identity to be inserted into the database at $this->_Connection.

public identity ( )

information() public method

Returns information about the database. Values include: Engine, Version, DatabaseName.

public information ( string $Request )
$Request	string	The piece of information being requested. Accepted values are: Engine, Version, and DatabaseName.

insert() public method

Builds the insert statement and runs the query, returning a result object.

public insert ( string $Table = '', mixed $Set = null, string $Select = '' )
$Table	string	The table to which data should be inserted.
$Set	mixed	An associative array (or object) of FieldName => Value pairs that should be inserted, or an array of FieldName values that should have values inserted from $Select.
$Select	string	A select query that will fill the FieldNames specified in $Set.

join() public method

The table(s) to which this query should join. Returns this object for chaining purposes.

public join ( string $TableName, string $On, string $Join = '' ) : Gdn_SQLDriver
$TableName	string	The name of a single table to join to.
$On	string	The conditions on which the join should occur. ie. "user.role_id = role.id"
$Join	string	The type of join to be made. Accepted values are: 'inner', 'outer', 'left', 'right', 'left outer', and 'right outer'.
return	Gdn_SQLDriver	$this

leftJoin() public method

A convenience method for Gdn_DatabaseDriver::Join that makes the join type 'left.'

like() public method

Adds to the $this->_Wheres collection. Used to generate the LIKE portion of a query. Called by $this->Like(), $this->NotLike()

public like ( mixed $Field, string $Match = '', string $Side = 'both', string $Op = 'like' ) : Gdn_SQLDriver
$Field	mixed	The field name (or array of field name => match values) to search in for a like $Match.
$Match	string	The value to try to match using a like statement in $Field.
$Side	string	A string indicating which side of the match to place asterisk operators. Accepted values are left, right, both, none. Default is both.
$Op	string	Either 'like' or 'not like' clause.
return	Gdn_SQLDriver	$this

limit() public method

Sets the limit (and offset optionally) for the query.

public limit ( integer $Limit, integer $Offset = false ) : Gdn_SQLDriver
$Limit	integer	The number of records to limit the query to.
$Offset	integer	The offset where the query results should begin.
return	Gdn_SQLDriver	$this

mapAliases() public method

Takes a provided table specification and parses out any table aliases provided, placing them in an alias mapping array. Returns the table specification with any table prefix prepended.

public mapAliases ( string $TableString ) : string
$TableString	string	The string specification of the table. ie. "tbl_User as u" or "user u".
return	string

namedParameter() public method

Takes a parameter name and makes sure it is cleaned up to be used as a named parameter in a pdo prepared statement.

public namedParameter ( string $Name, boolean $CreateNew = false, $Value = null ) : string
$Name	string	The name of the parameter to cleanup
$CreateNew	boolean	Wether or not this is a new or existing parameter.
return	string	The cleaned up named parameter name.

namedParameters() public method

public namedParameters ( $NewValue = null )

noReset() public method

Allows a query to be called without resetting the object.

public noReset ( $NoReset = true, boolean $OneTime = true ) : Gdn_SQLDriver
$OneTime	boolean	Whether or not this will apply for only the next query or for all subsequent queries.
return	Gdn_SQLDriver	$this

notLike() public method

A convenience method for Gdn_DatabaseDriver::Like that changes the operator to 'not like.'

offset() public method

Sets the offset for the query.

public offset ( integer $Offset ) : Gdn_SQLDriver
$Offset	integer	The offset where the query results should begin.
return	Gdn_SQLDriver	$this

options() public method

Gets/sets an option on the object.

public options ( string $Key, mixed $Value = null ) : mixed
$Key	string	The key of the option.
$Value	mixed	The value of the option or not specified just to get the current value.
return	mixed	The value of the option or $this if $Value is specified.

orBeginWhereGroup() public method

A convenience method that calls Gdn_DatabaseDriver::BeginWhereGroup with concatenated with an 'or.'

public orBeginWhereGroup ( ) : Gdn_SQLDriver
return	Gdn_SQLDriver	$this

orHaving() public method

Adds to the $this->_Havings collection. Concatenates multiple calls with OR.

orLike() public method

A convenience method that calls Gdn_DatabaseDriver::Like with concatenated with an 'or.'

public orLike ( $Field, $Match = '', $Side = 'both', $Op = 'like' ) : Gdn_SQLDriver
return	Gdn_SQLDriver	$this

orNotLike() public method

A convenience method for Gdn_DatabaseDriver::Like that changes the operator to 'not like,' and is concatenated with an 'or.'

orOp() public method

Concat the next where expression with an 'or' operator.

orWhere() public method

See link: Gdn_DatabaseDriver::Where()

public orWhere ( $Field, $Value = null, $EscapeFieldSql = true, $EscapeValueSql = true )

orWhereExists() public method

A convienience method for Gdn_DatabaseDriver::WhereExists() concatenates with an 'or.'

See also: Gdn_DatabaseDriver::WhereExists()

public orWhereExists ( $SqlDriver, $Op = 'exists' )

orWhereIn() public method

public orWhereIn ( $Field, $Values )

orWhereNotExists() public method

A convienience method for Gdn_DatabaseDriver::WhereExists() that changes the operator to 'not exists,' and concatenates with an 'or.'

See also: Gdn_DatabaseDriver::WhereExists()

See also: Gdn_DatabaseDriver::WhereNotExists()

public orWhereNotExists ( $SqlDriver )

orWhereNotIn() public method

A convenience method for Gdn_DatabaseDriver::WhereIn() that changes the operator to 'not in,' and concatenates with an 'or.'

orderBy() public method

Adds to the $this->_OrderBys collection.

public orderBy ( string $Fields, string $Direction = 'asc' ) : Gdn_SQLDriver
$Fields	string	A string of fields to be ordered.
$Direction	string	The direction of the sort.
return	Gdn_SQLDriver	$this

permission() public method

Joins the query to a permission junction table and limits the results accordingly.

public permission ( mixed $Permission, string $ForeignAlias, string $ForeignColumn, string $JunctionTable = '', string $JunctionColumn = '' ) : Gdn_SQLDriver
$Permission	mixed	The permission name (or array of names) to use when limiting the query.
$ForeignAlias	string	The alias of the table to join to (ie. Category).
$ForeignColumn	string	The primary key column name of $JunctionTable (ie. CategoryID).
$JunctionTable	string
$JunctionColumn	string
return	Gdn_SQLDriver	$this

prefixTable() public method

Prefixes a table with the database prefix if it is not already there.

public prefixTable ( string $Table )
$Table	string	The table name to prefix.

put() public method

Builds the update statement and runs the query, returning a result object.

public put ( string $Table = '', mixed $Set = null, string $Where = false, integer $Limit = false )
$Table	string	The table to which data should be updated.
$Set	mixed	An array of $FieldName => $Value pairs, or an object of $DataSet->Field properties containing one rowset.
$Where	string	Adds to the $this->_Wheres collection using $this->Where();
$Limit	integer	Adds a limit to the query.

query() public method

public query ( $Sql, $Type = 'select' )

quoteIdentifier() public method

public quoteIdentifier ( $String )

replace() public method

Inserts or updates values in the table depending on whether they are already there.

public replace ( string $Table = '', array $Set = null, array $Where, $CheckExisting = false )
$Table	string	The name of the table to insert/update.
$Set	array	The columns to update.
$Where	array	The columns to find the row to update. If a row is not found then one is inserted and the items in this array are merged with $Set.

reset() public method

Resets properties of this object that relate to building a select statement back to their default values. Called by $this->Get() and $this->GetWhere().

public reset ( ) : Gdn_SQLDriver
return	Gdn_SQLDriver	$this

select() public method

Returns this object for chaining purposes. ie. $db->Select()->From();

public select ( mixed $Select = '*', string $Function = '', string $Alias = '' ) : Gdn_SQLDriver
$Select	mixed	NotRequired "*" The field(s) being selected. It can be a comma delimited string, the name of a single field, or an array of field names.
$Function	string	NotRequired "" The aggregate function to be used on the select column. Only valid if a single column name is provided. Accepted values are MAX, MIN, AVG, SUM.
$Alias	string	NotRequired "" The alias to give a column name.
return	Gdn_SQLDriver	$this

selectCase() public method

Allows the specification of a case statement in the select list.

public selectCase ( string $Field, array $Options, string $Alias ) : Gdn_SQLDriver
$Field	string	The field being examined in the case statement.
$Options	array	The options and results in an associative array. A blank key will be the final "else" option of the case statement. eg. array('null' => 1, '' => 0) results in "when null then 1 else 0".
$Alias	string	The alias to give a column name.
return	Gdn_SQLDriver	$this

set() public method

Adds values to the $this->_Sets collection. Allows for the inserting and updating of values to the db.

public set ( mixed $Field, string $Value = '', boolean $EscapeString = true, boolean $CreateNewNamedParameter = true ) : Gdn_SQLDriver
$Field	mixed	The name of the field to save value as. Alternately this can be an array of $FieldName => $Value pairs, or even an object of $DataSet->Field properties containing one rowset.
$Value	string	The value to be set in $Field. Ignored if $Field was an array or object.
$EscapeString	boolean	A boolean value indicating if the $Value(s) should be escaped or not.
$CreateNewNamedParameter	boolean	A boolean value indicating that if (a) a named parameter is being created, and (b) that name already exists in $this->_NamedParameters collection, then a new one should be created rather than overwriting the existing one.
return	Gdn_SQLDriver	$this Returns this for fluent calls

setEncoding() public method

Sets the character encoding for this database engine.

public setEncoding ( $Encoding )

setOverwrite() public method

Similar to $this->Set() in every way except that if a named parameter is used in place of $Value, it will overwrite any existing value associated with that name as opposed to adding a new name/value (which is the default way that $this->Set() works).

public setOverwrite ( mixed $Field, string $Value = '', boolean $EscapeString = true )
$Field	mixed	The name of the field to save value as. Alternately this can be an array of $FieldName => $Value pairs, or even an object of $DataSet->Field properties containing one rowset.
$Value	string	The value to be set in $Field. Ignored if $Field was an array or object.
$EscapeString	boolean	A boolean value indicating if the $Value(s) should be escaped or not.

truncate() public method

Truncates all data from a table (will delete from the table if database does not support truncate).

public truncate ( string $Table = '' )
$Table	string	The table to truncate.

update() public method

Returns this object for chaining purposes. ie. $db->Update()->Join()->Set()->Where();

public update ( string $Table, mixed $Set = null, string $Where = false, integer $Limit = false ) : Gdn_SQLDriver
$Table	string	The table to which data should be updated.
$Set	mixed	An array of $FieldName => $Value pairs, or an object of $DataSet->Field properties containing one rowset.
$Where	string	Adds to the $this->_Wheres collection using $this->Where();
$Limit	integer	Adds a limit to the query.
return	Gdn_SQLDriver	$this

version() public method

Returns a plain-english string containing the version of the database engine.

public version ( )

where() public method

Adds to the $this->_Wheres collection. Called by $this->Where() and $this->OrWhere();

public where ( mixed $Field, mixed $Value = null, boolean $EscapeFieldSql = true, $EscapeValueSql = true ) : Gdn_SQLDriver
$Field	mixed	The string on the left side of the comparison, or an associative array of Field => Value items to compare.
$Value	mixed	The string on the right side of the comparison. You can optionally provide an array of DatabaseFunction => Value, which will be converted to DatabaseFunction('Value'). If DatabaseFunction contains a '%s' then sprintf will be used for to place DatabaseFunction into the value.
$EscapeFieldSql	boolean	A boolean value indicating if $this->EscapeSql method should be called on $Field.
return	Gdn_SQLDriver	$this

whereCount() public method

Get the number of items in the where array.

public whereCount ( ) : integer
return	integer	Returns the number of items in the where array.

whereExists() public method

Adds an Sql exists expression to the $this->_Wheres collection.

public whereExists ( Gdn_DatabaseDriver $SqlDriver, string $Op = 'exists' ) : Gdn_DatabaseDriver
$SqlDriver	Gdn_DatabaseDriver	The sql to add.
$Op	string	Either 'exists' or 'not exists'
return	Gdn_DatabaseDriver	$this

whereIn() public method

Adds to the $this->_WhereIns collection. Used to generate a "where field in (1,2,3)" query. Concatenated with AND.

public whereIn ( string $Field, array $Values, $Escape = true ) : Gdn_SQLDriver
$Field	string	The field to search in for $Values.
$Values	array	An array of values to look for in $Field.
return	Gdn_SQLDriver	$this

whereNotExists() public method

A convienience method for Gdn_DatabaseDriver::WhereExists() that changes the operator to 'not exists'.

See also: Gdn_DatabaseDriver::WhereExists()

public whereNotExists ( $SqlDriver )

whereNotIn() public method

A convenience method for Gdn_DatabaseDriver::WhereIn() that changes the operator to 'not in.'

Property Details

$CaptureModifications public property

Whether or not to capture (not execute) DML statements.

public $CaptureModifications

$ClassName public property

The name of the class that has been instantiated.

public $ClassName

$Database public property

The connection and engine information for the database.

public $Database

$_AliasMap protected property

An associative array of table alias => table name pairs.

protected $_AliasMap

$_CacheKey protected property

The name of the cache key associated with this query.

protected $_CacheKey

$_CacheOperation protected property

Cache op.

protected $_CacheOperation

$_CacheOptions protected property

Cache options.

protected $_CacheOptions

$_DatabaseInfo protected property

An associative array of information about the database to which the application is connected. Values include: Engine, Version, DatabaseName.

protected $_DatabaseInfo

$_Distinct protected property

A boolean value indicating if this is a distinct query.

protected $_Distinct

$_Froms protected property

A collection of tables from which data is being selected.

protected $_Froms

$_GroupBys protected property

A collection of group by clauses.

protected $_GroupBys

$_Havings protected property

A collection of having clauses.

protected $_Havings

$_Joins protected property

A collection of tables which have been joined to.

protected $_Joins

$_Limit protected property

The number of records to limit the query to. FALSE by default.

protected $_Limit

$_NamedParameters protected property

An associative array of parameter_name => parameter_value pairs to be inserted into the prepared $this->_PDOStatement.

protected $_NamedParameters

$_NoReset protected property

Whether or not to reset the properties when a query is executed. 0 = The object will reset after query execution. 1 = The object will not reset after the NEXT query execution. 2 = The object will not reset after ALL query executions.

protected $_NoReset

$_Offset protected property

The offset from which data should be returned. FALSE by default.

protected $_Offset

$_OpenWhereGroupCount protected property

The number of where groups currently open.

protected $_OpenWhereGroupCount

$_Options protected property

Extended options for a statement, usable by the driver.

protected $_Options

$_OrderBys protected property

A collection of order by statements.

protected $_OrderBys

$_Selects protected property

A collection of fields that are being selected.

protected $_Selects

$_Sets protected property

An associative array of Field Name => Value pairs to be saved to the database.

protected $_Sets

$_WhereConcat protected property

The logical operator used to concatenate where clauses.

protected $_WhereConcat

$_WhereConcatDefault protected property

The default $_WhereConcat that will be reverted back to after every where clause is appended.

protected $_WhereConcatDefault

$_WhereGroupConcat protected property

The logical operator used to concatenate where group clauses.

protected $_WhereGroupConcat

$_WhereGroupConcatDefault protected property

The default $_WhereGroupConcat that will be reverted back to after every where or where group clause is appended.

protected $_WhereGroupConcatDefault

$_WhereGroupCount protected property

The number of where groups to open.

protected $_WhereGroupCount

$_Wheres protected property

A collection of where clauses.

protected $_Wheres

public andOp ( boolean $SetDefault = false ) : Gdn_SQLDriver
$SetDefault	boolean	Whether or not the 'and' is one time or sets the default operator.
return	Gdn_SQLDriver	$this

public orHaving ( mixed $Field, string $Value = '', boolean $EscapeField = true, boolean $EscapeValue = true ) : Gdn_SQLDriver
$Field	mixed	The name of the field (or array of field names) in the having clause.
$Value	string	The string on the right side of the having comparison.
$EscapeField	boolean	A boolean value indicating if $this->EscapeSql method should be called on $Field.
$EscapeValue	boolean	A boolean value indicating if $this->EscapeString method should be called on $Value.
return	Gdn_SQLDriver	$this

public orOp ( boolean $SetDefault = false ) : Gdn_SQLDriver
$SetDefault	boolean	Whether or not the 'or' is one time, or will revert.
return	Gdn_SQLDriver	$this

public whereNotIn ( $Field, $Values, $Escape = true ) : Gdn_SQLDriver
return	Gdn_SQLDriver	$this