README

DBALite is a thin/lightweight Database Abstraction Library for PHP. It is built upon and leverages the excelent PDO extension which provides a unified API to several different database vendors. DBALite adds several extra conveniences and best practices to the API.

What it Provides

insert(), update(), delete() methods build and execute SQL INSERT, UPDATE, DELETE queries for you.
prepareInsert(), prepareUpdate(), prepareDelete() builds the SQL and returns prepared statements that can be run multiple times against various sets of data.
Class for programatically building SELECT queries.
Throws exceptions by default.
Exceptions capture the SQL statement whenever possible. Cast the exception to a string to see and extended error message including the SQL query used if applicable.
Uses array for connection options. No more messing with various DSN formats.
Automatic identifier (table & column names) quoting (can turn off if needed).
Shortened options, say assoc instead of PDO::FETCH_ASSOC for example.
Speaking of PDO::FETCH_ASSOC associative arrays are returned by default.
queryAll() convenience function executes the SQL query and calls fetchAll() immediately in one convenient method call.
queryOne() convenience function executes the SQL query and returns first row and first column only. Great for count queries.
Automatically sets MySQL to use native prepared queries, not to buffer query results, and sets the connection to use UTF-8 on connection (or uses SET NAMES 'utf8' on PHP < 5.3.6).
Last but not least a full suite of unit tests using PHPUnit.

Rationale for Writing

While writing a web application in PHP I found myself repeating code to generate the same kind of SQL queries, as many do. I knew it was time to look at libraries what would do that for me. I was already using PDO so I could easily switch between Sqlite and MySQL (test vs production). I wanted whatever library I used to also use PDO. I did not want to use a full ORM or Active Record solution. Things like Doctrine, Propel, and ADODB got thrown out for being to large and complicated. I liked the Zend_Db (part of the Zend Framework 1) API. However I felt the multiple levels of abstraction it uses to support both PDO drivers and the vendor specific extensions was adding too much code. So I decided to write my own library. Yes I know NIH. But at the time (2007-2008) there wasn't anything else that used PDO and wasn't part of some larger stack. It was a good learning experience and my first foray into using unit tests. I still use this library in 2012.

Requirements

DBALite requires PHP version 5.1.0 or greater due to it's reliance on the PDO extension (which was added in 5.1). Realistically it's never been tested on anything lower than 5.2.8. Since DBALite is built around PDO you will need that extension enabled (it is by default and most shared hosts should have it on by now). You will also need the PDO driver for your database (pdo_sqlite, pdo_mysql, pdo_pgsql) enabled. That's it!

Supported Databases

DBALite has been tested to work with:

Sqlite 3
MySQL
PostgreSQL

Plans were to support Microsoft SQL Server via the ODBC driver. Eventually they released PDO_SQLSRV. I was never able to get a full test environment set up in a VM for either one (no MS in my house anymore). If someone wants to run the test suite on the Windows stack go for it and give me a PR! For now all SQL Server support is deprecated.

Quick Start

All of the source code files are located in the src/ directory, tests are in test/. Drop the entire package or just the contents of src/ into your application or somewhere in your include path.
You only need to include 'src/DBALite.php'. It will require all of the other required files for you.
Create an array with your connection settings. The array should have the following keys:
- 'dbname' => 'Name of the database',
- 'host' => 'Hostname server is located on, usually localhost',
- 'username => 'database username',
- 'password' => 'database password'
- NOTE: Only 'dbname' is required for Sqlite.
Call DBALite::factory() with the name of the database driver and the configuration array.

$connection = DBALite::factory('mysql', $db_config);

Documentation

Full API Documentation is available in the Github Wiki.

Things You Should Be Aware Of

DBALite does several things for your automatically.

It will throw exceptions on all errors. The exception is of class DBALite_Exception. Use the __toString() magic method to get the most informative error message. echo $exception; will do the trick. The DBALite_Exception class encapsulates PDOExceptions and will show the PDO error message if the __toString() method is used.
DBALite assumes you want only associative arrays when you fetch results. You can change this default, call setOption('fetchmode', $mode) on your connection where $mode is one of assoc, num, both, obj, lazy, default.
On MySQL buffered queries are used to avoid the common errors associated with the default of unbuffered queries. This increases memory usage, but in 95% of use cases you won't notice it. If you are returning really big result sets this may cause you concern. Also the connection assumes UTF-8 encoding is being used. That is always how I program, with everything in UTF-8. And you should too! On PHP 5.3.6 and up charset=utf8 is passed in the connection string. On PHP 5.3.5 and lower the command SET NAMES 'utf-8' is run after the connection is opened.

License

DBALite is licensed under the GNU Lesser General Public License 3.0. Simply that means you can use it in any project you like, Open Source/Proprietary/Free/Paid, but if you fork it that fork must remain open source and LGPL licensed. See the LICENSE.txt file in the root of te project.

The Future

The next version of the library will be namespaced and PSR-0 autoloader friendly. Otherwise the library is quite stable. I've been using it since 2008 for my projects (which are granted, small).

paulyg / dbalite

Maintainers

Details