Skip to content

Commit 7011174

Browse files
committed
refactorings of subprogram resolving
1 parent 445eda7 commit 7011174

1 file changed

Lines changed: 40 additions & 72 deletions

File tree

‎plsql/client.py‎

Lines changed: 40 additions & 72 deletions
Original file line numberDiff line numberDiff line change
@@ -42,11 +42,11 @@ def __init__(self, plsql, name, owner, object_id, subprogram_id):
4242
self.name = name
4343

4444
binds = {'owner': self.owner, 'object_id': self.object_id, 'subprogram_id': self.subprogram_id}
45-
self.definition = plsql.query(sql_query=self.subprogram_sql, bind_variables=binds).first
45+
self.definition = plsql.query(sql_query=self.subprogram_sql, binds=binds).first
4646

4747
binds = {'owner': self.owner, 'object_name': self.definition.object_name,
4848
'object_id': self.object_id, 'subprogram_id': self.subprogram_id}
49-
self.arguments = list(plsql.query(sql_query=self.argument_sql, bind_variables=binds))
49+
self.arguments = list(plsql.query(sql_query=self.argument_sql, binds=binds))
5050

5151
try:
5252
self.return_type = next(
@@ -149,83 +149,47 @@ def oracle_table(argument, list_of_recs):
149149
raise NotImplementedError(f'Unrecognized object_type "{object_type}"!')
150150

151151

152-
def resolve_subprogram(attributes, parameters, plsql):
153-
assert attributes, 'Attributes should not be empty!'
154-
152+
def retrieve_subprograms(attributes, plsql):
155153
attribute_len = len(attributes)
156-
if attribute_len > 3:
157-
raise ValueError('Attribute access is too deep, maximum of 3 allowed! (schema, package, subprogram)')
158154

159-
if attribute_len == 3:
160-
schema, package, subprogram = attributes
161-
subprogram_sql = f'''
162-
SELECT owner, object_id, subprogram_id
163-
FROM all_procedures
164-
WHERE owner = UPPER(:owner)
165-
AND object_name = UPPER(:name)
166-
AND procedure_name = UPPER(:procedure_name)
167-
AND object_type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE')
168-
'''
169-
170-
binds = {'owner': schema, 'name': package, 'procedure_name': subprogram}
171-
matching_subprograms = list(plsql.query(subprogram_sql, binds))
172-
if len(matching_subprograms) > 1:
173-
raise NotImplementedError('Can not resolve to single subprogram!')
155+
base_query = '''
156+
SELECT owner, object_id, subprogram_id
157+
FROM all_procedures
158+
WHERE object_type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE') AND {extra_where_predicates}
159+
'''
174160

175-
if not matching_subprograms:
176-
raise AttributeError('No such subprogram exists!')
177-
elif attribute_len == 2:
178-
# todo: at some point use to refine search due to possibility of overloaded subprograms in package
179-
_ = parameters
161+
def query_subprograms(**kwargs):
162+
extra_where_predicates = (f'{key} = UPPER(:{key})' for key in kwargs.keys())
163+
query_sql = base_query.format(extra_where_predicates=' AND '.join(extra_where_predicates))
164+
return list(plsql.query(query_sql, binds=kwargs))
180165

181-
# search for subprogram assuming package name is provided
166+
if attribute_len == 3:
167+
owner, object_name, procedure_name = attributes
168+
return query_subprograms(owner=owner, object_name=object_name, procedure_name=procedure_name)
169+
elif attribute_len == 2:
182170
object_name, procedure_name = attributes
183-
subprogram_sql = f'''
184-
SELECT owner, object_id, subprogram_id
185-
FROM all_procedures
186-
WHERE object_name = UPPER(:name)
187-
AND procedure_name = UPPER(:procedure_name)
188-
AND object_type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE')
189-
'''
190-
binds = {'name': object_name, 'procedure_name': procedure_name}
191-
matching_subprograms = list(plsql.query(subprogram_sql, binds))
192-
193-
if len(matching_subprograms) > 1:
194-
raise NotImplementedError('Can not resolve to single subprogram!')
195-
196-
# if fails then must be standalone and schema is provided
171+
172+
matching_subprograms = query_subprograms(object_name=object_name, procedure_name=procedure_name)
173+
197174
if not matching_subprograms:
198-
subprogram_sql = f'''
199-
SELECT owner, object_id, subprogram_id
200-
FROM all_procedures
201-
WHERE owner = UPPER(:owner)
202-
AND object_name = UPPER(:name)
203-
AND object_type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE')
204-
'''
205-
owner, object_name = attributes
206-
binds = {'owner': owner, 'name': object_name}
207-
matching_subprograms = list(plsql.query(subprogram_sql, binds))
208-
if len(matching_subprograms) > 1:
209-
raise NotImplementedError('Can not resolve to single subprogram!')
210-
211-
if not matching_subprograms:
212-
raise AttributeError('No such subprogram exists!')
175+
return query_subprograms(owner=object_name, object_name=procedure_name)
176+
return matching_subprograms
213177
else:
214-
subprogram_sql = f'''
215-
SELECT owner, object_id, subprogram_id
216-
FROM all_procedures
217-
WHERE object_name = UPPER(:name)
218-
AND object_type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE')
219-
'''
220-
subprogram, = attributes
178+
object_name, = attributes
179+
return query_subprograms(object_name=object_name)
221180

222-
matching_subprograms = list(plsql.query(subprogram_sql, {'name': subprogram}))
223181

224-
if not matching_subprograms:
225-
raise AttributeError('No such subprogram exists!')
182+
def resolve_subprogram(attributes, parameters, plsql):
183+
assert attributes, 'Attributes should not be empty!'
184+
_ = parameters
185+
186+
matching_subprograms = retrieve_subprograms(attributes, plsql)
226187

227-
if len(matching_subprograms) > 1:
228-
raise NotImplementedError('Can not resolve to single subprogram!')
188+
if len(matching_subprograms) > 1:
189+
raise NotImplementedError('Can not resolve to single subprogram!')
190+
191+
if not matching_subprograms:
192+
raise AttributeError('No such subprogram exists!')
229193

230194
return matching_subprograms[0]
231195

@@ -235,6 +199,9 @@ def __init__(self, plsql, attr):
235199
self.plsql, self.attributes = plsql, [attr]
236200

237201
def __getattr__(self, item):
202+
if len(self.attributes) >= 3:
203+
raise ValueError('Attribute access is too deep, maximum of 3 allowed! (schema, package, subprogram)')
204+
238205
self.attributes.append(item)
239206
return self
240207

@@ -280,14 +247,15 @@ class Database:
280247

281248
def __init__(self, user, password, host, port, service_name, encoding):
282249
dsn = oracle.makedsn(host, port, service_name=service_name)
283-
self.connection = oracle.connect(user=user, password=password, dsn=dsn, encoding=encoding, nencoding=encoding)
250+
self.connection = oracle.connect(user=user, password=password, dsn=dsn, encoding=encoding,
251+
nencoding=encoding)
284252

285253
def execute_immediate(self, dynamic_string):
286254
with self.connection.cursor() as cursor:
287255
cursor.execute(dynamic_string)
288256

289-
def query(self, sql_query, bind_variables=None):
290-
return Query(self.connection, sql_query, bind_variables)
257+
def query(self, sql_query, binds=None):
258+
return Query(self.connection, sql_query, binds)
291259

292260
def __getattr__(self, item):
293261
return AttributeWalker(self, item)

0 commit comments

Comments
 (0)