table_name syntax

[Laurent Michel]

Le 29/04/2015 11:18, Mark Taylor a écrit :
> 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.
Your are right, it not impossible, but not easy at all. Imagine you get viz4.B/avo.rad/wsrt as table name
Dots might be (or not) path elements separators. Then, if you want to extract the table name, you have to look at a schema named 
viz4 in the column schema_table.schema. If there is one you can suppose that viz4.B/avo.rad/wsrt has the schema.table form and 
that the table name is B/avo.rad/wsrt (hoping that the table name does not start with viz4)
That is what I called doing inference.
My opinion is that forcing clients to do such computation is a clue of some weakness in the DB schema.
>
>> 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.
I'm definitely not satisfied with this polymorphic definition of the table_name column.
I've no experience with catalogues but it seems to me that using in this case catalog.schema as schema names could be an 
acceptable workaround.

Laurent
>
> 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/
>

-- 
jesuischarlie

Laurent Michel
SSC XMM-Newton
Tél : +33 (0)3 68 85 24 37
Fax : +33 (0)3 )3 68 85 24 32
laurent.michel at astro.unistra.fr <mailto:laurent.michel at astro.unistra.fr>
Université de Strasbourg <http://www.unistra.fr>
Observatoire Astronomique
11 Rue de l'Université
F - 67200 Strasbourg
http://amwdb.u-strasbg.fr/HighEnergy/spip.php?rubrique34