Project

General

Profile

« Previous | Next » 

Revision 7132

Added by Aaron Marcuse-Kubitza almost 12 years ago

inputs/.TNRS/: Changed tnrs+accepted to a view (defined in schema.sql) so accepted names would automatically be populated as they are parsed by TNRS, rather than needing to run `make inputs/.TNRS/tnrs+accepted/reinstall` to populate them

View differences:

inputs/.TNRS/tnrs+accepted/create.sql
1 
SELECT
2 
tnrs.*
3 
, tnrs_accepted."Time_submitted"                  AS "Accepted_name.Time_submitted"
4 
, tnrs_accepted."Name_number"                     AS "Accepted_name.Name_number"
5 
, tnrs_accepted."Name_submitted"                  AS "Accepted_name.Name_submitted"
6 
, tnrs_accepted."Overall_score"                   AS "Accepted_name.Overall_score"
7 
, tnrs_accepted."Name_matched"                    AS "Accepted_name.Name_matched"
8 
, tnrs_accepted."Name_matched_rank"               AS "Accepted_name.Name_matched_rank"
9 
, tnrs_accepted."Name_score"                      AS "Accepted_name.Name_score"
10 
, tnrs_accepted."Name_matched_author"             AS "Accepted_name.Name_matched_author"
11 
, tnrs_accepted."Name_matched_url"                AS "Accepted_name.Name_matched_url"
12 
, tnrs_accepted."Author_matched"                  AS "Accepted_name.Author_matched"
13 
, tnrs_accepted."Author_score"                    AS "Accepted_name.Author_score"
14 
, tnrs_accepted."Family_matched"                  AS "Accepted_name.Family_matched"
15 
, tnrs_accepted."Family_score"                    AS "Accepted_name.Family_score"
16 
, tnrs_accepted."Name_matched_accepted_family"    AS "Accepted_name.Name_matched_accepted_family"
17 
, tnrs_accepted."Genus_matched"                   AS "Accepted_name.Genus_matched"
18 
, tnrs_accepted."Genus_score"                     AS "Accepted_name.Genus_score"
19 
, tnrs_accepted."Specific_epithet_matched"        AS "Accepted_name.Specific_epithet_matched"
20 
, tnrs_accepted."Specific_epithet_score"          AS "Accepted_name.Specific_epithet_score"
21 
, tnrs_accepted."Infraspecific_rank"              AS "Accepted_name.Infraspecific_rank"
22 
, tnrs_accepted."Infraspecific_epithet_matched"   AS "Accepted_name.Infraspecific_epithet_matched"
23 
, tnrs_accepted."Infraspecific_epithet_score"     AS "Accepted_name.Infraspecific_epithet_score"
24 
, tnrs_accepted."Infraspecific_rank_2"            AS "Accepted_name.Infraspecific_rank_2"
25 
, tnrs_accepted."Infraspecific_epithet_2_matched" AS "Accepted_name.Infraspecific_epithet_2_matched"
26 
, tnrs_accepted."Infraspecific_epithet_2_score"   AS "Accepted_name.Infraspecific_epithet_2_score"
27 
, tnrs_accepted."Annotations"                     AS "Accepted_name.Annotations"
28 
, tnrs_accepted."Unmatched_terms"                 AS "Accepted_name.Unmatched_terms"
29 
, tnrs_accepted."Taxonomic_status"                AS "Accepted_name.Taxonomic_status"
30 
, tnrs_accepted."Accepted_name"                   AS "Accepted_name.Accepted_name"
31 
, tnrs_accepted."Accepted_name_author"            AS "Accepted_name.Accepted_name_author"
32 
, tnrs_accepted."Accepted_name_rank"              AS "Accepted_name.Accepted_name_rank"
33 
, tnrs_accepted."Accepted_name_url"               AS "Accepted_name.Accepted_name_url"
34 
, tnrs_accepted."Accepted_name_species"           AS "Accepted_name.Accepted_name_species"
35 
, tnrs_accepted."Accepted_name_family"            AS "Accepted_name.Accepted_name_family"
36 
, tnrs_accepted."Selected"                        AS "Accepted_name.Selected"
37 
, tnrs_accepted."Source"                          AS "Accepted_name.Source"
38 
, tnrs_accepted."Warnings"                        AS "Accepted_name.Warnings"
39 
, tnrs_accepted."Accepted_name_lsid"              AS "Accepted_name.Accepted_name_lsid"
40 
FROM tnrs
41 
LEFT JOIN tnrs tnrs_accepted ON
42 
tnrs_accepted."Name_submitted" = NULLIF(array_to_string(ARRAY[
43 
    NULLIF(tnrs."Accepted_name_family", 'Unknown')
44 
    , tnrs."Accepted_name"
45 
    , tnrs."Accepted_name_author"
46 
], ' '), '')
47 
ORDER BY "Name_submitted"
48 
;
49 

  		




  50
  
  
    
