# HG changeset patch
# User catherine@cordelia
# Date 1240603152 14400
# Node ID f92e15f3d0ed79fca984bb56f4b414d35db7bc6c
# Parent  c652478be4fdcae60b3ae6195567906a63b22710
show comments working

diff -r c652478be4fd -r f92e15f3d0ed sqlpython/sqlpyPlus.py
--- a/sqlpython/sqlpyPlus.py	Fri Apr 24 15:09:29 2009 -0400
+++ b/sqlpython/sqlpyPlus.py	Fri Apr 24 15:59:12 2009 -0400
@@ -747,7 +747,8 @@
         else:
             argpieces = arg.lower().split()
             for (kwd, shortcut) in (('index', '\\di'), ('schema', '\\dn'), ('tablespace', '\\db'), 
-                                    ('table', '\\dt'), ('view', '\\dv')):
+                                    ('trigger', '\\dt'), ('view', '\\dv'), ('constraint', '\\dc'),
+                                    ('comment', '\\dm')):
                 if arg.lower().startswith(kwd):
                     return self._show_shortcut(shortcut, argpieces)
             try:
@@ -1069,9 +1070,11 @@
         \q quit
         \w save
         \db _dir_tablespaces
+        \dc _dir_constraints
         \dd comments
+        \dm _dir_comments
         \dn _dir_schemas
-        \dt _dir_tables
+        \dt _dir_triggers
         \dv _dir_views
         \di _dir_indexes
         \? help psql'''
@@ -1093,37 +1096,53 @@
         except KeyError:
             self.perror('psql command \%s not yet supported.' % abbrev)
 
-    def do__dir_tables(self, arg):
-        '''
-        Lists all tables whose names match argument.
-        '''        
-        self.onecmd('ls table/%s' % arg)
-        
-    @options([all_users_option])
-    def do__dir_views(self, arg, opts):
+    def do__dir_views(self, arg):
         '''
         Lists all views whose names match argument.
         '''
-        sql = """SELECT view_name, 'VIEW' as type%s FROM %s_views WHERE view_name LIKE '%%%s%%';""" % \
-                       (opts.scope['col'], opts.scope['view'], arg.upper())
-        self.sqlfeedback(sql)
-        self.do_select(self.parsed(sql, terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix))
+        self.onecmd('ls view/%s' % arg)
         
     def do__dir_indexes(self, arg):
         '''
-        Called with an exact table name, lists the indexes of that table.
-        Otherwise, acts as shortcut for `ls index/*(arg)*`
+        Lists the indexes of a table.
+        '''
+        for (descrip, obj) in sorted(self.gerald_resolve(arg)):
+            if hasattr(obj, 'indexes'):
+                self.poutput('Indexes on ' + descrip)
+                rows = [(iname, i[0], i[1], ','.join(i[2])) for (iname, i) in obj.indexes.items()]
+                self.pseudo_query(arg=arg, colnames='name type unique columns', rows=rows)
+
+    def do__dir_triggers(self, arg):
+        '''
+        Lists the triggers on a table.
+        '''
+        for (descrip, obj) in sorted(self.gerald_resolve(arg)):
+            if hasattr(obj, 'triggers'):
+                self.poutput('Triggers on ' + descrip)
+                rows = [(tname, t.events, t.type, t.level, i[0], i[1], ','.join(i[2])) for (tname, t) in obj.triggers.items()]
+                self.pseudo_query(arg=arg, colnames='name event type level', rows=rows)
+        
+    def do__dir_constraints(self, arg):
         '''
-        try:
-            table_type, table_owner, table_name = self._resolve(arg)
-        except TypeError, IndexError:
-            return self.onecmd('ls Index/*%s*' % arg)
-        sql = """SELECT owner, index_name, index_type FROM all_indexes
-                 WHERE  table_owner = :table_owner
-                 AND    table_name = :table_name;
-                 ORDER BY owner, index_name"""
-        self.do_select(self.parsed(sql, terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix),
-                       bindVarsIn = {'table_owner': table_owner, 'table_name': table_name})
+        Lists the constraints on a table.
+        '''
+        for (descrip, obj) in sorted(self.gerald_resolve(arg)):
+            if hasattr(obj, 'constraints'):
+                self.poutput('Constraints on ' + descrip)
+                rows = [(cname, c[0], c[1], ','.join(c[2]), c[3], c[4], ','.join(c[5])) for (cname, c) in obj.constraints.items()]
+                self.pseudo_query(arg=arg, colnames='name type yn local_columns referenced_index referenced_table referenced_columns', rows=rows)
+
+    def do__dir_comments(self, arg):
+        '''
+        Lists the comments on a table.
+        '''
+        for (descrip, obj) in sorted(self.gerald_resolve(arg)):
+            if hasattr(obj, 'comments'):
+                self.poutput('Comments on ' + descrip)
+                self.poutput(obj.comments)
+                if hasattr(obj, 'columns'):
+                    rows = [(cname, c[9]) for (cname, c) in obj.columns.items()]
+                    self.pseudo_query(arg=arg, colnames='column_name comment', rows=rows)
 
     def do__dir_tablespaces(self, arg):
         '''
@@ -1140,7 +1159,7 @@
         sql = """SELECT owner, count(*) AS objects FROM all_objects GROUP BY owner ORDER BY owner;"""
         self.sqlfeedback(sql)
         self.do_select(self.parsed(sql, terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix))
-
+        
     def do_head(self, arg):
         '''Shortcut for SELECT * FROM <arg>;10
         The terminator (\\t, \\g, \\x, etc.) and number of rows can
diff -r c652478be4fd -r f92e15f3d0ed sqlpython/sqlpython.py
--- a/sqlpython/sqlpython.py	Fri Apr 24 15:09:29 2009 -0400
+++ b/sqlpython/sqlpython.py	Fri Apr 24 15:59:12 2009 -0400
@@ -114,6 +114,10 @@
         conn['gerald_result'] = conn['gerald']()        
         return conn
     
+    def refresh(self, arg):
+        "Refreshes sqlpython's cache of metadata; use after DDL changes structure of tables, views, etc."
+        conn['gerald_result'] = conn['gerald']()        
+        
     def ora_connect(self, arg):
         import cx_Oracle
         connection_modes = {re.compile(' AS SYSDBA', re.IGNORECASE): cx_Oracle.SYSDBA,