listserial.py
author Radek Brich <radek.brich@devl.cz>
Tue, 04 Feb 2014 16:10:04 +0100
changeset 94 a10f553e6f6a
parent 93 b72591087495
permissions -rwxr-xr-x
PgDiff: Add patch support for SQL functions.
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
93
b72591087495 Change python3.2 to python3 in scripts.
Radek Brich <brich.radek@ifortuna.cz>
parents: 83
diff changeset
     1
#!/usr/bin/env python3
40
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     2
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     3
from pgtoolkit import toolbase, pgbrowser
83
515fadd3d286 Add dependency on pycolib. Move common modules to pycolib. Add example table schema for meta DB.
Radek Brich <radek.brich@devl.cz>
parents: 40
diff changeset
     4
from pycolib.ansicolor import highlight, WHITE, YELLOW, RED, BOLD
40
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     5
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     6
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     7
class ListSerialTool(toolbase.SimpleTool):
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     8
    max_int = 2147483647
83
515fadd3d286 Add dependency on pycolib. Move common modules to pycolib. Add example table schema for meta DB.
Radek Brich <radek.brich@devl.cz>
parents: 40
diff changeset
     9
40
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    10
    def __init__(self):
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    11
        toolbase.SimpleTool.__init__(self, name='listserial', desc='List sequences attached to column of type integer with dangerous last_value.')
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    12
        self.init()
83
515fadd3d286 Add dependency on pycolib. Move common modules to pycolib. Add example table schema for meta DB.
Radek Brich <radek.brich@devl.cz>
parents: 40
diff changeset
    13
40
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    14
    def main(self):
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    15
        conn = self.pgm.get_conn('target')
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    16
        browser = pgbrowser.PgBrowser(conn)
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    17
        rows = browser.list_sequences()
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    18
        sequences = []
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    19
        for row in rows:
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    20
            if row['related_column_type'] == 'integer':
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    21
                # read sequence attributes like last_value
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    22
                q = 'SELECT * FROM "%s"."%s"' % (row['sequence_schema'], row['sequence_name'])
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    23
                curs = conn.cursor()
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    24
                curs.execute(q)
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    25
                attrs = curs.fetchone_dict()
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    26
                # skip this sequence if its cycled and has safe max_value
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    27
                if attrs['is_cycled'] and attrs['max_value'] <= self.max_int:
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    28
                    continue
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    29
                # skip sequences with last_value not yet in half of max_int
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    30
                if attrs['last_value'] < self.max_int / 2:
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    31
                    continue
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    32
                # remember rest of sequences
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    33
                row['attrs'] = attrs
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    34
                sequences.append(row)
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    35
        # sort most dangerous on top
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    36
        sequences.sort(key=lambda x: x['attrs']['last_value'], reverse=True)
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    37
        # print out what we've found
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    38
        for seq in sequences:
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    39
            print('Sequence:', seq['sequence_schema'] + '.' + seq['sequence_name'])
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    40
            print(' Related:', seq['sequence_schema'] + '.' + seq['related_table'], seq['related_column'], '(' + seq['related_column_type'] + ')')
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    41
            print('  integer max', '2147483647')
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    42
            # colorize last value
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    43
            last_val = seq['attrs']['last_value']
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    44
            col = WHITE + BOLD
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    45
            if last_val > self.max_int * 0.9:
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    46
                # near max
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    47
                col = YELLOW + BOLD
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    48
            if last_val > self.max_int:
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    49
                # over max
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    50
                col = RED + BOLD
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    51
            print('   last_value', highlight(1, col) + str(last_val) + highlight(0))
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    52
            for key in ('min_value', 'max_value', 'is_cycled'):
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    53
                print('   ', key, seq['attrs'][key])
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    54
            print()
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    55
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    56
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    57
tool = ListSerialTool()
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    58
tool.main()
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    59