3 namespace RedBeanPHP\QueryWriter;
5 use RedBeanPHP\QueryWriter\AQueryWriter as AQueryWriter;
6 use RedBeanPHP\QueryWriter as QueryWriter;
7 use RedBeanPHP\Adapter\DBAdapter as DBAdapter;
8 use RedBeanPHP\Adapter as Adapter;
9 use RedBeanPHP\RedException\SQL as SQLException;
12 * RedBeanPHP SQLiteWriter with support for SQLite types
13 * This is a QueryWriter class for RedBeanPHP.
14 * This QueryWriter provides support for the SQLite database platform.
16 * @file RedBeanPHP/QueryWriter/SQLiteT.php
17 * @author Gabor de Mooij and the RedBeanPHP Community
21 * (c) copyright G.J.G.T. (Gabor) de Mooij and the RedBeanPHP Community.
22 * This source file is subject to the BSD/GPLv2 License that is bundled
23 * with this source code in the file license.txt.
25 class SQLiteT extends AQueryWriter implements QueryWriter
30 const C_DATATYPE_INTEGER = 0;
31 const C_DATATYPE_NUMERIC = 1;
32 const C_DATATYPE_TEXT = 2;
33 const C_DATATYPE_SPECIFIED = 99;
43 protected $quoteCharacter = '`';
46 * Gets all information about a table (from a type).
50 * name => name of the table
51 * columns => array( name => datatype )
52 * indexes => array() raw index information rows from PRAGMA query
53 * keys => array() raw key information rows from PRAGMA query
56 * @param string $type type you want to get info of
60 protected function getTable( $type )
62 $tableName = $this->esc( $type, TRUE );
63 $columns = $this->getColumns( $type );
64 $indexes = $this->getIndexes( $type );
65 $keys = $this->getKeyMapForType( $type );
68 'columns' => $columns,
69 'indexes' => $indexes,
74 $this->tableArchive[$tableName] = $table;
80 * Puts a table. Updates the table structure.
81 * In SQLite we can't change columns, drop columns, change or add foreign keys so we
82 * have a table-rebuild function. You simply load your table with getTable(), modify it and
83 * then store it with putTable()...
85 * @param array $tableMap information array
89 protected function putTable( $tableMap )
91 $table = $tableMap['name'];
93 $q[] = "DROP TABLE IF EXISTS tmp_backup;";
95 $oldColumnNames = array_keys( $this->getColumns( $table ) );
97 foreach ( $oldColumnNames as $k => $v ) $oldColumnNames[$k] = "`$v`";
99 $q[] = "CREATE TEMPORARY TABLE tmp_backup(" . implode( ",", $oldColumnNames ) . ");";
100 $q[] = "INSERT INTO tmp_backup SELECT * FROM `$table`;";
101 $q[] = "PRAGMA foreign_keys = 0 ";
102 $q[] = "DROP TABLE `$table`;";
104 $newTableDefStr = '';
105 foreach ( $tableMap['columns'] as $column => $type ) {
106 if ( $column != 'id' ) {
107 $newTableDefStr .= ",`$column` $type";
112 foreach ( $tableMap['keys'] as $key ) {
113 $fkDef .= ", FOREIGN KEY(`{$key['from']}`)
114 REFERENCES `{$key['table']}`(`{$key['to']}`)
115 ON DELETE {$key['on_delete']} ON UPDATE {$key['on_update']}";
118 $q[] = "CREATE TABLE `$table` ( `id` INTEGER PRIMARY KEY AUTOINCREMENT $newTableDefStr $fkDef );";
120 foreach ( $tableMap['indexes'] as $name => $index ) {
121 if ( strpos( $name, 'UQ_' ) === 0 ) {
122 $cols = explode( '__', substr( $name, strlen( 'UQ_' . $table ) ) );
123 foreach ( $cols as $k => $v ) $cols[$k] = "`$v`";
124 $q[] = "CREATE UNIQUE INDEX $name ON `$table` (" . implode( ',', $cols ) . ")";
125 } else $q[] = "CREATE INDEX $name ON `$table` ({$index['name']}) ";
128 $q[] = "INSERT INTO `$table` SELECT * FROM tmp_backup;";
129 $q[] = "DROP TABLE tmp_backup;";
130 $q[] = "PRAGMA foreign_keys = 1 ";
132 foreach ( $q as $sq ) $this->adapter->exec( $sq );
136 * Returns the an array describing the indexes for type $type.
138 * @param string $type type to describe indexes of
142 protected function getIndexes( $type )
144 $table = $this->esc( $type, TRUE );
145 $indexes = $this->adapter->get( "PRAGMA index_list('$table')" );
147 $indexInfoList = array();
148 foreach ( $indexes as $i ) {
149 $indexInfoList[$i['name']] = $this->adapter->getRow( "PRAGMA index_info('{$i['name']}') " );
151 $indexInfoList[$i['name']]['unique'] = $i['unique'];
154 return $indexInfoList;
158 * Adds a foreign key to a type.
159 * Note: cant put this in try-catch because that can hide the fact
160 * that database has been damaged.
162 * @param string $type type you want to modify table of
163 * @param string $targetType target type
164 * @param string $field field of the type that needs to get the fk
165 * @param string $targetField field where the fk needs to point to
166 * @param integer $buildopt 0 = NO ACTION, 1 = ON DELETE CASCADE
170 protected function buildFK( $type, $targetType, $property, $targetProperty, $constraint = FALSE )
172 $table = $this->esc( $type, TRUE );
173 $targetTable = $this->esc( $targetType, TRUE );
174 $column = $this->esc( $property, TRUE );
175 $targetColumn = $this->esc( $targetProperty, TRUE );
177 $tables = $this->getTables();
178 if ( !in_array( $targetTable, $tables ) ) return FALSE;
180 if ( !is_null( $this->getForeignKeyForTypeProperty( $table, $column ) ) ) return FALSE;
181 $t = $this->getTable( $table );
182 $consSQL = ( $constraint ? 'CASCADE' : 'SET NULL' );
183 $label = 'from_' . $column . '_to_table_' . $targetTable . '_col_' . $targetColumn;
184 $t['keys'][$label] = array(
185 'table' => $targetTable,
187 'to' => $targetColumn,
188 'on_update' => $consSQL,
189 'on_delete' => $consSQL
191 $this->putTable( $t );
196 * @see AQueryWriter::getKeyMapForType
198 protected function getKeyMapForType( $type )
200 $table = $this->esc( $type, TRUE );
201 $keys = $this->adapter->get( "PRAGMA foreign_key_list('$table')" );
202 $keyInfoList = array();
203 foreach ( $keys as $k ) {
204 $label = $this->makeFKLabel( $k['from'], $k['table'], $k['to'] );
205 $keyInfoList[$label] = array(
207 'from' => $k['from'],
208 'table' => $k['table'],
210 'on_update' => $k['on_update'],
211 'on_delete' => $k['on_delete']
220 * @param Adapter $adapter Database Adapter
222 public function __construct( Adapter $adapter )
224 $this->typeno_sqltype = array(
225 SQLiteT::C_DATATYPE_INTEGER => 'INTEGER',
226 SQLiteT::C_DATATYPE_NUMERIC => 'NUMERIC',
227 SQLiteT::C_DATATYPE_TEXT => 'TEXT',
230 $this->sqltype_typeno = array();
232 foreach ( $this->typeno_sqltype as $k => $v ) {
233 $this->sqltype_typeno[$v] = $k;
236 $this->adapter = $adapter;
240 * This method returns the datatype to be used for primary key IDS and
241 * foreign keys. Returns one if the data type constants.
243 * @return integer $const data type to be used for IDS.
245 public function getTypeForID()
247 return self::C_DATATYPE_INTEGER;
251 * @see QueryWriter::scanType
253 public function scanType( $value, $flagSpecial = FALSE )
255 $this->svalue = $value;
257 if ( $value === NULL ) return self::C_DATATYPE_INTEGER;
258 if ( $value === INF ) return self::C_DATATYPE_TEXT;
260 if ( $this->startsWithZeros( $value ) ) return self::C_DATATYPE_TEXT;
262 if ( $value === TRUE || $value === FALSE ) return self::C_DATATYPE_INTEGER;
264 if ( is_numeric( $value ) && ( intval( $value ) == $value ) && $value < 2147483648 && $value > -2147483648 ) return self::C_DATATYPE_INTEGER;
266 if ( ( is_numeric( $value ) && $value < 2147483648 && $value > -2147483648)
267 || preg_match( '/\d{4}\-\d\d\-\d\d/', $value )
268 || preg_match( '/\d{4}\-\d\d\-\d\d\s\d\d:\d\d:\d\d/', $value )
270 return self::C_DATATYPE_NUMERIC;
273 return self::C_DATATYPE_TEXT;
277 * @see QueryWriter::addColumn
279 public function addColumn( $table, $column, $type )
281 $column = $this->check( $column );
282 $table = $this->check( $table );
283 $type = $this->typeno_sqltype[$type];
285 $this->adapter->exec( "ALTER TABLE `$table` ADD `$column` $type " );
289 * @see QueryWriter::code
291 public function code( $typedescription, $includeSpecials = FALSE )
293 $r = ( ( isset( $this->sqltype_typeno[$typedescription] ) ) ? $this->sqltype_typeno[$typedescription] : 99 );
299 * @see QueryWriter::widenColumn
301 public function widenColumn( $type, $column, $datatype )
303 $t = $this->getTable( $type );
305 $t['columns'][$column] = $this->typeno_sqltype[$datatype];
307 $this->putTable( $t );
311 * @see QueryWriter::getTables();
313 public function getTables()
315 return $this->adapter->getCol( "SELECT name FROM sqlite_master
316 WHERE type='table' AND name!='sqlite_sequence';" );
320 * @see QueryWriter::createTable
322 public function createTable( $table )
324 $table = $this->esc( $table );
326 $sql = "CREATE TABLE $table ( id INTEGER PRIMARY KEY AUTOINCREMENT ) ";
328 $this->adapter->exec( $sql );
332 * @see QueryWriter::getColumns
334 public function getColumns( $table )
336 $table = $this->esc( $table, TRUE );
338 $columnsRaw = $this->adapter->get( "PRAGMA table_info('$table')" );
341 foreach ( $columnsRaw as $r ) $columns[$r['name']] = $r['type'];
347 * @see QueryWriter::addUniqueIndex
349 public function addUniqueConstraint( $type, $properties )
351 $tableNoQ = $this->esc( $type, TRUE );
352 $name = 'UQ_' . $this->esc( $type, TRUE ) . implode( '__', $properties );
353 $t = $this->getTable( $type );
354 $t['indexes'][$name] = array( 'name' => $name );
356 $this->putTable( $t );
357 } catch( SQLException $e ) {
364 * @see QueryWriter::sqlStateIn
366 public function sqlStateIn( $state, $list )
369 'HY000' => QueryWriter::C_SQLSTATE_NO_SUCH_TABLE,
370 '23000' => QueryWriter::C_SQLSTATE_INTEGRITY_CONSTRAINT_VIOLATION
373 return in_array( ( isset( $stateMap[$state] ) ? $stateMap[$state] : '0' ), $list );
377 * @see QueryWriter::addIndex
379 public function addIndex( $type, $name, $column )
381 $columns = $this->getColumns( $type );
382 if ( !isset( $columns[$column] ) ) return FALSE;
384 $table = $this->esc( $type );
385 $name = preg_replace( '/\W/', '', $name );
386 $column = $this->esc( $column, TRUE );
389 $t = $this->getTable( $type );
390 $t['indexes'][$name] = array( 'name' => $column );
391 $this->putTable( $t );
393 } catch( SQLException $exception ) {
399 * @see QueryWriter::wipe
401 public function wipe( $type )
403 $table = $this->esc( $type );
405 $this->adapter->exec( "DELETE FROM $table " );
409 * @see QueryWriter::addFK
411 public function addFK( $type, $targetType, $property, $targetProperty, $isDep = FALSE )
413 return $this->buildFK( $type, $targetType, $property, $targetProperty, $isDep );
417 * @see QueryWriter::wipeAll
419 public function wipeAll()
421 $this->adapter->exec( 'PRAGMA foreign_keys = 0 ' );
423 foreach ( $this->getTables() as $t ) {
425 $this->adapter->exec( "DROP TABLE IF EXISTS `$t`" );
426 } catch ( SQLException $e ) {
430 $this->adapter->exec( "DROP TABLE IF EXISTS `$t`" );
431 } catch ( SQLException $e ) {
435 $this->adapter->exec( 'PRAGMA foreign_keys = 1 ' );