Revision 12631
Added by Aaron Marcuse-Kubitza almost 11 years ago
| trunk/schemas/vegbien.my.sql | ||
|---|---|---|
| 828 | 828 |
|
| 829 | 829 |
|
| 830 | 830 |
-- |
| 831 |
-- Name: locationevent_set_plot_id(); Type: FUNCTION; Schema: public; Owner: - |
|
| 832 |
-- |
|
| 833 |
|
|
| 834 |
|
|
| 835 |
|
|
| 836 |
|
|
| 837 |
-- |
|
| 831 | 838 |
-- Name: locationplace__set_location_current_place(); Type: FUNCTION; Schema: public; Owner: - |
| 832 | 839 |
-- |
| 833 | 840 |
|
| ... | ... | |
| 2290 | 2297 |
accesslevel varchar(255), |
| 2291 | 2298 |
place_visit_id int(11) NOT NULL, |
| 2292 | 2299 |
parent_id int(11), |
| 2300 |
plot_id int(11), |
|
| 2293 | 2301 |
location_id int(11), |
| 2294 | 2302 |
project_id int(11), |
| 2295 | 2303 |
stratum_id int(11), |
| ... | ... | |
| 2373 | 2381 |
|
| 2374 | 2382 |
|
| 2375 | 2383 |
-- |
| 2384 |
-- Name: COLUMN locationevent.place_visit_id; Type: COMMENT; Schema: public; Owner: - |
|
| 2385 |
-- |
|
| 2386 |
|
|
| 2387 |
|
|
| 2388 |
|
|
| 2389 |
|
|
| 2390 |
-- |
|
| 2391 |
-- Name: COLUMN locationevent.plot_id; Type: COMMENT; Schema: public; Owner: - |
|
| 2392 |
-- |
|
| 2393 |
|
|
| 2394 |
|
|
| 2395 |
|
|
| 2396 |
|
|
| 2397 |
-- |
|
| 2376 | 2398 |
-- Name: method; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 2377 | 2399 |
-- |
| 2378 | 2400 |
|
| ... | ... | |
| 8722 | 8744 |
|
| 8723 | 8745 |
|
| 8724 | 8746 |
-- |
| 8747 |
-- Name: locationevent_plot_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 8748 |
-- |
|
| 8749 |
|
|
| 8750 |
CREATE INDEX locationevent_plot_id ON locationevent (plot_id); |
|
| 8751 |
|
|
| 8752 |
|
|
| 8753 |
-- |
|
| 8725 | 8754 |
-- Name: locationevent_previous_id_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 8726 | 8755 |
-- |
| 8727 | 8756 |
|
| ... | ... | |
| 9520 | 9549 |
|
| 9521 | 9550 |
|
| 9522 | 9551 |
-- |
| 9552 |
-- Name: locationevent_set_plot_id; Type: TRIGGER; Schema: public; Owner: - |
|
| 9553 |
-- |
|
| 9554 |
|
|
| 9555 |
|
|
| 9556 |
|
|
| 9557 |
|
|
| 9558 |
-- |
|
| 9523 | 9559 |
-- Name: locationplace__set_location_current_place; Type: TRIGGER; Schema: public; Owner: - |
| 9524 | 9560 |
-- |
| 9525 | 9561 |
|
| ... | ... | |
| 9951 | 9987 |
|
| 9952 | 9988 |
|
| 9953 | 9989 |
-- |
| 9990 |
-- Name: locationevent_plot_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 9991 |
-- |
|
| 9992 |
|
|
| 9993 |
ALTER TABLE locationevent |
|
| 9994 |
ADD CONSTRAINT locationevent_plot_id_fkey FOREIGN KEY (plot_id) REFERENCES location(location_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 9995 |
|
|
| 9996 |
|
|
| 9997 |
-- |
|
| 9954 | 9998 |
-- Name: locationevent_previous_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 9955 | 9999 |
-- |
| 9956 | 10000 |
|
| trunk/schemas/vegbien.sql | ||
|---|---|---|
| 1498 | 1498 |
|
| 1499 | 1499 |
|
| 1500 | 1500 |
-- |
| 1501 |
-- Name: locationevent_set_plot_id(); Type: FUNCTION; Schema: public; Owner: - |
|
| 1502 |
-- |
|
| 1503 |
|
|
| 1504 |
CREATE FUNCTION locationevent_set_plot_id() RETURNS trigger |
|
| 1505 |
LANGUAGE plpgsql |
|
| 1506 |
AS $$ |
|
| 1507 |
BEGIN |
|
| 1508 |
new.plot_id = (SELECT plot_location_id FROM location |
|
| 1509 |
WHERE location_id = new.location_id); |
|
| 1510 |
RETURN new; |
|
| 1511 |
END; |
|
| 1512 |
$$; |
|
| 1513 |
|
|
| 1514 |
|
|
| 1515 |
-- |
|
| 1501 | 1516 |
-- Name: locationplace__set_location_current_place(); Type: FUNCTION; Schema: public; Owner: - |
| 1502 | 1517 |
-- |
| 1503 | 1518 |
|
| ... | ... | |
| 4171 | 4186 |
accesslevel accesslevel, |
| 4172 | 4187 |
place_visit_id integer NOT NULL, |
| 4173 | 4188 |
parent_id integer, |
| 4189 |
plot_id integer, |
|
| 4174 | 4190 |
location_id integer, |
| 4175 | 4191 |
project_id integer, |
| 4176 | 4192 |
stratum_id integer, |
| ... | ... | |
| 4261 | 4277 |
|
| 4262 | 4278 |
|
| 4263 | 4279 |
-- |
| 4280 |
-- Name: COLUMN locationevent.place_visit_id; Type: COMMENT; Schema: public; Owner: - |
|
| 4281 |
-- |
|
| 4282 |
|
|
| 4283 |
COMMENT ON COLUMN locationevent.place_visit_id IS ' |
|
| 4284 |
autopopulated |
|
| 4285 |
'; |
|
| 4286 |
|
|
| 4287 |
|
|
| 4288 |
-- |
|
| 4289 |
-- Name: COLUMN locationevent.plot_id; Type: COMMENT; Schema: public; Owner: - |
|
| 4290 |
-- |
|
| 4291 |
|
|
| 4292 |
COMMENT ON COLUMN locationevent.plot_id IS ' |
|
| 4293 |
autopopulated |
|
| 4294 |
'; |
|
| 4295 |
|
|
| 4296 |
|
|
| 4297 |
-- |
|
| 4264 | 4298 |
-- Name: method; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 4265 | 4299 |
-- |
| 4266 | 4300 |
|
| ... | ... | |
| 11653 | 11687 |
|
| 11654 | 11688 |
|
| 11655 | 11689 |
-- |
| 11690 |
-- Name: locationevent_plot_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 11691 |
-- |
|
| 11692 |
|
|
| 11693 |
CREATE INDEX locationevent_plot_id ON locationevent USING btree (plot_id); |
|
| 11694 |
|
|
| 11695 |
|
|
| 11696 |
-- |
|
| 11656 | 11697 |
-- Name: locationevent_previous_id_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 11657 | 11698 |
-- |
| 11658 | 11699 |
|
| ... | ... | |
| 12451 | 12492 |
|
| 12452 | 12493 |
|
| 12453 | 12494 |
-- |
| 12495 |
-- Name: locationevent_set_plot_id; Type: TRIGGER; Schema: public; Owner: - |
|
| 12496 |
-- |
|
| 12497 |
|
|
| 12498 |
CREATE TRIGGER locationevent_set_plot_id BEFORE INSERT OR UPDATE OF locationevent_id, plot_id, location_id ON locationevent FOR EACH ROW EXECUTE PROCEDURE locationevent_set_plot_id(); |
|
| 12499 |
|
|
| 12500 |
|
|
| 12501 |
-- |
|
| 12454 | 12502 |
-- Name: locationplace__set_location_current_place; Type: TRIGGER; Schema: public; Owner: - |
| 12455 | 12503 |
-- |
| 12456 | 12504 |
|
| ... | ... | |
| 12899 | 12947 |
|
| 12900 | 12948 |
|
| 12901 | 12949 |
-- |
| 12950 |
-- Name: locationevent_plot_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 12951 |
-- |
|
| 12952 |
|
|
| 12953 |
ALTER TABLE ONLY locationevent |
|
| 12954 |
ADD CONSTRAINT locationevent_plot_id_fkey FOREIGN KEY (plot_id) REFERENCES location(location_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 12955 |
|
|
| 12956 |
|
|
| 12957 |
-- |
|
| 12902 | 12958 |
-- Name: locationevent_previous_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 12903 | 12959 |
-- |
| 12904 | 12960 |
|
Also available in: Unified diff
schemas/vegbien.sql: locationevent: added autopopulated plot_id column which points to the outermost plot of the locationevent's location