sqlalchemy.engine.reflection.cache

Here are the examples of the python api sqlalchemy.engine.reflection.cache taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.

97 Examples 7

Page 1 Selected Page 2

Example 1

Project: CouchPotatoV1 Source File: base.py
Function: get_primary_keys
    @reflection.cache
    def get_primary_keys(self, connection, table_name, schema=None, **kw):
        # Query to extract the PK/FK constrained fields of the given table
        keyqry = """
        SELECT se.rdb$field_name AS fname
        FROM rdb$relation_constraints rc
             JOIN rdb$index_segments se ON rc.rdb$index_name=se.rdb$index_name
        WHERE rc.rdb$constraint_type=? AND rc.rdb$relation_name=?
        """
        tablename = self.denormalize_name(table_name)
        # get primary key fields
        c = connection.execute(keyqry, ["PRIMARY KEY", tablename])
        pkfields = [self.normalize_name(r['fname']) for r in c.fetchall()]
        return pkfields

Example 2

Project: crate-python Source File: dialect.py
Function: get_schema_names
    @reflection.cache
    def get_schema_names(self, connection, **kw):
        cursor = connection.execute(
            "select schema_name "
            "from information_schema.schemata "
            "order by schema_name asc"
        )
        return [row[0] for row in cursor.fetchall()]

Example 3

Project: SickGear Source File: base.py
    @reflection.cache
    def get_table_names(self, connection, schema=None, **kw):
        if schema is None:
            schema = self.default_schema_name

        TABLE_SQL = text("""
          SELECT o.name AS name
          FROM sysobjects o JOIN sysusers u ON o.uid = u.uid
          WHERE u.name = :schema_name
            AND o.type = 'U'
        """)

        if util.py2k:
            if isinstance(schema, unicode):
                schema = schema.encode("ascii")

        tables = connection.execute(TABLE_SQL, schema_name=schema)

        return [t["name"] for t in tables]

Example 4

Project: sqlalchemy_exasol Source File: base.py
Function: get_indexes
    @reflection.cache
    def get_indexes(self, connection, table_name, schema=None, **kw):
        schema = schema or connection.engine.url.database
        # EXASolution has no indexes
        # TODO: check if indexes are used by SQLA for optimizing SQL Statements.
        # If so, we should return all columns as being indexed
        return []

Example 5

Project: sqlalchemy-redshift Source File: dialect.py
Function: get_view_names
    @reflection.cache
    def get_view_names(self, connection, schema=None, **kw):
        """
        Return a list of view names for `schema`.

        Overrides interface
        :meth:`~sqlalchemy.engine.interfaces.Dialect.get_view_names`.
        """
        return self._get_table_or_view_names('v', connection, schema, **kw)

Example 6

Project: SickGear Source File: base.py
Function: get_view_names
    @reflection.cache
    def get_view_names(self, connection, schema=None, **kw):
        # see http://www.firebirdfaq.org/faq174/
        s = """
        select rdb$relation_name
        from rdb$relations
        where rdb$view_blr is not null
        and (rdb$system_flag is null or rdb$system_flag = 0);
        """
        return [self.normalize_name(row[0]) for row in connection.execute(s)]

Example 7

Project: CouchPotatoV1 Source File: base.py
Function: get_view_definition
    @reflection.cache
    def get_view_definition(self, connection, view_name, schema=None,
                            resolve_synonyms=False, dblink='', **kw):
        info_cache = kw.get('info_cache')
        (view_name, schema, dblink, synonym) = \
            self._prepare_reflection_args(connection, view_name, schema,
                                          resolve_synonyms, dblink,
                                          info_cache=info_cache)
        s = sql.text("""
        SELECT text FROM all_views
        WHERE owner = :schema
        AND view_name = :view_name
        """)
        rp = connection.execute(s,
                                view_name=view_name, schema=schema).scalar()
        if rp:
            return rp.decode(self.encoding)
        else:
            return None

Example 8

Project: CouchPotatoV1 Source File: base.py
Function: get_schema_names
    @reflection.cache
    def get_schema_names(self, connection, **kw):
        s = """
        SELECT nspname
        FROM pg_namespace
        ORDER BY nspname
        """
        rp = connection.execute(s)
        # what about system tables?
        # Py3K
        #schema_names = [row[0] for row in rp \
        #                if not row[0].startswith('pg_')]
        # Py2K
        schema_names = [row[0].decode(self.encoding) for row in rp \
                        if not row[0].startswith('pg_')]
        # end Py2K
        return schema_names

Example 9

Project: maraschino Source File: base.py
Function: get_table_names
    @reflection.cache
    def get_table_names(self, connection, schema=None, **kw):
        """Return a Unicode SHOW TABLES from a given schema."""
        if schema is not None:
            current_schema = schema
        else:
            current_schema = self.default_schema_name

        charset = 'utf8'
        rp = connection.execute("SHOW TABLES FROM %s" %
            self.identifier_preparer.quote_identifier(current_schema))
        return [row[0] for row in self._compat_fetchall(rp, charset=charset)]

Example 10

Project: SickGear Source File: base.py
Function: get_pk_constraint
    @reflection.cache
    def get_pk_constraint(self, connection, table_name, schema=None, **kw):
        # Query to extract the PK/FK constrained fields of the given table
        keyqry = """
        SELECT se.rdb$field_name AS fname
        FROM rdb$relation_constraints rc
             JOIN rdb$index_segments se ON rc.rdb$index_name=se.rdb$index_name
        WHERE rc.rdb$constraint_type=? AND rc.rdb$relation_name=?
        """
        tablename = self.denormalize_name(table_name)
        # get primary key fields
        c = connection.execute(keyqry, ["PRIMARY KEY", tablename])
        pkfields = [self.normalize_name(r['fname']) for r in c.fetchall()]
        return {'constrained_columns': pkfields, 'name': None}

Example 11

Project: sqlalchemy_exasol Source File: base.py
    @reflection.cache
    def _get_all_constraints(self, connection, schema=None, **kw):
        sql_stmnt = "SELECT constraint_name, column_name, referenced_schema, referenced_table, " \
                     "referenced_column, constraint_table, constraint_type " \
                     "FROM SYS.EXA_ALL_CONSTRAINT_COLUMNS where constraint_schema = "

        if schema is None:
            sql_stmnt += "CURRENT_SCHEMA "
        else:
            sql_stmnt += ":schema "
        sql_stmnt += "ORDER BY ordinal_position"
        rp = connection.execute(sql.text(sql_stmnt),
                schema=self.denormalize_name(schema))

        return list(rp)

Example 12

Project: sqlalchemy_exasol Source File: base.py
Function: get_table_names
    @reflection.cache
    def get_table_names(self, connection, schema, **kw):
        schema = schema or connection.engine.url.database
        sql_stmnt = "SELECT table_name FROM  SYS.EXA_ALL_TABLES WHERE table_schema = "
        if schema is None:
            sql_stmnt += "CURRENT_SCHEMA ORDER BY table_name"
            rs = connection.execute(sql_stmnt)
        else:
            sql_stmnt += ":schema ORDER BY table_name"
            rs = connection.execute(sql.text(sql_stmnt), \
                schema=self.denormalize_name(schema))
        return [self.normalize_name(row[0]) for row in rs]

Example 13

Project: CouchPotatoV1 Source File: base.py
    @reflection.cache
    def get_primary_keys(self, connection, table_name, schema=None, **kw):
        table_oid = self.get_table_oid(connection, table_name, schema,
                                       info_cache=kw.get('info_cache'))
        PK_SQL = """
          SELECT attname FROM pg_attribute
          WHERE attrelid = (
             SELECT indexrelid FROM pg_index i
             WHERE i.indrelid = :table_oid
             AND i.indisprimary = 't')
          ORDER BY attnum
        """
        t = sql.text(PK_SQL, typemap={'attname':sqltypes.Unicode})
        c = connection.execute(t, table_oid=table_oid)
        primary_keys = [r[0] for r in c.fetchall()]
        return primary_keys

Example 14

Project: CouchPotatoV1 Source File: base.py
Function: get_table_names
    @reflection.cache
    def get_table_names(self, connection, schema=None, **kw):
        s = """
        SELECT DISTINCT rdb$relation_name
        FROM rdb$relation_fields
        WHERE rdb$system_flag=0 AND rdb$view_context IS NULL
        """
        return [self.normalize_name(row[0]) for row in connection.execute(s)]

Example 15

Project: CouchPotatoV1 Source File: base.py
Function: get_view_names
    @reflection.cache
    def get_view_names(self, connection, schema=None, **kw):
        s = """
        SELECT distinct rdb$view_name
        FROM rdb$view_relations
        """
        return [self.normalize_name(row[0]) for row in connection.execute(s)]

Example 16

Project: CouchPotatoV1 Source File: base.py
Function: get_table_names
    @reflection.cache
    def get_table_names(self, connection, schema=None, **kw):
        if schema is None:
            sql = (" SELECT TABLENAME FROM TABLES WHERE "
                   " SCHEMANAME=CURRENT_SCHEMA ")
            rs = connection.execute(sql)
        else:
            sql = (" SELECT TABLENAME FROM TABLES WHERE "
                   " SCHEMANAME=? ")
            matchname = self.identifier_preparer._denormalize_name(schema)
            rs = connection.execute(sql, matchname)
        normalize = self.identifier_preparer._normalize_name
        return [normalize(row[0]) for row in rs]

Example 17

Project: SickGear Source File: base.py
Function: get_view_definition
    @reflection.cache
    def get_view_definition(self, connection, view_name, schema=None, **kw):
        qry = """
        SELECT rdb$view_source AS view_source
        FROM rdb$relations
        WHERE rdb$relation_name=?
        """
        rp = connection.execute(qry, [self.denormalize_name(view_name)])
        row = rp.first()
        if row:
            return row['view_source']
        else:
            return None

Example 18

Project: CouchPotatoV1 Source File: base.py
    @reflection.cache
    def get_table_names(self, connection, schema=None, **kw):
        if schema is not None:
            current_schema = schema
        else:
            current_schema = self.default_schema_name

        result = connection.execute(
            sql.text(u"SELECT relname FROM pg_class c "
                "WHERE relkind = 'r' "
                "AND '%s' = (select nspname from pg_namespace n "
                "where n.oid = c.relnamespace) " %
                current_schema,
                typemap = {'relname':sqltypes.Unicode}
            )
        )
        return [row[0] for row in result]

Example 19

Project: SickGear Source File: base.py
    @reflection.cache
    def get_schema_names(self, connection, **kw):

        SCHEMA_SQL = text("SELECT u.name AS name FROM sysusers u")

        schemas = connection.execute(SCHEMA_SQL)

        return [s["name"] for s in schemas]

Example 20

Project: kokoropy Source File: base.py
Function: get_table_names
    @reflection.cache
    def get_table_names(self, connection, schema=None, **kw):
        schema = self.denormalize_name(schema or self.default_schema_name)

        # note that table_names() isn't loading DBLINKed or synonym'ed tables
        if schema is None:
            schema = self.default_schema_name
        s = sql.text(
            "SELECT table_name FROM all_tables "
            "WHERE nvl(tablespace_name, 'no tablespace') NOT IN "
            "('SYSTEM', 'SYSAUX') "
            "AND OWNER = :owner "
            "AND IOT_NAME IS NULL")
        cursor = connection.execute(s, owner=schema)
        return [self.normalize_name(row[0]) for row in cursor]

Example 21

Project: SickGear Source File: base.py
    @reflection.cache
    def get_view_names(self, connection, schema=None, **kw):
        if schema is None:
            schema = self.default_schema_name

        VIEW_SQL = text("""
          SELECT o.name AS name
          FROM sysobjects o JOIN sysusers u ON o.uid = u.uid
          WHERE u.name = :schema_name
            AND o.type = 'V'
        """)

        if util.py2k:
            if isinstance(schema, unicode):
                schema = schema.encode("ascii")
        views = connection.execute(VIEW_SQL, schema_name=schema)

        return [v["name"] for v in views]

