Revision 6179
Added by Aaron Marcuse-Kubitza over 11 years ago
verify.plots.sql | ||
---|---|---|
1 | 1 |
SELECT '# projects' AS ___; |
2 | 2 |
SELECT count(*) AS count |
3 | 3 |
FROM project |
4 |
WHERE reference_id =
|
|
5 |
(SELECT reference_id FROM reference WHERE shortname = :datasource)
|
|
4 |
WHERE source_id =
|
|
5 |
(SELECT source_id FROM source WHERE shortname = :datasource)
|
|
6 | 6 |
; |
7 | 7 |
|
8 | 8 |
SELECT 'projects' AS ___; |
9 | 9 |
SELECT projectname |
10 | 10 |
FROM project |
11 |
WHERE reference_id =
|
|
12 |
(SELECT reference_id FROM reference WHERE shortname = :datasource)
|
|
11 |
WHERE source_id =
|
|
12 |
(SELECT source_id FROM source WHERE shortname = :datasource)
|
|
13 | 13 |
ORDER BY projectname |
14 | 14 |
; |
15 | 15 |
|
... | ... | |
17 | 17 |
SELECT projectname, count(*) AS locations_count |
18 | 18 |
FROM project |
19 | 19 |
JOIN locationevent USING (project_id) |
20 |
WHERE project.reference_id =
|
|
21 |
(SELECT reference_id FROM reference WHERE shortname = :datasource)
|
|
20 |
WHERE project.source_id =
|
|
21 |
(SELECT source_id FROM source WHERE shortname = :datasource)
|
|
22 | 22 |
GROUP BY projectname |
23 | 23 |
ORDER BY projectname |
24 | 24 |
; |
... | ... | |
26 | 26 |
SELECT '# top-level locations' AS ___; |
27 | 27 |
SELECT count(DISTINCT location_id) AS count |
28 | 28 |
FROM location |
29 |
WHERE reference_id =
|
|
30 |
(SELECT reference_id FROM reference WHERE shortname = :datasource)
|
|
29 |
WHERE source_id =
|
|
30 |
(SELECT source_id FROM source WHERE shortname = :datasource)
|
|
31 | 31 |
AND parent_id IS NULL -- no subplots |
32 | 32 |
; |
33 | 33 |
|
34 | 34 |
SELECT '# top-level location events' AS ___; |
35 | 35 |
SELECT count(*) AS count |
36 | 36 |
FROM locationevent |
37 |
WHERE reference_id =
|
|
38 |
(SELECT reference_id FROM reference WHERE shortname = :datasource)
|
|
37 |
WHERE source_id =
|
|
38 |
(SELECT source_id FROM source WHERE shortname = :datasource)
|
|
39 | 39 |
AND parent_id IS NULL -- no subplots |
40 | 40 |
; |
41 | 41 |
|
42 | 42 |
SELECT '# locations' AS ___; |
43 | 43 |
SELECT count(DISTINCT location_id) AS count |
44 | 44 |
FROM location |
45 |
WHERE reference_id =
|
|
46 |
(SELECT reference_id FROM reference WHERE shortname = :datasource)
|
|
45 |
WHERE source_id =
|
|
46 |
(SELECT source_id FROM source WHERE shortname = :datasource)
|
|
47 | 47 |
; |
48 | 48 |
|
49 | 49 |
SELECT '# location events' AS ___; |
50 | 50 |
SELECT count(*) AS count |
51 | 51 |
FROM locationevent |
52 |
WHERE reference_id =
|
|
53 |
(SELECT reference_id FROM reference WHERE shortname = :datasource)
|
|
52 |
WHERE source_id =
|
|
53 |
(SELECT source_id FROM source WHERE shortname = :datasource)
|
|
54 | 54 |
; |
55 | 55 |
|
56 | 56 |
SELECT '1st methods' AS ___; |
... | ... | |
60 | 60 |
FROM project |
61 | 61 |
JOIN locationevent USING (project_id) |
62 | 62 |
JOIN method USING (method_id) |
63 |
WHERE project.reference_id =
|
|
64 |
(SELECT reference_id FROM reference WHERE shortname = :datasource)
|
|
63 |
WHERE project.source_id =
|
|
64 |
(SELECT source_id FROM source WHERE shortname = :datasource)
|
|
65 | 65 |
ORDER BY projectname, method |
66 | 66 |
LIMIT 10; |
67 | 67 |
|
... | ... | |
72 | 72 |
FROM project |
73 | 73 |
JOIN locationevent USING (project_id) |
74 | 74 |
JOIN method USING (method_id) |
75 |
WHERE project.reference_id =
|
|
76 |
(SELECT reference_id FROM reference WHERE shortname = :datasource)
|
|
75 |
WHERE project.source_id =
|
|
76 |
(SELECT source_id FROM source WHERE shortname = :datasource)
|
|
77 | 77 |
ORDER BY projectname, observationmeasure |
78 | 78 |
LIMIT 10; |
Also available in: Unified diff
schemas/vegbien.sql: Renamed reference -> source to make this table more broadly applicable, and because this now stores the datasource metadata