Revision 10746
Added by Aaron Marcuse-Kubitza over 11 years ago
| schema.sql | ||
|---|---|---|
| 118 | 118 |
*/ |
| 119 | 119 |
CREATE TABLE tnrs |
| 120 | 120 |
( |
| 121 |
"Time_submitted" timestamp with time zone NOT NULL DEFAULT now(),
|
|
| 121 |
batch text NOT NULL DEFAULT now(),
|
|
| 122 | 122 |
"Name_number" integer NOT NULL, |
| 123 | 123 |
"Name_submitted" text NOT NULL, |
| 124 | 124 |
"Overall_score" double precision, |
| ... | ... | |
| 159 | 159 |
"Max_score" double precision, |
| 160 | 160 |
"Is_homonym" boolean, |
| 161 | 161 |
"Is_plant" boolean, |
| 162 |
CONSTRAINT tnrs_pkey PRIMARY KEY ("Time_submitted" , "Name_number" ),
|
|
| 162 |
CONSTRAINT tnrs_pkey PRIMARY KEY (batch , "Name_number" ), |
|
| 163 |
CONSTRAINT tnrs_batch_fkey FOREIGN KEY (batch) |
|
| 164 |
REFERENCES batch (id) MATCH SIMPLE |
|
| 165 |
ON UPDATE CASCADE ON DELETE CASCADE, |
|
| 163 | 166 |
CONSTRAINT "tnrs_Name_submitted_key" UNIQUE ("Name_submitted" )
|
| 164 | 167 |
) |
| 165 | 168 |
WITH ( |
| ... | ... | |
| 239 | 242 |
|
| 240 | 243 |
CREATE OR REPLACE VIEW "MatchedTaxon" AS |
| 241 | 244 |
SELECT |
| 242 |
"Time_submitted" AS "*Name_matched.Time_submitted"
|
|
| 245 |
"batch" AS "*Name_matched.batch"
|
|
| 243 | 246 |
, "Name_submitted" AS "concatenatedScientificName" |
| 244 | 247 |
, "Name_matched" AS "matchedTaxonName" |
| 245 | 248 |
, "Name_matched_rank" AS "matchedTaxonRank" |
| ... | ... | |
| 281 | 284 |
|
| 282 | 285 |
CREATE OR REPLACE VIEW "AcceptedTaxon" AS |
| 283 | 286 |
SELECT |
| 284 |
"Time_submitted" AS "*Accepted_name.Time_submitted"
|
|
| 287 |
"batch" AS "*Accepted_name.batch"
|
|
| 285 | 288 |
, "Name_submitted" AS "acceptedScientificName" |
| 286 | 289 |
, "Genus_matched" AS "acceptedGenus" |
| 287 | 290 |
, "Specific_epithet_matched" AS "acceptedSpecificEpithet" |
Also available in: Unified diff
inputs/.TNRS/schema.sql: tnrs.Time_submitted: renamed to batch and added fkey to batch.id. this requires including the batch table in inputs/.TNRS/data.sql, so that the fkey is satisfied (batch entries are already added by bin/tnrs_db.