Project

General

Profile

1
-- Existing tables
2

    
3
CREATE TABLE aux_role
4
(
5
  role_id serial NOT NULL,
6
  rolecode character varying(30) NOT NULL,
7
  roledescription character varying(200),
8
  CONSTRAINT aux_role_pkey PRIMARY KEY (role_id )
9
);
10

    
11
CREATE TABLE reference
12
(
13
  reference_id serial NOT NULL,
14
  shortname character varying(250),
15
  fulltext text,
16
  referencetype character varying(250)
17
);
18

    
19
CREATE TABLE party
20
(
21
  party_id serial NOT NULL,
22
  salutation character varying(20),
23
  givenname character varying(50),
24
  middlename character varying(50),
25
  surname character varying(50),
26
  organizationname character varying(100)
27
);
28

    
29
CREATE TABLE plantname
30
(
31
  plantname_id serial NOT NULL,
32
  plantname character varying(255) NOT NULL,
33
  reference_id integer,
34
  dateentered timestamp with time zone DEFAULT now(),
35
  CONSTRAINT plantname_pkey PRIMARY KEY (plantname_id ),
36
  CONSTRAINT plantname_reference_id FOREIGN KEY (reference_id)
37
      REFERENCES reference (reference_id) MATCH SIMPLE
38
      ON UPDATE CASCADE ON DELETE CASCADE
39
);
40

    
41
CREATE TABLE plantconcept
42
(
43
  plantconcept_id serial NOT NULL,
44
  plantname_id integer NOT NULL,
45
  reference_id integer NOT NULL,
46
  plantname character varying(200),
47
  plantcode character varying(23),
48
  plantdescription text,
49
  d_obscount integer,
50
  d_currentaccepted boolean,
51
  accessioncode character varying(255),
52
  CONSTRAINT plantconcept_pkey PRIMARY KEY (plantconcept_id ),
53
  CONSTRAINT plantconcept_plantname_id FOREIGN KEY (plantname_id)
54
      REFERENCES plantname (plantname_id) MATCH SIMPLE
55
      ON UPDATE CASCADE ON DELETE CASCADE,
56
  CONSTRAINT plantconcept_reference_id FOREIGN KEY (reference_id)
57
      REFERENCES reference (reference_id) MATCH SIMPLE
58
      ON UPDATE CASCADE ON DELETE CASCADE
59
);
60

    
61
CREATE TABLE namedplace
62
(
63
  namedplace_id serial NOT NULL,
64
  placesystem character varying(50),
65
  placename character varying(100) NOT NULL,
66
  placedescription text,
67
  placecode character varying(15),
68
  owner character varying(100),
69
  reference_id integer,
70
  d_obscount integer,
71
  accessioncode character varying(255),
72
  CONSTRAINT namedplace_pkey PRIMARY KEY (namedplace_id ),
73
  CONSTRAINT namedplace_reference_id FOREIGN KEY (reference_id)
74
      REFERENCES reference (reference_id) MATCH SIMPLE
75
      ON UPDATE CASCADE ON DELETE CASCADE,
76
  CONSTRAINT namedplace_keys UNIQUE (placesystem , placename )
77
);
78

    
79
CREATE TABLE locationplace
80
(
81
  locationplace_id serial NOT NULL,
82
  location_id integer NOT NULL,
83
  calculated boolean,
84
  namedplace_id integer NOT NULL,
85
  CONSTRAINT locationplace_pkey PRIMARY KEY (locationplace_id ),
86
  CONSTRAINT locationplace_location_id FOREIGN KEY (location_id)
87
      REFERENCES location (location_id) MATCH SIMPLE
88
      ON UPDATE CASCADE ON DELETE CASCADE,
89
  CONSTRAINT locationplace_namedplace_id FOREIGN KEY (namedplace_id)
90
      REFERENCES namedplace (namedplace_id) MATCH SIMPLE
91
      ON UPDATE CASCADE ON DELETE CASCADE,
92
  CONSTRAINT locationplace_keys UNIQUE (location_id , namedplace_id )
93
);
94

    
95
CREATE TABLE project
96
(
97
  project_id serial NOT NULL,
98
  projectname character varying(150) NOT NULL,
99
  projectdescription text,
100
  startdate timestamp with time zone,
101
  stopdate timestamp with time zone,
102
  d_obscount integer,
103
  d_lastlocationaddeddate timestamp with time zone,
104
  accessioncode character varying(255),
105
  CONSTRAINT project_pkey PRIMARY KEY (project_id ),
106
  CONSTRAINT project_keys UNIQUE (projectname , startdate , stopdate )
107
);
108

    
109
-- New tables
110

    
111
CREATE TABLE location
112
(
113
  location_id serial NOT NULL,
114
  authorlocationcode character varying(30) NOT NULL DEFAULT ''::character varying,
115
  reference_id integer,
116
  parent_id integer,
117
  reallatitude double precision,
118
  reallongitude double precision,
119
  locationaccuracy double precision,
120
  confidentialitystatus integer NOT NULL DEFAULT 0,
121
  confidentialityreason character varying(200),
122
  latitude double precision,
123
  longitude double precision,
124
  "... (truncated) ..." integer,
125
  accessioncode character varying(255),
126
  sublocationxposition double precision,
127
  sublocationyposition double precision,
128
  namedplace_id integer,
129
  CONSTRAINT location_pkey PRIMARY KEY (location_id ),
130
  CONSTRAINT location_namedplace_id FOREIGN KEY (namedplace_id)
131
      REFERENCES namedplace (namedplace_id) MATCH SIMPLE
132
      ON UPDATE CASCADE ON DELETE CASCADE,
133
  CONSTRAINT location_parent_id FOREIGN KEY (parent_id)
134
      REFERENCES location (location_id) MATCH SIMPLE
135
      ON UPDATE CASCADE ON DELETE CASCADE,
136
  CONSTRAINT location_reference_id FOREIGN KEY (reference_id)
137
      REFERENCES reference (reference_id) MATCH SIMPLE
138
      ON UPDATE CASCADE ON DELETE CASCADE,
139
  CONSTRAINT location_keys UNIQUE (reference_id , parent_id , authorlocationcode )
140
);
141

    
142
CREATE TABLE locationevent -- VegBank's observation table.
143
(
144
  locationevent_id serial NOT NULL,
145
  previous_id integer,
146
  location_id integer NOT NULL,
147
  project_id integer,
148
  authoreventcode character varying(30),
149
  "... (truncated) ..." integer,
150
  accessioncode character varying(255),
151
  CONSTRAINT locationevent_pkey PRIMARY KEY (locationevent_id ),
152
  CONSTRAINT locationevent_location_id FOREIGN KEY (location_id)
153
      REFERENCES location (location_id) MATCH SIMPLE
154
      ON UPDATE CASCADE ON DELETE CASCADE,
155
  CONSTRAINT locationevent_project_id FOREIGN KEY (project_id)
156
      REFERENCES project (project_id) MATCH SIMPLE
157
      ON UPDATE CASCADE ON DELETE CASCADE,
158
  CONSTRAINT locationevent_keys UNIQUE (location_id , project_id , authoreventcode )
159
);
160

    
161
CREATE TABLE taxonoccurrence -- VegBank's taxonobservation table.
162
(
163
  taxonoccurrence_id serial NOT NULL,
164
  locationevent_id integer NOT NULL,
165
  authorplantname character varying(255),
166
  reference_id integer,
167
  taxoninferencearea double precision,
168
  emb_taxonoccurrence integer,
169
  "... (truncated) ..." integer,
170
  accessioncode character varying(255),
171
  CONSTRAINT taxonoccurrence_pkey PRIMARY KEY (taxonoccurrence_id ),
172
  CONSTRAINT taxonoccurrence_locationevent_id FOREIGN KEY (locationevent_id)
173
      REFERENCES locationevent (locationevent_id) MATCH SIMPLE
174
      ON UPDATE CASCADE ON DELETE CASCADE,
175
  CONSTRAINT taxonoccurrence_reference_id FOREIGN KEY (reference_id)
176
      REFERENCES reference (reference_id) MATCH SIMPLE
177
      ON UPDATE CASCADE ON DELETE CASCADE
178
);
179

    
180
CREATE TABLE aggregateoccurrence -- VegBank's taxonimportance table.
181
(
182
  aggregateoccurrence_id serial NOT NULL,
183
  taxonoccurrence_id integer NOT NULL,
184
  taxonbin_id integer,
185
  cover double precision,
186
  basalarea double precision,
187
  biomass double precision,
188
  inferencearea double precision,
189
  stratumbase double precision,
190
  stratumheight double precision,
191
  emb_aggregateoccurrence integer,
192
  covercode character varying(10),
193
  count integer NOT NULL,
194
  accessioncode character varying(255),
195
  CONSTRAINT aggregateoccurrence_pkey PRIMARY KEY (aggregateoccurrence_id ),
196
  CONSTRAINT aggregateoccurrence_taxonbin_id FOREIGN KEY (taxonbin_id)
197
      REFERENCES taxonbin (taxonbin_id) MATCH SIMPLE
198
      ON UPDATE CASCADE ON DELETE CASCADE,
199
  CONSTRAINT aggregateoccurrence_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id)
