Can not get data from the newly created columns in SQLite

The current value for the columns countProductSelect and countProductInput - INTEGER NOT NULL;

Need to make INTEGER DEFAULT NULL;

PHP code below.

The problem is that when trying to update data and countProductSelect countProductInput, from the time column and countProductSelectTmp countProductInputTmp, getting the error - "no such column: countProductSelectTmp".

How to deal with this error? Or perhaps there is more educated algorithms for solving the original problem?

/**
 * Updating the database
 * 
 * @return bool
 */
protected function _updateDB()
{
    $version = '3.8.4.0';

    $this->_pdo->exec('
        ALTER TABLE data ADD optionalCountProduct INTEGER DEFAULT NULL;
        ALTER TABLE data ADD countProductSelectTmp INTEGER DEFAULT NULL;
        ALTER TABLE data ADD countProductInputTmp INTEGER DEFAULT NULL;
        UPDATE data SET countProductSelectTmp = countProductSelect, countProductInputTmp = countProductInput;
    ');

    $this->_deleteSQLiteColumn(array('countProductSelect', 'countProductInput'));

    // BUG
    // countProductSelect and countProductInput can not get the value countProductSelectTmp and countProductInputTmp
    $this->_pdo->exec('
        ALTER TABLE data ADD countProductSelect INTEGER DEFAULT NULL;
        ALTER TABLE data ADD countProductInput INTEGER DEFAULT NULL;
        UPDATE data SET countProductSelect = countProductSelectTmp, countProductInput = countProductInputTmp;
        UPDATE version SET id = "' . $version . '";
    ');

    $this->_deleteSQLiteColumn(array('countProductSelectTmp', 'countProductInputTmp'));

    return true;
}


/**
 * Remove column from a SQLite Table
 * 
 * @param array $column name of the column to remove
 * @return bool
 */
protected function _deleteSQLiteColumn(array $column)
{
    return (bool)$this->_pdo->exec('
        BEGIN TRANSACTION;
        CREATE TEMPORARY TABLE backup(' . $this->_getFullColumnsString() . ');
        INSERT INTO backup SELECT ' . $this->_getShortColumnsString() . ' FROM data;
        DROP TABLE data;
        CREATE TABLE data(' . $this->_getFullColumnsString($column) . ');
        INSERT INTO data SELECT ' . $this->_getShortColumnsString($column) . ' FROM backup;
        DROP TABLE backup;
   开发者_StackOverflow中文版     COMMIT;
    ');
}

From the fine manual:

PDO::exec — Execute an SQL statement and return the number of affected rows

But you're trying to execute multiple statements at once so perhaps it is only executing the last statement. Trying using exec as intended and executing your statements one at a time:

$this->_pdo->exec('ALTER TABLE data ADD optionalCountProduct INTEGER DEFAULT NULL');
$this->_pdo->exec('ALTER TABLE data ADD countProductSelectTmp INTEGER DEFAULT NULL');
$this->_pdo->exec('ALTER TABLE data ADD countProductInputTmp INTEGER DEFAULT NULL');
$this->_pdo->exec('UPDATE data SET countProductSelectTmp = countProductSelect, countProductInputTmp = countProductInput');

And then again with your next multi-statement exec and your _deleteSQLiteColumn.

As far as a better way goes, it looks like all you want to do is drop the NOT NULL constraint but SQLite's ALTER TABLE is a bit limited so your "add column, copy column, drop column via table copying" approach is the best you can do; from the SQLite FAQ:

For example, suppose you have a table named "t1" with columns names "a", "b", and "c" and that you want to delete column "c" from this table. The following steps illustrate how this could be done:

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;