dbal
dbal copied to clipboard
doctrine
listTables crashes when retrieving information on SQLite database with tables starting with sqlite_
Bug Report
| Q | A |
|---|---|
| BC Break | yes/no |
| Version | doctrine/dbal 2.8 |
Summary
Error : Unknown database type requested, Doctrine\DBAL\Platforms\SqlitePlatform may not support it. at line 479 of AbstractPlatform.php
Current behaviour
I have a SQLite database where I ran the command ANALYZE. This command generates an internal table sqlite_stat1 (see also point 2.6.3 on https://www.sqlite.org/fileformat.html) When this table exists, the method listTables() crashes. Stacktrace : $sm->listTables() - listTableDetails($tableName) in AbstractSchemaManager.php - listTableColumns($table, $database = null) in AbstractSchemaManager.php - $this->_getPortableTableColumnList($table, $database, $tableColumns) in AbstractSchemaManager.php - $list = parent::_getPortableTableColumnList($table, $database, $tableColumns) in SqliteSchemaManager.php -> this is where the error is thrown
How to reproduce
- take a SQLite database and run the command 'analyze'
- retrieve the schema information from the database or run the command php bin/console test:test on the following testapplication : test.zip
Expected behaviour
Possible outcomes:
- the information of the internal table(s) is retrieved or
- the internal table(s) is ignored (since it is 'for internal usage' only)
Can you attempt adding the DDL and expected values to the data provider in https://github.com/doctrine/dbal/blob/v2.8.0/tests/Doctrine/Tests/DBAL/Schema/SqliteSchemaManagerTest.php, and see if the crash can be reproduced?
Sorry, I can't fullfill that request. A table starting with sqlite_ cannot be created by code. See the remark in the documentation on page https://www.sqlite.org/lang_createtable.html :
Every CREATE TABLE statement must specify a name for the new table. Table names that begin with "sqlite_" are reserved for internal use. It is an error to attempt to create a table with a name that starts with "sqlite_".
The table is created by SQlite when the analyze command is run. Therefore the proposal to let the schemamanager ignore internal SQLite-tables (SQLite-tables starting with sqlite_)
Please check the test that I linked: it doesn't create tables.
On Wed, 12 Sep 2018, 20:57 pc-explore, [email protected] wrote:
Sorry, I can't fullfill that request. A table starting with sqlite_ cannot be created by code. See the remark in the documentation on page https://www.sqlite.org/lang_createtable.html :
Every CREATE TABLE statement must specify a name for the new table. Table names that begin with "sqlite_" are reserved for internal use. It is an error to attempt to create a table with a name that starts with "sqlite_".
The table is created by SQlite when the analyze command is run. Therefore the proposal to ignore internal SQLite-tables (SQLite-tables starting with sqlite_)
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/doctrine/dbal/issues/3281#issuecomment-420760212, or mute the thread https://github.com/notifications/unsubscribe-auth/AAJakHCovm0d0ApR4B4APVW53g0pYJh5ks5uaVkmgaJpZM4Wh4_4 .
The table is hidden in sqlite-editors like SQLite expert professional and only shows up when executing the query
SELECT name FROM my_db.sqlite_master WHERE type='table'
The DDL seems to be
CREATE TABLE sqlite_stat1(tbl,idx,stat);
A workaround is to delete the table sqlite_stat1.
In general, this issue could be resolved across platforms by introducing a Generic type of column. It would only contain the ~DDL~ name and wouldn't allow any other interactions or modifications. This way, a schema manager could manipulate other fields in the table w/o touching the unknown ones.
Re-opening, since the issue still exists, and the test for it is simply missing (can be created from the DDL in https://github.com/doctrine/dbal/issues/3281#issuecomment-421451087)
The SQLite-table sqlite_stat1 is generated when the SQLite command 'analyze' is run. AFAIK that is the only way to create this table. The 'create table' command will not create it.