Documentation

Sql extends AbstractSql
in package

Pop

Db

Sql class

Constants

BACKTICK = 'BACKTICK': Constants for id quote types
BRACKET = 'BRACKET'
DOUBLE_QUOTE = 'DOUBLE_QUOTE'
MYSQL = 'MYSQL': Constants for database types
NO_QUOTE = 'NO_QUOTE'
PGSQL = 'PGSQL'
SQLITE = 'SQLITE'
SQLSRV = 'SQLSRV'

Properties

$aggregateFunctions : array<string|int, mixed>: Supported standard SQL aggregate functions
$closeQuote : string|null: ID close quote
$dateTimeFunctions : array<string|int, mixed>: Supported standard SQL date-time functions
$db : AbstractAdapter|null: Database object
$dbType : string|null: Database type
$delete : Delete|null: Delete object
$idQuoteType : string: ID quote type
$insert : Insert|null: Insert object
$mathFunctions : array<string|int, mixed>: Supported standard SQL math functions
$openQuote : string|null: ID open quote
$parameterCount : int: Parameter count
$placeholder : string|null: SQL placeholder
$select : Select|null: Select object
$stringFunctions : array<string|int, mixed>: Supported standard SQL string functions
$update : Update|null: Update object

Methods

__construct() : mixed: Constructor
__toString() : string: Render the SQL statement
db() : AbstractAdapter|null: Get the current database adapter object (alias method)
decrementParameterCount() : AbstractSql: Decrement parameter count
delete() : Delete|null: Access the delete object
getCloseQuote() : string|null: Get close quote
getDb() : AbstractAdapter|null: Get the current database adapter object
getDbType() : string|null: Get the current database type
getIdQuoteType() : string: Get the quote ID type
getOpenQuote() : string|null: Get open quote
getParameter() : string: Get parameter placeholder value
getParameterCount() : int: Get parameter count
getPlaceholder() : string|null: Get the SQL placeholder
hasDelete() : bool: Determine if SQL object has a delete object
hasInsert() : bool: Determine if SQL object has a insert object
hasSelect() : bool: Determine if SQL object has a select object
hasUpdate() : bool: Determine if SQL object has a update object
incrementParameterCount() : AbstractSql: Increment parameter count
insert() : Insert|null: Access the insert object
isMysql() : bool: Determine if the DB type is MySQL
isParameter() : bool: Check if value is parameter placeholder
isPgsql() : bool: Determine if the DB type is PostgreSQL
isSqlite() : bool: Determine if the DB type is SQLite
isSqlsrv() : bool: Determine if the DB type is SQL Server
isSupportedFunction() : bool: Check if value contains a standard SQL supported function
isSupportedFunctionCall() : bool: Check if the value is a single, simple call to a standard SQL supported function, e.g. 'COUNT(*)', 'SUM(total)' or 'MAX(users.id)'
jsonExtract() : JsonExtract: Create a JSON path extraction expression, usable as a SELECT column value or an orderBy() argument
quote() : float|int|string: Quote the value (if it is not a numeric value)
quoteId() : string: Quote the identifier
render() : string: Render the SQL statement
reset() : Sql: Reset and clear the SQL object
select() : Select|null: Access the select object
setIdQuoteType() : AbstractSql: Set the quote ID type
setPlaceholder() : AbstractSql: Set the placeholder
update() : Update|null: Access the update object
init() : void: Initialize SQL object
initQuoteType() : void: Initialize quite type

BACKTICK

Constants for id quote types


    public
        mixed
    BACKTICK
    = 'BACKTICK'

BRACKET


    public
        mixed
    BRACKET
    = 'BRACKET'

DOUBLE_QUOTE


    public
        mixed
    DOUBLE_QUOTE
    = 'DOUBLE_QUOTE'

MYSQL

Constants for database types


    public
        mixed
    MYSQL
    = 'MYSQL'

NO_QUOTE


    public
        mixed
    NO_QUOTE
    = 'NO_QUOTE'

PGSQL


    public
        mixed
    PGSQL
    = 'PGSQL'

SQLITE


    public
        mixed
    SQLITE
    = 'SQLITE'

SQLSRV


    public
        mixed
    SQLSRV
    = 'SQLSRV'

$aggregateFunctions

Supported standard SQL aggregate functions


        protected
        static    array<string|int, mixed>
    $aggregateFunctions
     = ['AVG', 'COUNT', 'MAX', 'MIN', 'SUM']

$closeQuote

ID close quote


        protected
            string|null
    $closeQuote
     = null

$dateTimeFunctions

Supported standard SQL date-time functions


        protected
        static    array<string|int, mixed>
    $dateTimeFunctions
     = ['CURRENT_DATE', 'CURRENT_TIMESTAMP', 'CURRENT_TIME', 'CURDATE', 'CURTIME', 'DATE', 'DATETIME', 'DAY', 'EXTRACT', 'GETDATE', 'HOUR', 'LOCALTIME', 'LOCALTIMESTAMP', 'MINUTE', 'MONTH', 'NOW', 'SECOND', 'TIME', 'TIMEDIFF', 'TIMESTAMP', 'UNIX_TIMESTAMP', 'YEAR']

$db

Database object


        protected
            AbstractAdapter|null
    $db
     = null

$dbType

Database type


        protected
            string|null
    $dbType
     = null

$delete

Delete object


        protected
            Delete|null
    $delete
     = null

$idQuoteType

ID quote type


        protected
            string
    $idQuoteType
     = 'NO_QUOTE'

$insert

Insert object


        protected
            Insert|null
    $insert
     = null

$mathFunctions

Supported standard SQL math functions


        protected
        static    array<string|int, mixed>
    $mathFunctions
     = ['ABS', 'RAND', 'SQRT', 'POW', 'POWER', 'EXP', 'LN', 'LOG', 'LOG10', 'GREATEST', 'LEAST', 'DIV', 'MOD', 'ROUND', 'TRUNC', 'CEIL', 'CEILING', 'FLOOR', 'COS', 'ACOS', 'ACOSH', 'SIN', 'SINH', 'ASIN', 'ASINH', 'TAN', 'TANH', 'ATANH', 'ATAN2']

$openQuote

ID open quote


        protected
            string|null
    $openQuote
     = null

$parameterCount

Parameter count


        protected
            int
    $parameterCount
     = 0

$placeholder

SQL placeholder


        protected
            string|null
    $placeholder
     = null

$select

Select object


        protected
            Select|null
    $select
     = null

$stringFunctions

Supported standard SQL string functions


        protected
        static    array<string|int, mixed>
    $stringFunctions
     = ['CONCAT', 'FORMAT', 'INSTR', 'LCASE', 'LEFT', 'LENGTH', 'LOCATE', 'LOWER', 'LPAD', 'LTRIM', 'POSITION', 'QUOTE', 'REGEXP', 'REPEAT', 'REPLACE', 'REVERSE', 'RIGHT', 'RPAD', 'RTRIM', 'SPACE', 'STRCMP', 'SUBSTRING', 'SUBSTR', 'TRIM', 'UCASE', 'UPPER']

$update

Update object


        protected
            Update|null
    $update
     = null

__construct()

Constructor


    public
                    __construct(AbstractAdapter $db) : mixed

Instantiate the SQL object

Parameters

$db : AbstractAdapter

__toString()

Render the SQL statement


    public
                    __toString() : string

Return values

string

db()

Get the current database adapter object (alias method)


    public
                    db() : AbstractAdapter|null

Return values

AbstractAdapter|null

decrementParameterCount()

Decrement parameter count


    public
                    decrementParameterCount() : AbstractSql

Return values

AbstractSql

delete()

Access the delete object


    public
                    delete([string|null $table = null ]) : Delete|null

Parameters

$table : string|null = null

Return values

Delete|null

getCloseQuote()

Get close quote


    public
                    getCloseQuote() : string|null

Return values

string|null

getDb()

Get the current database adapter object


    public
                    getDb() : AbstractAdapter|null

Return values

AbstractAdapter|null

getDbType()

Get the current database type


    public
                    getDbType() : string|null

Return values

string|null

getIdQuoteType()

Get the quote ID type


    public
                    getIdQuoteType() : string

Return values

string

getOpenQuote()

Get open quote


    public
                    getOpenQuote() : string|null

Return values

string|null

getParameter()

Get parameter placeholder value


    public
                    getParameter(mixed $value[, string|null $column = null ]) : string

Parameters

$value : mixed
$column : string|null = null

Return values

string

getParameterCount()

Get parameter count


    public
                    getParameterCount() : int

Return values

int

getPlaceholder()

Get the SQL placeholder


    public
                    getPlaceholder() : string|null

Return values

string|null

hasDelete()

Determine if SQL object has a delete object


    public
                    hasDelete() : bool

Return values

bool

hasInsert()

Determine if SQL object has a insert object


    public
                    hasInsert() : bool

Return values

bool

hasSelect()

Determine if SQL object has a select object


    public
                    hasSelect() : bool

Return values

bool

hasUpdate()

Determine if SQL object has a update object


    public
                    hasUpdate() : bool

Return values

bool

incrementParameterCount()

Increment parameter count


    public
                    incrementParameterCount() : AbstractSql

Return values

AbstractSql

insert()

Access the insert object


    public
                    insert([string|null $table = null ]) : Insert|null

