Mercurial > sqlpython
changeset 516:74255a272f62
working out kinks in big merge
| author | catherine.devlin@gmail.com |
|---|---|
| date | Tue, 02 Nov 2010 05:06:11 -0400 |
| parents | 86d5408e596b |
| children | |
| files | sqlpython/connections.py sqlpython/exampleSession.txt |
| diffstat | 2 files changed, 53 insertions(+), 11 deletions(-) [+] |
line wrap: on
line diff
--- a/sqlpython/connections.py Tue Nov 02 04:32:19 2010 -0400 +++ b/sqlpython/connections.py Tue Nov 02 05:06:11 2010 -0400 @@ -180,9 +180,36 @@ 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 = db.parse_identifier('pets') + >>> (result.owner, result.type, result.name) + ('%', '%', 'pets') + >>> result = db.parse_identifier('pe*') + >>> (result.owner, result.type, result.name) + ('%', '%', 'pe%') + >>> result = db.parse_identifier('scott/table/pets') + >>> (result.owner, result.type, result.name) + ('scott', 'table', 'pets') + >>> result = db.parse_identifier('table/scott.pets') + >>> (result.owner, result.type, result.name) + ('scott', 'table', 'pets') + >>> result = db.parse_identifier('') + >>> (result.owner, result.type, result.name) + ('%', '%', '%') + >>> result = db.parse_identifier('table/scott.*') + >>> (str(result.owner), str(result.type), str(result.name)) + ('scott', 'table', '%') + """ + identifier = self.sql_format_wildcards(identifier) + 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' @@ -199,6 +226,10 @@ selectors[selector + '_op'] = '=' def objects(self, target, opts): identifier = self.parse_identifier(target) + if opts.all: + identifier['allowners'] = 'all' + else: + identifier['allowners'] = '' clauses = [] if (identifier['owner'] == '%') and (not opts.all): identifier['owner'] = self.username @@ -295,7 +326,7 @@ FROM information_schema.routines r WHERE r.routine_schema %(owner_op)s %(owner)S AND UPPER(r.routine_definition) LIKE %(text)S""" - all_object_qry = """SELECT table_schema, table_type, table_name + all_object_qry = """SELECT table_schema, table_type, table_name, NULL AS synonym_name FROM information_schema.tables WHERE table_schema %(owner_op)s %(owner)S AND table_type %(type_op)s %(type)S @@ -324,7 +355,7 @@ 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 + all_object_qry = """SELECT table_schema, table_type, table_name, NULL AS synonym_name FROM information_schema.tables WHERE ( (table_schema %(owner_op)s %(owner)S) OR (table_schema = 'public') ) AND table_type %(type_op)s %(type)S @@ -378,13 +409,24 @@ 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 + 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 owner %(owner_op)s UPPER(%(owner)S) AND object_type %(type_op)s UPPER(%(type)S) AND object_name %(name_op)s UPPER(%(name)S) - ORDER BY owner, object_type, object_name %(sort_direction)s""" + 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' = %(allowners)S + AND ao.object_type %(type_op)s UPPER(%(type)S) + AND asyn.synonym_name %(name_op)s UPPER(%(name)S) + AND ( asyn.owner = 'PUBLIC' + OR asyn.owner %(owner_op)s UPPER(%(owner)S) ) + ) ORDER BY owner, object_type, object_name %(sort_direction)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)