Merge lp:~gerdusvanzyl/storm/firebird into lp:storm

[Gerdus van Zyl]

Basic Firebird database support.

Passes all tests except "Connection.rollback() swallows disconnect errors" which I have so far been unable to fix.

example connection string env variable for tests: STORM_FIREBIRD_URI=firebird://sysdba:masterke@localhost:3050/stormtest.fdb

[Gustavo Niemeyer (niemeyer)]

Hi Gerdus,

Thanks for pushing this forward into a request!

Sorry if this has been asked already, but if not, can you please sign the contributor agreement at:

    http://www.canonical.com/contributors

[Gerdus van Zyl (gerdusvanzyl)]

No problem, I have just sent the email as per the instructions.

On Tue, Aug 10, 2010 at 7:23 PM, Gustavo Niemeyer <email address hidden> wrote:
> Hi Gerdus,
>
> Thanks for pushing this forward into a request!
>
> Sorry if this has been asked already, but if not, can you please sign the contributor agreement at:
>
>    http://www.canonical.com/contributors
> --
> https://code.launchpad.net/~gerdusvanzyl/storm/firebird/+merge/28559
> You are the owner of lp:~gerdusvanzyl/storm/firebird.
>

--
Gerdus van Zyl

Unmerged revisions

368. By Gerdus van Zyl on 2010-06-16

merge from trunk

367. By Gerdus van Zyl on 2010-06-16

cleanup unused comments

366. By Gerdus van Zyl on 2010-06-16

