Skip to content

Commit ba2e765

Browse files
lwasylowlwasylow
authored
Feature/json object (#928)
* Initial checkin. General skelet of code * Adding json tree parsing. Enabling 12.2 * Update tests. Update sonar rules. * small fixes * PHASE 2: Moving diff into temp table and init data compare during compare implementation to avoid finding same differences twice Adding have count and negated have count matcher.l * Added more complex tests. Added test when extract a piece from two different jsons. Small fixes to code on same hierarchy parent arrays * Update mddocs Fix to json traverse array when object is element of array, the element name is content of object which can be different order but same semantic content. * Fixing sonar * Switch to use supertype * PHASE3 : Adding a functionality for install in pre 12.2 * PHASE3 : Adding a functionality for install in pre 12.2 * PHASE3 : Adding a functionality for install in pre 12.2 * PHASE3 : Adding a functionality for install in pre 12.2 * PHASE3 : Adding a functionality for install in pre 12.2 * PHASE3 : Adding a functionality for install in pre 12.2 * update tests * Update set define off * Code cleanup * fix slash * Creating a dummy specs and removing conditional compilation for code cleanup * PR fixes. * Reverting development script changes. * Refactored JSON code. Changed formatting of JSON-diff output. Changed date formats. * Fixed install on Oracle < 12.2
1 parent 1205ff4 commit ba2e765

32 files changed

+2898
-32
lines changed

‎docs/userguide/advanced_data_comparison.md‎

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,10 +7,11 @@ utPLSQL expectations incorporates advanced data comparison options when comparin
77
- refcursor
88
- object type
99
- nested table and varray
10+
- json data-types
1011

1112
Advanced data-comparison options are available for the [`equal`](expectations.md#equal) and [`contain`](expectations.md#include--contain) matcher.
1213

13-
## Syntax
14+
Syntax
1415

1516
```
1617
ut.expect( a_actual {data-type} ).to_( equal( a_expected {data-type})[.extendend_option()[.extendend_option()[...]]]);
@@ -503,3 +504,5 @@ Finished in .046193 seconds
503504
1 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)
504505
```
505506

507+
508+

‎docs/userguide/expectations.md‎

Lines changed: 229 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -319,7 +319,7 @@ end;
319319
## have_count
320320
Unary matcher that validates if the provided dataset count is equal to expected value.
321321

322-
Can be used with `refcursor` or `table type`
322+
Can be used with `refcursor` , `json`or `table type`
323323

324324
Usage:
325325
```sql
@@ -1177,7 +1177,218 @@ Finished in .048181 seconds
11771177
```
11781178

11791179

1180+
1181+
# Comparing Json objects
1182+
1183+
utPLSQL is capable of comparing json data-types on Oracle 12.2 and above.
1184+
1185+
### Notes on comparison of json data
1186+
1187+
- Json data can contain objects, scalar or arrays.
1188+
- During comparison of json objects the order doesn't matter.
1189+
- During comparison of json arrays the index of element is taken into account
1190+
- To compare json you have to make sure its type of `json_element_t` or its subtypes
1191+
1192+
1193+
1194+
Some examples of using json data-types in matcher are :
1195+
1196+
```sql
1197+
create or replace package test_expectations_json is
1198+
1199+
--%suite(json expectations)
1200+
1201+
--%test(Gives success for identical data)
1202+
procedure success_on_same_data;
1203+
end;
1204+
/
1205+
1206+
create or replace package body test_expectations_json is
1207+
1208+
procedure success_on_same_data is
1209+
l_expected json_element_t;
1210+
l_actual json_element_t;
1211+
begin
1212+
-- Arrange
1213+
l_expected := json_element_t.parse('
1214+
{
1215+
"Actors":[
1216+
{
1217+
"name":"Tom Cruise",
1218+
"age":56,
1219+
"Born At":"Syracuse, NY",
1220+
"Birthdate":"July 3, 1962",
1221+
"photo":"https://jsonformatter.org/img/tom-cruise.jpg",
1222+
"wife":null,
1223+
"weight":67.5,
1224+
"hasChildren":true,
1225+
"hasGreyHair":false,
1226+
"children":[
1227+
"Suri",
1228+
"Isabella Jane",
1229+
"Connor"
1230+
]
1231+
},
1232+
{
1233+
"name":"Robert Downey Jr.",
1234+
"age":53,
1235+
"Born At":"New York City, NY",
1236+
"Birthdate":"April 4, 1965",
1237+
"photo":"https://jsonformatter.org/img/Robert-Downey-Jr.jpg",
1238+
"wife":"Susan Downey",
1239+
"weight":77.1,
1240+
"hasChildren":true,
1241+
"hasGreyHair":false,
1242+
"children":[
1243+
"Indio Falconer",
1244+
"Avri Roel",
1245+
"Exton Elias"
1246+
]
1247+
}
1248+
]
1249+
}');
1250+
1251+
l_actual := json_element_t.parse('
1252+
{
1253+
"Actors":[
1254+
{
1255+
"name":"Tom Cruise",
1256+
"age":56,
1257+
"Born At":"Syracuse, NY",
1258+
"Birthdate":"July 3, 1962",
1259+
"photo":"https://jsonformatter.org/img/tom-cruise.jpg",
1260+
"wife":null,
1261+
"weight":67.5,
1262+
"hasChildren":true,
1263+
"hasGreyHair":false,
1264+
"children":[
1265+
"Suri",
1266+
"Isabella Jane",
1267+
"Connor"
1268+
]
1269+
},
1270+
{
1271+
"name":"Robert Downey Jr.",
1272+
"age":53,
1273+
"Born At":"New York City, NY",
1274+
"Birthdate":"April 4, 1965",
1275+
"photo":"https://jsonformatter.org/img/Robert-Downey-Jr.jpg",
1276+
"wife":"Susan Downey",
1277+
"weight":77.1,
1278+
"hasChildren":true,
1279+
"hasGreyHair":false,
1280+
"children":[
1281+
"Indio Falconer",
1282+
"Avri Roel",
1283+
"Exton Elias"
1284+
]
1285+
}
1286+
]
1287+
}');
1288+
1289+
ut3.ut.expect( l_actual ).to_equal( l_actual );
1290+
1291+
end;
1292+
end;
1293+
/
1294+
```
1295+
1296+
It is possible to use a PL/SQL to extract a piece of JSON and compare it as follow
1297+
1298+
```sql
1299+
create or replace package test_expectations_json is
1300+
1301+
--%suite(json expectations)
1302+
1303+
--%test(Gives success for identical pieces of two different jsons)
1304+
procedure to_diff_json_extract_same;
1305+
1306+
end;
1307+
/
1308+
1309+
create or replace package body test_expectations_json is
1310+
1311+
procedure to_diff_json_extract_same as
1312+
l_expected json_object_t;
1313+
l_actual json_object_t;
1314+
l_array_actual json_array_t;
1315+
l_array_expected json_array_t;
1316+
begin
1317+
-- Arrange
1318+
l_expected := json_object_t.parse(' {
1319+
"Actors": [
1320+
{
1321+
"name": "Tom Cruise",
1322+
"age": 56,
1323+
"Born At": "Syracuse, NY",
1324+
"Birthdate": "July 3, 1962",
1325+
"photo": "https://jsonformatter.org/img/tom-cruise.jpg",
1326+
"wife": null,
1327+
"weight": 67.5,
1328+
"hasChildren": true,
1329+
"hasGreyHair": false,
1330+
"children": [
1331+
"Suri",
1332+
"Isabella Jane",
1333+
"Connor"
1334+
]
1335+
},
1336+
{
1337+
"name": "Robert Downey Jr.",
1338+
"age": 53,
1339+
"Born At": "New York City, NY",
1340+
"Birthdate": "April 4, 1965",
1341+
"photo": "https://jsonformatter.org/img/Robert-Downey-Jr.jpg",
1342+
"wife": "Susan Downey",
1343+
"weight": 77.1,
1344+
"hasChildren": true,
1345+
"hasGreyHair": false,
1346+
"children": [
1347+
"Indio Falconer",
1348+
"Avri Roel",
1349+
"Exton Elias"
1350+
]
1351+
}
1352+
]
1353+
}'
1354+
);
1355+
1356+
l_actual := json_object_t.parse(' {
1357+
"Actors":
1358+
{
1359+
"name": "Krzystof Jarzyna",
1360+
"age": 53,
1361+
"Born At": "Szczecin",
1362+
"Birthdate": "April 4, 1965",
1363+
"photo": "niewidzialny",
1364+
"wife": "Susan Downey",
1365+
"children": [
1366+
"Indio Falconer",
1367+
"Avri Roel",
1368+
"Exton Elias"
1369+
]
1370+
}
1371+
}'
1372+
);
1373+
1374+
l_array_actual := json_array_t(json_query(l_actual.stringify,'$.Actors.children'));
1375+
l_array_expected := json_array_t(json_query(l_expected.stringify,'$.Actors[1].children'));
1376+
--Act
1377+
ut3.ut.expect(l_array_actual).to_equal(l_array_expected);
1378+
1379+
end;
1380+
end;
1381+
/
1382+
```
1383+
1384+
1385+
1386+
1387+
1388+
1389+
11801390
# Negating a matcher
1391+
11811392
Expectations provide a very convenient way to perform a check on a negated matcher.
11821393

11831394
Syntax to check for matcher evaluating to true:
@@ -1211,21 +1422,21 @@ Since NULL is neither *true* nor *false*, both expectations will report failure.
12111422

12121423
The matrix below illustrates the data types supported by different matchers.
12131424

1214-
| Matcher | blob | boolean | clob | date | number | timestamp | timestamp<br>with<br>timezone | timestamp<br>with<br>local<br>timezone | varchar2 | interval<br>year<br>to<br>month | interval<br>day<br>to<br>second | cursor | nested<br>table<br>/ varray | object |
1215-
| :---------------------- | :--: | :-----: | :--: | :--: | :----: | :-------: | :---------------------------: | :------------------------------------: | :------: | :-----------------------------: | :-----------------------------: | :----: | :-------------------------: | :----: |
1216-
| **be_not_null** | X | X | X | X | X | X | X | X | X | X | X | X | X | X |
1217-
| **be_null** | X | X | X | X | X | X | X | X | X | X | X | X | X | X |
1218-
| **be_false** | | X | | | | | | | | | | | | |
1219-
| **be_true** | | X | | | | | | | | | | | | |
1220-
| **be_greater_than** | | | | X | X | X | X | X | | X | X | | | |
1221-
| **be_greater_or_equal** | | | | X | X | X | X | X | | X | X | | | |
1222-
| **be_less_or_equal** | | | | X | X | X | X | X | | X | X | | | |
1223-
| **be_less_than** | | | | X | X | X | X | X | | X | X | | | |
1224-
| **be_between** | | | | X | X | X | X | X | X | X | X | | | |
1225-
| **equal** | X | X | X | X | X | X | X | X | X | X | X | X | X | X |
1226-
| **contain** | | | | | | | | | | | | X | X | X |
1227-
| **match** | | | X | | | | | | X | | | | | |
1228-
| **be_like** | | | X | | | | | | X | | | | | |
1229-
| **be_empty** | X | | X | | | | | | | | | X | X | |
1230-
| **have_count** | | | | | | | | | | | | X | X | |
1425+
| Matcher | blob | boolean | clob | date | number | timestamp | timestamp<br>with<br>timezone | timestamp<br>with<br>local<br>timezone | varchar2 | interval<br>year<br>to<br>month | interval<br>day<br>to<br>second | cursor | nested<br>table<br>/ varray | object | json |
1426+
| :---------------------: | :--: | :-----: | :--: | :--: | :----: | :-------: | :---------------------------: | :------------------------------------: | :------: | :-----------------------------: | :-----------------------------: | :----: | :-------------------------: | :----: | :--: |
1427+
| **be_not_null** | X | X | X | X | X | X | X | X | X | X | X | X | X | X | X |
1428+
| **be_null** | X | X | X | X | X | X | X | X | X | X | X | X | X | X | X |
1429+
| **be_false** | | X | | | | | | | | | | | | | |
1430+
| **be_true** | | X | | | | | | | | | | | | | |
1431+
| **be_greater_than** | | | | X | X | X | X | X | | X | X | | | | |
1432+
| **be_greater_or_equal** | | | | X | X | X | X | X | | X | X | | | | |
1433+
| **be_less_or_equal** | | | | X | X | X | X | X | | X | X | | | | |
1434+
| **be_less_than** | | | | X | X | X | X | X | | X | X | | | | |
1435+
| **be_between** | | | | X | X | X | X | X | X | X | X | | | | |
1436+
| **equal** | X | X | X | X | X | X | X | X | X | X | X | X | X | X | X |
1437+
| **contain** | | | | | | | | | | | | X | X | X | |
1438+
| **match** | | | X | | | | | | X | | | | | | |
1439+
| **be_like** | | | X | | | | | | X | | | | | | |
1440+
| **be_empty** | X | | X | | | | | | | | | X | X | | X |
1441+
| **have_count** | | | | | | | | | | | | X | X | | X |
12311442

‎source/api/ut.pkb‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -93,6 +93,11 @@ create or replace package body ut is
9393
return ut_expectation(ut_data_value_dsinterval(a_actual), a_message);
9494
end;
9595

96+
function expect(a_actual in json_element_t , a_message varchar2 := null) return ut_expectation_json is
97+
begin
98+
return ut_expectation_json(ut_data_value_json(a_actual), a_message);
99+
end;
100+
96101
procedure fail(a_message in varchar2) is
97102
begin
98103
ut_expectation_processor.report_failure(a_message);

‎source/api/ut.pks‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -45,6 +45,8 @@ create or replace package ut authid current_user as
4545

4646
function expect(a_actual in dsinterval_unconstrained, a_message varchar2 := null) return ut_expectation;
4747

48+
function expect(a_actual in json_element_t , a_message varchar2 := null) return ut_expectation_json;
49+
4850
procedure fail(a_message in varchar2);
4951

5052
function run(

‎source/core/ut_suite_manager.pkb‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -916,7 +916,7 @@ create or replace package body ut_suite_manager is
916916
where exists (
917917
select 1
918918
from ]'||l_ut_owner||q'[.ut_suite_cache c
919-
where 1 = 1 ]'||case when can_skip_all_objects_scan(l_owner_name) then q'[
919+
where 1 = 1 ]'||case when not can_skip_all_objects_scan(l_owner_name) then q'[
920920
and exists
921921
( select 1
922922
from all_objects a

‎source/core/ut_utils.pkb‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -492,6 +492,7 @@ create or replace package body ut_utils is
492492
begin
493493
execute immediate 'delete from ut_compound_data_tmp';
494494
execute immediate 'delete from ut_compound_data_diff_tmp';
495+
execute immediate 'delete from ut_json_data_diff_tmp';
495496
end;
496497

497498
function to_version(a_version_no varchar2) return t_version is

‎source/create_synonyms_and_grants_for_public.sql‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -33,6 +33,7 @@ alter session set current_schema = &&ut3_owner;
3333

3434
grant execute on &&ut3_owner..ut_expectation to public;
3535
grant execute on &&ut3_owner..ut_expectation_compound to public;
36+
grant execute on &&ut3_owner..ut_expectation_json to public;
3637
grant execute on &&ut3_owner..ut_be_between to public;
3738
grant execute on &&ut3_owner..ut_be_empty to public;
3839
grant execute on &&ut3_owner..ut_be_false to public;
@@ -113,6 +114,7 @@ prompt Creating synonyms for UTPLSQL objects in &&ut3_owner schema to PUBLIC
113114

114115
create public synonym ut_expectation for &&ut3_owner..ut_expectation;
115116
create public synonym ut_expectation_compound for &&ut3_owner..ut_expectation_compound;
117+
create public synonym ut_expectation_json for &&ut3_owner..ut_expectation_json;
116118

117119
create public synonym be_between for &&ut3_owner..be_between;
118120
create public synonym be_empty for &&ut3_owner..be_empty;

‎source/create_user_grants.sql‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,7 @@ alter session set current_schema = &&ut3_owner;
5353

5454
grant execute on &&ut3_owner..ut_expectation to &ut3_user;
5555
grant execute on &&ut3_owner..ut_expectation_compound to &ut3_user;
56+
grant execute on &&ut3_owner..ut_expectation_json to &ut3_user;
5657

5758
grant execute on &&ut3_owner..ut_be_between to &ut3_user;
5859
grant execute on &&ut3_owner..ut_be_empty to &ut3_user;

‎source/create_user_synonyms.sql‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -55,6 +55,7 @@ prompt Creating synonyms for UTPLSQL objects in &&ut3_owner schema to user &&ut3
5555

5656
create or replace synonym &ut3_user..ut_expectation for &&ut3_owner..ut_expectation;
5757
create or replace synonym &ut3_user..ut_expectation_compound for &&ut3_owner..ut_expectation_compound;
58+
create or replace synonym &ut3_user..ut_expectation_json for &&ut3_owner..ut_expectation_json;
5859

5960
create or replace synonym &ut3_user..be_between for &&ut3_owner..be_between;
6061
create or replace synonym &ut3_user..be_empty for &&ut3_owner..be_empty;

0 commit comments

Comments
 (0)