<?php
/**
* Habari database specific connection class
*
* @package Habari
*/
class SQLiteConnection extends DatabaseConnection
{
/**
* database specific SQL translation function, loosely modelled on the
* internationalization _t() function
* Call with a database independent SQL string and it will be translated
* to a SQLite specific SQL string
*
* @param sql database independent SQL
* @return string translated SQL string
*/
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;
}
/**
* Replaces the MySQL CONCAT function with SQLite-compatible statements
* @todo needs work, kind of sucky conversion
* @param array $matches Matches from the regex in sql_t()
* @return string The replacement for the MySQL SQL
* @see SQLiteConnection::sql_t()
*/
function replace_concat( $matches )
{
$innards = explode( ',', $matches[1] );
return implode( ' || ', $innards );
}
/**
* Connect to SQLite
* Overrides the DatabaseConnection to return false if the SQLite file doesn't exist.
*
* @param connection_string string a PDO connection string
* @param db_user string the database user name
* @param db_pass string the database user password
* @return boolean True if connection succeeded, false if not.
*/
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;
}
/**
* automatic diffing function - used for determining required database upgrades
* based on Owen Winkler's microwiki upgrade function
*
* @param queries array of create table and insert statements which constitute a fresh install
* @param (optional) execute should the queries be executed against the database or just simulated. default = true
* @param (optional) silent silent running with no messages printed? default = true
* @return string translated SQL string
*/
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;
}
/**
* Execute a stored procedure
*
* @param procedure name of the stored procedure
* @param args arguments for the procedure
* @return mixed whatever the procedure returns...
* Not supported with SQLite
*/
public function execute_procedure( $procedure, $args = array() )
{
die( _t( 'not yet supported on SQLite' ) );
}
/**
* Run all of the upgrades slated for pre-dbdelta since the last database revision.
*
* @param integer $old_version The current version of the database that is being upgraded
* @return boolean True on success
*/
public function upgrade_pre( $old_version, $upgrade_path = '' )
{
return parent::upgrade( $old_version, dirname(__FILE__) . '/upgrades/pre');
}
/**
* Run all of the upgrades slated for post-dbdelta since the last database revision.
*
* @param integer $old_version The current version of the database that is being upgraded
* @return boolean True on success
*/
public function upgrade_post( $old_version, $upgrade_path = '' )
{
return parent::upgrade( $old_version, dirname(__FILE__) . '/upgrades/post');
}
/**
* Filter out the fieldnames from whole pragma rows
*
* @param StdClass $row A row result from a SQLite PRAGMA table_info query
* @return string The name of the associated field
*/
protected function filter_fieldnames($row)
{
return $row->name;
}
}
?>