Project

General

Profile

« Previous | Next » 

Revision 7750

Added schemas/BIEN2/

View differences:

schemas/BIEN2/datasource_schema/bien_web_datasource_schema.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';
4

  
5
CREATE SCHEMA IF NOT EXISTS `bien_web` DEFAULT CHARACTER SET latin1 ;
6
USE `bien_web` ;
7

  
8
-- -----------------------------------------------------
9
-- Table `bien_web`.`datasource`
10
-- -----------------------------------------------------
11
CREATE  TABLE IF NOT EXISTS `bien_web`.`datasource` (
12
  `dataSourceID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
13
  `dataSourceName` VARCHAR(100) NULL DEFAULT NULL ,
14
  `aggregatorOrPrimary` VARCHAR(25) NULL DEFAULT NULL ,
15
  `sourceType` VARCHAR(25) NULL DEFAULT NULL ,
16
  `accessLevel` VARCHAR(50) NULL DEFAULT NULL ,
17
  `accessConditions` VARCHAR(500) NULL DEFAULT NULL ,
18
  `authorshipContactEmail` VARCHAR(150) NULL DEFAULT NULL ,
19
  PRIMARY KEY (`dataSourceID`) ,
20
  UNIQUE INDEX `dataSourceName` (`dataSourceName` ASC) ,
21
  INDEX `aggregatorOrPrimary` (`aggregatorOrPrimary` ASC) ,
22
  INDEX `sourceType` (`sourceType` ASC) ,
23
  INDEX `accessLevel` (`accessLevel` ASC) )
24
ENGINE = MyISAM
25
DEFAULT CHARACTER SET = utf8;
26

  
27

  
28
-- -----------------------------------------------------
29
-- Table `bien_web`.`dataSourceNormalized`
30
-- -----------------------------------------------------
31
CREATE  TABLE IF NOT EXISTS `bien_web`.`dataSourceNormalized` (
32
  `dataSourceNormalizedID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
33
  `dataSourceID` INT(11) UNSIGNED NOT NULL ,
34
  `dataSourceNameCorrected` VARCHAR(255) NOT NULL ,
35
  `isHerbarium` INT(1) NOT NULL DEFAULT 0 ,
36
  `isAggregator` INT(1) NOT NULL DEFAULT 0 ,
37
  PRIMARY KEY (`dataSourceNormalizedID`) ,
38
  INDEX `dataSourceID` (`dataSourceID` ASC) ,
39
  INDEX `dataSourceName` (`dataSourceNameCorrected` ASC) ,
40
  INDEX `isHerbarium` (`isHerbarium` ASC) ,
41
  INDEX `fk_dataSourceNameCorrected_datasource1` (`dataSourceID` ASC) ,
42
  CONSTRAINT `fk_dataSourceNameCorrected_datasource1`
43
    FOREIGN KEY (`dataSourceID` )
44
    REFERENCES `bien_web`.`datasource` (`dataSourceID` )
45
    ON DELETE NO ACTION
46
    ON UPDATE NO ACTION)
