Mercurial > sqlpython
changeset 490:2d1235fe1252
most oracle refactors done
| author | catherine.devlin@gmail.com |
|---|---|
| date | Tue, 07 Sep 2010 03:01:41 -0400 |
| parents | 79bb13962a15 |
| children | d30471cc95ac |
| files | sqlpython/connections.py sqlpython/sqlpyPlus.py |
| diffstat | 2 files changed, 93 insertions(+), 106 deletions(-) [+] |
line wrap: on
line diff
--- a/sqlpython/connections.py Tue Sep 07 01:18:45 2010 -0400 +++ b/sqlpython/connections.py Tue Sep 07 03:01:41 2010 -0400 @@ -196,16 +196,19 @@ result = {'owner': '%', 'type': '%', 'name': '%'} result.update(dict(self.ls_parser.parseString(identifier))) return result + def comparison_operator(self, target): + if ('%' in target) or ('_' in target): + operator = 'LIKE' + else: + operator = '=' + return operator 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' - else: - operator = '=' + operator = self.comparison_operator(identifier[col]) clause = '%s %s' % (operator, self.bindSyntax(col)) clauses.append(clause) if hasattr(opts, 'reverse') and opts.reverse: @@ -214,9 +217,30 @@ sort_direction = 'ASC' clauses.append(sort_direction) qry = self.all_object_qry % tuple(clauses) - identifier = self.bindVariables(identifier) - result = self.connection.cursor().execute(qry, self.bindVariables(identifier)) + binds = (('owner', identifier['owner']), ('type', identifier['type']), ('name', identifier['name'])) + result = self.connection.cursor().execute(qry, self.bindVariables(binds)) return result + def columns(self, target, opts): + if opts.all: + owner = '%' + else: + owner = self.username + qry = self.column_qry % (self.comparison_operator(owner), self.bindSyntax('owner'), + self.comparison_operator(target), self.bindSyntax('colname')) + binds = (('owner', owner), ('colname', target)) + result = self.connection.cursor().execute(qry, self.bindVariables(binds)) + return result + def source(self, target, opts): + if opts.all: + owner = '%' + else: + owner = self.username + qry = self.source_qry % (self.comparison_operator(owner), self.bindSyntax('owner'), + self.bindSyntax('target')) + binds = (('owner', owner), ('target', target)) + result = self.connection.cursor().execute(qry, self.bindVariables(binds)) + return result + gerald_types = {'TABLE': gerald.oracle_schema.Table, 'VIEW': gerald.oracle_schema.View} def object_metadata(self, owner, object_type, name): @@ -257,12 +281,23 @@ port = self.port, sql_mode = 'ANSI') def bindSyntax(self, varname): return '%s' - def bindVariables(self, identifier): - return (identifier['owner'], identifier['type'], identifier['name']) + def bindVariables(self, binds): + 'Puts a tuple of (name, value) pairs into the bind format desired by MySQL' + return (i[1] for i in binds) + 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) + WHERE owner %s %s + AND column_name %s %s """ + source_qry = """SELECT owner, type, name, line, text + FROM all_source + WHERE owner %s %s + AND UPPER(text) LIKE %s""" class PostgresInstance(DatabaseInstance): rdbms = 'postgres' default_port = 5432 + case = str.lower def set_defaults(self): self.port = os.getenv('PGPORT') or self.default_port self.database = os.getenv('ORACLE_SID') @@ -273,9 +308,10 @@ password = self.password, database = self.database, port = self.port) def bindSyntax(self, varname): - return '%%(%s)s' % varname.lower() - def bindVariables(self, identifier): - return identifier + return '%%(%s)s' % varname + def bindVariables(self, binds): + 'Puts a tuple of (name, value) pairs into the bind format desired by psycopg2' + return dict((b[0], b[1].lower()) for b in binds) all_object_qry = """SELECT table_schema, table_type, table_name FROM ( SELECT table_schema, table_type, table_name @@ -283,16 +319,25 @@ UNION ALL SELECT table_schema, 'view', table_name FROM information_schema.views ) - WHERE ( (table_schema %s) OR (table_schema = 'PUBLIC') ) + 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""" - + column_qry = """SELECT table_schema, object_type, table_name, column_name + FROM information_schema.columns + WHERE ( (table_schema %s %s) OR (table_schema = 'public')) + AND column_name %s %s """ + source_qry = """SELECT owner, type, name, line, text + FROM all_source + WHERE owner %s %s + AND UPPER(text) LIKE %s""" + class OracleInstance(DatabaseInstance): rdbms = 'oracle' 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 def uri(self): if self.hostname: uri = '%s://%s:%s@%s:%s/%s' % (self.rdbms, self.username, self.password, @@ -329,12 +374,20 @@ AND object_type %s AND object_name %s ORDER BY owner, object_type, object_name %s""" + 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) + WHERE atc.owner %s %s + AND atc.column_name %s %s """ + source_qry = """SELECT owner, type, name, line, text + FROM all_source + WHERE owner %s %s + AND UPPER(text) LIKE %s""" def bindSyntax(self, varname): return ':' + varname - def bindVariables(self, identifier): - return {'owner': identifier['owner'].upper(), - 'type': identifier['type'].upper(), - 'name': identifier['name'].upper()} + def bindVariables(self, binds): + 'Puts a tuple of (name, value) pairs into the bind format desired by cx_Oracle' + return dict((b[0], b[1].upper()) for b in binds) if __name__ == '__main__': opts = OptionTestDummy(password='password')
--- a/sqlpython/sqlpyPlus.py Tue Sep 07 01:18:45 2010 -0400 +++ b/sqlpython/sqlpyPlus.py Tue Sep 07 03:01:41 2010 -0400 @@ -878,14 +878,6 @@ else: txt = 'REMARK BEGIN %s/%s/%s\n%s\nREMARK END\n' % (owner, object_type, name, txt) self.poutput(txt) - - - """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() statekeeper.restore() @@ -1018,26 +1010,13 @@ ]) def do_find(self, arg, opts): """Finds argument in source code or (with -c) in column definitions.""" - - seek = re.compile(self._regex_form_of_search_pattern(arg, exact=opts.col), - re.IGNORECASE) - qualified = opts.get('all') - for descrip in self._matching_database_objects('*', opts): - if opts.col: - if hasattr(descrip.dbobj, 'columns'): - for col in descrip.dbobj.columns: - if seek.search(col): - self.poutput('%s.%s' % (m.fullname, col)) - else: - if hasattr(descrip.dbobj, 'source'): - name_printed = False - for (line_num, line) in descrip.dbobj.source: - if seek.search(line): - if not name_printed: - self.poutput(descrip.fullname) - name_printed = True - self.poutput('%d: %s' % (line_num, line)) - + if opts.col: + for (owner, object_type, table_name, column_name) in self.current_instance.columns(arg, opts): + self.poutput('%s %s.%s.%s' % (object_type, owner, table_name, column_name)) + else: + for (owner, object_type, name, line_number, txt) in self.current_instance.source(arg, opts): + self.poutput('%s %s.%s %d: %s' % (object_type, owner, name, line_number, txt)) + def _col_type_descriptor(self, col): #if col['type'] in ('integer',): # return col['type'] @@ -1299,12 +1278,12 @@ def do__dir_(self, arg, opts, plural_name, str_function): long = opts.get('long') - reverse = opts.get('reverse') or False - for descrip in self._matching_database_objects(arg, opts): - if hasattr(descrip.dbobj, plural_name): - self.pfeedback('%s on %s' % (plural_name.title(), descrip.fullname)) - result = [str_function(depobj, long) for depobj in getattr(descrip.dbobj, plural_name).values()] - result.sort(reverse=opts.get('reverse') or False) + for (owner, object_type, 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))) + result = [str_function(depobj, long) for depobj in getattr(obj, plural_name).values()] + result.sort(reverse=bool(opts.reverse)) self.poutput('\n'.join(result)) @options(standard_options) @@ -1530,14 +1509,7 @@ 'WINDOW GROUP', 'XML SCHEMA') - def metadata(self): - username = self.current_instance.username - if self.rdbms == 'oracle': - username = username.upper() - elif self.rdbms == 'postgres': - username = username.lower() - return (username, self.current_instance.gerald) - + def _to_sql_wildcards(self, original): return original.replace('*','%').replace('?','_') #hmm... but these should be escape-able? @@ -1545,46 +1517,7 @@ def _to_re_wildcards(self, original): result = re.escape(original) return result.replace('\\*','.*').replace('\\?','.') - - def _regex_form_of_search_pattern(self, s, exact=False): - if not s: - return '^[^\.]*$' - s = s.replace('$','\$') # not re.escape(s) b/c ``?`` is valid in SQL and regex - if '.' in s: - seekpatt = r'[/\\]?%s[/\\]?' % ( - s.replace('*', '.*').replace('?','.').replace('%', '.*')) - else: - seekpatt = r'[/\\]?%s[/\\]?' % ( - s.replace('*', '[^\.]*').replace('?','[^\.]').replace('%', '[^\.]*')) - if exact: - seekpatt = '^%s$' % seekpatt - return seekpatt - def do_refresh(self, arg): - '''Refreshes metadata for the specified schema; only required - if table structures, etc. have changed.''' - if self.current_instance.gerald.complete and self.current_instance.gerald.current: - self.current_instance.discover_metadata() - else: - self.pfeedback('Metadata discovery is already underway.') - - def _print_gerald_status_warning(self, gerald_schema): - if not gerald_schema.complete: - self.pfeedback('Metadata is not available yet - still gathering') - elif not gerald_schema.current: - self.pfeedback('Metadata is stale - requested refresh still underway') - - def _matching_database_objects(self, arg, opts): - (username, gerald_schema) = self.metadata() - self._print_gerald_status_warning(gerald_schema) - if not gerald_schema.complete: - raise StopIteration - - seek = str(arg) and self._regex_form_of_search_pattern(arg, opts.get('exact')) - for (name, descrip) in gerald_schema.descriptions.items(): - if descrip.match_pattern(seek, specific_owner = ((not opts.all) and username)): - yield descrip - 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): @@ -1604,8 +1537,8 @@ def do_grep(self, arg, opts): """grep {target} {table} [{table2,...}] search for {target} in any of {table}'s fields""" + # TODO: permit regex arg = self.parsed(arg) - opts.exact = True args = arg.split() if len(args) < 2: self.perror(self.do_grep.__doc__) @@ -1620,18 +1553,19 @@ re_pattern = re.compile(self._to_re_wildcards(pattern), (opts.ignorecase and re.IGNORECASE) or 0) for target in targets: - for descrip in self._matching_database_objects(target, opts): - self.pfeedback(descrip.fullname) - if hasattr(descrip.dbobj, 'columns'): + for (owner, object_type, 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)) + if hasattr(obj, 'columns'): clauses = [] - for col in descrip.dbobj.columns: + for col in obj.columns: clauses.append(comparitor % (col, sql_pattern)) - sql = "SELECT * FROM %s WHERE 1=0\n%s;" % (descrip.fullname, ' '.join(clauses)) + sql = "SELECT * FROM %s.%s WHERE 1=0\n%s;" % (owner, name, ' '.join(clauses)) sql = self.parsed(sql, terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix) self.do_select(sql) - elif hasattr(descrip.dbobj, 'source'): - for (line_num, line) in descrip.dbobj.source: + elif hasattr(obj, 'source'): + for (line_num, line) in obj.source: if re_pattern.search(line): self.poutput('%4d: %s' % (line_num, line))