--- a/README.txt	Mon Nov 17 13:14:29 2008 -0500
+++ /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
-

--- a/__init__.py	Mon Nov 17 13:14:29 2008 -0500
+++ /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

--- a/completion.py	Mon Nov 17 13:14:29 2008 -0500
+++ /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

--- a/mysqlpy.py	Mon Nov 17 13:14:29 2008 -0500
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,189 +0,0 @@
-#!/usr/bin/python
-# MySqlPy V1.4.6
-# 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.6 - 'sqlplus in python'
-Author: Luca.Canali@cern.ch
-Rev: 1.4.8, 29-May-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 = '''
-          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 = '''
- 	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 = '''
-        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.do_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.do_select(self.query_top9i)
-    
-    def do_top(self,args): 
-        '''Runs query_ractop defined above, to display active sessions in Oracle 10g (and RAC)'''
-        self.do_select(self.query_ractop)
-
-    def do_longops(self,args):
-        '''Runs query_longops defined above, to display long running operations (full scans, etc)'''
-        self.do_select(self.query_longops)
-
-    def do_load(self,args):
-        '''Runs query_load10g defined above, to display OS load on cluster nodes (10gRAC)'''
-        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()        

--- a/pexpecter.py	Mon Nov 17 13:14:29 2008 -0500
+++ /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]

--- a/sqlpyPlus.py	Mon Nov 17 13:14:29 2008 -0500
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,1025 +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
-"""
-# note in cmd.cmd about supporting emacs commands?
-
-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       
-"""
-}
-
-import sys, os, re, sqlpython, cx_Oracle, pyparsing, re, completion
-from cmd2 import Cmd, make_option, options, Statekeeper
-
-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)
-
-    def output_row_as_xml(self, row):
-        result = ['  <%s>\n    %s\n  </%s>' %
-                  (colname.lower(), self.str_or_empty(itm), colname.lower()) 
-                  for (itm, colname) in zip(row, self.colnames)]
-        return '\n'.join(result)        
-    def output_as_xml(self):
-        result = ['<%s>\n%s\n</%s>' %
-                  (self.tblname, self.output_row_as_xml(row), self.tblname)
-                  for row in self.rows]
-        return '\n'.join(result)
-
-    html_template = """<html>
-  <head>
-    <title py:content="tblname">Table Name</title>
-  </head>
-  <body>
-    <table py:attr="{'id':tblname}">
-      <tr>
-        <th py:for="colname in colnames">
-          <span py:replace="colname">Column Name</span>
-        </th>
-      </tr>
-      <tr py:for="row in rows">
-        <td py:for="itm in row">
-          <span py:replace="str_or_empty(itm)">Value</span>
-        </td>
-      </tr>
-    </table>
-  </body>
-</html>"""
-    def output_as_html_table(self):
-        result = ''.join('<th>%s</th>' % c for c in self.colnames)
-        result = ['  <tr>\n    %s\n  </tr>' % result]
-        for row in self.rows:
-            result.append('  <tr>\n    %s\n  </tr>' %
-                          (''.join('<td>%s</td>' %
-                                   self.str_or_empty(itm)
-                                   for itm in row)))                
-        result = '''<table id="%s">
-%s
-</table>''' % (self.tblname, '\n'.join(result))
-        return result
-
-    #TODO: use serious templating to make these user-tweakable
-
-    def output_as_markup(self, genshi_template):
-        return None
-        #self.tblname, self.colnames, self.rows
-            
-    def output_as_list(self, align):
-        result = []
-        colnamelen = max(len(colname) for colname in self.colnames) + 1        
-        for (idx, row) in enumerate(self.rows):
-            result.append('\n**** Row: %d' % (idx+1))
-            for (itm, colname) in zip(row, self.colnames):
-                if align:
-                    colname = colname.ljust(colnamelen)
-                result.append('%s: %s' % (colname, itm))
-        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 ==  '\\x':
-            result = self.output_as_xml()
-        elif outformat == '\\g':
-            result = self.output_as_list(align=False)
-        elif outformat == '\\G':
-            result = self.output_as_list(align=True)            
-        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 == '\\h':
-            result = self.output_as_html_table()
-        elif outformat == '\\t':
-            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]) == "<type 'cx_Oracle.BINARY'>":  # 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)            
-        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')
-    terminatorPattern = (pyparsing.oneOf('; \\s \\S \\c \\C \\t \\x \\h')    
-                        ^ pyparsing.Literal('\n/') ^ \
-                        (pyparsing.Literal('\nEOF') + pyparsing.stringEnd)) \
-                        ('terminator') + \
-                        pyparsing.Optional(rowlimitPattern)
-    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        
-        object_type, owner, object_name = self.resolve(arg.upper())
-        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
-        #todo: resolve not finding cwm$ table
-
-    def do_resolve(self, arg):
-        self.stdout.write(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'''
-        targets = arg.split()
-        for target in targets:
-            self.do_select('* from %s' % target)
-
-    @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()

