PDO SQLite3

INTRODUCTION/BACKGROUND PHP5 core now includes the SQLite (file) database and PDO (PHP Data Objects) database abstraction layer.

SQLite is a (relatively) simple, small file-based database, written in C (if memory serves). SQLite is bullet fast (yes, that means faster than mySQL - for small stuff). A temporary SQLite database can even be used in memory (and forgotten, afterwards) - very PHP-like. That said, SQLite has been used for fairly large applications; there are many 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.

It seems that SQLite3 (the PHP default) requires PDO access, rather than just by PHP calls, as was the case with 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 abstraction layer, offers generic database programatic acess, which depends upon a "driver" from each database vendor - to implement the specifics of that vendor's database offering.

CodeIgniter IMPLEMENTATION 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.

/system/database/drivers/pdo/pdo_driver.php /system/database/drivers/pdo/pdo_result.php

Contents of those files follow; just copy and paste the following into new files in the above path:

PDO_DRIVER.PHP:

<?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);
}

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

/**
 * 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;
}

}

?>

PDO_RESULT.PHP:

<?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); }

}

?>

=================

Ok; you're ready to 'rock & roll'.

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!

============= <?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 &#40;i.e. a directory&#41;, RETURN True
        if (!parent::isFile&#40;&#41;)
            {
                parent::next();
                return $this->valid();
            }
        return TRUE;
        }
    return FALSE;
}

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

} // end class parenthesis

// create articles database and Insert directory files list

// establish SQLite DSN connection // For 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 />';
    }
}                                                  // end try parenthesis

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

// 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 "

";

?>

=============

Enjoy ...

/Mike

PDO SQLite3

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!