Project

General

Profile

« Previous | Next » 

Revision 12067

Added by Aaron Marcuse-Kubitza almost 11 years ago

validation/aggregating/specimens/qualitative_validations_specimens.sql: added CREATE VIEW prefixes using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#prepend-CREATE-VIEW

View differences:

trunk/validation/aggregating/specimens/qualitative_validations_specimens.sql
11 11 
-- 1. Count of total records (specimens) in source db
12 12 
-- Check: full join against equivalent query on BIEN3 db should return 1 row
13 13 
-- -------------------------------
14 
CREATE OR REPLACE VIEW specimens_1_count_of_total_records_specimens_in_source_db AS
14 15 

  		




  15
  16
  
    
-- -------------------------------


  		




  16
  17
  
    
-- 2. Count of unique (verbatim) non-null families


  		




  17
  18
  
    
-- Check: Full inner join to equivalent query on BIEN3 db should return 1 row


  		




  18
  19
  
    
-- -------------------------------


  		




  
  20
  
    
CREATE OR REPLACE VIEW specimens_2_count_of_unique_verbatim_non_null_families AS


  		




  19
  21
  
    

  		




  20
  22
  
    
-- -------------------------------


  		




  21
  23
  
    
-- 3. List of verbatim families


  		




  22
  24
  
    
-- Check: Full inner join to equivalent query on BIEN3 db should return same number of rows


  		




  23
  25
  
    
-- -------------------------------


  		




  
  26
  
    
CREATE OR REPLACE VIEW specimens_3_list_of_verbatim_families AS


  		




  24
  27
  
    

  		




  25
  28
  
    
-- -------------------------------


  		




  26
  29
  
    
-- 4. Count of unique (verbatim) non-null species, without author


  		




  27
  30
  
    
-- Check: Full inner join to equivalent query on BIEN3 db should return 1 row


  		




  28
  31
  
    
-- -------------------------------


  		




  
  32
  
    
CREATE OR REPLACE VIEW specimens_4_count_of_unique_verbatim_non_null_species_without_author AS


  		




  29
  33
  
    

  		




  30
  34
  
    
-- -------------------------------


  		




  31
  35
  
    
-- 5. List of verbatim species, excluding author


  		




  32
  36
  
    
-- Check: Full inner join to equivalent query on BIEN3 db should return 3335 rows


  		




  33
  37
  
    
-- -------------------------------


  		




  
  38
  
    
CREATE OR REPLACE VIEW specimens_5_list_of_verbatim_species_excluding_author AS


  		




  34
  39
  
    

  		




  35
  40
  
    
-- -------------------------------


  		




  36
  41
  
    
-- 6. Count of unique (verbatim) non-null subspecific taxa, without author


  		




  37
  42
  
    
-- Check: Full inner join to equivalent query on BIEN3 db should return 1 row


  		




  38
  43
  
    
-- -------------------------------


  		




  
  44
  
    
CREATE OR REPLACE VIEW specimens_6_count_of_unique_verbatim_non_null_subspecific_taxa_without_author AS


  		




  39
  45
  
    

  		




  40
  46
  
    
-- -------------------------------


  		




  41
  47
  
    
-- 7. List of verbatim subspecific taxa, without author


  		




  42
  48
  
    
-- Check: Full inner join to equivalent query on BIEN3 db should return 40145 rows


  		




  43
  49
  
    
-- -------------------------------


  		




  
  50
  
    
CREATE OR REPLACE VIEW specimens_7_list_of_verbatim_subspecific_taxa_without_author AS


  		




  44
  51
  
    

  		




  45
  52
  
    
-- -------------------------------


  		




  46
  53
  
    
-- 8. Count of unique (verbatim) taxa including author, for all taxa identified at least to genus


  		




  47
  54
  
    
-- Check: Full inner join to equivalent query on BIEN3 db should return 1 row


  		




  48
  55
  
    
-- -------------------------------


  		




  
  56
  
    
CREATE OR REPLACE VIEW specimens_8_count_of_unique_verbatim_taxa_including_author_for_all_taxa_identified_at_least_to_genus AS


  		




  49
  57
  
    

  		




  50
  58
  
    
-- -------------------------------


  		




  51
  59
  
    
-- 9. List of unique (verbatim) taxa including author, for all taxa identified at least to genus


  		




  52
  60
  
    