--- a/sqlpython.py	Mon Nov 17 13:14:29 2008 -0500
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,201 +0,0 @@
-#
-# SqlPython V1.4.7
-# 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
-    
-    # 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"""
-        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]) == "<type 'cx_Oracle.BINARY'>":  # 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]) == "<type 'cx_Oracle.NUMBER'>":  # 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)
-

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/sqlpython/README.txt	Mon Nov 17 14:26:53 2008 -0500
@@ -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
+

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/sqlpython/__init__.py	Mon Nov 17 14:26:53 2008 -0500
@@ -0,0 +1,3 @@
+import mysqlpy
+__all__ = ["sqlpython", "sqlpyPlus", "pexpecter", "mysqlpy", "output_templates", "plothandler", ]
+__version__ = '1.5.0'
\ No newline at end of file

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/sqlpython/completion.py	Mon Nov 17 14:26:53 2008 -0500
@@ -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

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/sqlpython/editplot.bash	Mon Nov 17 14:26:53 2008 -0500
@@ -0,0 +1,1 @@
+ipython -pylab -c "import sqlpython.plothandler; sqlpython.plothandler.Plot().unshelve()"
\ No newline at end of file

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/sqlpython/exampleSession.txt	Mon Nov 17 14:26:53 2008 -0500
@@ -0,0 +1,270 @@
+SQL.No_Connection> connect testschema/testschema@orcl
+testschema@orcl> CREATE TABLE play (
+>   title   VARCHAR2(40) CONSTRAINT xpk_play PRIMARY KEY,
+>   author  VARCHAR2(40));
+
+Executed
+
+testschema@orcl> INSERT INTO play VALUES ('Twelfth Night', 'Shakespeare');
+
+Executed (1 rows)
+
+testschema@orcl> INSERT INTO play VALUES ('The Tempest', 'Shakespeare');
+
+Executed (1 rows)
+
+testschema@orcl> INSERT INTO play VALUES ('Agamemnon', 'Aeschylus');
+
+Executed (1 rows)
+
+testschema@orcl> commit;
+
+Executed
+
+testschema@orcl> select
+> *
+> from
+> play;
+
+TITLE         AUTHOR
+------------- -----------
+Twelfth Night Shakespeare
+The Tempest   Shakespeare
+Agamemnon     Aeschylus
+
+3 rows selected.
+
+testschema@orcl> ls
+
+NAME          
+--------------
+INDEX/XPK_PLAY
+TABLE/PLAY    
+
+2 rows selected.
+
+testschema@orcl> ls --all
+
+NAME
+--------------
+INDEX/XPK_PLAY
+TABLE/PLAY
+testschema@orcl> ls table
+
+NAME      
+----------
+TABLE/PLAY
+
+1 row selected.
+
+testschema@orcl> desc play
+TABLE TESTSCHEMA.PLAY
+
+COLUMN_NAME Null?    DATA_TYPE
+----------- -------- ------------
+TITLE       NOT NULL VARCHAR2(40)
+AUTHOR      NULL     VARCHAR2(40)
+
+2 rows selected.
+
+testschema@orcl> COMMENT ON COLUMN play.author IS 'Primary author (if multiple)';
+
+Executed
+
+testschema@orcl> COMMENT ON TABLE play IS 'I like plays.';
+
+Executed
+
+testschema@orcl> comments play
+TABLE TESTSCHEMA.PLAY: I like plays.
+
+COLUMN_NAME COMMENTS
+----------- ----------------------------
+TITLE       None
+AUTHOR      Primary author (if multiple)
+
+2 rows selected.
+
+testschema@orcl> cat play
+
+TITLE         AUTHOR
+------------- -----------
+Twelfth Night Shakespeare
+The Tempest   Shakespeare
+Agamemnon     Aeschylus
+
+3 rows selected.
+
+testschema@orcl> help terminators
+;    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
+\l   line plot, with markers
+\L   scatter plot (no lines)
+\b   bar graph
+\p   pie chart
+testschema@orcl> select * from play where author='Shakespeare'\c
+
+TITLE,AUTHOR
+"Twelfth Night","Shakespeare"
+"The Tempest","Shakespeare"
+
+2 rows selected.
+
+testschema@orcl> select * from play where author='Shakespeare'\g
+
+
+
+**** Row: 1
+TITLE: Twelfth Night
+AUTHOR: Shakespeare
+
+**** Row: 2
+TITLE: The Tempest
+AUTHOR: Shakespeare
+
+
+2 rows selected.
+
+testschema@orcl> select * from play where author='Shakespeare'\h
+
+<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
+<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
+  <head>
+    <title>play</title>
+    <meta http-equiv="content-type" content="text/html;charset=utf-8"/>
+  </head>
+  <body>
+    <table id="play" summary="Result set from query on table play">
+      <tr>
+        <th id="header_title">
+          title
+        </th><th id="header_author">
+          author
+        </th>
+      </tr>
+      <tr>
+        <td headers="header_title">
+          Twelfth Night
+        </td><td headers="header_author">
+          Shakespeare
+        </td>
+      </tr><tr>
+        <td headers="header_title">
+          The Tempest
+        </td><td headers="header_author">
+          Shakespeare
+        </td>
+      </tr>
+    </table>
+  </body>
+</html>
+
+2 rows selected.
+
+testschema@orcl> select * from play\i
+
+
+INSERT INTO play (TITLE, AUTHOR) VALUES ('Twelfth Night', 'Shakespeare');
+INSERT INTO play (TITLE, AUTHOR) VALUES ('The Tempest', 'Shakespeare');
+INSERT INTO play (TITLE, AUTHOR) VALUES ('Agamemnon', 'Aeschylus');
+
+3 rows selected.
+
+testschema@orcl> select * from play where author='Shakespeare'\t
+
+
+COLUMN NAME ROW N.1       ROW N.2
+----------- ------------- -----------
+TITLE       Twelfth Night The Tempest
+AUTHOR      Shakespeare   Shakespeare
+
+2 rows selected.
+
+testschema@orcl> select * from play where author='Shakespeare'\x
+
+
+<xml>
+  <play_resultset>
+    <play>
+      <title>Twelfth Night</title>
+      <author>Shakespeare</author>
+    </play>
+    <play>
+      <title>The Tempest</title>
+      <author>Shakespeare</author>
+    </play>
+  </play_resultset>
+</xml>
+
+2 rows selected.    
+
+testschema@orcl> set
+autobind: False
+commit_on_exit: True
+echo: False
+maxfetch: 1000
+maxtselctrows: 10
+timeout: 30
+testschema@orcl> print
+testschema@orcl> set autobind on
+autobind - was: False
+now: True
+testschema@orcl> select * from play where author like 'A%';
+
+TITLE     AUTHOR   
+--------- ---------
+Agamemnon Aeschylus
+
+1 row selected.
+
+testschema@orcl> print
+:1 = Agamemnon
+:2 = Aeschylus
+:title = Agamemnon
+:author = Aeschylus
+testschema@orcl> select * from play where title = :1;
+
+TITLE     AUTHOR   
+--------- ---------
+Agamemnon Aeschylus
+
+1 row selected.
+
+testschema@orcl> select * from play where author = :author;
+
+TITLE     AUTHOR   
+--------- ---------
+Agamemnon Aeschylus
+
+1 row selected.
+
+testschema@orcl> help grep
+grep PATTERN TABLE - search for term in any of TABLE's fields
+Usage: grep [options] arg
+
+Options:
+  -h, --help         show this help message and exit
+  -i, --ignore-case  Case-insensitive search
+
+testschema@orcl> grep -i EM play
+play
+
+TITLE       AUTHOR
+----------- -----------
+The Tempest Shakespeare
+Agamemnon   Aeschylus
+
+2 rows selected.
+
+testschema@orcl> drop table play;
+
+Executed

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/sqlpython/mysqlpy.py	Mon Nov 17 14:26:53 2008 -0500
@@ -0,0 +1,203 @@
+#!/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, optparse, unittest
+
+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()
+
+class TestCase(Cmd2TestCase):
+    CmdApp = mysqlpy
+    transcriptFileName = 'exampleSession.txt'
+
+if __name__ == '__main__':
+    parser = optparse.OptionParser()
+    parser.add_option('-t', '--test', dest='unittests', action='store_true', default=False, help='Run unit test suite')
+    (callopts, callargs) = parser.parse_args()
+    if callopts.unittests:
+        sys.argv = [sys.argv[0]]  # the --test argument upsets unittest.main()
+        unittest.main()
+    else:
+        run()
\ No newline at end of file

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/sqlpython/output_templates.py	Mon Nov 17 14:26:53 2008 -0500
@@ -0,0 +1,67 @@
+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("""
+<xml>
+  <${tblname}_resultset>{% for row in rows %}
+    <$tblname>{% for (colname, itm) in zip(colnames, row) %}
+      <${colname.lower()}>$itm</${colname.lower()}>{% end %}
+    </$tblname>{% end %}
+  </${tblname}_resultset>
+</xml>"""),
+
+'\\h': genshi.template.MarkupTemplate("""
+<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
+<html xmlns:py="http://genshi.edgewall.org/" xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
+  <head>
+    <title py:content="tblname">Table Name</title>
+    <meta http-equiv="content-type" content="text/html;charset=utf-8" />
+  </head>
+  <body>
+    <table py:attrs="{'id':tblname, 
+     'summary':'Result set from query on table ' + tblname}">
+      <tr>
+        <th py:for="colname in colnames"
+         py:attrs="{'id':'header_' + colname.lower()}">
+          <span py:replace="colname.lower()">Column Name</span>
+        </th>
+      </tr>
+      <tr py:for="row in rows">
+        <td py:for="(colname, itm) in zip(colnames, row)" py:attrs="{'headers':'header_' + colname.lower()}">
+          <span py:replace="str(itm)">Value</span>
+        </td>
+      </tr>
+    </table>
+  </body>
+</html>"""),
+
+'\\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 %}"""),
+
+'\\i': genshi.template.NewTextTemplate("""{% for (rowNum, row) in enumerate(rows) %}
+INSERT INTO $tblname (${', '.join(colnames)}) VALUES (${', '.join(f % r for (r,f) in zip(row, formatters))});{% end %}"""),
+
+'\\c': genshi.template.NewTextTemplate("""
+${','.join(colnames)}{% for row in rows %}
+${','.join('"%s"' % val for val in row)}{% end %}"""),
+
+'\\C': genshi.template.NewTextTemplate("""
+{% for row in rows %}${','.join('"%s"' % val for val in row)}{% end %}""")
+
+}
+
+output_templates['\\s'] = output_templates['\\c']
+output_templates['\\S'] = output_templates['\\C']

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/sqlpython/pexpecter.py	Mon Nov 17 14:26:53 2008 -0500
@@ -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]

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/sqlpython/plothandler.py	Mon Nov 17 14:26:53 2008 -0500
@@ -0,0 +1,89 @@
+import shelve, pickle, cx_Oracle, datetime, sys, itertools
+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, outformat):
+            self.outformat = outformat
+            self.title = sqlSession.tblname
+            self.xlabel = sqlSession.curs.description[0][0]
+            self.datatypes = [d[1] for d in sqlSession.curs.description]
+            plottableSeries = [dt in self.plottable_types for dt in self.datatypes]
+            if plottableSeries.count(True) == 0:
+                raise ValueError, 'At least one quantitative column needed to plot.'
+            elif len(plottableSeries) == 1: # only one column, but it is plottable
+                idx = plottableSeries.index(True)
+                self.yserieslists = [[row[0] for row in sqlSession.rows]]
+                self.legends = [sqlSession.curs.description[0][0]]                
+                self.xvalues = range(len(sqlSession.rows))
+                self.xticks = [row[0] for row in sqlSession.rows]
+            else:
+                for (colNum, plottable) in enumerate(plottableSeries):
+                    if colNum > 0 and plottable:
+                        yseries = [row[colNum] for row in sqlSession.rows]
+                        self.yserieslists.append(yseries)
+                        self.legends.append(sqlSession.curs.description[colNum][0])
+                if plottableSeries[0]:
+                    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 outformat'.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 bar(self):
+            barEdges = pylab.arange(len(self.xvalues))
+            width = 0.6 / len(self.yserieslists)
+            colorcycler = itertools.cycle('rgb')
+            for (offset, yseries) in enumerate(self.yserieslists):
+                self.yplots.append(pylab.bar(barEdges + (offset*width), yseries, width=width, color=colorcycler.next()))
+            pylab.xticks(barEdges + 0.3, self.xticks or self.xvalues)            
+        def line(self, markers):
+            for yseries in self.yserieslists:
+                self.yplots.append(pylab.plot(self.xvalues, yseries, markers))
+            if self.xticks:
+                pylab.xticks(self.xvalues, self.xticks)
+        def pie(self):
+            self.yplots.append(pylab.pie(self.yserieslists[0], labels=self.xticks or self.xvalues))
+            self.legends = [self.legends[0]]
+        def draw(self):
+            if not self.yserieslists:
+                print 'At least one quantitative column needed to plot.'
+                return None
+            self.yplots = []
+            if self.outformat == '\\l':
+                self.line('-o')
+            elif self.outformat == '\\L':
+                self.line('o')
+            elif self.outformat == '\\p':
+                self.pie()
+            else:
+                self.bar()
+            pylab.xlabel(self.xlabel)
+            pylab.title(self.title)
+            pylab.legend([p[0] for p in self.yplots], self.legends, shadow=True)
+            pylab.show()
+            print 'You can edit this plot from the command prompt (outside sqlpython) by running'
+            print "ipython -pylab -c 'import sqlpython.plothandler; sqlpython.plothandler.Plot().unshelve()'"
+            print "See matplotlib documentation for editing instructions: http://matplotlib.sourceforge.net/"
+            # there's got to be a way to install a shell script like that through setuptools... but how?
+            
+except ImportError:
+    class Plot(object):
+        def __init__(self, *args, **kwargs):
+            raise ImportError, 'Must install python-matplotlib and pytyon-numpy to draw plots'
\ No newline at end of file

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/sqlpython/setup_test_db.sql	Mon Nov 17 14:26:53 2008 -0500
@@ -0,0 +1,14 @@
+CREATE USER testdata IDENTIFIED BY testdata;
+GRANT connect, resource TO testdata;
+CONNECT testdata/testdata@orcl
+DROP TABLE species;
+CREATE TABLE species (
+    id   NUMBER(6,0)  CONSTRAINT xpk_species PRIMARY KEY
+                      CONSTRAINT xnn1_species NOT NULL,
+    name VARCHAR2(40) CONSTRAINT xnn2_species NOT NULL );
+
+INSERT INTO species VALUES (0, 'turtle');
+INSERT INTO species VALUES (1, 'python');
+INSERT INTO species VALUES (2, 'parrot');
+
+

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

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/sqlpython/sqlpython.py	Mon Nov 17 14:26:53 2008 -0500
@@ -0,0 +1,163 @@
+#
+# 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 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.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)
+        except Exception, e:
+            print e
+            
+    
+    def emptyline(self):
+        pass
+                           
+    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
+\\l   line plot, with markers
+\\L   scatter plot (no lines)
+\\b   bar graph
+\\p   pie chart"""
+        print self.do_terminators.__doc__
+    
+    terminatorSearchString = '|'.join('\\' + d.split()[0] for d in do_terminators.__doc__.splitlines())
+        
+    def default(self, arg):
+        statement = self.parsed(arg)
+        self.query = statement.unterminated
+        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 '')
+            
+    def do_commit(self, arg):
+        self.default('commit %s;' % (arg))
+    def do_rollback(self, arg):
+        self.default('rollback %s;' % (arg))        
+    def do_quit(self, arg):
+        if self.commit_on_exit and hasattr(self, 'curs'):
+            self.default('commit;')
+        return cmd2.Cmd.do_quit(self, None)
+    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]) == "<type 'cx_Oracle.BINARY'>":  # 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]) == "<type 'cx_Oracle.NUMBER'>":  # 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)
+

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/sqlpython/test_sqlpyPlus.py	Mon Nov 17 14:26:53 2008 -0500
@@ -0,0 +1,77 @@
+import unittest, sys, tempfile, re, os.path, pyparsing
+from sqlpyPlus import *
+
+class Borg(object):
+    # from Python Cookbook, 2nd Ed., recipe 6.16
+    _shared_state = {}
+    def __new__(cls, *a, **k):
+        obj = object.__new__(cls, *a, **k)
+        obj.__dict__ = cls._shared_state
+        return obj
+    
+class OutputTrap(Borg):
+    old_stdout = sys.stdout
+    def __init__(self):
+        self.trap = tempfile.TemporaryFile()
+        sys.stdout = self.trap
+    def dump(self):
+        self.trap.seek(0)
+        result = self.trap.read()
+        self.trap.close()
+        self.trap = tempfile.TemporaryFile()
+        sys.stdout = self.trap
+        return result
+    def teardown(self):
+        sys.stdout = self.old_stdout
+
+class TestSqlPyPlus(unittest.TestCase):
+    transcriptReader = re.compile('testdata@eqdev> (.*?)\n(.*?)(?=testdata@eqdev>)', re.DOTALL)
+    transcriptFileName = 'test_sqlpyPlus.txt'
+    def setUp(self):
+        self.outputTrap = OutputTrap()
+        transcriptFile = open(self.transcriptFileName)
+        self.transcript = transcriptFile.read()
+        transcriptFile.close()
+        self.directives = self.transcriptReader.finditer(self.transcript)        
+        self.testsession = sqlpyPlus()
+        self.testsession.onecmd('connect ' + connectString)
+        self.transcriptReader = re.compile(
+            '%s(.*?)\n\n(.*?)(?=%s)' % (self.testsession.prompt, self.testsession.prompt), re.DOTALL)
+        self.commandCleaner = '\n%s' % (self.testsession.continuationPrompt)
+    def assertOutput(self, commandtext, expected, lineNum):
+        self.testsession.onecmd(commandtext)
+        result = self.outputTrap.dump()
+        self.assertEqual(expected.strip(), result.strip(), 
+            '\nFile %s, line %d\nCommand was:\n%s\nExpected:\n%s\nGot:\n%s\n' % 
+            (self.transcriptFileName, lineNum, commandtext, expected, result))
+    def testall(self):
+        for directive in self.directives:
+            (command, result) = directive.groups()
+            command = command.replace(self.commandCleaner, '\n')
+            self.assertOutput(command, result, lineNum=self.transcript.count('\n', 0, directive.start()))
+    def tearDown(self):
+        self.outputTrap.teardown()
+
+try:        
+    connectString = sys.argv.pop(1)
+except IndexError:
+    print 'Usage: python %s username/password@oracleSID' % os.path.split(__file__)[-1]
+    sys.exit()
+unittest.main()
+
+
+def transcript(cmdapp, filename='test_sqlpyPlus.txt'):
+    tfile = open(filename)
+    txt = tfile.read()
+    tfile.close()
+    prompt = pyparsing.Suppress(pyparsing.lineStart + cmd.prompt)
+    continuationPrompt = pyparsing.Suppress(pyparsing.lineStart + cmd.continuationPrompt)
+    cmdtxtPattern = (prompt + pyparsing.restOfLine + pyparsing.ZeroOrMore(
+        pyparsing.lineEnd + continuationPrompt + pyparsing.restOfLine))("command")
+    previousStartPoint = 0
+    results = []
+    for onecmd in cmdtxtPattern.scanString(txt):
+        if len(results) > 0:
+            results[-1]['response'] = txt[previousStartPoint:onecmd[1]]
+        results.append({'command': ''.join(onecmd[0].command), 'response': txt[onecmd[2]:]})
+        previousStartPoint = onecmd[2]
\ No newline at end of file