Project

General

Profile

« Previous | Next » 

Revision 11708

Added by Aaron Marcuse-Kubitza about 11 years ago

inputs/.TNRS/schema.sql, data.sql: updated for PostgreSQL 9.3

View differences:

schema.sql
3 3 
--
4 4 

  		




  5
  5
  
    
SET statement_timeout = 0;


  		




  
  6
  
    
SET lock_timeout = 0;


  		




  6
  7
  
    
SET client_encoding = 'UTF8';


  		




  7
  8
  
    
SET standard_conforming_strings = on;


  		




  8
  9
  
    
SET check_function_bodies = false;


  		




  ......




  211
  212
  
    
--


  		




  212
  213
  
    

  		




  213
  214
  
    
CREATE VIEW "MatchedTaxon" AS


  		




  214
  
  
    
    SELECT tnrs.batch AS "*Name_matched.batch", tnrs."Name_submitted" AS "concatenatedScientificName", tnrs."Name_matched" AS "matchedTaxonName", tnrs."Name_matched_rank" AS "matchedTaxonRank", tnrs."Name_score" AS "*Name_matched.Name_score", tnrs."Name_matched_author" AS "matchedScientificNameAuthorship", tnrs."Name_matched_url" AS "matchedScientificNameID", tnrs."Author_score" AS "*Name_matched.Author_score", tnrs."Family_score" AS "matchedFamilyConfidence_fraction", COALESCE(tnrs."Name_matched_accepted_family", tnrs."Accepted_name_family") AS "matchedFamily", tnrs."Genus_matched" AS "matchedGenus", tnrs."Genus_score" AS "matchedGenusConfidence_fraction", tnrs."Specific_epithet_matched" AS "matchedSpecificEpithet", tnrs."Specific_epithet_score" AS "matchedSpeciesConfidence_fraction", tnrs."Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet", tnrs."Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score", tnrs."Annotations" AS "identificationQualifier", tnrs."Unmatched_terms" AS "morphospeciesSuffix", tnrs."Taxonomic_status" AS "taxonomicStatus", tnrs."Accepted_name" AS "acceptedTaxonName", tnrs."Accepted_name_author" AS "acceptedScientificNameAuthorship", tnrs."Accepted_name_rank" AS "acceptedTaxonRank", tnrs."Accepted_name_url" AS "acceptedScientificNameID", tnrs."Accepted_name_species" AS "*Name_matched.Accepted_name_species", tnrs."Accepted_name_family" AS "acceptedFamily", tnrs."Selected" AS "*Name_matched.Selected", tnrs."Source" AS "*Name_matched.Source", tnrs."Warnings" AS "*Name_matched.Warnings", tnrs."Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid", tnrs.is_valid_match AS taxon_scrub__is_valid_match, tnrs.scrubbed_unique_taxon_name FROM tnrs;


  		




  
  215
  
    
 SELECT tnrs.batch AS "*Name_matched.batch", 


  		




  
  216
  
    
    tnrs."Name_submitted" AS "concatenatedScientificName", 


  		




  
  217
  
    
    tnrs."Name_matched" AS "matchedTaxonName", 


  		




  
  218
  
    
    tnrs."Name_matched_rank" AS "matchedTaxonRank", 


  		




  
  219
  
    
    tnrs."Name_score" AS "*Name_matched.Name_score", 


  		




  
  220
  
    
    tnrs."Name_matched_author" AS "matchedScientificNameAuthorship", 


  		




  
  221
  
    
    tnrs."Name_matched_url" AS "matchedScientificNameID", 


  		




  
  222
  
    
    tnrs."Author_score" AS "*Name_matched.Author_score", 


  		




  
  223
  
    
    tnrs."Family_score" AS "matchedFamilyConfidence_fraction", 


  		




  
  224
  
    
    COALESCE(tnrs."Name_matched_accepted_family", tnrs."Accepted_name_family") AS "matchedFamily", 


  		




  
  225
  
    
    tnrs."Genus_matched" AS "matchedGenus", 


  		




  
  226
  
    
    tnrs."Genus_score" AS "matchedGenusConfidence_fraction", 


  		




  
  227
  
    
    tnrs."Specific_epithet_matched" AS "matchedSpecificEpithet", 


  		




  
  228
  
    
    tnrs."Specific_epithet_score" AS "matchedSpeciesConfidence_fraction", 


  		




  
  229
  
    
    tnrs."Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet", 


  		




  
  230
  
    
    tnrs."Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score", 


  		




  
  231
  
    
    tnrs."Annotations" AS "identificationQualifier", 


  		




  
  232
  
    
    tnrs."Unmatched_terms" AS "morphospeciesSuffix", 


  		




  
  233
  
    
    tnrs."Taxonomic_status" AS "taxonomicStatus", 


  		




  
  234
  
    
    tnrs."Accepted_name" AS "acceptedTaxonName", 


  		




  
  235
  
    
    tnrs."Accepted_name_author" AS "acceptedScientificNameAuthorship", 


  		




  
  236
  
    
    tnrs."Accepted_name_rank" AS "acceptedTaxonRank", 


  		




  
  237
  
    
    tnrs."Accepted_name_url" AS "acceptedScientificNameID", 


  		




  
  238
  
    
    tnrs."Accepted_name_species" AS "*Name_matched.Accepted_name_species", 


  		




  
  239
  
    
    tnrs."Accepted_name_family" AS "acceptedFamily", 


  		




  
  240
  
    
    tnrs."Selected" AS "*Name_matched.Selected", 


  		




  
  241
  
    
    tnrs."Source" AS "*Name_matched.Source", 


  		




  
  242
  
    
    tnrs."Warnings" AS "*Name_matched.Warnings", 


  		




  
  243
  
    
    tnrs."Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid", 


  		




  
  244
  
    
    tnrs.is_valid_match AS taxon_scrub__is_valid_match, 


  		




  
  245
  
    
    tnrs.scrubbed_unique_taxon_name


  		




  
  246
  
    
   FROM tnrs;


  		




  215
  247
  
    

  		




  216
  248
  
    

  		




  217
  249
  
    
