Mercurial > sqlpython

changeset 338:a8835fe129f6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
colors
author Catherine Devlin <catherine.devlin@gmail.com>
date Thu, 09 Apr 2009 00:15:09 -0400
parents 8fbf49d3abe8 (current diff) ee7875c7e928 (diff)
children 545f63b6ef42 80a1976decf2
files sqlpython/sqlpyPlus.py sqlpython/sqlpython.py
diffstat 3 files changed, 75 insertions(+), 26 deletions(-) [+]
line wrap: on
line diff
--- a/sqlpython/metadata.py	Thu Apr 09 00:14:22 2009 -0400
+++ b/sqlpython/metadata.py	Thu Apr 09 00:15:09 2009 -0400
@@ -118,7 +118,7 @@
        object_type,
        status,
        last_ddl_time,
-       user as current_username
+       user as my_own
 FROM   all_objects"""
 
 metaqueries['ls']['information_schema'] = """
@@ -127,12 +127,35 @@
        table_type as object_type,
        null as status,
        null as last_ddl_time,
-       current_user as current_username
-FROM   information_schema.tables"""
+       %(my_own)s as my_own
+FROM   information_schema.tables
+UNION ALL
+SELECT trigger_schema as owner,
+       trigger_name as object_name,
+       'TRIGGER' as object_type,
+       null as status,
+       created as last_ddl_time,
+       %(my_own)s as my_own
+FROM   information_schema.triggers
+UNION ALL
+SELECT routine_schema as owner,
+       routine_name as object_name,
+       routine_type as object_type,
+       null as status,
+       last_altered as last_ddl_time,
+       %(my_own)s as my_own
+FROM   information_schema.routines
+"""
 
-metaqueries['ls']['postgres'] = metaqueries['ls']['information_schema']
-metaqueries['ls']['mysql'] = metaqueries['ls']['information_schema']
-metaqueries['ls']['mssql'] = metaqueries['ls']['information_schema']
+metaqueries['ls']['postgres'] = (metaqueries['ls']['information_schema'] + """UNION ALL
+SELECT sequence_schema as owner,
+       sequence_name as object_name,
+       'SEQUENCE' as object_type,
+       null as status,
+       null as last_ddl_time,
+       %(my_own)s as my_own
+FROM   information_schema.sequences""") % {'my_own': "text('public')"}
+metaqueries['ls']['mysql'] = metaqueries['ls']['information_schema'] % {'my_own':"database()"}
 
 metaqueries['ls']['sqlite'] = """
 SELECT '' as owner,
@@ -142,10 +165,3 @@
        null as last_ddl_time,
        '' as current_username
 FROM   sqlite_master"""
-
-metaqueries['resolve_many']['oracle'] = """
-SELECT owner, object_type, object_name, user as current_username 
-FROM   all_objects"""
-            
-
-'''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	Thu Apr 09 00:14:22 2009 -0400
+++ b/sqlpython/sqlpyPlus.py	Thu Apr 09 00:15:09 2009 -0400
@@ -36,7 +36,7 @@
 queries = {
 'resolve': """
 SELECT object_type, object_name, owner FROM (
-SELECT object_type, object_name, user owner, 1 priority
+SELECT object_type, object_name, user AS owner, 1 priority
 FROM   user_objects
 WHERE object_name = :objName
 UNION ALL
@@ -430,7 +430,7 @@
     def do_shortcuts(self,arg):
         """Lists available first-character shortcuts
         (i.e. '!dir' is equivalent to 'shell dir')"""
-        for (scchar, scto) in self.shortcuts.items():
+        for (scchar, scto) in self.shortcuts:
             self.poutput('%s: %s' % (scchar, scto))
 
     tableNameFinder = re.compile(r'from\s+([\w$#_"]+)', re.IGNORECASE | re.MULTILINE | re.DOTALL)          
@@ -438,14 +438,17 @@
         if datum is None:
             return 'NULL'
         elif isinstance(datum, basestring):
-            return "'%s'" % datum
+            return "'%s'" % datum.replace("'","''")
         try:
             return datum.strftime("TO_DATE('%Y-%m-%d %H:%M:%S', 'YYYY-MM-DD HH24:MI:SS')")
         except AttributeError:
-            return str(datum).replace("'","''")
+            return str(datum)
               
     def output(self, outformat, rowlimit):
-        self.tblname = self.tableNameFinder.search(self.querytext).group(1)
+        try:
+            self.tblname = self.tableNameFinder.search(self.querytext).group(1)
+        except AttributeError:
+            self.tblname = ''
         self.colnames = [d[0] for d in self.curs.description]
         if outformat in output_templates:
             self.colnamelen = max(len(colname) for colname in self.colnames)
@@ -693,7 +696,10 @@
         else:
             selecttext = arg
         self.querytext = 'select ' + selecttext
-        self.curs.execute(self.querytext, self.varsUsed)
+        if self.varsUsed:
+            self.curs.execute(self.querytext, self.varsUsed)
+        else: # this is an ugly workaround for the evil paramstyle curse upon DB-API2
+            self.curs.execute(self.querytext)
         self.rows = self.curs.fetchmany(min(self.maxfetch, (rowlimit or self.maxfetch)))
         self.rc = len(self.rows)
         if self.rc != 0:
@@ -785,6 +791,18 @@
         if opts.dump:
             statekeeper.restore()    
 
+    def _show_shortcut(self, shortcut, argpieces):
+        try:
+            newarg = argpieces[1]
+            if newarg == 'on':
+                try:
+                    newarg = argpieces[2]
+                except IndexError:
+                    pass
+        except IndexError:
+            newarg = ''
+        return self.onecmd(shortcut + ' ' + newarg)
+    
     def do_show(self, arg):
         '''
         show                  - display value of all sqlpython parameters
