system/schema/sqlite/connection.php

<?php
namespace Habari;

class SQLiteConnection extends DatabaseConnection
{
  function sql_t( $sql )
  {
    $sql = preg_replace_callback( '%concat\(([^)]+?)\)%i', array( &$this, 'replace_concat' ), $sql );
    $sql = preg_replace( '%DATE_SUB\s*\(\s*NOW\(\s*\)\s*,\s*INTERVAL\s+([0-9]+)\s+DAY\s*\)%ims', 'date(\'now\', \'-${1} days\')', $sql );
    $sql = preg_replace( '%OPTIMIZE TABLE ([^ ]*)%i', 'VACUUM;', $sql );
    //$sql= preg_replace( '%YEAR\s*\(\s*([^ ]*)\s*\)%ims', 'strftime(\'%Y\', ${1})', $sql );
    //$sql= preg_replace( '%MONTH\s*\(\s*([^ ]*)\s*\)%ims', 'strftime(\'%m\', ${1})', $sql );
    //$sql= preg_replace( '%DAY\s*\(\s*([^ ]*)\s*\)%ims', 'strftime(\'%d\', ${1})', $sql );
    $sql = preg_replace( '%YEAR\s*\(\s*FROM_UNIXTIME\s*\(\s*([^ ]*)\s*\)\s*\)%ims', 'strftime(\'%Y\', ${1}, \'unixepoch\')', $sql );
    $sql = preg_replace( '%MONTH\s*\(\s*FROM_UNIXTIME\s*\(\s*([^ ]*)\s*\)\s*\)%ims', 'strftime(\'%m\', ${1}, \'unixepoch\')', $sql );
    $sql = preg_replace( '%DAY\s*\(\s*FROM_UNIXTIME\s*\(\s*([^ ]*)\s*\)\s*\)%ims', 'strftime(\'%d\', ${1}, \'unixepoch\')', $sql );
    $sql = preg_replace( '%TRUNCATE \s*([^ ]*)%i', 'DELETE FROM ${1}', $sql );
    $sql = preg_replace( '%RAND\s*\(\s*\)%i', 'RANDOM()', $sql );
    return $sql;
  }

  function replace_concat( $matches )
  {
    $innards = explode( ',', $matches[1] );
    return implode( ' || ', $innards );
  }

  public function connect( $connect_string, $db_user, $db_pass )
  {
    list( $type, $file )= explode( ':', $connect_string, 2 );
    if ( $file == basename( $file ) ) {
      if ( file_exists( HABARI_PATH . '/' . $file ) ) {
        $file = HABARI_PATH . '/' . $file;
      }
      else {
        $file = HABARI_PATH . '/' . Site::get_path( 'user', true ) . $file;
      }
      $connect_string = implode( ':', array( $type, $file ) );
    }
    if ( file_exists( $file ) && !is_writable( $file ) ) {
      die( _t( 'Database file "%s" must be writable.', array($file) ) );
    }
    $conn = parent::connect( $connect_string, $db_user, $db_pass );
    $this->exec( 'PRAGMA synchronous = OFF' );
    return $conn;
  }

