[TAP] Summary: data type for column metadata

[Douglas Tody]

On Fri, 17 Apr 2009, Patrick Dowler wrote:

> On Friday 17 April 2009 08:15:14 Gerard wrote:
>> The second seems to say that VOTable could also be used to express the
>> structure of the database itself.
>> This should then be done using empty VOTable-s for example.
>> The attractive feature of this seems to be that VOTable metadata is richer
>> than a simple listing of columns with some attributes.
>> PARAM elements can assign extra features to TABLEs and possibly nested
>> GROUP elements can link columns together, potentiall adding
>> coordinate systems etc. This may be useful indeed, though I would rather
>> have this implemented in TAP as a separate servcice request,
>> something like a
>>         REQUEST=getEmptyVOTables
>> and NOT as a natural outcome of a "select top 0 * from sometable" ADQL
>> query.
>
> Doing this is one of the reasons that MAXREC can be used with an ADQL query,
> even though TOP appears to do the same thing. If a client wants to get clean
> and complete metadata via VOTable, this seems the most direct way:
>
> LANG=ADQL&QUERY=SELECT * FROM someTable&MAXREC=0

The original motivation for having MAXREC=0 return an empty votable
was that we needed to support the null query in any case, since it is
a legal boundary condition on any data query.  Hence we might as well
formalize this usage and state clearly that MAXREC=0 would return an
empty votable containing only metadata.

In many cases however the client will prefer to use the standard table
query mechanisms to directly query table metadata, hence the need
for TAP_SCHEMA.  This works without requiring special mechanisms on
the client side to deal with a header-only VOTable or registry XML.
Any client which can query data tables can also query table metadata.

Such a query could be posed with ADQL, but some folks have argued
in the past that using ADQL for table metadata queries should be an
optional advanced capability, due to the requirements it places on
the implementation to support general table metadata queries (this
feature would probably require that TAP compliant metadata be stored
in actual DBMS tables, which is a significant requirement to place
upon a data providers database).  We should not glibly assume that
all TAP services would provide this capability.

> or (e.g., PQL not really discussed much yet, but) might be:
>
> LANG=PQL&FROM=someTable&MAXREC=0
>
> Further,  MAXREC is already needed to negotiate max query result size with the
> service, so MAXREC=0 is already an available mechanism and applies to any
> query language used.

One of the original motivations for the param query was to provide
a simple way to query table metadata, more convenient and powerful
than registry XML, which would use the standard table query mechanisms
but not require generalized ADQL support for table metadata queries,
which is a significant requirement to place on service implementations.

With param query a metadata query for the columns of a table is
something like the following (generalizing LANG to QUERYTYPE and
replacing the fictional PQL with an actual TAP parameter query):

     QUERYTYPE=PARAM&FROM=TAP_SCHEMA.columns&WHERE=tablename,someTable

The result would be, as for an ADQL query, a table conforming
to the TAP schema containing the metadata for the named table.
Other TAP_SCHEMA tables could be queried in the same way if other
metadata is required (the most common case would be TAP_SCHEMA.tables).

To get the full tableset metadata as VOTable metadata one could either
do a null query of any type (MAXREC=0) or query the entire tableset:

     QUERYTYPE=PARAM&FROM=TAP_SCHEMA.tableset

would return metadata for all tables in the default output format
of VOTable.  If the service supports it one could instead request
only the metadata for a single table, or only the tables with data
available in a specified region on the sky.

If instead we have

     QUERYTYPE=PARAM&FROM=TAP_SCHEMA.tableset&FORMAT=xml

then the output would VOSI/registry compliant XML for the tableset
supported by the service.

The advantage of using a parameter query rather than ADQL for basic
table metadata queries is that we can do common simple things such
as list all tables or all the columns in a table, without requiring
a full implementation of table metadata in the underlying DBMS, as
is required for ADQL.  VOSI does this as well, but only supports
static metadata and is limited to describing the full tableset,
which could be a problem for very large databases (e.g., one cannot
get the metadata for a single table).

 	- Doug