Download the PHP package lucinda/sql-data-access without Composer

Configuration

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

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:

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:

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:

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

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

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:

UPDATE/DELETE

Example of processing results of an UPDATE/DELETE query:

SELECT

Example of getting a single value from SELECT resultset:

Example of parsing SELECT resultset row by row:

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

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

Example of getting all values from SELECT resultset:

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:

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:

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:

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:

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!