# HG changeset patch # User Catherine Devlin # Date 1239053839 14400 # Node ID 8dd71d47f3cbf532a1959d3c67eefb256c945472 # Parent 3efffbf7481fe00d7ab9a678b33be3108f58a4be# Parent b2fbb9de8845f7d8205b9edcc2b381c2311b5229 merged with rows_remembered diff -r 3efffbf7481f -r 8dd71d47f3cb sqlpython/metadata.py --- 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 diff -r 3efffbf7481f -r 8dd71d47f3cb sqlpython/sqlpyPlus.py --- 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):