1: <?php
  2: 
  3: namespace RedBeanPHP\QueryWriter;
  4: 
  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;
 10: 
 11: /**
 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.
 15:  *
 16:  * @file    RedBeanPHP/QueryWriter/SQLiteT.php
 17:  * @author  Gabor de Mooij and the RedBeanPHP Community
 18:  * @license BSD/GPLv2
 19:  *
 20:  * @copyright
 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.
 24:  */
 25: class SQLiteT extends AQueryWriter implements QueryWriter
 26: {
 27:     /**
 28:      * Data types
 29:      */
 30:     const C_DATATYPE_INTEGER   = 0;
 31:     const C_DATATYPE_NUMERIC   = 1;
 32:     const C_DATATYPE_TEXT      = 2;
 33:     const C_DATATYPE_SPECIFIED = 99;
 34: 
 35:     /**
 36:      * @var DBAdapter
 37:      */
 38:     protected $adapter;
 39: 
 40:     /**
 41:      * @var string
 42:      */
 43:     protected $quoteCharacter = '`';
 44: 
 45:     /**
 46:      * Gets all information about a table (from a type).
 47:      *
 48:      * Format:
 49:      * array(
 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
 54:      * )
 55:      *
 56:      * @param string $type type you want to get info of
 57:      *
 58:      * @return array
 59:      */
 60:     protected function getTable( $type )
 61:     {
 62:         $tableName = $this->esc( $type, TRUE );
 63:         $columns   = $this->getColumns( $type );
 64:         $indexes   = $this->getIndexes( $type );
 65:         $keys      = $this->getKeyMapForType( $type );
 66: 
 67:         $table = array(
 68:             'columns' => $columns,
 69:             'indexes' => $indexes,
 70:             'keys' => $keys,
 71:             'name' => $tableName
 72:         );
 73: 
 74:         $this->tableArchive[$tableName] = $table;
 75: 
 76:         return $table;
 77:     }
 78: 
 79:     /**
 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()...
 84:      *
 85:      * @param array $tableMap information array
 86:      *
 87:      * @return void
 88:      */
 89:     protected function putTable( $tableMap )
 90:     {
 91:         $table = $tableMap['name'];
 92:         $q     = array();
 93:         $q[]   = "DROP TABLE IF EXISTS tmp_backup;";
 94: 
 95:         $oldColumnNames = array_keys( $this->getColumns( $table ) );
 96: 
 97:         foreach ( $oldColumnNames as $k => $v ) $oldColumnNames[$k] = "`$v`";
 98: 
 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`;";
103: 
104:         $newTableDefStr = '';
105:         foreach ( $tableMap['columns'] as $column => $type ) {
106:             if ( $column != 'id' ) {
107:                 $newTableDefStr .= ",`$column` $type";
108:             }
109:         }
110: 
111:         $fkDef = '';
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']}";
116:         }
117: 
118:         $q[] = "CREATE TABLE `$table` ( `id` INTEGER PRIMARY KEY AUTOINCREMENT  $newTableDefStr  $fkDef );";
119: 
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']}) ";
126:         }
127: 
128:         $q[] = "INSERT INTO `$table` SELECT * FROM tmp_backup;";
129:         $q[] = "DROP TABLE tmp_backup;";
130:         $q[] = "PRAGMA foreign_keys = 1 ";
131: 
132:         foreach ( $q as $sq ) $this->adapter->exec( $sq );
133:     }
134: 
135:     /**
136:      * Returns the an array describing the indexes for type $type.
137:      *
138:      * @param string $type type to describe indexes of
139:      *
140:      * @return array
141:      */
142:     protected function getIndexes( $type )
143:     {
144:         $table   = $this->esc( $type, TRUE );
145:         $indexes = $this->adapter->get( "PRAGMA index_list('$table')" );
146: 
147:         $indexInfoList = array();
148:         foreach ( $indexes as $i ) {
149:             $indexInfoList[$i['name']] = $this->adapter->getRow( "PRAGMA index_info('{$i['name']}') " );
150: 
151:             $indexInfoList[$i['name']]['unique'] = $i['unique'];
152:         }
153: 
154:         return $indexInfoList;
155:     }
156: 
157:     /**
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.
161:      *
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
167:      *
168:      * @return boolean
169:      */
170:     protected function buildFK( $type, $targetType, $property, $targetProperty, $constraint = FALSE )
171:     {
172:         $table           = $this->esc( $type, TRUE );
173:         $targetTable     = $this->esc( $targetType, TRUE );
174:         $column          = $this->esc( $property, TRUE );
175:         $targetColumn    = $this->esc( $targetProperty, TRUE );
176: 
177:         $tables = $this->getTables();
178:         if ( !in_array( $targetTable, $tables ) ) return FALSE;
179: 
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,
186:             'from'      => $column,
187:             'to'        => $targetColumn,
188:             'on_update' => $consSQL,
189:             'on_delete' => $consSQL
190:         );
191:         $this->putTable( $t );
192:         return TRUE;
193:     }
194: 
195:     /**
196:      * @see AQueryWriter::getKeyMapForType
197:      */
198:     protected function getKeyMapForType( $type )
199:     {
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(
206:                 'name'          => $label,
207:                 'from'          => $k['from'],
208:                 'table'         => $k['table'],
209:                 'to'            => $k['to'],
210:                 'on_update'     => $k['on_update'],
211:                 'on_delete'     => $k['on_delete']
212:             );
213:         }
214:         return $keyInfoList;
215:     }
216: 
217:     /**
218:      * Constructor
219:      *
220:      * @param Adapter $adapter Database Adapter
221:      */
222:     public function __construct( Adapter $adapter )
223:     {
224:         $this->typeno_sqltype = array(
225:             SQLiteT::C_DATATYPE_INTEGER => 'INTEGER',
226:             SQLiteT::C_DATATYPE_NUMERIC => 'NUMERIC',
227:             SQLiteT::C_DATATYPE_TEXT    => 'TEXT',
228:         );
229: 
230:         $this->sqltype_typeno = array();
231: 
232:         foreach ( $this->typeno_sqltype as $k => $v ) {
233:             $this->sqltype_typeno[$v] = $k;
234:         }
235: 
236:         $this->adapter = $adapter;
237:     }
238: 
239:     /**
240:      * This method returns the datatype to be used for primary key IDS and
241:      * foreign keys. Returns one if the data type constants.
242:      *
243:      * @return integer $const data type to be used for IDS.
244:      */
245:     public function getTypeForID()
246:     {
247:         return self::C_DATATYPE_INTEGER;
248:     }
249: 
250:     /**
251:      * @see QueryWriter::scanType
252:      */
253:     public function scanType( $value, $flagSpecial = FALSE )
254:     {
255:         $this->svalue = $value;
256: 
257:         if ( $value === NULL ) return self::C_DATATYPE_INTEGER;
258:         if ( $value === INF ) return self::C_DATATYPE_TEXT;
259: 
260:         if ( $this->startsWithZeros( $value ) ) return self::C_DATATYPE_TEXT;
261: 
262:         if ( $value === TRUE || $value === FALSE )  return self::C_DATATYPE_INTEGER;
263: 
264:         if ( is_numeric( $value ) && ( intval( $value ) == $value ) && $value < 2147483648 && $value > -2147483648 ) return self::C_DATATYPE_INTEGER;
265: 
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 )
269:         ) {
270:             return self::C_DATATYPE_NUMERIC;
271:         }
272: 
273:         return self::C_DATATYPE_TEXT;
274:     }
275: 
276:     /**
277:      * @see QueryWriter::addColumn
278:      */
279:     public function addColumn( $table, $column, $type )
280:     {
281:         $column = $this->check( $column );
282:         $table  = $this->check( $table );
283:         $type   = $this->typeno_sqltype[$type];
284: 
285:         $this->adapter->exec( "ALTER TABLE `$table` ADD `$column` $type " );
286:     }
287: 
288:     /**
289:      * @see QueryWriter::code
290:      */
291:     public function code( $typedescription, $includeSpecials = FALSE )
292:     {
293:         $r = ( ( isset( $this->sqltype_typeno[$typedescription] ) ) ? $this->sqltype_typeno[$typedescription] : 99 );
294: 
295:         return $r;
296:     }
297: 
298:     /**
299:      * @see QueryWriter::widenColumn
300:      */
301:     public function widenColumn( $type, $column, $datatype )
302:     {
303:         $t = $this->getTable( $type );
304: 
305:         $t['columns'][$column] = $this->typeno_sqltype[$datatype];
306: 
307:         $this->putTable( $t );
308:     }
309: 
310:     /**
311:      * @see QueryWriter::getTables();
312:      */
313:     public function getTables()
314:     {
315:         return $this->adapter->getCol( "SELECT name FROM sqlite_master
316:             WHERE type='table' AND name!='sqlite_sequence';" );
317:     }
318: 
319:     /**
320:      * @see QueryWriter::createTable
321:      */
322:     public function createTable( $table )
323:     {
324:         $table = $this->esc( $table );
325: 
326:         $sql   = "CREATE TABLE $table ( id INTEGER PRIMARY KEY AUTOINCREMENT ) ";
327: 
328:         $this->adapter->exec( $sql );
329:     }
330: 
331:     /**
332:      * @see QueryWriter::getColumns
333:      */
334:     public function getColumns( $table )
335:     {
336:         $table      = $this->esc( $table, TRUE );
337: 
338:         $columnsRaw = $this->adapter->get( "PRAGMA table_info('$table')" );
339: 
340:         $columns    = array();
341:         foreach ( $columnsRaw as $r ) $columns[$r['name']] = $r['type'];
342: 
343:         return $columns;
344:     }
345: 
346:     /**
347:      * @see QueryWriter::addUniqueIndex
348:      */
349:     public function addUniqueConstraint( $type, $properties )
350:     {
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 );
355:         try {
356:             $this->putTable( $t );
357:         } catch( SQLException $e ) {
358:             return FALSE;
359:         }
360:         return TRUE;
361:     }
362: 
363:     /**
364:      * @see QueryWriter::sqlStateIn
365:      */
366:     public function sqlStateIn( $state, $list )
367:     {
368:         $stateMap = array(
369:             'HY000' => QueryWriter::C_SQLSTATE_NO_SUCH_TABLE,
370:             '23000' => QueryWriter::C_SQLSTATE_INTEGRITY_CONSTRAINT_VIOLATION
371:         );
372: 
373:         return in_array( ( isset( $stateMap[$state] ) ? $stateMap[$state] : '0' ), $list );
374:     }
375: 
376:     /**
377:      * @see QueryWriter::addIndex
378:      */
379:     public function addIndex( $type, $name, $column )
380:     {
381:         $columns = $this->getColumns( $type );
382:         if ( !isset( $columns[$column] ) ) return FALSE;
383: 
384:         $table  = $this->esc( $type );
385:         $name   = preg_replace( '/\W/', '', $name );
386:         $column = $this->esc( $column, TRUE );
387: 
388:         try {
389:             $t = $this->getTable( $type );
390:             $t['indexes'][$name] = array( 'name' => $column );
391:             $this->putTable( $t );
392:             return TRUE;
393:         } catch( SQLException $exception ) {
394:             return FALSE;
395:         }
396:     }
397: 
398:     /**
399:      * @see QueryWriter::wipe
400:      */
401:     public function wipe( $type )
402:     {
403:         $table = $this->esc( $type );
404: 
405:         $this->adapter->exec( "DELETE FROM $table " );
406:     }
407: 
408:     /**
409:      * @see QueryWriter::addFK
410:      */
411:     public function addFK( $type, $targetType, $property, $targetProperty, $isDep = FALSE )
412:     {
413:         return $this->buildFK( $type, $targetType, $property, $targetProperty, $isDep );
414:     }
415: 
416:     /**
417:      * @see QueryWriter::wipeAll
418:      */
419:     public function wipeAll()
420:     {
421:         $this->adapter->exec( 'PRAGMA foreign_keys = 0 ' );
422: 
423:         foreach ( $this->getTables() as $t ) {
424:             try {
425:                 $this->adapter->exec( "DROP TABLE IF EXISTS `$t`" );
426:             } catch ( SQLException $e ) {
427:             }
428: 
429:             try {
430:                 $this->adapter->exec( "DROP TABLE IF EXISTS `$t`" );
431:             } catch ( SQLException $e ) {
432:             }
433:         }
434: 
435:         $this->adapter->exec( 'PRAGMA foreign_keys = 1 ' );
436:     }
437: }
438: