Add oracle driver

I think we should have some cooperation. I did some work in http://siren.sourceforge.net/, may I have some of your comment?

The reason of parallel release for siren is the difficulty of integrate research progress into main stream, based on political but not technical concern. I give a summarize to most contributed issue for Oracle in http://groups.drupal.org/node/8663, part of them still need some love. Because I am not just targeting on Oracle but also DB2 and MSSQL support, those changes are the common factors of universal database abstraction but not over-engineering :S

Using CLOB may not be a suitable choice since PDO_OCI is buggy with that. As D7 force database support with PDO, the only suitable choice is implement those required field as BLOB. On the other hand, BLOB is more common among different databases, where CLOB is just available for some database engine with a lot of limitation.

For the reserved word issue, we are not able to handle uid/session/file/etc only. Take Views/CCK as example, they will generate tables on-the-fly, based on user input values. This may cause (http://drupal.org/node/371#comment-636053): 1. the conflict of reserved word, 2. didn't solve the auto upper case of constraint within Oracle engine, and 3. hit the maximum 30 characters limitation of constraint name in Oracle/DB2. Avoid the use of reserved word can only handle the conflict within core, but introduce a lot of hidden technical requirement to normal developer and end users, who are not DB expert (especially not Oracle expoert).

Target for stable release can't merge Oracle support into main stream, since CVS HEAD is keep on changing. We will never refine the stable release with additional feature add. If we hope to contribute for main stream, we need people to support and spend some time to work together with the community. This is the tradition of Drupal community, which I had discovered within this 2 years contribution.

P.S. The CVS HEAD of Siren is sync with D7 and I am now preparing it for main stream contribution, e.g. testing in Siren and generate patch for D7. I would like to invert you to join the research and give more indeed discussion for that.

why is this a critical bug for D6???

just create a new project if you care about oracle support in D6. http://drupal.org/node/7765
This will not be included in D6, as this is a new feature and such as will not be committed into stable version..

subscribe

For D7, an Oracle driver should be possible via contrib with no core modifications. If not, then we need someone to be trying to write a real D7 contrib driver so that we can figure out where it breaks and if we can do something about it in core, incrementally.

So far all efforts in that direction have been sledgehammer approaches that have been rejected with good reason. That's sad, as Oracle support would be cool. I wish someone would try to write one properly.

I've been working with the Oracle extension on an Oracle XE/drupal 6.4 installation. Seems to work reasonably well, but I have noticed one important bug related to your work around for CLOBs (using varchar2(4000) for larger text data). Entering a text string >4000 into a text field or text area (e.g., the body of a node) throws an insert error and appears to throw off version bookkeeping in Drupal. This seems to leave the node inaccessible.

To prevent users from entering >4000 characters, I wrote a hook_alter_form module to validate text areas...

/**
 * implementation of hook_form_alter
 */
function dcre_form_alter(&$form, $form_state, $form_id) {
  _dcre_set_max_text_length($form);
}

/*
 * recursively search form for textfield/text area and impose a 4000 character max length on the field
 */
function _dcre_set_max_text_length(&$form) {
  foreach ($form as $k => &$v) {
    if (is_array($v)) {
      if (isset($v['#type'])) {
        if ($v['#type'] == 'textfield') {
          if (!isset($v['#maxlength']) || $v['#maxlength'] > 4000) {
            $form[$k]['#maxlength'] = 4000;
            $form[$k]['#element_validate'][] = '_dcre_max_textarea_validate';
            $form[$k]['#description'] .= '--4000 character limit.';
          }
        }
        if ($v['#type'] == 'textarea') {
          if (!isset($form[$k]['#validate'])) {
            $form[$k]['#element_validate'] = array();
          }
          $form[$k]['#element_validate'][] = '_dcre_max_textarea_validate';
          $form[$k]['#description'] .= '--4000 character limit.';
        }
      }
      _dcre_set_max_text_length($v); // recurse
    }
  }
}

/*
 * validates that a field contains <= 4000 characters
 */
function _dcre_max_textarea_validate($element, &$form_state) {
  $n = strlen($element['#value']);
  if ($n > 4000) {
    form_error($element, t('Field contains %n characters (exceeds 4000 character limit).', array('%n' => $n)));
  }
}

That's a known issue with Oracle not being able to handle large text. hswong3i has been pushing to therefore convert all TEXT fields in Drupal into BLOB fields, but that's been rejected as we do not want to cripple other databases just because of Oracle limitations. The recommendation, which no one has tried yet AFAIK, is to alter the mapping in the Oracle driver to map larger text fields to BLOB on Oracle specifically. (There's no requirement that the same native field type be used on every database.)

Can you explain why you change all empty strings ('') to '^', including default values on create tables. Thanks.

@Crell: I have try your suggested mapping once before but not success. This is not a technical (e.g. Oracle functionality, PDO driver support, Drupal driver implementation, etc) but usability problem.

For solving the Oracle maximum 4000 VARCHAR2 limitation, we have 3 solution:

1. Map all Drupal's text type as VARCHAR2(4000) for maximum storage size with all SQL string functionality. This is not a good idea because we always have content more than 4000 characters, e.g. node body.
2. Map all Drupal's text type as CLOB/BLOB for Oracle driver only. This is not work because we assume text type with identical SQL string functionality as varchar in case of MySQL/PostgreSQL; BTW in case of Oracle both ORDER BY/GROUP BY/LIKE/etc are not support for CLOB and BLOB. This mapping will break most existing core code logic and implementation.
3. Map all text type as VARCHAR2(4000) for SQL string functionality, only change required field as BLOB for maximum storage size support (#147947: [DBTNG + XDB] Replace some TEXT:BIG with BLOB), and even revamp programming logic for compare if we do required for (#313999: [DBTNG + BLOB + hash]: Revamp locales_source.source with BLOB and MD5 hashing). With this solution impact for MySQL/PostgreSQL can be minimized, can keep most existing programming logic, and also maximal the support for Oracle.

If we do have some other suggestion/solution, please feel free to discuss for :D

Feature requests are for 7.x only. Marking as 'to be ported' since this needs to be ported to a DBTNG driver.

We are not adding an Oracle driver to core. See you in contrib. If you find something during the Oracle driver development that needs DBTNG assistance please file a patch.