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

Zephir Sqlite createTable, dropTable implementation for Phalcon

Introducion. I've recently written on the phalcon forums a topic remembering createTable for SQLite and pasting there my initial code for dropColumn implementation for this db engine. I also mentioned there an important thing, that normally you cannot drop column in SQLite you must use a workaround (read there please; thus dropColumn for SQLite must always be flawed, especially with big data in a particular table) All relevant information and (a )?link[s]{0,1} :) can be found here: https://forum.phalcon.io/discussion/7835/sqlite-dropcolumn-implementation-+-remembering-createtable

The point: I would like to introduce a Zephir example of an initial implementation of both createTable and dropColumn methods/functionality unavailable either in Phalcon or SQLite engine. This worked for me initially so you can test it, suggest any improvements (limited column types are now supported and schemas altogether not).

And the question is could it be implemented in one of the next releases of Phalcon framework?

namespace Zmanager\Phalcon\Db\Adapter\Pdo;

class Sqliteextended extends \Phalcon\Db\Adapter\Pdo\Sqlite {

     public function createTable(string tableName, string schemaName, array definition) {

        string createTableQuery,
                  name,
                  type,
                  primary,
                  notNull,
                  autoIncrement,
                  colon;
        array numberCapableColumns;
        var columnObj;          
        int countdefinitioncolumns, i;

        let countdefinitioncolumns = count(definition["columns"]) - 1;
        let createTableQuery="CREATE TABLE ".tableName." (";

        for i in range(0, countdefinitioncolumns) {

                let columnObj=definition["columns"][i];    
                let name=(string) columnObj->getName();
                let numberCapableColumns=[\Phalcon\Db\Column::TYPE_INTEGER, \Phalcon\Db\Column::TYPE_VARCHAR];

                switch (columnObj->getType()) {
                    case \Phalcon\Db\Column::TYPE_INTEGER: let type="INT"; break;
                    case \Phalcon\Db\Column::TYPE_VARCHAR: let type="VARCHAR"; break; 
                    case \Phalcon\Db\Column::TYPE_TEXT: let type="TEXT"; break;
                }

                if columnObj->isPrimary() == true {let primary = "PRIMARY KEY";} else {let primary = "";}

                if columnObj->isNotNull() == true {let notNull = "NOT NULL";} else {let notNull = "";}

                if columnObj->isAutoIncrement() == true {let autoIncrement = "AUTOINCREMENT";} else {let autoIncrement = "";}

                if i<countdefinitioncolumns {let colon = ",";} else {let colon = "";}

                //https://www.sqlite.org/faq.html#q1
                //Short answer: A column declared INTEGER PRIMARY KEY will autoincrement.
                //INTEGER(10) is wrong INTEGER IS WITHOUT NUMBER AND ONLY THIS CAN BE AUTOINCREMENTED
                //AUTOINCREMENT CAN BE ADDED
                //INT(10) is ok
                if columnObj->isPrimary()==true {
                    if type=="INT" {
                        if columnObj->isAutoIncrement()==true {
                              let type="INTEGER";
                        }
                    }    
                }

                if in_array(columnObj->getType(), numberCapableColumns)==true {
                        if columnObj->getSize()>0 {
                            if type!="INTEGER" {
                                    let type=type."(".columnObj->getSize().")";
                            }            
                        }            
                } 

                let createTableQuery=createTableQuery."\n".name." ".type." ".primary." ".autoIncrement." ".notNull." ".colon."\n";
        }

        let createTableQuery=createTableQuery."\n);";

        return this->execute(createTableQuery);

    }

    public function dropColumn(string tableName, string schemaName, string 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();
        string aquery;
        var columnsTemp, columnsTemp2;
        array currentStateColumnList,
                 newColumnNames;
        int countdefinitioncolumns, k, f;

        let currentStateColumnList=[];
        let columnsTemp= this->describeColumns(tableName, false);
        let countdefinitioncolumns=count(columnsTemp) - 1;

        for k in range(0, countdefinitioncolumns) {
                let currentStateColumnList[k]=columnsTemp[k]->getName();
        }

        for k in range(0, countdefinitioncolumns) {
            if (columnsTemp[k]->getName()==columnName) {
                array_splice(columnsTemp, k, 1);
                break;
            }
        }

        let columnsTemp2=["columns": columnsTemp];
        let countdefinitioncolumns =count(columnsTemp2["columns"]) - 1;

        let aquery="\nCREATE TEMPORARY TABLE ".tableName."_backup(".implode(",", currentStateColumnList).");\nINSERT INTO ".tableName."_backup SELECT ".implode(",", currentStateColumnList)." FROM ".tableName.";\nDROP TABLE ".tableName.";";
        this->execute(aquery);

        this->createTable(tableName, "", columnsTemp2);

        let newColumnNames=[];
        for f in range(0, countdefinitioncolumns) {
               let newColumnNames[f]=columnsTemp2["columns"][f]->getName();
        }

        this->execute("\nINSERT INTO ".tableName." SELECT ".implode(",", newColumnNames)." FROM ".tableName."_backup;\n");
        this->execute("\nDROP TABLE ".tableName."_backup;\n");
        this->commit();

    }

}

By the way Zephir is not that difficult if as a Zephir newbie i was able to do this PHP translation into Zephir. It took me about 4 hours with little knowledge. (edited updated code)



9.8k
edited Aug '15

I did some performance test with the below code - i don't see any relevant difference between Zephir and PHP implementations partly because of i suppose sqlite functions doing their quite long job on a database. 10 php 5, zephir 7 100 php 43, zephir 41 1000 php 420, zephir 431

code used for my primitive testing.

        // zephir or not zephir: $connection=$connectionZ;
        if ($connection->tableExists("table_name_testa")) $connection->dropTable("table_name_testa");

        $timeone=time();
        for ($a=0;$a<1000;$a++) {
                     $connection->createTable("table_name_testa",$app_tables[0][1],$app_tables[0][2]);
                     $connection->dropColumn("table_name_testa", null, "readable_name");
                     $connection->dropTable("table_name_testa");
        }
        $timetwo=time();
        echo "<h2>AA".($timetwo-$timeone)."BB</h2>";ob_flush();

By the way "zephir -fstatic-type-inference -flocal-context-pass" didn't work from me Exception: Unrecognized action "-flocal-context-pass" so it is without optimisations compiled