Merge lp:~jbaker/storm/oracle_support into lp:storm

[Jason Baker]

[Jason Baker (jbaker)]

This should be ready for merge. I've commented out the disconnection tests for now. Some parts of this are still somewhat ugly, but it's a good starting point.

[Jamu Kakar (jkakar)]

[1]

I've managed to get Oracle XE installed, but I'm having trouble
getting cx_Oracle in place, so I can't run the test suite yet. I'm
going to keep at it, but if you have any hints on how to get
cx_Oracle in place (on a 64-bit machine) I'd appreciate some help.
Ideally, packages for Ubuntu would be nice.

[2]

I've pushed a branch that adds some details about getting Oracle XE
installed on a 64-bit machine to the README file and makes some
cosmetic changes to make the code match the Storm coding style more
closely. Please merge:

lp:~jkakar/storm/oracle-support-tweaks

[3]

Why isn't the builtin reserved words functionality being used?

I've made a broad pass over the changeset and it looks really good.
I'd like to get the test suite running before I do a more detailed
pass.

[Jason Baker (jbaker)]

> [1]
>
> I've managed to get Oracle XE installed, but I'm having trouble
> getting cx_Oracle in place, so I can't run the test suite yet. I'm
> going to keep at it, but if you have any hints on how to get
> cx_Oracle in place (on a 64-bit machine) I'd appreciate some help.
> Ideally, packages for Ubuntu would be nice.

I found this blog post to be a good help: http://catherinedevlin.blogspot.com/2007/10/cxoracle-and-oracle-xe-on-ubuntu.html ... Although note that they're a little bit outdated. I don't believe it's necessary to install the XE patch.

If you can give me a specific error message, I might be able to help out.

> [2]
>
> I've pushed a branch that adds some details about getting Oracle XE
> installed on a 64-bit machine to the README file and makes some
> cosmetic changes to make the code match the Storm coding style more
> closely. Please merge:
>
> lp:~jkakar/storm/oracle-support-tweaks

I'll look at them. Thanks!

> [3]
>
> Why isn't the builtin reserved words functionality being used?

I don't recall the *exact* reason why I did that, but there was some issue with the reserved word keylist considering words to be reserved that weren't. Perhaps a better solution would be to update the reserved word list. It's a bit dated (SQL 1992).

[Jason Baker (jbaker)]

> lp:~jkakar/storm/oracle-support-tweaks

Erm.... This branch doesn't seem to exist. Unless of course I'm doing something wrong (which is possible).

[Jamu Kakar (jkakar)]

Oops, the branch was private by mistake. It should be visible now.

[lp:~jbaker/storm/oracle_support]

357. By Jason Baker on 2010-01-25

Merging in changes from trunk.

358. By Jason Baker on 2010-01-25

Merging some changes in from lp:~jkakar/storm/oracle-support-tweaks

359. By Jason Baker on 2010-03-15

Updating from trunk.

360. By Jason Baker on 2010-03-15

Making the oracle backend properly handle disconnection errors

[Jason Baker (jbaker)]

Where do we stand on this? I've just merged in the latest revisions from trunk and also added a bugfix that makes the disconnection tests now pass.

[Jamu Kakar (jkakar)]

I had spent some time looking at this a while ago. I got as far as
getting Oracle installed (which was hard on 64-bit) and then stopped
when I had to figure out how to compile cxOracle DB-API drivers. I
looked over the code, and it looks good, but want to actually be
able to run the test suite.

I've since installed a new disk in my machine, so I'll need to setup
Oracle et al. again to continue. I've been thinking that we really
need a buildbot, since getting Oracle in place is rather awkward and
most Storm developers probably won't want to do it.

[Jamu Kakar (jkakar)]

I haven't managed to get Oracle installed. I'm going to abstain
from reviewing this for now.

review: Abstain

Unmerged revisions

360. By Jason Baker on 2010-03-15

Making the oracle backend properly handle disconnection errors

359. By Jason Baker on 2010-03-15

Updating from trunk.

358. By Jason Baker on 2010-01-25

Merging some changes in from lp:~jkakar/storm/oracle-support-tweaks

357. By Jason Baker on 2010-01-25

Merging in changes from trunk.

356. By Jason Baker <jason@ubuntu> on 2009-11-30

Added instructions for getting started with Oracle.

355. By Jason Baker <jason@ubuntu> on 2009-11-30

Adding in a script to set up a test user in an oracle database.

354. By Jason Baker <jason@ubuntu> on 2009-11-30

Reverting the error handling back to the way it was for now. For whatever reason, this led to spurious error reports when running tests.

353. By Jason Baker <jason@ubuntu> on 2009-11-30

Temporarily commenting out the Disconnection tests until they can be fixed.

352. By Jason Baker <jason@ubuntu> on 2009-11-13

Adding an error description for cx_Oracle not being found

351. By Jason Baker <jason@ubuntu> on 2009-11-10

Merge from trunk.

=== modified file 'README'
--- README	2009-10-08 07:23:43 +0000
+++ README	2010-03-15 15:48:27 +0000
@@ -95,8 +95,8 @@
 same procedure will probably work without changes on a Debian system
 and with minimal changes on a non-Debian-based linux distribution.
 In order to run the test suite, and exercise all supported backends,
-you will need to install MySQL and PostgreSQL, along with the
-related Python database drivers:
+you will need to install MySQL, PostgreSQL and Oracle XE, along with
+the related Python database drivers:
 
  $ sudo apt-get install python-mysqldb python-psycopg2 mysql-server \
                         postgresql build-essential
@@ -110,6 +110,34 @@
 asked to enter a password multiple times.  Leave it blank in each
 case.
 
+To install Oracle on a 32-bit system you must first add the
+following line to your /etc/apt/sources.list:
+
+ deb http://oss.oracle.com/debian unstable main non-free
+
+You can then install Oracle XE by running the following commands:
+
+ $ sudo wget http://oss.oracle.com/el4/RPM-GPG-KEY-oracle -O- | sudo apt-key add -
+ $ sudo apt-get update
+ $ sudo apt-get install oracle-xe
+
+Binaries for Oracle XE on 64-bit systems are not available, so
+you'll need to install the 32-bit versions.  Start by downloading
+the packages:
+
+ $ wget -c http://oss.oracle.com/debian/dists/unstable/main/binary-i386/libaio_0.3.104-1_i386.deb http://oss.oracle.com/debian/dists/unstable/non-free/binary-i386/oracle-xe-universal_10.2.0.1-1.1_i386.deb
+
+Once you have the packages you need to install them manually:
+
+ $ sudo apt-get install libc6-i386 bc
+ $ sudo dpkg -i --force-architecture libaio_0.3.104-1_i386.deb
+ $ sudo dpkg -i --force-architecture oracle-xe-universal_10.2.0.1-1.1_i386.deb
+
+Finally, when the packages are installed, you can then run the
+following line to configure the database:
+
+ $ sudo /etc/init.d/oracle-xe configure
+
 Setting up database users and access security
 ---------------------------------------------
 
@@ -145,6 +173,14 @@
 mysql> GRANT ALL PRIVILEGES ON *.* TO 'YOUR_USERNAME'@'localhost'
 IDENTIFIED BY '' WITH GRANT OPTION;
 
+For Oracle, we've included a script to set up a test user.  To run it,
+do the following:
+
+ $ sqlplus sys/<sys_password>@localhost:1521
+ SQL> @oracle_test_setup
+
+And follow the prompts to create a user.
+
 Creating test databases
 -----------------------
 

=== added file 'oracle_test_setup.sql'
--- oracle_test_setup.sql	1970-01-01 00:00:00 +0000
+++ oracle_test_setup.sql	2010-03-15 15:48:27 +0000
@@ -0,0 +1,13 @@
+-- Note that this is a script that is intended to be run to set up a
+-- test database.  You probably don't want to run this on a production
+-- database!
+
+CREATE USER &&user IDENTIFIED BY &&password;
+GRANT CONNECT TO &&user;
+GRANT CREATE ANY TABLE TO &&user;
+GRANT CREATE ANY TRIGGER TO &&user;
+GRANT CREATE ANY SEQUENCE TO &&user;
+GRANT DROP ANY SEQUENCE TO &&user;
+GRANT DROP ANY TABLE TO &&user;
+GRANT DROP ANY TRIGGER TO &&user;
+GRANT UNLIMITED TABLESPACE TO &&user;
\ No newline at end of file

