Documentation

PdbHelpers
in package

Table of Contents

Constants

DATA_TYPES  : mixed = ['INTEGER', 'TINYINT', 'SMALLINT', 'MEDIUMINT',...
KEYWORDS  : mixed = [self::TYPE_SELECT, self::TYPE_INSERT, self::TY...
RE_FUNCTION  : mixed = '/[a-z_]+\(.*\)$/i'
RE_IDENTIFIER  : mixed = '/^~?[a-z_][a-z_0-9]*$/i'
RE_IDENTIFIER_EXTENDED  : mixed = '/^\~?[a-z_][a-z_0-9]*(?:\.[a-z_][a-z_0-9]*|\.\...
RE_IDENTIFIER_PARTS  : mixed = '/(\~?[a-z_][a-z_0-9]*)\.([a-z_][a-z_0-9]*|\.\*...
RE_IDENTIFIER_PREFIX  : mixed = '/\~([a-z0-9_]+)/i'
TYPE_ALTER  : mixed = 'ALTER'
TYPE_CREATE  : mixed = 'CREATE'
TYPE_DELETE  : mixed = 'DELETE'
TYPE_DROP  : mixed = 'DROP'
TYPE_INSERT  : mixed = 'INSERT'
TYPE_SELECT  : mixed = 'SELECT'
TYPE_UPDATE  : mixed = 'UPDATE'
TYPES  : mixed = [self::TYPE_SELECT, self::TYPE_INSERT, self::TY...

Methods

bindPlaceholders()  : string
Create a bunch of placeholder bind thingos.
convertDataType()  : string|null
Convert SQL types into PHP types.
convertEnumArr()  : array<int, string>
Convert an ENUM or SET definition from MySQL into an array of values
fieldEscape()  : string
Escape quoted fields.
getBindSubset()  : array<string|int, mixed>
Gets the subset of bind params which are associated with a particular query from a generic list of bind params.
getCharsetSize()  : int
getColumnSize()  : int
https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html#data-types-storage-reqs-date-time
getQueryType()  : string|null
Determine the query type.
likeEscape()  : string
Escapes the special characters % and _ for use in a LIKE clause.
normalizeType()  : string
Normalise a column type to something a bit more consistent.
parseAlias()  : PdbQueryInterface, 1: string|null}
Parse an alias array or field.
prettyQueryAnsi()  : string
Highlight keywords in a query with ANSI colours.
prettyQueryIndentation()  : string
Makes a query have pretty indentation.
stripStrings()  : string
Strips string elements from a query, e.g. 'hey', "yeah", and 'it\'s nice'.
prepareKeywords()  : string
preparePattern()  : string

Constants

DATA_TYPES

public mixed DATA_TYPES = ['INTEGER', 'TINYINT', 'SMALLINT', 'MEDIUMINT', 'BIGINT', 'FLOAT', 'INT', 'DOUBLE PRECISION', 'DOUBLE', 'DECIMAL', 'NUMERIC', 'VARCHAR', 'CHAR', 'VARBINARY', 'BINARY', 'TINYBLOB', 'MEDIUMBLOB', 'LONGBLOB', 'BLOB', 'TINYTEXT', 'MEDIUMTEXT', 'LONGTEXT', 'TEXT', 'ENUM', 'SET', 'DATETIME', 'TIMESTAMP', 'DATE', 'TIME', 'YEAR', 'BOOLEAN', 'JSON', 'SIGNED', 'UNSIGNED']

KEYWORDS

public mixed KEYWORDS = [self::TYPE_SELECT, self::TYPE_INSERT, self::TYPE_UPDATE, self::TYPE_CREATE, self::TYPE_ALTER, self::TYPE_DELETE, self::TYPE_DROP, 'TABLE', 'COLUMN', 'INDEX', 'FOREIGN KEY', 'PRIMARY KEY', 'AUTOINCREMENT', 'UNIQUE', 'DEFAULT', 'ROLE', 'USER', 'PASSWORD', 'WITH GRANT OPTION', 'WITH', 'ADD', 'AFTER', 'GRANT', 'REVOKE', 'ALL PRIVILEGES', 'NO PRIVILEGES', 'FROM', 'WHERE', 'GROUP BY', 'HAVING', 'ORDER BY', 'LIMIT', 'OFFSET', 'ROLLUP', 'LEFT JOIN', 'RIGHT JOIN', 'INNER JOIN', 'LEFT OUTER JOIN', 'RIGHT OUTER JOIN', 'FULL OUTER JOIN', 'OUTER JOIN', 'FULL JOIN', 'JOIN', 'UNION', 'BEGIN', 'COMMIT', 'ROLLBACK TO', 'ROLLBACK', 'RELEASE SAVEPOINT', 'SAVEPOINT', 'AS', 'BY', 'IN', 'NOT', 'IS', 'NULL', 'TRUE', 'FALSE', 'BETWEEN', 'AND', 'OR', 'NOT', 'ILIKE', 'LIKE', 'IN', 'EXISTS', 'VALUES', 'SET', 'DEFAULT']

RE_FUNCTION

public mixed RE_FUNCTION = '/[a-z_]+\(.*\)$/i'

RE_IDENTIFIER

public mixed RE_IDENTIFIER = '/^~?[a-z_][a-z_0-9]*$/i'

RE_IDENTIFIER_EXTENDED

public mixed RE_IDENTIFIER_EXTENDED = '/^\~?[a-z_][a-z_0-9]*(?:\.[a-z_][a-z_0-9]*|\.\*)?$/i'

RE_IDENTIFIER_PARTS

public mixed RE_IDENTIFIER_PARTS = '/(\~?[a-z_][a-z_0-9]*)\.([a-z_][a-z_0-9]*|\.\*)/i'

RE_IDENTIFIER_PREFIX

public mixed RE_IDENTIFIER_PREFIX = '/\~([a-z0-9_]+)/i'

TYPES

public mixed TYPES = [self::TYPE_SELECT, self::TYPE_INSERT, self::TYPE_UPDATE, self::TYPE_CREATE, self::TYPE_ALTER, self::TYPE_DELETE, self::TYPE_DROP]

Methods

bindPlaceholders()

Create a bunch of placeholder bind thingos.

public static bindPlaceholders(int|array<string|int, string> $data) : string

This accepts a number or an array of keys.

  • If a number, it will create that many placeholders.
  • If an array of keys, it will create placeholders for each item.

Examples:

  • (int) 4 => '?, ?, ?, ?'
  • [a, b, c] => ':a, :b, :c'
Parameters
$data : int|array<string|int, string>
Return values
string

convertDataType()

Convert SQL types into PHP types.

public static convertDataType(string $type[, bool $strict = false ]) : string|null

This does approximate conversions, e.g.

'VARCHAR' => 'string'
'DATE' => 'datetime'
'SET' => 'array'

In 'strict' mode this will only return the real types that are returned from the database, one of string|int|float.

Consider 'non-strict' mode as more of a 'hint' about how to use the value. Such as;

  • 'datetime' can be jammed into strtotime()
  • 'array' can be exploded into an array
Parameters
$type : string

the SQL type

$strict : bool = false

only return real types

Return values
string|null —

the PHP type, null if unknown

convertEnumArr()

Convert an ENUM or SET definition from MySQL into an array of values

public static convertEnumArr(string $enum_defn) : array<int, string>
Parameters
$enum_defn : string

The definition from MySQL, e.g. ENUM('aa','bb','cc')

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

Numerically indexed

fieldEscape()

Escape quoted fields.

public static fieldEscape(string $field, array<string|int, mixed> $quotes) : string

Such as; "abc"def" => "abc""def"

Parameters
$field : string
$quotes : array<string|int, mixed>

[ left, right ]

Return values
string —

quoted field

getBindSubset()

Gets the subset of bind params which are associated with a particular query from a generic list of bind params.

public static getBindSubset(string $q, array<string|int, mixed> $binds) : array<string|int, mixed>

This is used to support the SQL DB tool. N.B. This probably won't work if you mix named and numbered params in the same query.

Parameters
$q : string
$binds : array<string|int, mixed>

generic list of binds

Return values
array<string|int, mixed>

getCharsetSize()

public static getCharsetSize(string $charset) : int
Parameters
$charset : string
Return values
int

getColumnSize()

https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html#data-types-storage-reqs-date-time

public static getColumnSize(string $type[, string $charset = 'utf8' ]) : int
Parameters
$type : string
$charset : string = 'utf8'
Tags
throws
InvalidArgumentException
Return values
int

getQueryType()

Determine the query type.

public static getQueryType(string $query) : string|null
Parameters
$query : string
Return values
string|null —

null if invalid.

likeEscape()

Escapes the special characters % and _ for use in a LIKE clause.

public static likeEscape(string $str[, string $escape = '\\' ]) : string

SQL permits one to set the escape character, so the second parameter here should match that of the query.

WHERE x LIKE '^%foo' ESCAPE '^'

Use this like:

// Be aware this example is not safely quoted for brevity.
// Use prepared queries or `Pdb::quoteValue()`.

$esc = '^';
$like = likeEscape('%foo', $esc);
$q = "WHERE x LIKE '{$like}' ESCAPE '{$esc}'";

This defaults to backslash, which is the default for most (all?) databases.

Parameters
$str : string
$escape : string = '\\'

default backslash \

Return values
string

normalizeType()

Normalise a column type to something a bit more consistent.

public static normalizeType(string $type) : string
Parameters
$type : string
Return values
string

parseAlias()

Parse an alias array or field.

public static parseAlias(string|array<string|int, mixed>|PdbQueryInterface $field) : PdbQueryInterface, 1: string|null}

The second item is null if no alias present.

This converts:

  • [column, alias] (passthrough)
  • [alias => column] (array)
  • 'column as alias' (string, full syntax)
  • 'column alias' (string, shorthand)
Parameters
$field : string|array<string|int, mixed>|PdbQueryInterface
Return values
PdbQueryInterface, 1: string|null} —

[ field, alias ] second param is null if no alias is present.

prettyQueryAnsi()

Highlight keywords in a query with ANSI colours.

public static prettyQueryAnsi(string $query) : string
Parameters
$query : string
Return values
string

prettyQueryIndentation()

Makes a query have pretty indentation.

public static prettyQueryIndentation(string $query) : string

Typically a query is written as a multiline string embedded within PHP code, and when printed as-is, it looks horrible as the PHP indentation is basically incorporated into the SQL.

Alternatively, in PHP7.3+ heredoc strings will strip PHP indentation.

N.B. All tabs are converted to 4 spaces each

Parameters
$query : string
Return values
string

stripStrings()

Strips string elements from a query, e.g. 'hey', "yeah", and 'it\'s nice'.

public static stripStrings(string $q) : string

This function is used to support Pdb::getBindSubset.

Parameters
$q : string

The query

Return values
string —

The modified query

prepareKeywords()

protected static prepareKeywords(array<string|int, mixed> $keywords) : string
Parameters
$keywords : array<string|int, mixed>
Return values
string

preparePattern()

protected static preparePattern() : string
Return values
string
On this page

Search results