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;