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

[kjellpk]

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]

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

[kjellpk]

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.