tablediff.py
author Radek Brich <radek.brich@devl.cz>
Tue, 11 Dec 2012 10:49:42 +0100
changeset 52 26121a8fe78b
parent 41 6aad5e35efe8
child 56 94e091c23ebb
permissions -rwxr-xr-x
Update analyzeall tool: add REINDEX option. Add ibrowser tool (useful for PgBrowser testing). Fix PgBrowser.list_columns default value.
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
6
4ab077c93b2d Add table diff tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     1
#!/usr/bin/env python3.2
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
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    12
from pgtoolkit.highlight import *
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):
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
    17
        toolbase.SrcDstTablesTool.__init__(self, name='tablediff', desc='Table diff.')
6
4ab077c93b2d Add table diff tool.
Radek Brich <radek.brich@devl.cz>
parents:
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.')
6
4ab077c93b2d Add table diff tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    21
        
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')
14
a900bc629ecc TableDiffTool: add arguments to set different destination schema and table. PgDataDiff: fixes.
Radek Brich <radek.brich@devl.cz>
parents: 9
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)
14
a900bc629ecc TableDiffTool: add arguments to set different destination schema and table. PgDataDiff: fixes.
Radek Brich <radek.brich@devl.cz>
parents: 9
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))
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    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
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    45
            
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)
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents: 14
diff changeset
    48
            
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