Revision 12523
Added by Aaron Marcuse-Kubitza almost 11 years ago
| vegbien.sql | ||
|---|---|---|
| 5006 | 5006 |
|
| 5007 | 5007 |
|
| 5008 | 5008 |
-- |
| 5009 |
-- Name: plot; Type: VIEW; Schema: public; Owner: - |
|
| 5010 |
-- |
|
| 5011 |
|
|
| 5012 |
CREATE VIEW plot AS |
|
| 5013 |
SELECT location.location_id, |
|
| 5014 |
location.source_id, |
|
| 5015 |
location.sourceaccessioncode, |
|
| 5016 |
location.plot_location_id AS plot, |
|
| 5017 |
location.parent_id, |
|
| 5018 |
location.authorlocationcode, |
|
| 5019 |
location.place_id, |
|
| 5020 |
location.accesslevel, |
|
| 5021 |
location.accessconditions, |
|
| 5022 |
location.sublocationxposition_m, |
|
| 5023 |
location.sublocationyposition_m, |
|
| 5024 |
location.iscultivated, |
|
| 5025 |
location.authorzone, |
|
| 5026 |
location.authordatum, |
|
| 5027 |
location.authorlocation, |
|
| 5028 |
location.locationnarrative, |
|
| 5029 |
location.azimuth, |
|
| 5030 |
location.shape, |
|
| 5031 |
location.area_m2, |
|
| 5032 |
location.standsize, |
|
| 5033 |
location.placementmethod, |
|
| 5034 |
location.permanence, |
|
| 5035 |
location.layoutnarrative, |
|
| 5036 |
location.elevation_m, |
|
| 5037 |
location.elevationaccuracy_m, |
|
| 5038 |
location.elevationrange_m, |
|
| 5039 |
location.verbatimelevation, |
|
| 5040 |
location.slopeaspect_deg, |
|
| 5041 |
location.minslopeaspect_deg, |
|
| 5042 |
location.maxslopeaspect_deg, |
|
| 5043 |
location.slopegradient_fraction, |
|
| 5044 |
location.minslopegradient_fraction, |
|
| 5045 |
location.maxslopegradient_fraction, |
|
| 5046 |
location.topoposition, |
|
| 5047 |
location.landform, |
|
| 5048 |
location.surficialdeposits, |
|
| 5049 |
location.rocktype, |
|
| 5050 |
location.submitter_surname, |
|
| 5051 |
location.submitter_givenname, |
|
| 5052 |
location.submitter_email, |
|
| 5053 |
location.notespublic, |
|
| 5054 |
location.notesmgt, |
|
| 5055 |
location.revisions, |
|
| 5056 |
location.dateentered, |
|
| 5057 |
location.locationrationalenarrative |
|
| 5058 |
FROM location |
|
| 5059 |
WHERE (location.parent_id IS NULL); |
|
| 5060 |
|
|
| 5061 |
|
|
| 5062 |
-- |
|
| 5063 |
-- Name: VIEW plot; Type: COMMENT; Schema: public; Owner: - |
|
| 5064 |
-- |
|
| 5065 |
|
|
| 5066 |
COMMENT ON VIEW plot IS ' |
|
| 5067 |
when updating, use * as the column list |
|
| 5068 |
'; |
|
| 5069 |
|
|
| 5070 |
|
|
| 5071 |
-- |
|
| 5009 | 5072 |
-- Name: project_project_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
| 5010 | 5073 |
-- |
| 5011 | 5074 |
|
| ... | ... | |
| 5819 | 5882 |
|
| 5820 | 5883 |
|
| 5821 | 5884 |
-- |
| 5822 |
-- Name: top_plot; Type: VIEW; Schema: public; Owner: - |
|
| 5823 |
-- |
|
| 5824 |
|
|
| 5825 |
CREATE VIEW top_plot AS |
|
| 5826 |
SELECT location.location_id, |
|
| 5827 |
location.source_id, |
|
| 5828 |
location.sourceaccessioncode, |
|
| 5829 |
location.plot_location_id AS top_plot, |
|
| 5830 |
location.parent_id, |
|
| 5831 |
location.authorlocationcode, |
|
| 5832 |
location.place_id, |
|
| 5833 |
location.accesslevel, |
|
| 5834 |
location.accessconditions, |
|
| 5835 |
location.sublocationxposition_m, |
|
| 5836 |
location.sublocationyposition_m, |
|
| 5837 |
location.iscultivated, |
|
| 5838 |
location.authorzone, |
|
| 5839 |
location.authordatum, |
|
| 5840 |
location.authorlocation, |
|
| 5841 |
location.locationnarrative, |
|
| 5842 |
location.azimuth, |
|
| 5843 |
location.shape, |
|
| 5844 |
location.area_m2, |
|
| 5845 |
location.standsize, |
|
| 5846 |
location.placementmethod, |
|
| 5847 |
location.permanence, |
|
| 5848 |
location.layoutnarrative, |
|
| 5849 |
location.elevation_m, |
|
| 5850 |
location.elevationaccuracy_m, |
|
| 5851 |
location.elevationrange_m, |
|
| 5852 |
location.verbatimelevation, |
|
| 5853 |
location.slopeaspect_deg, |
|
| 5854 |
location.minslopeaspect_deg, |
|
| 5855 |
location.maxslopeaspect_deg, |
|
| 5856 |
location.slopegradient_fraction, |
|
| 5857 |
location.minslopegradient_fraction, |
|
| 5858 |
location.maxslopegradient_fraction, |
|
| 5859 |
location.topoposition, |
|
| 5860 |
location.landform, |
|
| 5861 |
location.surficialdeposits, |
|
| 5862 |
location.rocktype, |
|
| 5863 |
location.submitter_surname, |
|
| 5864 |
location.submitter_givenname, |
|
| 5865 |
location.submitter_email, |
|
| 5866 |
location.notespublic, |
|
| 5867 |
location.notesmgt, |
|
| 5868 |
location.revisions, |
|
| 5869 |
location.dateentered, |
|
| 5870 |
location.locationrationalenarrative |
|
| 5871 |
FROM location |
|
| 5872 |
WHERE (location.parent_id IS NULL); |
|
| 5873 |
|
|
| 5874 |
|
|
| 5875 |
-- |
|
| 5876 |
-- Name: VIEW top_plot; Type: COMMENT; Schema: public; Owner: - |
|
| 5877 |
-- |
|
| 5878 |
|
|
| 5879 |
COMMENT ON VIEW top_plot IS ' |
|
| 5880 |
when updating, use * as the column list |
|
| 5881 |
'; |
|
| 5882 |
|
|
| 5883 |
|
|
| 5884 |
-- |
|
| 5885 | 5885 |
-- Name: trait_trait_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
| 5886 | 5886 |
-- |
| 5887 | 5887 |
|
| ... | ... | |
| 5993 | 5993 |
|
| 5994 | 5994 |
CREATE VIEW _plots_03_count_of_all_plots_in_this_source AS |
| 5995 | 5995 |
SELECT count(*) AS plots |
| 5996 |
FROM (((public.top_plot l
|
|
| 5996 |
FROM (((public.plot l |
|
| 5997 | 5997 |
JOIN public.locationevent le ON ((l.location_id = le.location_id))) |
| 5998 | 5998 |
JOIN public.project p ON ((p.project_id = le.project_id))) |
| 5999 | 5999 |
JOIN public.source s ON ((p.source_id = s.source_id))) |
| ... | ... | |
| 6007 | 6007 |
CREATE VIEW _plots_04_count_of_plots_in_each_project_in_this_source AS |
| 6008 | 6008 |
SELECT p.projectname AS project_name, |
| 6009 | 6009 |
count(DISTINCT l.location_id) AS plots |
| 6010 |
FROM (((public.top_plot l
|
|
| 6010 |
FROM (((public.plot l |
|
| 6011 | 6011 |
JOIN public.locationevent le ON ((l.location_id = le.location_id))) |
| 6012 | 6012 |
JOIN public.project p ON ((p.project_id = le.project_id))) |
| 6013 | 6013 |
JOIN public.source s ON ((p.source_id = s.source_id))) |
| ... | ... | |
| 6035 | 6035 |
|
| 6036 | 6036 |
CREATE VIEW _plots_06_list_of_plots_with_stem_measurements AS |
| 6037 | 6037 |
SELECT project.projectname AS project_name, |
| 6038 |
top_plot.authorlocationcode AS "SiteCode"
|
|
| 6039 |
FROM ((public.top_plot
|
|
| 6038 |
plot.authorlocationcode AS "SiteCode" |
|
| 6039 |
FROM ((public.plot |
|
| 6040 | 6040 |
JOIN public.locationevent USING (location_id)) |
| 6041 | 6041 |
JOIN public.project USING (project_id)) |
| 6042 |
WHERE ((top_plot.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND (EXISTS ( SELECT NULL::unknown AS unknown
|
|
| 6042 |
WHERE ((plot.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND (EXISTS ( SELECT NULL::unknown AS unknown
|
|
| 6043 | 6043 |
FROM (((((public.location |
| 6044 | 6044 |
JOIN public.locationevent locationevent_1 USING (location_id)) |
| 6045 | 6045 |
JOIN public.taxonoccurrence USING (locationevent_id)) |
| 6046 | 6046 |
JOIN public.aggregateoccurrence USING (taxonoccurrence_id)) |
| 6047 | 6047 |
JOIN public.plantobservation USING (aggregateoccurrence_id)) |
| 6048 | 6048 |
JOIN public.stemobservation USING (plantobservation_id)) |
| 6049 |
WHERE (location.plot_location_id = top_plot.location_id)
|
|
| 6049 |
WHERE (location.plot_location_id = plot.location_id) |
|
| 6050 | 6050 |
LIMIT 1))) |
| 6051 |
ORDER BY top_plot.authorlocationcode;
|
|
| 6051 |
ORDER BY plot.authorlocationcode; |
|
| 6052 | 6052 |
|
| 6053 | 6053 |
|
| 6054 | 6054 |
-- |
| ... | ... | |
| 13062 | 13062 |
|
| 13063 | 13063 |
|
| 13064 | 13064 |
-- |
| 13065 |
-- Name: plot; Type: ACL; Schema: public; Owner: - |
|
| 13066 |
-- |
|
| 13067 |
|
|
| 13068 |
REVOKE ALL ON TABLE plot FROM PUBLIC; |
|
| 13069 |
REVOKE ALL ON TABLE plot FROM bien; |
|
| 13070 |
GRANT ALL ON TABLE plot TO bien; |
|
| 13071 |
GRANT SELECT ON TABLE plot TO bien_read; |
|
| 13072 |
|
|
| 13073 |
|
|
| 13074 |
-- |
|
| 13065 | 13075 |
-- Name: projectcontributor; Type: ACL; Schema: public; Owner: - |
| 13066 | 13076 |
-- |
| 13067 | 13077 |
|
| ... | ... | |
| 13285 | 13295 |
|
| 13286 | 13296 |
|
| 13287 | 13297 |
-- |
| 13288 |
-- Name: top_plot; Type: ACL; Schema: public; Owner: - |
|
| 13289 |
-- |
|
| 13290 |
|
|
| 13291 |
REVOKE ALL ON TABLE top_plot FROM PUBLIC; |
|
| 13292 |
REVOKE ALL ON TABLE top_plot FROM bien; |
|
| 13293 |
GRANT ALL ON TABLE top_plot TO bien; |
|
| 13294 |
GRANT SELECT ON TABLE top_plot TO bien_read; |
|
| 13295 |
|
|
| 13296 |
|
|
| 13297 |
-- |
|
| 13298 | 13298 |
-- Name: userdefined; Type: ACL; Schema: public; Owner: - |
| 13299 | 13299 |
-- |
| 13300 | 13300 |
|
Also available in: Unified diff
schemas/vegbien.sql: top_plot view: renamed to plot, as requested by Brad (wiki.vegpath.org/2014-02-27_conference_call#schema-changes)