changeset 330:8dd71d47f3cb

merged with rows_remembered
author Catherine Devlin <catherine.devlin@gmail.com>
date Mon, 06 Apr 2009 17:37:19 -0400
parents 3efffbf7481f (current diff) b2fbb9de8845 (diff)
children a6cbcf24f148
files sqlpython/sqlpyPlus.py
diffstat 2 files changed, 80 insertions(+), 39 deletions(-) [+]
line wrap: on
line diff
--- a/sqlpython/metadata.py	Mon Apr 06 14:45:05 2009 -0400
+++ b/sqlpython/metadata.py	Mon Apr 06 17:37:19 2009 -0400
@@ -111,3 +111,36 @@
 
 metaqueries['desc']['oracle']['VIEW'] = metaqueries['desc']['oracle']['TABLE']['short']
 metaqueries['desc']['oracle']['FUNCTION'] = metaqueries['desc']['oracle']['PROCEDURE']
+
+metaqueries['ls']['oracle'] = """
+SELECT owner, 
+       object_name,  
+       object_type,
+       status,
+       last_ddl_time,
+       user as current_username
+FROM   all_objects"""
+
+metaqueries['ls']['information_schema'] = """
+SELECT table_schema as owner,
+       table_name as object_name,
+       table_type as object_type,
+       null as status,
+       null as last_ddl_time,
+       current_user as current_username
+FROM   information_schema.tables"""
+
+metaqueries['ls']['postgres'] = metaqueries['ls']['information_schema']
+metaqueries['ls']['mysql'] = metaqueries['ls']['information_schema']
+metaqueries['ls']['mssql'] = metaqueries['ls']['information_schema']
+
+metaqueries['ls']['sqlite'] = """
+SELECT '' as owner,
+       tbl_name as object_name,
+       type as object_type,
+       null as status,
+       null as last_ddl_time,
+       '' as current_username
+FROM   sqlite_master"""
+
+'''oof, metadata is hard.  \d information_schema.tables, http://www.alberton.info/postgresql_meta_info.html'''
\ No newline at end of file
--- a/sqlpython/sqlpyPlus.py	Mon Apr 06 14:45:05 2009 -0400
+++ b/sqlpython/sqlpyPlus.py	Mon Apr 06 17:37:19 2009 -0400
@@ -316,7 +316,7 @@
     def __init__(self):
         sqlpython.sqlpython.__init__(self)
         self.binds = CaselessDict()
-        self.settable += 'autobind commit_on_exit maxfetch maxtselctrows scan serveroutput sql_echo store_results timeout heading wildsql'.split()
+        self.settable += 'autobind commit_on_exit maxfetch maxtselctrows rows_remembered scan serveroutput sql_echo timeout heading wildsql'.split()
         self.settable.remove('case_insensitive')
         self.settable.sort()
         self.stdoutBeforeSpool = sys.stdout
@@ -330,7 +330,7 @@
         self.nonpythoncommand = 'sql'
         self.substvars = {}
         self.result_history = []
-        self.store_results = True
+        self.rows_remembered = 10000
         
         self.pystate = {'r': [], 'binds': self.binds, 'substs': self.substvars}
         
@@ -663,6 +663,14 @@
         except IndexError:
             print self.do_bind.__doc__
         
