8000 Merge pull request #1181 from utPLSQL/feature/1114_support_native_jso… · utPLSQL/utPLSQL@ab1acfa · GitHub
[go: up one dir, main page]
Skip to content

Commit ab1acfa

Browse files
lwasylowlwasylow
authored
Merge pull request #1181 from utPLSQL/feature/1114_support_native_json_21c
Support native JSON data type of Oracle Database 21c
2 parents 02bf599 + e44830b commit ab1acfa

16 files changed

+295
-17
lines changed

‎.github/workflows/build.yml

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -153,6 +153,7 @@ jobs:
153153

154154
- name: SonarCloud Scan
155155
id: sonar
156+
if: ${{ matrix.db_version_name == '21xe' }}
156157
uses: SonarSource/sonarcloud-github-action@master
157158
env:
158159
GITHUB_TOKEN: ${{ secrets.GITHUB_TOKEN }} # Needed to get PR information, if any

‎docs/userguide/expectations.md

Lines changed: 8 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1749,18 +1749,23 @@ FAILURE
17491749
17501750
# Comparing Json objects
17511751
1752-
utPLSQL is capable of comparing json data-types **on Oracle 12.2 and above**.
1752+
utPLSQL is capable of comparing json data-types of `json_element_t` **on Oracle 12.2 and above**, and also `json` **on Oracle 21 and above**
1753+
1754+
**Note:**
1755+
> Whenever a database is upgraded to compatible version the utPLSQL needs to be reinstalled to pick up json changes. E.g. upgrade from 18c to 21c to enable `json` type compare.
1756+
17531757
17541758
### Notes on comparison of json data
17551759
17561760
- Json data can contain objects, scalar or arrays.
17571761
- During comparison of json objects the order doesn't matter.
17581762
- During comparison of json arrays the index of element is taken into account
17591763
- To compare json you have to make sure its type of `json_element_t` or its subtypes
1764+
- From version 21 and above a native `json` type is supported.
17601765

17611766

17621767

