We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

SQlite dropColumn implementation (+ remembering createTable)

Normally Sqlite doesn't implement dropColumn; how to do it the below piece of code shows. This is an initial approach which actually worked form me initially. How to implement createTable in SQLite, my other unperfect approach can be found here https://forum.phalcon.io/discussion/2274/sqlite-createtable (EDITED LATER: i've just noticed you must use createTable implementation too or change the proper phrase with createTable below into normal sql query) Hope all of it is useful but test it, please, well before it gets into production environment. Worked on 2.0.6 version of Phalcon.

<?php 

namespace PhalconAppLibrary\Phalcon\Db\Adapter\Pdo;

class Sqlite extends \Phalcon\Db\Adapter\Pdo\Sqlite {
    public function dropColumn($tableName, $schemaName, $columnName) {

        /*
            from sqlite page https://www.sqlite.org/faq.html#q11 - and this is implemented below

            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;
        */

        $this->begin();

        $columnsTemp=$this->describeColumns($tableName, null);

        $currentStateColumnList=array();
        for($k=0;$k<count($columnsTemp);$k++) {
                $currentStateColumnList[$k]=$columnsTemp[$k]->getName();
        }

        for($k=0;$k<count($columnsTemp);$k++) {
            if ($columnsTemp[$k]->getName()==$columnName) {
                array_splice($columnsTemp, $k, 1);
                break;
            }
        }

        $columnsTemp=array("columns" => $columnsTemp);

        $aquery="
            CREATE TEMPORARY TABLE ".$tableName."_backup(".implode(",", $currentStateColumnList).");
            INSERT INTO ".$tableName."_backup SELECT ".implode(",", $currentStateColumnList)." FROM ".$tableName.";
            DROP TABLE ".$tableName.";";
        $this->execute($aquery);

        $this->createTable($tableName, null, $columnsTemp);

        $newColumnNames=array();
        for ($f=0;$f<count($columnsTemp['columns']);$f++) {
                       $newColumnNames[$f]=$columnsTemp['columns'][$f]->getName();
       }

        $this->execute("
            INSERT INTO ".$tableName." SELECT ".implode(",", $newColumnNames)." FROM ".$tableName."_backup;
        ");
        $this->execute("
            DROP TABLE ".$tableName."_backup;            
        ");
        $this->commit();

    }

}


9.8k

Another related topic (decided to create a new one with "zephir" word in the title) https://forum.phalcon.io/discussion/7863/zephir-sqlite-createtable-droptable-implementation-for-phalcon