incubator-zeta-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Suwandi Tanuwijaya (Updated) (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (ZETACOMP-107) Repairing dropped index during compare schemas on sqlitewriter
Date Sat, 10 Dec 2011 14:49:40 GMT

     [ https://issues.apache.org/jira/browse/ZETACOMP-107?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Suwandi Tanuwijaya updated ZETACOMP-107:
----------------------------------------

    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 )
            {
                $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  */
...
}}}




  was:
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  */
...
}}}




    
> 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
>              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

        

Mime
View raw message