pgtoolkit/tools/listserial.py
author Radek Brich <brich.radek@ifortuna.cz>
Tue, 06 May 2014 18:37:43 +0200
changeset 101 2a2d0d5df03b
parent 93 listserial.py@b72591087495
permissions -rw-r--r--
Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
101
2a2d0d5df03b Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents: 93
diff changeset
     1
from pgtoolkit.toolbase import SimpleTool
2a2d0d5df03b Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents: 93
diff changeset
     2
from pgtoolkit import 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
     3
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
     4
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     5
101
2a2d0d5df03b Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents: 93
diff changeset
     6
class ListSerialTool(SimpleTool):
2a2d0d5df03b Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents: 93
diff changeset
     7
2a2d0d5df03b Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents: 93
diff changeset
     8
    """List sequences near to overflow.
2a2d0d5df03b Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents: 93
diff changeset
     9
2a2d0d5df03b Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents: 93
diff changeset
    10
    Checks all sequences attached to a column of type integer.
2a2d0d5df03b Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents: 93
diff changeset
    11
2a2d0d5df03b Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents: 93
diff changeset
    12
    Highlight dangerous values of sequence:
2a2d0d5df03b Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents: 93
diff changeset
    13
    * Yellow - near overflow (90%)
2a2d0d5df03b Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents: 93
diff changeset
    14
    * Red - already over...
2a2d0d5df03b Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents: 93
diff changeset
    15
2a2d0d5df03b Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents: 93
diff changeset
    16
    Does not list sequences with value under 50% of range.
2a2d0d5df03b Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents: 93
diff changeset
    17
2a2d0d5df03b Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents: 93
diff changeset
    18
    """
2a2d0d5df03b Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents: 93
diff changeset
    19
40
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    20
    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
    21
40
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    22
    def __init__(self):
101
2a2d0d5df03b Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents: 93
diff changeset
    23
        SimpleTool.__init__(self, name='listserial')
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
    24
40
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    25
    def main(self):
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    26
        conn = self.pgm.get_conn('target')
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    27
        browser = pgbrowser.PgBrowser(conn)
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    28
        rows = browser.list_sequences()
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    29
        sequences = []
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    30
        for row in rows:
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    31
            if row['related_column_type'] == 'integer':
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    32
                # read sequence attributes like last_value
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    33
                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
    34
                curs = conn.cursor()
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    35
                curs.execute(q)
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    36
                attrs = curs.fetchone_dict()
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    37
                # 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
    38
                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
    39
                    continue
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    40
                # 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
    41
                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
    42
                    continue
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    43
                # remember rest of sequences
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    44
                row['attrs'] = attrs
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    45
                sequences.append(row)
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    46
        # sort most dangerous on top
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    47
        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
    48
        # print out what we've found
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    49
        for seq in sequences:
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    50
            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
    51
            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
    52
            print('  integer max', '2147483647')
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    53
            # colorize last value
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    54
            last_val = seq['attrs']['last_value']
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    55
            col = WHITE + BOLD
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    56
            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
    57
                # near max
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    58
                col = YELLOW + BOLD
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    59
            if last_val > self.max_int:
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    60
                # over max
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    61
                col = RED + BOLD
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    62
            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
    63
            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
    64
                print('   ', key, seq['attrs'][key])
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    65
            print()
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    66
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    67
101
2a2d0d5df03b Refactor ToolBase to allow tool composition. Add TableSync tool (composited). Move more tools under pgtool.
Radek Brich <brich.radek@ifortuna.cz>
parents: 93
diff changeset
    68
cls = ListSerialTool
40
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    69