Example 22

Project: sqlalchemy-redshift Source File: dialect.py
Function: get_table_names
    @reflection.cache
    def get_table_names(self, connection, schema=None, **kw):
        """
        Return a list of table names for `schema`.

        Overrides interface
        :meth:`~sqlalchemy.engine.interfaces.Dialect.get_table_names`.
        """
        return self._get_table_or_view_names('r', connection, schema, **kw)

Example 23

Project: crate-python Source File: dialect.py
Function: get_table_names
    @reflection.cache
    def get_table_names(self, connection, schema=None, **kw):
        version = connection.connection.lowest_server_version
        schema_name = \
            "table_schema" if version >= SCHEMA_MIN_VERSION else "schema_name"

        cursor = connection.execute(
            "select table_name from information_schema.tables "
            "where {0} = ? "
            "order by table_name asc, {0} asc".format(schema_name),
            [schema or self.default_schema_name]
        )
        return [row[0] for row in cursor.fetchall()]

Example 24

Project: sqlalchemy-redshift Source File: dialect.py
Function: get_view_definition
    @reflection.cache
    def get_view_definition(self, connection, view_name, schema=None, **kw):
        """Return view definition.
        Given a :class:`.Connection`, a string `view_name`,
        and an optional string `schema`, return the view definition.

        Overrides interface
        :meth:`~sqlalchemy.engine.interfaces.Dialect.get_view_definition`.
        """
        view = self._get_redshift_relation(connection, view_name, schema, **kw)
        return sa.text(view.view_definition)

Example 25

Project: SickGear Source File: base.py
Function: get_view_names
    @reflection.cache
    def get_view_names(self, connection, schema=None, **kw):
        schema = self.denormalize_name(schema or self.default_schema_name)
        s = sql.text("SELECT view_name FROM all_views WHERE owner = :owner")
        cursor = connection.execute(s, owner=self.denormalize_name(schema))
        return [self.normalize_name(row[0]) for row in cursor]

Example 26

Project: sqlalchemy_exasol Source File: base.py
Function: get_all_columns
    @reflection.cache
    def _get_all_columns(self, connection, schema=None, **kw):
        sql_stmnt = "SELECT column_name, column_type, column_maxsize, column_num_prec, column_num_scale, " \
                    "column_is_nullable, column_default, column_identity, column_is_distribution_key, column_table " \
                    "FROM sys.exa_all_columns  WHERE column_object_type IN ('TABLE', 'VIEW') " \
                    "AND column_schema = "

        if schema is None:
            sql_stmnt += "CURRENT_SCHEMA "
        else:
            sql_stmnt += ":schema "
        sql_stmnt += "ORDER BY column_ordinal_position"
        rp = connection.execute(sql.text(sql_stmnt),
                schema=self.denormalize_name(schema))

        return list(rp)

Example 27

Project: sqlalchemy_exasol Source File: base.py
Function: get_view_names
    @reflection.cache
    def get_view_names(self, connection, schema=None, **kw):
        schema = schema or connection.engine.url.database
        sql_stmnt = "SELECT view_name FROM  SYS.EXA_ALL_VIEWS WHERE view_schema = "
        if schema is None:
            sql_stmnt += "CURRENT_SCHEMA ORDER BY view_name"
            rs = connection.execute(sql.text(sql_stmnt))
        else:
            sql_stmnt += ":schema ORDER BY view_name"
            rs = connection.execute(sql.text(sql_stmnt),
                schema=self.denormalize_name(schema))
        return [self.normalize_name(row[0]) for row in rs]

Example 28

Project: CouchPotatoV1 Source File: base.py
Function: get_view_definition
    @reflection.cache
    def get_view_definition(self, connection, view_name, schema=None, **kw):
        schema = schema or self.default_schema_name
        c = connection.execute(
            """select t1.viewtext
            from sysviews as t1 , systables as t2
            where t1.tabid=t2.tabid and t2.tabname=?
            and t2.owner=? order by seqno""",
             view_name, schema).fetchall()

        return ''.join([row[0] for row in c])

Example 29

Project: sqlalchemy_exasol Source File: base.py
Function: get_pk_constraint
    @reflection.cache
    def get_pk_constraint(self, connection, table_name, schema=None, **kw):
        schema = schema or connection.engine.url.database
        pkeys = []
        constraint_name = None
        table_name=self.denormalize_name(table_name)

        for row in self._get_all_constraints(connection, schema, info_cache=kw.get("info_cache")):
            if (row[5] != table_name and table_name is not None) or row[6] != 'PRIMARY KEY':
                continue
            pkeys.append(self.normalize_name(row[1]))
            constraint_name = self.normalize_name(row[0])
        return {'constrained_columns': pkeys, 'name': constraint_name}

Example 30

Project: CouchPotatoV1 Source File: base.py
    @reflection.cache
    def _prepare_reflection_args(self, connection, table_name, schema=None,
                                 resolve_synonyms=False, dblink='', **kw):

        if resolve_synonyms:
            actual_name, owner, dblink, synonym = self._resolve_synonym(
                                                         connection, 
                                                         desired_owner=self.denormalize_name(schema), 
                                                         desired_synonym=self.denormalize_name(table_name)
                                                   )
        else:
            actual_name, owner, dblink, synonym = None, None, None, None
        if not actual_name:
            actual_name = self.denormalize_name(table_name)
        if not dblink:
            dblink = ''
        if not owner:
            owner = self.denormalize_name(schema or self.default_schema_name)
        return (actual_name, owner, dblink, synonym)

Example 31

Project: CouchPotatoV1 Source File: base.py
Function: get_table_names
    @reflection.cache
    def get_table_names(self, connection, schema=None, **kw):
        # A fresh DAO connection is opened for each reflection
        # This is necessary, so we get the latest updates
        dtbs = daoEngine.OpenDatabase(connection.engine.url.database)

        names = [t.Name for t in dtbs.TableDefs 
                if t.Name[:4] != "MSys" and t.Name[:4] != "~TMP"]
        dtbs.Close()
        return names