--


  		




  ......




  219
  251
  
    
--


  		




  220
  252
  
    

  		




  221
  253
  
    
CREATE VIEW "ValidMatchedTaxon" AS


  		




  222
  
  
    
    SELECT "MatchedTaxon"."*Name_matched.batch", "MatchedTaxon"."concatenatedScientificName", "MatchedTaxon"."matchedTaxonName", "MatchedTaxon"."matchedTaxonRank", "MatchedTaxon"."*Name_matched.Name_score", "MatchedTaxon"."matchedScientificNameAuthorship", "MatchedTaxon"."matchedScientificNameID", "MatchedTaxon"."*Name_matched.Author_score", "MatchedTaxon"."matchedFamilyConfidence_fraction", "MatchedTaxon"."matchedFamily", "MatchedTaxon"."matchedGenus", "MatchedTaxon"."matchedGenusConfidence_fraction", "MatchedTaxon"."matchedSpecificEpithet", "MatchedTaxon"."matchedSpeciesConfidence_fraction", "MatchedTaxon"."matchedInfraspecificEpithet", "MatchedTaxon"."*Name_matched.Infraspecific_epithet_score", "MatchedTaxon"."identificationQualifier", "MatchedTaxon"."morphospeciesSuffix", "MatchedTaxon"."taxonomicStatus", "MatchedTaxon"."acceptedTaxonName", "MatchedTaxon"."acceptedScientificNameAuthorship", "MatchedTaxon"."acceptedTaxonRank", "MatchedTaxon"."acceptedScientificNameID", "MatchedTaxon"."*Name_matched.Accepted_name_species", "MatchedTaxon"."acceptedFamily", "MatchedTaxon"."*Name_matched.Selected", "MatchedTaxon"."*Name_matched.Source", "MatchedTaxon"."*Name_matched.Warnings", "MatchedTaxon"."*Name_matched.Accepted_name_lsid", "MatchedTaxon".taxon_scrub__is_valid_match, "MatchedTaxon".scrubbed_unique_taxon_name FROM "MatchedTaxon" WHERE "MatchedTaxon".taxon_scrub__is_valid_match;


  		




  
  254
  
    
 SELECT "MatchedTaxon"."*Name_matched.batch", 


  		




  
  255
  
    
    "MatchedTaxon"."concatenatedScientificName", 


  		




  
  256
  
    
    "MatchedTaxon"."matchedTaxonName", 


  		




  
  257
  
    
    "MatchedTaxon"."matchedTaxonRank", 


  		




  
  258
  
    
    "MatchedTaxon"."*Name_matched.Name_score", 


  		




  
  259
  
    
    "MatchedTaxon"."matchedScientificNameAuthorship", 


  		




  
  260
  
    
    "MatchedTaxon"."matchedScientificNameID", 


  		




  
  261
  
    
    "MatchedTaxon"."*Name_matched.Author_score", 


  		




  
  262
  
    
    "MatchedTaxon"."matchedFamilyConfidence_fraction", 


  		




  
  263
  
    
    "MatchedTaxon"."matchedFamily", 


  		




  
  264
  
    
    "MatchedTaxon"."matchedGenus", 


  		




  
  265
  
    
    "MatchedTaxon"."matchedGenusConfidence_fraction", 


  		




  
  266
  
    
    "MatchedTaxon"."matchedSpecificEpithet", 


  		




  
  267
  
    
    "MatchedTaxon"."matchedSpeciesConfidence_fraction", 


  		




  
  268
  
    
    "MatchedTaxon"."matchedInfraspecificEpithet", 


  		




  
  269
  
    
    "MatchedTaxon"."*Name_matched.Infraspecific_epithet_score", 


  		




  
  270
  
    
    "MatchedTaxon"."identificationQualifier", 


  		




  
  271
  
    
    "MatchedTaxon"."morphospeciesSuffix", 


  		




  
  272
  
    
    "MatchedTaxon"."taxonomicStatus", 


  		




  
  273
  
    
    "MatchedTaxon"."acceptedTaxonName", 


  		




  
  274
  
    
    "MatchedTaxon"."acceptedScientificNameAuthorship", 


  		




  
  275
  
    
    "MatchedTaxon"."acceptedTaxonRank", 


  		




  
  276
  
    
    "MatchedTaxon"."acceptedScientificNameID", 


  		




  
  277
  
    
    "MatchedTaxon"."*Name_matched.Accepted_name_species", 


  		




  
  278
  
    
    "MatchedTaxon"."acceptedFamily", 


  		




  
  279
  
    
    "MatchedTaxon"."*Name_matched.Selected", 


  		




  
  280
  
    
    "MatchedTaxon"."*Name_matched.Source", 


  		




  
  281
  
    
    "MatchedTaxon"."*Name_matched.Warnings", 


  		




  
  282
  
    
    "MatchedTaxon"."*Name_matched.Accepted_name_lsid", 


  		




  
  283
  
    
    "MatchedTaxon".taxon_scrub__is_valid_match, 


  		




  
  284
  
    
    "MatchedTaxon".scrubbed_unique_taxon_name


  		




  
  285
  
    
   FROM "MatchedTaxon"


  		




  
  286
  
    
  WHERE "MatchedTaxon".taxon_scrub__is_valid_match;


  		




  223
  287
  
    

  		




  224
  288
  
    

  		




  225
  289
  
    
--


  		




  ......




  315
  379
  
    
--


  		




  316
  380
  
    

  		




  317
  381
  
    
CREATE VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" AS


  		




  318
  
  
    
    SELECT tnrs."Name_submitted" AS scrubbed_unique_taxon_name, tnrs."Name_matched_rank" AS scrubbed_taxon_rank, tnrs."Name_matched_accepted_family" AS scrubbed_family, tnrs."Genus_matched" AS scrubbed_genus, tnrs."Specific_epithet_matched" AS scrubbed_specific_epithet, tnrs."Infraspecific_rank" AS scrubbed_infraspecific_rank, tnrs."Infraspecific_epithet_matched" AS scrubbed_infraspecific_epithet, tnrs."Name_matched_author" AS scrubbed_author, tnrs."Name_matched" AS scrubbed_taxon_name_no_author FROM tnrs;


  		




  
  382
  
    
 SELECT tnrs."Name_submitted" AS scrubbed_unique_taxon_name, 


  		




  
  383
  
    
    tnrs."Name_matched_rank" AS scrubbed_taxon_rank, 


  		




  
  384
  
    
    tnrs."Name_matched_accepted_family" AS scrubbed_family, 


  		




  
  385
  
    
    tnrs."Genus_matched" AS scrubbed_genus, 


  		




  
  386
  
    
    tnrs."Specific_epithet_matched" AS scrubbed_specific_epithet, 


  		




  
  387
  
    
    tnrs."Infraspecific_rank" AS scrubbed_infraspecific_rank, 


  		




  
  388
  
    
    tnrs."Infraspecific_epithet_matched" AS scrubbed_infraspecific_epithet, 


  		




  
  389
  
    
    tnrs."Name_matched_author" AS scrubbed_author, 


  		




  
  390
  
    
    tnrs."Name_matched" AS scrubbed_taxon_name_no_author


  		




  
  391
  
    
   FROM tnrs;


  		




  319
  392
  
    

  		




  320
  393
  
    

  		




  321
  394
  
    
