[TAP] data type for column metadata

[Gerard]

Dear colleagues

I would like to bring back the discussion to the question how to specify *in
TAP metadata* what the data type of a column is.

I believe that the usage of column data types extends beyond how to specify
what its serialisation in a VOTable is.
Imho the role of VOTable in TAP should (only) be that of transport vehicle
for the results of queries. There is no need to also have it be the
specification defining what data types to use in TAP metadata, Its type
system is too limited to serve the needs of ADQL writers. The latest
discussion seems to address mostly ways how to fix that by adding features
such as format to VOTable. While this may be a useful thing to do for
VOTable, it is not at all an obvious solution for TAP metadata.

TAP, with ADQL is meant to query databases. ADQL is based on SQL92 and SQL92
already defines a set of data types. 
That set is not equivalent with the set of data types defined in VOTable. It
is neither a subset nor a super set.
Those data types are the usual ones used in SQL92 itself, for example in
CAST, and in DDL create table and create function statements. These are
currently not part of ADQL, hence that spec also had no need to explicitly
define the datatypes (though they appear in the list of reserved words).
They may well become part of a future version of ADQL, which may then
acquire a <data type> expression ala
http://savage.net.au/SQL/sql-92.bnf.html#data%20type. 

So why not choose this set of types as the type system for TAP, if required
with extensions for ADQL specific features (REGION stuff), possibly extended
to SQL2003 (to include BIGINT, BLOB, CLOB etc). 
If deemed necessary one can always define (predefine?) a mapping from these
types to VOTable types. That seems to me the preferred usage of a low level
container such as VOTable is (freely quoting Mark).

I have made a table comparing various SQL-like datatypes in the wiki
http://www.ivoa.net/cgi-bin/twiki/bin/view/IVOA/GerardLemsonADQLDataTypes.
To me it seems that the JDBC types provide a nice list, as Doug also once
remarked:

BOOLEAN
BIGINT
BLOB
CHAR(P)
CLOB
DATE
DECIMAL(P,S)
FLOAT(P)
INTEGER
NCHAR(P)
NUMERIC(P,S)
NVARCHAR(P)
REAL
TIME
TIMESTAMP
VARCHAR(P)


Best regards

Gerard Lemson



> -----Original Message-----
> From: Francois Ochsenbein [mailto:francois at vizier.u-strasbg.fr] 
> Sent: Tuesday, March 24, 2009 11:04 AM
> To: Patrick Dowler
> Cc: dal at ivoa.net
> Subject: Re: [TAP] data type for column metadata 
> 
> 
> Well, that's typically why the utype attribute was introduced:
> specify as accurately as possible what is the meaning of a 
> parameter vs a data model. For instance, an ISO time could be 
> defined as utype='stc:AstroCoords.Time.TimeInstant.ISOTime'
> the stc prefix referring tp Space-Time coordinates (there 
> should therefore be at the top of the VOTable a reference to 
> the XML schema as:
>   xmlns:stc="http://www.ivoa.net/xml/STC/stc-v1.30.xsd" )
> 
> Or do you think that this is not appropriate ?
> Sure it looks complex, but Arnold would answer that 
> specifying accurately which time is used is complex...
> 
> Cheers, francois
> 
> >
> >On 2009-03-19 13:26:41 Mark Taylor wrote:
> >> I believe the best way to handle this would be to introduce an 
> >> additional attribute on VOTable FIELDs (columns) called something 
> >> like "format".  The interpretation of the content of this 
> field need 
> >> not (and probably should not) be specified by the VOTable standard 
> >> itself, but by VOTable 'customers', for instance the TAP 
> standard, or 
> >> user communities.  A string column labelled format="iso8601" (or 
> >> possibly something like format="tap:iso8601", or indeed, 
> >> format="postgresql:MAC") could be processed as a string by generic 
> >> VOTable libraries without requiring any changes to the VOTable 
> >> standard or implementations, but an application which 
> understood this 
> >> format type could do something sensible with it as appropriate.
> >
> >I like this idea as it lets you specify the way something 
> was encoded, 
> >typically into a string. In TAP we could use that for timestamps and 
> >STC regions (format="STC/S"?)... maybe some other uses that have not 
> >come to light yet.
> >
> >> Having these conventions outside the VOTable standard itself means 
> >> that the VOTable standard does not have to keep undergoing 
> changes as 
> >> different user communities find that the type they need is not 
> >> currently offered by VOTable.  This benefits the VOTable standard 
> >> (more stable) and the user communities (less effort adapting it to 
> >> their uses).
> >
> >On a  practical level, can we feasibly request/expect such 
> an addition 
> >to VOTable (it appears that 1.2 is still a WD) and have TAP require 
> >that new version of VOTable (for output)? On a relatively 
> short timescale?
> >
> >--
> >
> >Patrick Dowler
> >Tel/Tél: (250) 363-0044
> >Canadian Astronomy Data Centre
> >National Research Council Canada
> >5071 West Saanich Road
> >Victoria, BC V9E 2M7
> >
> >Centre canadien de donnees astronomiques Conseil national de 
> recherches 
> >Canada 5071, chemin West Saanich Victoria (C.-B.) V9E 2M7
> ==============================================================
> =========
> Francois Ochsenbein    ------   Observatoire Astronomique de 
> Strasbourg
>    11, rue de l'Universite 67000 STRASBOURG  Phone: 
> +33-(0)390 24 24 29
> Email: francois at astro.u-strasbg.fr (France)    Fax: 
> +33-(0)390 24 24 17
> ==============================================================
> =========
>