[storm] future database support

[Willi Langenberger]

According to Gustavo Niemeyer:
> Are you working in a Bazaar branch?  If so, can you push it up
> to Launchpad?  We (and others) can follow it this way.

I havn't worked with bazaar so far. But i will give it a try soon...

> > * SQL statements without table: oracle needs a table name in the
> >   statement. [...]
> >
> > I'd like to avoid parsing
> > SQL Statements in the backend, only to find out if the "from dual"
> > is needed...
>
> We definitely won't be getting into the SQL parsing business.

Fine ;-)

> In that
> specific case, we should make the tests in DatabaseTest be more friendly
> to Oracle.  I think we can use "SELECT 1 FROM test" in most of them,
> without sacrificing the test goal.

Ok, i will do it that way...

> Besides that, we should also make sure that Storm is able to compile
> things like Select(1) correctly.  That may be done with something
> as simple as the following (in the backend!):
> 
> @compile.when(Select)
> def compile_select_oracle(compile, state, select):
>     if select.default_tables is Undef:
>         select.default_tables = "DUAL"
>     return compile_select(compile, state, select)

Yeah, cool. This is also the solution to my "backend awareness of the
expr module" question!

> > * 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.)
> 
> What Storm wants in this case is a way to identify the row that
> was just inserted, so that it may be mapped back into the in-memory
> object.
> 
> With MySQL, Storm will use the lastrowid attribute of the cursor
> object. With SQLite, it will use the "OID".  Oracle seems to support
> some kind of magic "rowid" attribute as well.  Do you know if there's
> a way to get the rowid for the just inserted row?

This needs some investigation. Oracle's SELECT has a "returning" clause,
which you can use the get the rowid of the inserted record. In PL/SQL
you could say:

  DECLARE
  l_rowid ROWID;
  BEGIN
    ...
    INSERT INTO some_table
      (col1, col2) VALUES ('foo','bar')
    RETURNING ROWID INTO l_rowid; 
    ...
  END;

I think this can also be used with the Oracle Call Interface, ie
something like:

  rowid = cursor.execute("""INSERT INTO some_table
                              (col1, col2) VALUES (:1, :2)
                            RETURNING ROWID INTO :3""", col1, col2)

We'll see. Maybe this can help...

> > * 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.
> 
> This should be easy to fix, but I have no idea about what Oracle
> does in this case.  Can you point me to some documentation and
> examples about that?

If there is a select statement like

  SELECT DISTINCT col1, col2
    FROM some_table
   ORDER BY ...

you can get the LIMIT/OFFSET (pagination) queries as

  SELECT col1, col2
    FROM (SELECT rownum rn, col1, col2
            FROM (SELECT DISTINCT col1, col2
                    FROM some_table
                   ORDER BY ...))
   WHERE rn > :1    -- offset
     AND rn <= :2   -- (limit - offset)

> > And: how can we make the "expr" module backend aware?
>
> The expr module is fully backend aware.  You can see expressions being
> customized at will in other backends.

I've completely overlooked that. But yes, its easy to implement oracle
specific expr stuff.

Thank you very much for your comments!


\wlang{}

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