Skip to content

Commit c253079

Browse files
committed
refactoring
1 parent c5a0679 commit c253079

7 files changed

Lines changed: 184 additions & 73 deletions

File tree

‎plsql/__init__.py‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,6 @@
11
from cx_Oracle import DatabaseError
22

33
from .client import Database
4+
from .client import NotFound
45

5-
__all__ = ["Database", "DatabaseError"]
6+
__all__ = ["Database", "NotFound", "DatabaseError"]

‎plsql/client.py‎

Lines changed: 64 additions & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -1,10 +1,34 @@
11
from __future__ import annotations
22

33
from collections import namedtuple
4+
from enum import IntEnum
45
from typing import Tuple, Iterator
56

67
import cx_Oracle as oracle
78

9+
Argument = namedtuple(
10+
"Argument",
11+
"overload position level argument_name datatype default_value in_out length precision scale radix",
12+
)
13+
14+
ResolvedName = namedtuple(
15+
"ResolvedName", "schema name database_link object_type object_number"
16+
)
17+
18+
19+
class ObjectTypes(IntEnum):
20+
table = 2
21+
synonym = 5
22+
sequence = 6
23+
procedure = 7
24+
function = 8
25+
package = 9
26+
type = 13
27+
28+
29+
class NotFound(Exception):
30+
pass
31+
832

