SQLite + TAP_SCHEMA or similar for self-describing SQLite files
gmantele at ari.uni-heidelberg.de
Mon Sep 25 11:32:02 CEST 2017
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.
Although I do not know anybody using the TAPLibrary with SQLite, this
works fine everytime I try. So, I'd recommend using this solution.
More information about the dal