tablediff.py
author Radek Brich <brich.radek@ifortuna.cz>
Thu, 20 Mar 2014 16:33:34 +0100
changeset 95 6adcb7ee4517
parent 93 b72591087495
permissions -rwxr-xr-x
Update bigtables tool: Add size of indexes.
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
6
4ab077c93b2d Add table diff tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     2
#
41
6aad5e35efe8 PgDataDiff: Fix sorting - do not adapt primary key before sort condition.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
     3
# Print differencies between data in tables.
6
4ab077c93b2d Add table diff tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     4
#
4ab077c93b2d Add table diff tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     5
# Requirements:
41
6aad5e35efe8 PgDataDiff: Fix sorting - do not adapt primary key before sort condition.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
     6
#  * Source table must have defined PRIMARY KEY.
6
4ab077c93b2d Add table diff tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     7
#  * Destination table must contain all columns from source table.
4ab077c93b2d Add table diff tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     8
#    Order is not important.
4ab077c93b2d Add table diff tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     9
#
4ab077c93b2d Add table diff tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    10
34
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    11
from pgtoolkit import toolbase, pgmanager, pgdatadiff
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: 56
diff changeset
    12
from pycolib.ansicolor import highlight, BOLD, YELLOW
6
4ab077c93b2d Add table diff tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    13
4ab077c93b2d Add table diff tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    14
34
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    15
class TableDiffTool(toolbase.SrcDstTablesTool):
6
4ab077c93b2d Add table diff tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    16
    def __init__(self):
56
94e091c23ebb Add reverse parameter for diff tools.
Radek Brich <radek.brich@devl.cz>
parents: 41
diff changeset
    17
        toolbase.SrcDstTablesTool.__init__(self, name='tablediff', desc='Table diff.', allow_reverse = True)
94e091c23ebb Add reverse parameter for diff tools.
Radek Brich <radek.brich@devl.cz>
parents: 41
diff changeset
    18
7
685b20d2d3ab Reorganize directories. PgDataDiff - reworked. PgManager - add fetchone_adapted, fetchall_adapted to cursor.
Radek Brich <radek.brich@devl.cz>
parents: 6
diff changeset
    19
        self.parser.add_argument('--sql', action='store_true', help='Output is SQL script.')
34
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    20
        self.parser.add_argument('--rowcount', action='store_true', help='Compare number of rows.')
56
94e091c23ebb Add reverse parameter for diff tools.
Radek Brich <radek.brich@devl.cz>
parents: 41
diff changeset
    21
6
4ab077c93b2d Add table diff tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    22
        self.init()
4ab077c93b2d Add table diff tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    23
4ab077c93b2d Add table diff tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    24
    def main(self):
34
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    25
        srcconn = self.pgm.get_conn('src')
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    26
        dstconn = self.pgm.get_conn('dst')
56
94e091c23ebb Add reverse parameter for diff tools.
Radek Brich <radek.brich@devl.cz>
parents: 41
diff changeset
    27
34
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    28
        dd = pgdatadiff.PgDataDiff(srcconn, dstconn)
56
94e091c23ebb Add reverse parameter for diff tools.
Radek Brich <radek.brich@devl.cz>
parents: 41
diff changeset
    29
34
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    30
        for srcschema, srctable, dstschema, dsttable in self.tables():
35
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    31
            print('-- Diff from [%s] %s.%s to [%s] %s.%s' % (
34
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    32
                self.args.src, srcschema, srctable,
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    33
                self.args.dst, dstschema, dsttable))
56
94e091c23ebb Add reverse parameter for diff tools.
Radek Brich <radek.brich@devl.cz>
parents: 41
diff changeset
    34
34
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    35
            if self.args.rowcount:
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    36
                with self.pgm.cursor('src') as curs:
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    37
                    curs.execute('''SELECT count(*) FROM "%s"."%s"''' % (srcschema, srctable))
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    38
                    srccount = curs.fetchone()[0]
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    39
                with self.pgm.cursor('dst') as curs:
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    40
                    curs.execute('''SELECT count(*) FROM "%s"."%s"''' % (dstschema, dsttable))
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    41
                    dstcount = curs.fetchone()[0]
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    42
                if srccount != dstcount:
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    43
                    print(highlight(1, BOLD | YELLOW), "Row count differs: src=%s dst=%s" % (srccount, dstcount), highlight(0), sep='')
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    44
                continue
56
94e091c23ebb Add reverse parameter for diff tools.
Radek Brich <radek.brich@devl.cz>
parents: 41
diff changeset
    45
34
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    46
            dd.settable1(srctable, srcschema)
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    47
            dd.settable2(dsttable, dstschema)
56
94e091c23ebb Add reverse parameter for diff tools.
Radek Brich <radek.brich@devl.cz>
parents: 41
diff changeset
    48
34
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    49
            if self.args.sql:
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    50
                dd.print_patch()
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    51
            else:
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    52
                dd.print_diff()
6
4ab077c93b2d Add table diff tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    53
4ab077c93b2d Add table diff tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    54
4ab077c93b2d Add table diff tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    55
tool = TableDiffTool()
4ab077c93b2d Add table diff tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    56
tool.main()
7
685b20d2d3ab Reorganize directories. PgDataDiff - reworked. PgManager - add fetchone_adapted, fetchall_adapted to cursor.
Radek Brich <radek.brich@devl.cz>
parents: 6
diff changeset
    57