ALTER TABLE :table ADD PRIMARY KEY ("Name_submitted");


  		












  

    
      inputs/.TNRS/tnrs+accepted/header.csv
    
  





  1
  
  
    
Time_submitted,Name_number,Name_submitted,Overall_score,Name_matched,Name_matched_rank,Name_score,Name_matched_author,Name_matched_url,Author_matched,Author_score,Family_matched,Family_score,Name_matched_accepted_family,Genus_matched,Genus_score,Specific_epithet_matched,Specific_epithet_score,Infraspecific_rank,Infraspecific_epithet_matched,Infraspecific_epithet_score,Infraspecific_rank_2,Infraspecific_epithet_2_matched,Infraspecific_epithet_2_score,Annotations,Unmatched_terms,Taxonomic_status,Accepted_name,Accepted_name_author,Accepted_name_rank,Accepted_name_url,Accepted_name_species,Accepted_name_family,Selected,Source,Warnings,Accepted_name_lsid,Accepted_name.Time_submitted,Accepted_name.Name_number,Accepted_name.Name_submitted,Accepted_name.Overall_score,Accepted_name.Name_matched,Accepted_name.Name_matched_rank,Accepted_name.Name_score,Accepted_name.Name_matched_author,Accepted_name.Name_matched_url,Accepted_name.Author_matched,Accepted_name.Author_score,Accepted_name.Family_matched,Accepted_name.Family_score,Accepted_name.Name_matched_accepted_family,Accepted_name.Genus_matched,Accepted_name.Genus_score,Accepted_name.Specific_epithet_matched,Accepted_name.Specific_epithet_score,Accepted_name.Infraspecific_rank,Accepted_name.Infraspecific_epithet_matched,Accepted_name.Infraspecific_epithet_score,Accepted_name.Infraspecific_rank_2,Accepted_name.Infraspecific_epithet_2_matched,Accepted_name.Infraspecific_epithet_2_score,Accepted_name.Annotations,Accepted_name.Unmatched_terms,Accepted_name.Taxonomic_status,Accepted_name.Accepted_name,Accepted_name.Accepted_name_author,Accepted_name.Accepted_name_rank,Accepted_name.Accepted_name_url,Accepted_name.Accepted_name_species,Accepted_name.Accepted_name_family,Accepted_name.Selected,Accepted_name.Source,Accepted_name.Warnings,Accepted_name.Accepted_name_lsid


  		












  

    
      inputs/.TNRS/import_order.txt
    
  





  1
  1
  
    
Source


  		




  2
  
  
    
tnrs+accepted


  		




  3
  2
  
    
tnrs_accepted


  		




  4
  3
  
    
tnrs_other


  		




  5
  4
  
    
public.unscrubbed_taxondetermination_view


  		












  

    
      inputs/.TNRS/schema.sql
    
  





  50
  50
  
    
WITH (


  		




  51
  51
  
    
  OIDS=FALSE


  		




  52
  52
  
    
);


  		




  
  53
  
    

  		




  
  54
  
    
CREATE OR REPLACE VIEW "tnrs+accepted" AS


  		




  
  55
  
    
SELECT


  		




  
  56
  
    
tnrs.*


  		




  
  57
  
    
, tnrs_accepted."Time_submitted"                  AS "Accepted_name.Time_submitted"


  		




  
  58
  
    
, tnrs_accepted."Name_number"                     AS "Accepted_name.Name_number"


  		




  
  59
  
    
, tnrs_accepted."Name_submitted"                  AS "Accepted_name.Name_submitted"


  		




  
  60
  
    
, tnrs_accepted."Overall_score"                   AS "Accepted_name.Overall_score"


  		




  
  61
  
    
, tnrs_accepted."Name_matched"                    AS "Accepted_name.Name_matched"


  		




  
  62
  
    
, tnrs_accepted."Name_matched_rank"               AS "Accepted_name.Name_matched_rank"


  		




  
  63
  
    
, tnrs_accepted."Name_score"                      AS "Accepted_name.Name_score"


  		




  
  64
  
    
, tnrs_accepted."Name_matched_author"             AS "Accepted_name.Name_matched_author"


  		




  
  65
  
    
, tnrs_accepted."Name_matched_url"                AS "Accepted_name.Name_matched_url"


  		




  
  66
  
    
, tnrs_accepted."Author_matched"                  AS "Accepted_name.Author_matched"


  		




  
  67
  
    
