README

SQL Semantics is the semantic phase of a database front end for MySQL, PostgreSQL, and SQLite. It reads SQL as the server reads it: with the grammar of one release, under the session settings that change tokenization, and with the parameter markers the statement was written for. Every statement of the shipped grammars becomes an immutable, typed statement model that writes the SQL back, can be walked and rewritten without naming its classes, and can be composed from PHP values under stable names. A statement analyzed with the statements it depends on, the declarations that came before it, also resolves every table name it writes. 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; omitting the version tag uses the default for that database. Common table expressions in Builder need MySQL 8.0 or later.

MySQL

Version	Version tag	Default
5.6.51	`mysql-5.6.51`
5.7.44	`mysql-5.7.44`
8.0.44	`mysql-8.0.44`
8.1.0	`mysql-8.1.0`
8.2.0	`mysql-8.2.0`
8.3.0	`mysql-8.3.0`
8.4.7	`mysql-8.4.7`	Yes
9.0.1	`mysql-9.0.1`
9.1.0	`mysql-9.1.0`

PostgreSQL

Version	Version tag	Default
16.6	`pg-16.6`
17.2	`pg-17.2`	Yes

SQLite

Version	Version tag	Default
3.47.2	`sqlite-3.47.2`	Yes

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 ;'

A statement means something against the statements before it. Pass those as its dependencies, and every table name resolves to a table one of them declares, to a common table expression visible where it is written, or to a table the statement declares or drops itself; a name without a schema is read in the session's search path, such as MySQL's current database, and a name no dependency declares is an error unless the declarations are partial. Without dependencies, a statement is structured only. Pass an empty dependency list and Declarations::Partial to inspect an isolated declaration while retaining undeclared references; see reading declarations and literals.

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

$semantics = new Semantics(Dialect::MySql);
$users = $semantics->analyze('CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(64) NOT NULL)');
$query = $semantics->analyze('WITH recent AS (SELECT id FROM users) SELECT u.name FROM users u JOIN recent ON recent.id = u.id', [$users]);

$query->resolution->tables()[0]->declaration === $users; // true: the reference to users, with the declared columns in ->table
$query->resolution->references[0]->kind;                 // ReferenceKind::CommonTableExpression, for recent
$semantics->analyze('SELECT 1 FROM orders', [$users]);     // throws SemanticException: no dependency declares orders

A MySQL session's sql_mode changes how text is read, and a named placeholder such as :id is not in the server's language. Both are part of the language a Semantics reads:

use SqlSemantics\Core\Parameters;
use SqlSemantics\Facade\Semantics;
use SqlSemantics\Platform\MySql\Dialect;
use SqlSemantics\Platform\MySql\Mode;

$semantics = new Semantics(Dialect::MySql, 'mysql-8.4.7', Mode::fromString('ANSI_QUOTES,NO_BACKSLASH_ESCAPES'), Parameters::Named);

$semantics->analyze('SELECT "name" FROM users WHERE id = :id')->toString(); // "name" is an identifier, :id a parameter
$semantics->split("SELECT 1; CREATE PROCEDURE p() BEGIN SELECT 1; SELECT 2; END; SELECT 3");
// ['SELECT 1;', ' CREATE PROCEDURE p() BEGIN SELECT 1; SELECT 2; END;', ' SELECT 3']

Walk any statement for the values of a role, rewrite it from the leaves up, and compose new values without naming a generated class:

use SqlSemantics\Facade\Semantics;
use SqlSemantics\Platform\Sqlite\Dialect;
use SqlSemantics\Statement\Element;
use SqlSemantics\Statement\Model\Sqlite\Role\NmForm;
use SqlSemantics\Statement\Model\Sqlite\Value\NmWithIdj_a2015ecf as Name;
use SqlSemantics\Statement\Traversal;
use SqlSemantics\Statement\Writer;

$semantics = new Semantics(Dialect::Sqlite);
$statement = $semantics->analyze('SELECT id FROM users WHERE active = 1');

Traversal::find($statement->command, NmForm::class);                // every name in the statement
$rewritten = Traversal::rewrite($statement->command, static fn (Element $value): Element => $value instanceof Name && $value->name === 'users' ? $value->withName('members') : $value);
Writer::render($rewritten);                                         // 'SELECT id FROM members WHERE active = 1'

$builder = $semantics->builder();
$rows = $builder->unionAll(
    $builder->select([[$builder->integer(1), 'id'], [$builder->string('a'), 'name']]),
    $builder->select([[$builder->integer(2), 'id'], [$builder->string('b'), 'name']]),
);
Writer::render($builder->with([$builder->cte('users', $rows)], $statement->command));
// "WITH users AS( SELECT 1 AS id , 'a' AS name UNION ALL SELECT 2 AS id , 'b' AS name ) SELECT id FROM users WHERE active = 1"
Writer::render($builder->compare($builder->column('select'), '=', $builder->string("it's")));
// "\"select\" = 'it''s'"

See statement models for the models, traversal, comments, and statement boundaries; dependencies for declarations and what table names resolve to; composition for building values from PHP data; and declarations and literals for partial resolution, standalone types, effective numeric sizes, and literal decoding.

License

MIT License. See LICENSE for details.

k-kinzal / sql-semantics

Package info

Security

Statistics