lucinda/queries

API that generates SQL statements programmatically based on official standards or their vendor-specific derivation

Maintainers

Details

github.com/aherne/php-sql-statements-api

Homepage

Source

Issues

Installs: 19 900

Dependents: 0

Suggesters: 0

Security: 0

Stars: 1

Watchers: 2

Forks: 1

Open Issues: 0

v3.0.3 2022-06-11 15:04 UTC

Requires

  • php: ^8.1

Requires (Dev)

MIT 8b7148f1ba9d60fd7e0fd40b67ff8acf84468ed9

  • Lucian Popescu <lucian.gabriel.popescu.woop@gmail.com>

sqlquerygeneration

This package is auto-updated.

Last update: 2024-11-11 20:26:31 UTC

README

Table of contents:

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:
  • TRUNCATE:

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

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:

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:

Class SelectGroup

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

Class Insert

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

Class InsertSelect

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

Class Update

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

Class Delete

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

Class Truncate

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

Class MySQL Select

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

In addition of above operations, where method can use:

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:

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:

Class MySQL InsertSet

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

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:

Class MySQL Update

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

In addition of above operations, where method can use:

Class MySQL Delete

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

In addition of above operations, where method can use: