# HG changeset patch
# User catherine@dellzilla
# Date 1226950013 18000
# Node ID c5398d87498e0290c5a1304d88b5c9f9b4b8ba55
# Parent 4a639619814aecd4a957880f3e7c5fa32e498b2a
cat bug
diff -r 4a639619814a -r c5398d87498e README.txt
--- 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
-
diff -r 4a639619814a -r c5398d87498e __init__.py
--- 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
diff -r 4a639619814a -r c5398d87498e completion.py
--- 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
diff -r 4a639619814a -r c5398d87498e mysqlpy.py
--- 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()
diff -r 4a639619814a -r c5398d87498e pexpecter.py
--- 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]
diff -r 4a639619814a -r c5398d87498e setup.py
diff -r 4a639619814a -r c5398d87498e sqlpyPlus.py
--- 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 ' %
- (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' %
- (self.tblname, self.output_row_as_xml(row), self.tblname)
- for row in self.rows]
- return '\n'.join(result)
-
- html_template = """
-
- Table Name
-
-
-
-
- |
- Column Name
- |
-
-
- |
- Value
- |
-
-
-
-"""
- def output_as_html_table(self):
- result = ''.join('%s | ' % c for c in self.colnames)
- result = [' \n %s\n
' % result]
- for row in self.rows:
- result.append(' \n %s\n
' %
- (''.join('%s | ' %
- self.str_or_empty(itm)
- for itm in row)))
- result = '''''' % (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]) == "": # 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()
diff -r 4a639619814a -r c5398d87498e sqlpython.py
--- 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]) == "": # handles RAW columns
- rowsi[j] = binascii.b2a_hex(rowsi[j])
- maxen[j] = max(maxen[j], len(rowsi[j])) # computes max field length
- if maxen[j] <= maxlen:
- split.append('')
- else: # split the line is 2 because field is too long
- mustsplit = 1
- maxen[j] = maxlen
- split.append(rowsi[j][maxlen-1:2*maxlen-1])
- rowsi[j] = rowsi[j][0:maxlen-1] # this implem. truncates after maxlen*2
- toprint.append(rowsi) # 'toprint' is a printable copy of rows
- if mustsplit != 0:
- toprint.append(split)
- sepcols = []
- for i in rcols:
- maxcol = maxen[i]
- name = names[i]
- sepcols.append("-" * maxcol) # formats column names (header)
- names[i] = name + (" " * (maxcol-len(name))) # formats separ line with --
- rrows2 = range(len(toprint))
- for j in rrows2:
- val = toprint[j][i]
- if str(desc[i][1]) == "": # right align numbers
- toprint[j][i] = (" " * (maxcol-len(val))) + val
- else:
- toprint[j][i] = val + (" " * (maxcol-len(val)))
- for j in rrows2:
- toprint[j] = ' '.join(toprint[j])
- names = ' '.join(names)
- sepcols = ' '.join(sepcols)
- toprint.insert(0, sepcols)
- toprint.insert(0, names)
- return '\n'.join(toprint)
-
diff -r 4a639619814a -r c5398d87498e sqlpython/README.txt
--- /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
+
diff -r 4a639619814a -r c5398d87498e sqlpython/__init__.py
--- /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
diff -r 4a639619814a -r c5398d87498e sqlpython/completion.py
--- /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
diff -r 4a639619814a -r c5398d87498e sqlpython/editplot.bash
--- /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
diff -r 4a639619814a -r c5398d87498e sqlpython/exampleSession.txt
--- /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
+
+
+
+
+ play
+
+
+
+
+
+
+
+
+ |
+ Twelfth Night
+ |
+ Shakespeare
+ |
+
+ |
+ The Tempest
+ |
+ Shakespeare
+ |
+
+
+
+
+
+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
+
+
+
+
+
+ Twelfth Night
+ Shakespeare
+
+
+ The Tempest
+ Shakespeare
+
+
+
+
+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
diff -r 4a639619814a -r c5398d87498e sqlpython/mysqlpy.py
--- /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
diff -r 4a639619814a -r c5398d87498e sqlpython/output_templates.py
--- /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("""
+
+ <${tblname}_resultset>{% for row in rows %}
+ <$tblname>{% for (colname, itm) in zip(colnames, row) %}
+ <${colname.lower()}>$itm{% end %}
+ {% end %}
+
+"""),
+
+'\\h': genshi.template.MarkupTemplate("""
+
+
+
+ Table Name
+
+
+
+
+
+ |
+ Column Name
+ |
+
+
+ |
+ Value
+ |
+
+
+
+"""),
+
+'\\g': genshi.template.NewTextTemplate("""
+{% for (rowNum, row) in enumerate(rows) %}
+**** Row: ${rowNum + 1}
+{% for (colname, itm) in zip(colnames, row) %}$colname: $itm
+{% end %}{% end %}"""),
+
+'\\G': genshi.template.NewTextTemplate("""
+{% for (rowNum, row) in enumerate(rows) %}
+**** Row: ${rowNum + 1}
+{% for (colname, itm) in zip(colnames, row) %}${colname.ljust(colnamelen)}: $itm
+{% end %}{% end %}"""),
+
+'\\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']
diff -r 4a639619814a -r c5398d87498e sqlpython/pexpecter.py
--- /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]
diff -r 4a639619814a -r c5398d87498e sqlpython/plothandler.py
--- /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
diff -r 4a639619814a -r c5398d87498e sqlpython/setup_test_db.sql
--- /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');
+
+
diff -r 4a639619814a -r c5398d87498e sqlpython/sqlpyPlus.py
--- /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]) == "": # 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()
diff -r 4a639619814a -r c5398d87498e sqlpython/sqlpython.py
--- /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]) == "": # handles RAW columns
+ rowsi[j] = binascii.b2a_hex(rowsi[j])
+ maxen[j] = max(maxen[j], len(rowsi[j])) # computes max field length
+ if maxen[j] <= maxlen:
+ split.append('')
+ else: # split the line is 2 because field is too long
+ mustsplit = 1
+ maxen[j] = maxlen
+ split.append(rowsi[j][maxlen-1:2*maxlen-1])
+ rowsi[j] = rowsi[j][0:maxlen-1] # this implem. truncates after maxlen*2
+ toprint.append(rowsi) # 'toprint' is a printable copy of rows
+ if mustsplit != 0:
+ toprint.append(split)
+ sepcols = []
+ for i in rcols:
+ maxcol = maxen[i]
+ name = names[i]
+ sepcols.append("-" * maxcol) # formats column names (header)
+ names[i] = name + (" " * (maxcol-len(name))) # formats separ line with --
+ rrows2 = range(len(toprint))
+ for j in rrows2:
+ val = toprint[j][i]
+ if str(desc[i][1]) == "": # right align numbers
+ toprint[j][i] = (" " * (maxcol-len(val))) + val
+ else:
+ toprint[j][i] = val + (" " * (maxcol-len(val)))
+ for j in rrows2:
+ toprint[j] = ' '.join(toprint[j])
+ names = ' '.join(names)
+ sepcols = ' '.join(sepcols)
+ toprint.insert(0, sepcols)
+ toprint.insert(0, names)
+ return '\n'.join(toprint)
+
diff -r 4a639619814a -r c5398d87498e sqlpython/test_sqlpyPlus.py
--- /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
diff -r 4a639619814a -r c5398d87498e sqlpython/test_sqlpyPlus.txt
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sqlpython/test_sqlpyPlus.txt Mon Nov 17 14:26:53 2008 -0500
@@ -0,0 +1,92 @@
+testdata@eqdev> CREATE TABLE species (
+> ID NUMBER(8,0) CONSTRAINT xnn1_species NOT NULL
+> CONSTRAINT xpk_species PRIMARY KEY,
+> NAME VARCHAR2(12) CONSTRAINT xnn2_species NOT NULL
+> CONSTRAINT xuk1_species UNIQUE );
+
+Executed
+
+testdata@eqdev> :id = 0
+testdata@eqdev> :name = 'turtle'
+testdata@eqdev> INSERT INTO species VALUES (:id, :name);
+
+Executed (1 rows)
+
+testdata@eqdev> :id = 1
+testdata@eqdev> :name = 'python'
+testdata@eqdev> INSERT INTO species VALUES (:id, :name);
+
+Executed (1 rows)
+
+testdata@eqdev> :id = 2
+testdata@eqdev> :name = 'parrot'
+testdata@eqdev> INSERT INTO species VALUES (:id, :name);
+
+Executed (1 rows)
+
+testdata@eqdev> commit;
+
+Executed
+
+testdata@eqdev> select * from species;
+
+ID NAME
+-- ------
+ 0 turtle
+ 1 python
+ 2 parrot
+
+3 rows selected.
+
+testdata@eqdev> select *
+> from species;
+
+ID NAME
+-- ------
+ 0 turtle
+ 1 python
+ 2 parrot
+
+3 rows selected.
+
+testdata@eqdev> select * from species\h
+
+
+
+
+ species
+
+
+
+
+
+
+
+
+ |
+ 0
+ |
+ turtle
+ |
+
+ |
+ 1
+ |
+ python
+ |
+
+
+
+
+
+3 rows selected.
+
+testdata@eqdev> DROP TABLE species;
+
+Executed
+
+testdata@eqdev>