• Main Page
  • Related Pages
  • Namespaces
  • Classes
  • Files
  • Examples
  • File List

system/schema/sqlite/connection.php

00001 <?php
00008 namespace Habari;
00009 
00010 class SQLiteConnection extends DatabaseConnection
00011 {
00021   function sql_t( $sql )
00022   {
00023     $sql = preg_replace_callback( '%concat\(([^)]+?)\)%i', array( &$this, 'replace_concat' ), $sql );
00024     $sql = preg_replace( '%DATE_SUB\s*\(\s*NOW\(\s*\)\s*,\s*INTERVAL\s+([0-9]+)\s+DAY\s*\)%ims', 'date(\'now\', \'-${1} days\')', $sql );
00025     $sql = preg_replace( '%OPTIMIZE TABLE ([^ ]*)%i', 'VACUUM;', $sql );
00026     //$sql= preg_replace( '%YEAR\s*\(\s*([^ ]*)\s*\)%ims', 'strftime(\'%Y\', ${1})', $sql );
00027     //$sql= preg_replace( '%MONTH\s*\(\s*([^ ]*)\s*\)%ims', 'strftime(\'%m\', ${1})', $sql );
00028     //$sql= preg_replace( '%DAY\s*\(\s*([^ ]*)\s*\)%ims', 'strftime(\'%d\', ${1})', $sql );
00029     $sql = preg_replace( '%YEAR\s*\(\s*FROM_UNIXTIME\s*\(\s*([^ ]*)\s*\)\s*\)%ims', 'strftime(\'%Y\', ${1}, \'unixepoch\')', $sql );
00030     $sql = preg_replace( '%MONTH\s*\(\s*FROM_UNIXTIME\s*\(\s*([^ ]*)\s*\)\s*\)%ims', 'strftime(\'%m\', ${1}, \'unixepoch\')', $sql );
00031     $sql = preg_replace( '%DAY\s*\(\s*FROM_UNIXTIME\s*\(\s*([^ ]*)\s*\)\s*\)%ims', 'strftime(\'%d\', ${1}, \'unixepoch\')', $sql );
00032     $sql = preg_replace( '%TRUNCATE \s*([^ ]*)%i', 'DELETE FROM ${1}', $sql );
00033     $sql = preg_replace( '%RAND\s*\(\s*\)%i', 'RANDOM()', $sql );
00034     return $sql;
00035   }
00036 
00044   function replace_concat( $matches )
00045   {
00046     $innards = explode( ',', $matches[1] );
00047     return implode( ' || ', $innards );
00048   }
00049 
00059   public function connect( $connect_string, $db_user, $db_pass )
00060   {
00061     list( $type, $file )= explode( ':', $connect_string, 2 );
00062     if ( $file == basename( $file ) ) {
00063       if ( file_exists( HABARI_PATH . '/' . $file ) ) {
00064         $file = HABARI_PATH . '/' . $file;
00065       }
00066       else {
00067         $file = HABARI_PATH . '/' . Site::get_path( 'user', true ) . $file;
00068       }
00069       $connect_string = implode( ':', array( $type, $file ) );
00070     }
00071     if ( file_exists( $file ) && !is_writable( $file ) ) {
00072       die( _t( 'Database file "%s" must be writable.', array($file) ) );
00073     }
00074     $conn = parent::connect( $connect_string, $db_user, $db_pass );
00075     $this->exec( 'PRAGMA synchronous = OFF' );
00076     return $conn;
00077   }
00078 
00088   function dbdelta( $queries, $execute = true, $silent = true, $doinserts = false )
00089   {
00090     if ( !is_array( $queries ) ) {
00091       $queries = explode( ';', $queries );
00092       if ( '' == $queries[count( $queries ) - 1] ) {
00093         array_pop( $queries );
00094       }
00095     }
00096 
00097     $cqueries = array();
00098     $indexqueries = array();
00099     $iqueries = array();
00100     $pqueries = array();
00101     $for_update = array();
00102     $allqueries = array();
00103 
00104     foreach ( $queries as $qry ) {
00105       if ( preg_match( "|CREATE TABLE ([^ ]*)|", $qry, $matches ) ) {
00106         $cqueries[strtolower( $matches[1] )] = $qry;
00107         $for_update[$matches[1]] = 'Created table '.$matches[1];
00108       }
00109       else if ( preg_match( "|CREATE (UNIQUE )?INDEX ([^ ]*)|", $qry, $matches ) ) {
00110         $indexqueries[] = $qry;
00111       }
00112       else if ( preg_match( "|INSERT INTO ([^ ]*)|", $qry, $matches ) ) {
00113         $iqueries[] = $qry;
00114       }
00115       else if ( preg_match( "|UPDATE ([^ ]*)|", $qry, $matches ) ) {
00116         $iqueries[] = $qry;
00117       }
00118       else if ( preg_match ( "|PRAGMA ([^ ]*)|", $qry, $matches ) ) {
00119         $pqueries[] = $qry;
00120       }
00121       else {
00122         // Unrecognized query type
00123       }
00124     }
00125 
00126     // Merge the queries into allqueries; pragmas MUST go first
00127     $allqueries = array_merge($pqueries);
00128 
00129     $tables = $this->get_column( "SELECT name FROM sqlite_master WHERE type = 'table';" );
00130 
00131     foreach ( $cqueries as $tablename => $query ) {
00132       if ( in_array( $tablename, $tables ) ) {
00133         $sql = $this->get_value( "SELECT sql FROM sqlite_master WHERE type = 'table' AND name='" . $tablename . "';" );
00134         $sql = preg_replace( '%\s+%', ' ', $sql ) . ';';
00135         $query = preg_replace( '%\s+%', ' ', $query );
00136         if ( $sql != $query ) {
00137           $this->query("ALTER TABLE {$tablename} RENAME TO {$tablename}__temp;");
00138           $this->query($query);
00139 
00140           $new_fields_temp = $this->get_results( "pragma table_info({$tablename});" );
00141           $new_fields = array();
00142           foreach ( $new_fields_temp as $field ) {
00143             $new_fields[$field->name] = $field;
00144           }
00145           $old_fields = $this->get_results( "pragma table_info({$tablename}__temp);" );
00146           $new_field_names = array_map(array($this, 'filter_fieldnames'), $new_fields);
00147           $old_field_names = array_map(array($this, 'filter_fieldnames'), $old_fields);
00148           $used_field_names = array_intersect($new_field_names, $old_field_names);
00149           $used_field_names = implode(',', $used_field_names);
00150           $needed_fields = array_diff($new_field_names, $old_field_names);
00151           foreach ( $needed_fields as $needed_field_name ) {
00152             $used_field_names .= ",'" . $new_fields[$needed_field_name]->dflt_value . "' as " . $needed_field_name;
00153           }
00154 
00155           $this->query("INSERT INTO {$tablename} SELECT {$used_field_names} FROM {$tablename}__temp;");
00156           $this->query("DROP TABLE {$tablename}__temp;");
00157         }
00158       }
00159       else {
00160         $allqueries[] = $query;
00161       }
00162     }
00163 
00164     // Drop all indices that we created, they'll get recreated by indexqueries below.
00165     // The other option would be to loop through the indices, comparing with indexqueries, and only drop the ones that have changed.
00166     $indices = DB::get_column( "SELECT name FROM sqlite_master WHERE type='index' AND name NOT LIKE 'sqlite_autoindex_%'" );
00167     foreach ( $indices as $name ) {
00168       DB::exec( 'DROP INDEX ' . $name );
00169     }
00170 
00171     $allqueries = array_merge( $allqueries, $indexqueries );
00172     if ( $doinserts ) {
00173       $allqueries = array_merge( $allqueries, $iqueries );
00174     }
00175 
00176     if ( $execute ) {
00177       DB::exec( 'PRAGMA cache_size=4000' );
00178       foreach ( $allqueries as $query ) {
00179         if ( !$this->query( $query ) ) {
00180           $this->get_errors();
00181           return false;
00182         }
00183       }
00184     }
00185 
00186     return $allqueries;
00187   }
00188 
00197   public function execute_procedure( $procedure, $args = array() )
00198   {
00199     die( _t( 'not yet supported on SQLite' ) );
00200   }
00201 
00208   public function upgrade_pre( $old_version, $upgrade_path = '' )
00209   {
00210     return parent::upgrade( $old_version, dirname(__FILE__) . '/upgrades/pre');
00211   }
00212 
00219   public function upgrade_post( $old_version, $upgrade_path = '' )
00220   {
00221     return parent::upgrade( $old_version, dirname(__FILE__) . '/upgrades/post');
00222   }
00223 
00230   protected function filter_fieldnames($row)
00231   {
00232     return $row->name;
00233   }
00234 
00235 }
00236 ?>

Generated on Sun Aug 4 2013 12:51:44 for Habari by  doxygen 1.7.1