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 | """ |
---|
13 | Purpose |
---|
14 | ------- |
---|
15 | |
---|
16 | ``oradelete`` prints the delete statements for all tables in an Oracle database |
---|
17 | schema in the correct order (i.e. records will be deleted so that no errors |
---|
18 | happen during script execution). ``oradelete`` can also be used to actually |
---|
19 | make all tables empty. |
---|
20 | |
---|
21 | |
---|
22 | Options |
---|
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 | ``-i``, ``--ignore`` : ``false``, ``no``, ``0``, ``true``, ``yes`` or ``1`` |
---|
47 | If given, errors occuring while the database is read or written will be |
---|
48 | ignored. |
---|
49 | |
---|
50 | ``-e``, ``--encoding`` : encoding |
---|
51 | The encoding of the output (if ``-x`` is not given; default is ``utf-8``). |
---|
52 | |
---|
53 | ``-t``, ``--truncate`` : ``false``, ``no``, ``0``, ``true``, ``yes`` or ``1`` |
---|
54 | If given the script uses the ``TRUNCATE`` command instead of the ``DELETE`` |
---|
55 | command. |
---|
56 | """ |
---|
57 | |
---|
58 | |
---|
59 | import sys, os, argparse |
---|
60 | |
---|
61 | from ll import misc, astyle, orasql |
---|
62 | |
---|
63 | |
---|
64 | __docformat__ = "reStructuredText" |
---|
65 | |
---|
66 | |
---|
67 | s4warning = astyle.Style.fromenv("LL_ORASQL_REPRANSI_WARNING", "red:black") |
---|
68 | s4error = astyle.Style.fromenv("LL_ORASQL_REPRANSI_ERROR", "red:black") |
---|
69 | s4connectstring = astyle.Style.fromenv("LL_ORASQL_REPRANSI_CONNECTSTRING", "yellow:black") |
---|
70 | s4object = astyle.Style.fromenv("LL_ORASQL_REPRANSI_OBJECT", "green:black") |
---|
71 | |
---|
72 | |
---|
73 | def main(args=None): |
---|
74 | 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") |
---|
75 | p.add_argument("connectstring", help="Oracle connect string") |
---|
76 | p.add_argument("-v", "--verbose", dest="verbose", help="Give a progress report? (default %(default)s)", default=False, action=misc.FlagAction) |
---|
77 | p.add_argument("-c", "--color", dest="color", help="Color output (default %(default)s)", default="auto", choices=("yes", "no", "auto")) |
---|
78 | p.add_argument("-s", "--sequences", dest="sequences", help="Reset sequences? (default %(default)s)", default=False, action=misc.FlagAction) |
---|
79 | p.add_argument("-x", "--execute", dest="execute", action=misc.FlagAction, help="immediately execute the commands instead of printing them? (default %(default)s)") |
---|
80 | p.add_argument("-i", "--ignore", dest="ignore", help="Ignore errors? (default %(default)s)", default=False, action=misc.FlagAction) |
---|
81 | p.add_argument("-e", "--encoding", dest="encoding", help="Encoding for output (default %(default)s)", default="utf-8") |
---|
82 | p.add_argument("-t", "--truncate", dest="truncate", help="Truncate tables (instead of deleting)? (default %(default)s)", default=False, action=misc.FlagAction) |
---|
83 | |
---|
84 | args = p.parse_args(args) |
---|
85 | |
---|
86 | if args.color == "yes": |
---|
87 | color = True |
---|
88 | elif args.color == "no": |
---|
89 | color = False |
---|
90 | else: |
---|
91 | color = None |
---|
92 | |
---|
93 | stdout = astyle.Stream(sys.stdout, color) |
---|
94 | stderr = astyle.Stream(sys.stderr, color) |
---|
95 | |
---|
96 | connection = orasql.connect(args.connectstring) |
---|
97 | cursor = connection.cursor() |
---|
98 | |
---|
99 | cs = s4connectstring(connection.connectstring()) |
---|
100 | |
---|
101 | for (i, obj) in enumerate(connection.itertables(schema="user", mode="drop")): |
---|
102 | # Progress report |
---|
103 | if args.verbose: |
---|
104 | msg = "truncating" if args.truncate else "deleting from" |
---|
105 | msg = astyle.style_default("oradelete.py: ", cs, ": {} #{} ".format(msg, i+1), s4object(str(obj))) |
---|
106 | stderr.writeln(msg) |
---|
107 | |
---|
108 | # Print or execute SQL |
---|
109 | if args.execute: |
---|
110 | try: |
---|
111 | fmt = u"truncate table {}" if args.truncate else u"delete from {}" |
---|
112 | cursor.execute(fmt.format(obj.name)) |
---|
113 | except orasql.DatabaseError, exc: |
---|
114 | if not args.ignore or "ORA-01013" in str(exc): |
---|
115 | raise |
---|
116 | stderr.writeln("oradelete.py: ", s4error("{}: {}".format(exc.__class__, str(exc).strip()))) |
---|
117 | else: |
---|
118 | if args.truncate: |
---|
119 | sql = u"truncate table {};\n".format(obj.name) |
---|
120 | else: |
---|
121 | sql = u"delete from {};\n".format(obj.name) |
---|
122 | stdout.write(sql.encode(args.encoding)) |
---|
123 | if not args.truncate: |
---|
124 | connection.commit() |
---|
125 | |
---|
126 | if args.sequences: |
---|
127 | for (i, obj) in enumerate(connection.itersequences(schema="user")): |
---|
128 | # Progress report |
---|
129 | if args.verbose: |
---|
130 | msg = astyle.style_default("oradelete.py: ", cs, ": recreating #{} ".format(i+1), s4object(str(obj))) |
---|
131 | stderr.writeln(msg) |
---|
132 | |
---|
133 | # Print or execute SQL |
---|
134 | if args.execute: |
---|
135 | try: |
---|
136 | sql = obj.createddl(term=False) |
---|
137 | cursor.execute(obj.dropddl(term=False)) |
---|
138 | cursor.execute(sql) |
---|
139 | except orasql.DatabaseError, exc: |
---|
140 | if not args.ignore or "ORA-01013" in str(exc): |
---|
141 | raise |
---|
142 | stderr.writeln("oradelete.py: ", s4error("{}: {}".format(exc.__class__, str(exc).strip()))) |
---|
143 | else: |
---|
144 | sql = obj.dropddl(term=True) + obj.createddl(term=True) |
---|
145 | stdout.write(sql.encode(args.encoding)) |
---|
146 | |
---|
147 | if __name__ == "__main__": |
---|
148 | sys.exit(main()) |
---|