47
ENGINE = InnoDB
48
AUTO_INCREMENT = 1578
49
DEFAULT CHARACTER SET = latin1;
50

  
51

  
52
-- -----------------------------------------------------
53
-- Table `bien_web`.`party`
54
-- -----------------------------------------------------
55
CREATE  TABLE IF NOT EXISTS `bien_web`.`party` (
56
  `partyID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
57
  `partyType` VARCHAR(20) NOT NULL DEFAULT 'person' COMMENT '\'person\',\'institution\'' ,
58
  `partyFullName` VARCHAR(100) NOT NULL ,
59
  `userName` VARCHAR(50) NULL DEFAULT NULL ,
60
  `lastName` VARCHAR(50) NULL DEFAULT NULL ,
61
  `firstName` VARCHAR(50) NULL DEFAULT NULL ,
62
  `email` VARCHAR(150) NULL DEFAULT NULL ,
63
  `primaryInstitution` VARCHAR(255) NULL DEFAULT NULL ,
64
  `institutionName` VARCHAR(150) NULL DEFAULT NULL ,
65
  `department` VARCHAR(150) NULL DEFAULT NULL ,
66
  `address1` VARCHAR(150) NULL DEFAULT NULL ,
67
  `address2` VARCHAR(150) NULL DEFAULT NULL ,
68
  `city` VARCHAR(50) NULL DEFAULT NULL ,
69
  `stateProvince` VARCHAR(50) NULL DEFAULT NULL ,
70
  `postalCode` VARCHAR(25) NULL DEFAULT NULL ,
71
  `country` VARCHAR(50) NULL DEFAULT NULL ,
72
  `stateProvinceCode` VARCHAR(20) NULL DEFAULT NULL ,
73
  PRIMARY KEY (`partyID`) ,
74
  INDEX `partyType` (`partyType` ASC) ,
75
  INDEX `lastName` (`lastName` ASC) ,
76
  INDEX `firstName` (`firstName` ASC) ,
77
  INDEX `fullName` (`partyFullName` ASC) ,
78
  INDEX `institutionName` (`institutionName` ASC) ,
79
  INDEX `country` (`country` ASC) )
80
ENGINE = MyISAM
81
DEFAULT CHARACTER SET = utf8;
82

  
83

  
84
-- -----------------------------------------------------
85
-- Table `bien_web`.`datasource_party`
86
-- -----------------------------------------------------
87
CREATE  TABLE IF NOT EXISTS `bien_web`.`datasource_party` (
88
  `datasourceNormalized_party_ID` INT(11) NOT NULL AUTO_INCREMENT ,
89
  `dataSourceNormalizedID` INT(11) UNSIGNED NOT NULL ,
90
  `partyID` INT(11) UNSIGNED NOT NULL ,
91
  `userRole` VARCHAR(50) NULL DEFAULT NULL ,
92
  `isPI` INT(1) NULL DEFAULT NULL ,
93
  UNIQUE INDEX `user_datasource` (`partyID` ASC, `dataSourceNormalizedID` ASC) ,
94
  INDEX `userRole` (`userRole` ASC) ,
95
  INDEX `isPI` (`isPI` ASC) ,
96
  INDEX `fk_datasource_party_party1` (`partyID` ASC) ,
97
  INDEX `fk_datasource_party_dataSourceNormalized1` (`dataSourceNormalizedID` ASC) ,
98
  PRIMARY KEY (`datasourceNormalized_party_ID`) ,
99
  UNIQUE INDEX `datasourceNormalized_party_ID_UNIQUE` (`datasourceNormalized_party_ID` ASC) ,
100
  CONSTRAINT `fk_datasource_party_party1`
101
    FOREIGN KEY (`partyID` )
102
    REFERENCES `bien_web`.`party` (`partyID` )
103
    ON DELETE NO ACTION
104
    ON UPDATE NO ACTION,
105
  CONSTRAINT `fk_datasource_party_dataSourceNormalized1`
106
    FOREIGN KEY (`dataSourceNormalizedID` )
107
    REFERENCES `bien_web`.`dataSourceNormalized` (`dataSourceNormalizedID` )
108
    ON DELETE NO ACTION
109
    ON UPDATE NO ACTION)
110
ENGINE = MyISAM
111
DEFAULT CHARACTER SET = utf8;
112

  
113

  
114
-- -----------------------------------------------------
115
-- Table `bien_web`.`plot`
116
-- -----------------------------------------------------
117
CREATE  TABLE IF NOT EXISTS `bien_web`.`plot` (
118
  `plotID` INT(11) UNSIGNED NOT NULL COMMENT '=DBPlotID from bien2' ,
119
  `dataSourceID` INT(11) UNSIGNED NULL DEFAULT NULL ,
120
  `dataSourceName` VARCHAR(100) NULL DEFAULT NULL ,
121
  `plotCode` VARCHAR(100) NULL DEFAULT NULL ,
122
  `plotAreaHa` DOUBLE NULL DEFAULT NULL ,
123
  `plotMinDbh` DECIMAL(5,1) NULL DEFAULT NULL ,
124
  `plotMethod` VARCHAR(100) NULL DEFAULT NULL ,
125
  `country` VARCHAR(50) NULL DEFAULT NULL ,
126
  `stateProvince` VARCHAR(100) NULL DEFAULT NULL ,
127
  `countyParish` VARCHAR(100) NULL DEFAULT NULL ,
128
  `latitude` DECIMAL(10,5) NULL DEFAULT NULL ,
129
  `longitude` DECIMAL(10,5) NULL DEFAULT NULL ,
130
  `isValidLatLong` INT(1) NULL DEFAULT NULL ,
131
  `isGeovalid` INT(1) NULL DEFAULT NULL ,
132
  `elevation_m` DECIMAL(6,1) NULL DEFAULT NULL ,
133
  `localityDescription` VARCHAR(500) NULL DEFAULT NULL ,
134
  PRIMARY KEY (`plotID`) ,
135
  INDEX `dataSourceName` (`dataSourceName` ASC) ,
136
  INDEX `dataSourceID` (`dataSourceID` ASC) ,
137
  INDEX `plotID` (`plotID` ASC) ,
138
  INDEX `country` (`country` ASC) ,
139
  INDEX `stateProvince` (`stateProvince` ASC) ,
140
  INDEX `plotCode` (`plotCode` ASC) ,
141
  INDEX `plotAreaHa` (`plotAreaHa` ASC) ,
142
  INDEX `plotMinDbh` (`plotMinDbh` ASC) ,
143
  INDEX `plotMethod` (`plotMethod` ASC) ,
144
  INDEX `isValidLatLong` (`isValidLatLong` ASC) ,
145
  INDEX `isGeovalid` (`isGeovalid` ASC) ,
146
  INDEX `fk_plot_datasource1` (`dataSourceID` ASC) ,
147
  CONSTRAINT `fk_plot_datasource1`
148
    FOREIGN KEY (`dataSourceID` )
149
    REFERENCES `bien_web`.`datasource` (`dataSourceID` )
150
    ON DELETE NO ACTION
151
    ON UPDATE NO ACTION)
152
ENGINE = MyISAM
153
DEFAULT CHARACTER SET = utf8;
154

  
155

  
156
-- -----------------------------------------------------
157
-- Table `bien_web`.`taxon`
158
-- -----------------------------------------------------
159
CREATE  TABLE IF NOT EXISTS `bien_web`.`taxon` (
160
  `taxonID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
161
  `parentTaxonID` INT(10) UNSIGNED NULL DEFAULT NULL ,
162
  `taxon` VARCHAR(255) NOT NULL ,
163
  `rank` VARCHAR(255) NULL DEFAULT NULL ,
164
  `nameClass` VARCHAR(50) NULL DEFAULT NULL ,
165
  `taxonAuthor` VARCHAR(255) NULL DEFAULT NULL ,
166
  `family` VARCHAR(150) NULL DEFAULT NULL ,
167
  `genus` VARCHAR(100) NULL DEFAULT NULL ,
168
  `species` VARCHAR(100) NULL DEFAULT NULL ,
169
  `leftIndex` INT(11) NULL DEFAULT NULL ,
170
  `rightIndex` INT(11) NULL DEFAULT NULL ,
171
  `isAccepted` INT(1) NULL DEFAULT NULL ,
172
  `isHybrid` INT(10) UNSIGNED NOT NULL DEFAULT '0' ,
173
  PRIMARY KEY (`taxonID`) ,
174
  UNIQUE INDEX `leftIndex` (`leftIndex` ASC) ,
175
  UNIQUE INDEX `rightIndex` (`rightIndex` ASC) ,
176
  INDEX `taxon` (`taxon` ASC) ,
177
  INDEX `taxonAuthor` (`taxonAuthor` ASC) ,
178
  INDEX `rank` (`rank` ASC) ,
179
  INDEX `nameClass` (`nameClass` ASC) ,
180
  INDEX `family` (`family` ASC) ,
181
  INDEX `genus` (`genus` ASC) ,
182
  INDEX `species` (`species` ASC) ,
183
  INDEX `isHybrid` (`isHybrid` ASC) ,
184
  INDEX `parentTaxonID` (`parentTaxonID` ASC) )
185
ENGINE = MyISAM
186
DEFAULT CHARACTER SET = utf8;
187

  
188

  
189
-- -----------------------------------------------------
190
-- Table `bien_web`.`observation`
191
-- -----------------------------------------------------
192
CREATE  TABLE IF NOT EXISTS `bien_web`.`observation` (
193
  `observationID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
194
  `dataSourceID` INT(11) UNSIGNED NULL DEFAULT NULL ,
195
  `dataSourceName` VARCHAR(100) NULL DEFAULT NULL ,
196
  `dataSourceNamePrimary` VARCHAR(100) NULL DEFAULT NULL ,
197
  `plotID` INT(11) UNSIGNED NULL DEFAULT NULL ,
198
  `observationType` VARCHAR(10) NULL DEFAULT NULL COMMENT '=plot,specimen,trait)' ,
199
  `bien2_taxonomyID` INT(11) UNSIGNED NULL DEFAULT NULL ,
200
  `bien2_DBPlotID` INT(11) UNSIGNED NULL DEFAULT NULL ,
201
  `bien2_ObservationID` INT(11) UNSIGNED NULL DEFAULT NULL ,
202
  `bien2_OccurID` INT(11) UNSIGNED NULL DEFAULT NULL ,
203
  `bien2_TraitObservationID` INT(11) UNSIGNED NULL DEFAULT NULL ,
204
  `taxonAuthorityVerbatim` VARCHAR(255) NULL DEFAULT NULL ,
205
  `higherPlantGroup` VARCHAR(25) NULL DEFAULT NULL ,
206
  `taxonID` INT(10) UNSIGNED NULL ,
207
  `family` VARCHAR(100) NULL DEFAULT NULL ,
208
  `genus` VARCHAR(100) NULL DEFAULT NULL ,
209
  `species` VARCHAR(150) NULL DEFAULT NULL ,
210
  `taxon` VARCHAR(200) NULL DEFAULT NULL ,
211
  `taxonAuthor` VARCHAR(255) NULL DEFAULT NULL ,
212
  `taxonMorphospecies` VARCHAR(255) NULL DEFAULT NULL ,
213
  `rank` VARCHAR(50) NULL DEFAULT NULL ,
214
  `acceptance` VARCHAR(2) NULL DEFAULT NULL ,
215
  `country` VARCHAR(50) NULL DEFAULT NULL ,
216
  `stateProvince` VARCHAR(100) NULL DEFAULT NULL ,
217
  `countyParish` VARCHAR(100) NULL DEFAULT NULL ,
218
  `countryError` DOUBLE NULL DEFAULT NULL ,
219
  `stateProvinceError` DOUBLE NULL DEFAULT NULL ,
220
  `localityDescription` VARCHAR(500) NULL DEFAULT NULL ,
221
  `collector` VARCHAR(150) NULL DEFAULT NULL ,
222
  `collectionNumber` VARCHAR(50) NULL DEFAULT NULL ,
223
  `identifiedBy` VARCHAR(150) NULL DEFAULT NULL ,
224
  `observationDate` DATE NULL DEFAULT NULL ,
225
  `plotCode` VARCHAR(100) NULL DEFAULT NULL ,
226
  `plotAreaHa` DOUBLE NULL DEFAULT NULL ,
227
  `plotMinDbh` DECIMAL(5,1) NULL DEFAULT NULL ,
228
  `plotMethod` VARCHAR(100) NULL DEFAULT NULL ,
229
  `latitude` DECIMAL(10,5) NULL DEFAULT NULL ,
230
  `longitude` DECIMAL(10,5) NULL DEFAULT NULL ,
231
  `isValidLatLong` INT(1) NULL DEFAULT NULL ,
232
  `isGeovalid` INT(1) NULL DEFAULT NULL ,
233
  `isNewWorld` INT(1) NULL DEFAULT NULL ,
234
  `elevation_m` DECIMAL(6,1) NULL DEFAULT NULL ,
235
  `isCultivated` INT(1) NULL DEFAULT '0' ,
236
  `isCultivatedReason` VARCHAR(255) NULL DEFAULT NULL ,
237
  `abund` INT(11) NULL DEFAULT NULL ,
238
  `abund1` INT(11) NULL DEFAULT NULL ,
239
  `abund2.5` INT(11) NULL DEFAULT NULL ,
240
  `abund10` INT(11) NULL DEFAULT NULL ,
241
  `pctCover` DECIMAL(10,5) NULL DEFAULT NULL ,
242
  `traitName` VARCHAR(200) NULL DEFAULT NULL ,
243
  `traitValue` VARCHAR(100) NULL DEFAULT NULL ,
244
  `traitUnit` VARCHAR(100) NULL DEFAULT NULL ,
245
  `traitMethod` VARCHAR(255) NULL DEFAULT NULL ,
246
  PRIMARY KEY (`observationID`) ,
247
  INDEX `observationID` (`observationID` ASC) ,
248
  INDEX `dataSourceID` (`dataSourceID` ASC) ,
249
  INDEX `dataSourceName` (`dataSourceName` ASC) ,
250
  INDEX `dataSourceNamePrimary` (`dataSourceNamePrimary` ASC) ,
251
  INDEX `plotID` (`plotID` ASC) ,
252
  INDEX `observationType` (`observationType` ASC) ,
253
  INDEX `bien2_taxonomyID` (`bien2_taxonomyID` ASC) ,
254
  INDEX `bien2_DBPlotID` (`bien2_DBPlotID` ASC) ,
255
  INDEX `bien2_ObservationID` (`bien2_ObservationID` ASC) ,
256
  INDEX `bien2_OccurID` (`bien2_OccurID` ASC) ,
257
  INDEX `taxonAuthorityVerbatim` (`taxonAuthorityVerbatim` ASC) ,
258
  INDEX `higherPlantGroup` (`higherPlantGroup` ASC) ,
259
  INDEX `family` (`family` ASC) ,
260
  INDEX `genus` (`genus` ASC) ,
261
  INDEX `species` (`species` ASC) ,
262
  INDEX `taxon` (`taxon` ASC) ,
263
  INDEX `taxonAuthor` (`taxonAuthor` ASC) ,
264
  INDEX `taxonMorphospecies` (`taxonMorphospecies` ASC) ,
265
  INDEX `rank` (`rank` ASC) ,
266
  INDEX `acceptance` (`acceptance` ASC) ,
267
  INDEX `country` (`country` ASC) ,
268
  INDEX `stateProvince` (`stateProvince` ASC) ,
269
  INDEX `collector` (`collector` ASC) ,
270
  INDEX `identifiedBy` (`identifiedBy` ASC) ,
271
  INDEX `plotCode` (`plotCode` ASC) ,
272
  INDEX `plotAreaHa` (`plotAreaHa` ASC) ,
273
  INDEX `plotMinDbh` (`plotMinDbh` ASC) ,
274
  INDEX `plotMethod` (`plotMethod` ASC) ,
275
  INDEX `isValidLatLong` (`isValidLatLong` ASC) ,
276
  INDEX `isGeovalid` (`isGeovalid` ASC) ,
277
  INDEX `isNewWorld` (`isNewWorld` ASC) ,
278
  INDEX `isCultivated` (`isCultivated` ASC) ,
279
  INDEX `isCultivatedReason` (`isCultivatedReason` ASC) ,
280
  INDEX `bien2_TraitObservationID` (`bien2_TraitObservationID` ASC) ,
281
  INDEX `traitName` (`traitName` ASC) ,
282
  INDEX `traitMethod` (`traitMethod` ASC) ,
283
  INDEX `traitUnit` (`traitUnit` ASC) ,
284
  INDEX `fk_observation_datasource` (`dataSourceID` ASC) ,
285
  INDEX `fk_observation_plot1` (`plotID` ASC) ,
286
  INDEX `fk_observation_taxon1` (`taxonID` ASC) ,
287
  CONSTRAINT `fk_observation_datasource`
288
    FOREIGN KEY (`dataSourceID` )
289
    REFERENCES `bien_web`.`datasource` (`dataSourceID` )
290
    ON DELETE NO ACTION
291
    ON UPDATE NO ACTION,
292
  CONSTRAINT `fk_observation_plot1`
293
    FOREIGN KEY (`plotID` )
294
    REFERENCES `bien_web`.`plot` (`plotID` )
295
    ON DELETE NO ACTION
296
    ON UPDATE NO ACTION,
297
  CONSTRAINT `fk_observation_taxon1`
298
    FOREIGN KEY (`taxonID` )
299
    REFERENCES `bien_web`.`taxon` (`taxonID` )
300
    ON DELETE NO ACTION
301
    ON UPDATE NO ACTION)
302
ENGINE = MyISAM
303
AUTO_INCREMENT = 10052218
304
DEFAULT CHARACTER SET = utf8;
305

  
306

  
307

  
308
SET SQL_MODE=@OLD_SQL_MODE;
309
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
310
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Also available in: Unified diff