Changeset 857 for branches/4.x


Ignore:
Timestamp:
Mar 18, 2016, 8:22:21 AM (4 years ago)
Author:
cito
Message:

Add system parameter to get_relations()

Also fix a regression in the 4.x branch when using temporary tables,
related to filtering system tables (as discussed on the mailing list).

Location:
branches/4.x
Files:
4 edited

Legend:

Unmodified
Added
Removed
  • branches/4.x/docs/contents/changelog.rst

    r856 r857  
    11ChangeLog
    22=========
     3
     4Version 4.2.2
     5-------------
     6- The get_relations() and get_tables() methods now also return system views
     7  and tables if you set the optional "system" parameter to True.
     8- Fixed a regression when using temporary tables with DB wrapper methods
     9  (thanks to Patrick TJ McPhee for reporting).
    310
    411Version 4.2.1 (2016-02-18)
  • branches/4.x/docs/contents/pg/db_wrapper.rst

    r775 r857  
    8787------------------------------------------------------------
    8888
    89 .. method:: DB.get_relations(kinds)
     89.. method:: DB.get_relations([kinds], [system])
    9090
    9191    Get the list of relations in connected database
    9292
    9393    :param str kinds: a string or sequence of type letters
     94    :param bool system: whether system relations should be returned
    9495    :returns: all relations of the given kinds in the database
    9596    :rtype: list
    9697
    97 The type letters are ``r`` = ordinary table, ``i`` = index, ``S`` = sequence,
    98 ``v`` = view, ``c`` = composite type, ``s`` = special, ``t`` = TOAST table.
    99 If `kinds` is None or an empty string, all relations are returned (this is
    100 also the default). Although you can do this with a simple select, it is
    101 added here for convenience.
     98This method returns the list of relations in the connected database.  Although
     99you can do this with a simple select, it is added here for convenience.  You
     100can select which kinds of relations you are interested in by passing type
     101letters in the `kinds` parameter.  The type letters are ``r`` = ordinary table,
     102``i`` = index, ``S`` = sequence, ``v`` = view, ``c`` = composite type,
     103``s`` = special, ``t`` = TOAST table.  If `kinds` is None or an empty string,
     104all relations are returned (this is also the default).  If `system` is set to
     105`True`, then system tables and views (temporary tables, toast tables, catalog
     106vies and tables) will be returned as well, otherwise they will be ignored.
    102107
    103108get_tables -- get list of tables in connected database
    104109------------------------------------------------------
    105110
    106 .. method:: DB.get_tables()
     111.. method:: DB.get_tables([system])
    107112
    108113    Get the list of tables in connected database
    109114
     115    :param bool system: whether system tables should be returned
    110116    :returns: all tables in connected database
    111117    :rtype: list
    112118
    113 This is a shortcut for ``get_relations('r')`` that has been added for
     119This is a shortcut for ``get_relations('r', system)`` that has been added for
    114120convenience.
    115121
  • branches/4.x/pg.py

    r775 r857  
    736736                " FROM pg_class r"
    737737                " JOIN pg_namespace s ON s.oid = r.relnamespace"
    738                 " AND s.nspname NOT SIMILAR"
    739                 " TO 'pg/_%|information/_schema' ESCAPE '/'"
    740738                " JOIN pg_attribute a ON a.attrelid = r.oid"
    741739                " AND NOT a.attisdropped"
    742740                " JOIN pg_index i ON i.indrelid = r.oid"
    743                 " AND i.indisprimary AND a.attnum " + any_indkey)
     741                " AND i.indisprimary AND a.attnum %s"
     742                " AND r.relkind IN ('r', 'v')" % any_indkey)
    744743            for r in self.db.query(q).getresult():
    745744                cl, pkey = _join_parts(r[:2]), r[2]
     
    758757            self.db.query('SELECT datname FROM pg_database').getresult()]
    759758
    760     def get_relations(self, kinds=None):
     759    def get_relations(self, kinds=None, system=False):
    761760        """Get list of relations in connected database of specified kinds.
    762761
     
    765764        specifying which kind of relations you want to list.
    766765
    767         """
    768         where = kinds and " AND r.relkind IN (%s)" % ','.join(
    769             ["'%s'" % k for k in kinds]) or ''
     766        Set the system flag if you want to get the system relations as well.
     767        """
     768        where = []
     769        if kinds:
     770            where.append("r.relkind IN (%s)" %
     771                ','.join(["'%s'" % k for k in kinds]))
     772        if not system:
     773            where.append("s.nspname NOT SIMILAR"
     774                " TO 'pg/_%|information/_schema' ESCAPE '/'")
     775        where = where and " WHERE %s" % ' AND '.join(where) or ''
    770776        q = ("SELECT s.nspname, r.relname"
    771777            " FROM pg_class r"
    772             " JOIN pg_namespace s ON s.oid = r.relnamespace"
    773             " WHERE s.nspname NOT SIMILAR"
    774             " TO 'pg/_%%|information/_schema' ESCAPE '/' %s"
     778            " JOIN pg_namespace s ON s.oid = r.relnamespace%s"
    775779            " ORDER BY 1, 2") % where
    776780        return [_join_parts(r) for r in self.db.query(q).getresult()]
    777781
    778     def get_tables(self):
    779         """Return list of tables in connected database."""
    780         return self.get_relations('r')
     782    def get_tables(self, system=False):
     783        """Return list of tables in connected database.
     784
     785        Set the system flag if you want to get the system tables as well.
     786        """
     787        return self.get_relations('r', system)
    781788
    782789    def get_attnames(self, cl, newattnames=None):
     
    802809        if qcl in self._attnames:
    803810            return self._attnames[qcl]
    804         if qcl not in self.get_relations('rv'):
    805             raise _prg_error('Class %s does not exist' % qcl)
    806811
    807812        q = ("SELECT a.attname, t.typname%s"
     
    811816            " JOIN pg_type t ON t.oid = a.atttypid"
    812817            " WHERE s.nspname = $1 AND r.relname = $2"
     818            " AND r.relkind IN ('r', 'v')"
    813819            " AND (a.attnum > 0 OR a.attname = 'oid')"
    814820            " AND NOT a.attisdropped") % (
    815821                self._regtypes and '::regtype' or '',)
    816822        q = self.db.query(q, cl).getresult()
     823        if not q:
     824            r = ("SELECT r.relnamespace"
     825                 " FROM pg_class r"
     826                 " JOIN pg_namespace s ON s.oid = r.relnamespace"
     827                 " WHERE s.nspname =$1 AND r.relname = $2"
     828                 " AND r.relkind IN ('r', 'v') LIMIT 1")
     829            r = self.db.query(r, cl).getresult()
     830            if not r:
     831                raise _prg_error('Class %s does not exist' % qcl)
    817832
    818833        if self._regtypes:
  • branches/4.x/tests/test_classic_dbwrapper.py

    r771 r857  
    766766        self.assertEqual(result2, result1)
    767767
     768    def testGetSystemTables(self):
     769        get_tables = self.db.get_tables
     770        result = get_tables()
     771        self.assertNotIn('pg_catalog.pg_class', result)
     772        self.assertNotIn('information_schema.tables', result)
     773        result = get_tables(system=False)
     774        self.assertNotIn('pg_catalog.pg_class', result)
     775        self.assertNotIn('information_schema.tables', result)
     776        result = get_tables(system=True)
     777        self.assertIn('pg_catalog.pg_class', result)
     778        self.assertNotIn('information_schema.tables', result)
     779
    768780    def testGetRelations(self):
    769781        get_relations = self.db.get_relations
     
    812824            self.assertEqual(attributes, result)
    813825            self.db.query('drop table "%s"' % table)
     826
     827    def testGetSystemRelations(self):
     828        get_relations = self.db.get_relations
     829        result = get_relations()
     830        self.assertNotIn('pg_catalog.pg_class', result)
     831        self.assertNotIn('information_schema.tables', result)
     832        result = get_relations(system=False)
     833        self.assertNotIn('pg_catalog.pg_class', result)
     834        self.assertNotIn('information_schema.tables', result)
     835        result = get_relations(system=True)
     836        self.assertIn('pg_catalog.pg_class', result)
     837        self.assertIn('information_schema.tables', result)
    814838
    815839    def testHasTablePrivilege(self):
     
    12321256        query("drop table test_table")
    12331257
     1258    def testTempCrud(self):
     1259        query = self.db.query
     1260        table = 'test_temp_table'
     1261        query("drop table if exists %s" % table)
     1262        query("create temporary table %s"
     1263              " (n int primary key, t varchar)" % table)
     1264        self.db.insert(table, dict(n=1, t='one'))
     1265        self.db.insert(table, dict(n=2, t='too'))
     1266        self.db.insert(table, dict(n=3, t='three'))
     1267        r = self.db.get(table, 2)
     1268        self.assertEqual(r['t'], 'too')
     1269        self.db.update(table, dict(n=2, t='two'))
     1270        r = self.db.get(table, 2)
     1271        self.assertEqual(r['t'], 'two')
     1272        self.db.delete(table, r)
     1273        r = query('select n, t from %s order by 1' % table).getresult()
     1274        self.assertEqual(r, [(1, 'one'), (3, 'three')])
     1275        query("drop table %s" % table)
     1276
    12341277    def testTruncateRestart(self):
    12351278        truncate = self.db.truncate
Note: See TracChangeset for help on using the changeset viewer.