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
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
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, WITHOUTABSOLUTE, 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, ZONEThose 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