root/livinglogic.python.xist/src/ll/orasql/scripts/oradelete.py @ 4521:4d6d811cc30b

Revision 4521:4d6d811cc30b, 7.0 KB (checked in by Walter Doerwald <walter@…>, 8 years ago)

Add --keepjunk, --include and --exclude option to oradelete.

Line 
1#!/usr/bin/env python
2# -*- coding: utf-8 -*-
3
4## Copyright 2005-2011 by LivingLogic AG, Bayreuth/Germany.
5## Copyright 2005-2011 by Walter Dörwald
6##
7## All Rights Reserved
8##
9## See orasql/__init__.py for the license
10
11
12"""
13Purpose
14-------
15
16``oradelete`` prints the delete statements for all tables in an Oracle database
17schema in the correct order (i.e. records will be deleted so that no errors
18happen during script execution). ``oradelete`` can also be used to actually
19make all tables empty.
20
21
22Options
23-------
24
25``oradelete`` supports the following options:
26
27    ``connectstring``
28        An Oracle connectstring.
29
30    ``-v``, ``--verbose`` : ``false``, ``no``, ``0``, ``true``, ``yes`` or ``1``
31        Produces output (on stderr) while to database is read or written.
32
33    ``-c``, ``--color`` : ``yes``, ``no`` or ``auto``
34        Should the output (when the ``-v`` option is used) be colored. If ``auto``
35        is specified (the default) then the output is colored if stderr is a
36        terminal.
37
38    ``-s``, ``--sequences`` : ``false``, ``no``, ``0``, ``true``, ``yes`` or ``1``
39        Should sequences be reset to their initial values?
40
41    ``-x``, ``--execute`` : ``false``, ``no``, ``0``, ``true``, ``yes`` or ``1``
42        When the ``-x`` argument is given the SQL script isn't printed on stdout,
43        but is executed directly. Be careful with this: You *will* have empty
44        tables after ``oradelete -x``.
45
46    ``-k``, ``--keepjunk`` : ``false``, ``no``, ``0``, ``true``, ``yes`` or ``1``
47        If true (the default), database objects that have ``$`` or
48        ``SYS_EXPORT_SCHEMA_`` in their name will be skipped (otherwise these
49        objects will be included in the output).
50
51    ``-i``, ``--ignore`` : ``false``, ``no``, ``0``, ``true``, ``yes`` or ``1``
52        If given, errors occuring while the database is read or written will be
53        ignored.
54
55    ``-e``, ``--encoding`` : encoding
56        The encoding of the output (if ``-x`` is not given; default is ``utf-8``).
57
58    ``-t``, ``--truncate`` : ``false``, ``no``, ``0``, ``true``, ``yes`` or ``1``
59        If given the script uses the ``TRUNCATE`` command instead of the ``DELETE``
60        command.
61
62    ``--include`` : regexp
63        Only include objects in the output if their name contains the regular
64        expression.
65
66    ``--exclude`` : regexp
67        Exclude objects from the output if their name contains the regular
68        expression.
69"""
70
71
72import sys, os, re, argparse
73
74from ll import misc, astyle, orasql
75
76
77__docformat__ = "reStructuredText"
78
79
80s4warning = astyle.Style.fromenv("LL_ORASQL_REPRANSI_WARNING", "red:black")
81s4error = astyle.Style.fromenv("LL_ORASQL_REPRANSI_ERROR", "red:black")
82s4connectstring = astyle.Style.fromenv("LL_ORASQL_REPRANSI_CONNECTSTRING", "yellow:black")
83s4object = astyle.Style.fromenv("LL_ORASQL_REPRANSI_OBJECT", "green:black")
84
85
86def main(args=None):
87    p = argparse.ArgumentParser(description="Print (or execute) SQL for deleting all records from all tables in an Oracle database schema", epilog="For more info see http://www.livinglogic.de/Python/orasql/scripts/oradelete.html")
88    p.add_argument("connectstring", help="Oracle connect string")
89    p.add_argument("-v", "--verbose", dest="verbose", help="Give a progress report? (default %(default)s)", default=False, action=misc.FlagAction)
90    p.add_argument("-c", "--color", dest="color", help="Color output (default %(default)s)", default="auto", choices=("yes", "no", "auto"))
91    p.add_argument("-s", "--sequences", dest="sequences", help="Reset sequences? (default %(default)s)", default=False, action=misc.FlagAction)
92    p.add_argument("-x", "--execute", dest="execute", action=misc.FlagAction, help="immediately execute the commands instead of printing them? (default %(default)s)")
93    p.add_argument("-k", "--keepjunk", dest="keepjunk", help="Output objects with '$' or 'SYS_EXPORT_SCHEMA_' in their name? (default %(default)s)", default=False, action="store_true")
94    p.add_argument("-i", "--ignore", dest="ignore", help="Ignore errors? (default %(default)s)", default=False, action=misc.FlagAction)
95    p.add_argument("-e", "--encoding", dest="encoding", help="Encoding for output (default %(default)s)", default="utf-8")
96    p.add_argument("-t", "--truncate", dest="truncate", help="Truncate tables (instead of deleting)? (default %(default)s)", default=False, action=misc.FlagAction)
97    p.add_argument(      "--include", dest="include", metavar="REGEXP", help="Include only objects whose name contains PATTERN (default: %(default)s)", type=re.compile)
98    p.add_argument(      "--exclude", dest="exclude", metavar="REGEXP", help="Exclude objects whose name contains PATTERN (default: %(default)s)", type=re.compile)
99
100    args = p.parse_args(args)
101
102    if args.color == "yes":
103        color = True
104    elif args.color == "no":
105        color = False
106    else:
107        color = None
108
109    stdout = astyle.Stream(sys.stdout, color)
110    stderr = astyle.Stream(sys.stderr, color)
111
112    connection = orasql.connect(args.connectstring)
113    cursor = connection.cursor()
114
115    cs = s4connectstring(connection.connectstring())
116
117    def keep(obj):
118        if ("$" in obj.name or "/" in obj.name or obj.name.startswith("SYS_EXPORT_SCHEMA_")) and not args.keepjunk:
119            return False
120        if args.include is not None and args.include.search(obj.name) is None:
121            return False
122        if args.exclude is not None and args.exclude.search(obj.name) is not None:
123            return False
124        return True
125
126    for (i, obj) in enumerate(connection.itertables(owner=None, mode="drop")):
127        keepobj = keep(obj)
128        # Progress report
129        if args.verbose:
130            msg = "truncating" if args.truncate else "deleting from"
131            msg = astyle.style_default("oradelete.py: ", cs, ": {} #{} ".format(msg, i+1), s4object(str(obj)))
132            if not keepobj:
133                msg = astyle.style_default(msg, " ", s4warning("(skipped)"))
134            stderr.writeln(msg)
135
136        if keepobj:
137            # Print or execute SQL
138            if args.execute:
139                try:
140                    fmt = u"truncate table {}" if args.truncate else u"delete from {}"
141                    cursor.execute(fmt.format(obj.name))
142                except orasql.DatabaseError, exc:
143                    if not args.ignore or "ORA-01013" in str(exc):
144                        raise
145                    stderr.writeln("oradelete.py: ", s4error("{}: {}".format(exc.__class__, str(exc).strip())))
146            else:
147                if args.truncate:
148                    sql = u"truncate table {};\n".format(obj.name)
149                else:
150                    sql = u"delete from {};\n".format(obj.name)
151                stdout.write(sql.encode(args.encoding))
152    if not args.truncate:
153        connection.commit()
154
155    if args.sequences:
156        for (i, obj) in enumerate(connection.itersequences(owner=None)):
157            keepobj = keep(obj)
158            # Progress report
159            if args.verbose:
160                msg = astyle.style_default("oradelete.py: ", cs, ": recreating #{} ".format(i+1), s4object(str(obj)))
161                if not keepobj:
162                    msg = astyle.style_default(msg, " ", s4warning("(skipped)"))
163                stderr.writeln(msg)
164
165            if keepobj:
166                # Print or execute SQL
167                if args.execute:
168                    try:
169                        sql = obj.createddl(term=False)
170                        cursor.execute(obj.dropddl(term=False))
171                        cursor.execute(sql)
172                    except orasql.DatabaseError, exc:
173                        if not args.ignore or "ORA-01013" in str(exc):
174                            raise
175                        stderr.writeln("oradelete.py: ", s4error("{}: {}".format(exc.__class__, str(exc).strip())))
176                else:
177                    sql = obj.dropddl(term=True) + obj.createddl(term=True)
178                    stdout.write(sql.encode(args.encoding))
179
180if __name__ == "__main__":
181    sys.exit(main())
Note: See TracBrowser for help on using the browser.