|
1
|
-- MySQL dump 10.13 Distrib 5.5.34, for debian-linux-gnu (x86_64)
|
|
2
|
--
|
|
3
|
-- Host: localhost Database: VegCore
|
|
4
|
-- ------------------------------------------------------
|
|
5
|
-- Server version 5.5.34-0ubuntu0.12.04.1
|
|
6
|
|
|
7
|
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
|
|
8
|
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
|
|
9
|
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
|
|
10
|
/*!40101 SET NAMES utf8 */;
|
|
11
|
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
|
|
12
|
/*!40103 SET TIME_ZONE='+00:00' */;
|
|
13
|
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
|
|
14
|
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
|
|
15
|
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
|
|
16
|
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
|
|
17
|
|
|
18
|
--
|
|
19
|
-- Current Database: `VegCore`
|
|
20
|
--
|
|
21
|
|
|
22
|
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `VegCore` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */;
|
|
23
|
|
|
24
|
USE `VegCore`;
|
|
25
|
|
|
26
|
--
|
|
27
|
-- Table structure for table `aggregate_observation`
|
|
28
|
--
|
|
29
|
|
|
30
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
31
|
/*!40101 SET character_set_client = utf8 */;
|
|
32
|
CREATE TABLE `aggregate_observation` (
|
|
33
|
`id` varbinary(767) NOT NULL,
|
|
34
|
`occurrence_status` varbinary(767) DEFAULT NULL,
|
|
35
|
PRIMARY KEY (`id`),
|
|
36
|
CONSTRAINT `fk_taxon_presence_taxon_determination10` FOREIGN KEY (`id`) REFERENCES `taxon_determination` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
37
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
|
|
38
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
39
|
|
|
40
|
--
|
|
41
|
-- Dumping data for table `aggregate_observation`
|
|
42
|
--
|
|
43
|
|
|
44
|
/*!40000 ALTER TABLE `aggregate_observation` DISABLE KEYS */;
|
|
45
|
/*!40000 ALTER TABLE `aggregate_observation` ENABLE KEYS */;
|
|
46
|
|
|
47
|
--
|
|
48
|
-- Table structure for table `base_class`
|
|
49
|
--
|
|
50
|
|
|
51
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
52
|
/*!40101 SET character_set_client = utf8 */;
|
|
53
|
CREATE TABLE `base_class` (
|
|
54
|
`id` varbinary(767) NOT NULL,
|
|
55
|
`referenced_class` varbinary(767) NOT NULL,
|
|
56
|
PRIMARY KEY (`id`),
|
|
57
|
KEY `fk_base_class_referenced_class1_idx` (`referenced_class`),
|
|
58
|
CONSTRAINT `fk_base_class_referenced_class1` FOREIGN KEY (`referenced_class`) REFERENCES `referenced_class` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
59
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
|
|
60
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
61
|
|
|
62
|
--
|
|
63
|
-- Dumping data for table `base_class`
|
|
64
|
--
|
|
65
|
|
|
66
|
/*!40000 ALTER TABLE `base_class` DISABLE KEYS */;
|
|
67
|
/*!40000 ALTER TABLE `base_class` ENABLE KEYS */;
|
|
68
|
|
|
69
|
--
|
|
70
|
-- Table structure for table `collection`
|
|
71
|
--
|
|
72
|
|
|
73
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
74
|
/*!40101 SET character_set_client = utf8 */;
|
|
75
|
CREATE TABLE `collection` (
|
|
76
|
`id` varbinary(767) NOT NULL,
|
|
77
|
`institution` varbinary(767) NOT NULL,
|
|
78
|
`name` varbinary(767) NOT NULL,
|
|
79
|
PRIMARY KEY (`id`),
|
|
80
|
UNIQUE KEY `collection_unique` (`institution`,`name`),
|
|
81
|
KEY `fk_collection_organization1_idx` (`institution`),
|
|
82
|
KEY `fk_collection_source1_idx` (`id`),
|
|
83
|
CONSTRAINT `fk_collection_organization1` FOREIGN KEY (`institution`) REFERENCES `organization` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
84
|
CONSTRAINT `fk_collection_source1` FOREIGN KEY (`id`) REFERENCES `traceable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
85
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='"the collection within the institution where a specimen is held" ("Brad Boyle":https://projects.nceas.ucsb.edu/nceas/attachments/download/621/vegbien_identifier_examples.xlsx#terms/collectionCode)';
|
|
86
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
87
|
|
|
88
|
--
|
|
89
|
-- Dumping data for table `collection`
|
|
90
|
--
|
|
91
|
|
|
92
|
/*!40000 ALTER TABLE `collection` DISABLE KEYS */;
|
|
93
|
/*!40000 ALTER TABLE `collection` ENABLE KEYS */;
|
|
94
|
|
|
95
|
--
|
|
96
|
-- Table structure for table `community`
|
|
97
|
--
|
|
98
|
|
|
99
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
100
|
/*!40101 SET character_set_client = utf8 */;
|
|
101
|
CREATE TABLE `community` (
|
|
102
|
`id` varbinary(767) NOT NULL,
|
|
103
|
`name` varbinary(767) NOT NULL,
|
|
104
|
`info` set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
105
|
PRIMARY KEY (`id`),
|
|
106
|
CONSTRAINT `fk_community_record1` FOREIGN KEY (`id`) REFERENCES `traceable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
107
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='"A community type is an abstract grouping of organisms that tend to co-occur on the landscape due to shared ecological requirements or preferences. May be designated by syntaxon or physiognomic types." ([[VegCore#VegX|VegX]])';
|
|
108
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
109
|
|
|
110
|
--
|
|
111
|
-- Dumping data for table `community`
|
|
112
|
--
|
|
113
|
|
|
114
|
/*!40000 ALTER TABLE `community` DISABLE KEYS */;
|
|
115
|
/*!40000 ALTER TABLE `community` ENABLE KEYS */;
|
|
116
|
|
|
117
|
--
|
|
118
|
-- Table structure for table `data_owner`
|
|
119
|
--
|
|
120
|
|
|
121
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
122
|
/*!40101 SET character_set_client = utf8 */;
|
|
123
|
CREATE TABLE `data_owner` (
|
|
124
|
`id` varbinary(767) NOT NULL,
|
|
125
|
`use_conditions` varbinary(767) DEFAULT NULL COMMENT 'citation requirements, etc.',
|
|
126
|
PRIMARY KEY (`id`),
|
|
127
|
CONSTRAINT `fk_party_list_entry_record10` FOREIGN KEY (`id`) REFERENCES `party_list_entry` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
128
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
|
|
129
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
130
|
|
|
131
|
--
|
|
132
|
-- Dumping data for table `data_owner`
|
|
133
|
--
|
|
134
|
|
|
135
|
/*!40000 ALTER TABLE `data_owner` DISABLE KEYS */;
|
|
136
|
/*!40000 ALTER TABLE `data_owner` ENABLE KEYS */;
|
|
137
|
|
|
138
|
--
|
|
139
|
-- Table structure for table `dataset`
|
|
140
|
--
|
|
141
|
|
|
142
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
143
|
/*!40101 SET character_set_client = utf8 */;
|
|
144
|
CREATE TABLE `dataset` (
|
|
145
|
`id` varbinary(767) NOT NULL,
|
|
146
|
`name` varbinary(767) NOT NULL,
|
|
147
|
`contacts` varbinary(767) NOT NULL COMMENT 'contact person(s) to ask about attribution questions. there should always be at least one contact person. if unknown, the person loading the data should set this to themselves.',
|
|
148
|
`parent` varbinary(767) DEFAULT NULL COMMENT 'this provides a containment relationship, which differs from the attribution relationship provided by dataset_source. e.g. the GBIF.ARIZ dataset is contained within a specific GBIF *dataset* (the parent), but was obtained from the GBIF *organization* (the dataset_source). ARIZ is then the first_publisher.',
|
|
149
|
`dataset_source` varbinary(767) DEFAULT NULL COMMENT 'where the data in the dataset came from (as opposed to where the metadata *about* the dataset came from, which is in source, inherited from traceable); = Brad.vegpath.org?attribution.proximate_data_provider',
|
|
150
|
`first_publisher` varbinary(767) DEFAULT NULL COMMENT '= Brad.vegpath.org?attribution.data_publisher, Brad.vegpath.org?data_provenance.primarydataprovider',
|
|
151
|
`data_owners` varbinary(767) DEFAULT NULL COMMENT 'parties who must be given attribution, such as copyrightholders. this includes all aggregators through which the data has passed. = Brad.vegpath.org?attribution.data_indexer',
|
|
152
|
`access_rights` varbinary(767) DEFAULT NULL COMMENT 'e.g. public, private, by request, metadata only',
|
|
153
|
`use_conditions` varbinary(767) DEFAULT NULL COMMENT 'citation requirements, etc. this is the combination of all the use_conditions of the data_owners.',
|
|
154
|
PRIMARY KEY (`id`),
|
|
155
|
UNIQUE KEY `dataset_unique` (`parent`,`name`),
|
|
156
|
KEY `fk_source_party_list1_idx` (`data_owners`),
|
|
157
|
KEY `fk_source_party1_idx` (`contacts`),
|
|
158
|
KEY `fk_dataset_dataset1_idx` (`parent`),
|
|
159
|
KEY `fk_source_party2_idx` (`first_publisher`),
|
|
160
|
KEY `dataset_dataset_source_fkey1_idx` (`dataset_source`),
|
|
161
|
CONSTRAINT `dataset_dataset_source_fkey1` FOREIGN KEY (`dataset_source`) REFERENCES `source` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
162
|
CONSTRAINT `fk_dataset_dataset1` FOREIGN KEY (`parent`) REFERENCES `dataset` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
163
|
CONSTRAINT `fk_dataset_source1` FOREIGN KEY (`id`) REFERENCES `source` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
164
|
CONSTRAINT `fk_source_party1` FOREIGN KEY (`contacts`) REFERENCES `party_list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
165
|
CONSTRAINT `fk_source_party2` FOREIGN KEY (`first_publisher`) REFERENCES `source` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
166
|
CONSTRAINT `fk_source_party_list1` FOREIGN KEY (`data_owners`) REFERENCES `party_list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
167
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='a collection of records from the same place, with the same attribution requirements';
|
|
168
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
169
|
|
|
170
|
--
|
|
171
|
-- Dumping data for table `dataset`
|
|
172
|
--
|
|
173
|
|
|
174
|
/*!40000 ALTER TABLE `dataset` DISABLE KEYS */;
|
|
175
|
/*!40000 ALTER TABLE `dataset` ENABLE KEYS */;
|
|
176
|
|
|
177
|
--
|
|
178
|
-- Table structure for table `derived_class`
|
|
179
|
--
|
|
180
|
|
|
181
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
182
|
/*!40101 SET character_set_client = utf8 */;
|
|
183
|
CREATE TABLE `derived_class` (
|
|
184
|
`id` varbinary(767) NOT NULL,
|
|
185
|
PRIMARY KEY (`id`),
|
|
186
|
CONSTRAINT `fk_derived_class_base_class1` FOREIGN KEY (`id`) REFERENCES `base_class` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
187
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
|
|
188
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
189
|
|
|
190
|
--
|
|
191
|
-- Dumping data for table `derived_class`
|
|
192
|
--
|
|
193
|
|
|
194
|
/*!40000 ALTER TABLE `derived_class` DISABLE KEYS */;
|
|
195
|
/*!40000 ALTER TABLE `derived_class` ENABLE KEYS */;
|
|
196
|
|
|
197
|
--
|
|
198
|
-- Table structure for table `event`
|
|
199
|
--
|
|
200
|
|
|
201
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
202
|
/*!40101 SET character_set_client = utf8 */;
|
|
203
|
CREATE TABLE `event` (
|
|
204
|
`id` varbinary(767) NOT NULL,
|
|
205
|
`parent` varbinary(767) DEFAULT NULL,
|
|
206
|
`subject` varbinary(767) DEFAULT NULL COMMENT 'what was observed',
|
|
207
|
`name` varbinary(767) DEFAULT NULL,
|
|
208
|
`date_range` varbinary(767) DEFAULT NULL,
|
|
209
|
`participants` varbinary(767) DEFAULT NULL,
|
|
210
|
PRIMARY KEY (`id`),
|
|
211
|
UNIQUE KEY `event_unique_within_parent_by_name` (`parent`,`name`),
|
|
212
|
UNIQUE KEY `event_unique_within_subject_by_date` (`subject`,`date_range`,`participants`),
|
|
213
|
UNIQUE KEY `event_unique_within_parent_by_subject` (`parent`,`subject`),
|
|
214
|
KEY `fk_event1_idx` (`parent`),
|
|
215
|
KEY `fk_event_party_list1_idx` (`participants`),
|
|
216
|
KEY `fk_event_place1_idx` (`subject`),
|
|
217
|
KEY `event_unique_within_subject_by_name` (`subject`,`name`),
|
|
218
|
CONSTRAINT `fk_event1` FOREIGN KEY (`parent`) REFERENCES `event` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
219
|
CONSTRAINT `fk_event_party_list1` FOREIGN KEY (`participants`) REFERENCES `party_list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
220
|
CONSTRAINT `fk_event_place1` FOREIGN KEY (`subject`) REFERENCES `traceable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
221
|
CONSTRAINT `fk_event_record1` FOREIGN KEY (`id`) REFERENCES `traceable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
222
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='"an action that occurs at a place and during a period of time" ("DwC":http://rs.tdwg.org/dwc/terms/#Event)';
|
|
223
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
224
|
|
|
225
|
--
|
|
226
|
-- Dumping data for table `event`
|
|
227
|
--
|
|
228
|
|
|
229
|
/*!40000 ALTER TABLE `event` DISABLE KEYS */;
|
|
230
|
/*!40000 ALTER TABLE `event` ENABLE KEYS */;
|
|
231
|
|
|
232
|
--
|
|
233
|
-- Table structure for table `geological_context`
|
|
234
|
--
|
|
235
|
|
|
236
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
237
|
/*!40101 SET character_set_client = utf8 */;
|
|
238
|
CREATE TABLE `geological_context` (
|
|
239
|
`id` varbinary(767) NOT NULL,
|
|
240
|
`name` varbinary(767) NOT NULL,
|
|
241
|
`info` set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
242
|
PRIMARY KEY (`id`),
|
|
243
|
CONSTRAINT `fk_geological_context_record1` FOREIGN KEY (`id`) REFERENCES `traceable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
244
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='"information pertaining to a location within a geological context, such as stratigraphy" ("DwC":http://rs.tdwg.org/dwc/terms/#GeologicalContext)';
|
|
245
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
246
|
|
|
247
|
--
|
|
248
|
-- Dumping data for table `geological_context`
|
|
249
|
--
|
|
250
|
|
|
251
|
/*!40000 ALTER TABLE `geological_context` DISABLE KEYS */;
|
|
252
|
/*!40000 ALTER TABLE `geological_context` ENABLE KEYS */;
|
|
253
|
|
|
254
|
--
|
|
255
|
-- Table structure for table `geopath`
|
|
256
|
--
|
|
257
|
|
|
258
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
259
|
/*!40101 SET character_set_client = utf8 */;
|
|
260
|
CREATE TABLE `geopath` (
|
|
261
|
`id` varbinary(767) NOT NULL,
|
|
262
|
`ranks` set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
263
|
`continent` varbinary(767) DEFAULT NULL,
|
|
264
|
`country` varbinary(767) DEFAULT NULL,
|
|
265
|
`state_province` varbinary(767) DEFAULT NULL,
|
|
266
|
`county` varbinary(767) DEFAULT NULL,
|
|
267
|
`municipality` varbinary(767) DEFAULT NULL,
|
|
268
|
`locality` varbinary(767) DEFAULT NULL,
|
|
269
|
PRIMARY KEY (`id`)
|
|
270
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='A named region';
|
|
271
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
272
|
|
|
273
|
--
|
|
274
|
-- Dumping data for table `geopath`
|
|
275
|
--
|
|
276
|
|
|
277
|
/*!40000 ALTER TABLE `geopath` DISABLE KEYS */;
|
|
278
|
/*!40000 ALTER TABLE `geopath` ENABLE KEYS */;
|
|
279
|
|
|
280
|
--
|
|
281
|
-- Table structure for table `geoplace`
|
|
282
|
--
|
|
283
|
|
|
284
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
285
|
/*!40101 SET character_set_client = utf8 */;
|
|
286
|
CREATE TABLE `geoplace` (
|
|
287
|
`id` varbinary(767) NOT NULL,
|
|
288
|
`latitude_deg` varbinary(767) NOT NULL,
|
|
289
|
`longitude_deg` varbinary(767) NOT NULL,
|
|
290
|
`boundary_WKT` varbinary(767) NOT NULL DEFAULT 'point',
|
|
291
|
`georeferenced_by` varbinary(767) DEFAULT NULL,
|
|
292
|
`georeferencing_info` varbinary(767) DEFAULT NULL,
|
|
293
|
PRIMARY KEY (`id`),
|
|
294
|
KEY `geoplace_id_fkey1_idx` (`georeferenced_by`),
|
|
295
|
CONSTRAINT `fk_geoplace_place1` FOREIGN KEY (`id`) REFERENCES `place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
296
|
CONSTRAINT `geoplace_id_fkey1` FOREIGN KEY (`georeferenced_by`) REFERENCES `party_list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
297
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='A numerically-defined point. inherited fields: name: the official, scrubbed name';
|
|
298
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
299
|
|
|
300
|
--
|
|
301
|
-- Dumping data for table `geoplace`
|
|
302
|
--
|
|
303
|
|
|
304
|
/*!40000 ALTER TABLE `geoplace` DISABLE KEYS */;
|
|
305
|
/*!40000 ALTER TABLE `geoplace` ENABLE KEYS */;
|
|
306
|
|
|
307
|
--
|
|
308
|
-- Table structure for table `geovalidatable_place`
|
|
309
|
--
|
|
310
|
|
|
311
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
312
|
/*!40101 SET character_set_client = utf8 */;
|
|
313
|
CREATE TABLE `geovalidatable_place` (
|
|
314
|
`id` varbinary(767) NOT NULL,
|
|
315
|
`parent_boundary_WKT` varbinary(767) NOT NULL COMMENT 'the parent geoplace',
|
|
316
|
PRIMARY KEY (`id`),
|
|
317
|
CONSTRAINT `fk_nested_geoplace_geoplace1` FOREIGN KEY (`id`) REFERENCES `geoplace` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
318
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='stores only scrubbed geoplaces (GADM places, and distinct point coordinates that GNRS says should be located within them)';
|
|
319
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
320
|
|
|
321
|
--
|
|
322
|
-- Dumping data for table `geovalidatable_place`
|
|
323
|
--
|
|
324
|
|
|
325
|
/*!40000 ALTER TABLE `geovalidatable_place` DISABLE KEYS */;
|
|
326
|
/*!40000 ALTER TABLE `geovalidatable_place` ENABLE KEYS */;
|
|
327
|
|
|
328
|
--
|
|
329
|
-- Table structure for table `geovalidation`
|
|
330
|
--
|
|
331
|
|
|
332
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
333
|
/*!40101 SET character_set_client = utf8 */;
|
|
334
|
CREATE TABLE `geovalidation` (
|
|
335
|
`id` varbinary(767) NOT NULL,
|
|
336
|
`input_geoplace` varbinary(767) NOT NULL,
|
|
337
|
`geovalid` tinyint(1) NOT NULL,
|
|
338
|
`lat_long_domain_valid` tinyint(1) NOT NULL,
|
|
339
|
`corrected_geoplace` varbinary(767) DEFAULT NULL,
|
|
340
|
`lat_long_in_place_ranks` set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
341
|
PRIMARY KEY (`id`),
|
|
342
|
KEY `fk_geovalidation_geoplace1_idx` (`input_geoplace`),
|
|
343
|
KEY `fk_geovalidation_geoplace2_idx` (`corrected_geoplace`),
|
|
344
|
CONSTRAINT `fk_geovalidation_geoplace1` FOREIGN KEY (`input_geoplace`) REFERENCES `geovalidatable_place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
345
|
CONSTRAINT `fk_geovalidation_geoplace2` FOREIGN KEY (`corrected_geoplace`) REFERENCES `geovalidatable_place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
346
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='The accuracy of the [[VegCore#Coordinates|Coordinates]], taking into account the [[VegCore#Placename|Placename]]';
|
|
347
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
348
|
|
|
349
|
--
|
|
350
|
-- Dumping data for table `geovalidation`
|
|
351
|
--
|
|
352
|
|
|
353
|
/*!40000 ALTER TABLE `geovalidation` DISABLE KEYS */;
|
|
354
|
/*!40000 ALTER TABLE `geovalidation` ENABLE KEYS */;
|
|
355
|
|
|
356
|
--
|
|
357
|
-- Table structure for table `individual`
|
|
358
|
--
|
|
359
|
|
|
360
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
361
|
/*!40101 SET character_set_client = utf8 */;
|
|
362
|
CREATE TABLE `individual` (
|
|
363
|
`id` varbinary(767) NOT NULL,
|
|
364
|
`identifying_place` varbinary(767) DEFAULT NULL COMMENT 'subplace within plot. not specified for specimens since their coordinates are usually not precise enough to identify an individual.',
|
|
365
|
`tag` varbinary(767) DEFAULT NULL,
|
|
366
|
`tag_history` set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
367
|
PRIMARY KEY (`id`),
|
|
368
|
KEY `fk_individual_subplace1_idx` (`identifying_place`),
|
|
369
|
CONSTRAINT `fk_individual_record1` FOREIGN KEY (`id`) REFERENCES `reobservable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
370
|
CONSTRAINT `fk_individual_subplace1` FOREIGN KEY (`identifying_place`) REFERENCES `subplace` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
371
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='A distinct biological organism';
|
|
372
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
373
|
|
|
374
|
--
|
|
375
|
-- Dumping data for table `individual`
|
|
376
|
--
|
|
377
|
|
|
378
|
/*!40000 ALTER TABLE `individual` DISABLE KEYS */;
|
|
379
|
/*!40000 ALTER TABLE `individual` ENABLE KEYS */;
|
|
380
|
|
|
381
|
--
|
|
382
|
-- Table structure for table `individual_count`
|
|
383
|
--
|
|
384
|
|
|
385
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
386
|
/*!40101 SET character_set_client = utf8 */;
|
|
387
|
CREATE TABLE `individual_count` (
|
|
388
|
`id` varbinary(767) NOT NULL,
|
|
389
|
`parent` varbinary(767) NOT NULL COMMENT 'the taxon_presence for the taxon as a whole (not subdivided by size_class, etc.)',
|
|
390
|
`size_class` varbinary(767) DEFAULT NULL,
|
|
391
|
`aggregating_traits` set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
392
|
`count` int(11) DEFAULT NULL,
|
|
393
|
PRIMARY KEY (`id`),
|
|
394
|
KEY `fk_aggregate_observation_taxon_presence1_idx` (`parent`),
|
|
395
|
KEY `fk_aggregate_observation_size_class1_idx` (`size_class`),
|
|
396
|
CONSTRAINT `fk_aggregate_observation_size_class1` FOREIGN KEY (`size_class`) REFERENCES `size_class` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
397
|
CONSTRAINT `fk_aggregate_observation_taxon_presence1` FOREIGN KEY (`parent`) REFERENCES `taxon_presence` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
398
|
CONSTRAINT `fk_individual_count_taxon_presence1` FOREIGN KEY (`id`) REFERENCES `taxon_presence` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
399
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='= VegBank.vegpath.org?stemCount';
|
|
400
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
401
|
|
|
402
|
--
|
|
403
|
-- Dumping data for table `individual_count`
|
|
404
|
--
|
|
405
|
|
|
406
|
/*!40000 ALTER TABLE `individual_count` DISABLE KEYS */;
|
|
407
|
/*!40000 ALTER TABLE `individual_count` ENABLE KEYS */;
|
|
408
|
|
|
409
|
--
|
|
410
|
-- Table structure for table `individual_observation`
|
|
411
|
--
|
|
412
|
|
|
413
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
414
|
/*!40101 SET character_set_client = utf8 */;
|
|
415
|
CREATE TABLE `individual_observation` (
|
|
416
|
`id` varbinary(767) NOT NULL,
|
|
417
|
`subject` varbinary(767) DEFAULT NULL COMMENT 'optional because an individual_observation cannot have an associated individual unless the individual is traceable to a specific plant',
|
|
418
|
`specimenholder_institutions` varbinary(767) DEFAULT NULL COMMENT 'institutions which store specimens from this plant. when provided, the taxon_occurrence should have an autogenerated current_observation that merges together all the individual_observations for these institutions'' specimens.',
|
|
419
|
PRIMARY KEY (`id`),
|
|
420
|
KEY `fk_individual_observation_individual1_idx` (`subject`),
|
|
421
|
KEY `individual_observation_id_fkey1_idx` (`specimenholder_institutions`),
|
|
422
|
CONSTRAINT `fk_individual_observation_individual1` FOREIGN KEY (`subject`) REFERENCES `individual` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
423
|
CONSTRAINT `fk_individual_observation_taxon_occurrence1` FOREIGN KEY (`id`) REFERENCES `taxon_observation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
424
|
CONSTRAINT `individual_observation_id_fkey1` FOREIGN KEY (`specimenholder_institutions`) REFERENCES `party_list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
425
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='An observation of an [[VegCore#Individual|Individual]]; inherited fields: place: contains the place the individual was observed at';
|
|
426
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
427
|
|
|
428
|
--
|
|
429
|
-- Dumping data for table `individual_observation`
|
|
430
|
--
|
|
431
|
|
|
432
|
/*!40000 ALTER TABLE `individual_observation` DISABLE KEYS */;
|
|
433
|
/*!40000 ALTER TABLE `individual_observation` ENABLE KEYS */;
|
|
434
|
|
|
435
|
--
|
|
436
|
-- Table structure for table `method`
|
|
437
|
--
|
|
438
|
|
|
439
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
440
|
/*!40101 SET character_set_client = utf8 */;
|
|
441
|
CREATE TABLE `method` (
|
|
442
|
`id` varbinary(767) NOT NULL,
|
|
443
|
`name` varbinary(767) DEFAULT NULL,
|
|
444
|
`parent` varbinary(767) DEFAULT NULL,
|
|
445
|
`info` set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
446
|
PRIMARY KEY (`id`),
|
|
447
|
KEY `fk_method_method1_idx` (`parent`),
|
|
448
|
CONSTRAINT `fk_method_method1` FOREIGN KEY (`parent`) REFERENCES `method` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
449
|
CONSTRAINT `fk_method_record1` FOREIGN KEY (`id`) REFERENCES `traceable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
450
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='"A specific method definition followed in the creation of the dataset. Each method links to a protocol and literature citation reference. A protocol may have many method or steps." ("VegX":http://wiki.tdwg.org/twiki/pub/Vegetation/WebHome/VegX_Schema_1.5.3_proposed.zip#/veg.xsd#/methods/method)';
|
|
451
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
452
|
|
|
453
|
--
|
|
454
|
-- Dumping data for table `method`
|
|
455
|
--
|
|
456
|
|
|
457
|
/*!40000 ALTER TABLE `method` DISABLE KEYS */;
|
|
458
|
/*!40000 ALTER TABLE `method` ENABLE KEYS */;
|
|
459
|
|
|
460
|
--
|
|
461
|
-- Table structure for table `organization`
|
|
462
|
--
|
|
463
|
|
|
464
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
465
|
/*!40101 SET character_set_client = utf8 */;
|
|
466
|
CREATE TABLE `organization` (
|
|
467
|
`id` varbinary(767) NOT NULL,
|
|
468
|
`name` varbinary(767) NOT NULL,
|
|
469
|
`parent` varbinary(767) DEFAULT NULL,
|
|
470
|
PRIMARY KEY (`id`),
|
|
471
|
UNIQUE KEY `organization_unique` (`parent`,`name`),
|
|
472
|
KEY `fk_organization_organization1_idx` (`parent`),
|
|
473
|
CONSTRAINT `fk_organization_organization1` FOREIGN KEY (`parent`) REFERENCES `organization` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
474
|
CONSTRAINT `fk_organization_party1` FOREIGN KEY (`id`) REFERENCES `party` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
475
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
|
|
476
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
477
|
|
|
478
|
--
|
|
479
|
-- Dumping data for table `organization`
|
|
480
|
--
|
|
481
|
|
|
482
|
/*!40000 ALTER TABLE `organization` DISABLE KEYS */;
|
|
483
|
/*!40000 ALTER TABLE `organization` ENABLE KEYS */;
|
|
484
|
|
|
485
|
--
|
|
486
|
-- Table structure for table `party`
|
|
487
|
--
|
|
488
|
|
|
489
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
490
|
/*!40101 SET character_set_client = utf8 */;
|
|
491
|
CREATE TABLE `party` (
|
|
492
|
`id` varbinary(767) NOT NULL,
|
|
493
|
PRIMARY KEY (`id`),
|
|
494
|
CONSTRAINT `fk_collection_source10` FOREIGN KEY (`id`) REFERENCES `traceable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
495
|
CONSTRAINT `fk_party_source1` FOREIGN KEY (`id`) REFERENCES `source` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
496
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
|
|
497
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
498
|
|
|
499
|
--
|
|
500
|
-- Dumping data for table `party`
|
|
501
|
--
|
|
502
|
|
|
503
|
/*!40000 ALTER TABLE `party` DISABLE KEYS */;
|
|
504
|
/*!40000 ALTER TABLE `party` ENABLE KEYS */;
|
|
505
|
|
|
506
|
--
|
|
507
|
-- Table structure for table `party_list`
|
|
508
|
--
|
|
509
|
|
|
510
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
511
|
/*!40101 SET character_set_client = utf8 */;
|
|
512
|
CREATE TABLE `party_list` (
|
|
513
|
`id` varbinary(767) NOT NULL,
|
|
514
|
`count` int(11) NOT NULL,
|
|
515
|
`array` set('text[]') COLLATE utf8_bin DEFAULT NULL COMMENT 'an array combining all the list entries. if specified, a trigger will autogenerate party_list_entries from this field. if not specified, will be autopopulated whenever a party_list_entry is added.',
|
|
516
|
PRIMARY KEY (`id`),
|
|
517
|
CONSTRAINT `fk_collection_source100` FOREIGN KEY (`id`) REFERENCES `traceable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
518
|
CONSTRAINT `party_list_id_fkey1` FOREIGN KEY (`id`) REFERENCES `source` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
519
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
|
|
520
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
521
|
|
|
522
|
--
|
|
523
|
-- Dumping data for table `party_list`
|
|
524
|
--
|
|
525
|
|
|
526
|
/*!40000 ALTER TABLE `party_list` DISABLE KEYS */;
|
|
527
|
/*!40000 ALTER TABLE `party_list` ENABLE KEYS */;
|
|
528
|
|
|
529
|
--
|
|
530
|
-- Table structure for table `party_list_entry`
|
|
531
|
--
|
|
532
|
|
|
533
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
534
|
/*!40101 SET character_set_client = utf8 */;
|
|
535
|
CREATE TABLE `party_list_entry` (
|
|
536
|
`id` varbinary(767) NOT NULL,
|
|
537
|
`list` varbinary(767) NOT NULL,
|
|
538
|
`party` varbinary(767) NOT NULL,
|
|
539
|
`role` varbinary(767) DEFAULT NULL,
|
|
540
|
`sort_order` int(11) DEFAULT NULL,
|
|
541
|
PRIMARY KEY (`id`),
|
|
542
|
KEY `fk_party_list_has_party_party1_idx` (`party`),
|
|
543
|
KEY `fk_party_list_entry_party_list1_idx` (`list`),
|
|
544
|
CONSTRAINT `fk_party_list_entry_party_list1` FOREIGN KEY (`list`) REFERENCES `party_list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
545
|
CONSTRAINT `fk_party_list_entry_record1` FOREIGN KEY (`id`) REFERENCES `traceable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
546
|
CONSTRAINT `fk_party_list_has_party_party1` FOREIGN KEY (`party`) REFERENCES `party` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
547
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
|
|
548
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
549
|
|
|
550
|
--
|
|
551
|
-- Dumping data for table `party_list_entry`
|
|
552
|
--
|
|
553
|
|
|
554
|
/*!40000 ALTER TABLE `party_list_entry` DISABLE KEYS */;
|
|
555
|
/*!40000 ALTER TABLE `party_list_entry` ENABLE KEYS */;
|
|
556
|
|
|
557
|
--
|
|
558
|
-- Table structure for table `person`
|
|
559
|
--
|
|
560
|
|
|
561
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
562
|
/*!40101 SET character_set_client = utf8 */;
|
|
563
|
CREATE TABLE `person` (
|
|
564
|
`id` varbinary(767) NOT NULL,
|
|
565
|
`name` varbinary(767) NOT NULL,
|
|
566
|
`organizations` varbinary(767) DEFAULT NULL,
|
|
567
|
PRIMARY KEY (`id`),
|
|
568
|
UNIQUE KEY `person_unique` (`organizations`,`name`),
|
|
569
|
KEY `person_organization_fkey1_idx` (`organizations`),
|
|
570
|
CONSTRAINT `fk_organization_party10` FOREIGN KEY (`id`) REFERENCES `party` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
571
|
CONSTRAINT `person_organization_fkey1` FOREIGN KEY (`organizations`) REFERENCES `party_list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
572
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
|
|
573
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
574
|
|
|
575
|
--
|
|
576
|
-- Dumping data for table `person`
|
|
577
|
--
|
|
578
|
|
|
579
|
/*!40000 ALTER TABLE `person` DISABLE KEYS */;
|
|
580
|
/*!40000 ALTER TABLE `person` ENABLE KEYS */;
|
|
581
|
|
|
582
|
--
|
|
583
|
-- Table structure for table `place`
|
|
584
|
--
|
|
585
|
|
|
586
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
587
|
/*!40101 SET character_set_client = utf8 */;
|
|
588
|
CREATE TABLE `place` (
|
|
589
|
`id` varbinary(767) NOT NULL,
|
|
590
|
`rank` varbinary(767) NOT NULL COMMENT 'every place should have some kind of rank indicating what type of place it is, including lower ranks (e.g. plot, individual)',
|
|
591
|
`name` varbinary(767) NOT NULL COMMENT 'for geoplace, generated from the coordinates',
|
|
592
|
`parent` varbinary(767) DEFAULT NULL,
|
|
593
|
`geopath` varbinary(767) DEFAULT NULL,
|
|
594
|
`verbatim` set('hstore') COLLATE utf8_bin DEFAULT NULL COMMENT 'for verbatim coordinates, etc.',
|
|
595
|
PRIMARY KEY (`id`),
|
|
596
|
UNIQUE KEY `place__unique` (`parent`,`rank`,`name`),
|
|
597
|
KEY `fk_place1_idx` (`parent`),
|
|
598
|
KEY `fk_place_geopath1_idx` (`geopath`),
|
|
599
|
CONSTRAINT `fk_place1` FOREIGN KEY (`parent`) REFERENCES `place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
600
|
CONSTRAINT `fk_place_geopath1` FOREIGN KEY (`geopath`) REFERENCES `geopath` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
601
|
CONSTRAINT `fk_place_record1` FOREIGN KEY (`id`) REFERENCES `traceable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
602
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='"A spatial region" ("DwC":http://rs.tdwg.org/dwc/terms/#dcterms:Location) or point';
|
|
603
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
604
|
|
|
605
|
--
|
|
606
|
-- Dumping data for table `place`
|
|
607
|
--
|
|
608
|
|
|
609
|
/*!40000 ALTER TABLE `place` DISABLE KEYS */;
|
|
610
|
/*!40000 ALTER TABLE `place` ENABLE KEYS */;
|
|
611
|
|
|
612
|
--
|
|
613
|
-- Table structure for table `place_visit`
|
|
614
|
--
|
|
615
|
|
|
616
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
617
|
/*!40101 SET character_set_client = utf8 */;
|
|
618
|
CREATE TABLE `place_visit` (
|
|
619
|
`id` varbinary(767) NOT NULL,
|
|
620
|
`parent` varbinary(767) DEFAULT NULL COMMENT 'the concurrent observation of the parent place',
|
|
621
|
`subject` varbinary(767) NOT NULL,
|
|
622
|
`project` varbinary(767) DEFAULT NULL,
|
|
623
|
`community` varbinary(767) DEFAULT NULL,
|
|
624
|
`geological_context` varbinary(767) DEFAULT NULL,
|
|
625
|
`observations` set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
626
|
`elevation_m` double DEFAULT NULL,
|
|
627
|
`slope_incline_deg` double DEFAULT NULL,
|
|
628
|
`slope_direction_deg_N` double DEFAULT NULL,
|
|
629
|
PRIMARY KEY (`id`),
|
|
630
|
KEY `fk_place_observation_place1_idx` (`subject`),
|
|
631
|
KEY `fk_place_observation_geological_context1_idx` (`geological_context`),
|
|
632
|
KEY `fk_place_observation_community1_idx` (`community`),
|
|
633
|
KEY `place_observation_id_fkey1_idx` (`project`),
|
|
634
|
KEY `place_visit_id_fkey1_idx` (`parent`),
|
|
635
|
CONSTRAINT `fk_place_observation_community1` FOREIGN KEY (`community`) REFERENCES `community` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
636
|
CONSTRAINT `fk_place_observation_event1` FOREIGN KEY (`id`) REFERENCES `event` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
637
|
CONSTRAINT `fk_place_observation_geological_context1` FOREIGN KEY (`geological_context`) REFERENCES `geological_context` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
638
|
CONSTRAINT `fk_place_observation_place1` FOREIGN KEY (`subject`) REFERENCES `place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
639
|
CONSTRAINT `place_observation_id_fkey1` FOREIGN KEY (`project`) REFERENCES `project` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
640
|
CONSTRAINT `place_visit_id_fkey1` FOREIGN KEY (`parent`) REFERENCES `place_visit` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
641
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='A sampling of a [[VegCore#Location|Location]]. = VegX.vegpath.org?plotObservation, VegBank.vegpath.org?observation (which was confusingly named) + some of VegBank.vegpath.org?plot (which is actually 1:1 with observation)';
|
|
642
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
643
|
|
|
644
|
--
|
|
645
|
-- Dumping data for table `place_visit`
|
|
646
|
--
|
|
647
|
|
|
648
|
/*!40000 ALTER TABLE `place_visit` DISABLE KEYS */;
|
|
649
|
/*!40000 ALTER TABLE `place_visit` ENABLE KEYS */;
|
|
650
|
|
|
651
|
--
|
|
652
|
-- Table structure for table `plot`
|
|
653
|
--
|
|
654
|
|
|
655
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
656
|
/*!40101 SET character_set_client = utf8 */;
|
|
657
|
CREATE TABLE `plot` (
|
|
658
|
`id` varbinary(767) NOT NULL,
|
|
659
|
`area_m2` double DEFAULT NULL,
|
|
660
|
`shape` varbinary(767) DEFAULT NULL,
|
|
661
|
`length_m` varbinary(767) DEFAULT NULL,
|
|
662
|
`width_m` varbinary(767) DEFAULT NULL,
|
|
663
|
`azimuth_deg_N` varbinary(767) DEFAULT NULL,
|
|
664
|
`boundary_WKT` varbinary(767) DEFAULT NULL,
|
|
665
|
`dimensions` set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
666
|
PRIMARY KEY (`id`),
|
|
667
|
CONSTRAINT `fk_subplot_place1` FOREIGN KEY (`id`) REFERENCES `place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
668
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
|
|
669
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
670
|
|
|
671
|
--
|
|
672
|
-- Dumping data for table `plot`
|
|
673
|
--
|
|
674
|
|
|
675
|
/*!40000 ALTER TABLE `plot` DISABLE KEYS */;
|
|
676
|
/*!40000 ALTER TABLE `plot` ENABLE KEYS */;
|
|
677
|
|
|
678
|
--
|
|
679
|
-- Table structure for table `project`
|
|
680
|
--
|
|
681
|
|
|
682
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
683
|
/*!40101 SET character_set_client = utf8 */;
|
|
684
|
CREATE TABLE `project` (
|
|
685
|
`id` varbinary(767) NOT NULL,
|
|
686
|
`name` varbinary(767) NOT NULL,
|
|
687
|
`dataset` varbinary(767) DEFAULT NULL,
|
|
688
|
`info` set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
689
|
PRIMARY KEY (`id`),
|
|
690
|
UNIQUE KEY `project__unique` (`dataset`,`name`),
|
|
691
|
KEY `project_id_fkey1_idx` (`dataset`),
|
|
692
|
CONSTRAINT `fk_project_event1` FOREIGN KEY (`id`) REFERENCES `event` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
693
|
CONSTRAINT `project_id_fkey1` FOREIGN KEY (`dataset`) REFERENCES `dataset` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
694
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='A "project established to collect vegetation plot data. Each plot originates as part of a project." ("VegBank":http://vegbank.org/vegbank/views/dba_tabledescription_detail.jsp?view=detail&wparam=project&entity=dba_tabledescription&where=where_tablename)';
|
|
695
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
696
|
|
|
697
|
--
|
|
698
|
-- Dumping data for table `project`
|
|
699
|
--
|
|
700
|
|
|
701
|
/*!40000 ALTER TABLE `project` DISABLE KEYS */;
|
|
702
|
/*!40000 ALTER TABLE `project` ENABLE KEYS */;
|
|
703
|
|
|
704
|
--
|
|
705
|
-- Table structure for table `record`
|
|
706
|
--
|
|
707
|
|
|
708
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
709
|
/*!40101 SET character_set_client = utf8 */;
|
|
710
|
CREATE TABLE `record` (
|
|
711
|
`id` varbinary(767) NOT NULL,
|
|
712
|
`scoping_dataset` varbinary(767) NOT NULL COMMENT 'the dataset that scopes the id_within_dataset',
|
|
713
|
`id_within_dataset` varbinary(767) NOT NULL,
|
|
714
|
`attribution_dataset` varbinary(767) NOT NULL COMMENT 'the dataset that provides attribution information. defaults to the scoping_dataset.',
|
|
715
|
PRIMARY KEY (`id`),
|
|
716
|
UNIQUE KEY `record_unique` (`scoping_dataset`,`id_within_dataset`),
|
|
717
|
KEY `fk_record_source1_idx` (`scoping_dataset`),
|
|
718
|
KEY `record_id_fkey2_idx` (`attribution_dataset`),
|
|
719
|
CONSTRAINT `fk_record_source10` FOREIGN KEY (`scoping_dataset`) REFERENCES `dataset` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
720
|
CONSTRAINT `fk_record_source2` FOREIGN KEY (`id`) REFERENCES `source` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
721
|
CONSTRAINT `record_id_fkey2` FOREIGN KEY (`attribution_dataset`) REFERENCES `dataset` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
722
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='a collection of data relating to the same entity. this doesn''t have to be a row in a table, it can also be a paragraph of text relating to the same thing (e.g. a method description in a PDF). note that this is the record in the *source* data.';
|
|
723
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
724
|
|
|
725
|
--
|
|
726
|
-- Dumping data for table `record`
|
|
727
|
--
|
|
728
|
|
|
729
|
/*!40000 ALTER TABLE `record` DISABLE KEYS */;
|
|
730
|
/*!40000 ALTER TABLE `record` ENABLE KEYS */;
|
|
731
|
|
|
732
|
--
|
|
733
|
-- Table structure for table `referenced_class`
|
|
734
|
--
|
|
735
|
|
|
736
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
737
|
/*!40101 SET character_set_client = utf8 */;
|
|
738
|
CREATE TABLE `referenced_class` (
|
|
739
|
`id` varbinary(767) NOT NULL,
|
|
740
|
PRIMARY KEY (`id`),
|
|
741
|
CONSTRAINT `fk_example_record10` FOREIGN KEY (`id`) REFERENCES `traceable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
742
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
|
|
743
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
744
|
|
|
745
|
--
|
|
746
|
-- Dumping data for table `referenced_class`
|
|
747
|
--
|
|
748
|
|
|
749
|
/*!40000 ALTER TABLE `referenced_class` DISABLE KEYS */;
|
|
750
|
/*!40000 ALTER TABLE `referenced_class` ENABLE KEYS */;
|
|
751
|
|
|
752
|
--
|
|
753
|
-- Table structure for table `reobservable`
|
|
754
|
--
|
|
755
|
|
|
756
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
757
|
/*!40101 SET character_set_client = utf8 */;
|
|
758
|
CREATE TABLE `reobservable` (
|
|
759
|
`id` varbinary(767) NOT NULL,
|
|
760
|
`orig_observation` varbinary(767) DEFAULT NULL,
|
|
761
|
PRIMARY KEY (`id`),
|
|
762
|
KEY `fk_reobservable_taxon_determination1_idx` (`orig_observation`),
|
|
763
|
CONSTRAINT `fk_reobservable_taxon_determination1` FOREIGN KEY (`orig_observation`) REFERENCES `taxon_observation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
764
|
CONSTRAINT `fk_reobservable_taxon_occurrence1` FOREIGN KEY (`id`) REFERENCES `taxon_occurrence` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
765
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Any entity on which [[VegCore#TaxonDetermination|TaxonDetermination]]s can be made';
|
|
766
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
767
|
|
|
768
|
--
|
|
769
|
-- Dumping data for table `reobservable`
|
|
770
|
--
|
|
771
|
|
|
772
|
/*!40000 ALTER TABLE `reobservable` DISABLE KEYS */;
|
|
773
|
/*!40000 ALTER TABLE `reobservable` ENABLE KEYS */;
|
|
774
|
|
|
775
|
--
|
|
776
|
-- Table structure for table `size_class`
|
|
777
|
--
|
|
778
|
|
|
779
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
780
|
/*!40101 SET character_set_client = utf8 */;
|
|
781
|
CREATE TABLE `size_class` (
|
|
782
|
`id` varbinary(767) NOT NULL,
|
|
783
|
`diameter_min_m` double NOT NULL,
|
|
784
|
`diameter_max_m` double NOT NULL,
|
|
785
|
PRIMARY KEY (`id`),
|
|
786
|
CONSTRAINT `fk_layer_stratum10` FOREIGN KEY (`id`) REFERENCES `method` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
787
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
|
|
788
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
789
|
|
|
790
|
--
|
|
791
|
-- Dumping data for table `size_class`
|
|
792
|
--
|
|
793
|
|
|
794
|
/*!40000 ALTER TABLE `size_class` DISABLE KEYS */;
|
|
795
|
/*!40000 ALTER TABLE `size_class` ENABLE KEYS */;
|
|
796
|
|
|
797
|
--
|
|
798
|
-- Table structure for table `soil_observation`
|
|
799
|
--
|
|
800
|
|
|
801
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
802
|
/*!40101 SET character_set_client = utf8 */;
|
|
803
|
CREATE TABLE `soil_observation` (
|
|
804
|
`id` varbinary(767) NOT NULL,
|
|
805
|
`place_visit` varbinary(767) NOT NULL,
|
|
806
|
`measurement_spot` varbinary(767) DEFAULT NULL,
|
|
807
|
`observations` set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
808
|
PRIMARY KEY (`id`),
|
|
809
|
KEY `fk_soil_observation_place_observation1_idx` (`place_visit`),
|
|
810
|
KEY `fk_soil_observation_subplace1_idx` (`measurement_spot`),
|
|
811
|
CONSTRAINT `fk_soil_observation_place_observation1` FOREIGN KEY (`place_visit`) REFERENCES `place_visit` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
812
|
CONSTRAINT `fk_soil_observation_record1` FOREIGN KEY (`id`) REFERENCES `traceable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
813
|
CONSTRAINT `fk_soil_observation_subplace1` FOREIGN KEY (`measurement_spot`) REFERENCES `subplace` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
814
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='A sampling of a [[VegCore#Location|Location]]''s soil';
|
|
815
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
816
|
|
|
817
|
--
|
|
818
|
-- Dumping data for table `soil_observation`
|
|
819
|
--
|
|
820
|
|
|
821
|
/*!40000 ALTER TABLE `soil_observation` DISABLE KEYS */;
|
|
822
|
/*!40000 ALTER TABLE `soil_observation` ENABLE KEYS */;
|
|
823
|
|
|
824
|
--
|
|
825
|
-- Table structure for table `source`
|
|
826
|
--
|
|
827
|
|
|
828
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
829
|
/*!40101 SET character_set_client = utf8 */;
|
|
830
|
CREATE TABLE `source` (
|
|
831
|
`id` varbinary(767) NOT NULL,
|
|
832
|
`url` varbinary(767) NOT NULL COMMENT 'points to the source data and uniquely identifies the source',
|
|
833
|
`name` varbinary(767) DEFAULT NULL COMMENT 'source names will be scrubbed *upon insert* so that the row always contains the most accurate data. this scrubbing may use the assistance of a thesaurus table.',
|
|
834
|
`info` set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
835
|
PRIMARY KEY (`id`),
|
|
836
|
UNIQUE KEY `source__unique` (`url`)
|
|
837
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='where something came from; a "reference [...] cited within the database" ("VegBank":http://reference.VegBank.vegpath.org); = VegBank.vegpath.org?reference';
|
|
838
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
839
|
|
|
840
|
--
|
|
841
|
-- Dumping data for table `source`
|
|
842
|
--
|
|
843
|
|
|
844
|
/*!40000 ALTER TABLE `source` DISABLE KEYS */;
|
|
845
|
/*!40000 ALTER TABLE `source` ENABLE KEYS */;
|
|
846
|
|
|
847
|
--
|
|
848
|
-- Table structure for table `specimen`
|
|
849
|
--
|
|
850
|
|
|
851
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
852
|
/*!40101 SET character_set_client = utf8 */;
|
|
853
|
CREATE TABLE `specimen` (
|
|
854
|
`id` varbinary(767) NOT NULL,
|
|
855
|
`individual_observation` varbinary(767) DEFAULT NULL COMMENT 'the plant the specimen was collected from and any observations about it',
|
|
856
|
`id_within_individual` varbinary(767) DEFAULT NULL,
|
|
857
|
`orig_collection` varbinary(767) DEFAULT NULL,
|
|
858
|
`barcode` varbinary(767) DEFAULT NULL,
|
|
859
|
`accession_number` varbinary(767) DEFAULT NULL,
|
|
860
|
`defining_data` set('hstore') COLLATE utf8_bin DEFAULT NULL COMMENT 'of the observations made about the specimen in individual_observation.traits, this stores the subset that can be used to make a taxonomic redetermination. for a digital-only specimen, this would be the information that comprises the specimen (e.g. a photo, a sketch, or a description that is detailed enough to be able to make a redetermination). note that a taxon_presence without a physical voucher can still qualify as reobservable if a detailed description of it is provided here.',
|
|
861
|
PRIMARY KEY (`id`),
|
|
862
|
UNIQUE KEY `specimen_unique_in_individual` (`individual_observation`,`id_within_individual`),
|
|
863
|
UNIQUE KEY `specimen_unique_in_collection_by_barcode` (`orig_collection`,`barcode`),
|
|
864
|
UNIQUE KEY `specimen_unique_in_collection_by_accession_number` (`orig_collection`,`accession_number`),
|
|
865
|
UNIQUE KEY `specimen_unique_in_individual_observation` (`individual_observation`,`id_within_individual`),
|
|
866
|
KEY `fk_specimen_collection1_idx` (`orig_collection`),
|
|
867
|
KEY `fk_specimen_individual_observation1_idx` (`individual_observation`),
|
|
868
|
CONSTRAINT `fk_specimen_collection1` FOREIGN KEY (`orig_collection`) REFERENCES `collection` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
869
|
CONSTRAINT `fk_specimen_individual_observation1` FOREIGN KEY (`individual_observation`) REFERENCES `individual_observation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
870
|
CONSTRAINT `fk_specimen_taxon_occurrence1` FOREIGN KEY (`id`) REFERENCES `reobservable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
871
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='something collected from a plant. this can be a physical "part of a plant" ("Wikipedia":http://en.wikipedia.org/wiki/Specimen), or a picture or description of the plant. when there are multiple specimen replicates (copies) of a specimen, each gets its own specimen_observation pointing to the same specimen.';
|
|
872
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
873
|
|
|
874
|
--
|
|
875
|
-- Dumping data for table `specimen`
|
|
876
|
--
|
|
877
|
|
|
878
|
/*!40000 ALTER TABLE `specimen` DISABLE KEYS */;
|
|
879
|
/*!40000 ALTER TABLE `specimen` ENABLE KEYS */;
|
|
880
|
|
|
881
|
--
|
|
882
|
-- Table structure for table `specimen_observation`
|
|
883
|
--
|
|
884
|
|
|
885
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
886
|
/*!40101 SET character_set_client = utf8 */;
|
|
887
|
CREATE TABLE `specimen_observation` (
|
|
888
|
`id` varbinary(767) NOT NULL,
|
|
889
|
`subject` varbinary(767) NOT NULL,
|
|
890
|
`current_collection` varbinary(767) DEFAULT NULL,
|
|
891
|
`owner_collection` varbinary(767) DEFAULT NULL,
|
|
892
|
`description` varbinary(767) DEFAULT NULL,
|
|
893
|
PRIMARY KEY (`id`),
|
|
894
|
KEY `fk_specimen_observation_specimen1_idx` (`subject`),
|
|
895
|
KEY `specimen_observation_id_fkey1_idx` (`current_collection`),
|
|
896
|
KEY `specimen_observation_id_fkey2_idx` (`owner_collection`),
|
|
897
|
CONSTRAINT `fk_specimen_observation_specimen1` FOREIGN KEY (`subject`) REFERENCES `specimen` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
898
|
CONSTRAINT `fk_specimen_taxon_occurrence10` FOREIGN KEY (`id`) REFERENCES `taxon_observation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
899
|
CONSTRAINT `specimen_observation_id_fkey1` FOREIGN KEY (`current_collection`) REFERENCES `collection` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
900
|
CONSTRAINT `specimen_observation_id_fkey2` FOREIGN KEY (`owner_collection`) REFERENCES `collection` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
901
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
|
|
902
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
903
|
|
|
904
|
--
|
|
905
|
-- Dumping data for table `specimen_observation`
|
|
906
|
--
|
|
907
|
|
|
908
|
/*!40000 ALTER TABLE `specimen_observation` DISABLE KEYS */;
|
|
909
|
/*!40000 ALTER TABLE `specimen_observation` ENABLE KEYS */;
|
|
910
|
|
|
911
|
--
|
|
912
|
-- Table structure for table `stem`
|
|
913
|
--
|
|
914
|
|
|
915
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
916
|
/*!40101 SET character_set_client = utf8 */;
|
|
917
|
CREATE TABLE `stem` (
|
|
918
|
`id` varbinary(767) NOT NULL,
|
|
919
|
`individual` varbinary(767) DEFAULT NULL,
|
|
920
|
PRIMARY KEY (`id`),
|
|
921
|
KEY `fk_stem_individual1_idx` (`individual`),
|
|
922
|
CONSTRAINT `fk_stem_individual1` FOREIGN KEY (`individual`) REFERENCES `individual` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
923
|
CONSTRAINT `fk_stem_individual2` FOREIGN KEY (`id`) REFERENCES `individual` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
924
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='An "individual tree stem" ("VegBank":http://vegbank.org/vegbank/views/dba_tabledescription_detail.jsp?view=detail&wparam=stemlocation&entity=dba_tabledescription&where=where_tablename)';
|
|
925
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
926
|
|
|
927
|
--
|
|
928
|
-- Dumping data for table `stem`
|
|
929
|
--
|
|
930
|
|
|
931
|
/*!40000 ALTER TABLE `stem` DISABLE KEYS */;
|
|
932
|
/*!40000 ALTER TABLE `stem` ENABLE KEYS */;
|
|
933
|
|
|
934
|
--
|
|
935
|
-- Table structure for table `stem_observation`
|
|
936
|
--
|
|
937
|
|
|
938
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
939
|
/*!40101 SET character_set_client = utf8 */;
|
|
940
|
CREATE TABLE `stem_observation` (
|
|
941
|
`id` varbinary(767) NOT NULL,
|
|
942
|
`subject` varbinary(767) DEFAULT NULL,
|
|
943
|
`individual_observation` varbinary(767) DEFAULT NULL,
|
|
944
|
PRIMARY KEY (`id`),
|
|
945
|
UNIQUE KEY `stem_observation_unique` (`individual_observation`,`subject`),
|
|
946
|
KEY `fk_stem_observation_individual_observation1_idx` (`individual_observation`),
|
|
947
|
KEY `fk_stem_observation_stem1_idx` (`subject`),
|
|
948
|
CONSTRAINT `fk_stem_observation_individual_observation1` FOREIGN KEY (`individual_observation`) REFERENCES `individual_observation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
949
|
CONSTRAINT `fk_stem_observation_individual_observation2` FOREIGN KEY (`id`) REFERENCES `individual_observation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
950
|
CONSTRAINT `fk_stem_observation_stem1` FOREIGN KEY (`subject`) REFERENCES `stem` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
951
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='An observation of a [[VegCore#Stem|Stem]]';
|
|
952
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
953
|
|
|
954
|
--
|
|
955
|
-- Dumping data for table `stem_observation`
|
|
956
|
--
|
|
957
|
|
|
958
|
/*!40000 ALTER TABLE `stem_observation` DISABLE KEYS */;
|
|
959
|
/*!40000 ALTER TABLE `stem_observation` ENABLE KEYS */;
|
|
960
|
|
|
961
|
--
|
|
962
|
-- Table structure for table `stratum`
|
|
963
|
--
|
|
964
|
|
|
965
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
966
|
/*!40101 SET character_set_client = utf8 */;
|
|
967
|
CREATE TABLE `stratum` (
|
|
968
|
`id` varbinary(767) NOT NULL,
|
|
969
|
`name` varbinary(767) NOT NULL,
|
|
970
|
`height_min_m` varbinary(767) DEFAULT NULL,
|
|
971
|
`height_max_m` varbinary(767) DEFAULT NULL,
|
|
972
|
PRIMARY KEY (`id`),
|
|
973
|
CONSTRAINT `fk_layer_stratum1` FOREIGN KEY (`id`) REFERENCES `method` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
974
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='= VegBank.vegpath.org?stratumType';
|
|
975
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
976
|
|
|
977
|
--
|
|
978
|
-- Dumping data for table `stratum`
|
|
979
|
--
|
|
980
|
|
|
981
|
/*!40000 ALTER TABLE `stratum` DISABLE KEYS */;
|
|
982
|
/*!40000 ALTER TABLE `stratum` ENABLE KEYS */;
|
|
983
|
|
|
984
|
--
|
|
985
|
-- Table structure for table `subplace`
|
|
986
|
--
|
|
987
|
|
|
988
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
989
|
/*!40101 SET character_set_client = utf8 */;
|
|
990
|
CREATE TABLE `subplace` (
|
|
991
|
`id` varbinary(767) NOT NULL,
|
|
992
|
`parent` varbinary(767) NOT NULL,
|
|
993
|
`x_m` double DEFAULT NULL,
|
|
994
|
`y_m` double DEFAULT NULL,
|
|
995
|
`coords` set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
996
|
PRIMARY KEY (`id`),
|
|
997
|
KEY `fk_rel_place_place1_idx` (`parent`),
|
|
998
|
CONSTRAINT `fk_rel_place_place1` FOREIGN KEY (`parent`) REFERENCES `place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
999
|
CONSTRAINT `fk_subplot_place2` FOREIGN KEY (`id`) REFERENCES `place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
1000
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='stores plot elements such as subplots ("subplot, line, or any other subsample or subdivision of plot" ("SALVIAS":http://salvias.net/Documents/salvias_data_dictionary.html#Plot_data/subplot))';
|
|
1001
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
1002
|
|
|
1003
|
--
|
|
1004
|
-- Dumping data for table `subplace`
|
|
1005
|
--
|
|
1006
|
|
|
1007
|
/*!40000 ALTER TABLE `subplace` DISABLE KEYS */;
|
|
1008
|
/*!40000 ALTER TABLE `subplace` ENABLE KEYS */;
|
|
1009
|
|
|
1010
|
--
|
|
1011
|
-- Table structure for table `subplot`
|
|
1012
|
--
|
|
1013
|
|
|
1014
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
1015
|
/*!40101 SET character_set_client = utf8 */;
|
|
1016
|
CREATE TABLE `subplot` (
|
|
1017
|
`id` varbinary(767) NOT NULL,
|
|
1018
|
`parent` varbinary(767) NOT NULL COMMENT 'the parent plot',
|
|
1019
|
PRIMARY KEY (`id`),
|
|
1020
|
KEY `fk_subplot_plot2_idx` (`parent`),
|
|
1021
|
CONSTRAINT `fk_subplot_plot1` FOREIGN KEY (`id`) REFERENCES `plot` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
1022
|
CONSTRAINT `fk_subplot_plot2` FOREIGN KEY (`parent`) REFERENCES `plot` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
1023
|
CONSTRAINT `fk_subplot_rel_place1` FOREIGN KEY (`id`) REFERENCES `subplace` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
1024
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
|
|
1025
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
1026
|
|
|
1027
|
--
|
|
1028
|
-- Dumping data for table `subplot`
|
|
1029
|
--
|
|
1030
|
|
|
1031
|
/*!40000 ALTER TABLE `subplot` DISABLE KEYS */;
|
|
1032
|
/*!40000 ALTER TABLE `subplot` ENABLE KEYS */;
|
|
1033
|
|
|
1034
|
--
|
|
1035
|
-- Table structure for table `taxa_sampling_event`
|
|
1036
|
--
|
|
1037
|
|
|
1038
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
1039
|
/*!40101 SET character_set_client = utf8 */;
|
|
1040
|
CREATE TABLE `taxa_sampling_event` (
|
|
1041
|
`id` varbinary(767) NOT NULL,
|
|
1042
|
`parent` varbinary(767) DEFAULT NULL COMMENT 'the taxa_sampling_event for the parent stratum',
|
|
1043
|
`place_visit` varbinary(767) NOT NULL,
|
|
1044
|
`subsetting_method` varbinary(767) DEFAULT NULL,
|
|
1045
|
PRIMARY KEY (`id`),
|
|
1046
|
UNIQUE KEY `taxa_sampling_event__unique` (`place_visit`,`subsetting_method`),
|
|
1047
|
KEY `fk_sampling_event_method1_idx` (`subsetting_method`),
|
|
1048
|
KEY `taxa_sampling_event_id_fkey1_idx` (`place_visit`),
|
|
1049
|
KEY `taxa_sampling_event_id_fkey2_idx` (`parent`),
|
|
1050
|
CONSTRAINT `fk_project_event10` FOREIGN KEY (`id`) REFERENCES `event` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
1051
|
CONSTRAINT `fk_sampling_event_method1` FOREIGN KEY (`subsetting_method`) REFERENCES `method` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
1052
|
CONSTRAINT `taxa_sampling_event_id_fkey1` FOREIGN KEY (`place_visit`) REFERENCES `place_visit` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
1053
|
CONSTRAINT `taxa_sampling_event_id_fkey2` FOREIGN KEY (`parent`) REFERENCES `taxa_sampling_event` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
1054
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='= VegX.vegpath.org?stratumObservation, VegBank.vegpath.org?stratum (which was confusingly named)';
|
|
1055
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
1056
|
|
|
1057
|
--
|
|
1058
|
-- Dumping data for table `taxa_sampling_event`
|
|
1059
|
--
|
|
1060
|
|
|
1061
|
/*!40000 ALTER TABLE `taxa_sampling_event` DISABLE KEYS */;
|
|
1062
|
/*!40000 ALTER TABLE `taxa_sampling_event` ENABLE KEYS */;
|
|
1063
|
|
|
1064
|
--
|
|
1065
|
-- Table structure for table `taxon_absence`
|
|
1066
|
--
|
|
1067
|
|
|
1068
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
1069
|
/*!40101 SET character_set_client = utf8 */;
|
|
1070
|
CREATE TABLE `taxon_absence` (
|
|
1071
|
`id` varbinary(767) NOT NULL,
|
|
1072
|
PRIMARY KEY (`id`),
|
|
1073
|
CONSTRAINT `fk_taxon_absence_taxa_sampling_event1` FOREIGN KEY (`id`) REFERENCES `aggregate_observation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
1074
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='An observation that a [[VegCore#Taxon|Taxon]]''s does _not_ exist in a place';
|
|
1075
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
1076
|
|
|
1077
|
--
|
|
1078
|
-- Dumping data for table `taxon_absence`
|
|
1079
|
--
|
|
1080
|
|
|
1081
|
/*!40000 ALTER TABLE `taxon_absence` DISABLE KEYS */;
|
|
1082
|
/*!40000 ALTER TABLE `taxon_absence` ENABLE KEYS */;
|
|
1083
|
|
|
1084
|
--
|
|
1085
|
-- Table structure for table `taxon_assertion`
|
|
1086
|
--
|
|
1087
|
|
|
1088
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
1089
|
/*!40101 SET character_set_client = utf8 */;
|
|
1090
|
CREATE TABLE `taxon_assertion` (
|
|
1091
|
`id` varbinary(767) NOT NULL,
|
|
1092
|
`string` varbinary(767) NOT NULL COMMENT 'for parsed_taxon_assertion, this is the TNRS input name, not the concatenated matched name',
|
|
1093
|
`taxon` varbinary(767) DEFAULT NULL,
|
|
1094
|
`cf_aff` varbinary(767) DEFAULT NULL,
|
|
1095
|
`annotations` set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
1096
|
PRIMARY KEY (`id`),
|
|
1097
|
KEY `fk_taxon_assertion_taxon_string1_idx` (`string`),
|
|
1098
|
KEY `fk_taxon_assertion_taxon_name1_idx` (`taxon`),
|
|
1099
|
CONSTRAINT `fk_qualified_taxon_record1` FOREIGN KEY (`id`) REFERENCES `traceable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
1100
|
CONSTRAINT `fk_taxon_assertion_taxon_name1` FOREIGN KEY (`taxon`) REFERENCES `taxon_name` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
1101
|
CONSTRAINT `fk_taxon_assertion_taxon_string1` FOREIGN KEY (`string`) REFERENCES `taxon_string` (`string`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
1102
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
|
|
1103
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
1104
|
|
|
1105
|
--
|
|
1106
|
-- Dumping data for table `taxon_assertion`
|
|
1107
|
--
|
|
1108
|
|
|
1109
|
/*!40000 ALTER TABLE `taxon_assertion` DISABLE KEYS */;
|
|
1110
|
/*!40000 ALTER TABLE `taxon_assertion` ENABLE KEYS */;
|
|
1111
|
|
|
1112
|
--
|
|
1113
|
-- Table structure for table `taxon_concept`
|
|
1114
|
--
|
|
1115
|
|
|
1116
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
1117
|
/*!40101 SET character_set_client = utf8 */;
|
|
1118
|
CREATE TABLE `taxon_concept` (
|
|
1119
|
`id` varbinary(767) NOT NULL,
|
|
1120
|
`according_to` varbinary(767) NOT NULL,
|
|
1121
|
`parent` varbinary(767) DEFAULT NULL,
|
|
1122
|
`accepted_taxon_concept` varbinary(767) DEFAULT NULL,
|
|
1123
|
PRIMARY KEY (`id`),
|
|
1124
|
UNIQUE KEY `taxon_concept_unique_name` (`according_to`),
|
|
1125
|
KEY `fk_taxon_taxon1_idx` (`parent`),
|
|
1126
|
KEY `fk_taxon_concept_source1_idx` (`according_to`),
|
|
1127
|
KEY `fk_taxon_concept_taxon_concept1_idx` (`accepted_taxon_concept`),
|
|
1128
|
CONSTRAINT `fk_taxon_concept_source1` FOREIGN KEY (`according_to`) REFERENCES `dataset` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
1129
|
CONSTRAINT `fk_taxon_concept_taxon_concept1` FOREIGN KEY (`accepted_taxon_concept`) REFERENCES `taxon_concept` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
1130
|
CONSTRAINT `fk_taxon_concept_taxon_name1` FOREIGN KEY (`id`) REFERENCES `taxon_name` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
1131
|
CONSTRAINT `fk_taxon_taxon1` FOREIGN KEY (`parent`) REFERENCES `taxon_concept` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
1132
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='A [[VegCore#Taxon|Taxon]] described by a specific [[VegCore#Reference|Reference]]';
|
|
1133
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
1134
|
|
|
1135
|
--
|
|
1136
|
-- Dumping data for table `taxon_concept`
|
|
1137
|
--
|
|
1138
|
|
|
1139
|
/*!40000 ALTER TABLE `taxon_concept` DISABLE KEYS */;
|
|
1140
|
/*!40000 ALTER TABLE `taxon_concept` ENABLE KEYS */;
|
|
1141
|
|
|
1142
|
--
|
|
1143
|
-- Table structure for table `taxon_determination`
|
|
1144
|
--
|
|
1145
|
|
|
1146
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
1147
|
/*!40101 SET character_set_client = utf8 */;
|
|
1148
|
CREATE TABLE `taxon_determination` (
|
|
1149
|
`id` varbinary(767) NOT NULL,
|
|
1150
|
`taxon_assertion` varbinary(767) NOT NULL,
|
|
1151
|
`voucher` varbinary(767) DEFAULT NULL,
|
|
1152
|
`identified_by` varbinary(767) DEFAULT NULL,
|
|
1153
|
`fit_info` set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
1154
|
PRIMARY KEY (`id`),
|
|
1155
|
UNIQUE KEY `taxon_determination_unique` (`taxon_assertion`,`identified_by`),
|
|
1156
|
KEY `fk_taxon_occurrence_has_qualified_taxon1_idx` (`taxon_assertion`),
|
|
1157
|
KEY `fk_taxon_determination_party_list1_idx` (`identified_by`),
|
|
1158
|
KEY `taxon_determination_id_fkey1_idx` (`voucher`),
|
|
1159
|
CONSTRAINT `fk_taxon_determination_party_list1` FOREIGN KEY (`identified_by`) REFERENCES `party_list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
1160
|
CONSTRAINT `fk_taxon_determination_taxon_observation1` FOREIGN KEY (`id`) REFERENCES `taxon_observation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
1161
|
CONSTRAINT `fk_taxon_occurrence_has_qualified_taxon1` FOREIGN KEY (`taxon_assertion`) REFERENCES `taxon_assertion` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
1162
|
CONSTRAINT `taxon_determination_id_fkey1` FOREIGN KEY (`voucher`) REFERENCES `reobservable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
1163
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='An assertion that a [[VegCore#TaxonOccurrence|TaxonOccurrence]] is a particular [[VegCore#Taxon|Taxon]]';
|
|
1164
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
1165
|
|
|
1166
|
--
|
|
1167
|
-- Dumping data for table `taxon_determination`
|
|
1168
|
--
|
|
1169
|
|
|
1170
|
/*!40000 ALTER TABLE `taxon_determination` DISABLE KEYS */;
|
|
1171
|
/*!40000 ALTER TABLE `taxon_determination` ENABLE KEYS */;
|
|
1172
|
|
|
1173
|
--
|
|
1174
|
-- Table structure for table `taxon_name`
|
|
1175
|
--
|
|
1176
|
|
|
1177
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
1178
|
/*!40101 SET character_set_client = utf8 */;
|
|
1179
|
CREATE TABLE `taxon_name` (
|
|
1180
|
`id` varbinary(767) NOT NULL,
|
|
1181
|
`unique_name` varbinary(767) NOT NULL,
|
|
1182
|
`taxon_path` varbinary(767) DEFAULT NULL,
|
|
1183
|
`formal_name` varbinary(767) DEFAULT NULL,
|
|
1184
|
`taxon_name` varbinary(767) DEFAULT NULL,
|
|
1185
|
`author` varbinary(767) DEFAULT NULL,
|
|
1186
|
`common_name` varbinary(767) DEFAULT NULL,
|
|
1187
|
`rank` varbinary(767) DEFAULT NULL,
|
|
1188
|
PRIMARY KEY (`id`),
|
|
1189
|
KEY `fk_taxon_concept_taxon_string10_idx` (`unique_name`),
|
|
1190
|
KEY `fk_taxon_name_taxon_path1_idx` (`taxon_path`),
|
|
1191
|
CONSTRAINT `fk_taxon_concept_taxon_string10` FOREIGN KEY (`unique_name`) REFERENCES `taxon_string` (`string`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
1192
|
CONSTRAINT `fk_taxon_name_taxon_path1` FOREIGN KEY (`taxon_path`) REFERENCES `taxon_path` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
1193
|
CONSTRAINT `fk_taxon_record10` FOREIGN KEY (`id`) REFERENCES `traceable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
1194
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
|
|
1195
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
1196
|
|
|
1197
|
--
|
|
1198
|
-- Dumping data for table `taxon_name`
|
|
1199
|
--
|
|
1200
|
|
|
1201
|
/*!40000 ALTER TABLE `taxon_name` DISABLE KEYS */;
|
|
1202
|
/*!40000 ALTER TABLE `taxon_name` ENABLE KEYS */;
|
|
1203
|
|
|
1204
|
--
|
|
1205
|
-- Table structure for table `taxon_observation`
|
|
1206
|
--
|
|
1207
|
|
|
1208
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
1209
|
/*!40101 SET character_set_client = utf8 */;
|
|
1210
|
CREATE TABLE `taxon_observation` (
|
|
1211
|
`id` varbinary(767) NOT NULL,
|
|
1212
|
`parent` varbinary(767) DEFAULT NULL COMMENT 'the observation of the same taxon_occurrence in the parent sampling_event',
|
|
1213
|
`subject` varbinary(767) DEFAULT NULL,
|
|
1214
|
`taxon_occurrence` varbinary(767) NOT NULL COMMENT 'when not provided (and not fillable from another field), a taxon_occurrence will be created with the same id as the taxon_observation',
|
|
1215
|
`sampling_event` varbinary(767) NOT NULL COMMENT 'for specimens, this will have the same event information (date_range, participants, place) as the taxon_observation',
|
|
1216
|
`primary_collector` varbinary(767) DEFAULT NULL,
|
|
1217
|
`collector_number` varbinary(767) DEFAULT NULL,
|
|
1218
|
`all_collectors` varbinary(767) DEFAULT NULL,
|
|
1219
|
`traits` set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
1220
|
`growth_form` varbinary(767) DEFAULT NULL,
|
|
1221
|
`cultivated` tinyint(1) DEFAULT NULL,
|
|
1222
|
PRIMARY KEY (`id`),
|
|
1223
|
KEY `fk_taxon_observation_taxon_occurrence1_idx` (`taxon_occurrence`),
|
|
1224
|
KEY `fk_taxon_observation_taxa_sampling_event1_idx` (`sampling_event`),
|
|
1225
|
KEY `taxon_observation_id_fkey1_idx` (`primary_collector`),
|
|
1226
|
KEY `fk_taxon_observation_party_list1_idx` (`all_collectors`),
|
|
1227
|
KEY `taxon_observation__unique` (`sampling_event`,`primary_collector`,`collector_number`),
|
|
1228
|
KEY `taxon_observation_id_fkey2_idx` (`parent`),
|
|
1229
|
KEY `taxon_observation_id_fkey3_idx` (`subject`),
|
|
1230
|
CONSTRAINT `fk_taxon_observation_party_list1` FOREIGN KEY (`all_collectors`) REFERENCES `party_list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
1231
|
CONSTRAINT `fk_taxon_observation_sampling_event1` FOREIGN KEY (`id`) REFERENCES `event` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
1232
|
CONSTRAINT `fk_taxon_observation_taxa_sampling_event1` FOREIGN KEY (`sampling_event`) REFERENCES `taxa_sampling_event` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
1233
|
CONSTRAINT `fk_taxon_observation_taxon_occurrence1` FOREIGN KEY (`taxon_occurrence`) REFERENCES `taxon_occurrence` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
1234
|
CONSTRAINT `taxon_observation_id_fkey1` FOREIGN KEY (`primary_collector`) REFERENCES `party` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
1235
|
CONSTRAINT `taxon_observation_id_fkey2` FOREIGN KEY (`parent`) REFERENCES `taxon_observation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
1236
|
CONSTRAINT `taxon_observation_id_fkey3` FOREIGN KEY (`subject`) REFERENCES `taxon_occurrence` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
1237
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='= DwC.vegpath.org?Occurrence (which actually represents an *observation* of an occurrence)';
|
|
1238
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
1239
|
|
|
1240
|
--
|
|
1241
|
-- Dumping data for table `taxon_observation`
|
|
1242
|
--
|
|
1243
|
|
|
1244
|
/*!40000 ALTER TABLE `taxon_observation` DISABLE KEYS */;
|
|
1245
|
/*!40000 ALTER TABLE `taxon_observation` ENABLE KEYS */;
|
|
1246
|
|
|
1247
|
--
|
|
1248
|
-- Table structure for table `taxon_occurrence`
|
|
1249
|
--
|
|
1250
|
|
|
1251
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
1252
|
/*!40101 SET character_set_client = utf8 */;
|
|
1253
|
CREATE TABLE `taxon_occurrence` (
|
|
1254
|
`id` varbinary(767) NOT NULL,
|
|
1255
|
`within_place` varbinary(767) NOT NULL,
|
|
1256
|
`current_observation` varbinary(767) DEFAULT NULL,
|
|
1257
|
PRIMARY KEY (`id`),
|
|
1258
|
KEY `fk_taxon_occurrence_taxon_determination3_idx` (`current_observation`),
|
|
1259
|
KEY `fk_taxon_occurrence_place1_idx` (`within_place`),
|
|
1260
|
CONSTRAINT `fk_taxon_occurrence_place1` FOREIGN KEY (`within_place`) REFERENCES `place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
1261
|
CONSTRAINT `fk_taxon_occurrence_record1` FOREIGN KEY (`id`) REFERENCES `traceable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
1262
|
CONSTRAINT `fk_taxon_occurrence_taxon_determination3` FOREIGN KEY (`current_observation`) REFERENCES `taxon_observation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
1263
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='*not* DwC.vegpath.org?Occurrence';
|
|
1264
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
1265
|
|
|
1266
|
--
|
|
1267
|
-- Dumping data for table `taxon_occurrence`
|
|
1268
|
--
|
|
1269
|
|
|
1270
|
/*!40000 ALTER TABLE `taxon_occurrence` DISABLE KEYS */;
|
|
1271
|
/*!40000 ALTER TABLE `taxon_occurrence` ENABLE KEYS */;
|
|
1272
|
|
|
1273
|
--
|
|
1274
|
-- Table structure for table `taxon_path`
|
|
1275
|
--
|
|
1276
|
|
|
1277
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
1278
|
/*!40101 SET character_set_client = utf8 */;
|
|
1279
|
CREATE TABLE `taxon_path` (
|
|
1280
|
`id` varbinary(767) NOT NULL,
|
|
1281
|
`family` varbinary(767) DEFAULT NULL,
|
|
1282
|
`genus` varbinary(767) DEFAULT NULL,
|
|
1283
|
`specific_epithet` varbinary(767) DEFAULT NULL,
|
|
1284
|
`ranks` set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
1285
|
PRIMARY KEY (`id`)
|
|
1286
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='"a group of one (or more) populations of organism(s), which a taxonomist adjudges to be a unit" ("Wikipedia":http://en.wikipedia.org/wiki/Taxon)';
|
|
1287
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
1288
|
|
|
1289
|
--
|
|
1290
|
-- Dumping data for table `taxon_path`
|
|
1291
|
--
|
|
1292
|
|
|
1293
|
/*!40000 ALTER TABLE `taxon_path` DISABLE KEYS */;
|
|
1294
|
/*!40000 ALTER TABLE `taxon_path` ENABLE KEYS */;
|
|
1295
|
|
|
1296
|
--
|
|
1297
|
-- Table structure for table `taxon_presence`
|
|
1298
|
--
|
|
1299
|
|
|
1300
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
1301
|
/*!40101 SET character_set_client = utf8 */;
|
|
1302
|
CREATE TABLE `taxon_presence` (
|
|
1303
|
`id` varbinary(767) NOT NULL,
|
|
1304
|
`cover_percent` double DEFAULT NULL,
|
|
1305
|
PRIMARY KEY (`id`),
|
|
1306
|
CONSTRAINT `fk_taxon_presence_taxon_determination1` FOREIGN KEY (`id`) REFERENCES `aggregate_observation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
1307
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='= VegX.vegpath.org?aggregateOrganismObservation, VegBank.vegpath.org?taxonImportance; "An observation applying to all occurrences of an organism" ("VegX":http://wiki.tdwg.org/twiki/pub/Vegetation/WebHome/VegX_Schema_1.5.3_proposed.zip#/veg.xsd#/aggregateOrganismObservations/aggregateOrganismObservation).';
|
|
1308
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
1309
|
|
|
1310
|
--
|
|
1311
|
-- Dumping data for table `taxon_presence`
|
|
1312
|
--
|
|
1313
|
|
|
1314
|
/*!40000 ALTER TABLE `taxon_presence` DISABLE KEYS */;
|
|
1315
|
/*!40000 ALTER TABLE `taxon_presence` ENABLE KEYS */;
|
|
1316
|
|
|
1317
|
--
|
|
1318
|
-- Table structure for table `taxon_scrub`
|
|
1319
|
--
|
|
1320
|
|
|
1321
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
1322
|
/*!40101 SET character_set_client = utf8 */;
|
|
1323
|
CREATE TABLE `taxon_scrub` (
|
|
1324
|
`id` varbinary(767) NOT NULL,
|
|
1325
|
`input_string` varbinary(767) NOT NULL,
|
|
1326
|
`parsed_taxon_assertion` varbinary(767) NOT NULL,
|
|
1327
|
`matched_taxon_concept` varbinary(767) DEFAULT NULL,
|
|
1328
|
`match_info` set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
|
1329
|
`match_score` float DEFAULT NULL,
|
|
1330
|
PRIMARY KEY (`id`),
|
|
1331
|
KEY `fk_parsed_taxon_assertion_taxon_name1_idx` (`matched_taxon_concept`),
|
|
1332
|
KEY `fk_taxon_scrub_taxon_assertion1_idx` (`parsed_taxon_assertion`),
|
|
1333
|
KEY `fk_taxon_scrub_taxon_string1_idx` (`input_string`),
|
|
1334
|
CONSTRAINT `fk_parsed_taxon_assertion_taxon_name1` FOREIGN KEY (`matched_taxon_concept`) REFERENCES `taxon_concept` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
1335
|
CONSTRAINT `fk_taxon_scrub_record1` FOREIGN KEY (`id`) REFERENCES `traceable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
1336
|
CONSTRAINT `fk_taxon_scrub_taxon_assertion1` FOREIGN KEY (`parsed_taxon_assertion`) REFERENCES `taxon_assertion` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
1337
|
CONSTRAINT `fk_taxon_scrub_taxon_string1` FOREIGN KEY (`input_string`) REFERENCES `taxon_string` (`string`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
1338
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
|
|
1339
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
1340
|
|
|
1341
|
--
|
|
1342
|
-- Dumping data for table `taxon_scrub`
|
|
1343
|
--
|
|
1344
|
|
|
1345
|
/*!40000 ALTER TABLE `taxon_scrub` DISABLE KEYS */;
|
|
1346
|
/*!40000 ALTER TABLE `taxon_scrub` ENABLE KEYS */;
|
|
1347
|
|
|
1348
|
--
|
|
1349
|
-- Table structure for table `taxon_string`
|
|
1350
|
--
|
|
1351
|
|
|
1352
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
1353
|
/*!40101 SET character_set_client = utf8 */;
|
|
1354
|
CREATE TABLE `taxon_string` (
|
|
1355
|
`string` varbinary(767) NOT NULL,
|
|
1356
|
PRIMARY KEY (`string`)
|
|
1357
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='to get the parsed_taxon_assertion (TNRS result) for a taxon_string, join using taxon_string.string<-taxon_assertion(string)::parsed_taxon_assertion[source=''TNRS.version''] (see wiki.vegpath.org/SQL_dotpaths)';
|
|
1358
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
1359
|
|
|
1360
|
--
|
|
1361
|
-- Dumping data for table `taxon_string`
|
|
1362
|
--
|
|
1363
|
|
|
1364
|
/*!40000 ALTER TABLE `taxon_string` DISABLE KEYS */;
|
|
1365
|
/*!40000 ALTER TABLE `taxon_string` ENABLE KEYS */;
|
|
1366
|
|
|
1367
|
--
|
|
1368
|
-- Table structure for table `traceable`
|
|
1369
|
--
|
|
1370
|
|
|
1371
|
/*!40101 SET @saved_cs_client = @@character_set_client */;
|
|
1372
|
/*!40101 SET character_set_client = utf8 */;
|
|
1373
|
CREATE TABLE `traceable` (
|
|
1374
|
`id` varbinary(767) NOT NULL,
|
|
1375
|
`ids_by_source` set('text[]') COLLATE utf8_bin DEFAULT NULL COMMENT 'allows referencing a traceable by its source information even when the subclass chose a different value for the pkey',
|
|
1376
|
`source` varbinary(767) NOT NULL,
|
|
1377
|
`id_within_source` varbinary(767) DEFAULT NULL COMMENT 'the portion of the source that this traceable refers to. when specified, this must *uniquely identify* the traceable within the source. denormalized source data often contains data for many VegCore tables in the same row, and the traceables for each of these table entries must be distinguished *from each other* since they share the same source. this is usually just the VegCore table name, sometimes with a distinguishing prefix (e.g. collector.party/identified_by.party; current_observation.taxon_determination/orig_observation.taxon_determination). this can also identify e.g. a dataset within the source that it came from.',
|
|
1378
|
`authors` varbinary(767) DEFAULT NULL COMMENT 'the people who created the information in the data record. this is *different* from dataset.data_owners, and refers *only* to original data creators such as collectors and identifiers. note that in vegetation data, these record-specific authors generally do not receive attribution. this makes traceable mutually recursive with party_list. = Brad.vegpath.org?attribution.data_author',
|
|
1379
|
PRIMARY KEY (`id`),
|
|
1380
|
UNIQUE KEY `traceable_unique` (`source`,`id_within_source`),
|
|
1381
|
UNIQUE KEY `traceable__id_by_source` (`ids_by_source`),
|
|
1382
|
KEY `traceable_id_fkey1_idx` (`authors`),
|
|
1383
|
CONSTRAINT `traceable_id_fkey1` FOREIGN KEY (`authors`) REFERENCES `party_list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
1384
|
CONSTRAINT `fk_record_source1` FOREIGN KEY (`source`) REFERENCES `source` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
1385
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='a row that can have associated source information';
|
|
1386
|
/*!40101 SET character_set_client = @saved_cs_client */;
|
|
1387
|
|
|
1388
|
--
|
|
1389
|
-- Dumping data for table `traceable`
|
|
1390
|
--
|
|
1391
|
|
|
1392
|
/*!40000 ALTER TABLE `traceable` DISABLE KEYS */;
|
|
1393
|
/*!40000 ALTER TABLE `traceable` ENABLE KEYS */;
|
|
1394
|
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
|
|
1395
|
|
|
1396
|
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
|
|
1397
|
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
|
|
1398
|
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
|
|
1399
|
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
|
|
1400
|
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
|
|
1401
|
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
|
|
1402
|
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
|
|
1403
|
|
|
1404
|
-- Dump completed
|