=== added file 'storm/databases/oracle.py'
--- storm/databases/oracle.py	1970-01-01 00:00:00 +0000
+++ storm/databases/oracle.py	2010-03-15 15:48:27 +0000
@@ -0,0 +1,590 @@
+# Copyright (c) 2008 Alfaiati
+#
+# Written by Gustavo Noronha <[e-mail]>
+#            Willi Langenberger <[e-mail]>
+#
+# This file is part of Storm Object Relational Mapper.
+#
+# Storm is free software; you can redistribute it and/or modify
+# it under the terms of the GNU Lesser General Public License as
+# published by the Free Software Foundation; either version 2.1 of
+# the License, or (at your option) any later version.
+#
+# Storm is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU Lesser General Public License for more details.
+#
+# You should have received a copy of the GNU Lesser General Public License
+# along with this program.  If not, see <http://www.gnu.org/licenses/>.
+
+import os
+import sys
+from decimal import Decimal
+
+from storm.databases import dummy
+
+# If no NLS_LANG is set, we'll use english with UTF-8.  Note that this
+# variable *must* be set before importing cx_Oracle.
+if not os.environ.get("NLS_LANG", None):
+    os.environ["NLS_LANG"] = 'american_america.utf8'
+try:
+    import cx_Oracle as oracle
+except Exception, e:
+    oracle = dummy
+
+from storm.tracer import trace
+from storm.variables import Variable, RawStrVariable, PickleVariable
+from storm.database import Database, Connection, Result
+from storm.exceptions import (
+    install_exceptions, ClosedError, DatabaseModuleError, DatabaseError,
+    OperationalError)
+from storm.info import get_cls_info, ClassAlias
+from storm.expr import (
+    Undef, SetExpr, Select, Insert, Alias, And, Eq, FuncExpr, SQLRaw,
+    Le, Gt, Column, is_safe_token, Except, Expr, Sequence, SQLToken,
+    COLUMN, COLUMN_NAME, COLUMN_PREFIX, TABLE, compile, compile_eq,
+    compile_select, compile_insert, compile_set_expr, compile_sql_token,
+    State)
+
+
+install_exceptions(oracle)
+compile = compile.create_child()
+
+
+RESERVED = set(
+"""ACCESS ADD ALL ALTER AND ANY AS ASC AUDIT BETWEEN BY CHAR
+   CHECK CLUSTER COLUMN COMMENT COMPRESS CONNECT CREATE CURRENT
+   DATE DECIMAL DEFAULT DELETE DESC DISTINCT DROP ELSE EXCLUSIVE
+   EXISTS FILE FLOAT FOR FROM GRANT GROUP HAVING IDENTIFIED
+   IMMEDIATE IN INCREMENT INDEX INITIAL INSERT INTEGER INTERSECT
+   INTO IS LEVEL LIKE LOCK LONG MAXEXTENTS MINUS MLSLABEL MODE
+   MODIFY NOAUDIT NOCOMPRESS NOT NOWAIT NULL NUMBER OF OFFLINE
+   ON ONLINE OPTION OR ORDER PCTFREE PRIOR PRIVILEGES PUBLIC
+   RAW RENAME RESOURCE REVOKE ROW ROWS SELECT SESSION
+   SET SHARE SIZE SMALLINT START SUCCESSFUL SYNONYM SYSDATE
+   TABLE THEN TO TRIGGER UID UNION UNIQUE UPDATE USER VALIDATE
+   VALUES VARCHAR VARCHAR2 VIEW WHENEVER WHERE WITH""".split())
+
+
+def alias_names():
+    ct = 0
+    while 1:
+        yield "_%x" % ct
+        ct += 1
+
+
[e-mail](type)
+def compile_type(compile, expr, state):
+    cls_info = get_cls_info(expr)
+    table = compile(cls_info.table, state)
+    if state.context is TABLE and issubclass(expr, ClassAlias):
+        return "%s %s" % (compile(cls_info.cls, state), table)
+    return table
+
+
[e-mail](Alias)
+def compile_alias(compile, alias, state):
+    name = compile(alias.name, state, token=True)
+    if state.context is COLUMN or state.context is TABLE:
+        return "%s %s" % (compile(alias.expr, state), name)
+    return name
+
+
[e-mail](Sequence)
+def compile_sequence_oracle(compile, sequence, state):
+    return "%s.NEXTVAL" % sequence.name
+
+
+class Minus(SetExpr):
+    oper = " MINUS "
+
+
[e-mail](Except)
+def compile_except_oracle(compile, expr, state):
+    new_expr = Minus()
+    new_expr.exprs = expr.exprs
+    new_expr.all = expr.all
+    new_expr.order_by = expr.order_by
+    new_expr.limit = expr.limit
+    new_expr.offset = expr.offset
+    return compile_set_expr_oracle(compile, new_expr, state)
+
+
[e-mail](SetExpr)
+def compile_set_expr_oracle(compile, expr, state):
+    names = alias_names()
+    if isinstance(expr, Minus):
+        # Build new set expression without arguments (order_by, etc).
+        new_expr = expr.__class__()
+        new_expr.exprs = expr.exprs
+        new_expr.all = expr.all
+
+        if expr.order_by is not Undef:
+            # Make sure that state.aliases isn't None, since we want them to
+            # compile our order_by statement below.
+            no_aliases = state.aliases is None
+            if no_aliases:
+                state.push("aliases", {})
+
+            aliases = {}
+            for subexpr in expr.exprs:
+                if isinstance(subexpr, Select):
+                    columns = subexpr.columns
+                    if not isinstance(columns, (tuple, list)):
+                        columns = [columns]
+                    else:
+                        columns = list(columns)
+                    for i, column in enumerate(columns):
+                        if column not in aliases:
+                            if isinstance(column, Column):
+                                aliases[column] = columns[i] = Alias(
+                                    column, name=names.next())
+                            elif isinstance(column, Alias):
+                                aliases[column.expr] = column
+                    subexpr.columns = columns
+            aliases.update(state.aliases)
+            state.aliases = aliases
+            aliases = None
+
+        set_statement = SQLRaw("(%s)" % compile(expr.exprs, state,
+                                                join=expr.oper))
+
+        if expr.order_by is not Undef:
+            # Build order_by statement, using aliases.
+            state.push("context", COLUMN_NAME)
+            order_by_statement = SQLRaw(compile(expr.order_by, state))
+            state.pop()
+        else:
+            order_by_statement = Undef
+
+        # Build wrapping select statement.
+        select = Select(
+            SQLRaw("*"), tables=Alias(set_statement, name=names.next()),
+            limit=expr.limit, offset=expr.offset, order_by=order_by_statement)
+        return compile_select(compile, select, state)
+    return compile_set_expr(compile, expr, state)
+
+
[e-mail](Select)
+def compile_select_oracle(compile, select, state):
+    limit = select.limit
+    offset = select.offset
+    # Make sure limit is Undef'ed.
+    select.offset = select.limit = Undef
+
+    names = alias_names()
+
+    if select.default_tables is Undef:
+        select.default_tables = ["DUAL"]
+
+    if select.order_by is not Undef:
+        # Copied from expr.py's compile_set_expr.
+        aliases = {}
+        columns = select.columns
+        if not isinstance(columns, (tuple, list)):
+            columns = [columns]
+        else:
+            columns = list(columns)
+        for i, column in enumerate(columns):
+            if column not in aliases:
+                if isinstance(column, Column):
+                    aliases[column] = columns[i] = Alias(
+                        column, name=names.next())
+                elif isinstance(column, Alias):
+                    aliases[column.expr] = column
+        select.columns = columns
+        # Copied from expr.py's compile_set_expr.
+        statement = SQLRaw("(%s)" % compile_select(compile, select, state))
+        select = Select(SQLRaw("*"), tables=Alias(statement,
+                                                  name=names.next()))
+
+    if (limit is not Undef) and (offset is not Undef):
+        rownum_alias = Alias(SQLRaw("ROWNUM"), name=names.next())
+
+        # If we have an SQLRaw here that is because we are dealing with a
+        # subquery.
+        if isinstance(select.columns, SQLRaw):
+            select.columns = [SQLRaw('"' + select.tables.name + '".*'),
+                              rownum_alias]
+        else:
+            select.columns.append(rownum_alias)
+
+        where_expr = Le(SQLRaw("ROWNUM"), limit + offset)
+        if select.where is Undef:
+            select.where = where_expr
+        else:
+            select.where = And(select.where, where_expr)
+
+        statement = SQLRaw("(%s)" % compile_select(compile, select, state))
+        select = Select(SQLRaw("*"), tables=Alias(statement, names.next()),
+                        where=Gt(rownum_alias, offset))
+    elif limit is not Undef:
+        expr = Le(SQLRaw("ROWNUM"), limit)
+        if select.where is Undef:
+            select.where = expr
+        else:
+            select.where = And(select.where, expr)
+    elif offset is not Undef:
+        rownum_alias = Alias(SQLRaw("ROWNUM"), name=names.next())
+
+        # If we have an SQLRaw here that is because we are dealing with a
+        # subquery.
+        if isinstance(select.columns, SQLRaw):
+            select.columns = [SQLRaw('"' + select.tables.name + '".*'),
+                              rownum_alias]
+        else:
+            select.columns.append(rownum_alias)
+
+        statement = SQLRaw("(%s)" % compile_select(compile, select, state))
+        select = Select(SQLRaw("*"), tables=Alias(statement,
+                                                  name=names.next()),
+                        where=Gt(rownum_alias, offset))
+
+    return compile_select(compile, select, state)
+
+
[e-mail](Insert)
+def compile_insert_oracle(compile, insert, state):
+    # Shamelessly copied from PostgreSQL.
+    if not insert.map and insert.primary_columns is not Undef:
+        insert.map.update(dict.fromkeys(insert.primary_columns,
+                                        SQLRaw("DEFAULT")))
+    return compile_insert(compile, insert, state)
+
+
[e-mail](Sequence)
+def compile_sequence_oracle(compile, sequence, state):
+    return "%s.nextval" % sequence.name
+
+
[e-mail](bool)
+def compile_bool(compile, expr, state):
+    return compile_eq(compile, Eq(1, int(expr)), state)
+
+
+class currval(FuncExpr):
+
+    name = "currval"
+
+    def __init__(self, column):
+        self.column = column
+
[e-mail](currval)
+def compile_currval(compile, expr, state):
+    """Compile a L{currval}."""
+    state.push("context", COLUMN_PREFIX)
+    table = compile(expr.column.table, state, token=True)
+    state.pop()
+    return "%s_seq.currval" % (table)
+
+
+class Rowid(Expr):
+
+    def __init__(self, rowid):
+        self.rowid = rowid
+
[e-mail](Rowid)
+def compile_rowid(compile, expr, state):
+    state.parameters.append(expr.rowid)
+    return "?"
+
+
[e-mail](SQLToken)
+def compile_oracle_sql_token(compile, expr, state):
+    if "." in expr and state.context in (TABLE, COLUMN_PREFIX):
+        return ".".join(compile_sql_token(compile, subexpr, state)
+                        for subexpr in expr.split("."))
+    # A bit of a hack: we're skipping the built-in reserved word list and
+    # using our own set.
+    if is_safe_token(expr) and not str(expr) in RESERVED:
+        return expr
+    elif (state.context in (COLUMN, COLUMN_NAME)
+          and str(expr).lower() in ("rowid", "rownum")):
+        return expr
+    return '"%s"' % expr.replace('"', "|")
+
+
+class OracleResult(Result):
+
+    def __init__(self, connection, raw_cursor, rowid = None):
+        super(OracleResult, self).__init__(connection, raw_cursor)
+        self.lastrowid = rowid
+
+    def get_insert_identity(self, primary_key, primary_variables):
+        return Eq(Column("rowid"), Rowid(self.lastrowid))
+
+    @staticmethod
+    def set_variable(variable, value):
+        if isinstance(value, float):
+            value = Decimal(str(value))
+
+        variable.set(value, from_db=True)
+
+    @staticmethod
+    def from_database(row):
+        """Convert Oracle-specific datatypes to 'normal' Python types.
+
+        If there are anny C{buffer} instances in the row, convert them
+        to strings.
+        """
+        for value in row:
+            if isinstance(value, oracle.LOB):
+                yield value.read()
+            else:
+                yield value
+
+
+class OracleConnection(Connection):
+
+    result_factory = OracleResult
+    compile = compile
+    param_mark = "?"
+
+    def as_read_committed(self):
+        return _isolation_context(self)
+
+    def is_disconnection_error(self, exc):
+        if isinstance(exc, (oracle.OperationalError, oracle.DatabaseError)):
+            error, = exc.args
+            # Sometimes exceptions get caught here without a code attribute.
+            if hasattr(error, "code") and error.code in (3135, 3113):
+                return True
+        return False
+
+    def execute(self, statement, params=None, noresult=False):
+        """Execute a statement with the given parameters.
+
+        This method is completely overidden because the original from the base
+        class expects to receive only a C{raw_cursor} from C{raw_execute}, and
+        we need to receive also the C{rowid}, as we cannot set it in the
+        cursor object.
+
+        @type statement: L{Expr} or C{str}.
+        @param statement: The statement to execute.  It will be compiled if
+            necessary.
+        @param noresult: If True, no result will be returned.
+        @raise DisconnectionError: Raised when the connection is lost.
+            Reconnection happens automatically on rollback.
+        @return: The result of C{self.result_factory}, or None if C{noresult}
+            is True.
+        """
+        if self._closed:
+            raise ClosedError("Connection is closed")
+        self._ensure_connected()
+        if self._event:
+            self._event.emit("register-transaction")
+        if isinstance(statement, Expr):
+            if params is not None:
+                raise ValueError("Can't pass parameters with expressions")
+            state = State()
+            statement = self.compile(statement, state)
+            params = state.parameters
+        statement = convert_to_sequential(statement)
+        raw_cursor, rowid = self.raw_execute(statement, params)
+        if noresult:
+            self._check_disconnect(raw_cursor.close)
+            return None
+        return self.result_factory(self, raw_cursor, rowid)
+
+    def raw_execute(self, statement, params):
+        """Execute a raw statement with the given parameters.
+
+        This method is completely overidden because the original from the base
+        class converts params to a tuple, and we need a dictionary!  It's
+        acceptable to override this method in subclasses, but it is not
+        intended to be called externally.  If the global C{DEBUG} flag is
+        True, the statement will be printed to standard out.
+
+        @return: The DBAPI cursor object, as fetched from L{build_raw_cursor}.
+        """
+        rowid = None
+        raw_cursor = self.build_raw_cursor()
+
+        statement = str(statement)
+
+        if statement.startswith("INSERT INTO"):
+            statement = statement + " RETURNING ROWID INTO :out_rowid"
+
+            # Make sure params is a list as we need to add to it.
+            if params is None:
+                params = []
+            elif not isinstance(params, list):
+                params = list(params)
+
+            rowid = raw_cursor.var(oracle.ROWID)
+            params.append(rowid)
+
+        if not params:
+            params = ()
+        else:
+            params = tuple(self.to_database(params))
+
+        trace("connection_raw_execute", self, raw_cursor, statement,
+              params or ())
+
+        try:
+            self._check_disconnect(raw_cursor.execute, statement, params)
+            if rowid:
+                rowid = rowid.getvalue()
+        except DatabaseError, de:
+            error, = de.args
+            if error == 8177:
+                raise OperationalError("database is locked")
+            else:
+                raise
+        except Exception, error:
+            trace("connection_raw_execute_error", self, raw_cursor,
+                  statement, params or (), error)
+            raise
+        else:
+            trace("connection_raw_execute_success", self, raw_cursor,
+                  statement, params or ())
+        return raw_cursor, rowid
+
+    @staticmethod
+    def to_database(params):
+        for bind_var in params:
+            if isinstance(bind_var, (RawStrVariable, PickleVariable)):
+                yield oracle.Binary(bind_var.get(to_db=True))
+            elif isinstance(bind_var, Variable):
+                yield bind_var.get(to_db=True)
+            else:
+                yield  bind_var
+
+
+def convert_to_sequential(statement):
+    """Convert a query using ? bind variables to a query using
+       sequential bind variables.  For example, SELECT ? FROM DUAL
+       will be converted to SELECT :1 FROM DUAL"""
+    param_no = 1
+    tokens = statement.split("'")
+    for i in range(0, len(tokens), 2):
+        while True:
+            old_tokens = tokens[i]
+            new_tokens = old_tokens.replace('?', ':%s' % param_no, 1)
+            if old_tokens == new_tokens:
+                break
+            else:
+                tokens[i] = new_tokens
+                param_no += 1
+    return "'".join(tokens)
+
+
+class _isolation_context(object):
+
+    def __init__ (self, connection):
+        self.connection = connection
+
+    def __enter__(self):
+        self.connection.commit()
+        self.connection.execute(
+            "ALTER SESSION SET isolation_level = read committed")
+
+    def __exit__(self, exc_type, exc_value, traceback):
+        if exc_value:
+            self.connection.rollback()
+        else:
+            self.connection.commit()
+        self.connection.execute(
+            "ALTER SESSION SET isolation_level = serializable")
+        self.connection.commit()
+
+
+class _type_converter(object):
+    """This class wraps an input type handler and an output type handler.
+       The reason being to allow for saving the encoding for the session."""
+    def __init__(self, encoding):
+        self.encoding = encoding
+
+    def convert_unicode(self, value):
+        if isinstance(value, Variable):
+            value = value.get(to_db=True)
+        if not isinstance(value, unicode):
+            return unicode(value, "utf8")
+        else:
+            return value
+
+    def OutputTypeHandler(self, cursor, name, defaultType, size, precision,
+                          scale):
+        """Prepare cx_Oracle to turn an Oracle datatype into a Python datatype.
+
+        This function's purpose is to tell cx_Oracle how to convert an Oracle
+        datatype into a Python datatype.
+        """
+        if defaultType in (oracle.UNICODE, oracle.FIXED_UNICODE):
+            return cursor.var(unicode, size, cursor.arraysize,
+                              outconverter=self.convert_unicode)
+        elif defaultType in (oracle.STRING, oracle.FIXED_CHAR):
+            return cursor.var(str, size, cursor.arraysize)
+
+    def InputTypeHandler(self, cursor, value, numElements):
+        """Prepare cx_Oracle to turn a Python datatype into an Oracle datatype.
+
+        This function does the opposite of OutputTypeHandler: it tells
+        cx_Oracle how to convert a Python datatype into an Oracle datatype.
+        """
+        if isinstance(value, unicode):
+            return cursor.var(unicode, arraysize=numElements,
+                              inconverter=self.convert_unicode)
+
+class Oracle(Database):
+
+    connection_factory = OracleConnection
+    converter_factory = _type_converter
+    raw_connection_factory = oracle.connect
+
+    def __init__(self, uri):
+        if oracle is dummy:
+            raise DatabaseModuleError("'cx_Oracle' failed to load")
+
+        if not uri.port:
+            uri.port = 1521
+
+        isolation = uri.options.get("isolation", "serializable")
+        isolation_mapping = {
+            "serializable": "SERIALIZABLE",
+            "read-committed": "READ COMMITTED",
+        }
+        try:
+            self._isolation = isolation_mapping[isolation]
+        except KeyError:
+            raise ValueError(
+                "Unknown serialization level %r: expected one of "
+                "'serializable' or 'read-committed'" %
+                (isolation,))
+
+        # Optionally set ORACLE_HOME and TNS_ADMIN environment
+        # variables for controlling tnsnames.ora lookup
+        oracle_home = uri.options.get('oracle_home')
+        if oracle_home:
+            os.environ["ORACLE_HOME"] = oracle_home
+
+        # If tns is specified in the options, treat the host part of the URI as
+        # the TNS name, and therefore the DSN.
+        if uri.options.get("tns", False):
+            self._dsn = uri.host
+        else:
+            self._dsn = oracle.makedsn(uri.host, uri.port, uri.database)
+
+        self._username = uri.username
+        self._password = uri.password
+
+    def raw_connect(self):
+        if oracle is dummy:
+            raise ImportError, "Could not import cx_Oracle"
+
+        raw_connection = self.raw_connection_factory(self._username,
+                                                     self._password, self._dsn)
+
+        c = raw_connection.cursor()
+        c.execute("alter session set isolation_level=%s" % self._isolation)
+        c.close()
+
+        type_handler = self.converter_factory(raw_connection.nencoding)
+        raw_connection.inputtypehandler = type_handler.InputTypeHandler
+        raw_connection.outputtypehandler = type_handler.OutputTypeHandler
+
+        return raw_connection
+
+create_from_uri = Oracle

