Sample table description using VOTable

[Alex Szalay]

    Note 2: Does "datatype" refer to the SQL datatype or the VOTable
    datatype?

since this is in the description of the database table, I would think that
the datatype should refer to the internal datatype. This is the mechanism we
can use to discover what is in the tables. 

--Alex  

-----Original Message-----
From: owner-dal at eso.org [mailto:owner-dal at eso.org] On Behalf Of Doug Tody
Sent: Saturday, October 06, 2007 6:42 PM
To: Patrick Dowler; Francois Ochsenbein; Ray Plante; Mark Taylor
Cc: dal at ivoa.net
Subject: Re: Sample table description using VOTable

Hi team TAPIS -

I have extracted the important bits of draft TAP information schemas which
we each submitted, and summarize these below to make it easier to compare
each shema (minus the VOTable formatting which is a detail at this point).

There is a lot of overlap, and it would not be difficult to combine these,
if some questions were decided, e.g., 1) follow VOTable or IS conventions
for naming, datatypes, etc.?;  2) should we support the registry view at
this level, e.g., Role, Std? (note Role can also be useful to interpret
standard Views);  3) should we differentiate between PARAM and FIELD at this
level, or consider both to be like a VOTable FIELD (with PARAM being a
VOTable feature)?;  4) include SQL-IS features such as "catalog_name",
"schema_name", etc.?;  5) how to handle nil/nullable?

The NRAO systems are being updated this weekend and email is currently down,
so I have no idea whether this email will actually go out, or whether
anything else TAP-related was posted today.  I will check later when email
is restored to see whether the mail was sent.

        - Doug



DT Schema

    TABLES
        Id              Unique Table ID
        Name            Table name                              (Required)
        Type            Table type (base, view)                 (Required)
        Role            How table is used, e.g., "out"
        Ucd             Can a table actually have a UCD?
        Utype           UTYPE can apply to a table
        Description     Short one line table description


    COLUMNS
        Id              Unique column ID
        TableName       Table to which column belongs           (Required)
        Name            Column name                             (Required)
        Datatype        Datatype (as in VOTable + "string")     (Required)
        Arraysize       Arraysize (as in VOTable)
        ColumnType      Column type (as in VOTable Field, Param)
        Std             Standard column (boolean)
        Value           Value if ColumnType=Param (fixed value)
        Ucd             UCD for column
        Utype           UTYPE for column
        Unit            UNIT for column
        Description     Brief one-line description of column

    Note 1: Role and Std are provided for the registry view
    Note 2: Note that Columns can describe VOTable Param as well as Field


FO Schema

    (VOTable table header(s), plus the following for the schema approach)

    TABLES
        Name            Table name
        Description     Table description

    COLUMNS
        Name            Column label
        Ucd             UCD1_ representation
        Type            Data-type
        Width           Length for strings/blobs
        Unit            Units of the data (controlled)
        Description     Detailed explanation of the column

    Note 1: Here Columns describes only a single table


RP Schema

    TABLES
        Id              Unique ID for the table
        Name            The name of the table
        Ucd             The UCD describing the table
        Utype           The UTYPE describing the table
        Nrows           The number of rows in the table (if static/known)
        Description     A description of what the table contains

    COLUMNS
        table_ID        ID of the table this column belongs to
        Id              Unique ID for the column
        Name            The name of the column
        Ucd             The UCD describing the column
        Utype           The UTYPE describing the column
        Unit            The unit associated with values in this column
        Datatype        The datatype of values in this column
        Arraysize       Number and array "shape" of values in this column
        Precision       The precision description for values in this column
        Width           The number of characters used to encode the value
        Description     A description of what the column contains

    Note 1: A SQL-like query of Columns "where tableName = foo" will not
    work here as an internal ID is used instead of the table name.

    Note 2: Things like ID should not be XML specific, as we may need to
    represent this information in other contexts.


PD Schema

    (This is based on a subset of the SQL information schema rather than
    the VOTable model, with some additions)

    TABLES
        catalog_name    The name of the catalog containing the table
        schema_name     The name of the schema for the table
        table_name      The name of the table itself
        table_type      The type of the table: TABLE or VIEW
        ucd             The UCD describing the table
        utype           The UTYPE describing the table
        description     A description of what the table contains

    COLUMNS
        catalog_name    The name of the catalog containing the table
        schema_name     The name of the schema for the table
        table_name      The name of the table itself
        column_name     The name of the column
        ordinal_position        The position of this column in the table
        is_nullable     Whether this column may hold NULL values
        datatype        The datatype of values in this column
        ucd             The UCD describing the column
        utype           The UTYPE describing the column
        unit            The unit associated with values in this column
        description     A description of what the column contains

    Note 1: Catalog/Schema are included here and may be needed in TAP
    to reference tables in different "databases" (such as a local DBMS
    or a named VOSpace).

    Note 2: Does "datatype" refer to the SQL datatype or the VOTable
    datatype?