# HG changeset patch # User catherine.devlin@gmail.com # Date 1283836383 14400 # Node ID f2fafa02a090d837698771e9b3bbd3f050db5787 # Parent 58696fca94e1380935916c5afef36661edfc2d5e desc refactored diff -r 58696fca94e1 -r f2fafa02a090 sqlpython/connections.py --- a/sqlpython/connections.py Mon Sep 06 06:58:45 2010 -0400 +++ b/sqlpython/connections.py Tue Sep 07 01:13:03 2010 -0400 @@ -196,9 +196,11 @@ result = {'owner': '%', 'type': '%', 'name': '%'} result.update(dict(self.ls_parser.parseString(identifier))) return result - def findAll(self, target): + def objects(self, target, opts): identifier = self.parse_identifier(target) clauses = [] + if (identifier['owner'] == '%') and (not opts.all): + identifier['owner'] = self.username for col in ('owner', 'type', 'name'): if ('%' in identifier[col]) or ('_' in identifier[col]): operator = 'LIKE' @@ -206,12 +208,19 @@ operator = '=' clause = '%s %s' % (operator, self.bindSyntax(col)) clauses.append(clause) + if hasattr(opts, 'reverse') and opts.reverse: + sort_direction = 'DESC' + else: + sort_direction = 'ASC' + clauses.append(sort_direction) qry = self.all_object_qry % tuple(clauses) - if isinstance(self, MySQLInstance): - identifier = (identifier['owner'], identifier['type'], identifier['name']) - result = self.connection.cursor().execute(qry, identifier) + identifier = self.bindVariables(identifier) + result = self.connection.cursor().execute(qry, self.bindVariables(identifier)) return result - + gerald_types = {'TABLE': gerald.oracle_schema.Table, + 'VIEW': gerald.oracle_schema.View} + def object_metadata(self, owner, object_type, name): + return self.gerald_types[object_type](name, self.connection.cursor(), owner) parser = optparse.OptionParser() @@ -248,6 +257,8 @@ port = self.port, sql_mode = 'ANSI') def bindSyntax(self, varname): return '%s' + def bindVariables(self, identifier): + return (identifier['owner'], identifier['type'], identifier['name']) class PostgresInstance(DatabaseInstance): rdbms = 'postgres' @@ -262,7 +273,20 @@ password = self.password, database = self.database, port = self.port) def bindSyntax(self, varname): - return '%%(%s)s' % varname + return '%%(%s)s' % varname.lower() + def bindVariables(self, identifier): + return identifier + all_object_qry = """SELECT table_schema, table_type, table_name + FROM + ( SELECT table_schema, table_type, table_name + FROM information_schema.tables + UNION ALL + SELECT table_schema, 'view', table_name + FROM information_schema.views ) + WHERE ( (table_schema %s) OR (table_schema = 'PUBLIC') ) + AND table_type %s + AND table_name %s + ORDER BY table_schema, table_type, table_name %s""" class OracleInstance(DatabaseInstance): rdbms = 'oracle' @@ -303,10 +327,14 @@ FROM all_objects WHERE owner %s AND object_type %s - AND object_name %s""" + AND object_name %s + ORDER BY owner, object_type, object_name %s""" def bindSyntax(self, varname): return ':' + varname - + def bindVariables(self, identifier): + return {'owner': identifier['owner'].upper(), + 'type': identifier['type'].upper(), + 'name': identifier['name'].upper()} if __name__ == '__main__': opts = OptionTestDummy(password='password') diff -r 58696fca94e1 -r f2fafa02a090 sqlpython/sqlpyPlus.py --- a/sqlpython/sqlpyPlus.py Mon Sep 06 06:58:45 2010 -0400 +++ b/sqlpython/sqlpyPlus.py Tue Sep 07 01:13:03 2010 -0400 @@ -27,6 +27,7 @@ import datetime, pickle, binascii, subprocess, time, itertools, hashlib import traceback, operator from cmd2 import Cmd, make_option, options, Statekeeper, Cmd2TestCase, options_defined +import operator from output_templates import output_templates from metadata import metaqueries from plothandler import Plot @@ -855,57 +856,38 @@ self._pull(arg, opts) def _pull(self, arg, opts, vc=None): - opts.exact = True statekeeper = Statekeeper(opts.dump and self, ('stdout',)) - (username, gerald_schema) = self.metadata() try: - for description in self._matching_database_objects(arg, opts): - self.poutput(description.path) - txt = description.dbobj.get_ddl() - if hasattr(description.dbobj, 'get_body_ddl'): - bodytxt = description.dbobj.get_body_ddl() - else: - bodytxt = '' + for (owner, object_type, name) in self.current_instance.objects(arg, opts): + obj = self.current_instance.object_metadata(owner, object_type, name) + txt = obj.get_ddl() if opts.get('lines'): txt = self._with_line_numbers(txt) - bodytxt = self._with_line_numbers(bodytxt) if opts.dump: - owner = description.owner or self.current_instance.username - path = os.path.join(owner.lower(), description.type.lower()) \ - .replace(' ', '_') + path = os.path.join(owner.lower(), object_type.lower()).replace(' ', '_') try: os.makedirs(path) except OSError: pass - filename = os.path.join(path, '%s.sql' % description.unqualified_name.lower()) + filename = os.path.join(path, '%s.sql' % name.lower()) self.stdout = open(filename, 'w') - if bodytxt: - bodyfilename = os.path.join(path, '%s_body.sql' % description.unqualified_name.lower()) - bodyfile = open(bodyfilename, 'w') if opts.get('num') is not None: txt = txt.splitlines() txt = centeredSlice(txt, center=opts.num+1, width=opts.width) txt = '\n'.join(txt) else: - txt = 'REMARK BEGIN %s\n%s\nREMARK END\n' % (description.path, txt) - if bodytxt: - bodytxt = 'REMARK BEGIN %s\n%s\nREMARK END\n' % (description.path, bodytxt) - + txt = 'REMARK BEGIN %s/%s/%s\n%s\nREMARK END\n' % (owner, object_type, name, txt) self.poutput(txt) - if bodytxt: - if opts.dump: - bodyfile.write(bodytxt) - else: - self.poutput(bodytxt) - if opts.full: + + + """if opts.full: for dependent_type in ('constraints', 'triggers', 'indexes'): if hasattr(description.dbobj, dependent_type): for (depname, depobj) in getattr(description.dbobj, dependent_type).items(): self.poutput('REMARK BEGIN\n%s\nREMARK END\n\n' % depobj.get_ddl()) + """ if opts.dump: self.stdout.close() - if bodytxt: - bodyfile.close() statekeeper.restore() if vc: subprocess.call(vc + [filename]) @@ -1074,28 +1056,31 @@ #TODO: in postgres, _key_columns returns 'fishies_pkey' instead of 'n' else: return [] + + standard_options = [ + all_users_option, + make_option('-l', '--long', action='store_true', help='long descriptions'), + make_option('-r', '--reverse', action='store_true', help="Reverse order while sorting")] - @options([all_users_option, - make_option('-l', '--long', action='store_true', help='include column #, comments'), + @options(standard_options + [ make_option('-A', '--alpha', action='store_true', help='List columns alphabetically')]) def do_describe(self, arg, opts): - opts.exact = True rowlimit = self.rowlimit(arg) if opts.alpha: sortkey = operator.itemgetter('name') else: sortkey = operator.itemgetter('sequence') - for descrip in self._matching_database_objects(arg, opts): - self.tblname = descrip.fullname + for (owner, object_type, name) in self.current_instance.objects(arg, opts): + obj = self.current_instance.object_metadata(owner, object_type, name) + self.tblname = '%s %s.%s' % (object_type, owner, name) self.pfeedback(self.tblname) - if opts.long and hasattr(descrip.dbobj, 'comments'): - if descrip.dbobj.comments: - self.poutput(descrip.dbobj.comments) - if hasattr(descrip.dbobj, 'columns'): - cols = sorted(descrip.dbobj.columns.values(), key=sortkey)[:rowlimit] - if opts.long and hasattr(descrip.dbobj, 'constraints'): - primary_key_columns = self._key_columns(descrip.dbobj, 'Primary') - unique_key_columns = self._key_columns(descrip.dbobj, 'Unique') + if opts.long and hasattr(obj, 'comments') and obj.comments: + self.poutput(obj.comments) + if hasattr(obj, 'columns'): + cols = sorted(obj.columns.values(), key=sortkey, reverse=bool(opts.reverse))[:rowlimit] + if opts.long and hasattr(obj, 'constraints'): + primary_key_columns = self._key_columns(obj, 'Primary') + unique_key_columns = self._key_columns(obj, 'Unique') self.colnames = 'N Name Nullable Type Key Default Comments'.split() self.rows = [(col['sequence'], col['name'], (col['nullable'] and 'NULL') or 'NOT NULL', self._col_type_descriptor(col), @@ -1109,18 +1094,17 @@ for col in cols] self.coltypes = [str] * len(self.colnames) self.poutput('%s\n\n' % self.tabular_output(arg.parsed.terminator, self.tblname)) - elif hasattr(descrip.dbobj, 'increment_by'): + elif hasattr(obj, 'increment_by'): self.colnames = 'name min_value max_value increment_by'.split() self.coltypes = [str, int, int, int] - self.rows = [(getattr(descrip.dbobj, p) for p in self.colnames)] + self.rows = [(getattr(obj, p) for p in self.colnames)] self.poutput('%s\n\n' % self.tabular_output(arg.parsed.terminator, self.tblname)) - elif hasattr(descrip.dbobj, 'source'): + elif hasattr(obj, 'source'): end_heading = re.compile(r'\bDECLARE|BEGIN\b', re.IGNORECASE) - for (index, (ln, line)) in enumerate(descrip.dbobj.source): + for (index, (ln, line)) in enumerate(obj.source): if end_heading.search(line): break - self.poutput(''.join(l for (ln, l) in descrip.dbobj.source[:index])) - + self.poutput(''.join(l for (ln, l) in obj.source[:index])) @options([all_users_option]) def do_deps(self, arg, opts): '''Lists all objects that are dependent upon the object.''' @@ -1267,11 +1251,7 @@ def _do_dir(self, type, arg, opts): self._do_ls("%s/%s%s%s" % (type, str(arg), arg.parsed.terminator, arg.parsed.suffix), opts) - standard_options = [ - all_users_option, - make_option('-l', '--long', action='store_true', help='long descriptions'), - make_option('-r', '--reverse', action='store_true', help="Reverse order while sorting")] - + @options(standard_options) def do__dir_tables(self, arg, opts): 'Shortcut for ``ls table/``' @@ -1606,12 +1586,8 @@ def _do_ls(self, arg, opts): 'Functional core of ``do_ls``, split out into an undecorated version to be callable from other methods' - opts.exact = True - (username, schemas) = self.metadata() - result = [descrip.path for descrip in self._matching_database_objects(arg, opts)] - if result: - result.sort(reverse=bool(opts.reverse)) - self.poutput('\n'.join(result)) + for row in self.current_instance.objects(arg, opts): + self.poutput('%s/%s/%s' % row) @options(standard_options) def do_ls(self, arg, opts):