SQLITE createTable

I had a similar problem with Postgresql. Mysql is ok. With such things like createTable working someone could write code independent from db engine if he wishes. Phalcon (with PHQL) supports a couple of data types. It is simingly little but it is both enough and first of all multi-database compatible as i know. I think it is a priority that could be present in Phalcon 2.0.0. Another thing is supporting of more db engines out of box this way like MS SQL.

By the way: Does anybody know what is the roadmap for db related stuff alongside with all Phalcon roadmaps?

Phalcon is my first back-end framework :) I chose it because it seems to have the brightest future and is extremely fast as for php frameworks. Nice job.

As a temporary resolve to the problem - i've done a adapter class which implements as much functionality for createTable as i needed (see a complementary dropTable implementation https://forum.phalcon.io/discussion/7835/sqlite-dropcolumn-implementation-+-remembering-createtable). This idea might be extended and with little change ported to Postgres too. After this class i made an a try catch exception example giving come idea what to do: if a createTable throws an exception - use another adapter. If Phalcon in the meantime has been updated and a create table exists not exception will be thrown. So someone can write code with future in mind. I give it not a complete resolve but as a working idea to be extended.

namespace PhalconAppLibrary\Phalcon\Db\Adapter\Pdo;

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

    public function createTable($tableName, $schemaName, $definition) {

        $createTableQuery="CREATE TABLE $tableName (";

        for ($i=0;$i<count($definition["columns"]);$i++) {
                $columnObj=$definition["columns"][$i];    
                $name=$columnObj->getName();

                $numberCapableColumns=array(\Phalcon\Db\Column::TYPE_INTEGER, \Phalcon\Db\Column::TYPE_VARCHAR);

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

                $primary=($columnObj->isPrimary())?"PRIMARY KEY":"";

                $notNull=($columnObj->isNotNull())?"NOT NULL":"";
                $autoIncrement=($columnObj->isAutoIncrement ())?"AUTOINCREMENT":"";

                $colon=($i<(count($definition["columns"])-1))?",":"";

                //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()&&$type=="INT"&&$columnObj->isAutoIncrement()) {
                    $type="INTEGER";
                }

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

                $createTableQuery.="
                        $name $type $primary $autoIncrement $notNull $colon                   
                ";
        }

        $createTableQuery.="\n);";

        return $this->execute($createTableQuery);

    }

}

And try catch example

                                    // in Phalcon 1.3.3 there was no createTable function implemented
                                    // for Sqlite and Postgres - this is to catch the exception of it
                                    try {

                                             for ($i=0;$i<count($app_tables);$i++) { 
                                                  if ($connection->tableExists($app_tables[$i][0])) {
                                                      $connection->dropTable($app_tables[$i][0]);
                                                  }    
                                                  $connection->createTable($app_tables[$i][0],$app_tables[$i][1],$app_tables[$i][2]);
                                             }

                                    } catch (Exception $e) {

                                                $this->addAnOverallMessage($this->t->_('not-implemented'), "error");

                                                switch($this->request->getPost('select-db-engine')) {
                                                      case "postgresql":   
                                                          $connection = new PhalconAppLibrary\Phalcon\Db\Adapter\Pdo\Postgresql($config); 
                                                          break;        
                                                      case "sqlite": 
                                                          $connection = new PhalconAppLibrary\Phalcon\Db\Adapter\Pdo\Sqlite($config); 
                                                          break;        
                                                }

                                                if (isset($connection)) $this->di->set('db', $connection); else {
                                                        $connection_error=true;
                                                }                                   

                                                 try {

                                                     for ($i=0;$i<count($app_tables);$i++) { 
                                                          if ($connection->tableExists($app_tables[$i][0])) {
                                                                $connection->dropTable($app_tables[$i][0]);
                                                          }    
                                                          $connection->createTable($app_tables[$i][0],$app_tables[$i][1],$app_tables[$i][2]);
                                                     }
                                                 } catch (Exception $e) {

                                                        echo $e->getMessage();
                                                        ob_flush();
                                                        die();
                                                 }

                                    }                       

As you see it is a double try catch example :)