Yii2: Support for missing sqlite3 ALTER TABLE commands

[santilin]

It looks like there is no support for creating foreign keys on sqlite3.

What steps will reproduce the problem?

Configure your yii2 app to use a sqlite3 database and create this migration

    public function safeUp()
    {
        $this->createTable('parents', [
            'id' => $this->primaryKey(),
        ]);
        $this->createTable('children', [
            'id' => $this->primaryKey(),
            'parents_id' => $this->integer()->notNull(),
        ]);
               // creates index for column lemas_id
        $this->createIndex(
            'yii2idx-children-parents_id',
            'children',
            'parents_id'
        );
        $this->addForeignKey(
            'yii2fk-children-parents_id',
            'children',
            'parents_id',
            'parents',
            'id'
        ); 

What is the expected result?

The tables and foreign keys are created in the sqlite3 database

What do you get instead?

*** applying m171023_034457_capel_create_parents_table
    > create table parents ... done (time: 0.001s)
*** applied m171023_034457_capel_create_parents_table (time: 0.193s)

*** applying m171023_034458_capel_create_children_table
    > create table children ... done (time: 0.001s)
    > create index yii2idx-children-parents_id on children (parents_id) ... done (time: 0.000s)
    > add foreign key yii2fk-children-parents_id: children (parents_id) references parents (id) ...Exception: yii\db\sqlite\QueryBuilder::addForeignKey is not supported by SQLite. (/home/santilin/devel/yii2base/vendor/yiisoft/yii2/db/sqlite/QueryBuilder.php:238)

Additional info

| Q | A
| ---------------- | ---
| Yii version | 2.0.13-dev
| PHP version | 7.0
| Operating system | Debian

[cebe]

The ALTER TABLE statement in sqlite is quite limited and does not support adding foreign keys after a table has been created:

https://www.sqlite.org/lang_altertable.html
The only schema altering commands directly supported by SQLite are the "rename table" and "add column" commands shown above.

However they describe a way to alter a table, but that approach is quite complex and not easily automated: https://www.sqlite.org/lang_altertable.html#otheralter

In your case you should add the foreign key definition directly to the CREATE TABLE statement:

public function safeUp()
{
    $this->createTable('parents', [
        'id' => $this->primaryKey(),
    ]);
    $this->createTable('children', [
    'id' => $this->primaryKey(),
    'parents_id' => $this->integer()->notNull(),
        'FOREIGN KEY(parents_id) REFERENCES parents(id)'
    ]);
}

[santilin]

I have been poking around the code and I have found that the sqlite QueryBuilder lacks the implementation of certain features:

 public function addForeignKey($name, $table, $columns, $refTable, $refColumns, $delete = null, $update = null)
    {
        throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
    }

A few years ago, I added support for drop column in a C++ program, so I want to port it to php. What are the steps to add funcionality to the QueryBuilder? Create my own branch here and then a pull request?

[samdark]

Yes.

[santilin]

@samdark @schmunk42
I am in the middle of adding drop column support for sqlite3 on db/sqlite/QueryBuilder::dropColumn and I have found a problem. I have to return a compound sql query (of about 8 individual queries), but the migration only applies the first one. In order for drop column to work, I have to change db\Migration::dropColumn like this:

    public function dropColumn($table, $column)
    {
        $time = $this->beginCommand("drop column $column from table $table");
//          $this->db->createCommand()->dropColumn($table, $column)->execute();
        $sql = $this->db->createCommand()->dropColumn($table, $column)->getSql();
        foreach( explode( ";\n", $sql ) as $command) {
            $this->db->createCommand($command)->execute();
        }
        $this->endCommand($time);

Is all this correct?

[samdark]

No. ->dropColumn($table, $column)->execute(); could be called separately and it's not expected for end user to split SQL by ; and execute each part.

[santilin]

What can I do then? I have managed to implement dropColumn in this way.

[samdark]

@yiisoft/core-developers any ideas?

[santilin]

I don't see any problem in this approach. i f the query has only one statement, as before, it is executed without noting it, but if it has more than one, it is splitted and executed.

[santilin]

Maybe I could return directly an array of statements from the sqlite querybuilder and avoid problems with the split and simply cast the return to array and always use foreach.

This will be needed for the rest of DDL operations as well.

El 25 de octubre de 2017 11:27:16 CEST, Alexander Makarov [e-mail] escribió:

@yiisoft/core-developers any ideas?

--
You are receiving this because you authored the thread.
Reply to this email directly or view it on GitHub:
https://github.com/yiisoft/yii2/issues/15007#issuecomment-339270396

[samdark]

The problem is that it's public method and it could be used anywhere. If we'll change its return type then likely something using it would fail.

[santilin]

Then maybe the execute method of queryBuilder should handle an array of statements.

[cebe]

@santilin can you show the code that implements dropColumn in sqlite querybuilder?

[santilin]

Sure!

    /**
     * Builds a SQL statement for dropping a DB column.
     * @param string $table the table whose column is to be dropped. The name will be properly quoted by the method.
     * @param string $column the name of the column to be dropped. The name will be properly quoted by the method.
     * @return string the SQL statement for dropping a DB column.
     * @throws NotSupportedException this is not supported by SQLite
     * @autohr santilin <[email protected]>
     */
    public function dropColumn($tableName, $column)
    {
        // Simulate ALTER TABLE ... DROP COLUMN ...
        // Get the CREATE TABLE statement used to create this table
        $create_table = $this->db->createCommand("select SQL from SQLite_Master where tbl_name = '$tableName' and type='table'")->queryScalar();
        if ($create_table == NULL ) {
            throw new InvalidParamException("Table not found: $tableName");
        }
        // Parse de CREATE TABLE statement to skip any use of this column, namely field definitions and FOREIGN KEYS
        $code = (new SqlTokenizer($create_table))->tokenize();
        $pattern = (new SqlTokenizer('any CREATE any TABLE any()'))->tokenize();
        if (!$code[0]->matches($pattern, 0, $firstMatchIndex, $lastMatchIndex)) {
            throw new InvalidParamException("Table not found: $tableName");
        }
        // Get the fields definition and foreign keys tokens
        $fieldsDef = $code[0][$lastMatchIndex - 1];

        $ddl_fields_def = '';
        $sql_fields_to_insert = [];
        $skipping = false;
        $column_found = false;
        $quoted_column = $this->db->quoteColumnName($column);
        $offset = 0;
        // Traverse the tokens looking for either an identifier (field name) or a foreign key
        while( $fieldsDef->offsetExists($offset)) {
            $token = $fieldsDef[$offset++];
            // These searchs could be done whit another SqlTokenizer, but I don't konw how to do them, the documentation for sqltokenizer si really scarse.
            if( $token->type == \yii\db\SqlToken::TYPE_IDENTIFIER ) {
                $identifier = (string)$token;
                if( $identifier == $column || $identifier == $quoted_column) {
                    // found column definition for $column, set skipping on up until the next ,
                    $column_found = $skipping = true;
                } else {
                    // another column definition, keep and add to list of fields to select back
                    $sql_fields_to_insert[] = $identifier;
                    $skipping = false;
                }
            } else if( $token->type == \yii\db\SqlToken::TYPE_KEYWORD) {
                $keyword = (string)$token;
                if( $keyword == "FOREIGN" ) {
                    // Foreign key found
                    $other_offset = $offset;
                    while( $fieldsDef->offsetExists($other_offset) && $fieldsDef[$other_offset]->type != \yii\db\SqlToken::TYPE_PARENTHESIS) {
                        ++$other_offset;
                    }
                    $foreign_field = (string)$fieldsDef[$other_offset];
                    if ($foreign_field == $column || $foreign_field == $quoted_column) {
                        // Found foreign key for $column, skip it
                        $skipping = true;
                        $offset = $other_offset;
                    }
                }
            } else {
                /// @todo is there anything else. Look it up in the sqlite docs
                die("Unexpected: $token");
            }
            if( !$skipping ) {
                $ddl_fields_def .= $token . " ";
            }
            // Skip or keep until the next ,
            while( $fieldsDef->offsetExists($offset) ) {
                $skip_token = $fieldsDef[$offset];
                if( !$skipping ) {
                    $ddl_fields_def .= (string)$skip_token . " ";
                }
                if ($skip_token->type == \yii\db\SqlToken::TYPE_OPERATOR && (string)$skip_token == ',') {
                    $ddl_fields_def .= "\n";
                    ++$offset;
                    $skipping = false;
                    break;
                }
                ++$offset;
            }
        }
        if (!$column_found) {
            throw new InvalidParamException("column '$column' not found in table '$tableName'");
        }
        $create_query = 
            "BEGIN;\n" /// @todo investigar inmediate
            . "PRAGMA foreign_keys = OFF;\n"
            . "PRAGMA triggers = NO;\n"
            . "CREATE TABLE {{temp_$tableName}} AS SELECT * FROM {{" . $tableName . "}};\n"
            . "DROP TABLE {{" . $tableName . "}};\n"
            // Remove trailing , from $ddl_fields_def if any
            . "CREATE TABLE {{" . $tableName . "}} (" . trim($ddl_fields_def, " \n\r\t,") . ");\n"
            . "INSERT INTO {{" . $tableName . "}} SELECT " . join(",", $sql_fields_to_insert) . " FROM {{temp_$tableName}};\n"
            . "DROP TABLE `temp_$tableName`;\n";

        // Indexes. Skip any index referencing $column
        // Get all indexes on this table
        $indexes = $this->db->createCommand("select SQL from SQLite_Master where tbl_name = '$tableName' and type='index'")->queryAll();
        foreach( $indexes as $key => $index ) {
            $code = (new SqlTokenizer($index["sql"]))->tokenize();
            $pattern = (new SqlTokenizer('any CREATE any INDEX any ON any()'))->tokenize();
            // Extract the list of fields of this index
            if (!$code[0]->matches($pattern, 0, $firstMatchIndex, $lastMatchIndex)) {
                throw new InvalidParamException("Table not found: $tableName");
            }
            $found = false;
            $indexFieldsDef = $code[0][$lastMatchIndex - 1];
            $offset = 0;
            while( $indexFieldsDef->offsetExists($offset) ) {
                $token = $indexFieldsDef[$offset];
                if( $token->type == \yii\db\SqlToken::TYPE_IDENTIFIER) {
                    if( (string)$token == $column || (string)$token == $quoted_column) {
                        $found = true;
                        break;
                    }
                }
                ++$offset;
            }
            if (!$found) {
                // If the index contains this column, do not add it to the create table statement
                $create_query .= $index["sql"] . ";\n";
            }
        }
        $create_query .=  "PRAGMA foreign_keys = YES;\n"
            . "COMMIT;";
        return $create_query;
    }

I have tested it and it works correctly.
The code to parse the foreign key could be improved with anothe SqlTokenizer, but I have not found a good documentation on using SqlTokenizer.

I'm planning to make all the other DDL operations, creating a protected function with all the common code, as all the operations work more or less the same way.

[santilin]

I have created a fork from yiisoft/yii2 named santilin/yii2 and pushed the code.

So far I have modified db/Migration.php and db/sqlite/QueryBuilder.php to add drop column and add foreign key support.

There are a few things left:

• Check if the table has triggers to either cancel the operation if the triggers are very complicated or recreate them
• Update views associated with the table
• get create table additional info like WITHOUT ROWID, TEMPORARY, etc.

[samdark]

https://github.com/santilin/yii2

[cebe]

Sorry to say that, but considering the complexity of the method this does not look like something we could reliably maintain within Yii core. It simulates a feature of sqlite, which is not natively supported so it could quite well exist as an extension providing additional functionality in case someone needs this. Its not a common problem so imo it is too special to be in Yii core.

[santilin]

How can then I add this to my project? Should I create a new sqlite driver, like sqlite_advanced or is there any other way of adding it? @cebe @dynasource

[dynasource]

there are many ways. Please take a look at http://www.yiiframework.com/doc-2.0/guide-concept-di-container.html in which you can use Yii::$container to define your own class. You can also use Yii::$classMap to replace the Yii class with your own version.

[cebe]

If you only need this in db migrations, you can create a Migration base class and redefine the dropColumn method.