SQL Reserved Words

Ryan Fife ryan.fife at fifeventures.co.uk
Tue Aug 22 18:08:42 CEST 2006


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
>
>





More information about the Dev mailing list