Skip to content

Commit 0ae5b9e

Browse files
anthony-tuiningaanthony-tuininga
committed
Added test cases for session tagging.
1 parent 362aa82 commit 0ae5b9e

File tree

2 files changed

+190
-0
lines changed

2 files changed

+190
-0
lines changed

‎test/SessionPool.py‎

Lines changed: 73 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -240,6 +240,79 @@ def testHeterogeneousWrongPassword(self):
240240
self.assertRaises(cx_Oracle.DatabaseError, pool.acquire,
241241
TestEnv.GetProxyUser(), "this is the wrong password")
242242

243+
def testTaggingSession(self):
244+
"test tagging a session"
245+
pool = TestEnv.GetPool(min=2, max=8, increment=3,
246+
getmode=cx_Oracle.SPOOL_ATTRVAL_NOWAIT)
247+
248+
tagMST = "TIME_ZONE=MST"
249+
tagUTC = "TIME_ZONE=UTC"
250+
251+
conn = pool.acquire()
252+
self.assertEqual(conn.tag, None)
253+
pool.release(conn, tag=tagMST)
254+
255+
conn = pool.acquire()
256+
self.assertEqual(conn.tag, None)
257+
conn.tag = tagUTC
258+
conn.close()
259+
260+
conn = pool.acquire(tag=tagMST)
261+
self.assertEqual(conn.tag, tagMST)
262+
conn.close()
263+
264+
conn = pool.acquire(tag=tagUTC)
265+
self.assertEqual(conn.tag, tagUTC)
266+
conn.close()
267+
268+
def testPLSQLSessionCallbacks(self):
269+
"test PL/SQL session callbacks"
270+
clientVersion = cx_Oracle.clientversion()
271+
if clientVersion < (12, 2):
272+
self.skipTest("PL/SQL session callbacks not supported before 12.2")
273+
pool = TestEnv.GetPool(min=2, max=8, increment=3,
274+
getmode=cx_Oracle.SPOOL_ATTRVAL_NOWAIT,
275+
sessionCallback="pkg_SessionCallback.TheCallback")
276+
tags = ["NLS_DATE_FORMAT=SIMPLE", "NLS_DATE_FORMAT=FULL;TIME_ZONE=UTC",
277+
"NLS_DATE_FORMAT=FULL;TIME_ZONE=MST"]
278+
actualTags = [None, None, "NLS_DATE_FORMAT=FULL;TIME_ZONE=UTC"]
279+
280+
# truncate PL/SQL session callback log
281+
conn = pool.acquire()
282+
cursor = conn.cursor()
283+
cursor.execute("truncate table PLSQLSessionCallbacks")
284+
conn.close()
285+
286+
# request sessions with each of the first two tags
287+
for tag in tags[:2]:
288+
conn = pool.acquire(tag=tag)
289+
conn.close()
290+
291+
# for the last tag, use the matchanytag flag
292+
conn = pool.acquire(tag=tags[2], matchanytag=True)
293+
conn.close()
294+
295+
# verify the PL/SQL session callback log is accurate
296+
conn = pool.acquire()
297+
cursor = conn.cursor()
298+
cursor.execute("""
299+
select RequestedTag, ActualTag
300+
from PLSQLSessionCallbacks
301+
order by FixupTimestamp""")
302+
results = cursor.fetchall()
303+
expectedResults = list(zip(tags, actualTags))
304+
self.assertEqual(results, expectedResults)
305+
306+
def testTaggingInvalidKey(self):
307+
"""testTagging with Invalid key"""
308+
pool = TestEnv.GetPool(getmode=cx_Oracle.SPOOL_ATTRVAL_NOWAIT)
309+
conn = pool.acquire()
310+
self.assertRaises(TypeError, pool.release, conn, tag=12345)
311+
clientVersion = cx_Oracle.clientversion()
312+
if clientVersion >= (12, 2):
313+
self.assertRaises(cx_Oracle.DatabaseError, pool.release, conn,
314+
tag="INVALID_TAG")
315+
243316
if __name__ == "__main__":
244317
TestEnv.RunTestCases()
245318

‎test/sql/SetupTestExec.sql‎

Lines changed: 117 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -246,6 +246,13 @@ create table &main_user..TestRowids (
246246
)
247247
/
248248

