# HG changeset patch # User Catherine Devlin # Date 1239224500 14400 # Node ID 00b183a103b352e64a06250b4bc8bd4395fcd4c7 # Parent 1e199ea5b846ebaadb6f1717d8cdebdad185fdaf bare prefix switches connection diff -r 1e199ea5b846 -r 00b183a103b3 sqlpython/metadata.py --- a/sqlpython/metadata.py Wed Apr 08 10:49:20 2009 -0400 +++ b/sqlpython/metadata.py Wed Apr 08 17:01:40 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 diff -r 1e199ea5b846 -r 00b183a103b3 sqlpython/sqlpyPlus.py --- a/sqlpython/sqlpyPlus.py Wed Apr 08 10:49:20 2009 -0400 +++ b/sqlpython/sqlpyPlus.py Wed Apr 08 17:01:40 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 @@ -431,7 +431,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: print '%s: %s' % (scchar, scto) tableNameFinder = re.compile(r'from\s+([\w$#_"]+)', re.IGNORECASE | re.MULTILINE | re.DOTALL) @@ -446,7 +446,10 @@ 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) @@ -695,7 +698,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: @@ -787,6 +793,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 @@ -794,6 +812,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: @@ -810,6 +829,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:]) @@ -1288,10 +1311,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): @@ -1301,6 +1324,7 @@ return self.curs.fetchall() object_types = ( + 'BASE TABLE', 'CLUSTER', 'CONSUMER GROUP', 'CONTEXT', @@ -1393,11 +1417,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: @@ -1405,6 +1430,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={}): if self.sql_echo: print sql diff -r 1e199ea5b846 -r 00b183a103b3 sqlpython/sqlpython.py --- a/sqlpython/sqlpython.py Wed Apr 08 10:49:20 2009 -0400 +++ b/sqlpython/sqlpython.py Wed Apr 08 17:01:40 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