\RedBeanPHP\QueryWriterAQueryWriter

RedBeanPHP Abstract Query Writer.

Represents an abstract Database to RedBean To write a driver for a different database for RedBean Contains a number of functions all implementors can inherit or override.

Summary

Methods
Properties
Constants
setDDLTemplate()
getDDLTemplate()
useISNULLConditions()
useJSONColumns()
forbidNuke()
canBeTreatedAsInt()
getAssocTableFormat()
renameAssociation()
camelsSnake()
snakeCamel()
clearRenames()
setNarrowFieldMode()
setSQLFilters()
getSQLFilters()
tableExists()
glueSQLCondition()
glueLimitOne()
esc()
addColumn()
updateRecord()
parseJoin()
writeJoin()
setSQLSelectSnippet()
queryRecord()
queryRecordWithCursor()
queryRecordRelated()
queryRecordLink()
queryTagged()
queryCountTagged()
queryRecordCount()
queryRecordCountRelated()
queryRecursiveCommonTableExpression()
deleteRecord()
deleteRelations()
widenColumn()
wipe()
renameAssocTable()
getAssocTable()
setUseCache()
flushCache()
safeColumn()
safeTable()
addUniqueIndex()
$renames
$typeno_sqltype
C_SELECT_SNIPPET_FOR_UPDATE
C_DATA_TYPE_ONLY_IF_NOT_EXISTS
C_DATA_TYPE_MANUAL
isJSON()
getForeignKeyForTypeProperty()
getKeyMapForType()
makeFKLabel()
getSQLFilterSnippet()
getParametersForInClause()
addDataType()
getInsertSuffix()
startsWithZeros()
insertRecord()
check()
$flagUseJSONColumns
$enableISNULLConditions
$adapter
$defaultValue
$quoteCharacter
$flagUseCache
$cache
$maxCacheSizePerType
$sqlSelectSnippet
$noNuke
N/A
getCacheKey()
getCached()
updateCache()
putResultInCache()
makeSQLFromConditions()
getRelationalTablesAndColumns()
queryTaggedGeneric()
$sqlFilters
$flagSQLFilterSafeMode
$flagNarrowFieldMode
N/A

Constants

C_SELECT_SNIPPET_FOR_UPDATE

C_SELECT_SNIPPET_FOR_UPDATE

Constant: Select Snippet 'FOR UPDATE'

C_DATA_TYPE_ONLY_IF_NOT_EXISTS

C_DATA_TYPE_ONLY_IF_NOT_EXISTS

C_DATA_TYPE_MANUAL

C_DATA_TYPE_MANUAL

Properties

$renames

$renames : array

Type

array

$typeno_sqltype

$typeno_sqltype : array

Type

array

$flagUseJSONColumns

$flagUseJSONColumns : boolean

Type

boolean

$enableISNULLConditions

$enableISNULLConditions : boolean

Type

boolean

$defaultValue

$defaultValue : string

Type

string

$quoteCharacter

$quoteCharacter : string

Type

string

$flagUseCache

$flagUseCache : boolean

Type

boolean

$cache

$cache : array

Type

array

$maxCacheSizePerType

$maxCacheSizePerType : integer

Type

integer

$sqlSelectSnippet

$sqlSelectSnippet : string

Type

string

$noNuke

$noNuke : boolean

Type

boolean

$sqlFilters

$sqlFilters : array

Type

array

$flagSQLFilterSafeMode

$flagSQLFilterSafeMode : boolean

Type

boolean

$flagNarrowFieldMode

$flagNarrowFieldMode : boolean

Type

boolean

Methods

setDDLTemplate()

setDDLTemplate(string  $type, string  $beanType, string  $template) : void

Sets a data definition template to change the data creation statements per type.

For instance to add ROW_FORMAT=DYNAMIC to all MySQL tables upon creation:

$sql = $writer->getDDLTemplate( 'createTable', '' ); $writer->setDDLTemplate( 'createTable', '', $sql . ' ROW_FORMAT=DYNAMIC ' );