=== modified file 'storm/expr.py'
--- storm/expr.py	2009-11-02 11:11:20 +0000
+++ storm/expr.py	2010-03-15 15:48:27 +0000
@@ -1000,7 +1000,7 @@
     return "%s = %s" % (compile(eq.expr1, state), compile(eq.expr2, state))
 
 @compile_python.when(Eq)
-def compile_eq(compile, eq, state):
+def compile_python_eq(compile, eq, state):
     return "%s == %s" % (compile(eq.expr1, state), compile(eq.expr2, state))
 
 

=== modified file 'storm/variables.py'
--- storm/variables.py	2009-10-03 16:08:04 +0000
+++ storm/variables.py	2010-03-15 15:48:27 +0000
@@ -436,6 +436,8 @@
         if from_db:
             if value is None:
                 return None
+            if isinstance(value, datetime):
+                return value.date()
             if isinstance(value, date):
                 return value
             if not isinstance(value, (str, unicode)):

=== modified file 'tests/databases/base.py'
--- tests/databases/base.py	2009-09-19 15:56:13 +0000
+++ tests/databases/base.py	2010-03-15 15:48:27 +0000
@@ -75,7 +75,7 @@
         raise NotImplementedError
 
     def create_sample_data(self):
-        self.connection.execute("INSERT INTO number VALUES (1, 2, 3)")
+        self.connection.execute("INSERT INTO number_table VALUES (1, 2, 3)")
         self.connection.execute("INSERT INTO test VALUES (10, 'Title 10')")
         self.connection.execute("INSERT INTO test VALUES (20, 'Title 20')")
         self.connection.commit()
