search

r-dbi / RSQLite

R interface for SQLite
https://rsqlite.r-dbi.org
GNU Lesser General Public License v2.1
325 stars 78 forks source link

dbExistsTable(conn, Id(schema = "INFORMATION_SCHEMA", table = "TABLES")) throws error (should return FALSE?) #498

Closed kjellpk closed 3 months ago

kjellpk commented 4 months ago

dbExistsTable borks when conn is a SQLiteConnection and name is an Id with schema = "INFORMATION_SCHEMA" and table = "TABLES". I was expecting it to return FALSE.

library(DBI)
conn <- dbConnect(RSQLite::SQLite(), ":memory:")
dbExistsTable(conn, Id(schema = "INFORMATION_SCHEMA", table = "TABLES"))
# Error: no such table: INFORMATION_SCHEMA.sqlite_master
dbDisconnect(conn)
krlmlr commented 3 months ago

Thanks, good catch. I suspect we want to check if the schema exists first. Would you like to contribute?

kjellpk commented 3 months ago

Happy to help.

After a bit of Googling, my understanding is that schemas in SQLite (here I am using the word schema to mean the part before the . in schema.table) are the names of the ATTACHed databases. It seems like the only way there could ever be a schema other than "main" would be if ATTACH is used in a SQL statement.

The following code returns a character vector of the attached databases:

dbGetQuery(conn, "SELECT name FROM pragma_database_list;")$name

I'll see if I can find a good place to put a check.