modified: src1/input.c
[GalaxyCodeBases.git] / sql / pku201211.sql
blob024a9d6934a298afcabca3ded985e6586d3d24ac
1 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
5 DROP DATABASE `pkudb`;
7 CREATE SCHEMA IF NOT EXISTS `pkudb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
8 SHOW WARNINGS;
9 USE `pkudb` ;
11 -- -----------------------------------------------------
12 -- Table `pkudb`.`Samples`
13 -- -----------------------------------------------------
14 CREATE  TABLE IF NOT EXISTS `pkudb`.`Samples` (
15   `SampleID` CHAR(12) NOT NULL ,
16   `SourceID` CHAR(4) NULL ,
17   `SourceCnt` SMALLINT(4) NULL ,
18   `TissueID` CHAR(2) NULL ,
19   `TissueCnt` TINYINT(2) NULL ,
20   `DateCollected` DATETIME NULL ,
21   `DateReceived` DATETIME NULL ,
22   `DateFrozen` DATETIME NULL ,
23   `SampleCodePre` VARCHAR(45) NULL ,
24   `TissueReal` VARCHAR(45) NULL ,
25   `AnimalID` VARCHAR(45) NULL COMMENT 'Def = SourceID . SourceCnt' ,
26   `OldID` VARCHAR(45) NULL ,
27   `LabelPrintedCnt` INT NOT NULL DEFAULT 0 ,
28   `ivFreezer` VARCHAR(45) NULL ,
29   `ivShelf` VARCHAR(45) NULL ,
30   `ivRack` VARCHAR(45) NULL ,
31   `ivBox` VARCHAR(45) NULL ,
32   `ivPosition` VARCHAR(45) NULL ,
33   `Description` VARCHAR(4095) NULL ,
34   `ProjectName` VARCHAR(45) NULL ,
35   `Draw` VARCHAR(45) NULL ,
36   `Vial` VARCHAR(45) NULL ,
37   `TransportTemperature` DECIMAL(4,1) NULL ,
38   `Quality` VARCHAR(45) NULL ,
39   `VolumeInTubeUL` DECIMAL(5,1) NULL ,
40   `CreatedBy` VARCHAR(45) NULL ,
41   `CreatedAt` DATETIME NULL ,
42   `ModifiedBy` VARCHAR(45) NULL ,
43   `ModifiedAt` DATETIME NULL ,
44   `Depleted` TINYINT(1) NULL ,
45   PRIMARY KEY (`SampleID`) ,
46   UNIQUE INDEX `SampleCode_UNIQUE` (`SampleID` ASC) )
47 ENGINE = InnoDB;
49 SHOW WARNINGS;
51 -- -----------------------------------------------------
52 -- Table `pkudb`.`AdditionalSampleInfo`
53 -- -----------------------------------------------------
54 CREATE  TABLE IF NOT EXISTS `pkudb`.`AdditionalSampleInfo` (
55   `UID` INT NOT NULL AUTO_INCREMENT ,
56   `SampleID` CHAR(12) NOT NULL ,
57   `Key` VARCHAR(45) NOT NULL ,
58   `Value` VARCHAR(4095) NULL ,
59   UNIQUE INDEX `SampleID_UNIQUE` (`SampleID` ASC) ,
60   PRIMARY KEY (`UID`) ,
61   UNIQUE INDEX `UID_UNIQUE` (`UID` ASC) )
62 ENGINE = InnoDB;
64 SHOW WARNINGS;
66 -- -----------------------------------------------------
67 -- Table `pkudb`.`Project`
68 -- -----------------------------------------------------
69 CREATE  TABLE IF NOT EXISTS `pkudb`.`Project` (
70   `UID` INT NOT NULL ,
71   `ProjectName` VARCHAR(45) NOT NULL ,
72   `Started` DATETIME NULL ,
73   `Finished` DATETIME NULL ,
74   `Status` VARCHAR(45) NULL ,
75   `Comments` VARCHAR(4095) NULL ,
76   PRIMARY KEY (`UID`) ,
77   UNIQUE INDEX `UID_UNIQUE` (`UID` ASC) ,
78   UNIQUE INDEX `ProjectName_UNIQUE` (`ProjectName` ASC) )
79 ENGINE = InnoDB;
81 SHOW WARNINGS;
83 -- -----------------------------------------------------
84 -- Table `pkudb`.`SampleRemoval`
85 -- -----------------------------------------------------
86 CREATE  TABLE IF NOT EXISTS `pkudb`.`SampleRemoval` (
87   `SampleID` CHAR(12) NOT NULL ,
88   `rmBy` VARCHAR(45) NULL ,
89   `rmAt` VARCHAR(45) NULL ,
90   `rmReason` VARCHAR(45) NULL ,
91   `rmAmount` VARCHAR(45) NULL ,
92   `Comments` VARCHAR(4095) NULL ,
93   PRIMARY KEY (`SampleID`) ,
94   UNIQUE INDEX `SampleID_UNIQUE` (`SampleID` ASC) )
95 ENGINE = InnoDB;
97 SHOW WARNINGS;
99 -- -----------------------------------------------------
100 -- Table `pkudb`.`Contacts`
101 -- -----------------------------------------------------
102 CREATE  TABLE IF NOT EXISTS `pkudb`.`Contacts` (
103   `SampleID` CHAR(12) NOT NULL ,
104   `LabPerson` VARCHAR(45) NOT NULL ,
105   `Collector` VARCHAR(45) NULL ,
106   `Information` VARCHAR(4095) NULL ,
107   PRIMARY KEY (`SampleID`) ,
108   UNIQUE INDEX `SampleID_UNIQUE` (`SampleID` ASC) )
109 ENGINE = InnoDB;
111 SHOW WARNINGS;
113 -- -----------------------------------------------------
114 -- Table `pkudb`.`Geography`
115 -- -----------------------------------------------------
116 CREATE  TABLE IF NOT EXISTS `pkudb`.`Geography` (
117   `SampleID` CHAR(12) NOT NULL ,
118   `Continent` VARCHAR(45) NULL ,
119   `Country` VARCHAR(45) NULL ,
120   `StateProvince` VARCHAR(45) NULL ,
121   `Region` VARCHAR(45) NULL ,
122   `SubRegion` VARCHAR(45) NULL ,
123   `Info` VARCHAR(45) NULL ,
124   `Comments` VARCHAR(4095) NULL ,
125   `Latitude` VARCHAR(45) NULL ,
126   `Longitude` VARCHAR(45) NULL ,
127   `Elevation` VARCHAR(45) NULL ,
128   PRIMARY KEY (`SampleID`) ,
129   UNIQUE INDEX `SampleID_UNIQUE` (`SampleID` ASC) )
130 ENGINE = InnoDB;
132 SHOW WARNINGS;
134 -- -----------------------------------------------------
135 -- Table `pkudb`.`ProjectList`
136 -- -----------------------------------------------------
137 CREATE  TABLE IF NOT EXISTS `pkudb`.`ProjectList` (
138   `ProjectID` INT NOT NULL ,
139   `SampleID` CHAR(12) NULL ,
140   `AnimalID` VARCHAR(45) NULL ,
141   INDEX `UsedSamples` USING HASH (`SampleID` ASC, `AnimalID` ASC) )
142 ENGINE = InnoDB;
144 SHOW WARNINGS;
146 -- -----------------------------------------------------
147 -- Table `pkudb`.`TissueCode`
148 -- -----------------------------------------------------
149 CREATE  TABLE IF NOT EXISTS `pkudb`.`TissueCode` (
150   `TissueID` CHAR(2) NOT NULL ,
151   `Tissue` VARCHAR(45) NULL ,
152   `Description` VARCHAR(4095) NULL ,
153   PRIMARY KEY (`TissueID`) ,
154   UNIQUE INDEX `TissueID_UNIQUE` (`TissueID` ASC) ,
155   UNIQUE INDEX `Tissue_UNIQUE` (`Tissue` ASC) )
156 ENGINE = InnoDB;
158 SHOW WARNINGS;
160 -- -----------------------------------------------------
161 -- Table `pkudb`.`Animals`
162 -- -----------------------------------------------------
163 CREATE  TABLE IF NOT EXISTS `pkudb`.`Animals` (
164   `AnimalID` VARCHAR(45) NOT NULL ,
165   `Sex` CHAR(1) NULL ,
166   `SexDeterminedBy` VARCHAR(45) NULL ,
167   `SexComments` VARCHAR(4095) NULL ,
168   `DateBirth` DATETIME NULL ,
169   `DateDeath` DATETIME NULL ,
170   `Date1stSampling` DATETIME NULL ,
171   `CauseOfDeath` VARCHAR(4095) NULL ,
172   `Comments` VARCHAR(4095) NULL ,
173   `StatusBirth` VARCHAR(45) NULL ,
174   `StatusCurrent` VARCHAR(45) NULL ,
175   PRIMARY KEY (`AnimalID`) ,
176   UNIQUE INDEX `AnimalID_UNIQUE` (`AnimalID` ASC) )
177 ENGINE = InnoDB;
179 SHOW WARNINGS;
181 -- -----------------------------------------------------
182 -- Table `pkudb`.`OtherID`
183 -- -----------------------------------------------------
184 CREATE  TABLE IF NOT EXISTS `pkudb`.`OtherID` (
185   `OID` VARCHAR(45) NOT NULL ,
186   `identifier` VARCHAR(45) NULL ,
187   `Comments` VARCHAR(45) NULL ,
188   UNIQUE INDEX `OID_UNIQUE` (`OID` ASC) )
189 ENGINE = InnoDB;
191 SHOW WARNINGS;
193 -- -----------------------------------------------------
194 -- Table `pkudb`.`OtherIDList`
195 -- -----------------------------------------------------
196 CREATE  TABLE IF NOT EXISTS `pkudb`.`OtherIDList` (
197   `OID` VARCHAR(45) NOT NULL ,
198   `SampleID` CHAR(12) NULL ,
199   `AnimalID` VARCHAR(45) NULL ,
200   INDEX `UsedSamples` USING HASH (`SampleID` ASC, `AnimalID` ASC) ,
201   UNIQUE INDEX `OID_UNIQUE` (`OID` ASC) ,
202   INDEX `toSamples_idx` (`SampleID` ASC) ,
203   INDEX `toAnimals_idx` (`AnimalID` ASC) ,
204   CONSTRAINT `toSamples`
205     FOREIGN KEY (`SampleID` )
206     REFERENCES `pkudb`.`Samples` (`SampleID` )
207     ON DELETE NO ACTION
208     ON UPDATE CASCADE,
209   CONSTRAINT `toAnimals`
210     FOREIGN KEY (`AnimalID` )
211     REFERENCES `pkudb`.`Animals` (`AnimalID` )
212     ON DELETE NO ACTION
213     ON UPDATE CASCADE)
214 ENGINE = InnoDB;
216 SHOW WARNINGS;
217 USE `pkudb`;
219 DELIMITER $$
220 SHOW WARNINGS$$
221 USE `pkudb`$$
224 CREATE TRIGGER SampleID_Split BEFORE INSERT ON Samples
225 FOR EACH ROW
226 BEGIN
227         SET NEW.SampleID=UPPER(NEW.SampleID);
228         SET NEW.CreatedAt = CURRENT_TIMESTAMP;
229         IF NEW.SourceID IS NULL OR NEW.SourceID='' THEN
230                 SET NEW.SourceID=SUBSTRING(NEW.SampleID,1,4),
231                         NEW.SourceCnt=SUBSTRING(NEW.SampleID,5,4),
232                         NEW.TissueID=SUBSTRING(NEW.SampleID,9,2),
233                         NEW.TissueCnt=SUBSTRING(NEW.SampleID,11);
234         END IF;
235         IF NEW.AnimalID IS NULL OR NEW.AnimalID='' THEN
236                 SET NEW.AnimalID=CONCAT(NEW.SourceID,LPAD(NEW.SourceCnt,4,'0'));
237         END IF;
238         INSERT IGNORE INTO Animals SET AnimalID=NEW.AnimalID;
239         INSERT IGNORE INTO TissueCode SET TissueID=NEW.TissueID;
240 END; $$
242 SHOW WARNINGS$$
244 CREATE TRIGGER Animals_Date1stSampling AFTER INSERT ON Samples
245 FOR EACH ROW
246 BEGIN
247         DECLARE Oldata DATETIME;
248         IF NEW.DateCollected IS NOT NULL THEN
249                 SELECT Date1stSampling FROM Animals WHERE AnimalID=NEW.AnimalID INTO Oldata;
250                 IF Oldata IS NULL OR Oldata > NEW.DateCollected THEN
251                         UPDATE IGNORE Animals SET Date1stSampling=NEW.DateCollected WHERE AnimalID=NEW.AnimalID;
252                 END IF;
253         END IF;
254 END; $$
256 DELIMITER ;
259 SET SQL_MODE=@OLD_SQL_MODE;
260 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
261 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
263 -- -----------------------------------------------------
264 -- Test Data
265 -- -----------------------------------------------------
266 USE `pkudb`;
267 INSERT INTO Samples (SampleID) VALUES ('gdxj0000bl00');
268 INSERT INTO Samples (SampleID) VALUES ('gdxj9999ms99');
269 INSERT INTO Samples (SampleID) VALUES ('gdxj0001bl01');
270 INSERT INTO Samples (SampleID,DateCollected) VALUES ('gdxj0001bl02','2012-07-05 18:11:12');
271 SELECT AnimalID,Date1stSampling FROM Animals;
272 INSERT INTO Samples (SampleID,DateCollected) VALUES ('gdxj0001bl12','2012-06-05');
273 SELECT AnimalID,Date1stSampling FROM Animals;
274 INSERT INTO Samples (SampleID,DateCollected) VALUES ('gdxj0001bl32','2012-08-05 17:11:12');
275 SELECT AnimalID,Date1stSampling FROM Animals;
276 UPDATE Samples SET LabelPrintedCnt=LabelPrintedCnt+1 WHERE SampleID='gdxj0001bl12';
277 SELECT SampleID,SourceID,SourceCnt,TissueID,TissueCnt,AnimalID,DateCollected,LabelPrintedCnt FROM Samples;