PDO SQLite3

Category:Core | Category:Core::Community | Category:Core::Database

The following approach worked using a previous CodeIgniter version. Current changes to CodeIgniter database classes preclude using the following class files as a CodeIgniter PDO database interface.

The approach has been 'broken', as of CodeIgniter 1.7 (or so). To see the old code, one can still open the files in an editor.

Between:

• the SQLite versions (v.2 in 2000 and v.3 in 2004) and
• a dizzying array of SQLite language 'extensions', including for PHP, at: http://sqlite.org/cvstrac/wiki?p=SqliteWrappers
• PHP main zend review database library 'extensions', ADOdb, PEAR DB and PDO and
• procedural vs. object-oriented (e.g. PDO) functions syntax and
• dismal SQLite.org docs, albeit a tad better at http://us3.php.net/manual/en/book.sqlite3.php

... who can blame Rick Ellis & Co. for throwing up their hands and saying, 'Ya know what, I haven't got time to figure out all these SQLite alternatives ... and why should CodeIgniter add to the SQLite confusion with yet another database abstraction layer? What additional benefit might there be?' How well might PDO map to CodeIgniter's 'Active Record' data design implementation?

To get PDO and SQLite3 working, I suppose one could copy the SQLite2 driver, rename it for SQLite3, place it in a /PDO/ directory and edit the function names to get them to make SQLite3_whateverFunction() calls. Someone did that, here: http://blog.trevorbramble.com/past/2009/9/20/codeigniter_sqlite3/

So, for now, there is no real CodeIgniter PDO SQLite3 support! CodeIgniter is often billed as a 'help', when appropriate; for PDO-SQLite one may be better off 'dialing direct'.

CodeIgniter PDO SQLite3 quick setup

Download the CodeIgniter Wiki file: File:pdo_sqlite_driver.zip.

Warning! This driver can not work with current CI versions 1.7.x.

Create a /pdo/ subdirectory, as in:

 CodeIgniter/system/database/drivers/pdo/ 

Unzip pdo_sqlite_driver.zip into the new directory /pdo/.

Un-comment the PHP5 PDO database interface drivers in PHP.ini

extension=php_pdo.dll

and

extension=php_pdo_sqlite.dll

Re-start the web server e.g. Apache.

That's it for the setup. The rest of this article explains the derivation of the above files and includes some getting started ideas.

PDO SQLite3 driver for CodeIgniter v.1.7.x

This driver found on russian CI forum.

[strike]Download file File:sqlite3_pdo_driver_0_01_by_xi.zip[/strike]

UPD: In april 2010 this driver update to version 0.02. In this version fix problem, nascend as a result of cache database structure and data in PDO persistent connection mode (this mode is possible use only for Client-Server technology, but must not in File-Based technology).

Download file File:sqlite3_pdo_driver_0_02_by_xi.zip

For connect to SQLite3 database, use next steps.

1. Create directory /pdo in /database/drivers and copy to this directory driver *.php files

2. Create SQLite3 database file, and put him to any directory. My database file is [APPPATH]/db/base.db

3. In application database config [APPPATH]/config/database.php set next settings:

$db['default']['hostname'] = '';
$db['default']['username'] = '';
$db['default']['password'] = '';
$db['default']['database'] = 'sqlite:'.APPPATH.'db/base.db';
$db['default']['dbdriver'] = 'pdo';

Enjoy.

Introduction

PHP5 core now includes the SQLite (file-based) database and PDO (PHP Data Objects) database interface layer.

SQLite is an open-source, embedded, relational database. SQLite is written in C and is less than 1/4 Meg in size (the whole SQLite RDBMS 'engine' fits nicely in memory). SQLite is bullet fast (yes, faster than MySQL or PostgreSQL - until queries grow complex enough to benefit from a query optimizer). A temporary SQLite database can even be used in memory and left for garbage collection with the calling script - very PHP-like.

Embedded means that SQLite is intertwined in the host application process (PHP in our case) that calls it; embedded also means that there's no networking (configuration) required. SQLite was designed for embedded use (by D Richard Hipp at General Dynamics for Navy guided missile destroyers); therefore, no user names, passwords or groups are involved, either.

SQLite has been used for fairly large applications (the Apple OS X, Sun Solaris, Mozilla, Linux Palm OS, KDE Amarok audio player, YUM package manager, smart phones, D-Link etc.); there are many forums, blogs and wikis, based on SQLite. So, don't dismiss SQLite - just because it's not mySQL or PostgreSQL. Like any tool, there's a proper time and place for it e.g. for occasions when "squirrel hunting with an elephant gun" is not called for, such as a configuration repository, simple small volume uses etc. Remember, mySQL or PostgreSQL are not ALWAYS called for (or fastest), either.

SQLite3 (the PHP default), which came along in 2004, requires the PDO database interface, rather than PHP calls, as was the case with SQLite2. SQLite3 is 25% smaller than SQLite2.

Without an in-depth investigation it appears that the objective of PDO is analogous to Microsoft's ODBC database abstraction layer from the early 1990's i.e. PDO, as a database interface layer, offers generic database programatic access, which depends upon a "driver" from each database vendor - to implement the specific features of that vendor's database offering.

The SQLite.org documentation is sketchy.

"The Definitive Guide to SQLite" by Michael Owens delves into the SQLite internal design and its C API, but only touches on PHP PDO-SQLite use in pages 335 - 340.

**"Learning PHP Data Objects" by Dennis Popel ** is full of PDO examples, including MVC implementation in Chapter 7. Most examples are rendered for MySQL, SQLite and PostgreSQL.

When to use (and not use) SQLite

Communicating, directly, with a file or memory locks the entire file or memory location. Since SQLite is file or memory based, it is ** not ** suitable for cross-network, client-server architecture; so, SQLite would not be suitable for high-volume, high-contention (e.g. transaction) sites. However, calling SQLite from server-side code (e.g. from PHP on the ** same ** box as the Apache web server i.e. NOT across a network) works Great ... and is ** F A S T**.

SQLite excels at SELECTs (file reading, especially small, configuration files). If your INSERTs or UPDATEs are infrequent, compared to SELECTs, SQLite may be several times ** faster than a full-blown RDBMS**, like MySQL or PostgreSQL.

Because an SQLite in-memory database is bundled with its creation process (the PHP script that created it), the SQLite in-memory database will be dropped from memory after the PHP script is parsed.

SQLite has No user access control - other than file system permissions.

CodeIgniter PDO SQLite Implementation

SQLite3 requires the PHP5 PDO database interface drivers; ```php extension=php_pdo.dll

```php
extension=php_pdo_sqlite.dll
``` must be un-commmented in PHP.ini.  

####  A 'gotcha' to look out for - Windows Only 


This code is great, but a thing to look out for for any 'newbs' to PHP/CodeIgniter/Sqlite3/PDO(like myself)...

You may have a situation where you get an unexplainable error message of [pre]SQLSTATE[HY000] [14] unable to open database file[/pre]

If so, check in config/database.php that your ```php
 $db['default']['database'] 
``` setting value is enclosed in single quotes. Double quotes (the default) will cause escaping and should you (like me) have a sequence of characters like ```php
 c:\xa 
``` ..., etc. the \xa will be escaped into a new-line character, causing a weird database path to be provided and the database will never be found.

Thanks to all for this great article.