# HG changeset patch # User catherine@Elli.myhome.westell.com # Date 1226606047 18000 # Node ID b2683d01a72f9bceee7a37b7154d8374e52ac80f # Parent 2ab3a24d7974f6edc71c7098bde5e8d5b156c354 terminator problem in cat diff -r 2ab3a24d7974 -r b2683d01a72f sqlpython/sqlpyPlus.py --- a/sqlpython/sqlpyPlus.py Fri Nov 07 16:14:21 2008 -0500 +++ b/sqlpython/sqlpyPlus.py Thu Nov 13 14:54:07 2008 -0500 @@ -1,988 +1,976 @@ -"""sqlpyPlus - extra features (inspired by Oracle SQL*Plus) for Luca Canali's sqlpython.py - -Features include: - - SQL*Plus-style bind variables - - `set autobind on` stores single-line result sets in bind variables automatically - - SQL buffer with list, run, ed, get, etc.; unlike SQL*Plus, buffer stores session's full history - - @script.sql loads and runs (like SQL*Plus) - - ! runs operating-system command - - show and set to control sqlpython parameters - - SQL*Plus-style describe, spool - - write sends query result directly to file - - comments shows table and column comments - - compare ... to ... graphically compares results of two queries - - commands are case-insensitive - - context-sensitive tab-completion for table names, column names, etc. - -Use 'help' within sqlpython for details. - -Set bind variables the hard (SQL*Plus) way -exec :b = 3 -or with a python-like shorthand -:b = 3 - -- catherinedevlin.blogspot.com May 31, 2006 -""" -import sys, os, re, sqlpython, cx_Oracle, pyparsing, re, completion, datetime, pickle -from cmd2 import Cmd, make_option, options, Statekeeper, Cmd2TestCase -from output_templates import output_templates -from plothandler import Plot -try: - import pylab -except: - pass - -descQueries = { -'TABLE': (""" - 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 '' -ELSE '(' || -CASE atc.data_type WHEN 'NUMBER' THEN TO_CHAR(atc.data_precision) || -CASE atc.data_scale WHEN 0 THEN '' -ELSE ',' || TO_CHAR(atc.data_scale) END -ELSE TO_CHAR(atc.data_length) END -END || -CASE atc.data_type WHEN 'DATE' THEN '' ELSE ')' END -data_type -FROM all_tab_columns atc -WHERE atc.table_name = :object_name -AND atc.owner = :owner -ORDER BY atc.column_id;""",), -'PROCEDURE': (""" - NVL(argument_name, 'Return Value') argument_name, -data_type, -in_out, -default_value -FROM all_arguments -WHERE object_name = :object_name -AND owner = :owner -AND package_name IS NULL -ORDER BY sequence;""",), -'PackageObjects':(""" -SELECT DISTINCT object_name -FROM all_arguments -WHERE package_name = :package_name -AND owner = :owner""",), -'PackageObjArgs':(""" - object_name, -argument_name, -data_type, -in_out, -default_value -FROM all_arguments -WHERE package_name = :package_name -AND object_name = :object_name -AND owner = :owner -AND argument_name IS NOT NULL -ORDER BY sequence""",), -'TRIGGER':(""" - description -FROM all_triggers -WHERE owner = :owner -AND trigger_name = :object_name -""", -""" -table_owner, -base_object_type, -table_name, -column_name, -when_clause, -status, -action_type, -crossedition -FROM all_triggers -WHERE owner = :owner -AND trigger_name = :object_name -\\t -""", -), -'INDEX':(""" -index_type, -table_owner, -table_name, -table_type, -uniqueness, -compression, -partitioned, -temporary, -generated, -secondary, -dropped, -visibility -FROM all_indexes -WHERE owner = :owner -AND index_name = :object_name -\\t -""",) -} -descQueries['VIEW'] = descQueries['TABLE'] -descQueries['FUNCTION'] = descQueries['PROCEDURE'] - -queries = { -'resolve': """ -SELECT object_type, object_name, owner FROM ( -SELECT object_type, object_name, user owner, 1 priority -FROM user_objects -WHERE object_name = :objName -UNION ALL -SELECT ao.object_type, ao.object_name, ao.owner, 2 priority -FROM all_objects ao -JOIN user_synonyms us ON (us.table_owner = ao.owner AND us.table_name = ao.object_name) -WHERE us.synonym_name = :objName -AND ao.object_type != 'SYNONYM' -UNION ALL -SELECT ao.object_type, ao.object_name, ao.owner, 3 priority -FROM all_objects ao -JOIN all_synonyms asyn ON (asyn.table_owner = ao.owner AND asyn.table_name = ao.object_name) -WHERE asyn.synonym_name = :objName -AND ao.object_type != 'SYNONYM' -AND asyn.owner = 'PUBLIC' -UNION ALL -SELECT 'DIRECTORY' object_type, dir.directory_name, dir.owner, 6 priority -FROM all_directories dir -WHERE dir.directory_name = :objName -UNION ALL -SELECT 'DATABASE LINK' object_type, db_link, owner, 7 priority -FROM all_db_links dbl -WHERE dbl.db_link = :objName -) ORDER BY priority ASC, - length(object_type) ASC, - object_type DESC""", # preference: PACKAGE before PACKAGE BODY, TABLE before INDEX -'tabComments': """ -SELECT comments -FROM all_tab_comments -WHERE owner = :owner -AND table_name = :table_name""", -'colComments': """ -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;""", -'oneColComments': """ -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 -AND acc.column_name = :column_name -ORDER BY atc.column_id;""", -#thanks to Senora.pm for "refs" -'refs': """ -NULL referenced_by, -c2.table_name references, -c1.constraint_name constraint -FROM -user_constraints c1, -user_constraints c2 -WHERE -c1.table_name = :object_name -and c1.constraint_type ='R' -and c1.r_constraint_name = c2.constraint_name -and c1.r_owner = c2.owner -and c1.owner = :owner -UNION -SELECT c1.table_name referenced_by, -NULL references, -c1.constraint_name constraint -FROM -user_constraints c1, -user_constraints c2 -WHERE -c2.table_name = :object_name -and c1.constraint_type ='R' -and c1.r_constraint_name = c2.constraint_name -and c1.r_owner = c2.owner -and c1.owner = :owner -""" -} - -if float(sys.version[:3]) < 2.3: - def enumerate(lst): - return zip(range(len(lst)), lst) - -class SoftwareSearcher(object): - def __init__(self, softwareList, purpose): - self.softwareList = softwareList - self.purpose = purpose - self.software = None - def invoke(self, *args): - if not self.software: - (self.software, self.invokeString) = self.find() - argTuple = tuple([self.software] + list(args)) - os.system(self.invokeString % argTuple) - def find(self): - if self.purpose == 'text editor': - software = os.environ.get('EDITOR') - if software: - return (software, '%s %s') - for (n, (software, invokeString)) in enumerate(self.softwareList): - if os.path.exists(software): - if n > (len(self.softwareList) * 0.7): - print """ - - Using %s. Note that there are better options available for %s, - but %s couldn't find a better one in your PATH. - Feel free to open up %s - and customize it to find your favorite %s program. - - """ % (software, self.purpose, __file__, __file__, self.purpose) - return (software, invokeString) - stem = os.path.split(software)[1] - for p in os.environ['PATH'].split(os.pathsep): - if os.path.exists(os.sep.join([p, stem])): - return (stem, invokeString) - raise (OSError, """Could not find any %s programs. You will need to install one, - or customize %s to make it aware of yours. -Looked for these programs: -%s""" % (self.purpose, __file__, "\n".join([s[0] for s in self.softwareList]))) - #v2.4: %s""" % (self.purpose, __file__, "\n".join(s[0] for s in self.softwareList))) - -softwareLists = { - 'diff/merge': [ - ('/usr/bin/meld',"%s %s %s"), - ('/usr/bin/kdiff3',"%s %s %s"), - (r'C:\Program Files\Araxis\Araxis Merge v6.5\Merge.exe','"%s" %s %s'), - (r'C:\Program Files\TortoiseSVN\bin\TortoiseMerge.exe', '"%s" /base:"%s" /mine:"%s"'), - ('FileMerge','%s %s %s'), - ('kompare','%s %s %s'), - ('WinMerge','%s %s %s'), - ('xxdiff','%s %s %s'), - ('fldiff','%s %s %s'), - ('gtkdiff','%s %s %s'), - ('tkdiff','%s %s %s'), - ('gvimdiff','%s %s %s'), - ('diff',"%s %s %s"), - (r'c:\windows\system32\comp.exe',"%s %s %s")], - 'text editor': [ - ('gedit', '%s %s'), - ('textpad', '%s %s'), - ('notepad.exe', '%s %s'), - ('pico', '%s %s'), - ('emacs', '%s %s'), - ('vim', '%s %s'), - ('vi', '%s %s'), - ('ed', '%s %s'), - ('edlin', '%s %s') - ] -} - -diffMergeSearcher = SoftwareSearcher(softwareLists['diff/merge'],'diff/merge') -editSearcher = SoftwareSearcher(softwareLists['text editor'], 'text editor') -editor = os.environ.get('EDITOR') -if editor: - editSearcher.find = lambda: (editor, "%s %s") - -class CaselessDict(dict): - """dict with case-insensitive keys. - - Posted to ASPN Python Cookbook by Jeff Donner - http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/66315""" - def __init__(self, other=None): - if other: - # Doesn't do keyword args - if isinstance(other, dict): - for k,v in other.items(): - dict.__setitem__(self, k.lower(), v) - else: - for k,v in other: - dict.__setitem__(self, k.lower(), v) - def __getitem__(self, key): - return dict.__getitem__(self, key.lower()) - def __setitem__(self, key, value): - dict.__setitem__(self, key.lower(), value) - def __contains__(self, key): - return dict.__contains__(self, key.lower()) - def has_key(self, key): - return dict.has_key(self, key.lower()) - def get(self, key, def_val=None): - return dict.get(self, key.lower(), def_val) - def setdefault(self, key, def_val=None): - return dict.setdefault(self, key.lower(), def_val) - def update(self, other): - for k,v in other.items(): - dict.__setitem__(self, k.lower(), v) - def fromkeys(self, iterable, value=None): - d = CaselessDict() - for k in iterable: - dict.__setitem__(d, k.lower(), value) - return d - def pop(self, key, def_val=None): - return dict.pop(self, key.lower(), def_val) - -class Parser(object): - comment_def = "--" + pyparsing.ZeroOrMore(pyparsing.CharsNotIn("\n")) - def __init__(self, scanner, retainSeparator=True): - self.scanner = scanner - self.scanner.ignore(pyparsing.sglQuotedString) - self.scanner.ignore(pyparsing.dblQuotedString) - self.scanner.ignore(self.comment_def) - self.scanner.ignore(pyparsing.cStyleComment) - self.retainSeparator = retainSeparator - def separate(self, txt): - itms = [] - for (sqlcommand, start, end) in self.scanner.scanString(txt): - if sqlcommand: - if type(sqlcommand[0]) == pyparsing.ParseResults: - if self.retainSeparator: - itms.append("".join(sqlcommand[0])) - else: - itms.append(sqlcommand[0][0]) - else: - if sqlcommand[0]: - itms.append(sqlcommand[0]) - return itms - -bindScanner = Parser(pyparsing.Literal(':') + pyparsing.Word( pyparsing.alphanums + "_$#" )) - -def findBinds(target, existingBinds, givenBindVars = {}): - result = givenBindVars - for finding, startat, endat in bindScanner.scanner.scanString(target): - varname = finding[1] - try: - result[varname] = existingBinds[varname] - except KeyError: - if not givenBindVars.has_key(varname): - print 'Bind variable %s not defined.' % (varname) - return result - -class sqlpyPlus(sqlpython.sqlpython): - defaultExtension = 'sql' - sqlpython.sqlpython.shortcuts.update({':': 'setbind', - '\\': 'psql', - '@': '_load', - '--': '_comment', - '/*': '_multiline_comment'}) - multilineCommands = '''select insert update delete tselect - create drop alter _multiline_comment'''.split() - sqlpython.sqlpython.noSpecialParse.append('spool') - defaultFileName = 'afiedt.buf' - 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 - self.spoolFile = None - 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): - """Parse the line into a command name and a string containing - the arguments. Returns a tuple containing (command, args, line). - 'command' and 'args' may be None if the line couldn't be parsed. - Overrides cmd.cmd.parseline to accept variety of shortcuts..""" - - cmd, arg, line = sqlpython.sqlpython.parseline(self, line) - if cmd in ('select', 'sleect', 'insert', 'update', 'delete', 'describe', - 'desc', 'comments', 'pull', 'refs', 'desc', 'triggers', 'find') \ - and not hasattr(self, 'curs'): - print 'Not connected.' - return '', '', '' - return cmd, arg, line - - do__load = Cmd.do_load - - def do__comment(self, arg): - pass - def do__multiline_comment(self, arg): - pass - - def onecmd_plus_hooks(self, line): - line = self.precmd(line) - stop = self.onecmd(line) - stop = self.postcmd(stop, line) - - def do_shortcuts(self,arg): - """Lists available first-character shortcuts - (i.e. '!dir' is equivalent to 'shell dir')""" - for (scchar, scto) in self.shortcuts.items(): - print '%s: %s' % (scchar, scto) - - def colnames(self): - return [d[0] for d in curs.description] - - def sql_format_itm(self, itm, needsquotes): - if itm is None: - return 'NULL' - if needsquotes: - return "'%s'" % str(itm) - return str(itm) - tableNameFinder = re.compile(r'from\s+([\w$#_"]+)', re.IGNORECASE | re.MULTILINE | re.DOTALL) - inputStatementFormatters = { - cx_Oracle.STRING: "'%s'", - cx_Oracle.DATETIME: "TO_DATE('%s', 'YYYY-MM-DD HH24:MI:SS')"} - inputStatementFormatters[cx_Oracle.CLOB] = inputStatementFormatters[cx_Oracle.STRING] - inputStatementFormatters[cx_Oracle.TIMESTAMP] = inputStatementFormatters[cx_Oracle.DATETIME] - def output(self, outformat, rowlimit): - self.tblname = self.tableNameFinder.search(self.curs.statement).group(1) - self.colnames = [d[0] for d in self.curs.description] - if outformat in output_templates: - self.colnamelen = max(len(colname) for colname in self.colnames) - self.coltypes = [d[1] for d in self.curs.description] - self.formatters = [self.inputStatementFormatters.get(typ, '%s') for typ in self.coltypes] - result = output_templates[outformat].generate(**self.__dict__) - elif outformat == '\\t': # transposed - rows = [self.colnames] - rows.extend(list(self.rows)) - transpr = [[rows[y][x] for y in range(len(rows))]for x in range(len(rows[0]))] # matrix transpose - newdesc = [['ROW N.'+str(y),10] for y in range(len(rows))] - for x in range(len(self.curs.description)): - if str(self.curs.description[x][1]) == "": # handles RAW columns - rname = transpr[x][0] - transpr[x] = map(binascii.b2a_hex, transpr[x]) - transpr[x][0] = rname - newdesc[0][0] = 'COLUMN NAME' - result = '\n' + sqlpython.pmatrix(transpr,newdesc) - elif outformat in ('\\l', '\\L', '\\p', '\\b'): - plot = Plot() - plot.build(self, outformat) - plot.shelve() - plot.draw() - return '' - else: - result = sqlpython.pmatrix(self.rows, self.curs.description, self.maxfetch) - return result - - legalOracle = re.compile('[a-zA-Z_$#]') - - def select_scalar_list(self, sql, binds={}): - self.curs.execute(sql, binds) - return [r[0] for r in self.curs.fetchall()] - - columnNameRegex = re.compile( - r'select\s+(.*)from', - re.IGNORECASE | re.DOTALL | re.MULTILINE) - def completedefault(self, text, line, begidx, endidx): - segment = completion.whichSegment(line) - text = text.upper() - completions = [] - if segment == 'select': - stmt = "SELECT column_name FROM user_tab_columns WHERE column_name LIKE '%s%%'" - completions = self.select_scalar_list(stmt % (text)) - if not completions: - stmt = "SELECT column_name FROM all_tab_columns WHERE column_name LIKE '%s%%'" - completions = self.select_scalar_list(stmt % (text)) - if segment == 'from': - columnNames = self.columnNameRegex.search(line) - if columnNames: - columnNames = columnNames.group(1) - columnNames = [c.strip().upper() for c in columnNames.split(',')] - stmt1 = "SELECT table_name FROM all_tab_columns WHERE column_name = '%s' AND table_name LIKE '%s%%'" - for columnName in columnNames: - # and if columnName is * ? - completions.extend(self.select_scalar_list(stmt1 % (columnName, text))) - if segment in ('from', 'update', 'insert into') and (not completions): - stmt = "SELECT table_name FROM user_tables WHERE table_name LIKE '%s%%'" - completions = self.select_scalar_list(stmt % (text)) - if not completions: - stmt = """SELECT table_name FROM user_tables WHERE table_name LIKE '%s%%' - UNION - SELECT DISTINCT owner FROM all_tables WHERE owner LIKE '%%%s'""" - completions = self.select_scalar_list(stmt % (text, text)) - if segment in ('where', 'group by', 'order by', 'having', 'set'): - tableNames = completion.tableNamesFromFromClause(line) - if tableNames: - stmt = """SELECT column_name FROM all_tab_columns - WHERE table_name IN (%s)""" % \ - (','.join("'%s'" % (t) for t in tableNames)) - stmt = "%s AND column_name LIKE '%s%%'" % (stmt, text) - completions = self.select_scalar_list(stmt) - if not segment: - stmt = "SELECT object_name FROM all_objects WHERE object_name LIKE '%s%%'" - completions = self.select_scalar_list(stmt % (text)) - return completions - - rowlimitPattern = pyparsing.Word(pyparsing.nums)('rowlimit') - rawTerminators = '; \\s \\S \\c \\C \\t \\i \\p \\l \\L \\b ' + ' '.join(output_templates.keys()) - terminatorPattern = (pyparsing.oneOf(rawTerminators) - ^ pyparsing.Literal('\n/') ^ \ - (pyparsing.Literal('\nEOF') + pyparsing.stringEnd)) \ - ('terminator') + \ - pyparsing.Optional(rowlimitPattern) #+ \ - #pyparsing.FollowedBy(pyparsing.LineEnd()) - def do_select(self, arg, bindVarsIn=None, override_terminator=None): - """Fetch rows from a table. - - Limit the number of rows retrieved by appending - an integer after the terminator - (example: SELECT * FROM mytable;10 ) - - Output may be formatted by choosing an alternative terminator - ("help terminators" for details) - """ - bindVarsIn = bindVarsIn or {} - statement = self.parsed('select ' + arg) - self.query = statement.unterminated - if override_terminator: - statement['terminator'] = override_terminator - statement['rowlimit'] = int(statement.rowlimit or 0) - try: - self.varsUsed = findBinds(self.query, self.binds, bindVarsIn) - self.curs.execute(self.query, self.varsUsed) - self.rows = self.curs.fetchmany(min(self.maxfetch, (statement.rowlimit or self.maxfetch))) - self.desc = self.curs.description - self.rc = self.curs.rowcount - if self.rc > 0: - self.stdout.write('\n%s\n' % (self.output(statement.terminator, statement.rowlimit))) - if self.rc == 0: - print '\nNo rows Selected.\n' - elif self.rc == 1: - print '\n1 row selected.\n' - if self.autobind: - self.binds.update(dict(zip([''.join(l for l in d[0] if l.isalnum()) for d in self.desc], self.rows[0]))) - for (i, val) in enumerate(self.rows[0]): - varname = ''.join(letter for letter in self.desc[i][0] if letter.isalnum() or letter == '_') - self.binds[varname] = val - self.binds[str(i+1)] = val - elif self.rc < self.maxfetch: - print '\n%d rows selected.\n' % self.rc - else: - print '\nSelected Max Num rows (%d)' % self.rc - except Exception, e: - print e - import traceback - traceback.print_exc(file=sys.stdout) - self.sqlBuffer.append(self.query) - - - @options([make_option('-f', '--full', action='store_true', help='get dependent objects as well')]) - def do_pull(self, arg, opts): - """Displays source code.""" - - arg = self.parsed(arg).unterminated.upper() - object_type, owner, object_name = self.resolve(arg) - if not object_type: - return - self.stdout.write("%s %s.%s\n" % (object_type, owner, object_name)) - self.stdout.write(str(self.curs.callfunc('DBMS_METADATA.GET_DDL', cx_Oracle.CLOB, - [object_type, object_name, owner]))) - if opts.full: - for dependent_type in ('OBJECT_GRANT', 'CONSTRAINT', 'TRIGGER'): - try: - self.stdout.write(str(self.curs.callfunc('DBMS_METADATA.GET_DEPENDENT_DDL', cx_Oracle.CLOB, - [dependent_type, object_name, owner]))) - 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'), - 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.""" - - arg = self.parsed(arg).unterminated.upper() - - if opts.col: - sql = "owner, table_name, column_name from all_tab_columns where column_name like '%%%s%%'" % (arg) - elif opts.table: - sql = "owner, table_name from all_tables where table_name like '%%%s%%'" % (arg) - else: - if opts.insensitive: - searchfor = "LOWER(text)" - arg = arg.lower() - else: - searchfor = "text" - sql = "* from all_source where %s like '%%%s%%'" % (searchfor, arg) - if not opts.all: - sql = '%s and owner = user' % (sql) - self.do_select(sql) - - @options([make_option('-a','--all',action='store_true', - help='Describe all objects (not just my own)')]) - def do_describe(self, arg, opts): - "emulates SQL*Plus's DESCRIBE" - - arg = self.parsed(arg).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) - return - object_type, owner, object_name = self.resolve(arg) - 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()) ) - return - 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}) - 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}) - do_desc = do_describe - - def do_deps(self, arg): - arg = self.parsed(arg).unterminated.upper() - object_type, owner, object_name = self.resolve(arg) - if object_type == 'PACKAGE BODY': - q = "and (type != 'PACKAGE BODY' or name != :object_name)'" - object_type = 'PACKAGE' - else: - q = "" - q = """ 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}) - - def do_comments(self, arg): - 'Prints comments on a table and its columns.' - arg = self.parsed(arg).unterminated.upper() - object_type, owner, object_name, colName = self.resolve_with_column(arg) - if object_type: - 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}) - else: - self.do_select(queries['colComments'],bindVarsIn={'owner':owner, 'object_name': object_name}) - - def resolve(self, identifier): - """Checks (my objects).name, (my synonyms).name, (public synonyms).name - to resolve a database object's name. """ - parts = identifier.split('.') - try: - if len(parts) == 2: - owner, object_name = parts - object_type = self.select_scalar_list('SELECT object_type FROM all_objects WHERE owner = :owner AND object_name = :object_name', - {'owner': owner, 'object_name': object_name} - )[0] - elif len(parts) == 1: - object_name = parts[0] - self.curs.execute(queries['resolve'], {'objName':object_name}) - object_type, object_name, owner = self.curs.fetchone() - except (TypeError, IndexError): - print 'Could not resolve object %s.' % identifier - object_type, owner, object_name = '', '', '' - return object_type, owner, object_name - - def resolve_with_column(self, identifier): - colName = None - object_type, owner, object_name = self.resolve(identifier) - if not object_type: - parts = identifier.split('.') - if len(parts) > 1: - colName = parts[-1] - identifier = '.'.join(parts[:-1]) - object_type, owner, object_name = self.resolve(identifier) - return object_type, owner, object_name, colName - - def do_resolve(self, arg): - arg = self.parsed(arg).unterminated.upper() - self.stdout.write(','.join(self.resolve(arg))+'\n') - - def spoolstop(self): - if self.spoolFile: - self.stdout = self.stdoutBeforeSpool - print 'Finished spooling to ', self.spoolFile.name - self.spoolFile.close() - self.spoolFile = None - - def do_spool(self, arg): - """spool [filename] - begins redirecting output to FILENAME.""" - self.spoolstop() - arg = arg.strip() - if not arg: - arg = 'output.lst' - if arg.lower() != 'off': - if '.' not in arg: - arg = '%s.lst' % arg - print 'Sending output to %s (until SPOOL OFF received)' % (arg) - self.spoolFile = open(arg, 'w') - self.stdout = self.spoolFile - - def do_write(self, args): - print 'Use (query) > outfilename instead.' - return - - def do_compare(self, args): - """COMPARE query1 TO query2 - uses external tool to display differences. - - 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.""" - fnames = [] - args2 = args.split(' to ') - if len(args2) < 2: - print self.do_compare.__doc__ - return - for n in range(len(args2)): - query = args2[n] - fnames.append('compare%s.txt' % n) - #TODO: update this terminator-stripping - if query.rstrip()[-1] != self.terminator: - query = '%s%s' % (query, self.terminator) - self.onecmd_plus_hooks('%s > %s' % (query, fnames[n])) - diffMergeSearcher.invoke(fnames[0], fnames[1]) - - bufferPosPattern = re.compile('\d+') - rangeIndicators = ('-',':') - - def do_psql(self, arg): - '''Shortcut commands emulating psql's backslash commands. - - \c connect - \d desc - \e edit - \g run - \h help - \i load - \o spool - \p list - \q quit - \w save - \db _dir_tablespaces - \dd comments - \dn _dir_schemas - \dt _dir_tables - \dv _dir_views - \di _dir_indexes - \? help psql''' - commands = {} - for c in self.do_psql.__doc__.splitlines()[2:]: - (abbrev, command) = c.split(None, 1) - commands[abbrev[1:]] = command - words = arg.split(None,1) - try: - abbrev = words[0] - except IndexError: - return - try: - args = words[1] - except IndexError: - args = '' - try: - return self.onecmd('%s %s' % (commands[abbrev], args)) - 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)')]) - 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())) - - @options([make_option('-a','--all',action='store_true', - help='Describe all objects (not just my own)')]) - 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())) - - @options([make_option('-a','--all',action='store_true', - help='Describe all objects (not just my own)')]) - 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())) - - def do__dir_tablespaces(self, arg): - self.do_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""") - - 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)) - - def do_print(self, arg): - 'print VARNAME: Show current value of bind variable VARNAME.' - if arg: - if arg[0] == ':': - arg = arg[1:] - try: - self.stdout.write(str(self.binds[arg])+'\n') - except KeyError: - self.stdout.write('No bind variable %s\n' % arg) - else: - for (var, val) in self.binds.items(): - print ':%s = %s' % (var, val) - - assignmentScanner = Parser(pyparsing.Literal(':=') ^ '=') - def do_setbind(self, arg): - if not arg: - return self.do_print(arg) - arg = self.parsed(arg).unterminated - try: - assigner, startat, endat = self.assignmentScanner.scanner.scanString(arg).next() - except StopIteration: - self.do_print(arg) - return - var, val = arg[:startat].strip(), arg[endat:].strip() - if val[0] == val[-1] == "'" and len(val) > 1: - self.binds[var] = val[1:-1] - return - try: - self.binds[var] = int(val) - return - except ValueError: - try: - self.binds[var] = float(val) - return - except ValueError: - statekeeper = Statekeeper(self, ('autobind',)) - self.autobind = True - self.do_select('%s AS %s FROM dual;' % (val, var)) - statekeeper.restore() - - def do_exec(self, arg): - if arg[0] == ':': - self.do_setbind(arg[1:]) - else: - arg = self.parsed(arg).unterminated - varsUsed = findBinds(arg, self.binds, {}) - try: - self.curs.execute('begin\n%s;end;' % arg, varsUsed) - except Exception, e: - print e - - ''' - Fails: - select n into :n from test;''' - - def anon_plsql(self, line1): - lines = [line1] - while True: - line = self.pseudo_raw_input(self.continuationPrompt) - if line.strip() == '/': - try: - self.curs.execute('\n'.join(lines)) - except Exception, e: - print e - return - lines.append(line) - - def do_begin(self, arg): - self.anon_plsql('begin ' + arg) - - def do_declare(self, arg): - self.anon_plsql('declare ' + arg) - - #def do_create(self, arg): - # self.anon_plsql('create ' + arg) - - @options([make_option('-l', '--long', action='store_true', help='long descriptions'), - make_option('-a', '--all', action='store_true', help="all schemas' objects")]) - def do_ls(self, arg, opts): - where = '' - if arg: - where = """\nWHERE object_type || '/' || object_name - LIKE '%%%s%%'""" % (arg.upper().replace('*','%')) - else: - where = '' - if opts.all: - whose = 'all' - objname = "owner || '.' || object_name" - else: - whose = 'user' - objname = 'object_name' - if opts.long: - extraInfo = ', status, last_ddl_time AS modified' - else: - extraInfo = '' - 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) - - 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%s%s' % (target, arg.terminator, arg.rowlimit)) # permissive of space before terminator - - @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""" - - arg = self.parsed(arg) - targetnames = arg.unterminated.split() - pattern = targetnames.pop(0) - targets = [] - for target in targetnames: - if '*' in target: - self.curs.execute("SELECT owner, table_name FROM all_tables WHERE table_name LIKE '%s'%s" % - (target.upper().replace('*','%')), arg.terminator) - for row in self.curs: - targets.append('%s.%s' % row) - else: - targets.append(target) - for target in targets: - print target - target = target.rstrip(';') - sql = [] - try: - self.curs.execute('select * from %s where 1=0' % target) # just to fill description - if opts.ignorecase: - sql = ' or '.join("LOWER(%s) LIKE '%%%s%%'" % (d[0], pattern.lower()) for d in self.curs.description) - 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('%s%s%s' % (sql, arg.terminator, arg.rowlimit)) - except Exception, e: - print e - import traceback - traceback.print_exc(file=sys.stdout) - - def do_refs(self, arg): - arg = self.parsed(arg).unterminated.upper() - object_type, owner, object_name = self.resolve(arg) - if object_type == 'TABLE': - self.do_select(queries['refs'],bindVarsIn={'object_name':object_name, 'owner':owner}) - -def _test(): - import doctest - doctest.testmod() - -if __name__ == "__main__": - "Silent return implies that all unit tests succeeded. Use -v to see details." +"""sqlpyPlus - extra features (inspired by Oracle SQL*Plus) for Luca Canali's sqlpython.py + +Features include: + - SQL*Plus-style bind variables + - `set autobind on` stores single-line result sets in bind variables automatically + - SQL buffer with list, run, ed, get, etc.; unlike SQL*Plus, buffer stores session's full history + - @script.sql loads and runs (like SQL*Plus) + - ! runs operating-system command + - show and set to control sqlpython parameters + - SQL*Plus-style describe, spool + - write sends query result directly to file + - comments shows table and column comments + - compare ... to ... graphically compares results of two queries + - commands are case-insensitive + - context-sensitive tab-completion for table names, column names, etc. + +Use 'help' within sqlpython for details. + +Set bind variables the hard (SQL*Plus) way +exec :b = 3 +or with a python-like shorthand +:b = 3 + +- catherinedevlin.blogspot.com May 31, 2006 +""" +import sys, os, re, sqlpython, cx_Oracle, pyparsing, re, completion, datetime, pickle +from cmd2 import Cmd, make_option, options, Statekeeper, Cmd2TestCase +from output_templates import output_templates +from plothandler import Plot +try: + import pylab +except: + pass + +descQueries = { +'TABLE': (""" + 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 '' +ELSE '(' || +CASE atc.data_type WHEN 'NUMBER' THEN TO_CHAR(atc.data_precision) || +CASE atc.data_scale WHEN 0 THEN '' +ELSE ',' || TO_CHAR(atc.data_scale) END +ELSE TO_CHAR(atc.data_length) END +END || +CASE atc.data_type WHEN 'DATE' THEN '' ELSE ')' END +data_type +FROM all_tab_columns atc +WHERE atc.table_name = :object_name +AND atc.owner = :owner +ORDER BY atc.column_id;""",), +'PROCEDURE': (""" + NVL(argument_name, 'Return Value') argument_name, +data_type, +in_out, +default_value +FROM all_arguments +WHERE object_name = :object_name +AND owner = :owner +AND package_name IS NULL +ORDER BY sequence;""",), +'PackageObjects':(""" +SELECT DISTINCT object_name +FROM all_arguments +WHERE package_name = :package_name +AND owner = :owner""",), +'PackageObjArgs':(""" + object_name, +argument_name, +data_type, +in_out, +default_value +FROM all_arguments +WHERE package_name = :package_name +AND object_name = :object_name +AND owner = :owner +AND argument_name IS NOT NULL +ORDER BY sequence""",), +'TRIGGER':(""" + description +FROM all_triggers +WHERE owner = :owner +AND trigger_name = :object_name +""", +""" +table_owner, +base_object_type, +table_name, +column_name, +when_clause, +status, +action_type, +crossedition +FROM all_triggers +WHERE owner = :owner +AND trigger_name = :object_name +\\t +""", +), +'INDEX':(""" +index_type, +table_owner, +table_name, +table_type, +uniqueness, +compression, +partitioned, +temporary, +generated, +secondary, +dropped, +visibility +FROM all_indexes +WHERE owner = :owner +AND index_name = :object_name +\\t +""",) +} +descQueries['VIEW'] = descQueries['TABLE'] +descQueries['FUNCTION'] = descQueries['PROCEDURE'] + +queries = { +'resolve': """ +SELECT object_type, object_name, owner FROM ( +SELECT object_type, object_name, user owner, 1 priority +FROM user_objects +WHERE object_name = :objName +UNION ALL +SELECT ao.object_type, ao.object_name, ao.owner, 2 priority +FROM all_objects ao +JOIN user_synonyms us ON (us.table_owner = ao.owner AND us.table_name = ao.object_name) +WHERE us.synonym_name = :objName +AND ao.object_type != 'SYNONYM' +UNION ALL +SELECT ao.object_type, ao.object_name, ao.owner, 3 priority +FROM all_objects ao +JOIN all_synonyms asyn ON (asyn.table_owner = ao.owner AND asyn.table_name = ao.object_name) +WHERE asyn.synonym_name = :objName +AND ao.object_type != 'SYNONYM' +AND asyn.owner = 'PUBLIC' +UNION ALL +SELECT 'DIRECTORY' object_type, dir.directory_name, dir.owner, 6 priority +FROM all_directories dir +WHERE dir.directory_name = :objName +UNION ALL +SELECT 'DATABASE LINK' object_type, db_link, owner, 7 priority +FROM all_db_links dbl +WHERE dbl.db_link = :objName +) ORDER BY priority ASC, + length(object_type) ASC, + object_type DESC""", # preference: PACKAGE before PACKAGE BODY, TABLE before INDEX +'tabComments': """ +SELECT comments +FROM all_tab_comments +WHERE owner = :owner +AND table_name = :table_name""", +'colComments': """ +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;""", +'oneColComments': """ +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 +AND acc.column_name = :column_name +ORDER BY atc.column_id;""", +#thanks to Senora.pm for "refs" +'refs': """ +NULL referenced_by, +c2.table_name references, +c1.constraint_name constraint +FROM +user_constraints c1, +user_constraints c2 +WHERE +c1.table_name = :object_name +and c1.constraint_type ='R' +and c1.r_constraint_name = c2.constraint_name +and c1.r_owner = c2.owner +and c1.owner = :owner +UNION +SELECT c1.table_name referenced_by, +NULL references, +c1.constraint_name constraint +FROM +user_constraints c1, +user_constraints c2 +WHERE +c2.table_name = :object_name +and c1.constraint_type ='R' +and c1.r_constraint_name = c2.constraint_name +and c1.r_owner = c2.owner +and c1.owner = :owner +""" +} + +if float(sys.version[:3]) < 2.3: + def enumerate(lst): + return zip(range(len(lst)), lst) + +class SoftwareSearcher(object): + def __init__(self, softwareList, purpose): + self.softwareList = softwareList + self.purpose = purpose + self.software = None + def invoke(self, *args): + if not self.software: + (self.software, self.invokeString) = self.find() + argTuple = tuple([self.software] + list(args)) + os.system(self.invokeString % argTuple) + def find(self): + if self.purpose == 'text editor': + software = os.environ.get('EDITOR') + if software: + return (software, '%s %s') + for (n, (software, invokeString)) in enumerate(self.softwareList): + if os.path.exists(software): + if n > (len(self.softwareList) * 0.7): + print """ + + Using %s. Note that there are better options available for %s, + but %s couldn't find a better one in your PATH. + Feel free to open up %s + and customize it to find your favorite %s program. + + """ % (software, self.purpose, __file__, __file__, self.purpose) + return (software, invokeString) + stem = os.path.split(software)[1] + for p in os.environ['PATH'].split(os.pathsep): + if os.path.exists(os.sep.join([p, stem])): + return (stem, invokeString) + raise (OSError, """Could not find any %s programs. You will need to install one, + or customize %s to make it aware of yours. +Looked for these programs: +%s""" % (self.purpose, __file__, "\n".join([s[0] for s in self.softwareList]))) + #v2.4: %s""" % (self.purpose, __file__, "\n".join(s[0] for s in self.softwareList))) + +softwareLists = { + 'diff/merge': [ + ('/usr/bin/meld',"%s %s %s"), + ('/usr/bin/kdiff3',"%s %s %s"), + (r'C:\Program Files\Araxis\Araxis Merge v6.5\Merge.exe','"%s" %s %s'), + (r'C:\Program Files\TortoiseSVN\bin\TortoiseMerge.exe', '"%s" /base:"%s" /mine:"%s"'), + ('FileMerge','%s %s %s'), + ('kompare','%s %s %s'), + ('WinMerge','%s %s %s'), + ('xxdiff','%s %s %s'), + ('fldiff','%s %s %s'), + ('gtkdiff','%s %s %s'), + ('tkdiff','%s %s %s'), + ('gvimdiff','%s %s %s'), + ('diff',"%s %s %s"), + (r'c:\windows\system32\comp.exe',"%s %s %s")], + 'text editor': [ + ('gedit', '%s %s'), + ('textpad', '%s %s'), + ('notepad.exe', '%s %s'), + ('pico', '%s %s'), + ('emacs', '%s %s'), + ('vim', '%s %s'), + ('vi', '%s %s'), + ('ed', '%s %s'), + ('edlin', '%s %s') + ] +} + +diffMergeSearcher = SoftwareSearcher(softwareLists['diff/merge'],'diff/merge') +editSearcher = SoftwareSearcher(softwareLists['text editor'], 'text editor') +editor = os.environ.get('EDITOR') +if editor: + editSearcher.find = lambda: (editor, "%s %s") + +class CaselessDict(dict): + """dict with case-insensitive keys. + + Posted to ASPN Python Cookbook by Jeff Donner - http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/66315""" + def __init__(self, other=None): + if other: + # Doesn't do keyword args + if isinstance(other, dict): + for k,v in other.items(): + dict.__setitem__(self, k.lower(), v) + else: + for k,v in other: + dict.__setitem__(self, k.lower(), v) + def __getitem__(self, key): + return dict.__getitem__(self, key.lower()) + def __setitem__(self, key, value): + dict.__setitem__(self, key.lower(), value) + def __contains__(self, key): + return dict.__contains__(self, key.lower()) + def has_key(self, key): + return dict.has_key(self, key.lower()) + def get(self, key, def_val=None): + return dict.get(self, key.lower(), def_val) + def setdefault(self, key, def_val=None): + return dict.setdefault(self, key.lower(), def_val) + def update(self, other): + for k,v in other.items(): + dict.__setitem__(self, k.lower(), v) + def fromkeys(self, iterable, value=None): + d = CaselessDict() + for k in iterable: + dict.__setitem__(d, k.lower(), value) + return d + def pop(self, key, def_val=None): + return dict.pop(self, key.lower(), def_val) + +class Parser(object): + comment_def = "--" + pyparsing.ZeroOrMore(pyparsing.CharsNotIn("\n")) + def __init__(self, scanner, retainSeparator=True): + self.scanner = scanner + self.scanner.ignore(pyparsing.sglQuotedString) + self.scanner.ignore(pyparsing.dblQuotedString) + self.scanner.ignore(self.comment_def) + self.scanner.ignore(pyparsing.cStyleComment) + self.retainSeparator = retainSeparator + def separate(self, txt): + itms = [] + for (sqlcommand, start, end) in self.scanner.scanString(txt): + if sqlcommand: + if type(sqlcommand[0]) == pyparsing.ParseResults: + if self.retainSeparator: + itms.append("".join(sqlcommand[0])) + else: + itms.append(sqlcommand[0][0]) + else: + if sqlcommand[0]: + itms.append(sqlcommand[0]) + return itms + +bindScanner = Parser(pyparsing.Literal(':') + pyparsing.Word( pyparsing.alphanums + "_$#" )) + +def findBinds(target, existingBinds, givenBindVars = {}): + result = givenBindVars + for finding, startat, endat in bindScanner.scanner.scanString(target): + varname = finding[1] + try: + result[varname] = existingBinds[varname] + except KeyError: + if not givenBindVars.has_key(varname): + print 'Bind variable %s not defined.' % (varname) + return result + +class sqlpyPlus(sqlpython.sqlpython): + defaultExtension = 'sql' + sqlpython.sqlpython.shortcuts.update({':': 'setbind', + '\\': 'psql', + '@': '_load'}) + multilineCommands = '''select insert update delete tselect + create drop alter _multiline_comment'''.split() + sqlpython.sqlpython.noSpecialParse.append('spool') + commentGrammars = pyparsing.Or([pyparsing.Literal('--') + pyparsing.restOfLine, pyparsing.cStyleComment]) + defaultFileName = 'afiedt.buf' + 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 + self.spoolFile = None + 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): + """Parse the line into a command name and a string containing + the arguments. Returns a tuple containing (command, args, line). + 'command' and 'args' may be None if the line couldn't be parsed. + Overrides cmd.cmd.parseline to accept variety of shortcuts..""" + + cmd, arg, line = sqlpython.sqlpython.parseline(self, line) + if cmd in ('select', 'sleect', 'insert', 'update', 'delete', 'describe', + 'desc', 'comments', 'pull', 'refs', 'desc', 'triggers', 'find') \ + and not hasattr(self, 'curs'): + print 'Not connected.' + return '', '', '' + return cmd, arg, line + + do__load = Cmd.do_load + + def onecmd_plus_hooks(self, line): + line = self.precmd(line) + stop = self.onecmd(line) + stop = self.postcmd(stop, line) + + def do_shortcuts(self,arg): + """Lists available first-character shortcuts + (i.e. '!dir' is equivalent to 'shell dir')""" + for (scchar, scto) in self.shortcuts.items(): + print '%s: %s' % (scchar, scto) + + def colnames(self): + return [d[0] for d in curs.description] + + def sql_format_itm(self, itm, needsquotes): + if itm is None: + return 'NULL' + if needsquotes: + return "'%s'" % str(itm) + return str(itm) + tableNameFinder = re.compile(r'from\s+([\w$#_"]+)', re.IGNORECASE | re.MULTILINE | re.DOTALL) + inputStatementFormatters = { + cx_Oracle.STRING: "'%s'", + cx_Oracle.DATETIME: "TO_DATE('%s', 'YYYY-MM-DD HH24:MI:SS')"} + inputStatementFormatters[cx_Oracle.CLOB] = inputStatementFormatters[cx_Oracle.STRING] + inputStatementFormatters[cx_Oracle.TIMESTAMP] = inputStatementFormatters[cx_Oracle.DATETIME] + def output(self, outformat, rowlimit): + self.tblname = self.tableNameFinder.search(self.curs.statement).group(1) + self.colnames = [d[0] for d in self.curs.description] + if outformat in output_templates: + self.colnamelen = max(len(colname) for colname in self.colnames) + self.coltypes = [d[1] for d in self.curs.description] + self.formatters = [self.inputStatementFormatters.get(typ, '%s') for typ in self.coltypes] + result = output_templates[outformat].generate(**self.__dict__) + elif outformat == '\\t': # transposed + rows = [self.colnames] + rows.extend(list(self.rows)) + transpr = [[rows[y][x] for y in range(len(rows))]for x in range(len(rows[0]))] # matrix transpose + newdesc = [['ROW N.'+str(y),10] for y in range(len(rows))] + for x in range(len(self.curs.description)): + if str(self.curs.description[x][1]) == "": # handles RAW columns + rname = transpr[x][0] + transpr[x] = map(binascii.b2a_hex, transpr[x]) + transpr[x][0] = rname + newdesc[0][0] = 'COLUMN NAME' + result = '\n' + sqlpython.pmatrix(transpr,newdesc) + elif outformat in ('\\l', '\\L', '\\p', '\\b'): + plot = Plot() + plot.build(self, outformat) + plot.shelve() + plot.draw() + return '' + else: + result = sqlpython.pmatrix(self.rows, self.curs.description, self.maxfetch) + return result + + legalOracle = re.compile('[a-zA-Z_$#]') + + def select_scalar_list(self, sql, binds={}): + self.curs.execute(sql, binds) + return [r[0] for r in self.curs.fetchall()] + + columnNameRegex = re.compile( + r'select\s+(.*)from', + re.IGNORECASE | re.DOTALL | re.MULTILINE) + def completedefault(self, text, line, begidx, endidx): + segment = completion.whichSegment(line) + text = text.upper() + completions = [] + if segment == 'select': + stmt = "SELECT column_name FROM user_tab_columns WHERE column_name LIKE '%s%%'" + completions = self.select_scalar_list(stmt % (text)) + if not completions: + stmt = "SELECT column_name FROM all_tab_columns WHERE column_name LIKE '%s%%'" + completions = self.select_scalar_list(stmt % (text)) + if segment == 'from': + columnNames = self.columnNameRegex.search(line) + if columnNames: + columnNames = columnNames.group(1) + columnNames = [c.strip().upper() for c in columnNames.split(',')] + stmt1 = "SELECT table_name FROM all_tab_columns WHERE column_name = '%s' AND table_name LIKE '%s%%'" + for columnName in columnNames: + # and if columnName is * ? + completions.extend(self.select_scalar_list(stmt1 % (columnName, text))) + if segment in ('from', 'update', 'insert into') and (not completions): + stmt = "SELECT table_name FROM user_tables WHERE table_name LIKE '%s%%'" + completions = self.select_scalar_list(stmt % (text)) + if not completions: + stmt = """SELECT table_name FROM user_tables WHERE table_name LIKE '%s%%' + UNION + SELECT DISTINCT owner FROM all_tables WHERE owner LIKE '%%%s'""" + completions = self.select_scalar_list(stmt % (text, text)) + if segment in ('where', 'group by', 'order by', 'having', 'set'): + tableNames = completion.tableNamesFromFromClause(line) + if tableNames: + stmt = """SELECT column_name FROM all_tab_columns + WHERE table_name IN (%s)""" % \ + (','.join("'%s'" % (t) for t in tableNames)) + stmt = "%s AND column_name LIKE '%s%%'" % (stmt, text) + completions = self.select_scalar_list(stmt) + if not segment: + stmt = "SELECT object_name FROM all_objects WHERE object_name LIKE '%s%%'" + completions = self.select_scalar_list(stmt % (text)) + return completions + + rowlimitPattern = pyparsing.Word(pyparsing.nums)('rowlimit') + rawTerminators = '; \\s \\S \\c \\C \\t \\i \\p \\l \\L \\b ' + ' '.join(output_templates.keys()) + terminatorPattern = (pyparsing.oneOf(rawTerminators) + ^ pyparsing.Literal('\n/') ^ \ + (pyparsing.Literal('\nEOF') + pyparsing.stringEnd)) \ + ('terminator') + \ + pyparsing.Optional(rowlimitPattern) #+ \ + #pyparsing.FollowedBy(pyparsing.LineEnd()) + def do_select(self, arg, bindVarsIn=None, override_terminator=None): + """Fetch rows from a table. + + Limit the number of rows retrieved by appending + an integer after the terminator + (example: SELECT * FROM mytable;10 ) + + Output may be formatted by choosing an alternative terminator + ("help terminators" for details) + """ + bindVarsIn = bindVarsIn or {} + statement = self.parsed('select ' + arg) + self.query = statement.unterminated + if override_terminator: + statement['terminator'] = override_terminator + statement['rowlimit'] = int(statement.rowlimit or 0) + self.varsUsed = findBinds(self.query, self.binds, bindVarsIn) + self.curs.execute(self.query, self.varsUsed) + self.rows = self.curs.fetchmany(min(self.maxfetch, (statement.rowlimit or self.maxfetch))) + self.rc = self.curs.rowcount + if self.rc > 0: + self.stdout.write('\n%s\n' % (self.output(statement.terminator, statement.rowlimit))) + if self.rc == 0: + print '\nNo rows Selected.\n' + elif self.rc == 1: + print '\n1 row selected.\n' + if self.autobind: + self.binds.update(dict(zip([''.join(l for l in d[0] if l.isalnum()) for d in self.curs.description], self.rows[0]))) + for (i, val) in enumerate(self.rows[0]): + varname = ''.join(letter for letter in self.curs.description[i][0] if letter.isalnum() or letter == '_') + self.binds[varname] = val + self.binds[str(i+1)] = val + elif self.rc < self.maxfetch: + print '\n%d rows selected.\n' % self.rc + else: + print '\nSelected Max Num rows (%d)' % self.rc + self.sqlBuffer.append(self.query) + + + @options([make_option('-f', '--full', action='store_true', help='get dependent objects as well')]) + def do_pull(self, arg, opts): + """Displays source code.""" + + arg = self.parsed(arg).unterminated.upper() + object_type, owner, object_name = self.resolve(arg) + if not object_type: + return + self.stdout.write("%s %s.%s\n" % (object_type, owner, object_name)) + self.stdout.write(str(self.curs.callfunc('DBMS_METADATA.GET_DDL', cx_Oracle.CLOB, + [object_type, object_name, owner]))) + if opts.full: + for dependent_type in ('OBJECT_GRANT', 'CONSTRAINT', 'TRIGGER'): + try: + self.stdout.write(str(self.curs.callfunc('DBMS_METADATA.GET_DEPENDENT_DDL', cx_Oracle.CLOB, + [dependent_type, object_name, owner]))) + 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'), + 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.""" + + arg = self.parsed(arg).unterminated.upper() + + if opts.col: + sql = "owner, table_name, column_name from all_tab_columns where column_name like '%%%s%%'" % (arg) + elif opts.table: + sql = "owner, table_name from all_tables where table_name like '%%%s%%'" % (arg) + else: + if opts.insensitive: + searchfor = "LOWER(text)" + arg = arg.lower() + else: + searchfor = "text" + sql = "* from all_source where %s like '%%%s%%'" % (searchfor, arg) + if not opts.all: + sql = '%s and owner = user' % (sql) + self.do_select(sql) + + @options([make_option('-a','--all',action='store_true', + help='Describe all objects (not just my own)')]) + def do_describe(self, arg, opts): + "emulates SQL*Plus's DESCRIBE" + + arg = self.parsed(arg).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) + return + object_type, owner, object_name = self.resolve(arg) + 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()) ) + return + 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}) + 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}) + do_desc = do_describe + + def do_deps(self, arg): + arg = self.parsed(arg).unterminated.upper() + object_type, owner, object_name = self.resolve(arg) + if object_type == 'PACKAGE BODY': + q = "and (type != 'PACKAGE BODY' or name != :object_name)'" + object_type = 'PACKAGE' + else: + q = "" + q = """ 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}) + + def do_comments(self, arg): + 'Prints comments on a table and its columns.' + arg = self.parsed(arg).unterminated.upper() + object_type, owner, object_name, colName = self.resolve_with_column(arg) + if object_type: + 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}) + else: + self.do_select(queries['colComments'],bindVarsIn={'owner':owner, 'object_name': object_name}) + + def resolve(self, identifier): + """Checks (my objects).name, (my synonyms).name, (public synonyms).name + to resolve a database object's name. """ + parts = identifier.split('.') + try: + if len(parts) == 2: + owner, object_name = parts + object_type = self.select_scalar_list('SELECT object_type FROM all_objects WHERE owner = :owner AND object_name = :object_name', + {'owner': owner, 'object_name': object_name} + )[0] + elif len(parts) == 1: + object_name = parts[0] + self.curs.execute(queries['resolve'], {'objName':object_name}) + object_type, object_name, owner = self.curs.fetchone() + except (TypeError, IndexError): + print 'Could not resolve object %s.' % identifier + object_type, owner, object_name = '', '', '' + return object_type, owner, object_name + + def resolve_with_column(self, identifier): + colName = None + object_type, owner, object_name = self.resolve(identifier) + if not object_type: + parts = identifier.split('.') + if len(parts) > 1: + colName = parts[-1] + identifier = '.'.join(parts[:-1]) + object_type, owner, object_name = self.resolve(identifier) + return object_type, owner, object_name, colName + + def do_resolve(self, arg): + arg = self.parsed(arg).unterminated.upper() + self.stdout.write(','.join(self.resolve(arg))+'\n') + + def spoolstop(self): + if self.spoolFile: + self.stdout = self.stdoutBeforeSpool + print 'Finished spooling to ', self.spoolFile.name + self.spoolFile.close() + self.spoolFile = None + + def do_spool(self, arg): + """spool [filename] - begins redirecting output to FILENAME.""" + self.spoolstop() + arg = arg.strip() + if not arg: + arg = 'output.lst' + if arg.lower() != 'off': + if '.' not in arg: + arg = '%s.lst' % arg + print 'Sending output to %s (until SPOOL OFF received)' % (arg) + self.spoolFile = open(arg, 'w') + self.stdout = self.spoolFile + + def do_write(self, args): + print 'Use (query) > outfilename instead.' + return + + def do_compare(self, args): + """COMPARE query1 TO query2 - uses external tool to display differences. + + 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.""" + fnames = [] + args2 = args.split(' to ') + if len(args2) < 2: + print self.do_compare.__doc__ + return + for n in range(len(args2)): + query = args2[n] + fnames.append('compare%s.txt' % n) + #TODO: update this terminator-stripping + if query.rstrip()[-1] != self.terminator: + query = '%s%s' % (query, self.terminator) + self.onecmd_plus_hooks('%s > %s' % (query, fnames[n])) + diffMergeSearcher.invoke(fnames[0], fnames[1]) + + bufferPosPattern = re.compile('\d+') + rangeIndicators = ('-',':') + + def do_psql(self, arg): + '''Shortcut commands emulating psql's backslash commands. + + \c connect + \d desc + \e edit + \g run + \h help + \i load + \o spool + \p list + \q quit + \w save + \db _dir_tablespaces + \dd comments + \dn _dir_schemas + \dt _dir_tables + \dv _dir_views + \di _dir_indexes + \? help psql''' + commands = {} + for c in self.do_psql.__doc__.splitlines()[2:]: + (abbrev, command) = c.split(None, 1) + commands[abbrev[1:]] = command + words = arg.split(None,1) + try: + abbrev = words[0] + except IndexError: + return + try: + args = words[1] + except IndexError: + args = '' + try: + return self.onecmd('%s %s' % (commands[abbrev], args)) + 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)')]) + 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())) + + @options([make_option('-a','--all',action='store_true', + help='Describe all objects (not just my own)')]) + 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())) + + @options([make_option('-a','--all',action='store_true', + help='Describe all objects (not just my own)')]) + 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())) + + def do__dir_tablespaces(self, arg): + self.do_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""") + + 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)) + + def do_print(self, arg): + 'print VARNAME: Show current value of bind variable VARNAME.' + if arg: + if arg[0] == ':': + arg = arg[1:] + try: + self.stdout.write(str(self.binds[arg])+'\n') + except KeyError: + self.stdout.write('No bind variable %s\n' % arg) + else: + for (var, val) in self.binds.items(): + print ':%s = %s' % (var, val) + + assignmentScanner = Parser(pyparsing.Literal(':=') ^ '=') + def do_setbind(self, arg): + if not arg: + return self.do_print(arg) + arg = self.parsed(arg).unterminated + try: + assigner, startat, endat = self.assignmentScanner.scanner.scanString(arg).next() + except StopIteration: + self.do_print(arg) + return + var, val = arg[:startat].strip(), arg[endat:].strip() + if val[0] == val[-1] == "'" and len(val) > 1: + self.binds[var] = val[1:-1] + return + try: + self.binds[var] = int(val) + return + except ValueError: + try: + self.binds[var] = float(val) + return + except ValueError: + statekeeper = Statekeeper(self, ('autobind',)) + self.autobind = True + self.do_select('%s AS %s FROM dual;' % (val, var)) + statekeeper.restore() + + def do_exec(self, arg): + if arg[0] == ':': + self.do_setbind(arg[1:]) + else: + arg = self.parsed(arg).unterminated + varsUsed = findBinds(arg, self.binds, {}) + try: + self.curs.execute('begin\n%s;end;' % arg, varsUsed) + except Exception, e: + print e + + ''' + Fails: + select n into :n from test;''' + + def anon_plsql(self, line1): + lines = [line1] + while True: + line = self.pseudo_raw_input(self.continuationPrompt) + if line.strip() == '/': + try: + self.curs.execute('\n'.join(lines)) + except Exception, e: + print e + return + lines.append(line) + + def do_begin(self, arg): + self.anon_plsql('begin ' + arg) + + def do_declare(self, arg): + self.anon_plsql('declare ' + arg) + + #def do_create(self, arg): + # self.anon_plsql('create ' + arg) + + @options([make_option('-l', '--long', action='store_true', help='long descriptions'), + make_option('-a', '--all', action='store_true', help="all schemas' objects")]) + def do_ls(self, arg, opts): + where = '' + if arg: + where = """\nWHERE object_type || '/' || object_name + LIKE '%%%s%%'""" % (arg.upper().replace('*','%')) + else: + where = '' + if opts.all: + whose = 'all' + objname = "owner || '.' || object_name" + else: + whose = 'user' + objname = 'object_name' + if opts.long: + extraInfo = ', status, last_ddl_time AS modified' + else: + extraInfo = '' + 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) + + 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%s%s' % (target, arg.terminator or ';', arg.rowlimit)) # permissive of space before terminator + + @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""" + + arg = self.parsed(arg) + targetnames = arg.unterminated.split() + pattern = targetnames.pop(0) + targets = [] + for target in targetnames: + if '*' in target: + self.curs.execute("SELECT owner, table_name FROM all_tables WHERE table_name LIKE '%s'%s" % + (target.upper().replace('*','%')), arg.terminator) + for row in self.curs: + targets.append('%s.%s' % row) + else: + targets.append(target) + for target in targets: + print target + target = target.rstrip(';') + sql = [] + try: + self.curs.execute('select * from %s where 1=0' % target) # just to fill description + if opts.ignorecase: + sql = ' or '.join("LOWER(%s) LIKE '%%%s%%'" % (d[0], pattern.lower()) for d in self.curs.description) + 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('%s%s%s' % (sql, arg.terminator, arg.rowlimit)) + except Exception, e: + print e + import traceback + traceback.print_exc(file=sys.stdout) + + def do_refs(self, arg): + arg = self.parsed(arg).unterminated.upper() + object_type, owner, object_name = self.resolve(arg) + if object_type == 'TABLE': + self.do_select(queries['refs'],bindVarsIn={'object_name':object_name, 'owner':owner}) + +def _test(): + import doctest + doctest.testmod() + +if __name__ == "__main__": + "Silent return implies that all unit tests succeeded. Use -v to see details." _test()