Repairing dropped index during compare schemas on sqlitewriter
--------------------------------------------------------------
Key: ZETACOMP-107
URL: https://issues.apache.org/jira/browse/ZETACOMP-107
Project: Zeta Components
Issue Type: Improvement
Components: DatabaseSchema
Environment: Ubuntu Natty
PHP 5.3.6
SimpleTest
Reporter: Suwandi Tanuwijaya
Priority: Critical
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 ).
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 )
{
$query = $this->generateDropIndexSql( $tableName, $indexName );
$schemas [] = $query;
$needle = array( 'CREATE INDEX ', 'CREATE UNIQUE INDEX ' );
$replace = array( 'CREATE INDEX IF NOT EXISTS ', 'CREATE UNIQUE
INDEX IF NOT EXISTS ' );
$query = str_replace( $needle, $replace,
$this->generateAddIndexSql( $tableName, $indexName, $indexDefinition ));
$schemas [] = $query;
}
}
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' ) )
{
preg_match( '@ALTER TABLE (.*) DROP COLUMN (.*)@', $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 );
}
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 */
...
}}}
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
