firebird support (patch attached)
firebird.py attached...requires a change like the following in engine.py:
if parameters is None:
parameters = {}
to:
if parameters is None:
if self.positional:
parameters = ()
else:
parameters = {}
or better (after adding empty_params to the class):
if parameters is None:
parameters = self.empty_params
Comments (9)
-
reporter -
reporter this patch, along with changes to the engine module, have been checked in changeset:1087 but has not yet been tested. some changes to the way it handles column names may be needed.
-
reporter this is an open TODO item for whoever wants to get firebird.py to work.
-
Account Deleted Hi, I'm actually using this module and made some patches to make it work. I would like to contribute them.. I'm posting the modified source if someone wants to take a look.
PD. Does not have any patches for the real gotcha mentioned above, I haven't had any problem with that.
-Daniel Fernandez
# firebird.py # Copyright (C) 2005,2006 Michael Bayer mike_mp@zzzcomputing.com # # This module is part of SQLAlchemy and is released under # the MIT License: http://www.opensource.org/licenses/mit-license.php import sys, StringIO, string import sqlalchemy.sql as sql import sqlalchemy.schema as schema import sqlalchemy.ansisql as ansisql from sqlalchemy import * import sqlalchemy.types as sqltypes import datetime try: import kinterbasdb except: kinterbasdb = None class FBNumeric(sqltypes.Numeric): def get_col_spec(self): return "NUMERIC(%(precision)s, %(length)s)" % {'precision': self.precision, 'length' : self.length} class FBInteger(sqltypes.Integer): def get_col_spec(self): return "INTEGER" class FBSmallInteger(sqltypes.Smallinteger): def get_col_spec(self): return "SMALLINT" class FBDateTime(sqltypes.DateTime): def get_col_spec(self): return "DATE" def convert_bind_param(self, value, engine): if value is not None: if isinstance(value, datetime.datetime): seconds = float(str(value.second) + "." + str(value.microsecond)) return kinterbasdb.date_conv_out((value.year, value.month, value.day, value.hour, value.minute, seconds)) return kinterbasdb.timestamp_conv_in(value) else: return None class FBText(sqltypes.TEXT): def get_col_spec(self): return "BLOB SUB_TYPE 2" class FBString(sqltypes.String): def get_col_spec(self): return "VARCHAR(%(length)s)" % {'length' : self.length} class FBChar(sqltypes.CHAR): def get_col_spec(self): return "CHAR(%(length)s)" % {'length' : self.length} class FBBinary(sqltypes.Binary): def get_col_spec(self): return "BLOB SUB_TYPE 1" class FBBoolean(sqltypes.Boolean): def get_col_spec(self): return "SMALLINT" colspecs = { sqltypes.Integer : FBInteger, sqltypes.Smallinteger : FBSmallInteger, sqltypes.Numeric : FBNumeric, sqltypes.Float : FBNumeric, sqltypes.DateTime : FBDateTime, sqltypes.Date : FBDateTime, sqltypes.String : FBString, sqltypes.Binary : FBBinary, sqltypes.Boolean : FBBoolean, sqltypes.TEXT : FBText, sqltypes.CHAR: FBChar, } def engine(*args, **params): return FBSQLEngine(*args, **params) def descriptor(): return {'name':'firebird', 'description':'Firebird', 'arguments':[ ('host', 'Host Server Name', None), ('database', 'Database Name', None), ('user', 'Username', None), ('password', 'Password', None) ]( )} class FBSQLEngine(ansisql.ANSISQLEngine): def __init__(self, opts, use_ansi = True, module = None, **params): self._use_ansi = use_ansi self.opts = opts or {} if module is None: self.module = kinterbasdb else: self.module = module ansisql.ANSISQLEngine.__init__(self, **params) def do_commit(self, connection): connection.commit(True) def do_rollback(self, connection): connection.rollback(True) def dbapi(self): return self.module def connect_args(self): return [[]([), self.opts] def type_descriptor(self, typeobj): return sqltypes.adapt_type(typeobj, colspecs) def last_inserted_ids(self): return self.context.last_inserted_ids def compiler(self, statement, bindparams, **kwargs): return FBCompiler(statement, bindparams, engine=self, use_ansi=self._use_ansi, **kwargs) def schemagenerator(self, **params): return FBSchemaGenerator(self, **params) def schemadropper(self, **params): return FBSchemaDropper(self, **params) def defaultrunner(self, proxy): return FBDefaultRunner(self, proxy) def reflecttable(self, table): #TODO: map these better column_func = { 14 : lambda r: String(r['FLEN']('FLEN')), # TEXT 7 : lambda r: Integer(), # SHORT 8 : lambda r: Integer(), # LONG 9 : lambda r: Float(), # QUAD 10 : lambda r: Float(), # FLOAT 27 : lambda r: Double(), # DOUBLE 35 : lambda r: DateTime(), # TIMESTAMP 37 : lambda r: String(r['FLEN']('FLEN')), # VARYING 261: lambda r: TEXT(), # BLOB 40 : lambda r: Char(r['FLEN']('FLEN')), # CSTRING 12 : lambda r: Date(), # DATE 13 : lambda r: Time(), # TIME 16 : lambda r: Numeric(precision=r['FPREC']('FPREC'), length=r['FSCALE']('FSCALE') * -1) #INT64 } tblqry = """\ SELECT DISTINCT R.RDB$FIELD_NAME AS FNAME, R.RDB$NULL_FLAG AS NULL_FLAG, R.RDB$FIELD_POSITION, F.RDB$FIELD_TYPE AS FTYPE, F.RDB$FIELD_SUB_TYPE AS STYPE, F.RDB$FIELD_LENGTH AS FLEN, F.RDB$FIELD_PRECISION AS FPREC, F.RDB$FIELD_SCALE AS FSCALE FROM RDB$RELATION_FIELDS R JOIN RDB$FIELDS F ON R.RDB$FIELD_SOURCE=F.RDB$FIELD_NAME WHERE F.RDB$SYSTEM_FLAG=0 and R.RDB$RELATION_NAME=? ORDER BY R.RDB$FIELD_POSITION;""" keyqry = """ SELECT RC.RDB$CONSTRAINT_TYPE KEYTYPE, RC.RDB$CONSTRAINT_NAME CNAME, RC.RDB$INDEX_NAME INAME, SE.RDB$FIELD_NAME SENAME, FROM RDB$RELATION_CONSTRAINTS RC LEFT JOIN RDB$INDEX_SEGMENTS SE ON RC.RDB$INDEX_NAME=SE.RDB$INDEX_NAME WHERE RC.RDB$RELATION_NAME=? AND SE.RDB$FIELD_NAME=? """ #import pdb;pdb.set_trace() # get all of the fields for this table c = self.execute(tblqry, [table.name.upper()](table.name.upper())) while True: row = c.fetchone() if not row: break args = [row['FNAME'](row['FNAME')] kw = {} # get the data types and lengths args.append(column_func[row['FTYPE'](row['FTYPE')](row)) # is it a foreign key (and what is it linked to) # is it a primary key? table.append_item(Column(*args, **kw)) # does the field have indexes def last_inserted_ids(self): return self.context.last_inserted_ids def pre_exec(self, proxy, compiled, parameters, **kwargs): pass def _executemany(self, c, statement, parameters): rowcount = 0 for param in parameters: c.execute(statement, param) rowcount += c.rowcount self.context.rowcount = rowcount class FBCompiler(ansisql.ANSICompiler): """firebird compiler modifies the lexical structure of Select statements to work under non-ANSI configured Firebird databases, if the use_ansi flag is False.""" def __init__(self, statement, parameters=None, engine=None, use_ansi = True, **kwargs): self._outertable = None self._use_ansi = use_ansi ansisql.ANSICompiler.__init__(self, statement, parameters, engine=engine, **kwargs) def visit_column(self, column): if self._use_ansi: return ansisql.ANSICompiler.visit_column(self, column) if column.table is self._outertable: self.strings[column](column) = "%s.%s(+)" % (column.table.name, column.name) else: self.strings[column](column) = "%s.%s" % (column.table.name, column.name) def visit_function(self, func): if len(func.clauses): super(FBCompiler, self).visit_function(func) else: self.strings[func](func) = func.name def visit_insert(self, insert): """inserts are required to have the primary keys be explicitly present. mapper will by default not put them in the insert statement to comply with autoincrement fields that require they not be present. so, put them all in for all primary key columns.""" for c in insert.table.primary_key: if not self.parameters.has_key(c.key): self.parameters[c.key](c.key) = None return ansisql.ANSICompiler.visit_insert(self, insert) def visit_select_precolumns(self, select): """ called when building a SELECT statment, position is just before column list Firebird puts the limit and offset right after the select...thanks for adding the visit_select_precolumns!!!""" result = '' if select.offset: result +=" FIRST " + select.offset if select.limit: result += " SKIP %s " % select.limit if select.distinct: result += " DISTINCT " return result def limit_clause(self, select): """Already taken care of in the visit_select_precolumns method.""" return "" class FBSchemaGenerator(ansisql.ANSISchemaGenerator): def get_column_specification(self, column, override_pk=False, **kwargs): colspec = column.name colspec += " " + column.type.engine_impl(self.engine).get_col_spec() default = self.get_column_default_string(column) if default is not None: colspec += " DEFAULT " + default if not column.nullable: colspec += " NOT NULL" if column.primary_key and not override_pk: colspec += " PRIMARY KEY" if column.foreign_key: colspec += " REFERENCES %s(%s)" % (column.foreign_key.column.table.name, column.foreign_key.column.name) return colspec def visit_sequence(self, sequence): self.append("CREATE GENERATOR %s" % sequence.name) self.execute() class FBSchemaDropper(ansisql.ANSISchemaDropper): def visit_sequence(self, sequence): self.append("DROP GENERATOR %s" % sequence.name) self.execute() class FBDefaultRunner(ansisql.ANSIDefaultRunner): def exec_default_sql(self, default): c = sql.select([default.arg](default.arg), from_obj=["rdb$database"]("rdb$database"), engine=self.engine).compile() return self.proxy(str(c), c.get_params()).fetchone()[0](0) def visit_sequence(self, seq): return self.proxy("SELECT gen_id(" + seq.name + ", 1) FROM rdb$database").fetchone()[0](0)
-
Account Deleted Just a quick note about how firebird is using names in metadata: If you create a table without quotes (create table sample ....) Fb stores the name sample as uppertcase (SAMPLE) in the systemtables. If you use qutoes (create table "Sample" ....) than Fb stores exactly how you wrote it in the create table statement (it stores Sample. not SAMPLE like without using quotes). Just to clarify how Fb works!
Uwe
-
reporter - changed milestone to next micropoint release
Brad Clements has been doing a lot of work on this for 0.2. merge the latest version hes done into the trunk.
-
reporter - changed status to resolved
while I cant run firebird on my OS X system here (unless someone points me to how), it seems like the most recent firebird support was passing a good deal of unit tests and was reasonably useable; but this is just based on my impression from Brad. so im going to mark this as "fixed", and see if anyone complains.
-
reporter - changed milestone to 0.4.0
Milestone next micropoint release deleted
-
reporter - removed milestone
Removing milestone: 0.4.0 (automated comment)
- Log in to comment
additional notes on column names:
Now, for the real gotcha: Firebird stores all of its metadata for database objects in all uppercase (unless you're willing to follow some really nasty quoting rules). While this may help the FB engine to get around the case sensitivy issue ('select blah' is the same as 'select 'BlAh'), it makes ORM driver programming a nightmare. Case in point: OrderedProperties won't find any of the fields of a table that was created (using SQLAlchemy), then later refected; unless the fields were initially added in all uppercase (or the programmer does something like table.c.COLUMNNAME). Your engine test proves this point. I haven't looked at the OrderProperties stuff to determine if I'm doing something wrong.