249+
create table &main_user..PlsqlSessionCallbacks (
250+
RequestedTag varchar2(250),
251+
ActualTag varchar2(250),
252+
FixupTimestamp timestamp
253+
)
254+
/
255+
249256
-- create queue table and queues for testing advanced queuing
250257
begin
251258
dbms_aqadm.create_queue_table('&main_user..BOOK_QUEUE',
@@ -990,3 +997,113 @@ create or replace package body &main_user..pkg_TestRecords as
990997
end;
991998
/
992999

1000+
create or replace package &main_user..pkg_SessionCallback as
1001+
1002+
procedure TheCallback (
1003+
a_RequestedTag varchar2,
1004+
a_ActualTag varchar2
1005+
);
1006+
1007+
end;
1008+
/
1009+
1010+
create or replace package body &main_user..pkg_SessionCallback as
1011+
1012+
type udt_Properties is table of varchar2(64) index by varchar2(64);
1013+
1014+
procedure LogCall (
1015+
a_RequestedTag varchar2,
1016+
a_ActualTag varchar2
1017+
) is
1018+
pragma autonomous_transaction;
1019+
begin
1020+
insert into PlsqlSessionCallbacks
1021+
values (a_RequestedTag, a_ActualTag, systimestamp);
1022+
commit;
1023+
end;
1024+
1025+
procedure ParseProperty (
1026+
a_Property varchar2,
1027+
a_Name out nocopy varchar2,
1028+
a_Value out nocopy varchar2
1029+
) is
1030+
t_Pos number;
1031+
begin
1032+
t_Pos := instr(a_Property, '=');
1033+
if t_Pos = 0 then
1034+
raise_application_error(-20000, 'Tag must contain key=value pairs');
1035+
end if;
1036+
a_Name := substr(a_Property, 1, t_Pos - 1);
1037+
a_Value := substr(a_Property, t_Pos + 1);
1038+
end;
1039+
1040+
procedure SetProperty (
1041+
a_Name varchar2,
1042+
a_Value varchar2
1043+
) is
1044+
t_ValidValues udt_Properties;
1045+
begin
1046+
if a_Name = 'TIME_ZONE' then
1047+
t_ValidValues('UTC') := 'UTC';
1048+
t_ValidValues('MST') := '-07:00';
1049+
elsif a_Name = 'NLS_DATE_FORMAT' then
1050+
t_ValidValues('SIMPLE') := 'YYYY-MM-DD HH24:MI';
1051+
t_ValidValues('FULL') := 'YYYY-MM-DD HH24:MI:SS';
1052+
else
1053+
raise_application_error(-20000, 'Unsupported session setting');
1054+
end if;
1055+
if not t_ValidValues.exists(a_Value) then
1056+
raise_application_error(-20000, 'Unsupported session setting');
1057+
end if;
1058+
execute immediate
1059+
'ALTER SESSION SET ' || a_Name || '=''' ||
1060+
t_ValidValues(a_Value) || '''';
1061+
end;
1062+
1063+
procedure ParseTag (
1064+
a_Tag varchar2,
1065+
a_Properties out nocopy udt_Properties
1066+
) is
1067+
t_PropertyName varchar2(64);
1068+
t_PropertyValue varchar2(64);
1069+
t_StartPos number;
1070+
t_EndPos number;
1071+
begin
1072+
t_StartPos := 1;
1073+
while t_StartPos < length(a_Tag) loop
1074+
t_EndPos := instr(a_Tag, ';', t_StartPos);
1075+
if t_EndPos = 0 then
1076+
t_EndPos := length(a_Tag) + 1;
1077+
end if;
1078+
ParseProperty(substr(a_Tag, t_StartPos, t_EndPos - t_StartPos),
1079+
t_PropertyName, t_PropertyValue);
1080+
a_Properties(t_PropertyName) := t_PropertyValue;
1081+
t_StartPos := t_EndPos + 1;
1082+
end loop;
1083+
end;
1084+
1085+
procedure TheCallback (
1086+
a_RequestedTag varchar2,
1087+
a_ActualTag varchar2
1088+
) is
1089+
t_RequestedProps udt_Properties;
1090+
t_ActualProps udt_Properties;
1091+
t_PropertyName varchar2(64);
1092+
begin
1093+
LogCall(a_RequestedTag, a_ActualTag);
1094+
ParseTag(a_RequestedTag, t_RequestedProps);
1095+
ParseTag(a_ActualTag, t_ActualProps);
1096+
t_PropertyName := t_RequestedProps.first;
1097+
while t_PropertyName is not null loop
1098+
if not t_ActualProps.exists(t_PropertyName) or
1099+
t_ActualProps(t_PropertyName) !=
1100+
t_RequestedProps(t_PropertyName) then
1101+
SetProperty(t_PropertyName, t_RequestedProps(t_PropertyName));
1102+
end if;
1103+
t_PropertyName := t_RequestedProps.next(t_PropertyName);
1104+
end loop;
1105+
end;
1106+
1107+
end;
1108+
/
1109+

0 commit comments

Comments
 (0)