Example 32

Project: SickGear Source File: base.py
Function: get_table_names
    @reflection.cache
    def get_table_names(self, connection, schema=None, **kw):
        schema = self.denormalize_name(schema or self.default_schema_name)

        # note that table_names() isnt loading DBLINKed or synonym'ed tables
        if schema is None:
            schema = self.default_schema_name
        s = sql.text(
            "SELECT table_name FROM all_tables "
            "WHERE nvl(tablespace_name, 'no tablespace') NOT IN ('SYSTEM', 'SYSAUX') "
            "AND OWNER = :owner "
            "AND IOT_NAME IS NULL")
        cursor = connection.execute(s, owner=schema)
        return [self.normalize_name(row[0]) for row in cursor]

Example 33

Project: SickGear Source File: base.py
Function: get_view_definition
    @reflection.cache
    def get_view_definition(self, connection, view_name, schema=None, **kw):
        if schema is None:
            schema = self.default_schema_name

        VIEW_DEF_SQL = text("""
          SELECT c.text
          FROM syscomments c JOIN sysobjects o ON c.id = o.id
          WHERE o.name = :view_name
            AND o.type = 'V'
        """)

        if util.py2k:
            if isinstance(view_name, unicode):
                view_name = view_name.encode("ascii")

        view = connection.execute(VIEW_DEF_SQL, view_name=view_name)

        return view.scalar()

Example 34

Project: SickGear Source File: base.py
Function: get_table_names
    @reflection.cache
    def get_table_names(self, connection, schema=None, **kw):
        """Return a Unicode SHOW TABLES from a given schema."""

        if schema is not None:
            current_schema = schema
        else:
            current_schema = self.default_schema_name

        charset = 'utf8'
        rp = connection.execute("SHOW TABLES FROM %s" %
            self.identifier_preparer.quote_identifier(current_schema))
        return [row[0] for row in self._compat_fetchall(rp, charset=charset)]

Example 35

Project: sqlalchemy_exasol Source File: base.py
Function: get_view_definition
    @reflection.cache
    def get_view_definition(self, connection, view_name, schema=None, **kw):
        schema = schema or connection.engine.url.database
        sql_stmnt = "SELECT view_text FROM sys.exa_all_views WHERE view_name = :view_name AND view_schema = "
        if schema is None:
            sql_stmnt += "CURRENT_SCHEMA"
        else:
            sql_stmnt += ":schema"
        rp = connection.execute(sql.text(sql_stmnt),
                view_name=self.denormalize_name(view_name),
                schema=self.denormalize_name(schema)).scalar()
        if rp:
            if six.PY3:
                return rp
            else:
                return rp.decode(self.encoding)
        else:
            return None

Example 36

Project: SickGear Source File: base.py
Function: get_pk_constraint
    @reflection.cache
    def get_pk_constraint(self, connection, table_name, schema=None, **kw):
        resolve_synonyms = kw.get('oracle_resolve_synonyms', False)
        dblink = kw.get('dblink', '')
        info_cache = kw.get('info_cache')

        (table_name, schema, dblink, synonym) = \
            self._prepare_reflection_args(connection, table_name, schema,
                                          resolve_synonyms, dblink,
                                          info_cache=info_cache)
        pkeys = []
        constraint_name = None
        constraint_data = self._get_constraint_data(connection, table_name,
                                        schema, dblink,
                                        info_cache=kw.get('info_cache'))

        for row in constraint_data:
            (cons_name, cons_type, local_column, remote_table, remote_column, remote_owner) = \
                row[0:2] + tuple([self.normalize_name(x) for x in row[2:6]])
            if cons_type == 'P':
                if constraint_name is None:
                    constraint_name = self.normalize_name(cons_name)
                pkeys.append(local_column)
        return {'constrained_columns': pkeys, 'name': constraint_name}

Example 37

Project: SickGear Source File: base.py
    @reflection.cache
    def get_columns(self, connection, table_name, schema=None, **kw):
        # Query to extract the details of all the fields of the given table
        tblqry = """
        SELECT r.rdb$field_name AS fname,
                        r.rdb$null_flag AS null_flag,
                        t.rdb$type_name AS ftype,
                        f.rdb$field_sub_type AS stype,
                        f.rdb$field_length/
                            COALESCE(cs.rdb$bytes_per_character,1) AS flen,
                        f.rdb$field_precision AS fprec,
                        f.rdb$field_scale AS fscale,
                        COALESCE(r.rdb$default_source,
                                f.rdb$default_source) AS fdefault
        FROM rdb$relation_fields r
             JOIN rdb$fields f ON r.rdb$field_source=f.rdb$field_name
             JOIN rdb$types t
              ON t.rdb$type=f.rdb$field_type AND
                    t.rdb$field_name='RDB$FIELD_TYPE'
             LEFT JOIN rdb$character_sets cs ON
                    f.rdb$character_set_id=cs.rdb$character_set_id
        WHERE f.rdb$system_flag=0 AND r.rdb$relation_name=?
        ORDER BY r.rdb$field_position
        """
        # get the PK, used to determine the eventual associated sequence
        pk_constraint = self.get_pk_constraint(connection, table_name)
        pkey_cols = pk_constraint['constrained_columns']

        tablename = self.denormalize_name(table_name)
        # get all of the fields for this table
        c = connection.execute(tblqry, [tablename])
        cols = []
        while True:
            row = c.fetchone()
            if row is None:
                break
            name = self.normalize_name(row['fname'])
            orig_colname = row['fname']

            # get the data type
            colspec = row['ftype'].rstrip()
            coltype = self.ischema_names.get(colspec)
            if coltype is None:
                util.warn("Did not recognize type '%s' of column '%s'" %
                          (colspec, name))
                coltype = sqltypes.NULLTYPE
            elif issubclass(coltype, Integer) and row['fprec'] != 0:
                coltype = NUMERIC(
                                precision=row['fprec'],
                                scale=row['fscale'] * -1)
            elif colspec in ('VARYING', 'CSTRING'):
                coltype = coltype(row['flen'])
            elif colspec == 'TEXT':
                coltype = TEXT(row['flen'])
            elif colspec == 'BLOB':
                if row['stype'] == 1:
                    coltype = TEXT()
                else:
                    coltype = BLOB()
            else:
                coltype = coltype()

            # does it have a default value?
            defvalue = None
            if row['fdefault'] is not None:
                # the value comes down as "DEFAULT 'value'": there may be
                # more than one whitespace around the "DEFAULT" keyword
                # and it may also be lower case
                # (see also http://tracker.firebirdsql.org/browse/CORE-356)
                defexpr = row['fdefault'].lstrip()
                assert defexpr[:8].rstrip().upper() == \
                            'DEFAULT', "Unrecognized default value: %s" % \
                            defexpr
                defvalue = defexpr[8:].strip()
                if defvalue == 'NULL':
                    # Redundant
                    defvalue = None
            col_d = {
                'name': name,
                'type': coltype,
                'nullable': not bool(row['null_flag']),
                'default': defvalue,
                'autoincrement': defvalue is None
            }

            if orig_colname.lower() == orig_colname:
                col_d['quote'] = True

            # if the PK is a single field, try to see if its linked to
            # a sequence thru a trigger
            if len(pkey_cols) == 1 and name == pkey_cols[0]:
                seq_d = self.get_column_sequence(connection, tablename, name)
                if seq_d is not None:
                    col_d['sequence'] = seq_d

            cols.append(col_d)
        return cols

