SQL Statements API

Table of contents:

• About
• Installation
• Unit Tests
• Examples
• Reference Guide

About

The purpose of this API is to automate generation of SQL statements (queries) based on SQL standards or their vendor-specific derivation. API is fully PSR-4 compliant, only requiring PHP 8.1+ interpreter. To quickly see how it works, check:

• installation: describes how to install API on your computer
• unit tests: API has 100% Unit Test coverage, using UnitTest API instead of PHPUnit for greater flexibility
• examples: shows a example of API functionality

All classes inside use Lucinda\Query namespace!

Installation

To install this api, you only need to go to your project root then run this command from console:

composer require lucinda/queries

Once you have it installed, you're able to generate queries. Each standard SQL statement corresponds to one or more classes:

• SELECT:
  • Lucinda\Query\Select: encapsulates a single SELECT statement (eg: SELECT id FROM table)
  • Lucinda\Query\SelectGroup: encapsulates a group of SELECT statements united by a SET operator (eg: (SELECT id from table1) UNION (SELECT id FROM table2))
• INSERT:
  • Lucinda\Query\Insert: encapsulates an INSERT INTO ... VALUES statement (eg: INSERT INTO table (id, name) VALUES (1, 'asd'))
  • Lucinda\Query\InsertSelect: encapsulates an INSERT INTO ... SELECT statement (eg: INSERT INTO table (id, name) SELECT id, name FROM table2)
• UPDATE:
  • Lucinda\Query\Update: encapsulates an UPDATE statement (eg: UPDATE users SET name='Lucian' WHERE id=18)
• DELETE:
  • Lucinda\Query\Delete: encapsulates a DELETE statement (eg: DELETE FROM users WHERE id=18)
• TRUNCATE:
  • Lucinda\Query\Truncate: encapsulates a TRUNCATE statement (eg: TRUNCATE TABLE users)

For each vendor implementing SQL standards, you can either use above or their vendor-specific derivations. MySQL vendor is already supported:

• SELECT:
  • Lucinda\Query\Vendor\MySQL\Select: extends Lucinda\Query\Select in order to support vendor-specific operations (eg: SQL_NO_CACHE)
• INSERT:
  • Lucinda\Query\Vendor\MySQL\Insert: extends Lucinda\Query\Insert in order to support vendor-specific operations (eg: IGNORE)
  • Lucinda\Query\Vendor\MySQL\InsertSelect: extends Lucinda\Query\InsertSelect in order to support vendor-specific operations (eg: IGNORE)
  • Lucinda\Query\Vendor\MySQL\InsertSet: encapsulates vendor-specific statement INSERT INTO ... SET statement (eg: INSERT INTO table (id, name) SET id=1, name='Lucian')
• REPLACE:
  • Lucinda\Query\Vendor\MySQL\Replace: extends Lucinda\Query\Insert in order to support vendor-specific REPLACE INTO ... VALUES statement
  • Lucinda\Query\Vendor\MySQL\ReplaceSelect: extends Lucinda\Query\InsertSelect in order to support vendor-specific REPLACE INTO ... SELECT statement
  • Lucinda\Query\Vendor\MySQL\ReplaceSet: encapsulates vendor-specific statement REPLACE INTO ... SET statement (eg: REPLACE INTO table (id, name) SET id=1, name='Lucian')
• UPDATE
  • Lucinda\Query\Vendor\MySQL\Update: extends Lucinda\Query\Update in order to support vendor-specific operations (eg: IGNORE)
• DELETE
  • Lucinda\Query\Vendor\MySQL\Delete: extends Lucinda\Query\Delete in order to support vendor-specific operations (eg: IGNORE)

Each of above or clauses they individually call to implement \Stringable, which manages conversion of statement/clause into SQL via __toString() method.

Unit Tests

For tests and examples, check following files/folders in API sources:

• test.php: runs unit tests in console
• unit-tests.xml: sets up unit tests
• tests: unit tests for classes from src folder
• tests_drivers: unit tests for classes from drivers folder

NOTICE: super-global functions __toString() (used by statements and clauses) were tested via toString() unit test methods. By themselves they cannot be mirrored by a unit test (due to string instead of Result return type), so they will give: Invalid unit test response

Examples

To see examples how each classes are used, check unit tests in tests or tests_drivers folder! Simple example:

$statement = new \Lucinda\Query\Select("users", "t1");
$statement->fields(["t3.name"]);
$statement->joinInner("user_departments", "t2")->on(["t1.id"=>"t2.user_id"]);
$statement->joinInner("departments", "t3")->on(["t2.department_id"=>"t3.id"]);
$statement->where(["t1.id"=>":id"]);
$statement->orderBy(["t3.name"]);

Encapsulating:

SELECT t3.name
FROM users AS t1
INNER JOIN user_departments AS t2 ON t1.id = t2.user_id
INNER JOIN departments AS t3 ON t2.department_id = t3.id
WHERE t1.id = :id
ORDER BY t3.name

Reference Guide

Class Select

Lucinda\Query\Select encapsulates a standard SELECT statement via following public methods:

Method	Arguments	Returns	Description
__construct	string $table, string $alias=""	void	Constructs a SELECT statement based on table name and optional alias
distinct	void	void	Sets statement as DISTINCT, filtering out repeating rows
fields	array $columns = []	Lucinda\Query\Clause\Fields	Sets fields or columns to select
joinLeft	string $tableName, string $tableAlias = ""	Lucinda\Query\Clause\Join	Adds a LEFT JOIN statement
joinRight	string $tableName, string $tableAlias = ""	Lucinda\Query\Clause\Join	Adds a RIGHT JOIN statement
joinInner	string $tableName, string $tableAlias = ""	Lucinda\Query\Clause\Join	Adds a INNER JOIN statement
joinCross	string $tableName, string $tableAlias = ""	Lucinda\Query\Clause\Join	Adds a CROSS JOIN statement
where	array $condition=[], Lucinda\Query\Operator\Logical $logicalOperator = Lucinda\Query\Operator\Logical::AND	Lucinda\Query\Clause\Condition	Sets up WHERE clause.
groupBy	array $columns = []	Lucinda\Query\Clause\Columns	Sets up GROUP BY statement
having	array $condition=[], Lucinda\Query\Operator\Logical $logicalOperator = Lucinda\Query\Operator\Logical::AND	Lucinda\Query\Clause\Condition	Sets up HAVING clause.
orderBy	array $fields = []	Lucinda\Query\Clause\OrderBy	Sets up ORDER BY clause
limit	int $limit, int $offset=0	void	Sets a LIMIT clause
__toString	void	string	Converts object to SQL statement.

Class SelectGroup

Lucinda\Query\SelectGroup encapsulates a list of SELECT statements joined by a SET operator (eg: UNION) via following public methods:

Method	Arguments	Returns	Description
__construct	Lucinda\Query\Operator\Set $operator = Lucinda\Query\Operator\Set::UNION	void	Constructs a SELECT ... OPERATOR ... SELECT statement based on Set OPERATOR
addSelect	Lucinda\Query\Select $select	void	Adds SELECT statement to group
addSelect	Lucinda\Query\SelectGroup $select	void	Adds SELECT ... OPERATOR ... SELECT statement to group
orderBy	array $fields = []	Lucinda\Query\Clause\OrderBy	Sets up ORDER BY clause
limit	int $limit, int $offset=0	void	Sets a LIMIT clause
__toString	void	string	Converts object to SQL statement.

Class Insert

Lucinda\Query\Insert encapsulates a standard INSERT INTO VALUES statement via following public methods:

Method	Arguments	Returns	Description
__construct	string $table	void	Constructs a INSERT INTO ... VALUES statement based on table name
columns	array $columns = []	Lucinda\Query\Clause\Columns	Sets columns that will be inserted into.
values	array $updates = []	Lucinda\Query\Clause\Row	Adds row to table via list of values to insert in columns
__toString	void	string	Converts object to SQL statement.

Class InsertSelect

Lucinda\Query\InsertSelect encapsulates a standard INSERT INTO SELECT statement via following public methods:

Method	Arguments	Returns	Description
__construct	string $table	void	Constructs a INSERT INTO ... SELECT statement based on table name
columns	array $columns = []	Lucinda\Query\Clause\Columns	Sets columns that will be inserted into.
select	Lucinda\Query\Select $select	void	Sets rows to insert based on a SELECT statement
select	Lucinda\Query\SelectGroup $select	void	Sets rows to insert based on a SELECT ... OPERATOR ... SELECT group statement
__toString	void	string	Converts object to SQL statement.

Class Update

Lucinda\Query\Update encapsulates a standard UPDATE statement via following public methods:

Method	Arguments	Returns	Description
__construct	string $table	void	Constructs a UPDATE statement based on table name
set	array $contents = []	Lucinda\Query\Clause\Set	Sets up SET clause.
where	array $condition = [], Lucinda\Query\Operator\Logical $logicalOperator = Lucinda\Query\Operator\Logical::AND	Lucinda\Query\Clause\Condition	Sets up WHERE clause.
__toString	void	string	Converts object to SQL statement.

Class Delete

Lucinda\Query\Delete encapsulates a standard DELETE statement via following public methods:

Method	Arguments	Returns	Description
__construct	string $table	void	Constructs a DELETE statement based on table name
where	array $condition=[], Lucinda\Query\Operator\Logical $logicalOperator = Lucinda\Query\Operator\Logical::AND	Lucinda\Query\Clause\Condition	Sets up WHERE clause.
__toString	void	string	Converts object to SQL statement.

