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