\RedBeanPHP\QueryWriterSQLiteT

RedBeanPHP SQLiteWriter with support for SQLite types This is a QueryWriter class for RedBeanPHP.

This QueryWriter provides support for the SQLite database platform.

Summary

Methods
Properties
Constants
useJSONColumns()
forbidNuke()
canBeTreatedAsInt()
getAssocTableFormat()
renameAssociation()
camelsSnake()
clearRenames()
setNarrowFieldMode()
setSQLFilters()
getSQLFilters()
tableExists()
glueSQLCondition()
glueLimitOne()
esc()
addColumn()
updateRecord()
writeJoin()
setSQLSelectSnippet()
queryRecord()
queryRecordWithCursor()
queryRecordRelated()
queryRecordLink()
queryTagged()
queryCountTagged()
queryRecordCount()
queryRecordCountRelated()
queryRecursiveCommonTableExpression()
deleteRecord()
deleteRelations()
widenColumn()
wipe()
renameAssocTable()
getAssocTable()
setUseCache()
flushCache()
safeColumn()
safeTable()
inferFetchType()
addUniqueIndex()
__construct()
getTypeForID()
scanType()
code()
getTables()
createTable()
getColumns()
addUniqueConstraint()
sqlStateIn()
addIndex()
addFK()
wipeAll()
$renames
$typeno_sqltype
C_SELECT_SNIPPET_FOR_UPDATE
C_DATA_TYPE_ONLY_IF_NOT_EXISTS
C_DATA_TYPE_MANUAL
C_DATATYPE_INTEGER
C_DATATYPE_NUMERIC
C_DATATYPE_TEXT
C_DATATYPE_SPECIFIED
isJSON()
getForeignKeyForTypeProperty()
getKeyMapForType()
makeFKLabel()
getSQLFilterSnippet()
getParametersForInClause()
addDataType()
getInsertSuffix()
startsWithZeros()
insertRecord()
check()
getTable()
putTable()
getIndexes()
buildFK()
$flagUseJSONColumns
$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

C_DATATYPE_INTEGER

C_DATATYPE_INTEGER

Data types

C_DATATYPE_NUMERIC

C_DATATYPE_NUMERIC

C_DATATYPE_TEXT

C_DATATYPE_TEXT

C_DATATYPE_SPECIFIED

C_DATATYPE_SPECIFIED

Properties

$renames

$renames : array

Type

array

$typeno_sqltype

$typeno_sqltype : array

Type

array

$flagUseJSONColumns

$flagUseJSONColumns : 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

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 converty to snake case

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(  $table,   $column,   $type) 

Parameters

$table
$column
$type

updateRecord()

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

Parameters

$type
$updatevalues
$id

writeJoin()

writeJoin(  $type,   $targetType,   $leftRight = 'LEFT') 

Parameters

$type
$targetType
$leftRight

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()) 

Parameters

$type
$id
$up
$addSql
$bindings

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,   $column,   $datatype) 

Parameters

$type
$column
$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) : integer

Flushes the Query Writer Cache.

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

Parameters

$newMaxCacheSizePerType

Returns

integer

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

inferFetchType()

inferFetchType(  $type,   $property) 

Parameters

$type
$property

addUniqueIndex()

addUniqueIndex(  $type,   $properties) 

Parameters

$type
$properties

__construct()

__construct(\RedBeanPHP\Adapter  $adapter) 

Constructor Most of the time, you do not need to use this constructor, since the facade takes care of constructing and wiring the RedBeanPHP core objects. However if you would like to assemble an OODB instance yourself, this is how it works:

Usage:

$database = new RPDO( $dsn, $user, $pass ); $adapter = new DBAdapter( $database ); $writer = new PostgresWriter( $adapter ); $oodb = new OODB( $writer, FALSE ); $bean = $oodb->dispense( 'bean' ); $bean->name = 'coffeeBean'; $id = $oodb->store( $bean ); $bean = $oodb->load( 'bean', $id );

The example above creates the 3 RedBeanPHP core objects: the Adapter, the Query Writer and the OODB instance and wires them together. The example also demonstrates some of the methods that can be used with OODB, as you see, they closely resemble their facade counterparts.

The wiring process: create an RPDO instance using your database connection parameters. Create a database adapter from the RPDO object and pass that to the constructor of the writer. Next, create an OODB instance from the writer. Now you have an OODB object.

Parameters

\RedBeanPHP\Adapter $adapter

Database Adapter

getTypeForID()

getTypeForID() : integer

This method returns the datatype to be used for primary key IDS and foreign keys. Returns one if the data type constants.

Returns

integer —

$const data type to be used for IDS.

