# HG changeset patch # User catherine@dellzilla # Date 1222709261 14400 # Node ID d504e9760dadbc8f887d67a9b17dd78885a174cf # Parent 4680d0629b82e590ed6bd36b3ef2663b3c4fab96 trying directory restructure for proper package diff -r 4680d0629b82 -r d504e9760dad README.txt --- a/README.txt Mon Sep 29 09:42:35 2008 -0400 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,118 +0,0 @@ -SQLPython is an open-source command-line environment for interacting with an -Oracle database. It is designed as an alternative to Oracle's SQL\*Plus. - -Installing ----------- - -Debian/Ubuntu:: - - $ sudo apt-get install python python-dev python-setuptools - $ sudo easy_install cx_Oracle - $ sudo easy_install -UZ sqlpython - -Windows: -Download and run executable installers from:: - - http://www.python.org (Python language) - http://cx-oracle.sourceforge.net/ (cx_Oracle) - http://pypi.python.org/pypi/sqlpython (sqlpython) - -Other: -Python is typically already installed. You'll need its -development package (python-dev); then easy_install -cx_Oracle and sqlpython as per Debian. - -Using ------ - -Use sqlpython more or less as you would use SQL\*Plus. - -Read the help. Experiment with UNIX-style and postgresql-style -commands. - -Special output (inspired by YASQL) ----------------------------------- - -An integer following a command terminator limits output to that number of rows, like SQL's LIMIT keyword:: - - hr@xe> SELECT * FROM jobs;2 - -If `;` is replaced by one of these special characters, the output will be formatted as such:: - ----------- ---------------------- -terminator format ----------- ---------------------- -; standard Oracle format -\c CSV (with headings) -\C CSV (no headings) -\g list -\G aligned list -\h HTML table -\i INSERT statements -\s CSV (with headings) -\S CSV (no headings) -\t transposed -\x XML ----------- ---------------------- - -Special terminators can also be combined with row limits:: - - hr@xe> SELECT * FROM jobs\h5 - -Redirecting output ------------------- - -`>` and `>>` write or append the output of a command. If a -filename is given, that will be the destination of the output. - -If no filename is given, the output will go into the paste buffer and -can immediately pasted to any program. This requires `xclip` (*nix) or -`pywin32` (Windows) to be installed on the operating system. - -Connecting ----------- - -sqlpython supports every version of connecting that SQL*Plus does, including EZCONNECT:: - - $ > sqlpython - $ > sqlpython hr/hr@xe - $ > sqlpython hr (uses ORACLE_SID, prompts for password) - $ > sqlpython hr/hr@hostmachine.somewhere.com/xe - $ > sqlpython hr/hr@hostmachine.somewhere.com:1521/xe - $ > sqlpython sys@xe as sysdba - -You may also supply commands that will be run immediately after connection:: - - $ > sqlpython hr/hr@xe @myscript.sql @another_script.sql quit - -Multi-word commands must be enclosed in double-quotes:: - - $ > sqlpython hr/hr@xe "cat jobs" "select * from employees;" - -Combining special output terminators with redirectors and command-line arguments -can produce powerful one-line programs. For instance, this generates an HTML -report and exits:: - - $ > sqlpython hr/hr@xe "select * from jobs\h > jobs.html" quit - -Modifying ---------- - -Modify mysqlpy.py; add `do_mycommand(self, arg)` -methods to the mysqlpy class to add your own commands. - -Use `self.stdout.write(txt)` in place of `print txt` -to make sure your output can be redirected into text -files or the paste buffer with `>` and `>>`. - -Contributing ------------- - -Development trunk is available from:: - - http://www.assembla.com/wiki/show/sqlpython - -Bugs and suggestions can be filed at:: - - http://www.assembla.com/spaces/sqlpython/tickets - diff -r 4680d0629b82 -r d504e9760dad __init__.py --- a/__init__.py Mon Sep 29 09:42:35 2008 -0400 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,2 +0,0 @@ -__all__ = ["sqlpython", "sqlpyPlus", "pexpecter", "mysqlpy"] -__version__ = '1.4.8' \ No newline at end of file diff -r 4680d0629b82 -r d504e9760dad completion.py --- a/completion.py Mon Sep 29 09:42:35 2008 -0400 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,62 +0,0 @@ -import pyparsing, re - -sqlStyleComment = pyparsing.Literal("--") + pyparsing.ZeroOrMore(pyparsing.CharsNotIn("\n")) -keywords = {'order by': pyparsing.Keyword('order', caseless=True) + - pyparsing.Keyword('by', caseless=True), - 'select': pyparsing.Keyword('select', caseless=True), - 'from': pyparsing.Keyword('from', caseless=True), - 'having': pyparsing.Keyword('having', caseless=True), - 'update': pyparsing.Keyword('update', caseless=True), - 'set': pyparsing.Keyword('set', caseless=True), - 'delete': pyparsing.Keyword('delete', caseless=True), - 'insert into': pyparsing.Keyword('insert', caseless=True) + - pyparsing.Keyword('into', caseless=True), - 'values': pyparsing.Keyword('values', caseless=True), - 'group by': pyparsing.Keyword('group', caseless=True) + - pyparsing.Keyword('by', caseless=True), - 'where': pyparsing.Keyword('where', caseless=True)} -for (name, parser) in keywords.items(): - parser.ignore(pyparsing.sglQuotedString) - parser.ignore(pyparsing.dblQuotedString) - parser.ignore(pyparsing.cStyleComment) - parser.ignore(sqlStyleComment) - parser.name = name - -fromClauseFinder = re.compile(r".*(from|update)(.*)(where|set)", - re.IGNORECASE | re.DOTALL | re.MULTILINE) -oracleTerms = oracleTerms = re.compile(r"[A-Z$_#][0-9A-Z_$#]*", re.IGNORECASE) -def tableNamesFromFromClause(statement): - result = fromClauseFinder.search(statement) - if not result: - return [] - result = oracleTerms.findall(result.group(2)) - result = [r.upper() for r in result if r.upper() not in ('JOIN','ON')] - return result - -def orderedParseResults(parsers, statement): - results = [] - for parser in parsers: - results.extend(parser.scanString(statement)) - results.sort(cmp=lambda x,y:cmp(x[1],y[1])) - return results - -def whichSegment(statement): - results = orderedParseResults(keywords.values(), statement) - if results: - return ' '.join(results[-1][0]) - else: - return None - -oracleIdentifierCharacters = pyparsing.alphanums + '_#$' -def wordInProgress(statement): - result = [] - letters = list(statement) - letters.reverse() - for letter in letters: - if letter not in oracleIdentifierCharacters: - result.reverse() - return ''.join(result) - result.append(letter) - result.reverse() - return ''.join(result) - \ No newline at end of file diff -r 4680d0629b82 -r d504e9760dad editplot.bash --- a/editplot.bash Mon Sep 29 09:42:35 2008 -0400 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,1 +0,0 @@ -ipython -pylab editplot.py \ No newline at end of file diff -r 4680d0629b82 -r d504e9760dad editplot.py --- a/editplot.py Mon Sep 29 09:42:35 2008 -0400 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,4 +0,0 @@ -#!/bin/bash -from plothandler import Plot - -Plot().unshelve() \ No newline at end of file diff -r 4680d0629b82 -r d504e9760dad mysqlpy.py --- a/mysqlpy.py Mon Sep 29 09:42:35 2008 -0400 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,192 +0,0 @@ -#!/usr/bin/python -# MySqlPy V1.5.0 -# Author: Luca.Canali@cern.ch -# -# -# Companion of SqlPython, a python module that reproduces Oracle's command line within python -# 'sqlplus inside python' -# See also: http://twiki.cern.ch/twiki/bin/view/PSSGroup/SqlPython -# http://catherine.devlin.googlepages.com/ - -from sqlpyPlus import * -import binascii, sys, tempfile - -class mysqlpy(sqlpyPlus): - ''' -MySqlPy V1.4.9 - 'sqlplus in python' -Author: Luca.Canali@cern.ch -Rev: 1.4.9, 26-Sep-08 - -Companion of SqlPython, a python module that reproduces Oracle's command line within python -and sqlpyPlus. Major contributions by Catherine Devlin, http://catherinedevlin.blogspot.com - -Usage: sqlpython [connect string] [single-word command] ["multi-word command"]... - -Quick start command list: - -- top -> executes a query to list all active sessions in (Oracle 10g and RAC) - (use: instance activity monitoring, a DBA tool) -- tselect -> prints the result set in trasposed form, useful to print result sets with - many columns such as dba_ or v$ views (ex: dba_tables or v$instance) -- py -> execute a python command (C.D.) -- db -> quick connect using credentials in pass.txt file - (Ex: write username and pass in pass.txt and then "db db_alias" to connect) -- sql -> prints the sql text from the cache. parameter: sql_id of the statement - (Ex: sql fzqa1qj65nagki) -- explain -> prints the execution plan from the cache. parameter: sql_id of the statement -- sessinfo-> prints session information. 1 parameter sid (Ex: sql 101 print info for sid 101) -- longops -> prints from gv$session_longops (running full scans, etc) -- load -> prints the OS load on all cluster nodes (10g RAC) -- sleect,slect -> alias for select (I mistyped select this way too many times...) -- top9i -> 9i (and single instance) version of top -- describe, @, !, spool, show, set, list, get, write -> sql*plus-like, from sqlpyPlus (C.D.) -- shortcuts: \c (connect), \d (describe), etc, from sqlpyPlus (C.D.) -- :myvarname = xx, set autobind 1, print -> bind variables management extension, to sqlplus (C.D.) - -Example: - SQL> connect username@dbalias or username/pass@dbalias - SQL> select sysdate from dual; - SQL> exit - ''' - - def __init__(self): - sqlpyPlus.__init__(self) - self.maxtselctrows = 10 - self.query_load10g = ''' - ins.instance_name,ins.host_name,round(os.value,2) load - from gv$osstat os, gv$instance ins - where os.inst_id=ins.inst_id and os.stat_name='LOAD' - order by 3 desc - ''' - self.query_top9i = '''SELECT - sid,username,osuser||'@'||terminal "Server User@terminal",program,taddr, status, - module, sql_hash_value hash, fixed_table_sequence seq, last_call_et elaps - from v$session - where username is not null and program not like 'emagent%' and status='ACTIVE' - and audsid !=sys_context('USERENV','SESSIONID') ; - ''' - self.query_ractop = '''SELECT - inst_id||'_'||sid inst_sid,username,osuser||'@'||terminal "User@Term",program, decode(taddr,null,null,'NN') tr, - sql_id, '.'||mod(fixed_table_sequence,1000) seq, state||': '||event event, - case state when 'WAITING' then seconds_in_wait else wait_time end w_tim, last_call_et elaps - from gv$session - where status='ACTIVE' and username is not null - and not (event like '% waiting for messages in the queue' and state='WAITING') - and audsid !=sys_context('USERENV','SESSIONID'); - ''' - self.query_longops = '''SELECT - inst_id,sid,username,time_remaining remaining, elapsed_seconds elapsed, sql_hash_value hash, opname,message - from gv$session_longops - where time_remaining>0; - ''' - - def do_new(self, args): - 'tells you about new objects' - self.onecmd('''SELECT owner, - object_name, - object_type -FROM all_objects -WHERE created > SYSDATE - 7;''') - def do_top9i(self,args): - '''Runs query_top9i defined above, to display active sessions in Oracle 9i''' - self.onecmd(self.query_top9i) - - def do_top(self,args): - '''Runs query_ractop defined above, to display active sessions in Oracle 10g (and RAC)''' - self.onecmd(self.query_ractop) - - def do_longops(self,args): - '''Runs query_longops defined above, to display long running operations (full scans, etc)''' - self.onecmd(self.query_longops) - - do_get = Cmd.do__load - def do_load(self,args): - '''Runs query_load10g defined above, to display OS load on cluster nodes (10gRAC) -Do not confuse with `GET myfile.sql` and `@myfile.sql`, -which get and run SQL scripts from disk.''' - self.do_select(self.query_load10g) - - def do_himom(self,args): - '''greets your mom''' - print 'hi mom' - - def do_db(self,args,filepath='pass.txt'): - '''Exec do_connect to db_alias in args (credentials form the file pass.txt) ''' - f = open(filepath,'r') - connectstr = f.readline().strip() +'@'+args - self.do_connect(connectstr) - f.close() - - def do_py(self, arg): - '''Executes a python command''' - try: - exec(arg) - except Exception, e: - print e - - def do_tselect(self, arg): - '''executes a query and prints the result in trasposed form. Useful when querying tables with many columns''' - - self.do_select(arg, override_terminator='\\t') - - def do_sql(self,args): - '''prints sql statement give the sql_id (Oracle 10gR2)''' - self.query = "select inst_id, sql_fulltext from gv$sqlstats where sql_id='"+args+"'" - try: - self.curs.execute(self.query) - row = self.curs.fetchone() - print "\nSQL statement from cache" - print "------------------------\n" - while row: - print "\nINST_ID = "+str(row[0])+" - SQL TEXT:\n", row[1].read() - row = self.curs.next() - except Exception, e: - print e - - def do_explain(self,args): - '''prints the plan of a given statement from the sql cache. 1 parameter: sql_id, see also do_sql ''' - self.query = "select * from table(dbms_xplan.display_cursor('"+args+"'))" - try: - self.curs.execute(self.query) - rows = self.curs.fetchall() - desc = self.curs.description - self.rc = self.curs.rowcount - if self.rc > 0: - print '\n' + sqlpython.pmatrix(rows,desc,200) - except Exception, e: - print e - - def do_sessinfo(self,args): - '''Reports session info for the give sid, extended to RAC with gv$''' - self.do_tselect('* from gv$session where sid='+args+';') - - def do_sleect(self,args): - '''implements sleect = select, a common typo''' - self.do_select(args) - - do_slect = do_sleect - -def run(): - my=mysqlpy() - print my.__doc__ - try: - if sys.argv[1][0] != '@': - connectstring = sys.argv.pop(1) - try: # attach AS SYSDBA or AS SYSOPER if present - for connectmode in my.connection_modes.keys(): - if connectmode.search(' %s %s' % tuple(sys.argv[1:3])): - for i in (1,2): - connectstring += ' ' + sys.argv.pop(1) - break - except TypeError: - pass - my.do_connect(connectstring) - for arg in sys.argv[1:]: - if my.onecmd(arg, assumeComplete=True) == my._STOP_AND_EXIT: - return - except IndexError: - pass - my.cmdloop() - -if __name__ == '__main__': - run() diff -r 4680d0629b82 -r d504e9760dad output_templates.py --- a/output_templates.py Mon Sep 29 09:42:35 2008 -0400 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,54 +0,0 @@ -import genshi.template - -# To make more output formats available to sqlpython, just edit this -# file, or place a copy in your local directory and edit that. - -output_templates = { - -'\\x': genshi.template.NewTextTemplate(""" - - <${tblname}_resultset>{% for row in rows %} - <$tblname>{% for (colname, itm) in zip(colnames, row) %} - <${colname.lower()}>$itm{% end %} - {% end %} - -"""), - -'\\h': genshi.template.MarkupTemplate(""" - - - - Table Name - - - - - - - - - - -
- Column Name -
- Value -
- -"""), - -'\\g': genshi.template.NewTextTemplate(""" -{% for (rowNum, row) in enumerate(rows) %} -**** Row: ${rowNum + 1} -{% for (colname, itm) in zip(colnames, row) %}$colname: $itm -{% end %}{% end %}"""), - -'\\G': genshi.template.NewTextTemplate(""" -{% for (rowNum, row) in enumerate(rows) %} -**** Row: ${rowNum + 1} -{% for (colname, itm) in zip(colnames, row) %}${colname.ljust(colnamelen)}: $itm -{% end %}{% end %}"""), - -} diff -r 4680d0629b82 -r d504e9760dad pexpecter.py --- a/pexpecter.py Mon Sep 29 09:42:35 2008 -0400 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,93 +0,0 @@ -""" -pexpecter - -Uses pexpect to handle interactive sessions -Create subclass of Session for each type of program to be used -""" -import re, os - -try: - import pexpect - - class Session(object): - available = True - call = 'theprogram %s' - errPattern = re.compile('.') - validPattern = re.compile('Connected to:') - promptstub = '>' - def __init__(self, argstring): - self.argstring = argstring - self.sess = pexpect.spawn("%s %s" % (self.call, self.argstring)) - try: - self.sess.expect(self.promptstub) - self.valid = self.validPattern.search(self.sess.before) - self.prompt = '[\r\n]%s%s' % (self.sess.before.splitlines()[-1], self.promptstub) - except: - self.valid = False - def success(self, result): - return not self.errPattern.search(result) - def attempt(self, command, timeout=30): - self.sess.sendline(self._pre_attempt(command)) - try: - self.sess.expect(self.prompt, timeout=timeout) - except pexpect.TIMEOUT: - return (False, """Errror: Waited %d seconds with no response from %s. - To wait longer, set timeout.""" % (timeout, str(self.__class__))) - result = self.sess.before - success = self.success(result) - if success: - print 'Executed through %s' % (str(self.__class__)) - return (success, result) - def _pre_attempt(self, command): - return command - - class YASQLSession(Session): - errPattern = re.compile('\n[A-Z2]{3,4}-\d{4}:\s') - terminatorPattern = re.compile('(;|\\g|\\i|\/|\\G|\\s|\\S)\s*\d*\s*$') - call = os.popen('locate -r /yasql$').readline().strip() - if not call: - print 'yasql not found; commands cannot failover to YASQL' - available = False - def _pre_attempt(self, command): - if not self.terminatorPattern.search(command): - return '%s;' % (command) - return command - - class SQLSession(Session): - def _pre_attempt(self, command): - if command.strip()[-1] != ';': - return '%s;' % (command) - return command - - class SqlPlusSession(SQLSession): - call = r'sqlplus' - errPattern = re.compile('\n[A-Z2]{3,4}-\d{4}:\s') - """ def _pre_attempt(self, command): - if command.strip()[-1] != ';': - return '%s;' % (command) - return command""" - # can still trip on: apparent error messages listed as data - - class SenoraSession(SQLSession): - errPattern = re.compile('(\n[A-Z2]{3,4}-\d{4}:\s)|(\nwhat ? )') - call = os.popen('locate -r Senora\.pm$').readline().strip() - if call: - call = 'perl %s' % (call) - else: - print 'Senora.pm not found; commands cannot failover to Senora' - available = False - -except ImportError: - print '''Python's pexpect module is not installed; cannot pass - commands through to sqlplus, etc.''' - class Session(object): - valid = False - available = False - class YASQLSession(Session): - pass - class SqlPlusSession(Session): - pass - class SenoraSession(Session): - pass - -available = [s for s in [SenoraSession, YASQLSession, SqlPlusSession] if s.available] diff -r 4680d0629b82 -r d504e9760dad plothandler.py --- a/plothandler.py Mon Sep 29 09:42:35 2008 -0400 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,58 +0,0 @@ -import shelve, pickle, cx_Oracle, datetime, sys -shelvename = 'plot.shelve' - -try: - import pylab - class Plot(object): - plottable_types = (cx_Oracle.NUMBER, datetime.datetime) - def __init__(self): - self.legends = [] - self.yserieslists = [] - self.xticks = [] - def build(self, sqlSession): - self.title = sqlSession.tblname - self.xlabel = sqlSession.curs.description[0][0] - self.datatypes = [d[1] for d in sqlSession.curs.description] - for (colNum, datatype) in enumerate(self.datatypes): - if colNum > 0 and datatype in self.plottable_types: - yseries = [row[colNum] for row in sqlSession.rows] - if max(yseries) is not None: - self.yserieslists.append(yseries) - self.legends.append(sqlSession.curs.description[colNum][0]) - if self.datatypes[0] in self.plottable_types: - self.xvalues = [r[0] for r in sqlSession.rows] - else: - self.xvalues = range(sqlSession.curs.rowcount) - self.xticks = [r[0] for r in sqlSession.rows] - def shelve(self): - s = shelve.open(shelvename,'c') - for k in ('xvalues xticks yserieslists title legends xlabel'.split()): - s[k] = getattr(self, k) - s.close() - # reading pickles fails with EOF error, don't understand - def unshelve(self): - s = shelve.open(shelvename) - self.__dict__.update(s) - s.close() - self.draw() - def draw(self): - if not self.yserieslists: - print 'At least one quantitative column needed to plot.' - return None - for yseries in self.yserieslists: - pylab.plot(self.xvalues, yseries, '-o') - if self.xticks: - pylab.xticks(self.xvalues, self.xticks) - pylab.xlabel(self.xlabel) - pylab.title(self.title) - pylab.legend(self.legends) - pylab.show() - -except ImportError: - class Plot(object): - def build(self, sqlSession): - pass - def save(self): - pass - def draw(self): - return 'Must install python-matplotlib to plot query results.' \ No newline at end of file diff -r 4680d0629b82 -r d504e9760dad setup.py --- a/setup.py Mon Sep 29 09:42:35 2008 -0400 +++ b/setup.py Mon Sep 29 13:27:41 2008 -0400 @@ -16,7 +16,7 @@ author_email="luca.canali@cern.ch", url="https://twiki.cern.ch/twiki/bin/view/PSSGroup/SqlPython", packages=find_packages(), - py_modules = ['mysqlpy','completion','sqlpyPlus','sqlpython','pexpecter','output_templates','plothandler'], + #py_modules = ['mysqlpy','completion','sqlpyPlus','sqlpython','pexpecter','output_templates','plothandler'], include_package_data=True, install_requires=['pyparsing','cmd2>=0.3.7','cx_Oracle','genshi'], keywords = 'client oracle database', diff -r 4680d0629b82 -r d504e9760dad sqlpyPlus.py --- a/sqlpyPlus.py Mon Sep 29 09:42:35 2008 -0400 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,975 +0,0 @@ -"""sqlpyPlus - extra features (inspired by Oracle SQL*Plus) for Luca Canali's sqlpython.py - -Features include: - - SQL*Plus-style bind variables - - Query result stored in special bind variable ":_" if one row, one item - - 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 -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;""", -#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'''.split() - defaultFileName = 'afiedt.buf' - def __init__(self): - sqlpython.sqlpython.__init__(self) - self.binds = CaselessDict() - self.sqlBuffer = [] - self.settable = ['maxtselctrows', 'maxfetch', 'autobind', - 'failover', 'timeout', 'commit_on_exit'] # settables must be lowercase - self.stdoutBeforeSpool = sys.stdout - self.spoolFile = None - self.autobind = False - self.failover = False - def default(self, arg, do_everywhere=False): - sqlpython.sqlpython.default(self, arg, do_everywhere) - 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) - def str_or_empty(self, itm): - if itm is None: - return '' - return str(itm) - def output_as_insert_statements(self): - usequotes = [d[1] != cx_Oracle.NUMBER for d in self.curs.description] - def formatRow(row): - return ','.join(self.sql_format_itm(itm, useq) - for (itm, useq) in zip(row, usequotes)) - result = ['INSERT INTO %s (%s) VALUES (%s);' % - (self.tblname, ','.join(self.colnames), formatRow(row)) - for row in self.rows] - return '\n'.join(result) - tableNameFinder = re.compile(r'from\s+([\w$#_"]+)', re.IGNORECASE | re.MULTILINE | re.DOTALL) - 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 == '\\i': - result = self.output_as_insert_statements() - elif outformat in output_templates: - self.colnamelen = max(len(colname) for colname in self.colnames) - result = output_templates[outformat].generate(**self.__dict__) - elif outformat in ('\\s', '\\S', '\\c', '\\C'): #csv - result = [] - if outformat in ('\\s', '\\c'): - result.append(','.join('"%s"' % colname for colname in self.colnames)) - for row in self.rows: - result.append(','.join('"%s"' % self.str_or_empty(itm) for itm in row)) - result = '\n'.join(result) - 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 == '\\p': - plot = Plot() - plot.build(self) - 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 ' + ' '.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]))) - if len(self.desc) == 1: - self.binds['_'] = self.rows[0][0] - 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 = self.resolve(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])) - 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: - print 'Could not resolve object %s.' % identifier - object_type, owner, object_name = '', '', '' - return object_type, owner, object_name - - 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): - 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: - owner = 'owner' - whose = 'all' - else: - owner = "'' AS owner" - whose = 'user' - result = [] - statement = '''SELECT object_type, object_name, - status, last_ddl_time, %s - FROM %s_objects %s - ORDER BY object_type, object_name''' % (owner, whose, where) - self.curs.execute(statement) - for (object_type, object_name, status, last_ddl_time, owner) in self.curs.fetchall(): - if opts.all: - qualified_name = '%s.%s' % (owner, object_name) - else: - qualified_name = object_name - if opts.long: - result.append('%s\t%s\t%s/%s' % (status, last_ddl_time, object_type, qualified_name)) - else: - result.append('%s/%s' % (object_type, qualified_name)) - self.stdout.write('\n'.join(result) + '\n') - - 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." - _test() diff -r 4680d0629b82 -r d504e9760dad sqlpython.py --- a/sqlpython.py Mon Sep 29 09:42:35 2008 -0400 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,203 +0,0 @@ -# -# SqlPython V1.5.0 -# Author: Luca.Canali@cern.ch, Apr 2006 -# Rev 29-May-08 -# -# A python module to reproduce Oracle's command line 'sqlplus-like' within python -# Intended to allow easy customizations and extentions -# Best used with the companion modules sqlpyPlus and mysqlpy -# See also http://twiki.cern.ch/twiki/bin/view/PSSGroup/SqlPython - -import cmd2,getpass,binascii,cx_Oracle,re,os -import pexpecter, sqlpyPlus -__version__ = '1.5.0' - # complication! separate sessions -> - # separate transactions !!!!! - # also: timeouts, other session failures - -class sqlpython(cmd2.Cmd): - '''A python module to reproduce Oracle's command line with focus on customization and extention''' - - def __init__(self): - cmd2.Cmd.__init__(self) - self.prompt = 'SQL.No_Connection> ' - self.maxfetch = 1000 - self.failoverSessions = [] - self.terminator = ';' - self.timeout = 30 - self.commit_on_exit = True - - connection_modes = {re.compile(' AS SYSDBA', re.IGNORECASE): cx_Oracle.SYSDBA, - re.compile(' AS SYSOPER', re.IGNORECASE): cx_Oracle.SYSOPER} - def do_connect(self, arg): - '''Opens the DB connection''' - modeval = 0 - for modere, modevalue in self.connection_modes.items(): - if modere.search(arg): - arg = modere.sub('', arg) - modeval = modevalue - try: - orauser, oraserv = arg.split('@') - except ValueError: - try: - oraserv = os.environ['ORACLE_SID'] - except KeyError: - print 'instance not specified and environment variable ORACLE_SID not set' - return - orauser = arg - sid = oraserv - try: - host, sid = oraserv.split('/') - try: - host, port = host.split(':') - port = int(port) - except ValueError: - port = 1521 - oraserv = cx_Oracle.makedsn(host, port, sid) - except ValueError: - pass - try: - orauser, orapass = orauser.split('/') - except ValueError: - orapass = getpass.getpass('Password: ') - if orauser.upper() == 'SYS' and not modeval: - print 'Privilege not specified for SYS, assuming SYSOPER' - modeval = cx_Oracle.SYSOPER - try: - self.orcl = cx_Oracle.connect(orauser,orapass,oraserv,modeval) - self.curs = self.orcl.cursor() - self.prompt = '%s@%s> ' % (orauser, sid) - self.failoverSessions = [f for f in [fbs(arg) for fbs in pexpecter.available] if f.available] - except Exception, e: - print e - - - def emptyline(self): - pass - - def fail(self, arg, do_everywhere=False): - if self.failover: - success, result = False, '' - for fbs in self.failoverSessions: - success, result = fbs.attempt(arg) - if success: - print result - if not do_everywhere: - return True - print result - return False - - def designated_session(self, arg, sesstype): - for fbs in self.failoverSessions: - if fbs.valid and fbs.__class__ == sesstype: - success, result = fbs.attempt(arg) - print result - return - print 'Valid %s not found' % (sesstype.__name__) - - def do_terminators(self, arg): - """; standard Oracle format -\\c CSV (with headings) -\\C CSV (no headings) -\\g list -\\G aligned list -\\h HTML table -\\i INSERT statements -\\s CSV (with headings) -\\S CSV (no headings) -\\t transposed -\\x XML -\\p plot, with markers -\\P plot, continuous lines""" - print self.do_terminators.__doc__ - - terminatorSearchString = '|'.join('\\' + d.split()[0] for d in do_terminators.__doc__.splitlines()) - - def do_yasql(self, arg): - '''Sends a command to a YASQL session (http://sourceforge.net/projects/yasql/)''' - self.designated_session(arg, pexpecter.YASQLSession) - do_y = do_yasql - def do_sqlplus(self, arg): - '''Sends a command to a SQL*Plus session''' - self.designated_session(arg, pexpecter.SqlPlusSession) - do_sqlp = do_sqlplus - def do_senora(self, arg): - '''Sends a command to a Senora session (http://senora.sourceforge.net/)''' - self.designated_session(arg, pexpecter.SenoraSession) - do_sen = do_senora - - def default(self, arg, do_everywhere = False): - statement = self.parsed(arg) - self.query = statement.unterminated - try: - self.varsUsed = sqlpyPlus.findBinds(self.query, self.binds, givenBindVars={}) - self.curs.execute(self.query, self.varsUsed) - print '\nExecuted%s\n' % ((self.curs.rowcount > 0) and ' (%d rows)' % self.curs.rowcount or '') - if do_everywhere: - self.fail(arg, do_everywhere = True ) - except Exception, e: - result = self.fail(arg) - if not result: - print str(e) - - def do_commit(self, arg): - self.default('commit %s;' % (arg), do_everywhere=True) - def do_rollback(self, arg): - self.default('rollback %s;' % (arg), do_everywhere=True) - def do_quit(self, arg): - if self.commit_on_exit and hasattr(self, 'curs'): - self.default('commit;') - cmd2.Cmd.do_quit() - do_exit = do_quit - do_q = do_quit - -def pmatrix(rows,desc,maxlen=30): - '''prints a matrix, used by sqlpython to print queries' result sets''' - names = [] - maxen = [] - toprint = [] - for d in desc: - n = d[0] - names.append(n) # list col names - maxen.append(len(n)) # col length - rcols = range(len(desc)) - rrows = range(len(rows)) - for i in rrows: # loops for all rows - rowsi = map(str, rows[i]) # current row to process - split = [] # service var is row split is needed - mustsplit = 0 # flag - for j in rcols: - if str(desc[j][1]) == "": # handles RAW columns - rowsi[j] = binascii.b2a_hex(rowsi[j]) - maxen[j] = max(maxen[j], len(rowsi[j])) # computes max field length - if maxen[j] <= maxlen: - split.append('') - else: # split the line is 2 because field is too long - mustsplit = 1 - maxen[j] = maxlen - split.append(rowsi[j][maxlen-1:2*maxlen-1]) - rowsi[j] = rowsi[j][0:maxlen-1] # this implem. truncates after maxlen*2 - toprint.append(rowsi) # 'toprint' is a printable copy of rows - if mustsplit != 0: - toprint.append(split) - sepcols = [] - for i in rcols: - maxcol = maxen[i] - name = names[i] - sepcols.append("-" * maxcol) # formats column names (header) - names[i] = name + (" " * (maxcol-len(name))) # formats separ line with -- - rrows2 = range(len(toprint)) - for j in rrows2: - val = toprint[j][i] - if str(desc[i][1]) == "": # right align numbers - toprint[j][i] = (" " * (maxcol-len(val))) + val - else: - toprint[j][i] = val + (" " * (maxcol-len(val))) - for j in rrows2: - toprint[j] = ' '.join(toprint[j]) - names = ' '.join(names) - sepcols = ' '.join(sepcols) - toprint.insert(0, sepcols) - toprint.insert(0, names) - return '\n'.join(toprint) - diff -r 4680d0629b82 -r d504e9760dad sqlpython/README.txt --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sqlpython/README.txt Mon Sep 29 13:27:41 2008 -0400 @@ -0,0 +1,118 @@ +SQLPython is an open-source command-line environment for interacting with an +Oracle database. It is designed as an alternative to Oracle's SQL\*Plus. + +Installing +---------- + +Debian/Ubuntu:: + + $ sudo apt-get install python python-dev python-setuptools + $ sudo easy_install cx_Oracle + $ sudo easy_install -UZ sqlpython + +Windows: +Download and run executable installers from:: + + http://www.python.org (Python language) + http://cx-oracle.sourceforge.net/ (cx_Oracle) + http://pypi.python.org/pypi/sqlpython (sqlpython) + +Other: +Python is typically already installed. You'll need its +development package (python-dev); then easy_install +cx_Oracle and sqlpython as per Debian. + +Using +----- + +Use sqlpython more or less as you would use SQL\*Plus. + +Read the help. Experiment with UNIX-style and postgresql-style +commands. + +Special output (inspired by YASQL) +---------------------------------- + +An integer following a command terminator limits output to that number of rows, like SQL's LIMIT keyword:: + + hr@xe> SELECT * FROM jobs;2 + +If `;` is replaced by one of these special characters, the output will be formatted as such:: + +---------- ---------------------- +terminator format +---------- ---------------------- +; standard Oracle format +\c CSV (with headings) +\C CSV (no headings) +\g list +\G aligned list +\h HTML table +\i INSERT statements +\s CSV (with headings) +\S CSV (no headings) +\t transposed +\x XML +---------- ---------------------- + +Special terminators can also be combined with row limits:: + + hr@xe> SELECT * FROM jobs\h5 + +Redirecting output +------------------ + +`>` and `>>` write or append the output of a command. If a +filename is given, that will be the destination of the output. + +If no filename is given, the output will go into the paste buffer and +can immediately pasted to any program. This requires `xclip` (*nix) or +`pywin32` (Windows) to be installed on the operating system. + +Connecting +---------- + +sqlpython supports every version of connecting that SQL*Plus does, including EZCONNECT:: + + $ > sqlpython + $ > sqlpython hr/hr@xe + $ > sqlpython hr (uses ORACLE_SID, prompts for password) + $ > sqlpython hr/hr@hostmachine.somewhere.com/xe + $ > sqlpython hr/hr@hostmachine.somewhere.com:1521/xe + $ > sqlpython sys@xe as sysdba + +You may also supply commands that will be run immediately after connection:: + + $ > sqlpython hr/hr@xe @myscript.sql @another_script.sql quit + +Multi-word commands must be enclosed in double-quotes:: + + $ > sqlpython hr/hr@xe "cat jobs" "select * from employees;" + +Combining special output terminators with redirectors and command-line arguments +can produce powerful one-line programs. For instance, this generates an HTML +report and exits:: + + $ > sqlpython hr/hr@xe "select * from jobs\h > jobs.html" quit + +Modifying +--------- + +Modify mysqlpy.py; add `do_mycommand(self, arg)` +methods to the mysqlpy class to add your own commands. + +Use `self.stdout.write(txt)` in place of `print txt` +to make sure your output can be redirected into text +files or the paste buffer with `>` and `>>`. + +Contributing +------------ + +Development trunk is available from:: + + http://www.assembla.com/wiki/show/sqlpython + +Bugs and suggestions can be filed at:: + + http://www.assembla.com/spaces/sqlpython/tickets + diff -r 4680d0629b82 -r d504e9760dad sqlpython/__init__.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sqlpython/__init__.py Mon Sep 29 13:27:41 2008 -0400 @@ -0,0 +1,3 @@ +import mysqlpy +__all__ = ["sqlpython", "sqlpyPlus", "pexpecter", "mysqlpy", "output_templates", "plothandler", "editplot"] +__version__ = '1.5.0' \ No newline at end of file diff -r 4680d0629b82 -r d504e9760dad sqlpython/completion.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sqlpython/completion.py Mon Sep 29 13:27:41 2008 -0400 @@ -0,0 +1,62 @@ +import pyparsing, re + +sqlStyleComment = pyparsing.Literal("--") + pyparsing.ZeroOrMore(pyparsing.CharsNotIn("\n")) +keywords = {'order by': pyparsing.Keyword('order', caseless=True) + + pyparsing.Keyword('by', caseless=True), + 'select': pyparsing.Keyword('select', caseless=True), + 'from': pyparsing.Keyword('from', caseless=True), + 'having': pyparsing.Keyword('having', caseless=True), + 'update': pyparsing.Keyword('update', caseless=True), + 'set': pyparsing.Keyword('set', caseless=True), + 'delete': pyparsing.Keyword('delete', caseless=True), + 'insert into': pyparsing.Keyword('insert', caseless=True) + + pyparsing.Keyword('into', caseless=True), + 'values': pyparsing.Keyword('values', caseless=True), + 'group by': pyparsing.Keyword('group', caseless=True) + + pyparsing.Keyword('by', caseless=True), + 'where': pyparsing.Keyword('where', caseless=True)} +for (name, parser) in keywords.items(): + parser.ignore(pyparsing.sglQuotedString) + parser.ignore(pyparsing.dblQuotedString) + parser.ignore(pyparsing.cStyleComment) + parser.ignore(sqlStyleComment) + parser.name = name + +fromClauseFinder = re.compile(r".*(from|update)(.*)(where|set)", + re.IGNORECASE | re.DOTALL | re.MULTILINE) +oracleTerms = oracleTerms = re.compile(r"[A-Z$_#][0-9A-Z_$#]*", re.IGNORECASE) +def tableNamesFromFromClause(statement): + result = fromClauseFinder.search(statement) + if not result: + return [] + result = oracleTerms.findall(result.group(2)) + result = [r.upper() for r in result if r.upper() not in ('JOIN','ON')] + return result + +def orderedParseResults(parsers, statement): + results = [] + for parser in parsers: + results.extend(parser.scanString(statement)) + results.sort(cmp=lambda x,y:cmp(x[1],y[1])) + return results + +def whichSegment(statement): + results = orderedParseResults(keywords.values(), statement) + if results: + return ' '.join(results[-1][0]) + else: + return None + +oracleIdentifierCharacters = pyparsing.alphanums + '_#$' +def wordInProgress(statement): + result = [] + letters = list(statement) + letters.reverse() + for letter in letters: + if letter not in oracleIdentifierCharacters: + result.reverse() + return ''.join(result) + result.append(letter) + result.reverse() + return ''.join(result) + \ No newline at end of file diff -r 4680d0629b82 -r d504e9760dad sqlpython/editplot.bash --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sqlpython/editplot.bash Mon Sep 29 13:27:41 2008 -0400 @@ -0,0 +1,1 @@ +ipython -pylab editplot.py \ No newline at end of file diff -r 4680d0629b82 -r d504e9760dad sqlpython/editplot.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sqlpython/editplot.py Mon Sep 29 13:27:41 2008 -0400 @@ -0,0 +1,3 @@ +#!/usr/bin/python +import plothandler +plothandler.Plot().unshelve() \ No newline at end of file diff -r 4680d0629b82 -r d504e9760dad sqlpython/mysqlpy.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sqlpython/mysqlpy.py Mon Sep 29 13:27:41 2008 -0400 @@ -0,0 +1,192 @@ +#!/usr/bin/python +# MySqlPy V1.5.0 +# Author: Luca.Canali@cern.ch +# +# +# Companion of SqlPython, a python module that reproduces Oracle's command line within python +# 'sqlplus inside python' +# See also: http://twiki.cern.ch/twiki/bin/view/PSSGroup/SqlPython +# http://catherine.devlin.googlepages.com/ + +from sqlpyPlus import * +import binascii, sys, tempfile + +class mysqlpy(sqlpyPlus): + ''' +MySqlPy V1.4.9 - 'sqlplus in python' +Author: Luca.Canali@cern.ch +Rev: 1.4.9, 26-Sep-08 + +Companion of SqlPython, a python module that reproduces Oracle's command line within python +and sqlpyPlus. Major contributions by Catherine Devlin, http://catherinedevlin.blogspot.com + +Usage: sqlpython [connect string] [single-word command] ["multi-word command"]... + +Quick start command list: + +- top -> executes a query to list all active sessions in (Oracle 10g and RAC) + (use: instance activity monitoring, a DBA tool) +- tselect -> prints the result set in trasposed form, useful to print result sets with + many columns such as dba_ or v$ views (ex: dba_tables or v$instance) +- py -> execute a python command (C.D.) +- db -> quick connect using credentials in pass.txt file + (Ex: write username and pass in pass.txt and then "db db_alias" to connect) +- sql -> prints the sql text from the cache. parameter: sql_id of the statement + (Ex: sql fzqa1qj65nagki) +- explain -> prints the execution plan from the cache. parameter: sql_id of the statement +- sessinfo-> prints session information. 1 parameter sid (Ex: sql 101 print info for sid 101) +- longops -> prints from gv$session_longops (running full scans, etc) +- load -> prints the OS load on all cluster nodes (10g RAC) +- sleect,slect -> alias for select (I mistyped select this way too many times...) +- top9i -> 9i (and single instance) version of top +- describe, @, !, spool, show, set, list, get, write -> sql*plus-like, from sqlpyPlus (C.D.) +- shortcuts: \c (connect), \d (describe), etc, from sqlpyPlus (C.D.) +- :myvarname = xx, set autobind 1, print -> bind variables management extension, to sqlplus (C.D.) + +Example: + SQL> connect username@dbalias or username/pass@dbalias + SQL> select sysdate from dual; + SQL> exit + ''' + + def __init__(self): + sqlpyPlus.__init__(self) + self.maxtselctrows = 10 + self.query_load10g = ''' + ins.instance_name,ins.host_name,round(os.value,2) load + from gv$osstat os, gv$instance ins + where os.inst_id=ins.inst_id and os.stat_name='LOAD' + order by 3 desc + ''' + self.query_top9i = '''SELECT + sid,username,osuser||'@'||terminal "Server User@terminal",program,taddr, status, + module, sql_hash_value hash, fixed_table_sequence seq, last_call_et elaps + from v$session + where username is not null and program not like 'emagent%' and status='ACTIVE' + and audsid !=sys_context('USERENV','SESSIONID') ; + ''' + self.query_ractop = '''SELECT + inst_id||'_'||sid inst_sid,username,osuser||'@'||terminal "User@Term",program, decode(taddr,null,null,'NN') tr, + sql_id, '.'||mod(fixed_table_sequence,1000) seq, state||': '||event event, + case state when 'WAITING' then seconds_in_wait else wait_time end w_tim, last_call_et elaps + from gv$session + where status='ACTIVE' and username is not null + and not (event like '% waiting for messages in the queue' and state='WAITING') + and audsid !=sys_context('USERENV','SESSIONID'); + ''' + self.query_longops = '''SELECT + inst_id,sid,username,time_remaining remaining, elapsed_seconds elapsed, sql_hash_value hash, opname,message + from gv$session_longops + where time_remaining>0; + ''' + + def do_new(self, args): + 'tells you about new objects' + self.onecmd('''SELECT owner, + object_name, + object_type +FROM all_objects +WHERE created > SYSDATE - 7;''') + def do_top9i(self,args): + '''Runs query_top9i defined above, to display active sessions in Oracle 9i''' + self.onecmd(self.query_top9i) + + def do_top(self,args): + '''Runs query_ractop defined above, to display active sessions in Oracle 10g (and RAC)''' + self.onecmd(self.query_ractop) + + def do_longops(self,args): + '''Runs query_longops defined above, to display long running operations (full scans, etc)''' + self.onecmd(self.query_longops) + + do_get = Cmd.do__load + def do_load(self,args): + '''Runs query_load10g defined above, to display OS load on cluster nodes (10gRAC) +Do not confuse with `GET myfile.sql` and `@myfile.sql`, +which get and run SQL scripts from disk.''' + self.do_select(self.query_load10g) + + def do_himom(self,args): + '''greets your mom''' + print 'hi mom' + + def do_db(self,args,filepath='pass.txt'): + '''Exec do_connect to db_alias in args (credentials form the file pass.txt) ''' + f = open(filepath,'r') + connectstr = f.readline().strip() +'@'+args + self.do_connect(connectstr) + f.close() + + def do_py(self, arg): + '''Executes a python command''' + try: + exec(arg) + except Exception, e: + print e + + def do_tselect(self, arg): + '''executes a query and prints the result in trasposed form. Useful when querying tables with many columns''' + + self.do_select(arg, override_terminator='\\t') + + def do_sql(self,args): + '''prints sql statement give the sql_id (Oracle 10gR2)''' + self.query = "select inst_id, sql_fulltext from gv$sqlstats where sql_id='"+args+"'" + try: + self.curs.execute(self.query) + row = self.curs.fetchone() + print "\nSQL statement from cache" + print "------------------------\n" + while row: + print "\nINST_ID = "+str(row[0])+" - SQL TEXT:\n", row[1].read() + row = self.curs.next() + except Exception, e: + print e + + def do_explain(self,args): + '''prints the plan of a given statement from the sql cache. 1 parameter: sql_id, see also do_sql ''' + self.query = "select * from table(dbms_xplan.display_cursor('"+args+"'))" + try: + self.curs.execute(self.query) + rows = self.curs.fetchall() + desc = self.curs.description + self.rc = self.curs.rowcount + if self.rc > 0: + print '\n' + sqlpython.pmatrix(rows,desc,200) + except Exception, e: + print e + + def do_sessinfo(self,args): + '''Reports session info for the give sid, extended to RAC with gv$''' + self.do_tselect('* from gv$session where sid='+args+';') + + def do_sleect(self,args): + '''implements sleect = select, a common typo''' + self.do_select(args) + + do_slect = do_sleect + +def run(): + my=mysqlpy() + print my.__doc__ + try: + if sys.argv[1][0] != '@': + connectstring = sys.argv.pop(1) + try: # attach AS SYSDBA or AS SYSOPER if present + for connectmode in my.connection_modes.keys(): + if connectmode.search(' %s %s' % tuple(sys.argv[1:3])): + for i in (1,2): + connectstring += ' ' + sys.argv.pop(1) + break + except TypeError: + pass + my.do_connect(connectstring) + for arg in sys.argv[1:]: + if my.onecmd(arg, assumeComplete=True) == my._STOP_AND_EXIT: + return + except IndexError: + pass + my.cmdloop() + +if __name__ == '__main__': + run() diff -r 4680d0629b82 -r d504e9760dad sqlpython/output_templates.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sqlpython/output_templates.py Mon Sep 29 13:27:41 2008 -0400 @@ -0,0 +1,54 @@ +import genshi.template + +# To make more output formats available to sqlpython, just edit this +# file, or place a copy in your local directory and edit that. + +output_templates = { + +'\\x': genshi.template.NewTextTemplate(""" + + <${tblname}_resultset>{% for row in rows %} + <$tblname>{% for (colname, itm) in zip(colnames, row) %} + <${colname.lower()}>$itm{% end %} + {% end %} + +"""), + +'\\h': genshi.template.MarkupTemplate(""" + + + + Table Name + + + + + + + + + + +
+ Column Name +
+ Value +
+ +"""), + +'\\g': genshi.template.NewTextTemplate(""" +{% for (rowNum, row) in enumerate(rows) %} +**** Row: ${rowNum + 1} +{% for (colname, itm) in zip(colnames, row) %}$colname: $itm +{% end %}{% end %}"""), + +'\\G': genshi.template.NewTextTemplate(""" +{% for (rowNum, row) in enumerate(rows) %} +**** Row: ${rowNum + 1} +{% for (colname, itm) in zip(colnames, row) %}${colname.ljust(colnamelen)}: $itm +{% end %}{% end %}"""), + +} diff -r 4680d0629b82 -r d504e9760dad sqlpython/pexpecter.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sqlpython/pexpecter.py Mon Sep 29 13:27:41 2008 -0400 @@ -0,0 +1,93 @@ +""" +pexpecter + +Uses pexpect to handle interactive sessions +Create subclass of Session for each type of program to be used +""" +import re, os + +try: + import pexpect + + class Session(object): + available = True + call = 'theprogram %s' + errPattern = re.compile('.') + validPattern = re.compile('Connected to:') + promptstub = '>' + def __init__(self, argstring): + self.argstring = argstring + self.sess = pexpect.spawn("%s %s" % (self.call, self.argstring)) + try: + self.sess.expect(self.promptstub) + self.valid = self.validPattern.search(self.sess.before) + self.prompt = '[\r\n]%s%s' % (self.sess.before.splitlines()[-1], self.promptstub) + except: + self.valid = False + def success(self, result): + return not self.errPattern.search(result) + def attempt(self, command, timeout=30): + self.sess.sendline(self._pre_attempt(command)) + try: + self.sess.expect(self.prompt, timeout=timeout) + except pexpect.TIMEOUT: + return (False, """Errror: Waited %d seconds with no response from %s. + To wait longer, set timeout.""" % (timeout, str(self.__class__))) + result = self.sess.before + success = self.success(result) + if success: + print 'Executed through %s' % (str(self.__class__)) + return (success, result) + def _pre_attempt(self, command): + return command + + class YASQLSession(Session): + errPattern = re.compile('\n[A-Z2]{3,4}-\d{4}:\s') + terminatorPattern = re.compile('(;|\\g|\\i|\/|\\G|\\s|\\S)\s*\d*\s*$') + call = os.popen('locate -r /yasql$').readline().strip() + if not call: + print 'yasql not found; commands cannot failover to YASQL' + available = False + def _pre_attempt(self, command): + if not self.terminatorPattern.search(command): + return '%s;' % (command) + return command + + class SQLSession(Session): + def _pre_attempt(self, command): + if command.strip()[-1] != ';': + return '%s;' % (command) + return command + + class SqlPlusSession(SQLSession): + call = r'sqlplus' + errPattern = re.compile('\n[A-Z2]{3,4}-\d{4}:\s') + """ def _pre_attempt(self, command): + if command.strip()[-1] != ';': + return '%s;' % (command) + return command""" + # can still trip on: apparent error messages listed as data + + class SenoraSession(SQLSession): + errPattern = re.compile('(\n[A-Z2]{3,4}-\d{4}:\s)|(\nwhat ? )') + call = os.popen('locate -r Senora\.pm$').readline().strip() + if call: + call = 'perl %s' % (call) + else: + print 'Senora.pm not found; commands cannot failover to Senora' + available = False + +except ImportError: + print '''Python's pexpect module is not installed; cannot pass + commands through to sqlplus, etc.''' + class Session(object): + valid = False + available = False + class YASQLSession(Session): + pass + class SqlPlusSession(Session): + pass + class SenoraSession(Session): + pass + +available = [s for s in [SenoraSession, YASQLSession, SqlPlusSession] if s.available] diff -r 4680d0629b82 -r d504e9760dad sqlpython/plothandler.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sqlpython/plothandler.py Mon Sep 29 13:27:41 2008 -0400 @@ -0,0 +1,60 @@ +import shelve, pickle, cx_Oracle, datetime, sys +shelvename = 'plot.shelve' + +try: + import pylab + class Plot(object): + plottable_types = (cx_Oracle.NUMBER, datetime.datetime) + def __init__(self): + self.legends = [] + self.yserieslists = [] + self.xticks = [] + def build(self, sqlSession): + self.title = sqlSession.tblname + self.xlabel = sqlSession.curs.description[0][0] + self.datatypes = [d[1] for d in sqlSession.curs.description] + for (colNum, datatype) in enumerate(self.datatypes): + if colNum > 0 and datatype in self.plottable_types: + yseries = [row[colNum] for row in sqlSession.rows] + if max(yseries) is not None: + self.yserieslists.append(yseries) + self.legends.append(sqlSession.curs.description[colNum][0]) + if self.datatypes[0] in self.plottable_types: + self.xvalues = [r[0] for r in sqlSession.rows] + else: + self.xvalues = range(sqlSession.curs.rowcount) + self.xticks = [r[0] for r in sqlSession.rows] + def shelve(self): + s = shelve.open(shelvename,'c') + for k in ('xvalues xticks yserieslists title legends xlabel'.split()): + s[k] = getattr(self, k) + s.close() + # reading pickles fails with EOF error, don't understand + def unshelve(self): + s = shelve.open(shelvename) + self.__dict__.update(s) + s.close() + self.draw() + def draw(self): + if not self.yserieslists: + print 'At least one quantitative column needed to plot.' + return None + for yseries in self.yserieslists: + pylab.plot(self.xvalues, yseries, '-o') + if self.xticks: + pylab.xticks(self.xvalues, self.xticks) + pylab.xlabel(self.xlabel) + pylab.title(self.title) + pylab.legend(self.legends) + pylab.show() + +except ImportError: + class Plot(object): + def build(self, sqlSession): + pass + def save(self): + pass + def shelve(self): + pass + def draw(self): + return 'Must install python-matplotlib to plot query results.' \ No newline at end of file diff -r 4680d0629b82 -r d504e9760dad sqlpython/sqlpyPlus.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sqlpython/sqlpyPlus.py Mon Sep 29 13:27:41 2008 -0400 @@ -0,0 +1,975 @@ +"""sqlpyPlus - extra features (inspired by Oracle SQL*Plus) for Luca Canali's sqlpython.py + +Features include: + - SQL*Plus-style bind variables + - Query result stored in special bind variable ":_" if one row, one item + - 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 +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;""", +#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'''.split() + defaultFileName = 'afiedt.buf' + def __init__(self): + sqlpython.sqlpython.__init__(self) + self.binds = CaselessDict() + self.sqlBuffer = [] + self.settable = ['maxtselctrows', 'maxfetch', 'autobind', + 'failover', 'timeout', 'commit_on_exit'] # settables must be lowercase + self.stdoutBeforeSpool = sys.stdout + self.spoolFile = None + self.autobind = False + self.failover = False + def default(self, arg, do_everywhere=False): + sqlpython.sqlpython.default(self, arg, do_everywhere) + 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) + def str_or_empty(self, itm): + if itm is None: + return '' + return str(itm) + def output_as_insert_statements(self): + usequotes = [d[1] != cx_Oracle.NUMBER for d in self.curs.description] + def formatRow(row): + return ','.join(self.sql_format_itm(itm, useq) + for (itm, useq) in zip(row, usequotes)) + result = ['INSERT INTO %s (%s) VALUES (%s);' % + (self.tblname, ','.join(self.colnames), formatRow(row)) + for row in self.rows] + return '\n'.join(result) + tableNameFinder = re.compile(r'from\s+([\w$#_"]+)', re.IGNORECASE | re.MULTILINE | re.DOTALL) + 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 == '\\i': + result = self.output_as_insert_statements() + elif outformat in output_templates: + self.colnamelen = max(len(colname) for colname in self.colnames) + result = output_templates[outformat].generate(**self.__dict__) + elif outformat in ('\\s', '\\S', '\\c', '\\C'): #csv + result = [] + if outformat in ('\\s', '\\c'): + result.append(','.join('"%s"' % colname for colname in self.colnames)) + for row in self.rows: + result.append(','.join('"%s"' % self.str_or_empty(itm) for itm in row)) + result = '\n'.join(result) + 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 == '\\p': + plot = Plot() + plot.build(self) + 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 ' + ' '.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]))) + if len(self.desc) == 1: + self.binds['_'] = self.rows[0][0] + 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 = self.resolve(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])) + 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: + print 'Could not resolve object %s.' % identifier + object_type, owner, object_name = '', '', '' + return object_type, owner, object_name + + 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): + 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: + owner = 'owner' + whose = 'all' + else: + owner = "'' AS owner" + whose = 'user' + result = [] + statement = '''SELECT object_type, object_name, + status, last_ddl_time, %s + FROM %s_objects %s + ORDER BY object_type, object_name''' % (owner, whose, where) + self.curs.execute(statement) + for (object_type, object_name, status, last_ddl_time, owner) in self.curs.fetchall(): + if opts.all: + qualified_name = '%s.%s' % (owner, object_name) + else: + qualified_name = object_name + if opts.long: + result.append('%s\t%s\t%s/%s' % (status, last_ddl_time, object_type, qualified_name)) + else: + result.append('%s/%s' % (object_type, qualified_name)) + self.stdout.write('\n'.join(result) + '\n') + + 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." + _test() diff -r 4680d0629b82 -r d504e9760dad sqlpython/sqlpython.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sqlpython/sqlpython.py Mon Sep 29 13:27:41 2008 -0400 @@ -0,0 +1,203 @@ +# +# SqlPython V1.5.0 +# Author: Luca.Canali@cern.ch, Apr 2006 +# Rev 29-May-08 +# +# A python module to reproduce Oracle's command line 'sqlplus-like' within python +# Intended to allow easy customizations and extentions +# Best used with the companion modules sqlpyPlus and mysqlpy +# See also http://twiki.cern.ch/twiki/bin/view/PSSGroup/SqlPython + +import cmd2,getpass,binascii,cx_Oracle,re,os +import pexpecter, sqlpyPlus +__version__ = '1.5.0' + # complication! separate sessions -> + # separate transactions !!!!! + # also: timeouts, other session failures + +class sqlpython(cmd2.Cmd): + '''A python module to reproduce Oracle's command line with focus on customization and extention''' + + def __init__(self): + cmd2.Cmd.__init__(self) + self.prompt = 'SQL.No_Connection> ' + self.maxfetch = 1000 + self.failoverSessions = [] + self.terminator = ';' + self.timeout = 30 + self.commit_on_exit = True + + connection_modes = {re.compile(' AS SYSDBA', re.IGNORECASE): cx_Oracle.SYSDBA, + re.compile(' AS SYSOPER', re.IGNORECASE): cx_Oracle.SYSOPER} + def do_connect(self, arg): + '''Opens the DB connection''' + modeval = 0 + for modere, modevalue in self.connection_modes.items(): + if modere.search(arg): + arg = modere.sub('', arg) + modeval = modevalue + try: + orauser, oraserv = arg.split('@') + except ValueError: + try: + oraserv = os.environ['ORACLE_SID'] + except KeyError: + print 'instance not specified and environment variable ORACLE_SID not set' + return + orauser = arg + sid = oraserv + try: + host, sid = oraserv.split('/') + try: + host, port = host.split(':') + port = int(port) + except ValueError: + port = 1521 + oraserv = cx_Oracle.makedsn(host, port, sid) + except ValueError: + pass + try: + orauser, orapass = orauser.split('/') + except ValueError: + orapass = getpass.getpass('Password: ') + if orauser.upper() == 'SYS' and not modeval: + print 'Privilege not specified for SYS, assuming SYSOPER' + modeval = cx_Oracle.SYSOPER + try: + self.orcl = cx_Oracle.connect(orauser,orapass,oraserv,modeval) + self.curs = self.orcl.cursor() + self.prompt = '%s@%s> ' % (orauser, sid) + self.failoverSessions = [f for f in [fbs(arg) for fbs in pexpecter.available] if f.available] + except Exception, e: + print e + + + def emptyline(self): + pass + + def fail(self, arg, do_everywhere=False): + if self.failover: + success, result = False, '' + for fbs in self.failoverSessions: + success, result = fbs.attempt(arg) + if success: + print result + if not do_everywhere: + return True + print result + return False + + def designated_session(self, arg, sesstype): + for fbs in self.failoverSessions: + if fbs.valid and fbs.__class__ == sesstype: + success, result = fbs.attempt(arg) + print result + return + print 'Valid %s not found' % (sesstype.__name__) + + def do_terminators(self, arg): + """; standard Oracle format +\\c CSV (with headings) +\\C CSV (no headings) +\\g list +\\G aligned list +\\h HTML table +\\i INSERT statements +\\s CSV (with headings) +\\S CSV (no headings) +\\t transposed +\\x XML +\\p plot, with markers +\\P plot, continuous lines""" + print self.do_terminators.__doc__ + + terminatorSearchString = '|'.join('\\' + d.split()[0] for d in do_terminators.__doc__.splitlines()) + + def do_yasql(self, arg): + '''Sends a command to a YASQL session (http://sourceforge.net/projects/yasql/)''' + self.designated_session(arg, pexpecter.YASQLSession) + do_y = do_yasql + def do_sqlplus(self, arg): + '''Sends a command to a SQL*Plus session''' + self.designated_session(arg, pexpecter.SqlPlusSession) + do_sqlp = do_sqlplus + def do_senora(self, arg): + '''Sends a command to a Senora session (http://senora.sourceforge.net/)''' + self.designated_session(arg, pexpecter.SenoraSession) + do_sen = do_senora + + def default(self, arg, do_everywhere = False): + statement = self.parsed(arg) + self.query = statement.unterminated + try: + self.varsUsed = sqlpyPlus.findBinds(self.query, self.binds, givenBindVars={}) + self.curs.execute(self.query, self.varsUsed) + print '\nExecuted%s\n' % ((self.curs.rowcount > 0) and ' (%d rows)' % self.curs.rowcount or '') + if do_everywhere: + self.fail(arg, do_everywhere = True ) + except Exception, e: + result = self.fail(arg) + if not result: + print str(e) + + def do_commit(self, arg): + self.default('commit %s;' % (arg), do_everywhere=True) + def do_rollback(self, arg): + self.default('rollback %s;' % (arg), do_everywhere=True) + def do_quit(self, arg): + if self.commit_on_exit and hasattr(self, 'curs'): + self.default('commit;') + cmd2.Cmd.do_quit() + do_exit = do_quit + do_q = do_quit + +def pmatrix(rows,desc,maxlen=30): + '''prints a matrix, used by sqlpython to print queries' result sets''' + names = [] + maxen = [] + toprint = [] + for d in desc: + n = d[0] + names.append(n) # list col names + maxen.append(len(n)) # col length + rcols = range(len(desc)) + rrows = range(len(rows)) + for i in rrows: # loops for all rows + rowsi = map(str, rows[i]) # current row to process + split = [] # service var is row split is needed + mustsplit = 0 # flag + for j in rcols: + if str(desc[j][1]) == "": # handles RAW columns + rowsi[j] = binascii.b2a_hex(rowsi[j]) + maxen[j] = max(maxen[j], len(rowsi[j])) # computes max field length + if maxen[j] <= maxlen: + split.append('') + else: # split the line is 2 because field is too long + mustsplit = 1 + maxen[j] = maxlen + split.append(rowsi[j][maxlen-1:2*maxlen-1]) + rowsi[j] = rowsi[j][0:maxlen-1] # this implem. truncates after maxlen*2 + toprint.append(rowsi) # 'toprint' is a printable copy of rows + if mustsplit != 0: + toprint.append(split) + sepcols = [] + for i in rcols: + maxcol = maxen[i] + name = names[i] + sepcols.append("-" * maxcol) # formats column names (header) + names[i] = name + (" " * (maxcol-len(name))) # formats separ line with -- + rrows2 = range(len(toprint)) + for j in rrows2: + val = toprint[j][i] + if str(desc[i][1]) == "": # right align numbers + toprint[j][i] = (" " * (maxcol-len(val))) + val + else: + toprint[j][i] = val + (" " * (maxcol-len(val))) + for j in rrows2: + toprint[j] = ' '.join(toprint[j]) + names = ' '.join(names) + sepcols = ' '.join(sepcols) + toprint.insert(0, sepcols) + toprint.insert(0, names) + return '\n'.join(toprint) +