  function dbdelta( $queries, $execute = true, $silent = true, $doinserts = false )
  {
    if ( !is_array( $queries ) ) {
      $queries = explode( ';', $queries );
      if ( '' == $queries[count( $queries ) - 1] ) {
        array_pop( $queries );
      }
    }

    $cqueries = array();
    $indexqueries = array();
    $iqueries = array();
    $pqueries = array();
    $for_update = array();
    $allqueries = array();

    foreach ( $queries as $qry ) {
      if ( preg_match( "|CREATE TABLE ([^ ]*)|", $qry, $matches ) ) {
        $cqueries[strtolower( $matches[1] )] = $qry;
        $for_update[$matches[1]] = 'Created table '.$matches[1];
      }
      else if ( preg_match( "|CREATE (UNIQUE )?INDEX ([^ ]*)|", $qry, $matches ) ) {
        $indexqueries[] = $qry;
      }
      else if ( preg_match( "|INSERT INTO ([^ ]*)|", $qry, $matches ) ) {
        $iqueries[] = $qry;
      }
      else if ( preg_match( "|UPDATE ([^ ]*)|", $qry, $matches ) ) {
        $iqueries[] = $qry;
      }
      else if ( preg_match ( "|PRAGMA ([^ ]*)|", $qry, $matches ) ) {
        $pqueries[] = $qry;
      }
      else {
        // Unrecognized query type
      }
    }

    // Merge the queries into allqueries; pragmas MUST go first
    $allqueries = array_merge($pqueries);

    $tables = $this->get_column( "SELECT name FROM sqlite_master WHERE type = 'table';" );

    foreach ( $cqueries as $tablename => $query ) {
      if ( in_array( $tablename, $tables ) ) {
        $sql = $this->get_value( "SELECT sql FROM sqlite_master WHERE type = 'table' AND name='" . $tablename . "';" );
        $sql = preg_replace( '%\s+%', ' ', $sql ) . ';';
        $query = preg_replace( '%\s+%', ' ', $query );
        if ( $sql != $query ) {
          $this->query("ALTER TABLE {$tablename} RENAME TO {$tablename}__temp;");
          $this->query($query);

          $new_fields_temp = $this->get_results( "pragma table_info({$tablename});" );
          $new_fields = array();
          foreach ( $new_fields_temp as $field ) {
            $new_fields[$field->name] = $field;
          }
          $old_fields = $this->get_results( "pragma table_info({$tablename}__temp);" );
          $new_field_names = array_map(array($this, 'filter_fieldnames'), $new_fields);
          $old_field_names = array_map(array($this, 'filter_fieldnames'), $old_fields);
          $used_field_names = array_intersect($new_field_names, $old_field_names);
          $used_field_names = implode(',', $used_field_names);
          $needed_fields = array_diff($new_field_names, $old_field_names);
          foreach ( $needed_fields as $needed_field_name ) {
            $used_field_names .= ",'" . $new_fields[$needed_field_name]->dflt_value . "' as " . $needed_field_name;
          }

          $this->query("INSERT INTO {$tablename} SELECT {$used_field_names} FROM {$tablename}__temp;");
          $this->query("DROP TABLE {$tablename}__temp;");
        }
      }
      else {
        $allqueries[] = $query;
      }
    }

    // Drop all indices that we created, they'll get recreated by indexqueries below.
    // The other option would be to loop through the indices, comparing with indexqueries, and only drop the ones that have changed.
    $indices = DB::get_column( "SELECT name FROM sqlite_master WHERE type='index' AND name NOT LIKE 'sqlite_autoindex_%'" );
    foreach ( $indices as $name ) {
      DB::exec( 'DROP INDEX ' . $name );
    }

    $allqueries = array_merge( $allqueries, $indexqueries );
    if ( $doinserts ) {
      $allqueries = array_merge( $allqueries, $iqueries );
    }

    if ( $execute ) {
      DB::exec( 'PRAGMA cache_size=4000' );
      foreach ( $allqueries as $query ) {
        if ( !$this->query( $query ) ) {
          $this->get_errors();
          return false;
        }
      }
    }

    return $allqueries;
  }

  public function execute_procedure( $procedure, $args = array() )
  {
    die( _t( 'not yet supported on SQLite' ) );
  }

  public function upgrade_pre( $old_version, $upgrade_path = '' )
  {
    return parent::upgrade( $old_version, dirname(__FILE__) . '/upgrades/pre');
  }

  public function upgrade_post( $old_version, $upgrade_path = '' )
  {
    return parent::upgrade( $old_version, dirname(__FILE__) . '/upgrades/post');
  }

  protected function filter_fieldnames($row)
  {
    return $row->name;
  }

}
?>