Run the entire test suite in Oracle on CI

[jviotti]

cc @loiclefevre

[loiclefevre]

To be adapted (Windows paths) but you can run a testsuite as following:

drop table if exists testsuite purge;

create table testsuite (
    version varchar2(1000) not null,
    file_content bfile invisible not null,
    data json
);

create or replace directory TESTSUITE_DRAFT2020_12 as 'C:\dev\projects\JSON-Schema-Test-Suite\tests\draft2020-12';

insert into TESTSUITE(version,file_content)
values ('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','additionalProperties.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','allOf.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','anchor.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','anyOf.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','boolean_schema.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','const.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','contains.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','content.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','default.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','defs.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','dependentRequired.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','dependentSchemas.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','dynamicRef.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','enum.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','exclusiveMaximum.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','exclusiveMinimum.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','format.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','if-then-else.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','infinite-loop-detection.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','items.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','maxContains.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','maximum.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','maxItems.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','maxLength.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','maxProperties.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','minContains.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','minimum.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','minItems.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','minLength.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','minProperties.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','multipleOf.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','not.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','oneOf.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','pattern.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','patternProperties.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','prefixItems.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','properties.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','propertyNames.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','ref.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','refRemote.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','required.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','type.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','unevaluatedItems.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','unevaluatedProperties.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','uniqueItems.json')),
('draft2020-12',bfilename('TESTSUITE_DRAFT2020_12','vocabulary.json'))
;

commit;

CREATE OR REPLACE function getfilecontent(p_bfile IN BFILE) return blob
AS
  l_bfile bfile;
  l_blob   BLOB;
  l_dest_offset INTEGER := 1;
  l_src_offset  INTEGER := 1;
BEGIN
  l_bfile := p_bfile;
  DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
  DBMS_LOB.CREATETEMPORARY(l_blob,false);
  IF DBMS_LOB.getlength(l_bfile) > 0 THEN
    DBMS_LOB.loadblobfromfile (
      dest_lob    => l_blob,
      src_bfile   => l_bfile,
      amount      => DBMS_LOB.lobmaxsize,
      dest_offset => l_dest_offset,
      src_offset  => l_src_offset);
  END IF;
  DBMS_LOB.fileclose(l_bfile);
  return l_blob;
END getfilecontent;
/

CREATE OR REPLACE FUNCTION getfilename (bf BFILE) RETURN VARCHAR2 is
    dir_alias VARCHAR2(255);
    file_name VARCHAR2(255);
BEGIN
    IF bf is NULL
    THEN
  	 RETURN NULL;
    ELSE
  	 DBMS_LOB.FILEGETNAME (bf, dir_alias, file_name);
  	 RETURN file_name;
    END IF;
END;
/

-- load external JSON files into DATA column
update testsuite set data = treat(getfilecontent(file_content) as json);
commit;

select * from testsuite;

-- 1245 tests
select count(*)
from testsuite nested data[*] COLUMNS (description, schema json, nested tests[*] columns ( test_number for ordinality, test_description path '$.description', test_data json path '$.data', expected_result boolean path '$.valid' ) ) t;

with test_results as (
    select description, test_data, schema, test_description, expected_result, 
           dbms_json_schema.is_valid( t.test_data, t.schema ) = 1 as current_result
      from testsuite nested data[*] COLUMNS (description, schema json, nested tests[*] columns ( test_number for ordinality, test_description path '$.description', test_data json path '$.data', expected_result boolean path '$.valid' ) ) t
),
summary as (select sum(case when expected_result != current_result then 1 else 0 end) as failed, 
count(*) as total from test_results)
select total as "Total tests", failed as "Failed tests", round(100*(1-(failed/total)),2) as "Success rate (%)" from summary;

with test_results as (
    select getfilename(file_content) as filename, description, test_data, schema, test_description, expected_result, 
           dbms_json_schema.is_valid( t.test_data, t.schema ) = 1 as current_result
      from testsuite nested data[*] COLUMNS (description, schema json, nested tests[*] columns ( test_number for ordinality, test_description path '$.description', test_data json path '$.data', expected_result boolean path '$.valid' ) ) t
)
select filename, count(*), (select count(*) from test_results t2 where t2.filename=t.filename) as tests_in_file, round(ratio_to_report(count(*)) over()*100,2) as overall_percentage from test_results t where expected_result != current_result
--and description like '%format'
group by filename
order by overall_percentage desc
;

[jviotti]

Thanks for the details! I'll be looking into this hopefully next week and see how it goes.