root/livinglogic.python.xist/src/ll/orasql/scripts/oracreate.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``oracreate`` prints the DDL of all objects in an Oracle database schema in a
17way that can be used to recreate the schema (i.e. objects will be ordered so
18that no errors happen for non-existant objects during script execution).
19``oracreate`` can also be used to actually recreate the schema.
20
21
22Options
23-------
24
25``oracreate`` 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``, ``--seqcopy`` : ``false``, ``no``, ``0``, ``true``, ``yes`` or ``1``
39        Outputs ``CREATE SEQUENCE`` statements for the existing sequences that have
40        the current value of the sequence as the starting value. (Otherwise the
41        sequences will restart with their initial value)
42
43    ``-x``, ``--execute`` : connectstring
44        When the ``-x`` argument is given the SQL script isn't printed on stdout,
45        but executed in the database specfied as the ``-x`` argument.
46
47    ``-k``, ``--keepjunk`` : ``false``, ``no``, ``0``, ``true``, ``yes`` or ``1``
48        If true, database objects that have ``$`` or ``SYS_EXPORT_SCHEMA_`` in
49        their name will be skipped (otherwise these objects will be included).
50
51    ``-i``, ``--ignore`` : ``false``, ``no``, ``0``, ``true``, ``yes`` or ``1``
52        If true, 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    ``--include`` : regexp
59        Only include objects in the output if their name contains the regular
60        expression.
61
62    ``--exclude`` : regexp
63        Exclude objects from the output if their name contains the regular
64        expression.
65
66Examples
67--------
68
69Print the content of the database schema ``user@db``::
70
71    $ oracreate user/pwd@db >db.sql
72
73Copy the database schema ``user@db`` to ``user2@db2``::
74
75    $ oracreate user/pwd@db -x user2/pwd2@db2 -v
76"""
77
78
79import sys, os, re, argparse
80
81from ll import misc, astyle, orasql
82
83
84__docformat__ = "reStructuredText"
85
86
87s4warning = astyle.Style.fromenv("LL_ORASQL_REPRANSI_WARNING", "red:black")
88s4error = astyle.Style.fromenv("LL_ORASQL_REPRANSI_ERROR", "red:black")
89s4connectstring = astyle.Style.fromenv("LL_ORASQL_REPRANSI_CONNECTSTRING", "yellow:black")
90s4object = astyle.Style.fromenv("LL_ORASQL_REPRANSI_OBJECT", "green:black")
91
92
93def main(args=None):
94    p = argparse.ArgumentParser(description="Print (or execute) the DDL of all objects in an Oracle database schema", epilog="For more info see http://www.livinglogic.de/Python/orasql/scripts/oracreate.html")
95    p.add_argument("connectstring", help="Oracle connect string")
96    p.add_argument("-v", "--verbose", dest="verbose", help="Give a progress report? (default %(default)s)", default=False, action=misc.FlagAction)
97    p.add_argument("-c", "--color", dest="color", help="Color output (default %(default)s)", default="auto", choices=("yes", "no", "auto"))
98    p.add_argument("-s", "--seqcopy", dest="seqcopy", help="copy sequence values? (default %(default)s)", default=False, action=misc.FlagAction)
99    p.add_argument("-x", "--execute", metavar="CONNECTSTRING2", dest="execute", help="Execute in target database")
100    p.add_argument("-k", "--keepjunk", dest="keepjunk", help="Output objects with '$' or 'SYS_EXPORT_SCHEMA_' in their name? (default %(default)s)", default=False, action=misc.FlagAction)
101    p.add_argument("-i", "--ignore", dest="ignore", help="Ignore errors? (default %(default)s)", default=False, action=misc.FlagAction)
102    p.add_argument("-e", "--encoding", dest="encoding", help="Encoding for output (default %(default)s)", default="utf-8")
103    p.add_argument(      "--include", dest="include", metavar="REGEXP", help="Include only objects whose name contains PATTERN (default: %(default)s)", type=re.compile)
104    p.add_argument(      "--exclude", dest="exclude", metavar="REGEXP", help="Exclude objects whose name contains PATTERN (default: %(default)s)", type=re.compile)
105
106    args = p.parse_args(args)
107
108    if args.color == "yes":
109        color = True
110    elif args.color == "no":
111        color = False
112    else:
113        color = None
114    stdout = astyle.Stream(sys.stdout, color)
115    stderr = astyle.Stream(sys.stderr, color)
116
117    connection = orasql.connect(args.connectstring)
118
119    if args.execute:
120        connection2 = orasql.connect(args.execute)
121        cursor2 = connection2.cursor()
122        term = False
123    else:
124        term = True
125
126    cs1 = s4connectstring(connection.connectstring())
127    if args.execute:
128        cs2 = s4connectstring(connection2.connectstring())
129
130    def keep(obj):
131        if obj.owner is not None:
132            return False
133        # output pk, fks etc. only when they belong to a table we do output
134        if isinstance(obj, (orasql.Constraint, orasql.Index)):
135            obj = obj.table()
136        if ("$" in obj.name or "/" in obj.name or obj.name.startswith("SYS_EXPORT_SCHEMA_")) and not args.keepjunk:
137            return False
138        if args.include is not None and args.include.search(obj.name) is None:
139            return False
140        if args.exclude is not None and args.exclude.search(obj.name) is not None:
141            return False
142        return True
143
144    for (i, obj) in enumerate(connection.iterobjects(owner=None, mode="create")):
145        keepobj = keep(obj)
146        if args.verbose:
147            if args.execute:
148                msg = astyle.style_default("oracreate.py: ", cs1, " -> ", cs2, ": fetching/creating #{}".format(i+1))
149            else:
150                msg = astyle.style_default("oracreate.py: ", cs1, " fetching #{}".format(i+1))
151            msg = astyle.style_default(msg, " ", s4object(str(obj)))
152            if not keepobj:
153                msg = astyle.style_default(msg, " ", s4warning("(skipped)"))
154            stderr.writeln(msg)
155
156        if keepobj:
157            if isinstance(obj, orasql.Sequence) and args.seqcopy:
158                ddl = obj.createddlcopy(connection, term)
159            else:
160                ddl = obj.createddl(connection, term)
161            if ddl:
162                if args.execute:
163                    try:
164                        cursor2.execute(ddl)
165                    except orasql.DatabaseError, exc:
166                        if not args.ignore or "ORA-01013" in str(exc):
167                            raise
168                        stderr.writeln("oracreate.py: ", s4error("{}: {}".format(exc.__class__.__name__, str(exc).strip())))
169                else:
170                    stdout.writeln(ddl.encode(args.encoding))
171                    stdout.writeln()
172
173
174if __name__ == "__main__":
175    sys.exit(main())
Note: See TracBrowser for help on using the browser.