For the MySQL Million Challenge, I was going through the server syntax in order to understand what things can be created in the server. And now my OCD triggered. DDL is a mess.
As a database developer, I want to be able to create server objects using the
The server gives you that for the following things:
- FUNCTION (and FUNCTION SONAME)
- LOGFILE GROUP (NDB only, not going to look at this)
- RESOURCE GROUP
- SPATIAL REFERENCE SYSTEM
As a database developer I want to be able to script things safely, so I need
IF NOT EXISTSclauses in my
Statements that create things often allow you to specify
IF NOT EXISTS for conditional creation of the thing:
Creation of the thing will fail if it already exists.
IF NOT EXISTS clause the creation is conditional, so if the thing already exists this is not an error.
This is important for scripting: If the thing already exists, the script will throw an error and depending on settings will stop. It is even more important in replication, because if you replicate the creation statement to a replica, the replication will stop with an error unless it is conditional.
CREATE statements replicate, but do not offer conditional syntax.
They cannot be used safely in replication, and they are cumbersome in scripting.
CREATE statements offer deviant safe creation: Instead of
CREATE ... IF NOT EXISTS they offer
CREATE OR REPLACE ....
Multiple object syntax
As a database developer I want to be able to chain multiple
DROPactions together in a single statement, so that the things are created (or dropped) together, atomically.
Alternatively, DDL could be made transactional. This makes sense even if a rollback is not possible, if they are being executed in a kind of DDL transaction under a lock, so that they appear to happen atomically.
CREATE syntax does not exist.
For few objects, a multiple drop syntax does exist, but support is thin.
As a database developer, I want to be able to
DROPthe things that I created.
As a database developer, I want to be able to make the
DROPconditional using an
IF EXISTSclause, to be able to script and replicate this operation safely.
The support for this is better than for creation, for some reason, and there is no deviant syntax. Still, some things cannot be dropped safely.
Enumeration and Definition display
As a developer, I want to be able to enumerate all the things of a category.
Logically, an API is incomplete without enumeration. I need to be able to see what exists.
In MySQL, a lot of things can be listed with
SHOW commands, and all things can be listed by looking into the appropriate system tables.
Apparently, a lot of things can not be listed using the
SHOW plural syntax (
For a few things, a deviant syntax is supported (
SHOW PROCEDURE STATUS) that also shows the definition of the thing.
For other things you have to acces system tables. These system tables seem to be spread around, some are in I_S (Tablespace), others in mysql.* (servers).
Generally, MySQL seems to favor the syntax
SHOW CREATE thing to show an SQL statement that would recreate the thing if I dropped it.
But for some things, this is not implemented, despite the fact that the information for this is available in various system tables.
Altering and renaming things
As a database developer, I want to be able to change the definition of a thing after creation.
Most things can be altered after creation.
For some things, some attributes cannot be altered even if an
ALTER statement exists. Notably, it is impossible to rename a database.
Only two things can be renamed, tables and users.
For other things, renaming is possible using
ALTER TABLE RENAME syntax, but a pure
RENAME does not exist despite the fact that renaming is supported.
For roles and users,
ALTER thing IF EXISTS conditional syntax is supported, but for all other things it is not.
All thing creations are replicated, except resource groups and servers. That even makes sense, which is unusual, given all the other illogical anomalies.
A table, because this is about databases
|Command||replicates||enumerate SHOW||CREATE||IF T EXISTS||OR REPLACE||DROP||IF EXISTS||MULTI||SHOW CREATE||ALTER||IF EXISTS||RENAME|
|RES GROUP||* *||YES||YES|
SHOW FUNCTION STATUSdeviant syntax enumerates the function names and definitions.
- *2: Indexes are not first class citizens, they are part of table definitions.
SHOW PROCEDURE STATUSdeviant syntax enumerates the procedure names and definitions.
ALTER thing RENAMEsyntax exists, but no