# HG changeset patch # User catherine@dellzilla # Date 1227560195 18000 # Node ID 01548a399ccfd091ea979ce87aa9bf19c193a498 # Parent 6bb8a112af6bdeb6bbf6a57ee4ea97d41dd87e73 big switch to ParsedString diff -r 6bb8a112af6b -r 01548a399ccf sqlpython/sqlpyPlus.py --- a/sqlpython/sqlpyPlus.py Wed Nov 19 16:37:44 2008 -0500 +++ b/sqlpython/sqlpyPlus.py Mon Nov 24 15:56:35 2008 -0500 @@ -34,7 +34,7 @@ descQueries = { 'TABLE': (""" - atc.column_name, +SELECT atc.column_name, CASE atc.nullable WHEN 'Y' THEN 'NULL' ELSE 'NOT NULL' END "Null?", atc.data_type || CASE atc.data_type WHEN 'DATE' THEN '' @@ -49,9 +49,9 @@ FROM all_tab_columns atc WHERE atc.table_name = :object_name AND atc.owner = :owner -ORDER BY atc.column_id""",), +ORDER BY atc.column_id;""",), 'PROCEDURE': (""" - NVL(argument_name, 'Return Value') argument_name, +SELECT NVL(argument_name, 'Return Value') argument_name, data_type, in_out, default_value @@ -59,14 +59,14 @@ WHERE object_name = :object_name AND owner = :owner AND package_name IS NULL -ORDER BY sequence""",), +ORDER BY sequence;""",), 'PackageObjects':(""" SELECT DISTINCT object_name FROM all_arguments WHERE package_name = :package_name AND owner = :owner""",), 'PackageObjArgs':(""" - object_name, +SELECT object_name, argument_name, data_type, in_out, @@ -76,15 +76,15 @@ AND object_name = :object_name AND owner = :owner AND argument_name IS NOT NULL -ORDER BY sequence""",), +ORDER BY sequence;""",), 'TRIGGER':(""" - description +SELECT description FROM all_triggers WHERE owner = :owner -AND trigger_name = :object_name +AND trigger_name = :object_name; """, """ -table_owner, +SELECT table_owner, base_object_type, table_name, column_name, @@ -99,7 +99,7 @@ """, ), 'INDEX':(""" -index_type, +SELECT index_type, table_owner, table_name, table_type, @@ -156,22 +156,23 @@ WHERE owner = :owner AND table_name = :table_name""", 'colComments': """ +SELECT atc.column_name, acc.comments FROM all_tab_columns atc JOIN all_col_comments acc ON (atc.owner = acc.owner and atc.table_name = acc.table_name and atc.column_name = acc.column_name) WHERE atc.table_name = :object_name AND atc.owner = :owner -ORDER BY atc.column_id""", +ORDER BY atc.column_id;""", 'oneColComments': """ -atc.column_name, +SELECTatc.column_name, acc.comments FROM all_tab_columns atc JOIN all_col_comments acc ON (atc.owner = acc.owner and atc.table_name = acc.table_name and atc.column_name = acc.column_name) WHERE atc.table_name = :object_name AND atc.owner = :owner AND acc.column_name = :column_name -ORDER BY atc.column_id""", +ORDER BY atc.column_id;""", #thanks to Senora.pm for "refs" 'refs': """ NULL referenced_by, @@ -366,7 +367,6 @@ def __init__(self): sqlpython.sqlpython.__init__(self) self.binds = CaselessDict() - self.sqlBuffer = [] self.settable = 'autobind commit_on_exit echo maxfetch maxtselctrows timeout'.split() # settables must be lowercase self.stdoutBeforeSpool = sys.stdout @@ -374,7 +374,6 @@ self.autobind = False def default(self, arg): sqlpython.sqlpython.default(self, arg) - self.sqlBuffer.append(self.query) # overrides cmd's parseline def parseline(self, line): @@ -517,16 +516,10 @@ ("help terminators" for details) """ bindVarsIn = bindVarsIn or {} - statement = self.parsed('select ' + arg, assumeComplete=True) - self.query = statement.unterminated - if isinstance(terminator, pyparsing.ParseResults): - statement['terminator'] = terminator.terminator - statement['rowlimit'] = terminator.rowlimit - elif terminator: - statement['terminator'] = terminator - statement['rowlimit'] = int(statement.rowlimit or 0) + arg.terminator = terminator #unneeded? + rowlimit = int(statement.suffix or 0) self.varsUsed = findBinds(self.query, self.binds, bindVarsIn) - self.curs.execute(self.query, self.varsUsed) + self.curs.execute('select ' + arg, self.varsUsed) self.rows = self.curs.fetchmany(min(self.maxfetch, (statement.rowlimit or self.maxfetch))) self.rc = self.curs.rowcount if self.rc > 0: @@ -545,8 +538,9 @@ print '\n%d rows selected.\n' % self.rc else: print '\nSelected Max Num rows (%d)' % self.rc - self.sqlBuffer.append(self.query) - + + def do_cat(self, arg): + return self.do_select(arg.replaceArgs('SELECT * FROM %s;' % arg)) @options([make_option('-f', '--full', action='store_true', help='get dependent objects as well')]) def do_pull(self, arg, opts): @@ -567,98 +561,86 @@ except cx_Oracle.DatabaseError: pass - @options([make_option('-a','--all',action='store_true', help='Find in all schemas (not just my own)'), - make_option('-i', '--insensitive', action='store_true', help='case-insensitive search'), + all_users_option = optparse.make_option('-a', action='store_const', dest="which_schemas", + default=('','user'), const=(', owner','all'), + help='Describe all objects (not just my own)') + @options([sqlpyPlus.all_users_option, make_option('-c', '--col', action='store_true', help='find column'), make_option('-t', '--table', action='store_true', help='find table')]) def do_find(self, arg, opts): """Finds argument in source code or (with -c) in column definitions.""" - - parsed = self.parsed(arg) - arg = parsed.unterminated.upper() + + capArg = arg.upper() if opts.col: - sql = "owner, table_name, column_name from all_tab_columns where column_name like '%%%s%%'" % (arg) + sql = "SELECT owner, table_name, column_name from all_tab_columns where column_name like '%%%s%%';" % (capArg) elif opts.table: - sql = "owner, table_name from all_tables where table_name like '%%%s%%'" % (arg) + sql = "SELECT owner, table_name from all_tables where table_name like '%%%s%%';" % (capArg) else: - if opts.insensitive: - searchfor = "LOWER(text)" - arg = arg.lower() - else: - searchfor = "text" - sql = "* from all_source where %s like '%%%s%%'" % (searchfor, arg) + sql = "SELECT * from all_source where UPPER(text) like '%%%s%%';" % (capArg) if not opts.all: sql = '%s and owner = user' % (sql) - self.do_select(sql, terminator=parsed) - - @options([make_option('-a','--all',action='store_true', - help='Describe all objects (not just my own)')]) + self.do_select(arg.replaceArgs(sql)) + + @options([sqlpyPlus.all_users_option]) def do_describe(self, arg, opts): "emulates SQL*Plus's DESCRIBE" - parsed = self.parsed(arg) - arg = parsed.unterminated.upper() - if opts.all: - which_view = (', owner', 'all') - else: - which_view = ('', 'user') - - if not arg: - self.do_select("""object_name, object_type%s FROM %s_objects WHERE object_type IN ('TABLE','VIEW','INDEX') ORDER BY object_name""" % which_view, terminator=parsed) - return - object_type, owner, object_name = self.resolve(arg) + which_view = self._which_view(opts) + target = arg.upper() + if not target: + return self.do_select(arg.replaceArgs("""SELECT object_name, object_type%s + FROM %s_objects + WHERE object_type IN ('TABLE','VIEW','INDEX') + ORDER BY object_name;""" % opts.which_schemas)) + object_type, owner, object_name = self.resolve(target) if not object_type: - self.do_select("""object_name, object_type%s FROM %s_objects - WHERE object_type IN ('TABLE','VIEW','INDEX') - AND object_name LIKE '%%%s%%' - ORDER BY object_name""" % - (which_view[0], which_view[1], arg.upper()), terminator=parsed ) - return + return self.do_select(arg.replaceArgs("""SELECT object_name, object_type%s FROM %s_objects + WHERE object_type IN ('TABLE','VIEW','INDEX') + AND object_name LIKE '%%%s%%' + ORDER BY object_name;""" % + (opts.which_schemas[0], opts.which_schemas[1], target))) self.stdout.write("%s %s.%s\n" % (object_type, owner, object_name)) descQ = descQueries.get(object_type) if descQ: for q in descQ: - self.do_select(q, bindVarsIn={'object_name':object_name, 'owner':owner}, terminator=parsed) + self.do_select(arg.newCommand(q), bindVarsIn={'object_name':object_name, 'owner':owner}) elif object_type == 'PACKAGE': packageContents = self.select_scalar_list(descQueries['PackageObjects'][0], {'package_name':object_name, 'owner':owner}) for packageObj_name in packageContents: self.stdout.write('Arguments to %s\n' % (packageObj_name)) - self.do_select(descQueries['PackageObjArgs'][0], - bindVarsIn={'package_name':object_name, 'owner':owner, 'object_name':packageObj_name}, - terminator=parsed) + self.do_select(arg.replaceArgs(descQueries['PackageObjArgs'][0]), + bindVarsIn={'package_name':object_name, 'owner':owner, 'object_name':packageObj_name}) do_desc = do_describe def do_deps(self, arg): - parsed = self.parsed(arg) - arg = parsed.unterminated.upper() - object_type, owner, object_name = self.resolve(arg) + target = arg.upper() + object_type, owner, object_name = self.resolve(target) if object_type == 'PACKAGE BODY': q = "and (type != 'PACKAGE BODY' or name != :object_name)'" object_type = 'PACKAGE' else: q = "" - q = """ name, + q = """SELECT name, type from user_dependencies where referenced_name like :object_name and referenced_type like :object_type and referenced_owner like :owner - %s""" % (q) - self.do_select(q, {'object_name':object_name, 'object_type':object_type, 'owner':owner}, terminator=parsed) + %s;""" % (q) + self.do_select(arg.replaceArgs(q), bindVarsIn={'object_name':object_name, 'object_type':object_type, 'owner':owner}) def do_comments(self, arg): 'Prints comments on a table and its columns.' - parsed = self.parsed(arg) - arg = parsed.unterminated.upper() - object_type, owner, object_name, colName = self.resolve_with_column(arg) + target = arg.upper() + object_type, owner, object_name, colName = self.resolve_with_column(target) if object_type: - self.curs.execute(queries['tabComments'],{'table_name':object_name, 'owner':owner}) + self.curs.execute(queries['tabComments'], {'table_name':object_name, 'owner':owner}) self.stdout.write("%s %s.%s: %s\n" % (object_type, owner, object_name, self.curs.fetchone()[0])) if colName: - self.do_select(queries['oneColComments'], bindVarsIn={'owner':owner, 'object_name': object_name, 'column_name': colName}, terminator=parsed) + self.do_select(arg.replaceArgs(queries['oneColComments']), bindVarsIn={'owner':owner, 'object_name': object_name, 'column_name': colName}) else: - self.do_select(queries['colComments'], bindVarsIn={'owner':owner, 'object_name': object_name}, terminator=parsed) + self.do_select(arg.replaceArgs(queries['colComments']), bindVarsIn={'owner':owner, 'object_name': object_name}) def resolve(self, identifier): """Checks (my objects).name, (my synonyms).name, (public synonyms).name @@ -724,6 +706,7 @@ Sorting is recommended to avoid false hits. Will attempt to use a graphical diff/merge tool like kdiff3, meld, or Araxis Merge, if they are installed.""" + #TODO: Update this to use pyparsing fnames = [] args2 = args.split(' to ') if len(args2) < 2: @@ -779,55 +762,32 @@ except KeyError: print 'psql command \%s not yet supported.' % abbrev - @options([make_option('-a','--all',action='store_true', - help='Describe all objects (not just my own)')]) + @options([sqlpyPlus.all_users_option]) def do__dir_tables(self, arg, opts): - if opts.all: - which_view = (', owner', 'all') - else: - which_view = ('', 'user') - self.do_select("""table_name, 'TABLE' as type%s FROM %s_tables WHERE table_name LIKE '%%%s%%'""" % - (which_view[0], which_view[1], arg.upper())) + which_view = self._which_view(opts) + self.do_select(arg.replaceArgs("""SELECT table_name, 'TABLE' as type%s FROM %s_tables WHERE table_name LIKE '%%%s%%';""" % + (opts.which_schemas[0], opts.which_schemas[1], arg.upper()))) - @options([make_option('-a','--all',action='store_true', - help='Describe all objects (not just my own)')]) + @options([sqlpyPlus.all_users_option]) def do__dir_views(self, arg, opts): - if opts.all: - which_view = (', owner', 'all') - else: - which_view = ('', 'user') - self.do_select("""view_name, 'VIEW' as type%s FROM %s_views WHERE view_name LIKE '%%%s%%'""" % - (which_view[0], which_view[1], arg.upper())) + self.do_select(arg.replaceArgs("""SELECT view_name, 'VIEW' as type%s FROM %s_views WHERE view_name LIKE '%%%s%%';""" % + (opts.which_schemas[0], opts.which_schemas[1], arg.upper()))) - @options([make_option('-a','--all',action='store_true', - help='Describe all objects (not just my own)')]) + @options([sqlpyPlus.all_users_option]) def do__dir_indexes(self, arg, opts): - if opts.all: - which_view = (', owner', 'all') - else: - which_view = ('', 'user') - self.do_select("""index_name, index_type%s FROM %s_indexes WHERE index_name LIKE '%%%s%%' OR table_name LIKE '%%%s%%'""" % - (which_view[0], which_view[1], arg.upper(), arg.upper())) + self.do_select(arg.replaceArgs("""SELECT index_name, index_type%s FROM %s_indexes WHERE index_name LIKE '%%%s%%' OR table_name LIKE '%%%s%%';""" % + (opts.which_schemas[0], opts.which_schemas[1], arg.upper(), arg.upper()))) def do__dir_tablespaces(self, arg): - self.do_select("""tablespace_name, file_name from dba_data_files""") + self.do_select(arg.replaceArgs("""SELECT tablespace_name, file_name from dba_data_files;""")) def do__dir_schemas(self, arg): - self.do_select("""owner, count(*) AS objects FROM all_objects GROUP BY owner ORDER BY owner""") + self.do_select(arg.replaceArgs"""SELECT owner, count(*) AS objects FROM all_objects GROUP BY owner ORDER BY owner;""")) def do_head(self, arg): - nrows = 10 - args = arg.split() - if len(args) > 1: - for a in args: - if a[0] == '-': - try: - nrows = int(a[1:]) - args.remove(a) - except: - pass - arg = ' '.join(args) - self.do_select('* from %s;%d' % (arg, nrows)) + newStatement = arg.replaceArgs('SELECT * FROM %s;' % arg) + newStatement.suffix = newStatement.suffix or 10 + self.do_select(newStatement) def do_print(self, arg): 'print VARNAME: Show current value of bind variable VARNAME.' @@ -866,7 +826,7 @@ except ValueError: statekeeper = Statekeeper(self, ('autobind',)) self.autobind = True - self.do_select('%s AS %s FROM dual;' % (val, var)) + self.onecmd('SELECT %s AS %s FROM dual;' % (val, var)) statekeeper.restore() def do_exec(self, arg): @@ -927,18 +887,8 @@ statement = '''SELECT object_type || '/' || %s AS name %s FROM %s_objects %s ORDER BY object_type, object_name;''' % (objname, extraInfo, whose, where) - self.onecmd(statement) + self.do_select(arg.replaceArgs(statement)) - def do_cat(self, arg): - '''cat TABLENAME --> SELECT * FROM equivalent''' - if not arg: - print self.do_cat.__doc__ - return - arg = self.parsed(arg) - targets = arg.unterminated.split() - for target in targets: - self.do_select('* from %s' % target, terminator=arg) - @options([make_option('-i', '--ignore-case', dest='ignorecase', action='store_true', help='Case-insensitive search')]) def do_grep(self, arg, opts): """grep PATTERN TABLE - search for term in any of TABLE's fields""" @@ -966,7 +916,7 @@ else: sql = ' or '.join("%s LIKE '%%%s%%'" % (d[0], pattern) for d in self.curs.description) sql = '* FROM %s WHERE %s' % (target, sql) - self.do_select(sql, terminator=arg) + self.do_select(arg.replaceArgs(sql)) except Exception, e: print e import traceback