200
      REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE
201
      ON UPDATE CASCADE ON DELETE CASCADE,
202
  CONSTRAINT aggregateoccurrence_keys UNIQUE (taxonoccurrence_id , taxonbin_id )
203
);
204

    
205
CREATE TABLE individualplant -- VegBank's stemcount table.
206
(
207
  individualplant_id serial NOT NULL,
208
  aggregateoccurrence_id integer NOT NULL,
209
  overallheight double precision,
210
  overallheightaccuracy double precision,
211
  emb_individualplant integer,
212
  authorplantcode character varying(20),
213
  accessioncode character varying(255),
214
  stemcount integer,
215
  CONSTRAINT individualplant_pkey PRIMARY KEY (individualplant_id ),
216
  CONSTRAINT individualplant_aggregateoccurrence_id FOREIGN KEY (aggregateoccurrence_id)
217
      REFERENCES aggregateoccurrence (aggregateoccurrence_id) MATCH SIMPLE
218
      ON UPDATE CASCADE ON DELETE CASCADE,
219
  CONSTRAINT individualplant_keys UNIQUE (aggregateoccurrence_id , authorplantcode )
220
);
221

    
222
CREATE TABLE stem -- VegBank's stemlocation table.
223
(
224
  stem_id serial NOT NULL,
225
  individualplant_id integer NOT NULL,
226
  authorstemcode character varying(20),
227
  xposition double precision,
228
  yposition double precision,
229
  health character varying(50),
230
  emb_stem integer,
231
  diameter double precision,
232
  height double precision,
233
  heightaccuracy double precision,
234
  age double precision,
235
  accessioncode character varying(255),
236
  diameteraccuracy double precision,
237
  CONSTRAINT stem_pkey PRIMARY KEY (stem_id ),
238
  CONSTRAINT stem_individualplant_id FOREIGN KEY (individualplant_id)
239
      REFERENCES individualplant (individualplant_id) MATCH SIMPLE
240
      ON UPDATE CASCADE ON DELETE CASCADE,
241
  CONSTRAINT stem_keys UNIQUE (individualplant_id , authorstemcode )
242
);
243

    
244
CREATE TABLE specimen -- A herbarium specimen. Contains Darwin Core specimen data.
245
(
246
  specimen_id serial NOT NULL,
247
  reference_id integer NOT NULL,
248
  collectioncode_dwc character varying(255),
249
  catalognumber_dwc character varying(255),
250
  collectiondate timestamp with time zone,
251
  collector_id integer,
252
  museum_id integer,
253
  sourceaccessionnumber character varying(100),
254
  accessioncode character varying(255),
255
  taxonoccurrence_id integer,
256
  collectornumber_dwc character varying(255),
257
  CONSTRAINT specimen_pkey PRIMARY KEY (specimen_id ),
258
  CONSTRAINT specimen_collector_id FOREIGN KEY (collector_id)
259
      REFERENCES party (party_id) MATCH SIMPLE
260
      ON UPDATE CASCADE ON DELETE CASCADE,
261
  CONSTRAINT specimen_museum_id FOREIGN KEY (museum_id)
262
      REFERENCES party (party_id) MATCH SIMPLE
263
      ON UPDATE CASCADE ON DELETE CASCADE,
264
  CONSTRAINT specimen_reference_id_fkey FOREIGN KEY (reference_id)
265
      REFERENCES reference (reference_id) MATCH SIMPLE
266
      ON UPDATE CASCADE ON DELETE CASCADE,
267
  CONSTRAINT specimen_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id)
