PHP class for SQLite database (using PDO)

This class was created as a universal solution for small scripts that need to permanently store information in some kind of database. There are of course other ways like JSON/XML in files, or CDB, but using SQLite gives the flexibility of SQL without a need for any database server.

The following class uses PDO to interact with a database. It needs write permissions to a directory where the database file will be located. It turned out that the database becomes quite slow when tables grow to a half a million records so the class keeps the database in memory, flushing the data to a file when needed. It fits for a long lasting scripts because loading a large database to memory is not an easy task and script start may take some time.

This is a first version of the class and it needs some improvements, but it already helped me in a couple of tasks.

Here is a short usage snippet:

<?php

$db = new DB(
     DB_PATH.'db_name.sqlite', 
     'table_name', 
     array(
         'id' => 'INTEGER PRIMARY KEY AUTOINCREMENT', 
         'column1' => 'VARCHAR(1024) NOT NULL UNIQUE', 
         'column2' => 'VARCHAR(1024) NOT NULL'
     )
 );

?>

And the class:

<?php
class DB {

    protected $_db = null;
    protected $_db_file_name = null;
    
    protected $_table = null;
    protected $_table_structure = array();
    
    public function __construct($db_file_name, $table_name, $table_structure) {
        $this->_db_file_name = $db_file_name;
        $this->_table = $table_name;
        $this->_table_structure = $table_structure;
        
        if(!file_exists($this->_db_file_name))
            $this->init();
        else {
            $this->_db = new PDO('sqlite::memory:');
            $this->_db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->_db->exec('ATTACH "'.$this->_db_file_name.'" as filedb');
            $this->_db->exec('CREATE TABLE '.$this->_table.' AS SELECT * FROM filedb.'.$this->_table);
            $this->_db->exec('DETACH filedb');
        }
    }
    
    public function __destruct() {
        $this->flush();
    }

    public function flush() {
        $this->_db->exec('ATTACH "'.$this->_db_file_name.'" as filedb');
        if($this->_db->exec(
            'INSERT OR IGNORE INTO filedb.'.$this->_table.
           '('.implode(',', array_keys($this->_table_structure)).') SELECT * FROM '.$this->_table
        ) === false)
            $this->error();
        $this->_db->exec('DETACH filedb');
    }

    public function __get($name) {
        if (property_exists($this->_db, $name)) {
            return $this->_db->{$name};
        }
        else
            return $this->{$name};
    }

    public function __set($name, $value) {
        if (property_exists($this->_db, $name)) {
            $this->_db->{$name} = $value;
        }
        else
            $this->{$name} = $value;
    }


    public function __call($method, $args = array()) {
        if (method_exists($this->_db, $method)) {
            return call_user_func_array(array($this->_db, $method), $args);
        } else {
            return call_user_func_array(array($this, $method), $args);
        }
    }
    
    public function init() {
        file_put_contents($this->_db_file_name, '');
        $this->_db = new PDO('sqlite:'.$this->_db_file_name);
        $sql = 'CREATE TABLE '.$this->_table.'(';
        foreach($this->_table_structure as $key => $properties)
            $sql .= "`$key` $properties,";
        $sql = substr_replace($sql, ')', -1);
        if($this->_db->exec($sql) === false) {
            $this->error();
        }
    }

    public function clear($file = true) {
        $this->_db->exec('
            DELETE FROM '.$this->_table.' WHERE 1
        ');
        if($file) {
            $this->_db->exec('ATTACH "'.$this->_db_file_name.'" as filedb');
            $this->_db->exec('
                DELETE FROM filedb.'.$this->_table.' WHERE 1
            ');
            $this->_db->exec('DETACH filedb');
        }
    }
    
    public function error() {
        var_dump($this->_db->errorInfo());
        debug_print_backtrace();
        die;
    }
    
    public static function filterColumnName($str) {
        return "`$str`";
    }
    
    public function insert($values) {
        $stmt = $this->_db->prepare('INSERT OR IGNORE INTO '.$this->_table.
            '('.
                implode(',', array_map(array('DB', 'filterColumnName'), array_keys($values)))
            .') values('.
                implode(',', array_fill(0, count(array_values($values)), '?'))
            .')'
        );
        if($stmt->execute(array_values($values)) === false)
            $this->error();
    }

}
?>