1 |
7772
|
aaronmk
|
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';
|
4 |
|
|
|
5 |
|
|
ALTER TABLE `default_schema`.`bien_genus_family` ENGINE = InnoDB ;
|
6 |
|
|
|
7 |
|
|
ALTER TABLE `default_schema`.`clade` ENGINE = InnoDB ;
|
8 |
|
|
|
9 |
|
|
ALTER TABLE `default_schema`.`cladeName` ENGINE = InnoDB ;
|
10 |
|
|
|
11 |
|
|
ALTER TABLE `default_schema`.`geoIndividualObservation` CHANGE COLUMN `ID` `ID` INT(11) NOT NULL COMMENT 'Links 1:1 with DBPlotID' ,
|
12 |
|
|
ADD CONSTRAINT `geoindividualobservation_ibfk_1`
|
13 |
|
|
FOREIGN KEY (`ID` )
|
14 |
|
|
REFERENCES `default_schema`.`PlotMetaDataDimension` (`DBPlotID` )
|
15 |
|
|
ON DELETE CASCADE
|
16 |
|
|
ON UPDATE CASCADE;
|
17 |
|
|
|
18 |
|
|
ALTER TABLE `default_schema`.`geoPlotMetaDataDimension` CHANGE COLUMN `ID` `ID` INT(11) NOT NULL COMMENT 'Links 1:1 with DBPlotID' ,
|
19 |
|
|
ADD CONSTRAINT `geoplotmetadatadimension_ibfk_1`
|
20 |
|
|
FOREIGN KEY (`ID` )
|
21 |
|
|
REFERENCES `default_schema`.`PlotMetaDataDimension` (`DBPlotID` )
|
22 |
|
|
ON DELETE CASCADE
|
23 |
|
|
ON UPDATE CASCADE;
|
24 |
|
|
|
25 |
|
|
ALTER TABLE `default_schema`.`IndividualObservation`
|
26 |
|
|
ADD CONSTRAINT `individualobservation_ibfk_3`
|
27 |
|
|
FOREIGN KEY (`TaxonomyID` )
|
28 |
|
|
REFERENCES `default_schema`.`TaxonDimension` (`TaxonomyID` )
|
29 |
|
|
ON DELETE CASCADE
|
30 |
|
|
ON UPDATE CASCADE,
|
31 |
|
|
ADD CONSTRAINT `individualobservation_ibfk_1`
|
32 |
|
|
FOREIGN KEY (`ObservationSpecimenID` )
|
33 |
|
|
REFERENCES `default_schema`.`ObservationSpecimen` (`ObservationSpecimenID` )
|
34 |
|
|
ON DELETE CASCADE
|
35 |
|
|
ON UPDATE CASCADE,
|
36 |
|
|
ADD CONSTRAINT `individualobservation_ibfk_2`
|
37 |
|
|
FOREIGN KEY (`DBPlotID` )
|
38 |
|
|
REFERENCES `default_schema`.`PlotMetaDataDimension` (`DBPlotID` )
|
39 |
|
|
ON DELETE CASCADE
|
40 |
|
|
ON UPDATE CASCADE;
|
41 |
|
|
|
42 |
|
|
ALTER TABLE `default_schema`.`IndividualObservationMeasurement` ENGINE = InnoDB , ADD COLUMN `new` VARCHAR(45) NULL DEFAULT NULL AFTER `test`
|
43 |
|
|
, DROP PRIMARY KEY
|
44 |
|
|
, ADD PRIMARY KEY (`StemNO`)
|
45 |
|
|
, ADD UNIQUE INDEX `ObservationID` (`ObservationID` ASC, `StemNO` ASC, `ObservationNO` ASC) ;
|
46 |
|
|
|
47 |
|
|
ALTER TABLE `default_schema`.`ObservationSpecimen`
|
48 |
|
|
ADD CONSTRAINT `observationspecimen_ibfk_1`
|
49 |
|
|
FOREIGN KEY (`DBSourceID` )
|
50 |
|
|
REFERENCES `default_schema`.`SpecimenSourceData` (`DBSourceID` )
|
51 |
|
|
ON DELETE CASCADE
|
52 |
|
|
ON UPDATE CASCADE;
|
53 |
|
|
|
54 |
|
|
ALTER TABLE `default_schema`.`PlotAggregateFact`
|
55 |
|
|
ADD CONSTRAINT `plotaggregatefact_ibfk_3`
|
56 |
|
|
FOREIGN KEY (`TaxonomyID` )
|
57 |
|
|
REFERENCES `default_schema`.`TaxonDimension` (`TaxonomyID` )
|
58 |
|
|
ON DELETE CASCADE
|
59 |
|
|
ON UPDATE CASCADE,
|
60 |
|
|
ADD CONSTRAINT `plotaggregatefact_ibfk_1`
|
61 |
|
|
FOREIGN KEY (`DBPlotID` )
|
62 |
|
|
REFERENCES `default_schema`.`PlotMetaDataDimension` (`DBPlotID` )
|
63 |
|
|
ON DELETE CASCADE
|
64 |
|
|
ON UPDATE CASCADE,
|
65 |
|
|
ADD CONSTRAINT `plotaggregatefact_ibfk_2`
|
66 |
|
|
FOREIGN KEY (`PlotTimeID` )
|
67 |
|
|
REFERENCES `default_schema`.`PlotTimeDimension` (`PlotTimeID` )
|
68 |
|
|
ON DELETE CASCADE
|
69 |
|
|
ON UPDATE CASCADE
|
70 |
|
|
, DROP INDEX `DBPlotID_4`
|
71 |
|
|
, DROP INDEX `DBPlotID_3`
|
72 |
|
|
, DROP INDEX `DBPlotID_2` ;
|
73 |
|
|
|
74 |
|
|
ALTER TABLE `default_schema`.`PlotTimeDimension`
|
75 |
|
|
ADD CONSTRAINT `plottimedimension_ibfk_1`
|
76 |
|
|
FOREIGN KEY (`DBPlotID` )
|
77 |
|
|
REFERENCES `default_schema`.`PlotMetaDataDimension` (`DBPlotID` )
|
78 |
|
|
ON DELETE CASCADE
|
79 |
|
|
ON UPDATE CASCADE
|
80 |
|
|
, DROP INDEX `CensusName_8`
|
81 |
|
|
, DROP INDEX `CensusName_7`
|
82 |
|
|
, DROP INDEX `CensusName_6`
|
83 |
|
|
, DROP INDEX `CensusName_4`
|
84 |
|
|
, DROP INDEX `CensusName_3` ;
|
85 |
|
|
|
86 |
|
|
ALTER TABLE `default_schema`.`TaxonDataSource` CHANGE COLUMN `TaxonomyID` `TaxonomyID` INT(11) NOT NULL ,
|
87 |
|
|
ADD CONSTRAINT `TaxonDataSource_ibfk_2`
|
88 |
|
|
FOREIGN KEY (`TaxonomyID` )
|
89 |
|
|
REFERENCES `default_schema`.`TaxonDimension` (`TaxonomyID` )
|
90 |
|
|
ON DELETE CASCADE
|
91 |
|
|
ON UPDATE CASCADE,
|
92 |
|
|
ADD CONSTRAINT `TaxonDataSource_ibfk_1`
|
93 |
|
|
FOREIGN KEY (`DataSourceID` )
|
94 |
|
|
REFERENCES `default_schema`.`DataSource` (`DataSourceID` )
|
95 |
|
|
ON DELETE CASCADE
|
96 |
|
|
ON UPDATE CASCADE;
|
97 |
|
|
|
98 |
|
|
ALTER TABLE `default_schema`.`TaxonObservation`
|
99 |
|
|
ADD CONSTRAINT `taxonobservation_ibfk_3`
|
100 |
|
|
FOREIGN KEY (`TaxonomyID` )
|
101 |
|
|
REFERENCES `default_schema`.`TaxonDimension` (`TaxonomyID` )
|
102 |
|
|
ON DELETE CASCADE
|
103 |
|
|
ON UPDATE CASCADE,
|
104 |
|
|
ADD CONSTRAINT `taxonobservation_ibfk_1`
|
105 |
|
|
FOREIGN KEY (`DBPlotID` )
|
106 |
|
|
REFERENCES `default_schema`.`PlotMetaDataDimension` (`DBPlotID` )
|
107 |
|
|
ON DELETE CASCADE
|
108 |
|
|
ON UPDATE CASCADE,
|
109 |
|
|
ADD CONSTRAINT `taxonobservation_ibfk_2`
|
110 |
|
|
FOREIGN KEY (`DBSourceID` )
|
111 |
|
|
REFERENCES `default_schema`.`SpecimenSourceData` (`DBSourceID` )
|
112 |
|
|
ON DELETE CASCADE
|
113 |
|
|
ON UPDATE CASCADE
|
114 |
|
|
, ADD INDEX `DBSourceID` (`DBSourceID` ASC)
|
115 |
|
|
, DROP INDEX `TaxonomyID_4`
|
116 |
|
|
, DROP INDEX `TaxonomyID_3` ;
|
117 |
|
|
|
118 |
|
|
ALTER TABLE `default_schema`.`TraitObservation` CHANGE COLUMN `TaxonomyID` `TaxonomyID` INT(11) NOT NULL ,
|
119 |
|
|
ADD CONSTRAINT `traitobservation_ibfk_1`
|
120 |
|
|
FOREIGN KEY (`TaxonomyID` )
|
121 |
|
|
REFERENCES `default_schema`.`TaxonDimension` (`TaxonomyID` )
|
122 |
|
|
ON DELETE CASCADE
|
123 |
|
|
ON UPDATE CASCADE;
|
124 |
|
|
|
125 |
|
|
ALTER TABLE `default_schema`.`viewFullOccurrence` ENGINE = InnoDB , CHANGE COLUMN `DBPlotID` `DBPlotID` INT(11) NOT NULL , CHANGE COLUMN `TaxonomyID` `TaxonomyID` INT(11) NOT NULL ;
|
126 |
|
|
|
127 |
|
|
DROP TABLE IF EXISTS `default_schema`.`viewFullOccurrence_bak20110825` ;
|
128 |
|
|
|
129 |
|
|
DROP TABLE IF EXISTS `default_schema`.`TaxonDimension_bak` ;
|
130 |
|
|
|
131 |
|
|
|
132 |
|
|
SET SQL_MODE=@OLD_SQL_MODE;
|
133 |
|
|
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
|
134 |
|
|
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
|