theodorejb/peachy-sql

A small, speedy database abstraction layer for PHP

Installs: 162

Dependents: 0

Stars: 5

Watchers: 2

Language: PHP

v4.0.2 2015-05-11 05:46 UTC

README

Packagist Version Total Downloads License Build Status

PeachySQL is a speedy database abstraction layer with a goal of simplifying the experience of performing common SQL queries and building JSON APIs in PHP. It supports both MySQL (via MySQLi) and SQL Server (via Microsoft's SQLSRV extension) and runs on PHP 5.5+ as well as HHVM.

Installation

To install via Composer, add the following to the composer.json file in your project root:

{
    "require": {
        "theodorejb/peachy-sql": "4.x"
    }
}

Then run composer install and require vendor/autoload.php in your application's bootstrap file.

Usage

Start by instantiating the Mysql or SqlServer class with a database connection, which should be an existing mysqli object or SQLSRV connection resource:

$peachySql = new PeachySQL\Mysql($mysqlConn);

or

$peachySql = new PeachySQL\SqlServer($sqlSrvConn);

After instantiation, arbitrary queries can be executed by passing a SQL string and array of bound parameters to the query method:

$sql = 'SELECT * FROM Users WHERE fname LIKE ? AND lname LIKE ?';
$result = $peachySql->query($sql, ['theo%', 'b%']);
echo json_encode($result->getAll());

The SqlResult object returned by query has the following methods:

Method Behavior getIterator Returns a Generator object which can be used to iterate over large result sets without caching them in memory. getAll Returns all selected rows as an array of associative arrays. getFirst Returns the first selected row as an associative array (or null if no rows were selected). getAffected Returns the number of rows affected by the query.

If using MySQL, query will return a MysqlResult subclass which adds a getInsertId method.

Internally, getAll and getFirst are implemented using getIterator. As such they can only be called once for a given SqlResult object.

Shorthand methods

When creating a new instance of PeachySQL, an array of options can be passed to the constructor specifying a table name and list of valid columns:

$options = [
    'table'   => 'Users',
    'columns' => ['user_id', 'fname', 'lname']
];

$userTable = new PeachySQL\Mysql($mysqlConn, $options);

If using SQL Server, an additional option can be passed to specify the table's identity column. This is necessary so that PeachySQL can generate an output clause to retrieve insert IDs.

$userTable = new PeachySQL\SqlServer($sqlSrvConn, [
    'table'   => 'Users',
    'columns' => ['user_id', 'fname', 'lname'],
    'idCol'   => 'user_id'
]);

You can then make use of PeachySQL's five shorthand methods: select, insertOne, insertBulk, update, and delete. To prevent SQL injection, the queries PeachySQL generates for these methods always use bound parameters for values, and column names are checked against the list of valid columns in the options array.

select

The select method takes three arguments, all of which are optional:

  1. An array of columns to select.
  2. A WHERE array to filter results.
  3. An array of column names to sort by.

Selected rows are returned as an array of associative arrays, similar to calling the getAll method on a SqlResult object for a custom query.

// select all columns and rows in the table, ordered by last name and then first name
$rows = $userTable->select([], [], ['lname', 'fname']);

// select first and last name columns where user_id is equal to 5
$rows = $userTable->select(['fname', 'lname'], ['user_id' => 5]);

// select all columns for an array of user IDs
$ids = [57, 239, 31, 54, 28];
$rows = $userTable->select([], ['user_id' => $ids]);
insertOne

The insertOne method allows a single row to be inserted from an associative array. It returns an InsertResult object with getId and getAffected methods.

$userData = [
    'fname' => 'Donald',
    'lname' => 'Chamberlin'
];

$id = $userTable->insertOne($userData)->getId();
insertBulk

The insertBulk method makes it possible to bulk-insert multiple rows from an array. It returns a BulkInsertResult object with getIds, getAffected, and getQueryCount methods.

$userData = [
    [
        'fname' => 'Theodore',
        'lname' => 'Brown'
    ],
    [
        'fname' => 'Grace',
        'lname' => 'Hopper'
    ],
    [
        'fname' => 'Douglas',
        'lname' => 'Engelbart'
    ]
];

$result = $userTable->insertBulk($userData);
$ids = $result->getIds(); // e.g. [64, 65, 66]
$affected = $result->getAffected(); // 3
$queries = $result->getQueryCount(); // 1

SQL Server allows a maximum of 1,000 rows to be inserted at a time, and limits individual queries to 2,099 or fewer bound parameters. MySQL supports a maximum of 65,536 bound parameters per query. These limits can be easily reached when attempting to bulk-insert hundreds or thousands of rows at a time. To avoid these limits, the insertBulk method automatically splits large bulk insert queries into batches to efficiently handle any number of rows (getQueryCount returns the number of required batches). The default limits (listed above) can be customized via the "maxBoundParams" and "maxInsertRows" PeachySQL options.

update and delete

The update method takes two arguments: an associative array of columns/values to update, and a WHERE array to filter which rows are updated.

The delete method takes a single WHERE array argument to filter the rows to delete.

Both methods return the number of affected rows.

// update the user with user_id 4
$newData = ['fname' => 'Raymond', 'lname' => 'Boyce'];
$userTable->update($newData, ['user_id' => 4]);

// delete users with IDs 1, 2, and 3
$userTable->delete(['user_id' => [1, 2, 3]]);

Transactions

Call the begin method to start a transaction. query and any of the shorthand methods can then be called as needed, before committing or rolling back the transaction with commit or rollback.

Other methods and options

The database connection can be swapped out at any time with setConnection, and setOptions and getOptions methods allow PeachySQL options to be changed and retrieved at will.

In addition to the previously mentioned options, there is a MySQL-specific "autoIncrementIncrement" option which can be used to set the interval between successive auto-incremented values in the table (defaults to 1). This option is used to determine the array of insert IDs for bulk-inserts, since MySQL only provides the first insert ID.

Author

Theodore Brown
http://theodorejb.me

License

MIT