SQLite notes

Overview

SQLite is an open-source, embedded relational database, modularly written as a small (<250 k-bytes) C API library. As memory-based or even a file-based database, SQLite makes a f a s t alternative to a full-blown RDBMS, like MySQL or PostgreSQL. Like PHP, SQLite is type-less (i.e. loosely-typed).

SQLite was released in 2000 by D. Richard Hipp. A major re-write (version 3) was done in 2004. SQLite came embedded within PHP5.

SQLite.org Wiki is a broad source of SQLite resources and information. SQLite driver 'extensions'/'wrappers' are listed at: SQLite extension wrappers

For SQLite in-depth, "The Definitive Guide to SQLite" by Michael Owens (Apress 2006) introduces SQLite architecture and provides guidance for writing SQLite driver 'extensions'; however, it only addresses PHP in passing ... on 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.

The PHP CodeIgniter framework PDO SQLite3 notes has sort of orphaned PDO-SQLite ... for the time being (see: CodeIgniter PDO support ).

When to use (and not use) SQLite

Communicating, directly, with a memory-based or file-based database locks the memory location or the entire file. Since SQLite is memory based or file-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, as server-side database process (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 embedded with its creation process (i.e. the PHP script that created it), the SQLite in-memory database will be dropped from memory after the PHP script is parsed.

Configuration

PHP5 compiles SQLite as a shared, bundled library. SQLite3 requires PDO to interface with SQLite; so, PHP 5.1 introduced the PDO (PHP Data Objects) data abstraction layer. The PDO libraries must, also, be enabled (un-commented) in PHP.ini.

on *nix

On a *nix box, after copying your pre-existing files (like the models, views and controllers), if you do not provide at least READ Permissions on those copied files, you'll receive warnings and errors, like:

    ... failed to open stream: Permission denied
    ... error: 404 ... page not found

For SQLite3 on Unix/Linux, the libraries must be configured as shared e.g.

./configure -- zlib -- enable-pdo = shared -- with-pdo-sqlite = shared -- with shared

Debian/Ubuntu configures PHP via /etc/php5/apache2/php.ini , which includes any files within /etc/php5/apache2/conf.d/ e.g. a /etc/php5/apache2/conf.d/pdo.ini

    extension=pdo.so

Into pdo.ini add the PDO Driver for SQLite 3.x ...

    extension=pdo_sqlite.so

/etc/php5/apache2/conf.d/sqlite.ini contains:

    extension=sqlite.so

... and /etc/php5/apache2/conf.d/xdebug.ini contains:

    zend_extension=/usr/lib/php5/20060613/xdebug.so

Into /etc/php5/apache2/conf.d/xdebug.ini add:

    ; from http://wiki.netbeans.org/HowToConfigureXDebug
    xdebug.remote_enable=1
    xdebug.remote_handler=dbgp
    xdebug.remote_mode=req
    xdebug.remote_host=127.0.0.1
    xdebug.remote_port=9000

on Windows

For SQLite3 on Windows, in PHP.ini enable/un-comment:

    extension=php_pdo.dll
    extension=php_pdo_sqlite.dll

The PDO library must be called before the SQLite library. The older library, php_sqlite.dll , was only required for SQLite2.

Procedural vs. object-oriented SQLite

If the differences between SQLite2 vs. SQLite3 didn't make things complicated enough, SQLite3 can operated either with procedural syntax or object-oriented syntax. When you look up SQLite functions, you'll find multiple lists ... for procedural or object-oriented use ... for example:

procedural SQLite2	procedural SQLite3	object-oriented
$result = sqlite_whateverFunction();	$result = sqlite3_whateverFunction();	$instantiatedObject = NEW(parentClass);
$dbConnection = sqlite_open(databaseFile);	$dbConnection = sqlite3_open(databaseFile);	$pdo = NEW PDO("/path/to/databaseFile");
		$pdo->methodFunction

Note that the primary difference between many (most?) sqlite2 and sqlite3 procedural functions is the "3", following sqlite ... and there is no "2" after sqlite_.

Other than backward compatibility, I can't see any reason for SQLite2, now, years after SQLite3 was born in 2004. Nevertheless, many old books will add to your confusion with SQLite2 procedural syntax examples.

While the SQLite.org documentation is dismal, the PHP.net SQLite3 docs are better: http://us3.php.net/manual/en/book.sqlite3.php

SQLite3 -> object-oriented SQLite

Creating an SQLite database via DSN

The database driver “DSN prefix” for SQLite 3 is … sqlite (see code below).

    $pdoConnection = new PDO(‘sqlite:/path/to/SQLite.SQL3’);

• The PDO SQLite database driver (sqlite) is specified, followed by a colon and the full path to the database file.
• sqlite designates using the SQLite3 driver; sqlite2 designates using the (guess what!) SQLite 2 driver.
Note the inconsistency between these driver designations vs. function designations ... SQLite3_whateverFunction() vs. SQLite_whateverFunction().
• Any filename extension may be used e.g. .SQL3 or .SQLite3 etc.
• The SQLite user (e.g. CodeIgniter ... from PHP ... from Apache i.e. www-data ) needs read/write Permissions on the database directory.
• In-memory databases must be named :memory:, which tells SQLite to create the database in memory.

For (an SQLite2) example, opening an SQLite database in memory, rather than from a file:

	$dbConnection = sqlite_open(":memory:");

For speed, create a database in memory (which is f a s t ); when finished, save the database to disk via the SQL ATTACH databaseName command.

Executing connection object commands

In general, the PDO functions omit (i.e. don't need) the sqlite_ prefix, used by the procedural SQLite2 function names.

	$dbConnection->exec(“SQL command”);

Create a table

    $dbConn->exec("
            CREATE TABLE tableName
                (
                    tableNameID INTEGER PRIMARY KEY,
                    column1 TEXT UNIQUE,
                    column2 TEXT
                )
        ");

Integer Primary Key creates an Auto-increment column.

Prepared statement – to Insert data

    $preparedStatement = $dbConn->prepare("
        INSERT INTO tableName
            (
                column1,
                column2
            )
    
        VALUES
            (
                :column1,
                :column2
            )");

    $preparedStatement->bindParam(':column1', $column1_val);
    $preparedStatement->bindParam(':column2', $column2_val);

    $column1_val = "sqlite";
    $column2_val = "is small";

    $ preparedStatement ->execute();

SELECT … and print_r

    // indicate that the output is predefined for print_r
    print "<pre>";

        foreach ($dbConn->query
            ('
                SELECT *
                FROM tableName
            ',

        PDO::FETCH_ASSOC) as $row)

    	print_r($row);

     print "</pre>"

object-oriented approach … withOUT PDO

Default directory

The default SQLiteDatabase() file location is the operating system root directory (e.g. “db.sqlite” results in the file being created at C:\db.sqlite ), rather than in the current directory, such as … /htdocs/application/.

a walkthrough …

<?php
    
    // set database file path
    $dbPath  = $_SERVER['DOCUMENT_ROOT'] . "/path/to/db.SQL3";
  
    // instantiate database class object
    // … creates a database file, if  it doesn’t exist, but
    // SQLite can not create a non-existent folder)
    $db =  new SQLiteDatabase($dbPath) or die("Could not create database object");

    // create table
    $db->query("CREATE TABLE books
        (
            ID INTEGER PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            author VARCHAR(255) NOT NULL
         )"
                )
    or die("Error in query");

    // insert records
    $db->query("INSERT INTO books
        (
            title,
            author
        )
        VALUES 
            (
              'The Lord Of The Rings',
              'J.R.R. Tolkien'
            )"
                )
      or die("Error in query");
  
    $db->query("INSERT INTO books
        (
            title,
            author
        )
        VALUES 
        (
            'The Murders In The Rue Morgue',
            'Edgar Allan Poe'
        )"
                )
        or die("Error in query");
  
    // print success message
    echo  "<i>Database successfully initialized!</i>";

    // destroy the database object
    unset($db);

?>

If the script attempts to enter a duplicate key, that portion of the script will just be ignored.

Handling query errors

The sqlite_last_error() function returns the last error code (integer), returned by SQLite.  To convert the sqlite_last_error() to a human-readable text message, couple it with the sqlite_error_string() function, as follows.

<?php

    // set path of database file
    $db =  $_SERVER['DOCUMENT_ROOT']."/../library.db";
    
    // open database file
    $dbHandle  = sqlite_open($db)
    or die("Could not open database");
    
    // generate query string, containing a deliberate test error
    $query = "DELETE books WHERE id = 1";
    
    // execute query
    $result  =  sqlite_query($dbHandle, $query)
    or die("Error in query: ".sqlite_error_string(sqlite_last_error($handle))

    );
    
    // close the database file
    sqlite_close($dbHandle);

?>

The output looks like:

The sqlite_last_error() and sqlite_error_string() functions don't work exactly as the mysql_error() and mysql_error() functions. The mysql_errno() and mysql_error() functions can be used independently - to retrieve the last error code and message, respectively; but, the sqlite_error_string() is dependent on the error code, returned by sqlite_last_error().

Security

Running as an embedded process, SQLite has No user access control - other than file-system Permissions. Since SQLite operates as an embedded process, SQLite is only restricted by Permissions of the controlling process (that called it) ... e.g. PHP (for web applications) and Apache's www-data User and Group (in Debian/Ubuntu).

Transactions

SQLite will execute multiple queries, grouped as transactions, much faster than executing the queries, individually.

Versions

Prior to SQLite3, (procedural) SQLite was notably slow at Inserts and Updates (Remember: disk writes are slow.). SQLite3 is faster and makes a smaller footprint (in memory).

To access SQLite3, PDO is required in PHP. PDO is a generic database interface abstraction layer (a la Microsoft's ODBC), used in PHP5.1.

To check the database file SQLite version from your application, call:

	SELECT	sqlite_version();

Back Home