Source code

001package org.hl7.fhir.convertors.misc;
002
003import java.io.File;
004import java.io.FileInputStream;
005import java.io.FileNotFoundException;
006import java.io.IOException;
007import java.sql.Connection;
008import java.sql.DriverManager;
009import java.sql.PreparedStatement;
010import java.sql.SQLException;
011import java.sql.Statement;
012import java.util.HashMap;
013import java.util.Map;
014
015import org.hl7.fhir.exceptions.FHIRException;
016import org.hl7.fhir.utilities.CSVReader;
017import org.hl7.fhir.utilities.Utilities;
018import org.hl7.fhir.utilities.filesystem.ManagedFileAccess;
019
020public class OMOPImporter {
021
022  public class Tracker {
023    private int blip;
024    private long start;
025    private int counter = 0;
026    boolean processed = false;
027
028    public Tracker(String name, int estimate) {
029      this.start = System.currentTimeMillis();
030      this.blip = estimate < 100 ? 1 : estimate / 80;
031      System.out.print(name);
032    }
033
034    public void skip() {
035      System.out.println(" ... skipped");
036    }
037    
038    public void scan() {
039      System.out.println("");
040      System.out.print(" Scan :");
041      counter = 0;
042    }
043
044    public void process() {
045      System.out.println("");
046      System.out.print(" Build:");
047      counter = 0;
048      processed = true;
049    }
050
051    public void step() {
052      counter++;
053      if (counter % blip == 0) {
054        System.out.print(".");
055      }
056    }
057
058    public void done() {
059      if (counter > 0) {
060        System.out.println("");        
061      }
062      if (processed) {
063        long elapsed = System.currentTimeMillis()-start; 
064        if (elapsed > 3000) {
065          System.out.println(" Finished: "+counter+" rows, "+Utilities.describeDuration(elapsed)+" ("+(counter/(elapsed/1000))+" rows/msec)");
066        } else {
067          System.out.println(" Finished: "+counter+" rows, "+Utilities.describeDuration(elapsed));
068        }
069      } else {
070        System.out.println(" Finished: "+counter+" rows");        
071      }
072    }
073
074
075    public void error(String e) {
076      System.out.println("error: "+e);
077      System.out.println("row: "+counter);        
078      throw new Error(e);
079    }
080
081  }
082
083  private Connection con;
084  private Map<String, String> relationships = new HashMap<>();
085  private Map<String, String> vocabularies = new HashMap<>();
086  private Map<String, String> domains = new HashMap<>();
087  private Map<String, String> classes = new HashMap<>();
088
089  public static void main(String[] args) throws Exception {
090    new OMOPImporter().process(args[0], args[1]); 
091    // "/Users/grahamegrieve/Downloads/vocabulary_download_v5_{97cc5432-0dc9-4f14-9da2-d0624129d2f7}_1688068174909");
092    // /Users/grahamegrieve/temp/omop/omop.db
093  }
094
095  private void process(String folder, String dest) throws ClassNotFoundException, SQLException, FHIRException, FileNotFoundException, IOException {
096    connect(dest);
097
098    processRelationships(folder, true);
099    processVocabularies(folder, true);
100    processDomains(folder, true);
101    processConceptClasses(folder, true);
102    processConcepts(folder, true);
103    processConceptSynonyms(folder, true);
104    processConceptRelationships(folder, true);
105    // disabled - don't consume space that isn't required
106    processDrugStrength(folder, false);
107    processConceptAncestors(folder, false);
108  }
109
110
111  private void connect(String dest) throws SQLException, ClassNotFoundException, IOException {
112    //    Class.forName("com.mysql.jdbc.Driver");  
113    //    con = DriverManager.getConnection("jdbc:mysql://localhost:3306/omop?useSSL=false","root",{pwd}); 
114    ManagedFileAccess.file("/Users/grahamegrieve/temp/omop/omop.db").delete();
115    con = DriverManager.getConnection("jdbc:sqlite:"+dest); 
116  }
117  private void processRelationships(String folder, boolean process) throws FHIRException, FileNotFoundException, IOException, SQLException {
118    Tracker t = new Tracker("Relationships", 700);
119    CSVReader csv = new CSVReader(ManagedFileAccess.inStream(Utilities.path(folder, "RELATIONSHIP.csv")));
120    csv.setDelimiter('\t');
121    csv.readHeaders();
122    csv.setDoingQuotes(false);
123    int lid = 0;
124    int lname = 0;
125
126    t.scan();
127    while (csv.line()) {
128      relationships.put(csv.cell("relationship_id"), csv.cell("relationship_concept_id"));
129
130      lid = max(lid, csv.cell("relationship_id"));
131      lname = max(lname, csv.cell("relationship_name"));
132      t.step();
133    }
134
135    csv.close();   
136    if (process) {
137      t.process();
138      Statement stmt = con.createStatement();
139      stmt.execute("CREATE TABLE Relationships (\r\n"+
140          "`relationship_concept_id` bigint NOT NULL,\r\n"+
141          "`relationship_id` varchar("+lid+") DEFAULT NULL,\r\n"+
142          "`relationship_name` varchar("+lname+") DEFAULT NULL,\r\n"+
143          "`is_hierarchical` int DEFAULT NULL,\r\n"+
144          "`defines_ancestry` int DEFAULT NULL,\r\n"+
145          "`reverse_relationship_id` varchar(45) DEFAULT NULL,\r\n"+
146          "PRIMARY KEY (`relationship_concept_id`))\r\n");
147
148      stmt.execute("Create Index `RelationshipsId` on Relationships  (`relationship_id`)");
149      stmt.execute("Create Index`RelationshipsReverse` on Relationships (`reverse_relationship_id`)");
150
151      csv = new CSVReader(ManagedFileAccess.inStream(Utilities.path(folder, "RELATIONSHIP.csv")));
152      csv.setDelimiter('\t');
153      csv.readHeaders();
154      csv.setDoingQuotes(false);
155
156      while (csv.line()) {
157        relationships.put(csv.cell("relationship_id"), csv.cell("relationship_concept_id"));
158        if (process) {
159          String sql = "INSERT INTO `Relationships` (`relationship_concept_id`, `relationship_id`, `relationship_name`, `is_hierarchical`, `defines_ancestry`, `reverse_relationship_id`) VALUES ("+
160              sw(csv.cell("relationship_concept_id"))+", "+
161              sw(csv.cell("relationship_id"))+", "+
162              sw(csv.cell("relationship_name"))+", "+
163              sw(csv.cell("is_hierarchical"))+", "+
164              sw(csv.cell("defines_ancestry"))+", "+
165              sw(csv.cell("reverse_relationship_id"))+")";
166          try {
167            stmt.executeUpdate(sql);
168          } catch (Exception e) {
169            t.error(e.getMessage());
170          }
171        }
172        t.step();
173      }
174      csv.close();    
175    }
176    t.done();
177  }
178
179  private int max(int lid, String cell) {
180    int i = cell == null? 0 : cell.length();
181    return i > lid ? i : lid;
182  }
183
184  private void processVocabularies(String folder, boolean process) throws FHIRException, FileNotFoundException, IOException, SQLException {
185    Tracker t = new Tracker("Vocabularies", 60);
186    CSVReader csv = new CSVReader(ManagedFileAccess.inStream(Utilities.path(folder, "VOCABULARY.csv")));
187    csv.setDelimiter('\t');
188    csv.readHeaders();
189    csv.setDoingQuotes(false);
190    int lid = 0;
191    int lname = 0;
192    int lref = 0;
193    int lver = 0;
194    t.scan();
195    while (csv.line()) {
196      vocabularies.put(csv.cell("vocabulary_id"), csv.cell("vocabulary_concept_id"));
197
198      lid = max(lid, csv.cell("vocabulary_id"));
199      lname = max(lname, csv.cell("vocabulary_name"));
200      lref = max(lref, csv.cell("vocabulary_reference"));
201      lver = max(lver, csv.cell("vocabulary_version"));
202      t.step();
203    }
204    csv.close(); 
205
206    if (process) {
207      t.process();
208      Statement stmt = con.createStatement();
209
210      stmt.execute("CREATE TABLE `Vocabularies` (\r\n"+
211          "  `vocabulary_concept_id` bigint NOT NULL,\r\n"+
212          "  `vocabulary_id` varchar("+lid+") DEFAULT NULL,\r\n"+
213          "  `vocabulary_name` varchar("+lname+") DEFAULT NULL,\r\n"+
214          "  `vocabulary_reference` varchar("+lref+") DEFAULT NULL,\r\n"+
215          "  `vocabulary_version` varchar("+lver+") DEFAULT NULL,\r\n"+
216          "  PRIMARY KEY (`vocabulary_concept_id`)\r\n"+
217          ") \r\n"+
218          "\r\n");
219      stmt.execute("CREATE INDEX `VocabulariesId` on Vocabularies (`vocabulary_id`)");
220
221      csv = new CSVReader(ManagedFileAccess.inStream(Utilities.path(folder, "VOCABULARY.csv")));
222      csv.setDelimiter('\t');
223      csv.readHeaders();
224      csv.setDoingQuotes(false);
225      while (csv.line()) {
226        String sql = "INSERT INTO `Vocabularies` (`vocabulary_concept_id`, `vocabulary_id`, `vocabulary_name`, `vocabulary_reference`, `vocabulary_version`) VALUES ("+
227            sw(csv.cell("vocabulary_concept_id"))+", "+
228            sw(csv.cell("vocabulary_id"))+", "+
229            sw(csv.cell("vocabulary_name"))+", "+
230            sw(csv.cell("vocabulary_reference"))+", "+
231            sw(csv.cell("vocabulary_version"))+")";
232        try {
233          stmt.executeUpdate(sql);
234        } catch (Exception e) {
235          t.error(e.getMessage());
236        }
237        t.step();
238      }
239      csv.close(); 
240    }
241    t.done();
242  }
243
244
245  private void processDomains(String folder, boolean process) throws FHIRException, FileNotFoundException, IOException, SQLException {
246    Tracker t = new Tracker("Domains", 50);
247
248    CSVReader csv = new CSVReader(ManagedFileAccess.inStream(Utilities.path(folder, "DOMAIN.csv")));
249    csv.setDelimiter('\t');
250    csv.readHeaders();
251    csv.setDoingQuotes(false);
252    int lid = 0;
253    int lname = 0;
254    t.scan();
255    while (csv.line()) {
256
257      domains.put(csv.cell("domain_id"), csv.cell("domain_concept_id"));
258
259      lid = max(lid, csv.cell("domain_id"));
260      lname = max(lname, csv.cell("domain_name"));
261      t.step();
262    }
263    csv.close();
264
265    if (process) {
266      t.process();
267      Statement stmt = con.createStatement();
268
269      stmt.execute("CREATE TABLE `Domains` (\r\n"+
270          "  `domain_concept_id` bigint NOT NULL,\r\n"+
271          "  `domain_id` varchar("+lid+") DEFAULT NULL,\r\n"+
272          "  `domain_name` varchar("+lname+") DEFAULT NULL,\r\n"+
273          "  PRIMARY KEY (`domain_concept_id`)\r\n"+
274          ") \r\n"+
275          "\r\n");
276
277      stmt.execute("CREATE INDEX `DomainId` on Domains (`domain_id`)");
278
279      csv = new CSVReader(ManagedFileAccess.inStream(Utilities.path(folder, "DOMAIN.csv")));
280      csv.setDelimiter('\t');
281      csv.readHeaders();
282      csv.setDoingQuotes(false);
283      while (csv.line()) {
284        String sql = "INSERT INTO `Domains` (`domain_concept_id`, `domain_id`, `domain_name`) VALUES ("+
285            sw(csv.cell("domain_concept_id"))+", "+
286            sw(csv.cell("domain_id"))+", "+
287            sw(csv.cell("domain_name"))+")";
288        try {
289          stmt.executeUpdate(sql);
290        } catch (Exception e) {
291          t.error(e.getMessage());
292        }
293        t.step();
294      }
295      csv.close();
296    }
297    t.done();
298  }
299
300  private void processConceptClasses(String folder, boolean process) throws FHIRException, FileNotFoundException, IOException, SQLException {
301    Tracker t = new Tracker("ConceptClasses", 400);
302
303    CSVReader csv = new CSVReader(ManagedFileAccess.inStream(Utilities.path(folder, "CONCEPT_CLASS.csv")));
304    csv.setDelimiter('\t');
305    csv.readHeaders();
306    csv.setDoingQuotes(false);
307    int lid = 0;
308    int lname = 0;
309    t.scan();
310    while (csv.line()) {
311      classes.put(csv.cell("concept_class_id"), csv.cell("concept_class_concept_id"));
312
313      lid = max(lid, csv.cell("concept_class_id"));
314      lname = max(lname, csv.cell("concept_class_name"));
315      t.step();
316    }
317    csv.close(); 
318
319    if (process) {
320      t.process();
321      Statement stmt = con.createStatement();
322      stmt.execute("CREATE TABLE `ConceptClasses` (\r\n"+
323          "  `concept_class_concept_id` bigint NOT NULL,\r\n"+
324          "  `concept_class_id` varchar("+lid+") DEFAULT NULL,\r\n"+
325          "  `concept_class_name` varchar("+lname+") DEFAULT NULL,\r\n"+
326          "  PRIMARY KEY (`concept_class_concept_id`)\r\n"+
327          ") \r\n"+
328          "\r\n");
329      csv = new CSVReader(ManagedFileAccess.inStream(Utilities.path(folder, "CONCEPT_CLASS.csv")));
330      csv.setDelimiter('\t');
331      csv.readHeaders();
332      csv.setDoingQuotes(false);
333
334      while (csv.line()) {
335        String sql = "INSERT INTO `ConceptClasses` (`concept_class_concept_id`, `concept_class_id`, `concept_class_name`) VALUES ("+
336            sw(csv.cell("concept_class_concept_id"))+", "+
337            sw(csv.cell("concept_class_id"))+", "+
338            sw(csv.cell("concept_class_name"))+")";
339        try {
340          stmt.executeUpdate(sql);
341        } catch (Exception e) {
342          t.error(e.getMessage());
343        }
344        t.step();
345      }
346      csv.close();
347    }
348    t.done();
349
350  }
351
352
353  private void processDrugStrength(String folder, boolean process) throws FHIRException, FileNotFoundException, IOException, SQLException {
354    Tracker t = new Tracker("DrugStrengths", 3000000);
355    if (!process) {
356      t.skip();
357      return;
358    }
359
360    CSVReader csv = new CSVReader(ManagedFileAccess.inStream(Utilities.path(folder, "DRUG_STRENGTH.csv")));
361    csv.setDelimiter('\t');
362    csv.readHeaders();
363    csv.setDoingQuotes(false);
364    int lreason = 0;
365    int lamount1 = 0;
366    int lnum1 = 0;
367    int lden1 = 0;
368    int lamount2 = 0;
369    int lnum2 = 0;
370    int lden2 = 0;
371    t.scan();
372    while (csv.line()) {
373      lreason = max(lreason, csv.cell("invalid_reason"));
374      lamount1 = dmax1(lamount1, csv.cell("amount_value"));
375      lamount2 = dmax2(lamount2, csv.cell("amount_value"));
376      lnum1 = dmax1(lnum1, csv.cell("numerator_value"));
377      lnum2 = dmax2(lnum2, csv.cell("numerator_value"));
378      lden1 = dmax1(lden1, csv.cell("denominator_value"));
379      lden2 = dmax2(lden2, csv.cell("denominator_value"));
380      t.step();
381    }
382    csv.close();
383    t.process();
384
385    Statement stmt = con.createStatement();
386    stmt.execute("CREATE TABLE `DrugStrengths` (\r\n"+
387        "  `drug_concept_id` bigint NOT NULL,\r\n"+
388        "  `ingredient_concept_id` bigint NOT NULL,\r\n"+
389        "  `amount_value` decimal("+lamount1+","+lamount2+") DEFAULT NULL,\r\n"+
390        "  `amount_unit_concept_id` bigint DEFAULT NULL,\r\n"+
391        "  `numerator_value` decimal("+lnum1+","+lnum2+") DEFAULT NULL,\r\n"+
392        "  `numerator_unit_concept_id` bigint DEFAULT NULL,\r\n"+
393        "  `denominator_value` decimal("+lden1+","+lden2+") DEFAULT NULL,\r\n"+
394        "  `denominator_unit_concept_id` bigint DEFAULT NULL,\r\n"+
395        "  `box_size` int DEFAULT NULL,\r\n"+
396        "  `valid_start_date` date DEFAULT NULL,\r\n"+
397        "  `valid_end_date` date DEFAULT NULL,\r\n"+
398        "  `invalid_reason` varchar("+lreason+") DEFAULT NULL,\r\n"+
399        "  PRIMARY KEY (`drug_concept_id`,`ingredient_concept_id`)\r\n"+
400        ") \r\n"+
401        "\r\n");
402    csv = new CSVReader(ManagedFileAccess.inStream(Utilities.path(folder, "DRUG_STRENGTH.csv")));
403    csv.setDelimiter('\t');
404    csv.readHeaders();
405    csv.setDoingQuotes(false);
406    PreparedStatement pstmt = con.prepareStatement("INSERT INTO `DrugStrengths` (`drug_concept_id`, `ingredient_concept_id`, `amount_value`, `amount_unit_concept_id`, `numerator_value`, `numerator_unit_concept_id`, `denominator_value`, "
407        + "`denominator_unit_concept_id`, `box_size`, `valid_start_date`, `valid_end_date`, `invalid_reason`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
408    while (csv.line()) {
409      try {
410        pstmt.setString(1, csv.cell("drug_concept_id"));
411        pstmt.setString(2, csv.cell("ingredient_concept_id"));
412        pstmt.setString(3, csv.cell("amount_value"));
413        pstmt.setString(4, csv.cell("amount_unit_concept_id"));
414        pstmt.setString(5, csv.cell("numerator_value"));
415        pstmt.setString(6, csv.cell("numerator_unit_concept_id"));
416        pstmt.setString(7, csv.cell("denominator_value"));
417        pstmt.setString(8, csv.cell("denominator_unit_concept_id"));
418        pstmt.setString(9, csv.cell("box_size"));
419        pstmt.setString(10, date(csv.cell("valid_start_date")));
420        pstmt.setString(11, date(csv.cell("valid_end_date")));
421        pstmt.setString(11, csv.cell("invalid_reason"));
422        pstmt.executeUpdate();
423      } catch (Exception e) {
424        t.error(e.getMessage());
425      }
426      t.step();
427    }
428    csv.close();
429    t.done();
430
431  }
432
433  private int dmax1(int lid, String cell) {
434    int i = cell == null? 0 : cell.indexOf('.');
435    return i > lid ? i : lid;
436  }
437
438  private int dmax2(int lid, String cell) {
439    int i = cell == null? 0 : cell.length() - cell.indexOf('.') - 1;
440    return i > lid ? i : lid;
441  }
442
443  private void processConcepts(String folder, boolean process) throws FHIRException, FileNotFoundException, IOException, SQLException {
444    Tracker t = new Tracker("Concepts", 5617348);
445    if (!process) {
446      t.skip();
447      return;
448    }
449
450    CSVReader csv = new CSVReader(ManagedFileAccess.inStream(Utilities.path(folder, "CONCEPT.csv")));
451    csv.setDelimiter('\t');
452    csv.readHeaders();
453    csv.setDoingQuotes(false);
454    int lname = 0;
455    int lstd = 0;
456    int lcode = 0;
457    int lreason = 0;
458    t.scan();
459    while (csv.line()) {
460      lname = max(lname, csv.cell("concept_name"));
461      lstd = max(lstd, csv.cell("standard_concept"));
462      lcode = max(lcode, csv.cell("concept_code"));
463      lreason = max(lreason, csv.cell("invalid_reason"));
464      t.step();
465    }
466    csv.close();
467    t.process();
468
469    Statement stmt = con.createStatement();
470    stmt.execute("CREATE TABLE `Concepts` (\r\n"+
471        "    `concept_id` bigint NOT NULL,\r\n"+
472        "    `concept_name` varchar("+lname+") DEFAULT NULL,\r\n"+
473        "    `domain_id` bigint DEFAULT NULL,\r\n"+
474        "    `vocabulary_id` bigint DEFAULT NULL,\r\n"+
475        "    `concept_class_id` bigint DEFAULT NULL,\r\n"+
476        "    `standard_concept` varchar("+lstd+") DEFAULT NULL,\r\n"+
477        "    `concept_code` varchar("+lcode+") DEFAULT NULL,\r\n"+
478        "    `valid_start_date` date DEFAULT NULL,\r\n"+
479        "    `valid_end_date` date DEFAULT NULL,\r\n"+
480        "    `invalid_reason` varchar("+lreason+") DEFAULT NULL,\r\n"+
481        "    PRIMARY KEY (`concept_id`)\r\n"+
482        "  ) \r\n"+
483        "\r\n");
484
485    stmt.execute("CREATE INDEX `ConceptDomain` on Concepts (`domain_id`)");
486    stmt.execute("CREATE INDEX `ConceptVocabulary` on Concepts (`vocabulary_id`,`concept_code`)");
487    stmt.execute("CREATE INDEX `ConceptClass` on Concepts (`concept_class_id`)");
488
489    csv = new CSVReader(ManagedFileAccess.inStream(Utilities.path(folder, "CONCEPT.csv")));
490    csv.setDelimiter('\t');
491    csv.readHeaders();
492    csv.setDoingQuotes(false);
493
494    PreparedStatement pstmt = con.prepareStatement(
495        "INSERT INTO `Concepts` (`concept_id`, `concept_name`, `domain_id`, `vocabulary_id`, `concept_class_id`, `standard_concept`, `concept_code`, `valid_start_date`, `valid_end_date`, `invalid_reason`) "+
496        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); 
497    while (csv.line()) {
498      try {
499        pstmt.setString(1, csv.cell("concept_id"));
500        pstmt.setString(2, csv.cell("concept_name"));
501        pstmt.setString(3, domains.get(csv.cell("domain_id")));
502        pstmt.setString(4, vocabularies.get(csv.cell("vocabulary_id")));
503        pstmt.setString(5, classes.get(csv.cell("concept_class_id")));
504        pstmt.setString(6, csv.cell("standard_concept"));
505        pstmt.setString(7, csv.cell("concept_code"));
506        pstmt.setString(8, date(csv.cell("valid_start_date")));
507        pstmt.setString(9, date(csv.cell("valid_end_date")));
508        pstmt.setString(10, csv.cell("invalid_reason"));
509        pstmt.executeUpdate();
510      } catch (Exception e) {
511        t.error(e.getMessage());
512      }
513      t.step();
514    }
515    csv.close();    
516    t.done();
517  }
518
519  private void processConceptSynonyms(String folder, boolean process) throws FHIRException, FileNotFoundException, IOException, SQLException {
520    Tracker t = new Tracker("ConceptSynonyms", 1933498);
521    if (!process) {
522      t.skip();
523      return;
524    }    
525    t.scan();
526
527    CSVReader csv = new CSVReader(ManagedFileAccess.inStream(Utilities.path(folder, "CONCEPT_SYNONYM.csv")));
528    csv.setDelimiter('\t');
529    csv.readHeaders();
530    csv.setDoingQuotes(false);
531    int lname = 0;
532    while (csv.line()) {
533      lname = max(lname, csv.cell("concept_synonym_name"));
534      t.step();
535    }
536    csv.close();
537    t.process();
538
539    Statement stmt = con.createStatement();
540    stmt.execute("CREATE TABLE `ConceptSynonyms` (\r\n"+
541        "  `concept_id` bigint NOT NULL,\r\n"+
542        "  `concept_synonym_name` varchar("+lname+") DEFAULT NULL,\r\n"+
543        "  `language_concept_id` bigint DEFAULT NULL\r\n"+
544        ") \r\n"+
545        "\r\n");
546    stmt.execute("CREATE INDEX `SynonymId` on ConceptSynonyms (`concept_id`)");
547    stmt.execute("CREATE INDEX `SynonymLang` on ConceptSynonyms (`language_concept_id`)");
548
549    csv = new CSVReader(ManagedFileAccess.inStream(Utilities.path(folder, "CONCEPT_SYNONYM.csv")));
550    csv.setDelimiter('\t');
551    csv.readHeaders();
552    csv.setDoingQuotes(false);
553    
554    PreparedStatement pstmt = con.prepareStatement("INSERT INTO `ConceptSynonyms` (`concept_id`, `concept_synonym_name`, `language_concept_id`) VALUES (?, ?, ?)");
555    while (csv.line()) {
556      try {
557        pstmt.setString(1, csv.cell("concept_id"));
558        pstmt.setString(2, csv.cell("concept_synonym_name"));
559        pstmt.setString(3, csv.cell("language_concept_id"));
560        pstmt.executeUpdate();
561      } catch (Exception e) {
562        t.error(e.getMessage());
563      }
564      t.step();
565    }
566    csv.close();
567    t.done();
568  }
569
570
571  private void processConceptAncestors(String folder, boolean process) throws FHIRException, FileNotFoundException, IOException, SQLException {
572    Tracker t = new Tracker("ConceptAncestors", 67425885);
573    if (!process) {
574      t.skip();
575      return;
576    }
577
578    t.process();
579    CSVReader csv = new CSVReader(ManagedFileAccess.inStream(Utilities.path(folder, "CONCEPT_ANCESTOR.csv")));
580    csv.setDelimiter('\t');
581    csv.readHeaders();
582    csv.setDoingQuotes(false);
583    con.createStatement().execute("CREATE TABLE `ConceptAncestors` (\r\n"+
584        "    `ancestor_concept_id` bigint NOT NULL,\r\n"+
585        "    `descendant_concept_id` bigint NOT NULL,\r\n"+
586        "    `min_levels_of_separation` int DEFAULT NULL,\r\n"+
587        "    `max_levels_of_separation` int DEFAULT NULL,\r\n"+
588        "    PRIMARY KEY (`ancestor_concept_id`,`descendant_concept_id`)\r\n"+
589        "  ) \r\n"+
590        "\r\n");
591    
592    PreparedStatement pstmt = con.prepareStatement("INSERT INTO `ConceptAncestors` (`ancestor_concept_id`, `descendant_concept_id`, `min_levels_of_separation`, `max_levels_of_separation`) VALUES (?, ?, ?, ?)");
593    while (csv.line()) {
594      try {
595        pstmt.setString(1, csv.cell("ancestor_concept_id"));
596        pstmt.setString(2, csv.cell("descendant_concept_id"));
597        pstmt.setString(3, csv.cell("min_levels_of_separation"));
598        pstmt.setString(4, csv.cell("max_levels_of_separation"));
599        pstmt.executeUpdate();
600      } catch (Exception e) {
601        t.error(e.getMessage());
602      }
603      t.step();
604    }
605    csv.close();
606    t.done();
607  }
608
609
610  private void processConceptRelationships(String folder, boolean process) throws FHIRException, FileNotFoundException, IOException, SQLException {
611    Tracker t = new Tracker("ConceptRelationships", 47000000);
612    if (!process) {
613      t.skip();
614      return;
615    }
616
617    t.scan();
618    CSVReader csv = new CSVReader(ManagedFileAccess.inStream(Utilities.path(folder, "CONCEPT_RELATIONSHIP.csv")));
619    csv.setDelimiter('\t');
620    csv.readHeaders();
621    csv.setDoingQuotes(false);
622    int lreason = 0;
623    while (csv.line()) {
624      lreason = max(lreason, csv.cell("invalid_reason"));
625      t.step();
626    }
627    csv.close();
628    t.process();
629
630    Statement stmt = con.createStatement();
631    stmt.execute("CREATE TABLE `ConceptRelationships` (\r\n"+
632        "  `concept_id_1` bigint NOT NULL,\r\n"+
633        "  `concept_id_2` bigint NOT NULL,\r\n"+
634        "  `relationship_id` bigint NOT NULL,\r\n"+
635        "  `valid_start_date` date DEFAULT NULL,\r\n"+
636        "  `valid_end_date` date DEFAULT NULL,\r\n"+
637        "  `invalid_reason` varchar("+lreason+") DEFAULT NULL)\r\n"+
638        "  \r\n");
639
640    stmt.execute("CREATE INDEX `Reverse` on ConceptRelationships (`concept_id_2`,`concept_id_1`,`relationship_id`)");
641    stmt.execute("CREATE INDEX `Forward` on ConceptRelationships (`concept_id_1`,`concept_id_2`,`relationship_id`)");  
642//  stmt.execute("CREATE INDEX `type1` on ConceptRelationships (`relationship_id`,`concept_id_1`,`concept_id_2`)");
643//  stmt.execute("CREATE INDEX `type2` on ConceptRelationships (`relationship_id`,`concept_id_2`,`concept_id_1`)");
644
645    csv = new CSVReader(ManagedFileAccess.inStream(Utilities.path(folder, "CONCEPT_RELATIONSHIP.csv")));
646    csv.setDelimiter('\t');
647    csv.readHeaders();
648    csv.setDoingQuotes(false);
649
650    PreparedStatement pstmt = con.prepareStatement("INSERT INTO `ConceptRelationships` (`concept_id_1`, `concept_id_2`, `relationship_id`, `valid_start_date`, `valid_end_date`, `invalid_reason`) VALUES (?, ?, ?, ?, ?, ?)");
651    while (csv.line()) {
652      try {
653        pstmt.setString(1, csv.cell("concept_id_1"));
654        pstmt.setString(2, csv.cell("concept_id_2"));
655        pstmt.setString(3, relationships.get(csv.cell("relationship_id")));
656        pstmt.setString(4, csv.cell("valid_start_date"));
657        pstmt.setString(5, date(csv.cell("valid_end_date")));
658        pstmt.setString(6, date(csv.cell("invalid_reason")));
659        pstmt.executeUpdate();
660      } catch (Exception e) {
661        t.error(e.getMessage());
662      }
663      t.step();
664    }
665    csv.close();
666    t.done();
667
668  }
669
670
671  private String date(String cell) {
672    return cell;
673  }
674
675  private String sw(String value) {
676    if (value == null) {
677      return  "null";
678    }
679    StringBuilder b = new StringBuilder();
680    b.append('"');
681    for (char ch : value.toCharArray()) {
682      if (ch == '"') {
683        b.append('"');        
684      }
685      b.append(ch);
686    }
687    b.append('"');
688    return b.toString();
689  }
690
691  
692}