Skip to content

Feature/json object #928

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
merged 30 commits into from
Jun 17, 2019
Merged

Feature/json object #928

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
30 commits
Select commit Hold shift + click to select a range
ef1976a
Initial checkin.
lwasylow May 17, 2019
9e7b6c4
Adding json tree parsing.
lwasylow May 18, 2019
ba1392a
Update tests.
lwasylow May 21, 2019
021e48d
small fixes
lwasylow May 21, 2019
86aa49d
PHASE 2:
lwasylow May 22, 2019
8e224c0
Added more complex tests.
lwasylow May 23, 2019
1a17e7a
Update mddocs
lwasylow May 23, 2019
c16de97
Fixing sonar
lwasylow May 23, 2019
45e91e1
Switch to use supertype
lwasylow May 31, 2019
ac8ab96
PHASE3 : Adding a functionality for install in pre 12.2
lwasylow May 31, 2019
587da95
PHASE3 : Adding a functionality for install in pre 12.2
lwasylow May 31, 2019
7192a51
PHASE3 : Adding a functionality for install in pre 12.2
May 31, 2019
f475258
PHASE3 : Adding a functionality for install in pre 12.2
May 31, 2019
d48bdc2
PHASE3 : Adding a functionality for install in pre 12.2
May 31, 2019
0aa94d3
PHASE3 : Adding a functionality for install in pre 12.2
lwasylow May 31, 2019
b775475
Merge branch 'develop' of https://github.com/utPLSQL/utPLSQL into fea…
lwasylow Jun 4, 2019
a5c7ca0
update tests
lwasylow Jun 7, 2019
f6c95e5
Update set define off
lwasylow Jun 7, 2019
e87aa6b
Code cleanup
lwasylow Jun 8, 2019
78394ab
Merge branch 'develop' of github.com:utPLSQL/utPLSQL into feature/jso…
lwasylow Jun 8, 2019
273b21a
fix slash
lwasylow Jun 8, 2019
9f8cc71
Creating a dummy specs and removing conditional compilation for code …
lwasylow Jun 8, 2019
6f827ae
Merge branch 'develop' of github.com:utPLSQL/utPLSQL into feature/jso…
lwasylow Jun 11, 2019
9031856
PR fixes.
lwasylow Jun 13, 2019
7452983
Merge branch 'develop' of github.com:utPLSQL/utPLSQL into feature/jso…
lwasylow Jun 13, 2019
6dd2026
Merge branch 'develop' of github.com:utPLSQL/utPLSQL into feature/jso…
lwasylow Jun 15, 2019
ee73b69
Reverting development script changes.
jgebal Jun 15, 2019
d6a6801
Refactored JSON code.
jgebal Jun 17, 2019
6586342
Fixed install on Oracle < 12.2
jgebal Jun 17, 2019
6e9dc12
Merge branch 'develop' into feature/json_object
jgebal Jun 17, 2019
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
5 changes: 4 additions & 1 deletion docs/userguide/advanced_data_comparison.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -7,10 +7,11 @@ utPLSQL expectations incorporates advanced data comparison options when comparin
- refcursor
- object type
- nested table and varray
- json data-types

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

## Syntax
Syntax

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



247 changes: 229 additions & 18 deletions docs/userguide/expectations.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -319,7 +319,7 @@ end;
## have_count
Unary matcher that validates if the provided dataset count is equal to expected value.

Can be used with `refcursor` or `table type`
Can be used with `refcursor` , `json`or `table type`

Usage:
```sql
Expand Down Expand Up @@ -1177,7 +1177,218 @@ Finished in .048181 seconds
```



# Comparing Json objects

utPLSQL is capable of comparing json data-types on Oracle 12.2 and above.

### Notes on comparison of json data

- Json data can contain objects, scalar or arrays.
- During comparison of json objects the order doesn't matter.
- During comparison of json arrays the index of element is taken into account
- To compare json you have to make sure its type of `json_element_t` or its subtypes



Some examples of using json data-types in matcher are :

