From 543eb7bb4e8fad6e0ae80eb63f50dc44fccbe2c7 Mon Sep 17 00:00:00 2001 From: "David M. Syzdek" Date: Thu, 23 May 2019 22:02:02 -0800 Subject: replacing netblock.assignable with netblock.busyout --- notes/projects/radius/sql/ippool-demo.sql | 13 +-- notes/projects/radius/sql/ippool-functions.sql | 92 +++++++++--------- notes/projects/radius/sql/ippool-schema.sql | 47 +++++---- notes/projects/radius/sql/untitled.sql | 129 +++++++++++++++++++++++++ 4 files changed, 207 insertions(+), 74 deletions(-) create mode 100644 notes/projects/radius/sql/untitled.sql diff --git a/notes/projects/radius/sql/ippool-demo.sql b/notes/projects/radius/sql/ippool-demo.sql index 3693c7a..57352d8 100644 --- a/notes/projects/radius/sql/ippool-demo.sql +++ b/notes/projects/radius/sql/ippool-demo.sql @@ -21,12 +21,13 @@ INSERT INTO pool ( name ) VALUES ( 'reserved' ); INSERT INTO region ( name ) VALUES ( 'Anchorage' ); INSERT INTO region ( name ) VALUES ( 'Soldotna' ); INSERT INTO region ( name ) VALUES ( 'Fairbanks' ); -INSERT INTO region ( name ) VALUES ( 'No where' ); +INSERT INTO region ( name ) VALUES ( 'Juneau' ); +INSERT INTO region ( name ) VALUES ( 'Lab' ); -- create example NAS clients SELECT nas_add( nasName := 'br1.cwc', regionName := 'Anchorage' ); -SELECT nas_add( nasName := 'bras.eng', regionName := 'Anchorage' ); +SELECT nas_add( nasName := 'bras.eng', regionName := 'Lab' ); SELECT nas_add( nasName := 'br1.sol', regionName := 'Soldotna' ); @@ -41,7 +42,7 @@ SELECT netblock_add poolName := 'DHCP', regionName := 'Soldotna', netDesc := 'Reserved for engineering test accounts', - netAssignable := FALSE + netBusyout := TRUE ); SELECT netblock_add ( netAddress := '209.193.4.4/30', @@ -75,19 +76,19 @@ SELECT netblock_add SELECT netblock_add ( netAddress := '209.193.4.96/28', poolName := 'DHCP', - netAssignable := FALSE, + netBusyout := TRUE, addIpEntries := FALSE ); SELECT netblock_add ( netAddress := '209.193.4.112/28', poolName := 'PPPoE', - netAssignable := FALSE, + netBusyout := TRUE, addIpEntries := FALSE ); SELECT netblock_add ( netAddress := '209.193.4.128/25', poolName := 'Reserved', - netAssignable := FALSE, + netBusyout := TRUE, addIpEntries := FALSE ); diff --git a/notes/projects/radius/sql/ippool-functions.sql b/notes/projects/radius/sql/ippool-functions.sql index dc91396..7ec18a6 100644 --- a/notes/projects/radius/sql/ippool-functions.sql +++ b/notes/projects/radius/sql/ippool-functions.sql @@ -175,7 +175,7 @@ BEGIN PERFORM network FROM netblock WHERE netblock.network >>= assignIp - AND netblock.assignable = FALSE; + AND netblock.busyout = TRUE; IF FOUND THEN RETURN FALSE; END IF; @@ -228,7 +228,7 @@ CREATE OR REPLACE FUNCTION ip_audit poolName VARCHAR(32), regionName VARCHAR(32), ipAddress INET, - assignable BOOLEAN, + busyout BOOLEAN, assigned BOOLEAN, modified TIMESTAMP WITH TIME ZONE, nasName VARCHAR(32), @@ -244,9 +244,9 @@ BEGIN SELECT DISTINCT pool.name AS poolName, region.name AS regionName, ip.address AS address, - TRUE = ALL + TRUE = ANY ( - SELECT nb2.assignable + SELECT nb2.busyout FROM netblock AS nb2 WHERE ip.address <<= nb2.network ) AS assignable, @@ -561,7 +561,7 @@ CREATE OR REPLACE FUNCTION netblock_add netAddress CIDR, poolName VARCHAR(32), regionName VARCHAR(32) DEFAULT NULL, - netAssignable BOOLEAN DEFAULT TRUE, + netBusyout BOOLEAN DEFAULT FALSE, netDesc VARCHAR(128) DEFAULT NULL, addIpEntries BOOLEAN DEFAULT TRUE ) @@ -600,13 +600,13 @@ BEGIN ( network, poolId, regionId, - assignable, + busyout, description ) VALUES ( netAddress, netPoolId, netRegionId, - netAssignable, + netBusyout, netDesc ) RETURNING id INTO returnId; @@ -636,7 +636,7 @@ CREATE OR REPLACE FUNCTION netblock_audit poolName VARCHAR(32), regionName VARCHAR(32), network CIDR, - assignable BOOLEAN, + busyout BOOLEAN, ipAssigned BIGINT, ipUnassigned BIGINT, ipAvailable BIGINT, @@ -653,7 +653,7 @@ BEGIN SELECT DISTINCT pool.name AS poolName, region.name AS regionname, netblock.network AS network, - netblock.assignable AS assignable, + netblock.busyout AS busyout, ( SELECT count(address) FROM ip @@ -671,7 +671,7 @@ BEGIN FROM ip INNER JOIN netblock AS nb2 ON nb2.id = ip.netblockId - WHERE nb2.assignable IS TRUE + WHERE nb2.busyout IS FALSE AND ip.address <<= netblock.network AND ip.nasId IS NULL ) AS ipAvailable, @@ -705,10 +705,10 @@ CREATE OR REPLACE FUNCTION region_audit ( poolName VARCHAR(32), regionName VARCHAR(32), - ipAssigned BIGINT, - ipUnassigned BIGINT, + ipAssignedFree BIGINT, + ipAssignedBusy BIGINT, ipAvailable BIGINT, - ipUnassignable BIGINT, + ipBusyout BIGINT, ipTotal BIGINT, description TEXT ) @@ -724,21 +724,23 @@ BEGIN ( SELECT COUNT( address ) FROM ip - INNER JOIN netblock AS nb2 - ON nb2.id = ip.netblockId - WHERE nb2.regionId = region.id - AND nb2.poolId = pool.id - AND ip.nasId IS NOT NULL - ) AS ipAssigned, - ( + INNER JOIN netblock AS nb2 + ON nb2.id = ip.netblockId + WHERE nb2.regionId = region.id + AND nb2.poolId = pool.id + AND nb2.busyout IS FALSE + AND ip.nasId IS NOT NULL + ) AS ipAssignedFree, + ( SELECT COUNT( address ) FROM ip - INNER JOIN netblock AS nb2 - ON nb2.id = ip.netblockId - WHERE nb2.regionId = region.id - AND nb2.poolId = pool.id - AND ip.nasId IS NULL - ) AS ipUnassigned, + INNER JOIN netblock AS nb2 + ON nb2.id = ip.netblockId + WHERE nb2.regionId = region.id + AND nb2.poolId = pool.id + AND nb2.busyout IS TRUE + AND ip.nasId IS NOT NULL + ) AS ipAssignedBusy, ( SELECT COUNT( address ) FROM ip @@ -746,7 +748,7 @@ BEGIN ON nb2.id = ip.netblockId WHERE nb2.regionId = region.id AND nb2.poolId = pool.id - AND nb2.assignable IS TRUE + AND nb2.busyout IS FALSE AND ip.nasId IS NULL ) AS ipAvailable, ( @@ -756,8 +758,8 @@ BEGIN ON nb2.id = ip.netblockId WHERE nb2.regionId = region.id AND nb2.poolId = pool.id - AND nb2.assignable IS FALSE - ) AS ipUnassignable, + AND nb2.busyout IS TRUE + ) AS ipBusyout, ( SELECT COUNT( address ) FROM ip @@ -784,21 +786,23 @@ BEGIN ( SELECT COUNT( address ) FROM ip - INNER JOIN netblock AS nb2 - ON nb2.id = ip.netblockId - WHERE nb2.regionId IS NULL - AND nb2.poolId = pool.id - AND ip.nasId IS NOT NULL - ) AS ipAssigned, + INNER JOIN netblock AS nb2 + ON nb2.id = ip.netblockId + WHERE nb2.regionId IS NULL + AND nb2.poolId = pool.id + AND nb2.busyout IS FALSE + AND ip.nasId IS NOT NULL + ) AS ipAssignFree, ( SELECT COUNT( address ) FROM ip - INNER JOIN netblock AS nb2 - ON nb2.id = ip.netblockId - WHERE nb2.regionId IS NULL - AND nb2.poolId = pool.id - AND ip.nasId IS NULL - ) AS ipUnassigned, + INNER JOIN netblock AS nb2 + ON nb2.id = ip.netblockId + WHERE nb2.regionId IS NULL + AND nb2.poolId = pool.id + AND nb2.busyout IS TRUE + AND ip.nasId IS NOT NULL + ) AS ipAssignFree, ( SELECT COUNT( address ) FROM ip @@ -806,7 +810,7 @@ BEGIN ON nb2.id = ip.netblockId WHERE nb2.regionId IS NULL AND nb2.poolId = pool.id - AND nb2.assignable IS TRUE + AND nb2.busyout IS FALSE AND ip.nasId IS NULL ) AS ipAvailable, ( @@ -816,8 +820,8 @@ BEGIN ON nb2.id = ip.netblockId WHERE nb2.regionId IS NULL AND nb2.poolId = pool.id - AND nb2.assignable IS FALSE - ) AS ipUnassignable, + AND nb2.busyout IS TRUE + ) AS ipBusyout, ( SELECT COUNT( address ) FROM ip diff --git a/notes/projects/radius/sql/ippool-schema.sql b/notes/projects/radius/sql/ippool-schema.sql index 00288b0..d64dfd5 100644 --- a/notes/projects/radius/sql/ippool-schema.sql +++ b/notes/projects/radius/sql/ippool-schema.sql @@ -145,8 +145,8 @@ CREATE INDEX * regionId * : reference to the physical region served by the block * - * active - * : assignable to customer if true, disabled/inactive if false + * busyout + * : auto assignable to customer if false, disabled/inactive if false * * description * : human readable description of network block @@ -157,7 +157,7 @@ CREATE TABLE IF NOT EXISTS netblock network CIDR NOT NULL UNIQUE, poolId INTEGER NOT NULL, regionId INTEGER, - assignable BOOLEAN NOT NULL DEFAULT TRUE, + busyout BOOLEAN NOT NULL DEFAULT TRUE, description VARCHAR(256), PRIMARY KEY ( id ), FOREIGN KEY ( poolId ) @@ -173,7 +173,7 @@ CREATE INDEX ( id, poolId, network, - assignable + busyout ); @@ -299,7 +299,7 @@ CREATE OR REPLACE FUNCTION ip_before_update LANGUAGE plpgsql AS $$ DECLARE - ipAssignable BOOLEAN; + ipBusyout BOOLEAN; BEGIN -- prevents IP address from being modified @@ -309,20 +309,20 @@ BEGIN -- verifies netblock contains IP address IF ( NEW.netblockId != NEW.netblockId ) THEN - SELECT assignable INTO ipAssignable + SELECT busyout INTO ipBusyout FROM netblock WHERE id = NEW.netblockId AND network >>= NEW.address; - IF ipAssignable IS NULL THEN + IF ipBusyout IS NULL THEN RAISE EXCEPTION 'invalid netblock'; END IF; - IF ipAssignable IS TRUE THEN + IF ipBusyout IS FALSE THEN PERFORM id FROM netblock - WHERE assignable IS TRUE + WHERE busyout IS TRUE AND network >>= NEW.address; IF FOUND THEN - RAISE EXCEPTION 'cannot associate assignable netblock if an unassignable netblock contains IP address'; + RAISE EXCEPTION 'cannot associate not busy netblock to a busied out netblock contains IP address'; END IF; END IF; END IF; @@ -330,8 +330,6 @@ BEGIN -- update 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; @@ -409,17 +407,6 @@ BEGIN 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; $$; @@ -432,8 +419,20 @@ CREATE OR REPLACE FUNCTION netblock_after_insert_or_update AS $$ DECLARE BEGIN + + -- updates IP Association of netblock if an update and is now assignable + IF ( ( OLD.busyout IS TRUE ) AND ( NEW.busyout IS FALSE ) ) 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.busyout IS TRUE; + END IF; + -- updates IP association of netblock is not assignable - IF ( NEW.assignable IS FALSE ) THEN + IF ( NEW.busyout IS TRUE ) THEN UPDATE ip SET netblockId = NEW.id WHERE address <<= NEW.network; diff --git a/notes/projects/radius/sql/untitled.sql b/notes/projects/radius/sql/untitled.sql new file mode 100644 index 0000000..beacd77 --- /dev/null +++ b/notes/projects/radius/sql/untitled.sql @@ -0,0 +1,129 @@ + + +SELECT * FROM region_audit() ORDER BY poolName, regionName; +SELECT * FROM netblock_audit() ORDER BY poolName, network; +SELECT * FROM ip_audit() ORDER BY ipAddress; + + +CREATE TABLE IF NOT EXISTS netblock +( + id SERIAL, + network CIDR NOT NULL UNIQUE, + poolId INTEGER NOT NULL, + regionId INTEGER, + active BOOLEAN NOT NULL DEFAULT TRUE, + description VARCHAR(32), + PRIMARY KEY ( id ), + FOREIGN KEY ( poolId ) + REFERENCES pool + ( id ), + FOREIGN KEY ( regionId ) + REFERENCES region + ( id ) +); + + +CREATE TABLE IF NOT EXISTS ip +( + address INET, + modified timestamp WITH TIME ZONE NOT NULL + DEFAULT TIMESTAMP 'epoch', + username VARCHAR(64), + acctSession VARCHAR(32), + nasId INTEGER, + PRIMARY KEY ( address ), + FOREIGN KEY ( nasId ) + REFERENCES nas + ( id ) +); + + +\c syzdek +set role wheel; +drop database radius_ippool; + + +psql -h raddbtest.prv.acsalaska.net -f radius-roles.sql +psql -h raddbtest.prv.acsalaska.net -f ippool-database.sql +psql -h raddbtest.prv.acsalaska.net -f ippool-schema.sql \ + -f ippool-functions.sql radius_ippool +psql -h raddbtest.prv.acsalaska.net -f ippool-demo.sql radius_ippool + + +SELECT ip.address AS address, + region.name AS region, + pool.name AS pool, + ip.modified AS modified, + ip.username AS username, + ip.acctsession AS acctsession + FROM ip + INNER JOIN netblock + ON netblock.network >>= ip.address + INNER JOIN region + ON region.id = netblock.regionId + INNER JOIN pool + ON pool.id = netblock.poolId + INNER JOIN nas + ON nas.regionId = region.id + WHERE ip.nasId IS NULL + AND nas.name LIKE lower( 'br1.sol' ) + AND pool.name LIKE lower( 'DHCP' ) + AND netblock.active = TRUE + ORDER BY ip.modified ASC + LIMIT 1; + + +UPDATE ip + SET nasId = nas.id, + username = 'test1' + FROM ip + INNER JOIN netblock + ON netblock.network >>= ip.address + INNER JOIN region + ON region.id = netblock.regionId + INNER JOIN pool + ON pool.id = netblock.poolId + INNER JOIN nas + ON nas.regionId = region.id + WHERE ip.nasId IS NULL + AND nas.name LIKE 'br1.sol' + AND pool.name LIKE lower( 'DHCP' ) + AND netblock.active = TRUE + ORDER BY ip.modified ASC + LIMIT 1; + +DECLARE + blah := 'test1'; +BEGIN + SELECT *, blah, FROM region; +END; + + + + +INSERT INTO foo + ( attr1, + attr2, + attr3 + ) + VALUES ( 'val1', + 'val2', + 'val3' + ); + + +DELETE FROM foo + WHERE attr1 like 'val1'; + + +UPDATE foo + SET attr1 = 'valI', + attr2 = 'valII', + attr3 = 'valIII' + WHERE attr1 like 'val1'; + + + + + + -- cgit v1.2.3