SELECT '# projects' AS ___;
SELECT count(*) AS count
FROM project
WHERE reference_id =
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
;

SELECT 'projects' AS ___;
SELECT projectname
FROM project
WHERE reference_id =
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
ORDER BY projectname
;

SELECT E'each project\'s # locations' AS ___;
SELECT projectname, count(*) AS locations_count
FROM project
JOIN locationevent USING (project_id)
WHERE project.reference_id =
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
GROUP BY projectname
ORDER BY projectname
;

SELECT '# top-level locations' AS ___;
SELECT count(DISTINCT location_id) AS count
FROM location
WHERE reference_id =
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
AND parent_id IS NULL -- no subplots
;

SELECT '# top-level location events' AS ___;
SELECT count(*) AS count
FROM locationevent
WHERE reference_id =
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
AND parent_id IS NULL -- no subplots
;

SELECT '# locations' AS ___;
SELECT count(DISTINCT location_id) AS count
FROM location
WHERE reference_id =
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
;

SELECT '# location events' AS ___;
SELECT count(*) AS count
FROM locationevent
WHERE reference_id =
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
;

SELECT '1st methods' AS ___;
SELECT DISTINCT
    projectname
    , method.name AS method
FROM project
JOIN locationevent USING (project_id)
JOIN method USING (method_id)
WHERE project.reference_id =
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
ORDER BY projectname, method
LIMIT 10;

SELECT '1st observation measures' AS ___;
SELECT DISTINCT
    projectname
    , method.observationmeasure AS observationmeasure
FROM project
JOIN locationevent USING (project_id)
JOIN method USING (method_id)
WHERE project.reference_id =
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
ORDER BY projectname, observationmeasure
LIMIT 10;