1 #!/usr/bin/env python3

     2 #

     3 # Print differencies between data in tables.

     4 #

     5 # Requirements:

     6 #  * Source table must have defined PRIMARY KEY.

     7 #  * Destination table must contain all columns from source table.

     8 #    Order is not important.

     9 #

    10 

    11 from pgtoolkit import toolbase, pgmanager, pgdatadiff

    12 from pycolib.ansicolor import highlight, BOLD, YELLOW

    13 

    14 

    15 class TableDiffTool(toolbase.SrcDstTablesTool):

    16     def __init__(self):

    17         toolbase.SrcDstTablesTool.__init__(self, name='tablediff', desc='Table diff.', allow_reverse = True)

    18 

    19         self.parser.add_argument('--sql', action='store_true', help='Output is SQL script.')

    20         self.parser.add_argument('--rowcount', action='store_true', help='Compare number of rows.')

    21 

    22         self.init()

    23 

    24     def main(self):

    25         srcconn = self.pgm.get_conn('src')

    26         dstconn = self.pgm.get_conn('dst')

    27 

    28         dd = pgdatadiff.PgDataDiff(srcconn, dstconn)

    29 

    30         for srcschema, srctable, dstschema, dsttable in self.tables():

    31             print('-- Diff from [%s] %s.%s to [%s] %s.%s' % (

    32                 self.args.src, srcschema, srctable,

    33                 self.args.dst, dstschema, dsttable))

    34 

    35             if self.args.rowcount:

    36                 with self.pgm.cursor('src') as curs:

    37                     curs.execute('''SELECT count(*) FROM "%s"."%s"''' % (srcschema, srctable))

    38                     srccount = curs.fetchone()[0]

    39                 with self.pgm.cursor('dst') as curs:

    40                     curs.execute('''SELECT count(*) FROM "%s"."%s"''' % (dstschema, dsttable))

    41                     dstcount = curs.fetchone()[0]

    42                 if srccount != dstcount:

    43                     print(highlight(1, BOLD | YELLOW), "Row count differs: src=%s dst=%s" % (srccount, dstcount), highlight(0), sep='')

    44                 continue

    45 

    46             dd.settable1(srctable, srcschema)

    47             dd.settable2(dsttable, dstschema)

    48 

    49             if self.args.sql:

    50                 dd.print_patch()

    51             else:

    52                 dd.print_diff()

    53 

    54 

    55 tool = TableDiffTool()

    56 tool.main()

    57