tables(db) for empty database should be `(names=[],)`

[kescobo]

The function SQLite.tables(db) returns a NamedTuple like (name=["table1", "table2"],), but if there are no tables, it's just an empty NamedTuple(). This makes it a bit tricky to do something like if "blah" in SQLite.tables(mydb).name....

MWE:

julia> using SQLite

julia> db = SQLite.DB()
SQLite.DB(":memory:")

julia> SQLite.tables(db)
NamedTuple()

julia> for _ in 1:5; (a=rand(10), b=collect(1:10)) |> SQLite.load!(db); end

julia> SQLite.tables(db)
(name = ["sqlitejl_z3Fcq", "sqlitejl_8BDzG", "sqlitejl_L23C9", "sqlitejl_pvmFu", "sqlitejl_SFEBB"],)

Is there any time that tables() returns something with other columns?

[quinnj]

Sorry for the slow response here. The reason for this is we now return nothing for Tables.schema(query), because it's simpler, ends up giving better typed output tables, and sqlite doesn't give any guarantees on column types anyway (in their book, every column type is literally Any).

Now, I had played around with the idea that you could return Tables.Schema{names, nothing} to return a "partial" schema for a table, and there's actually some plumbing in place to support it, but I'm a little worried about sinks that wouldn't be able to handle it.

That said, we could probably find a solution for this case specifically, because it's a builtin function and wouldn't be too hard to support on a one-off case.

[kescobo]

Ah, that makes sense - I didn't look deep enough to realize there was Tables stuff happening under the hood. I definitely don't think this one use case justifies additional work for the upstream API (unless it's a useful excuse to do something you were planning to do anyway 😆 ). But I do think it might make sense to special-case this function, since the output never seems to be anything other than a single-field NamedTuple.

I looked at the code, but wasn't sure how to handle the sink kwarg.

[quinnj]

The right solution here, if someone is up for it, would be to create a dedicated struct DBTables ... end type that was it's own "table type" that did a very easy/quick Tables.jl implementation and provided what the known schema will be. It would be an easy way for someone to get some hands-on, simple experience implementing Tables.jl API. Happy to chat here or on slack if someone's interested. Marking "help wanted"