SQL Reserved Words

Brett Patterson brett at bpatterson.net
Tue Aug 22 14:31:30 CEST 2006


Ryan Fife wrote:
> Hello,
>
> I'm working on getting RoundCube to work with Oracle and have run into 
> problems with SQL reserved words being used for both table and column 
> names.  Oracle is one of the most strict databases with regard to 
> reserved words.  Here is the list of tables and column names that I've 
> found to cause problems:
>
> Tables:
> session (I changed it to sessions and updated my config file. Easy fix.)
>
> Reserved Field names:
> users.language
> messages.uid (Not SQL-standard reserved but Oracle reserved)
> messages.from
> messages.to
> messages.date
> messages.size
>
> There's also a problem in the identities table with a minus sign in 
> one of the fields:
> identities.reply-to
>
> There is no mechanism to have table and column names mapped in the 
> config file so I'm asking for a recommendation on how to proceed.  I 
> think the ideal solution would be to change all the columns from 
> reserved words to allowed words since only UID is a special Oracle 
> (and Postgres) reserved word and the rest shouldn't really be allowed 
> anywhere.  The other alternative is to quote them everywhere but I'm 
> not sure how portable this is and I'm testing Oracle on it now.  What 
> does everyone suggest?
>
> I've also attached my first attempt at oracle.initial.sql.
>
> Cheers,
>
> Ryan
>
>
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.

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.

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
messages.author
messages.recipient
messages.sent
messages.Kbsize

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

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




More information about the Dev mailing list