root/livinglogic.python.xist/src/ll/orasql/scripts/orafind.py @ 4451:2eb8cce037b3

Revision 4451:2eb8cce037b3, 6.6 KB (checked in by Walter Doerwald <walter@…>, 8 years ago)

Docstring for orafind.

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``orafind`` can be used to search all tables in an Oracle database schema for
17a string.
18
19
20Options
21-------
22
23``orafind`` supports the following options:
24
25    ``connectstring``
26        An Oracle connectstring.
27
28    ``searchstring``
29        The text to be searched for.
30
31    ``tables``
32        Zero or more tables names. If any table name is specified the search will
33        be limited to those tables. Otherwise all tables will be searched.
34       
35    ``-v``, ``--verbose`` : ``false``, ``no``, ``0``, ``true``, ``yes`` or ``1``
36        Produces output (on stderr) while to datebase is read or written.
37
38    ``-c``, ``--color`` : ``yes``, ``no`` or ``auto``
39        Should the output (when the ``-v`` option is used) be colored. If ``auto``
40        is specified (the default) then the output is colored if stderr is a
41        terminal.
42
43    ``-i``, ``--ignore-case`` : ``false``, ``no``, ``0``, ``true``, ``yes`` or ``1``
44        If given, the search will be case insensitve.
45
46    ``-r``, ``--read-lobs`` : ``false``, ``no``, ``0``, ``true``, ``yes`` or ``1``
47        If given, ``CLOB``\s will be read when printing search results.
48
49    ``-e``, ``--encoding`` : encoding
50        The encoding of the command line arguments (default is ``utf-8``).
51
52Example
53-------
54
55Search for ``spam`` in all tables in the schema ``user@db``. The search is case
56insensitive and ``CLOB``\s will be printed::
57
58    $ orafind user/pwd@db spam -i -r
59"""
60
61
62import sys, os, argparse
63
64from ll import misc, orasql, astyle
65
66
67__docformat__ = "reStructuredText"
68
69
70s4warning = astyle.Style.fromenv("LL_ORASQL_REPRANSI_WARNING", "red:black")
71s4error = astyle.Style.fromenv("LL_ORASQL_REPRANSI_ERROR", "red:black")
72s4comment = astyle.Style.fromenv("LL_ORASQL_REPRANSI_COMMENT", "black:black:bold")
73s4addedfile = astyle.Style.fromenv("LL_ORASQL_REPRANSI_ADDEDFILE", "black:green")
74s4addedline = astyle.Style.fromenv("LL_ORASQL_REPRANSI_ADDEDLINE", "green:black")
75s4removedfile = astyle.Style.fromenv("LL_ORASQL_REPRANSI_REMOVEDFILE", "black:red")
76s4removedline = astyle.Style.fromenv("LL_ORASQL_REPRANSI_REMOVEDLINE", "red:black")
77s4changedfile = astyle.Style.fromenv("LL_ORASQL_REPRANSI_CHANGEDFILE", "black:blue")
78s4changedline = astyle.Style.fromenv("LL_ORASQL_REPRANSI_CHANGEDLINE", "blue:black")
79s4pos = astyle.Style.fromenv("LL_ORASQL_REPRANSI_POS", "black:black:bold")
80s4connectstring = astyle.Style.fromenv("LL_ORASQL_REPRANSI_CONNECTSTRING", "yellow:black")
81s4connid = astyle.Style.fromenv("LL_ORASQL_REPRANSI_NOTE", "yellow:black:bold")
82s4action = astyle.Style.fromenv("LL_ORASQL_REPRANSI_NOTE", "magenta:black")
83s4object = astyle.Style.fromenv("LL_ORASQL_REPRANSI_OBJECT", "green:black")
84
85
86
87def cs(connection):
88    return s4connectstring(connection.connectstring())
89
90
91def df(obj):
92    return s4object(str(obj))
93
94
95def connid(name):
96    return s4connid("[{}]".format(name))
97
98
99def showcomment(out, *texts):
100    out.writeln(s4comment("-- ", *texts))
101
102
103def conflictmarker(prefix, *text):
104    return astyle.style_default(s4error(prefix), " ", *text)
105
106
107def showreport(out, type, countcreate, countdrop, countcollision, countmerge, countmergeconflict):
108    first = True
109    data = (("added", countcreate), ("dropped", countdrop), ("collided", countcollision), ("merged", countmerge), ("mergeconflict", countmergeconflict))
110    for (name, count) in data:
111        if count:
112            if first:
113                out.write(" => ")
114                first = False
115            else:
116                out.write("; ")
117            if name in ("collided", "mergeconflict"):
118                cls = s4error
119            else:
120                cls = s4action
121            if count > 1:
122                msg = "{} {}s {}".format(count, type, name)
123            else:
124                msg = "1 {} {}".format(type, name)
125            out.write(cls(msg))
126    if first:
127        out.write(" => identical")
128    out.writeln()
129
130
131def gettimestamp(obj, cursor, format):
132    try:
133        timestamp = obj.udate(cursor)
134    except orasql.SQLObjectNotFoundError:
135        return "doesn't exist"
136    if timestamp is not None:
137        timestamp = timestamp.strftime(format)
138    else:
139        timestamp = "without timestamp"
140    return timestamp
141
142
143def main(args=None):
144    p = argparse.ArgumentParser(description="Search for a string in all fields of all tables in an Oracle database schema")
145    p.add_argument("connectstring", help="Oracle connect string")
146    p.add_argument("searchstring", help="String to search for")
147    p.add_argument("tables", metavar="table", nargs="*", help="Limit search to those tables")
148    p.add_argument("-v", "--verbose", dest="verbose", help="Give a progress report? (default: %(default)s)", action=misc.FlagAction, default=False)
149    p.add_argument("-c", "--color", dest="color", help="Color output (default: %(default)s)", default="auto", choices=("yes", "no", "auto"))
150    p.add_argument("-i", "--ignore-case", dest="ignorecase", help="Ignore case distinctions? (default: %(default)s)", action=misc.FlagAction, default=False)
151    p.add_argument("-r", "--read-lobs", dest="readlobs", help="Read CLOBs when printing records? (default: %(default)s)", action=misc.FlagAction, default=False)
152    p.add_argument("-e", "--encoding", dest="encoding", help="Encoding of the command line arguments (default: %(default)s)", default="utf-8")
153
154    args = p.parse_args(args)
155
156    if args.color == "yes":
157        color = True
158    elif args.color == "no":
159        color = False
160    else:
161        color = None
162    stdout = astyle.Stream(sys.stdout, color)
163    stderr = astyle.Stream(sys.stderr, color)
164
165    connectstring = args.connectstring.decode(args.encoding)
166    searchstring = args.searchstring.decode(args.encoding)
167    if args.ignorecase:
168        searchstring = searchstring.lower()
169    searchstring = u"%{}%".format(searchstring.replace(u"%", u"%%"))
170    tablenames = [name.decode(args.encoding).lower() for name in args.tables]
171
172    connection = orasql.connect(connectstring, readlobs=args.readlobs)
173    c = connection.cursor()
174
175    tables = list(connection.itertables())
176    for (i, table) in enumerate(tables):
177        skip = tablenames and table.name.lower() not in tablenames
178        if args.verbose:
179            msg = "skipped" if skip else "searching"
180            stderr.writeln("orafind.py: ", df(table), " #", str(i+1), "/", str(len(tables)), ": ", msg)
181        if not skip:
182            where = []
183            for col in table.itercolumns():
184                datatype = col.datatype()
185                if datatype == "clob" or datatype.startswith("varchar2"):
186                    if args.ignorecase:
187                        where.append("lower({}) like :searchstring".format(col.name))
188                    else:
189                        where.append("{} like :searchstring".format(col.name))
190            if not where:
191                continue # no string columns
192            query = "select * from {} where {}".format(table.name, " or ".join(where))
193            c.execute(query, searchstring=searchstring)
194            for r in c:
195                stdout.writeln("orafind.py: in ", df(table), ": ", repr(r))
196    return 0
197
198
199if __name__ == "__main__":
200    sys.exit(main())
Note: See TracBrowser for help on using the browser.