schemas := $(root)/schemas

verify.out: $(schemas)/verify.sql _always

SELECT '# projects' AS ___;

SELECT count(*) AS count

FROM project

JOIN party ON party_id = datasource_id AND organizationname = :datasource

;

SELECT 'projects' AS ___;

SELECT projectname AS project

FROM project

JOIN party ON party_id = datasource_id AND organizationname = :datasource

ORDER BY project

;

SELECT E'each project\'s # locations' AS ___;

SELECT projectname AS project, count(*) AS locations_count

FROM project

JOIN party ON party_id = datasource_id AND organizationname = :datasource

JOIN locationevent USING (project_id)

GROUP BY project

ORDER BY project

;

SELECT '# locations' AS ___;

SELECT count(DISTINCT location_id) AS count

FROM locationevent

JOIN party ON party_id = datasource_id AND organizationname = :datasource

WHERE parent_id IS NULL -- no subplots

;

SELECT '# location events' AS ___;

SELECT count(*) AS count

FROM locationevent

JOIN party ON party_id = datasource_id AND organizationname = :datasource

AND parent_id IS NULL -- no subplots

;

SELECT '1st methods' AS ___;

SELECT DISTINCT

    projectname AS project

    , method.name AS method

FROM project

JOIN party ON party_id = datasource_id AND organizationname = :datasource

JOIN locationevent USING (project_id)

JOIN method USING (method_id)

ORDER BY project, method

LIMIT 10;

SELECT '1st observation measures' AS ___;

SELECT DISTINCT

    projectname AS project

    , method.observationmeasure AS observationmeasure

FROM project

JOIN party ON party_id = datasource_id AND organizationname = :datasource

JOIN locationevent USING (project_id)

JOIN method USING (method_id)

ORDER BY project, observationmeasure

LIMIT 10;