lucinda/sql-data-access

API abstracting communication with SQL providers (eg: MySQL) on top of PDO inspired by Java JDBC

Maintainers

Details

github.com/aherne/php-sql-data-access-api

Homepage

Source

Issues

Installs: 18 984

Dependents: 2

Suggesters: 0

Security: 0

Stars: 0

Watchers: 3

Forks: 1

Open Issues: 0

v4.1.5 2022-12-26 05:52 UTC

Requires

  • php: ^8.1
  • ext-pdo: *
  • ext-simplexml: *

Requires (Dev)

MIT 53bc4412d1d820d93cc2dd1e3b9d26cbb52cefdf

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

sqlprocessingdata accessquerying

This package is auto-updated.

Last update: 2024-03-26 08:39:35 UTC

README

Table of contents:

About

This API is a ultra light weight Data Access Layer built on top of PDO and inspired by JDBC in terms of architecture. As a data access layer, its purpose is to to shield complexity of working with different SQL vendors and provide a simple and elegant interface for connecting, querying and parsing query results that overcomes PDO design flaws (such as chaotic architecture and functionality).

diagram

The whole idea of working with SQL databases (vendors) is reduced to following steps:

API is fully PSR-4 compliant, only requiring PHP8.1+ interpreter, SimpleXML and PDO extensions. To quickly see how it works, check:

  • installation: describes how to install API on your computer, in light of steps above
  • unit tests: API has 100% Unit Test coverage, using UnitTest API instead of PHPUnit for greater flexibility
  • examples: shows a number of examples in how to implement CRUD queries using this API

Configuration

To configure this API you must have a XML with a sql tag inside:

<sql>
	<{ENVIRONMENT}>
		<server name="..." driver="..." host="..." port="..." username="..." password="..." schema="..." charset="..."/>
		...
	</{ENVIRONMENT}>
	...
</sql>

Where:

  • sql: holds global connection information for SQL servers used
    • {ENVIRONMENT}: name of development environment (to be replaced with "local", "dev", "live", etc)
      • server: stores connection information about a single server via attributes:
        • name: (optional) unique identifier. Required if multiple sql servers are used for same environment!
        • driver: (mandatory) PDO driver name (pdo drivers)
        • host: (mandatory) server host name.
        • port: (optional) server port. If not set, default server port is used.
        • username: (mandatory) user name to use in connection.
        • password: (mandatory) password to use in connection.
        • schema: (optional) default schema to use after connecting.
        • charset: (optional) default charset to use in queries after connecting.
        • autocommit: (not recommended) whether or not INSERT/UPDATE operations should be auto-committed (value can be: 0 or 1). Not supported by all vendors!
        • persistent: (not recommended) whether or not connections should be persisted across sections (value can be: 0 or 1). Not supported by all vendors!
        • timeout: (not recommended) time in seconds by which idle connection is automatically closed. Not supported by all vendors!

Example:

<sql>
    <local>
        <server driver="mysql" host="localhost" port="3306" username="root" password="" schema="example" charset="utf8"/>
    </local>
    <live>
        <server driver="mysql" host="localhost" port="3306" username="hello" password="world" schema="example" charset="utf8"/>
    </live>
</sql>

Execution

Once you have completed step above, you need to run this in order to be able to connect and query database(s) later on:

new Lucinda\SQL\Wrapper(simplexml_load_file(XML_FILE_NAME), DEVELOPMENT_ENVIRONMENT);

This will wrap each server tag found for current development environment into Lucinda\SQL\DataSource objects and inject them statically into Lucinda\SQL\ConnectionFactory class.

Class above insures a single Lucinda\SQL\Connection is reused per server throughout session (input-output request flow) duration. To use that connection in querying, following methods are available:

Once an SQL statement was executed via execute methods above, users are able to process results based on Lucinda\SQL\StatementResults object returned.

Installation

First choose a folder where API will be installed then write this command there using console:

composer require lucinda/sql-data-access

Then create a configuration.xml file holding configuration settings (see configuration above) and a index.php file (see initialization above) in project root with following code:

require(__DIR__."/vendor/autoload.php");
new Lucinda\SQL\Wrapper(simplexml_load_file("configuration.xml"), "local");

Then you are able to query server, as in below example:

$connection = Lucinda\SQL\ConnectionFactory::getInstance("");
$users = $connection->statement("SELECT id, name FROM users")->toMap("id", "name");

Unit Tests

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

  • unit-tests.sql: SQL commands you need to run ONCE on server (assuming MySQL) before unit tests execution
  • test.php: runs unit tests in console
  • unit-tests.xml: sets up unit tests and mocks "sql" tag
  • tests: unit tests for classes from src folder

If you desire to run test.php yourselves, import unit-tests.sql file first!

Examples

INSERT

Example of processing results of an INSERT query:

$connection = Lucinda\SQL\ConnectionFactory::getInstance("");
$resultSet = $connection->statement("INSERT INTO users (first_name, last_name) VALUES ('John', 'Doe')");
$lastInsertID = $resultSet->getInsertId();

UPDATE/DELETE

Example of processing results of an UPDATE/DELETE query:

$connection = Lucinda\SQL\ConnectionFactory::getInstance("");
$resultSet = $connection->statement("UPDATE users SET first_name='Jane' WHERE id=1");
if($resultSet->getAffectedRows()>0) {
    // update occurred
}

SELECT

Example of getting a single value from SELECT resultset:

$connection = Lucinda\SQL\ConnectionFactory::getInstance("");
$firstName = $connection->statement("SELECT first_name FROM users WHERE id=1")->toValue();

