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