[ax: cumulating three separate "oracle reserved word" bug reports into one. if there should be more oracle reserved words, please add them *here*]

'access' is a reserved work in Oracle. The 'access' table cannot be created. Implementing an Oracle database requires that this tablename be changed.

Also, the term 'uid' is reserved in Oracle. This term cannot be used as a column name in the Oracle enviromment. Implementing an Oracle database for Drupal requires that the column name be changed in the following tables:

accesslog
authmap
comments
history
node
sessions
users
watchdog

Additionally, 'size' is a reserved word in Oracle. The word is used as a column name in the table site. Implementing an Oracle database will require the column name to be changed.

[cumulating END]

to fix the "sql reserved word" issue once and forever and for all db systems, i'd suggest to prefix drupal table and field names. i'd prefix table names with 'dp_', eg. 'dp_access', 'dp_accesslog' and field names, as mentioned at [3], with a 2 or 3 character contraction of the (unprefixed) table name, e.g. 'acs_id', 'acs_mask' etc. for the 'access' table.

beside solving the reserved word issue, such a naming scheme has the advantage "that in multi-table queries involving complex joins, you don't have to worry about ambiguous column names, and don't have to use table aliases to prefix the columns. It also makes your queries more readable."

References:

[1] Extending Database Abstraction Layer <http://drupal.org/node/view/4882> - rinehart's post in the module development forum regarding extending the database abstraction layer for Oracle support
[2] Oracle Reserved Words <http://google.com/search?q=Oracle+Reserved+Words>
[3] Bug: Mysql --ansi cannot import install database; background, rationales, and suggestions for database naming schemes <http://drupal.org/node/view/893#comment-5>

CommentFileSizeAuthor
#2 database.oracle8i29.91 KBrinehart
#1 database.zip9.2 KBrinehart
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

rinehart’s picture

FileSize
9.2 KB

Contributing updated schemas with suggested revisions.

Attachement:

database.zip includes:
[1] database.pgsql
[2] database.mysql
[3] database.mssql

Submissions are not tested.

rinehart’s picture

FileSize
29.91 KB

New Oracle database build script is attached. This script is tested in Oracle 8.1.7. This script should work for Oracle8i or later, and probably works on Oracle 7.x and Oracle 8. I've been careful to ensure that all table and field names are consistent across all build scripts, but encourage review.

Usage:
[1] Define new tablespace called 'drupal' in target database
[2] Modify database.oracle8i file. Near the bottom of the file, immediately before inserting default values the script reconnects to your database using the drupal_usr account (created in the script). Alter that statement and supply the correct service name for your database, then save change.
[2] Launch SQL*Plus and connect to target database using an admin account
[3] Execute Script using command @
/database.oracle8i

[4] Provided that your account has sufficient rights script will create new schema, all schema objects, and then insert default values.

Notes:
[1] Primary Key definitions in this script match those in database.pgsql script.
[2] Where the 'autoincrement' feature was employed the Oracle script includes a sequence and on insert trigger that automatically populates the PK column with a unique number.
[3] The history table has a composit primary key - the key must be provided on insert.
[4] All primary keys with character type must be supplied on insert.
[5] The script defines the greater function as part of a 'drupal_services' package. A public synonym called 'greater' is defined to ensure consistency across platforms when envoking the greater function.
[6] To simplify database abstraction and ensure a robust solution all clobs are implicitly initialized with the default value of EMPTY_CLOB(). That ensures that if no data is supplied for the CLOB field on insert, it will be initialized automatically and ready to accept updates with no special consideration.

Dries’s picture

The site table is no longer part of Drupal HEAD (development version). There is no way Oracle support will be added (or backported) to the stable 4.3 branch as the proposed changes are too intrusive: they affect nearly all core and all contributed modules. I'm up'ing the version of this issue to 'cvs'. Only patches against the latest development version of Drupal will be accepted.

Clearly, one of the key issues is having to rename the users' table uid column. I'd like to hear what people think of that and - when positive - what replacement name they suggest. Is it worth it?

moshe weitzman’s picture

I've never used Oracle. I do know though that MSSQL lets you use field names which are reserved words. You have to reference these words with brackets around them like so: SELECT [uid] FROM users. The PEAR library does this automatically for us. I would be surprised if Oracle doesn't hve a similar capability.

Note that you cannot manage table name conflicts this way, only field names. Fortunately, DB prefixing is an easy way to avoid table name conflicts.

rinehart’s picture

Responding to the possible use of reserved words in Oracle SQL - there is no capability similar to the use of brackets in MSSQL for Oracle reserved words. In Oracle if a word is reserved it's not available.

Dries’s picture

I checked and there are almost 2.000 occurences/uses of uid. Initially we could try to instruct the Oracle backend (i.e. _db_query() in includes/database.oracle.inc) to replace instances of uid dynamically instead of changing all the code. This should be possible with a regex.

moshe weitzman’s picture

ax’s picture

hmmm - so you think we shouldn't make drupal oracle compatible just because a string has to be renamed 2000 times? there have been bigger changes, without so much hesitation.

i'd vote for doing this change. or, even better, get rid of sql reserved words once and for all. at least, i wouldn't mark this WONTFIX, but POSTPONED.

or even better, PATCH - anyone? ;)

moshe weitzman’s picture

Anonymous’s picture

I've been contemplating the regex idea, and don't recommend it. For simple SQL statements I think it could work. To do so one would have to write a regular expression that searches for the "replace_what" expression positionally relative to keywords in the statement. Example:

In a Select statement the regular expression would have to replace for the word ".uid", or " uid ", or ",uid ", or " uid," that appears after a "SELECT" and before a "FROM", or after a "WHERE" (somehow excluding contents of a literal string expression), or after an "ORDER BY", or after a "GROUP BY", or after a "HAVING". Even if you work around the where clause issue, you're still left with the possibility that someone will implement a nested select statement and potentially break the regex. I'm guessing that doesn't happen very often, but given the amount of SQL in the project it seems there will be the occasional scenario that a regular expression can't handle.

I suggest that it's better to update the schema as part of a major release and deal with these problems then.

Cheers,

Mac

beginner’s picture

Status: Postponed » Active

Is there still demand for Oracle support?

killes@www.drop.org’s picture

Status: Active » Closed (duplicate)

There is more recent info on this here:

http://drupal.org/node/39260