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
Datei anzeigen 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.
See also: Gdn_DatabaseDriver::OrOp()
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

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.'
See also: Gdn_DatabaseDriver::Join()
public leftJoin ( $TableName, $On )

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.'
See also: Gdn_DatabaseDriver::Like()
public notLike ( $Field, $Match = '', $Side = 'both' )

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.
See also: Gdn_DatabaseDriver::Having()
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

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.'
See also: Gdn_DatabaseDriver::NotLike()
See also: GenricDriver::Like()
public orNotLike ( $Field, $Match = '', $Side = 'both' )

orOp() public method

Concat the next where expression with an 'or' operator.
See also: Gdn_DatabaseDriver::AndOp()
public orOp ( boolean $SetDefault = false ) : Gdn_SQLDriver
$SetDefault boolean Whether or not the 'or' is one time, or will revert.
return Gdn_SQLDriver $this

orWhere() public method

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.'
See also: Gdn_DatabaseDriver::WhereIn()
See also: Gdn_DatabaseDriver::WhereNotIn()
public orWhereNotIn ( $Field, $Values )

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.'
See also: Gdn_DatabaseDriver::WhereIn()
public whereNotIn ( $Field, $Values, $Escape = true ) : Gdn_SQLDriver
return Gdn_SQLDriver $this

Property Details

$CaptureModifications public_oe property

Whether or not to capture (not execute) DML statements.
public $CaptureModifications

$ClassName public_oe property

The name of the class that has been instantiated.
public $ClassName

$Database public_oe property

The connection and engine information for the database.
public $Database

$_AliasMap protected_oe property

An associative array of table alias => table name pairs.
protected $_AliasMap

$_CacheKey protected_oe property

The name of the cache key associated with this query.
protected $_CacheKey

$_CacheOperation protected_oe property

Cache op.
protected $_CacheOperation

$_CacheOptions protected_oe property

Cache options.
protected $_CacheOptions

$_DatabaseInfo protected_oe property

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

$_Distinct protected_oe property

A boolean value indicating if this is a distinct query.
protected $_Distinct

$_Froms protected_oe property

A collection of tables from which data is being selected.
protected $_Froms

$_GroupBys protected_oe property

A collection of group by clauses.
protected $_GroupBys

$_Havings protected_oe property

A collection of having clauses.
protected $_Havings

$_Joins protected_oe property

A collection of tables which have been joined to.
protected $_Joins

$_Limit protected_oe property

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

$_NamedParameters protected_oe property

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

$_NoReset protected_oe 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_oe property

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

$_OpenWhereGroupCount protected_oe property

The number of where groups currently open.
protected $_OpenWhereGroupCount

$_Options protected_oe property

Extended options for a statement, usable by the driver.
protected $_Options

$_OrderBys protected_oe property

A collection of order by statements.
protected $_OrderBys

$_Selects protected_oe property

A collection of fields that are being selected.
protected $_Selects

$_Sets protected_oe property

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

$_WhereConcat protected_oe property

The logical operator used to concatenate where clauses.
protected $_WhereConcat

$_WhereConcatDefault protected_oe property

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

$_WhereGroupConcat protected_oe property

The logical operator used to concatenate where group clauses.
protected $_WhereGroupConcat

$_WhereGroupConcatDefault protected_oe property

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

$_WhereGroupCount protected_oe property

The number of where groups to open.
protected $_WhereGroupCount

$_Wheres protected_oe property

A collection of where clauses.
protected $_Wheres