I have been using sqlalchemy with firebird3 for some time on a small
project. Now that Firbeird3 has reached RC2 status, I think it may be
appropriate to submit the attached patches which are based on the
rel_1_0 git branch.
I use the fdb-1.4.9 driver
Regards, Treeve
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
From 326d81ee83d3d7ae5c1bbf73c921d7ae3fd1796d Mon Sep 17 00:00:00 2001
From: Treeve Jelbert <tre...@sourcemage.org>
Date: Sat, 28 Dec 2013 13:51:03 +0100
Subject: [PATCH 1/4] detect firebird version 3
---
lib/sqlalchemy/dialects/firebird/base.py | 58 +++++++++++++++++++++++++++++++-
1 file changed, 57 insertions(+), 1 deletion(-)
diff --git a/lib/sqlalchemy/dialects/firebird/base.py
b/lib/sqlalchemy/dialects/firebird/base.py
index c34829c..1f19628 100644
--- a/lib/sqlalchemy/dialects/firebird/base.py
+++ b/lib/sqlalchemy/dialects/firebird/base.py
@@ -98,7 +98,7 @@ RESERVED_WORDS = set([
"execute", "exists", "exit", "external", "extract", "fetch", "file",
"filter", "float", "for", "foreign", "from", "full", "function",
"gdscode", "generator", "gen_id", "global", "grant", "group",
- "having", "hour", "if", "in", "inactive", "index", "inner",
+ "having", "hour", "identity", "if", "in", "inactive", "index", "inner",
"input_type", "insensitive", "insert", "int", "integer", "into", "is",
"isolation", "join", "key", "leading", "left", "length", "level",
"like", "long", "lower", "manual", "max", "maximum_segment", "merge",
@@ -409,6 +409,7 @@ class FBDialect(default.DefaultDialect):
# will be autodetected off upon
# first connect
_version_two = True
+ _version_three = False
def initialize(self, connection):
super(FBDialect, self).initialize(connection)
@@ -419,6 +420,14 @@ class FBDialect(default.DefaultDialect):
self.server_version_info >= (6, )
)
+ self._version_three = ('firebird' in self.server_version_info and \
+ self.server_version_info >= (3, )
+ )
+ if self._version_three:
+ self.ddl_compiler = FB3DDLCompiler
+ self.execution_ctx_cls = FB3ExecutionContext
+
+
if not self._version_two:
# TODO: whatever other pre < 2.0 stuff goes here
self.ischema_names = ischema_names.copy()
@@ -736,3 +745,50 @@ class FBDialect(default.DefaultDialect):
self.normalize_name(row['field_name']))
return list(indexes.values())
+
+# this dialect is only for Firebird-3
+# the other fdb dialect is for Firebird-2+
+# older versions of Firebird are not supported, nor is InterBase
+class FB3ExecutionContext(default.DefaultExecutionContext):
+ def fire_sequence(self, seq, type_):
+ """Get the next value from the sequence using ``select next value``."""
+
+ return self._execute_scalar(
+ "SELECT NEXT VALUE FOR %s FROM RDB$DATABASE;" % \
+ self.dialect.identifier_preparer.format_sequence(seq),
+ type_
+ )
+
+class FB3DDLCompiler(sql.compiler.DDLCompiler):
+ """Firebird syntactic idiosyncrasies"""
+
+ """Firebird-3.0 adds support for identity columns"""
+ def get_column_specification(self, column, **kwargs):
+ colspec = self.preparer.format_column(column)
+ impl_type = column.type.dialect_impl(self.dialect)
+ colspec += " " + self.dialect.type_compiler.process(column.type)
+
+ if column.primary_key and \
+ column is column.table._autoincrement_column:
+ colspec += " GENERATED BY DEFAULT AS IDENTITY"
+ else:
+ default = self.get_column_default_string(column)
+ if default is not None:
+ colspec += " DEFAULT " + default
+
+ if not column.nullable:
+ colspec += " NOT NULL"
+ return colspec
+
+ def visit_create_sequence(self, create):
+ """Generate a ``CREATE SEQUENCE`` statement for the sequence."""
+
+ # no syntax for these
+ # http://www.firebirdsql.org/manual/generatorguide-sqlsyntax.html
+ if create.element.increment is not None:
+ raise NotImplemented(
+ "Firebird SEQUENCE doesn't support INCREMENT BY")
+
+
+ return "CREATE SEQUENCE %s" % \
+ self.preparer.format_sequence(create.element)
--
2.6.3
From c40c69149352edc51722bf51a2d4000b9707974a Mon Sep 17 00:00:00 2001
From: Treeve Jelbert <tre...@sourcemage.org>
Date: Sun, 10 Mar 2013 13:19:06 +0100
Subject: [PATCH 2/4] fb3 - add native boolean support
Conflicts:
lib/sqlalchemy/dialects/firebird/base.py
---
lib/sqlalchemy/dialects/firebird/base.py | 21 +++++++++++++++------
1 file changed, 15 insertions(+), 6 deletions(-)
diff --git a/lib/sqlalchemy/dialects/firebird/base.py
b/lib/sqlalchemy/dialects/firebird/base.py
index 1f19628..90df438 100644
--- a/lib/sqlalchemy/dialects/firebird/base.py
+++ b/lib/sqlalchemy/dialects/firebird/base.py
@@ -73,19 +73,19 @@ the SQLAlchemy ``returning()`` method, such as::
import datetime
from sqlalchemy import schema as sa_schema
-from sqlalchemy import exc, types as sqltypes, sql, util
+from sqlalchemy import exc, types as sqltypes, sql, util, processors
from sqlalchemy.sql import expression
from sqlalchemy.engine import base, default, reflection
from sqlalchemy.sql import compiler
-from sqlalchemy.types import (BIGINT, BLOB, DATE, FLOAT, INTEGER, NUMERIC,
+from sqlalchemy.types import (BIGINT, BLOB, BOOLEAN, DATE, FLOAT, INTEGER,
NUMERIC,
SMALLINT, TEXT, TIME, TIMESTAMP, Integer)
RESERVED_WORDS = set([
"active", "add", "admin", "after", "all", "alter", "and", "any", "as",
"asc", "ascending", "at", "auto", "avg", "before", "begin", "between",
- "bigint", "bit_length", "blob", "both", "by", "case", "cast", "char",
+ "bigint", "bit_length", "blob", "boolean", "both", "by", "case", "cast",
"char",
"character", "character_length", "char_length", "check", "close",
"collate", "column", "commit", "committed", "computed", "conditional",
"connect", "constraint", "containing", "count", "create", "cross",
@@ -155,11 +155,17 @@ class _FBDateTime(sqltypes.DateTime):
return value
return process
+class _FBBoolean(sqltypes.Boolean):
+ def get_dbapi_type(self, dbapi):
+ return dbapi.BOOLEAN
+
colspecs = {
+ sqltypes.Boolean: _FBBoolean,
sqltypes.DateTime: _FBDateTime
}
ischema_names = {
+ 'BOOLEAN': BOOLEAN,
'SHORT': SMALLINT,
'LONG': INTEGER,
'QUAD': FLOAT,
@@ -180,8 +186,11 @@ ischema_names = {
# _FBDate, etc. as bind/result functionality is required)
class FBTypeCompiler(compiler.GenericTypeCompiler):
- def visit_boolean(self, type_, **kw):
- return self.visit_SMALLINT(type_, **kw)
+ def visit_boolean(self, type_):
+ if self.dialect._version_three:
+ return self.visit_BOOLEAN(type_, **kw)
+ else:
+ return self.visit_SMALLINT(type_, **kw)
def visit_datetime(self, type_, **kw):
return self.visit_TIMESTAMP(type_, **kw)
@@ -426,8 +435,8 @@ class FBDialect(default.DefaultDialect):
if self._version_three:
self.ddl_compiler = FB3DDLCompiler
self.execution_ctx_cls = FB3ExecutionContext
+ self.supports_native_boolean = True
-
if not self._version_two:
# TODO: whatever other pre < 2.0 stuff goes here
self.ischema_names = ischema_names.copy()
--
2.6.3
From 5d001f830c0324dcd3e87143018d7d19738a2a74 Mon Sep 17 00:00:00 2001
From: Treeve Jelbert <tre...@sourcemage.org>
Date: Wed, 15 May 2013 11:04:21 +0200
Subject: [PATCH 3/4] firebird3 now supports 'create sequence .. start with ..'
fb3 now supports INCREMENT [BY]
---
lib/sqlalchemy/dialects/firebird/base.py | 16 +++-------------
1 file changed, 3 insertions(+), 13 deletions(-)
diff --git a/lib/sqlalchemy/dialects/firebird/base.py
b/lib/sqlalchemy/dialects/firebird/base.py
index 90df438..8afa5c7 100644
--- a/lib/sqlalchemy/dialects/firebird/base.py
+++ b/lib/sqlalchemy/dialects/firebird/base.py
@@ -347,6 +347,9 @@ class FBDDLCompiler(sql.compiler.DDLCompiler):
"Firebird SEQUENCE doesn't support INCREMENT BY")
if self.dialect._version_two:
+ if create.element.start is not None:
+ raise NotImplemented(
+ "Firebird SEQUENCE doesn't support START WITH")
return "CREATE SEQUENCE %s" % \
self.preparer.format_sequence(create.element)
else:
@@ -788,16 +791,3 @@ class FB3DDLCompiler(sql.compiler.DDLCompiler):
if not column.nullable:
colspec += " NOT NULL"
return colspec
-
- def visit_create_sequence(self, create):
- """Generate a ``CREATE SEQUENCE`` statement for the sequence."""
-
- # no syntax for these
- # http://www.firebirdsql.org/manual/generatorguide-sqlsyntax.html
- if create.element.increment is not None:
- raise NotImplemented(
- "Firebird SEQUENCE doesn't support INCREMENT BY")
-
-
- return "CREATE SEQUENCE %s" % \
- self.preparer.format_sequence(create.element)
--
2.6.3
From 96762d3c7ae20453eb57629b25559b6be146f85f Mon Sep 17 00:00:00 2001
From: Treeve Jelbert <tre...@sourcemage.org>
Date: Wed, 28 Aug 2013 11:50:15 +0200
Subject: [PATCH 4/4] fb3 reserved words
---
lib/sqlalchemy/dialects/firebird/base.py | 6 +++++-
1 file changed, 5 insertions(+), 1 deletion(-)
diff --git a/lib/sqlalchemy/dialects/firebird/base.py
b/lib/sqlalchemy/dialects/firebird/base.py
index 8afa5c7..0f74adc 100644
--- a/lib/sqlalchemy/dialects/firebird/base.py
+++ b/lib/sqlalchemy/dialects/firebird/base.py
@@ -98,7 +98,7 @@ RESERVED_WORDS = set([
"execute", "exists", "exit", "external", "extract", "fetch", "file",
"filter", "float", "for", "foreign", "from", "full", "function",
"gdscode", "generator", "gen_id", "global", "grant", "group",
- "having", "hour", "identity", "if", "in", "inactive", "index", "inner",
+ "having", "hour", "if", "in", "inactive", "index", "inner",
"input_type", "insensitive", "insert", "int", "integer", "into", "is",
"isolation", "join", "key", "leading", "left", "length", "level",
"like", "long", "lower", "manual", "max", "maximum_segment", "merge",
@@ -119,6 +119,10 @@ RESERVED_WORDS = set([
"union", "unique", "update", "upper", "user", "using", "value",
"values", "varchar", "variable", "varying", "view", "wait", "when",
"where", "while", "with", "work", "write", "year",
+ "similar", #2.5
+ "corr", "deleting" , "deterministic" , "false" , "inserting" , #3.0
+ "offset", "over", "return", "row" , "scroll", "sqlstate", "true" , #3.0
+ "unknown" , "updating" , # 3.0
])
--
2.6.3
