Tsugi

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>|false

allRowsDie()

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|false

limitVars()

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|false

queryDie()

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


        
On this page

Search results