Mercurial > sqlpython

comparison sqlpyPlus.py @ 4:23c3a58d7804

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
about to strip out tselect
author devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
date Tue, 04 Dec 2007 16:28:55 -0500
parents cd23cd62de3c
children 65ae6cec71c6
comparison
equal deleted inserted replaced
3:cd23cd62de3c 4:23c3a58d7804
23 :b = 3 23 :b = 3
24 24
25 - catherinedevlin.blogspot.com May 31, 2006 25 - catherinedevlin.blogspot.com May 31, 2006
26 """ 26 """
27 # note in cmd.cmd about supporting emacs commands? 27 # note in cmd.cmd about supporting emacs commands?
28
29 descQueries = {
30 'TABLE': """
31 atc.column_name,
32 CASE atc.nullable WHEN 'Y' THEN 'NULL' ELSE 'NOT NULL' END "Null?",
33 atc.data_type ||
34 CASE atc.data_type WHEN 'DATE' THEN ''
35 ELSE '(' ||
36 CASE atc.data_type WHEN 'NUMBER' THEN TO_CHAR(atc.data_precision) ||
37 CASE atc.data_scale WHEN 0 THEN ''
38 ELSE ',' || TO_CHAR(atc.data_scale) END
39 ELSE TO_CHAR(atc.data_length) END
40 END ||
41 CASE atc.data_type WHEN 'DATE' THEN '' ELSE ')' END
42 data_type
43 FROM all_tab_columns atc
44 WHERE atc.table_name = :object_name
45 AND atc.owner = :owner
46 ORDER BY atc.column_id;""",
47 'PROCEDURE':"""
48 argument_name,
49 data_type,
50 in_out,
51 default_value
52 FROM all_arguments
53 WHERE object_name = :object_name
54 AND owner = :owner
55 AND package_name IS NULL
56 AND argument_name IS NOT NULL
57 ORDER BY sequence;""",
58 'PackageObjects':"""
59 SELECT DISTINCT object_name
60 FROM all_arguments
61 WHERE package_name = :package_name
62 AND owner = :owner""",
63 'PackageObjArgs':"""
64 object_name,
65 argument_name,
66 data_type,
67 in_out,
68 default_value
69 FROM all_arguments
70 WHERE package_name = :package_name
71 AND object_name = :object_name
72 AND owner = :owner
73 AND argument_name IS NOT NULL
74 ORDER BY sequence""",
75 'TRIGGER':"""
76 trigger_name,
77 trigger_type,
78 triggering_event,
79 table_owner,
80 base_object_type,
81 table_name,
82 column_name,
83 when_clause,
84 status,
85 description,
86 action_type,
87 crossedition
88 FROM all_triggers
89 WHERE owner = :owner
90 AND trigger_name = :object_name
91 \\t
92 """
93 }
94 descQueries['VIEW'] = descQueries['TABLE']
95 descQueries['FUNCTION'] = descQueries['PROCEDURE']
28 96
29 queries = { 97 queries = {
30 'resolve': """ 98 'resolve': """
31 SELECT object_type, object_name, owner FROM ( 99 SELECT object_type, object_name, owner FROM (
32 SELECT object_type, object_name, user owner, 1 priority 100 SELECT object_type, object_name, user owner, 1 priority
44 JOIN all_synonyms asyn ON (asyn.table_owner = ao.owner AND asyn.table_name = ao.object_name) 112 JOIN all_synonyms asyn ON (asyn.table_owner = ao.owner AND asyn.table_name = ao.object_name)
45 WHERE asyn.synonym_name = :objName 113 WHERE asyn.synonym_name = :objName
46 AND ao.object_type != 'SYNONYM' 114 AND ao.object_type != 'SYNONYM'
47 AND asyn.owner = 'PUBLIC' 115 AND asyn.owner = 'PUBLIC'
48 ) ORDER BY priority ASC""", 116 ) ORDER BY priority ASC""",
49 'descTable': """
50 atc.column_name,
51 CASE atc.nullable WHEN 'Y' THEN 'NULL' ELSE 'NOT NULL' END "Null?",
52 atc.data_type ||
53 CASE atc.data_type WHEN 'DATE' THEN ''
54 ELSE '(' ||
55 CASE atc.data_type WHEN 'NUMBER' THEN TO_CHAR(atc.data_precision) ||
56 CASE atc.data_scale WHEN 0 THEN ''
57 ELSE ',' || TO_CHAR(atc.data_scale) END
58 ELSE TO_CHAR(atc.data_length) END
59 END ||
60 CASE atc.data_type WHEN 'DATE' THEN '' ELSE ')' END
61 data_type
62 FROM all_tab_columns atc
63 WHERE atc.table_name = :object_name
64 AND atc.owner = :owner
65 ORDER BY atc.column_id;""",
66 'PackageObjects':"""
67 SELECT DISTINCT object_name
68 FROM all_arguments
69 WHERE package_name = :package_name
70 AND owner = :owner""",
71 'PackageObjArgs':"""
72 object_name,
73 argument_name,
74 data_type,
75 in_out,
76 default_value
77 FROM all_arguments
78 WHERE package_name = :package_name
79 AND object_name = :object_name
80 AND owner = :owner
81 AND argument_name IS NOT NULL
82 ORDER BY sequence""",
83 'descProcedure':"""
84 argument_name,
85 data_type,
86 in_out,
87 default_value
88 FROM all_arguments
89 WHERE object_name = :object_name
90 AND owner = :owner
91 AND package_name IS NULL
92 AND argument_name IS NOT NULL
93 ORDER BY sequence;""",
94 'tabComments': """ 117 'tabComments': """
95 SELECT comments 118 SELECT comments
96 FROM all_tab_comments 119 FROM all_tab_comments
97 WHERE owner = :owner 120 WHERE owner = :owner
98 AND table_name = :table_name""", 121 AND table_name = :table_name""",
309 self.settable = ['maxtselctrows', 'maxfetch', 'autobind', 'failover', 'timeout'] # settables must be lowercase 332 self.settable = ['maxtselctrows', 'maxfetch', 'autobind', 'failover', 'timeout'] # settables must be lowercase
310 self.stdoutBeforeSpool = sys.stdout 333 self.stdoutBeforeSpool = sys.stdout
311 self.spoolFile = None 334 self.spoolFile = None
312 self.autobind = False 335 self.autobind = False
313 self.failover = False 336 self.failover = False
314 self.singleline = '''select insert update delete 337 self.multiline = '''select insert update delete tselect
315 create drop alter'''.split() 338 create drop alter'''.split()
339 self.excludeFromHistory = '''run r list l history hi ed'''.split()
316 340
317 def default(self, arg, do_everywhere=False): 341 def default(self, arg, do_everywhere=False):
318 sqlpython.sqlpython.default(self, arg, do_everywhere) 342 sqlpython.sqlpython.default(self, arg, do_everywhere)
319 self.sqlBuffer.append(self.query) 343 self.sqlBuffer.append(self.query)
320 344
345 def precmd(self, line): 369 def precmd(self, line):
346 """Hook method executed just before the command line is 370 """Hook method executed just before the command line is
347 interpreted, but after the input prompt is generated and issued. 371 interpreted, but after the input prompt is generated and issued.
348 Makes commands case-insensitive (but unfortunately does not alter command completion). 372 Makes commands case-insensitive (but unfortunately does not alter command completion).
349 """ 373 """
374
350 ''' 375 '''
351 savestdout = sys.stdout
352 pipefilename = 'sqlpython.pipeline.tmp'
353 pipedCommands = pipeSeparator.separate(line) 376 pipedCommands = pipeSeparator.separate(line)
354 if len(pipedCommands) > 1: 377 if len(pipedCommands) > 1:
355 f = open(pipefilename,'w') 378 pipefilename = 'sqlpython.pipe.tmp'
356 sys.stdout = f 379 for (idx, pipedCommand) in enumerate(pipedCommands[:-1]):
357 self.precmd(pipedCommands[0]) 380 savestdout = sys.stdout
358 self.onecmd(pipedCommands[0]) 381 f = open(pipefilename,'w')
359 self.postcmd(False, pipedCommands[0]) 382 sys.stdout = f
360 f.close() 383 self.precmd(pipedCommand)
361 sys.stdout = savestdout 384 self.onecmd(pipedCommand)
362 os.system('%s < %s' % (pipedCommands[1], pipefilename)) 385 self.postcmd(False, pipedCommands[0])
386 f.close()
387 sys.stdout = savestdout
388 f = os.popen('%s < %s' % (pipedCommands[idx+1], pipefilename))
389 f.read()
390
363 ''' 391 '''
364 try: 392 try:
365 args = line.split(None,1) 393 args = line.split(None,1)
366 args[0] = args[0].lower() 394 args[0] = args[0].lower()
367 statement = ' '.join(args) 395 statement = ' '.join(args)
368 if args[0] in self.singleline: 396 if args[0] in self.multiline:
369 statement = sqlpython.finishStatement(statement) 397 statement = sqlpython.finishStatement(statement)
370 self.history.append(statement) 398 if args[0] not in self.excludeFromHistory:
399 self.history.append(statement)
371 return statement 400 return statement
372 except Exception: 401 except Exception:
373 return line 402 return line
374 403
375 def do_shortcuts(self,arg): 404 def do_shortcuts(self,arg):
452 for row in self.rows: 481 for row in self.rows:
453 result.append(','.join('"%s"' % ('' if itm is None else itm) for itm in row)) 482 result.append(','.join('"%s"' % ('' if itm is None else itm) for itm in row))
454 result = '\n'.join(result) 483 result = '\n'.join(result)
455 elif outformat == '\\h': 484 elif outformat == '\\h':
456 result = self.output_as_html_table() 485 result = self.output_as_html_table()
486 elif outformat == '\\t':
487
457 else: 488 else:
458 result = sqlpython.pmatrix(self.rows, self.curs.description, self.maxfetch) 489 result = sqlpython.pmatrix(self.rows, self.curs.description, self.maxfetch)
459 return result 490 return result
460 491
461 def do_select(self, arg, bindVarsIn=None): 492 def do_select(self, arg, bindVarsIn=None):
553 584
554 def do_describe(self, arg): 585 def do_describe(self, arg):
555 "emulates SQL*Plus's DESCRIBE" 586 "emulates SQL*Plus's DESCRIBE"
556 object_type, owner, object_name = self.resolve(arg.strip(self.terminator).upper()) 587 object_type, owner, object_name = self.resolve(arg.strip(self.terminator).upper())
557 print "%s %s.%s" % (object_type, owner, object_name) 588 print "%s %s.%s" % (object_type, owner, object_name)
558 if object_type in ('TABLE','VIEW'): 589 if object_type in ('TABLE','VIEW','PROCEDURE','FUNCTION','TRIGGER'):
559 self.do_select(queries['descTable'],{'object_name':object_name, 'owner':owner}) 590 self.do_select(descQueries[object_type],{'object_name':object_name, 'owner':owner})
560 elif object_type == 'PACKAGE': 591 elif object_type == 'PACKAGE':
561 self.curs.execute(queries['PackageObjects'], {'package_name':object_name, 'owner':owner}) 592 self.curs.execute(descQueries['PackageObjects'], {'package_name':object_name, 'owner':owner})
562 for (packageObj_name,) in self.curs: 593 for (packageObj_name,) in self.curs:
563 print packageObj_name 594 print packageObj_name
564 self.do_select(queries['PackageObjArgs'],{'package_name':object_name, 'owner':owner, 'object_name':packageObj_name}) 595 self.do_select(descQueries['PackageObjArgs'],{'package_name':object_name, 'owner':owner, 'object_name':packageObj_name})
565 else:
566 self.do_select(queries['descProcedure'],{'owner':owner, 'object_name':object_name})
567 do_desc = do_describe 596 do_desc = do_describe
568 597
569 def do_comments(self, arg): 598 def do_comments(self, arg):
570 'Prints comments on a table and its columns.' 599 'Prints comments on a table and its columns.'
571 object_type, owner, object_name = self.resolve(arg.strip(self.terminator).upper()) 600 object_type, owner, object_name = self.resolve(arg.strip(self.terminator).upper())
591 except TypeError: 620 except TypeError:
592 print 'Could not resolve object %s.' % identifier 621 print 'Could not resolve object %s.' % identifier
593 object_type, owner, object_name = '', '', '' 622 object_type, owner, object_name = '', '', ''
594 return object_type, owner, object_name 623 return object_type, owner, object_name
595 624
625 def do_resolve(self, arg):
626 print self.resolve(arg)
627
596 def do_shell(self, arg): 628 def do_shell(self, arg):
597 'execute a command as if at the OS prompt.' 629 'execute a command as if at the OS prompt.'
598 os.system(arg) 630 os.system(arg)
599 631
600 def spoolstop(self): 632 def spoolstop(self):
797 def do_exec(self, arg): 829 def do_exec(self, arg):
798 if arg[0] == ':': 830 if arg[0] == ':':
799 self.do_setbind(arg[1:]) 831 self.do_setbind(arg[1:])
800 else: 832 else:
801 self.default('exec %s' % arg) 833 self.default('exec %s' % arg)
802 834
835 def do_cat(self, arg):
836 targets = arg.split()
837 for target in targets:
838 self.do_select('* from %s' % target)
839
840 def do_grep(self, arg):
841 """grep PATTERN TABLE - search for term in any of TABLE's fields"""
842 targets = arg.split()
843 pattern = targets.pop(0)
844 for target in targets:
845 sql = []
846 self.curs.execute('select * from %s where 1=0' % target)
847 sql = ' or '.join("%s LIKE '%%%s%%'" % (d[0], pattern) for d in self.curs.description)
848 sql = '* FROM %s WHERE %s' % (target, sql)
849 self.do_select(sql)
850
803 def _test(): 851 def _test():
804 import doctest 852 import doctest
805 doctest.testmod() 853 doctest.testmod()
806 854
807 if __name__ == "__main__": 855 if __name__ == "__main__":