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