Mercurial > sqlpython

comparison sqlpyPlus.py @ 119:7882c5e4d744

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
ls -a
author catherine@Elli.myhome.westell.com
date Sun, 13 Jul 2008 07:55:13 -0400
parents 0776ceacfc79
children e11bbeb3f34c
comparison
equal deleted inserted replaced
118:0776ceacfc79 119:7882c5e4d744
109 AND index_name = :object_name 109 AND index_name = :object_name
110 \\t 110 \\t
111 """,) 111 """,)
112 } 112 }
113 descQueries['VIEW'] = descQueries['TABLE'] 113 descQueries['VIEW'] = descQueries['TABLE']
114 descQueries['FUNCTION'] = descQueries['PROCEDURE'] 114 descQueries['FUNCTION'] = (
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'])
115 128
116 queries = { 129 queries = {
117 'resolve': """ 130 'resolve': """
118 SELECT object_type, object_name, owner FROM ( 131 SELECT object_type, object_name, owner FROM (
119 SELECT object_type, object_name, user owner, 1 priority 132 SELECT object_type, object_name, user owner, 1 priority
506 # what about quote-enclosed? 519 # what about quote-enclosed?
507 520
508 legalOracle = re.compile('[a-zA-Z_$#]') 521 legalOracle = re.compile('[a-zA-Z_$#]')
509 522
510 rowlimitPattern = pyparsing.Word(pyparsing.nums)('rowlimit') 523 rowlimitPattern = pyparsing.Word(pyparsing.nums)('rowlimit')
511 terminatorPattern = pyparsing.oneOf('; \\s \\S \\c \\C \\t \\x \\h \n/\n')('terminator') + \ 524 terminatorPattern = (pyparsing.oneOf('; \\s \\S \\c \\C \\t \\x \\h')
525 ^ pyparsing.Literal('\n/\n')) ('terminator') + \
512 pyparsing.Optional(rowlimitPattern) 526 pyparsing.Optional(rowlimitPattern)
513 def do_select(self, arg, bindVarsIn=None, override_terminator=None): 527 def do_select(self, arg, bindVarsIn=None, override_terminator=None):
514 """Fetch rows from a table. 528 """Fetch rows from a table.
515 529
516 Limit the number of rows retrieved by appending 530 Limit the number of rows retrieved by appending
808 else: 822 else:
809 for (var, val) in self.binds.items(): 823 for (var, val) in self.binds.items():
810 print ':%s = %s' % (var, val) 824 print ':%s = %s' % (var, val)
811 825
812 def do_setbind(self, arg): 826 def do_setbind(self, arg):
827 arg = self.parsed(arg).statement # removes terminators?
813 args = arg.split(None, 2) 828 args = arg.split(None, 2)
814 if len(args) < 2: 829 if len(args) < 2:
815 self.do_print(arg) 830 self.do_print(arg)
816 return 831 return
817 elif len(args) > 2 and args[1] in ('=',':='): 832 elif len(args) > 2 and args[1] in ('=',':='):
838 853
839 def do_exec(self, arg): 854 def do_exec(self, arg):
840 if arg[0] == ':': 855 if arg[0] == ':':
841 self.do_setbind(arg[1:]) 856 self.do_setbind(arg[1:])
842 else: 857 else:
858 arg = self.parsed(arg).statement
859 varsUsed = findBinds(arg, self.binds, {})
843 try: 860 try:
844 self.curs.execute('begin\n%s;end;' % arg) 861 self.curs.execute('begin\n%s;end;' % arg, varsUsed)
845 except Exception, e: 862 except Exception, e:
846 print e 863 print e
847 ''' 864 '''
848 exec :x := 'box' 865 exec :x := 'box'
849 exec :y := sysdate 866 exec :y := sysdate
881 self.anon_plsql('declare ' + arg) 898 self.anon_plsql('declare ' + arg)
882 899
883 #def do_create(self, arg): 900 #def do_create(self, arg):
884 # self.anon_plsql('create ' + arg) 901 # self.anon_plsql('create ' + arg)
885 902
886 @options([make_option('-l', '--long', action='store_true', help='long descriptions')]) 903 @options([make_option('-l', '--long', action='store_true', help='long descriptions'),
904 make_option('-a', '--all', action='store_true', help="all schemas' objects")])
887 def do_ls(self, arg, opts): 905 def do_ls(self, arg, opts):
888 where = '' 906 where = ''
889 if arg: 907 if arg:
890 where = """\nWHERE object_type || '/' || object_name 908 where = """\nWHERE object_type || '/' || object_name
891 LIKE '%%%s%%'""" % (arg.upper().replace('*','%')) 909 LIKE '%%%s%%'""" % (arg.upper().replace('*','%'))
892 else: 910 else:
893 where = '' 911 where = ''
912 if opts.all:
913 owner = 'owner'
914 whose = 'all'
915 else:
916 owner = "'' AS owner"
917 whose = 'user'
894 result = [] 918 result = []
895 statement = '''SELECT object_type, object_name, 919 statement = '''SELECT object_type, object_name,
896 status, last_ddl_time 920 status, last_ddl_time, %s
897 FROM user_objects %s 921 FROM %s_objects %s
898 ORDER BY object_type, object_name''' % (where) 922 ORDER BY object_type, object_name''' % (owner, whose, where)
899 self.curs.execute(statement) 923 self.curs.execute(statement)
900 for (object_type, object_name, status, last_ddl_time) in self.curs.fetchall(): 924 for (object_type, object_name, status, last_ddl_time, owner) in self.curs.fetchall():
925 if opts.all:
926 qualified_name = '%s.%s' % (owner, object_name)
927 else:
928 qualified_name = object_name
901 if opts.long: 929 if opts.long:
902 result.append('%s\t%s\t%s/%s' % (status, last_ddl_time, object_type, object_name)) 930 result.append('%s\t%s\t%s/%s' % (status, last_ddl_time, object_type, qualified_name))
903 else: 931 else:
904 result.append('%s/%s' % (object_type, object_name)) 932 result.append('%s/%s' % (object_type, qualified_name))
905 self.stdout.write('\n'.join(result) + '\n') 933 self.stdout.write('\n'.join(result) + '\n')
906 934
907 def do_cat(self, arg): 935 def do_cat(self, arg):
908 targets = arg.split() 936 targets = arg.split()
909 for target in targets: 937 for target in targets: