WL#2073: Schemas in ANSI style

Given that we will map databases to schemas 
(as in WL#2071 Database = Schema), our next 
step is to support all ANSI/ISO "schema" 
statements and capabilities. 
 
Most of the description of this task was 
salvaged from WL#942 progress reports. 
Originally WL#942 was "Database = schema", 
but as it slowly became a "Catalogs" task 
the original requirement was destroyed. 
Despite my efforts to recover all the 
original, the text is now quite a mess.  
 
A MySQL "database" is analogous to a       
standard-SQL "schema". Given that       
decision, we can do some mapping.       
       
There will be a full hierarchy       
"catalog.schema.table" but it is       
equivalent to       
"catalog.database.table" and not       
"database.schema.table".       
       
The catalog level does not need       
to be implemented immediately:       
catalogs are a non-core feature,       
not required for the NIST tests,       
and not used in all DBMSs. But 
there is a separate worklog task 
for catalogs now, WL#942.     
       
When the catalog level is implemented,        
this means that the OS directory       
structure must change. The top level        
is catalog, within that are the schema       
directories. We did not discuss whether        
InnoDB would need any change.       
       
The catalog directory does not have to "include"       
the schema directories. As long as        
there is a way to "point to" the       
schema directories from the catalog        
directory, and vice versa, the DBMS       
can know what the hierarchy is.        
This might make a transition easier.       
       
Connector/J and MyODBC shall both       
return "schema" instead of "catalog"        
for the functions that ask what the       
equivalent name is for "database".        
       
These statements are equivalent:        
       
USE DATABASE = SET SCHEMA        
       
DROP DATABASE = DROP SCHEMA        
       
CREATE DATABASE = CREATE SCHEMA       
   
Monty has also suggested that we could   
add support for these statements,   
presumably to make transition easier:   
   
 USE CATALOG catalog_name;   
 USE SCHEMA|DATABASE schema_name;   
 USE catalog_name.schema_name;   
 
The following details have not been       
agreed upon, but seem uncontroversial.       
       
There must be a "default catalog".       
In the present structure (which has       
no real 3-level hierarchy) the default       
catalog is always "SYSTEM", or "PUBLIC",       
or whatever.       
       
Every schema has an "owner". For us,       
this "owner" is the user who said        
"CREATE SCHEMA" or "CREATE DATABASE".       
For always-present databases like mysql       
or test, there is an arbitrary owner,       
which I'll again call "SYSTEM".       
In standard SQL it is illegal for       
any user to create a table in a       
schema that he/she does not own --       
but we cannot follow standard SQL       
in that respect. Also, the "owner"        
of a schema has ALL PRIVILEGES on       
all tables in the schema automatically,       
even on the tables he/she did not       
create. (This may be undesirable,  
so it is a detail that's subject  
to change.).       
       
There is a schema INFORMATION_SCHEMA.       
It is virtual, that is, there is no        
directory structure of this name.       
The owner of INFORMATION_SCHEMA is        
a user named _SYSTEM (notice that       
this name is illegal in standard        
SQL, so that one cannot connect using       
this name). The description of 
INFORMATION_SCHEMA is in WL#173. 
       
A table is always within a schema.        
 
A character set or a collation is        
always within INFORMATION_SCHEMA.       
However, these objects are always        
the same in MySQL, so there is       
never a need to qualify them with        
INFORMATION_SCHEMA.latin1_general_ci       
or whatever. That should be legal,        
but isn't necessary.       
       
A user is NOT within a schema. One       
does not qualify a user name. In        
standard SQL, a user is not even       
within a catalog. It is a system-wide        
object. 
       
A privilege IS within a schema. The       
privilege is subsidiary to the object        
and not subsidiary to the user. Since    
we already store privilege information    
in the "user" database (i.e. the "user"    
SCHEMA), this might be okay.       
       
A procedure is always within a       
schema. In standard SQL, a built-in        
procedure (such as SUBSTRING) is in       
INFORMATION_SCHEMA, but I understand        
that we will not try to implement       
such a fiction.        
       
We have discussed the concept of a        
"public schema" or "public database".       
It could be named mysql.        
My preference would be a new one,       
named PUBLIC, which anyone can read,        
but only a limited number of users       
can write. Primarily we want        
this schema to store common-to-all       
procedures in.        
       
On startup, the public schema is the        
CURRENT schema. The CURRENT schema is       
usually not the same as the DEFAULT        
schema. The mechanism for changing       
the CURRENT schema is not yet known.        
       
The notation .table shall mean        
" . table".       
In other words, an empty space before        
a dot shall be interpreted as the       
current schema name.        
       
I suggested that we do not need a        
public or current schema, that we       
should use the standard-SQL PATH statement,       
but that motion did not pass.       
       
So a basic installation looks like this:       
       
----------- --------       
- catalog - - user -       
----------- --------       
|       
|________________________________       
|                 |             |       
----------------  -----------  -------------------       
- information_ -  - public  -  - schemas created -       
- schema       -  -         -  - by users       -       
----------------  -----------  -------------------       
|                 |            |       
----------------- ------------ -------------------       
- metadata view - - common   - - tables created  -       
- or            - - routines - - by users        -       
- _system       - ------------ -------------------       
- object        -       
-----------------        
       
Remember that everything in information_schema        
is virtual, and we don't need a public schema       
until 5.0, so the above diagram doesn't really        
represent a drastic set of changes.       
       
Eventually we want to claim compliance      
with SQL standard feature F031      
"Basic schema manipulation".      
      
We would also like to support SQL standard feature   
F311 "Schema definition statement" (core)   
   We have to support "CREATE SCHEMA    
   + authorizations + object-creation". For example:   
   CREATE SCHEMA S AUTHORIZATION PETER;   
   CREATE SCHEMA S CREATE TABLE T (S1 INT);   
   CREATE SCHEMA S   
   ... CREATE TABLE T ...   
   ... CREATE VIEW V ...   
   ... GRANT ...   
   ;   
The "AUTHORIZATION user-name" clause has no analogue   
in current MySQL, it should become the owner.   
The "CHARACTER SET character-set-name" clause   
(not illustrated) has an analogue in current   
MYSQL, it should become the database character set.   
 
The "CHARACTER SET character-set-name" clause    
(not illustrated) has an analogue in current    
MYSQL, it should become the database character set.  
 
These statements are equivalent:         
        
USE DATABASE = SET SCHEMA         
        
DROP DATABASE = DROP SCHEMA         
        
CREATE DATABASE = CREATE SCHEMA        
     
The full syntax for CREATE SCHEMA is       
in SQL-99 Complete, Really (pages 343ff).        
The only things I should add are:       
       
-- NIST assumes that CREATE SCHEMA implies        
SET SCHEMA, that is, CREATE DATABASE       
implies USE DATABASE. I don't see why.   
It's not certain that we'll allow this.       
Monty has also suggested that we could    
add support for these statements,    
presumably to make transition easier:    
    
 USE CATALOG catalog_name;    
 USE SCHEMA|DATABASE schema_name;    
 USE catalog_name.schema_name;    
    
    
The following details have not been        
agreed upon, but seem uncontroversial.        
         
-- SQL:2003 allows SET COLLATION       
.. But we'll worry about that later.   
       
Additional note, we should look at following the complete spec for CREATE 
SCHEMA and allow all create tables that occur as a part of it to not test for 
foeign key attributes until the schema is loaded (aka this allows two tables 
that rely on each other to be loaded). 
 
This task is a requirement for SAP DB compatibility,  
Milestone2 "M2 Schemas". 
 
See also: 
WL#2071 "Database = Schema" 
 
Write specification for implementation of schemas in MySQL.   
Note: Done, Worklog entry #942.   
Add CREATE SCHEMA and DROP SCHEMA, as in SQL-99.   
Add the framework for an implicit schema, for cases where no CREATE SCHEMA  
statement has been executed.   
Map the AUTHORIZATION clause of the CREATE SCHEMA statement to the owner of all  
tables created for the schema. This will fulfill the requirement that SAP DB  
tables have owners.   
Note that this task is related to that of establishing standard security  
handling; MySQL must have the ability to create users and user groups before  
CREATE SCHEMA can define an owner.   
Modify the CREATE TABLE statement to allow the owner to be explicitly defined,  
i.e. CREATE TABLE . and CREATE TABLE TEMP..   
Add a user (i.e. an owner) called TEMP, to specify a temporary table.   
Modify the CREATE TEMPORARY TABLE statement to support the creation of a  
temporary table with the syntax CREATE TABLE TEMP..   
For each user, add a silent schema with the same name as the user name. SAP  
DB's qualification level is users/owners, while MySQL's is schemas. By making  
sure there is a one-to-one relationship between a schema and a user you can  
ensure that there will be no conflict.   

Feature requests:
BUG#5754 Please: SCHEMA & GROUP support
 


        Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.