Revision 6908
Added by Aaron Marcuse-Kubitza almost 12 years ago
| vegbien.sql | ||
|---|---|---|
| 973 | 973 |
|
| 974 | 974 |
|
| 975 | 975 |
-- |
| 976 |
-- Name: sync_owner_count_to_view(); Type: FUNCTION; Schema: public; Owner: - |
|
| 977 |
-- |
|
| 978 |
|
|
| 979 |
CREATE FUNCTION sync_owner_count_to_view() RETURNS void |
|
| 980 |
LANGUAGE sql |
|
| 981 |
AS $$ |
|
| 982 |
DROP TABLE IF EXISTS owner_count; |
|
| 983 |
CREATE TABLE owner_count AS SELECT * FROM owner_count_view LIMIT 0; |
|
| 984 |
|
|
| 985 |
GRANT SELECT ON TABLE owner_count TO bien_read; |
|
| 986 |
GRANT SELECT ON TABLE owner_count TO public_; |
|
| 987 |
GRANT SELECT ON TABLE owner_count_view TO bien_read; |
|
| 988 |
GRANT SELECT ON TABLE owner_count_view TO public_; |
|
| 989 |
|
|
| 990 |
ALTER TABLE owner_count ADD PRIMARY KEY (owner); |
|
| 991 |
$$; |
|
| 992 |
|
|
| 993 |
|
|
| 994 |
-- |
|
| 995 | 976 |
-- Name: sync_provider_count_to_view(); Type: FUNCTION; Schema: public; Owner: - |
| 996 | 977 |
-- |
| 997 | 978 |
|
| ... | ... | |
| 1006 | 987 |
GRANT SELECT ON TABLE provider_count_view TO bien_read; |
| 1007 | 988 |
GRANT SELECT ON TABLE provider_count_view TO public_; |
| 1008 | 989 |
|
| 1009 |
ALTER TABLE provider_count ADD PRIMARY KEY (provider); |
|
| 990 |
ALTER TABLE provider_count ADD PRIMARY KEY (level, provider);
|
|
| 1010 | 991 |
$$; |
| 1011 | 992 |
|
| 1012 | 993 |
|
| ... | ... | |
| 3371 | 3352 |
|
| 3372 | 3353 |
|
| 3373 | 3354 |
-- |
| 3374 |
-- Name: owner_count; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 3375 |
-- |
|
| 3376 |
|
|
| 3377 |
CREATE TABLE owner_count ( |
|
| 3378 |
owner text NOT NULL, |
|
| 3379 |
count bigint |
|
| 3380 |
); |
|
| 3381 |
|
|
| 3382 |
|
|
| 3383 |
-- |
|
| 3384 |
-- Name: sourcename; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 3385 |
-- |
|
| 3386 |
|
|
| 3387 |
CREATE TABLE sourcename ( |
|
| 3388 |
sourcelist_id integer NOT NULL, |
|
| 3389 |
name text NOT NULL, |
|
| 3390 |
matched_source_id integer |
|
| 3391 |
); |
|
| 3392 |
|
|
| 3393 |
|
|
| 3394 |
-- |
|
| 3395 |
-- Name: owner_count_view; Type: VIEW; Schema: public; Owner: - |
|
| 3396 |
-- |
|
| 3397 |
|
|
| 3398 |
CREATE VIEW owner_count_view AS |
|
| 3399 |
SELECT sourcename.name AS owner, count(*) AS count FROM ((sourcename JOIN sourcelist USING (sourcelist_id)) JOIN specimenreplicate ON ((specimenreplicate.institution_id = sourcelist.sourcelist_id))) GROUP BY sourcename.name; |
|
| 3400 |
|
|
| 3401 |
|
|
| 3402 |
-- |
|
| 3403 | 3355 |
-- Name: party_party_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
| 3404 | 3356 |
-- |
| 3405 | 3357 |
|
| ... | ... | |
| 3721 | 3673 |
-- |
| 3722 | 3674 |
|
| 3723 | 3675 |
CREATE TABLE provider_count ( |
| 3676 |
level text NOT NULL, |
|
| 3724 | 3677 |
provider text NOT NULL, |
| 3725 |
count bigint |
|
| 3678 |
count bigint, |
|
| 3679 |
sourcetype sourcetype, |
|
| 3680 |
observationtype observationtype |
|
| 3726 | 3681 |
); |
| 3727 | 3682 |
|
| 3728 | 3683 |
|
| 3729 | 3684 |
-- |
| 3730 |
-- Name: provider_count_view; Type: VIEW; Schema: public; Owner: -
|
|
| 3685 |
-- Name: sourcename; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
| 3731 | 3686 |
-- |
| 3732 | 3687 |
|
| 3733 |
CREATE VIEW provider_count_view AS |
|
| 3734 |
SELECT analytical_stem.datasource AS provider, count(*) AS count FROM analytical_stem GROUP BY analytical_stem.datasource; |
|
| 3688 |
CREATE TABLE sourcename ( |
|
| 3689 |
sourcelist_id integer NOT NULL, |
|
| 3690 |
name text NOT NULL, |
|
| 3691 |
matched_source_id integer |
|
| 3692 |
); |
|
| 3735 | 3693 |
|
| 3736 | 3694 |
|
| 3737 | 3695 |
-- |
| 3738 |
-- Name: provider_view; Type: VIEW; Schema: public; Owner: - |
|
| 3696 |
-- Name: provider_count_view; Type: VIEW; Schema: public; Owner: -
|
|
| 3739 | 3697 |
-- |
| 3740 | 3698 |
|
| 3741 |
CREATE VIEW provider_view AS |
|
| 3742 |
(SELECT source.shortname AS name, source.sourcetype, source.observationtype FROM source ORDER BY source.sourcetype DESC NULLS LAST, source.shortname) UNION ALL (SELECT sourcename.name, 'herbarium'::sourcetype AS sourcetype, 'specimen'::observationtype AS observationtype FROM sourcename WHERE (sourcename.matched_source_id IS NULL) ORDER BY sourcename.name);
|
|
| 3699 |
CREATE VIEW provider_count_view AS
|
|
| 3700 |
SELECT s.level, s.provider, s.count, COALESCE(source.sourcetype, 'herbarium'::sourcetype) AS sourcetype, COALESCE(source.observationtype, 'specimen'::observationtype) AS 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) s LEFT JOIN source ON ((source.shortname = s.provider))) ORDER BY s.level DESC, s.count DESC, s.provider;
|
|
| 3743 | 3701 |
|
| 3744 | 3702 |
|
| 3745 | 3703 |
-- |
| ... | ... | |
| 5284 | 5242 |
|
| 5285 | 5243 |
|
| 5286 | 5244 |
-- |
| 5287 |
-- Data for Name: owner_count; Type: TABLE DATA; Schema: public; Owner: - |
|
| 5288 |
-- |
|
| 5289 |
|
|
| 5290 |
|
|
| 5291 |
|
|
| 5292 |
-- |
|
| 5293 | 5245 |
-- Data for Name: party; Type: TABLE DATA; Schema: public; Owner: - |
| 5294 | 5246 |
-- |
| 5295 | 5247 |
|
| ... | ... | |
| 5790 | 5742 |
|
| 5791 | 5743 |
|
| 5792 | 5744 |
-- |
| 5793 |
-- Name: owner_count_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 5794 |
-- |
|
| 5795 |
|
|
| 5796 |
ALTER TABLE ONLY owner_count |
|
| 5797 |
ADD CONSTRAINT owner_count_pkey PRIMARY KEY (owner); |
|
| 5798 |
|
|
| 5799 |
|
|
| 5800 |
-- |
|
| 5801 | 5745 |
-- Name: party_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 5802 | 5746 |
-- |
| 5803 | 5747 |
|
| ... | ... | |
| 5890 | 5834 |
-- |
| 5891 | 5835 |
|
| 5892 | 5836 |
ALTER TABLE ONLY provider_count |
| 5893 |
ADD CONSTRAINT provider_count_pkey PRIMARY KEY (provider); |
|
| 5837 |
ADD CONSTRAINT provider_count_pkey PRIMARY KEY (level, provider);
|
|
| 5894 | 5838 |
|
| 5895 | 5839 |
|
| 5896 | 5840 |
-- |
| ... | ... | |
| 8274 | 8218 |
|
| 8275 | 8219 |
|
| 8276 | 8220 |
-- |
| 8277 |
-- Name: owner_count; Type: ACL; Schema: public; Owner: - |
|
| 8278 |
-- |
|
| 8279 |
|
|
| 8280 |
REVOKE ALL ON TABLE owner_count FROM PUBLIC; |
|
| 8281 |
REVOKE ALL ON TABLE owner_count FROM bien; |
|
| 8282 |
GRANT ALL ON TABLE owner_count TO bien; |
|
| 8283 |
GRANT SELECT ON TABLE owner_count TO bien_read; |
|
| 8284 |
GRANT SELECT ON TABLE owner_count TO public_; |
|
| 8285 |
|
|
| 8286 |
|
|
| 8287 |
-- |
|
| 8288 |
-- Name: sourcename; Type: ACL; Schema: public; Owner: - |
|
| 8289 |
-- |
|
| 8290 |
|
|
| 8291 |
REVOKE ALL ON TABLE sourcename FROM PUBLIC; |
|
| 8292 |
REVOKE ALL ON TABLE sourcename FROM bien; |
|
| 8293 |
GRANT ALL ON TABLE sourcename TO bien; |
|
| 8294 |
GRANT SELECT ON TABLE sourcename TO bien_read; |
|
| 8295 |
GRANT SELECT ON TABLE sourcename TO public_; |
|
| 8296 |
|
|
| 8297 |
|
|
| 8298 |
-- |
|
| 8299 |
-- Name: owner_count_view; Type: ACL; Schema: public; Owner: - |
|
| 8300 |
-- |
|
| 8301 |
|
|
| 8302 |
REVOKE ALL ON TABLE owner_count_view FROM PUBLIC; |
|
| 8303 |
REVOKE ALL ON TABLE owner_count_view FROM bien; |
|
| 8304 |
GRANT ALL ON TABLE owner_count_view TO bien; |
|
| 8305 |
GRANT SELECT ON TABLE owner_count_view TO bien_read; |
|
| 8306 |
GRANT SELECT ON TABLE owner_count_view TO public_; |
|
| 8307 |
|
|
| 8308 |
|
|
| 8309 |
-- |
|
| 8310 | 8221 |
-- Name: partymember; Type: ACL; Schema: public; Owner: - |
| 8311 | 8222 |
-- |
| 8312 | 8223 |
|
| ... | ... | |
| 8378 | 8289 |
|
| 8379 | 8290 |
|
| 8380 | 8291 |
-- |
| 8292 |
-- Name: sourcename; Type: ACL; Schema: public; Owner: - |
|
| 8293 |
-- |
|
| 8294 |
|
|
| 8295 |
REVOKE ALL ON TABLE sourcename FROM PUBLIC; |
|
| 8296 |
REVOKE ALL ON TABLE sourcename FROM bien; |
|
| 8297 |
GRANT ALL ON TABLE sourcename TO bien; |
|
| 8298 |
GRANT SELECT ON TABLE sourcename TO bien_read; |
|
| 8299 |
GRANT SELECT ON TABLE sourcename TO public_; |
|
| 8300 |
|
|
| 8301 |
|
|
| 8302 |
-- |
|
| 8381 | 8303 |
-- Name: provider_count_view; Type: ACL; Schema: public; Owner: - |
| 8382 | 8304 |
-- |
| 8383 | 8305 |
|
| ... | ... | |
| 8389 | 8311 |
|
| 8390 | 8312 |
|
| 8391 | 8313 |
-- |
| 8392 |
-- Name: provider_view; Type: ACL; Schema: public; Owner: - |
|
| 8393 |
-- |
|
| 8394 |
|
|
| 8395 |
REVOKE ALL ON TABLE provider_view FROM PUBLIC; |
|
| 8396 |
REVOKE ALL ON TABLE provider_view FROM bien; |
|
| 8397 |
GRANT ALL ON TABLE provider_view TO bien; |
|
| 8398 |
GRANT SELECT ON TABLE provider_view TO bien_read; |
|
| 8399 |
GRANT SELECT ON TABLE provider_view TO public_; |
|
| 8400 |
|
|
| 8401 |
|
|
| 8402 |
-- |
|
| 8403 | 8314 |
-- Name: revision; Type: ACL; Schema: public; Owner: - |
| 8404 | 8315 |
-- |
| 8405 | 8316 |
|
Also available in: Unified diff
schemas/vegbien.sql: Merged provider_view, provider_count, and owner_count into provider_count, using the combining query for Brad's data providers page at <http://bien.nceas.ucsb.edu/bien/people/data-providers/>