PDOX
extends PDO
in package
This is our "improved" version of PDO
The PDOX class adds a number of non-trivial convienence methods to the underlying PHP PDO class. These methods combine several PDO calls into a single call for common patterns and add far more extensive error checking and simpler error handling.
The primary value is in the queryReturnError() function which combines prepare() and execute() as well as adding in extensive error checking. It turns out that to properly check all of the return values and possible errors which can happen using prepare() and execute() is really challenging and not even obvious from the PDO documentation. So we have collected all that wisdom into this method and then use it throughout Tsugi.
The rest of the methods are convienence methods to combine common multi-step operations into a single call to make tool code more readable.
While this seems to be bending over backwards, It makes the calling code very succinct as follows:
$stmt = $PDOX->queryDie(
"INSERT INTO .... ",
array('SHA' => $userSHA, ... )
);
if ( $stmt->success) $profile_id = $PDOX->lastInsertId();
Whilst many of these methods seem focused on calling the die() function, the only time that die() is called is when there is an SQL syntax error. Not finding a record is non-fatal. In general SQL syntax errors only happen during development (if you are doing it right) so you might as well die() if there is an SQL syntax error as it most likely indicates a coding bug rather than a runtime user error or missing data problem.
Table of Contents
Properties
- $PDOX_LastInsertStatement : mixed
- $PDOX_LastSqlQuery : mixed
- $PDOX_MetaEntries : mixed
- Array of meta entries
- $slow_query : mixed
- Threshold for logging slow queries - 0 means don't log
- $sqlPatch : mixed
- $PDOX_upsert_marker : mixed
Methods
- addPDOXMeta() : mixed
- addPDOXMeta - Add a meta entry for a table
- allRows() : array<string|int, mixed>|false
- Same rows as allRowsDie(), but a failed query returns false.
- allRowsDie() : array<string|int, mixed>
- Prepare and execute an SQL query and retrieve all the rows as an array
- columnExists() : bool
- Check if a column exists
- columnIsNull() : mixed
- Check if a column is null
- columnLength() : mixed
- Get the column length
- columnType() : mixed
- Get the column type
- describe() : mixed
- Retrieve the metadata for a table.
- describeColumn() : mixed
- Retrieve the metadata for a column in a table.
- getInsertTable() : mixed
- Check if an SQL statement is an INSERT and return the table name
- getPDOXDriverName() : mixed
- getPDOXDriverName - Get the driver name
- getPDOXMeta() : mixed
- getPDOXMeta - Extract and parse the PDOX Metadata comment from a query or entries
- indexDetail() : mixed
- Retrieve the indexes for a table.
- indexes() : mixed
- indexExists() : bool
- Check if an index exists on a table
- insureColumnExists() : mixed
- Insure that a column exists
- isInsertStatement() : mixed
- Check if an SQL statement is an INSERT statement
- isMySQL() : mixed
- Return true if the current connection is MySQL
- isPgSQL() : mixed
- Return true if the current connection is PgSQL
- isSQLite() : mixed
- Return true if the current connection is SQLite
- lastInsertId() : string|false
- lastInsertId - Override to use parent implementation
- limitVars() : An
- limitVars - Filter out substitution variables that are not needed
- metadata() : mixed
- Retrieve the metadata for a table.
- parseServerVersion() : array{raw: string, product: string, version: string, modern: bool, add_column: string}
- Parse a VERSION() string without opening a connection.
- prepare() : PDOStatement|false
- prepare - Override prepare() to capture the PDOX and LastInsertStatement (for INSERTs)
- queryDie() : PDOStatement
- Prepare and execute an SQL query or die() in the attempt.
- queryReturnError() : PDOStatement
- Prepare and execute an SQL query with lots of error checking.
- queryReturnErrorInternal() : mixed
- rowDie() : array<string|int, mixed>
- Prepare and execute an SQL query and retrieve a single row.
- serverVersion() : array{raw: string, product: string, version: string, modern: bool, add_column: string}
- Which database this connection is, and whether it is modern enough.
- sqlDisplay() : string
- Create a display version of SQL with actual values substituted for placeholders This is for display/debugging purposes only - the actual query should use parameterized queries
- timeFromMySQLTimeStamp() : mixed
- timeToMySQLTimeStamp() : mixed
- upsertGetPKReturnError() : PDOStatement
- upsertGetPKReturnError - Insert or update a record and return the lastInsertID
- versionAtLeast() : mixed
- Check the current connection against a version
- versionNumber() : mixed
- Get the version number of the current connection
Properties
$PDOX_LastInsertStatement
public
mixed
$PDOX_LastInsertStatement
= False
$PDOX_LastSqlQuery
public
mixed
$PDOX_LastSqlQuery
= False
$PDOX_MetaEntries
Array of meta entries
public
mixed
$PDOX_MetaEntries
= array()
$slow_query
Threshold for logging slow queries - 0 means don't log
public
mixed
$slow_query
= 0
$sqlPatch
public
mixed
$sqlPatch
$PDOX_upsert_marker
private
mixed
$PDOX_upsert_marker
= '/* upsert */'
Methods
addPDOXMeta()
addPDOXMeta - Add a meta entry for a table
public
addPDOXMeta(mixed $table, mixed $meta) : mixed
Usage: $PDOX->addPDOXMeta("{$p}lti_context", array("pk" => "context_id", "lk" => array("context_sha256", "key_id")) $PDOX->addPDOXMeta("{$p}lti_link", array("pk" => "link_id", "lk" => array("link_sha256", "context_id"))
Parameters
- $table : mixed
- $meta : mixed
allRows()
Same rows as allRowsDie(), but a failed query returns false.
public
allRows(mixed $sql[, mixed $arr = false ][, mixed $error_log = true ]) : array<string|int, mixed>|false
The caller decides whether that failure is fatal.
Parameters
- $sql : mixed
- $arr : mixed = false
- $error_log : mixed = true
Return values
array<string|int, mixed>|falseallRowsDie()
Prepare and execute an SQL query and retrieve all the rows as an array
public
allRowsDie(mixed $sql[, mixed $arr = false ][, mixed $error_log = true ]) : array<string|int, mixed>
While this might seem like a bad idea, the coding style for Tsugi is to make every query a paged query with a limited number of records to be retrieved to in most cases, it is quite reasonable to retrieve 10-30 rows into an array.
If code wants to stream the results of a query, they should do their own query and loop through the rows in their own code.
Parameters
- $sql : mixed
-
The SQL to execute in a string. If the SQL is badly formed this function will die.
- $arr : mixed = false
-
An optional array of the substitition values if needed by the query
- $error_log : mixed = true
-
Indicates whether or not errors are to be logged. Default is TRUE.
Return values
array<string|int, mixed> —This is either the rows that were retrieved or or an empty array if there were no rows.
columnExists()
Check if a column exists
public
columnExists(mixed $fieldname, mixed $source) : bool
Parameters
- $fieldname : mixed
-
The name of the column
- $source : mixed
-
Either an array of the column metadata or the name of the table
Return values
bool —Returns true/false
columnIsNull()
Check if a column is null
public
columnIsNull(mixed $fieldname, mixed $source) : mixed
Parameters
- $fieldname : mixed
-
The name of the column
- $source : mixed
-
Either an array of the column metadata or the name of the table
Return values
mixed —Returns true / false if the columns exists and null if the column does not exist.
columnLength()
Get the column length
public
columnLength(mixed $fieldname, mixed $source) : mixed
Parameters
- $fieldname : mixed
-
The name of the column
- $source : mixed
-
Either an array of the column metadata or the name of the table
Return values
mixed —Returns an integer has an explicit length, 0 if the column has no length and null if the column does not exist.
columnType()
Get the column type
public
columnType(mixed $fieldname, mixed $source) : mixed
Parameters
- $fieldname : mixed
-
The name of the column
- $source : mixed
-
Either an array of the column metadata or the name of the table
Return values
mixed —Returns a string if the columns exists and null if the column does not exist.
describe()
Retrieve the metadata for a table.
public
describe(mixed $tablename) : mixed
Parameters
- $tablename : mixed
describeColumn()
Retrieve the metadata for a column in a table.
public
describeColumn(mixed $fieldname, mixed $source) : mixed
For the output format for MySQL, see: https://dev.mysql.com/doc/refman/5.7/en/explain.html
Parameters
- $fieldname : mixed
-
The name of the column
- $source : mixed
-
Either an array of the column metadata or the name of the table
Return values
mixed —An array of the column metadata or null
getInsertTable()
Check if an SQL statement is an INSERT and return the table name
public
getInsertTable(mixed $sql) : mixed
Parameters
- $sql : mixed
getPDOXDriverName()
getPDOXDriverName - Get the driver name
public
getPDOXDriverName() : mixed
getPDOXMeta()
getPDOXMeta - Extract and parse the PDOX Metadata comment from a query or entries
public
getPDOXMeta(mixed $sql, mixed $table) : mixed
Space added between the slash and star below because this documentation is in a comment :)
INSERT INTO lti_context / *PDOX pk: context_id lk: context_sha256,key_id * /
The syntax is very picky:
A space is required after the colon but no space is allowed before the colon. There must be a space after the comment start and comment end. There cannot be a space after the comma when there is more than one logical key.
A default for this metadata can be stored for a table by calling $PDOX->addPDOXMeta(); If a comment is included it overrides any stored default.
Parameters
- $sql : mixed
- $table : mixed
indexDetail()
Retrieve the indexes for a table.
public
indexDetail(mixed $tablename) : mixed
Parameters
- $tablename : mixed
indexes()
public
indexes(mixed $tablename) : mixed
Parameters
- $tablename : mixed
indexExists()
Check if an index exists on a table
public
indexExists(string $indexname, string $tablename) : bool
Parameters
- $indexname : string
-
The name of the index to check
- $tablename : string
-
The name of the table
Return values
bool —True if the index exists, false otherwise
insureColumnExists()
Insure that a column exists
public
insureColumnExists(mixed $table, mixed $column, mixed $type) : mixed
Parameters
- $table : mixed
-
The name of the table like '{$CFG->dbprefix}lti_result'
- $column : mixed
-
The name of the column like 'grading_progress'
- $type : mixed
-
Desired type for the column like 'TINYINT(1) NOT NULL DEFAULT 0'
Return values
mixed —- Either true or a string with a message
isInsertStatement()
Check if an SQL statement is an INSERT statement
public
isInsertStatement(mixed $sql) : mixed
Parameters
- $sql : mixed
isMySQL()
Return true if the current connection is MySQL
public
isMySQL() : mixed
isPgSQL()
Return true if the current connection is PgSQL
public
isPgSQL() : mixed
PostgreSQL support has been removed from Tsugi
isSQLite()
Return true if the current connection is SQLite
public
isSQLite() : mixed
SQLite support has been removed from Tsugi
lastInsertId()
lastInsertId - Override to use parent implementation
public
lastInsertId([mixed $seqname = null ]) : string|false
Parameters
- $seqname : mixed = null
Return values
string|falselimitVars()
limitVars - Filter out substitution variables that are not needed
public
static limitVars(mixed $sql, mixed $vars) : An
Parameters
- $sql : mixed
-
The SQL to execute in a string.
- $vars : mixed
Return values
An —array of the variables that actually can be found in the SQL.
metadata()
Retrieve the metadata for a table.
public
metadata(mixed $tablename) : mixed
Parameters
- $tablename : mixed
parseServerVersion()
Parse a VERSION() string without opening a connection.
public
static parseServerVersion(mixed $version) : array{raw: string, product: string, version: string, modern: bool, add_column: string}
product is mysql, mariadb, aurora, or unknown. version is comparable as major.minor.patch. MariaDB's 5.5.5- compatibility prefix is ignored. Aurora MySQL 3 with no MySQL patch (8.0.mysql_aurora.3.x) is reported as 8.0.23, the oldest MySQL 8.0 that release is based on. modern is MySQL or Aurora at 8.0.16 or newer, or MariaDB at 10.3.2 or newer. add_column is instant or inplace when a nullable column can be added without blocking writes. Empty means use a plain ALTER TABLE. That copy can lock the table.
Parameters
- $version : mixed
Return values
array{raw: string, product: string, version: string, modern: bool, add_column: string}prepare()
prepare - Override prepare() to capture the PDOX and LastInsertStatement (for INSERTs)
public
prepare(mixed $statement[, mixed $options = null ]) : PDOStatement|false
We won't really have a statement until they run execute() but we tolerate that.
Parameters
- $statement : mixed
- $options : mixed = null
Return values
PDOStatement|falsequeryDie()
Prepare and execute an SQL query or die() in the attempt.
public
queryDie(mixed $sql[, mixed $arr = false ][, mixed $error_log = true ]) : PDOStatement
Parameters
- $sql : mixed
-
The SQL to execute in a string. If the SQL is badly formed this function will die.
- $arr : mixed = false
-
An optional array of the substitition values if needed by the query
- $error_log : mixed = true
-
Indicates whether or not errors are to be logged. Default is TRUE.
Return values
PDOStatement —This is either the real PDOStatement from the prepare() call or a stdClass mocked to have error indications as described above.
queryReturnError()
Prepare and execute an SQL query with lots of error checking.
public
queryReturnError(mixed $sql[, mixed $arr = false ][, mixed $error_log = true ]) : PDOStatement
This routine will call prepare() and then execute() with the resulting PDOStatement and return the PDOStatement. If the prepare() fails, we fake up a stdClass() with a few fields that mimic a simple failed execute().
$stmt->errorCode
$stmt->errorInfo
We also augment the real or fake PDOStatement with these fields:
$stmt->success
$stmt->ellapsed_time
$stmt->errorImplode
$stmt->success is TRUE/FALSE based on the success of the operation to simplify error checking
$stmt->ellapsed_time includes the length of time the query took
$stmt->errorImplode an imploded version of errorInfo suitable for dropping into a log.
Parameters
- $sql : mixed
-
The SQL to execute in a string.
- $arr : mixed = false
-
An optional array of the substitition values if needed by the query
- $error_log : mixed = true
-
Indicates whether or not errors are to be logged. Default is TRUE.
Return values
PDOStatement —This is either the real PDOStatement from the prepare() call or a stdClass mocked to have error indications as described above.
queryReturnErrorInternal()
public
queryReturnErrorInternal(mixed $sql[, mixed $arr = false ][, mixed $error_log = true ]) : mixed
Parameters
- $sql : mixed
- $arr : mixed = false
- $error_log : mixed = true
rowDie()
Prepare and execute an SQL query and retrieve a single row.
public
rowDie(mixed $sql[, mixed $arr = false ][, mixed $error_log = true ]) : array<string|int, mixed>
Parameters
- $sql : mixed
-
The SQL to execute in a string. If the SQL is badly formed this function will die.
- $arr : mixed = false
-
An optional array of the substitition values if needed by the query
- $error_log : mixed = true
-
Indicates whether or not errors are to be logged. Default is TRUE.
Return values
array<string|int, mixed> —This is either the row that was returned or FALSE if no rows were returned.
serverVersion()
Which database this connection is, and whether it is modern enough.
public
serverVersion() : array{raw: string, product: string, version: string, modern: bool, add_column: string}
Return values
array{raw: string, product: string, version: string, modern: bool, add_column: string}sqlDisplay()
Create a display version of SQL with actual values substituted for placeholders This is for display/debugging purposes only - the actual query should use parameterized queries
public
static sqlDisplay(string $sql, array<string|int, mixed> $params) : string
Parameters
- $sql : string
-
SQL query with placeholders
- $params : array<string|int, mixed>
-
Parameters array (e.g., array(':UID' => 123, ':DAYS' => 180))
Return values
string —SQL with values substituted
timeFromMySQLTimeStamp()
public
static timeFromMySQLTimeStamp(string $datetime) : mixed
Parameters
- $datetime : string
timeToMySQLTimeStamp()
public
static timeToMySQLTimeStamp(int $when) : mixed
Parameters
- $when : int
upsertGetPKReturnError()
upsertGetPKReturnError - Insert or update a record and return the lastInsertID
public
upsertGetPKReturnError(mixed $sql, mixed $values[, mixed $error_log = true ]) : PDOStatement
Uses MySQL INSERT ... ON DUPLICATE KEY UPDATE syntax with LAST_INSERT_ID() trick to get the primary key whether the INSERT works or the UPDATE clause is triggered.
Parameters
- $sql : mixed
-
The SQL to execute in a string.
- $values : mixed
- $error_log : mixed = true
-
Indicates whether or not errors are to be logged. Default is True.
Return values
PDOStatement —This is either the real PDOStatement from the prepare() call or a stdClass mocked to have error indications as in queryReturnError().
Usage (# are actually slashes - C comment style):
$sql = "INSERT INTO {$p}lti_membership
#*PDOX pk: membership_id lk: context_id,user_id *#
( context_id, user_id, role, created_at, updated_at ) VALUES
( :context_id, :user_id, :role, NOW(), NOW() )
ON DUPLICATE KEY UPDATE updated_at = NOW();";
$PDOX->upsertGetPKReturnError($sql, array(
':context_id' => $row['context_id'],
':user_id' => $row['user_id'],
':role' => $post['role']));
References:
https://www.php.net/manual/en/pdo.lastinsertid.php#102614
https://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html
versionAtLeast()
Check the current connection against a version
public
versionAtLeast(mixed $min) : mixed
if ( $PDOX->versionAtLeast('8.0.0') ) {
Parameters
- $min : mixed
versionNumber()
Get the version number of the current connection
public
versionNumber() : mixed
$version = $PDOX->versionNumber(); $min = '5.6.0'; if ( version_compare($version, $min) >= 0) {