scanType()

scanType(string  $value,   $flagSpecial = FALSE) : integer

Returns the Column Type Code (integer) that corresponds to the given value type. This method is used to determine the minimum column type required to represent the given value. There are two modes of operation: with or without special types. Scanning without special types requires the second parameter to be set to FALSE. This is useful when the column has already been created and prevents it from being modified to an incompatible type leading to data loss. Special types will be taken into account when a column does not exist yet (parameter is then set to TRUE).

Special column types are determines by the AQueryWriter constant C_DATA_TYPE_ONLY_IF_NOT_EXISTS (usually 80). Another 'very special' type is type C_DATA_TYPE_MANUAL (usually 99) which represents a user specified type. Although no special treatment has been associated with the latter for now.

Parameters

string $value

value

$flagSpecial

Returns

integer

code()

code(string  $typedescription, boolean  $includeSpecials = FALSE) : integer

Returns the Type Code for a Column Description.

Given an SQL column description this method will return the corresponding code for the writer. If the include specials flag is set it will also return codes for special columns. Otherwise special columns will be identified as specified columns.

Parameters

string $typedescription

description

boolean $includeSpecials

whether you want to get codes for special columns as well

Returns

integer

getTables()

getTables() : array

Returns the tables that are in the database.

Returns

array

createTable()

createTable(  $table) : void

This method will create a table for the bean.

This methods accepts a type and infers the corresponding table name.

Parameters

$table

getColumns()

getColumns(  $table) : array

Returns an array containing all the columns of the specified type.

The format of the return array looks like this: $field => $type where $field is the name of the column and $type is a database specific description of the datatype.

This methods accepts a type and infers the corresponding table name.

Parameters

$table

Returns

array

addUniqueConstraint()

addUniqueConstraint(string  $type,   $properties) : void

This method will add a UNIQUE constraint index to a table on columns $columns.

This methods accepts a type and infers the corresponding table name.

Parameters

string $type

target bean type

$properties

sqlStateIn()

sqlStateIn(string  $state, array  $list, array  $extraDriverDetails = array()) : boolean

This method will check whether the SQL state is in the list of specified states and returns TRUE if it does appear in this list or FALSE if it does not. The purpose of this method is to translate the database specific state to a one of the constants defined in this class and then check whether it is in the list of standard states provided.

Parameters

string $state

SQL state to consider

array $list

list of standardized SQL state constants to check against

array $extraDriverDetails

Some databases communicate state information in a driver-specific format rather than through the main sqlState code. For those databases, this extra information can be used to determine the standardized state

Returns

boolean

addIndex()

addIndex(string  $type, string  $name,   $column) : void

This method will add an index to a type and field with name $name.

This methods accepts a type and infers the corresponding table name.

Parameters

string $type

type to add index to

string $name

name of the new index

$column

addFK()

addFK(string  $type, string  $targetType, string  $property, string  $targetProperty, string  $isDep = FALSE) : void

This method will add a foreign key from type and field to target type and target field.

The foreign key is created without an action. On delete/update no action will be triggered. The FK is only used to allow database tools to generate pretty diagrams and to make it easy to add actions later on. This methods accepts a type and infers the corresponding table name.

Parameters

string $type

type that will have a foreign key field

string $targetType

points to this type

string $property

field that contains the foreign key value

string $targetProperty

field where the fk points to

string $isDep

whether target is dependent and should cascade on update/delete

wipeAll()

wipeAll() : void

Removes all tables and views from the database.

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 dont 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 (i.e. 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

getTable()

getTable(string  $type) : array

Gets all information about a table (from a type).

Format: array( name => name of the table columns => array( name => datatype ) indexes => array() raw index information rows from PRAGMA query keys => array() raw key information rows from PRAGMA query )

Parameters

string $type

type you want to get info of

Returns

array

putTable()

putTable(array  $tableMap) : void

Puts a table. Updates the table structure.

In SQLite we can't change columns, drop columns, change or add foreign keys so we have a table-rebuild function. You simply load your table with getTable(), modify it and then store it with putTable()...

Parameters

array $tableMap

information array

getIndexes()

getIndexes(string  $type) : array

Returns the an array describing the indexes for type $type.

Parameters

string $type

type to describe indexes of

Returns

array

buildFK()

buildFK(string  $type, string  $targetType,   $property,   $targetProperty,   $constraint = FALSE) : boolean

Adds a foreign key to a type.

Note: cant put this in try-catch because that can hide the fact that database has been damaged.

Parameters

string $type

type you want to modify table of

string $targetType

target type

$property
$targetProperty
$constraint

Returns

boolean

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  $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 $values

content 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