documentSQL Semantics

SQL Semantics

License: MIT Docs PHP Version Ask DeepWiki

SQL Semantics is the semantic phase of a database front end for MySQL, PostgreSQL, and SQLite. It turns any statement of the shipped grammars into an immutable, typed statement model that writes the SQL back, and it binds SELECT statements against a schema built from CREATE TABLE statements, resolving names, types, conservative NULL facts, and the relation occurrences each value comes from. No database connection is needed. This package is the shared runtime; install it through the package of your database.

Requirements

• PHP 8.1+ with the zlib extension

Support Syntax

The following grammar versions are supported. Pass the dialect of your database package and, optionally, the version tag to Semantics or Schema; omitting the version tag uses the default for that database. State declarations support all listed versions; SELECT binding with Binder requires MySQL 8.0 or later.

MySQL

PostgreSQL

SQLite

Installation

Install the package of your database; it installs this runtime.

MySQL:

composer require k-kinzal/sql-semantics-mysql

PostgreSQL:

composer require k-kinzal/sql-semantics-postgres

SQLite:

composer require k-kinzal/sql-semantics-sqlite

Each package provides its dialect: SqlSemantics\Platform\MySql\Dialect::MySql, SqlSemantics\Platform\PostgreSql\Dialect::PostgreSql, or SqlSemantics\Platform\Sqlite\Dialect::Sqlite.

Usage

use SqlSemantics\Facade\Semantics;
use SqlSemantics\Platform\PostgreSql\Dialect;

$statement = (new Semantics(Dialect::PostgreSql))->analyze(<<<'SQL'
WITH changed AS (
    UPDATE accounts SET balance = balance + 10 WHERE id = 7 RETURNING id, balance
)
SELECT id, balance FROM changed;
SQL);

$statement->command;    // the typed model of the statement
$statement->toString(); // 'WITH changed AS( UPDATE accounts SET balance = balance + 10 WHERE id = 7 RETURNING id , balance ) SELECT id , balance FROM changed ;'

Update a SQLite WHERE clause with structured values while keeping the original statement:

use SqlSemantics\Platform\Sqlite\Dialect as SqliteDialect;
use SqlSemantics\Statement\Model\Sqlite\Value\EcmdWithCmdxSemi_b7577a8f as CommandEnvelope;
use SqlSemantics\Statement\Model\Sqlite\Value\ExprWithExprEqNeExpr_49d16f16 as Comparison;
use SqlSemantics\Statement\Model\Sqlite\Value\ExprWithIdj_e1794d68 as Field;
use SqlSemantics\Statement\Model\Sqlite\Value\OneselectWithSelectDistinctSelcollistFromWhereOptGroupbyOptHavingOptOrderbyOptLimitOpt_218e0475 as Select;
use SqlSemantics\Statement\Model\Sqlite\Value\TermWithInteger_298801b2 as IntegerValue;
use SqlSemantics\Statement\Model\Sqlite\Value\WhereOptWithWhereExpr_93445e09 as Where;

$original = (new Semantics(SqliteDialect::Sqlite))->analyze('SELECT foo FROM items');
$command = $original->command;

if ($command instanceof CommandEnvelope && $command->cmdx instanceof Select) {
    $where = new Where(new Comparison(new Field('foo'), '=', new IntegerValue('1')));
    $select = $command->cmdx->withWhere($where);
    $updated = $original->withCommand($command->withCmdx($select));

    $original->toString(); // 'SELECT foo FROM items'
    $updated->toString();  // 'SELECT foo FROM items WHERE foo = 1'
}

See statement models for building statements without SQL, and schema binding for names, types, and NULL facts.

License

MIT License. See LICENSE for details.