933
def _dbms_describe_describe_procedure(
1034
connection: oracle.Connection, name: str
@@ -56,7 +80,7 @@ def _dbms_describe_describe_procedure(
5680
level.aslist(),
5781
argument_name.aslist(),
5882
datatype.aslist(),
59-
default_value.aslist(),
83+
map(bool, default_value.aslist()),
6084
in_out.aslist(),
6185
length.aslist(),
6286
precision.aslist(),
@@ -102,25 +126,27 @@ def _dbms_utility_name_resolve(
102126

103127
def _exhaustive_name_resolution(
104128
connection: oracle.Connection, name: str
105-
) -> Tuple[str, str, str, int]:
106-
not_found_error = None
129+
) -> Tuple[str, str, str, str, int, int]:
107130
for context in range(0, 10):
108131
try:
109-
(schema, part1, part2, _, part1_type, _,) = _dbms_utility_name_resolve(
132+
return _dbms_utility_name_resolve(
110133
connection=connection, name=name, context=context
111134
)
112135
except oracle.DatabaseError as err:
113-
if (error_code := err.args[0].code) in (4047, 6564):
114-
if error_code == 6564:
115-
not_found_error = err
136+
# ORA-04047: object specified is incompatible with the flag specified
137+
# ORA-06564: object name does not exist
138+
if err.args[0].code in (4047, 6564):
116139
continue
117140
raise
118141
else:
119142
break
120-
else:
121-
raise not_found_error
122143

123-
return schema, part1, part2, part1_type
144+
raise NotFound(f"name {name} not found")
145+
146+
147+
class Function:
148+
def __init__(self, name: ResolvedName):
149+
self._name = name
124150

125151

126152
class Database:
@@ -138,38 +164,43 @@ def __init__(
138164
user=user, password=password, dsn=dsn, encoding=encoding, nencoding=encoding
139165
)
140166

141-
def _describe_procedure(self, name: str):
142-
result = _dbms_describe_describe_procedure(self._connection, name)
167+
def __getattr__(self, item):
168+
try:
169+
name = self._name_resolve(item)
170+
except oracle.DatabaseError:
171+
raise AttributeError
143172

144-
Arguments = namedtuple(
145-
"Arguments",
146-
"overload position level argument_name datatype default_value in_out length precision scale radix",
147-
)
173+
if name.object_type == ObjectTypes.function:
174+
return Function(name)
148175

149-
return [Arguments(*argument) for argument in result]
176+
raise AttributeError
150177

151-
def _name_resolve(self, name: str):
152-
schema, part1, part2, part1_type = _exhaustive_name_resolution(
153-
connection=self._connection, name=name
154-
)
178+
def _describe_procedure(self, name: str) -> List[Argument]:
179+
result = _dbms_describe_describe_procedure(self._connection, name)
180+
181+
return [Argument(*argument) for argument in result]
155182

156-
type_mapping = {
157-
2: "TABLE",
158-
5: "SYNONYM",
159-
6: "SEQUENCE",
160-
7: "PROCEDURE",
161-
8: "FUNCTION",
162-
9: "PACKAGE",
163-
13: "TYPE",
164-
}
165-
ResolvedName = namedtuple("ResolvedName", "schema name object_type")
183+
def _name_resolve(self, name: str) -> ResolvedName:
184+
(
185+
schema,
186+
part1,
187+
part2,
188+
database_link,
189+
part1_type,
190+
object_number,
191+
) = _exhaustive_name_resolution(connection=self._connection, name=name)
166192

167193
return ResolvedName(
168194
schema,
169195
".".join([part1 or "", part2 or ""]).strip("."),
170-
type_mapping[part1_type],
196+
database_link,
197+
ObjectTypes(part1_type),
198+
object_number,
171199
)
172200

173-
def execute(self, statement: str, *args, **kwargs):
201+
def query(self, sql: str, *args, **kwargs):
202+
pass
203+
204+
def execute(self, statement: str, *args, **kwargs) -> None:
174205
with self._connection.cursor() as cursor:
175206
cursor.execute(statement, *args, **kwargs)
Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,37 @@
1+
-- noinspection SqlDeprecateTypeForFile
2+
-- noinspection SqlUnusedForFile
3+
CREATE OR REPLACE PACKAGE BODY test_describe AS
4+
5+
PROCEDURE oracle_datatypes(
6+
p_vc2 IN VARCHAR2,
7+
p_vc OUT VARCHAR,
8+
p_str IN OUT STRING,
9+
p_long IN LONG,
10+
p_rowid IN ROWID,
11+
p_chara IN CHARACTER,
12+
p_char IN CHAR,
13+
p_raw IN RAW,
14+
p_lraw IN LONG RAW,
15+
p_binint IN BINARY_INTEGER,
16+
p_plsint IN PLS_INTEGER,
17+
p_bool IN BOOLEAN,
18+
p_nat IN NATURAL,
19+
p_pos IN POSITIVE,
20+
p_posn IN POSITIVEN,
21+
p_natn IN NATURALN,
22+
p_num IN NUMBER,
23+
p_intgr IN INTEGER,
24+
p_int IN INT,
25+
p_small IN SMALLINT,
26+
p_dec IN DECIMAL,
27+
p_real IN REAL,
28+
p_float IN FLOAT,
29+
p_numer IN NUMERIC,
30+
p_dp IN DOUBLE PRECISION,
31+
p_date IN DATE,
32+
p_mls IN MLSLABEL) AS
33+
BEGIN
34+
NULL;
35+
END;
36+
37+
END test_describe;
Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,33 @@
1+
-- noinspection SqlDeprecateTypeForFile
2+
CREATE OR REPLACE PACKAGE test_describe AS
3+
4+
PROCEDURE oracle_datatypes(
5+
p_vc2 IN VARCHAR2,
6+
p_vc OUT VARCHAR,
7+
p_str IN OUT STRING,
8+
p_long IN LONG,
9+
p_rowid IN ROWID,
10+
p_chara IN CHARACTER,
11+
p_char IN CHAR,
12+
p_raw IN RAW,
13+
p_lraw IN LONG RAW,
14+
p_binint IN BINARY_INTEGER,
15+
p_plsint IN PLS_INTEGER,
16+
p_bool IN BOOLEAN,
17+
p_nat IN NATURAL,
18+
p_pos IN POSITIVE,
19+
p_posn IN POSITIVEN,
20+
p_natn IN NATURALN,
21+
p_num IN NUMBER,
22+
p_intgr IN INTEGER,
23+
p_int IN INT,
24+
p_small IN SMALLINT,
25+
p_dec IN DECIMAL,
26+
p_real IN REAL,
27+
p_float IN FLOAT,
28+
p_numer IN NUMERIC,
29+
p_dp IN DOUBLE PRECISION,
30+
p_date IN DATE,
31+
p_mls IN MLSLABEL);
32+
33+
END test_describe;

‎tests/test_describe_procedure.py‎

Lines changed: 29 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -1,29 +1,35 @@
1-
# 0 placeholder for procedures with no arguments
2-
# 1 VARCHAR, VARCHAR, STRING
3-
# 2 NUMBER, INTEGER, SMALLINT, REAL, FLOAT, DECIMAL
4-
# 3 BINARY_INTEGER, PLS_INTEGER, POSITIVE, NATURAL
5-
# 8 LONG
6-
# 11 ROWID
7-
# 12 DATE
8-
# 23 RAW
9-
# 24 LONG RAW
10-
# 58 OPAQUE TYPE
11-
# 96 CHAR (ANSI FIXED CHAR), CHARACTER
12-
# 106 MLSLABEL
13-
# 121 OBJECT
14-
# 122 NESTED TABLE
15-
# 123 VARRAY
16-
# 178 TIME
17-
# 179 TIME WITH TIME ZONE
18-
# 180 TIMESTAMP
19-
# 181 TIMESTAMP WITH TIME ZONE
20-
# 231 TIMESTAMP WITH LOCAL TIME ZONE
21-
# 250 PL/SQL RECORD
22-
# 251 PL/SQL TABLE
23-
# 252 PL/SQL BOOLEAN
1+
# 0 placeholder for procedures with no arguments -- ------||------
2+
# 1 VARCHAR, VARCHAR, STRING -- string
3+
# 2 NUMBER, INTEGER, SMALLINT, REAL, FLOAT, DECIMAL -- float
4+
# 3 BINARY_INTEGER, PLS_INTEGER, POSITIVE, NATURAL -- int
5+
# 8 LONG -- ??? str or io ???
6+
# 11 ROWID -- str
7+
# 12 DATE -- datetime.datetime
8+
# 23 RAW -- bytes
9+
# 24 LONG RAW -- ??? bytes or byte io ???
10+
# 58 OPAQUE TYPE -- ??? XMLType in Oracle
11+
# 96 CHAR (ANSI FIXED CHAR), CHARACTER -- str
12+
# 106 MLSLABEL -- ??? only backwards compatibility
13+
# 121 OBJECT -- ???
14+
# 122 NESTED TABLE -- list
15+
# 123 VARRAY -- list
16+
# 178 TIME -- datetime.datetime
17+
# 179 TIME WITH TIME ZONE -- datetime.datetime
18+
# 180 TIMESTAMP -- datetime.datetime
19+
# 181 TIMESTAMP WITH TIME ZONE -- datetime.datetime
20+
# 231 TIMESTAMP WITH LOCAL TIME ZONE -- datetime.datetime
21+
# 250 PL/SQL RECORD -- tuple - namedtuple
22+
# 251 PL/SQL TABLE -- mapping
23+
# 252 PL/SQL BOOLEAN -- bool
2424

2525

2626
def test_describe_simple_function(plsql):
2727
result = plsql._describe_procedure("test_synonym_procedure")
2828

2929
assert result is not None
30+
31+
32+
def test_describe_all_parameters(plsql):
33+
result = plsql._describe_procedure("test_describe.oracle_datatypes")
34+
35+
assert result is not None

‎tests/test_name_resolve.py‎

Lines changed: 14 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -1,29 +1,27 @@
11
import pytest
22

3-
from plsql import DatabaseError
3+
from plsql import DatabaseError, NotFound
44

55

66
@pytest.mark.parametrize(
77
"name,expected",
88
[
9-
("simple_procedure", ("TEST_USER", "SIMPLE_PROCEDURE", "PROCEDURE")),
10-
("simple_function", ("TEST_USER", "SIMPLE_FUNCTION", "FUNCTION")),
11-
("test_package", ("TEST_USER", "TEST_PACKAGE", "PACKAGE")),
12-
(
13-
"test_package.simple_procedure",
14-
("TEST_USER", "TEST_PACKAGE.SIMPLE_PROCEDURE", "PACKAGE"),
15-
),
16-
("test_table", ("TEST_USER", "TEST_TABLE", "TABLE"),),
17-
("test_sequence", ("TEST_USER", "TEST_SEQUENCE", "SEQUENCE"),),
18-
("test_type", ("TEST_USER", "TEST_TYPE", "TYPE"),),
19-
("test_synonym_procedure", ("TEST_USER", "SIMPLE_PROCEDURE", "PROCEDURE"),),
20-
("test_synonym_function", ("TEST_USER", "SIMPLE_FUNCTION", "FUNCTION"),),
9+
("simple_procedure", ("TEST_USER", "SIMPLE_PROCEDURE")),
10+
("simple_function", ("TEST_USER", "SIMPLE_FUNCTION")),
11+
("test_package", ("TEST_USER", "TEST_PACKAGE")),
12+
("test_package", ("TEST_USER", "TEST_PACKAGE"),),
13+
("test_table", ("TEST_USER", "TEST_TABLE"),),
14+
("test_sequence", ("TEST_USER", "TEST_SEQUENCE"),),
15+
("test_type", ("TEST_USER", "TEST_TYPE"),),
16+
("test_synonym_procedure", ("TEST_USER", "SIMPLE_PROCEDURE"),),
17+
("test_synonym_function", ("TEST_USER", "SIMPLE_FUNCTION"),),
2118
],
2219
)
2320
def test_names_are_resolved(plsql, name, expected):
24-
assert plsql._name_resolve(name) == expected
21+
resolved = plsql._name_resolve(name=name)
22+
assert (resolved.schema, resolved.name) == expected
2523

2624

2725
def test_nonexistent_name_raises_error(plsql):
28-
with pytest.raises(DatabaseError, match="ORA-06564"):
29-
plsql._name_resolve("aoeuqjkxpyfglrcgsnthzvwm")
26+
with pytest.raises(NotFound):
27+
plsql._name_resolve(name="aoeuqjkxpyfglrcgsnthzvwm")

‎tests/test_query.py‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,5 @@
1+
import pytest
2+
3+
4+
def test_with_no_binds(plsql):
5+
plsql.query("""SELECT 1 FROM dual""").first == 1

0 commit comments

Comments
 (0)