Revision 10796
Added by Aaron Marcuse-Kubitza over 11 years ago
| vegbien.sql | ||
|---|---|---|
| 672 | 672 |
$_$; |
| 673 | 673 |
|
| 674 | 674 |
|
| 675 |
SET default_tablespace = ''; |
|
| 676 |
|
|
| 677 |
SET default_with_oids = false; |
|
| 678 |
|
|
| 675 | 679 |
-- |
| 680 |
-- Name: source; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 681 |
-- |
|
| 682 |
|
|
| 683 |
CREATE TABLE source ( |
|
| 684 |
source_id integer NOT NULL, |
|
| 685 |
matched_source_id integer, |
|
| 686 |
parent_id integer, |
|
| 687 |
shortname text NOT NULL, |
|
| 688 |
citation text, |
|
| 689 |
sourcetype sourcetype, |
|
| 690 |
accesslevel accesslevel, |
|
| 691 |
accessconditions text, |
|
| 692 |
observationtype observationtype, |
|
| 693 |
title text, |
|
| 694 |
titlesuperior text, |
|
| 695 |
volume text, |
|
| 696 |
issue text, |
|
| 697 |
pagerange text, |
|
| 698 |
totalpages integer, |
|
| 699 |
publisher text, |
|
| 700 |
publicationplace text, |
|
| 701 |
isbn text, |
|
| 702 |
edition text, |
|
| 703 |
numberofvolumes integer, |
|
| 704 |
chapternumber integer, |
|
| 705 |
reportnumber integer, |
|
| 706 |
communicationtype text, |
|
| 707 |
degree text, |
|
| 708 |
url text, |
|
| 709 |
doi text, |
|
| 710 |
additionalinfo text, |
|
| 711 |
pubdate date, |
|
| 712 |
accessdate date, |
|
| 713 |
conferencedate date, |
|
| 714 |
datecreated date DEFAULT now() NOT NULL, |
|
| 715 |
createdby text, |
|
| 716 |
datelastmodified date DEFAULT now() NOT NULL, |
|
| 717 |
lastmodifiedby text, |
|
| 718 |
import_revision text |
|
| 719 |
); |
|
| 720 |
|
|
| 721 |
|
|
| 722 |
-- |
|
| 723 |
-- Name: datasource_rm(text, anyelement); Type: FUNCTION; Schema: public; Owner: - |
|
| 724 |
-- |
|
| 725 |
|
|
| 726 |
CREATE FUNCTION datasource_rm(datasource text, schema_null anyelement DEFAULT NULL::source) RETURNS void |
|
| 727 |
LANGUAGE sql |
|
| 728 |
AS $_$ |
|
| 729 |
SELECT set_config('search_path', util.schema_ident($2), is_local := true);
|
|
| 730 |
DELETE FROM source WHERE shortname = $1; |
|
| 731 |
$_$; |
|
| 732 |
|
|
| 733 |
|
|
| 734 |
-- |
|
| 735 |
-- Name: FUNCTION datasource_rm(datasource text, schema_null anyelement); Type: COMMENT; Schema: public; Owner: - |
|
| 736 |
-- |
|
| 737 |
|
|
| 738 |
COMMENT ON FUNCTION datasource_rm(datasource text, schema_null anyelement) IS 'secure against renamings of the public schema. |
|
| 739 |
|
|
| 740 |
schema_null: identifies which schema''s tables to use. the default value is usually fine.'; |
|
| 741 |
|
|
| 742 |
|
|
| 743 |
-- |
|
| 676 | 744 |
-- Name: delete_scrubbed_taxondeterminations(text); Type: FUNCTION; Schema: public; Owner: - |
| 677 | 745 |
-- |
| 678 | 746 |
|
| ... | ... | |
| 775 | 843 |
$$; |
| 776 | 844 |
|
| 777 | 845 |
|
| 778 |
SET default_tablespace = ''; |
|
| 779 |
|
|
| 780 |
SET default_with_oids = false; |
|
| 781 |
|
|
| 782 | 846 |
-- |
| 783 | 847 |
-- Name: place; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 784 | 848 |
-- |
| ... | ... | |
| 2201 | 2265 |
|
| 2202 | 2266 |
|
| 2203 | 2267 |
-- |
| 2204 |
-- Name: source; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 2205 |
-- |
|
| 2206 |
|
|
| 2207 |
CREATE TABLE source ( |
|
| 2208 |
source_id integer NOT NULL, |
|
| 2209 |
matched_source_id integer, |
|
| 2210 |
parent_id integer, |
|
| 2211 |
shortname text NOT NULL, |
|
| 2212 |
citation text, |
|
| 2213 |
sourcetype sourcetype, |
|
| 2214 |
accesslevel accesslevel, |
|
| 2215 |
accessconditions text, |
|
| 2216 |
observationtype observationtype, |
|
| 2217 |
title text, |
|
| 2218 |
titlesuperior text, |
|
| 2219 |
volume text, |
|
| 2220 |
issue text, |
|
| 2221 |
pagerange text, |
|
| 2222 |
totalpages integer, |
|
| 2223 |
publisher text, |
|
| 2224 |
publicationplace text, |
|
| 2225 |
isbn text, |
|
| 2226 |
edition text, |
|
| 2227 |
numberofvolumes integer, |
|
| 2228 |
chapternumber integer, |
|
| 2229 |
reportnumber integer, |
|
| 2230 |
communicationtype text, |
|
| 2231 |
degree text, |
|
| 2232 |
url text, |
|
| 2233 |
doi text, |
|
| 2234 |
additionalinfo text, |
|
| 2235 |
pubdate date, |
|
| 2236 |
accessdate date, |
|
| 2237 |
conferencedate date, |
|
| 2238 |
datecreated date DEFAULT now() NOT NULL, |
|
| 2239 |
createdby text, |
|
| 2240 |
datelastmodified date DEFAULT now() NOT NULL, |
|
| 2241 |
lastmodifiedby text, |
|
| 2242 |
import_revision text |
|
| 2243 |
); |
|
| 2244 |
|
|
| 2245 |
|
|
| 2246 |
-- |
|
| 2247 | 2268 |
-- Name: sourcelist; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 2248 | 2269 |
-- |
| 2249 | 2270 |
|
| ... | ... | |
| 8444 | 8465 |
|
| 8445 | 8466 |
|
| 8446 | 8467 |
-- |
| 8468 |
-- Name: source; Type: ACL; Schema: public; Owner: - |
|
| 8469 |
-- |
|
| 8470 |
|
|
| 8471 |
REVOKE ALL ON TABLE source FROM PUBLIC; |
|
| 8472 |
REVOKE ALL ON TABLE source FROM bien; |
|
| 8473 |
GRANT ALL ON TABLE source TO bien; |
|
| 8474 |
GRANT SELECT ON TABLE source TO bien_read; |
|
| 8475 |
GRANT SELECT ON TABLE source TO public_; |
|
| 8476 |
|
|
| 8477 |
|
|
| 8478 |
-- |
|
| 8447 | 8479 |
-- Name: place; Type: ACL; Schema: public; Owner: - |
| 8448 | 8480 |
-- |
| 8449 | 8481 |
|
| ... | ... | |
| 8615 | 8647 |
|
| 8616 | 8648 |
|
| 8617 | 8649 |
-- |
| 8618 |
-- Name: source; Type: ACL; Schema: public; Owner: - |
|
| 8619 |
-- |
|
| 8620 |
|
|
| 8621 |
REVOKE ALL ON TABLE source FROM PUBLIC; |
|
| 8622 |
REVOKE ALL ON TABLE source FROM bien; |
|
| 8623 |
GRANT ALL ON TABLE source TO bien; |
|
| 8624 |
GRANT SELECT ON TABLE source TO bien_read; |
|
| 8625 |
GRANT SELECT ON TABLE source TO public_; |
|
| 8626 |
|
|
| 8627 |
|
|
| 8628 |
-- |
|
| 8629 | 8650 |
-- Name: sourcelist; Type: ACL; Schema: public; Owner: - |
| 8630 | 8651 |
-- |
| 8631 | 8652 |
|
Also available in: Unified diff
schemas/vegbien.sql: added datasource_rm(). this uses an internal schema-scoping parameter to ensure that the function always operates on tables in the schema it was defined in, rather than tables in the search_path. this ensures that when the public schema is renamed (e.g. from an imported version), the function will continue to operate on its own schema rather than whichever schema happens to be called public. this avoids any surprises if you are trying to remove a datasource in one schema, and don't want it to unintentionally be removed in another schema instead.