From 48596ab3c37652a006d1c55ab39dce27888ae090 Mon Sep 17 00:00:00 2001 From: "David M. Syzdek" Date: Thu, 23 May 2019 21:12:45 -0800 Subject: Updating names of SQL triggers --- notes/projects/radius/sql/ippool-schema.sql | 130 ++++++++++++++++++++++------ 1 file changed, 104 insertions(+), 26 deletions(-) (limited to 'notes') diff --git a/notes/projects/radius/sql/ippool-schema.sql b/notes/projects/radius/sql/ippool-schema.sql index 9a92ef8..00288b0 100644 --- a/notes/projects/radius/sql/ippool-schema.sql +++ b/notes/projects/radius/sql/ippool-schema.sql @@ -231,7 +231,7 @@ CREATE INDEX /* * trigger functions for ip table */ -CREATE OR REPLACE FUNCTION ip_trigger_delete +CREATE OR REPLACE FUNCTION ip_before_delete ( ) RETURNS trigger LANGUAGE plpgsql @@ -249,7 +249,7 @@ END; $$; -CREATE OR REPLACE FUNCTION ip_trigger_insert +CREATE OR REPLACE FUNCTION ip_before_insert ( ) RETURNS trigger LANGUAGE plpgsql @@ -262,6 +262,15 @@ BEGIN NEW.nasId := NULL; NEW.modified := TIMESTAMP 'epoch'; + -- verifies netblock contains IP address + PERFORM id + FROM netblock + WHERE id = NEW.netblockId + AND network >>= NEW.address; + IF NOT FOUND THEN + RAISE EXCEPTION 'invalid netblock'; + END IF; + -- determines mask for single IP address IF ( family(NEW.address) != 4 ) THEN RAISE EXCEPTION 'only IPv4 addresses are supported'; @@ -272,7 +281,7 @@ END; $$; -CREATE OR REPLACE FUNCTION ip_trigger_truncate +CREATE OR REPLACE FUNCTION ip_before_truncate ( ) RETURNS trigger LANGUAGE plpgsql @@ -284,12 +293,13 @@ END; $$; -CREATE OR REPLACE FUNCTION ip_trigger_update +CREATE OR REPLACE FUNCTION ip_before_update ( ) RETURNS trigger LANGUAGE plpgsql AS $$ DECLARE + ipAssignable BOOLEAN; BEGIN -- prevents IP address from being modified @@ -297,8 +307,32 @@ BEGIN RAISE EXCEPTION 'address cannot be modified'; END IF; + -- verifies netblock contains IP address + IF ( NEW.netblockId != NEW.netblockId ) THEN + SELECT assignable INTO ipAssignable + FROM netblock + WHERE id = NEW.netblockId + AND network >>= NEW.address; + IF ipAssignable IS NULL THEN + RAISE EXCEPTION 'invalid netblock'; + END IF; + IF ipAssignable IS TRUE THEN + PERFORM id + FROM netblock + WHERE assignable IS TRUE + AND network >>= NEW.address; + IF FOUND THEN + RAISE EXCEPTION 'cannot associate assignable netblock if an unassignable netblock contains IP address'; + END IF; + END IF; + END IF; + -- update timestamp - NEW.modified := current_timestamp; + IF ( ( OLD.nasId != NEW.nasId ) OR ( OLD.username != NEW.username ) ) THEN + NEW.modified := current_timestamp; + ELSE + NEW.modified := OLD.modified; + END IF; RETURN NEW; END; @@ -308,7 +342,7 @@ $$; /* * trigger functions for nas table */ -CREATE OR REPLACE FUNCTION nas_trigger_insert_or_update +CREATE OR REPLACE FUNCTION nas_before_insert_or_update ( ) RETURNS trigger LANGUAGE plpgsql @@ -327,7 +361,7 @@ $$; /* * trigger functions for netblock table */ -CREATE OR REPLACE FUNCTION netblock_trigger_insert_or_update +CREATE OR REPLACE FUNCTION netblock_before_insert_or_update ( ) RETURNS trigger LANGUAGE plpgsql @@ -353,7 +387,7 @@ BEGIN RAISE EXCEPTION 'conflict with existing network regions'; END IF; - -- verifies that new region does not conflict with new network + -- verifies that new pool does not conflict with new network SELECT count(id) INTO resultCount FROM netblock WHERE poolId != NEW.poolId @@ -366,6 +400,45 @@ BEGIN RAISE EXCEPTION 'conflict with existing network pools'; END IF; + -- verifies that IP address will not be orphaned + PERFORM netblockId + FROM ip + WHERE ip.netblockId = NEW.id + AND NOT ip.address <<= NEW.network; + IF FOUND THEN + RAISE EXCEPTION 'changing network will orphan existing IP addresses'; + END IF; + + -- updates IP Association of netblock if an update and is now assignable + IF ( ( OLD.assignable IS FALSE ) AND ( NEW.assignable IS TRUE ) ) THEN + UPDATE ip + SET netblockId = nb.id + FROM ip + INNER JOIN netblock AS nb + ON nb.network >>= ip.address + WHERE ip.address <<= NEW.network + AND nb.assignable IS TRUE; + END IF; + + RETURN NEW; +END; +$$; + + +CREATE OR REPLACE FUNCTION netblock_after_insert_or_update +( ) + RETURNS trigger + LANGUAGE plpgsql + AS $$ +DECLARE +BEGIN + -- updates IP association of netblock is not assignable + IF ( NEW.assignable IS FALSE ) THEN + UPDATE ip + SET netblockId = NEW.id + WHERE address <<= NEW.network; + END IF; + RETURN NEW; END; $$; @@ -374,7 +447,7 @@ $$; /* * trigger functions for pool table */ -CREATE OR REPLACE FUNCTION pool_trigger_insert_or_update +CREATE OR REPLACE FUNCTION pool_before_insert_or_update ( ) RETURNS trigger LANGUAGE plpgsql @@ -393,7 +466,7 @@ $$; /* * trigger functions for region table */ -CREATE OR REPLACE FUNCTION region_trigger_insert_or_update +CREATE OR REPLACE FUNCTION region_before_insert_or_update ( ) RETURNS trigger LANGUAGE plpgsql @@ -416,58 +489,63 @@ $$; -- //////////////// -- address table triggers -CREATE TRIGGER ip_trigger_delete +CREATE TRIGGER ip_before_delete BEFORE DELETE ON ip FOR EACH ROW - EXECUTE PROCEDURE ip_trigger_delete(); -CREATE TRIGGER ip_trigger_insert + EXECUTE PROCEDURE ip_before_delete(); +CREATE TRIGGER ip_before_insert BEFORE INSERT ON ip FOR EACH ROW - EXECUTE PROCEDURE ip_trigger_insert(); -CREATE TRIGGER ip_trigger_truncate + EXECUTE PROCEDURE ip_before_insert(); +CREATE TRIGGER ip_before_truncate BEFORE TRUNCATE ON ip FOR EACH STATEMENT - EXECUTE PROCEDURE ip_trigger_truncate(); -CREATE TRIGGER ip_trigger_update + EXECUTE PROCEDURE ip_before_truncate(); +CREATE TRIGGER ip_before_update BEFORE UPDATE ON ip FOR EACH ROW - EXECUTE PROCEDURE ip_trigger_update(); + EXECUTE PROCEDURE ip_before_update(); -- netblock table triggers -CREATE TRIGGER netblock_trigger_insert_or_update +CREATE TRIGGER netblock_before_insert_or_update BEFORE INSERT OR UPDATE ON netblock FOR EACH ROW - EXECUTE PROCEDURE netblock_trigger_insert_or_update(); + EXECUTE PROCEDURE netblock_before_insert_or_update(); +CREATE TRIGGER netblock_after_insert_or_update + AFTER INSERT OR UPDATE + ON netblock + FOR EACH ROW + EXECUTE PROCEDURE netblock_after_insert_or_update(); -- nas table triggers -CREATE TRIGGER nas_trigger_insert_or_update +CREATE TRIGGER nas_before_insert_or_update BEFORE INSERT OR UPDATE ON nas FOR EACH ROW - EXECUTE PROCEDURE nas_trigger_insert_or_update(); + EXECUTE PROCEDURE nas_before_insert_or_update(); -- pool table triggers -CREATE TRIGGER pool_trigger_insert_or_update +CREATE TRIGGER pool_before_insert_or_update BEFORE INSERT OR UPDATE ON pool FOR EACH ROW - EXECUTE PROCEDURE pool_trigger_insert_or_update(); + EXECUTE PROCEDURE pool_before_insert_or_update(); -- region table triggers -CREATE TRIGGER region_trigger_insert_or_update +CREATE TRIGGER region_before_insert_or_update BEFORE INSERT OR UPDATE ON region FOR EACH ROW - EXECUTE PROCEDURE region_trigger_insert_or_update(); + EXECUTE PROCEDURE region_before_insert_or_update(); /* end of sql */ -- cgit v1.2.3