[storm] Oracle support review

[Gustavo Noronha Silva]

On Wed, 2008-09-10 at 13:36 +0800, James Henstridge wrote:
> The Django ORM has the following in its Oracle backend:
> 
>     def quote_name(self, name):
>         # SQL92 requires delimited (quoted) names to be case-sensitive.  When
>         # not quoted, Oracle has case-insensitive behavior for identifiers, but
>         # always defaults to uppercase.
>         # We simplify things by making Oracle identifiers always uppercase.
>         if not name.startswith('"') and not name.endswith('"'):
>             name = '"%s"' % util.truncate_name(name.upper(),
> self.max_name_length())
>         return name.upper()
> 
> So, if you don't quote the name of the "value" column in the CREATE
> TABLE statement for the money table, it will be called VALUE.  Since
> quoted identifiers are compared case sensitively, "value" doesn't
> match "VALUE".  I'd try quoting the column name "value" in the CREATE
> TABLE statement and see if that clears things up.

hmm, I'll try that.

> > The problem is basically that I need to pass the rowid in, and I cannot
> > use the raw_cursor as a bearer, as is done by other backends.
> 
> The code you've got in OracleConnection.raw_execute() looks like
> you're generating a similar to the "INSERT ... RETURNING" statements
> generated by the PostgreSQL backend.  Can you get Oracle to return the
> primary key variables directly, similar to what we do with PostgreSQL?
> 
> Here is a short example of what you can do with PostgreSQL (using a
> direct DB-API call):
> 
>     c.execute("INSERT INTO money (value) VALUES (42) RETURNING money.id")
>     new_id = c.fetchone()[0]
> 
> If that works, it should be pretty easily to implement efficient inserts.

For Oracle that would be:

rowid = c.var(oracle.ROWID)
c.execute("INSERT INTO money (value) VALUES (42) RETURNING ROWID INTO :out_rowid")
newid = rowid.getvalue()

And it's being done like this already, I only had to do more changes to
how the rowid eventually gets to the Result object because cx_Oracle
won't let me add arbitrary attributes to its raw cursor object, as is
done in the pg backend, for instance, and, as you can see, it's not the
cursor who holds the value in the end, but that magical rowid variable.

Thanks!

-- 
Gustavo Noronha Silva <kov at alfaiati.net>
AlfaiaTI