Example of parsing SELECT resultset row by row:

$connection = Lucinda\SQL\ConnectionFactory::getInstance("");
$resultSet = $connection->statement("SELECT * FROM users");
while ($row = $resultSet->toRow()) {
    // process row
}

Example of getting all values of first column from SELECT resultset:

$connection = Lucinda\SQL\ConnectionFactory::getInstance("");
$ids = $connection->statement("SELECT id FROM users")->toColumn();

Example of getting all rows from SELECT resultset as array where value of first becomes key and value of second becomes value:

$connection = Lucinda\SQL\ConnectionFactory::getInstance("");
$users = $connection->statement("SELECT id, name FROM users")->toMap("id", "name");
// above is an array where id of user becomes key and name becomes value

Example of getting all values from SELECT resultset:

$connection = Lucinda\SQL\ConnectionFactory::getInstance("");
$users = $connection->statement("SELECT * FROM users")->toList();
// above is an array containing all rows, each as column-value associative array

Reference Guide

Class Connection

Lucinda\SQL\Connection can be used to execute operations on a connection.

Following methods are relevant to connection management (HANDLED BY API AUTOMATICALLY, so to be used only in niche situations):

Method Arguments Returns Description
connect Lucinda\SQL\DataSource void Connects to database server based on data source. Throws Lucinda\SQL\ConnectionException if connection fails!
disconnect void void Closes connection to database server.
reconnect void void Closes then opens connection to database server based on stored data source. Throws Lucinda\SQL\ConnectionException if connection fails!
keepAlive void void Restores connection to database server in case it got closed unexpectedly. Throws Lucinda\SQL\ConnectionException if connection fails!

Following methods are relevant for querying:

Method Arguments Returns Description
statement void Lucinda\SQL\Statement Creates a statement to use in querying.
preparedStatement void Lucinda\SQL\PreparedStatement Creates a prepared statement to use in querying.
transaction void Lucinda\SQL\Transaction Creates a transaction wrap above operations with.

Class ConnectionFactory

Lucinda\SQL\ConnectionFactory class insures single Lucinda\SQL\Connection per session and server name. Has following static methods:

Method Arguments Returns Description
static setDataSource string $serverName, Lucinda\SQL\DataSource void Sets data source detected beforehand per value of name attribute @ server tag. Done automatically by API!
static getInstance string $serverName Lucinda\SQL\Connection Connects to server based on above data source ONCE and returns connection for later querying. Throws Lucinda\SQL\ConnectionException if connection fails!

^ if your application uses a single database server per environment and name attribute @ server XML tag isn't set, empty string must be used as server name!

Usage example:

$connection = Lucinda\SQL\ConnectionFactory::getInstance("myServer");
$conection->statement()->execute("UPDATE users SET name='John' WHERE name='Jane'");

Please note this class closes all open connections automatically on destruction!

Class Statement

Lucinda\SQL\Statement implements normal SQL unprepared statement operations and comes with following public methods:

Method Arguments Returns Description
quote mixed $value void Escapes and quotes value against SQL injection.
execute string $query Lucinda\SQL\StatementResults Executes query and returns results. Throws Lucinda\SQL\StatementException if execution fails!

Usage example:

$connection = Lucinda\SQL\ConnectionFactory::getInstance("");
$statement = $connection->statement();
$resultSet = $statement->execute("SELECT id FROM users WHERE name='".$statement->quote($name)."'");

Please note this class closes all open connections automatically on destruction!

Class PreparedStatement

Lucinda\SQL\PreparedStatement implements SQL prepared statement operations and comes with following public methods:

Method Arguments Returns Description
prepare string $query void Prepares query for execution.
bind string $parameter, mixed $value, int $dataType=\PDO::PARAM_STR void Binds parameter to prepared query.
execute array $boundParameters = array() Lucinda\SQL\StatementResults Executes query and returns results. Throws Lucinda\SQL\StatementException if execution fails!

Usage example:

$connection = Lucinda\SQL\ConnectionFactory::getInstance("");
$preparedStatement = $connection->preparedStatement();
$preparedStatement->prepare("SELECT id FROM users WHERE name=:name");
$preparedStatement->bind(":name", $name);
$resultSet = $preparedStatement->execute();

Class Transaction

Lucinda\SQL\Transaction can wrap execute methods of two classes above in transactions, in order to maintain data integrity, and thus comes with following public methods:

Method Arguments Returns Description
begin void void Starts a transaction.
commit void void Commits transaction.
rollback void void Rolls back transaction.

Usage example:

$connection = Lucinda\SQL\ConnectionFactory::getInstance("");
$transaction = $connection->transaction();
$transaction->begin();
$connection->statement()->execute("UPDATE users SET name='John Doe' WHERE id=1");
$transaction->commit();

Class StatementResults

Lucinda\SQL\StatementResults encapsulates patterns of processing results of sql statement execution and comes with following public methods:

Method Arguments Returns Description
getInsertId void int Gets last insert id following INSERT statement execution.
getAffectedRows void int Gets affected rows following UPDATE/DELETE statement execution.
toValue void string Gets value of first column & row in resultset following SELECT statement execution.
toRow void array false
toColumn void array Gets first column in resulting rows following SELECT statement execution.
toMap string $columnKeyName, string $columnValueName array Gets two columns from resulting rows, where value of one becomes key and another as value, following SELECT statement execution.
toList void array Gets all resulting rows, each as column-value associative array, following SELECT statement execution.

Usage examples of above methods can be seen below or in unit tests!