table_name syntax

[Patrick Dowler]

In Sybase, the convention semes to be that schema support is sketchy and 
people generally use the catalog as both allocation and namespace. So, 
if someone implements a TAP service on Sybase, they would quite likely 
want to expose tablenames line foo..bar (implicit schema between the two 
dots). Yeah, that's all kinds of ugly but this is what people do.

You can also put foo.dbo.bar since the schema is usually dbo (in sybase, 
schema appears to be pretty much equivalent to owner as you need an 
account with that name to create objects in that schema). Now, one could 
omit the catalog (foo.) part as long as foo is the current catalog; that 
can be accomplished by configuring connections appropiately but if 
someone ever has a TAP service on sybase that spans multiple catalogs 
(because they used catalog as the namespace) then they are in for a 
world of pain.

So: yes: this problem just comes up again in the catalog part of a real 
RDBMS server. Unfortunately, tap_schema and VODataService (VOSI-tables 
content) do not model catalog at all. The idea of just putting the 
table_name people actually need to use in the tap_schema is to hide such 
gory details that I think for the most part users don't need to care about.

Pat

PS- I'm pretty sure I came down on the side of "use the quoted 
identifier in the tap_schema if that is what is required for the query 
to work" and I really think requiring the client to put quotes in where 
needed is asking for a lot of pain. Services know when they are needed 
and when they break things and only services should put them in place... 
and avoid the need for them whenever possible!!

PPS- tap-lint does a test where it quotes a column name it finds in a 
table in the tap_schema. I noted 2-4 query failures per day in our logs 
caused by this test. It broke because in postgresql it subverts the way 
case-insensitive table and column names are implemented (PG just 
converts everything to lower unless you quote them!). My "fix" was 
simply to strip the quotes in that case because I Know they can only 
break it. While the test is probably technically correct, it is really 
just asking for trouble :-)


On 28/04/15 07:45 AM, Marco Molinaro wrote:
>
> Also, if someone comes up really using catalogs, will we have to redo
> the exercise for "catalog"."schema" thing?
>
> I agree in any case that a transition phase adding the schema_name in is
> columns table can solve the problem in a neat way (or at least so
> appears to me).
>

-- 

Patrick Dowler
Canadian Astronomy Data Centre
National Research Council Canada
5071 West Saanich Road
Victoria, BC V9E 2E7

250-363-0044 (office) 250-363-0045 (fax)