-- Check: Full inner join to equivalent query on BIEN3 db should return 45997 rows


  		




  53
  61
  
    
-- -------------------------------


  		




  
  62
  
    
CREATE OR REPLACE VIEW specimens_9_list_of_unique_verbatim_taxa_including_author_for_all_taxa_identified_at_least_to_genus AS


  		




  54
  63
  
    

  		




  55
  64
  
    
-- -------------------------------


  		




  56
  65
  
    
-- 10. Count number of records by institution


  		




  57
  66
  
    
-- Check: Full inner join to equivalent query on BIEN3 db should return 6 rows


  		




  58
  67
  
    
-- Note: Majority should be from 'NY'; these are standard herbarium acronyms


  		




  59
  68
  
    
-- -------------------------------


  		




  
  69
  
    
CREATE OR REPLACE VIEW specimens_10_count_number_of_records_by_institution AS


  		




  60
  70
  
    

  		




  61
  71
  
    
-- -------------------------------


  		




  62
  72
  
    
-- 11. List of three standard political divisions


  		




  ......




  64
  74
  
    
-- Note: character set issues may cause mis-matches. This query is a good way to reveal


  		




  65
  75
  
    
-- character set issues, either in source db or in BIEN


  		




  66
  76
  
    
-- -------------------------------


  		




  
  77
  
    
CREATE OR REPLACE VIEW specimens_11_list_of_three_standard_political_divisions AS


  		




  67
  78
  
    

  		




  68
  79
  
    
-- -------------------------------


  		




  69
  80
  
    
-- 12. Check distinct Collector names + collection numbers + collection dates, 


  		




  70
  81
  
    
-- plus total records


  		




  71
  82
  
    
-- Check: Full inner join to equivalent query on BIEN3 db should return 309396 rows


  		




  72
  83
  
    
-- -------------------------------


  		




  
  84
  
    
CREATE OR REPLACE VIEW specimens_12_check_distinct_collector_names_collection_numbers_collection_dates_ AS


  		




  73
  85
  
    

  		




  74
  86
  
    
-- -------------------------------


  		




  75
  87
  
    
-- 13. Count of all  verbatim Latitude and Longitude values, as well as all 


  		




  76
  88
  
    
-- latitude and longitude values that are decimals numbers


  		




  77
  89
  
    
-- Check: full join to equivalent query against BIEN3 should return 1 row


  		




  78
  90
  
    
-- -------------------------------


  		




  
  91
  
    
CREATE OR REPLACE VIEW specimens_13_count_of_all_verbatim_latitude_and_longitude_values_as_well_as_all_ AS


  		




  79
  92
  
    

  		




  80
  93
  
    
-- -------------------------------


  		




  81
  94
  
    
-- 14. Count of all verbatim Latitude and Longitude values that are not valid values


  		




  82
  95
  
    
-- of decimal latitude or decimal longitude


  		




  83
  96
  
    
-- Check: full join to equivalent query against BIEN3 should return 1 row


  		




  84
  97
  
    
-- -------------------------------


  		




  
  98
  
    
CREATE OR REPLACE VIEW specimens_14_count_of_all_verbatim_latitude_and_longitude_values_that_are_not_valid_values AS


  		




  85
  99
  
    

  		




  86
  100
  
    
-- -------------------------------


  		




  87
  101
  
    
-- 15. List distinct non-null locality descriptions


  		




  88
  102
  
    
-- Check: full join to equivalent query against BIEN3 should return 125592 records


  		




  89
  103
  
    
-- -------------------------------


  		




  
  104
  
    
CREATE OR REPLACE VIEW specimens_15_list_distinct_non_null_locality_descriptions AS


  		




  90
  105
  
    

  		




  91
  106
  
    
-- -------------------------------


  		




  92
  107
  
    
-- 16. List distinct non-null specimen descriptions


  		




  93
  108
  
    
-- Check: full join to equivalent query against BIEN3 should return 158460 records


  		




  94
  109
  
    
-- Note: specimens descriptions in nybg extract is in column PlantFungusDescription


  		




  95
  110
  
    
-- -------------------------------


  		




  
  111
  
    
CREATE OR REPLACE VIEW specimens_16_list_distinct_non_null_specimen_descriptions AS


  		












Also available in:  Unified diff