# HG changeset patch # User Catherine Devlin # Date 1238515429 14400 # Node ID f200a222a9360696a2481fe18d7541bc03aa49b9 # Parent ea0b0e1ab6daf9705275b2ffeed590c684eb2ff8 beginning to set up metadata.py diff -r ea0b0e1ab6da -r f200a222a936 setup.py --- a/setup.py Tue Mar 31 09:21:09 2009 -0400 +++ b/setup.py Tue Mar 31 12:03:49 2009 -0400 @@ -9,7 +9,7 @@ Operating System :: OS Independent""".splitlines() setup(name="sqlpython", - version="1.6.3", + version="1.7.0", description="Command-line interface to Oracle", long_description="Customizable alternative to Oracle's SQL*PLUS command-line interface", author="Luca Canali", diff -r ea0b0e1ab6da -r f200a222a936 sqlpython/__init__.py --- a/sqlpython/__init__.py Tue Mar 31 09:21:09 2009 -0400 +++ b/sqlpython/__init__.py Tue Mar 31 12:03:49 2009 -0400 @@ -1,3 +1,4 @@ import mysqlpy -__all__ = ["sqlpython", "sqlpyPlus", "pexpecter", "mysqlpy", "output_templates", "plothandler", ] -__version__ = '1.5.0' \ No newline at end of file +__all__ = ["sqlpython", "sqlpyPlus", "pexpecter", "mysqlpy", \ + "output_templates", "plothandler", "metadata",] +__version__ = '1.7.0' \ No newline at end of file diff -r ea0b0e1ab6da -r f200a222a936 sqlpython/metadata.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sqlpython/metadata.py Tue Mar 31 12:03:49 2009 -0400 @@ -0,0 +1,73 @@ +from collections import defaultdict + +metaqueries = defaultdict(defaultdict) + +metaqueries['desc']['oracle'] = defaultdict(defaultdict) +metaqueries['desc']['oracle']['TABLE']['long'] = """ +SELECT atc.column_id "#", +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, +acc.comments +FROM all_tab_columns atc +JOIN all_col_comments acc ON (acc.owner = atc.owner AND acc.table_name = atc.table_name AND acc.column_name = atc.column_name) +WHERE atc.table_name = :object_name +AND atc.owner = :owner +ORDER BY atc.column_id;""" + +metaqueries['desc']['oracle']['TABLE']['short'] = """ +SELECT atc.column_name, +CASE atc.nullable WHEN 'Y' THEN 'NULL' ELSE 'NOT NULL' END "Null?", +atc.data_type || +CASE atc.data_type WHEN 'DATE' THEN '' +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;""" + +metaqueries['desc']['oracle']['PROCEDURE'] = """ +SELECT 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;""" + +metaqueries['desc']['oracle']['PackageObjects'] = """ +SELECT DISTINCT object_name +FROM all_arguments +WHERE package_name = :package_name +AND owner = :owner""" + +metaqueries['desc']['oracle']['PackageObjArgs'] = """ +SELECT 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;""" diff -r ea0b0e1ab6da -r f200a222a936 sqlpython/sqlpyPlus.py --- a/sqlpython/sqlpyPlus.py Tue Mar 31 09:21:09 2009 -0400 +++ b/sqlpython/sqlpyPlus.py Tue Mar 31 12:03:49 2009 -0400 @@ -26,6 +26,7 @@ import sys, os, re, sqlpython, cx_Oracle, pyparsing, re, completion, datetime, pickle, binascii, subprocess from cmd2 import Cmd, make_option, options, Statekeeper, Cmd2TestCase from output_templates import output_templates +from metadata import metaqueries from plothandler import Plot try: import pylab @@ -36,49 +37,6 @@ # round-trip PL/SQL packages; print/stdout inconsistency; \dt (show triggers); # spell check descQueries = { -'TABLE': { - True: # long description -(""" -SELECT atc.column_id "#", -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, -acc.comments -FROM all_tab_columns atc -JOIN all_col_comments acc ON (acc.owner = atc.owner AND acc.table_name = atc.table_name AND acc.column_name = atc.column_name) -WHERE atc.table_name = :object_name -AND atc.owner = :owner -ORDER BY atc.column_id;""",), - None: # short description -(""" -SELECT atc.column_name, -CASE atc.nullable WHEN 'Y' THEN 'NULL' ELSE 'NOT NULL' END "Null?", -atc.data_type || -CASE atc.data_type WHEN 'DATE' THEN '' -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': (""" SELECT NVL(argument_name, 'Return Value') argument_name, data_type, @@ -146,7 +104,7 @@ \\t """,) } -descQueries['VIEW'] = descQueries['TABLE'] +descQueries['VIEW'] = metaqueries['desc']['oracle']['TABLE']['short'] descQueries['FUNCTION'] = descQueries['PROCEDURE'] queries = { @@ -480,7 +438,7 @@ def postcmd(self, stop, line): """Hook method executed just after a command dispatch is finished.""" - if self.orcl and self.serveroutput: + if (self.rdbms == 'oracle') and self.serveroutput: self.dbms_output() return stop @@ -538,7 +496,7 @@ stop = self.postcmd(stop, line) def _onchange_serveroutput(self, old, new): - if self.orcl: + if (self.rdbms == 'oracle'): if new: self.curs.callproc('dbms_output.enable', []) else: @@ -1035,7 +993,7 @@ if opts.long: self._execute(queries['tabComments'], {'table_name':object_name, 'owner':owner}) self.stdout.write(self.curs.fetchone()[0]) - descQ = descQueries[object_type][opts.long] + descQ = metaqueries['desc'][object_type][self.rdbms][(opts.long and 'long') or 'short'] else: descQ = descQueries[object_type][opts.long] for q in descQ: diff -r ea0b0e1ab6da -r f200a222a936 sqlpython/sqlpython.py --- a/sqlpython/sqlpython.py Tue Mar 31 09:21:09 2009 -0400 +++ b/sqlpython/sqlpython.py Tue Mar 31 12:03:49 2009 -0400 @@ -1,5 +1,5 @@ # -# SqlPython V1.6.3 +# SqlPython V1.7.0 # Author: Luca.Canali@cern.ch, Apr 2006 # Rev 30-Mar-09 # @@ -10,7 +10,7 @@ import cmd2,getpass,binascii,cx_Oracle,re,os import sqlpyPlus, sqlalchemy -__version__ = '1.6.3' +__version__ = '1.7.0' class sqlpython(cmd2.Cmd): '''A python module to reproduce Oracle's command line with focus on customization and extention''' @@ -30,17 +30,21 @@ self.conn = None self.connection_number = None + def make_connection_current(self, connection_number): + self.conn = self.connections[connection_number]['conn'] + self.prompt = self.connections[connection_number]['prompt'] + self.rdbms = self.connections[connection_number]['rdbms'] + self.connection_number = connection_number + self.curs = self.conn.cursor() + def successful_connection_to_number(self, arg): try: connection_number = int(arg) - self.conn = self.connections[connection_number]['conn'] - self.prompt = self.connections[connection_number]['prompt'] - self.connection_number = connection_number - self.curs = self.conn.cursor() - if self.orcl and self.serveroutput: - self.curs.callproc('dbms_output.enable', []) except ValueError: return False + self.make_connection_current(connection_number) + if (self.rdbms == 'oracle') and self.serveroutput: + self.curs.callproc('dbms_output.enable', []) return True def list_connections(self): @@ -67,8 +71,8 @@ self.curs = None self.no_connection() - def url_connect(self, arg): - eng = sqlalchemy.create_engine(arg) + def url_connect(self, arg, mode=0): + eng = sqlalchemy.create_engine(arg) #create_engine refuses "mode" argument self.conn = eng.connect().connection conn = {'conn': self.conn, 'prompt': self.prompt, 'dbname': eng.url.database, 'rdbms': eng.url.drivername, 'user': eng.url.username or '', @@ -109,7 +113,9 @@ print 'Privilege not specified for SYS, assuming SYSOPER' modeval = cx_Oracle.SYSOPER if modeval == 0: # can sqlalchemy connect as sysoper, sysdba? - return self.url_connect('oracle://%s:%s@%s' % (orauser, orapass, oraserv)) + result = self.url_connect('oracle://%s:%s@%s' % (orauser, orapass, oraserv), mode=modeval) + result['dbname'] = oraserv + return result else: self.conn = cx_Oracle.connect(orauser,orapass,oraserv,modeval) result = {'user': orauser, 'rdbms': 'oracle', 'dbname': sid, 'conn': self.conn} @@ -151,13 +157,11 @@ self.connection_number = max(self.connections.keys()) + 1 except ValueError: self.connection_number = 0 + connect_info['prompt'] = '%d:%s@%s> ' % (self.connection_number, connect_info['user'], connect_info['dbname']) self.connections[self.connection_number] = connect_info + self.make_connection_current(self.connection_number) self.curs = self.conn.cursor() - self.orcl = connect_info['rdbms'] == 'oracle' - self.prompt = '%d:%s@%s> ' % (self.connection_number, - connect_info['user'], connect_info['dbname']) - self.connections[self.connection_number]['prompt'] = self.prompt - if self.orcl and self.serveroutput: + if (self.rdbms == 'oracle') and self.serveroutput: self.curs.callproc('dbms_output.enable', []) def postparsing_precmd(self, statement): stop = 0 @@ -252,11 +256,11 @@ command = '%s %s;' else: command = '%s %s' - if self.orcl: + if self.rdbms == 'oracle': current_time = self.current_database_time() self.curs.execute(command % (arg.parsed.command, arg.parsed.args), self.varsUsed) executionmessage = '\nExecuted%s\n' % ((self.curs.rowcount > 0) and ' (%d rows)' % self.curs.rowcount or '') - if self.orcl: + if self.rdbms == 'oracle': self._show_errors(all_users=True, limit=1, mintime=current_time) print executionmessage