@@ -84,7 +84,7 @@
         pass
 
     def drop_tables(self):
-        for table in ["number", "test", "datetime_test", "bin_test"]:
+        for table in ["number_table", "test", "datetime_test", "bin_test"]:
             try:
                 self.connection.execute("DROP TABLE " + table)
                 self.connection.commit()
@@ -131,7 +131,7 @@
         self.assertTrue(result.get_one())
 
     def test_execute_result(self):
-        result = self.connection.execute("SELECT 1")
+        result = self.connection.execute(Select(1))
         self.assertTrue(isinstance(result, Result))
         self.assertTrue(result.get_one())
 
@@ -143,15 +143,15 @@
         self.assertTrue(isinstance(row[0], unicode))
 
     def test_execute_params(self):
-        result = self.connection.execute("SELECT one FROM number "
+        result = self.connection.execute("SELECT one FROM number_table "
                                          "WHERE 1=?", (1,))
         self.assertTrue(result.get_one())
-        result = self.connection.execute("SELECT one FROM number "
+        result = self.connection.execute("SELECT one FROM number_table "
                                          "WHERE 1=?", (2,))
         self.assertFalse(result.get_one())
 
     def test_execute_empty_params(self):
-        result = self.connection.execute("SELECT one FROM number", ())
+        result = self.connection.execute("SELECT one FROM number_table", ())
         self.assertTrue(result.get_one())
 
     def test_execute_expression(self):
@@ -356,7 +356,7 @@
         event.hook("register-transaction", register_transaction)
 
         connection = self.database.connect(event)
-        connection.execute("SELECT 1")
+        connection.execute(Select(1))
         self.assertEqual(len(calls), 1)
         self.assertEqual(calls[0], marker)
 
@@ -364,17 +364,17 @@
         return [int(item)+1 for item in row]
 
     def test_wb_result_get_one_goes_through_from_database(self):
-        result = self.connection.execute("SELECT one, two FROM number")
+        result = self.connection.execute("SELECT one, two FROM number_table")
         result.from_database = self.from_database
         self.assertEquals(result.get_one(), (2, 3))
 
     def test_wb_result_get_all_goes_through_from_database(self):
-        result = self.connection.execute("SELECT one, two FROM number")
+        result = self.connection.execute("SELECT one, two FROM number_table")
         result.from_database = self.from_database
         self.assertEquals(result.get_all(), [(2, 3)])
 
     def test_wb_result_iter_goes_through_from_database(self):
-        result = self.connection.execute("SELECT one, two FROM number")
+        result = self.connection.execute("SELECT one, two FROM number_table")
         result.from_database = self.from_database
         self.assertEquals(iter(result).next(), (2, 3))
 
@@ -505,6 +505,7 @@
                                "%s.default_port" % (type(self).__name__,
                                                     type(self).__name__))
         uri_str = os.environ.get(self.host_environment_variable)
+        
         if uri_str:
             uri = URI(uri_str)
             if not uri.host:
@@ -542,24 +543,24 @@
 
     def test_proxy_works(self):
         """Ensure that we can talk to the database through the proxy."""
-        result = self.connection.execute("SELECT 1")
+        result = self.connection.execute(Select(1))
         self.assertEqual(result.get_one(), (1,))
 
     def test_catch_disconnect_on_execute(self):
         """Test that database disconnections get caught on execute()."""
-        result = self.connection.execute("SELECT 1")
+        result = self.connection.execute(Select(1))
         self.assertTrue(result.get_one())
         self.proxy.restart()
         self.assertRaises(DisconnectionError,
-                          self.connection.execute, "SELECT 1")
+                          self.connection.execute, Select(1))
 
     def test_catch_disconnect_on_commit(self):
         """Test that database disconnections get caught on commit()."""