Example 38

Project: SickGear Source File: base.py
Function: get_foreign_keys
    @reflection.cache
    def get_foreign_keys(self, connection, table_name, schema=None, **kw):
        """

        kw arguments can be:

            oracle_resolve_synonyms

            dblink

        """

        requested_schema = schema  # to check later on
        resolve_synonyms = kw.get('oracle_resolve_synonyms', False)
        dblink = kw.get('dblink', '')
        info_cache = kw.get('info_cache')

        (table_name, schema, dblink, synonym) = \
            self._prepare_reflection_args(connection, table_name, schema,
                                          resolve_synonyms, dblink,
                                          info_cache=info_cache)

        constraint_data = self._get_constraint_data(connection, table_name,
                                                schema, dblink,
                                                info_cache=kw.get('info_cache'))

        def fkey_rec():
            return {
                'name': None,
                'constrained_columns': [],
                'referred_schema': None,
                'referred_table': None,
                'referred_columns': []
            }

        fkeys = util.defaultdict(fkey_rec)

        for row in constraint_data:
            (cons_name, cons_type, local_column, remote_table, remote_column, remote_owner) = \
                    row[0:2] + tuple([self.normalize_name(x) for x in row[2:6]])

            if cons_type == 'R':
                if remote_table is None:
                    # ticket 363
                    util.warn(
                        ("Got 'None' querying 'table_name' from "
                         "all_cons_columns%(dblink)s - does the user have "
                         "proper rights to the table?") % {'dblink': dblink})
                    continue

                rec = fkeys[cons_name]
                rec['name'] = cons_name
                local_cols, remote_cols = rec['constrained_columns'], rec['referred_columns']

                if not rec['referred_table']:
                    if resolve_synonyms:
                        ref_remote_name, ref_remote_owner, ref_dblink, ref_synonym = \
                                self._resolve_synonym(
                                    connection,
                                    desired_owner=self.denormalize_name(remote_owner),
                                    desired_table=self.denormalize_name(remote_table)
                                )
                        if ref_synonym:
                            remote_table = self.normalize_name(ref_synonym)
                            remote_owner = self.normalize_name(ref_remote_owner)

                    rec['referred_table'] = remote_table

                    if requested_schema is not None or self.denormalize_name(remote_owner) != schema:
                        rec['referred_schema'] = remote_owner

                local_cols.append(local_column)
                remote_cols.append(remote_column)

        return list(fkeys.values())

Example 39

Project: SickGear Source File: base.py
Function: get_view_definition
    @reflection.cache
    def get_view_definition(self, connection, view_name, schema=None,
                            resolve_synonyms=False, dblink='', **kw):
        info_cache = kw.get('info_cache')
        (view_name, schema, dblink, synonym) = \
            self._prepare_reflection_args(connection, view_name, schema,
                                          resolve_synonyms, dblink,
                                          info_cache=info_cache)

        params = {'view_name': view_name}
        text = "SELECT text FROM all_views WHERE view_name=:view_name"

        if schema is not None:
            text += " AND owner = :schema"
            params['schema'] = schema

        rp = connection.execute(sql.text(text), **params).scalar()
        if rp:
            if util.py2k:
                rp = rp.decode(self.encoding)
            return rp
        else:
            return None

Example 40

Project: SickGear Source File: base.py
Function: get_foreign_keys
    @reflection.cache
    def get_foreign_keys(self, connection, table_name, schema=None, **kw):
        # Query to extract the details of each UK/FK of the given table
        fkqry = """
        SELECT rc.rdb$constraint_name AS cname,
               cse.rdb$field_name AS fname,
               ix2.rdb$relation_name AS targetrname,
               se.rdb$field_name AS targetfname
        FROM rdb$relation_constraints rc
             JOIN rdb$indices ix1 ON ix1.rdb$index_name=rc.rdb$index_name
             JOIN rdb$indices ix2 ON ix2.rdb$index_name=ix1.rdb$foreign_key
             JOIN rdb$index_segments cse ON
                        cse.rdb$index_name=ix1.rdb$index_name
             JOIN rdb$index_segments se
                  ON se.rdb$index_name=ix2.rdb$index_name
                     AND se.rdb$field_position=cse.rdb$field_position
        WHERE rc.rdb$constraint_type=? AND rc.rdb$relation_name=?
        ORDER BY se.rdb$index_name, se.rdb$field_position
        """
        tablename = self.denormalize_name(table_name)

        c = connection.execute(fkqry, ["FOREIGN KEY", tablename])
        fks = util.defaultdict(lambda: {
            'name': None,
            'constrained_columns': [],
            'referred_schema': None,
            'referred_table': None,
            'referred_columns': []
        })

        for row in c:
            cname = self.normalize_name(row['cname'])
            fk = fks[cname]
            if not fk['name']:
                fk['name'] = cname
                fk['referred_table'] = self.normalize_name(row['targetrname'])
            fk['constrained_columns'].append(
                                self.normalize_name(row['fname']))
            fk['referred_columns'].append(
                                self.normalize_name(row['targetfname']))
        return list(fks.values())

