Db_Query_Mysql Class
This class lets you create and use Db queries
Constructor
Db_Query_Mysql
-
$db -
$type -
[$clauses=array()] -
[$parameters=array()] -
[$tables=null]
Parameters:
-
$dbDb_InterfaceAn instance of a Db adapter
-
$typeIntegerThe type of the query. See class constants beginning with TYPE_ .
-
[$clauses=array()]Array optionalThe clauses to add to the query right away
-
[$parameters=array()]Array optionalThe parameters to add to the query right away (to be bound when executing). Values corresponding to numeric keys replace question marks, while values corresponding to string keys replace ":key" placeholders, in the SQL.
-
[$tables=null]Array optionalThe tables operated with query
Item Index
Methods
- __toString
- after
- andWhere
- applyHash
- begin
- bind
- build
- caching
- commit
- criteria
- criteria_internal
- excecute
- fetchAll
- fetchDbRow
- fetchDbRows
- getClause
- getSQL
- groupBy
- hashed
- having
- ignoreCache
- join
- limit
- lock
- map_shard
- onDuplicateKeyUpdate
- onDuplicateKeyUpdate_internal
- options
- orderBy
- orWhere
- reallyConnect
- replace
- replaceKeysCompare
- resume
- reuseStatement
- rollback
- select
- set
- set_internal
- setContext
- shard
- shard_internal
- slice_partitions
- where
Properties
Methods
__toString
()
String
Convert Db_Query_Mysql to it's representation
Returns:
after
-
$after -
$clause
Inserts a custom clause after a particular clause
Parameters:
-
$afterStringThe name of the standard clause to add after, such as FROM or UPDATE
-
$clauseStringThe text of the clause to add
andWhere
-
$criteria -
[$or_criteria=null]
Adds to the WHERE clause, like this: "... AND (x OR y OR z)", where x, y and z are the arguments to this function.
Parameters:
-
$criteriaDb_Expression | StringAn associative array of expression => value pairs. The values are automatically escaped using the database server, or turned into PDO placeholders for prepared statements They can also be arrays, in which case they are placed into an expression of the form "key IN ('val1', 'val2')" Or, this could be a Db_Expression object.
-
[$or_criteria=null]Db_Expression | String optional
Returns:
The resulting object implementing Db_Query_Interface
applyHash
-
$value -
[$hash='normalize'] -
[$len=self::HASH_LEN]
Calculates hash of the value
Parameters:
-
$valueString -
[$hash='normalize']String optional -
[$len=self::HASH_LEN]Integer optional
Returns:
begin
-
[$lock_type='FOR
Begins a transaction right before executing this query. The reason this method is part of the query class is because you often need the "where" clauses to figure out which database to send it to, if sharding is being used.
Parameters:
-
[$lock_type='FORStringUPDATE'] Defaults to 'FOR UPDATE', but can also be 'LOCK IN SHARE MODE' or set it to null to avoid adding a "LOCK" clause
bind
-
[$parameters=array()]
You can bind more parameters to the query manually using this method. These parameters are bound in the order they are passed to the query. Here is an example:
Parameters:
-
[$parameters=array()]Array optionalAn associative array of parameters. The query should contain :name, where :name is a placeholder for the parameter under the key "name". The parameters will be properly escaped. You can also have the query contain question marks (the binding is done using PDO), but then the order of the parameters matters.
Returns:
The resulting object implementing Db_Query_Interface.
Example:
$result = $db->select('*', 'foo')
->where(array('a' => $a))
->andWhere('a = :moo')
->bind(array('moo' => $moo))
->execute();build
()
String
Builds the query from the clauses
Returns:
The SQL query built according to defined clauses
caching
-
[$mode=null]
Turn off automatic caching on fetchAll and fetchDbRows.
Parameters:
-
[$mode=null]Boolean optionalPass false to suppress all caching. Pass true to cache everything. The default is null, which caches everything except empty results.
Returns:
commit
()
chainable
Commits a transaction right after executing this query. The reason this method is part of the query class is because you often need the "where" clauses to figure out which database to send it to, if sharding is being used.
criteria
-
$criteria
This function is specifically for adding criteria to query for sharding purposes. It doesn't affect the SQL generated for the query.
Parameters:
-
$criteriaDb_Expression | ArrayAn associative array of expression => value pairs.
criteria_internal
-
$criteria
Calculates criteria
Parameters:
-
$criteriaDb_Expression | Array
Returns:
excecute
-
[$prepareStatement=false] -
[$shards]
Executes a query against the database and returns the result set.
Parameters:
-
[$prepareStatement=false]Boolean optionalIf true, a PDO statement will be prepared from the query before it is executed. It is also saved for future invocations to use. Do this only if the statement will be executed many times with different parameters. Basically you would use ->bind(...) between invocations of ->execute().
-
[$shards]Array | String optionalYou can pass a shard name here, or a numerically indexed array of shard names, or an associative array where the keys are shard names and the values are the query to execute. This will bypass the usual sharding algorithm.
Returns:
The Db_Result object containing the PDO statement that resulted from the query.
fetchAll
-
$fetch_style=PDO::FETCH_BOTH -
$column_index=null -
$ctor_args=null
Fetches an array of database rows matching the query. If this exact query has already been executed and fetchAll() has been called on the Db_Query, and the return value was cached by the Db_Query class, then that cached value is returned, unless $this->ignoreCache is true. Otherwise, the query is executed and fetchAll() is called on the result.
Parameters:
-
$fetch_style=PDO::FETCH_BOTHEnum -
$column_index=nullEnum -
$ctor_args=nullArray
Returns:
fetchDbRow
-
[$class_name=null] -
[$fields_prefix='']
Fetches one Db_Row object (possibly extended). You can pass a prefix to strip from the field names. It will also filter the result.
Parameters:
-
[$class_name=null]String optionalThe name of the class to instantiate and fill objects from. Must extend Db_Row. Defaults to $this->query->className
-
[$fields_prefix='']String optionalThis is the prefix, if any, to strip out when fetching the rows.
Returns:
Returns false if no row, otherwise returns an object of type $class_name
fetchDbRows
-
[$class_name=null] -
[$fields_prefix=''] -
[$by_field=null]
Fetches an array of Db_Row objects (possibly extended). If this exact query has already been executed and fetchAll() has been called on the Db_Query, and the return value was cached by the Db_Query class, then that cached value is returned. Otherwise, the query is executed and fetchDbRows() is called on the result.
Parameters:
-
[$class_name=null]String optionalThe name of the class to instantiate and fill objects from. Must extend Db_Row. Defaults to $this->className
-
[$fields_prefix='']String optionalThis is the prefix, if any, to strip out when fetching the rows.
-
[$by_field=null]String optionalA field name to index the array by. If the field's value is NULL in a given row, that row is just appended in the usual way to the array.
Returns:
getClause
-
$clauseName -
[$withAfter=false]
Gets a clause from the query
Parameters:
-
$clauseNameString -
[$withAfter=false]Boolean optional
Returns:
If $withAfter is true, returns array($clause, $after) otherwise just returns $clause
getSQL
-
[$callback=null] -
[$template=false]
Gets the SQL that would be executed with the execute() method. See build.
Parameters:
-
[$callback=null]Callable optionalIf not set, this function returns the generated SQL string. If it is set, this function calls $callback, passing it the SQL string, and then returns $this, for chainable interface.
-
[$template=false]Boolean optional
Returns:
Depends on whether $callback is set or not.
groupBy
-
$expression
Adds a GROUP BY clause to a query
Parameters:
-
$expressionDb_Expression | String
Returns:
The resulting object implementing Db_Query_Interface
hashed
-
$value -
[$hash=null]
Calculate hash of the value
Parameters:
-
$valueString -
[$hash=null]String optionalHash is one of 'md5' or 'normalize' optionally followed by '%' and number
Returns:
having
-
$criteria
Adds a HAVING clause to a query
Parameters:
-
$criteriaDb_Expression | ArrayAn associative array of expression => value pairs. The values are automatically escaped using PDO placeholders. Or, this could be a Db_Expression object.
Returns:
The resulting object implementing Db_Query_Interface
ignoreCache
()
chainable
Turn off automatic caching on fetchAll and fetchDbRows.
join
-
$table -
$condition -
[$join_type='INNER']
Joins another table to use in the query
Parameters:
-
$tableStringThe name of the table. May also be "name alias".
-
$conditionDb_Expression | Array | StringThe condition to join on. Thus, JOIN table ON ($condition)
-
[$join_type='INNER']String optionalThe string to prepend to JOIN, such as 'INNER' (default), 'LEFT OUTER', etc.
Returns:
The resulting object implementing Db_Query_Interface
limit
-
$limit -
[$offset=null]
Adds optional LIMIT and OFFSET clauses to the query
Parameters:
-
$limitIntegerA non-negative integer showing how many rows to return
-
[$offset=null]Integer optionalA non-negative integer showing what row to start the result set with.
Returns:
The resulting object implementing Db_Query_Interface
lock
-
[$type='FOR
Works with SELECT queries to lock the selected rows. Use only with MySQL.
Parameters:
-
[$type='FORStringUPDATE'] Defaults to 'FOR UPDATE', but can also be 'LOCK IN SHARE MODE'
map_shard
-
$a
Make partition from array of points
Parameters:
-
$aArray
Returns:
onDuplicateKeyUpdate
-
$updates
Adds an ON DUPLICATE KEY UPDATE clause to an INSERT statement. Use only with MySQL.
Parameters:
-
$updatesArrayAn associative array of column => value pairs. The values are automatically escaped using PDO placeholders.
Returns:
The resulting object implementing Db_Query_Interface $chainable
onDuplicateKeyUpdate_internal
-
$updates
Calculates an ON DUPLICATE KEY UPDATE clause
Parameters:
-
$updatesArrayAn associative array of column => value pairs. The values are automatically escaped using PDO placeholders.
Returns:
options
-
$options
This function provides an easy way to provide additional clauses to the query.
Parameters:
-
$optionsArrayAn associative array of key => value pairs, where the key is the name of the method to call, and the value is the array of arguments. If the value is not an array, it is wrapped in one.
orderBy
-
$expression -
$ascending=true
Adds an ORDER BY clause to the query
Parameters:
-
$expressionDb_Expression | StringA string or Db_Expression with the expression to order the results by.
-
$ascending=trueBooleanIf false, sorts results as descending, otherwise ascending.
Returns:
The resulting object implementing Db_Query_Interface
orWhere
-
$criteria -
[$and_criteria=null]
Adds to the WHERE clause, like this: "... OR (x AND y AND z)", where x, y and z are the arguments to this function.
Parameters:
-
$criteriaDb_Expression | StringAn associative array of expression => value pairs. The values are automatically escaped using the database server, or turned into PDO placeholders for prepared statements They can also be arrays, in which case they are placed into an expression of the form key IN ('val1', 'val2') Or, this could be a Db_Expression object.
-
[$and_criteria=null]Db_Expression | String optional
Returns:
The resulting object implementing Db_Query_Interface
reallyConnect
-
[$shard_name=null]
Connects to database
Parameters:
-
[$shard_name=null]String optional
Returns:
The PDO object for connection
replace
-
[$replacements=array()]
Merges additional replacements over the default replacement array, which is currently just
Parameters:
-
[$replacements=array()]Array optionalThis must be an array.
Example:
array (
'{$prefix}' => $conn['prefix']
)The replacements array is used to replace strings in the SQL before using it. Watch out, because it may replace more than you want!
replaceKeysCompare
()
Integer
private
Returns:
resume
()
Can only be called if this is a query returned from a function that was supposed to execute it, but the user requested a chance to modify it. For example, Db_Row->getRelated and Db_Row->retrieve. After calling a chain of methods, call the resume() method to complete the original function and return the result.
reuseStatement
-
$query
Re-use an existing (prepared) statement. Rarely used except internally.
Parameters:
-
$queryDb_Query_Mysql
rollback
-
[$criteria=null]
Roll back a transaction right after executing this query. The reason this method is part of the query class is because you often need the "where" clauses to figure out which database to send it to, if sharding is being used.
Parameters:
-
[$criteria=null]String optionalPass this to target the rollback to the right shard.
select
-
$fields -
[$tables=''] -
[$repeat=false]
Creates a query to select fields from one or more tables.
Parameters:
-
$fieldsString | ArrayThe fields as strings, or array of alias=>field
-
[$tables='']String | Array optionalThe tables as strings, or array of alias=>field
-
[$repeat=false]Boolean optionalIf $tables is an array, and select() has already been called with the exact table name and alias as one of the tables in that array, then this table is not appended to the tables list if $repeat is false. Otherwise it is. This is really just for using in your hooks.
Returns:
The resulting object implementing Db_Query_Interface. You can use it to chain the calls together.
set
-
$updates
Adds a SET clause to an UPDATE statement
Parameters:
-
$updatesArrayAn associative array of column => value pairs. The values are automatically escaped using PDO placeholders.
Returns:
The resulting object implementing Db_Query_Interface
set_internal
-
$updates
Calculates SET clause
Parameters:
-
$updatesArrayAn associative array of column => value pairs. The values are automatically escaped using PDO placeholders.
Returns:
setContext
-
$callback -
[$args=array()]
Sets context
Parameters:
-
$callbackCallable -
[$args=array()]Array optional
shard
-
[$upcoming=null] -
[$criteria=null]
Analyzes the query's criteria and decides where to execute the query. Here is sample shards config:
NOTE: "fields" shall be an object with keys as fields names and values containing hash definition in the format "type%length" where type is one of 'md5' or 'normalize' and length is hash length hash definition can be empty string or false. In such case 'md5%7' is used
NOTE: "partition" can be an array. In such case shards shall be named after partition points
"Streams": {
"prefix": "streams_",
"dsn": "mysql:host=127.0.0.1;dbname=DBNAME",
"username": "USER",
"password": "PASSWORD",
"driver_options": {
"3": 2
},
"shards": {
"alpha": {
"prefix": "alpha_",
"dsn": "mysql:host=127.0.0.1;dbname=SHARDDBNAME",
"username": "USER",
"password": "PASSWORD",
"driver_options": {
"3": 2
}
},
"betta": {
"prefix": "betta_",
"dsn": "mysql:host=127.0.0.1;dbname=SHARDDBNAME",
"username": "USER",
"password": "PASSWORD",
"driver_options": {
"3": 2
}
},
"gamma": {
"prefix": "gamma_",
"dsn": "mysql:host=127.0.0.1;dbname=SHARDDBNAME",
"username": "USER",
"password": "PASSWORD",
"driver_options": {
"3": 2
}
},
"delta": {
"prefix": "delta_",
"dsn": "mysql:host=127.0.0.1;dbname=SHARDDBNAME",
"username": "USER",
"password": "PASSWORD",
"driver_options": {
"3": 2
}
}
},
"indexes": {
"Stream": {
"fields": {"publisherId": "md5", "name": "normalize"},
"partition": {
"0000000. ": "alpha",
"0000000.sample_": "betta",
"4000000. ": "gamma",
"4000000.sample_": "delta",
"8000000. ": "alpha",
"8000000.sample_": "betta",
"c000000. ": "gamma",
"c000000.sample_": "delta"
}
}
}
}Parameters:
-
[$upcoming=null]Array optionalTemporary config to use in sharding. Used during shard split process only
-
[$criteria=null]Array optionalOverrides the sharding criteria for the query. Rarely used unless testing what shards the query would be executed on.
Returns:
Returns an array of ($shardName => $query) pairs, where $shardName can be the name of a shard, '' for just the main shard, or "*" to have the query run on all the shards.
shard_internal
-
$index -
$hashed
does a depth first search and returns the array of shardname => $query pairs corresponding to which shards are affected
Parameters:
-
$indexArray -
$hashedString
Returns:
slice_partitions
-
$partition -
$j -
$hashed -
[$adjust=false]
Narrows the partition list according to hashes
Parameters:
-
$partitionArray -
$jIntegerCurrently processed hashed array member
-
$hashedArray -
[$adjust=false]Boolean optional
Returns:
where
-
$criteria
Adds a WHERE clause to a query
Parameters:
-
$criteriaDb_Expression | ArrayAn associative array of expression => value pairs. The values are automatically escaped using the database server, or turned into PDO placeholders for prepared statements They can also be arrays, in which case they are placed into an expression of the form key IN ('val1', 'val2') Or, this could be a Db_Expression object.
Returns:
The resulting object implementing Db_Query_Interface
Properties
$after
Array
Any additional text that comes after a clause
Default: array()
$cache
Array
Class cache
$caching
Boolean
Whether to cache or not
Default: false
$className
String
The name of the class to instantiate when fetching database rows.
$clauses
Array
Clauses that this query has (WHERE, ORDER BY, etc.)
Default: array()
$context
Array
The context of the query. Contains the following keys:
- 'callback' => the function or method to call back
- 'args' => the arguments to pass to that function or method
Default: null
$criteria
Array
Criteria used for sharding the query
Default: array()
$expression
String
The expression as a string
$ignoreCache
Boolean
Whether to use the cache or not
Default: false
$mapping
Array
private
Actual points mapping depending if partition is plain or associative array
$parameters
Array
The parameters passed to this query
Default: array()
$replacements
Array
Strings to replace in the query, if getSQL() or execute() is called
Default: array()
$statement
PDOStatement
If this query is prepared, this would point to the PDOStatement object
Default: null
$table
String
The tables operated with query
$type
Integer
The type of query this is (select, insert, etc.)
HASH_LEN
Integer
final
Default length of the hash used for sharding
Default: 7
TYPE_DELETE
Integer
final
Delete query
TYPE_INSERT
Integer
final
Insert query
TYPE_RAW
Integer
final
Raw query
TYPE_ROLLBACK
Integer
final
Rollback query
TYPE_SELECT
Integer
final
Select query
TYPE_UPDATE
Integer
final
Update query
Events
Db/query/exception
After
Event Payload:
-
queryDb_Query_Mysql -
queriesArray -
sqlString -
exceptionException