future database support

[Willi Langenberger]

According to Gustavo Niemeyer:
> > Any plans to support Oracle, and SQL Server or you will storm be
> > focused on open source databases?
> 
> Someone said he'd be working on it, during EuroPython, but I haven't
> heard about it since then.

That was me ;-)

> In any case, we're certainly keen on supporting Oracle and others,
> and adding support for them should be very simple.  I can even do it
> by myself, if there's no one else interested on the task.  We'll just
> have to find an environment for testing it.

The backend itself was no problem. Getting the tests to work, is
harder ;-)

Anyway, these are the problems so far:

* AUTO INCREMENT (mysql), SERIAL (postgres) columns

  Oracle doesn't have these column types.

  Solved with oracle sequences and "after insert" triggers.

* Binary inserts: oracle has to know, if a value is inserted in a
  binary (raw) column. The following won't work if the column "bin" is
  a blob:

    conn.execute("INSERT INTO bin VALUES (10, 'Blob 30')")

  Instead, it should be:

    conn.execute("INSERT INTO bin VALUES (10, ?)", CharsVariable('Blob 30'))

  Now the backend knows that it is a "raw" value and can make the
  right variable binding. But it also means, that some tests must be
  changed.

  (Side note: the name "Chars" for raw data confused me a lot!)

* SQL statements without table: oracle needs a table name in the
  statement. This won't work:

    def test_execute(self):
        result = self.store.execute("SELECT 1")

  Here the pseude table "DUAL" is needed:

    => "SELECT 1 FROM DUAL"

  How can we solve this in a generic way? I'd like to avoid parsing
  SQL Statements in the backend, only to find out if the "from dual"
  is needed...

* Insert Identity: i dont know, how to find the last inserted record
  in oracle. Any ideas?

  (Currently i take the record with max(id), but thats only a
  workaround.)

* Pagination: the "expr" module uses the LIMIT and OFFSET tokens, but
  oracle doesnt support these.

  This could be solved with the oracle pseudocolumn "ROWNUM" and
  subselects. But i havnt done it by now. And: how can we make the
  "expr" module backend aware?


\wlang{}

-- 
Willi.Langenberger at wu-wien.ac.at                Fax: +43/1/31336/9207
Zentrum fuer Informatikdienste, Wirtschaftsuniversitaet Wien, Austria