# HG changeset patch
# User Radek Brich <radek.brich@devl.cz>
# Date 1345194442 -7200
# Node ID 922d7fb63384aa05f771983d36f501401031f84e
# Parent  0cef3540b69f491aff06ad50e6918ce9be50b1ef
Add listserial tool. PgBrowser: Add list_sequences.

diff -r 0cef3540b69f -r 922d7fb63384 listserial.py
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/listserial.py	Fri Aug 17 11:07:22 2012 +0200
@@ -0,0 +1,59 @@
+#!/usr/bin/env python3.2
+
+from pgtoolkit import toolbase, pgbrowser
+from pgtoolkit.highlight import highlight, WHITE, YELLOW, RED, BOLD
+
+
+class ListSerialTool(toolbase.SimpleTool):
+    max_int = 2147483647
+    
+    def __init__(self):
+        toolbase.SimpleTool.__init__(self, name='listserial', desc='List sequences attached to column of type integer with dangerous last_value.')
+        self.init()
+    
+    def main(self):
+        conn = self.pgm.get_conn('target')
+        browser = pgbrowser.PgBrowser(conn)
+        rows = browser.list_sequences()
+        sequences = []
+        for row in rows:
+            if row['related_column_type'] == 'integer':
+                # read sequence attributes like last_value
+                q = 'SELECT * FROM "%s"."%s"' % (row['sequence_schema'], row['sequence_name'])
+                curs = conn.cursor()
+                curs.execute(q)
+                attrs = curs.fetchone_dict()
+                # skip this sequence if its cycled and has safe max_value
+                if attrs['is_cycled'] and attrs['max_value'] <= self.max_int:
+                    continue
+                # skip sequences with last_value not yet in half of max_int
+                if attrs['last_value'] < self.max_int / 2:
+                    continue
+                # remember rest of sequences
+                row['attrs'] = attrs
+                sequences.append(row)
+        # sort most dangerous on top
+        sequences.sort(key=lambda x: x['attrs']['last_value'], reverse=True)
+        # print out what we've found
+        for seq in sequences:
+            print('Sequence:', seq['sequence_schema'] + '.' + seq['sequence_name'])
+            print(' Related:', seq['sequence_schema'] + '.' + seq['related_table'], seq['related_column'], '(' + seq['related_column_type'] + ')')
+            print('  integer max', '2147483647')
+            # colorize last value
+            last_val = seq['attrs']['last_value']
+            col = WHITE + BOLD
+            if last_val > self.max_int * 0.9:
+                # near max
+                col = YELLOW + BOLD
+            if last_val > self.max_int:
+                # over max
+                col = RED + BOLD
+            print('   last_value', highlight(1, col) + str(last_val) + highlight(0))
+            for key in ('min_value', 'max_value', 'is_cycled'):
+                print('   ', key, seq['attrs'][key])
+            print()
+
+
+tool = ListSerialTool()
+tool.main()
+
diff -r 0cef3540b69f -r 922d7fb63384 pgtoolkit/pgbrowser.py
--- a/pgtoolkit/pgbrowser.py	Mon Jul 09 10:29:47 2012 +0200
+++ b/pgtoolkit/pgbrowser.py	Fri Aug 17 11:07:22 2012 +0200
@@ -42,11 +42,13 @@
         
 
 class Constraint:
-    def __init__(self, browser, table, name, type, definition):
+    def __init__(self, browser, table, name, type, fname, fschema, definition):
         self.browser = browser
         self.table = table
         self.name = name
         self.type = type
+        self.fname = fname  # foreign table name
+        self.fschema = fschema  # foreign table schema
         self.definition = definition
 
 
@@ -211,7 +213,7 @@
     def list_columns(self, table, schema='public', order=2):
         return self._query('''
             SELECT
-                a.attrelid,
+                --a.attrelid,
                 a.attname as "name",
                 format_type(a.atttypid, a.atttypmod) AS "type",
                 a.attnotnull as "notnull",
@@ -261,6 +263,24 @@
             ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname
             ''', {'schema': schema, 'table': table})
 
+    def list_sequences(self, schema=None):
+        '''List sequences in schema.'''
+        return self._query('''
+            SELECT
+                nc.nspname AS "sequence_schema",
+                c.relname AS "sequence_name",
+                t.relname AS "related_table",
+                a.attname AS "related_column",
+                format_type(a.atttypid, a.atttypmod) AS "related_column_type"
+            FROM pg_class c
+            JOIN pg_namespace nc ON nc.oid = c.relnamespace
+            JOIN pg_depend d ON d.objid = c.oid
+            JOIN pg_class t ON d.refobjid = t.oid
+            JOIN pg_attribute a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum)
+            WHERE c.relkind = 'S' AND NOT pg_is_other_temp_schema(nc.oid)
+            ''' + (schema and ' AND nc.nspname = %(schema)s' or '') + '''
+        ''', {'schema': schema})
+
     def list_column_usage(self, table, column, schema='public'):
         '''List objects using the column.