Mercurial > sqlpython
changeset 503:0b584c8238b4
ls working on Oracle
| author | catherine.devlin@gmail.com |
|---|---|
| date | Wed, 22 Sep 2010 15:16:33 -0400 |
| parents | 2b3974b0a584 |
| children | e86bd0457a8b |
| files | sqlpython/connections.py sqlpython/sqlpyPlus.py |
| diffstat | 2 files changed, 92 insertions(+), 67 deletions(-) [+] |
line wrap: on
line diff
--- a/sqlpython/connections.py Tue Sep 21 15:26:24 2010 -0400 +++ b/sqlpython/connections.py Wed Sep 22 15:16:33 2010 -0400 @@ -173,38 +173,35 @@ pyparsing.Optional(sqlname("owner") + ".") + pyparsing.Optional(sqlname("name")) + pyparsing.stringEnd )) + identifier_regex = re.compile( + r'((?P<object_type>DATABASE LINK|DIRECTORY|FUNCTION|INDEX|JOB|MATERIALIZED VIEW|PACKAGE|PROCEDURE|SEQUENCE|SYNONYM|TABLE|TRIGGER|TYPE|VIEW|BASE TABLE)($|[\\/.\s])+)?(?P<remainder>.*)', + re.IGNORECASE) def parse_identifier(self, identifier): """ >>> opts = OptionTestDummy(postgres=True, password='password') >>> db = DatabaseInstance('thedatabase theuser', opts) >>> result = db.parse_identifier('scott.pets') - >>> (result.owner, result.type, result.name) - ('scott', '%', 'pets') + >>> (result['object_type'], result['name1'], result['name2']) + (None, 'scott', 'pets') >>> result = db.parse_identifier('pets') - >>> (result.owner, result.type, result.name) - ('%', '%', 'pets') + >>> (result['object_type'], result['name1'], result['name2']) + (None, 'pets', '') >>> result = db.parse_identifier('pe*') - >>> (result.owner, result.type, result.name) + >>> (result['object_type'], result['name1'], result['name2']) ('%', '%', 'pe%') >>> result = db.parse_identifier('scott/table/pets') - >>> (result.owner, result.type, result.name) + >>> (result['object_type'], result['name1'], result['name2']) ('scott', 'table', 'pets') >>> result = db.parse_identifier('table/scott.pets') - >>> (result.owner, result.type, result.name) + >>> (result['object_type'], result['name1'], result['name2']) ('scott', 'table', 'pets') >>> result = db.parse_identifier('') - >>> (result.owner, result.type, result.name) + >>> (result['object_type'], result['name1'], result['name2']) ('%', '%', '%') >>> result = db.parse_identifier('table/scott.*') - >>> (str(result.owner), str(result.type), str(result.name)) + >>> (result['object_type'], result['name1'], result['name2']) ('scott', 'table', '%') """ - identifier = self.sql_format_wildcards(identifier) - result = {'owner': '%', 'type': '%', 'name': '%'} - result.update(dict(self.ls_parser.parseString(identifier))) - return result - dotsplitter = re.compile(r'([a-zA-Z$_#]+)([./]([a-zA-Z$_#]+))?') - def parse_identifier(self, identifier): object_type = None for otype in self.gerald_types: stripped = re.search('$' + otype + '\b(.*)', identifier, re.IGNORECASE) @@ -212,9 +209,8 @@ identifier = stripped.groups(0) object_type = otype break - match = self.dotsplitter.search(identifier) - return {'object_type': object_type, 'name1': match.group(1), 'name2': match.group(3)} - + names = identifier.split('.') + return (object_type, names) def comparison_operator(self, target): if ('%' in target) or ('_' in target): @@ -224,20 +220,29 @@ return operator def sql_format_wildcards(self, target): return target.replace('*', '%').replace('?', '_') + def comparitor(self, target): + if '%' in target or '_' in target: + return 'LIKE' + else: + return '=' def objects(self, target, opts): - identifier = self.parse_identifier(target) - clauses = {'sort': 'ASC'} - if (identifier['owner'] == '%') and (not opts.all): - identifier['owner'] = self.username - for col in ('owner', 'type', 'name'): - operator = self.comparison_operator(identifier[col]) - clauses[col] = '%s %s' % (operator, self.bindSyntax(col)) + match = self.identifier_regex.search(target) + object_type = self.object_type_case(match.group('object_type') or '%') + names = [n.strip() or '%' for n in self.name_case(match.group('remainder').replace('*', '%').replace('?', '_')).split('.')] + ['%', '%'] + replacements = {'name1_comparitor': self.comparitor(names[0]), + 'name2_comparitor': self.comparitor(names[1]), + 'object_type_comparitor': self.comparitor(object_type), + 'sort_order': 'ASC', + 'all': '1 = 0'} if hasattr(opts, 'reverse') and opts.reverse: - clauses['sort'] = 'DESC' - qry = self.all_object_qry % clauses - binds = (('owner', identifier['owner']), ('type', identifier['type']), ('name', identifier['name'])) + replacements['sort'] = 'DESC' + if hasattr(opts, 'all') and opts.all: + replacements['all'] = '1 = 1' + qry = self.all_object_qry % replacements + binds = {'schema': self.name_case(self.username), 'object_type': object_type, + 'name1': names[0], 'name2': names[1]} curs = self.connection.cursor() - curs.execute(qry, self.bindVariables(binds)) + curs.execute(qry, binds) return curs def columns(self, target, opts): target = self.sql_format_wildcards(target) @@ -319,8 +324,10 @@ class PostgresInstance(DatabaseInstance): rdbms = 'postgres' default_port = 5432 - case = str.lower - object_type_case = str.upper + def name_case(self, s): + return s.lower() + def object_type_case(self, s): + return s.upper() def set_defaults(self): self.port = os.getenv('PGPORT') or self.default_port self.database = os.getenv('ORACLE_SID') @@ -359,10 +366,9 @@ default_port = 1521 connection_parser = re.compile('(?P<username>[^/\s@]*)(/(?P<password>[^/\s@]*))?(@((?P<hostname>[^/\s:]*)(:(?P<port>\d{1,4}))?/)?(?P<database>[^/\s:]*))?(\s+as\s+(?P<mode>sys(dba|oper)))?', re.IGNORECASE) - case = str.upper - database_types = ['DATABASE LINK', 'DIRECTORY', 'FUNCTION', 'INDEX', 'JOB', - 'MATERIALIZED VIEW', 'PACKAGE', 'PROCEDURE', 'SEQUENCE', - 'SYNONYM', 'TABLE', 'TRIGGER', 'TYPE', 'VIEW'] + def name_case(self, s): + return s.upper() + object_type_case = name_case def uri(self): if self.hostname: uri = '%s://%s:%s@%s:%s/%s' % (self.rdbms, self.username, self.password, @@ -392,20 +398,39 @@ return False def connect(self): self.connection = cx_Oracle.connect(user = self.username, password = self.password, - dsn = self.dsn, mode = self.mode) - all_object_qry = """SELECT owner, object_type, object_name - FROM all_objects - WHERE ( owner %(owner)s - AND object_type %(type)s - AND object_name %(name)s ) - OR - ( (owner, object_name) - IN - ( SELECT table_owner, table_name - FROM all_synonyms - WHERE synonym_name %(name)s ) - ) - ORDER BY owner, object_type, object_name %(sort)s""" + dsn = self.dsn, mode = self.mode) + all_object_qry = """SELECT * FROM ( + SELECT ao.owner, ao.object_type, ao.object_name, NULL AS synonym_name + FROM all_objects ao + WHERE ao.object_type %(object_type_comparitor)s :object_type + AND ao.owner = :schema + AND ao.object_name %(name1_comparitor)s :name1 + UNION + SELECT asyn.table_owner, ao.object_type, asyn.table_name, asyn.synonym_name + FROM all_synonyms asyn + JOIN all_objects ao ON ( asyn.table_owner = ao.owner + AND asyn.table_name = ao.object_name) + WHERE %(all)s + AND ao.object_type %(object_type_comparitor)s :object_type + AND asyn.synonym_name %(name1_comparitor)s :name1 + AND asyn.owner IN (:schema, 'PUBLIC') + UNION + SELECT ao.owner, ao.object_type, ao.object_name, NULL AS synonym_name + FROM all_objects ao + WHERE :name1 != '%%' + AND ao.object_type %(object_type_comparitor)s :object_type + AND ao.owner %(name1_comparitor)s :name1 + AND ao.object_name %(name2_comparitor)s :name2 + UNION + SELECT asyn.table_owner, ao.object_type, asyn.table_name, asyn.synonym_name + FROM all_synonyms asyn + JOIN all_objects ao ON ( asyn.table_owner = ao.owner + AND asyn.table_name = ao.object_name) + WHERE %(all)s + AND ao.object_type %(object_type_comparitor)s :object_type + AND asyn.synonym_name %(name2_comparitor)s :name2 + AND asyn.owner %(name1_comparitor)s :name1 + ) ORDER BY object_type, owner, object_name ASC""" column_qry = """SELECT atc.owner, ao.object_type, atc.table_name, atc.column_name FROM all_tab_columns atc JOIN all_objects ao ON (atc.table_name = ao.object_name AND atc.owner = ao.owner) @@ -425,7 +450,4 @@ if __name__ == '__main__': - opts = OptionTestDummy(password='password') - db = DatabaseInstance('oracle://system:twttatl@orcl', opts) - print list(db.findAll('')) - #doctest.testmod() + doctest.testmod()
--- a/sqlpython/sqlpyPlus.py Tue Sep 21 15:26:24 2010 -0400 +++ b/sqlpython/sqlpyPlus.py Wed Sep 22 15:16:33 2010 -0400 @@ -866,7 +866,7 @@ def _pull(self, arg, opts, vc=None): statekeeper = Statekeeper(opts.dump and self, ('stdout',)) try: - for (owner, object_type, name) in self.current_instance.objects(arg, opts): + for (owner, object_type, name, synonym_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'): @@ -1048,7 +1048,9 @@ 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")] - + + def object_label(self, object_type, owner, name, synonym_name): + return '%s %s.%s%s' % (object_type, owner, name, synonym_name and (synonym_name != name) and ' ("%s")' % synonym_name or '') @options(standard_options + [ make_option('-A', '--alpha', action='store_true', help='List columns alphabetically')]) def do_describe(self, arg, opts): @@ -1057,9 +1059,9 @@ sortkey = operator.itemgetter('name') else: sortkey = operator.itemgetter('sequence') - for (owner, object_type, name) in self.current_instance.objects(arg, opts): + for (owner, object_type, name, synonym_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.tblname = self.object_label(object_type, owner, name, synonym_name) self.pfeedback(self.tblname) if opts.long and hasattr(obj, 'comments') and obj.comments: self.poutput(obj.comments) @@ -1096,8 +1098,9 @@ def do_deps(self, arg, opts): '''Lists indexes, constraints, and triggers depending on an object''' #TODO: doesn't account for views; don't know about primary keys - for (owner, object_type, name) in self.current_instance.objects(arg, opts): + for (owner, object_type, name, synonym_name) in self.current_instance.objects(arg, opts): obj = self.current_instance.object_metadata(owner, object_type, name) + self.poutput(self.object_label(object_type, owner, name, synonym_name)) for deptype in ('indexes', 'constraints', 'triggers'): if hasattr(obj, deptype): for (depname, depobj) in getattr(obj, deptype).items(): @@ -1106,10 +1109,10 @@ @options([all_users_option]) def do_comments(self, arg, opts): 'Prints comments on a table and its columns.' - for (owner, object_type, name) in self.current_instance.objects(arg, opts): + for (owner, object_type, name, synonym_name) in self.current_instance.objects(arg, opts): obj = self.current_instance.object_metadata(owner, object_type, name) - if hasattr(obj, 'comments'): - self.poutput('%s %s.%s' % object_type, owner, name) + if hasattr(obj, 'comments'): + self.poutput(self.object_label(object_type, owner, name, synonym_name)) self.poutput(obj.comments) if hasattr(obj, 'columns'): columns = obj.columns.values() @@ -1286,10 +1289,10 @@ def do__dir_(self, arg, opts, plural_name, str_function): long = opts.get('long') - for (owner, object_type, name) in self.current_instance.objects(arg, opts): + for (owner, object_type, name, synonym_name) in self.current_instance.objects(arg, opts): obj = self.current_instance.object_metadata(owner, object_type, name) if hasattr(obj, plural_name): - self.pfeedback('%s on %s' % (plural_name.title(), '%s %s.%s' % (object_type, owner, name))) + self.pfeedback('%s on %s' % (plural_name.title(), self.object_label(object_type, owner, name, synonym_name))) result = [str_function(depobj, long) for depobj in getattr(obj, plural_name).values()] result.sort(reverse=bool(opts.reverse)) self.poutput('\n'.join(result)) @@ -1527,8 +1530,8 @@ def _do_ls(self, arg, opts): 'Functional core of ``do_ls``, split out into an undecorated version to be callable from other methods' - for row in self.current_instance.objects(arg, opts): - self.poutput('%s/%s/%s' % row) + for (owner, type, name, synonym_name) in self.current_instance.objects(arg, opts): + self.poutput(self.object_label(type, owner, name, synonym_name)) @options(standard_options) def do_ls(self, arg, opts): @@ -1560,9 +1563,9 @@ re_pattern = re.compile(self._to_re_wildcards(pattern), (opts.ignorecase and re.IGNORECASE) or 0) for target in targets: - for (owner, object_type, name) in self.current_instance.objects(target, opts): + for (owner, object_type, name, synonym_name) in self.current_instance.objects(target, opts): obj = self.current_instance.object_metadata(owner, object_type, name) - self.pfeedback('%s %s.%s' % (object_type, owner, name)) + self.pfeedback(self.object_label(object_type, owner, name, synonym_name)) if hasattr(obj, 'columns'): clauses = [] for col in obj.columns: