Documentation

PdbPgsql extends Pdb
in package

Class for doing database queries via PDO (PDO Database => Pdb)

Table of Contents

Constants

FORMAT_DATE  : mixed = 'Y-m-d'
Format for DATE types.
FORMAT_DATE_TIME  : mixed = 'Y-m-d H:i:s'
Format for DATETIME types.
FORMAT_DATE_TIME_MS  : mixed = 'Y-m-d H:i:s.v'
Format for DATETIME(3) types (with milliseconds).
FORMAT_DATE_TIME_US  : mixed = 'Y-m-d H:i:s.u'
Format for DATETIME(6) types (with microseconds).
QUOTE_FIELD  : mixed = 'field'
QUOTE_VALUE  : mixed = 'value'
RETURN_ARR  : mixed = 'arr'
An array of rows, where each row is an associative array.
RETURN_ARR_NUM  : mixed = 'arr-num'
An array of rows, where each row is a numeric array.
RETURN_COL  : mixed = 'col'
All values from the first column, as a numeric array.
RETURN_COUNT  : mixed = 'count'
The number of rows returned, or if the query is a `SELECT COUNT()` this will behave like 'val'.
RETURN_MAP  : mixed = 'map'
An array of `[identifier => value]` pairs, where the identifier is the first column in the result set, and the value is the second.
RETURN_MAP_ARR  : mixed = 'map-arr'
An array of `[identifier => value]` pairs, where the identifier is the first column in the result set, and the value an associative array of `[name => value]` pairs.
RETURN_NULL  : mixed = 'null'
Return nothing.
RETURN_NULLABLE  : mixed = [self::RETURN_TRY_VAL, self::RETURN_TRY_ROW, se...
A subset of return types that are nullable.
RETURN_PDO  : mixed = 'pdo'
Return a PDOStatement object.
RETURN_ROW  : mixed = 'row'
A single row, as an associative array.
RETURN_ROW_NUM  : mixed = 'row-num'
A single row, as a numeric array
RETURN_TRY_ROW  : mixed = 'row?'
A single associative row, or 'null' if the result is empty.
RETURN_TRY_ROW_NUM  : mixed = 'row-num?'
A single numeric row, or 'null' if the result is empty.
RETURN_TRY_VAL  : mixed = 'val?'
A single value, or 'null' if the result is empty.
RETURN_TYPES  : mixed = [self::RETURN_PDO, self::RETURN_NULL, self::RET...
All valid return types, without arguments.
RETURN_VAL  : mixed = 'val'
A single value (i.e. the value of the first column of the first row).
UUID_NAMESPACE  : mixed = '3afdd7bd-b030-4c46-a3b6-f4d600670865'
The default namespace for UUIDv5 generation.

Properties

$config  : PdbConfig
$_formatters  : array<string|int, PdbDataFormatterInterface>
$_timezone  : DateTimeZone|null
$cache  : PdbCache
$connection  : PDO|null
$debugger  : callable|null
$inflector  : InflectorInterface|null
$last_insert_id  : int|null
$profiler  : callable|null
$transaction_key  : string|false
$loggers  : array<string|int, callable>

Methods

__construct()  : mixed
__serialize()  : array<string|int, mixed>
addLogger()  : int
Register a logger.
attach()  : void
Forward any logs from this loggable to a parent loggable.
buildClause()  : string
Builds a clause string by combining conditions, e.g. for a WHERE or ON clause.
clearLoggers()  : void
Remove all loggers.
commit()  : void
Commits a transaction or a savepoint.
connect()  : PDO
Loads db config and creates a new PDO connection with it.
create()  : Pdb
createLock()  : bool
delete()  : int
Runs a DELETE query
deleteLock()  : bool
execute()  : mixed
Executes a prepared statement
extractEnumArr()  : array<string|int, string>
Returns definition list from column of type ENUM
fieldList()  : array<string|int, PdbColumn>
find()  : PdbQuery
Shorthand for creating a new PdbQuery.
format()  : mixed
Format a object in accordance to the registered formatters.
formatRs()  : string|int|null|array<string|int, mixed>
Converts a PDO result set to a common data format:
generateUid()  : string
Create a UUIDv5 for this table + id.
get()  : ($throw is true ? array : array|null)
Return all columns for a single row of a table.
getConnection()  : PDO
Gets a PDO connection, creating a new one if necessary
getDependentKeys()  : array<string|int, PdbForeignKey>
Gets all of the dependent foreign key columns (i.e. with the CASCADE delete rule) in other tables which link to the id column of a specific table
getForeignKeys()  : array<string|int, PdbForeignKey>
Gets all of the columns which have foreign key constraints in a table
getInflector()  : InflectorInterface
An inflector for flecting.
getLastInsertId()  : int|null
Return the value from the autoincement of the most recent INSERT query
getPermissions()  : array<string|int, string>
Get the permissions of the current connection.
getPrefix()  : string
Get the prefix for table, or global
getSchema()  : PdbSchema
getServerVersion()  : string
Get the server version string.
getTable()  : PdbTable|null
getTableAttributes()  : array<string|int, mixed>
Get adapter specific table information.
getTableNames()  : array<string|int, string>
Fetches the current list of tables.
getTimezone()  : DateTimeZone
Get the session timezone.
indexList()  : array<string|int, PdbIndex>
insert()  : int
Runs an INSERT query
insertPrefixes()  : string
Replaces tilde placeholders with table prefixes, and quotes tables according to the rules of the underlying DBMS
inTransaction()  : bool
Checks if there's a current transaction in progress
listTables()  : array<string|int, string>
Fetches the current list of tables.
log()  : void
Log something.
lookup()  : array<string|int, mixed>
Fetches a mapping of id => value values from a table, using the 'name' values by default
now()  : string
Gets a datetime value for the current time, relative to the session timezone.
prepare()  : PDOStatement
Prepare a query into a prepared statement
prettyQuery()  : string
Return a query with the values substituted into their respective binding positions.
q()  : mixed
Alias for {@see Pdb::query}
query()  : mixed
Executes a PDO query
quote()  : string
quoteAll()  : array<string|int, string>
quoteField()  : string
quoteIdentifiers()  : string
Wrap extended field identifiers in quotes.
quoteValue()  : string
recordExists()  : bool
Check to see that at least one record exists for certain conditions.
removeFormatter()  : mixed
Remove the formatter function for a given class.
removeLogger()  : void
Remove a log callback.
rollback()  : void
Rolls a transaction or savepoint back.
savepoint()  : string
Create a savepoint.
setDebugger()  : void
Attach/remove a debug logger.
setFormatter()  : mixed
Set the formatter function for a given class.
setProfiler()  : void
Attach/remove a profiler logger.
setTablePrefixOverride()  : mixed
Sets an overriding prefix to prepend to an individual table.
tableExists()  : bool
tableList()  : array<string|int, PdbTable>
transact()  : PdbTransaction
Starts a transaction, or savepoint if nested transactions are enabled.
update()  : int
Runs an UPDATE query
upsert()  : int
validateAlias()  : void
Validate an alias.
validateDirection()  : void
Validates an order-by direction.
validateEnum()  : bool
Validates a value meant for an ENUM field, e.g.
validateFunction()  : void
Validate an SQL function.
validateIdentifier()  : void
Validates a identifier (column name, table name, etc)
validateIdentifierExtended()  : void
Validates a identifier in extended format -- table.column Also accepts short format like {@see validateIdentifier} does.
validateReturnType()  : void
Validate the return type.
withTransaction()  : T
Wrap a function in a transaction. This will commit or rollback automatically after the function is complete.
afterConnect()  : void
Called after a new PDO connection is created.
bindParams()  : void
Bind the array of parameters to a PDO statement
buildInsertSet()  : array<string|int, mixed>
Build the VALUES clause for an INSERT query.
buildUpdateSet()  : array<string|int, mixed>
Build the SET clause for an UPDATE query.
getBindingQuery()  : string
Build a binding query for a SET clause.
getCacheKey()  : string|null
Produce a key appropriate to represent a query and it's parameters.
getCacheTtl()  : int
Determine the cache TTL from the return type or the global config.
getFormatter()  : PdbDataFormatterInterface|null
Fetch an appropriate formatter for this object.
logQuery()  : void
Log something.
stripPrefix()  : string
Remove prefixes from a table name.

Constants

FORMAT_DATE

Format for DATE types.

public mixed FORMAT_DATE = 'Y-m-d'

FORMAT_DATE_TIME

Format for DATETIME types.

public mixed FORMAT_DATE_TIME = 'Y-m-d H:i:s'

FORMAT_DATE_TIME_MS

Format for DATETIME(3) types (with milliseconds).

public mixed FORMAT_DATE_TIME_MS = 'Y-m-d H:i:s.v'

FORMAT_DATE_TIME_US

Format for DATETIME(6) types (with microseconds).

public mixed FORMAT_DATE_TIME_US = 'Y-m-d H:i:s.u'

QUOTE_FIELD

public mixed QUOTE_FIELD = 'field'
Tags
see
quote()
quoteAll()

QUOTE_VALUE

public mixed QUOTE_VALUE = 'value'
Tags
see
quote()
quoteAll()

RETURN_ARR

An array of rows, where each row is an associative array.

public mixed RETURN_ARR = 'arr'

RETURN_ARR_NUM

An array of rows, where each row is a numeric array.

public mixed RETURN_ARR_NUM = 'arr-num'

RETURN_COL

All values from the first column, as a numeric array.

public mixed RETURN_COL = 'col'

DO NOT USE with boolean columns; see note at http://php.net/manual/en/pdostatement.fetchcolumn.php

RETURN_COUNT

The number of rows returned, or if the query is a `SELECT COUNT()` this will behave like 'val'.

public mixed RETURN_COUNT = 'count'

RETURN_MAP

An array of `[identifier => value]` pairs, where the identifier is the first column in the result set, and the value is the second.

public mixed RETURN_MAP = 'map'

RETURN_MAP_ARR

An array of `[identifier => value]` pairs, where the identifier is the first column in the result set, and the value an associative array of `[name => value]` pairs.

public mixed RETURN_MAP_ARR = 'map-arr'

Optionally, one can provide an 'column' argument with the type string in the form: 'map-arr:column'.

RETURN_NULL

Return nothing.

public mixed RETURN_NULL = 'null'

RETURN_NULLABLE

A subset of return types that are nullable.

public mixed RETURN_NULLABLE = [self::RETURN_TRY_VAL, self::RETURN_TRY_ROW, self::RETURN_TRY_ROW_NUM]

RETURN_PDO

Return a PDOStatement object.

public mixed RETURN_PDO = 'pdo'

RETURN_ROW

A single row, as an associative array.

public mixed RETURN_ROW = 'row'

Given an argument 'row:null' or the shorthand 'row?' this will return 'null' if the row is empty. Otherwise this will throw a RowMissingException.

RETURN_ROW_NUM

A single row, as a numeric array

public mixed RETURN_ROW_NUM = 'row-num'

Given an argument 'row-num:null' or the shorthand 'row-num?' this will return 'null' if the row is empty. Otherwise this will throw a RowMissingException.

RETURN_TRY_ROW

A single associative row, or 'null' if the result is empty.

public mixed RETURN_TRY_ROW = 'row?'

RETURN_TRY_ROW_NUM

A single numeric row, or 'null' if the result is empty.

public mixed RETURN_TRY_ROW_NUM = 'row-num?'

RETURN_TRY_VAL

A single value, or 'null' if the result is empty.

public mixed RETURN_TRY_VAL = 'val?'

RETURN_TYPES

All valid return types, without arguments.

public mixed RETURN_TYPES = [self::RETURN_PDO, self::RETURN_NULL, self::RETURN_COUNT, self::RETURN_ARR, self::RETURN_ARR_NUM, self::RETURN_ROW, self::RETURN_ROW_NUM, self::RETURN_MAP, self::RETURN_MAP_ARR, self::RETURN_VAL, self::RETURN_COL, self::RETURN_TRY_VAL, self::RETURN_TRY_ROW, self::RETURN_TRY_ROW_NUM]

RETURN_VAL

A single value (i.e. the value of the first column of the first row).

public mixed RETURN_VAL = 'val'

Given an argument 'val:null' or the shorthand 'val?' this will return 'null' if the result is empty. Otherwise this will throw a RowMissingException.

UUID_NAMESPACE

The default namespace for UUIDv5 generation.

public mixed UUID_NAMESPACE = '3afdd7bd-b030-4c46-a3b6-f4d600670865'
Tags
see
generateUid()

Properties

$_timezone

protected DateTimeZone|null $_timezone = null

$connection

protected PDO|null $connection = null

$debugger

protected callable|null $debugger = null

(query, params, result|exception)

$last_insert_id

protected int|null $last_insert_id = null

$profiler

protected callable|null $profiler = null

(position, token)

$transaction_key

protected string|false $transaction_key = false

$loggers

private array<string|int, callable> $loggers = []

Methods

__construct()

public __construct(PdbConfig|array<string|int, mixed> $config) : mixed
Parameters
$config : PdbConfig|array<string|int, mixed>

__serialize()

public __serialize() : array<string|int, mixed>
Tags
inheritdoc
Return values
array<string|int, mixed>

addLogger()

Register a logger.

public addLogger(callable|LogSinkInterface $logger[, int|null $level = null ][, string|array<string|int, mixed>|null $category = null ]) : int

Logs can be filtered by level and category.

// Only permit log levels higher than this:
// (excludes INFO, DEBUG, etc)
$loggable->addLogger($logger, Log::LEVEL_WARNING);

// Only permit this category:
$loggable->addLogger($logger, null, 'progress');

// Only permit these categories:
$loggable->addLogger($logger, null, ['log.request', 'log.response']);

// Exclude these categories:
$loggable->addLogger($logger, null, ['stats' => false, 'meta' => false]);
Parameters
$logger : callable|LogSinkInterface

(message, level, category, timestamp)

$level : int|null = null

filter by level

$category : string|array<string|int, mixed>|null = null

filter by category

Return values
int

attach()

Forward any logs from this loggable to a parent loggable.

public attach(callable|LogSinkInterface $logger[, int|null $level = null ][, string|array<string|int, mixed>|null $category = null ]) : void

Use addLogger() instead

Parameters
$logger : callable|LogSinkInterface
$level : int|null = null

filter by level

$category : string|array<string|int, mixed>|null = null

filter by category

buildClause()

Builds a clause string by combining conditions, e.g. for a WHERE or ON clause.

public buildClause(array<string|int, mixed> $conditions, array<string|int, mixed> &$values[, string $combine = 'AND' ]) : string

The resultant clause will contain ? markers for safe use in a prepared SQL statement. The statement and the generated $values can then be run via Pdb::query.

Each condition (see $conditions) is one of:

  • The scalar value 1 or 0 (to match either all or no records)
  • A column => value pair
  • An array with three elements: [column, operator, value(s)].

Conditions are usually combined using AND, but can also be OR or XOR; see the $combine parameter.

Parameters
$conditions : array<string|int, mixed>

Conditions for the clause. Each condition is either:

  • The scalar value 1 (to match ALL records -- BEWARE if using the clause in an UPDATE or DELETE)
  • The scalar value 0 (to match no records)
  • A column => value pair for an '=' condition. For example: 'id' => 3
  • An array with three elements: [column, operator, value(s)] For example: ['id', '=', 3] ['date_added', 'BETWEEN', ['2010', '2015']] ['status', 'IN', ['ACTIVE', 'APPROVE']] Simple operators: = <= >= < > != <> Operators for LIKE conditions; escaping of characters like % is handled automatically: CONTAINS string BEGINS string ENDS string Other operators: IS string 'NULL' or 'NOT NULL' BETWEEN array of 2 values (NOT) IN array of values IN SET string -- note the order matches other operators; ['column', 'IN SET', 'val1,ca']
$values : array<string|int, mixed>

Array of bind parameters. Additional parameters will be appended to this array

$combine : string = 'AND'

String to be placed between the conditions. Must be one of: 'AND', 'OR', 'XOR'

Tags
throws
InvalidConditionException
InvalidArgumentException
example

$conditions = ['active' => 1, ['date_added', 'BETWEEN', ['2015-01-01', '2016-01-01']]]; $params = []; $where = $pdb->buildClause($conditions, $params); // // Variable contents: // $where == "active = ? AND date_added BETWEEN ? AND ?" // $params == [1, '2015-01-01', '2016-01-01']; // $q = "SELECT * FROM ~my_table WHERE {$where}"; $res = Pdb::query($q, $params, 'pdo'); foreach ($res as $row) { // Record processing here } $res->closeCursor();

Return values
string —

A clause which is safe to use in a prepared SQL statement

clearLoggers()

Remove all loggers.

public clearLoggers() : void

Note, this does not remove the debug logger.

commit()

Commits a transaction or a savepoint.

public commit([PdbTransaction|string|null $name = null ]) : void

Given a savepoint key (nested transaction) the savepoint is released and the wrapping transaction remains uncommitted. If rollback() is called, the savepoint data is still lost.

Committing a root transaction will release all savepoints.

The transaction key is required when TX_FORCE_COMMIT_KEYS is enabled. This forces the user to maintain the transaction key through their control flow and encourages one to properly clean up after themselves.

Parameters
$name : PdbTransaction|string|null = null

a transaction key

Tags
throws
InvalidArgumentException

An invalid transaction key

ConnectionException

If the connection fails

TransactionNameException

The transaction/savepoint doesn't exist

TransactionEmptyException

If there's no active transaction

connect()

Loads db config and creates a new PDO connection with it.

public static connect(PdbConfig|array<string|int, mixed> $config[, array<string|int, mixed> $options = [] ]) : PDO
Parameters
$config : PdbConfig|array<string|int, mixed>
$options : array<string|int, mixed> = []

PDO attributes

Tags
throws
ConnectionException
Return values
PDO

create()

public static create(PdbConfig|array<string|int, mixed> $config) : Pdb
Parameters
$config : PdbConfig|array<string|int, mixed>
Return values
Pdb

createLock()

public createLock(string $name[, float $timeout = 0 ]) : bool
Parameters
$name : string
$timeout : float = 0

in seconds

Tags
inheritdoc
Return values
bool

delete()

Runs a DELETE query

public delete(string $table, array<string|int, mixed> $conditions) : int
Parameters
$table : string

The table (without prefix) to insert the data into

$conditions : array<string|int, mixed>

Conditions for updates. Pdb::buildClause

Tags
throws
InvalidArgumentException
QueryException
ConnectionException
Return values
int —

The number of affected rows

deleteLock()

public deleteLock(string $name) : bool
Parameters
$name : string
Tags
inheritdoc
Return values
bool

execute()

Executes a prepared statement

public execute(PDOStatement $st, array<string|int, mixed> $params, string|array<string|int, mixed>|PdbReturnInterface $config) : mixed

For the return type 'pdo', a PDOStatement is returned. You need to close it using $res->closeCursor() once you're finished. Additional return types are available; Pdb::formatRs for a full list.

When working with datasets larger than about 50 rows, you may run out of ram when using return types other than 'pdo', 'null', 'count' or 'val' because the other types all return the values as arrays

Parameters
$st : PDOStatement

The query to execute. Prepare using Pdb::prepare

$params : array<string|int, mixed>

Parameters to bind to the query

$config : string|array<string|int, mixed>|PdbReturnInterface

a return type or config PdbReturn

Tags
throws
InvalidArgumentException

If the return type isn't valid

QueryException

If the query execution or formatting failed

ConnectionException

If the connection fails

extractEnumArr()

Returns definition list from column of type ENUM

public extractEnumArr(string $table, string $column) : array<string|int, string>
Parameters
$table : string

non-prefixed

$column : string
Tags
inheritdoc
Return values
array<string|int, string>

fieldList()

public fieldList(string $table) : array<string|int, PdbColumn>
Parameters
$table : string

non-prefixed

Tags
inheritdoc
Return values
array<string|int, PdbColumn> —

[ name => PdbColumn ]

find()

Shorthand for creating a new PdbQuery.

public find(string|array<string|int, string>|PdbQueryInterface $table[, array<string|int, mixed> $conditions = [] ]) : PdbQuery
Parameters
$table : string|array<string|int, string>|PdbQueryInterface
$conditions : array<string|int, mixed> = []
Return values
PdbQuery

format()

Format a object in accordance to the registered formatters.

public format(mixed $value) : mixed

Formatters convert objects to saveable types, such as a string or an integer

Parameters
$value : mixed

The value to format

Tags
throws
InvalidArgumentException
Return values
mixed —

The formatted value

formatRs()

Converts a PDO result set to a common data format:

public static formatRs(PDOStatement $rs, string|array<string|int, mixed>|PdbReturnInterface $config) : string|int|null|array<string|int, mixed>

  • null just a null

  • count the number of rows returned, or if the query is a SELECT COUNT() this will behave like 'val'

  • arr An array of rows, where each row is an associative array. Use only for very small result sets, e.g. <= 20 rows.

  • arr-num An array of rows, where each row is a numeric array. Use only for very small result sets, e.g. <= 20 rows.

  • row A single row, as an associative array Given an argument 'row:null' or the shorthand 'row?' this will return 'null' if the row is empty. Otherwise this will throw a RowMissingException.

  • row-num A single row, as a numeric array Given an argument 'row-num:null' or the shorthand 'row?' this will return 'null' if the row is empty. Otherwise this will throw a RowMissingException.

  • map An array of identifier => value pairs, where the identifier is the first column in the result set, and the value is the second

  • map-arr An array of identifier => value pairs, where the identifier is the first column in the result set, and the value an associative array of name => value pairs (if there are multiple subsequent columns) Optionally, one can provide an 'column' argument with the type string in the form: 'map-arr:column'.

  • val A single value (i.e. the value of the first column of the first row) Given an argument 'val:null' or the shorthand 'val?' this will return 'null' if the result is empty. Otherwise this will throw a RowMissingException.

  • col All values from the first column, as a numeric array. DO NOT USE with boolean columns; see note at http://php.net/manual/en/pdostatement.fetchcolumn.php

Arguments:

Some types permit arguments in the type string, such as:

  • row:null
  • val:null
  • map-arr:{column}

This also supports the shorthand row? and val? forms.

Parameters
$rs : PDOStatement
$config : string|array<string|int, mixed>|PdbReturnInterface

One of 'null', 'count', 'arr', 'arr-num', 'row', 'row-num', 'map', 'map-arr', 'val' or 'col'

Tags
throws
RowMissingException

If the result set didn't contain the required row

InvalidArgumentException

If the type is invalid

Return values
string|int|null|array<string|int, mixed>

generateUid()

Create a UUIDv5 for this table + id.

public generateUid(string $table, int $id) : string

These are 'namespaced' UUIDs. Within this namespace we have a 'scheme' that is built from the database + table + id. This means UUIDs are unique and reproducible.

If the id is zero the UUID will be a nil.

Parameters
$table : string
$id : int
Return values
string

get()

Return all columns for a single row of a table.

public get(string $table, int $id[, bool $throw = true ]) : ($throw is true ? array : array|null)

The row is specified using its id.

Parameters
$table : string

The table name, not prefixed

$id : int

The id of the record to fetch

$throw : bool = true

Whether to throw an exception if the row is missing

Tags
throws
QueryException

If the query fails

RowMissingException

If there's no row

InvalidArgumentException
ConnectionException
Return values
($throw is true ? array : array|null) —

The record data

getConnection()

Gets a PDO connection, creating a new one if necessary

public getConnection() : PDO
Tags
throws
ConnectionException

If connection fails

Return values
PDO

getDependentKeys()

Gets all of the dependent foreign key columns (i.e. with the CASCADE delete rule) in other tables which link to the id column of a specific table

public getDependentKeys(string $table) : array<string|int, PdbForeignKey>
Parameters
$table : string

non-prefixed

Tags
inheritdoc
Return values
array<string|int, PdbForeignKey>

getForeignKeys()

Gets all of the columns which have foreign key constraints in a table

public getForeignKeys(string $table) : array<string|int, PdbForeignKey>
Parameters
$table : string

non-prefixed

Tags
inheritdoc
Return values
array<string|int, PdbForeignKey>

getLastInsertId()

Return the value from the autoincement of the most recent INSERT query

public getLastInsertId() : int|null
Return values
int|null —

The record id, or null if there hasn't been an insert

getPermissions()

Get the permissions of the current connection.

public getPermissions() : array<string|int, string>
Tags
inheritdoc
Return values
array<string|int, string>

getPrefix()

Get the prefix for table, or global

public getPrefix([string $table = '*' ]) : string
Parameters
$table : string = '*'
Return values
string

getServerVersion()

Get the server version string.

public getServerVersion() : string
Tags
throws
ConnectionException
Return values
string

getTable()

public getTable(string $table) : PdbTable|null
Parameters
$table : string

non-prefixed

Return values
PdbTable|null

getTableAttributes()

Get adapter specific table information.

public getTableAttributes(string $table) : array<string|int, mixed>
Parameters
$table : string

non-prefixed

Tags
inheritdoc
Return values
array<string|int, mixed> —

[ key => value]

getTableNames()

Fetches the current list of tables.

public getTableNames([string $filter = '*' ][, bool $strip = true ]) : array<string|int, string>
Parameters
$filter : string = '*'
  • '' - (empty) to return all tables
  • '*' - to return only tables with the default prefix
  • other - filter on this prefix
$strip : bool = true

remove the prefix from the table names

Tags
inheritdoc
Return values
array<string|int, string>

getTimezone()

Get the session timezone.

public getTimezone([bool $refresh = false ]) : DateTimeZone

The 'use_session_timezone' config is enabled by default, so this should typically return the PHP system timezone.

Parameters
$refresh : bool = false
Return values
DateTimeZone

indexList()

public indexList(string $table) : array<string|int, PdbIndex>
Parameters
$table : string

non-prefixed

Tags
inheritdoc
Return values
array<string|int, PdbIndex>

insert()

Runs an INSERT query

public insert(string $table, array<string|int, mixed> $data) : int
Parameters
$table : string

The table (without prefix) to insert the data into

$data : array<string|int, mixed>

Data to insert, column => value

Tags
throws
InvalidArgumentException
QueryException
ConnectionException
Return values
int —

The id of the newly-inserted record, if applicable

insertPrefixes()

Replaces tilde placeholders with table prefixes, and quotes tables according to the rules of the underlying DBMS

public insertPrefixes(string $query) : string
Parameters
$query : string

Query which contains tilde placeholders, e.g. 'SELECT * FROM ~pages WHERE id = 1'

Return values
string —

Query with tildes replaced by prefixes, e.g. 'SELECT * FROM fwc_pages WHERE id = 1'

inTransaction()

Checks if there's a current transaction in progress

public inTransaction() : bool
Return values
bool —

True if inside a transaction

listTables()

Fetches the current list of tables.

public listTables() : array<string|int, string>

use getTableNames()

Return values
array<string|int, string> —

non-prefixed

log()

Log something.

public log(mixed $message[, int $level = null ][, string|null $category = null ][, float|null $timestamp = null ]) : void
Parameters
$message : mixed
$level : int = null

default: LEVEL_INFO

$category : string|null = null

default: class name (static)

$timestamp : float|null = null

default: now

lookup()

Fetches a mapping of id => value values from a table, using the 'name' values by default

public lookup(string $table[, array<string|int, mixed> $conditions = [] ][, array<string|int, mixed> $order = ['name'] ][, string $name = 'name' ]) : array<string|int, mixed>

This wraps PdbQuery::find()->orderBy()->map()

Parameters
$table : string

The table name, without prefix

$conditions : array<string|int, mixed> = []

Optional where clause Pdb::buildClause

$order : array<string|int, mixed> = ['name']

Optional columns to ORDER BY. Defaults to 'name'

$name : string = 'name'

The field to use for the mapped values

Return values
array<string|int, mixed> —

A lookup table

now()

Gets a datetime value for the current time, relative to the session timezone.

public now([string $format = self::FORMAT_DATE_TIME ]) : string

This function should only be used for generating values right before an INSERT or UPDATE query is run.

Parameters
$format : string = self::FORMAT_DATE_TIME

default FORMAT_DATE_TIME

Return values
string

prepare()

Prepare a query into a prepared statement

public prepare(string $query[, array<string|int, mixed> $options = [] ]) : PDOStatement
Parameters
$query : string

The query to execute. Prefix a table name with a tilde (~) to automatically include the table prefix, e.g. ~pages will be converted to fwc_pages

$options : array<string|int, mixed> = []

PDO attributes

Tags
throws
QueryException

If the query execution or formatting failed

ConnectionException

If the connection fails

Return values
PDOStatement —

The prepared statement, for execution with Pdb::execute

prettyQuery()

Return a query with the values substituted into their respective binding positions.

public prettyQuery(string $query, array<string|int, mixed> $values) : string

DO NOT EXECUTE THIS STRING.

  • These values are not properly escaped.
  • This is purely for logging.
Parameters
$query : string
$values : array<string|int, mixed>
Return values
string

q()

Alias for {@see Pdb::query}

public q(string $query, array<string|int, mixed> $params, string|array<string|int, mixed>|PdbReturnInterface $config) : mixed

because just don't be lazy.

Parameters
$query : string

The query to execute. Prefix a table name with a tilde (~) to automatically include the table prefix, e.g. ~pages will be converted to fwc_pages

$params : array<string|int, mixed>

Parameters to bind to the query

$config : string|array<string|int, mixed>|PdbReturnInterface

a return type or config PdbReturn

Tags
throws
InvalidArgumentException

If the return type isn't valid

QueryException

If the query execution or formatting failed

ConnectionException

If the connection fails

query()

Executes a PDO query

public query(string $query, array<string|int, mixed> $params, string|array<string|int, mixed>|PdbReturnInterface $config) : mixed

For the return type 'pdo', a PDOStatement is returned. You need to close it using $res->closeCursor() once you're finished. Additional return types are available; Pdb::formatRs for a full list.

When working with datasets larger than about 50 rows, you may run out of ram when using return types other than 'pdo', 'null', 'count' or 'val' because the other types all return the values as arrays

Parameters
$query : string

The query to execute. Prefix a table name with a tilde (~) to automatically include the table prefix, e.g. ~pages will be converted to fwc_pages

$params : array<string|int, mixed>

Parameters to bind to the query

$config : string|array<string|int, mixed>|PdbReturnInterface

a return type or config PdbReturn

Tags
throws
InvalidArgumentException

If the return type isn't valid

QueryException

If the query execution or formatting failed

ConnectionException

If the connection fails

quote()

public quote(string $field, string $type) : string
Parameters
$field : string
$type : string

Pdb::QUOTE

Tags
throws
ConnectionException
PdbException
Return values
string

quoteAll()

public quoteAll(iterable<string|int, string> $fields, string $type) : array<string|int, string>
Parameters
$fields : iterable<string|int, string>
$type : string

Pdb::QUOTE

Tags
throws
ConnectionException
PdbException
Return values
array<string|int, string>

quoteField()

public quoteField(string $field[, bool $prefixes = false ]) : string
Parameters
$field : string
$prefixes : bool = false
Return values
string

quoteIdentifiers()

Wrap extended field identifiers in quotes.

public quoteIdentifiers(string $query[, array<string|int, string>|null $tables = null ][, bool $prefixes = false ]) : string

Such as:

table.column => `table`.`column`
~table.column => `pdb_table`.`column`

// Given 'prefixes = false'
~table.column => ~table.`column`

Given a list of table identifiers this will only quote matching table identifiers. This is recommended to prevent accidentally mangling inline values.

Table identifiers must be in their full non-prefixed form.

Parameters
$query : string
$tables : array<string|int, string>|null = null

list of identifiers

$prefixes : bool = false

Convert prefixes, otherwise only quotes the column component

Return values
string

recordExists()

Check to see that at least one record exists for certain conditions.

public recordExists(string $table, array<string|int, mixed> $conditions) : bool

This wrap PdbQuery::find()->exists()

Parameters
$table : string

The table name, not prefixed

$conditions : array<string|int, mixed>

Conditions for the WHERE clause, formatted as per Pdb::buildClause

Tags
throws
QueryException

If the query fails

example

if (!Pdb::recordExists('users', ['id' => 123, 'active' => 1])) { // ... }

Return values
bool —

True if a matching record exists

removeFormatter()

Remove the formatter function for a given class.

public removeFormatter(string $class_name) : mixed
Parameters
$class_name : string

The class to remove the formatter from

removeLogger()

Remove a log callback.

public removeLogger(int $index) : void

The index is returned from addLogger().

TODO This should live in the kbphp - LoggerTrait.

Parameters
$index : int

rollback()

Rolls a transaction or savepoint back.

public rollback([PdbTransaction|string|null $name = null ]) : void

Given a savepoint name it'll rollback to that savepoint and leave the wrapping transaction in place.

Given the transaction key or empty it'll rollback to while transaction and all savepoints.

Parameters
$name : PdbTransaction|string|null = null

a transaction key

Tags
throws
InvalidArgumentException

An invalid transaction key

TransactionNameException

The transaction/savepoint doesn't exist

TransactionEmptyException

No active transaction

ConnectionException

If the connection fails

savepoint()

Create a savepoint.

public savepoint([string|null $name = null ]) : string

Savepoints are effectively nested transactions.

All savepoints within a transaction are 'released' upon commit of the wrapping transaction.

Parameters
$name : string|null = null

optionally specify a savepoint name

Tags
throws
InvalidArgumentException

An invalid savepoint name

TransactionEmptyException

If there's no active transaction

ConnectionException

If the connection fails

Return values
string —

the savepoint name

setDebugger()

Attach/remove a debug logger.

public setDebugger(callable|null $debugger) : void

This receives more detail than the loggable trait.

Parameters
$debugger : callable|null

log method:

  • string $query
  • array $params
  • PDOStatement|QueryException $result

setFormatter()

Set the formatter function for a given class.

public setFormatter(string $class_name, callable $func) : mixed

Formatter functions are called when an object is passed to Pdb as a bind parameter or in other ways.

The function signature should be: function(mixed $val) : string

Parameters
$class_name : string

The class to attach the formatter to

$func : callable

The formatter function to use

setProfiler()

Attach/remove a profiler logger.

public setProfiler(callable|null $profiler) : void
Parameters
$profiler : callable|null

log method:

  • string $position
  • string $token

setTablePrefixOverride()

Sets an overriding prefix to prepend to an individual table.

public setTablePrefixOverride(string $table, string $prefix) : mixed
Parameters
$table : string

The table to use a specific prefix for, e.g. 'pages'

$prefix : string

The prefix to use, e.g. 'temp_only_'

tableExists()

public tableExists(string $table) : bool
Parameters
$table : string

non-prefixed

Tags
inheritdoc
Return values
bool

tableList()

public tableList() : array<string|int, PdbTable>
Return values
array<string|int, PdbTable>

update()

Runs an UPDATE query

public update(string $table, array<string|int, mixed> $data, array<string|int, mixed> $conditions) : int
Parameters
$table : string

The table (without prefix) to insert the data into

$data : array<string|int, mixed>

Data to update, column => value

$conditions : array<string|int, mixed>

Conditions for updates. Pdb::buildClause

Tags
throws
InvalidArgumentException
QueryException
ConnectionException
Return values
int —

The number of matched rows

upsert()

public upsert(string $table, array<string|int, mixed> $data, array<string|int, mixed> $conditions) : int
Parameters
$table : string
$data : array<string|int, mixed>
$conditions : array<string|int, mixed>
Tags
throws
InvalidArgumentException
QueryException
ConnectionException
Return values
int

validateAlias()

Validate an alias.

public static validateAlias(string|array{0: mixed, 1?: string} $field[, bool $loose = false ]) : void

An 'alias' in pdb is represented as an array pair: field, alias.

Parameters
$field : string|array{0: mixed, 1?: string}
$loose : bool = false

Permit integers/functions, e.g. SELECT 1, COUNT(*), etc

Tags
throws
InvalidArgumentException

validateDirection()

Validates an order-by direction.

public static validateDirection(mixed $name) : void
Parameters
$name : mixed

The identifier to check

Tags
throws
InvalidArgumentException

If the identifier is invalid

validateEnum()

Validates a value meant for an ENUM field, e.g.

public validateEnum(string $val, array<string|int, mixed> $field) : bool

$valid->addRules('col1', 'required', 'Pdb::validateEnum[table, col]');

This doesn't emit exceptions but instead returns a boolean.

Parameters
$val : string

The value to find in the ENUM

$field : array<string|int, mixed>

[0] Table name [1] Column name

Return values
bool

validateFunction()

Validate an SQL function.

public static validateFunction(mixed $value) : void
Parameters
$value : mixed
Tags
throws
InvalidArgumentException

validateIdentifier()

Validates a identifier (column name, table name, etc)

public static validateIdentifier(mixed $name[, bool $loose = false ]) : void
Parameters
$name : mixed

The identifier to check

$loose : bool = false

Permit integers + functions - e.g. SELECT 1, COUNT(*), etc

Tags
throws
InvalidArgumentException

If the identifier is invalid

validateIdentifierExtended()

Validates a identifier in extended format -- table.column Also accepts short format like {@see validateIdentifier} does.

public static validateIdentifierExtended(mixed $name[, bool $loose = false ]) : void
Parameters
$name : mixed

The identifier to check

$loose : bool = false

Permit integers + functions - e.g. SELECT 1, COUNT(*), etc

Tags
throws
InvalidArgumentException

If the identifier is invalid

validateReturnType()

Validate the return type.

public static validateReturnType(string $return_type) : void
Parameters
$return_type : string
Tags
throws
InvalidArgumentException

withTransaction()

Wrap a function in a transaction. This will commit or rollback automatically after the function is complete.

public withTransaction(callable(Pdb, PdbTransaction): T $callback) : T

IMPORTANT: if TX_ENABLE_NESTED is not enabled and there is a transaction already in process, this will not create a savepoint nor commit/rollback on the parent transaction.

Otherwise with nesting enabled, this will create savepoints instead with automatic commit/rollback behaviour.

Parameters
$callback : callable(Pdb, PdbTransaction): T
Tags
template
throws
TransactionRecursionException
ConnectionException
Return values
T —

the callback result

afterConnect()

Called after a new PDO connection is created.

protected static afterConnect(PDO $pdo, PdbConfig $config, array<string|int, mixed> $options) : void
Parameters
$pdo : PDO
$config : PdbConfig
$options : array<string|int, mixed>

PDO attributes

Tags
inheritdoc

bindParams()

Bind the array of parameters to a PDO statement

protected static bindParams(PDOStatement $st, array<string|int, mixed> $params) : void

Unlike PDOStatement::execute which binds everything as PARAM_STR, this method will bind integers as PARAM_INT

Parameters
$st : PDOStatement

Statement to bind parameters to

$params : array<string|int, mixed>

Parameters to bind

buildInsertSet()

Build the VALUES clause for an INSERT query.

protected buildInsertSet(array<string|int, mixed> $data) : array<string|int, mixed>

This will not apply binding rules. However the value formatting is still applied when processed by execute().

Parameters
$data : array<string|int, mixed>

[ col => val ]

Tags
throws
InvalidArgumentException
Return values
array<string|int, mixed> —

[ string, string, values[] ]

buildUpdateSet()

Build the SET clause for an UPDATE query.

protected buildUpdateSet(array<string|int, mixed> $data) : array<string|int, mixed>

This will apply binding rules if the value is a PdbDataBinder or if there is a formatter registered for the respective class type.

Parameters
$data : array<string|int, mixed>

[ col => val ]

Tags
throws
InvalidArgumentException
Return values
array<string|int, mixed> —

[ string, values[] ]

getBindingQuery()

Build a binding query for a SET clause.

protected getBindingQuery(string $col, mixed $val) : string

This applies the appropriate binder or formatter, if applicable.

Parameters
$col : string
$val : mixed
Tags
throws
InvalidArgumentException
Return values
string

getCacheKey()

Produce a key appropriate to represent a query and it's parameters.

protected getCacheKey(string $sql, array<string|int, mixed> $params, PdbReturnInterface $config) : string|null
Parameters
$sql : string
$params : array<string|int, mixed>
$config : PdbReturnInterface
Return values
string|null —

the key or null if the cache is disabled

getCacheTtl()

Determine the cache TTL from the return type or the global config.

protected getCacheTtl(PdbReturnInterface $config) : int
Parameters
$config : PdbReturnInterface
Return values
int —

seconds

logQuery()

Log something.

protected logQuery(string $query, array<string|int, mixed> $params, PDOStatement|QueryException $result) : void

Be careful when attaching a logger that uses pdb to record logs. In this case one should clone the pdb instance and remove any loggers on it before using it to record logs.

Such as:

$log = clone $pdb;
$log->clearLoggers();

$pdb->addLogger(function($message) use ($log) {
    $log->insert('sql_log', ['message' => $message]);
});

Note, this method will still use the same connection. But the separation of Pdb instances prevents a logging loop.

The 'debugger' logger will swap itself out while logging. Theoretically this should prevent any self-logging within the same instance.

TODO It's possible we could detect loops? Maybe with debug_backtrace()?

Parameters
$query : string
$params : array<string|int, mixed>
$result : PDOStatement|QueryException

stripPrefix()

Remove prefixes from a table name.

protected stripPrefix(string $value) : string

This strips both the global prefix config and table_prefixes.

This does not remove the ~ placeholder.

Parameters
$value : string
Return values
string
On this page

Search results