table_name syntax

[Mark Taylor]

Laurent,

On Wed, 29 Apr 2015, Laurent Michel wrote:

> Le 28/04/2015 18:08, Mark Taylor a écrit :
> > I don't really understand this suggestion.
> >
> > A schema_name column already exists in the tap_schema.tables table.
> >
> > Marco, are you really saying we need a schema_name column in the
> > columns table too?  Would that help?
> My understanding is that you can have multiple tables with the same name but
> in different schemas.

In general yes, but for some services it may not be the case;
for instance in TAPVizier the table "II/246/out" is in schema vizls,
but the name "II/246/out" is unique in the service, and I can write

   SELECT * FROM "II/246/out"

or

   SELECT * FROM vizls."II/246/out"

I know that because the TAPVizier's TAP_SCHEMA.tables table_name
column contains the entry "II/246/out"[*] (and not vizls."II/246/out").
That suggests using the former (shorter) form, which is fine by me.
([*] Well actually it doesn't have the quotes right now, but I
     think it should.)

> That implies to use a path (schema.table) to reference them in the
> table_column for instance. The client is supposed to parse that kind of path
> to get the  name of the table which can not always be achieved with some
> Vizier tables, unless using inferences about the possible double quotes.

Most clients do not need to do that kind of parsing.  If all you want
to do is display table metadata or assemble ADQL text (these are the
things topcat needs to do, and I guess TapHandle too), or ensure
uniqueness of the table+column content as mentioned by Marco,
you can just use the table_name value.  This is a guaranteed unique
identifier for the table; it may or may not include a schema
(or catalog.schema) prefix according to whether it's required for
uniqueness, which is something the service knows and the client
doesn't need to worry about.

Admittedly, if you need to recover the unqualified and unquoted
table name for some reason (I get the impression this is one of
Dave's requirements, though I may be wrong) it's a bit more fiddly
under the current scheme, but it's not impossible, and I would
say that's a niche requirement.

> My idea is that since tables are located by both a name and a schema then
> these 2 elements must be given everywhere we need to locate a table. These 2
> elements should never be merged in any tap_schema cell and thus we could get
> rid of quoting issues.

As I say, you don't always need a name and a schema to identify a table,
it depends on the service implementation.  Conversely, as Pat pointed
out, sometimes you might need a catalogue too.  I think leaving the
decisions about how to construct the best table identification string
for use in ADQL, as presented in the existing table_name field,
is a Good Thing.

Mark

--
Mark Taylor   Astronomical Programmer   Physics, Bristol University, UK
m.b.taylor at bris.ac.uk +44-117-9288776  http://www.star.bris.ac.uk/~mbt/