Mercurial > sqlpython

comparison sqlpython/sqlpyPlus.py @ 189:c5398d87498e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
cat bug
author catherine@dellzilla
date Mon, 17 Nov 2008 14:26:53 -0500
parents
children e9d0492d7358
comparison
equal deleted inserted replaced
188:4a639619814a 189:c5398d87498e
1 """sqlpyPlus - extra features (inspired by Oracle SQL*Plus) for Luca Canali's sqlpython.py
2
3 Features include:
4 - SQL*Plus-style bind variables
5 - `set autobind on` stores single-line result sets in bind variables automatically
6 - SQL buffer with list, run, ed, get, etc.; unlike SQL*Plus, buffer stores session's full history
7 - @script.sql loads and runs (like SQL*Plus)
8 - ! runs operating-system command
9 - show and set to control sqlpython parameters
10 - SQL*Plus-style describe, spool
11 - write sends query result directly to file
12 - comments shows table and column comments
13 - compare ... to ... graphically compares results of two queries
14 - commands are case-insensitive
15 - context-sensitive tab-completion for table names, column names, etc.
16
17 Use 'help' within sqlpython for details.
18
19 Set bind variables the hard (SQL*Plus) way
20 exec :b = 3
21 or with a python-like shorthand
22 :b = 3
23
24 - catherinedevlin.blogspot.com May 31, 2006
25 """
26 import sys, os, re, sqlpython, cx_Oracle, pyparsing, re, completion, datetime, pickle
27 from cmd2 import Cmd, make_option, options, Statekeeper, Cmd2TestCase
28 from output_templates import output_templates
29 from plothandler import Plot
30 try:
31 import pylab
32 except:
33 pass
34
35 descQueries = {
36 'TABLE': ("""
37 atc.column_name,
38 CASE atc.nullable WHEN 'Y' THEN 'NULL' ELSE 'NOT NULL' END "Null?",
39 atc.data_type ||
40 CASE atc.data_type WHEN 'DATE' THEN ''
41 ELSE '(' ||
42 CASE atc.data_type WHEN 'NUMBER' THEN TO_CHAR(atc.data_precision) ||
43 CASE atc.data_scale WHEN 0 THEN ''
44 ELSE ',' || TO_CHAR(atc.data_scale) END
45 ELSE TO_CHAR(atc.data_length) END
46 END ||
47 CASE atc.data_type WHEN 'DATE' THEN '' ELSE ')' END
48 data_type
49 FROM all_tab_columns atc
50 WHERE atc.table_name = :object_name
51 AND atc.owner = :owner
52 ORDER BY atc.column_id;""",),
53 'PROCEDURE': ("""
54 NVL(argument_name, 'Return Value') argument_name,
55 data_type,
56 in_out,
57 default_value
58 FROM all_arguments
59 WHERE object_name = :object_name
60 AND owner = :owner
61 AND package_name IS NULL
62 ORDER BY sequence;""",),
63 'PackageObjects':("""
64 SELECT DISTINCT object_name
65 FROM all_arguments
66 WHERE package_name = :package_name
67 AND owner = :owner""",),
68 'PackageObjArgs':("""
69 object_name,
70 argument_name,
71 data_type,
72 in_out,
73 default_value
74 FROM all_arguments
75 WHERE package_name = :package_name
76 AND object_name = :object_name
77 AND owner = :owner
78 AND argument_name IS NOT NULL
79 ORDER BY sequence""",),
80 'TRIGGER':("""
81 description
82 FROM all_triggers
83 WHERE owner = :owner
84 AND trigger_name = :object_name
85 """,
86 """
87 table_owner,
88 base_object_type,
89 table_name,
90 column_name,
91 when_clause,
92 status,
93 action_type,
94 crossedition
95 FROM all_triggers
96 WHERE owner = :owner
97 AND trigger_name = :object_name
98 \\t
99 """,
100 ),
101 'INDEX':("""
102 index_type,
103 table_owner,
104 table_name,
105 table_type,
106 uniqueness,
107 compression,
108 partitioned,
109 temporary,
110 generated,
111 secondary,
112 dropped,
113 visibility
114 FROM all_indexes
115 WHERE owner = :owner
116 AND index_name = :object_name
117 \\t
118 """,)
119 }
120 descQueries['VIEW'] = descQueries['TABLE']
121 descQueries['FUNCTION'] = descQueries['PROCEDURE']
122
123 queries = {
124 'resolve': """
125 SELECT object_type, object_name, owner FROM (
126 SELECT object_type, object_name, user owner, 1 priority
127 FROM user_objects
128 WHERE object_name = :objName
129 UNION ALL
130 SELECT ao.object_type, ao.object_name, ao.owner, 2 priority
131 FROM all_objects ao
132 JOIN user_synonyms us ON (us.table_owner = ao.owner AND us.table_name = ao.object_name)
133 WHERE us.synonym_name = :objName
134 AND ao.object_type != 'SYNONYM'
135 UNION ALL
136 SELECT ao.object_type, ao.object_name, ao.owner, 3 priority
137 FROM all_objects ao
138 JOIN all_synonyms asyn ON (asyn.table_owner = ao.owner AND asyn.table_name = ao.object_name)
139 WHERE asyn.synonym_name = :objName
140 AND ao.object_type != 'SYNONYM'
141 AND asyn.owner = 'PUBLIC'
142 UNION ALL
143 SELECT 'DIRECTORY' object_type, dir.directory_name, dir.owner, 6 priority
144 FROM all_directories dir
145 WHERE dir.directory_name = :objName
146 UNION ALL
147 SELECT 'DATABASE LINK' object_type, db_link, owner, 7 priority
148 FROM all_db_links dbl
149 WHERE dbl.db_link = :objName
150 ) ORDER BY priority ASC,
151 length(object_type) ASC,
152 object_type DESC""", # preference: PACKAGE before PACKAGE BODY, TABLE before INDEX
153 'tabComments': """
154 SELECT comments
155 FROM all_tab_comments
156 WHERE owner = :owner
157 AND table_name = :table_name""",
158 'colComments': """
159 atc.column_name,
160 acc.comments
161 FROM all_tab_columns atc
162 JOIN all_col_comments acc ON (atc.owner = acc.owner and atc.table_name = acc.table_name and atc.column_name = acc.column_name)
163 WHERE atc.table_name = :object_name
164 AND atc.owner = :owner
165 ORDER BY atc.column_id;""",
166 'oneColComments': """
167 atc.column_name,
168 acc.comments
169 FROM all_tab_columns atc
170 JOIN all_col_comments acc ON (atc.owner = acc.owner and atc.table_name = acc.table_name and atc.column_name = acc.column_name)
171 WHERE atc.table_name = :object_name
172 AND atc.owner = :owner
173 AND acc.column_name = :column_name
174 ORDER BY atc.column_id;""",
175 #thanks to Senora.pm for "refs"
176 'refs': """
177 NULL referenced_by,
178 c2.table_name references,
179 c1.constraint_name constraint
180 FROM
181 user_constraints c1,
182 user_constraints c2
183 WHERE
184 c1.table_name = :object_name
185 and c1.constraint_type ='R'
186 and c1.r_constraint_name = c2.constraint_name
187 and c1.r_owner = c2.owner
188 and c1.owner = :owner
189 UNION
190 SELECT c1.table_name referenced_by,
191 NULL references,
192 c1.constraint_name constraint
193 FROM
194 user_constraints c1,
195 user_constraints c2
196 WHERE
197 c2.table_name = :object_name
198 and c1.constraint_type ='R'
199 and c1.r_constraint_name = c2.constraint_name
200 and c1.r_owner = c2.owner
201 and c1.owner = :owner
202 """
203 }
204
205 if float(sys.version[:3]) < 2.3:
206 def enumerate(lst):
207 return zip(range(len(lst)), lst)
208
209 class SoftwareSearcher(object):
210 def __init__(self, softwareList, purpose):
211 self.softwareList = softwareList
212 self.purpose = purpose
213 self.software = None
214 def invoke(self, *args):
215 if not self.software:
216 (self.software, self.invokeString) = self.find()
217 argTuple = tuple([self.software] + list(args))
218 os.system(self.invokeString % argTuple)
219 def find(self):
220 if self.purpose == 'text editor':
221 software = os.environ.get('EDITOR')
222 if software:
223 return (software, '%s %s')
224 for (n, (software, invokeString)) in enumerate(self.softwareList):
225 if os.path.exists(software):
226 if n > (len(self.softwareList) * 0.7):
227 print """
228
229 Using %s. Note that there are better options available for %s,
230 but %s couldn't find a better one in your PATH.
231 Feel free to open up %s
232 and customize it to find your favorite %s program.
233
234 """ % (software, self.purpose, __file__, __file__, self.purpose)
235 return (software, invokeString)
236 stem = os.path.split(software)[1]
237 for p in os.environ['PATH'].split(os.pathsep):
238 if os.path.exists(os.sep.join([p, stem])):
239 return (stem, invokeString)
240 raise (OSError, """Could not find any %s programs. You will need to install one,
241 or customize %s to make it aware of yours.
242 Looked for these programs:
243 %s""" % (self.purpose, __file__, "\n".join([s[0] for s in self.softwareList])))
244 #v2.4: %s""" % (self.purpose, __file__, "\n".join(s[0] for s in self.softwareList)))
245
246 softwareLists = {
247 'diff/merge': [
248 ('/usr/bin/meld',"%s %s %s"),
249 ('/usr/bin/kdiff3',"%s %s %s"),
250 (r'C:\Program Files\Araxis\Araxis Merge v6.5\Merge.exe','"%s" %s %s'),
251 (r'C:\Program Files\TortoiseSVN\bin\TortoiseMerge.exe', '"%s" /base:"%s" /mine:"%s"'),
252 ('FileMerge','%s %s %s'),
253 ('kompare','%s %s %s'),
254 ('WinMerge','%s %s %s'),
255 ('xxdiff','%s %s %s'),
256 ('fldiff','%s %s %s'),
257 ('gtkdiff','%s %s %s'),
258 ('tkdiff','%s %s %s'),
259 ('gvimdiff','%s %s %s'),
260 ('diff',"%s %s %s"),
261 (r'c:\windows\system32\comp.exe',"%s %s %s")],
262 'text editor': [
263 ('gedit', '%s %s'),
264 ('textpad', '%s %s'),
265 ('notepad.exe', '%s %s'),
266 ('pico', '%s %s'),
267 ('emacs', '%s %s'),
268 ('vim', '%s %s'),
269 ('vi', '%s %s'),
270 ('ed', '%s %s'),
271 ('edlin', '%s %s')
272 ]
273 }
274
275 diffMergeSearcher = SoftwareSearcher(softwareLists['diff/merge'],'diff/merge')
276 editSearcher = SoftwareSearcher(softwareLists['text editor'], 'text editor')
277 editor = os.environ.get('EDITOR')
278 if editor:
279 editSearcher.find = lambda: (editor, "%s %s")
280
281 class CaselessDict(dict):
282 """dict with case-insensitive keys.
283
284 Posted to ASPN Python Cookbook by Jeff Donner - http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/66315"""
285 def __init__(self, other=None):
286 if other:
287 # Doesn't do keyword args
288 if isinstance(other, dict):
289 for k,v in other.items():
290 dict.__setitem__(self, k.lower(), v)
291 else:
292 for k,v in other:
293 dict.__setitem__(self, k.lower(), v)
294 def __getitem__(self, key):
295 return dict.__getitem__(self, key.lower())
296 def __setitem__(self, key, value):
297 dict.__setitem__(self, key.lower(), value)
298 def __contains__(self, key):
299 return dict.__contains__(self, key.lower())
300 def has_key(self, key):
301 return dict.has_key(self, key.lower())
302 def get(self, key, def_val=None):
303 return dict.get(self, key.lower(), def_val)
304 def setdefault(self, key, def_val=None):
305 return dict.setdefault(self, key.lower(), def_val)
306 def update(self, other):
307 for k,v in other.items():
308 dict.__setitem__(self, k.lower(), v)
309 def fromkeys(self, iterable, value=None):
310 d = CaselessDict()
311 for k in iterable:
312 dict.__setitem__(d, k.lower(), value)
313 return d
314 def pop(self, key, def_val=None):
315 return dict.pop(self, key.lower(), def_val)
316
317 class Parser(object):
318 comment_def = "--" + pyparsing.ZeroOrMore(pyparsing.CharsNotIn("\n"))
319 def __init__(self, scanner, retainSeparator=True):
320 self.scanner = scanner
321 self.scanner.ignore(pyparsing.sglQuotedString)
322 self.scanner.ignore(pyparsing.dblQuotedString)
323 self.scanner.ignore(self.comment_def)
324 self.scanner.ignore(pyparsing.cStyleComment)
325 self.retainSeparator = retainSeparator
326 def separate(self, txt):
327 itms = []
328 for (sqlcommand, start, end) in self.scanner.scanString(txt):
329 if sqlcommand:
330 if type(sqlcommand[0]) == pyparsing.ParseResults:
331 if self.retainSeparator:
332 itms.append("".join(sqlcommand[0]))
333 else:
334 itms.append(sqlcommand[0][0])
335 else:
336 if sqlcommand[0]:
337 itms.append(sqlcommand[0])
338 return itms
339
340 bindScanner = Parser(pyparsing.Literal(':') + pyparsing.Word( pyparsing.alphanums + "_$#" ))
341
342 def findBinds(target, existingBinds, givenBindVars = {}):
343 result = givenBindVars
344 for finding, startat, endat in bindScanner.scanner.scanString(target):
345 varname = finding[1]
346 try:
347 result[varname] = existingBinds[varname]
348 except KeyError:
349 if not givenBindVars.has_key(varname):
350 print 'Bind variable %s not defined.' % (varname)
351 return result
352
353 class sqlpyPlus(sqlpython.sqlpython):
354 defaultExtension = 'sql'
355 sqlpython.sqlpython.shortcuts.update({':': 'setbind',
356 '\\': 'psql',
357 '@': '_load'})
358 multilineCommands = '''select insert update delete tselect
359 create drop alter _multiline_comment'''.split()
360 sqlpython.sqlpython.noSpecialParse.append('spool')
361 commentGrammars = pyparsing.Or([pyparsing.Literal('--') + pyparsing.restOfLine, pyparsing.cStyleComment])
362 defaultFileName = 'afiedt.buf'
363 def __init__(self):
364 sqlpython.sqlpython.__init__(self)
365 self.binds = CaselessDict()
366 self.sqlBuffer = []
367 self.settable = 'autobind commit_on_exit echo maxfetch maxtselctrows timeout'.split()
368 # settables must be lowercase
369 self.stdoutBeforeSpool = sys.stdout
370 self.spoolFile = None
371 self.autobind = False
372 def default(self, arg):
373 sqlpython.sqlpython.default(self, arg)
374 self.sqlBuffer.append(self.query)
375
376 # overrides cmd's parseline
377 def parseline(self, line):
378 """Parse the line into a command name and a string containing
379 the arguments. Returns a tuple containing (command, args, line).
380 'command' and 'args' may be None if the line couldn't be parsed.
381 Overrides cmd.cmd.parseline to accept variety of shortcuts.."""
382
383 cmd, arg, line = sqlpython.sqlpython.parseline(self, line)
384 if cmd in ('select', 'sleect', 'insert', 'update', 'delete', 'describe',
385 'desc', 'comments', 'pull', 'refs', 'desc', 'triggers', 'find') \
386 and not hasattr(self, 'curs'):
387 print 'Not connected.'
388 return '', '', ''
389 return cmd, arg, line
390
391 do__load = Cmd.do_load
392
393 def onecmd_plus_hooks(self, line):
394 line = self.precmd(line)
395 stop = self.onecmd(line)
396 stop = self.postcmd(stop, line)
397
398 def do_shortcuts(self,arg):
399 """Lists available first-character shortcuts
400 (i.e. '!dir' is equivalent to 'shell dir')"""
401 for (scchar, scto) in self.shortcuts.items():
402 print '%s: %s' % (scchar, scto)
403
404 def colnames(self):
405 return [d[0] for d in curs.description]
406
407 def sql_format_itm(self, itm, needsquotes):
408 if itm is None:
409 return 'NULL'
410 if needsquotes:
411 return "'%s'" % str(itm)
412 return str(itm)
413 tableNameFinder = re.compile(r'from\s+([\w$#_"]+)', re.IGNORECASE | re.MULTILINE | re.DOTALL)
414 inputStatementFormatters = {
415 cx_Oracle.STRING: "'%s'",
416 cx_Oracle.DATETIME: "TO_DATE('%s', 'YYYY-MM-DD HH24:MI:SS')"}
417 inputStatementFormatters[cx_Oracle.CLOB] = inputStatementFormatters[cx_Oracle.STRING]
418 inputStatementFormatters[cx_Oracle.TIMESTAMP] = inputStatementFormatters[cx_Oracle.DATETIME]
419 def output(self, outformat, rowlimit):
420 self.tblname = self.tableNameFinder.search(self.curs.statement).group(1)
421 self.colnames = [d[0] for d in self.curs.description]
422 if outformat in output_templates:
423 self.colnamelen = max(len(colname) for colname in self.colnames)
424 self.coltypes = [d[1] for d in self.curs.description]
425 self.formatters = [self.inputStatementFormatters.get(typ, '%s') for typ in self.coltypes]
426 result = output_templates[outformat].generate(**self.__dict__)
427 elif outformat == '\\t': # transposed
428 rows = [self.colnames]
429 rows.extend(list(self.rows))
430 transpr = [[rows[y][x] for y in range(len(rows))]for x in range(len(rows[0]))] # matrix transpose
431 newdesc = [['ROW N.'+str(y),10] for y in range(len(rows))]
432 for x in range(len(self.curs.description)):
433 if str(self.curs.description[x][1]) == "<type 'cx_Oracle.BINARY'>": # handles RAW columns
434 rname = transpr[x][0]
435 transpr[x] = map(binascii.b2a_hex, transpr[x])
436 transpr[x][0] = rname
437 newdesc[0][0] = 'COLUMN NAME'
438 result = '\n' + sqlpython.pmatrix(transpr,newdesc)
439 elif outformat in ('\\l', '\\L', '\\p', '\\b'):
440 plot = Plot()
441 plot.build(self, outformat)
442 plot.shelve()
443 plot.draw()
444 return ''
445 else:
446 result = sqlpython.pmatrix(self.rows, self.curs.description, self.maxfetch)
447 return result
448
449 legalOracle = re.compile('[a-zA-Z_$#]')
450
451 def select_scalar_list(self, sql, binds={}):
452 self.curs.execute(sql, binds)
453 return [r[0] for r in self.curs.fetchall()]
454
455 columnNameRegex = re.compile(
456 r'select\s+(.*)from',
457 re.IGNORECASE | re.DOTALL | re.MULTILINE)
458 def completedefault(self, text, line, begidx, endidx):
459 segment = completion.whichSegment(line)
460 text = text.upper()
461 completions = []
462 if segment == 'select':
463 stmt = "SELECT column_name FROM user_tab_columns WHERE column_name LIKE '%s%%'"
464 completions = self.select_scalar_list(stmt % (text))
465 if not completions:
466 stmt = "SELECT column_name FROM all_tab_columns WHERE column_name LIKE '%s%%'"
467 completions = self.select_scalar_list(stmt % (text))
468 if segment == 'from':
469 columnNames = self.columnNameRegex.search(line)
470 if columnNames:
471 columnNames = columnNames.group(1)
472 columnNames = [c.strip().upper() for c in columnNames.split(',')]
473 stmt1 = "SELECT table_name FROM all_tab_columns WHERE column_name = '%s' AND table_name LIKE '%s%%'"
474 for columnName in columnNames:
475 # and if columnName is * ?
476 completions.extend(self.select_scalar_list(stmt1 % (columnName, text)))
477 if segment in ('from', 'update', 'insert into') and (not completions):
478 stmt = "SELECT table_name FROM user_tables WHERE table_name LIKE '%s%%'"
479 completions = self.select_scalar_list(stmt % (text))
480 if not completions:
481 stmt = """SELECT table_name FROM user_tables WHERE table_name LIKE '%s%%'
482 UNION
483 SELECT DISTINCT owner FROM all_tables WHERE owner LIKE '%%%s'"""
484 completions = self.select_scalar_list(stmt % (text, text))
485 if segment in ('where', 'group by', 'order by', 'having', 'set'):
486 tableNames = completion.tableNamesFromFromClause(line)
487 if tableNames:
488 stmt = """SELECT column_name FROM all_tab_columns
489 WHERE table_name IN (%s)""" % \
490 (','.join("'%s'" % (t) for t in tableNames))
491 stmt = "%s AND column_name LIKE '%s%%'" % (stmt, text)
492 completions = self.select_scalar_list(stmt)
493 if not segment:
494 stmt = "SELECT object_name FROM all_objects WHERE object_name LIKE '%s%%'"
495 completions = self.select_scalar_list(stmt % (text))
496 return completions
497
498 rowlimitPattern = pyparsing.Word(pyparsing.nums)('rowlimit')
499 rawTerminators = '; \\s \\S \\c \\C \\t \\i \\p \\l \\L \\b ' + ' '.join(output_templates.keys())
500 terminatorPattern = (pyparsing.oneOf(rawTerminators)
501 ^ pyparsing.Literal('\n/') ^ \
502 (pyparsing.Literal('\nEOF') + pyparsing.stringEnd)) \
503 ('terminator') + \
504 pyparsing.Optional(rowlimitPattern) #+ \
505 #pyparsing.FollowedBy(pyparsing.LineEnd())
506 def do_select(self, arg, bindVarsIn=None, override_terminator=None):
507 """Fetch rows from a table.
508
509 Limit the number of rows retrieved by appending
510 an integer after the terminator
511 (example: SELECT * FROM mytable;10 )
512
513 Output may be formatted by choosing an alternative terminator
514 ("help terminators" for details)
515 """
516 bindVarsIn = bindVarsIn or {}
517 statement = self.parsed('select ' + arg)
518 self.query = statement.unterminated
519 if override_terminator:
520 statement['terminator'] = override_terminator
521 statement['rowlimit'] = int(statement.rowlimit or 0)
522 self.varsUsed = findBinds(self.query, self.binds, bindVarsIn)
523 self.curs.execute(self.query, self.varsUsed)
524 self.rows = self.curs.fetchmany(min(self.maxfetch, (statement.rowlimit or self.maxfetch)))
525 self.rc = self.curs.rowcount
526 if self.rc > 0:
527 self.stdout.write('\n%s\n' % (self.output(statement.terminator, statement.rowlimit)))
528 if self.rc == 0:
529 print '\nNo rows Selected.\n'
530 elif self.rc == 1:
531 print '\n1 row selected.\n'
532 if self.autobind:
533 self.binds.update(dict(zip([''.join(l for l in d[0] if l.isalnum()) for d in self.curs.description], self.rows[0])))
534 for (i, val) in enumerate(self.rows[0]):
535 varname = ''.join(letter for letter in self.curs.description[i][0] if letter.isalnum() or letter == '_')
536 self.binds[varname] = val
537 self.binds[str(i+1)] = val
538 elif self.rc < self.maxfetch:
539 print '\n%d rows selected.\n' % self.rc
540 else:
541 print '\nSelected Max Num rows (%d)' % self.rc
542 self.sqlBuffer.append(self.query)
543
544
545 @options([make_option('-f', '--full', action='store_true', help='get dependent objects as well')])
546 def do_pull(self, arg, opts):
547 """Displays source code."""
548
549 arg = self.parsed(arg).unterminated.upper()
550 object_type, owner, object_name = self.resolve(arg)
551 if not object_type:
552 return
553 self.stdout.write("%s %s.%s\n" % (object_type, owner, object_name))
554 self.stdout.write(str(self.curs.callfunc('DBMS_METADATA.GET_DDL', cx_Oracle.CLOB,
555 [object_type, object_name, owner])))
556 if opts.full:
557 for dependent_type in ('OBJECT_GRANT', 'CONSTRAINT', 'TRIGGER'):
558 try:
559 self.stdout.write(str(self.curs.callfunc('DBMS_METADATA.GET_DEPENDENT_DDL', cx_Oracle.CLOB,
560 [dependent_type, object_name, owner])))
561 except cx_Oracle.DatabaseError:
562 pass
563
564 @options([make_option('-a','--all',action='store_true', help='Find in all schemas (not just my own)'),
565 make_option('-i', '--insensitive', action='store_true', help='case-insensitive search'),
566 make_option('-c', '--col', action='store_true', help='find column'),
567 make_option('-t', '--table', action='store_true', help='find table')])
568 def do_find(self, arg, opts):
569 """Finds argument in source code or (with -c) in column definitions."""
570
571 arg = self.parsed(arg).unterminated.upper()
572
573 if opts.col:
574 sql = "owner, table_name, column_name from all_tab_columns where column_name like '%%%s%%'" % (arg)
575 elif opts.table:
576 sql = "owner, table_name from all_tables where table_name like '%%%s%%'" % (arg)
577 else:
578 if opts.insensitive:
579 searchfor = "LOWER(text)"
580 arg = arg.lower()
581 else:
582 searchfor = "text"
583 sql = "* from all_source where %s like '%%%s%%'" % (searchfor, arg)
584 if not opts.all:
585 sql = '%s and owner = user' % (sql)
586 self.do_select(sql)
587
588 @options([make_option('-a','--all',action='store_true',
589 help='Describe all objects (not just my own)')])
590 def do_describe(self, arg, opts):
591 "emulates SQL*Plus's DESCRIBE"
592
593 arg = self.parsed(arg).unterminated.upper()
594 if opts.all:
595 which_view = (', owner', 'all')
596 else:
597 which_view = ('', 'user')
598
599 if not arg:
600 self.do_select("""object_name, object_type%s FROM %s_objects WHERE object_type IN ('TABLE','VIEW','INDEX') ORDER BY object_name""" % which_view)
601 return
602 object_type, owner, object_name = self.resolve(arg)
603 if not object_type:
604 self.do_select("""object_name, object_type%s FROM %s_objects
605 WHERE object_type IN ('TABLE','VIEW','INDEX')
606 AND object_name LIKE '%%%s%%'
607 ORDER BY object_name""" %
608 (which_view[0], which_view[1], arg.upper()) )
609 return
610 self.stdout.write("%s %s.%s\n" % (object_type, owner, object_name))
611 descQ = descQueries.get(object_type)
612 if descQ:
613 for q in descQ:
614 self.do_select(q,bindVarsIn={'object_name':object_name, 'owner':owner})
615 elif object_type == 'PACKAGE':
616 packageContents = self.select_scalar_list(descQueries['PackageObjects'][0], {'package_name':object_name, 'owner':owner})
617 for packageObj_name in packageContents:
618 self.stdout.write('Arguments to %s\n' % (packageObj_name))
619 self.do_select(descQueries['PackageObjArgs'][0],bindVarsIn={'package_name':object_name, 'owner':owner, 'object_name':packageObj_name})
620 do_desc = do_describe
621
622 def do_deps(self, arg):
623 arg = self.parsed(arg).unterminated.upper()
624 object_type, owner, object_name = self.resolve(arg)
625 if object_type == 'PACKAGE BODY':
626 q = "and (type != 'PACKAGE BODY' or name != :object_name)'"
627 object_type = 'PACKAGE'
628 else:
629 q = ""
630 q = """ name,
631 type
632 from user_dependencies
633 where
634 referenced_name like :object_name
635 and referenced_type like :object_type
636 and referenced_owner like :owner
637 %s""" % (q)
638 self.do_select(q, {'object_name':object_name, 'object_type':object_type, 'owner':owner})
639
640 def do_comments(self, arg):
641 'Prints comments on a table and its columns.'
642 arg = self.parsed(arg).unterminated.upper()
643 object_type, owner, object_name, colName = self.resolve_with_column(arg)
644 if object_type:
645 self.curs.execute(queries['tabComments'],{'table_name':object_name, 'owner':owner})
646 self.stdout.write("%s %s.%s: %s\n" % (object_type, owner, object_name, self.curs.fetchone()[0]))
647 if colName:
648 self.do_select(queries['oneColComments'],bindVarsIn={'owner':owner, 'object_name': object_name, 'column_name': colName})
649 else:
650 self.do_select(queries['colComments'],bindVarsIn={'owner':owner, 'object_name': object_name})
651
652 def resolve(self, identifier):
653 """Checks (my objects).name, (my synonyms).name, (public synonyms).name
654 to resolve a database object's name. """
655 parts = identifier.split('.')
656 try:
657 if len(parts) == 2:
658 owner, object_name = parts
659 object_type = self.select_scalar_list('SELECT object_type FROM all_objects WHERE owner = :owner AND object_name = :object_name',
660 {'owner': owner, 'object_name': object_name}
661 )[0]
662 elif len(parts) == 1:
663 object_name = parts[0]
664 self.curs.execute(queries['resolve'], {'objName':object_name})
665 object_type, object_name, owner = self.curs.fetchone()
666 except (TypeError, IndexError):
667 print 'Could not resolve object %s.' % identifier
668 object_type, owner, object_name = '', '', ''
669 return object_type, owner, object_name
670
671 def resolve_with_column(self, identifier):
672 colName = None
673 object_type, owner, object_name = self.resolve(identifier)
674 if not object_type:
675 parts = identifier.split('.')
676 if len(parts) > 1:
677 colName = parts[-1]
678 identifier = '.'.join(parts[:-1])
679 object_type, owner, object_name = self.resolve(identifier)
680 return object_type, owner, object_name, colName
681
682 def do_resolve(self, arg):
683 arg = self.parsed(arg).unterminated.upper()
684 self.stdout.write(','.join(self.resolve(arg))+'\n')
685
686 def spoolstop(self):
687 if self.spoolFile:
688 self.stdout = self.stdoutBeforeSpool
689 print 'Finished spooling to ', self.spoolFile.name
690 self.spoolFile.close()
691 self.spoolFile = None
692
693 def do_spool(self, arg):
694 """spool [filename] - begins redirecting output to FILENAME."""
695 self.spoolstop()
696 arg = arg.strip()
697 if not arg:
698 arg = 'output.lst'
699 if arg.lower() != 'off':
700 if '.' not in arg:
701 arg = '%s.lst' % arg
702 print 'Sending output to %s (until SPOOL OFF received)' % (arg)
703 self.spoolFile = open(arg, 'w')
704 self.stdout = self.spoolFile
705
706 def do_write(self, args):
707 print 'Use (query) > outfilename instead.'
708 return
709
710 def do_compare(self, args):
711 """COMPARE query1 TO query2 - uses external tool to display differences.
712
713 Sorting is recommended to avoid false hits.
714 Will attempt to use a graphical diff/merge tool like kdiff3, meld, or Araxis Merge,
715 if they are installed."""
716 fnames = []
717 args2 = args.split(' to ')
718 if len(args2) < 2:
719 print self.do_compare.__doc__
720 return
721 for n in range(len(args2)):
722 query = args2[n]
723 fnames.append('compare%s.txt' % n)
724 #TODO: update this terminator-stripping
725 if query.rstrip()[-1] != self.terminator:
726 query = '%s%s' % (query, self.terminator)
727 self.onecmd_plus_hooks('%s > %s' % (query, fnames[n]))
728 diffMergeSearcher.invoke(fnames[0], fnames[1])
729
730 bufferPosPattern = re.compile('\d+')
731 rangeIndicators = ('-',':')
732
733 def do_psql(self, arg):
734 '''Shortcut commands emulating psql's backslash commands.
735
736 \c connect
737 \d desc
738 \e edit
739 \g run
740 \h help
741 \i load
742 \o spool
743 \p list
744 \q quit
745 \w save
746 \db _dir_tablespaces
747 \dd comments
748 \dn _dir_schemas
749 \dt _dir_tables
750 \dv _dir_views
751 \di _dir_indexes
752 \? help psql'''
753 commands = {}
754 for c in self.do_psql.__doc__.splitlines()[2:]:
755 (abbrev, command) = c.split(None, 1)
756 commands[abbrev[1:]] = command
757 words = arg.split(None,1)
758 try:
759 abbrev = words[0]
760 except IndexError:
761 return
762 try:
763 args = words[1]
764 except IndexError:
765 args = ''
766 try:
767 return self.onecmd('%s %s' % (commands[abbrev], args))
768 except KeyError:
769 print 'psql command \%s not yet supported.' % abbrev
770
771 @options([make_option('-a','--all',action='store_true',
772 help='Describe all objects (not just my own)')])
773 def do__dir_tables(self, arg, opts):
774 if opts.all:
775 which_view = (', owner', 'all')
776 else:
777 which_view = ('', 'user')
778 self.do_select("""table_name, 'TABLE' as type%s FROM %s_tables WHERE table_name LIKE '%%%s%%'""" %
779 (which_view[0], which_view[1], arg.upper()))
780
781 @options([make_option('-a','--all',action='store_true',
782 help='Describe all objects (not just my own)')])
783 def do__dir_views(self, arg, opts):
784 if opts.all:
785 which_view = (', owner', 'all')
786 else:
787 which_view = ('', 'user')
788 self.do_select("""view_name, 'VIEW' as type%s FROM %s_views WHERE view_name LIKE '%%%s%%'""" %
789 (which_view[0], which_view[1], arg.upper()))
790
791 @options([make_option('-a','--all',action='store_true',
792 help='Describe all objects (not just my own)')])
793 def do__dir_indexes(self, arg, opts):
794 if opts.all:
795 which_view = (', owner', 'all')
796 else:
797 which_view = ('', 'user')
798 self.do_select("""index_name, index_type%s FROM %s_indexes WHERE index_name LIKE '%%%s%%' OR table_name LIKE '%%%s%%'""" %
799 (which_view[0], which_view[1], arg.upper(), arg.upper()))
800
801 def do__dir_tablespaces(self, arg):
802 self.do_select("""tablespace_name, file_name from dba_data_files""")
803
804 def do__dir_schemas(self, arg):
805 self.do_select("""owner, count(*) AS objects FROM all_objects GROUP BY owner ORDER BY owner""")
806
807 def do_head(self, arg):
808 nrows = 10
809 args = arg.split()
810 if len(args) > 1:
811 for a in args:
812 if a[0] == '-':
813 try:
814 nrows = int(a[1:])
815 args.remove(a)
816 except:
817 pass
818 arg = ' '.join(args)
819 self.do_select('* from %s;%d' % (arg, nrows))
820
821 def do_print(self, arg):
822 'print VARNAME: Show current value of bind variable VARNAME.'
823 if arg:
824 if arg[0] == ':':
825 arg = arg[1:]
826 try:
827 self.stdout.write(str(self.binds[arg])+'\n')
828 except KeyError:
829 self.stdout.write('No bind variable %s\n' % arg)
830 else:
831 for (var, val) in self.binds.items():
832 print ':%s = %s' % (var, val)
833
834 assignmentScanner = Parser(pyparsing.Literal(':=') ^ '=')
835 def do_setbind(self, arg):
836 if not arg:
837 return self.do_print(arg)
838 arg = self.parsed(arg).unterminated
839 try:
840 assigner, startat, endat = self.assignmentScanner.scanner.scanString(arg).next()
841 except StopIteration:
842 self.do_print(arg)
843 return
844 var, val = arg[:startat].strip(), arg[endat:].strip()
845 if val[0] == val[-1] == "'" and len(val) > 1:
846 self.binds[var] = val[1:-1]
847 return
848 try:
849 self.binds[var] = int(val)
850 return
851 except ValueError:
852 try:
853 self.binds[var] = float(val)
854 return
855 except ValueError:
856 statekeeper = Statekeeper(self, ('autobind',))
857 self.autobind = True
858 self.do_select('%s AS %s FROM dual;' % (val, var))
859 statekeeper.restore()
860
861 def do_exec(self, arg):
862 if arg[0] == ':':
863 self.do_setbind(arg[1:])
864 else:
865 arg = self.parsed(arg).unterminated
866 varsUsed = findBinds(arg, self.binds, {})
867 try:
868 self.curs.execute('begin\n%s;end;' % arg, varsUsed)
869 except Exception, e:
870 print e
871
872 '''
873 Fails:
874 select n into :n from test;'''
875
876 def anon_plsql(self, line1):
877 lines = [line1]
878 while True:
879 line = self.pseudo_raw_input(self.continuationPrompt)
880 if line.strip() == '/':
881 try:
882 self.curs.execute('\n'.join(lines))
883 except Exception, e:
884 print e
885 return
886 lines.append(line)
887
888 def do_begin(self, arg):
889 self.anon_plsql('begin ' + arg)
890
891 def do_declare(self, arg):
892 self.anon_plsql('declare ' + arg)
893
894 #def do_create(self, arg):
895 # self.anon_plsql('create ' + arg)
896
897 @options([make_option('-l', '--long', action='store_true', help='long descriptions'),
898 make_option('-a', '--all', action='store_true', help="all schemas' objects")])
899 def do_ls(self, arg, opts):
900 where = ''
901 if arg:
902 where = """\nWHERE object_type || '/' || object_name
903 LIKE '%%%s%%'""" % (arg.upper().replace('*','%'))
904 else:
905 where = ''
906 if opts.all:
907 whose = 'all'
908 objname = "owner || '.' || object_name"
909 else:
910 whose = 'user'
911 objname = 'object_name'
912 if opts.long:
913 extraInfo = ', status, last_ddl_time AS modified'
914 else:
915 extraInfo = ''
916 statement = '''SELECT object_type || '/' || %s AS name %s
917 FROM %s_objects %s
918 ORDER BY object_type, object_name;''' % (objname, extraInfo, whose, where)
919 self.onecmd(statement)
920
921 def do_cat(self, arg):
922 '''cat TABLENAME --> SELECT * FROM equivalent'''
923 if not arg:
924 print self.do_cat.__doc__
925 return
926 arg = self.parsed(arg)
927 targets = arg.unterminated.split()
928 for target in targets:
929 self.do_select('* from %s%s%s' % (target, arg.terminator or ';', arg.rowlimit)) # permissive of space before terminator
930
931 @options([make_option('-i', '--ignore-case', dest='ignorecase', action='store_true', help='Case-insensitive search')])
932 def do_grep(self, arg, opts):
933 """grep PATTERN TABLE - search for term in any of TABLE's fields"""
934
935 arg = self.parsed(arg)
936 targetnames = arg.unterminated.split()
937 pattern = targetnames.pop(0)
938 targets = []
939 for target in targetnames:
940 if '*' in target:
941 self.curs.execute("SELECT owner, table_name FROM all_tables WHERE table_name LIKE '%s'%s" %
942 (target.upper().replace('*','%')), arg.terminator)
943 for row in self.curs:
944 targets.append('%s.%s' % row)
945 else:
946 targets.append(target)
947 for target in targets:
948 print target
949 target = target.rstrip(';')
950 sql = []
951 try:
952 self.curs.execute('select * from %s where 1=0' % target) # just to fill description
953 if opts.ignorecase:
954 sql = ' or '.join("LOWER(%s) LIKE '%%%s%%'" % (d[0], pattern.lower()) for d in self.curs.description)
955 else:
956 sql = ' or '.join("%s LIKE '%%%s%%'" % (d[0], pattern) for d in self.curs.description)
957 sql = '* FROM %s WHERE %s' % (target, sql)
958 self.do_select('%s%s%s' % (sql, arg.terminator, arg.rowlimit))
959 except Exception, e:
960 print e
961 import traceback
962 traceback.print_exc(file=sys.stdout)
963
964 def do_refs(self, arg):
965 arg = self.parsed(arg).unterminated.upper()
966 object_type, owner, object_name = self.resolve(arg)
967 if object_type == 'TABLE':
968 self.do_select(queries['refs'],bindVarsIn={'object_name':object_name, 'owner':owner})
969
970 def _test():
971 import doctest
972 doctest.testmod()
973
974 if __name__ == "__main__":
975 "Silent return implies that all unit tests succeeded. Use -v to see details."
976 _test()