Soothsaying SQL Standardization Stuff
In an earlier blog posting “SQL Standards, ANSI committees, and Sun”, I (Peter Gulutzan) talked about our prospects for joining the American committee charged with database standards, which we typically call “ANSI” although that’s not the formal name (and by the way the formal name is about to change, but I’ll chat about organization some other time).
Well, I’m now Sun’s official voting delegate to the committee. There are also three “alternate” delegates from other parts of Sun; I’ll loosely categorize them as advocates from our PostgreSQL-ophile and Java / Java DB interest groupings. Mostly my concern is the MySQL side of things.
The committee holds frequent meetings by telephone conference, and infrequent ones in personal get-togethers. I’ve just finished attending one of the lengthier meetings. I won’t discuss details or say how votes are going — our meetings are not public. But it’s always been harmless to inform the public about some things that might or might not be in a future version of the standard (”SQL:201x”). So, if we MySQLers act on some relevant feature in the next few years, you’ll know what we’ll take into account from the standard.
Enforced constraints.
A table foreign-key or check constraint may contain [NOT] ENFORCED. For example:
CREATE TABLE t2 (s1 INT, FOREIGN KEY f REFERENCES t1 (s1) NOT ENFORCED);
ALTER TABLE t2 ALTER CONSTRAINT f ENFORCED;
The DBMS ignores a not-enforced constraint. The default is ENFORCED. Currently MySQL has no equivalent, although it does have
SET @@foreign_key_checks = 0 | 1;
ALTER TABLE t2 DISABLE | ENABLE KEYS;
The words DISABLE and ENABLE are what Oracle uses. The words [NOT] ENFORCED are what DB2 uses.
Versioned tables.
Think of a bank-application table which has current balances and an associated history of deposits/withdrawals. We’d like to ask “what was the balance as of January 1″, which involves reversing transactions that took place since January 1. In other words, the DBMS should maintain and be aware of a history of changes. An example of syntax is:
CREATE TABLE t
(bank_balance DECIMAL(5,2),
sv_start TIMESTAMP GENERATED ALWAYS AS SYSTEM VERSION START,
sv_end TIMESTAMP GENERATED ALWAYS AS SYSTEM VERSION END
) WITH SYSTEM VERSIONING KEEP VERSIONS FOR 2 YEARS;
This might be good for audit trails. I don’t regard this as terribly close to Flashback Technology or version-enabled tables in Oracle; this is new to me.
General intervals.
The currrent standard INTERVAL data type is for datetime differences, for example “2 hours 15 minutes”. A more general interval would be applicable for spatial differences as well a datetime differences, and could have an anchor, for example “2 hours 15 minutes starting at noon on January 1″. Sometimes this sort of thing has been called a PERIOD. The effect on MySQL is small, we don’t even support INTERVAL yet (WL#831).
Named parameters
You can give a parameter a name, for example
CREATE PROCEDURE p (p1 INT, p2 INT) …
CALL p(p2 => 5, p1 => 10) …
I would have preferred to use AS for naming (as we do for SQL select lists), but Oracle and (since very recently) DB2 have decided to go with the new ‘=>’ token.
Defaults for parameters
You can use a parameter’s default value, for example
CREATE PROCEDURE p (p1 INT DEFAULT 1, p2 INT DEFAULT 2) …
CALL p (5, DEFAULT);
CALL p (5);
Presumably, if we keep the MySQL non-standard extension that allows CALL without parentheses, this will mean that
CALL p;
is the same as “CALL p(1,2);”.
Spatial changes
For the “Spatial” features, some changes will appear that we can mostly regard as bug fixes.
Leave a Reply