Parameters

$table : string|null = null

Return values

Insert|null

isMysql()

Determine if the DB type is MySQL


    public
                    isMysql() : bool

Return values

bool

isParameter()

Check if value is parameter placeholder


    public
                    isParameter(mixed $value[, string|null $column = null ]) : bool

Parameters

$value : mixed
$column : string|null = null

Return values

bool

isPgsql()

Determine if the DB type is PostgreSQL


    public
                    isPgsql() : bool

Return values

bool

isSqlite()

Determine if the DB type is SQLite


    public
                    isSqlite() : bool

Return values

bool

isSqlsrv()

Determine if the DB type is SQL Server


    public
                    isSqlsrv() : bool

Return values

bool

isSupportedFunction()

Check if value contains a standard SQL supported function


    public
            static        isSupportedFunction(mixed $value) : bool

Parameters

$value : mixed

Return values

bool

isSupportedFunctionCall()

Check if the value is a single, simple call to a standard SQL supported function, e.g. 'COUNT(*)', 'SUM(total)' or 'MAX(users.id)'


    public
            static        isSupportedFunctionCall(mixed $value) : bool

The argument list is deliberately restricted to identifier characters, digits, '*', '.', ',' and whitespace, and the whole value must be nothing but that one call. That keeps anything that could carry additional SQL (quotes, operators, comment markers, a nested statement) out of the "render me verbatim" path.

Parameters

$value : mixed

Return values

bool

jsonExtract()

Create a JSON path extraction expression, usable as a SELECT column value or an orderBy() argument


    public
                    jsonExtract(string $column, string $path) : JsonExtract

Parameters

$column : string
$path : string

Return values

JsonExtract

quote()

Quote the value (if it is not a numeric value)


    public
                    quote([mixed $value = null ][, bool $force = false ]) : float|int|string

Parameters

$value : mixed = null
$force : bool = false

Return values

float|int|string

quoteId()

Quote the identifier


    public
                    quoteId(string $identifier) : string

A supported SQL function call is an expression rather than an identifier, so it is passed through untouched - quoting it would produce a bogus identifier such as COUNT(*), which errors on MySQL/PostgreSQL and silently matches nothing on SQLite.

Parameters

$identifier : string

Return values

string

render()

Render the SQL statement


    public
                    render() : string

Rendering is side effect free: the clause object that produced the string is left in place, so the same object can be rendered again (logged and then executed, inspected while debugging, echoed by a caller it was returned to). Use reset() to explicitly clear the object before building an unrelated statement with it.

Return values

string

reset()

Reset and clear the SQL object


    public
                    reset() : Sql

Return values

Sql

select()

Access the select object


    public
                    select([mixed $columns = null ]) : Select|null

Parameters

$columns : mixed = null

Return values

Select|null

setIdQuoteType()

Set the quote ID type


    public
                    setIdQuoteType([string $type = self::NO_QUOTE ]) : AbstractSql

Parameters

$type : string = self::NO_QUOTE

Return values

AbstractSql

setPlaceholder()

Set the placeholder


    public
                    setPlaceholder(string $placeholder) : AbstractSql

Parameters

$placeholder : string

Return values

AbstractSql

update()

Access the update object


    public
                    update([string|null $table = null ]) : Update|null

Parameters

$table : string|null = null

Return values

Update|null

init()

Initialize SQL object


    protected
                    init(string $adapter) : void

Parameters

$adapter : string

initQuoteType()

Initialize quite type


    protected
                    initQuoteType() : void

Sql extends AbstractSql in package Pop Db

Tags

Table of Contents

Constants

Properties

Methods

Constants

BACKTICK

BRACKET

DOUBLE_QUOTE

MYSQL

NO_QUOTE

PGSQL

SQLITE

SQLSRV

Properties

$aggregateFunctions

$closeQuote

$dateTimeFunctions

$db

$dbType

$delete

$idQuoteType

$insert

$mathFunctions

$openQuote

$parameterCount

$placeholder

$select

$stringFunctions

$update

Methods

__construct()

Parameters

__toString()

Return values

db()

Return values

decrementParameterCount()

Return values

delete()

Parameters

Return values

getCloseQuote()

Return values

getDb()

Return values

getDbType()

Return values

getIdQuoteType()

Return values

getOpenQuote()

Return values

getParameter()

Parameters

Return values

getParameterCount()

Return values

getPlaceholder()

Return values

hasDelete()

Return values

hasInsert()

Return values

hasSelect()

Return values

hasUpdate()

Return values

incrementParameterCount()

Return values

insert()

Parameters

Return values

isMysql()

Return values

isParameter()

Parameters

Return values

isPgsql()

Return values

Sql extends AbstractSql
in package

Pop

Db