/ - Diff - BIEN 3 - NCEAS Projects

« Previous | Next »

Revision 12162

Added by Aaron Marcuse-Kubitza almost 11 years ago

validation/aggregating/*/*.sql: use current_schema instead of a hardcoded datasource name or psql variable

     SET search_path TO public_validation_plots, public;
     -- datasource
     \set ds '\'SALVIAS\''
     -- ------------------
     -- 1. Count of projects
     -- Check: identical count in source db
-...
     SELECT COUNT(*) AS projects
     FROM project p JOIN source s
     ON p.source_id=s.source_id
     WHERE s.shortname=:ds;
     WHERE s.shortname=current_schema;
     -- ------------------
     -- 2. List of project names
-...
     SELECT p.projectname
     FROM project p JOIN source s
     ON p.source_id=s.source_id
     WHERE s.shortname=:ds;
     WHERE s.shortname=current_schema;
     -- ------------------
     -- 3. Count of all plots in this source
-...
     ON p.project_id=le.project_id
     JOIN source s
     ON p.source_id=s.source_id
     WHERE s.shortname=:ds;
     WHERE s.shortname=current_schema;
     -- ------------------
     -- 4. Count of plots in each project in this source
-...
     ON p.project_id=le.project_id
     JOIN source s
     ON p.source_id=s.source_id
     WHERE s.shortname=:ds
     WHERE s.shortname=current_schema
     GROUP BY p.projectname;
     -- ------------------
-...
     ON p.project_id=le.project_id
     JOIN source s
     ON p.source_id=s.source_id
     WHERE s.shortname=:ds;
     WHERE s.shortname=current_schema;
     -- ------------------
     -- 6. List of plots with stem measurements
-...
     SELECT authorlocationcode
     FROM top_plot
     WHERE source_id = source_by_shortname(:ds)
     WHERE source_id = source_by_shortname(current_schema)
     AND EXISTS(
     	SELECT NULL
     	FROM location
-...
     join location l on le.location_id=l.location_id
     join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id
     join aggregateoccurrence ao on o.taxonoccurrence_id=ao.taxonoccurrence_id
     where s.shortname=:ds
     where s.shortname=current_schema
     group by p.projectname, l.authorlocationcode
     order by p.projectname, l.authorlocationcode;
-...
     join aggregateoccurrence ao on o.taxonoccurrence_id=ao.taxonoccurrence_id
     join plantobservation po on po.aggregateoccurrence_id=ao.aggregateoccurrence_id
     join stemobservation so on so.plantobservation_id=po.plantobservation_id
     where s.shortname=:ds
     where s.shortname=current_schema
     group by p.projectname, l.authorlocationcode
     order by p.projectname, l.authorlocationcode;
-...
     join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id
     join taxondetermination td on td.taxonoccurrence_id=o.taxonoccurrence_id
     join taxonverbatim tv on td.taxonverbatim_id=tv.taxonverbatim_id
     where s.shortname=:ds and td.isoriginal='t'
     where s.shortname=current_schema and td.isoriginal='t'
     group by p.projectname, l.authorlocationcode
     order by p.projectname, l.authorlocationcode 12;
-...
     join taxonoccurrence o on sub_locationevent.locationevent_id=o.locationevent_id
     join taxondetermination td on td.taxonoccurrence_id=o.taxonoccurrence_id
     join taxonverbatim tv on td.taxonverbatim_id=tv.taxonverbatim_id
     where s.shortname=:ds and td.isoriginal='t'
     where s.shortname=current_schema and td.isoriginal='t'
     order by p.projectname, l.authorlocationcode, taxon;
     -- ------------------
-...
     join taxondetermination td on td.taxonoccurrence_id=o.taxonoccurrence_id
     join taxonverbatim tv on td.taxonverbatim_id=tv.taxonverbatim_id
     join aggregateoccurrence ao on o.taxonoccurrence_id=ao.taxonoccurrence_id
     where s.shortname=:ds and td.isoriginal='t'
     where s.shortname=current_schema and td.isoriginal='t'
     group by p.projectname, l.authorlocationcode, taxon
     order by p.projectname, l.authorlocationcode, taxon;
-...
     join taxonverbatim tv on td.taxonverbatim_id=tv.taxonverbatim_id
     join aggregateoccurrence ao on o.taxonoccurrence_id=ao.taxonoccurrence_id
     join coverindex ci on ao.coverindex_id=ci.coverindex_id
     where s.shortname=:ds and td.isoriginal='t'
     where s.shortname=current_schema and td.isoriginal='t'
     and ci.coverpercent is not null
     group by p.projectname, l.authorlocationcode, taxon
     order by p.projectname, l.authorlocationcode, taxon;
-...
     join locationevent le on p.project_id=le.project_id
     join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id
     join location l on le.location_id=l.location_id
     where s.shortname=:ds
     where s.shortname=current_schema
     group by p.projectname, l.authorlocationcode
     order by p.projectname, l.authorlocationcode;
-...
     join locationevent le on p.project_id=le.project_id
     join locationevent sub_locationevent on sub_locationevent.parent_id=le.locationevent_id
     join location l on sub_locationevent.location_id=l.location_id
     where s.shortname=:ds
     where s.shortname=current_schema
     order by p.projectname, l.authorlocationcode;

     SELECT COUNT(*) AS totalRecords
     FROM trait
     JOIN taxonoccurrence USING (taxonoccurrence_id)
     WHERE taxonoccurrence.source_id = source_by_shortname('bien2_traits')
     WHERE taxonoccurrence.source_id = source_by_shortname(current_schema)
+    ;
     -- ------------------
-...
     SELECT COUNT(DISTINCT name) AS traits
     FROM trait
     JOIN taxonoccurrence USING (taxonoccurrence_id)
     WHERE taxonoccurrence.source_id = source_by_shortname('bien2_traits');
     WHERE taxonoccurrence.source_id = source_by_shortname(current_schema);
     -- ------------------
     -- 3. List trait names
-...
     SELECT DISTINCT name AS trait
     FROM trait
     JOIN taxonoccurrence USING (taxonoccurrence_id)
     WHERE taxonoccurrence.source_id = source_by_shortname('bien2_traits')
     WHERE taxonoccurrence.source_id = source_by_shortname(current_schema)
     ORDER BY name
+    ;
-...
     SELECT name AS trait, COUNT(*) AS measurements
     FROM trait
     JOIN taxonoccurrence USING (taxonoccurrence_id)
     WHERE taxonoccurrence.source_id = source_by_shortname('bien2_traits')
     WHERE taxonoccurrence.source_id = source_by_shortname(current_schema)
     GROUP BY name
     ORDER BY name
+    ;
-...
     CREATE OR REPLACE VIEW _05_count_taxa AS
     SELECT COUNT(DISTINCT (taxonname, author)) AS taxa
     FROM taxonverbatim
     WHERE source_id = source_by_shortname('bien2_traits')
     WHERE source_id = source_by_shortname(current_schema)
+    ;
     -- ------------------
-...
     CREATE OR REPLACE VIEW _06_list_taxa AS
     SELECT DISTINCT concat_ws(' ', taxonname, author) AS taxonwithauthor
     FROM taxonverbatim
     WHERE source_id = source_by_shortname('bien2_traits')
     WHERE source_id = source_by_shortname(current_schema)
     ORDER BY concat_ws(' ', taxonname, author)
+    ;
-...
     SELECT name AS trait, value, units
     FROM trait
     JOIN taxonoccurrence USING (taxonoccurrence_id)
     WHERE taxonoccurrence.source_id = source_by_shortname('bien2_traits')
     WHERE taxonoccurrence.source_id = source_by_shortname(current_schema)
     ORDER BY name, value, units
     LIMIT 5000
+    ;
-...
     FROM taxonverbatim
     JOIN taxondetermination ON taxondetermination.taxonverbatim_id = taxonverbatim.taxonverbatim_id AND taxondetermination.iscurrent
     JOIN trait USING (taxonoccurrence_id)
     WHERE taxonverbatim.source_id = source_by_shortname('bien2_traits')
     WHERE taxonverbatim.source_id = source_by_shortname(current_schema)
     ORDER BY taxonname
     LIMIT 5000
+    ;

Also available in: Unified diff

Project

General

Profile

Revision 12162

Added by Aaron Marcuse-Kubitza almost 11 years ago