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
00027
00028
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
00123 }
00124 }
00125
00126
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
00165
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 ?>