Hi Thufir, all !
> On Tue, 04 Mar 2008 09:48:03 -0600, Paul DuBois wrote:
>> In MySQL, the two are equivalent. The keyword DATABASE or DATABASES can
>> be replaced with SCHEMA or SCHEMAS wherever it appears.
> Right, but that wasn't exactly what I was asking. I'm fairly familiar
> with MySQL but am trying to understand this criticism of it. Not being
> familiar with other databases I have no reference point. What are they
> getting at?
> Why would you want to do a query of:
> SELECT * FROM database.schema.table;
> Obviously, this is non-sense in MySQL, where database <==> schema.
AFAIK, this all goes back to an ANSI standard for SQL in the mid-80s.
That standard had a "CREATE SCHEMA" command, and it served to introduce
multiple name spaces for table and view names. All tables and views were
created within a "schema". I do not know whether that version defined
some cross-schema access to tables and views, but I assume it did.
AFAIR, no product (at least back then) really implemented it, that whole
concept was more theory than practice.
OTOH, ISTR this version of the standard did not have the concept of a
"user" or a "CREATE USER" command, so there were products that used the
concept of a "user" (who then had his own name space for tables and
views) to implement their equivalent of "schema".
This is an area where systems differ.
As far as administration is concerned, this should not matter too much,
because here you have differences anyway.
As far as you look at application code, you "only" have to care about
cases where one application accesses tables from multiple name spaces.
AFAIK, all systems support a syntax "<name space>.<local identifier>",
and for this it should not matter whether the name space is that of a
user, a "schema", or a "database".
(I do not claim having done a complete research, so maybe there are
systems which differ in this regard.)
I have not heard of a three level naming scheme yet.
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com