@@ -792,6 +810,7 @@
         show parameter (parameter name) - display value of an ORACLE parameter
         show err (object type/name)     - errors from latest PL/SQL object compilation.
         show all err (type/name)        - all compilation errors from the user's PL/SQL objects.
+        show index on (table)
         '''
         if arg.startswith('param'):
             try:
@@ -808,6 +827,10 @@
                            value FROM v$parameter WHERE name LIKE '%%%s%%';""" % paramname)
         else:
             argpieces = arg.lower().split()
+            for (kwd, shortcut) in (('index', '\\di'), ('schema', '\\dn'), ('tablespace', '\\db'), 
+                                    ('table', '\\dt'), ('view', '\\dv')):
+                if arg.lower().startswith(kwd):
+                    return self._show_shortcut(shortcut, argpieces)
             try:
                 if argpieces[0][:3] == 'err':
                     return self._show_errors(all_users=False, limit=1, targets=argpieces[1:])
@@ -1286,10 +1309,10 @@
             if target in self.object_types:
                 target += '/%'
             where.append("""
-                AND(   object_type || '/' || object_name LIKE '%s'
-                       OR object_name LIKE '%s')""" % (target, target))
+                AND(   UPPER(object_type) || '/' || UPPER(object_name) LIKE '%s'
+                       OR UPPER(object_name) LIKE '%s')""" % (target, target))
         if not opts.all:
-            where.append("AND owner = current_username")
+            where.append("AND owner = my_own")
         return '\n'.join(where)
         
     def resolve_many(self, arg, opts):
@@ -1299,6 +1322,7 @@
         return self.curs.fetchall()
 
     object_types = (
+        'BASE TABLE',
         'CLUSTER',              
         'CONSUMER GROUP',       
         'CONTEXT',              
@@ -1391,11 +1415,12 @@
             self.stdout.write('%s\n' % target)
             target = target.rstrip(';')
             try:
-                self._execute('select * from %s where 1=0' % target) # just to fill description
+                self._execute('select * from %s where 1=0' % target) # first pass fills description
                 if opts.ignorecase:
-                    sql = ' or '.join("LOWER(%s) LIKE '%%%s%%'" % (d[0], pattern.lower()) for d in self.curs.description)                                        
+                    colnames = ['LOWER(%s)' % self._cast(d[0], 'CHAR') for d in self.curs.description]
                 else:
-                    sql = ' or '.join("%s LIKE '%%%s%%'" % (d[0], pattern) for d in self.curs.description)
+                    colnames = ['LOWER(%s)' % self._cast(d[0], 'CHAR') for d in self.curs.description]
+                sql = ' or '.join("%s LIKE '%%%s%%'" % (cn, pattern.lower()) for cn in colnames)
                 sql = self.parsed('SELECT * FROM %s WHERE %s;' % (target, sql), terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix)
                 self.do_select(sql)
             except Exception, e:
@@ -1403,6 +1428,11 @@
                 import traceback
                 traceback.print_exc(file=sys.stdout)                
 
+    def _cast(self, colname, typ='CHAR'):
+        'self._cast(colname, typ) => Returns the RDBMS-equivalent "CAST (colname AS typ) expression.' 
+        converter = {'oracle': 'TO_%(typ)s(%(colname)s)'}.get(self.rdbms, 'CAST(%(colname)s AS %(typ)s)')
+        return converter % {'colname': colname, 'typ': typ}
+    
     def _execute(self, sql, bindvars={}):
         self.sqlfeedback(sql)
         self.curs.execute(sql, bindvars)
--- a/sqlpython/sqlpython.py	Thu Apr 09 00:14:22 2009 -0400
+++ b/sqlpython/sqlpython.py	Thu Apr 09 00:15:09 2009 -0400
@@ -158,6 +158,8 @@
         self.curs = self.conn.cursor()
         if (self.rdbms == 'oracle') and self.serveroutput:
             self.curs.callproc('dbms_output.enable', [])
+        if (self.rdbms == 'mysql'):
+            self.curs.execute('SET SQL_MODE=ANSI')
     def postparsing_precmd(self, statement):
         stop = 0
         self.saved_connection_number = None
@@ -165,7 +167,8 @@
             saved_connection_number = self.connection_number
             try:
                 if self.successful_connection_to_number(statement.parsed.connection_number):
-                    self.saved_connection_number = saved_connection_number
+                    if statement.parsed.command:
+                        self.saved_connection_number = saved_connection_number
             except KeyError:
                 self.list_connections()
                 raise KeyError, 'No connection #%s' % statement.parsed.connection_number