Example 41

Project: SickGear Source File: base.py
    @reflection.cache
    def get_columns(self, connection, table_name, schema=None, **kw):
        table_id = self.get_table_id(connection, table_name, schema,
                                     info_cache=kw.get("info_cache"))

        COLUMN_SQL = text("""
          SELECT col.name AS name,
                 t.name AS type,
                 (col.status & 8) AS nullable,
                 (col.status & 128) AS autoincrement,
                 com.text AS 'default',
                 col.prec AS precision,
                 col.scale AS scale,
                 col.length AS length
          FROM systypes t, syscolumns col LEFT OUTER JOIN syscomments com ON
              col.cdefault = com.id
          WHERE col.usertype = t.usertype
              AND col.id = :table_id
          ORDER BY col.colid
        """)

        results = connection.execute(COLUMN_SQL, table_id=table_id)

        columns = []
        for (name, type_, nullable, autoincrement, default, precision, scale,
             length) in results:
            col_info = self._get_column_info(name, type_, bool(nullable),
                             bool(autoincrement), default, precision, scale,
                             length)
            columns.append(col_info)

        return columns

Example 42

Project: SickGear Source File: base.py
Function: get_view_names
    @reflection.cache
    def get_view_names(self, connection, schema=None, **kw):
        raise NotImplementedError

Example 43

Project: SickGear Source File: base.py
    @reflection.cache
    def get_foreign_keys(self, connection, table_name, schema=None, **kw):

        table_id = self.get_table_id(connection, table_name, schema,
                                     info_cache=kw.get("info_cache"))

        table_cache = {}
        column_cache = {}
        foreign_keys = []

        table_cache[table_id] = {"name": table_name, "schema": schema}

        COLUMN_SQL = text("""
          SELECT c.colid AS id, c.name AS name
          FROM syscolumns c
          WHERE c.id = :table_id
        """)

        results = connection.execute(COLUMN_SQL, table_id=table_id)
        columns = {}
        for col in results:
            columns[col["id"]] = col["name"]
        column_cache[table_id] = columns

        REFCONSTRAINT_SQL = text("""
          SELECT o.name AS name, r.reftabid AS reftable_id,
            r.keycnt AS 'count',
            r.fokey1 AS fokey1, r.fokey2 AS fokey2, r.fokey3 AS fokey3,
            r.fokey4 AS fokey4, r.fokey5 AS fokey5, r.fokey6 AS fokey6,
            r.fokey7 AS fokey7, r.fokey1 AS fokey8, r.fokey9 AS fokey9,
            r.fokey10 AS fokey10, r.fokey11 AS fokey11, r.fokey12 AS fokey12,
            r.fokey13 AS fokey13, r.fokey14 AS fokey14, r.fokey15 AS fokey15,
            r.fokey16 AS fokey16,
            r.refkey1 AS refkey1, r.refkey2 AS refkey2, r.refkey3 AS refkey3,
            r.refkey4 AS refkey4, r.refkey5 AS refkey5, r.refkey6 AS refkey6,
            r.refkey7 AS refkey7, r.refkey1 AS refkey8, r.refkey9 AS refkey9,
            r.refkey10 AS refkey10, r.refkey11 AS refkey11,
            r.refkey12 AS refkey12, r.refkey13 AS refkey13,
            r.refkey14 AS refkey14, r.refkey15 AS refkey15,
            r.refkey16 AS refkey16
          FROM sysreferences r JOIN sysobjects o on r.tableid = o.id
          WHERE r.tableid = :table_id
        """)
        referential_constraints = connection.execute(REFCONSTRAINT_SQL,
                                                     table_id=table_id)

        REFTABLE_SQL = text("""
          SELECT o.name AS name, u.name AS 'schema'
          FROM sysobjects o JOIN sysusers u ON o.uid = u.uid
          WHERE o.id = :table_id
        """)

        for r in referential_constraints:
            reftable_id = r["reftable_id"]

            if reftable_id not in table_cache:
                c = connection.execute(REFTABLE_SQL, table_id=reftable_id)
                reftable = c.fetchone()
                c.close()
                table_info = {"name": reftable["name"], "schema": None}
                if (schema is not None or
                        reftable["schema"] != self.default_schema_name):
                    table_info["schema"] = reftable["schema"]

                table_cache[reftable_id] = table_info
                results = connection.execute(COLUMN_SQL, table_id=reftable_id)
                reftable_columns = {}
                for col in results:
                    reftable_columns[col["id"]] = col["name"]
                column_cache[reftable_id] = reftable_columns

            reftable = table_cache[reftable_id]
            reftable_columns = column_cache[reftable_id]

            constrained_columns = []
            referred_columns = []
            for i in range(1, r["count"] + 1):
                constrained_columns.append(columns[r["fokey%i" % i]])
                referred_columns.append(reftable_columns[r["refkey%i" % i]])

            fk_info = {
                    "constrained_columns": constrained_columns,
                    "referred_schema": reftable["schema"],
                    "referred_table": reftable["name"],
                    "referred_columns": referred_columns,
                    "name": r["name"]
                }

            foreign_keys.append(fk_info)

        return foreign_keys

Example 44

