github.com/Bio-core/jtree@v0.0.0-20190705165106-1d7a7e7d6272/sql/CreateTables.sql (about)

     1  USE `JTree`
     2  
     3  CREATE TABLE `patients` (
     4    `first_name` nvarchar(50) DEFAULT NULL,
     5    `last_name` nvarchar(50) DEFAULT NULL,
     6    `initials` nvarchar(50) DEFAULT NULL,
     7    `gender` nvarchar(50) DEFAULT NULL,
     8    `mrn` nvarchar(50) DEFAULT NULL,
     9    `dob` date DEFAULT NULL,
    10    `on_hcn` nvarchar(50) DEFAULT NULL,
    11    `clinical_history` nvarchar(255) DEFAULT NULL,
    12    `patient_type` nvarchar(50) DEFAULT NULL,
    13    `se_num` nvarchar(50) DEFAULT NULL,
    14    `patient_id` nvarchar(50) NOT NULL,
    15    `date_received` date DEFAULT NULL,
    16    `referring_physican` nvarchar(150) DEFAULT NULL,
    17    `date_reported` date DEFAULT NULL,
    18    `surgical_date` date DEFAULT NULL,
    19    PRIMARY KEY (`patient_id`)
    20  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    21  
    22  CREATE TABLE `samples` (
    23    `sample_id` nvarchar(50) NOT NULL,
    24    `facility` nvarchar(255) DEFAULT NULL,
    25    `test_requested` nvarchar(50) DEFAULT NULL,
    26    `se_num` nvarchar(50) DEFAULT NULL,
    27    `date_collected` date DEFAULT NULL,
    28    `date_received` date DEFAULT NULL,
    29    `sample_type` nvarchar(50) DEFAULT NULL,
    30    `material_received` nvarchar(150) DEFAULT NULL,
    31    `material_received_num` nvarchar(150) DEFAULT NULL,
    32    `material_received_other` nvarchar(150) DEFAULT NULL,
    33    `volume_of_blood_marrow` float(5,1) DEFAULT NULL,
    34    `surgical_num` nvarchar(50) DEFAULT NULL,
    35    `tumor_site` nvarchar(255) DEFAULT NULL,
    36    `historical_diagnosis` nvarchar(255) DEFAULT NULL,
    37    `tumor_percnt_of_total` float(5,2) DEFAULT NULL,
    38    `tumor_percnt_of_circled` float(5,2) DEFAULT NULL,
    39    `reviewed_by` nvarchar(150) DEFAULT NULL,
    40    `h_e_slide_location` nvarchar(150) DEFAULT NULL,
    41    `non_uhn_id` nvarchar(50) DEFAULT NULL,
    42    `name_of_requestor` nvarchar(150) DEFAULT NULL,
    43    `dna_concentration` float(10,4) DEFAULT NULL,
    44    `dna_volume` float(5,1) DEFAULT NULL,
    45    `dna_location` nvarchar(255) DEFAULT NULL,
    46    `rna_concentration` float(10,4) DEFAULT NULL,
    47    `rna_volume` float(5,1) DEFAULT NULL,
    48    `rna_location` nvarchar(150) DEFAULT NULL,
    49    `wbc_location` nvarchar(50) DEFAULT NULL,
    50    `plasma_location` nvarchar(50) DEFAULT NULL,
    51    `cf_plasma_location` nvarchar(50) DEFAULT NULL,
    52    `pb_bm_location` nvarchar(50) DEFAULT NULL,
    53    `rna_lysate_location` nvarchar(50) DEFAULT NULL,
    54    `sample_size` nvarchar(50) DEFAULT NULL,
    55    `study_id` nvarchar(50) DEFAULT NULL,
    56    `sample_name` nvarchar(50) DEFAULT NULL,
    57    `date_submitted` date DEFAULT NULL,
    58    `container_type` nvarchar(50) DEFAULT NULL,
    59    `container_name` nvarchar(100) DEFAULT NULL,
    60    `container_id` nvarchar(100) DEFAULT NULL,
    61    `container_well` nvarchar(50) DEFAULT NULL,
    62    `copath_num` nvarchar(50) DEFAULT NULL,
    63    `other_identifier` nvarchar(50) DEFAULT NULL,
    64    `has_sample_files` tinyint(1) DEFAULT NULL,
    65    `dna_sample_barcode` nvarchar(50) DEFAULT NULL,
    66    `dna_extraction_date` date DEFAULT NULL,
    67    `dna_quality` nvarchar(255) DEFAULT NULL,
    68    `ffpe_qc_date` date DEFAULT NULL,
    69    `delta_ct_value` float(10,4) DEFAULT NULL,
    70    `comments` nvarchar(255) DEFAULT NULL,
    71    `rnase_p_date` date DEFAULT NULL,
    72    `dna_quality_by_rnase_p` float(10,4) DEFAULT NULL,
    73    `rna_quality` float(10,4) DEFAULT NULL,
    74    `rna_extraction_date` date DEFAULT NULL,
    75    `patient_id` nvarchar(50) DEFAULT NULL,
    76    PRIMARY KEY (`sample_id`),
    77    KEY `patient_id` (`patient_id`),
    78    CONSTRAINT `patient_id` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`patient_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
    79  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    80  
    81  CREATE TABLE `experiments` (
    82    `experiment_id` nvarchar(255) NOT NULL,
    83    `study_id` nvarchar(50) DEFAULT NULL,
    84    `panel_assay_screened` nvarchar(50) DEFAULT NULL,
    85    `test_date` date DEFAULT NULL,
    86    `chip_cartridge_barcode` nvarchar(50) DEFAULT NULL,
    87    `complete_date` date DEFAULT NULL,
    88    `pcr` nvarchar(50) DEFAULT NULL,
    89    `sample_id` nvarchar(50) DEFAULT NULL,
    90    `project_name` nvarchar(50) DEFAULT NULL,
    91    `priority` nvarchar(50) DEFAULT NULL,
    92    `opened_date` date DEFAULT NULL,
    93    `project_id` nvarchar(50) DEFAULT NULL,
    94    `has_project_files` tinyint(1) DEFAULT NULL,
    95    `procedure_order_datetime` datetime DEFAULT NULL,
    96    PRIMARY KEY (`experiment_id`),
    97    KEY `sample_id_idx` (`sample_id`),
    98    CONSTRAINT `sample_id_ex` FOREIGN KEY (`sample_id`) REFERENCES `samples` (`sample_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
    99  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   100  
   101  CREATE TABLE `results` (
   102    `failed_regions` nvarchar(255) DEFAULT NULL,
   103    `mean_depth_of_coveage` float DEFAULT NULL,
   104    `mlpa_pcr` nvarchar(255) DEFAULT NULL,
   105    `mutation` nvarchar(255) DEFAULT NULL,
   106    `overall_hotspots_threshold` float DEFAULT NULL,
   107    `overall_quality_threshold` float DEFAULT NULL,
   108    `results_id` nvarchar(255) NOT NULL,
   109    `uid` nvarchar(255) DEFAULT NULL,
   110    `verification_pcr` nvarchar(255) DEFAULT NULL,
   111    `experiment_id` nvarchar(255) DEFAULT NULL,
   112    PRIMARY KEY (`results_id`),
   113    KEY `FK_experiment_id` (`experiment_id`),
   114    CONSTRAINT `FK_experiment_id` FOREIGN KEY (`experiment_id`) REFERENCES `experiments` (`experiment_id`)
   115  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   116  
   117  CREATE TABLE `resultdetails` (
   118    `VAF` float DEFAULT NULL,
   119    `c_nomenclature` nvarchar(255) DEFAULT NULL,
   120    `coverage` int(11) DEFAULT NULL,
   121    `exon` int(11) DEFAULT NULL,
   122    `gene` nvarchar(255) DEFAULT NULL,
   123    `p_nomenclature` nvarchar(255) DEFAULT NULL,
   124    `pcr` nvarchar(255) DEFAULT NULL,
   125    `quality_score` float DEFAULT NULL,
   126    `result` nvarchar(255) DEFAULT NULL,
   127    `results_details_id` nvarchar(255) NOT NULL,
   128    `results_id` nvarchar(255) DEFAULT NULL,
   129    `risk_score` float DEFAULT NULL,
   130    `uid` nvarchar(255) DEFAULT NULL,
   131    PRIMARY KEY (`results_details_id`),
   132    KEY `FK_results_id` (`results_id`),
   133    CONSTRAINT `FK_results_id` FOREIGN KEY (`results_id`) REFERENCES `results` (`results_id`)
   134  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;