+    def age_out_resultsets(self):
+        total_len = sum(len(rs) for rs in self.pystate['r'])
+        for (i, rset) in enumerate(self.pystate['r'][:-1]):
+            if total_len <= self.rows_remembered:
+                return
+            total_len -= len(rset)
+            self.pystate['r'][i] = []
+            
     def do_select(self, arg, bindVarsIn=None, terminator=None):
         """Fetch rows from a table.
 
@@ -689,7 +697,7 @@
         self.querytext = 'select ' + selecttext
         self.curs.execute(self.querytext, self.varsUsed)
         self.rows = self.curs.fetchmany(min(self.maxfetch, (rowlimit or self.maxfetch)))
-        self.rc = self.curs.rowcount
+        self.rc = len(self.rows)
         if self.rc != 0:
             resultset = ResultSet()
             resultset.colnames = [d[0].lower() for d in self.curs.description]
@@ -700,6 +708,7 @@
             for row in resultset:
                 row.resultset = resultset
             self.pystate['r'].append(resultset)
+            self.age_out_resultsets()
             self.stdout.write('\n%s\n' % (self.output(arg.parsed.terminator, rowlimit)))
         if self.rc == 0:
             print '\nNo rows Selected.\n'
@@ -1272,44 +1281,24 @@
     def do_declare(self, arg):
         self.anon_plsql('declare ' + arg)
         
-    def _ls_statement(self, arg, opts):
+    def ls_where_clause(self, arg, opts):
+        where = ['WHERE (1=1) ']
         if arg:
             target = arg.upper().replace('*','%')
             if target in self.object_types:
                 target += '/%'
-            where = """\nWHERE object_type || '/' || object_name LIKE '%s'
-                       OR object_name LIKE '%s'""" % (target, target)
-        else:
-            where = ''
-        if opts.all:
-            whose = 'all'
-            objname = "owner || '.' || object_name"            
-        else:
-            whose = 'user'
-            objname = 'object_name'            
-        if hasattr(opts, 'long') and opts.long:
-            moreColumns = ', status, last_ddl_time'
-        else:
-            moreColumns = ''
-            
-        # 'Normal' sort order is DATE DESC (maybe), object type ASC, object name ASC
-        sortdirection = (hasattr(opts, 'reverse') and opts.reverse and 'DESC') or 'ASC'
-        orderby = 'object_type %s, object_name %s' % (sortdirection, sortdirection)
-        if hasattr(opts, 'timesort') and opts.timesort:
-            orderby = 'last_ddl_time %s, %s' % (('ASC' if hasattr(opts, 'reverse') and opts.reverse else 'DESC'), orderby)
-        return {'objname': objname, 'moreColumns': moreColumns,
-                'whose': whose, 'where': where, 'orderby': orderby}        
+            where.append("""
+                AND(   object_type || '/' || object_name LIKE '%s'
+                       OR object_name LIKE '%s')""" % (target, target))
+        if not opts.all:
+            where.append("AND owner = current_username")
+        return '\n'.join(where)
         
     def resolve_many(self, arg, opts):
-        opts.long = False
-        clauses = self._ls_statement(arg, opts)
-        if opts.all:
-            clauses['owner'] = 'owner'
-        else:
-            clauses['owner'] = 'user'
-        statement = '''SELECT %(owner)s, object_type, object_name 
-                  FROM   %(whose)s_objects %(where)s
-                  ORDER BY object_type, object_name''' % clauses
+        statement = '''
+            SELECT owner, object_type, object_name 
+            FROM   all_objects %s
+            ORDER BY object_type, object_name''' % self.ls_where_clause(arg, opts)
         self._execute(statement)
         return self.curs.fetchall()
 
@@ -1360,10 +1349,29 @@
         Lists objects as through they were in an {object_type}/{object_name} UNIX
         directory structure.  `*` and `%` may be used as wildcards.
         '''
-        statement = '''SELECT object_type || '/' || %(objname)s AS name %(moreColumns)s 
-                  FROM   %(whose)s_objects %(where)s
-                  ORDER BY %(orderby)s;''' % self._ls_statement(arg, opts)
-        self.do_select(self.parsed(statement, terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix))
+        clauses = {'owner': '', 'moreColumns': '',
+                   'source': metaqueries['ls'][self.rdbms],
+                   'where': self.ls_where_clause(arg, opts)}
+        if opts.long:
+            clauses['moreColumns'] = ', status, last_ddl_time'
+        if opts.all:
+            clauses['owner'] = "owner || '.' ||"
+
+        # 'Normal' sort order is DATE DESC (maybe), object type ASC, object name ASC
+        sortdirection = (hasattr(opts, 'reverse') and opts.reverse and 'DESC') or 'ASC'
+        orderby = 'object_type %s, object_name %s' % (sortdirection, sortdirection)
+        if hasattr(opts, 'timesort') and opts.timesort:
+            orderby = 'last_ddl_time %s, %s' % (
+                ('ASC' if hasattr(opts, 'reverse') and opts.reverse else 'DESC'), orderby)
+        clauses['orderby'] = orderby    
+        statement = '''
+            SELECT object_type || '/' || %(owner)s object_name AS name %(moreColumns)s 
+            FROM   (%(source)s) source
+            %(where)s
+            ORDER BY %(orderby)s;''' % clauses
+        self.do_select(self.parsed(statement, 
+                                   terminator=arg.parsed.terminator or ';', 
+                                   suffix=arg.parsed.suffix))
         
     @options([make_option('-i', '--ignore-case', dest='ignorecase', action='store_true', help='Case-insensitive search')])        
     def do_grep(self, arg, opts):