Revision 6908
Added by Aaron Marcuse-Kubitza almost 12 years ago
| schemas/vegbien.my.sql | ||
|---|---|---|
| 423 | 423 |
|
| 424 | 424 |
|
| 425 | 425 |
-- |
| 426 |
-- Name: sync_owner_count_to_view(); Type: FUNCTION; Schema: public; Owner: - |
|
| 427 |
-- |
|
| 428 |
|
|
| 429 |
|
|
| 430 |
|
|
| 431 |
|
|
| 432 |
-- |
|
| 433 | 426 |
-- Name: sync_provider_count_to_view(); Type: FUNCTION; Schema: public; Owner: - |
| 434 | 427 |
-- |
| 435 | 428 |
|
| ... | ... | |
| 2415 | 2408 |
|
| 2416 | 2409 |
|
| 2417 | 2410 |
-- |
| 2418 |
-- Name: owner_count; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 2419 |
-- |
|
| 2420 |
|
|
| 2421 |
CREATE TABLE owner_count ( |
|
| 2422 |
owner varchar(255) NOT NULL, |
|
| 2423 |
count varchar(255) |
|
| 2424 |
); |
|
| 2425 |
|
|
| 2426 |
|
|
| 2427 |
-- |
|
| 2428 |
-- Name: sourcename; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 2429 |
-- |
|
| 2430 |
|
|
| 2431 |
CREATE TABLE sourcename ( |
|
| 2432 |
sourcelist_id int(11) NOT NULL, |
|
| 2433 |
name varchar(255) NOT NULL, |
|
| 2434 |
matched_source_id int(11) |
|
| 2435 |
); |
|
| 2436 |
|
|
| 2437 |
|
|
| 2438 |
-- |
|
| 2439 |
-- Name: owner_count_view; Type: VIEW; Schema: public; Owner: - |
|
| 2440 |
-- |
|
| 2441 |
|
|
| 2442 |
|
|
| 2443 |
|
|
| 2444 |
|
|
| 2445 |
-- |
|
| 2446 | 2411 |
-- Name: party_party_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
| 2447 | 2412 |
-- |
| 2448 | 2413 |
|
| ... | ... | |
| 2717 | 2682 |
-- |
| 2718 | 2683 |
|
| 2719 | 2684 |
CREATE TABLE provider_count ( |
| 2685 |
level varchar(255) NOT NULL, |
|
| 2720 | 2686 |
provider varchar(255) NOT NULL, |
| 2721 |
count varchar(255) |
|
| 2687 |
count varchar(255), |
|
| 2688 |
sourcetype varchar(255), |
|
| 2689 |
observationtype varchar(255) |
|
| 2722 | 2690 |
); |
| 2723 | 2691 |
|
| 2724 | 2692 |
|
| 2725 | 2693 |
-- |
| 2726 |
-- Name: provider_count_view; Type: VIEW; Schema: public; Owner: -
|
|
| 2694 |
-- Name: sourcename; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
| 2727 | 2695 |
-- |
| 2728 | 2696 |
|
| 2697 |
CREATE TABLE sourcename ( |
|
| 2698 |
sourcelist_id int(11) NOT NULL, |
|
| 2699 |
name varchar(255) NOT NULL, |
|
| 2700 |
matched_source_id int(11) |
|
| 2701 |
); |
|
| 2729 | 2702 |
|
| 2730 | 2703 |
|
| 2731 |
|
|
| 2732 | 2704 |
-- |
| 2733 |
-- Name: provider_view; Type: VIEW; Schema: public; Owner: - |
|
| 2705 |
-- Name: provider_count_view; Type: VIEW; Schema: public; Owner: -
|
|
| 2734 | 2706 |
-- |
| 2735 | 2707 |
|
| 2736 | 2708 |
|
| ... | ... | |
| 4158 | 4130 |
|
| 4159 | 4131 |
|
| 4160 | 4132 |
-- |
| 4161 |
-- Data for Name: owner_count; Type: TABLE DATA; Schema: public; Owner: - |
|
| 4162 |
-- |
|
| 4163 |
|
|
| 4164 |
|
|
| 4165 |
|
|
| 4166 |
-- |
|
| 4167 | 4133 |
-- Data for Name: party; Type: TABLE DATA; Schema: public; Owner: - |
| 4168 | 4134 |
-- |
| 4169 | 4135 |
|
| ... | ... | |
| 4664 | 4630 |
|
| 4665 | 4631 |
|
| 4666 | 4632 |
-- |
| 4667 |
-- Name: owner_count_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 4668 |
-- |
|
| 4669 |
|
|
| 4670 |
ALTER TABLE owner_count |
|
| 4671 |
ADD CONSTRAINT owner_count_pkey PRIMARY KEY (owner); |
|
| 4672 |
|
|
| 4673 |
|
|
| 4674 |
-- |
|
| 4675 | 4633 |
-- Name: party_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 4676 | 4634 |
-- |
| 4677 | 4635 |
|
| ... | ... | |
| 4764 | 4722 |
-- |
| 4765 | 4723 |
|
| 4766 | 4724 |
ALTER TABLE provider_count |
| 4767 |
ADD CONSTRAINT provider_count_pkey PRIMARY KEY (provider); |
|
| 4725 |
ADD CONSTRAINT provider_count_pkey PRIMARY KEY (level, provider);
|
|
| 4768 | 4726 |
|
| 4769 | 4727 |
|
| 4770 | 4728 |
-- |
| ... | ... | |
| 7100 | 7058 |
|
| 7101 | 7059 |
|
| 7102 | 7060 |
-- |
| 7103 |
-- Name: owner_count; Type: ACL; Schema: public; Owner: - |
|
| 7104 |
-- |
|
| 7105 |
|
|
| 7106 |
|
|
| 7107 |
|
|
| 7108 |
|
|
| 7109 |
|
|
| 7110 |
|
|
| 7111 |
|
|
| 7112 |
|
|
| 7113 |
-- |
|
| 7114 |
-- Name: sourcename; Type: ACL; Schema: public; Owner: - |
|
| 7115 |
-- |
|
| 7116 |
|
|
| 7117 |
|
|
| 7118 |
|
|
| 7119 |
|
|
| 7120 |
|
|
| 7121 |
|
|
| 7122 |
|
|
| 7123 |
|
|
| 7124 |
-- |
|
| 7125 |
-- Name: owner_count_view; Type: ACL; Schema: public; Owner: - |
|
| 7126 |
-- |
|
| 7127 |
|
|
| 7128 |
|
|
| 7129 |
|
|
| 7130 |
|
|
| 7131 |
|
|
| 7132 |
|
|
| 7133 |
|
|
| 7134 |
|
|
| 7135 |
-- |
|
| 7136 | 7061 |
-- Name: partymember; Type: ACL; Schema: public; Owner: - |
| 7137 | 7062 |
-- |
| 7138 | 7063 |
|
| ... | ... | |
| 7204 | 7129 |
|
| 7205 | 7130 |
|
| 7206 | 7131 |
-- |
| 7207 |
-- Name: provider_count_view; Type: ACL; Schema: public; Owner: -
|
|
| 7132 |
-- Name: sourcename; Type: ACL; Schema: public; Owner: -
|
|
| 7208 | 7133 |
-- |
| 7209 | 7134 |
|
| 7210 | 7135 |
|
| ... | ... | |
| 7215 | 7140 |
|
| 7216 | 7141 |
|
| 7217 | 7142 |
-- |
| 7218 |
-- Name: provider_view; Type: ACL; Schema: public; Owner: - |
|
| 7143 |
-- Name: provider_count_view; Type: ACL; Schema: public; Owner: -
|
|
| 7219 | 7144 |
-- |
| 7220 | 7145 |
|
| 7221 | 7146 |
|
| schemas/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 |
|
| bin/make_analytical_db | ||
|---|---|---|
| 35 | 35 |
TRUNCATE threatened_taxonlabel; |
| 36 | 36 |
INSERT INTO threatened_taxonlabel SELECT * FROM threatened_taxonlabel_view; |
| 37 | 37 |
|
| 38 |
$(mk_analytical_table owner_count) |
|
| 39 | 38 |
$(mk_analytical_table taxon_trait) |
| 40 | 39 |
$(mk_analytical_table analytical_stem) |
| 41 | 40 |
$(mk_analytical_table provider_count) |
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/>