TAP information schema

[Patrick Dowler]

On 2007-10-11 14:08, Doug Tody wrote:
> I'm still trying to figure out if we really need CATALOG_NAME.  This is
> NULL or undefined for some DBMS implementations (MySQL and Informix,
> so far as I have been able to check), and in other cases appears to
> be fixed for a runtime DBMS context (PostgreSQL, possibly MSSQL).

The catalog is nominally about storage allocation within the server (it is 
the "database") but practically man people use it as both the storage 
allocation (which users don't care about) and the namespace (and only ever 
use one schema value within a catalog (the default, which is the db owner or 
sometimes the userid for things like temporary databases). 

People get away with it because servers typically allow one to write joins 
across catalogs. The fully-qualified table name is really $catalog.$schema.
$table and in the case of the default schema one uses $catalog..$table (yes, 
with 2 dots) so you can't really ignore it.

As for the use of the schema name, we use it in the namespace sense quite a 
lot, specifically to allow multiple versions of sets of tables to co-exist. 
The trend of using an ORM approach to creating and maintaining DBs makes this 
happen a lot and for various reasons one wants to re-use table names and just 
change the schema name. The same is true if you re-use some class(es) in 
multiple data models since the data model nominally maps to the schema.
But, there are lots of interesting and useful queries that join tables in 
different schemata...

One might be tempted to just say that in TAP "table name" mans fully qualified 
table name (eg just put $catalog.$schema.$table in there), but that would 
mean that table name in TAP is not the same thing as table name in ADQL, 
where these other things are explicitly present and dot is not alllowed in 
the table name.  I suppose it wouldn't be too dangerous if the VOResource 
contained FullyQualifiedTableName for unambiguous use.

For the VOSpace example, if I was implementing that I would most likely make 
vospace a database (for storage allocation purposes), require authentication, 
and give each user implicit schema creation privaledge. Then the uploaded 
VOtable would be known as vospace.$user.$tableName and I would have to do 
minimal work to make that happen and protect one user's tables from another.

As an aside, I would probably filter the metadata responses so that users see 
only tables they are allowed to query -- to save on subsequent permission 
errors and so people don't have to obfuscate their table names and use a fake  
identity to conceal their scientific work :)

-- 

Patrick Dowler
Tel/Tél: (250) 363-6914                  | fax/télécopieur: (250) 363-0045
Canadian Astronomy Data Centre   | Centre canadien de donnees astronomiques
National Research Council Canada | Conseil national de recherches Canada
Government of Canada                  | Gouvernement du Canada
5071 West Saanich Road               | 5071, chemin West Saanich
Victoria, BC                                  | Victoria (C.-B.)