| [dtp-dev] Default schema for a connection |
I asked Max (Hui) Cao a question about the notion of the default schema for a connection. This was prompted by my confusion about the choices that were presented by the SQL Editor when its content assist was giving me a list of tables. The tables were from the schema that the SQL Editor deemed the connection's default schema.
The name of the default schema turns out to be the user name from the connection profile.
Max suggested that if I still had concerns that I post my question to dtp-dev so here it is. I hope that this is an acceptable question for this mailing list.
Max explained the reasoning behind the use of the user name as follows (I'm assuming that quoting from his email is OK since he suggested I might want to broaden the discussion):
Hi Charles,
Below is the exerpt from the SQL 2003 standards: 5WD-02-Foundation-2004-07.pdf
4.20 SQL-schemas
An SQL-schema is a persistent descriptor that includes:
— The name of the SQL-schema.
— The <authorization identifier> of the owner of the SQL-schema.
— The name of the default character set for the SQL-schema.
— The <schema path specification> defining the SQL-path for SQL-invoked routines for the SQL-schema.
— The descriptor of every component of the SQL-schema.
In this part of ISO/IEC 9075, the term “schema” is used only in the sense of SQL-schema. The persistent objects
described by the descriptors are said to be owned by or to have been created by the <authorization identifier>
of the schema.
You can see from the definiton that a Schema is always associated with an <authorization identifier>, which could be a user, group, or role. The term "Database" is used ubiquitously, and I admit that even in DTP, the use of this term is not consistent. In SQL model, we have Database->Catalog->Schema objects with Database as the root object; while in connection profile, the "Database" name is referring to the catalog name indeed. Hope that helps.
In fact, you can post your question to the dtp-dev mailing list for a broader range of discussion if you have doubts.
Best Regards!
Max (Hui) Cao
Sybase Shanghai RD
(end of Max's reply)
Where I am still confused is Max's statement that a Schema is always associated with an <authorization identifier> (which I assume means the user name from the connection profile). The SQLDBUtils.getDefaultSchemaName method uses ProfileUtil.getProfileUserName for its value which seems to be treating the <authorization identifier> as a schema rather than the owner of a schema.
In our database system (Teradata), it happens that a user name has a schema associated with it but that schema doesn't necessarily have to be the user's default schema. And the default schema for a connection can be set in the connection URL.
Once connected, Teradata has a notion of the default schema which isn't necessarily anything found in the connection profile. So it seems to me that the default schema for a connection should be a property of the connection itself rather than the connection profile. Or is Teradata so very different from all other database systems?
We are using the "Database" property value of the connection profile in the connection URL to set the default schema for the connection. (Teradata doesn't have catalogs.) Max's comment about DTP's inconsistent use of the term "database" was in response to that. If anybody can suggest what the Database property from the connection profile should mean for systems that don't have catalogs like ours, we'd be glad to hear them. (Are we using the Database property badly?)
Thanks for your attention.
--Charles Eutsler
NCR (soon to be Teradata) Corporation