fixed proxy to work on windows
replaced os.read(self.request.fileno() .. with self.request.recv

365. By Gerdus van Zyl on 2010-06-16

-

364. By Gerdus van Zyl on 2010-06-16

disconnect cleanup, push to vpc

363. By Gerdus van Zyl on 2010-06-16

is_disconnection_error

362. By Gerdus van Zyl on 2010-06-16

test cases for returning and select limit

361. By Gerdus van Zyl on 2010-06-13

start of disconnect test, fix base database test to not use "select 1"

360. By Gerdus van Zyl on 2010-06-13

cleanup and override non implementable tests

359. By Gerdus van Zyl on 2010-06-06

code to pass test test_execute_expression and test_execute_expression_empty_params

=== added file 'storm/databases/firebird.py'
--- storm/databases/firebird.py	1970-01-01 00:00:00 +0000
+++ storm/databases/firebird.py	2010-06-26 13:26:23 +0000
@@ -0,0 +1,229 @@
+#
+# Copyright (c) 2010 Canonical
+#
+# Initial Code by Gerdus van Zyl
+#
+# 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/>.
+#
+#
+"""
+Tested only with: Kinterbase 3.3 and Firebird 2.1
+"""
+
+import os.path
+from datetime import timedelta
+from storm.databases import dummy
+
+from storm.expr import (compile, Select, compile_select, Undef,
+                        Expr, Insert,COLUMN,Sequence)
+from storm.variables import (Variable,IntVariable)
+from storm.database import Database, Connection, Result
+    
+from storm.exceptions import (
+    install_exceptions, DatabaseError, DatabaseModuleError, InterfaceError,
+    OperationalError, ProgrammingError, TimeoutError)
+
+try:
+    import kinterbasdb
+    install_exceptions(kinterbasdb)
+except ImportError:
+    kinterbasdb = dummy
+    
+try:
+    kinterbasdb.init(type_conv=200)
+except kinterbasdb.ProgrammingError:
+    pass #Cannot initialize module more than once.    
+    
+compile = compile.create_child()
+
[e-mail](int, long)
+def compile_int(compile, expr, state):
+    state.parameters.append(IntVariable(expr))
+    return "cast(? as integer)"
+
[e-mail](Select)
+def compile_select_firebird(compile, select, state):
+    limit = select.limit
+    offset = select.offset
+    # Make sure limit is Undef'ed.
+    select.offset = select.limit = Undef
+
+    if select.default_tables is Undef:
+        select.default_tables = ["RDB$DATABASE"]
+
+    sql = compile_select(compile, select, state)
+
+    if limit is not Undef or offset is not Undef:
+        rowstart = 1
+        rowstop = None
+        if offset is not Undef:
+            rowstart = offset + 1
+        if limit is not Undef:
+            rowstop = rowstart + limit - 1
+            if rowstop < rowstart:
+                rowstop = rowstart
+
+        sql += " ROWS %i" % rowstart
+        if rowstop:
+            sql += " TO %i " % rowstop
+        #print sql
+        
+    return sql
+
[e-mail](Sequence)
+def compile_sequence_firebird(compile, sequence, state):
+    return "gen_id(%s, 1)" % sequence.name
+
+class Returning(Expr):
+    """Appends the "RETURNING <primary_columns>" suffix to an INSERT.
+
+    This is only supported in Firebird 2.0
+    """
+    def __init__(self, insert):
+        self.insert = insert
+        
[e-mail](Returning)
+def compile_returning(compile, expr, state):
+    state.push("context", COLUMN)
+    columns = compile(expr.insert.primary_columns, state)
+    state.pop()
+    state.push("precedence", 0)
+    insert = compile(expr.insert, state)
+    state.pop()
+    return "%s RETURNING %s" % (insert, columns)        
+
+class FirebirdResult(Result):
+    @staticmethod
+    def from_database(row):
+        """Convert Firebird-specific datatypes to "normal" Python types.
+
+        If there are any C{array} instances in the row, convert them
+        to strings.
+        """
+        for value in row:
+            yield value
+            
+    def get_insert_identity(self, primary_key, primary_variables):
+        """
+            Firebird does not support insert identity
+            - autoinc is implemented using custom triggers
+              so no clear way to support it in a generic way
+        """
+        raise NotImplementedError
+        
+class FirebirdConnection(Connection):
+
+    result_factory = FirebirdResult
+    param_mark = "?"
+    compile = compile
+    server_version = None
+    
+    def execute(self, statement, params=None, noresult=False):
+        """Execute a statement with the given parameters.
+
+        This extends the L{Connection.execute} method to add support
+        for automatic retrieval of inserted primary keys to link
+        in-memory objects with their specific rows.
+        """
+        if not self.server_version:
+            version = 0
+            version = self._raw_connection.db_info(kinterbasdb.isc_info_version)
+            version = str(version).split("Firebird")[1].strip()
+            version = float(version)
+            self.server_version = version
+        
+        if (isinstance(statement, Insert) and
+            self.server_version >= 2 and
+            statement.primary_variables is not Undef and
+            statement.primary_columns is not Undef):
+
+            # Here we decorate the Insert statement with a Returning
+            # expression, so that we get back in the result the values
+            # for the primary key just inserted.  This prevents a round
+            # trip to the database for obtaining these values.
+            result = Connection.execute(self, Returning(statement), params)
+            for variable, value in zip(statement.primary_variables,
+                                       result.get_one()):
+                result.set_variable(variable, value)
+            return result
+
+        return Connection.execute(self, statement, params, noresult)
+
+    def is_disconnection_error(self, exc):
+        """Check whether an exception represents a database disconnection.
+
+        """
+        if isinstance(exc, ProgrammingError) or isinstance(exc,OperationalError):
+            code,description = exc.args
+            if code == -902:
+                return True
+
+
+        return False
+
+    def to_database(self, params):
+        for param in params:
+            if isinstance(param, Variable):
+                param = param.get(to_db=True)
+            if isinstance(param, timedelta):
+                yield str(param)
+            else:
+                yield param
+                
+class Firebird(Database):
+
+    connection_factory = FirebirdConnection
+    _converters = None
+
+    def __init__(self, uri):
+        if kinterbasdb is dummy:
+            raise DatabaseModuleError("'kinterbasdb' module not found")
+        self._connect_kwargs = {}
+        if uri.database is not None:
+            if os.path.isfile(uri.database):
+                uri.database = os.path.abspath(uri.database)
+            self._connect_kwargs["database"] = uri.database            
+        if uri.host is not None:
+            self._connect_kwargs["host"] = uri.host
+            if uri.port is not None:
+                #firebird expects nonstandard port spec: http://www.firebirdfaq.org/faq259/
+                self._connect_kwargs["host"] = "%s/%s" % (uri.host,uri.port)
+        if uri.port is not None:
+            self._connect_kwargs["port"] = uri.port
+        if uri.username is not None:
+            self._connect_kwargs["user"] = uri.username
+        if uri.password is not None:
+            self._connect_kwargs["password"] = uri.password
+        for option in ["unix_socket"]:
+            if option in uri.options:
+                self._connect_kwargs[option] = uri.options.get(option)
+
+
+
+    def raw_connect(self):
+        customTPB = (
+              kinterbasdb.isc_tpb_write
+            + kinterbasdb.isc_tpb_concurrency
+          )
+        
+        raw_connection = kinterbasdb.connect(**self._connect_kwargs)
+        raw_connection.default_tpb = customTPB
+        
+        return raw_connection
+
+
+create_from_uri = Firebird                
 
=== modified file 'tests/databases/base.py'
--- tests/databases/base.py	2010-04-16 07:12:13 +0000
+++ tests/databases/base.py	2010-06-26 13:26:23 +0000
@@ -132,7 +132,7 @@
         self.assertTrue(result.get_one())
 
     def test_execute_result(self):
-        result = self.connection.execute("SELECT 1")
+        result = self.connection.execute("SELECT 1 FROM TEST")
         self.assertTrue(isinstance(result, Result))
         self.assertTrue(result.get_one())
 
@@ -357,7 +357,7 @@
         event.hook("register-transaction", register_transaction)
 
         connection = self.database.connect(event)
-        connection.execute("SELECT 1")
+        connection.execute("SELECT 1 FROM TEST")
         self.assertEqual(len(calls), 1)
         self.assertEqual(calls[0], marker)
 
@@ -425,16 +425,16 @@
 
     def test_block_access(self):
         """Access to the connection is blocked by block_access()."""
-        self.connection.execute("SELECT 1")
+        self.connection.execute("SELECT 1 FROM TEST")
         self.connection.block_access()
         self.assertRaises(ConnectionBlockedError,
-                          self.connection.execute, "SELECT 1")
+                          self.connection.execute, "SELECT 1 FROM TEST")
         self.assertRaises(ConnectionBlockedError, self.connection.commit)
         # Allow rolling back a blocked connection.
         self.connection.rollback()
         # Unblock the connection, allowing access again.
         self.connection.unblock_access()