--


  		




  ......




  323
  396
  
    
--


  		




  324
  397
  
    

  		




  325
  398
  
    
CREATE VIEW taxon_scrub AS


  		




  326
  
  
    
    SELECT "ValidMatchedTaxon".scrubbed_unique_taxon_name, "ValidMatchedTaxon"."*Name_matched.batch", "ValidMatchedTaxon"."concatenatedScientificName", "ValidMatchedTaxon"."matchedTaxonName", "ValidMatchedTaxon"."matchedTaxonRank", "ValidMatchedTaxon"."*Name_matched.Name_score", "ValidMatchedTaxon"."matchedScientificNameAuthorship", "ValidMatchedTaxon"."matchedScientificNameID", "ValidMatchedTaxon"."*Name_matched.Author_score", "ValidMatchedTaxon"."matchedFamilyConfidence_fraction", "ValidMatchedTaxon"."matchedFamily", "ValidMatchedTaxon"."matchedGenus", "ValidMatchedTaxon"."matchedGenusConfidence_fraction", "ValidMatchedTaxon"."matchedSpecificEpithet", "ValidMatchedTaxon"."matchedSpeciesConfidence_fraction", "ValidMatchedTaxon"."matchedInfraspecificEpithet", "ValidMatchedTaxon"."*Name_matched.Infraspecific_epithet_score", "ValidMatchedTaxon"."identificationQualifier", "ValidMatchedTaxon"."morphospeciesSuffix", "ValidMatchedTaxon"."taxonomicStatus", "ValidMatchedTaxon"."acceptedTaxonName", "ValidMatchedTaxon"."acceptedScientificNameAuthorship", "ValidMatchedTaxon"."acceptedTaxonRank", "ValidMatchedTaxon"."acceptedScientificNameID", "ValidMatchedTaxon"."*Name_matched.Accepted_name_species", "ValidMatchedTaxon"."acceptedFamily", "ValidMatchedTaxon"."*Name_matched.Selected", "ValidMatchedTaxon"."*Name_matched.Source", "ValidMatchedTaxon"."*Name_matched.Warnings", "ValidMatchedTaxon"."*Name_matched.Accepted_name_lsid", "ValidMatchedTaxon".taxon_scrub__is_valid_match, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_genus, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_specific_epithet, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_rank, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_epithet, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_author, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author FROM ("ValidMatchedTaxon" NATURAL LEFT JOIN "taxon_scrub.scrubbed_unique_taxon_name.*");


  		




  
  399
  
    
 SELECT "ValidMatchedTaxon".scrubbed_unique_taxon_name, 


  		




  
  400
  
    
    "ValidMatchedTaxon"."*Name_matched.batch", 


  		




  
  401
  
    
    "ValidMatchedTaxon"."concatenatedScientificName", 


  		




  
  402
  
    
    "ValidMatchedTaxon"."matchedTaxonName", 


  		




  
  403
  
    
    "ValidMatchedTaxon"."matchedTaxonRank", 


  		




  
  404
  
    
    "ValidMatchedTaxon"."*Name_matched.Name_score", 


  		




  
  405
  
    
    "ValidMatchedTaxon"."matchedScientificNameAuthorship", 


  		




  
  406
  
    
    "ValidMatchedTaxon"."matchedScientificNameID", 


  		




  
  407
  
    
    "ValidMatchedTaxon"."*Name_matched.Author_score", 


  		




  
  408
  
    
    "ValidMatchedTaxon"."matchedFamilyConfidence_fraction", 


  		




  
  409
  
    
    "ValidMatchedTaxon"."matchedFamily", 


  		




  
  410
  
    
    "ValidMatchedTaxon"."matchedGenus", 


  		




  
  411
  
    
    "ValidMatchedTaxon"."matchedGenusConfidence_fraction", 


  		




  
  412
  
    
    "ValidMatchedTaxon"."matchedSpecificEpithet", 


  		




  
  413
  
    
    "ValidMatchedTaxon"."matchedSpeciesConfidence_fraction", 


  		




  
  414
  
    
    "ValidMatchedTaxon"."matchedInfraspecificEpithet", 


  		




  
  415
  
    
    "ValidMatchedTaxon"."*Name_matched.Infraspecific_epithet_score", 


  		




  
  416
  
    
    "ValidMatchedTaxon"."identificationQualifier", 


  		




  
  417
  
    
    "ValidMatchedTaxon"."morphospeciesSuffix", 


  		




  
  418
  
    
    "ValidMatchedTaxon"."taxonomicStatus", 


  		




  
  419
  
    
    "ValidMatchedTaxon"."acceptedTaxonName", 


  		




  
  420
  
    
    "ValidMatchedTaxon"."acceptedScientificNameAuthorship", 


  		




  
  421
  
    
    "ValidMatchedTaxon"."acceptedTaxonRank", 


  		




  
  422
  
    
    "ValidMatchedTaxon"."acceptedScientificNameID", 


  		




  
  423
  
    
    "ValidMatchedTaxon"."*Name_matched.Accepted_name_species", 


  		




  
  424
  
    
    "ValidMatchedTaxon"."acceptedFamily", 


  		




  
  425
  
    
    "ValidMatchedTaxon"."*Name_matched.Selected", 


  		




  
  426
  
    
    "ValidMatchedTaxon"."*Name_matched.Source", 


  		




  
  427
  
    
    "ValidMatchedTaxon"."*Name_matched.Warnings", 


  		




  
  428
  
    
    "ValidMatchedTaxon"."*Name_matched.Accepted_name_lsid", 


  		




  
  429
  
    
    "ValidMatchedTaxon".taxon_scrub__is_valid_match, 


  		




  
  430
  
    
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank, 


  		




  
  431
  
    
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family, 


  		




  
  432
  
    
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_genus, 


  		




  
  433
  
    
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_specific_epithet, 


  		




  
  434
  
    
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_rank, 


  		




  
  435
  
    
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_epithet, 


  		




  
  436
  
    
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_author, 


  		




  
  437
  
    
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author


  		




  
  438
  
    
   FROM ("ValidMatchedTaxon"


  		




  
  439
  
    
   LEFT JOIN "taxon_scrub.scrubbed_unique_taxon_name.*" USING (scrubbed_unique_taxon_name));


  		




  327
  440
  
    

  		




  328
  441
  
    

  		




  329
  442
  
    
--


  		












Also available in:  Unified diff