tablecopy.py
author Radek Brich <radek.brich@devl.cz>
Thu, 21 Mar 2013 17:40:11 +0100
changeset 80 b830c6f7b30a
parent 56 94e091c23ebb
child 93 b72591087495
permissions -rwxr-xr-x
PgManager: add support for str, repr of RowDict.
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
34
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     1
#!/usr/bin/env python3.2
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     2
#
35
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
     3
# Copy data between tables with same table schema.
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
     4
#
56
94e091c23ebb Add reverse parameter for diff tools.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
     5
# Copies full table, target table must be empty.
35
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
     6
# Can copy multiple tables in one run.
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
     7
# Sorts the tables according to references.
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
     8
#
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
     9
# This may be used instead of dump/restore.
34
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    10
#
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    11
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    12
import io
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    13
35
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    14
from pgtoolkit import toolbase, pgmanager, pgbrowser, pgdatacopy
34
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    15
from pgtoolkit.progresswrapper import ProgressWrapper
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    16
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    17
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    18
class TableCopyTool(toolbase.SrcDstTablesTool):
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    19
    def __init__(self):
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    20
        toolbase.SrcDstTablesTool.__init__(self, name='tablecopy', desc='Table copy tool.')
56
94e091c23ebb Add reverse parameter for diff tools.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
    21
34
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    22
        self.parser.add_argument('-n', '--no-action', dest='noaction', action='store_true',
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    23
            help="Do nothing, just print tables to be copied. Useful in combination with --regex.")
35
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    24
        self.parser.add_argument('--no-sort', dest='nosort', action='store_true',
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    25
            help="Do not sort. By default, tables are sorted by foreign key references.")
56
94e091c23ebb Add reverse parameter for diff tools.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
    26
34
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    27
        self.init()
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    28
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    29
    def main(self):
35
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    30
        self.srcconn = self.pgm.get_conn('src')
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    31
        self.dstconn = self.pgm.get_conn('dst')
56
94e091c23ebb Add reverse parameter for diff tools.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
    32
35
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    33
        dc = pgdatacopy.PgDataCopy(self.srcconn, self.dstconn)
56
94e091c23ebb Add reverse parameter for diff tools.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
    34
35
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    35
        if self.args.nosort:
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    36
            for table in self.tables():
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    37
                self.copy_table(dc, *table)
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    38
        else:
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    39
            # sort tables with respect to references
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    40
            details = dict()
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    41
            pending = set()
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    42
            references = dict()
56
94e091c23ebb Add reverse parameter for diff tools.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
    43
35
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    44
            # build list of all table to be copied (pending) and references map
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    45
            for table in self.tables():
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    46
                srcschema, srctable, dstschema, dsttable = table
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    47
                name = dstschema + '.' + dsttable
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    48
                details[name] = table
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    49
                pending.add(name)
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    50
                references[name] = self.get_references(dstschema, dsttable)
56
94e091c23ebb Add reverse parameter for diff tools.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
    51
35
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    52
            # copy files with fulfilled references, repeat until all done
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    53
            while pending:
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    54
                for name in list(pending):
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    55
                    ok = True
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    56
                    for ref in references[name]:
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    57
                        if ref in pending:
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    58
                            ok = False
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    59
                    if ok:
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    60
                        table = details[name]
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    61
                        self.copy_table(dc, *table)
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    62
                        pending.remove(name)
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    63
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    64
    def get_references(self, schema, table):
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    65
        browser = pgbrowser.PgBrowser(self.dstconn)
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    66
        cons = browser.list_constraints(table, schema)
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    67
        return [con['fschema'] + '.' + con['fname'] for con in cons if con['type'] == 'f']
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    68
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    69
    def copy_table(self, dc, srcschema, srctable, dstschema, dsttable):
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    70
        print('Copying [%s] %s.%s --> [%s] %s.%s' % (
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    71
            self.args.src, srcschema, srctable,
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    72
            self.args.dst, dstschema, dsttable))
56
94e091c23ebb Add reverse parameter for diff tools.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
    73
35
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    74
        if self.args.noaction:
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    75
            return
56
94e091c23ebb Add reverse parameter for diff tools.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
    76
35
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    77
        dc.set_source(srctable, srcschema)
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    78
        dc.set_destination(dsttable, dstschema)
56
94e091c23ebb Add reverse parameter for diff tools.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
    79
35
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    80
        try:
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    81
            dc.check()
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    82
        except pgdatacopy.TargetNotEmptyError as e:
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    83
            print(' - error:', str(e))
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    84
            return
56
94e091c23ebb Add reverse parameter for diff tools.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
    85
35
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    86
        print(' - read                           ')
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    87
        buf = io.BytesIO()
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    88
        wrapped = ProgressWrapper(buf)
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    89
        dc.read(wrapped)
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    90
        data = buf.getvalue()
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    91
        buf.close()
56
94e091c23ebb Add reverse parameter for diff tools.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
    92
35
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    93
        print(' - write                          ')
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    94
        buf = io.BytesIO(data)
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    95
        wrapped = ProgressWrapper(buf, len(data))
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    96
        dc.write(wrapped)
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    97
        buf.close()
56
94e091c23ebb Add reverse parameter for diff tools.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
    98
35
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
    99
        print(' - analyze                        ')
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 34
diff changeset
   100
        dc.analyze()
34
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   101
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   102
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   103
tool = TableCopyTool()
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   104
tool.main()
98c7809af415 Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   105