268
      REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE
269
      ON UPDATE CASCADE ON DELETE CASCADE,
270
  CONSTRAINT specimen_keys UNIQUE (reference_id , collectioncode_dwc , catalognumber_dwc )
271
);
272

    
273
CREATE TABLE voucher
274
(
275
  voucher_id serial NOT NULL,
276
  taxonoccurrence_id integer NOT NULL,
277
  specimen_id integer NOT NULL,
278
  accessioncode character varying(255),
279
  CONSTRAINT voucher_pkey PRIMARY KEY (voucher_id ),
280
  CONSTRAINT voucher_specimen_id FOREIGN KEY (specimen_id)
281
      REFERENCES specimen (specimen_id) MATCH SIMPLE
282
      ON UPDATE CASCADE ON DELETE CASCADE,
283
  CONSTRAINT voucher_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id)
284
      REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE
285
      ON UPDATE CASCADE ON DELETE CASCADE,
286
  CONSTRAINT voucher_keys UNIQUE (taxonoccurrence_id , specimen_id )
287
);
288

    
289
CREATE TABLE taxondetermination -- VegBank's taxoninterpretation table.
290
(
291
  taxondetermination_id serial NOT NULL,
292
  taxonoccurrence_id integer NOT NULL,
293
  plantconcept_id integer NOT NULL,
294
  party_id integer,
295
  role_id integer NOT NULL,
296
  determinationtype character varying(30),
297
  reference_id integer,
298
  originaldetermination boolean NOT NULL DEFAULT false,
299
  currentdetermination boolean NOT NULL DEFAULT false,
300
  taxonfit character varying(50),
301
  taxonconfidence character varying(50),
302
  grouptype character varying(20),
303
  notes text,
304
  notespublic boolean,
305
  notesmgt boolean,
306
  revisions boolean,
307
  determinationdate timestamp with time zone NOT NULL,
308
  emb_taxondetermination integer,
309
  accessioncode character varying(255),
310
  CONSTRAINT taxondetermination_pkey PRIMARY KEY (taxondetermination_id ),
311
  CONSTRAINT taxondetermination_party_id FOREIGN KEY (party_id)
312
      REFERENCES party (party_id) MATCH SIMPLE
313
      ON UPDATE CASCADE ON DELETE CASCADE,
314
  CONSTRAINT taxondetermination_plantconcept_id FOREIGN KEY (plantconcept_id)
315
      REFERENCES plantconcept (plantconcept_id) MATCH SIMPLE
316
      ON UPDATE CASCADE ON DELETE CASCADE,
317
  CONSTRAINT taxondetermination_reference_id FOREIGN KEY (reference_id)
318
      REFERENCES reference (reference_id) MATCH SIMPLE
319
      ON UPDATE CASCADE ON DELETE CASCADE,
320
  CONSTRAINT taxondetermination_role_id FOREIGN KEY (role_id)
321
      REFERENCES aux_role (role_id) MATCH SIMPLE
322
      ON UPDATE CASCADE ON DELETE CASCADE,
323
  CONSTRAINT taxondetermination_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id)