```sql
create or replace package test_expectations_json is

--%suite(json expectations)

--%test(Gives success for identical data)
procedure success_on_same_data;
end;
/

create or replace package body test_expectations_json is

procedure success_on_same_data is
l_expected json_element_t;
l_actual json_element_t;
begin
-- Arrange
l_expected := json_element_t.parse('
{
"Actors":[
{
"name":"Tom Cruise",
"age":56,
"Born At":"Syracuse, NY",
"Birthdate":"July 3, 1962",
"photo":"https://jsonformatter.org/img/tom-cruise.jpg",
"wife":null,
"weight":67.5,
"hasChildren":true,
"hasGreyHair":false,
"children":[
"Suri",
"Isabella Jane",
"Connor"
]
},
{
"name":"Robert Downey Jr.",
"age":53,
"Born At":"New York City, NY",
"Birthdate":"April 4, 1965",
"photo":"https://jsonformatter.org/img/Robert-Downey-Jr.jpg",
"wife":"Susan Downey",
"weight":77.1,
"hasChildren":true,
"hasGreyHair":false,
"children":[
"Indio Falconer",
"Avri Roel",
"Exton Elias"
]
}
]
}');

l_actual := json_element_t.parse('
{
"Actors":[
{
"name":"Tom Cruise",
"age":56,
"Born At":"Syracuse, NY",
"Birthdate":"July 3, 1962",
"photo":"https://jsonformatter.org/img/tom-cruise.jpg",
"wife":null,
"weight":67.5,
"hasChildren":true,
"hasGreyHair":false,
"children":[
"Suri",
"Isabella Jane",
"Connor"
]
},
{
"name":"Robert Downey Jr.",
"age":53,
"Born At":"New York City, NY",
"Birthdate":"April 4, 1965",
"photo":"https://jsonformatter.org/img/Robert-Downey-Jr.jpg",
"wife":"Susan Downey",
"weight":77.1,
"hasChildren":true,
"hasGreyHair":false,
"children":[
"Indio Falconer",
"Avri Roel",
"Exton Elias"
]
}
]
}');

ut3.ut.expect( l_actual ).to_equal( l_actual );

end;
end;
/
```

It is possible to use a PL/SQL to extract a piece of JSON and compare it as follow

```sql
create or replace package test_expectations_json is

--%suite(json expectations)

--%test(Gives success for identical pieces of two different jsons)
procedure to_diff_json_extract_same;

end;
/

create or replace package body test_expectations_json is

procedure to_diff_json_extract_same as
l_expected json_object_t;
l_actual json_object_t;
l_array_actual json_array_t;
l_array_expected json_array_t;
begin
-- Arrange
l_expected := json_object_t.parse(' {
"Actors": [
{
"name": "Tom Cruise",
"age": 56,
"Born At": "Syracuse, NY",
"Birthdate": "July 3, 1962",
"photo": "https://jsonformatter.org/img/tom-cruise.jpg",
"wife": null,
"weight": 67.5,
"hasChildren": true,
"hasGreyHair": false,
"children": [
"Suri",
"Isabella Jane",
"Connor"
]
},
{
"name": "Robert Downey Jr.",
"age": 53,
"Born At": "New York City, NY",
"Birthdate": "April 4, 1965",
"photo": "https://jsonformatter.org/img/Robert-Downey-Jr.jpg",
"wife": "Susan Downey",
"weight": 77.1,
"hasChildren": true,
"hasGreyHair": false,
"children": [
"Indio Falconer",
"Avri Roel",
"Exton Elias"
]
}
]
}'
);

l_actual := json_object_t.parse(' {
"Actors":
{
"name": "Krzystof Jarzyna",
"age": 53,
"Born At": "Szczecin",
"Birthdate": "April 4, 1965",
"photo": "niewidzialny",
"wife": "Susan Downey",
"children": [
"Indio Falconer",
"Avri Roel",
"Exton Elias"
]
}
}'
);

l_array_actual := json_array_t(json_query(l_actual.stringify,'$.Actors.children'));
l_array_expected := json_array_t(json_query(l_expected.stringify,'$.Actors[1].children'));
--Act
ut3.ut.expect(l_array_actual).to_equal(l_array_expected);

end;
end;
/
```







# Negating a matcher

Expectations provide a very convenient way to perform a check on a negated matcher.

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

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

| 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 |
| :---------------------- | :--: | :-----: | :--: | :--: | :----: | :-------: | :---------------------------: | :------------------------------------: | :------: | :-----------------------------: | :-----------------------------: | :----: | :-------------------------: | :----: |
| **be_not_null** | X | X | X | X | X | X | X | X | X | X | X | X | X | X |
| **be_null** | X | X | X | X | X | X | X | X | X | X | X | X | X | X |
| **be_false** | | X | | | | | | | | | | | | |
| **be_true** | | X | | | | | | | | | | | | |
| **be_greater_than** | | | | X | X | X | X | X | | X | X | | | |
| **be_greater_or_equal** | | | | X | X | X | X | X | | X | X | | | |
| **be_less_or_equal** | | | | X | X | X | X | X | | X | X | | | |
| **be_less_than** | | | | X | X | X | X | X | | X | X | | | |
| **be_between** | | | | X | X | X | X | X | X | X | X | | | |
| **equal** | X | X | X | X | X | X | X | X | X | X | X | X | X | X |
| **contain** | | | | | | | | | | | | X | X | X |
| **match** | | | X | | | | | | X | | | | | |
| **be_like** | | | X | | | | | | X | | | | | |
| **be_empty** | X | | X | | | | | | | | | X | X | |
| **have_count** | | | | | | | | | | | | X | X | |
| 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 |
| :---------------------: | :--: | :-----: | :--: | :--: | :----: | :-------: | :---------------------------: | :------------------------------------: | :------: | :-----------------------------: | :-----------------------------: | :----: | :-------------------------: | :----: | :--: |
| **be_not_null** | X | X | X | X | X | X | X | X | X | X | X | X | X | X | X |
| **be_null** | X | X | X | X | X | X | X | X | X | X | X | X | X | X | X |
| **be_false** | | X | | | | | | | | | | | | | |
| **be_true** | | X | | | | | | | | | | | | | |
| **be_greater_than** | | | | X | X | X | X | X | | X | X | | | | |
| **be_greater_or_equal** | | | | X | X | X | X | X | | X | X | | | | |
| **be_less_or_equal** | | | | X | X | X | X | X | | X | X | | | | |
| **be_less_than** | | | | X | X | X | X | X | | X | X | | | | |
| **be_between** | | | | X | X | X | X | X | X | X | X | | | | |
| **equal** | X | X | X | X | X | X | X | X | X | X | X | X | X | X | X |
| **contain** | | | | | | | | | | | | X | X | X | |
| **match** | | | X | | | | | | X | | | | | | |
| **be_like** | | | X | | | | | | X | | | | | | |
| **be_empty** | X | | X | | | | | | | | | X | X | | X |
| **have_count** | | | | | | | | | | | | X | X | | X |

5 changes: 5 additions & 0 deletions source/api/ut.pkb
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -93,6 +93,11 @@ create or replace package body ut is
return ut_expectation(ut_data_value_dsinterval(a_actual), a_message);
end;

function expect(a_actual in json_element_t , a_message varchar2 := null) return ut_expectation_json is
begin
return ut_expectation_json(ut_data_value_json(a_actual), a_message);
end;

procedure fail(a_message in varchar2) is
begin
ut_expectation_processor.report_failure(a_message);
Expand Down
2 changes: 2 additions & 0 deletions source/api/ut.pks
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -45,6 +45,8 @@ create or replace package ut authid current_user as

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

function expect(a_actual in json_element_t , a_message varchar2 := null) return ut_expectation_json;

procedure fail(a_message in varchar2);

function run(
Expand Down
2 changes: 1 addition & 1 deletion source/core/ut_suite_manager.pkb
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -916,7 +916,7 @@ create or replace package body ut_suite_manager is
where exists (
select 1
from ]'||l_ut_owner||q'[.ut_suite_cache c
where 1 = 1 ]'||case when can_skip_all_objects_scan(l_owner_name) then q'[
where 1 = 1 ]'||case when not can_skip_all_objects_scan(l_owner_name) then q'[
and exists
( select 1
from all_objects a
Expand Down
1 change: 1 addition & 0 deletions source/core/ut_utils.pkb
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -492,6 +492,7 @@ create or replace package body ut_utils is
begin
execute immediate 'delete from ut_compound_data_tmp';
execute immediate 'delete from ut_compound_data_diff_tmp';
execute immediate 'delete from ut_json_data_diff_tmp';
end;

function to_version(a_version_no varchar2) return t_version is
Expand Down
2 changes: 2 additions & 0 deletions source/create_synonyms_and_grants_for_public.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -33,6 +33,7 @@ alter session set current_schema = &&ut3_owner;

grant execute on &&ut3_owner..ut_expectation to public;
grant execute on &&ut3_owner..ut_expectation_compound to public;
grant execute on &&ut3_owner..ut_expectation_json to public;
grant execute on &&ut3_owner..ut_be_between to public;
grant execute on &&ut3_owner..ut_be_empty to public;
grant execute on &&ut3_owner..ut_be_false to public;
Expand Down Expand Up @@ -113,6 +114,7 @@ prompt Creating synonyms for UTPLSQL objects in &&ut3_owner schema to PUBLIC

create public synonym ut_expectation for &&ut3_owner..ut_expectation;
create public synonym ut_expectation_compound for &&ut3_owner..ut_expectation_compound;
create public synonym ut_expectation_json for &&ut3_owner..ut_expectation_json;

create public synonym be_between for &&ut3_owner..be_between;
create public synonym be_empty for &&ut3_owner..be_empty;
Expand Down
1 change: 1 addition & 0 deletions source/create_user_grants.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -53,6 +53,7 @@ alter session set current_schema = &&ut3_owner;

grant execute on &&ut3_owner..ut_expectation to &ut3_user;
grant execute on &&ut3_owner..ut_expectation_compound to &ut3_user;
grant execute on &&ut3_owner..ut_expectation_json to &ut3_user;

grant execute on &&ut3_owner..ut_be_between to &ut3_user;
grant execute on &&ut3_owner..ut_be_empty to &ut3_user;
Expand Down
1 change: 1 addition & 0 deletions source/create_user_synonyms.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -55,6 +55,7 @@ prompt Creating synonyms for UTPLSQL objects in &&ut3_owner schema to user &&ut3

create or replace synonym &ut3_user..ut_expectation for &&ut3_owner..ut_expectation;
create or replace synonym &ut3_user..ut_expectation_compound for &&ut3_owner..ut_expectation_compound;
create or replace synonym &ut3_user..ut_expectation_json for &&ut3_owner..ut_expectation_json;

create or replace synonym &ut3_user..be_between for &&ut3_owner..be_between;
create or replace synonym &ut3_user..be_empty for &&ut3_owner..be_empty;
Expand Down
Loading