Project: SickGear Source File: base.py
Function: get_indexes
    @reflection.cache
    def get_indexes(self, connection, table_name, schema=None, **kw):
        qry = """
        SELECT ix.rdb$index_name AS index_name,
               ix.rdb$unique_flag AS unique_flag,
               ic.rdb$field_name AS field_name
        FROM rdb$indices ix
             JOIN rdb$index_segments ic
                  ON ix.rdb$index_name=ic.rdb$index_name
             LEFT OUTER JOIN rdb$relation_constraints
                  ON rdb$relation_constraints.rdb$index_name =
                        ic.rdb$index_name
        WHERE ix.rdb$relation_name=? AND ix.rdb$foreign_key IS NULL
          AND rdb$relation_constraints.rdb$constraint_type IS NULL
        ORDER BY index_name, ic.rdb$field_position
        """
        c = connection.execute(qry, [self.denormalize_name(table_name)])

        indexes = util.defaultdict(dict)
        for row in c:
            indexrec = indexes[row['index_name']]
            if 'name' not in indexrec:
                indexrec['name'] = self.normalize_name(row['index_name'])
                indexrec['column_names'] = []
                indexrec['unique'] = bool(row['unique_flag'])

            indexrec['column_names'].append(
                                self.normalize_name(row['field_name']))

        return list(indexes.values())

Example 45

Project: SickGear Source File: base.py
    @reflection.cache
    def get_indexes(self, connection, table_name, schema=None, **kw):
        table_id = self.get_table_id(connection, table_name, schema,
                                     info_cache=kw.get("info_cache"))

        INDEX_SQL = text("""
          SELECT object_name(i.id) AS table_name,
                 i.keycnt AS 'count',
                 i.name AS name,
                 (i.status & 0x2) AS 'unique',
                 index_col(object_name(i.id), i.indid, 1) AS col_1,
                 index_col(object_name(i.id), i.indid, 2) AS col_2,
                 index_col(object_name(i.id), i.indid, 3) AS col_3,
                 index_col(object_name(i.id), i.indid, 4) AS col_4,
                 index_col(object_name(i.id), i.indid, 5) AS col_5,
                 index_col(object_name(i.id), i.indid, 6) AS col_6,
                 index_col(object_name(i.id), i.indid, 7) AS col_7,
                 index_col(object_name(i.id), i.indid, 8) AS col_8,
                 index_col(object_name(i.id), i.indid, 9) AS col_9,
                 index_col(object_name(i.id), i.indid, 10) AS col_10,
                 index_col(object_name(i.id), i.indid, 11) AS col_11,
                 index_col(object_name(i.id), i.indid, 12) AS col_12,
                 index_col(object_name(i.id), i.indid, 13) AS col_13,
                 index_col(object_name(i.id), i.indid, 14) AS col_14,
                 index_col(object_name(i.id), i.indid, 15) AS col_15,
                 index_col(object_name(i.id), i.indid, 16) AS col_16
          FROM sysindexes i, sysobjects o
          WHERE o.id = i.id
            AND o.id = :table_id
            AND (i.status & 2048) = 0
            AND i.indid BETWEEN 1 AND 254
        """)

        results = connection.execute(INDEX_SQL, table_id=table_id)
        indexes = []
        for r in results:
            column_names = []
            for i in range(1, r["count"]):
                column_names.append(r["col_%i" % (i,)])
            index_info = {"name": r["name"],
                          "unique": bool(r["unique"]),
                          "column_names": column_names}
            indexes.append(index_info)

        return indexes

Example 46

Project: SickGear Source File: base.py
    @reflection.cache
    def get_columns(self, connection, table_name, schema=None, **kw):
        """

        kw arguments can be:

            oracle_resolve_synonyms

            dblink

        """

        resolve_synonyms = kw.get('oracle_resolve_synonyms', False)
        dblink = kw.get('dblink', '')
        info_cache = kw.get('info_cache')

        (table_name, schema, dblink, synonym) = \
            self._prepare_reflection_args(connection, table_name, schema,
                                          resolve_synonyms, dblink,
                                          info_cache=info_cache)
        columns = []
        if self._supports_char_length:
            char_length_col = 'char_length'
        else:
            char_length_col = 'data_length'

        params = {"table_name": table_name}
        text = "SELECT column_name, data_type, %(char_length_col)s, "\
                "data_precision, data_scale, "\
                "nullable, data_default FROM ALL_TAB_COLUMNS%(dblink)s "\
                "WHERE table_name = :table_name"
        if schema is not None:
            params['owner'] = schema
            text += " AND owner = :owner "
        text += " ORDER BY column_id"
        text = text % {'dblink': dblink, 'char_length_col': char_length_col}

        c = connection.execute(sql.text(text), **params)

        for row in c:
            (colname, orig_colname, coltype, length, precision, scale, nullable, default) = \
                (self.normalize_name(row[0]), row[0], row[1], row[2], row[3], row[4], row[5] == 'Y', row[6])

            if coltype == 'NUMBER':
                coltype = NUMBER(precision, scale)
            elif coltype in ('VARCHAR2', 'NVARCHAR2', 'CHAR'):
                coltype = self.ischema_names.get(coltype)(length)
            elif 'WITH TIME ZONE' in coltype:
                coltype = TIMESTAMP(timezone=True)
            else:
                coltype = re.sub(r'\(\d+\)', '', coltype)
                try:
                    coltype = self.ischema_names[coltype]
                except KeyError:
                    util.warn("Did not recognize type '%s' of column '%s'" %
                              (coltype, colname))
                    coltype = sqltypes.NULLTYPE

            cdict = {
                'name': colname,
                'type': coltype,
                'nullable': nullable,
                'default': default,
                'autoincrement': default is None
            }
            if orig_colname.lower() == orig_colname:
                cdict['quote'] = True

            columns.append(cdict)
        return columns

Example 47

