GoogleCloudPlatform / professional-services-data-validator

Utility to compare data between homogeneous or heterogeneous environments to ensure source and target tables match
Apache License 2.0
407 stars 119 forks source link

Add support for Oracle/PostgreSQL JSON validations #1338

Open nj1973 opened 1 day ago

nj1973 commented 1 day ago

Test case

Oracle:

CREATE TABLE dvt_test.tab_json
(
  id VARCHAR2(10) NOT NULL PRIMARY KEY
, col_json1 CLOB
, col_json2 CLOB
);
ALTER TABLE dvt_test.tab_json ADD CONSTRAINT tab_json_chk1 CHECK (col_json1 IS JSON) ENABLE;
ALTER TABLE dvt_test.tab_json ADD CONSTRAINT tab_json_chk2 CHECK (col_json2 IS JSON) ENABLE;
INSERT INTO dvt_test.tab_json VALUES (1,'{"a": 1}','{"a": 1}');
COMMIT;

PostgreSQL:

CREATE TABLE dvt_test.tab_json
(
  id varchar(10) NOT NULL PRIMARY KEY
, col_json1 json
, col_json2 jsonb
);
INSERT INTO dvt_test.tab_json VALUES (1,'{"a": 1}','{"a": 1}');

Oracle uses CLOB/NCLOB columns for JSON data, we should be able to compare these with PostgreSQL JSON columns. At a minimum, for column validation, we should be able to compare by string length like we do for some other string columns.

We need to check all validation types. Search for "issue-1338" in the repo.

We might need to revert some of the changes from https://github.com/GoogleCloudPlatform/professional-services-data-validator/issues/1335.