# HG changeset patch # User catherine@cordelia # Date 1240656884 14400 # Node ID 8e341308ea45d4ec4c1c8c0aee803334f6f8f889 # Parent cc67405f54557ee5feac223c26f34b008a7b1bb0 find working diff -r cc67405f5455 -r 8e341308ea45 sqlpython/sqlpyPlus.py --- a/sqlpython/sqlpyPlus.py Sat Apr 25 06:08:49 2009 -0400 +++ b/sqlpython/sqlpyPlus.py Sat Apr 25 06:54:44 2009 -0400 @@ -841,69 +841,30 @@ default={'col':'', 'view':'user', 'schemas':'user', 'firstcol': ''}, const={'col':', owner', 'view':'all', 'schemas':'all', 'firstcol': 'owner, '}, help='Describe all objects (not just my own)') - @options([all_users_option, + @options([#all_users_option, make_option('-c', '--col', action='store_true', help='find column'), make_option('-t', '--table', action='store_true', help='find table')]) def do_find(self, arg, opts): """Finds argument in source code or (with -c) in column definitions.""" capArg = arg.upper() - if opts.col: - sql = "SELECT table_name, column_name %s FROM %s_tab_columns where column_name like '%%%s%%' ORDER BY %s table_name, column_name;" \ - % (opts.scope['col'], opts.scope['view'], capArg, opts.scope['firstcol']) + target = oracle_wildcards_to_regex(arg) + '$' + for (descrip, obj) in sorted(self.gerald_resolve('')): + if hasattr(obj, 'columns'): + for col in obj.columns: + if re.match(target, col): + self.poutput('%s.%s' % (descrip, col)) elif opts.table: - sql = "SELECT table_name %s from %s_tables where table_name like '%%%s%%' ORDER BY %s table_name;" \ - % (opts.scope['col'], opts.scope['view'], capArg, opts.scope['firstcol']) + return self.onecmd('ls table/%s' % arg.upper()) else: - sql = "SELECT * from %s_source where UPPER(text) like '%%%s%%';" % (opts.scope['view'], capArg) - self.do_select(self.parsed(sql, terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix)) - + target = oracle_wildcards_to_regex(arg) + for (descrip, obj) in sorted(self.gerald_resolve('')): + if hasattr(obj, 'source'): + for (lineNum, source) in obj.source: + if re.search(target, source): + self.poutput('%s %d: %s' % (descrip, lineNum, source)) - @options([all_users_option, - make_option('-l', '--long', action='store_true', help='include column #, comments')]) - def _do_describe_oracle(self, arg, opts): - "emulates SQL*Plus's DESCRIBE" - target = arg.upper() - objnameclause = '' - if target: - objnameclause = "AND object_name LIKE '%%%s%%' " % target - object_type, owner, object_name = self.resolve(target) - if (not target) or (not object_type): - if opts.long: - query = """SELECT o.object_name, o.object_type, o.owner, c.comments - FROM all_objects o - LEFT OUTER JOIN all_tab_comments c ON (o.owner = c.owner AND o.object_name = c.table_name AND o.object_type = 'TABLE') - WHERE object_type IN ('TABLE','VIEW','INDEX') - %sORDER BY object_name;""" % objnameclause - else: - query = """SELECT object_name, object_type%s - FROM %s_objects - WHERE object_type IN ('TABLE','VIEW','INDEX') - %sORDER BY object_name;""" % \ - (opts.scope['col'], opts.scope['view'], objnameclause) - return self.do_select(self.parsed(query, terminator=arg.parsed.terminator or ';', - suffix=arg.parsed.suffix)) - self.stdout.write("%s %s.%s\n" % (object_type, owner, object_name)) - try: - if object_type == 'TABLE': - if opts.long: - self._execute(queries['tabComments'], {'table_name':object_name, 'owner':owner}) - self.stdout.write(self.curs.fetchone()[0]) - descQ = metaqueries['desc'][self.rdbms][object_type][(opts.long and 'long') or 'short'] - else: - descQ = metaqueries['desc'][self.rdbms][object_type] - for q in descQ: - self.do_select(self.parsed(q, terminator=arg.parsed.terminator or ';' , suffix=arg.parsed.suffix), - bindVarsIn={'object_name':object_name, 'owner':owner}) - except KeyError: - if object_type == 'PACKAGE': - packageContents = self.select_scalar_list(descQueries['PackageObjects'][0], {'package_name':object_name, 'owner':owner}) - for packageObj_name in packageContents: - self.stdout.write('Arguments to %s\n' % (packageObj_name)) - sql = self.parsed(descQueries['PackageObjArgs'][0], terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix) - self.do_select(sql, bindVarsIn={'package_name':object_name, 'owner':owner, 'object_name':packageObj_name}) - def _str_datatype_(self, datatype, length, scale, precision): if precision is not None: result = '%s(%s,%s)' % (datatype, scale, precision) @@ -917,19 +878,28 @@ make_option('-r', '--refresh', action='store_true', help='Refresh cache of metadata'), make_option('-l', '--long', action='store_true', help='include column #, comments')]) def do_describe(self, arg, opts): - if self.rdbms == 'oracle': + if False and self.rdbms == 'oracle': return self._do_describe_oracle () if opts.refresh: self.connections[self.connection_number]['gerald_result'] = self.connections[self.connection_number]['gerald']() + if opts.long: + colnames = 'pos name type null default comments' + else: + colnames = 'pos name type null default' for (descriptor, obj) in sorted(self.gerald_resolve(arg)): self.poutput(descriptor) + if opts.long and hasattr(obj, 'comments'): + self.poutput(obj.comments) if hasattr(obj, 'columns'): self.tblname = obj.name - columns = obj.columns.values() - columns.sort() - self.pseudo_query(arg=arg, colnames = 'pos name type null default', - rows=[(c[0], c[1], self._str_datatype_(c[2], c[3], c[4], c[5]), c[6], c[7]) - for c in columns]) + columns = sorted(obj.columns.values()) + if opts.long: + rows=[(c[0], c[1], self._str_datatype_(c[2], c[3], c[4], c[5]), c[6], c[7], c[9]) for c in columns] + else: + rows=[(c[0], c[1], self._str_datatype_(c[2], c[3], c[4], c[5]), c[6], c[7]) for c in columns] + self.pseudo_query(arg=arg, colnames=colnames, rows=rows) + elif opts.long and hasattr(obj, 'get_ddl'): + self.poutput(obj.get_ddl()) # or dump()? def do_deps(self, arg): '''Lists all objects that are dependent upon the object.''' @@ -953,19 +923,7 @@ def do_comments(self, arg): 'Prints comments on a table and its columns.' - target = arg.upper() - object_type, owner, object_name, colName = self.resolve_with_column(target) - if object_type: - self._execute(queries['tabComments'], {'table_name':object_name, 'owner':owner}) - self.stdout.write("%s %s.%s: %s\n" % (object_type, owner, object_name, self.curs.fetchone()[0])) - if colName: - sql = queries['oneColComments'] - bindVarsIn={'owner':owner, 'object_name': object_name, 'column_name': colName} - else: - sql = queries['colComments'] - bindVarsIn={'owner':owner, 'object_name': object_name} - self.do_select(self.parsed(sql, terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix), - bindVarsIn=bindVarsIn) + return self.onecmd('SHOW comments ON %s' % arg) def _resolve(self, identifier): parts = identifier.split('.') @@ -1142,8 +1100,8 @@ 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) + cols = sorted(obj.columns.values()) + self.pseudo_query(arg=arg, colnames='column_name comment', rows=[(c[1], c[9]) for c in cols]) def do__dir_tablespaces(self, arg): '''