-        result = self.connection.execute("SELECT 1")
+        result = self.connection.execute(Select(1))
         self.assertTrue(result.get_one())
         self.proxy.restart()
         self.assertRaises(DisconnectionError, self.connection.commit)
-
+        
     def test_wb_catch_already_disconnected_on_rollback(self):
         """Connection.rollback() swallows disconnection errors.
 
@@ -567,13 +568,13 @@
         then it is possible that Storm won't see the disconnection.
         It should be able to recover from this situation though.
         """
-        result = self.connection.execute("SELECT 1")
+        result = self.connection.execute(Select(1))
         self.assertTrue(result.get_one())
         self.proxy.restart()
         # Perform an action that should result in a disconnection.
         try:
             cursor = self.connection._raw_connection.cursor()
-            cursor.execute("SELECT 1")
+            cursor.execute(Select(1))
             cursor.fetchone()
         except Error, exc:
             self.assertTrue(self.connection.is_disconnection_error(exc))
@@ -600,59 +601,59 @@
         then it is possible that Storm won't see the disconnection.
         It should be able to recover from this situation though.
         """
-        result = self.connection.execute("SELECT 1")
+        result = self.connection.execute(Select(1))
         self.assertTrue(result.get_one())
         self.proxy.restart()
         # Perform an action that should result in a disconnection.
         try:
             cursor = self.connection._raw_connection.cursor()
-            cursor.execute("SELECT 1")
+            cursor.execute(Select(1))
             cursor.fetchone()
         except DatabaseError, exc:
             self.assertTrue(self.connection.is_disconnection_error(exc))
         else:
             self.fail("Disconnection was not caught.")
         self.assertRaises(DisconnectionError,
-                          self.connection.execute, "SELECT 1")
+                          self.connection.execute, Select(1))
 
     def test_connection_stays_disconnected_in_transaction(self):
         """Test that connection does not immediately reconnect."""
-        result = self.connection.execute("SELECT 1")
+        result = self.connection.execute(Select(1))
         self.assertTrue(result.get_one())
         self.proxy.restart()
         self.assertRaises(DisconnectionError,
-                          self.connection.execute, "SELECT 1")
+                          self.connection.execute, Select(1))
         self.assertRaises(DisconnectionError,
-                          self.connection.execute, "SELECT 1")
+                          self.connection.execute, Select(1))
 
     def test_reconnect_after_rollback(self):
         """Test that we reconnect after rolling back the connection."""
-        result = self.connection.execute("SELECT 1")
+        result = self.connection.execute(Select(1))
         self.assertTrue(result.get_one())
         self.proxy.restart()
         self.assertRaises(DisconnectionError,
-                          self.connection.execute, "SELECT 1")
+                          self.connection.execute, Select(1))
         self.connection.rollback()
-        result = self.connection.execute("SELECT 1")
+        result = self.connection.execute(Select(1))
         self.assertTrue(result.get_one())
 
     def test_catch_disconnect_on_reconnect(self):
         """Test that reconnection failures result in DisconnectionError."""
-        result = self.connection.execute("SELECT 1")
+        result = self.connection.execute(Select(1))
         self.assertTrue(result.get_one())
         self.proxy.stop()
         self.assertRaises(DisconnectionError,
-                          self.connection.execute, "SELECT 1")
+                          self.connection.execute, Select(1))
         # Rollback the connection, but because the proxy is still
         # down, we get a DisconnectionError again.
         self.connection.rollback()
         self.assertRaises(DisconnectionError,
-                          self.connection.execute, "SELECT 1")
+                          self.connection.execute, Select(1))
 
     def test_close_connection_after_disconnect(self):
-        result = self.connection.execute("SELECT 1")
+        result = self.connection.execute(Select(1))
         self.assertTrue(result.get_one())
         self.proxy.stop()
         self.assertRaises(DisconnectionError,
-                          self.connection.execute, "SELECT 1")
+                          self.connection.execute, Select(1))
         self.connection.close()

=== added file 'tests/databases/oracle.py'
--- tests/databases/oracle.py	1970-01-01 00:00:00 +0000
+++ tests/databases/oracle.py	2010-03-15 15:48:27 +0000
@@ -0,0 +1,372 @@
+from __future__ import with_statement
+
+import os
+import pickle
+from binascii import hexlify
+from datetime import time, timedelta
+from unittest import TestSuite, defaultTestLoader as loader, TestCase
+
+from cx_Oracle import makedsn, DatabaseError, LOB
+
+from storm.database import *
+from storm.databases.oracle import (
+    _type_converter, convert_to_sequential, Oracle, _isolation_context,
+    OracleConnection, OracleResult, compile)
+from storm.event import EventSystem
+from storm.exceptions import NotSupportedError
+from storm.expr import Select, SQLToken, Alias
+from storm.tracer import debug
+from storm.uri import URI
+from storm.variables import PickleVariable, RawStrVariable, TimeDeltaVariable
+
+from tests.databases.base import (
+    DatabaseTest, DatabaseDisconnectionTest, UnsupportedDatabaseTest)
+from tests.helper import TestHelper
+from tests.mocker import ANY, ARGS
+from tests.store.oracle import create_table_helpers, drop_tables
+
+
+DEBUG = True
+debug(DEBUG)
+
+class Marker(object):
+    pass
+
+marker = Marker()
+
+class OracleDatabaseTest(DatabaseTest, TestHelper):
+    _table_names = ['number_table', 'test', 'datetime_test', 'bin_test',
+                    'like_case_insensitive_test', 'insert_returning_test']
+    supports_microseconds = False
+    def is_supported(self):
+        return bool(os.environ.get('STORM_ORACLE_URI'))
+
+    def create_database(self):
+        self.database = create_database(os.environ['STORM_ORACLE_URI'])
+
+    def create_tables(self):
+        try:
+            with self.connection.as_read_committed():
+                self.connection.execute("CREATE TABLE number_table "
+                                        "(id INTEGER, one INTEGER, two INTEGER,"
+                                        " three INTEGER)")
+                self.connection.execute("CREATE TABLE test "
+                                        "(id INTEGER PRIMARY KEY, title NVARCHAR2(2000))")
+                self.connection.execute("CREATE TABLE datetime_test "
+                                        "(id INTEGER PRIMARY KEY,"
+                                        " dt TIMESTAMP(9), d DATE, t DATE, "
+                                        " td INTERVAL DAY TO SECOND)")
+                self.connection.execute("CREATE TABLE bin_test "
+                                        "(id INTEGER PRIMARY KEY, b BLOB)")
+                self.connection.execute("CREATE TABLE like_case_insensitive_test "
+                                        "(id INTEGER PRIMARY KEY, description CLOB)")
+                self.connection.execute("CREATE TABLE insert_returning_test "
+                                        "(id INTEGER PRIMARY KEY, "
+                                        "id1 INTEGER DEFAULT 123, "
+                                        " id2 INTEGER DEFAULT 456)")
+                create_table_helpers(self.connection, self._table_names)
+
+        except Exception, e:
+            self.connection.rollback()
+            raise
+        finally:
+            debug(DEBUG)
+
+    def create_sample_data(self):
+        self.connection.execute("INSERT INTO number_table (one, two, three) VALUES (1, 2, 3)")
+        self.connection.execute("INSERT INTO test VALUES (10, 'Title 10')")
+        self.connection.execute("INSERT INTO test VALUES (20, 'Title 20')")
+        self.connection.commit()
+
+    def drop_tables(self):
+        try:
+            drop_tables(self.connection, self._table_names)
+        finally:
+            debug(DEBUG)
+
+    def test_binary(self):
+        """Ensure database works with high bits and embedded zeros.
+        Overriding this because we need to send the data as a hexlified
+        string."""
+
+        value = "\xff\x00\xff\x00"
+        self.connection.execute("INSERT INTO bin_test (b) VALUES (?)",
+                                (hexlify(value),))
+        self.connection.commit()
+        result = self.connection.execute("SELECT b FROM bin_test")
+        variable = RawStrVariable()
+        result.set_variable(variable, result.get_one()[0])
+        self.assertEquals(variable.get(), value)
+
+    def test_binary_ascii(self):
+        """Some databases like pysqlite2 may return unicode for strings.
+        Overriding this because we need to send the data as a hexlified
+        string."""
+        bin_text = 'Value'
+        self.connection.execute("INSERT INTO bin_test VALUES (10, ?)",
+                                (hexlify(bin_text),))
+        result = self.connection.execute("SELECT b FROM bin_test")
+        variable = RawStrVariable()
+        # If the following doesn't raise a TypeError we're good.
+        result.set_variable(variable, result.get_one()[0])
+        self.assertEquals(variable.get(), "Value")
+
+    def test_execute_result(self):
+        result = self.connection.execute("SELECT 1 FROM DUAL")
+        self.assertTrue(isinstance(result, Result))
+        self.assertTrue(result.get_one())
+
+    def test_execute_sends_event(self):
+        event = EventSystem(marker)
+        calls = []
+        def register_transaction(owner):
+            calls.append(owner)
+        event.hook("register-transaction", register_transaction)
+
+        connection = self.database.connect(event)
+        connection.execute("SELECT 1 FROM DUAL")
+        self.assertEqual(len(calls), 1)
+        self.assertEqual(calls[0], marker)
+
+    def test_timedelta(self):
+        value = timedelta(12, 34)
+        self.connection.execute("INSERT INTO datetime_test (td) VALUES (?)",
+                                (value,))
+        result = self.connection.execute("SELECT td FROM datetime_test")
+        variable = TimeDeltaVariable()
+        result.set_variable(variable, result.get_one()[0])
+        self.assertEquals(variable.get(), value)
+
+    def test_pickle(self):
+        value = {"a": 1, "b": 2}
+        value_dump = pickle.dumps(value, -1)
+        self.connection.execute("INSERT INTO bin_test (b) VALUES (?)",
+                                (hexlify(value_dump),))
+        result = self.connection.execute("SELECT b FROM bin_test")
+        variable = PickleVariable()
+        result.set_variable(variable, result.get_one()[0])
+        self.assertEquals(variable.get(), value)
+
+    def test_time(self):
+        """Oracle doesn't have a time-only datatype, so we need to test
+        that using one fails gracefully."""
+        self.assertRaises(NotSupportedError, self.connection.execute,
+                          'SELECT * FROM ', (time(1, 2, 3),))
+
+
+class OracleUnsupportedDatabaseTest(UnsupportedDatabaseTest, TestHelper):
+    dbapi_module_names = ['cx_Oracle']
+    db_module_name = 'oracle'
+
+# FIXME - Oracle doesn't seem to work with these disconnection tests
+# class OracleDatabaseDisconnectionTest(DatabaseDisconnectionTest, TestHelper):
+#     environment_variable = "STORM_ORACLE_URI"
+#     host_environment_variable = "STORM_ORACLE_HOST_URI"
+#     default_port=1521
+
+class MockObject(object):
+    def __init__(self, allowed_sets, allowed_gets, *args, **argd):
+        self.allowed_sets = allowed_sets
+        self.allowed_gets = allowed_gets
+        self.args = args
+        self.argd = argd
+        self.was_set = set()
+        self.was_gotten = set()
+
+    def __getattr__(self, name):
+        if self.allowed_gets.get(name, False):
+            self.was_gotten.add(name)
+            return self.allowed_gets[name]
+
+        else:
+            raise NotImplementedError()
+
+    def __setattr__(self, name, val):
+
+        if self.allowed_sets.get(name, False):
+            self.was_set.add(name)
+            self.__dict__[name] = val
+        else:
+            raise NotImplementedError()
+
+    def __delattr__(self, name):
+        raise NotImplementedError()
+
+    def __call__(self, *args, **argd):
+        return self
+
+def dummy_func(*args, **argd):
+    pass
+
+
+class UnicodeUnitTests(TestCase):
+    unit = True
+    def setUp(self):
+        self.converter = _type_converter('utf8')
+
+    def test_identity(self):
+        expected = u"Some string"
+        actual = self.converter.convert_unicode(expected)
+        self.assertEqual(actual, expected)
+
+    def test_cross_conversion(self):
+        initial = unicode('Some string', 'latin-1')
+        expected = unicode('Some string', 'utf8')
+        actual = self.converter.convert_unicode(initial)
+        self.assertEqual(actual, expected)
+
+class OracleUnitTests(TestHelper):
+    unit = True
+    def test_tns_uri(self):
+        uri = URI('oracle://foo:bar@test?tns=true')
+        ora = Oracle(uri)
+        self.assertEqual(ora._dsn, 'test')
+        self.assertEqual(ora._username, 'foo')
+        self.assertEqual(ora._password, 'bar')
+
+    def test_host_str(self):
+        uri = URI('oracle://foo:bar@localhost:1521/test')
+        ora = Oracle(uri)
+        self.assertEqual(ora._username, 'foo')
+        self.assertEqual(ora._password, 'bar')
+        expected_dsn = makedsn('localhost', 1521, 'test')
+        self.assertEqual(ora._dsn, expected_dsn)
+
+    def test_type_handlers_set(self):
+        mocker = self.mocker
+        mock = mocker.mock()
+        converter = mocker.mock()
+        mock(ARGS)
+        mocker.result(mock)
+        mock.cursor()
+        mocker.result(mock)
+        mock.execute(ARGS)
+        mocker.result(None)
+        mock.nencoding
+        mocker.result('utf8')
+        converter('utf8')
+        mocker.result(converter)
+
+        converter.InputTypeHandler
+        mocker.result(converter)
+
+        mock.inputtypehandler = converter
+        mocker.result(None)
+
+        converter.OutputTypeHandler
+        mocker.result(converter)
+        mock.outputtypehandler = converter
+        mock.close()
+        mocker.result(None)
+        mocker.replay()
+
+        class DummyOracle(Oracle):
+            raw_connection_factory = mock
+            converter_factory = converter
+            def __init__(self):
+                self._username = ''
+                self._password = ''
+                self._dsn = ''
+                self._isolation = ''
+
+        DummyOracle().raw_connect()
+
+
+class OracleConnectionTests(TestHelper):
+    unit = True
+    def test_isolation_context_pass(self):
+        mocker = self.mocker
+        mock = mocker.mock()
+        mock.commit()
+        mocker.count(0, None)
+        mock.execute('ALTER SESSION SET isolation_level = read committed')
+        mock.execute('ALTER SESSION SET isolation_level = serializable')
+        mocker.replay()
+
+        with _isolation_context(mock):
+            pass
+
+    def test_isolation_context_fail(self):
+        mocker = self.mocker
+        mock = mocker.mock()
+        mock.commit()
+        mocker.result(None)
+        mock.execute('ALTER SESSION SET isolation_level = read committed')
+        mock.rollback()
+        mocker.result(None)
+        mock.execute('ALTER SESSION SET isolation_level = serializable')
+        mock.commit()
+        mocker.result(None)
+        mocker.replay()
+
+        try:
+            with _isolation_context(mock):
+                raise TypeError()
+        except TypeError:
+            pass
+
+    def test_is_disconnection_error(self):
+        codes = [3135, 3113]
+        non_codes = [0, 1000]
+
+        database = self.mocker.mock()
+        database.raw_connect()
+        self.mocker.result(database)
+        self.mocker.replay()
+
+        conn = OracleConnection(database)
+
+        class DummyError(object):
+            def __init__(self, code):
+                self.code = code
+
+        for code in codes:
+            exc = DatabaseError(DummyError(code))
+            assert conn.is_disconnection_error(exc)
+
+        for code in non_codes:
+            exc = DatabaseError(DummyError(code))
+            assert not conn.is_disconnection_error(exc)
+
+    def test_from_database_LOB(self):
+        mocker = self.mocker
+        mock = mocker.mock(LOB)
+
+        iter(mock)
+        mocker.generate([mock])
+        expected = 'result string'
+        mock.read()
+        mocker.result(expected)
+        mocker.replay()
+
+        row_iter = OracleResult.from_database(mock)
+        self.assertEqual(row_iter.next(), expected)
+        self.assertRaises(StopIteration, row_iter.next)
+
+
+class QueryUnitTests(TestHelper):
+    unit = True
+    def test_convert_to_sequential(self):
+        initial = 'SELECT ? FROM DUAL'
+        expected = 'SELECT :1 FROM DUAL'
+        actual = convert_to_sequential(initial)
+        self.assertEqual(expected, actual)
+
+    def test_convert_to_sequential_in_str(self):
+        expected = "SELECT '?' FROM DUAL"
+        actual = convert_to_sequential(expected)
+        self.assertEqual(expected, actual)
+
+    def test_conver_to_sequential_not_in_str(self):
+        initial = "SELECT 'Foo', ? FROM DUAL"
+        expected = "SELECT 'Foo', :1 FROM DUAL"
+        actual = convert_to_sequential(initial)
+        self.assertEqual(expected, actual)
+
+    def test_double_quotes(self):
+        initial = 'word"word'
+        expected = '"word|word"'
+        actual = compile(SQLToken(initial))
+        self.assertEqual(expected, actual)
+
+

=== modified file 'tests/store/base.py'
--- tests/store/base.py	2010-02-08 11:30:44 +0000
+++ tests/store/base.py	2010-03-15 15:48:27 +0000
@@ -280,11 +280,11 @@
         return store._cache
 
     def test_execute(self):
-        result = self.store.execute("SELECT 1")
+        result = self.store.execute(Select(1))
         self.assertTrue(isinstance(result, Result))
         self.assertEquals(result.get_one(), (1,))
 
-        result = self.store.execute("SELECT 1", noresult=True)
+        result = self.store.execute(Select(1), noresult=True)
         self.assertEquals(result, None)
 
     def test_execute_params(self):
@@ -302,7 +302,7 @@
     def test_close(self):
         store = Store(self.database)
         store.close()
-        self.assertRaises(ClosedError, store.execute, "SELECT 1")
+        self.assertRaises(ClosedError, store.execute, Select(1))
 
     def test_get(self):
         foo = self.store.get(Foo, 10)
@@ -1423,7 +1423,7 @@
         result.group_by(Foo)
         foo1 = self.store.get(Foo, 10)
         foo2 = self.store.get(Foo, 20)
-        self.assertEquals(list(result), [(5, foo1), (16, foo2)])
+        self.assertEquals(sorted(list(result)), [(5, foo1), (16, foo2)])
 
     def test_find_group_by_table_contains(self):
         result = self.store.find(
@@ -5675,7 +5675,7 @@
         def register_transaction(owner):
             calls.append(owner)
         self.store._event.hook("register-transaction", register_transaction)
-        self.store.execute("SELECT 1")
+        self.store.execute(Select(1))
         self.assertEqual(len(calls), 1)
         self.assertEqual(calls[0], self.store)
 

=== added file 'tests/store/oracle.py'
--- tests/store/oracle.py	1970-01-01 00:00:00 +0000
+++ tests/store/oracle.py	2010-03-15 15:48:27 +0000
@@ -0,0 +1,234 @@
+#
+# Copyright (c) 2006, 2007 Canonical
+#
+# Written by Gustavo Niemeyer <[e-mail]>
+#
+# This file is part of Storm Object Relational Mapper.
+#
+# Storm is free software; you can redistribute it and/or modify
+# it under the terms of the GNU Lesser General Public License as
+# published by the Free Software Foundation; either version 2.1 of
+# the License, or (at your option) any later version.
+#
+# Storm is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU Lesser General Public License for more details.
+#
+# You should have received a copy of the GNU Lesser General Public License
+# along with this program.  If not, see <http://www.gnu.org/licenses/>.
+#
+from __future__ import with_statement
+import os, sys
+
+from storm.database import create_database
+from storm.databases.oracle import Oracle
+from storm.uri import URI
+from cx_Oracle import DatabaseError
+
+from storm.database import Result
+from tests.store.base import StoreTest, EmptyResultSetTest
+from tests.store.base import Foo
+from storm.properties import Int, Float, RawStr, Unicode, Property, Pickle
+from tests.helper import TestHelper, MakePath
+
+from storm.info import ClassAlias
+
+from storm.tracer import debug
+
+DEBUG = False
+debug(DEBUG)
+
+def _safe_drop(connection, query):
+    """
+    This function will execute a given DROP query
+    and ignore any error codes that indicate that
+    the object doesn't exist.
+    """
+    with connection.as_read_committed():
+        try:
+            connection.execute(query)
+        except DatabaseError, exc:
+            error, = exc.args
+            if error.code not in [942, 4080, 2289, 903]:
+                raise
+            
+def create_table_helpers(connection, table_names):
+    with connection.as_read_committed():
+        for table_name in table_names:
+            connection.execute("CREATE SEQUENCE %s_s "
+                               "START WITH 1 "
+                               "INCREMENT BY 1 "
+                               "NOMAXVALUE " 
+                               "ORDER NOCYCLE" % (table_name),
+                               )
+            connection.execute("CREATE OR REPLACE TRIGGER %s_t "
+                               "BEFORE INSERT ON %s "
+                               "FOR EACH ROW "
+                               "WHEN (new.id is null) "
+                               "BEGIN "
+                               "SELECT %s_s.nextval INTO :new.id FROM DUAL; "
+                               "END;" % (table_name, table_name, table_name),
+                               )
+            
+def drop_tables(connection, tables):
+    for table in tables:
+        _safe_drop(connection, "DROP TRIGGER %s_t" % table)
+        _safe_drop(connection, "DROP SEQUENCE %s_s" % table)
+        
+        _safe_drop(connection, "DROP TABLE %s" % table)
+
+class OracleStoreTest(TestHelper, StoreTest):
+    DUMMY_SELECT = 'SELECT 1 FROM DUAL'
+    helpers = [MakePath]
+    _tables = ['foo', 'bar', 'bin', 'link', 'money', 'selfref', 'foovalue']
+
+    def create_tables(self):
+        connection = self.connection
+        with connection.as_read_committed():
+            connection.execute("CREATE TABLE foo (id NUMBER, "
+                               "title NVARCHAR2(2000) DEFAULT 'Default Title')")
+            connection.execute("CREATE TABLE bar "
+                               "(id NUMBER, foo_id NUMBER, "
+                               "title NVARCHAR2(2000))")
+            connection.execute("CREATE TABLE bin "
+                               "(id NUMBER, bin BLOB, foo_id INTEGER)")
+            connection.execute("CREATE TABLE link "
+                               "(id NUMBER, foo_id NUMBER, bar_id NUMBER)")
+            connection.execute("CREATE TABLE money "
+                               "(id NUMBER, value NUMBER(6,4))")
+            connection.execute("CREATE TABLE selfref "
+                               "(id NUMBER, title NVARCHAR2(2000),"
+                               " selfref_id NUMBER)")
+            connection.execute("CREATE TABLE foovalue "
+                       "(id NUMBER, foo_id INTEGER,"
+                       " value1 INTEGER, value2 INTEGER)")
+            create_table_helpers(connection, self._tables)
+    
+    def drop_tables(self):
+        drop_tables(self.connection, self._tables)
+
+    def create_sample_data(self):
+        connection = self.connection
+        connection.execute("INSERT INTO foo (id, title)"
+                           " VALUES (10, 'Title 30')")
+        connection.execute("INSERT INTO foo (id, title)"
+                           " VALUES (20, 'Title 20')")
+        connection.execute("INSERT INTO foo (id, title)"
+                           " VALUES (30, 'Title 10')")
+        connection.execute("INSERT INTO bar (id, foo_id, title)"
+                           " VALUES (100, 10, 'Title 300')")
+        connection.execute("INSERT INTO bar (id, foo_id, title)"
+                           " VALUES (200, 20, 'Title 200')")
+        connection.execute("INSERT INTO bar (id, foo_id, title)"
+                           " VALUES (300, 30, 'Title 100')")
+        connection.execute("INSERT INTO bin (id, bin) VALUES (10, rawtohex('Blob 30'))")
+        connection.execute("INSERT INTO bin (id, bin) VALUES (20, rawtohex('Blob 20'))")
+        connection.execute("INSERT INTO bin (id, bin) VALUES (30, rawtohex('Blob 10'))")
+        connection.execute("INSERT INTO link (foo_id, bar_id) VALUES (10, 100)")
+        connection.execute("INSERT INTO link (foo_id, bar_id) VALUES (10, 200)")
+        connection.execute("INSERT INTO link (foo_id, bar_id) VALUES (10, 300)")
+        connection.execute("INSERT INTO link (foo_id, bar_id) VALUES (20, 100)")
+        connection.execute("INSERT INTO link (foo_id, bar_id) VALUES (20, 200)")
+        connection.execute("INSERT INTO link (foo_id, bar_id) VALUES (30, 300)")
+        connection.execute("INSERT INTO money (id, value)"
+                           " VALUES (10, '12.3455')")
+        connection.execute("INSERT INTO selfref (id, title, selfref_id)"
+                           " VALUES (15, 'SelfRef 15', NULL)")
+        connection.execute("INSERT INTO selfref (id, title, selfref_id)"
+                           " VALUES (25, 'SelfRef 25', NULL)")
+        connection.execute("INSERT INTO selfref (id, title, selfref_id)"
+                           " VALUES (35, 'SelfRef 35', 15)")
+        connection.execute("INSERT INTO foovalue (id, foo_id, value1, value2)"
+                           " VALUES (1, 10, 2, 1)")
+        connection.execute("INSERT INTO foovalue (id, foo_id, value1, value2)"
+                           " VALUES (2, 10, 2, 1)")
+        connection.execute("INSERT INTO foovalue (id, foo_id, value1, value2)"
+                           " VALUES (3, 10, 2, 1)")
+        connection.execute("INSERT INTO foovalue (id, foo_id, value1, value2)"
+                           " VALUES (4, 10, 2, 2)")
+        connection.execute("INSERT INTO foovalue (id, foo_id, value1, value2)"
+                           " VALUES (5, 20, 1, 3)")
+        connection.execute("INSERT INTO foovalue (id, foo_id, value1, value2)"
+                           " VALUES (6, 20, 1, 3)")
+        connection.execute("INSERT INTO foovalue (id, foo_id, value1, value2)"
+                           " VALUES (7, 20, 1, 4)")
+        connection.execute("INSERT INTO foovalue (id, foo_id, value1, value2)"
+                           " VALUES (8, 20, 1, 4)")
+        connection.execute("INSERT INTO foovalue (id, foo_id, value1, value2)"
+                           " VALUES (9, 20, 1, 2)")
+        connection.commit()
+
+    #Overriding this because SELECT ? should be SELECT ? FROM DUAL
+    def test_execute_params(self):
+        result = self.store.execute("SELECT ? FROM DUAL", (1,))
+        self.assertTrue(isinstance(result, Result))
+        self.assertEquals(result.get_one(), (1,))
+        
+    def test_is_in_empty_list(self):
+         result2 = self.store.find(Foo, Foo.id.is_in([]))
+         self.assertEquals(result2.count(), 0)
+
+    def setUp(self):
+        TestHelper.setUp(self)
+        StoreTest.setUp(self)
+        self.connection = self.database.connect()
+
+    def tearDown(self):
+        TestHelper.tearDown(self)
+        StoreTest.tearDown(self)
+        self.connection.close()
+
+    def is_supported(self):
+        return bool(os.environ.get("STORM_ORACLE_URI"))
+
+    def create_database(self):
+        self.database = create_database(os.environ["STORM_ORACLE_URI"])
+
+         
+class testOracleEmptyResultSet(TestHelper, EmptyResultSetTest):
+
+    helpers = [MakePath]
+
+    def setUp(self):
+        TestHelper.setUp(self)
+        EmptyResultSetTest.setUp(self)
+        self.connection = self.database.connect()
+
+    def tearDown(self):
+        TestHelper.tearDown(self)
+        EmptyResultSetTest.tearDown(self)
+        self.connection.close()
+
+    def is_supported(self):
+        return bool(os.environ.get("STORM_ORACLE_URI"))
+
+    def create_database(self):
+        self.database = create_database(os.environ["STORM_ORACLE_URI"])
+
+    def create_tables(self):
+        connection = self.connection
+        with connection.as_read_committed():
+            connection.execute("CREATE TABLE foo (id NUMBER, "
+                               "title VARCHAR2(4000) DEFAULT 'Default Title')")
+        #connection.execute("CREATE OR REPLACE SEQUENCE foo_seq "
+                           #"START WITH 1 "
+                           #"INCREMENT BY 1 "
+                           #"NOMAXVALUE")
+        #connection.execute("CREATE OR REPLACE TRIGGER foo_trigger "
+                           #"BEFORE INSERT ON foo "
+                           #"FOR EACH ROW "
+                           #"BEGIN "
+                           #"SELECT foo_seq.nextval INTO :new.id FROM DUAL; "
+                           #"END")
+
+    def drop_tables(self):
+        for table in ["foo"]:
+            connection = self.connection
+            try:
+                #connection.execute("DROP TRIGGER %s_trigger" % table)
+                #connection.execute("DROP SEQUENCE %s_seq" % table)
+                connection.execute("DROP TABLE %s" % table)
+                connection.commit()
+            except:
+                connection.rollback()