PDO SQLite3

Category:databases

[h3]INTRODUCTION/BACKGROUND[/h3] 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) requires PDO access, 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 from the early `90'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 site documentation is sketchy. "The Definitive Guide to SQLite" by Michael Owens is a 5-star Apress (hardcover) book ... MORE than worthwhile.

[h3]CodeIgniter IMPLEMENTATION[/h3] This (http://codeigniter.com/forums/viewthread/52588/P15/#259228) CI forum thread chronicles how I got to the implementation, that follows. CI PDO/SQLite capability boils down to adding the following two files to the CI file tree.

[code] /system/database/drivers/pdo/pdo_driver.php /system/database/drivers/pdo/pdo_result.php [/code]

Contents of those files follow; just copy and paste the following into new files in the above path. For your convenience, I uploaded these two files; I had to use .txt file extensions, as the file upload facility precludes uploading .php files; so, search for pdo_driver.txt and pdo_result.txt; just rename the .txt extension to .php

[h3]PDO_DRIVER.PHP:[/h3]

[code]<?php if (!defined('BASEPATH')) exit('No direct script access allowed'); /**

• Code Igniter
• An open source application development framework for PHP 4.3.2 or newer
• @package CodeIgniter
• @author Rick Ellis
• @copyright Copyright (c) 2006, pMachine, Inc.
• @license http://www.codeignitor.com/user_guide/license.html
• @link http://www.codeigniter.com
• @since Version 1.0
• @filesource */

// ------------------------------------------------------------------------

/**

• PDO Database Adapter Class
• Note: _DB is an extender class that the app controller
• creates dynamically based on whether the active record
• class is being used or not.
• @package CodeIgniter
• @subpackage Drivers
• @category Database
• @author Dready
• @link http://dready.jexiste.fr/dotclear/ */

class CI_DB_pdo_driver extends CI_DB { var $class_version = '0.1';

/**
 * Non-persistent database connection
 *
 * @access private called by the base class
 * @return resource
 */
function db_connect()
{
    $conn_id = false;

    try
    {
        $conn_id = new PDO ($this->database, $this->username, $this->password);
        log_message('debug', 'connecting ' . $this->database);
    }
    catch (PDOException $e)
    {
        log_message('debug', 'merde');
        log_message('error', $e->getMessage());

        if ($this->db_debug)
        {
            $this->display_error($e->getMessage(), '', TRUE);
        }
    }

    log_message('debug', print_r($conn_id, true));

    if ($conn_id)
    {
        log_message('debug', 'connection ok');
    }

    return $conn_id;
}

/**
 * Persistent database connection
 *
 * @access private, called by the base class
 * @return resource
 */
function db_pconnect()
{
    try
    {
        $conn_id = new PDO ($this->database, $this->username, $this->password, array(PDO::ATTR_PERSISTENT => true));
    }
    catch (PDOException $e)
    {
        log_message('error', $e->getMessage());

        if ($this->db_debug)
        {
            $this->display_error($e->getMessage(), '', TRUE);
        }
    }

    return $conn_id;
}

/**
 * Select the database
 *
 * @access private called by the base class
 * @return resource
 */
function db_select()
{
    return TRUE;
}

/**
 * Execute the query
 *
 * @access private, called by the base class
 * @param string an SQL query
 * @return resource
 */
function _execute($sql)
{
    $sql = $this->_prep_query($sql);
    log_message('debug', 'SQL : ' . $sql);

    return @$this->conn_id->query($sql); // Do we really need to use error supression here? :(
}

/**
 * Prep the query
 *
 * If needed, each database adapter can prep the query string
 *
 * @access private called by execute()
 * @param string an SQL query
 * @return string
 */
function &_prep_query($sql)
{
    return $sql;
}

/**
 * "Smart" Escape String
 *
 * Escapes data based on type
 * Sets boolean and null types
 *
 * @access public
 * @param string
 * @return integer
 */
function escape($str)
{
    switch (gettype($str))
    {
        case 'string':
            $str = $this->escape_str($str);
            break;
        case 'boolean':
            $str = ($str === FALSE) ? 0 : 1;
            break;
        default:
            $str = ($str === NULL) ? 'NULL' : $str;
            break;
    }

    return $str;
}

/**
 * Escape String
 *
 * @access public
 * @param string
 * @return string
 */
function escape_str($str)
{
    if (get_magic_quotes_gpc())
    {
        $str = stripslashes($str);
    }

    return $this->conn_id->quote($str);
}

/**
 * Close DB Connection
 *
 * @access public
 * @param resource
 * @return void
 */
function destroy($conn_id)
{
    $conn_id = null;
}

/**
 * Insert ID
 *
 * @access public
 * @return integer
 */
function insert_id()
{
    return @$this->conn_id->lastInsertId();
}

/**
 * "Count All" query
 *
 * Generates a platform-specific query string that counts all records in
 * the specified database
 *
 * @access public
 * @param string
 * @return string
 */
function count_all($table = '')
{
    if ($table == '')
    {
        return '0';
    }

    $query = $this->query('SELECT COUNT(*) AS numrows FROM `' . $table . '`');

    if ($query->num_rows() == 0)
    {
        return '0';
    }

    $row = $query->row();

    return $row->numrows;
}

/**
 * The error message string
 *
 * @access private
 * @return string
 */
function _error_message()
{
    $infos = $this->conn_id->errorInfo();

    return $infos[2];
}

/**
 * The error message number
 *
 * @access private
 * @return integer
 */
function _error_number()
{
    $infos = $this->conn_id->errorInfo();

    return $infos[1];
}

/**
 * Version number query string
 *
 * @access public
 * @return string
 */
function version()
{
    return $this->conn_id->getAttribute(constant("PDO::ATTR_SERVER_VERSION"));
}

/**
 * Escape Table Name
 *
 * This function adds backticks if the table name has a period
 * in it. Some DBs will get cranky unless periods are escaped
 *
 * @access public
 * @param string the table name
 * @return string
 */
function escape_table($table)
{
    if (stristr($table, '.'))
    {
        $table = preg_replace("/\./", '`.`', $table);
    }

    return $table;
}

/**
 * Field data query
 *
 * Generates a platform-specific query so that the column data can be retrieved
 *
 * @access public
 * @param string the table name
 * @return object
 */
function _field_data($table)
{
    $sql = 'SELECT * FROM ' . $this->escape_table($table) . ' LIMIT 1';
    $query = $this->query($sql);

    return $query->field_data();
}

/**
 * Insert statement
 *
 * Generates a platform-specific insert string from the supplied data
 *
 * @access public
 * @param string the table name
 * @param array the insert keys
 * @param array the insert values
 * @return string
 */
function _insert($table, $keys, $values)
{
    return "INSERT INTO " . $this->escape_table($table) . " (" . implode(', ', $keys) . ") VALUES (" . implode(', ', $values) . ")";
}

/**
 * Update statement
 *
 * Generates a platform-specific update string from the supplied data
 *
 * @access public
 * @param string the table name
 * @param array the update data
 * @param array the where clause
 * @return string
 */
function _update($table, $values, $where)
{
    foreach ($values as $key => $val)
    {
        $valstr[] = $key." = ".$val;
    }

    return "UPDATE " . $this->escape_table($table) . " SET " . implode(', ', $valstr) . " WHERE " . implode(" ", $where);
}

/**
 * Delete statement
 *
 * Generates a platform-specific delete string from the supplied data
 *
 * @access public
 * @param string the table name
 * @param array the where clause
 * @return string
 */
function _delete($table, $where)
{
    return "DELETE FROM " . $this->escape_table($table) . " WHERE " . implode(" ", $where);
}

/**
 * Show table query
 *
 * Generates a platform-specific query string so that the table names can be fetched
 *
 * @access public
 * @return string
 */
function _show_tables()
{
    return "SELECT name from sqlite_master WHERE type='table'";
}

/**
 * Show columnn query
 *
 * Generates a platform-specific query string so that the column names can be fetched
 *
 * @access public
 * @param string the table name
 * @return string
 */
function _show_columns($table = '')
{
    // Not supported
    return FALSE;
}

/**
 * Limit string
 *
 * Generates a platform-specific LIMIT clause
 *
 * @access public
 * @param string the sql query string
 * @param integer the number of rows to limit the query to
 * @param integer the offset value
 * @return string
 */
function _limit($sql, $limit, $offset)
{
    if ($offset == 0)
    {
        $offset = '';
    }
    else
    {
        $offset .= ', ';
    }

    return $sql . "LIMIT " . $offset . $limit;
}

}

?>[/code]

[h3]PDO_RESULT.PHP:[/h3]

[code]<?php /**

• PDO Result Class

• This class extends the parent result class: CI_DB_result

• @category Database

• @author Dready

• @link http://dready.jexiste.fr/dotclear/ */ class CI_DB_pdo_result extends CI_DB_result { var $pdo_results = ''; var $pdo_index = 0;

  /**

  • Number of rows in the result set

  • pfff... that's ugly !!!!!!!

  • PHP manual for PDO tell us about nom_rows :

  • "For most databases, PDOStatement::rowCount() does not return the number of rows affected by

  • a SELECT statement. Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the

  • same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to

  • retrieve the number of rows that will be returned.

  • which means

  • 1/ select count(*) as c from table where $where

  • => numrows

  • 2/ select * from table where $where

  • => treatment

  • Holy cow !

  • @access public

  • @return integer */ function num_rows() { if (!$this->pdo_results) { $this->pdo_results = $this->result_id->fetchAll(PDO::FETCH_ASSOC); }

    return sizeof($this->pdo_results); }

  /**

  • Number of fields in the result set
  • @access public
  • @return integer */ function num_fields() { if (is_array($this->pdo_results)) { return sizeof($this->pdo_results[$this->pdo_index]); } else { return $this->result_id->columnCount(); } }

  /**

  • Field data

  • Generates an array of objects containing field meta-data

  • @access public

  • @return array / / function field_data() { $retval = array();

    for ($i = 0; $i < $this->num_fields(); $i++) { $F = new CI_DB_field(); $F->name = sqlite_field_name($this->result_id, $i); $F->type = 'varchar'; $F->max_length = 0; $F->primary_key = 0; $F->default = '';

     $retval[] = $F;
    

    }

    return $retval; } */

  /**

  • Result - associative array

  • Returns the result set as an array

  • @access private

  • @return array */ function _fetch_assoc() { if (is_array($this->pdo_results)) { $i = $this->pdo_index; $this->pdo_index++;

     if (isset($this->pdo_results[$i]))
     {
         return $this->pdo_results[$i];
     }
    
     return null;
    

    }

    return $this->result_id->fetch(PDO::FETCH_ASSOC); }

  /**

  • Result - object

  • Returns the result set as an object

  • @access private

  • @return object */ function _fetch_object() { if (is_array($this->pdo_results)) { $i = $this->pdo_index; $this->pdo_index++;

     if (isset($this->pdo_results[$i]))
     {
         $back = '';
    
         foreach ($this->pdo_results[$i] as $key => $val)
         {
             $back->$key = $val;
         }
    
         return $back;
     }
    
     return null;
    

    }

    return $this->result_id->fetch(PDO::FETCH_OBJ); } } ?>[/code]

[h3]For CI 1.6.1 [/h3] (quindo) To be able to use SQLite3 with CI 1.6.1 (and PHP 5.2.0) two Functions from sqlite_driver.php have to be copied into pdo_driver.php:

• _protect_identifiers()
• _from_tables()

For completeness, these are the functions: [code]

/** * COPY FROM sqlite_driver.php * Protect Identifiers * * This function adds backticks if appropriate based on db type * * @access private * @param mixed the item to escape * @param boolean only affect the first word * @return mixed the item with backticks */ function _protect_identifiers($item, $first_word_only = FALSE) { if (is_array($item)) { $escaped_array = array();

        foreach($item as $k=>$v)
        {
            $escaped_array[$this->_protect_identifiers($k)] = $this->_protect_identifiers($v, $first_word_only);
        }

        return $escaped_array;
    {   

    // This function may get "item1 item2" as a string, and so
    // we may need "item1 item2" and not "item1 item2"
    if (ctype_alnum($item) === FALSE)
    {
        if (strpos($item, '.') !== FALSE)
        {
            $aliased_tables = implode(".",$this->ar_aliased_tables).'.';
            $table_name =  substr($item, 0, strpos($item, '.')+1);
            $item = (strpos($aliased_tables, $table_name) !== FALSE) ? $item = $item : $this->dbprefix.$item;
        }

        // This function may get "field >= 1", and need it to return "field >= 1"
        $lbound = ($first_word_only === TRUE) ? '' : '|\s|\(';

        $item = preg_replace('/(^'.$lbound.')([\w\d\-\_]+?)(\s|\)|$)/iS', '$1$2$3', $item);
    }
    else
    {
        return "{$item}";
    }

    $exceptions = array('AS', '/', '-', '%', '+', '*');

    foreach ($exceptions as $exception)
    {
        if (stristr($item, " {$exception} ") !== FALSE)
        {
            $item = preg_replace('/ ('.preg_quote($exception).') /i', ' $1 ', $item);
        }
    }
    return $item;
}

/** * From Tables * * This function implicitly groups FROM tables so there is no confusion * about operator precedence in harmony with SQL standards * * @access public * @param type * @return type */ function _from_tables($tables) { if (! is_array($tables)) { $tables = array($tables); }

    return implode(', ', $tables);
}

[/code]

[h3]For CI 1.6.2 [/h3] (quindo) To be able to use SQLite3 with CI 1.6.2 (and PHP 5.2.0) two additional Functions from sqlite_driver.php have to be copied into pdo_driver.php:

[code] // --------------------------------------------------------------------

/**
 * Set client character set
 *
 * @access    public
 * @param    string
 * @param    string
 * @return    resource
 */    
function db_set_charset($charset, $collation)
{
    // TODO - add support if needed
    return TRUE;
}

// --------------------------------------------------------------------

/**
 * Close DB Connection
 *
 * @access    public
 * @param    resource
 * @return    void
 */
function _close($conn_id)
{
    // Do nothing since PDO don't have close
}    

[/code]

[h3]Ok; you're ready to 'rock & roll'.[/h3]

SQLite use is beyond the intent of this WIKI posting; but, the following might give you an idea to get started. It loads directory file names (say, some .html web pages) into an SQLite database ... and, then, prints them to the browser for verification. For straightforward simplicity, it's procedural. I've, also, done it, using CI MVC classes, and it calculates my sidebar links in 0.02 seconds (per CI benchmarking) ... not TOO pokey!

[code] <?php /**

• articleLoader.php loads a directory file listing into an SQLite table.
• Place this script in the directory, containing files to be "loaded";
• then, run it.
• This needs to be run from the browser - not from the Editor. */

// Extend/inherit fileLister class from DirectoryIterator class. class fileLister extends DirectoryIterator { function __construct($path) { parent::__construct($path); }

function current()
{
    return parent::getFileName();
}

function valid()
{
    if(parent::valid())
    {
    // If parent is NOT a file (i.e. a directory), RETURN True
    if (!parent::isFile())
    {
    parent::next();
    return $this->valid();
    }
    return TRUE;
    }
    return FALSE;
}

function rewind()
{
    parent::rewind();
}

}

//============================================================

/**

• Create articles database and Insert directory files list.
• Establish SQLite DSN connection. Disk (rather than memory) access
• requires appending absolute path to the database-driver prefix. */

$dbConn = new PDO('sqlite:C:/xampp/htdocs/_/_articles/articles.SQLite');

// create the table $dbConn->exec("CREATE TABLE articlesTbl (articlesID INTEGER PRIMARY KEY, articleFileName TEXT UNIQUE)");

// create an Insert Prepared Statement $preparedStatement = $dbConn->prepare("INSERT INTO articlesTbl (articleFileName) VALUES (:articleFileName)");

// bind variable to Prepared Statement parameter $preparedStatement->bindParam(':articleFileName', $articleFileName_val);

// Insert the directory file list ... into the SQLite database table try { $articlesPath = $SERVER['DOCUMENT_ROOT'] . './/_articles/';

// Instantiate a fileLister object ... for $articlesPath
$fileList = new fileLister($articlesPath);

// Loop through the directory files
foreach ($fileList as $directoryFile)
{
    $articleFileName_val = $directoryFile;
    $preparedStatement->execute();
    // echo $directoryFile.'<br />';
}

} catch (Exception $e) { echo 'No files Found!
'; }

// SELECT, then display results print "

";
// query the connection table in associative array format
foreach ($dbConn->query('SELECT * FROM articles', PDO::FETCH_ASSOC) as $row)
{
print_r($row);
}
print "

";

?> [/code]

Enjoy ...

/Mike