Details
-
Improvement
-
Status: Open
-
Major
-
Resolution: Unresolved
-
Ubuntu Natty
PHP 5.3.6
SimpleTest
Sqlite
Description
I need improvement of this component since implementation of sqlitewriter has been trying to emulate ALTER FIELD ( Sqlite had not native syntax to achieve this ), but forgot to restore the index attached on it.
ezcDbSchemaComparator component emulate this altering field with this logic:
- reading the schema of changed table
- create temporary table, and then copy data of the changed table to temporary
- drop the original change table
- create new schema of the original table with modified schema
- transfer back the data from temporary to the new one
Unfortunately, this logic forgot to restore indexes that attached to the table.
And, testingApply1 on ezcDatabaseSchemaGenericDiffTest should be failed because some index was not found.
So, I need improvement ( or bug fixes, depends on your point of view ) of this database schema components.
My quickhacked is, put repairingDroppedIndex function on dbschema writer, on this case Sqlitewriter. This function reads the index structure using pragma method. I bound this function on applyDiffToDb method on ezcDbSchemaSqliteWriter.
The idea is, to put DDL ( with repairing dropped index sql syntax on it ) before the loop.
And, for this purpose, I modify the index naming by default to include its table name with underscore ( tablename_indexname ) to avoid clashes with other same naming index ( instead of. just _pri for primary index ) .
The Complete of my quickhacked code is:
repairedDroppedIndex method:
{{{
protected function repaireDroppedIndex( Abstraction\PdoHandler $db, $schemas, $tableName )
{
$indexes = $this->generateIndexFromPragmaSql( $db, $tableName );
if( count( $indexes ) > 0 )
{
foreach( $indexes as $indexName => $indexDefinition )
}
return $schemas;
}
}}}
generateIndexFromPragmaSql method:
{{{
protected function generateIndexFromPragmaSql( Abstraction\PdoHandler $db, $tableName )
{
$query = 'PRAGMA INDEX_LIST (\''.$tableName.'\');';
$statement = $db->query( $query );
$statement->setFetchMode( \PDO::FETCH_ASSOC );
$resultArray = $statement->fetchAll();
$indexes = array();
$indexFields = array();
foreach( $resultArray as $key => $index )
{
$query = 'PRAGMA INDEX_INFO (\''.$index['name'].'\');';
$indexArray = $db->query( $query );
$indexArray->setFetchMode( \PDO::FETCH_ASSOC );
foreach( $indexArray as $dummy => $indexField )
{ $indexFields [$indexField['name']] = new Entity\DbSchemaIndexField(); } $primary = FALSE;
if( strstr( $index['name'], '_pri') ) $primary = TRUE;
$unique = FALSE;
if( $index['unique'] === '1' OR $index['unique'] === TRUE ) $unique = TRUE;
$indexes[$index['name']] = new Entity\DbSchemaIndex(
$indexFields, $primary, $unique
);
}
return $indexes;
}
}}}
and modified applyDiffToDb method:
{{{
...
$schemas = $this->convertDiffToDDL( $dbSchemaDiff );
foreach( $schemas as $query )
{
if( strstr( $query, 'DROP COLUMN' ) )
elseif( strstr( $query, 'CHANGE' ) )
{ preg_match( '@ALTER TABLE (.*) CHANGE (.*?) (.*?) (.*)@', $query, $matches ); if( ! $matches ) throw new Entity\DbSchemaSqliteDropFieldException( 'Can\'t fetch field for droping from SQL query: '.$query ); $tableName = trim( $matches[1], "'" ); $schemas = $this->repaireDroppedIndex( $db, $schemas, $tableName ); }}
/* and so on */
...
}}}
UPDATED:
I am sorry, that idea does not work. That idea failed because it appends the queries before loop, and so, override any updated indexes changes.
then, I've put the patched code at function changeField() and dropField() on ezcDbSchemaSqliteWriter. The goal of the patch is, to restore the necessary index
during DROP COLUMN and CHANGE of ZetaComponents's emulation since sqlite does not have native syntax to modify fields.
Here is my patched:
changeField() method
{{{
protected function changeField( Abstraction\PdoHandler $db, $tableName, $changeFieldName,
$changeFieldNewName, $changeFieldNewDefinition )
{
$tmpTableName = $tableName.'_cfbackup';
$indexes = $this->generateIndexFromPragmaSql( $db, $tableName, $changeFieldName );
$query = 'PRAGMA TABLE_INFO ( \''.$tableName.'\' )';
$resultArray = $db->query( $query );
$resultArray->setFetchMode( \PDO::FETCH_NUM );
$fieldDefinitions = array();
$fieldList = array();
foreach( $resultArray as $row )
{
$fieldSql = array();
$fieldSql [] = '\''.$row[1].'\'';
if( $row[1] === $changeFieldName )
{ $fieldDefinitions [] = '\''.$changeFieldNewName.'\' '.$changeFieldNewDefinition; $fieldList [] = $fieldSql[0]; continue; } $fieldSql [] = $row[2];
if( $row[3] === '99' ) $fieldSql [] = 'NOT NULL';
$fieldDefault = NULL;
if( $row[4] !== '' ) $fieldSql [] = 'DEFAULT \''.$row[4].'\'';
if( $row[5] === '1' ) $fieldSql [] = 'PRIMARY KEY AUTOINCREMENT';
$fieldUnsigned = FALSE;
$fieldDefinitions [] = implode( ' ', $fieldSql );
$fieldList [] = $fieldSql[0];
}
if( count( $fieldDefinitions ) > 0 )
{
$fields = implode( ', ', $fieldDefinitions );
$tmpTableCreateSql = 'CREATE TEMPORARY TABLE \''.$tmpTableName.'\' ( '.$fields.' );';
$newTableCreateSql = 'CREATE TABLE \''.$tableName.'\' ( '.$fields.' );';
if( count( $fieldList ) > 0 )
{ $db->exec( $tmpTableCreateSql ); $db->exec( 'INSERT INTO \''.$tmpTableName.'\' SELECT '.implode( ', ', $fieldList ).' FROM \''.$tableName.'\';' ); $db->exec( 'DROP TABLE \''.$tableName.'\';' ); $db->exec( $newTableCreateSql ); $db->exec( 'INSERT INTO \''.$tableName.'\' SELECT '.implode( ', ', $fieldList ).' FROM \''.$tmpTableName.'\';' ); $db->exec( 'DROP TABLE \''.$tmpTableName.'\';' ); }else
{ $db->exec( 'DROP TABLE \''.$tableName.'\';' ); $newTableCreateSql = 'CREATE TABLE \''.$tableName.'\' ( '.$changeFieldNewName.' '.$changeFieldNewDefinition.' );'; $db->exec( $newTableCreateSql ); }}
if( count( $indexes ) > 0 )
{
foreach( $indexes as $indexName => $indexDefinition )
}
}
}}}
and, DropField() method
{{{
protected function dropField( Abstraction\PdoHandler $db, $tableName, $dropFieldName )
{
$tmpTableName = $tableName.'_cfbackup';
$indexes = $this->generateIndexFromPragmaSql( $db, $tableName, $dropFieldName );
$query = 'PRAGMA TABLE_INFO ( '.$tableName.' )';
$resultArray = $db->query( $query );
$resultArray->setFetchMode( \PDO::FETCH_NUM );
$fieldDefinitions = array();
$fieldList = array();
foreach( $resultArray as $row )
{ $fieldSql = array(); $fieldSql [] = '\''.$row[1].'\''; if( $row[1] === $dropFieldName ) continue; $fieldSql [] = $row[2]; if( $row[3] === '99' ) $fieldSql [] = 'NOT NULL'; $fieldDefault = NULL; if( $row[4] !== '' ) $fieldSql [] = 'DEFAULT \''.$row[4].'\''; if( $row[5] === '1' ) $fieldSql [] = 'PRIMARY KEY AUTOINCREMENT'; $fieldUnsigned = FALSE; $fieldDefinitions [] = implode( ' ', $fieldSql ); $fieldList [] = $fieldSql[0]; }
$fields = implode( ', ', $fieldDefinitions );
$tmpTableCreateSql = 'CREATE TEMPORARY TABLE \''.$tmpTableName.'\' ( '.$fields.' );';
$newTableCreateSql = 'CREATE TABLE \''.$tableName.'\' ( '.$fields.' );';
if( count( $fieldList ) > 0 )
{ $db->exec( $tmpTableCreateSql ); $db->exec( 'INSERT INTO \''.$tmpTableName.'\' SELECT '.implode( ', ', $fieldList ).' FROM \''.$tableName.'\';' ); $db->exec( 'DROP TABLE \''.$tableName.'\';' ); $db->exec( $newTableCreateSql ); $db->exec( 'INSERT INTO \''.$tableName.'\' SELECT '.implode( ', ', $fieldList ).' FROM \''.$tmpTableName.'\';' ); $db->exec( 'DROP TABLE \''.$tmpTableName.'\';' ); }
else
{ throw new Entity\DbSchemaDropAllColumnsException( 'Trying to delete all column in table: '. $tableName ); }
if( count( $indexes ) > 0 )
{
foreach( $indexes as $indexName => $indexDefinition )
{ $needle = array( 'CREATE INDEX ''.$indexName.''', 'CREATE UNIQUE INDEX ''.$indexName.''' ); $replace = array( 'CREATE INDEX IF NOT EXISTS ''.$indexName.''', 'CREATE UNIQUE INDEX IF NOT EXISTS ''.$indexName.''' ); $query = str_replace( $needle, $replace, $this->generateAddIndexSql( $tableName, $indexName, $indexDefinition )); $db->exec( $query ); }
}
}
}}}
