Mercurial > sqlpython

comparison sqlpyPlus.py @ 122:61e2a824b66b

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
wow, assignment from function call working?
author catherine@Elli.myhome.westell.com
date Wed, 30 Jul 2008 10:35:16 -0400
parents 3dd852ab45c0
children 898ed97bec38
comparison
equal deleted inserted replaced
121:3dd852ab45c0 122:61e2a824b66b
41 FROM all_tab_columns atc 41 FROM all_tab_columns atc
42 WHERE atc.table_name = :object_name 42 WHERE atc.table_name = :object_name
43 AND atc.owner = :owner 43 AND atc.owner = :owner
44 ORDER BY atc.column_id;""",), 44 ORDER BY atc.column_id;""",),
45 'PROCEDURE': (""" 45 'PROCEDURE': ("""
46 argument_name, 46 NVL(argument_name, 'Return Value') argument_name,
47 data_type, 47 data_type,
48 in_out, 48 in_out,
49 default_value 49 default_value
50 FROM all_arguments 50 FROM all_arguments
51 WHERE object_name = :object_name 51 WHERE object_name = :object_name
52 AND owner = :owner 52 AND owner = :owner
53 AND package_name IS NULL 53 AND package_name IS NULL
54 AND argument_name IS NOT NULL
55 ORDER BY sequence;""",), 54 ORDER BY sequence;""",),
56 'PackageObjects':(""" 55 'PackageObjects':("""
57 SELECT DISTINCT object_name 56 SELECT DISTINCT object_name
58 FROM all_arguments 57 FROM all_arguments
59 WHERE package_name = :package_name 58 WHERE package_name = :package_name
109 AND index_name = :object_name 108 AND index_name = :object_name
110 \\t 109 \\t
111 """,) 110 """,)
112 } 111 }
113 descQueries['VIEW'] = descQueries['TABLE'] 112 descQueries['VIEW'] = descQueries['TABLE']
114 descQueries['FUNCTION'] = ( 113 descQueries['FUNCTION'] = descQueries['PROCEDURE']
115 #TODO: fix arg-finding function
116 """
117 argument_name,
118 data_type,
119 in_out,
120 default_value
121 FROM all_arguments
122 WHERE object_name = :object_name
123 AND owner = :owner
124 AND package_name IS NULL
125 AND argument_name IS NOT NULL
126 ORDER BY sequence;""",
127 descQueries['PROCEDURE'][0])
128 114
129 queries = { 115 queries = {
130 'resolve': """ 116 'resolve': """
131 SELECT object_type, object_name, owner FROM ( 117 SELECT object_type, object_name, owner FROM (
132 SELECT object_type, object_name, user owner, 1 priority 118 SELECT object_type, object_name, user owner, 1 priority
196 and c1.owner = :owner 182 and c1.owner = :owner
197 """ 183 """
198 } 184 }
199 185
200 import sys, os, re, sqlpython, cx_Oracle, pyparsing 186 import sys, os, re, sqlpython, cx_Oracle, pyparsing
201 from cmd2 import Cmd, make_option, options 187 from cmd2 import Cmd, make_option, options, Statekeeper
202 188
203 if float(sys.version[:3]) < 2.3: 189 if float(sys.version[:3]) < 2.3:
204 def enumerate(lst): 190 def enumerate(lst):
205 return zip(range(len(lst)), lst) 191 return zip(range(len(lst)), lst)
206 192
432 html_template = """<html> 418 html_template = """<html>
433 <head> 419 <head>
434 <title py:content="tblname">Table Name</title> 420 <title py:content="tblname">Table Name</title>
435 </head> 421 </head>
436 <body> 422 <body>
437 <table py:attr={'id':tblname}> 423 <table py:attr="{'id':tblname}">
438 <tr> 424 <tr>
439 <th py:for="colname in colnames"> 425 <th py:for="colname in colnames">
440 <span py:replace="colname">Column Name</span> 426 <span py:replace="colname">Column Name</span>
441 </th> 427 </th>
442 </tr> 428 </tr>
581 [dependent_type, object_name, owner]))) 567 [dependent_type, object_name, owner])))
582 except cx_Oracle.DatabaseError: 568 except cx_Oracle.DatabaseError:
583 pass 569 pass
584 570
585 @options([make_option('-i', '--insensitive', action='store_true', help='case-insensitive search'), 571 @options([make_option('-i', '--insensitive', action='store_true', help='case-insensitive search'),
586 make_option('-c', '--col', action='store_true', help='find column')]) 572 make_option('-c', '--col', action='store_true', help='find column'),
573 make_option('-t', '--table', action='store_true', help='find table')])
587 def do_find(self, arg, opts): 574 def do_find(self, arg, opts):
588 """Finds argument in source code or (with -c) in column definitions.""" 575 """Finds argument in source code or (with -c) in column definitions."""
589 576
590 arg = self.parsed(arg).unterminated.upper() 577 arg = self.parsed(arg).unterminated.upper()
591 if opts.col: 578 if opts.col:
592 self.do_select("table_name, column_name from all_tab_columns where column_name like '%%%s%%'" % (arg)) 579 self.do_select("owner, table_name, column_name from all_tab_columns where column_name like '%%%s%%'" % (arg))
580 elif opts.table:
581 self.do_select("owner, table_name from all_tables where table_name like '%%%s%%'" % (arg))
593 else: 582 else:
594 if opts.insensitive: 583 if opts.insensitive:
595 searchfor = "LOWER(text)" 584 searchfor = "LOWER(text)"
596 arg = arg.lower() 585 arg = arg.lower()
597 else: 586 else:
676 object_type, object_name, owner = self.curs.fetchone() 665 object_type, object_name, owner = self.curs.fetchone()
677 except TypeError: 666 except TypeError:
678 print 'Could not resolve object %s.' % identifier 667 print 'Could not resolve object %s.' % identifier
679 object_type, owner, object_name = '', '', '' 668 object_type, owner, object_name = '', '', ''
680 return object_type, owner, object_name 669 return object_type, owner, object_name
670 #todo: resolve not finding cwm$ table
681 671
682 def do_resolve(self, arg): 672 def do_resolve(self, arg):
683 self.stdout.write(self.resolve(arg)+'\n') 673 self.stdout.write(self.resolve(arg)+'\n')
684 674
685 def spoolstop(self): 675 def spoolstop(self):
828 self.stdout.write('No bind variable %s\n' % arg) 818 self.stdout.write('No bind variable %s\n' % arg)
829 else: 819 else:
830 for (var, val) in self.binds.items(): 820 for (var, val) in self.binds.items():
831 print ':%s = %s' % (var, val) 821 print ':%s = %s' % (var, val)
832 822
823 assignmentScanner = Parser(pyparsing.Literal(':=') ^ '=')
833 def do_setbind(self, arg): 824 def do_setbind(self, arg):
834 arg = self.parsed(arg).unterminated 825 arg = self.parsed(arg).unterminated
835 args = arg.split(None, 2) 826 try:
836 if len(args) < 2: 827 assigner, startat, endat = self.assignmentScanner.scanner.scanString(arg).next()
828 except StopIteration:
837 self.do_print(arg) 829 self.do_print(arg)
838 return 830 return
839 elif len(args) > 2 and args[1] in ('=',':='): 831 var, val = arg[:startat].strip(), arg[endat:].strip()
840 var, val = args[0], args[2] 832 if val[0] == val[-1] == "'" and len(val) > 1:
841 if val[0] == val[-1] == "'" and len(val) > 1: 833 self.binds[var] = val[1:-1]
842 self.binds[var] = val[1:-1] 834 return
843 return 835 try:
836 self.binds[var] = int(val)
837 return
838 except ValueError:
844 try: 839 try:
845 self.binds[var] = int(val) 840 self.binds[var] = float(val)
846 return 841 return
847 except ValueError: 842 except ValueError:
848 try: 843 try:
849 self.binds[var] = float(val) 844 statekeeper = Statekeeper(self, ('autobind',))
845 self.autobind = True
846 self.do_select('%s AS %s FROM dual;' % (val, var))
847 statekeeper.restore()
850 return 848 return
851 except ValueError: 849 except:
852 try: 850 pass
853 varsUsed = findBinds(arg, self.binds, {}) 851
854 self.binds[var] = self.curs.callfunc(val, varsUsed) #TODO: wrong args 852 print 'Could not parse'
855 # NotSupportedError: Variable_TypeByPythonType(): unhandled data type
856 # need to warn that it's a date?
857 return
858 except:
859 pass
860
861 print 'Could not parse ', args
862 853
863 def do_exec(self, arg): 854 def do_exec(self, arg):
864 if arg[0] == ':': 855 if arg[0] == ':':
865 self.do_setbind(arg[1:]) 856 self.do_setbind(arg[1:])
866 else: 857 else:
867 arg = self.parsed(arg).statement 858 arg = self.parsed(arg).statement
868 varsUsed = findBinds(arg, self.binds, {}) 859 varsUsed = findBinds(arg, self.binds, {})
869 try: 860 try:
861 # save autobind to state
862 # select varname from ...
863 # restore state
870 self.curs.execute('begin\n%s;end;' % arg, varsUsed) 864 self.curs.execute('begin\n%s;end;' % arg, varsUsed)
871 except Exception, e: 865 except Exception, e:
872 print e 866 print e
873 ''' 867 '''
874 exec :x := 'box' 868 exec :x := 'box'