Re: SQL Reserved Words

On 22 Aug 2006, at 13:31, Brett Patterson wrote:

Can you not use database.tablename syntax to work around the
reserved words that are SQL1992 compliant (like uid)? Does Oracle
allow that? Because that would be an easy fix.

Oracle doesn't allow that. You can put the table name in quotes and
it will accept it however, sessions as a table name is actually more
consistent since all the other table names are users, messages, etc.
RoundCube handled that quite nicely anyway since it's just a config
file option.

As for the other SQL reserved words, I agree, RC shouldn't use
them. One option I find, and one I like, is to use a DB prefix
(which is required) so that you can use "reserved" words inside the
column name but not actually have them be reserved.

I have mixed feelings on the column prefix but it is a guaranteed way
to avoid reserved words. Also some minor updates to your suggestion,
Brett.

The way to achieve this is to change the config file (db.inc.php)
and add "{$db_prefix}" before each table name. As for the column
names, I'm sure I suggest: users.lang messages.userID

message.imap_uid (uid referred to the IMAP message id)

messages.author messages.recipient

messages.recipients (could be more than one)

messages.sent messages.Kbsize

messages.byte_size (prevent calculations and stick with the '_'
notation used elsewhere)

Something like that, not suggesting that we use those exact ones.
But to help the devs, here's the list of reserved words:

Good list. Avoiding these will take care of most of the issues
between databases...sadly, still not all!

Cheers,

Ryan

AFTER, ALIAS, ASYNC, BEFORE, BOOLEAN, BREADTH, COMPLETION, CALL,
CYCLE, DATA, DEPTH, DICTIONARY, EACH, ELSEIF, EQUALS, GENERAL, IF,
IGNORE, LEAVE, LESS, LIMIT, LOOP, MODIFY, NEW, NONE, OBJECT, OFF,
OID, OLD, OPERATION, OPERATORS, OTHERS, PARAMETERS, PENDANT,
PREORDER, PRIVATE, PROTECTED, RECURSIVE, REF, REFERENCING, REPLACE,
RESIGNAL, RETURN, RETURNS, ROLE, ROUTINE, ROW, SAVEPOINT, SEARCH,
SENSITIVE, SEQUENCE, SIGNAL, SIMILAR, SQLEXCEPTION, SQLWARNING,
STRUCTURE, TEST, THERE, TRIGGER, TYPE, UNDER, VARIABLE, VIRTUAL,
VISIBLE, WAIT, WHILE, WITHOUT

ABSOLUTE, ACTION, ADD, ALLOCATE, ALTER, ARE, ASSERTION, AT,
BETWEEN, BIT, BIT_LENGTH, BOTH, CASCADE, CASCADED, CASE, CAST,
CATALOG, CHAR_LENGTH, CHARACTER_LENGTH, COALESCE, COLLATE,
COLLATION, COLUMN, CONNECT, CONNECTION, CONSTRAINT, CONSTRAINTS,
CONVERT, CORRESPONDING, CROSS, CURRENT_DATE, CURRENT_TIME,
CURRENT_TIMESTAMP, CURRENT_USER, DATE, DAY, DEALLOCATE, DEFERRABLE,
DEFERRED, DESCRIBE, DESCRIPTOR, DIAGNOSTICS, DISCONNECT, DOMAIN,
DROP, ELSE, END-EXEC, EXCEPT, EXCEPTION, EXECUTE, EXTERNAL,
EXTRACT, FALSE, FIRST, FULL, GET, GLOBAL, HOUR, IDENTITY,
IMMEDIATE, INITIALLY, INNER, INPUT, INSENSITIVE, INTERSECT,
INTERVAL, ISOLATION, JOIN, LAST, LEADING, LEFT, LEVEL, LOCAL,
LOWER, MATCH, MINUTE, MONTH, NAMES, NATIONAL, NATURAL, NCHAR, NEXT,
NO, NULLIF, OCTET_LENGTH, ONLY, OUTER, OUTPUT, OVERLAPS, PAD,
PARTIAL, POSITION, PREPARE, PRESERVE, PRIOR, READ, RELATIVE,
RESTRICT, REVOKE, RIGHT, ROWS, SCROLL, SECOND, SESSION,
SESSION_USER, SIZE, SPACE, SQLSTATE, SUBSTRING, SYSTEM_USER,
TEMPORARY, THEN, TIME, TIMESTAMP, TIMEZONE_HOUR, TIMEZONE_MINUTE,
TRAILING, TRANSACTION, TRANSLATE, TRANSLATION, TRIM, TRUE, UNKNOWN,
UPPER, USAGE, USING, VALUE, VARCHAR, VARYING, WHEN, WRITE, YEAR, ZONE

Those are all reserved in SQL1992 (SQL2) databases; although some
are more lenient in their interpretation. For interoperability
sake, we should stay away from SQL1992 reserved words when we can,
and then we can deal with individual RDBMS issues one by one.

~Brett