Changeset 857


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).

Files:
8 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
  • trunk/docs/contents/changelog.rst

    r856 r857  
    133133      removed since it is not supported any more since PostgreSQL 7.4.
    134134
     135Version 4.2.2
     136-------------
     137- The get_relations() and get_tables() methods now also return system views
     138  and tables if you set the optional "system" parameter to True.
     139- Fixed a regression when using temporary tables with DB wrapper methods
     140  (thanks to Patrick TJ McPhee for reporting).
     141
    135142Version 4.2.1 (2016-02-18)
    136143--------------------------
  • trunk/docs/contents/pg/db_wrapper.rst

    r810 r857  
    8585------------------------------------------------------------
    8686
    87 .. method:: DB.get_relations(kinds)
     87.. method:: DB.get_relations([kinds], [system])
    8888
    8989    Get the list of relations in connected database
    9090
    9191    :param str kinds: a string or sequence of type letters
     92    :param bool system: whether system relations should be returned
    9293    :returns: all relations of the given kinds in the database
    9394    :rtype: list
    9495
    95 The type letters are ``r`` = ordinary table, ``i`` = index, ``S`` = sequence,
    96 ``v`` = view, ``c`` = composite type, ``s`` = special, ``t`` = TOAST table.
    97 If `kinds` is None or an empty string, all relations are returned (this is
    98 also the default). Although you can do this with a simple select, it is
    99 added here for convenience.
     96This method returns the list of relations in the connected database.  Although
     97you can do this with a simple select, it is added here for convenience.  You
     98can select which kinds of relations you are interested in by passing type
     99letters in the `kinds` parameter.  The type letters are ``r`` = ordinary table,
     100``i`` = index, ``S`` = sequence, ``v`` = view, ``c`` = composite type,
     101``s`` = special, ``t`` = TOAST table.  If `kinds` is None or an empty string,
     102all relations are returned (this is also the default).  If `system` is set to
     103`True`, then system tables and views (temporary tables, toast tables, catalog
     104vies and tables) will be returned as well, otherwise they will be ignored.
    100105
    101106get_tables -- get list of tables in connected database
    102107------------------------------------------------------
    103108
    104 .. method:: DB.get_tables()
     109.. method:: DB.get_tables([system])
    105110
    106111    Get the list of tables in connected database
    107112
     113    :param bool system: whether system tables should be returned
    108114    :returns: all tables in connected database
    109115    :rtype: list
    110116
    111 This is a shortcut for ``get_relations('r')`` that has been added for
     117This is a shortcut for ``get_relations('r', system)`` that has been added for
    112118convenience.
    113119
  • trunk/pg.py

    r849 r857  
    17521752            self.db.query('SELECT datname FROM pg_database').getresult()]
    17531753
    1754     def get_relations(self, kinds=None):
     1754    def get_relations(self, kinds=None, system=False):
    17551755        """Get list of relations in connected database of specified kinds.
    17561756
     
    17581758        Otherwise kinds can be a string or sequence of type letters
    17591759        specifying which kind of relations you want to list.
    1760         """
    1761         where = " AND r.relkind IN (%s)" % ','.join(
    1762             ["'%s'" % k for k in kinds]) if kinds else ''
     1760
     1761        Set the system flag if you want to get the system relations as well.
     1762        """
     1763        where = []
     1764        if kinds:
     1765            where.append("r.relkind IN (%s)" %
     1766                ','.join("'%s'" % k for k in kinds))
     1767        if not system:
     1768            where.append("s.nspname NOT SIMILAR"
     1769                " TO 'pg/_%|information/_schema' ESCAPE '/'")
     1770        where = " WHERE %s" % ' AND '.join(where) if where else ''
    17631771        q = ("SELECT quote_ident(s.nspname)||'.'||quote_ident(r.relname)"
    17641772            " FROM pg_class r"
    1765             " JOIN pg_namespace s ON s.oid = r.relnamespace"
    1766             " WHERE s.nspname NOT SIMILAR"
    1767             " TO 'pg/_%%|information/_schema' ESCAPE '/' %s"
     1773            " JOIN pg_namespace s ON s.oid = r.relnamespace%s"
    17681774            " ORDER BY s.nspname, r.relname") % where
    17691775        return [r[0] for r in self.db.query(q).getresult()]
    17701776
    1771     def get_tables(self):
    1772         """Return list of tables in connected database."""
    1773         return self.get_relations('r')
     1777    def get_tables(self, system=False):
     1778        """Return list of tables in connected database.
     1779
     1780        Set the system flag if you want to get the system tables as well.
     1781        """
     1782        return self.get_relations('r', system)
    17741783
    17751784    def get_attnames(self, table, with_oid=True, flush=False):
  • trunk/tests/test_classic_dbwrapper.py

    r849 r857  
    990990        self.assertEqual(after_tables, before_tables)
    991991
     992    def testGetSystemTables(self):
     993        get_tables = self.db.get_tables
     994        result = get_tables()
     995        self.assertNotIn('pg_catalog.pg_class', result)
     996        self.assertNotIn('information_schema.tables', result)
     997        result = get_tables(system=False)
     998        self.assertNotIn('pg_catalog.pg_class', result)
     999        self.assertNotIn('information_schema.tables', result)
     1000        result = get_tables(system=True)
     1001        self.assertIn('pg_catalog.pg_class', result)
     1002        self.assertNotIn('information_schema.tables', result)
     1003
    9921004    def testGetRelations(self):
    9931005        get_relations = self.db.get_relations
     
    10071019        self.assertNotIn('public.test', result)
    10081020        self.assertNotIn('public.test_view', result)
     1021
     1022    def testGetSystemRelations(self):
     1023        get_relations = self.db.get_relations
     1024        result = get_relations()
     1025        self.assertNotIn('pg_catalog.pg_class', result)
     1026        self.assertNotIn('information_schema.tables', result)
     1027        result = get_relations(system=False)
     1028        self.assertNotIn('pg_catalog.pg_class', result)
     1029        self.assertNotIn('information_schema.tables', result)
     1030        result = get_relations(system=True)
     1031        self.assertIn('pg_catalog.pg_class', result)
     1032        self.assertIn('information_schema.tables', result)
    10091033
    10101034    def testGetAttnames(self):
     
    23082332        q = "select n from test_parent natural join test_child limit 2"
    23092333        self.assertEqual(query(q).getresult(), [(1,)])
     2334
     2335    def testTempCrud(self):
     2336        table = 'test_temp_table'
     2337        self.createTable(table, "n int primary key, t varchar", temporary=True)
     2338        self.db.insert(table, dict(n=1, t='one'))
     2339        self.db.insert(table, dict(n=2, t='too'))
     2340        self.db.insert(table, dict(n=3, t='three'))
     2341        r = self.db.get(table, 2)
     2342        self.assertEqual(r['t'], 'too')
     2343        self.db.update(table, dict(n=2, t='two'))
     2344        r = self.db.get(table, 2)
     2345        self.assertEqual(r['t'], 'two')
     2346        self.db.delete(table, r)
     2347        r = self.db.query('select n, t from %s order by 1' % table).getresult()
     2348        self.assertEqual(r, [(1, 'one'), (3, 'three')])
    23102349
    23112350    def testTruncate(self):
Note: See TracChangeset for help on using the changeset viewer.