Overview

SQLite is a C library that makes a file-based or memory-based database, which is fast (compared to full-blown RDBMS, like MySQL or PostgreSQL). Like PHP, SQLite is type-less (i.e. loosely typed).

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) works great.

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.

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.

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 (memory) footprint.

To access SQLite3, PDO is required in PHP. PDO is a generic database interface, used in PHP5.

	SELECT	sqlite_version();

Configuration

PHP5 compiles SQLite as a shared bundled library; to use SQLite, its libraries must be un-commented in PHP.ini.

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.

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

extension=php_pdo.so
extension=php_pdo_sqlite.so

? not sure the following are really needed
extension=pdo.so
extension=sqlite.so
extension=pdo_sqlite.so

SQLite3 - object-oriented SQLite

Creating an SQLite database via DSN

  $dbConnection = 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.  Any filename extension may be used e.g. .SQL3 or .SQLite3. The SQLite user needs read/write permission on the database directory.

In-memory databases must be named :memory:, which tells SQLite to create the database in memory. For (an SQLite2) example:

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

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

Executing connection object commands

In general, the PDO functions omit (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

        )
");

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, it
can’t 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 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 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 database file
    sqlite_close($dbHandle);

?>

The sqlite_last_error() and sqlite_error_string() functions don't work in exactly the same way 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().

Transactions

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