Mercurial > sqlpython

changeset 317:f200a222a936

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
beginning to set up metadata.py
author Catherine Devlin <catherine.devlin@gmail.com>
date Tue, 31 Mar 2009 12:03:49 -0400
parents ea0b0e1ab6da
children 0aad38fbc361
files setup.py sqlpython/__init__.py sqlpython/metadata.py sqlpython/sqlpyPlus.py sqlpython/sqlpython.py
diffstat 5 files changed, 104 insertions(+), 68 deletions(-) [+]
line wrap: on
line diff
--- 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",
--- 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
--- /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;"""
--- 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:
--- 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