Sqlite connector's use of schema-names doesn't work properly

[cryslith]

The sqlite connector opens a new in-memory database connection, then executes a command like ATTACH /path/to/database AS quaint;. This results in the main schema being the in-memory one, and the quaint schema being the actual database. This is a problem for the following reasons:

• The default schema for queries is main, and this cannot be changed. Thus, unqualified queries like SELECT * FROM my_table are executed against the in-memory database.
• You can use an alternate schema by prepending the schema name to the table name, as in SELECT * FROM quaint.my_table. However, there are a couple problems with that.
  • quaint's visitor implementation (correctly, imo) surrounds table names with quotes, making it impossible to write the above query (you get SELECT * FROM "quaint.my_table" instead).
  • Doing this would require the code to know the schema name in advance, which defeats the purpose of dynamically specifying it in the database URL.

The net result of this situation is that all queries get executed against the in-memory database, rather than the database on disk. I think the simplest way to resolve this would be to drop the db_name functionality and avoid using schema-names other than main entirely, since sqlite doesn't have any way to select a default schema to use, unlike e.g. Postgres's \connect or MySQL's use statements.

I couldn't find any way around this issue to execute commands against the database on disk, but please let me know if I missed something obvious.

[cryslith]

Actually, looking at the issue again I realized that this usually doesn't arise in practice but was a consequence of something unusual I was doing. Table names will be resolved to the schema containing them if it's unambiguous, which it generally will be. And you can use a tuple like ("quaint", "my_table") to query against a specific schema. Sorry for the noise.

Though, it would still be nice if there were a way to have the main db just be the on-disk database.