Note: We no longer publish the latest version of our code here. We primarily use a kumc-bmi github organization. The heron ETL repository, in particular, is not public. Peers in the informatics community should see MultiSiteDev for details on requesting access.

source: heron_load/etl_tests_init.sql @ 0:42ad7288920a

heron-michigan tip
Last change on this file since 0:42ad7288920a was 0:42ad7288920a, checked in by Matt Hoag <mhoag@…>, 6 years ago

Merge with demo_concepts_3800

File size: 2.8 KB
Line 
1/* etl_tests_init -- Initialize ETL tests
2
3Copyright (c) 2015 University of Kansas Medical Center
4part of the HERON* open source codebase; see NOTICE file for license details.
5* http://informatics.kumc.edu/work/wiki/HERON
6
7References:
8  * KUMC Informatics ticket #2781
9*/
10
11whenever sqlerror continue;
12drop table etl_tests;
13whenever sqlerror exit;
14
15create table etl_tests (
16  /* Description of test */
17  test_domain varchar(256) not null -- Such as top-level folder in HERON that's affected (Frontiers Registry...)
18  , test_name varchar(1024) not null -- User friendly name (hictr_mrn_leading_zero...)
19  /* Pass/Fail criteria - can define one or both of the following - INCLUSIVE */
20  , lower_bound number null
21  , upper_bound number null
22  , units varchar(256) null -- What are we counting?  (patients, facts, concepts, etc.)
23  , test_description varchar(4000) null -- More detailed description - how is the end-user affected if this test fails?
24  );
25
26alter table etl_tests add constraint pk_etl_tests primary key (test_domain, test_name);
27
28-- Table to store test values (inserted during ETL)
29whenever sqlerror continue;
30drop table etl_test_values;
31whenever sqlerror exit;
32
33create table etl_test_values as
34  select test_domain, test_name from etl_tests where 1=0;
35
36alter table etl_test_values
37  add (test_value number not null
38      , detail_char_1 varchar(256)
39      , detail_char_2 varchar(256)
40      , detail_num_1 number
41      , detail_num_2 number
42      , test_note varchar(256)
43      , result_id number not null
44      , result_date date not null);
45
46alter table etl_test_values add constraint pk_etl_test_results primary key (result_id);
47
48-- I wish we had "auto increment".  We can use a trigger but then we need PL/SQL.
49whenever sqlerror continue;
50drop sequence sq_result_id;
51create sequence sq_result_id;
52whenever sqlerror exit;
53
54/* Insert some example tests for debugging. */
55-- Example test that doesn't have something in the CSV
56insert into etl_test_values (test_value, test_domain, test_name, result_id, result_date)
57with test_key as (
58  select 'test' test_domain,
59  'test_not_found_in_csv' test_name from dual
60  )
61select 666 test_value, test_key.*, sq_result_id.nextval, sysdate from test_key;
62
63-- Example successful test
64insert into etl_test_values (test_value, test_domain, test_name, result_id, result_date)
65with test_key as (
66  select 'test' test_domain,
67  'test_success' test_name from dual
68  )
69select 1 test_value, test_key.*, sq_result_id.nextval, sysdate from test_key;
70
71-- Example test with no bounds specified
72insert into etl_test_values (test_value, test_domain, test_name, result_id, result_date)
73with test_key as (
74  select 'test' test_domain,
75  'test_no_bound' test_name from dual
76  )
77select 1 test_value, test_key.*, sq_result_id.nextval, sysdate from test_key;
Note: See TracBrowser for help on using the repository browser.