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