SQL Data Access API

Table of contents:

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

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).

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

• configuration: setting up an XML file where SQL vendors used by your site are configured per development environment
• execution: using Lucinda\SQL\Wrapper to read above XML based on development environment, compile Lucinda\SQL\DataSource object(s) storing connection information and inject them statically into Lucinda\SQL\ConnectionFactory classes to use in querying

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:

• statement: returns a Lucinda\SQL\Statement object to use in creation and execution of a sql statement
• preparedStatement: returns a Lucinda\SQL\PreparedStatement object to use in creation and execution of a sql prepared statement
• transaction: returns a Lucinda\SQL\Transaction object to use in wrapping operations with above two in transactions

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):

Following methods are relevant for querying:

Class ConnectionFactory

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

^ 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:

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:

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:

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:

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