6 # * First column of both tables must be numerical primary key. |
6 # * First column of both tables must be numerical primary key. |
7 # * Destination table must contain all columns from source table. |
7 # * Destination table must contain all columns from source table. |
8 # Order is not important. |
8 # Order is not important. |
9 # |
9 # |
10 |
10 |
11 from pgtoolkit import pgmanager, pgbrowser, pgdatadiff, toolbase |
11 from pgtoolkit import toolbase, pgmanager, pgdatadiff |
|
12 from pgtoolkit.highlight import * |
12 |
13 |
13 |
14 |
14 class TableDiffTool(toolbase.SrcDstTool): |
15 class TableDiffTool(toolbase.SrcDstTablesTool): |
15 def __init__(self): |
16 def __init__(self): |
16 toolbase.SrcDstTool.__init__(self, name='tablediff', desc='Table diff.') |
17 toolbase.SrcDstTablesTool.__init__(self, name='tablediff', desc='Table diff.') |
17 |
18 |
18 self.parser.add_argument('srctable', metavar='srctable', |
|
19 type=str, help='Source table name.') |
|
20 self.parser.add_argument('--dst-table', dest='dsttable', metavar='dsttable', |
|
21 type=str, default=None, help='Destination table (default=srctable).') |
|
22 self.parser.add_argument('-s', '--src-schema', dest='srcschema', metavar='srcschema', |
|
23 type=str, default='public', help='Schema name (default=public).') |
|
24 self.parser.add_argument('--dst-schema', dest='dstschema', metavar='dstschema', |
|
25 type=str, default=None, help='Destination schema name (default=srcschema).') |
|
26 self.parser.add_argument('--sql', action='store_true', help='Output is SQL script.') |
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.') |
27 |
21 |
28 self.init() |
22 self.init() |
29 |
23 |
30 def main(self): |
24 def main(self): |
31 srcschema = self.args.srcschema |
25 srcconn = self.pgm.get_conn('src') |
32 dstschema = self.args.dstschema if self.args.dstschema else self.args.srcschema |
26 dstconn = self.pgm.get_conn('dst') |
33 |
27 |
34 srctable = self.args.srctable |
28 dd = pgdatadiff.PgDataDiff(srcconn, dstconn) |
35 dsttable = self.args.dsttable if self.args.dsttable else self.args.srctable |
|
36 |
29 |
37 dd = pgdatadiff.PgDataDiff(self.pgm.get_conn('src'), self.pgm.get_conn('dst')) |
30 for srcschema, srctable, dstschema, dsttable in self.tables(): |
38 dd.settable1(srctable, srcschema) |
31 print('Diff from [%s] %s.%s to [%s] %s.%s' % ( |
39 dd.settable2(dsttable, dstschema) |
32 self.args.src, srcschema, srctable, |
40 |
33 self.args.dst, dstschema, dsttable)) |
41 if self.args.sql: |
34 |
42 dd.print_patch() |
35 if self.args.rowcount: |
43 else: |
36 with self.pgm.cursor('src') as curs: |
44 dd.print_diff() |
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() |
45 |
53 |
46 |
54 |
47 tool = TableDiffTool() |
55 tool = TableDiffTool() |
48 tool.main() |
56 tool.main() |
49 |
57 |