324
      REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE
325
      ON UPDATE CASCADE ON DELETE CASCADE
326
);
327

    
328
CREATE TABLE stratum
329
(
330
  stratum_id serial NOT NULL,
331
  locationevent_id integer NOT NULL,
332
  stratumtype_id integer NOT NULL,
333
  stratummethod_id integer,
334
  stratumname character varying(30),
335
  stratumheight double precision,
336
  stratumbase double precision,
337
  stratumcover double precision,
338
  stratumdescription character varying(200),
339
  CONSTRAINT stratum_pkey PRIMARY KEY (stratum_id ),
340
  CONSTRAINT stratum_locationevent_id FOREIGN KEY (locationevent_id)
341
      REFERENCES locationevent (locationevent_id) MATCH SIMPLE
342
      ON UPDATE CASCADE ON DELETE CASCADE
343
);
344

    
345
CREATE TABLE sizeclass -- A range of size measurements used to aggregate organisms.
346
(
347
  sizeclass_id serial NOT NULL,
348
  mindiameter double precision,
349
  minheight double precision,
350
  maxdiameter double precision,
351
  maxheight double precision,
352
  accessioncode character varying(255),
353
  CONSTRAINT sizeclass_pkey PRIMARY KEY (sizeclass_id )
354
);
355

    
356
CREATE TABLE taxonbin -- Defines a set of aggregate measurements. Used by aggregateoccurrence to define how measurements were aggregated.
357
(
358
  taxonbin_id integer NOT NULL,
359
  label character varying(255) NOT NULL,
360
  stratum_id integer,
361
  sizeclass_id integer,
362
  coverindex_id integer,
363
  accessioncode character varying(255),
364
  count integer NOT NULL,
365
  CONSTRAINT taxonbin_pkey PRIMARY KEY (taxonbin_id ),
366
  CONSTRAINT taxonbin_sizeclass_id FOREIGN KEY (sizeclass_id)
367
      REFERENCES sizeclass (sizeclass_id) MATCH SIMPLE
368
      ON UPDATE CASCADE ON DELETE CASCADE
369
);
(8-8/12)