[3] | 1 | # firebird.py |
---|
| 2 | # Copyright (C) 2005, 2006, 2007, 2008, 2009 Michael Bayer mike_mp@zzzcomputing.com |
---|
| 3 | # |
---|
| 4 | # This module is part of SQLAlchemy and is released under |
---|
| 5 | # the MIT License: http://www.opensource.org/licenses/mit-license.php |
---|
| 6 | |
---|
| 7 | """ |
---|
| 8 | Firebird backend |
---|
| 9 | ================ |
---|
| 10 | |
---|
| 11 | This module implements the Firebird backend, thru the kinterbasdb_ |
---|
| 12 | DBAPI module. |
---|
| 13 | |
---|
| 14 | Firebird dialects |
---|
| 15 | ----------------- |
---|
| 16 | |
---|
| 17 | Firebird offers two distinct dialects_ (not to be confused with the |
---|
| 18 | SA ``Dialect`` thing): |
---|
| 19 | |
---|
| 20 | dialect 1 |
---|
| 21 | This is the old syntax and behaviour, inherited from Interbase pre-6.0. |
---|
| 22 | |
---|
| 23 | dialect 3 |
---|
| 24 | This is the newer and supported syntax, introduced in Interbase 6.0. |
---|
| 25 | |
---|
| 26 | From the user point of view, the biggest change is in date/time |
---|
| 27 | handling: under dialect 1, there's a single kind of field, ``DATE`` |
---|
| 28 | with a synonim ``DATETIME``, that holds a `timestamp` value, that is a |
---|
| 29 | date with hour, minute, second. Under dialect 3 there are three kinds, |
---|
| 30 | a ``DATE`` that holds a date, a ``TIME`` that holds a *time of the |
---|
| 31 | day* value and a ``TIMESTAMP``, equivalent to the old ``DATE``. |
---|
| 32 | |
---|
| 33 | The problem is that the dialect of a Firebird database is a property |
---|
| 34 | of the database itself [#]_ (that is, any single database has been |
---|
| 35 | created with one dialect or the other: there is no way to change the |
---|
| 36 | after creation). SQLAlchemy has a single instance of the class that |
---|
| 37 | controls all the connections to a particular kind of database, so it |
---|
| 38 | cannot easily differentiate between the two modes, and in particular |
---|
| 39 | it **cannot** simultaneously talk with two distinct Firebird databases |
---|
| 40 | with different dialects. |
---|
| 41 | |
---|
| 42 | By default this module is biased toward dialect 3, but you can easily |
---|
| 43 | tweak it to handle dialect 1 if needed:: |
---|
| 44 | |
---|
| 45 | from sqlalchemy import types as sqltypes |
---|
| 46 | from sqlalchemy.databases.firebird import FBDate, colspecs, ischema_names |
---|
| 47 | |
---|
| 48 | # Adjust the mapping of the timestamp kind |
---|
| 49 | ischema_names['TIMESTAMP'] = FBDate |
---|
| 50 | colspecs[sqltypes.DateTime] = FBDate, |
---|
| 51 | |
---|
| 52 | Other aspects may be version-specific. You can use the ``server_version_info()`` method |
---|
| 53 | on the ``FBDialect`` class to do whatever is needed:: |
---|
| 54 | |
---|
| 55 | from sqlalchemy.databases.firebird import FBCompiler |
---|
| 56 | |
---|
| 57 | if engine.dialect.server_version_info(connection) < (2,0): |
---|
| 58 | # Change the name of the function ``length`` to use the UDF version |
---|
| 59 | # instead of ``char_length`` |
---|
| 60 | FBCompiler.LENGTH_FUNCTION_NAME = 'strlen' |
---|
| 61 | |
---|
| 62 | Pooling connections |
---|
| 63 | ------------------- |
---|
| 64 | |
---|
| 65 | The default strategy used by SQLAlchemy to pool the database connections |
---|
| 66 | in particular cases may raise an ``OperationalError`` with a message |
---|
| 67 | `"object XYZ is in use"`. This happens on Firebird when there are two |
---|
| 68 | connections to the database, one is using, or has used, a particular table |
---|
| 69 | and the other tries to drop or alter the same table. To garantee DDL |
---|
| 70 | operations success Firebird recommend doing them as the single connected user. |
---|
| 71 | |
---|
| 72 | In case your SA application effectively needs to do DDL operations while other |
---|
| 73 | connections are active, the following setting may alleviate the problem:: |
---|
| 74 | |
---|
| 75 | from sqlalchemy import pool |
---|
| 76 | from sqlalchemy.databases.firebird import dialect |
---|
| 77 | |
---|
| 78 | # Force SA to use a single connection per thread |
---|
| 79 | dialect.poolclass = pool.SingletonThreadPool |
---|
| 80 | |
---|
| 81 | RETURNING support |
---|
| 82 | ----------------- |
---|
| 83 | |
---|
| 84 | Firebird 2.0 supports returning a result set from inserts, and 2.1 extends |
---|
| 85 | that to deletes and updates. |
---|
| 86 | |
---|
| 87 | To use this pass the column/expression list to the ``firebird_returning`` |
---|
| 88 | parameter when creating the queries:: |
---|
| 89 | |
---|
| 90 | raises = tbl.update(empl.c.sales > 100, values=dict(salary=empl.c.salary * 1.1), |
---|
| 91 | firebird_returning=[empl.c.id, empl.c.salary]).execute().fetchall() |
---|
| 92 | |
---|
| 93 | |
---|
| 94 | .. [#] Well, that is not the whole story, as the client may still ask |
---|
| 95 | a different (lower) dialect... |
---|
| 96 | |
---|
| 97 | .. _dialects: http://mc-computing.com/Databases/Firebird/SQL_Dialect.html |
---|
| 98 | .. _kinterbasdb: http://sourceforge.net/projects/kinterbasdb |
---|
| 99 | """ |
---|
| 100 | |
---|
| 101 | |
---|
| 102 | import datetime, decimal, re |
---|
| 103 | |
---|
| 104 | from sqlalchemy import exc, schema, types as sqltypes, sql, util |
---|
| 105 | from sqlalchemy.engine import base, default |
---|
| 106 | |
---|
| 107 | |
---|
| 108 | _initialized_kb = False |
---|
| 109 | |
---|
| 110 | |
---|
| 111 | class FBNumeric(sqltypes.Numeric): |
---|
| 112 | """Handle ``NUMERIC(precision,scale)`` datatype.""" |
---|
| 113 | |
---|
| 114 | def get_col_spec(self): |
---|
| 115 | if self.precision is None: |
---|
| 116 | return "NUMERIC" |
---|
| 117 | else: |
---|
| 118 | return "NUMERIC(%(precision)s, %(scale)s)" % { 'precision': self.precision, |
---|
| 119 | 'scale' : self.scale } |
---|
| 120 | |
---|
| 121 | def bind_processor(self, dialect): |
---|
| 122 | return None |
---|
| 123 | |
---|
| 124 | def result_processor(self, dialect): |
---|
| 125 | if self.asdecimal: |
---|
| 126 | return None |
---|
| 127 | else: |
---|
| 128 | def process(value): |
---|
| 129 | if isinstance(value, decimal.Decimal): |
---|
| 130 | return float(value) |
---|
| 131 | else: |
---|
| 132 | return value |
---|
| 133 | return process |
---|
| 134 | |
---|
| 135 | |
---|
| 136 | class FBFloat(sqltypes.Float): |
---|
| 137 | """Handle ``FLOAT(precision)`` datatype.""" |
---|
| 138 | |
---|
| 139 | def get_col_spec(self): |
---|
| 140 | if not self.precision: |
---|
| 141 | return "FLOAT" |
---|
| 142 | else: |
---|
| 143 | return "FLOAT(%(precision)s)" % {'precision': self.precision} |
---|
| 144 | |
---|
| 145 | |
---|
| 146 | class FBInteger(sqltypes.Integer): |
---|
| 147 | """Handle ``INTEGER`` datatype.""" |
---|
| 148 | |
---|
| 149 | def get_col_spec(self): |
---|
| 150 | return "INTEGER" |
---|
| 151 | |
---|
| 152 | |
---|
| 153 | class FBSmallInteger(sqltypes.Smallinteger): |
---|
| 154 | """Handle ``SMALLINT`` datatype.""" |
---|
| 155 | |
---|
| 156 | def get_col_spec(self): |
---|
| 157 | return "SMALLINT" |
---|
| 158 | |
---|
| 159 | |
---|
| 160 | class FBDateTime(sqltypes.DateTime): |
---|
| 161 | """Handle ``TIMESTAMP`` datatype.""" |
---|
| 162 | |
---|
| 163 | def get_col_spec(self): |
---|
| 164 | return "TIMESTAMP" |
---|
| 165 | |
---|
| 166 | def bind_processor(self, dialect): |
---|
| 167 | def process(value): |
---|
| 168 | if value is None or isinstance(value, datetime.datetime): |
---|
| 169 | return value |
---|
| 170 | else: |
---|
| 171 | return datetime.datetime(year=value.year, |
---|
| 172 | month=value.month, |
---|
| 173 | day=value.day) |
---|
| 174 | return process |
---|
| 175 | |
---|
| 176 | |
---|
| 177 | class FBDate(sqltypes.DateTime): |
---|
| 178 | """Handle ``DATE`` datatype.""" |
---|
| 179 | |
---|
| 180 | def get_col_spec(self): |
---|
| 181 | return "DATE" |
---|
| 182 | |
---|
| 183 | |
---|
| 184 | class FBTime(sqltypes.Time): |
---|
| 185 | """Handle ``TIME`` datatype.""" |
---|
| 186 | |
---|
| 187 | def get_col_spec(self): |
---|
| 188 | return "TIME" |
---|
| 189 | |
---|
| 190 | |
---|
| 191 | class FBText(sqltypes.Text): |
---|
| 192 | """Handle ``BLOB SUB_TYPE 1`` datatype (aka *textual* blob).""" |
---|
| 193 | |
---|
| 194 | def get_col_spec(self): |
---|
| 195 | return "BLOB SUB_TYPE 1" |
---|
| 196 | |
---|
| 197 | |
---|
| 198 | class FBString(sqltypes.String): |
---|
| 199 | """Handle ``VARCHAR(length)`` datatype.""" |
---|
| 200 | |
---|
| 201 | def get_col_spec(self): |
---|
| 202 | if self.length: |
---|
| 203 | return "VARCHAR(%(length)s)" % {'length' : self.length} |
---|
| 204 | else: |
---|
| 205 | return "BLOB SUB_TYPE 1" |
---|
| 206 | |
---|
| 207 | |
---|
| 208 | class FBChar(sqltypes.CHAR): |
---|
| 209 | """Handle ``CHAR(length)`` datatype.""" |
---|
| 210 | |
---|
| 211 | def get_col_spec(self): |
---|
| 212 | if self.length: |
---|
| 213 | return "CHAR(%(length)s)" % {'length' : self.length} |
---|
| 214 | else: |
---|
| 215 | return "BLOB SUB_TYPE 1" |
---|
| 216 | |
---|
| 217 | |
---|
| 218 | class FBBinary(sqltypes.Binary): |
---|
| 219 | """Handle ``BLOB SUB_TYPE 0`` datatype (aka *binary* blob).""" |
---|
| 220 | |
---|
| 221 | def get_col_spec(self): |
---|
| 222 | return "BLOB SUB_TYPE 0" |
---|
| 223 | |
---|
| 224 | |
---|
| 225 | class FBBoolean(sqltypes.Boolean): |
---|
| 226 | """Handle boolean values as a ``SMALLINT`` datatype.""" |
---|
| 227 | |
---|
| 228 | def get_col_spec(self): |
---|
| 229 | return "SMALLINT" |
---|
| 230 | |
---|
| 231 | |
---|
| 232 | colspecs = { |
---|
| 233 | sqltypes.Integer : FBInteger, |
---|
| 234 | sqltypes.Smallinteger : FBSmallInteger, |
---|
| 235 | sqltypes.Numeric : FBNumeric, |
---|
| 236 | sqltypes.Float : FBFloat, |
---|
| 237 | sqltypes.DateTime : FBDateTime, |
---|
| 238 | sqltypes.Date : FBDate, |
---|
| 239 | sqltypes.Time : FBTime, |
---|
| 240 | sqltypes.String : FBString, |
---|
| 241 | sqltypes.Binary : FBBinary, |
---|
| 242 | sqltypes.Boolean : FBBoolean, |
---|
| 243 | sqltypes.Text : FBText, |
---|
| 244 | sqltypes.CHAR: FBChar, |
---|
| 245 | } |
---|
| 246 | |
---|
| 247 | |
---|
| 248 | ischema_names = { |
---|
| 249 | 'SHORT': lambda r: FBSmallInteger(), |
---|
| 250 | 'LONG': lambda r: FBInteger(), |
---|
| 251 | 'QUAD': lambda r: FBFloat(), |
---|
| 252 | 'FLOAT': lambda r: FBFloat(), |
---|
| 253 | 'DATE': lambda r: FBDate(), |
---|
| 254 | 'TIME': lambda r: FBTime(), |
---|
| 255 | 'TEXT': lambda r: FBString(r['flen']), |
---|
| 256 | 'INT64': lambda r: FBNumeric(precision=r['fprec'], scale=r['fscale'] * -1), # This generically handles NUMERIC() |
---|
| 257 | 'DOUBLE': lambda r: FBFloat(), |
---|
| 258 | 'TIMESTAMP': lambda r: FBDateTime(), |
---|
| 259 | 'VARYING': lambda r: FBString(r['flen']), |
---|
| 260 | 'CSTRING': lambda r: FBChar(r['flen']), |
---|
| 261 | 'BLOB': lambda r: r['stype']==1 and FBText() or FBBinary() |
---|
| 262 | } |
---|
| 263 | |
---|
| 264 | RETURNING_KW_NAME = 'firebird_returning' |
---|
| 265 | |
---|
| 266 | class FBExecutionContext(default.DefaultExecutionContext): |
---|
| 267 | pass |
---|
| 268 | |
---|
| 269 | |
---|
| 270 | class FBDialect(default.DefaultDialect): |
---|
| 271 | """Firebird dialect""" |
---|
| 272 | name = 'firebird' |
---|
| 273 | supports_sane_rowcount = False |
---|
| 274 | supports_sane_multi_rowcount = False |
---|
| 275 | max_identifier_length = 31 |
---|
| 276 | preexecute_pk_sequences = True |
---|
| 277 | supports_pk_autoincrement = False |
---|
| 278 | |
---|
| 279 | def __init__(self, type_conv=200, concurrency_level=1, **kwargs): |
---|
| 280 | default.DefaultDialect.__init__(self, **kwargs) |
---|
| 281 | |
---|
| 282 | self.type_conv = type_conv |
---|
| 283 | self.concurrency_level = concurrency_level |
---|
| 284 | |
---|
| 285 | def dbapi(cls): |
---|
| 286 | import kinterbasdb |
---|
| 287 | return kinterbasdb |
---|
| 288 | dbapi = classmethod(dbapi) |
---|
| 289 | |
---|
| 290 | def create_connect_args(self, url): |
---|
| 291 | opts = url.translate_connect_args(username='user') |
---|
| 292 | if opts.get('port'): |
---|
| 293 | opts['host'] = "%s/%s" % (opts['host'], opts['port']) |
---|
| 294 | del opts['port'] |
---|
| 295 | opts.update(url.query) |
---|
| 296 | |
---|
| 297 | type_conv = opts.pop('type_conv', self.type_conv) |
---|
| 298 | concurrency_level = opts.pop('concurrency_level', self.concurrency_level) |
---|
| 299 | global _initialized_kb |
---|
| 300 | if not _initialized_kb and self.dbapi is not None: |
---|
| 301 | _initialized_kb = True |
---|
| 302 | self.dbapi.init(type_conv=type_conv, concurrency_level=concurrency_level) |
---|
| 303 | return ([], opts) |
---|
| 304 | |
---|
| 305 | def type_descriptor(self, typeobj): |
---|
| 306 | return sqltypes.adapt_type(typeobj, colspecs) |
---|
| 307 | |
---|
| 308 | def server_version_info(self, connection): |
---|
| 309 | """Get the version of the Firebird server used by a connection. |
---|
| 310 | |
---|
| 311 | Returns a tuple of (`major`, `minor`, `build`), three integers |
---|
| 312 | representing the version of the attached server. |
---|
| 313 | """ |
---|
| 314 | |
---|
| 315 | # This is the simpler approach (the other uses the services api), |
---|
| 316 | # that for backward compatibility reasons returns a string like |
---|
| 317 | # LI-V6.3.3.12981 Firebird 2.0 |
---|
| 318 | # where the first version is a fake one resembling the old |
---|
| 319 | # Interbase signature. This is more than enough for our purposes, |
---|
| 320 | # as this is mainly (only?) used by the testsuite. |
---|
| 321 | |
---|
| 322 | from re import match |
---|
| 323 | |
---|
| 324 | fbconn = connection.connection.connection |
---|
| 325 | version = fbconn.server_version |
---|
| 326 | m = match('\w+-V(\d+)\.(\d+)\.(\d+)\.(\d+) \w+ (\d+)\.(\d+)', version) |
---|
| 327 | if not m: |
---|
| 328 | raise AssertionError("Could not determine version from string '%s'" % version) |
---|
| 329 | return tuple([int(x) for x in m.group(5, 6, 4)]) |
---|
| 330 | |
---|
| 331 | def _normalize_name(self, name): |
---|
| 332 | """Convert the name to lowercase if it is possible""" |
---|
| 333 | |
---|
| 334 | # Remove trailing spaces: FB uses a CHAR() type, |
---|
| 335 | # that is padded with spaces |
---|
| 336 | name = name and name.rstrip() |
---|
| 337 | if name is None: |
---|
| 338 | return None |
---|
| 339 | elif name.upper() == name and not self.identifier_preparer._requires_quotes(name.lower()): |
---|
| 340 | return name.lower() |
---|
| 341 | else: |
---|
| 342 | return name |
---|
| 343 | |
---|
| 344 | def _denormalize_name(self, name): |
---|
| 345 | """Revert a *normalized* name to its uppercase equivalent""" |
---|
| 346 | |
---|
| 347 | if name is None: |
---|
| 348 | return None |
---|
| 349 | elif name.lower() == name and not self.identifier_preparer._requires_quotes(name.lower()): |
---|
| 350 | return name.upper() |
---|
| 351 | else: |
---|
| 352 | return name |
---|
| 353 | |
---|
| 354 | def table_names(self, connection, schema): |
---|
| 355 | """Return a list of *normalized* table names omitting system relations.""" |
---|
| 356 | |
---|
| 357 | s = """ |
---|
| 358 | SELECT r.rdb$relation_name |
---|
| 359 | FROM rdb$relations r |
---|
| 360 | WHERE r.rdb$system_flag=0 |
---|
| 361 | """ |
---|
| 362 | return [self._normalize_name(row[0]) for row in connection.execute(s)] |
---|
| 363 | |
---|
| 364 | def has_table(self, connection, table_name, schema=None): |
---|
| 365 | """Return ``True`` if the given table exists, ignoring the `schema`.""" |
---|
| 366 | |
---|
| 367 | tblqry = """ |
---|
| 368 | SELECT 1 FROM rdb$database |
---|
| 369 | WHERE EXISTS (SELECT rdb$relation_name |
---|
| 370 | FROM rdb$relations |
---|
| 371 | WHERE rdb$relation_name=?) |
---|
| 372 | """ |
---|
| 373 | c = connection.execute(tblqry, [self._denormalize_name(table_name)]) |
---|
| 374 | row = c.fetchone() |
---|
| 375 | if row is not None: |
---|
| 376 | return True |
---|
| 377 | else: |
---|
| 378 | return False |
---|
| 379 | |
---|
| 380 | def has_sequence(self, connection, sequence_name): |
---|
| 381 | """Return ``True`` if the given sequence (generator) exists.""" |
---|
| 382 | |
---|
| 383 | genqry = """ |
---|
| 384 | SELECT 1 FROM rdb$database |
---|
| 385 | WHERE EXISTS (SELECT rdb$generator_name |
---|
| 386 | FROM rdb$generators |
---|
| 387 | WHERE rdb$generator_name=?) |
---|
| 388 | """ |
---|
| 389 | c = connection.execute(genqry, [self._denormalize_name(sequence_name)]) |
---|
| 390 | row = c.fetchone() |
---|
| 391 | if row is not None: |
---|
| 392 | return True |
---|
| 393 | else: |
---|
| 394 | return False |
---|
| 395 | |
---|
| 396 | def is_disconnect(self, e): |
---|
| 397 | if isinstance(e, self.dbapi.OperationalError): |
---|
| 398 | return 'Unable to complete network request to host' in str(e) |
---|
| 399 | elif isinstance(e, self.dbapi.ProgrammingError): |
---|
| 400 | msg = str(e) |
---|
| 401 | return ('Invalid connection state' in msg or |
---|
| 402 | 'Invalid cursor state' in msg) |
---|
| 403 | else: |
---|
| 404 | return False |
---|
| 405 | |
---|
| 406 | def reflecttable(self, connection, table, include_columns): |
---|
| 407 | # Query to extract the details of all the fields of the given table |
---|
| 408 | tblqry = """ |
---|
| 409 | SELECT DISTINCT r.rdb$field_name AS fname, |
---|
| 410 | r.rdb$null_flag AS null_flag, |
---|
| 411 | t.rdb$type_name AS ftype, |
---|
| 412 | f.rdb$field_sub_type AS stype, |
---|
| 413 | f.rdb$field_length AS flen, |
---|
| 414 | f.rdb$field_precision AS fprec, |
---|
| 415 | f.rdb$field_scale AS fscale, |
---|
| 416 | COALESCE(r.rdb$default_source, f.rdb$default_source) AS fdefault |
---|
| 417 | FROM rdb$relation_fields r |
---|
| 418 | JOIN rdb$fields f ON r.rdb$field_source=f.rdb$field_name |
---|
| 419 | JOIN rdb$types t ON t.rdb$type=f.rdb$field_type AND t.rdb$field_name='RDB$FIELD_TYPE' |
---|
| 420 | WHERE f.rdb$system_flag=0 AND r.rdb$relation_name=? |
---|
| 421 | ORDER BY r.rdb$field_position |
---|
| 422 | """ |
---|
| 423 | # Query to extract the PK/FK constrained fields of the given table |
---|
| 424 | keyqry = """ |
---|
| 425 | SELECT se.rdb$field_name AS fname |
---|
| 426 | FROM rdb$relation_constraints rc |
---|
| 427 | JOIN rdb$index_segments se ON rc.rdb$index_name=se.rdb$index_name |
---|
| 428 | WHERE rc.rdb$constraint_type=? AND rc.rdb$relation_name=? |
---|
| 429 | """ |
---|
| 430 | # Query to extract the details of each UK/FK of the given table |
---|
| 431 | fkqry = """ |
---|
| 432 | SELECT rc.rdb$constraint_name AS cname, |
---|
| 433 | cse.rdb$field_name AS fname, |
---|
| 434 | ix2.rdb$relation_name AS targetrname, |
---|
| 435 | se.rdb$field_name AS targetfname |
---|
| 436 | FROM rdb$relation_constraints rc |
---|
| 437 | JOIN rdb$indices ix1 ON ix1.rdb$index_name=rc.rdb$index_name |
---|
| 438 | JOIN rdb$indices ix2 ON ix2.rdb$index_name=ix1.rdb$foreign_key |
---|
| 439 | JOIN rdb$index_segments cse ON cse.rdb$index_name=ix1.rdb$index_name |
---|
| 440 | JOIN rdb$index_segments se ON se.rdb$index_name=ix2.rdb$index_name AND se.rdb$field_position=cse.rdb$field_position |
---|
| 441 | WHERE rc.rdb$constraint_type=? AND rc.rdb$relation_name=? |
---|
| 442 | ORDER BY se.rdb$index_name, se.rdb$field_position |
---|
| 443 | """ |
---|
| 444 | # Heuristic-query to determine the generator associated to a PK field |
---|
| 445 | genqry = """ |
---|
| 446 | SELECT trigdep.rdb$depended_on_name AS fgenerator |
---|
| 447 | FROM rdb$dependencies tabdep |
---|
| 448 | JOIN rdb$dependencies trigdep ON (tabdep.rdb$dependent_name=trigdep.rdb$dependent_name |
---|
| 449 | AND trigdep.rdb$depended_on_type=14 |
---|
| 450 | AND trigdep.rdb$dependent_type=2) |
---|
| 451 | JOIN rdb$triggers trig ON (trig.rdb$trigger_name=tabdep.rdb$dependent_name) |
---|
| 452 | WHERE tabdep.rdb$depended_on_name=? |
---|
| 453 | AND tabdep.rdb$depended_on_type=0 |
---|
| 454 | AND trig.rdb$trigger_type=1 |
---|
| 455 | AND tabdep.rdb$field_name=? |
---|
| 456 | AND (SELECT count(*) |
---|
| 457 | FROM rdb$dependencies trigdep2 |
---|
| 458 | WHERE trigdep2.rdb$dependent_name = trigdep.rdb$dependent_name) = 2 |
---|
| 459 | """ |
---|
| 460 | |
---|
| 461 | tablename = self._denormalize_name(table.name) |
---|
| 462 | |
---|
| 463 | # get primary key fields |
---|
| 464 | c = connection.execute(keyqry, ["PRIMARY KEY", tablename]) |
---|
| 465 | pkfields = [self._normalize_name(r['fname']) for r in c.fetchall()] |
---|
| 466 | |
---|
| 467 | # get all of the fields for this table |
---|
| 468 | c = connection.execute(tblqry, [tablename]) |
---|
| 469 | |
---|
| 470 | found_table = False |
---|
| 471 | while True: |
---|
| 472 | row = c.fetchone() |
---|
| 473 | if row is None: |
---|
| 474 | break |
---|
| 475 | found_table = True |
---|
| 476 | |
---|
| 477 | name = self._normalize_name(row['fname']) |
---|
| 478 | if include_columns and name not in include_columns: |
---|
| 479 | continue |
---|
| 480 | args = [name] |
---|
| 481 | |
---|
| 482 | kw = {} |
---|
| 483 | # get the data type |
---|
| 484 | coltype = ischema_names.get(row['ftype'].rstrip()) |
---|
| 485 | if coltype is None: |
---|
| 486 | util.warn("Did not recognize type '%s' of column '%s'" % |
---|
| 487 | (str(row['ftype']), name)) |
---|
| 488 | coltype = sqltypes.NULLTYPE |
---|
| 489 | else: |
---|
| 490 | coltype = coltype(row) |
---|
| 491 | args.append(coltype) |
---|
| 492 | |
---|
| 493 | # is it a primary key? |
---|
| 494 | kw['primary_key'] = name in pkfields |
---|
| 495 | |
---|
| 496 | # is it nullable? |
---|
| 497 | kw['nullable'] = not bool(row['null_flag']) |
---|
| 498 | |
---|
| 499 | # does it have a default value? |
---|
| 500 | if row['fdefault'] is not None: |
---|
| 501 | # the value comes down as "DEFAULT 'value'" |
---|
| 502 | assert row['fdefault'].upper().startswith('DEFAULT '), row |
---|
| 503 | defvalue = row['fdefault'][8:] |
---|
| 504 | args.append(schema.DefaultClause(sql.text(defvalue))) |
---|
| 505 | |
---|
| 506 | col = schema.Column(*args, **kw) |
---|
| 507 | if kw['primary_key']: |
---|
| 508 | # if the PK is a single field, try to see if its linked to |
---|
| 509 | # a sequence thru a trigger |
---|
| 510 | if len(pkfields)==1: |
---|
| 511 | genc = connection.execute(genqry, [tablename, row['fname']]) |
---|
| 512 | genr = genc.fetchone() |
---|
| 513 | if genr is not None: |
---|
| 514 | col.sequence = schema.Sequence(self._normalize_name(genr['fgenerator'])) |
---|
| 515 | |
---|
| 516 | table.append_column(col) |
---|
| 517 | |
---|
| 518 | if not found_table: |
---|
| 519 | raise exc.NoSuchTableError(table.name) |
---|
| 520 | |
---|
| 521 | # get the foreign keys |
---|
| 522 | c = connection.execute(fkqry, ["FOREIGN KEY", tablename]) |
---|
| 523 | fks = {} |
---|
| 524 | while True: |
---|
| 525 | row = c.fetchone() |
---|
| 526 | if not row: |
---|
| 527 | break |
---|
| 528 | |
---|
| 529 | cname = self._normalize_name(row['cname']) |
---|
| 530 | try: |
---|
| 531 | fk = fks[cname] |
---|
| 532 | except KeyError: |
---|
| 533 | fks[cname] = fk = ([], []) |
---|
| 534 | rname = self._normalize_name(row['targetrname']) |
---|
| 535 | schema.Table(rname, table.metadata, autoload=True, autoload_with=connection) |
---|
| 536 | fname = self._normalize_name(row['fname']) |
---|
| 537 | refspec = rname + '.' + self._normalize_name(row['targetfname']) |
---|
| 538 | fk[0].append(fname) |
---|
| 539 | fk[1].append(refspec) |
---|
| 540 | |
---|
| 541 | for name, value in fks.iteritems(): |
---|
| 542 | table.append_constraint(schema.ForeignKeyConstraint(value[0], value[1], name=name, link_to_name=True)) |
---|
| 543 | |
---|
| 544 | def do_execute(self, cursor, statement, parameters, **kwargs): |
---|
| 545 | # kinterbase does not accept a None, but wants an empty list |
---|
| 546 | # when there are no arguments. |
---|
| 547 | cursor.execute(statement, parameters or []) |
---|
| 548 | |
---|
| 549 | def do_rollback(self, connection): |
---|
| 550 | # Use the retaining feature, that keeps the transaction going |
---|
| 551 | connection.rollback(True) |
---|
| 552 | |
---|
| 553 | def do_commit(self, connection): |
---|
| 554 | # Use the retaining feature, that keeps the transaction going |
---|
| 555 | connection.commit(True) |
---|
| 556 | |
---|
| 557 | |
---|
| 558 | def _substring(s, start, length=None): |
---|
| 559 | "Helper function to handle Firebird 2 SUBSTRING builtin" |
---|
| 560 | |
---|
| 561 | if length is None: |
---|
| 562 | return "SUBSTRING(%s FROM %s)" % (s, start) |
---|
| 563 | else: |
---|
| 564 | return "SUBSTRING(%s FROM %s FOR %s)" % (s, start, length) |
---|
| 565 | |
---|
| 566 | |
---|
| 567 | class FBCompiler(sql.compiler.DefaultCompiler): |
---|
| 568 | """Firebird specific idiosincrasies""" |
---|
| 569 | |
---|
| 570 | # Firebird lacks a builtin modulo operator, but there is |
---|
| 571 | # an equivalent function in the ib_udf library. |
---|
| 572 | operators = sql.compiler.DefaultCompiler.operators.copy() |
---|
| 573 | operators.update({ |
---|
| 574 | sql.operators.mod : lambda x, y:"mod(%s, %s)" % (x, y) |
---|
| 575 | }) |
---|
| 576 | |
---|
| 577 | def visit_alias(self, alias, asfrom=False, **kwargs): |
---|
| 578 | # Override to not use the AS keyword which FB 1.5 does not like |
---|
| 579 | if asfrom: |
---|
| 580 | return self.process(alias.original, asfrom=True, **kwargs) + " " + self.preparer.format_alias(alias, self._anonymize(alias.name)) |
---|
| 581 | else: |
---|
| 582 | return self.process(alias.original, **kwargs) |
---|
| 583 | |
---|
| 584 | functions = sql.compiler.DefaultCompiler.functions.copy() |
---|
| 585 | functions['substring'] = _substring |
---|
| 586 | |
---|
| 587 | def function_argspec(self, func): |
---|
| 588 | if func.clauses: |
---|
| 589 | return self.process(func.clause_expr) |
---|
| 590 | else: |
---|
| 591 | return "" |
---|
| 592 | |
---|
| 593 | def default_from(self): |
---|
| 594 | return " FROM rdb$database" |
---|
| 595 | |
---|
| 596 | def visit_sequence(self, seq): |
---|
| 597 | return "gen_id(%s, 1)" % self.preparer.format_sequence(seq) |
---|
| 598 | |
---|
| 599 | def get_select_precolumns(self, select): |
---|
| 600 | """Called when building a ``SELECT`` statement, position is just |
---|
| 601 | before column list Firebird puts the limit and offset right |
---|
| 602 | after the ``SELECT``... |
---|
| 603 | """ |
---|
| 604 | |
---|
| 605 | result = "" |
---|
| 606 | if select._limit: |
---|
| 607 | result += "FIRST %d " % select._limit |
---|
| 608 | if select._offset: |
---|
| 609 | result +="SKIP %d " % select._offset |
---|
| 610 | if select._distinct: |
---|
| 611 | result += "DISTINCT " |
---|
| 612 | return result |
---|
| 613 | |
---|
| 614 | def limit_clause(self, select): |
---|
| 615 | """Already taken care of in the `get_select_precolumns` method.""" |
---|
| 616 | |
---|
| 617 | return "" |
---|
| 618 | |
---|
| 619 | LENGTH_FUNCTION_NAME = 'char_length' |
---|
| 620 | def function_string(self, func): |
---|
| 621 | """Substitute the ``length`` function. |
---|
| 622 | |
---|
| 623 | On newer FB there is a ``char_length`` function, while older |
---|
| 624 | ones need the ``strlen`` UDF. |
---|
| 625 | """ |
---|
| 626 | |
---|
| 627 | if func.name == 'length': |
---|
| 628 | return self.LENGTH_FUNCTION_NAME + '%(expr)s' |
---|
| 629 | return super(FBCompiler, self).function_string(func) |
---|
| 630 | |
---|
| 631 | def _append_returning(self, text, stmt): |
---|
| 632 | returning_cols = stmt.kwargs[RETURNING_KW_NAME] |
---|
| 633 | def flatten_columnlist(collist): |
---|
| 634 | for c in collist: |
---|
| 635 | if isinstance(c, sql.expression.Selectable): |
---|
| 636 | for co in c.columns: |
---|
| 637 | yield co |
---|
| 638 | else: |
---|
| 639 | yield c |
---|
| 640 | columns = [self.process(c, within_columns_clause=True) |
---|
| 641 | for c in flatten_columnlist(returning_cols)] |
---|
| 642 | text += ' RETURNING ' + ', '.join(columns) |
---|
| 643 | return text |
---|
| 644 | |
---|
| 645 | def visit_update(self, update_stmt): |
---|
| 646 | text = super(FBCompiler, self).visit_update(update_stmt) |
---|
| 647 | if RETURNING_KW_NAME in update_stmt.kwargs: |
---|
| 648 | return self._append_returning(text, update_stmt) |
---|
| 649 | else: |
---|
| 650 | return text |
---|
| 651 | |
---|
| 652 | def visit_insert(self, insert_stmt): |
---|
| 653 | text = super(FBCompiler, self).visit_insert(insert_stmt) |
---|
| 654 | if RETURNING_KW_NAME in insert_stmt.kwargs: |
---|
| 655 | return self._append_returning(text, insert_stmt) |
---|
| 656 | else: |
---|
| 657 | return text |
---|
| 658 | |
---|
| 659 | def visit_delete(self, delete_stmt): |
---|
| 660 | text = super(FBCompiler, self).visit_delete(delete_stmt) |
---|
| 661 | if RETURNING_KW_NAME in delete_stmt.kwargs: |
---|
| 662 | return self._append_returning(text, delete_stmt) |
---|
| 663 | else: |
---|
| 664 | return text |
---|
| 665 | |
---|
| 666 | |
---|
| 667 | class FBSchemaGenerator(sql.compiler.SchemaGenerator): |
---|
| 668 | """Firebird syntactic idiosincrasies""" |
---|
| 669 | |
---|
| 670 | def get_column_specification(self, column, **kwargs): |
---|
| 671 | colspec = self.preparer.format_column(column) |
---|
| 672 | colspec += " " + column.type.dialect_impl(self.dialect).get_col_spec() |
---|
| 673 | |
---|
| 674 | default = self.get_column_default_string(column) |
---|
| 675 | if default is not None: |
---|
| 676 | colspec += " DEFAULT " + default |
---|
| 677 | |
---|
| 678 | if not column.nullable or column.primary_key: |
---|
| 679 | colspec += " NOT NULL" |
---|
| 680 | |
---|
| 681 | return colspec |
---|
| 682 | |
---|
| 683 | def visit_sequence(self, sequence): |
---|
| 684 | """Generate a ``CREATE GENERATOR`` statement for the sequence.""" |
---|
| 685 | |
---|
| 686 | if not self.checkfirst or not self.dialect.has_sequence(self.connection, sequence.name): |
---|
| 687 | self.append("CREATE GENERATOR %s" % self.preparer.format_sequence(sequence)) |
---|
| 688 | self.execute() |
---|
| 689 | |
---|
| 690 | |
---|
| 691 | class FBSchemaDropper(sql.compiler.SchemaDropper): |
---|
| 692 | """Firebird syntactic idiosincrasies""" |
---|
| 693 | |
---|
| 694 | def visit_sequence(self, sequence): |
---|
| 695 | """Generate a ``DROP GENERATOR`` statement for the sequence.""" |
---|
| 696 | |
---|
| 697 | if not self.checkfirst or self.dialect.has_sequence(self.connection, sequence.name): |
---|
| 698 | self.append("DROP GENERATOR %s" % self.preparer.format_sequence(sequence)) |
---|
| 699 | self.execute() |
---|
| 700 | |
---|
| 701 | |
---|
| 702 | class FBDefaultRunner(base.DefaultRunner): |
---|
| 703 | """Firebird specific idiosincrasies""" |
---|
| 704 | |
---|
| 705 | def visit_sequence(self, seq): |
---|
| 706 | """Get the next value from the sequence using ``gen_id()``.""" |
---|
| 707 | |
---|
| 708 | return self.execute_string("SELECT gen_id(%s, 1) FROM rdb$database" % \ |
---|
| 709 | self.dialect.identifier_preparer.format_sequence(seq)) |
---|
| 710 | |
---|
| 711 | |
---|
| 712 | RESERVED_WORDS = set( |
---|
| 713 | ["action", "active", "add", "admin", "after", "all", "alter", "and", "any", |
---|
| 714 | "as", "asc", "ascending", "at", "auto", "autoddl", "avg", "based", "basename", |
---|
| 715 | "base_name", "before", "begin", "between", "bigint", "blob", "blobedit", "buffer", |
---|
| 716 | "by", "cache", "cascade", "case", "cast", "char", "character", "character_length", |
---|
| 717 | "char_length", "check", "check_point_len", "check_point_length", "close", "collate", |
---|
| 718 | "collation", "column", "commit", "committed", "compiletime", "computed", "conditional", |
---|
| 719 | "connect", "constraint", "containing", "continue", "count", "create", "cstring", |
---|
| 720 | "current", "current_connection", "current_date", "current_role", "current_time", |
---|
| 721 | "current_timestamp", "current_transaction", "current_user", "cursor", "database", |
---|
| 722 | "date", "day", "db_key", "debug", "dec", "decimal", "declare", "default", "delete", |
---|
| 723 | "desc", "descending", "describe", "descriptor", "disconnect", "display", "distinct", |
---|
| 724 | "do", "domain", "double", "drop", "echo", "edit", "else", "end", "entry_point", |
---|
| 725 | "escape", "event", "exception", "execute", "exists", "exit", "extern", "external", |
---|
| 726 | "extract", "fetch", "file", "filter", "float", "for", "foreign", "found", "free_it", |
---|
| 727 | "from", "full", "function", "gdscode", "generator", "gen_id", "global", "goto", |
---|
| 728 | "grant", "group", "group_commit_", "group_commit_wait", "having", "help", "hour", |
---|
| 729 | "if", "immediate", "in", "inactive", "index", "indicator", "init", "inner", "input", |
---|
| 730 | "input_type", "insert", "int", "integer", "into", "is", "isolation", "isql", "join", |
---|
| 731 | "key", "lc_messages", "lc_type", "left", "length", "lev", "level", "like", "logfile", |
---|
| 732 | "log_buffer_size", "log_buf_size", "long", "manual", "max", "maximum", "maximum_segment", |
---|
| 733 | "max_segment", "merge", "message", "min", "minimum", "minute", "module_name", "month", |
---|
| 734 | "names", "national", "natural", "nchar", "no", "noauto", "not", "null", "numeric", |
---|
| 735 | "num_log_buffers", "num_log_bufs", "octet_length", "of", "on", "only", "open", "option", |
---|
| 736 | "or", "order", "outer", "output", "output_type", "overflow", "page", "pagelength", |
---|
| 737 | "pages", "page_size", "parameter", "password", "plan", "position", "post_event", |
---|
| 738 | "precision", "prepare", "primary", "privileges", "procedure", "protected", "public", |
---|
| 739 | "quit", "raw_partitions", "rdb$db_key", "read", "real", "record_version", "recreate", |
---|
| 740 | "references", "release", "release", "reserv", "reserving", "restrict", "retain", |
---|
| 741 | "return", "returning_values", "returns", "revoke", "right", "role", "rollback", |
---|
| 742 | "row_count", "runtime", "savepoint", "schema", "second", "segment", "select", |
---|
| 743 | "set", "shadow", "shared", "shell", "show", "singular", "size", "smallint", |
---|
| 744 | "snapshot", "some", "sort", "sqlcode", "sqlerror", "sqlwarning", "stability", |
---|
| 745 | "starting", "starts", "statement", "static", "statistics", "sub_type", "sum", |
---|
| 746 | "suspend", "table", "terminator", "then", "time", "timestamp", "to", "transaction", |
---|
| 747 | "translate", "translation", "trigger", "trim", "type", "uncommitted", "union", |
---|
| 748 | "unique", "update", "upper", "user", "using", "value", "values", "varchar", |
---|
| 749 | "variable", "varying", "version", "view", "wait", "wait_time", "weekday", "when", |
---|
| 750 | "whenever", "where", "while", "with", "work", "write", "year", "yearday" ]) |
---|
| 751 | |
---|
| 752 | |
---|
| 753 | class FBIdentifierPreparer(sql.compiler.IdentifierPreparer): |
---|
| 754 | """Install Firebird specific reserved words.""" |
---|
| 755 | |
---|
| 756 | reserved_words = RESERVED_WORDS |
---|
| 757 | |
---|
| 758 | def __init__(self, dialect): |
---|
| 759 | super(FBIdentifierPreparer, self).__init__(dialect, omit_schema=True) |
---|
| 760 | |
---|
| 761 | |
---|
| 762 | dialect = FBDialect |
---|
| 763 | dialect.statement_compiler = FBCompiler |
---|
| 764 | dialect.schemagenerator = FBSchemaGenerator |
---|
| 765 | dialect.schemadropper = FBSchemaDropper |
---|
| 766 | dialect.defaultrunner = FBDefaultRunner |
---|
| 767 | dialect.preparer = FBIdentifierPreparer |
---|
| 768 | dialect.execution_ctx_cls = FBExecutionContext |
---|