Changeset 857 for trunk


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:
trunk
Files:
4 edited

Legend:

Unmodified
Added
Removed
  • 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.