-        self.connection.execute("SELECT 1")
+        self.connection.execute("SELECT 1 FROM TEST")
 
 
 class UnsupportedDatabaseTest(object):
@@ -556,20 +556,20 @@
 
     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)
@@ -581,13 +581,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 from test")
             cursor.fetchone()
         except Error, exc:
             self.assertTrue(self.connection.is_disconnection_error(exc))
@@ -614,59 +614,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 FROM TEST")
             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/firebird.py'
--- tests/databases/firebird.py	1970-01-01 00:00:00 +0000
+++ tests/databases/firebird.py	2010-06-26 13:26:23 +0000
@@ -0,0 +1,171 @@
+#
+# Copyright (c) 2010 Canonical
+#
+# Initial Code by Gerdus van Zyl
+#
+# 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
+
+from storm.database import create_database
+from storm.database import *
+
+from tests.helper import TestHelper
+
+from tests.databases.base import (
+    DatabaseTest, UnsupportedDatabaseTest, DatabaseDisconnectionTest)
+
+from storm.variables import (
+    IntVariable, Variable )
+
+from storm.exceptions import (
+    DatabaseError, DatabaseModuleError, 
+    DisconnectionError, Error, OperationalError, ConnectionBlockedError)
+
+from tests.expr import (
+    Select,Column,Insert, column1, column2, column3, elem1,
+    table1, TrackContext,Sequence)
+
+#import storm.tracer
+#import sys
+#storm.tracer.debug(True, stream=sys.stdout)
+
+class FirebirdTest(DatabaseTest, TestHelper):
+    supports_microseconds = False
+    
+    def is_supported(self):
+        return bool(os.environ.get("STORM_FIREBIRD_URI"))
+    
+    def create_database(self):
+        self.database = create_database(os.environ["STORM_FIREBIRD_URI"])
+            
+    def create_tables(self):
+        self.connection.execute('CREATE TABLE NUMBER (one INTEGER, two INTEGER, three INTEGER)')
+        self.connection.execute('CREATE TABLE TEST (id INTEGER PRIMARY KEY, title VARCHAR(50) CHARACTER SET UTF8)')
+        self.connection.execute('CREATE TABLE DATETIME_TEST (id INT UNIQUE,dt TIMESTAMP, d DATE, t TIME, td BLOB SUB_TYPE TEXT)')
+                                
+        self.connection.execute('CREATE TABLE BIN_TEST (id INT UNIQUE,b BLOB)')
+
+        self.connection.execute('CREATE GENERATOR GEN_TEST_AUTOID')
+        self.connection.execute('SET GENERATOR GEN_TEST_AUTOID TO 0')
+
+        self.connection.execute("""CREATE TRIGGER TEST_PK_AUTO FOR TEST ACTIVE BEFORE INSERT POSITION 0 AS
+begin
+  if ( (new.ID is null) or (new.ID = 0) )
+  then new.ID = gen_id(GEN_TEST_AUTOID, 1);
+end""")
+
+        self.connection.execute("""CREATE TABLE insert_returning_test
+                                (id0 INTEGER, 
+                                id1 INTEGER DEFAULT 123, 
+                                 id2 INTEGER DEFAULT 456)""")
+                                
+        self.connection.commit()
+
+    def drop_tables(self):
+        try:
+            self.connection.execute("DROP TRIGGER TEST_PK_AUTO")
+            self.connection.execute("DROP GENERATOR GEN_TEST_AUTOID")
+            self.connection.commit()
+        except:
+            self.connection.rollback()
+
+        for table in ["number", "test", "datetime_test", "bin_test", "insert_returning_test"]:
+            try:
+                self.connection.execute("DROP TABLE " + table)
+                self.connection.commit()
+            except:
+                self.connection.rollback()
+
+    def test_get_insert_identity(self):
+        """test_get_insert_identity -Does not support insert identity"""
+        #http://www.firebirdfaq.org/faq243/
+	pass
+
+    def test_get_insert_identity_composed(self):
+        """test_get_insert_identity_composed - Does not support insert identity"""
+        #http://www.firebirdfaq.org/faq243/
+	pass
+
+    def test_execute_insert_returning(self):
+        if self.connection.server_version < 2:
+            return # Can't run this test with old PostgreSQL versions.
+
+        column0 = Column("id0", "insert_returning_test")
+        column1 = Column("id1", "insert_returning_test")
+        column2 = Column("id2", "insert_returning_test")
+        variable1 = IntVariable()
+        variable2 = IntVariable()
+        insert = Insert({column0: 999}, primary_columns=(column1, column2),
+                            primary_variables=(variable1, variable2))
+        self.connection.execute(insert)
+            
+        self.assertTrue(variable1.is_defined())
+        self.assertTrue(variable2.is_defined())
+
+        self.assertEquals(variable1.get(), 123)
+        self.assertEquals(variable2.get(), 456)
+
+        result = self.connection.execute("SELECT * FROM insert_returning_test")
+        self.assertEquals(result.get_one(), (999,123, 456))
+
+    def test_sequence(self):
+        expr1 = Select(Sequence("GEN_TEST_AUTOID"))
+        expr2 = "SELECT gen_id(GEN_TEST_AUTOID,0) FROM RDB$DATABASE"
+        value1 = self.connection.execute(expr1).get_one()[0]
+        value2 = self.connection.execute(expr2).get_one()[0]
+        value3 = self.connection.execute(expr1).get_one()[0]
+        self.assertEquals(value1, value2)
+        self.assertEquals(value3-value1, 1)
+
+    def test_limit_offset(self):
+        self.connection.execute("delete from test")
+        self.connection.commit()
+
+        for z in range(100,200+1):
+            sql = "INSERT INTO test (id,title) VALUES (%i,'%i')" % (z,z)
+            self.connection.execute(sql)
+        self.connection.commit()
+
+        select = Select(Column("id", "test"))
+        select.limit = 1
+        select.offset = 0
+        select.order_by = Column("id", "test")
+
+        result = self.connection.execute(select)
+        self.assertEquals(result.get_all(), [(100,),] )
+
+        
+        select = Select(Column("id", "test"))
+        select.limit = 2
+        select.offset = 50
+        select.order_by = Column("id", "test")
+
+        result = self.connection.execute(select)
+        self.assertEquals(result.get_all(), [(150,),(151,)] )
+
+
+class FirebirdUnsupportedTest(UnsupportedDatabaseTest, TestHelper):
+    
+    dbapi_module_names = ["kinterbasdb"]
+    db_module_name = "firebird"
+    
+
+class FirebirdDisconnectionTest(DatabaseDisconnectionTest, TestHelper):
+
+    environment_variable = "STORM_FIREBIRD_URI"
+    host_environment_variable = "STORM_FIREBIRD_HOST_URI"
+    default_port = 3050
 
=== modified file 'tests/databases/proxy.py'
--- tests/databases/proxy.py	2007-10-24 06:27:06 +0000
+++ tests/databases/proxy.py	2010-06-26 13:26:23 +0000
@@ -52,14 +52,14 @@
                 return
 
             if self.request in rlist:
-                chunk = os.read(self.request.fileno(), 1024)
+                chunk = self.request.recv(1024)
                 dst.send(chunk)
                 if chunk == "":
                     readers.remove(self.request)
                     dst.shutdown(socket.SHUT_WR)
 
             if dst in rlist:
-                chunk = os.read(dst.fileno(), 1024)
+                chunk = dst.recv(1024)
                 self.request.send(chunk)
                 if chunk == "":
                     readers.remove(dst)