[
https://issues.apache.org/jira/browse/ZETACOMP-107?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Suwandi Tanuwijaya updated ZETACOMP-107:
----------------------------------------
Priority: Major (was: Critical)
> 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
> Labels: improvement, patch
> Original Estimate: 2h
> Remaining Estimate: 2h
>
> 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 )
> {
> $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
