| 1126 |
1126 |
GRANT SELECT ON TABLE provider_count_view TO bien_read;
|
| 1127 |
1127 |
GRANT SELECT ON TABLE provider_count_view TO public_;
|
| 1128 |
1128 |
|
| 1129 |
|
ALTER TABLE provider_count ADD PRIMARY KEY (level, provider);
|
|
1129 |
ALTER TABLE provider_count ADD PRIMARY KEY (dataprovider, dataset);
|
| 1130 |
1130 |
$$;
|
| 1131 |
1131 |
|
| 1132 |
1132 |
|
| ... | ... | |
| 3841 |
3841 |
--
|
| 3842 |
3842 |
|
| 3843 |
3843 |
CREATE TABLE provider_count (
|
| 3844 |
|
level text NOT NULL,
|
| 3845 |
|
provider text NOT NULL,
|
| 3846 |
|
count bigint,
|
|
3844 |
dataprovider text NOT NULL,
|
|
3845 |
dataset text NOT NULL,
|
|
3846 |
records bigint,
|
| 3847 |
3847 |
sourcetype sourcetype,
|
| 3848 |
3848 |
observationtype observationtype
|
| 3849 |
3849 |
);
|
| ... | ... | |
| 3865 |
3865 |
--
|
| 3866 |
3866 |
|
| 3867 |
3867 |
CREATE VIEW provider_count_view AS
|
| 3868 |
|
SELECT s.level, s.provider, s.count, source.sourcetype, source.observationtype FROM ((SELECT 'top-level'::text AS level, analytical_stem.datasource AS provider, count(*) AS count FROM analytical_stem GROUP BY analytical_stem.datasource UNION ALL SELECT 'data owner'::text AS level, sourcename.name AS provider, count(*) AS count FROM ((sourcename JOIN sourcelist USING (sourcelist_id)) JOIN specimenreplicate ON ((specimenreplicate.institution_id = sourcelist.sourcelist_id))) GROUP BY sourcename.name HAVING (count(*) > 0)) s LEFT JOIN source ON ((source.shortname = s.provider))) ORDER BY s.level DESC, s.count DESC, s.provider;
|
|
3868 |
(SELECT s.dataprovider, '(total)'::text AS dataset, s.records, source.sourcetype, source.observationtype FROM ((SELECT analytical_stem.datasource AS dataprovider, count(*) AS records FROM analytical_stem GROUP BY analytical_stem.datasource) s JOIN source ON ((source.shortname = s.dataprovider))) ORDER BY s.records DESC) UNION ALL (SELECT dataprovider.shortname AS dataprovider, s.dataset, s.records, dataset.sourcetype, COALESCE(dataset.observationtype, dataprovider.observationtype) AS observationtype FROM (((SELECT specimenreplicate.source_id AS dataprovider_id, sourcename.name AS dataset, sourcename.matched_source_id AS dataset_id, count(*) AS records FROM ((specimenreplicate JOIN sourcelist ON ((sourcelist.sourcelist_id = specimenreplicate.institution_id))) JOIN sourcename USING (sourcelist_id)) GROUP BY specimenreplicate.source_id, sourcename.name, sourcename.matched_source_id) s JOIN source dataprovider ON ((dataprovider.source_id = s.dataprovider_id))) LEFT JOIN source dataset ON ((dataset.source_id = s.dataset_id))) ORDER BY dataprovider.shortname, s.records DESC, s.dataset);
|
| 3869 |
3869 |
|
| 3870 |
3870 |
|
| 3871 |
3871 |
--
|
| ... | ... | |
| 6014 |
6014 |
--
|
| 6015 |
6015 |
|
| 6016 |
6016 |
ALTER TABLE ONLY provider_count
|
| 6017 |
|
ADD CONSTRAINT provider_count_pkey PRIMARY KEY (level, provider);
|
|
6017 |
ADD CONSTRAINT provider_count_pkey PRIMARY KEY (dataprovider, dataset);
|
| 6018 |
6018 |
|
| 6019 |
6019 |
|
| 6020 |
6020 |
--
|
schemas/vegbien.sql: provider_count_view: Group data by dataprovider and dataset, rather than by each separately, to satisfy the GBIF attribution requirements as described by Brad