Bug Report: Issue with `getTableNames` in Yii2 Schema Handling on sqlite3 database

[santilin]

Summary:
There is a bug in the findTableNames method of the Schema class in Yii2 that affects the retrieval of table names when a schema is specified. The current implementation does not correctly handle the schema prefix, leading to incorrect SQL queries for SQLite databases.

Environment:

• Yii2 Framework Version: develop
• Database: SQLite

Description:
The method findTableNames is designed to return distinct table names from the SQLite database. However, when a schema is provided, the SQL query constructed does not correctly reference the sqlite_master table. The modification proposed in the code snippet aims to prepend the schema to the sqlite_master reference.

Code Snippet:

protected function findTableNames($schema = '')
{
    if ($schema != '') {
        $schema .= '.';
    }
    $sql = "SELECT DISTINCT tbl_name FROM {$schema}sqlite_master WHERE tbl_name<>'sqlite_sequence' ORDER BY tbl_name";
    return $this->db->createCommand($sql)->queryColumn();
}

Expected Behavior:
When calling findTableNames('my_schema'), the expected SQL should be:

SELECT DISTINCT tbl_name FROM my_schema.sqlite_master WHERE tbl_name<>'sqlite_sequence' ORDER BY tbl_name;

This query should return all table names under the specified schema.

Actual Behavior:
The current implementation returns the table names of the main database.

Steps to Reproduce:

1. Set up a Yii2 application connected to an SQLite database.
2. Attach a second database named 'second_schema'
3. Call the findTableNames method with the schema name second_schema as a parameter.
4. Observe that the returned schemas are those of the main schema

References:

• Yii2 API Documentation on Schema [1]
• Community discussions on related issues [4][5][6]

Citations:
[1] https://www.yiiframework.com/doc/api/2.0/yii-db-schema
[2] https://www.yiiframework.com/doc/api/2.0/yii-db-mysql-schema
[3] https://www.yiiframework.com/doc/api/2.0/yii-db-sqlite-schema
[4] https://forum.yiiframework.com/t/cdbschema-findtablenames-not-part-of-schemacachingduration/73388
[5] #8096
[6] yiisoft/yii#2299
[7] https://forum.yiiframework.com/t/table-name-issue-in-oracle-db-and-yii2/79566
[8] https://www.yiiframework.com/doc/api/2.0/yii-db-oci-schema

[samdark]

@Tigrov would you please take a look? Is it the same in yiisoft/db?

[Tigrov]

Current Yii SQLite driver does not support scheme names.

From the documentation

If a schema-name is specified, it must be either "main", "temp", or the name of an attached database.

It makes sense to add support schema names. In Yii3 the same.

[santilin]

In my yii2-sqlite3-full-support proyect I have added support for schemes replacing yii\db\sqlite\Scheme.php with my own version. I have been using it for a while and it works well, thogh it may not be complete.

It would be great to include this onto yii2.

[Tigrov]

@santilin Could you make a PR for yii2 to support scheme names?

[santilin]

@santilin Could you make a PR for yii2 to support scheme names?

Yes, but firstly, please, point me to a guide on correct formatting of the source code (tools) so that we don't get errors on th PR and another one for making a PR to yii2

[Tigrov]

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

Usually a PR should contain:

• code that solves the problem
• validation tests
• updated documentation (if needed)
• a line in the CHANGELOG file