Project

General

Profile

« Previous | Next » 

Revision 10746

Added by Aaron Marcuse-Kubitza over 11 years ago

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.

View differences:

inputs/.TNRS/data.sql
1 
COPY tnrs ("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_scientific_name", "Max_score", "Is_homonym", "Is_plant") FROM stdin;
1 
COPY batch (id, id_by_time, time_submitted, client_version) FROM stdin;
2 
2013-06-20 07:56:01.42646-07	2013-06-20 07:56:01.42646-07	2013-06-20 07:56:01.42646-07	\N
3 
2013-06-20 07:56:03.390156-07	2013-06-20 07:56:03.390156-07	2013-06-20 07:56:03.390156-07	\N
4 
2013-06-20 07:55:58.532661-07	2013-06-20 07:55:58.532661-07	2013-06-20 07:55:58.532661-07	\N
5 
\.
6 

  		




  
  7
  
    
COPY tnrs (batch, "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_scientific_name", "Max_score", "Is_homonym", "Is_plant") FROM stdin;


  		




  2
  8
  
    
2013-06-20 07:55:58.532661-07	0	Fam_indet. Boyle#6501	0	No suitable matches found.	\N	0	\N	\N	\N	0	\N	0	\N	\N	0	\N	0	\N	\N	0	\N	\N	0	\N	\N	\N	\N	\N	\N	\N	\N	\N	true	\N	 	\N	\N	0	f	\N


  		




  3
  9
  
    
2013-06-20 07:55:58.532661-07	1	Poa annua var. eriolepis	1	Poa annua var. eriolepis	variety	1	E. Desv.	http://www.tropicos.org/Name/50119145	\N	\N	\N	\N	Poaceae	Poa	1	annua	1	var.	eriolepis	1	\N	\N	\N	\N	\N	Synonym	Poa annua	L.	species	http://www.tropicos.org/Name/25509881	Poa annua	Poaceae	true	tropicos	 	\N	Poaceae Poa annua L.	1	f	t


  		




  4
  10
  
    
2013-06-20 07:55:58.532661-07	2	Poa annua	1	Poa annua	species	1	L.	http://www.tropicos.org/Name/25509881	\N	\N	\N	\N	Poaceae	Poa	1	annua	1	\N	\N	\N	\N	\N	\N	\N	\N	Accepted	Poa annua	L.	species	http://www.tropicos.org/Name/25509881	Poa annua	Poaceae	true	tropicos	 	\N	Poaceae Poa annua L.	1	f	t


  		












  

    
      inputs/.TNRS/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