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
