Storm should support Oracle databases

[Gustavo Niemeyer]

That's it. Storm should support Oracle databases.

[Gustavo Niemeyer (niemeyer)]

Willi Langenberger is working on a branch to implement that support (linked
to this bug), and has just requested comments about it in the mailing list:

[Gustavo Niemeyer (niemeyer)]

Willi, thanks for your work on the module. Here are some
intial comments about the current implementation:

[1]

  if select.offset is not Undef or select.limit is not Undef:
      pagination = True
      limit = select.limit or sys.maxint
      select.limit = Undef
      offset = select.offset or 0
      select.offset = Undef
      maxrn = offset + limit

It'd be good to create another select statement rather than modifying
the original one. Otherwise executing the same statement twice will
bring different results.

[2]

  # XXX
  # temporary hack to remove the table part of the
  # column specifiction: 'tab.col1, tab.col2' => 'col1, col2'
  # i will fix it when i have a better understanding
  # of the expression compilation process

Can you describe in which cases Oracle doesn't like the table
prefix? E.g. how does it handle the case where there are two
tables being selected from, and there are columns from both
tables in the columns specification?

I can try to explain to you how the compilation system might
help if I understand the problem better.

[3]

  # XXX: this is needed for alias tables as in "test_class_alias"
  # and is copied from info.py (with the "AS" removed)
  # wouldnt it be better to reference to compile_oracle_alias from there?

Yeah, it'd be nice to make it more flexible rather than requiring
copying the whole logic. Don't worry about it for now..

[4]

   def _from_database(self, params):
       for param in params:
       (...)
       elif isinstance(param, Variable):
           yield param.get(to_db=True)

The param may never be a Variable in this case, since the value
is coming from the database.

[5]

  _param_mark = ":1"

Is that the only mark kind accepted by the DBAPI module?

[6]

  raise DatabaseModuleError("DCORacle2' module not found")

Minor typo in the message.

[7]

  dsn = "%s/%s@%s" % (uri.username, uri.password, uri.host)

Doesn't Oracle require a database name?

[8]

- yield result
+ yield tuple(self._from_database(result))

Good catch! Thanks!

[Gustavo Noronha Silva (kov-alfaiati)]

Our company is on a project that requires using Plone with Oracle, and we had this idea of adding an Archetypes store that uses Storm, and write Oracle support for Storm. I was dumb enough to not look at the bug reports before starting, so I have mostly duplicated the effort. My branch is noted on the bug report; it passes all the tests that were available before my last merge from trunk; now there are two new tests that are not passing. Things look good overall:

-------------------------------------
Ran 400 tests in 1064.039s

FAILED (errors=2)
Running unittests...
[tests/store/oracle.py]
(tests=400, failures=0, errors=2)

Total test cases: 400
Total doctests: 0
Total failures: 0
Total errors: 2
-------------------------------------

There are some nasty hacks to overcome problems caused apparentely by cx_Oracle, so I am going to try DCOracle2 and see what happens. Perhaps some merging between both patches is in order? I find my code to be much more verbose than the original submitter's, but I didn't try to run the tests on his code yet.

[Gustavo Noronha Silva (kov-alfaiati)]

I've updated my branch with everything I found on the Willi Langenberger's branch and that was simpler/better written than in mine. I decided to stick to cx_Oracle based on my conversation with Gustavo Niemeyer, in which he told me that upstream support is something to be considered. cx_Oracle is still being maintained as of this year, while DCOracle2 seems to be dead since 2002.