1763-
Compare JSON example:
1768+
Compare JSON example using `json_element_t`:
17641769
```sql
17651770
declare
17661771
l_expected json_element_t;
@@ -1842,7 +1847,7 @@ FAILURE
18421847
at "anonymous block", line 59
18431848
```
18441849
1845-
Comparing parts of JSON example:
1850+
Comparing parts of JSON example using `json_element_t` subtypes:
18461851
```sql
18471852
declare
18481853
l_actual json_object_t;

‎source/api/ut.pkb

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -98,6 +98,11 @@ create or replace package body ut is
9898
return ut_expectation_json(ut_data_value_json(a_actual), a_message);
9999
end;
100100

101+
function expect(a_actual in json , a_message varchar2 := null) return ut_expectation_json is
102+
begin
103+
return ut_expectation_json(ut_data_value_json(a_actual), a_message);
104+
end;
105+
101106
procedure fail(a_message in varchar2) is
102107
begin
103108
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
@@ -47,6 +47,8 @@ create or replace package ut authid current_user as
4747

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

50+
function expect(a_actual in json , a_message varchar2 := null) return ut_expectation_json;
51+
5052
procedure fail(a_message in varchar2);
5153

5254
function run(

‎source/expectations/data_values/ut_compound_data_helper.pkb

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -785,6 +785,15 @@ create or replace package body ut_compound_data_helper is
785785
return l_diffs;
786786
end;
787787

788+
function get_json_object(a_json_t json) return json_element_t is
789+
l_obj json_element_t;
790+
begin
791+
$if dbms_db_version.version >= 21 $then
792+
l_obj := case when a_json_t is null then cast (null as json_element_t ) else json_element_t.parse(json_query(a_json_t, '$' returning clob)) end;
793+
$end
794+
return l_obj;
795+
end;
796+
788797
begin
789798
g_anytype_name_map(dbms_types.typecode_date) := 'DATE';
790799
g_anytype_name_map(dbms_types.typecode_number) := 'NUMBER';

‎source/expectations/data_values/ut_compound_data_helper.pks

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -134,5 +134,7 @@ create or replace package ut_compound_data_helper authid definer is
134134

135135
function get_json_diffs_type(a_diff_id raw) return tt_json_diff_type_tab;
136136

137+
function get_json_object(a_json_t json) return json_element_t;
138+
137139
end;
138140
/

‎source/expectations/data_values/ut_data_value_json.tpb

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -32,6 +32,13 @@ create or replace type body ut_data_value_json as
3232
return;
3333
end;
3434

35+
constructor function ut_data_value_json(self in out nocopy ut_data_value_json, a_value json) return self as result is
36+
l_value json_element_t := ut_compound_data_helper.get_json_object(a_value);
37+
begin
38+
init(l_value);
39+
return;
40+
end;
41+
3542
overriding member function is_null return boolean is
3643
begin
3744
return (ut_utils.int_to_boolean(self.is_data_null));
@@ -140,7 +147,7 @@ create or replace type body ut_data_value_json as
140147
l_result :=
141148
case
142149
when ut_compound_data_helper.insert_json_diffs(
143-
l_diff_id, self.json_tree.json_tree_info, l_other.json_tree.json_tree_info
150+
l_diff_id, l_other.json_tree.json_tree_info, self.json_tree.json_tree_info
144151
) > 0 then 1
145152
else 0
146153
end;

‎source/expectations/data_values/ut_data_value_json.tps

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -19,6 +19,7 @@ create or replace type ut_data_value_json under ut_compound_data_value(
1919
json_tree ut_json_tree_details,
2020
member procedure init (self in out nocopy ut_data_value_json, a_value json_element_t),
2121
constructor function ut_data_value_json(self in out nocopy ut_data_value_json, a_value json_element_t) return self as result,
22+
constructor function ut_data_value_json(self in out nocopy ut_data_value_json, a_value json) return self as result,
2223
overriding member function is_null return boolean,
2324
overriding member function is_empty return boolean,
2425
overriding member function to_string return varchar2,

‎source/expectations/json_objects_specs.sql

Lines changed: 12 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,8 +1,18 @@
11
BEGIN
22

3-
$if dbms_db_version.version = 12 and dbms_db_version.release >= 2 or dbms_db_version.version > 12 $then
3+
$if dbms_db_version.version >= 21 $then
44
dbms_output.put_line('Object exists , dont install');
5-
$else
5+
$elsif dbms_db_version.version = 12 and dbms_db_version.release >= 2 or ( dbms_db_version.version > 12 and dbms_db_version.version < 21 ) $then
6+
dbms_output.put_line('Installing json structures specs for native json.');
7+
execute immediate q'[create or replace TYPE JSON FORCE AUTHID CURRENT_USER AS OBJECT(
8+
dummyobjt NUMBER
9+
) NOT FINAL NOT INSTANTIABLE;]';
10+
$else
11+
dbms_output.put_line('Installing json structures specs for native json.');
12+
execute immediate q'[create or replace TYPE JSON FORCE AUTHID CURRENT_USER AS OBJECT(
13+
dummyobjt NUMBER
14+
) NOT FINAL NOT INSTANTIABLE;]';
15+
616
dbms_output.put_line('Installing json structures specs.');
717
execute immediate q'[create or replace TYPE JSON_Element_T FORCE AUTHID CURRENT_USER AS OBJECT(
818
dummyobjt NUMBER,

‎source/expectations/matchers/ut_equal.tpb

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -158,6 +158,12 @@ create or replace type body ut_equal as
158158
return;
159159
end;
160160

161+
constructor function ut_equal(self in out nocopy ut_equal, a_expected json, a_nulls_are_equal boolean := null) return self as result is
162+
begin
163+
init(ut_data_value_json(a_expected), a_nulls_are_equal);
164+
return;
165+
end;
166+
161167
member function include(a_items varchar2) return ut_equal is
162168
l_result ut_equal := self;
163169
begin

‎source/expectations/matchers/ut_equal.tps

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -42,6 +42,7 @@ create or replace type ut_equal force under ut_comparison_matcher(
4242
constructor function ut_equal(self in out nocopy ut_equal, a_expected yminterval_unconstrained, a_nulls_are_equal boolean := null) return self as result,
4343
constructor function ut_equal(self in out nocopy ut_equal, a_expected dsinterval_unconstrained, a_nulls_are_equal boolean := null) return self as result,
4444
constructor function ut_equal(self in out nocopy ut_equal, a_expected json_element_t, a_nulls_are_equal boolean := null) return self as result,
45+
constructor function ut_equal(self in out nocopy ut_equal, a_expected json, a_nulls_are_equal boolean := null) return self as result,
4546
member function include(a_items varchar2) return ut_equal,
4647
member function include(a_items ut_varchar2_list) return ut_equal,
4748
member procedure include(self in ut_equal, a_items varchar2),

‎source/expectations/ut_expectation.tpb

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -176,6 +176,11 @@ create or replace type body ut_expectation as
176176
self.to_( ut_equal(a_expected, a_nulls_are_equal) );
177177
end;
178178

179+
member procedure to_equal(self in ut_expectation, a_expected json, a_nulls_are_equal boolean := null) is
180+
begin
181+
self.to_( ut_equal(a_expected, a_nulls_are_equal) );
182+
end;
183+
179184
member procedure not_to_equal(self in ut_expectation, a_expected anydata, a_nulls_are_equal boolean := null) is
180185
begin
181186
self.not_to( ut_equal(a_expected, a_nulls_are_equal) );

‎source/expectations/ut_expectation.tps

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -49,6 +49,7 @@ create or replace type ut_expectation force under ut_expectation_base(
4949
member procedure to_equal(self in ut_expectation, a_expected yminterval_unconstrained, a_nulls_are_equal boolean := null),
5050
member procedure to_equal(self in ut_expectation, a_expected dsinterval_unconstrained, a_nulls_are_equal boolean := null),
5151
member procedure to_equal(self in ut_expectation, a_expected json_element_t, a_nulls_are_equal boolean := null),
52+
member procedure to_equal(self in ut_expectation, a_expected json, a_nulls_are_equal boolean := null),
5253

5354
member procedure not_to_equal(self in ut_expectation, a_expected anydata, a_nulls_are_equal boolean := null),
5455
member procedure not_to_equal(self in ut_expectation, a_expected anydata, a_exclude varchar2, a_nulls_are_equal boolean := null),

‎source/expectations/ut_expectation_json.tps

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -24,4 +24,4 @@ create or replace type ut_expectation_json under ut_expectation(
2424
member procedure to_have_count(self in ut_expectation_json, a_expected integer),
2525
member procedure not_to_have_count(self in ut_expectation_json, a_expected integer)
2626
)
27-
/
27+
/

0 commit comments

Comments
 (0)
0