listserial.py
changeset 40 922d7fb63384
child 83 515fadd3d286
equal deleted inserted replaced
39:0cef3540b69f 40:922d7fb63384
       
     1 #!/usr/bin/env python3.2
       
     2 
       
     3 from pgtoolkit import toolbase, pgbrowser
       
     4 from pgtoolkit.highlight import highlight, WHITE, YELLOW, RED, BOLD
       
     5 
       
     6 
       
     7 class ListSerialTool(toolbase.SimpleTool):
       
     8     max_int = 2147483647
       
     9     
       
    10     def __init__(self):
       
    11         toolbase.SimpleTool.__init__(self, name='listserial', desc='List sequences attached to column of type integer with dangerous last_value.')
       
    12         self.init()
       
    13     
       
    14     def main(self):
       
    15         conn = self.pgm.get_conn('target')
       
    16         browser = pgbrowser.PgBrowser(conn)
       
    17         rows = browser.list_sequences()
       
    18         sequences = []
       
    19         for row in rows:
       
    20             if row['related_column_type'] == 'integer':
       
    21                 # read sequence attributes like last_value
       
    22                 q = 'SELECT * FROM "%s"."%s"' % (row['sequence_schema'], row['sequence_name'])
       
    23                 curs = conn.cursor()
       
    24                 curs.execute(q)
       
    25                 attrs = curs.fetchone_dict()
       
    26                 # skip this sequence if its cycled and has safe max_value
       
    27                 if attrs['is_cycled'] and attrs['max_value'] <= self.max_int:
       
    28                     continue
       
    29                 # skip sequences with last_value not yet in half of max_int
       
    30                 if attrs['last_value'] < self.max_int / 2:
       
    31                     continue
       
    32                 # remember rest of sequences
       
    33                 row['attrs'] = attrs
       
    34                 sequences.append(row)
       
    35         # sort most dangerous on top
       
    36         sequences.sort(key=lambda x: x['attrs']['last_value'], reverse=True)
       
    37         # print out what we've found
       
    38         for seq in sequences:
       
    39             print('Sequence:', seq['sequence_schema'] + '.' + seq['sequence_name'])
       
    40             print(' Related:', seq['sequence_schema'] + '.' + seq['related_table'], seq['related_column'], '(' + seq['related_column_type'] + ')')
       
    41             print('  integer max', '2147483647')
       
    42             # colorize last value
       
    43             last_val = seq['attrs']['last_value']
       
    44             col = WHITE + BOLD
       
    45             if last_val > self.max_int * 0.9:
       
    46                 # near max
       
    47                 col = YELLOW + BOLD
       
    48             if last_val > self.max_int:
       
    49                 # over max
       
    50                 col = RED + BOLD
       
    51             print('   last_value', highlight(1, col) + str(last_val) + highlight(0))
       
    52             for key in ('min_value', 'max_value', 'is_cycled'):
       
    53                 print('   ', key, seq['attrs'][key])
       
    54             print()
       
    55 
       
    56 
       
    57 tool = ListSerialTool()
       
    58 tool.main()
       
    59