Revision 13610
Added by Aaron Marcuse-Kubitza over 10 years ago
trunk/schemas/vegbien.my.sql | ||
---|---|---|
1283 | 1283 |
|
1284 | 1284 |
|
1285 | 1285 |
-- |
1286 |
-- Name: analytical_stem_view_modify(); Type: FUNCTION; Schema: public; Owner: - |
|
1287 |
-- |
|
1288 |
|
|
1289 |
|
|
1290 |
|
|
1291 |
|
|
1292 |
-- |
|
1286 | 1293 |
-- Name: datasource_publish(varchar(255), anyelement); Type: FUNCTION; Schema: public; Owner: - |
1287 | 1294 |
-- |
1288 | 1295 |
|
... | ... | |
1346 | 1353 |
|
1347 | 1354 |
|
1348 | 1355 |
-- |
1356 |
-- Name: geoscrub_input_view_modify(); Type: FUNCTION; Schema: public; Owner: - |
|
1357 |
-- |
|
1358 |
|
|
1359 |
|
|
1360 |
|
|
1361 |
|
|
1362 |
-- |
|
1349 | 1363 |
-- Name: location__pull_forward_from_parent(); Type: FUNCTION; Schema: public; Owner: - |
1350 | 1364 |
-- |
1351 | 1365 |
|
... | ... | |
1555 | 1569 |
|
1556 | 1570 |
|
1557 | 1571 |
-- |
1572 |
-- Name: provider_count_view_modify(); Type: FUNCTION; Schema: public; Owner: - |
|
1573 |
-- |
|
1574 |
|
|
1575 |
|
|
1576 |
|
|
1577 |
|
|
1578 |
-- |
|
1558 | 1579 |
-- Name: public_schema_publish(varchar(255)); Type: FUNCTION; Schema: public; Owner: - |
1559 | 1580 |
-- |
1560 | 1581 |
|
... | ... | |
1618 | 1639 |
|
1619 | 1640 |
|
1620 | 1641 |
-- |
1621 |
-- Name: sync_analytical_stem_to_view(); Type: FUNCTION; Schema: public; Owner: -
|
|
1642 |
-- Name: taxon_trait_view_modify(); Type: FUNCTION; Schema: public; Owner: -
|
|
1622 | 1643 |
-- |
1623 | 1644 |
|
1624 | 1645 |
|
1625 | 1646 |
|
1626 | 1647 |
|
1627 | 1648 |
-- |
1628 |
-- Name: sync_geoscrub_input_to_view(); Type: FUNCTION; Schema: public; Owner: - |
|
1629 |
-- |
|
1630 |
|
|
1631 |
|
|
1632 |
|
|
1633 |
|
|
1634 |
-- |
|
1635 |
-- Name: sync_provider_count_to_view(); Type: FUNCTION; Schema: public; Owner: - |
|
1636 |
-- |
|
1637 |
|
|
1638 |
|
|
1639 |
|
|
1640 |
|
|
1641 |
-- |
|
1642 |
-- Name: sync_taxon_trait_to_view(); Type: FUNCTION; Schema: public; Owner: - |
|
1643 |
-- |
|
1644 |
|
|
1645 |
|
|
1646 |
|
|
1647 |
|
|
1648 |
-- |
|
1649 | 1649 |
-- Name: taxondetermination_set_iscurrent(int(11)); Type: FUNCTION; Schema: public; Owner: - |
1650 | 1650 |
-- |
1651 | 1651 |
|
trunk/schemas/public_.sql | ||
---|---|---|
1982 | 1982 |
|
1983 | 1983 |
|
1984 | 1984 |
-- |
1985 |
-- Name: analytical_stem_view_modify(); Type: FUNCTION; Schema: public; Owner: - |
|
1986 |
-- |
|
1987 |
|
|
1988 |
CREATE FUNCTION analytical_stem_view_modify() RETURNS void |
|
1989 |
LANGUAGE sql |
|
1990 |
AS $_$ |
|
1991 |
SELECT util.recreate($$ |
|
1992 |
DROP TABLE IF EXISTS analytical_stem; |
|
1993 |
SELECT util.copy('analytical_stem_view', 'analytical_stem'); |
|
1994 |
ALTER TABLE analytical_stem ADD COLUMN taxon_occurrence__pkey serial PRIMARY KEY; |
|
1995 |
|
|
1996 |
GRANT SELECT ON TABLE analytical_stem TO bien_read; |
|
1997 |
GRANT SELECT ON TABLE analytical_stem_view TO bien_read; |
|
1998 |
|
|
1999 |
ALTER TABLE analytical_stem ALTER COLUMN "datasource" SET NOT NULL; |
|
2000 |
ALTER TABLE analytical_stem ALTER COLUMN "locationID" SET NOT NULL; |
|
2001 |
|
|
2002 |
CREATE INDEX ON analytical_stem ("datasource", "occurrenceID"); |
|
2003 |
CREATE INDEX ON analytical_stem ("datasource", "specimenHolderInstitutions", "collection", "accessionNumber"); |
|
2004 |
CREATE INDEX ON analytical_stem ("datasource", "projectID", "locationID", "eventDate"); |
|
2005 |
CREATE INDEX ON analytical_stem ("scrubbed_morphospecies_binomial"); |
|
2006 |
$$); |
|
2007 |
$_$; |
|
2008 |
|
|
2009 |
|
|
2010 |
-- |
|
1985 | 2011 |
-- Name: datasource_publish(text, anyelement); Type: FUNCTION; Schema: public; Owner: - |
1986 | 2012 |
-- |
1987 | 2013 |
|
... | ... | |
2141 | 2167 |
|
2142 | 2168 |
|
2143 | 2169 |
-- |
2170 |
-- Name: geoscrub_input_view_modify(); Type: FUNCTION; Schema: public; Owner: - |
|
2171 |
-- |
|
2172 |
|
|
2173 |
CREATE FUNCTION geoscrub_input_view_modify() RETURNS void |
|
2174 |
LANGUAGE sql |
|
2175 |
AS $$ |
|
2176 |
DROP TABLE IF EXISTS geoscrub_input; |
|
2177 |
SELECT util.copy('geoscrub_input_view', 'geoscrub_input'); |
|
2178 |
|
|
2179 |
GRANT SELECT ON TABLE geoscrub_input TO bien_read; |
|
2180 |
GRANT SELECT ON TABLE geoscrub_input_view TO bien_read; |
|
2181 |
$$; |
|
2182 |
|
|
2183 |
|
|
2184 |
-- |
|
2144 | 2185 |
-- Name: location__pull_forward_from_parent(); Type: FUNCTION; Schema: public; Owner: - |
2145 | 2186 |
-- |
2146 | 2187 |
|
... | ... | |
2650 | 2691 |
|
2651 | 2692 |
|
2652 | 2693 |
-- |
2694 |
-- Name: provider_count_view_modify(); Type: FUNCTION; Schema: public; Owner: - |
|
2695 |
-- |
|
2696 |
|
|
2697 |
CREATE FUNCTION provider_count_view_modify() RETURNS void |
|
2698 |
LANGUAGE sql |
|
2699 |
AS $$ |
|
2700 |
DROP TABLE IF EXISTS provider_count; |
|
2701 |
SELECT util.copy('provider_count_view', 'provider_count'); |
|
2702 |
|
|
2703 |
GRANT SELECT ON TABLE provider_count TO bien_read; |
|
2704 |
GRANT SELECT ON TABLE provider_count TO public_; |
|
2705 |
GRANT SELECT ON TABLE provider_count_view TO bien_read; |
|
2706 |
GRANT SELECT ON TABLE provider_count_view TO public_; |
|
2707 |
|
|
2708 |
ALTER TABLE provider_count ADD PRIMARY KEY (dataprovider, dataset); |
|
2709 |
$$; |
|
2710 |
|
|
2711 |
|
|
2712 |
-- |
|
2653 | 2713 |
-- Name: public_schema_publish(text); Type: FUNCTION; Schema: public; Owner: - |
2654 | 2714 |
-- |
2655 | 2715 |
|
... | ... | |
2812 | 2872 |
|
2813 | 2873 |
|
2814 | 2874 |
-- |
2815 |
-- Name: sync_analytical_stem_to_view(); Type: FUNCTION; Schema: public; Owner: -
|
|
2875 |
-- Name: taxon_trait_view_modify(); Type: FUNCTION; Schema: public; Owner: -
|
|
2816 | 2876 |
-- |
2817 | 2877 |
|
2818 |
CREATE FUNCTION sync_analytical_stem_to_view() RETURNS void
|
|
2878 |
CREATE FUNCTION taxon_trait_view_modify() RETURNS void
|
|
2819 | 2879 |
LANGUAGE sql |
2820 |
AS $_$ |
|
2821 |
SELECT util.recreate($$ |
|
2822 |
DROP TABLE IF EXISTS analytical_stem; |
|
2823 |
SELECT util.copy('analytical_stem_view', 'analytical_stem'); |
|
2824 |
ALTER TABLE analytical_stem ADD COLUMN taxon_occurrence__pkey serial PRIMARY KEY; |
|
2825 |
|
|
2826 |
GRANT SELECT ON TABLE analytical_stem TO bien_read; |
|
2827 |
GRANT SELECT ON TABLE analytical_stem_view TO bien_read; |
|
2828 |
|
|
2829 |
ALTER TABLE analytical_stem ALTER COLUMN "datasource" SET NOT NULL; |
|
2830 |
ALTER TABLE analytical_stem ALTER COLUMN "locationID" SET NOT NULL; |
|
2831 |
|
|
2832 |
CREATE INDEX ON analytical_stem ("datasource", "occurrenceID"); |
|
2833 |
CREATE INDEX ON analytical_stem ("datasource", "specimenHolderInstitutions", "collection", "accessionNumber"); |
|
2834 |
CREATE INDEX ON analytical_stem ("datasource", "projectID", "locationID", "eventDate"); |
|
2835 |
CREATE INDEX ON analytical_stem ("scrubbed_morphospecies_binomial"); |
|
2836 |
$$); |
|
2837 |
$_$; |
|
2838 |
|
|
2839 |
|
|
2840 |
-- |
|
2841 |
-- Name: sync_geoscrub_input_to_view(); Type: FUNCTION; Schema: public; Owner: - |
|
2842 |
-- |
|
2843 |
|
|
2844 |
CREATE FUNCTION sync_geoscrub_input_to_view() RETURNS void |
|
2845 |
LANGUAGE sql |
|
2846 | 2880 |
AS $$ |
2847 |
DROP TABLE IF EXISTS geoscrub_input; |
|
2848 |
SELECT util.copy('geoscrub_input_view', 'geoscrub_input'); |
|
2849 |
|
|
2850 |
GRANT SELECT ON TABLE geoscrub_input TO bien_read; |
|
2851 |
GRANT SELECT ON TABLE geoscrub_input_view TO bien_read; |
|
2852 |
$$; |
|
2853 |
|
|
2854 |
|
|
2855 |
-- |
|
2856 |
-- Name: sync_provider_count_to_view(); Type: FUNCTION; Schema: public; Owner: - |
|
2857 |
-- |
|
2858 |
|
|
2859 |
CREATE FUNCTION sync_provider_count_to_view() RETURNS void |
|
2860 |
LANGUAGE sql |
|
2861 |
AS $$ |
|
2862 |
DROP TABLE IF EXISTS provider_count; |
|
2863 |
SELECT util.copy('provider_count_view', 'provider_count'); |
|
2864 |
|
|
2865 |
GRANT SELECT ON TABLE provider_count TO bien_read; |
|
2866 |
GRANT SELECT ON TABLE provider_count TO public_; |
|
2867 |
GRANT SELECT ON TABLE provider_count_view TO bien_read; |
|
2868 |
GRANT SELECT ON TABLE provider_count_view TO public_; |
|
2869 |
|
|
2870 |
ALTER TABLE provider_count ADD PRIMARY KEY (dataprovider, dataset); |
|
2871 |
$$; |
|
2872 |
|
|
2873 |
|
|
2874 |
-- |
|
2875 |
-- Name: sync_taxon_trait_to_view(); Type: FUNCTION; Schema: public; Owner: - |
|
2876 |
-- |
|
2877 |
|
|
2878 |
CREATE FUNCTION sync_taxon_trait_to_view() RETURNS void |
|
2879 |
LANGUAGE sql |
|
2880 |
AS $$ |
|
2881 | 2881 |
DROP TABLE IF EXISTS taxon_trait; |
2882 | 2882 |
SELECT util.copy('taxon_trait_view', 'taxon_trait'); |
2883 | 2883 |
|
... | ... | |
6802 | 6802 |
|
6803 | 6803 |
COMMENT ON VIEW analytical_stem_view IS ' |
6804 | 6804 |
after updating this: |
6805 |
SELECT sync_analytical_stem_to_view()
|
|
6805 |
SELECT analytical_stem_view_modify()
|
|
6806 | 6806 |
add applicable columns to analytical_specimen, analytical_plot |
6807 | 6807 |
'; |
6808 | 6808 |
|
trunk/schemas/vegbien.sql | ||
---|---|---|
1982 | 1982 |
|
1983 | 1983 |
|
1984 | 1984 |
-- |
1985 |
-- Name: analytical_stem_view_modify(); Type: FUNCTION; Schema: public; Owner: - |
|
1986 |
-- |
|
1987 |
|
|
1988 |
CREATE FUNCTION analytical_stem_view_modify() RETURNS void |
|
1989 |
LANGUAGE sql |
|
1990 |
AS $_$ |
|
1991 |
SELECT util.recreate($$ |
|
1992 |
DROP TABLE IF EXISTS analytical_stem; |
|
1993 |
SELECT util.copy('analytical_stem_view', 'analytical_stem'); |
|
1994 |
ALTER TABLE analytical_stem ADD COLUMN taxon_occurrence__pkey serial PRIMARY KEY; |
|
1995 |
|
|
1996 |
GRANT SELECT ON TABLE analytical_stem TO bien_read; |
|
1997 |
GRANT SELECT ON TABLE analytical_stem_view TO bien_read; |
|
1998 |
|
|
1999 |
ALTER TABLE analytical_stem ALTER COLUMN "datasource" SET NOT NULL; |
|
2000 |
ALTER TABLE analytical_stem ALTER COLUMN "locationID" SET NOT NULL; |
|
2001 |
|
|
2002 |
CREATE INDEX ON analytical_stem ("datasource", "occurrenceID"); |
|
2003 |
CREATE INDEX ON analytical_stem ("datasource", "specimenHolderInstitutions", "collection", "accessionNumber"); |
|
2004 |
CREATE INDEX ON analytical_stem ("datasource", "projectID", "locationID", "eventDate"); |
|
2005 |
CREATE INDEX ON analytical_stem ("scrubbed_morphospecies_binomial"); |
|
2006 |
$$); |
|
2007 |
$_$; |
|
2008 |
|
|
2009 |
|
|
2010 |
-- |
|
1985 | 2011 |
-- Name: datasource_publish(text, anyelement); Type: FUNCTION; Schema: public; Owner: - |
1986 | 2012 |
-- |
1987 | 2013 |
|
... | ... | |
2141 | 2167 |
|
2142 | 2168 |
|
2143 | 2169 |
-- |
2170 |
-- Name: geoscrub_input_view_modify(); Type: FUNCTION; Schema: public; Owner: - |
|
2171 |
-- |
|
2172 |
|
|
2173 |
CREATE FUNCTION geoscrub_input_view_modify() RETURNS void |
|
2174 |
LANGUAGE sql |
|
2175 |
AS $$ |
|
2176 |
DROP TABLE IF EXISTS geoscrub_input; |
|
2177 |
SELECT util.copy('geoscrub_input_view', 'geoscrub_input'); |
|
2178 |
|
|
2179 |
GRANT SELECT ON TABLE geoscrub_input TO bien_read; |
|
2180 |
GRANT SELECT ON TABLE geoscrub_input_view TO bien_read; |
|
2181 |
$$; |
|
2182 |
|
|
2183 |
|
|
2184 |
-- |
|
2144 | 2185 |
-- Name: location__pull_forward_from_parent(); Type: FUNCTION; Schema: public; Owner: - |
2145 | 2186 |
-- |
2146 | 2187 |
|
... | ... | |
2650 | 2691 |
|
2651 | 2692 |
|
2652 | 2693 |
-- |
2694 |
-- Name: provider_count_view_modify(); Type: FUNCTION; Schema: public; Owner: - |
|
2695 |
-- |
|
2696 |
|
|
2697 |
CREATE FUNCTION provider_count_view_modify() RETURNS void |
|
2698 |
LANGUAGE sql |
|
2699 |
AS $$ |
|
2700 |
DROP TABLE IF EXISTS provider_count; |
|
2701 |
SELECT util.copy('provider_count_view', 'provider_count'); |
|
2702 |
|
|
2703 |
GRANT SELECT ON TABLE provider_count TO bien_read; |
|
2704 |
GRANT SELECT ON TABLE provider_count TO public_; |
|
2705 |
GRANT SELECT ON TABLE provider_count_view TO bien_read; |
|
2706 |
GRANT SELECT ON TABLE provider_count_view TO public_; |
|
2707 |
|
|
2708 |
ALTER TABLE provider_count ADD PRIMARY KEY (dataprovider, dataset); |
|
2709 |
$$; |
|
2710 |
|
|
2711 |
|
|
2712 |
-- |
|
2653 | 2713 |
-- Name: public_schema_publish(text); Type: FUNCTION; Schema: public; Owner: - |
2654 | 2714 |
-- |
2655 | 2715 |
|
... | ... | |
2812 | 2872 |
|
2813 | 2873 |
|
2814 | 2874 |
-- |
2815 |
-- Name: sync_analytical_stem_to_view(); Type: FUNCTION; Schema: public; Owner: -
|
|
2875 |
-- Name: taxon_trait_view_modify(); Type: FUNCTION; Schema: public; Owner: -
|
|
2816 | 2876 |
-- |
2817 | 2877 |
|
2818 |
CREATE FUNCTION sync_analytical_stem_to_view() RETURNS void
|
|
2878 |
CREATE FUNCTION taxon_trait_view_modify() RETURNS void
|
|
2819 | 2879 |
LANGUAGE sql |
2820 |
AS $_$ |
|
2821 |
SELECT util.recreate($$ |
|
2822 |
DROP TABLE IF EXISTS analytical_stem; |
|
2823 |
SELECT util.copy('analytical_stem_view', 'analytical_stem'); |
|
2824 |
ALTER TABLE analytical_stem ADD COLUMN taxon_occurrence__pkey serial PRIMARY KEY; |
|
2825 |
|
|
2826 |
GRANT SELECT ON TABLE analytical_stem TO bien_read; |
|
2827 |
GRANT SELECT ON TABLE analytical_stem_view TO bien_read; |
|
2828 |
|
|
2829 |
ALTER TABLE analytical_stem ALTER COLUMN "datasource" SET NOT NULL; |
|
2830 |
ALTER TABLE analytical_stem ALTER COLUMN "locationID" SET NOT NULL; |
|
2831 |
|
|
2832 |
CREATE INDEX ON analytical_stem ("datasource", "occurrenceID"); |
|
2833 |
CREATE INDEX ON analytical_stem ("datasource", "specimenHolderInstitutions", "collection", "accessionNumber"); |
|
2834 |
CREATE INDEX ON analytical_stem ("datasource", "projectID", "locationID", "eventDate"); |
|
2835 |
CREATE INDEX ON analytical_stem ("scrubbed_morphospecies_binomial"); |
|
2836 |
$$); |
|
2837 |
$_$; |
|
2838 |
|
|
2839 |
|
|
2840 |
-- |
|
2841 |
-- Name: sync_geoscrub_input_to_view(); Type: FUNCTION; Schema: public; Owner: - |
|
2842 |
-- |
|
2843 |
|
|
2844 |
CREATE FUNCTION sync_geoscrub_input_to_view() RETURNS void |
|
2845 |
LANGUAGE sql |
|
2846 | 2880 |
AS $$ |
2847 |
DROP TABLE IF EXISTS geoscrub_input; |
|
2848 |
SELECT util.copy('geoscrub_input_view', 'geoscrub_input'); |
|
2849 |
|
|
2850 |
GRANT SELECT ON TABLE geoscrub_input TO bien_read; |
|
2851 |
GRANT SELECT ON TABLE geoscrub_input_view TO bien_read; |
|
2852 |
$$; |
|
2853 |
|
|
2854 |
|
|
2855 |
-- |
|
2856 |
-- Name: sync_provider_count_to_view(); Type: FUNCTION; Schema: public; Owner: - |
|
2857 |
-- |
|
2858 |
|
|
2859 |
CREATE FUNCTION sync_provider_count_to_view() RETURNS void |
|
2860 |
LANGUAGE sql |
|
2861 |
AS $$ |
|
2862 |
DROP TABLE IF EXISTS provider_count; |
|
2863 |
SELECT util.copy('provider_count_view', 'provider_count'); |
|
2864 |
|
|
2865 |
GRANT SELECT ON TABLE provider_count TO bien_read; |
|
2866 |
GRANT SELECT ON TABLE provider_count TO public_; |
|
2867 |
GRANT SELECT ON TABLE provider_count_view TO bien_read; |
|
2868 |
GRANT SELECT ON TABLE provider_count_view TO public_; |
|
2869 |
|
|
2870 |
ALTER TABLE provider_count ADD PRIMARY KEY (dataprovider, dataset); |
|
2871 |
$$; |
|
2872 |
|
|
2873 |
|
|
2874 |
-- |
|
2875 |
-- Name: sync_taxon_trait_to_view(); Type: FUNCTION; Schema: public; Owner: - |
|
2876 |
-- |
|
2877 |
|
|
2878 |
CREATE FUNCTION sync_taxon_trait_to_view() RETURNS void |
|
2879 |
LANGUAGE sql |
|
2880 |
AS $$ |
|
2881 | 2881 |
DROP TABLE IF EXISTS taxon_trait; |
2882 | 2882 |
SELECT util.copy('taxon_trait_view', 'taxon_trait'); |
2883 | 2883 |
|
... | ... | |
6802 | 6802 |
|
6803 | 6803 |
COMMENT ON VIEW analytical_stem_view IS ' |
6804 | 6804 |
after updating this: |
6805 |
SELECT sync_analytical_stem_to_view()
|
|
6805 |
SELECT analytical_stem_view_modify()
|
|
6806 | 6806 |
add applicable columns to analytical_specimen, analytical_plot |
6807 | 6807 |
'; |
6808 | 6808 |
|
Also available in: Unified diff
schemas/public_.sql: sync_*(): renamed to *_modify() to facilitate finding these functions when modifying the corresponding view (using the new naming convention for a view's on-modify function)