Project: SickGear Source File: base.py
    @reflection.cache
    def get_indexes(self, connection, table_name, schema=None,
                    resolve_synonyms=False, dblink='', **kw):

        info_cache = kw.get('info_cache')
        (table_name, schema, dblink, synonym) = \
            self._prepare_reflection_args(connection, table_name, schema,
                                          resolve_synonyms, dblink,
                                          info_cache=info_cache)
        indexes = []

        params = {'table_name': table_name}
        text = \
            "SELECT a.index_name, a.column_name, b.uniqueness "\
            "\nFROM ALL_IND_COLUMNS%(dblink)s a, "\
            "\nALL_INDEXES%(dblink)s b "\
            "\nWHERE "\
            "\na.index_name = b.index_name "\
            "\nAND a.table_owner = b.table_owner "\
            "\nAND a.table_name = b.table_name "\
            "\nAND a.table_name = :table_name "

        if schema is not None:
            params['schema'] = schema
            text += "AND a.table_owner = :schema "

        text += "ORDER BY a.index_name, a.column_position"

        text = text % {'dblink': dblink}

        q = sql.text(text)
        rp = connection.execute(q, **params)
        indexes = []
        last_index_name = None
        pk_constraint = self.get_pk_constraint(
            connection, table_name, schema, resolve_synonyms=resolve_synonyms,
            dblink=dblink, info_cache=kw.get('info_cache'))
        pkeys = pk_constraint['constrained_columns']
        uniqueness = dict(NONUNIQUE=False, UNIQUE=True)

        oracle_sys_col = re.compile(r'SYS_NC\d+\$', re.IGNORECASE)

        def upper_name_set(names):
            return set([i.upper() for i in names])

        pk_names = upper_name_set(pkeys)

        def remove_if_primary_key(index):
            # don't include the primary key index
            if index is not None and \
               upper_name_set(index['column_names']) == pk_names:
                indexes.pop()

        index = None
        for rset in rp:
            if rset.index_name != last_index_name:
                remove_if_primary_key(index)
                index = dict(name=self.normalize_name(rset.index_name), column_names=[])
                indexes.append(index)
            index['unique'] = uniqueness.get(rset.uniqueness, False)

            # filter out Oracle SYS_NC names.  could also do an outer join
            # to the all_tab_columns table and check for real col names there.
            if not oracle_sys_col.match(rset.column_name):
                index['column_names'].append(self.normalize_name(rset.column_name))
            last_index_name = rset.index_name
        remove_if_primary_key(index)
        return indexes

Example 48

Project: SickGear Source File: base.py
Function: get_table_names
    @reflection.cache
    def get_table_names(self, connection, schema=None, **kw):
        # there are two queries commonly mentioned for this.
        # this one, using view_blr, is at the Firebird FAQ among other places:
        # http://www.firebirdfaq.org/faq174/
        s = """
        select rdb$relation_name
        from rdb$relations
        where rdb$view_blr is null
        and (rdb$system_flag is null or rdb$system_flag = 0);
        """

        # the other query is this one.  It's not clear if there's really
        # any difference between these two.  This link:
        # http://www.alberton.info/firebird_sql_meta_info.html#.Ur3vXfZGni8
        # states them as interchangeable.  Some discussion at [ticket:2898]
        # SELECT DISTINCT rdb$relation_name
        # FROM rdb$relation_fields
        # WHERE rdb$system_flag=0 AND rdb$view_context IS NULL

        return [self.normalize_name(row[0]) for row in connection.execute(s)]

Example 49

Project: SickGear Source File: base.py
Function: get_column_sequence
    @reflection.cache
    def get_column_sequence(self, connection,
                                table_name, column_name,
                                schema=None, **kw):
        tablename = self.denormalize_name(table_name)
        colname = self.denormalize_name(column_name)
        # Heuristic-query to determine the generator associated to a PK field
        genqry = """
        SELECT trigdep.rdb$depended_on_name AS fgenerator
        FROM rdb$dependencies tabdep
             JOIN rdb$dependencies trigdep
                  ON tabdep.rdb$dependent_name=trigdep.rdb$dependent_name
                     AND trigdep.rdb$depended_on_type=14
                     AND trigdep.rdb$dependent_type=2
             JOIN rdb$triggers trig ON
                    trig.rdb$trigger_name=tabdep.rdb$dependent_name
        WHERE tabdep.rdb$depended_on_name=?
          AND tabdep.rdb$depended_on_type=0
          AND trig.rdb$trigger_type=1
          AND tabdep.rdb$field_name=?
          AND (SELECT count(*)
           FROM rdb$dependencies trigdep2
           WHERE trigdep2.rdb$dependent_name = trigdep.rdb$dependent_name) = 2
        """
        genr = connection.execute(genqry, [tablename, colname]).first()
        if genr is not None:
            return dict(name=self.normalize_name(genr['fgenerator']))

Example 50

Project: SickGear Source File: base.py
Function: get_constraint_data
    @reflection.cache
    def _get_constraint_data(self, connection, table_name, schema=None,
                            dblink='', **kw):

        params = {'table_name': table_name}

        text = \
            "SELECT"\
            "\nac.constraint_name,"\
            "\nac.constraint_type,"\
            "\nloc.column_name AS local_column,"\
            "\nrem.table_name AS remote_table,"\
            "\nrem.column_name AS remote_column,"\
            "\nrem.owner AS remote_owner,"\
            "\nloc.position as loc_pos,"\
            "\nrem.position as rem_pos"\
            "\nFROM all_constraints%(dblink)s ac,"\
            "\nall_cons_columns%(dblink)s loc,"\
            "\nall_cons_columns%(dblink)s rem"\
            "\nWHERE ac.table_name = :table_name"\
            "\nAND ac.constraint_type IN ('R','P')"

        if schema is not None:
            params['owner'] = schema
            text += "\nAND ac.owner = :owner"

        text += \
            "\nAND ac.owner = loc.owner"\
            "\nAND ac.constraint_name = loc.constraint_name"\
            "\nAND ac.r_owner = rem.owner(+)"\
            "\nAND ac.r_constraint_name = rem.constraint_name(+)"\
            "\nAND (rem.position IS NULL or loc.position=rem.position)"\
            "\nORDER BY ac.constraint_name, loc.position"

        text = text % {'dblink': dblink}
        rp = connection.execute(sql.text(text), **params)
        constraint_data = rp.fetchall()
        return constraint_data
See More Examples - Go to Next Page
Page 1 Selected Page 2