--- /dev/null
+<?php
+
+namespace RedBeanPHP\QueryWriter;
+
+use RedBeanPHP\QueryWriter\AQueryWriter as AQueryWriter;
+use RedBeanPHP\QueryWriter as QueryWriter;
+use RedBeanPHP\Adapter\DBAdapter as DBAdapter;
+use RedBeanPHP\Adapter as Adapter;
+use RedBeanPHP\RedException\SQL as SQLException;
+
+/**
+ * RedBeanPHP SQLiteWriter with support for SQLite types
+ * This is a QueryWriter class for RedBeanPHP.
+ * This QueryWriter provides support for the SQLite database platform.
+ *
+ * @file RedBeanPHP/QueryWriter/SQLiteT.php
+ * @author Gabor de Mooij and the RedBeanPHP Community
+ * @license BSD/GPLv2
+ *
+ * @copyright
+ * (c) copyright G.J.G.T. (Gabor) de Mooij and the RedBeanPHP Community.
+ * This source file is subject to the BSD/GPLv2 License that is bundled
+ * with this source code in the file license.txt.
+ */
+class SQLiteT extends AQueryWriter implements QueryWriter
+{
+ /**
+ * Data types
+ */
+ const C_DATATYPE_INTEGER = 0;
+ const C_DATATYPE_NUMERIC = 1;
+ const C_DATATYPE_TEXT = 2;
+ const C_DATATYPE_SPECIFIED = 99;
+
+ /**
+ * @var DBAdapter
+ */
+ protected $adapter;
+
+ /**
+ * @var string
+ */
+ protected $quoteCharacter = '`';
+
+ /**
+ * Gets all information about a table (from a type).
+ *
+ * Format:
+ * array(
+ * name => name of the table
+ * columns => array( name => datatype )
+ * indexes => array() raw index information rows from PRAGMA query
+ * keys => array() raw key information rows from PRAGMA query
+ * )
+ *
+ * @param string $type type you want to get info of
+ *
+ * @return array
+ */
+ protected function getTable( $type )
+ {
+ $tableName = $this->esc( $type, TRUE );
+ $columns = $this->getColumns( $type );
+ $indexes = $this->getIndexes( $type );
+ $keys = $this->getKeyMapForType( $type );
+
+ $table = array(
+ 'columns' => $columns,
+ 'indexes' => $indexes,
+ 'keys' => $keys,
+ 'name' => $tableName
+ );
+
+ $this->tableArchive[$tableName] = $table;
+
+ return $table;
+ }
+
+ /**
+ * Puts a table. Updates the table structure.
+ * In SQLite we can't change columns, drop columns, change or add foreign keys so we
+ * have a table-rebuild function. You simply load your table with getTable(), modify it and
+ * then store it with putTable()...
+ *
+ * @param array $tableMap information array
+ *
+ * @return void
+ */
+ protected function putTable( $tableMap )
+ {
+ $table = $tableMap['name'];
+ $q = array();
+ $q[] = "DROP TABLE IF EXISTS tmp_backup;";
+
+ $oldColumnNames = array_keys( $this->getColumns( $table ) );
+
+ foreach ( $oldColumnNames as $k => $v ) $oldColumnNames[$k] = "`$v`";
+
+ $q[] = "CREATE TEMPORARY TABLE tmp_backup(" . implode( ",", $oldColumnNames ) . ");";
+ $q[] = "INSERT INTO tmp_backup SELECT * FROM `$table`;";
+ $q[] = "PRAGMA foreign_keys = 0 ";
+ $q[] = "DROP TABLE `$table`;";
+
+ $newTableDefStr = '';
+ foreach ( $tableMap['columns'] as $column => $type ) {
+ if ( $column != 'id' ) {
+ $newTableDefStr .= ",`$column` $type";
+ }
+ }
+
+ $fkDef = '';
+ foreach ( $tableMap['keys'] as $key ) {
+ $fkDef .= ", FOREIGN KEY(`{$key['from']}`)
+ REFERENCES `{$key['table']}`(`{$key['to']}`)
+ ON DELETE {$key['on_delete']} ON UPDATE {$key['on_update']}";
+ }
+
+ $q[] = "CREATE TABLE `$table` ( `id` INTEGER PRIMARY KEY AUTOINCREMENT $newTableDefStr $fkDef );";
+
+ foreach ( $tableMap['indexes'] as $name => $index ) {
+ if ( strpos( $name, 'UQ_' ) === 0 ) {
+ $cols = explode( '__', substr( $name, strlen( 'UQ_' . $table ) ) );
+ foreach ( $cols as $k => $v ) $cols[$k] = "`$v`";
+ $q[] = "CREATE UNIQUE INDEX $name ON `$table` (" . implode( ',', $cols ) . ")";
+ } else $q[] = "CREATE INDEX $name ON `$table` ({$index['name']}) ";
+ }
+
+ $q[] = "INSERT INTO `$table` SELECT * FROM tmp_backup;";
+ $q[] = "DROP TABLE tmp_backup;";
+ $q[] = "PRAGMA foreign_keys = 1 ";
+
+ foreach ( $q as $sq ) $this->adapter->exec( $sq );
+ }
+
+ /**
+ * Returns the an array describing the indexes for type $type.
+ *
+ * @param string $type type to describe indexes of
+ *
+ * @return array
+ */
+ protected function getIndexes( $type )
+ {
+ $table = $this->esc( $type, TRUE );
+ $indexes = $this->adapter->get( "PRAGMA index_list('$table')" );
+
+ $indexInfoList = array();
+ foreach ( $indexes as $i ) {
+ $indexInfoList[$i['name']] = $this->adapter->getRow( "PRAGMA index_info('{$i['name']}') " );
+
+ $indexInfoList[$i['name']]['unique'] = $i['unique'];
+ }
+
+ return $indexInfoList;
+ }
+
+ /**
+ * Adds a foreign key to a type.
+ * Note: cant put this in try-catch because that can hide the fact
+ * that database has been damaged.
+ *
+ * @param string $type type you want to modify table of
+ * @param string $targetType target type
+ * @param string $field field of the type that needs to get the fk
+ * @param string $targetField field where the fk needs to point to
+ * @param integer $buildopt 0 = NO ACTION, 1 = ON DELETE CASCADE
+ *
+ * @return boolean
+ */
+ protected function buildFK( $type, $targetType, $property, $targetProperty, $constraint = FALSE )
+ {
+ $table = $this->esc( $type, TRUE );
+ $targetTable = $this->esc( $targetType, TRUE );
+ $column = $this->esc( $property, TRUE );
+ $targetColumn = $this->esc( $targetProperty, TRUE );
+
+ $tables = $this->getTables();
+ if ( !in_array( $targetTable, $tables ) ) return FALSE;
+
+ if ( !is_null( $this->getForeignKeyForTypeProperty( $table, $column ) ) ) return FALSE;
+ $t = $this->getTable( $table );
+ $consSQL = ( $constraint ? 'CASCADE' : 'SET NULL' );
+ $label = 'from_' . $column . '_to_table_' . $targetTable . '_col_' . $targetColumn;
+ $t['keys'][$label] = array(
+ 'table' => $targetTable,
+ 'from' => $column,
+ 'to' => $targetColumn,
+ 'on_update' => $consSQL,
+ 'on_delete' => $consSQL
+ );
+ $this->putTable( $t );
+ return TRUE;
+ }
+
+ /**
+ * @see AQueryWriter::getKeyMapForType
+ */
+ protected function getKeyMapForType( $type )
+ {
+ $table = $this->esc( $type, TRUE );
+ $keys = $this->adapter->get( "PRAGMA foreign_key_list('$table')" );
+ $keyInfoList = array();
+ foreach ( $keys as $k ) {
+ $label = $this->makeFKLabel( $k['from'], $k['table'], $k['to'] );
+ $keyInfoList[$label] = array(
+ 'name' => $label,
+ 'from' => $k['from'],
+ 'table' => $k['table'],
+ 'to' => $k['to'],
+ 'on_update' => $k['on_update'],
+ 'on_delete' => $k['on_delete']
+ );
+ }
+ return $keyInfoList;
+ }
+
+ /**
+ * Constructor
+ *
+ * @param Adapter $adapter Database Adapter
+ */
+ public function __construct( Adapter $adapter )
+ {
+ $this->typeno_sqltype = array(
+ SQLiteT::C_DATATYPE_INTEGER => 'INTEGER',
+ SQLiteT::C_DATATYPE_NUMERIC => 'NUMERIC',
+ SQLiteT::C_DATATYPE_TEXT => 'TEXT',
+ );
+
+ $this->sqltype_typeno = array();
+
+ foreach ( $this->typeno_sqltype as $k => $v ) {
+ $this->sqltype_typeno[$v] = $k;
+ }
+
+ $this->adapter = $adapter;
+ }
+
+ /**
+ * This method returns the datatype to be used for primary key IDS and
+ * foreign keys. Returns one if the data type constants.
+ *
+ * @return integer $const data type to be used for IDS.
+ */
+ public function getTypeForID()
+ {
+ return self::C_DATATYPE_INTEGER;
+ }
+
+ /**
+ * @see QueryWriter::scanType
+ */
+ public function scanType( $value, $flagSpecial = FALSE )
+ {
+ $this->svalue = $value;
+
+ if ( $value === NULL ) return self::C_DATATYPE_INTEGER;
+ if ( $value === INF ) return self::C_DATATYPE_TEXT;
+
+ if ( $this->startsWithZeros( $value ) ) return self::C_DATATYPE_TEXT;
+
+ if ( $value === TRUE || $value === FALSE ) return self::C_DATATYPE_INTEGER;
+
+ if ( is_numeric( $value ) && ( intval( $value ) == $value ) && $value < 2147483648 && $value > -2147483648 ) return self::C_DATATYPE_INTEGER;
+
+ if ( ( is_numeric( $value ) && $value < 2147483648 && $value > -2147483648)
+ || preg_match( '/\d{4}\-\d\d\-\d\d/', $value )
+ || preg_match( '/\d{4}\-\d\d\-\d\d\s\d\d:\d\d:\d\d/', $value )
+ ) {
+ return self::C_DATATYPE_NUMERIC;
+ }
+
+ return self::C_DATATYPE_TEXT;
+ }
+
+ /**
+ * @see QueryWriter::addColumn
+ */
+ public function addColumn( $table, $column, $type )
+ {
+ $column = $this->check( $column );
+ $table = $this->check( $table );
+ $type = $this->typeno_sqltype[$type];
+
+ $this->adapter->exec( "ALTER TABLE `$table` ADD `$column` $type " );
+ }
+
+ /**
+ * @see QueryWriter::code
+ */
+ public function code( $typedescription, $includeSpecials = FALSE )
+ {
+ $r = ( ( isset( $this->sqltype_typeno[$typedescription] ) ) ? $this->sqltype_typeno[$typedescription] : 99 );
+
+ return $r;
+ }
+
+ /**
+ * @see QueryWriter::widenColumn
+ */
+ public function widenColumn( $type, $column, $datatype )
+ {
+ $t = $this->getTable( $type );
+
+ $t['columns'][$column] = $this->typeno_sqltype[$datatype];
+
+ $this->putTable( $t );
+ }
+
+ /**
+ * @see QueryWriter::getTables();
+ */
+ public function getTables()
+ {
+ return $this->adapter->getCol( "SELECT name FROM sqlite_master
+ WHERE type='table' AND name!='sqlite_sequence';" );
+ }
+
+ /**
+ * @see QueryWriter::createTable
+ */
+ public function createTable( $table )
+ {
+ $table = $this->esc( $table );
+
+ $sql = "CREATE TABLE $table ( id INTEGER PRIMARY KEY AUTOINCREMENT ) ";
+
+ $this->adapter->exec( $sql );
+ }
+
+ /**
+ * @see QueryWriter::getColumns
+ */
+ public function getColumns( $table )
+ {
+ $table = $this->esc( $table, TRUE );
+
+ $columnsRaw = $this->adapter->get( "PRAGMA table_info('$table')" );
+
+ $columns = array();
+ foreach ( $columnsRaw as $r ) $columns[$r['name']] = $r['type'];
+
+ return $columns;
+ }
+
+ /**
+ * @see QueryWriter::addUniqueIndex
+ */
+ public function addUniqueConstraint( $type, $properties )
+ {
+ $tableNoQ = $this->esc( $type, TRUE );
+ $name = 'UQ_' . $this->esc( $type, TRUE ) . implode( '__', $properties );
+ $t = $this->getTable( $type );
+ $t['indexes'][$name] = array( 'name' => $name );
+ try {
+ $this->putTable( $t );
+ } catch( SQLException $e ) {
+ return FALSE;
+ }
+ return TRUE;
+ }
+
+ /**
+ * @see QueryWriter::sqlStateIn
+ */
+ public function sqlStateIn( $state, $list )
+ {
+ $stateMap = array(
+ 'HY000' => QueryWriter::C_SQLSTATE_NO_SUCH_TABLE,
+ '23000' => QueryWriter::C_SQLSTATE_INTEGRITY_CONSTRAINT_VIOLATION
+ );
+
+ return in_array( ( isset( $stateMap[$state] ) ? $stateMap[$state] : '0' ), $list );
+ }
+
+ /**
+ * @see QueryWriter::addIndex
+ */
+ public function addIndex( $type, $name, $column )
+ {
+ $columns = $this->getColumns( $type );
+ if ( !isset( $columns[$column] ) ) return FALSE;
+
+ $table = $this->esc( $type );
+ $name = preg_replace( '/\W/', '', $name );
+ $column = $this->esc( $column, TRUE );
+
+ try {
+ $t = $this->getTable( $type );
+ $t['indexes'][$name] = array( 'name' => $column );
+ $this->putTable( $t );
+ return TRUE;
+ } catch( SQLException $exception ) {
+ return FALSE;
+ }
+ }
+
+ /**
+ * @see QueryWriter::wipe
+ */
+ public function wipe( $type )
+ {
+ $table = $this->esc( $type );
+
+ $this->adapter->exec( "DELETE FROM $table " );
+ }
+
+ /**
+ * @see QueryWriter::addFK
+ */
+ public function addFK( $type, $targetType, $property, $targetProperty, $isDep = FALSE )
+ {
+ return $this->buildFK( $type, $targetType, $property, $targetProperty, $isDep );
+ }
+
+ /**
+ * @see QueryWriter::wipeAll
+ */
+ public function wipeAll()
+ {
+ $this->adapter->exec( 'PRAGMA foreign_keys = 0 ' );
+
+ foreach ( $this->getTables() as $t ) {
+ try {
+ $this->adapter->exec( "DROP TABLE IF EXISTS `$t`" );
+ } catch ( SQLException $e ) {
+ }
+
+ try {
+ $this->adapter->exec( "DROP TABLE IF EXISTS `$t`" );
+ } catch ( SQLException $e ) {
+ }
+ }
+
+ $this->adapter->exec( 'PRAGMA foreign_keys = 1 ' );
+ }
+}
projects / yaffs-website / blobdiff