SQLite + TAP_SCHEMA or similar for self-describing SQLite files

[Grégory Mantelet]

Hi Brian,

On 09/22/2017 12:30 AM, Van Klaveren, Brian N. wrote:
> For the metadata describing the tables, I'm hoping to use a variant of the TAP_SCHEMA supporting tables and/or the RegTAP tables (e.g. res_table, table_column), sticking as close as possible to those tables. We are also planning on also using SQLite files as a portable data format, which doesn't have the notion of schemas (well it does, but they are separate files), so we need to keep those tables within the same schema as the tables we are writing, so actually having a separate "TAP_SCHEMA" or "rr" schema doesn't work, because you'd need two files.
>
> Initially, we thought about creating corresponding metadata tables for every table with some namespace. For example, if you had a table named simply "result", you might have "result$res_table" (with one row), "result$table_column".

Just a short comment here. The method you suggest is the method I use in 
the TAPLibrary when a database (and especially SQLite) does not have the 
notion of schemas. In the published TAP_SCHEMA and ADQL queries, 
everything is like the notion of schema is supported. But internally in 
the library, I perform a "translation": ADQL names are translated into 
DB names. Those DB names are as you suggest a table name prefixed by a 
schema name and an underscore ; so: <schema name>_<table name> (e.g. 
TAP_SCHEMA_tables).

Although I do not know anybody using the TAPLibrary with SQLite, this 
works fine everytime I try. So, I'd recommend using this solution.

Cheers,
Grégory