sql/pku201211.sql

   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';
   4
   5 DROP DATABASE `pkudb`;
   6
   7 CREATE SCHEMA IF NOT EXISTS `pkudb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
   8 SHOW WARNINGS;
   9 USE `pkudb` ;
  10
  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;
  48
  49 SHOW WARNINGS;
  50
  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;
  63
  64 SHOW WARNINGS;
  65
  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;
  80
  81 SHOW WARNINGS;
  82
  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;
  96
  97 SHOW WARNINGS;
  98
  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;
 110
 111 SHOW WARNINGS;
 112
 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;
 131
 132 SHOW WARNINGS;
 133
 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;
 143
 144 SHOW WARNINGS;
 145
 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;
 157
 158 SHOW WARNINGS;
 159
 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;
 178
 179 SHOW WARNINGS;
 180
 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;
 190
 191 SHOW WARNINGS;
 192
 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;
 215
 216 SHOW WARNINGS;
 217 USE `pkudb`;
 218
 219 DELIMITER $$
 220 SHOW WARNINGS$$
 221 USE `pkudb`$$
 222
 223
 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; $$
 241
 242 SHOW WARNINGS$$
 243
 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; $$
 255
 256 DELIMITER ;
 257
 258
 259 SET SQL_MODE=@OLD_SQL_MODE;
 260 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
 261 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
 262
 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;