changeset 242:8866fe0706c3 1.6.0

message when all columns excluded
author catherine@dellzilla
date Mon, 02 Mar 2009 16:21:49 -0500
parents d1f1022d2387
children 98d8746f211b
files setup.py sqlpython/mysqlpy.py sqlpython/sqlpyPlus.py sqlpython/sqlpython.py
diffstat 4 files changed, 20 insertions(+), 16 deletions(-) [+]
line wrap: on
line diff
--- a/setup.py	Mon Mar 02 06:38:30 2009 -0500
+++ b/setup.py	Mon Mar 02 16:21:49 2009 -0500
@@ -17,7 +17,7 @@
       url="https://twiki.cern.ch/twiki/bin/view/PSSGroup/SqlPython",
       packages=find_packages(),
       include_package_data=True,    
-      install_requires=['pyparsing','cmd2>=0.4.6','cx_Oracle','genshi>=0.5'],
+      install_requires=['pyparsing','cmd2>=0.4.7','cx_Oracle','genshi>=0.5'],
       keywords = 'client oracle database',
       license = 'MIT',
       platforms = ['any'],
--- a/sqlpython/mysqlpy.py	Mon Mar 02 06:38:30 2009 -0500
+++ b/sqlpython/mysqlpy.py	Mon Mar 02 16:21:49 2009 -0500
@@ -13,9 +13,9 @@
 
 class mysqlpy(sqlpyPlus):
     '''
-MySqlPy V1.5.3 - 'sqlplus in python'
+MySqlPy V1.6.0 - 'sqlplus in python'
 Author: Luca.Canali@cern.ch
-Rev: 1.5.3, 25-Feb-09
+Rev: 1.6.0, 02-Mar-09
 
 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
--- a/sqlpython/sqlpyPlus.py	Mon Mar 02 06:38:30 2009 -0500
+++ b/sqlpython/sqlpyPlus.py	Mon Mar 02 16:21:49 2009 -0500
@@ -391,9 +391,10 @@
     do__load = Cmd.do_load
     
     def do_remark_begin(self, line):
-        '''Wrapping a *single* SQL or PL/SQL statement in `REMARK_BEGIN` and `REMARK_END`
+        '''
+        Wrapping a *single* SQL or PL/SQL statement in `REMARK_BEGIN` and `REMARK_END`
         tells sqlpython to submit the enclosed code directly to Oracle as a single
-        unit of code.
+        unit of code.  
         
         Without these markers, sqlpython fails to properly distinguish the beginning
         and end of all but the simplest PL/SQL blocks, causing errors.  sqlpython also
@@ -401,7 +402,8 @@
         the statement has ended yet; `REMARK_BEGIN` and `REMARK_END` allow it to skip this
         parsing.
         
-        REMARK_BEGIN and REMARK_END will be read as comments by standard SQL*Plus.
+        Standard SQL*Plus interprets REMARK_BEGIN and REMARK_END as comments, so it is
+        safe to include them in SQL*Plus scripts.
         '''
         statement = []
         next = self.pseudo_raw_input(self.continuationPrompt)
@@ -409,8 +411,7 @@
             #and not next.lower().strip().startswith('--- end'):
             statement.append(next)
             next = self.pseudo_raw_input(self.continuationPrompt)
-        statement = self.parsed('\n'.join(statement))
-        return self.onecmd(statement)        
+        return self.onecmd('\n'.join(statement))        
     
     def remark(self, line):
         pass
@@ -524,9 +525,11 @@
                         pyparsing.SkipTo(pyparsing.stringEnd)('remainder')
 
     negator = pyparsing.Literal('!')('exclude')
-    colNumber = pyparsing.Optional(negator) + pyparsing.Literal('.') + pyparsing.Word('-' + pyparsing.nums, pyparsing.nums)('column_number')
+    colNumber = pyparsing.Optional(negator) + pyparsing.Literal('#') + pyparsing.Word('-' + pyparsing.nums, pyparsing.nums)('column_number')
     colName = negator + pyparsing.Word('$_#' + pyparsing.alphas, '$_#' + pyparsing.alphanums)('column_name')
     wildColName = pyparsing.Optional(negator) + pyparsing.Word('?*%$_#' + pyparsing.alphas, '?*%$_#' + pyparsing.alphanums, min=2)('column_name')
+    colNumber.ignore(pyparsing.cStyleComment).ignore(Parser.comment_def). \
+              ignore(pyparsing.sglQuotedString).ignore(pyparsing.dblQuotedString) 
     wildSqlParser = colNumber ^ colName ^ wildColName
     wildSqlParser.ignore(pyparsing.cStyleComment).ignore(Parser.comment_def). \
                   ignore(pyparsing.sglQuotedString).ignore(pyparsing.dblQuotedString)   
@@ -577,22 +580,23 @@
                     replacers[arg[startpos:endpos]].extend(i for i in include_here if i not in replacers[arg[startpos:endpos]])
                     excluded.add(colname)
                 else:
-                    #included.add(colname)
                     excluded.discard(colname)
                     replacers[arg[startpos:endpos]].append(colname)
                     
         replacers = sorted(replacers.items(), key=len, reverse=True)
         result = columnlist.columns
         for (target, replacement) in replacers:
-            cols = [r for r in replacement if r not in excluded and r not in included]
-            
+            cols = [r for r in replacement if r not in excluded and r not in included]            
             replacement = ', '.join(cols)
             included.update(cols)
             result = result.replace(target, replacement)
-        # some column names could get wiped out completely - fix their dangling commas
+        # some column names could get wiped out completely, so we fix their dangling commas
         result = self.emptyCommaRegex.sub(',', result)
         result = self.deadStarterCommaRegex.sub('', result)
         result = self.deadEnderCommaRegex.sub('', result)
+        if not result.strip():
+            print 'No columns found matching criteria.'
+            return 'null from dual'        
         return result + ' ' + columnlist.remainder
         
     rowlimitPattern = pyparsing.Word(pyparsing.nums)('rowlimit')
@@ -669,7 +673,7 @@
                             ddlargs = [object_type, object_name]
                         else:
                             ddlargs = [object_type, object_name, owner]
-                        self.stdout.write(str(self.curs.callfunc('DBMS_METADATA.GET_DDL', cx_Oracle.CLOB, ddlargs)))
+                        self.stdout.write('REMARK_BEGIN\n%s\nREMARK_END\n\n' % str(self.curs.callfunc('DBMS_METADATA.GET_DDL', cx_Oracle.CLOB, ddlargs)))
                     except cx_Oracle.DatabaseError:
                         if object_type == 'JOB':
                             print '%s: DBMS_METADATA.GET_DDL does not support JOBs (MetaLink DocID 567504.1)' % object_name
@@ -678,7 +682,7 @@
                     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,
+                                self.stdout.write('REMARK_BEGIN\n%s\nREMARK_END\n\n' % str(self.curs.callfunc('DBMS_METADATA.GET_DEPENDENT_DDL', cx_Oracle.CLOB,
                                                                          [dependent_type, object_name, owner])))
                             except cx_Oracle.DatabaseError:
                                 pass
--- a/sqlpython/sqlpython.py	Mon Mar 02 06:38:30 2009 -0500
+++ b/sqlpython/sqlpython.py	Mon Mar 02 16:21:49 2009 -0500
@@ -10,7 +10,7 @@
 
 import cmd2,getpass,binascii,cx_Oracle,re,os
 import sqlpyPlus
-__version__ = '1.5.3'    
+__version__ = '1.6.0'    
 
 class sqlpython(cmd2.Cmd):
     '''A python module to reproduce Oracle's command line with focus on customization and extention'''