, tnrs_accepted."Author_score"                    AS "Accepted_name.Author_score"


  		




  
  68
  
    
, tnrs_accepted."Family_matched"                  AS "Accepted_name.Family_matched"


  		




  
  69
  
    
, tnrs_accepted."Family_score"                    AS "Accepted_name.Family_score"


  		




  
  70
  
    
, tnrs_accepted."Name_matched_accepted_family"    AS "Accepted_name.Name_matched_accepted_family"


  		




  
  71
  
    
, tnrs_accepted."Genus_matched"                   AS "Accepted_name.Genus_matched"


  		




  
  72
  
    
, tnrs_accepted."Genus_score"                     AS "Accepted_name.Genus_score"


  		




  
  73
  
    
, tnrs_accepted."Specific_epithet_matched"        AS "Accepted_name.Specific_epithet_matched"


  		




  
  74
  
    
, tnrs_accepted."Specific_epithet_score"          AS "Accepted_name.Specific_epithet_score"


  		




  
  75
  
    
, tnrs_accepted."Infraspecific_rank"              AS "Accepted_name.Infraspecific_rank"


  		




  
  76
  
    
, tnrs_accepted."Infraspecific_epithet_matched"   AS "Accepted_name.Infraspecific_epithet_matched"


  		




  
  77
  
    
, tnrs_accepted."Infraspecific_epithet_score"     AS "Accepted_name.Infraspecific_epithet_score"


  		




  
  78
  
    
, tnrs_accepted."Infraspecific_rank_2"            AS "Accepted_name.Infraspecific_rank_2"


  		




  
  79
  
    
, tnrs_accepted."Infraspecific_epithet_2_matched" AS "Accepted_name.Infraspecific_epithet_2_matched"


  		




  
  80
  
    
, tnrs_accepted."Infraspecific_epithet_2_score"   AS "Accepted_name.Infraspecific_epithet_2_score"


  		




  
  81
  
    
, tnrs_accepted."Annotations"                     AS "Accepted_name.Annotations"


  		




  
  82
  
    
, tnrs_accepted."Unmatched_terms"                 AS "Accepted_name.Unmatched_terms"


  		




  
  83
  
    
, tnrs_accepted."Taxonomic_status"                AS "Accepted_name.Taxonomic_status"


  		




  
  84
  
    
, tnrs_accepted."Accepted_name"                   AS "Accepted_name.Accepted_name"


  		




  
  85
  
    
, tnrs_accepted."Accepted_name_author"            AS "Accepted_name.Accepted_name_author"


  		




  
  86
  
    
, tnrs_accepted."Accepted_name_rank"              AS "Accepted_name.Accepted_name_rank"


  		




  
  87
  
    
, tnrs_accepted."Accepted_name_url"               AS "Accepted_name.Accepted_name_url"


  		




  
  88
  
    
, tnrs_accepted."Accepted_name_species"           AS "Accepted_name.Accepted_name_species"


  		




  
  89
  
    
, tnrs_accepted."Accepted_name_family"            AS "Accepted_name.Accepted_name_family"


  		




  
  90
  
    
, tnrs_accepted."Selected"                        AS "Accepted_name.Selected"


  		




  
  91
  
    
, tnrs_accepted."Source"                          AS "Accepted_name.Source"


  		




  
  92
  
    
, tnrs_accepted."Warnings"                        AS "Accepted_name.Warnings"


  		




  
  93
  
    
, tnrs_accepted."Accepted_name_lsid"              AS "Accepted_name.Accepted_name_lsid"


  		




  
  94
  
    
FROM tnrs


  		




  
  95
  
    
LEFT JOIN tnrs tnrs_accepted ON


  		




  
  96
  
    
tnrs_accepted."Name_submitted" = NULLIF(array_to_string(ARRAY[


  		




  
  97
  
    
    NULLIF(tnrs."Accepted_name_family", 'Unknown')


  		




  
  98
  
    
    , tnrs."Accepted_name"


  		




  
  99
  
    
    , tnrs."Accepted_name_author"


  		




  
  100
  
    
], ' '), '')


  		




  
  101
  
    
;


  		












  

    
      bin/import_all
    
  





  16
  16
  
    
    local dump_opts=${dump_opts---exclude-schema=public} public_import=1


  		




  17
  17
  
    
    export version dump_opts public_import


  		




  18
  18
  
    
    


  		




  19
  
  
    
    make inputs/.TNRS/tnrs+accepted/reinstall


  		




  20
  19
  
    
    make inputs/.TNRS/tnrs_accepted/reinstall


  		




  21
  20
  
    
    make inputs/.TNRS/tnrs_other/reinstall


  		




  22
  21
  
    
    


  		












Also available in:  Unified diff