For property-specific templates set $beanType to: account.username -- then the template will only be applied to SQL statements relating to that column/property.

Parameters

string $type

( 'createTable' | 'widenColumn' | 'addColumn' )

string $beanType

( type of bean or '*' to apply to all types )

string $template

SQL template, contains %s for slots

getDDLTemplate()

getDDLTemplate(string  $type, string  $beanType = '*', string|NULL  $property = NULL) : string

Returns the specified data definition template.

If no template can be found for the specified type, the template for '*' will be returned instead.

Parameters

string $type

( 'createTable' | 'widenColumn' | 'addColumn' )

string $beanType

( type of bean or '*' to apply to all types )

string|NULL $property

specify if you're looking for a property-specific template

Returns

string

useISNULLConditions()

useISNULLConditions(boolean  $flag) : boolean

Toggles support for IS-NULL-conditions.

If IS-NULL-conditions are enabled condition arrays for functions including findLike() are treated so that 'field' => NULL will be interpreted as field IS NULL instead of being skipped. Returns the previous value of the flag.

Parameters

boolean $flag

TRUE or FALSE

Returns

boolean

useJSONColumns()

useJSONColumns(boolean  $flag) : boolean

Toggles support for automatic generation of JSON columns.

Using JSON columns means that strings containing JSON will cause the column to be created (not modified) as a JSON column. However it might also trigger exceptions if this means the DB attempts to convert a non-json column to a JSON column. Returns the previous value of the flag.

Parameters

boolean $flag

TRUE or FALSE

Returns

boolean

forbidNuke()

forbidNuke(boolean  $flag) : boolean

Toggles support for nuke().

Can be used to turn off the nuke() feature for security reasons. Returns the old flag value.

Parameters

boolean $flag

TRUE or FALSE

Returns

boolean

canBeTreatedAsInt()

canBeTreatedAsInt(string  $value) : boolean

Checks whether a number can be treated like an int.

Parameters

string $value

string representation of a certain value

Returns

boolean

getAssocTableFormat()

getAssocTableFormat(  $types) 

Parameters

$types

renameAssociation()

renameAssociation(  $from,   $to = NULL) 

Parameters

$from
$to

camelsSnake()

camelsSnake(string  $camel) : string

Globally available service method for RedBeanPHP.

Converts a camel cased string to a snake cased string.

Parameters

string $camel

camelCased string to convert to snake case

Returns

string

snakeCamel()

snakeCamel(string  $snake,   $dolphinMode = false) : string

Globally available service method for RedBeanPHP.

Converts a snake cased string to a camel cased string.

Parameters

string $snake

snake_cased string to convert to camelCase

$dolphinMode

Returns

string

clearRenames()

clearRenames() : void

Clears renames.

setNarrowFieldMode()

setNarrowFieldMode(boolean  $narrowField) : void

Toggles 'Narrow Field Mode'.

In Narrow Field mode the queryRecord method will narrow its selection field to

SELECT {table}.*

instead of

SELECT *

This is a better way of querying because it allows more flexibility (for instance joins). However if you need the wide selector for backward compatibility; use this method to turn OFF Narrow Field Mode by passing FALSE. Default is TRUE.

Parameters

boolean $narrowField

TRUE = Narrow Field FALSE = Wide Field

setSQLFilters()

setSQLFilters(  $sqlFilters,   $safeMode = FALSE) : void

Sets SQL filters.

This is a lowlevel method to set the SQL filter array. The format of this array is:

array( '<MODE, i.e. 'r' for read, 'w' for write>' => array( '

' => array( '' => '' ) ) )

Example:

array( QueryWriter::C_SQLFILTER_READ => array( 'book' => array( 'title' => ' LOWER(book.title) ' ) )

Note that you can use constants instead of magical chars as keys for the uppermost array. This is a lowlevel method. For a more friendly method please take a look at the facade: R::bindFunc().

Parameters

$sqlFilters
$safeMode

getSQLFilters()

getSQLFilters() : array

Returns current SQL Filters.

This method returns the raw SQL filter array. This is a lowlevel method. For a more friendly method please take a look at the facade: R::bindFunc().

Returns

array

tableExists()

tableExists(string  $table) : boolean

Checks whether the specified type (i.e. table) already exists in the database.

Not part of the Object Database interface!

Parameters

string $table

table name

Returns

boolean

glueSQLCondition()

glueSQLCondition(  $sql,   $glue = NULL) 

Parameters

$sql
$glue

glueLimitOne()

glueLimitOne(  $sql = '') 

Parameters

$sql

esc()

esc(  $dbStructure,   $dontQuote = FALSE) 

Parameters

$dbStructure
$dontQuote

addColumn()

addColumn(  $beanType,   $column,   $field) 

Parameters

$beanType
$column
$field

updateRecord()

updateRecord(  $type,   $updatevalues,   $id = NULL) 

Parameters

$type
$updatevalues
$id

parseJoin()

parseJoin(  $type,   $sql,   $cteType = NULL) 

Parameters

$type
$sql
$cteType

writeJoin()

writeJoin(  $type,   $targetType,   $leftRight = 'LEFT',   $joinType = 'parent',   $firstOfChain = TRUE,   $suffix = '',   $aliases = array(),   $cteType = NULL) 

Parameters

$type
$targetType
$leftRight
$joinType
$firstOfChain
$suffix
$aliases
$cteType

setSQLSelectSnippet()

setSQLSelectSnippet(  $sqlSelectSnippet = '') 

Sets an SQL snippet to be used for the next queryRecord() operation.

A select snippet will be inserted at the end of the SQL select statement and can be used to modify SQL-select commands to enable locking, for instance using the 'FOR UPDATE' snippet (this will generate an SQL query like: 'SELECT * FROM ... FOR UPDATE'. After the query has been executed the SQL snippet will be erased. Note that only the first upcoming direct or indirect invocation of queryRecord() through batch(), find() or load() will be affected. The SQL snippet will be cached.

Parameters

$sqlSelectSnippet

queryRecord()

queryRecord(  $type,   $conditions = array(),   $addSql = NULL,   $bindings = array()) 

Parameters

$type
$conditions
$addSql
$bindings

queryRecordWithCursor()

queryRecordWithCursor(  $type,   $addSql = NULL,   $bindings = array()) 

Parameters

$type
$addSql
$bindings

queryRecordRelated()

queryRecordRelated(  $sourceType,   $destType,   $linkIDs,   $addSql = '',   $bindings = array()) 

Parameters

$sourceType
$destType
$linkIDs
$addSql
$bindings

queryRecordLink()

queryRecordLink(  $sourceType,   $destType,   $sourceID,   $destID) 

Parameters

$sourceType
$destType
$sourceID
$destID

queryTagged()

queryTagged(  $type,   $tagList,   $all = FALSE,   $addSql = '',   $bindings = array()) 

Parameters

$type
$tagList
$all
$addSql
$bindings

queryCountTagged()

queryCountTagged(  $type,   $tagList,   $all = FALSE,   $addSql = '',   $bindings = array()) 

Parameters

$type
$tagList
$all
$addSql
$bindings

queryRecordCount()

queryRecordCount(  $type,   $conditions = array(),   $addSql = NULL,   $bindings = array()) 

Parameters

$type
$conditions
$addSql
$bindings

queryRecordCountRelated()

queryRecordCountRelated(  $sourceType,   $destType,   $linkID,   $addSql = '',   $bindings = array()) 

Parameters

$sourceType
$destType
$linkID
$addSql
$bindings

queryRecursiveCommonTableExpression()

queryRecursiveCommonTableExpression(  $type,   $id,   $up = TRUE,   $addSql = NULL,   $bindings = array(),   $selectForm = FALSE) 

Parameters

$type
$id
$up
$addSql
$bindings
$selectForm

deleteRecord()

deleteRecord(  $type,   $conditions = array(),   $addSql = NULL,   $bindings = array()) 

Parameters

$type
$conditions
$addSql
$bindings

deleteRelations()

deleteRelations(  $sourceType,   $destType,   $sourceID) 

Parameters

$sourceType
$destType
$sourceID

widenColumn()

widenColumn(  $type,   $property,   $dataType) 

Parameters

$type
$property
$dataType

wipe()

wipe(  $type) 

Parameters

$type

renameAssocTable()

renameAssocTable(  $from,   $to = NULL) 

Parameters

$from
$to

getAssocTable()

getAssocTable(  $types) 

Parameters

$types

setUseCache()

setUseCache(  $yesNo) : void

Turns caching on or off. Default: off.

If caching is turned on retrieval queries fired after eachother will use a result row cache.

Parameters

$yesNo

flushCache()

flushCache(  $newMaxCacheSizePerType = NULL,   $countCache = TRUE) : mixed

Flushes the Query Writer Cache.

Clears the internal query cache array and returns its overall size.

Parameters

$newMaxCacheSizePerType
$countCache

Returns

mixed

safeColumn()

safeColumn(string  $column, boolean  $noQuotes = FALSE) : string

Parameters

string $column

column to be escaped

boolean $noQuotes

omit quotes

Returns

string

safeTable()

safeTable(string  $table, boolean  $noQuotes = FALSE) : string

Parameters

string $table

table to be escaped

boolean $noQuotes

omit quotes

Returns

string

addUniqueIndex()

addUniqueIndex(  $type,   $properties) 

Parameters

$type
$properties

isJSON()

isJSON(string  $value) : boolean

Determines whether a string can be considered JSON or not.

This is used by writers that support JSON columns. However we don't want that code duplicated over all JSON supporting Query Writers.

Parameters

string $value

value to determine 'JSONness' of.

Returns

boolean

getForeignKeyForTypeProperty()

getForeignKeyForTypeProperty(string  $type, string  $property) : array|NULL

Given a type and a property name this method returns the foreign key map section associated with this pair.

Parameters

string $type

name of the type

string $property

name of the property

Returns

array|NULL

getKeyMapForType()

getKeyMapForType(string  $type) : array

Returns the foreign key map (FKM) for a type.

A foreign key map describes the foreign keys in a table. A FKM always has the same structure:

array( 'name' => 'from' => 'table' => 'to' => (most of the time 'id') 'on_update' => <update rule: 'SET NULL','CASCADE' or 'RESTRICT'> 'on_delete' => <delete rule: 'SET NULL','CASCADE' or 'RESTRICT'> )

Parameters

string $type

the bean type you wish to obtain a key map of

Returns

array

makeFKLabel()

makeFKLabel(string  $from, string  $type, string  $to) : string

This method makes a key for a foreign key description array.

This key is a readable string unique for every source table. This uniform key is called the FKDL Foreign Key Description Label. Note that the source table is not part of the FKDL because this key is supposed to be 'per source table'. If you wish to include a source table, prefix the key with 'ontable_'.

Parameters

string $from

the column of the key in the source table

string $type

the type (table) where the key points to

string $to

the target column of the foreign key (mostly just 'id')

Returns

string

getSQLFilterSnippet()

getSQLFilterSnippet(string  $type) : string

Returns an SQL Filter snippet for reading.

Parameters

string $type

type of bean

Returns

string

getParametersForInClause()

getParametersForInClause(  $valueList, array  $otherBindings, integer  $offset) : string

Generates a list of parameters (slots) for an SQL snippet.

This method calculates the correct number of slots to insert in the SQL snippet and determines the correct type of slot. If the bindings array contains named parameters this method will return named ones and update the keys in the value list accordingly (that's why we use the &).

If you pass an offset the bindings will be re-added to the value list. Some databases cant handle duplicate parameter names in queries.

Parameters

$valueList
array $otherBindings

list of additional bindings

integer $offset

start counter at...

Returns

string

addDataType()

addDataType(integer  $dataTypeID, string  $SQLDefinition) : self

Adds a data type to the list of data types.

Use this method to add a new column type definition to the writer. Used for UUID support.

Parameters

integer $dataTypeID

magic number constant assigned to this data type

string $SQLDefinition

SQL column definition (e.g. INT(11))

Returns

self

getInsertSuffix()

getInsertSuffix(string  $table) : string

Returns the sql that should follow an insert statement.

Parameters

string $table

name

Returns

string

startsWithZeros()

startsWithZeros(string  $value) : boolean

Checks whether a value starts with zeros. In this case the value should probably be stored using a text datatype instead of a numerical type in order to preserve the zeros.

Parameters

string $value

value to be checked.

Returns

boolean

insertRecord()

insertRecord(  $type, array  $insertcolumns, array  $insertvalues) : integer

Inserts a record into the database using a series of insert columns and corresponding insertvalues. Returns the insert id.

Parameters

$type
array $insertcolumns

columns to be inserted

array $insertvalues

values to be inserted

Returns

integer

check()

check(  $struct) : string

Checks table name or column name.

Parameters

$struct

Returns

string

getCacheKey()

getCacheKey(array  $keyValues) : string

Returns a cache key for the cache values passed.

This method returns a fingerprint string to be used as a key to store data in the writer cache.

Parameters

array $keyValues

key-value to generate key for

Returns

string

getCached()

getCached(string  $cacheTag, string  $key) : mixed

Returns the values associated with the provided cache tag and key.

Parameters

string $cacheTag

cache tag to use for lookup

string $key

key to use for lookup

Returns

mixed

updateCache()

updateCache() : boolean

Checks if the previous query had a keep-cache tag.

If so, the cache will persist, otherwise the cache will be flushed.

Returns TRUE if the cache will remain and FALSE if a flush has been performed.

Returns

boolean

putResultInCache()

putResultInCache(string  $cacheTag, string  $key, array|integer  $values) : void

Stores data from the writer in the cache under a specific key and cache tag.

A cache tag is used to make sure the cache remains consistent. In most cases the cache tag will be the bean type, this makes sure queries associated with a certain reference type will never contain conflicting data. Why not use the cache tag as a key? Well we need to make sure the cache contents fits the key (and key is based on the cache values). Otherwise it would be possible to store two different result sets under the same key (the cache tag).

In previous versions you could only store one key-entry, I have changed this to improve caching efficiency (issue #400).

Parameters

string $cacheTag

cache tag (secondary key)

string $key

key to store values under

array|integer $values

rows or count to be stored

makeSQLFromConditions()

makeSQLFromConditions(array  $conditions, array  $bindings, string  $addSql = '') : string

Creates an SQL snippet from a list of conditions of format:

array( key => array( value1, value2, value3 .... ) )

Parameters

array $conditions

list of conditions

array $bindings

parameter bindings for SQL snippet

string $addSql

additional SQL snippet to append to result

Returns

string

getRelationalTablesAndColumns()

getRelationalTablesAndColumns(string  $sourceType, string  $destType, boolean  $noQuote = FALSE) : array

Returns the table names and column names for a relational query.

Parameters

string $sourceType

type of the source bean

string $destType

type of the bean you want to obtain using the relation

boolean $noQuote

TRUE if you want to omit quotes

Returns

array

queryTaggedGeneric()

queryTaggedGeneric(string  $type, array  $tagList, boolean  $all = FALSE, string  $addSql = '', array  $bindings = array(), string  $wrap = '%s') : array

Returns or counts all rows of specified type that have been tagged with one of the strings in the specified tag list array.

Note that the additional SQL snippet can only be used for pagination, the SQL snippet will be appended to the end of the query.

Parameters

string $type

the bean type you want to query

array $tagList

an array of strings, each string containing a tag title

boolean $all

if TRUE only return records that have been associated with ALL the tags in the list

string $addSql

addition SQL snippet, for pagination

array $bindings

parameter bindings for additional SQL snippet

string $wrap

SQL wrapper string (use %s for subquery)

Returns

array