root/livinglogic.python.xist/src/ll/orasql/scripts/oradrop.py @ 4519:c4c0660af381

Revision 4519:c4c0660af381, 6.4 KB (checked in by Walter Doerwald <walter@…>, 8 years ago)

Added options --include and --exclude to oracreate, oradrop and oragrant.

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``oradrop`` prints the drop statements for all objects in an Oracle database
17schema in the correct order (i.e. objects will be dropped so that no errors
18happen during script execution). ``oradrop`` can also be used to actually
19make the schema empty.
20
21
22Options
23-------
24
25``oradrop`` 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    ``-f``, ``--fks`` : ``keep``, ``disable``, ``drop``
39        Specifies how foreign keys from other schemas pointing to this schema
40        should be treated: ``keep`` will now change the foreign keys in any way
41        (this *will* lead to errors); ``disable`` will disable the foreign keys
42        and ``drop`` will drop them completely.
43
44    ``-x``, ``--execute`` : ``false``, ``no``, ``0``, ``true``, ``yes`` or ``1``
45        When the ``-x`` argument is given the SQL script isn't printed on stdout,
46        but is executed directly. Be careful with this: You *will* have an empty
47        schema after ``oradrop -x``.
48
49    ``-k``, ``--keepjunk`` : ``false``, ``no``, ``0``, ``true``, ``yes`` or ``1``
50        If true (the default), database objects that have ``$`` or
51        ``SYS_EXPORT_SCHEMA_`` in their name will be skipped (otherwise these
52        objects will be included in the output).
53
54    ``-i``, ``--ignore`` : ``false``, ``no``, ``0``, ``true``, ``yes`` or ``1``
55        If true, errors occuring while the database is read or written will be
56        ignored.
57
58    ``-e``, ``--encoding`` : encoding
59        The encoding of the output (if ``-x`` is not given; default is ``utf-8``).
60
61    ``--include`` : regexp
62        Only include objects in the output if their name contains the regular
63        expression.
64
65    ``--exclude`` : regexp
66        Exclude objects from the output if their name contains the regular
67        expression.
68
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) drop statements for all objects in an Oracle database schema", epilog="For more info see http://www.livinglogic.de/Python/orasql/scripts/oradrop.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)", action=misc.FlagAction, default=False)
90    p.add_argument("-c", "--color", dest="color", help="Color output (default %(default)s)", default="auto", choices=("yes", "no", "auto"))
91    p.add_argument("-f", "--fks", dest="fks", help="How should foreign keys from other schemas be treated? (default %(default)s)", default="disable", choices=("keep", "disable", "drop"))
92    p.add_argument("-x", "--execute", dest="execute", help="immediately execute the commands instead of printing them? (default %(default)s)", action=misc.FlagAction, default=False)
93    p.add_argument("-k", "--keepjunk", dest="keepjunk", help="Output objects with '$' in their name? (default %(default)s)", action=misc.FlagAction, default=False)
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(      "--include", dest="include", metavar="REGEXP", help="Include only objects whose name contains PATTERN (default: %(default)s)", type=re.compile)
97    p.add_argument(      "--exclude", dest="exclude", metavar="REGEXP", help="Exclude objects whose name contains PATTERN (default: %(default)s)", type=re.compile)
98
99    args = p.parse_args(args)
100
101    if args.color == "yes":
102        color = True
103    elif args.color == "no":
104        color = False
105    else:
106        color = None
107
108    stdout = astyle.Stream(sys.stdout, color)
109    stderr = astyle.Stream(sys.stderr, color)
110
111    connection = orasql.connect(args.connectstring)
112
113    term = not args.execute
114
115    cs = s4connectstring(connection.connectstring())
116
117    def keep(obj):
118        if obj.owner is not None and not isinstance(obj, orasql.ForeignKey):
119            return False
120        if ("$" in obj.name or obj.name.startswith("SYS_EXPORT_SCHEMA_")) and not args.keepjunk:
121            return False
122        if args.include is not None and args.include.search(obj.name) is None:
123            return False
124        if args.exclude is not None and args.exclude.search(obj.name) is not None:
125            return False
126        return True
127
128    ddls = []
129    for (i, obj) in enumerate(connection.iterobjects(owner=None, mode="drop")):
130        keepdef = keep(obj)
131        # Get DDL
132        ddl = ""
133        action = "skipped"
134        if obj.owner is not None:
135            if isinstance(obj, orasql.ForeignKey):
136                if args.fks == "disable":
137                    ddl = obj.disableddl(cursor, term)
138                    action = "disabled"
139                elif args.fks == "drop":
140                    ddl = obj.dropddl(cursor, term)
141                    action = None
142        elif keepdef:
143            ddl = obj.dropddl(connection, term)
144            action = None
145
146        # Progress report
147        if args.verbose:
148            msg = astyle.style_default("oradrop.py: ", cs, ": fetching #{} ".format(i+1), s4object(str(obj)))
149            if action is not None:
150                msg = astyle.style_default(msg, " ", s4warning("({})".format(action)))
151            stderr.writeln(msg)
152
153        if ddl:
154            # Print or execute DDL
155            if args.execute:
156                ddls.append((obj, ddl))
157            else:
158                stdout.write(ddl.encode(args.encoding))
159
160    # Execute DDL
161    if args.execute:
162        cursor = connection.cursor()
163        for (i, (obj, ddl)) in enumerate(ddls):
164            if args.verbose:
165                stderr.writeln("oradrop.py: ", cs, ": dropping #{}/{} ".format(i+1, len(ddls)), s4object(str(obj)))
166            try:
167                cursor.execute(ddl)
168            except orasql.DatabaseError, exc:
169                if not args.ignore or "ORA-01013" in str(exc):
170                    raise
171                stderr.writeln("oradrop.py: ", s4error("{}: {}".format(exc.__class__, str(exc).strip())))
172
173
174if __name__ == "__main__":
175    sys.exit(main())
Note: See TracBrowser for help on using the browser.