Class Truncate

Lucinda\Query\Truncate encapsulates a standard TRUNCATE statement via following public methods:

Method	Arguments	Returns	Description
__construct	string $table	void	Constructs a TRUNCATE statement based on table name
__toString	void	string	Converts object to SQL statement.

Class MySQL Select

Lucinda\Query\Vendor\MySQL\Select encapsulates a MySQL SELECT statement on top of Lucinda\Query\Select via following extra methods:

Method	Arguments	Returns	Description
setCalcFoundRows	void	void	Appends a SQL_CALC_FOUND_ROWS option to SELECT
setStraightJoin	void	void	Appends a STRAIGHT_JOIN option to SELECT
getCalcFoundRows	void	string	Gets query to retrieve found rows after a SELECT with SQL_CALC_FOUND_ROWS has ran

In addition of above operations, where method can use:

• Lucinda\Query\Vendor\MySQL\Clause\Condition to support regexp condition and fulltext searches
• Lucinda\Query\Vendor\MySQL\Operator\Logical to support XOR operator

Class MySQL Insert

Lucinda\Query\Vendor\MySQL\Insert encapsulates a MySQL INSERT INTO VALUES statement on top of Lucinda\Query\Insert via following extra methods:

Method	Arguments	Returns	Description
ignore	void	void	Sets statement as IGNORE, ignoring foreign key errors and duplicates
onDuplicateKeyUpdate	array $contents = []	Lucinda\Query\Clause\Set	Sets up ON DUPLICATE KEY UPDATE clause.

Class MySQL InsertSelect

Lucinda\Query\Vendor\MySQL\InsertSelect encapsulates a MySQL INSERT INTO SELECT statement on top of Lucinda\Query\InsertSelect via following extra methods:

Method	Arguments	Returns	Description
ignore	void	void	Sets statement as IGNORE, ignoring foreign key errors and duplicates
onDuplicateKeyUpdate	array $contents = []	Lucinda\Query\Clause\Set	Sets up ON DUPLICATE KEY UPDATE clause.

Class MySQL InsertSet

Lucinda\Query\Vendor\MySQL\InsertSet encapsulates a MySQL INSERT INTO SET statement via following public methods:

Method	Arguments	Returns	Description
__construct	string $table	void	Constructs a INSERT INTO ... SET statement based on table name
ignore	void	void	Sets statement as IGNORE, ignoring foreign key errors and duplicates
set	array $contents = []	Lucinda\Query\Clause\Set	Sets up SET clause.
onDuplicateKeyUpdate	array $contents = []	Lucinda\Query\Clause\Set	Sets up ON DUPLICATE KEY UPDATE clause.
__toString	void	string	Converts object to SQL statement.

Class MySQL Replace

Lucinda\Query\Vendor\MySQL\Replace encapsulates a MySQL REPLACE INTO VALUES statement on top of Lucinda\Query\Insert with no extra methods, except that INSERT will have REPLACE instead.

Class MySQL ReplaceSelect

Lucinda\Query\Vendor\MySQL\ReplaceSelect encapsulates a MySQL REPLACE INTO SELECT statement on top of Lucinda\Query\InsertSelect with no extra methods, except that INSERT will have REPLACE instead.

Class MySQL ReplaceSet

Lucinda\Query\Vendor\MySQL\ReplaceSet encapsulates a MySQL REPLACE INTO SET statement via following public methods:

Method	Arguments	Returns	Description
__construct	string $table	void	Constructs a REPLACE INTO ... SET statement based on table name
set	array $contents = []	Lucinda\Query\Clause\Set	Sets up SET clause.
__toString	void	string	Converts object to SQL statement.

Class MySQL Update

Lucinda\Query\Vendor\MySQL\Update encapsulates a MySQL UPDATE statement on top of Lucinda\Query\Update via following extra methods:

Method	Arguments	Returns	Description
ignore	void	void	Sets statement as IGNORE, ignoring foreign key errors and duplicates

In addition of above operations, where method can use:

• Lucinda\Query\Vendor\MySQL\Clause\Condition to support regexp condition and fulltext searches
• Lucinda\Query\Vendor\MySQL\Operator\Logical to support XOR operator

Class MySQL Delete

Lucinda\Query\Vendor\MySQL\Delete encapsulates a MySQL DELETE statement on top of Lucinda\Query\Delete via following extra methods:

Method	Arguments	Returns	Description
ignore	void	void	Sets statement as IGNORE, ignoring foreign key errors and duplicates

In addition of above operations, where method can use:

• Lucinda\Query\Vendor\MySQL\Clause\Condition to support regexp condition and fulltext searches
• Lucinda\Query\Vendor\MySQL\Operator\Logical to support XOR operator