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 Sun, 11 Dec 2011 01:10:41 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  */
...
}}}

UPDATED:

I am sorry, that idea does not work. That idea failed because it appends the queries before
loop, and override any updated changes.
then, I put the patched code at function changeField() and dropField() on ezcDbSchemaSqliteWriter.

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 )
            {
                $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 );
            }
        }
    }
}}}

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 );
            }
        }
    }
}}}


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




    Remaining Estimate: 24h  (was: 2h)
     Original Estimate: 24h  (was: 2h)
    
> 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: 24h
>  Remaining Estimate: 24h
>
> 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  */
> ...
> }}}
> UPDATED:
> I am sorry, that idea does not work. That idea failed because it appends the queries
before loop, and override any updated changes.
> then, I put the patched code at function changeField() and dropField() on ezcDbSchemaSqliteWriter.
> 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 )
>             {
>                 $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 );
>             }
>         }
>     }
> }}}
> 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 );
>             }
>         }
>     }
> }}}

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