Skip to content

Commit ed4e215

Browse files
committed
extending functionality just a bit more before starting anew
1 parent 6d40979 commit ed4e215

6 files changed

Lines changed: 116 additions & 73 deletions

File tree

‎plsql/client.py‎

Lines changed: 80 additions & 54 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,5 @@
1+
from __future__ import annotations
2+
13
import datetime
24
from collections import namedtuple
35
from itertools import starmap, chain
@@ -10,9 +12,53 @@
1012
PACKAGE = "PACKAGE"
1113

1214

15+
def parse_in_out(parameters):
16+
return {
17+
param_name: param_value.getvalue() for param_name, param_value in parameters
18+
}
19+
20+
21+
def oracle_plsql_record(connection, argument, mapping, record_type=None):
22+
if record_type is None:
23+
record_type = connection.gettype(argument.extended_type.upper())
24+
record = record_type.newobject()
25+
26+
for key, value in mapping.items():
27+
setattr(record, key.upper(), value)
28+
return record
29+
30+
31+
def oracle_plsql_table(connection, argument, mapping_of_recs):
32+
table_type = connection.gettype(argument.extended_type)
33+
table = table_type.newobject()
34+
35+
if table_type.elementType is not None:
36+
for key, value in mapping_of_recs.items():
37+
rec = oracle_plsql_record(connection, None, value, table_type.elementType)
38+
table.setelement(key, rec)
39+
return table
40+
41+
return mapping_of_recs
42+
43+
44+
def translate_types(connection, arguments, parameters, conversion_func, data_type):
45+
return {
46+
argument.argument_name.lower(): conversion_func(
47+
connection, argument, parameters[argument.argument_name.lower()]
48+
)
49+
for argument in arguments
50+
if "IN" in argument.in_out
51+
if argument.data_type == data_type
52+
}
53+
54+
1355
class Subprogram:
14-
argument_sql = f"""
15-
SELECT argument_name, data_type, defaulted, in_out, type_owner || '.' || type_name || '.' || type_subname extended_type
56+
argument_sql = """
57+
SELECT argument_name,
58+
data_type,
59+
defaulted,
60+
in_out,
61+
type_owner || '.' || type_name || '.' || type_subname extended_type
1662
FROM all_arguments
1763
WHERE owner = UPPER(:owner)
1864
AND object_name = :object_name
@@ -79,12 +125,6 @@ def __call__(self, **kwargs):
79125
pass
80126
kwargs[name] = parameter
81127

82-
def parse_in_out(parameters):
83-
return {
84-
param_name: param_value.getvalue()
85-
for param_name, param_value in parameters
86-
}
87-
88128
object_type = self.definition.object_type
89129

90130
if object_type == PACKAGE:
@@ -93,44 +133,13 @@ def parse_in_out(parameters):
93133
else:
94134
object_type = PROCEDURE
95135

96-
def oracle_record(argument, mapping, record_type=None):
97-
if record_type is None:
98-
record_type = self.plsql.connection.gettype(
99-
argument.extended_type.upper()
100-
)
101-
record = record_type.newobject()
102-
103-
for key, value in mapping.items():
104-
setattr(record, key.upper(), value)
105-
return record
106-
107-
def oracle_table(argument, list_of_recs):
108-
table_type = self.plsql.connection.gettype(argument.extended_type)
109-
table = table_type.newobject()
110-
111-
if table_type.elementType is not None:
112-
for mapping in list_of_recs:
113-
rec = oracle_record(None, mapping, table_type.elementType)
114-
table.append(rec)
115-
return table
116-
117-
return list_of_recs
118-
119-
def translate_types(conversion_func, data_type):
120-
return {
121-
argument.argument_name.lower(): conversion_func(
122-
argument, kwargs[argument.argument_name.lower()]
123-
)
124-
for argument in self.arguments
125-
if "IN" in argument.in_out
126-
if argument.data_type == data_type
127-
}
128-
129136
translated_types = chain.from_iterable(
130-
translate_types(func, data_type).items()
137+
translate_types(
138+
self.plsql.connection, self.arguments, kwargs, func, data_type
139+
).items()
131140
for func, data_type in [
132-
(oracle_record, "PL/SQL RECORD"),
133-
(oracle_table, "PL/SQL TABLE"),
141+
(oracle_plsql_record, "PL/SQL RECORD"),
142+
(oracle_plsql_table, "PL/SQL TABLE"),
134143
]
135144
)
136145

@@ -152,6 +161,8 @@ def translate_types(conversion_func, data_type):
152161
attributes = result_list[0].type.attributes
153162
except IndexError:
154163
result = []
164+
except AttributeError:
165+
result = result_list
155166
else:
156167
result = [
157168
{
@@ -167,7 +178,6 @@ def translate_types(conversion_func, data_type):
167178
attribute.name.lower(): getattr(result, attribute.name)
168179
for attribute in attributes
169180
}
170-
171181
except AttributeError:
172182
pass
173183

@@ -187,10 +197,14 @@ def retrieve_subprograms(attributes, plsql):
187197
attribute_len = len(attributes)
188198

189199
base_query = """
190-
SELECT owner, object_id, subprogram_id, NVL(procedure_name, object_name) object_name, object_type
191-
FROM all_procedures
192-
WHERE object_type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE') AND {extra_where_predicates}
193-
"""
200+
SELECT owner,
201+
object_id,
202+
subprogram_id,
203+
NVL(procedure_name, object_name) object_name,
204+
object_type
205+
FROM all_procedures
206+
WHERE object_type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE') AND {extra_where_predicates}
207+
"""
194208

195209
def query_subprograms(**kwargs):
196210
extra_where_predicates = (f"{key} = UPPER(:{key})" for key in kwargs.keys())
@@ -221,8 +235,12 @@ def query_subprograms(**kwargs):
221235

222236
def match_parameters(subprograms, parameters, plsql):
223237
# fixme: this sql is essentially the same as in when building the Subprogram
224-
argument_sql = f"""
225-
SELECT argument_name, data_type, defaulted, in_out, type_owner || '.' || type_name || '.' || type_subname extended_type
238+
argument_sql = """
239+
SELECT argument_name,
240+
data_type,
241+
defaulted,
242+
in_out,
243+
type_owner || '.' || type_name || '.' || type_subname extended_type
226244
FROM all_arguments
227245
WHERE owner = UPPER(:owner)
228246
AND object_name = :object_name
@@ -319,17 +337,25 @@ def __iter__(self):
319337

320338

321339
class Database:
322-
def __init__(self, user, password, host, port, service_name, encoding):
340+
def __init__(
341+
self,
342+
user: str,
343+
password: str,
344+
host: str,
345+
port: int,
346+
service_name: str,
347+
encoding: str,
348+
):
323349
dsn = oracle.makedsn(host, port, service_name=service_name)
324350
self.connection = oracle.connect(
325351
user=user, password=password, dsn=dsn, encoding=encoding, nencoding=encoding
326352
)
327353

328-
def execute_immediate(self, dynamic_string):
354+
def execute_immediate(self, dynamic_string: str):
329355
with self.connection.cursor() as cursor:
330356
cursor.execute(dynamic_string)
331357

332-
def query(self, sql_query, binds=None):
358+
def query(self, sql_query: str, binds=None):
333359
return Query(self.connection, sql_query, binds)
334360

335361
def __getattr__(self, item):
Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,10 +1,10 @@
11
CREATE OR REPLACE PACKAGE test_overload AS
22

3-
FUNCTION simple_function(p_int IN INTEGER)
4-
RETURN INTEGER;
5-
63
FUNCTION simple_function(p_int IN INTEGER,
74
p_divide IN BOOLEAN)
85
RETURN INTEGER;
96

7+
FUNCTION simple_function(p_int IN INTEGER)
8+
RETURN INTEGER;
9+
1010
END test_overload;

‎tests/sql/package/test_package-b.sql‎

Lines changed: 7 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,11 @@
11
CREATE OR REPLACE PACKAGE BODY test_package AS
22

3+
FUNCTION out_integers(p_integer IN INTEGER)
4+
RETURN tt_integers IS
5+
BEGIN
6+
RETURN tt_integers(1, 2, p_integer);
7+
END;
8+
39
FUNCTION return_record
410
RETURN tr_rec IS
511
vr_rec tr_rec;
@@ -59,7 +65,7 @@ CREATE OR REPLACE PACKAGE BODY test_package AS
5965
v_result INTEGER := 0;
6066
BEGIN
6167
FOR i IN pt_rec.FIRST .. pt_rec.LAST LOOP
62-
v_result := v_result + (pt_rec(i).int_1 * pt_rec(i).int_2 * pt_rec(i).int_3 * pt_rec(i).int_4);
68+
v_result := v_result + compute_rec_product(pr_rec => pt_rec(i));
6369
END LOOP;
6470
RETURN v_result;
6571
EXCEPTION

‎tests/sql/package/test_package-h.sql‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,8 @@
11
CREATE OR REPLACE PACKAGE test_package AS
22
TYPE tt_stringlist IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
33

4+
TYPE tt_integers IS TABLE OF INTEGER;
5+
46
TYPE tr_rec IS RECORD (
57
int_1 INTEGER,
68
int_2 INTEGER,
@@ -10,6 +12,11 @@ CREATE OR REPLACE PACKAGE test_package AS
1012

1113
TYPE tt_rec IS TABLE OF tr_rec INDEX BY BINARY_INTEGER;
1214

15+
"g_int" INTEGER := 5;
16+
17+
FUNCTION out_integers(p_integer IN INTEGER)
18+
RETURN tt_integers;
19+
1320
FUNCTION return_record
1421
RETURN tr_rec;
1522

‎tests/test_functions.py‎

Lines changed: 13 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -18,15 +18,15 @@ def test_function_with_defaults(plsql):
1818

1919

2020
def test_string_function(plsql):
21-
assert plsql.string_function(p_str='test') == 'test' * 2
21+
assert plsql.string_function(p_str="test") == "test" * 2
2222

2323

2424
def test_function_in_out(plsql):
25-
result, in_out = plsql.function_in_out(p_int=21, p_float=1337.42, p_str='input')
25+
result, in_out = plsql.function_in_out(p_int=21, p_float=1337.42, p_str="input")
2626

2727
assert result == 42
28-
assert in_out['p_str'] == 'input: 42'
29-
assert in_out['p_float'] == 10.5
28+
assert in_out["p_str"] == "input: 42"
29+
assert in_out["p_float"] == 10.5
3030

3131

3232
def test_datetime_func(plsql):
@@ -36,22 +36,22 @@ def test_datetime_func(plsql):
3636

3737
result, in_out = plsql.datetime_func(p_date=date)
3838
assert result == ten_days_ahead
39-
assert in_out['p_date'] == ten_days_ahead
39+
assert in_out["p_date"] == ten_days_ahead
4040

4141

4242
def test_function_clob(plsql):
43-
clob = '1'
43+
clob = "1"
4444
result, in_out = plsql.function_clob(p_clob=clob)
45-
result, in_out['p_clob'] = result.read(), in_out['p_clob'].read()
45+
result, in_out["p_clob"] = result.read(), in_out["p_clob"].read()
4646

47-
assert len(result) == len(in_out['p_clob']) == 1000
48-
assert type(clob) == type(result) == type(in_out['p_clob'])
47+
assert len(result) == len(in_out["p_clob"]) == 1000
48+
assert type(clob) == type(result) == type(in_out["p_clob"])
4949

5050

5151
def test_function_blob(plsql):
52-
blob = b'1'
52+
blob = b"1"
5353
result, in_out = plsql.function_blob(p_blob=blob)
54-
result, in_out['p_blob'] = result.read(), in_out['p_blob'].read()
54+
result, in_out["p_blob"] = result.read(), in_out["p_blob"].read()
5555

56-
assert len(result) == len(in_out['p_blob']) == 1000
57-
assert type(blob) == type(result) == type(in_out['p_blob'])
56+
assert len(result) == len(in_out["p_blob"]) == 1000
57+
assert type(blob) == type(result) == type(in_out["p_blob"])

‎tests/test_package_type.py‎

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -30,11 +30,15 @@ def test_rec_type(plsql):
3030
assert result == reduce(mul, record.values())
3131

3232

33-
def test_rec_table(plsql):
33+
def test_rec_plsql_table(plsql):
3434
record = {"int_1": 1, "int_2": 2, "int_3": 3, "int_4": 4}
3535

36-
table = [record] * 100
36+
table = {i: record for i in range(1, 101)}
3737

3838
result = plsql.test_package.compute_rec_table(pt_rec=table)
3939

4040
assert result == reduce(mul, record.values()) * 100
41+
42+
43+
def test_nested_table(plsql):
44+
assert plsql.test_package.out_integers(p_integer=5) == [1, 2, 5]

0 commit comments

Comments
 (0)