Open hargathor opened 6 years ago
tchevallier
commented
6 years ago please test with the CREATE table and INSERT commands in a single transaction (i.e. BEGIN/COMMIT) It is probably required when running the pgsql proxy in "buffering mode" (typically the case for anonymization)
tchevallier
commented
6 years ago For example : BEGIN CREATE TABLE tbl ... INSERT INTO tbl (col1 ,col2, ... ) VALUES ... COMMIT
hargathor
commented
6 years ago Tested and it is the same issue. New table scheme
begin;CREATE TABLE patient( pat_id text PRIMARY KEY, pat_name text, pat_last1 text, pat_last2 text, pat_gen text, pat_zip text);commit;
begin;CREATE TABLE episode(ep_id text PRIMARY KEY, ep_pat text REFERENCES patient(pat_id) ON DELETE CASCADE, ep_age text, ep_range text);commit;
begin;CREATE TABLE discharge_report(dis_id text, dis_ver text, CONSTRAINT discharge_report_pk PRIMARY KEY (dis_id, dis_ver), dis_ep text REFERENCES episode(ep_id) ON DELETE CASCADE, dis_serv text, dis_adm text, dis_dis text, dis_days text, dis_adtp text, dis_dest text, dis_sig1 text, dis_sig2 text, dis_pdf text);commit;
begin;CREATE TABLE diagnose_cie9mc(dia_id text PRIMARY KEY, dia_desc text);commit;
begin;CREATE TABLE document_diagnose (dis_id text, dis_ver text, CONSTRAINT discharge_fk FOREIGN KEY (dis_id, dis_ver) REFERENCES discharge_report(dis_id, dis_ver) ON DELETE CASCADE, dia_id text REFERENCES diagnose_cie9mc(dia_id));commit;
begin;CREATE TABLE lab_result(lab_id text, lab_ver text, CONSTRAINT lab_pk PRIMARY KEY (lab_id, lab_ver), lab_ep text REFERENCES episode(ep_id) ON DELETE CASCADE, lab_pdf text);commit;
begin;CREATE TABLE medical_service_loinc(ms_id text PRIMARY KEY, ms_desc text, ms_unit text, ms_ref text);commit;
begin;CREATE TABLE document_ms(lab_id text, lab_ver text, CONSTRAINT lab_fk FOREIGN KEY (lab_id, lab_ver) REFERENCES lab_result(lab_id, lab_ver) ON DELETE CASCADE, ms_id text REFERENCES medical_service_loinc(ms_id), res_val text, res_abn text);commit;PGPASSWORD=password PGUSER=postgres psql -h localhost -c "\dt;"
No relations found.I'm just testing the table creation for now since the data insertion depends on it and are already in a single transaction
dieriver
commented
6 years ago I was consulting with @rmulero and he told me that he tested the requests with a single table. This means that he merged all the tables into a big, single one and he run all the inserts on that table. In addition, as @tchevallier said, Anonimization module usually works in "buffering mode", which requires transactional requests. (BEGIN; CREATE... INSERT... INSERT.... COMMIT;)
hargathor
commented
6 years ago even when I try to create one single table (patient) by hand using psql client I cannot make it work I use the command:
$ PGPASSWORD=password PGUSER=postgres psql -h localhost -c "begin;CREATE TABLE patient( pat_id text PRIMARY KEY, pat_name text, pat_last1 text, pat_last2 text, pat_gen text, pat_zip text);commit;"
COMMIT
$ PGPASSWORD=password PGUSER=postgres psql -h localhost -c "\dt;"
No relations found.I've identified the issue, the clarus proxy does not support multiple table creation. It seems that you need to let some time between the table creation and data insertion. ex: begin; create table patient; insert data..... commit; wait.......... begin; create table diagnose etc....
hargathor
commented
6 years ago In fact only the patient table is working with this method.... Will try with the simple_encryption module
I'm testing the CLARUS proxy using the dataset provided in the repository
benchmarking. For now i'm focusing on simple tasks, creating the database, the table structure and insertion of some data.I'm using the version
1.2-snap-oktagged in all repositories I'm using the security policy patient_anonymisation.xml I'm using the dataset table scheme for the table creation and the dataset table data for the data insert.Here is my test:
The Clarus proxy output a stacktrace:
When i tried to inspect the database I can see that the database has been created but not the tables.