sqlite3: CREATE INDEX with schema.table syntax fails

[santilin]

What steps will reproduce the problem?

Create two sqlite3 databases named otherdb and mydb and create a connection for yii2 to use the sqlite3 mydb database.

Then, create a migration like this:

	public function safeUp()
	{
                 $this->db->createCommand("attach 'otherdb.db' as otherdb");
		$this->createTable('{{otherdb.users}}', [
			'id' => $this->primaryKey(),
			'email' => $this->string(100)->null(),
                 ]);
		$this->createIndex('my_index', 
                      '{{otherdb.users}}',
                      'email' );
        }

What is the expected result?

An index is created in the otherdb database

What do you get instead?

The following error:

Failed to prepare SQL: CREATE INDEX `my_index` ON `otherdb`.`users` (`email`) (.../vendor/yiisoft/yii2/db/Command.php:264)

Cause

The correct syntax for the CREATE INDEX in this case is:

CREATE INDEX otherdb.`my_index` ON `users` (`email`)

Solution:

Create a new function in yii\db\sqlite\QueryBuilder:

    public function createIndex($name, $table, $columns, $unique = false)
    {
		$sql = ($unique ? 'CREATE UNIQUE INDEX ' : 'CREATE INDEX ')
            . $this->db->quoteTableName($name) . ' ON '
            . $this->db->quoteTableName($table)
            . ' (' . $this->buildColumns($columns) . ')';
        $sql = preg_replace_callback(
            '/(`.*`) ON (\{\{(%?)([\w\-]+)\}\}\.\{\{((%?)[\w\-]+)\\}\\})|(`.*`) ON (\{\{(%?)([\w\-]+)\.([\w\-]+)\\}\\})/',
            function ($matches) {
                if (!empty($matches[1])) {
					return $matches[4].".".$matches[1]
					 . " ON {{" .$matches[3].$matches[5] . "}}";
                } else if( !empty($matches[7]) ) {
					return $matches[10].".".$matches[7]
					 . " ON {{" .$matches[9].$matches[11] . "}}";
                }
            },
            $sql
        );
		return $sql;
    }

This problem and a lot more with sqlite3 is solved using the extension yii2-sqlite3-full-support.

Additional info

Q	A
Yii version	2.0.30-dev
PHP version	7.3
Operating system	Linux Debian 10

[samdark]

@santilin looks interesting. As an author of that package, do you want to prepare pull requests / tests?

[santilin]

If you tell me how to prepare tests I'd do it happily. But I need a throroughly detailed tutorial, as I am new to testing Yii2

[samdark]

https://github.com/yiisoft/yii2/blob/master/docs/internals/git-workflow.md#unit-tests

The tests themselves are created with phpunit: https://phpunit.readthedocs.io/en/8.4/

[terabytesoftw]

This solution works correctly, tested in Sqlite 3.30.1, we should implement it, you just have to do the PR and the framework has tests for the indexes, foreingkey.