pgtoolkit/tools/listserial.py
changeset 101 2a2d0d5df03b
parent 93 b72591087495
equal deleted inserted replaced
100:d6088dba8fea 101:2a2d0d5df03b
       
     1 from pgtoolkit.toolbase import SimpleTool
       
     2 from pgtoolkit import pgbrowser
       
     3 from pycolib.ansicolor import highlight, WHITE, YELLOW, RED, BOLD
       
     4 
       
     5 
       
     6 class ListSerialTool(SimpleTool):
       
     7 
       
     8     """List sequences near to overflow.
       
     9 
       
    10     Checks all sequences attached to a column of type integer.
       
    11 
       
    12     Highlight dangerous values of sequence:
       
    13     * Yellow - near overflow (90%)
       
    14     * Red - already over...
       
    15 
       
    16     Does not list sequences with value under 50% of range.
       
    17 
       
    18     """
       
    19 
       
    20     max_int = 2147483647
       
    21 
       
    22     def __init__(self):
       
    23         SimpleTool.__init__(self, name='listserial')
       
    24 
       
    25     def main(self):
       
    26         conn = self.pgm.get_conn('target')
       
    27         browser = pgbrowser.PgBrowser(conn)
       
    28         rows = browser.list_sequences()
       
    29         sequences = []
       
    30         for row in rows:
       
    31             if row['related_column_type'] == 'integer':
       
    32                 # read sequence attributes like last_value
       
    33                 q = 'SELECT * FROM "%s"."%s"' % (row['sequence_schema'], row['sequence_name'])
       
    34                 curs = conn.cursor()
       
    35                 curs.execute(q)
       
    36                 attrs = curs.fetchone_dict()
       
    37                 # skip this sequence if its cycled and has safe max_value
       
    38                 if attrs['is_cycled'] and attrs['max_value'] <= self.max_int:
       
    39                     continue
       
    40                 # skip sequences with last_value not yet in half of max_int
       
    41                 if attrs['last_value'] < self.max_int / 2:
       
    42                     continue
       
    43                 # remember rest of sequences
       
    44                 row['attrs'] = attrs
       
    45                 sequences.append(row)
       
    46         # sort most dangerous on top
       
    47         sequences.sort(key=lambda x: x['attrs']['last_value'], reverse=True)
       
    48         # print out what we've found
       
    49         for seq in sequences:
       
    50             print('Sequence:', seq['sequence_schema'] + '.' + seq['sequence_name'])
       
    51             print(' Related:', seq['sequence_schema'] + '.' + seq['related_table'], seq['related_column'], '(' + seq['related_column_type'] + ')')
       
    52             print('  integer max', '2147483647')
       
    53             # colorize last value
       
    54             last_val = seq['attrs']['last_value']
       
    55             col = WHITE + BOLD
       
    56             if last_val > self.max_int * 0.9:
       
    57                 # near max
       
    58                 col = YELLOW + BOLD
       
    59             if last_val > self.max_int:
       
    60                 # over max
       
    61                 col = RED + BOLD
       
    62             print('   last_value', highlight(1, col) + str(last_val) + highlight(0))
       
    63             for key in ('min_value', 'max_value', 'is_cycled'):
       
    64                 print('   ', key, seq['attrs'][key])
       
    65             print()
       
    66 
       
    67 
       
    68 cls = ListSerialTool
       
    69