1# firebird/base.py 2# Copyright (C) 2005-2018 the SQLAlchemy authors and contributors 3# <see AUTHORS file> 4# 5# This module is part of SQLAlchemy and is released under 6# the MIT License: http://www.opensource.org/licenses/mit-license.php 7 8r""" 9 10.. dialect:: firebird 11 :name: Firebird 12 13Firebird Dialects 14----------------- 15 16Firebird offers two distinct dialects_ (not to be confused with a 17SQLAlchemy ``Dialect``): 18 19dialect 1 20 This is the old syntax and behaviour, inherited from Interbase pre-6.0. 21 22dialect 3 23 This is the newer and supported syntax, introduced in Interbase 6.0. 24 25The SQLAlchemy Firebird dialect detects these versions and 26adjusts its representation of SQL accordingly. However, 27support for dialect 1 is not well tested and probably has 28incompatibilities. 29 30Locking Behavior 31---------------- 32 33Firebird locks tables aggressively. For this reason, a DROP TABLE may 34hang until other transactions are released. SQLAlchemy does its best 35to release transactions as quickly as possible. The most common cause 36of hanging transactions is a non-fully consumed result set, i.e.:: 37 38 result = engine.execute("select * from table") 39 row = result.fetchone() 40 return 41 42Where above, the ``ResultProxy`` has not been fully consumed. The 43connection will be returned to the pool and the transactional state 44rolled back once the Python garbage collector reclaims the objects 45which hold onto the connection, which often occurs asynchronously. 46The above use case can be alleviated by calling ``first()`` on the 47``ResultProxy`` which will fetch the first row and immediately close 48all remaining cursor/connection resources. 49 50RETURNING support 51----------------- 52 53Firebird 2.0 supports returning a result set from inserts, and 2.1 54extends that to deletes and updates. This is generically exposed by 55the SQLAlchemy ``returning()`` method, such as:: 56 57 # INSERT..RETURNING 58 result = table.insert().returning(table.c.col1, table.c.col2).\ 59 values(name='foo') 60 print result.fetchall() 61 62 # UPDATE..RETURNING 63 raises = empl.update().returning(empl.c.id, empl.c.salary).\ 64 where(empl.c.sales>100).\ 65 values(dict(salary=empl.c.salary * 1.1)) 66 print raises.fetchall() 67 68 69.. _dialects: http://mc-computing.com/Databases/Firebird/SQL_Dialect.html 70 71""" 72 73import datetime 74 75from sqlalchemy import schema as sa_schema 76from sqlalchemy import exc, types as sqltypes, sql, util 77from sqlalchemy.sql import expression 78from sqlalchemy.engine import base, default, reflection 79from sqlalchemy.sql import compiler 80from sqlalchemy.sql.elements import quoted_name 81 82from sqlalchemy.types import (BIGINT, BLOB, DATE, FLOAT, INTEGER, NUMERIC, 83 SMALLINT, TEXT, TIME, TIMESTAMP, Integer) 84 85 86RESERVED_WORDS = set([ 87 "active", "add", "admin", "after", "all", "alter", "and", "any", "as", 88 "asc", "ascending", "at", "auto", "avg", "before", "begin", "between", 89 "bigint", "bit_length", "blob", "both", "by", "case", "cast", "char", 90 "character", "character_length", "char_length", "check", "close", 91 "collate", "column", "commit", "committed", "computed", "conditional", 92 "connect", "constraint", "containing", "count", "create", "cross", 93 "cstring", "current", "current_connection", "current_date", 94 "current_role", "current_time", "current_timestamp", 95 "current_transaction", "current_user", "cursor", "database", "date", 96 "day", "dec", "decimal", "declare", "default", "delete", "desc", 97 "descending", "disconnect", "distinct", "do", "domain", "double", 98 "drop", "else", "end", "entry_point", "escape", "exception", 99 "execute", "exists", "exit", "external", "extract", "fetch", "file", 100 "filter", "float", "for", "foreign", "from", "full", "function", 101 "gdscode", "generator", "gen_id", "global", "grant", "group", 102 "having", "hour", "if", "in", "inactive", "index", "inner", 103 "input_type", "insensitive", "insert", "int", "integer", "into", "is", 104 "isolation", "join", "key", "leading", "left", "length", "level", 105 "like", "long", "lower", "manual", "max", "maximum_segment", "merge", 106 "min", "minute", "module_name", "month", "names", "national", 107 "natural", "nchar", "no", "not", "null", "numeric", "octet_length", 108 "of", "on", "only", "open", "option", "or", "order", "outer", 109 "output_type", "overflow", "page", "pages", "page_size", "parameter", 110 "password", "plan", "position", "post_event", "precision", "primary", 111 "privileges", "procedure", "protected", "rdb$db_key", "read", "real", 112 "record_version", "recreate", "recursive", "references", "release", 113 "reserv", "reserving", "retain", "returning_values", "returns", 114 "revoke", "right", "rollback", "rows", "row_count", "savepoint", 115 "schema", "second", "segment", "select", "sensitive", "set", "shadow", 116 "shared", "singular", "size", "smallint", "snapshot", "some", "sort", 117 "sqlcode", "stability", "start", "starting", "starts", "statistics", 118 "sub_type", "sum", "suspend", "table", "then", "time", "timestamp", 119 "to", "trailing", "transaction", "trigger", "trim", "uncommitted", 120 "union", "unique", "update", "upper", "user", "using", "value", 121 "values", "varchar", "variable", "varying", "view", "wait", "when", 122 "where", "while", "with", "work", "write", "year", 123]) 124 125 126class _StringType(sqltypes.String): 127 """Base for Firebird string types.""" 128 129 def __init__(self, charset=None, **kw): 130 self.charset = charset 131 super(_StringType, self).__init__(**kw) 132 133 134class VARCHAR(_StringType, sqltypes.VARCHAR): 135 """Firebird VARCHAR type""" 136 __visit_name__ = 'VARCHAR' 137 138 def __init__(self, length=None, **kwargs): 139 super(VARCHAR, self).__init__(length=length, **kwargs) 140 141 142class CHAR(_StringType, sqltypes.CHAR): 143 """Firebird CHAR type""" 144 __visit_name__ = 'CHAR' 145 146 def __init__(self, length=None, **kwargs): 147 super(CHAR, self).__init__(length=length, **kwargs) 148 149 150class _FBDateTime(sqltypes.DateTime): 151 def bind_processor(self, dialect): 152 def process(value): 153 if type(value) == datetime.date: 154 return datetime.datetime(value.year, value.month, value.day) 155 else: 156 return value 157 return process 158 159colspecs = { 160 sqltypes.DateTime: _FBDateTime 161} 162 163ischema_names = { 164 'SHORT': SMALLINT, 165 'LONG': INTEGER, 166 'QUAD': FLOAT, 167 'FLOAT': FLOAT, 168 'DATE': DATE, 169 'TIME': TIME, 170 'TEXT': TEXT, 171 'INT64': BIGINT, 172 'DOUBLE': FLOAT, 173 'TIMESTAMP': TIMESTAMP, 174 'VARYING': VARCHAR, 175 'CSTRING': CHAR, 176 'BLOB': BLOB, 177} 178 179 180# TODO: date conversion types (should be implemented as _FBDateTime, 181# _FBDate, etc. as bind/result functionality is required) 182 183class FBTypeCompiler(compiler.GenericTypeCompiler): 184 def visit_boolean(self, type_, **kw): 185 return self.visit_SMALLINT(type_, **kw) 186 187 def visit_datetime(self, type_, **kw): 188 return self.visit_TIMESTAMP(type_, **kw) 189 190 def visit_TEXT(self, type_, **kw): 191 return "BLOB SUB_TYPE 1" 192 193 def visit_BLOB(self, type_, **kw): 194 return "BLOB SUB_TYPE 0" 195 196 def _extend_string(self, type_, basic): 197 charset = getattr(type_, 'charset', None) 198 if charset is None: 199 return basic 200 else: 201 return '%s CHARACTER SET %s' % (basic, charset) 202 203 def visit_CHAR(self, type_, **kw): 204 basic = super(FBTypeCompiler, self).visit_CHAR(type_, **kw) 205 return self._extend_string(type_, basic) 206 207 def visit_VARCHAR(self, type_, **kw): 208 if not type_.length: 209 raise exc.CompileError( 210 "VARCHAR requires a length on dialect %s" % 211 self.dialect.name) 212 basic = super(FBTypeCompiler, self).visit_VARCHAR(type_, **kw) 213 return self._extend_string(type_, basic) 214 215 216class FBCompiler(sql.compiler.SQLCompiler): 217 """Firebird specific idiosyncrasies""" 218 219 ansi_bind_rules = True 220 221 # def visit_contains_op_binary(self, binary, operator, **kw): 222 # cant use CONTAINING b.c. it's case insensitive. 223 224 # def visit_notcontains_op_binary(self, binary, operator, **kw): 225 # cant use NOT CONTAINING b.c. it's case insensitive. 226 227 def visit_now_func(self, fn, **kw): 228 return "CURRENT_TIMESTAMP" 229 230 def visit_startswith_op_binary(self, binary, operator, **kw): 231 return '%s STARTING WITH %s' % ( 232 binary.left._compiler_dispatch(self, **kw), 233 binary.right._compiler_dispatch(self, **kw)) 234 235 def visit_notstartswith_op_binary(self, binary, operator, **kw): 236 return '%s NOT STARTING WITH %s' % ( 237 binary.left._compiler_dispatch(self, **kw), 238 binary.right._compiler_dispatch(self, **kw)) 239 240 def visit_mod_binary(self, binary, operator, **kw): 241 return "mod(%s, %s)" % ( 242 self.process(binary.left, **kw), 243 self.process(binary.right, **kw)) 244 245 def visit_alias(self, alias, asfrom=False, **kwargs): 246 if self.dialect._version_two: 247 return super(FBCompiler, self).\ 248 visit_alias(alias, asfrom=asfrom, **kwargs) 249 else: 250 # Override to not use the AS keyword which FB 1.5 does not like 251 if asfrom: 252 alias_name = isinstance(alias.name, 253 expression._truncated_label) and \ 254 self._truncated_identifier("alias", 255 alias.name) or alias.name 256 257 return self.process( 258 alias.original, asfrom=asfrom, **kwargs) + \ 259 " " + \ 260 self.preparer.format_alias(alias, alias_name) 261 else: 262 return self.process(alias.original, **kwargs) 263 264 def visit_substring_func(self, func, **kw): 265 s = self.process(func.clauses.clauses[0]) 266 start = self.process(func.clauses.clauses[1]) 267 if len(func.clauses.clauses) > 2: 268 length = self.process(func.clauses.clauses[2]) 269 return "SUBSTRING(%s FROM %s FOR %s)" % (s, start, length) 270 else: 271 return "SUBSTRING(%s FROM %s)" % (s, start) 272 273 def visit_length_func(self, function, **kw): 274 if self.dialect._version_two: 275 return "char_length" + self.function_argspec(function) 276 else: 277 return "strlen" + self.function_argspec(function) 278 279 visit_char_length_func = visit_length_func 280 281 def function_argspec(self, func, **kw): 282 # TODO: this probably will need to be 283 # narrowed to a fixed list, some no-arg functions 284 # may require parens - see similar example in the oracle 285 # dialect 286 if func.clauses is not None and len(func.clauses): 287 return self.process(func.clause_expr, **kw) 288 else: 289 return "" 290 291 def default_from(self): 292 return " FROM rdb$database" 293 294 def visit_sequence(self, seq): 295 return "gen_id(%s, 1)" % self.preparer.format_sequence(seq) 296 297 def get_select_precolumns(self, select, **kw): 298 """Called when building a ``SELECT`` statement, position is just 299 before column list Firebird puts the limit and offset right 300 after the ``SELECT``... 301 """ 302 303 result = "" 304 if select._limit_clause is not None: 305 result += "FIRST %s " % self.process(select._limit_clause, **kw) 306 if select._offset_clause is not None: 307 result += "SKIP %s " % self.process(select._offset_clause, **kw) 308 if select._distinct: 309 result += "DISTINCT " 310 return result 311 312 def limit_clause(self, select, **kw): 313 """Already taken care of in the `get_select_precolumns` method.""" 314 315 return "" 316 317 def returning_clause(self, stmt, returning_cols): 318 columns = [ 319 self._label_select_column(None, c, True, False, {}) 320 for c in expression._select_iterables(returning_cols) 321 ] 322 323 return 'RETURNING ' + ', '.join(columns) 324 325 326class FBDDLCompiler(sql.compiler.DDLCompiler): 327 """Firebird syntactic idiosyncrasies""" 328 329 def visit_create_sequence(self, create): 330 """Generate a ``CREATE GENERATOR`` statement for the sequence.""" 331 332 # no syntax for these 333 # http://www.firebirdsql.org/manual/generatorguide-sqlsyntax.html 334 if create.element.start is not None: 335 raise NotImplemented( 336 "Firebird SEQUENCE doesn't support START WITH") 337 if create.element.increment is not None: 338 raise NotImplemented( 339 "Firebird SEQUENCE doesn't support INCREMENT BY") 340 341 if self.dialect._version_two: 342 return "CREATE SEQUENCE %s" % \ 343 self.preparer.format_sequence(create.element) 344 else: 345 return "CREATE GENERATOR %s" % \ 346 self.preparer.format_sequence(create.element) 347 348 def visit_drop_sequence(self, drop): 349 """Generate a ``DROP GENERATOR`` statement for the sequence.""" 350 351 if self.dialect._version_two: 352 return "DROP SEQUENCE %s" % \ 353 self.preparer.format_sequence(drop.element) 354 else: 355 return "DROP GENERATOR %s" % \ 356 self.preparer.format_sequence(drop.element) 357 358 359class FBIdentifierPreparer(sql.compiler.IdentifierPreparer): 360 """Install Firebird specific reserved words.""" 361 362 reserved_words = RESERVED_WORDS 363 illegal_initial_characters = compiler.ILLEGAL_INITIAL_CHARACTERS.union( 364 ['_']) 365 366 def __init__(self, dialect): 367 super(FBIdentifierPreparer, self).__init__(dialect, omit_schema=True) 368 369 370class FBExecutionContext(default.DefaultExecutionContext): 371 def fire_sequence(self, seq, type_): 372 """Get the next value from the sequence using ``gen_id()``.""" 373 374 return self._execute_scalar( 375 "SELECT gen_id(%s, 1) FROM rdb$database" % 376 self.dialect.identifier_preparer.format_sequence(seq), 377 type_ 378 ) 379 380 381class FBDialect(default.DefaultDialect): 382 """Firebird dialect""" 383 384 name = 'firebird' 385 386 max_identifier_length = 31 387 388 supports_sequences = True 389 sequences_optional = False 390 supports_default_values = True 391 postfetch_lastrowid = False 392 393 supports_native_boolean = False 394 395 requires_name_normalize = True 396 supports_empty_insert = False 397 398 statement_compiler = FBCompiler 399 ddl_compiler = FBDDLCompiler 400 preparer = FBIdentifierPreparer 401 type_compiler = FBTypeCompiler 402 execution_ctx_cls = FBExecutionContext 403 404 colspecs = colspecs 405 ischema_names = ischema_names 406 407 construct_arguments = [] 408 409 # defaults to dialect ver. 3, 410 # will be autodetected off upon 411 # first connect 412 _version_two = True 413 414 def initialize(self, connection): 415 super(FBDialect, self).initialize(connection) 416 self._version_two = ('firebird' in self.server_version_info and 417 self.server_version_info >= (2, ) 418 ) or \ 419 ('interbase' in self.server_version_info and 420 self.server_version_info >= (6, ) 421 ) 422 423 if not self._version_two: 424 # TODO: whatever other pre < 2.0 stuff goes here 425 self.ischema_names = ischema_names.copy() 426 self.ischema_names['TIMESTAMP'] = sqltypes.DATE 427 self.colspecs = { 428 sqltypes.DateTime: sqltypes.DATE 429 } 430 431 self.implicit_returning = self._version_two and \ 432 self.__dict__.get('implicit_returning', True) 433 434 def normalize_name(self, name): 435 # Remove trailing spaces: FB uses a CHAR() type, 436 # that is padded with spaces 437 name = name and name.rstrip() 438 if name is None: 439 return None 440 elif name.upper() == name and \ 441 not self.identifier_preparer._requires_quotes(name.lower()): 442 return name.lower() 443 elif name.lower() == name: 444 return quoted_name(name, quote=True) 445 else: 446 return name 447 448 def denormalize_name(self, name): 449 if name is None: 450 return None 451 elif name.lower() == name and \ 452 not self.identifier_preparer._requires_quotes(name.lower()): 453 return name.upper() 454 else: 455 return name 456 457 def has_table(self, connection, table_name, schema=None): 458 """Return ``True`` if the given table exists, ignoring 459 the `schema`.""" 460 461 tblqry = """ 462 SELECT 1 AS has_table FROM rdb$database 463 WHERE EXISTS (SELECT rdb$relation_name 464 FROM rdb$relations 465 WHERE rdb$relation_name=?) 466 """ 467 c = connection.execute(tblqry, [self.denormalize_name(table_name)]) 468 return c.first() is not None 469 470 def has_sequence(self, connection, sequence_name, schema=None): 471 """Return ``True`` if the given sequence (generator) exists.""" 472 473 genqry = """ 474 SELECT 1 AS has_sequence FROM rdb$database 475 WHERE EXISTS (SELECT rdb$generator_name 476 FROM rdb$generators 477 WHERE rdb$generator_name=?) 478 """ 479 c = connection.execute(genqry, [self.denormalize_name(sequence_name)]) 480 return c.first() is not None 481 482 @reflection.cache 483 def get_table_names(self, connection, schema=None, **kw): 484 # there are two queries commonly mentioned for this. 485 # this one, using view_blr, is at the Firebird FAQ among other places: 486 # http://www.firebirdfaq.org/faq174/ 487 s = """ 488 select rdb$relation_name 489 from rdb$relations 490 where rdb$view_blr is null 491 and (rdb$system_flag is null or rdb$system_flag = 0); 492 """ 493 494 # the other query is this one. It's not clear if there's really 495 # any difference between these two. This link: 496 # http://www.alberton.info/firebird_sql_meta_info.html#.Ur3vXfZGni8 497 # states them as interchangeable. Some discussion at [ticket:2898] 498 # SELECT DISTINCT rdb$relation_name 499 # FROM rdb$relation_fields 500 # WHERE rdb$system_flag=0 AND rdb$view_context IS NULL 501 502 return [self.normalize_name(row[0]) for row in connection.execute(s)] 503 504 @reflection.cache 505 def get_view_names(self, connection, schema=None, **kw): 506 # see http://www.firebirdfaq.org/faq174/ 507 s = """ 508 select rdb$relation_name 509 from rdb$relations 510 where rdb$view_blr is not null 511 and (rdb$system_flag is null or rdb$system_flag = 0); 512 """ 513 return [self.normalize_name(row[0]) for row in connection.execute(s)] 514 515 @reflection.cache 516 def get_view_definition(self, connection, view_name, schema=None, **kw): 517 qry = """ 518 SELECT rdb$view_source AS view_source 519 FROM rdb$relations 520 WHERE rdb$relation_name=? 521 """ 522 rp = connection.execute(qry, [self.denormalize_name(view_name)]) 523 row = rp.first() 524 if row: 525 return row['view_source'] 526 else: 527 return None 528 529 @reflection.cache 530 def get_pk_constraint(self, connection, table_name, schema=None, **kw): 531 # Query to extract the PK/FK constrained fields of the given table 532 keyqry = """ 533 SELECT se.rdb$field_name AS fname 534 FROM rdb$relation_constraints rc 535 JOIN rdb$index_segments se ON rc.rdb$index_name=se.rdb$index_name 536 WHERE rc.rdb$constraint_type=? AND rc.rdb$relation_name=? 537 """ 538 tablename = self.denormalize_name(table_name) 539 # get primary key fields 540 c = connection.execute(keyqry, ["PRIMARY KEY", tablename]) 541 pkfields = [self.normalize_name(r['fname']) for r in c.fetchall()] 542 return {'constrained_columns': pkfields, 'name': None} 543 544 @reflection.cache 545 def get_column_sequence(self, connection, 546 table_name, column_name, 547 schema=None, **kw): 548 tablename = self.denormalize_name(table_name) 549 colname = self.denormalize_name(column_name) 550 # Heuristic-query to determine the generator associated to a PK field 551 genqry = """ 552 SELECT trigdep.rdb$depended_on_name AS fgenerator 553 FROM rdb$dependencies tabdep 554 JOIN rdb$dependencies trigdep 555 ON tabdep.rdb$dependent_name=trigdep.rdb$dependent_name 556 AND trigdep.rdb$depended_on_type=14 557 AND trigdep.rdb$dependent_type=2 558 JOIN rdb$triggers trig ON 559 trig.rdb$trigger_name=tabdep.rdb$dependent_name 560 WHERE tabdep.rdb$depended_on_name=? 561 AND tabdep.rdb$depended_on_type=0 562 AND trig.rdb$trigger_type=1 563 AND tabdep.rdb$field_name=? 564 AND (SELECT count(*) 565 FROM rdb$dependencies trigdep2 566 WHERE trigdep2.rdb$dependent_name = trigdep.rdb$dependent_name) = 2 567 """ 568 genr = connection.execute(genqry, [tablename, colname]).first() 569 if genr is not None: 570 return dict(name=self.normalize_name(genr['fgenerator'])) 571 572 @reflection.cache 573 def get_columns(self, connection, table_name, schema=None, **kw): 574 # Query to extract the details of all the fields of the given table 575 tblqry = """ 576 SELECT r.rdb$field_name AS fname, 577 r.rdb$null_flag AS null_flag, 578 t.rdb$type_name AS ftype, 579 f.rdb$field_sub_type AS stype, 580 f.rdb$field_length/ 581 COALESCE(cs.rdb$bytes_per_character,1) AS flen, 582 f.rdb$field_precision AS fprec, 583 f.rdb$field_scale AS fscale, 584 COALESCE(r.rdb$default_source, 585 f.rdb$default_source) AS fdefault 586 FROM rdb$relation_fields r 587 JOIN rdb$fields f ON r.rdb$field_source=f.rdb$field_name 588 JOIN rdb$types t 589 ON t.rdb$type=f.rdb$field_type AND 590 t.rdb$field_name='RDB$FIELD_TYPE' 591 LEFT JOIN rdb$character_sets cs ON 592 f.rdb$character_set_id=cs.rdb$character_set_id 593 WHERE f.rdb$system_flag=0 AND r.rdb$relation_name=? 594 ORDER BY r.rdb$field_position 595 """ 596 # get the PK, used to determine the eventual associated sequence 597 pk_constraint = self.get_pk_constraint(connection, table_name) 598 pkey_cols = pk_constraint['constrained_columns'] 599 600 tablename = self.denormalize_name(table_name) 601 # get all of the fields for this table 602 c = connection.execute(tblqry, [tablename]) 603 cols = [] 604 while True: 605 row = c.fetchone() 606 if row is None: 607 break 608 name = self.normalize_name(row['fname']) 609 orig_colname = row['fname'] 610 611 # get the data type 612 colspec = row['ftype'].rstrip() 613 coltype = self.ischema_names.get(colspec) 614 if coltype is None: 615 util.warn("Did not recognize type '%s' of column '%s'" % 616 (colspec, name)) 617 coltype = sqltypes.NULLTYPE 618 elif issubclass(coltype, Integer) and row['fprec'] != 0: 619 coltype = NUMERIC( 620 precision=row['fprec'], 621 scale=row['fscale'] * -1) 622 elif colspec in ('VARYING', 'CSTRING'): 623 coltype = coltype(row['flen']) 624 elif colspec == 'TEXT': 625 coltype = TEXT(row['flen']) 626 elif colspec == 'BLOB': 627 if row['stype'] == 1: 628 coltype = TEXT() 629 else: 630 coltype = BLOB() 631 else: 632 coltype = coltype() 633 634 # does it have a default value? 635 defvalue = None 636 if row['fdefault'] is not None: 637 # the value comes down as "DEFAULT 'value'": there may be 638 # more than one whitespace around the "DEFAULT" keyword 639 # and it may also be lower case 640 # (see also http://tracker.firebirdsql.org/browse/CORE-356) 641 defexpr = row['fdefault'].lstrip() 642 assert defexpr[:8].rstrip().upper() == \ 643 'DEFAULT', "Unrecognized default value: %s" % \ 644 defexpr 645 defvalue = defexpr[8:].strip() 646 if defvalue == 'NULL': 647 # Redundant 648 defvalue = None 649 col_d = { 650 'name': name, 651 'type': coltype, 652 'nullable': not bool(row['null_flag']), 653 'default': defvalue, 654 'autoincrement': 'auto', 655 } 656 657 if orig_colname.lower() == orig_colname: 658 col_d['quote'] = True 659 660 # if the PK is a single field, try to see if its linked to 661 # a sequence thru a trigger 662 if len(pkey_cols) == 1 and name == pkey_cols[0]: 663 seq_d = self.get_column_sequence(connection, tablename, name) 664 if seq_d is not None: 665 col_d['sequence'] = seq_d 666 667 cols.append(col_d) 668 return cols 669 670 @reflection.cache 671 def get_foreign_keys(self, connection, table_name, schema=None, **kw): 672 # Query to extract the details of each UK/FK of the given table 673 fkqry = """ 674 SELECT rc.rdb$constraint_name AS cname, 675 cse.rdb$field_name AS fname, 676 ix2.rdb$relation_name AS targetrname, 677 se.rdb$field_name AS targetfname 678 FROM rdb$relation_constraints rc 679 JOIN rdb$indices ix1 ON ix1.rdb$index_name=rc.rdb$index_name 680 JOIN rdb$indices ix2 ON ix2.rdb$index_name=ix1.rdb$foreign_key 681 JOIN rdb$index_segments cse ON 682 cse.rdb$index_name=ix1.rdb$index_name 683 JOIN rdb$index_segments se 684 ON se.rdb$index_name=ix2.rdb$index_name 685 AND se.rdb$field_position=cse.rdb$field_position 686 WHERE rc.rdb$constraint_type=? AND rc.rdb$relation_name=? 687 ORDER BY se.rdb$index_name, se.rdb$field_position 688 """ 689 tablename = self.denormalize_name(table_name) 690 691 c = connection.execute(fkqry, ["FOREIGN KEY", tablename]) 692 fks = util.defaultdict(lambda: { 693 'name': None, 694 'constrained_columns': [], 695 'referred_schema': None, 696 'referred_table': None, 697 'referred_columns': [] 698 }) 699 700 for row in c: 701 cname = self.normalize_name(row['cname']) 702 fk = fks[cname] 703 if not fk['name']: 704 fk['name'] = cname 705 fk['referred_table'] = self.normalize_name(row['targetrname']) 706 fk['constrained_columns'].append( 707 self.normalize_name(row['fname'])) 708 fk['referred_columns'].append( 709 self.normalize_name(row['targetfname'])) 710 return list(fks.values()) 711 712 @reflection.cache 713 def get_indexes(self, connection, table_name, schema=None, **kw): 714 qry = """ 715 SELECT ix.rdb$index_name AS index_name, 716 ix.rdb$unique_flag AS unique_flag, 717 ic.rdb$field_name AS field_name 718 FROM rdb$indices ix 719 JOIN rdb$index_segments ic 720 ON ix.rdb$index_name=ic.rdb$index_name 721 LEFT OUTER JOIN rdb$relation_constraints 722 ON rdb$relation_constraints.rdb$index_name = 723 ic.rdb$index_name 724 WHERE ix.rdb$relation_name=? AND ix.rdb$foreign_key IS NULL 725 AND rdb$relation_constraints.rdb$constraint_type IS NULL 726 ORDER BY index_name, ic.rdb$field_position 727 """ 728 c = connection.execute(qry, [self.denormalize_name(table_name)]) 729 730 indexes = util.defaultdict(dict) 731 for row in c: 732 indexrec = indexes[row['index_name']] 733 if 'name' not in indexrec: 734 indexrec['name'] = self.normalize_name(row['index_name']) 735 indexrec['column_names'] = [] 736 indexrec['unique'] = bool(row['unique_flag']) 737 738 indexrec['column_names'].append( 739 self.normalize_name(row['field_name'])) 740 741 return list(indexes.values()) 742