lucinda/sql-data-access

Abstracts all communication with SQL servers on top of PDO

v3.0.4.2 2020-01-19 13:30 UTC

README

Documentation below refers to latest API version, available in branch v3.0.0! For older version in master branch, please check Lucinda Framework.

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:

API is fully PSR-4 compliant, only requiring PHP7.1+ interpreter and SimpleXML extension. 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 deep example of API functionality based on unit tests

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 sql server 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.

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>

Initialization

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:

Both classes above insure a single Lucinda\SQL\Connection is reused per server throughout session (input-output request flow) duration.

ConnectionSingleton

Lucinda\SQL\ConnectionSingleton defines following public methods:

Method Arguments Returns Description
static setDataSource Lucinda\SQL\DataSource void Sets data source detected beforehand. Done automatically by API!
static getInstance void 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!
__destruct void void Automatically closes connection when it becomes idle. Done automatically by API!

Usage example:

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

ConnectionFactory

Lucinda\SQL\ConnectionFactory defines following public 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!
__destruct void void Automatically closes each connection when it becomes idle. Done automatically by API!

Usage example:

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

Connection

Now that a Lucinda\SQL\Connection is retrieved, you are able to query database via following public methods:

Following methods are relevant to connection management:

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.

Querying

Using Lucinda\SQL\Statement, Lucinda\SQL\PreparedStatement and Lucinda\SQL\Transaction objects returned by statement, preparedStatement and transaction methods of Lucinda\SQL\Connection object, users are finally able to execute queries, wrapping them in transactions if needed, then process execution results using Lucinda\SQL\StatementResults.

Statement

Lucinda\SQL\Statement runs normal SQL unprepared statements 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\ConnectionSingleton::getInstance();
$statement = $connection->statement();
$resultSet = $statement->execute("SELECT id FROM users WHERE name='".$statement->quote($name)."'");

PreparedStatement

Lucinda\SQL\PreparedStatement runs SQL prepared statements 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\ConnectionSingleton::getInstance();
$preparedStatement = $connection->preparedStatement();
$preparedStatement->prepare("SELECT id FROM users WHERE name=:name");
$preparedStatement->bind(":name", $name);
$resultSet = $preparedStatement->execute();

Transaction

Lucinda\SQL\Transaction can wrap execute methods 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\ConnectionSingleton::getInstance();
$transaction = $connection->transaction();
$transaction->begin();
$connection->statement()->execute("UPDATE users SET name='John Doe' WHERE id=1");
$transaction->commit();

Processing

Once an SQL statement was executed via execute methods above, users are able to process results based on Lucinda\SQL\StatementResults object returned. This class 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 Gets current row from resultset as column-value associative array following SELECT statement execution.
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 string $columnKeyName, string $columnValueName 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!

INSERT

Example of processing results of an INSERT query:

$connection = Lucinda\SQL\ConnectionSingleton::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\ConnectionSingleton::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\ConnectionSingleton::getInstance();
$firstName = $connection->statement("SELECT first_name FROM users WHERE id=1")->toValue();

Example of parsing SELECT resultset row by row:

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

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\ConnectionSingleton::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