diff options
| -rw-r--r-- | .gitlab-ci.yml | 8 | ||||
| -rw-r--r-- | configure.ac | 2 | ||||
| -rw-r--r-- | src/lib/pgsql/pgsql_connection.h | 4 | ||||
| -rw-r--r-- | src/share/database/scripts/mysql/dhcpdb_drop.mysql | 3 | ||||
| -rw-r--r-- | src/share/database/scripts/pgsql/.gitignore | 1 | ||||
| -rw-r--r-- | src/share/database/scripts/pgsql/Makefile.am | 1 | ||||
| -rw-r--r-- | src/share/database/scripts/pgsql/dhcpdb_create.pgsql | 615 | ||||
| -rw-r--r-- | src/share/database/scripts/pgsql/dhcpdb_drop.pgsql | 25 | ||||
| -rw-r--r-- | src/share/database/scripts/pgsql/upgrade_011_to_012.sh.in | 1 | ||||
| -rw-r--r-- | src/share/database/scripts/pgsql/upgrade_012_to_013.sh.in | 657 | ||||
| -rw-r--r-- | src/share/database/scripts/pgsql/wipe_data.sh.in | 2 |
11 files changed, 1315 insertions, 4 deletions
diff --git a/.gitlab-ci.yml b/.gitlab-ci.yml index 636b11f55a..2183dfcca8 100644 --- a/.gitlab-ci.yml +++ b/.gitlab-ci.yml @@ -76,6 +76,9 @@ shellcheck: - SCRIPTS+="src/share/database/scripts/mysql/upgrade_009.5_to_009.6.sh.in " - SCRIPTS+="src/share/database/scripts/mysql/upgrade_009.6_to_010.0.sh.in " - SCRIPTS+="src/share/database/scripts/mysql/upgrade_010_to_011.sh.in " + - SCRIPTS+="src/share/database/scripts/mysql/upgrade_011_to_012.sh.in " + - SCRIPTS+="src/share/database/scripts/mysql/upgrade_012_to_013.sh.in " + - SCRIPTS+="src/share/database/scripts/mysql/upgrade_013_to_014.sh.in " - SCRIPTS+="src/share/database/scripts/mysql/wipe_data.sh.in " - SCRIPTS+="src/share/database/scripts/pgsql/upgrade_001.0_to_002.0.sh.in " - SCRIPTS+="src/share/database/scripts/pgsql/upgrade_002.0_to_003.0.sh.in " @@ -90,6 +93,11 @@ shellcheck: - SCRIPTS+="src/share/database/scripts/pgsql/upgrade_006.1_to_006.2.sh.in " - SCRIPTS+="src/share/database/scripts/pgsql/upgrade_006.2_to_007.0.sh.in " - SCRIPTS+="src/share/database/scripts/pgsql/upgrade_007_to_008.sh.in " + - SCRIPTS+="src/share/database/scripts/pgsql/upgrade_008_to_009.sh.in " + - SCRIPTS+="src/share/database/scripts/pgsql/upgrade_009_to_010.sh.in " + - SCRIPTS+="src/share/database/scripts/pgsql/upgrade_010_to_011.sh.in " + - SCRIPTS+="src/share/database/scripts/pgsql/upgrade_011_to_012.sh.in " + - SCRIPTS+="src/share/database/scripts/pgsql/upgrade_012_to_013.sh.in " - SCRIPTS+="src/share/database/scripts/pgsql/wipe_data.sh.in " - SCRIPTS+="src/share/yang/modules/utils/check-hashes.sh " - SCRIPTS+="src/share/yang/modules/utils/check-revisions.sh " diff --git a/configure.ac b/configure.ac index bc64d9fa32..226d5ed445 100644 --- a/configure.ac +++ b/configure.ac @@ -1670,6 +1670,8 @@ AC_CONFIG_FILES([src/share/database/scripts/pgsql/upgrade_010_to_011.sh], [chmod +x src/share/database/scripts/pgsql/upgrade_010_to_011.sh]) AC_CONFIG_FILES([src/share/database/scripts/pgsql/upgrade_011_to_012.sh], [chmod +x src/share/database/scripts/pgsql/upgrade_011_to_012.sh]) +AC_CONFIG_FILES([src/share/database/scripts/pgsql/upgrade_012_to_013.sh], + [chmod +x src/share/database/scripts/pgsql/upgrade_012_to_013.sh]) AC_CONFIG_FILES([src/share/database/scripts/pgsql/wipe_data.sh], [chmod +x src/share/database/scripts/pgsql/wipe_data.sh]) AC_CONFIG_FILES([src/share/yang/Makefile]) diff --git a/src/lib/pgsql/pgsql_connection.h b/src/lib/pgsql/pgsql_connection.h index edd1d8b54d..65e8e42ada 100644 --- a/src/lib/pgsql/pgsql_connection.h +++ b/src/lib/pgsql/pgsql_connection.h @@ -17,8 +17,8 @@ namespace isc { namespace db { -/// @brief Define PostgreSQL backend version: 12.0 -const uint32_t PGSQL_SCHEMA_VERSION_MAJOR = 12; +/// @brief Define the PostgreSQL backend version. +const uint32_t PGSQL_SCHEMA_VERSION_MAJOR = 13; const uint32_t PGSQL_SCHEMA_VERSION_MINOR = 0; // Maximum number of parameters that can be used a statement diff --git a/src/share/database/scripts/mysql/dhcpdb_drop.mysql b/src/share/database/scripts/mysql/dhcpdb_drop.mysql index 41c90e402e..0d17890784 100644 --- a/src/share/database/scripts/mysql/dhcpdb_drop.mysql +++ b/src/share/database/scripts/mysql/dhcpdb_drop.mysql @@ -149,4 +149,5 @@ DROP TRIGGER IF EXISTS lease6_AINS; DROP TRIGGER IF EXISTS lease6_AUPD; DROP TRIGGER IF EXISTS lease6_ADEL; DROP FUNCTION IF EXISTS checkLease4Limits; -DROP FUNCTION IF EXISTS checkLease6Limits;
\ No newline at end of file +DROP FUNCTION IF EXISTS checkLease6Limits; +DROP FUNCTION IF EXISTS isJsonSupported; diff --git a/src/share/database/scripts/pgsql/.gitignore b/src/share/database/scripts/pgsql/.gitignore index d2e50568ca..96744d8d6a 100644 --- a/src/share/database/scripts/pgsql/.gitignore +++ b/src/share/database/scripts/pgsql/.gitignore @@ -15,4 +15,5 @@ /upgrade_009_to_010.sh /upgrade_010_to_011.sh /upgrade_011_to_012.sh +/upgrade_012_to_013.sh /wipe_data.sh diff --git a/src/share/database/scripts/pgsql/Makefile.am b/src/share/database/scripts/pgsql/Makefile.am index e0b8ea565e..f9c20961e4 100644 --- a/src/share/database/scripts/pgsql/Makefile.am +++ b/src/share/database/scripts/pgsql/Makefile.am @@ -26,6 +26,7 @@ pgsql_SCRIPTS += upgrade_008_to_009.sh pgsql_SCRIPTS += upgrade_009_to_010.sh pgsql_SCRIPTS += upgrade_010_to_011.sh pgsql_SCRIPTS += upgrade_011_to_012.sh +pgsql_SCRIPTS += upgrade_012_to_013.sh pgsql_SCRIPTS += wipe_data.sh DISTCLEANFILES = ${pgsql_SCRIPTS} diff --git a/src/share/database/scripts/pgsql/dhcpdb_create.pgsql b/src/share/database/scripts/pgsql/dhcpdb_create.pgsql index 7d992e70f9..c44a32e415 100644 --- a/src/share/database/scripts/pgsql/dhcpdb_create.pgsql +++ b/src/share/database/scripts/pgsql/dhcpdb_create.pgsql @@ -4987,6 +4987,621 @@ UPDATE schema_version -- This line concludes the schema upgrade to version 12. +-- This line starts the schema upgrade to version 13. + +-- JSON functions -- + +-- Helper function that avoids a casting error when the string +-- presumed to be in JSON format, is empty. +CREATE OR REPLACE FUNCTION json_cast(IN json_candidate TEXT) +RETURNS JSON +AS $$ +BEGIN + IF LENGTH(json_candidate) = 0 THEN + RETURN '{}'::json; + END IF; + RETURN json_candidate::json; +END; +$$ LANGUAGE plpgsql; + +-- Function that establishes whether JSON functions are supported. +-- They should be provided with PostgreSQL >= 9.4. +CREATE OR REPLACE FUNCTION isJsonSupported() +RETURNS BOOLEAN +AS $$ +BEGIN + IF get_session_value('json_supported') IS NULL THEN + IF (SELECT proname FROM pg_proc WHERE proname = 'json_extract_path') = 'json_extract_path' THEN + PERFORM set_session_value('kea.json_supported', true); + ELSE + PERFORM set_session_value('kea.json_supported', false); + END IF; + END IF; + RETURN get_session_value('kea.json_supported'); +END +$$ LANGUAGE plpgsql; + +-- Schema changes related to lease limiting start here. -- + +-- Recreate the triggers that update the leaseX_stat tables as stored procedures. -- + +CREATE OR REPLACE FUNCTION lease4_AINS_lease4_stat(IN new_state BIGINT, + IN new_subnet_id BIGINT) +RETURNS VOID +AS $$ +BEGIN + IF new_state = 0 OR new_state = 1 THEN + -- Update the state count if it exists. + UPDATE lease4_stat SET leases = leases + 1 + WHERE subnet_id = new_subnet_id AND state = new_state; + + -- Insert the state count record if it does not exist. + IF NOT FOUND THEN + INSERT INTO lease4_stat VALUES (new_subnet_id, new_state, 1); + END IF; + END IF; +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION lease4_AUPD_lease4_stat(IN old_state BIGINT, + IN old_subnet_id BIGINT, + IN new_state BIGINT, + IN new_subnet_id BIGINT) +RETURNS VOID +AS $$ +BEGIN + IF old_subnet_id != new_subnet_id OR old_state != new_state THEN + IF old_state = 0 OR old_state = 1 THEN + -- Decrement the old state count if record exists. + UPDATE lease4_stat + SET leases = GREATEST(leases - 1, 0) + WHERE subnet_id = old_subnet_id AND state = old_state; + END IF; + + IF new_state = 0 OR new_state = 1 THEN + -- Increment the new state count if record exists. + UPDATE lease4_stat SET leases = leases + 1 + WHERE subnet_id = new_subnet_id AND state = new_state; + + -- Insert new state record if it does not exist. + IF NOT FOUND THEN + INSERT INTO lease4_stat VALUES (new_subnet_id, new_state, 1); + END IF; + END IF; + END IF; +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION lease4_ADEL_lease4_stat(IN old_state BIGINT, + IN old_subnet_id BIGINT) +RETURNS VOID +AS $$ +BEGIN + IF old_state = 0 OR old_state = 1 THEN + -- Decrement the state count if record exists. + UPDATE lease4_stat + SET leases = GREATEST(leases - 1, 0) + WHERE subnet_id = old_subnet_id AND old_state = state; + END IF; +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION lease6_AINS_lease6_stat(IN new_state BIGINT, + IN new_subnet_id BIGINT, + IN new_lease_type BIGINT) +RETURNS VOID +AS $$ +BEGIN + IF new_state = 0 OR new_state = 1 THEN + -- Update the state count if it exists. + UPDATE lease6_stat SET leases = leases + 1 + WHERE subnet_id = new_subnet_id AND lease_type = new_lease_type + AND state = new_state; + + -- Insert the state count record if it does not exist. + IF NOT FOUND THEN + INSERT INTO lease6_stat VALUES (new_subnet_id, new_lease_type, new_state, 1); + END IF; + END IF; +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION lease6_AUPD_lease6_stat(IN old_state BIGINT, + IN old_subnet_id BIGINT, + IN old_lease_type BIGINT, + IN new_state BIGINT, + IN new_subnet_id BIGINT, + IN new_lease_type BIGINT) +RETURNS VOID +AS $$ +BEGIN + IF old_subnet_id != new_subnet_id OR + old_lease_type != new_lease_type OR + old_state != new_state THEN + IF old_state = 0 OR old_state = 1 THEN + -- Decrement the old state count if record exists. + UPDATE lease6_stat + SET leases = GREATEST(leases - 1, 0) + WHERE subnet_id = old_subnet_id AND lease_type = old_lease_type + AND state = old_state; + END IF; + + IF new_state = 0 OR new_state = 1 THEN + -- Increment the new state count if record exists + UPDATE lease6_stat SET leases = leases + 1 + WHERE subnet_id = new_subnet_id AND lease_type = new_lease_type + AND state = new_state; + + -- Insert new state record if it does not exist + IF NOT FOUND THEN + INSERT INTO lease6_stat + VALUES (new_subnet_id, new_lease_type, new_state, 1); + END IF; + END IF; + END IF; +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION lease6_ADEL_lease6_stat(IN old_state BIGINT, + IN old_subnet_id BIGINT, + IN old_lease_type BIGINT) +RETURNS VOID +AS $$ +BEGIN + IF old_state = 0 OR old_state = 1 THEN + -- Decrement the state count if record exists + UPDATE lease6_stat + SET leases = GREATEST(leases - 1, 0) + WHERE subnet_id = old_subnet_id AND lease_type = old_lease_type + AND state = old_state; + END IF; +END; +$$ LANGUAGE plpgsql; + +-- Create tables that contain the number of active leases. -- + +CREATE TABLE lease4_stat_by_client_class ( + client_class VARCHAR(128) NOT NULL PRIMARY KEY, + leases BIGINT NOT NULL +); + +CREATE TABLE lease6_stat_by_client_class ( + client_class VARCHAR(128) NOT NULL, + lease_type BIGINT NOT NULL, + leases BIGINT NOT NULL, + PRIMARY KEY (client_class, lease_type), + CONSTRAINT fk_lease6_stat_by_client_class_lease_type FOREIGN KEY (lease_type) + REFERENCES lease6_types (lease_type) +); + +-- Create procedures to be called for each row in after-event triggers for +-- INSERT, UPDATE and DELETE on lease tables. + +CREATE OR REPLACE FUNCTION lease4_AINS_lease4_stat_by_client_class(IN new_state BIGINT, + IN new_user_context TEXT) +RETURNS VOID +AS $$ +DECLARE + class VARCHAR(128); +BEGIN + -- Only state 0 is needed for lease limiting. + IF new_state = 0 THEN + -- Dive into client classes. + FOR class IN SELECT * FROM JSON_ARRAY_ELEMENTS(json_cast(new_user_context)->'ISC'->'client-classes') LOOP + SELECT TRIM('"' FROM class) INTO class; + + -- Upsert to increment the lease count. + UPDATE lease4_stat_by_client_class SET leases = leases + 1 + WHERE client_class = class; + IF NOT FOUND THEN + INSERT INTO lease4_stat_by_client_class VALUES (class, 1); + END IF; + END LOOP; + END IF; +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION lease4_AUPD_lease4_stat_by_client_class(IN old_state BIGINT, + IN old_user_context TEXT, + IN new_state BIGINT, + IN new_user_context TEXT) +RETURNS VOID +AS $$ +DECLARE + old_client_classes TEXT; + new_client_classes TEXT; + class VARCHAR(128); + length INT; + i INT; +BEGIN + SELECT json_cast(old_user_context)->'ISC'->'client-classes' INTO old_client_classes; + SELECT json_cast(new_user_context)->'ISC'->'client-classes' INTO new_client_classes; + + IF old_state != new_state OR old_client_classes != new_client_classes THEN + -- Check if it's moving away from a counted state. + IF old_state = 0 THEN + -- Dive into client classes. + FOR class IN SELECT * FROM JSON_ARRAY_ELEMENTS(json_cast(old_client_classes)) LOOP + -- Decrement the lease count if the record exists. + UPDATE lease4_stat_by_client_class SET leases = GREATEST(leases - 1, 0) + WHERE client_class = class; + END LOOP; + END IF; + + -- Check if it's moving into a counted state. + IF new_state = 0 THEN + -- Dive into client classes. + FOR class IN SELECT * FROM JSON_ARRAY_ELEMENTS(json_cast(new_client_classes)) LOOP + -- Upsert to increment the lease count. + UPDATE lease4_stat_by_client_class SET leases = leases + 1 + WHERE client_class = class; + IF NOT FOUND THEN + INSERT INTO lease4_stat_by_client_class VALUES (class, 1); + END IF; + END LOOP; + END IF; + END IF; +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION lease4_ADEL_lease4_stat_by_client_class(IN old_state BIGINT, + IN old_user_context TEXT) +RETURNS VOID +AS $$ +DECLARE + class VARCHAR(128); +BEGIN + -- Only state 0 is accounted for in lease limiting. + IF old_state = 0 THEN + -- Dive into client classes. + FOR class IN SELECT * FROM JSON_ARRAY_ELEMENTS(json_cast(old_user_context)->'ISC'->'client-classes') LOOP + SELECT TRIM('"' FROM class) INTO class; + + -- Decrement the lease count if the record exists. + UPDATE lease4_stat_by_client_class SET leases = GREATEST(leases - 1, 0) + WHERE client_class = class; + END LOOP; + END IF; +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION lease6_AINS_lease6_stat_by_client_class(IN new_state BIGINT, + IN new_user_context TEXT, + IN new_lease_type BIGINT) +RETURNS VOID +AS $$ +DECLARE + client_classes TEXT; + class VARCHAR(128); + length INT; + i INT; +BEGIN + -- Only state 0 is needed for lease limiting. + IF new_state = 0 THEN + -- Dive into client classes. + FOR class IN SELECT * FROM JSON_ARRAY_ELEMENTS(json_cast(new_user_context)->'ISC'->'client-classes') LOOP + SELECT TRIM('"' FROM class) INTO class; + + -- Upsert to increment the lease count. + UPDATE lease6_stat_by_client_class SET leases = leases + 1 + WHERE client_class = class AND lease_type = new_lease_type; + IF NOT FOUND THEN + INSERT INTO lease6_stat_by_client_class VALUES (class, new_lease_type, 1); + END IF; + END LOOP; + END IF; +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION lease6_AUPD_lease6_stat_by_client_class(IN old_state BIGINT, + IN old_user_context TEXT, + IN old_lease_type BIGINT, + IN new_state BIGINT, + IN new_user_context TEXT, + IN new_lease_type BIGINT) +RETURNS VOID +AS $$ +DECLARE + old_client_classes TEXT; + new_client_classes TEXT; + class VARCHAR(128); + length INT; + i INT; +BEGIN + SELECT json_cast(old_user_context)->'ISC'->'client-classes' INTO old_client_classes; + SELECT json_cast(new_user_context)->'ISC'->'client-classes' INTO new_client_classes; + + IF old_state != new_state OR old_client_classes != new_client_classes OR old_lease_type != new_lease_type THEN + -- Check if it's moving away from a counted state. + IF old_state = 0 THEN + -- Dive into client classes. + FOR class IN SELECT * FROM JSON_ARRAY_ELEMENTS(json_cast(old_client_classes)) LOOP + -- Decrement the lease count if the record exists. + UPDATE lease6_stat_by_client_class SET leases = GREATEST(leases - 1, 0) + WHERE client_class = class AND lease_type = old_lease_type; + END LOOP; + END IF; + + -- Check if it's moving into a counted state. + IF new_state = 0 THEN + -- Dive into client classes. + FOR class IN SELECT * FROM JSON_ARRAY_ELEMENTS(json_cast(new_client_classes)) LOOP + -- Upsert to increment the lease count. + UPDATE lease6_stat_by_client_class SET leases = leases + 1 + WHERE client_class = class AND lease_type = new_lease_type; + IF NOT FOUND THEN + INSERT INTO lease6_stat_by_client_class VALUES (class, new_lease_type, 1); + END IF; + END LOOP; + END IF; + END IF; +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION lease6_ADEL_lease6_stat_by_client_class(IN old_state BIGINT, + IN old_user_context TEXT, + IN old_lease_type BIGINT) +RETURNS VOID +AS $$ +DECLARE + client_classes VARCHAR(1024); + class VARCHAR(128); + length INT; + i INT; +BEGIN + -- Only state 0 is accounted for in lease limiting. But check both states to be consistent with lease6_stat. + IF old_state = 0 THEN + -- Dive into client classes. + FOR class IN SELECT * FROM JSON_ARRAY_ELEMENTS(json_cast(old_user_context)->'ISC'->'client-classes') LOOP + SELECT TRIM('"' FROM class) INTO class; + + -- Decrement the lease count if the record exists. + UPDATE lease6_stat_by_client_class SET leases = GREATEST(leases - 1, 0) + WHERE client_class = class AND lease_type = old_lease_type; + END LOOP; + END IF; +END; +$$ LANGUAGE plpgsql; + +-- Recreate the after-event triggers for INSERT, UPDATE and DELETE on lease tables to call the -- +-- stored procedures above in pairs of two: for client classes and for subnets. -- + +DROP TRIGGER IF EXISTS stat_lease4_insert ON lease4; + +CREATE OR REPLACE FUNCTION func_lease4_AINS() +RETURNS trigger AS $lease4_AINS$ +BEGIN + IF isJsonSupported() = true THEN + PERFORM lease4_AINS_lease4_stat_by_client_class(NEW.state, NEW.user_context); + END IF; + PERFORM lease4_AINS_lease4_stat(NEW.state, NEW.subnet_id); + RETURN NULL; +END; +$lease4_AINS$ LANGUAGE plpgsql; + +CREATE TRIGGER lease4_AINS AFTER INSERT ON lease4 + FOR EACH ROW EXECUTE PROCEDURE func_lease4_AINS(); + +DROP TRIGGER IF EXISTS stat_lease4_update ON lease4; + +CREATE OR REPLACE FUNCTION func_lease4_AUPD() +RETURNS trigger AS $lease4_AUPD$ +BEGIN + IF isJsonSupported() = true THEN + PERFORM lease4_AUPD_lease4_stat_by_client_class(OLD.state, OLD.user_context, NEW.state, NEW.user_context); + END IF; + PERFORM lease4_AUPD_lease4_stat(OLD.state, OLD.subnet_id, NEW.state, NEW.subnet_id); + RETURN NULL; +END; +$lease4_AUPD$ LANGUAGE plpgsql; + +CREATE TRIGGER lease4_AUPD AFTER UPDATE ON lease4 + FOR EACH ROW EXECUTE PROCEDURE func_lease4_AUPD(); + +DROP TRIGGER IF EXISTS stat_lease4_delete ON lease4; + +CREATE OR REPLACE FUNCTION func_lease4_ADEL() +RETURNS trigger AS $lease4_ADEL$ +BEGIN + IF isJsonSupported() = true THEN + PERFORM lease4_ADEL_lease4_stat_by_client_class(OLD.state, OLD.user_context); + END IF; + PERFORM lease4_ADEL_lease4_stat(OLD.state, OLD.subnet_id); + RETURN NULL; +END; +$lease4_ADEL$ LANGUAGE plpgsql; + +CREATE TRIGGER lease4_ADEL AFTER DELETE ON lease4 + FOR EACH ROW EXECUTE PROCEDURE func_lease4_ADEL(); + +DROP TRIGGER IF EXISTS stat_lease6_insert ON lease6; + +CREATE OR REPLACE FUNCTION func_lease6_AINS() +RETURNS trigger AS $lease6_AINS$ +BEGIN + IF isJsonSupported() = true THEN + PERFORM lease6_AINS_lease6_stat_by_client_class(NEW.state, NEW.user_context, NEW.lease_type); + END IF; + PERFORM lease6_AINS_lease6_stat(NEW.state, NEW.subnet_id, NEW.lease_type); + RETURN NULL; +END; +$lease6_AINS$ LANGUAGE plpgsql; + +CREATE TRIGGER lease6_AINS AFTER INSERT ON lease6 + FOR EACH ROW EXECUTE PROCEDURE func_lease6_AINS(); + +DROP TRIGGER IF EXISTS stat_lease6_update ON lease6; + +CREATE OR REPLACE FUNCTION func_lease6_AUPD() +RETURNS trigger AS $lease6_AUPD$ +BEGIN + IF isJsonSupported() = true THEN + PERFORM lease6_AUPD_lease6_stat_by_client_class(OLD.state, OLD.user_context, OLD.lease_type, NEW.state, NEW.user_context, NEW.lease_type); + END IF; + PERFORM lease6_AUPD_lease6_stat(OLD.state, OLD.subnet_id, OLD.lease_type, NEW.state, NEW.subnet_id, NEW.lease_type); + RETURN NULL; +END; +$lease6_AUPD$ LANGUAGE plpgsql; + +CREATE TRIGGER lease6_AUPD AFTER UPDATE ON lease6 + FOR EACH ROW EXECUTE PROCEDURE func_lease6_AUPD(); + +DROP TRIGGER IF EXISTS stat_lease6_delete ON lease6; + +CREATE OR REPLACE FUNCTION func_lease6_ADEL() +RETURNS trigger AS $lease6_ADEL$ +BEGIN + IF isJsonSupported() = true THEN + PERFORM lease6_ADEL_lease6_stat_by_client_class(OLD.state, OLD.user_context, OLD.lease_type); + END IF; + PERFORM lease6_ADEL_lease6_stat(OLD.state, OLD.subnet_id, OLD.lease_type); + RETURN NULL; +END; +$lease6_ADEL$ LANGUAGE plpgsql; + +CREATE TRIGGER lease6_ADEL AFTER DELETE ON lease6 + FOR EACH ROW EXECUTE PROCEDURE func_lease6_ADEL(); + +-- Create functions that return an empty TEXT if all limits allow for more leases, or otherwise a +-- TEXT in one of the following JSON formats detailing the limit that was reached: +-- { "limit-type": "client-class", "name": foo, "lease-type": "address", "limit": 2, "count": 2 } +-- { "limit-type": "subnet", "id": 1, "lease-type": "IA_PD", "limit": 2, "count": 2 } +-- The following format for user_context is assumed: +-- { "ISC": { "limits": { "client-classes": [ { "name": "foo", "address-limit": 2, "prefix-limit": 1 } ], +-- "subnet": { "id": 1, "address-limit": 2, "prefix-limit": 1 } } } } + +CREATE OR REPLACE FUNCTION checkLease4Limits(user_context TEXT) +RETURNS TEXT +AS $$ +DECLARE + class TEXT; + name VARCHAR(255); + sid INT; + lease_limit INT; + lease_count INT; +BEGIN + -- Dive into client class limits. + FOR class IN SELECT * FROM JSON_ARRAY_ELEMENTS(json_cast(user_context)->'ISC'->'limits'->'client-classes') LOOP + SELECT TRIM('"' FROM (json_cast(class)->'name')::text) INTO name; + SELECT json_cast(class)->'address-limit' INTO lease_limit; + + IF lease_limit IS NOT NULL THEN + -- Get the lease count for this client class. + SELECT leases FROM lease4_stat_by_client_class INTO lease_count WHERE client_class = name; + IF lease_count IS NULL THEN + lease_count := 0; + END IF; + + -- Compare. Return immediately if the limit is surpassed. + IF lease_limit <= lease_count THEN + RETURN CONCAT('address limit ', lease_limit, ' for client class "', name, '", current lease count ', lease_count); + END IF; + END IF; + END LOOP; + + -- Dive into subnet limits. + SELECT json_cast(user_context)->'ISC'->'limits'->'subnet'->'id' INTO sid; + SELECT json_cast(user_context)->'ISC'->'limits'->'subnet'->'address-limit' INTO lease_limit; + + IF lease_limit IS NOT NULL THEN + -- Get the lease count for this client class. + SELECT leases FROM lease4_stat WHERE subnet_id = sid AND state = 0 INTO lease_count; + IF lease_count IS NULL THEN + lease_count := 0; + END IF; + + -- Compare. Return immediately if the limit is surpassed. + IF lease_limit <= lease_count THEN + RETURN CONCAT('address limit ', lease_limit, ' for subnet ID ', sid, ', current lease count ', lease_count); + END IF; + END IF; + + RETURN ''; +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION checkLease6Limits(user_context TEXT) +RETURNS TEXT +AS $$ +DECLARE + class TEXT; + name VARCHAR(255); + sid INT; + lease_limit INT; + lease_count INT; +BEGIN + -- Dive into client class limits. + FOR class IN SELECT * FROM JSON_ARRAY_ELEMENTS(json_cast(user_context)->'ISC'->'limits'->'client-classes') LOOP + SELECT TRIM('"' FROM (json_cast(class)->'name')::text) INTO name; + SELECT json_cast(class)->'address-limit' INTO lease_limit; + + IF lease_limit IS NOT NULL THEN + -- Get the address count for this client class. + SELECT leases FROM lease6_stat_by_client_class WHERE client_class = name AND lease_type = 0 INTO lease_count; + IF lease_count IS NULL THEN + lease_count := 0; + END IF; + + -- Compare. Return immediately if the limit is surpassed. + IF lease_limit <= lease_count THEN + RETURN CONCAT('address limit ', lease_limit, ' for client class "', name, '", current lease count ', lease_count); + END IF; + END IF; + + SELECT json_cast(class)->'prefix-limit' INTO lease_limit; + IF lease_limit IS NOT NULL THEN + -- Get the prefix count for this client class. + SELECT leases FROM lease6_stat_by_client_class WHERE client_class = name AND lease_type = 2 INTO lease_count; + IF lease_count IS NULL THEN + lease_count := 0; + END IF; + + -- Compare. Return immediately if the limit is surpassed. + IF lease_limit <= lease_count THEN + RETURN CONCAT('prefix limit ', lease_limit, ' for client class "', name, '", current lease count ', lease_count); + END IF; + END IF; + END LOOP; + + -- Dive into subnet limits. + SELECT json_cast(user_context)->'ISC'->'limits'->'subnet'->'id' INTO sid; + SELECT json_cast(user_context)->'ISC'->'limits'->'subnet'->'address-limit' INTO lease_limit; + IF lease_limit IS NOT NULL THEN + -- Get the lease count for this subnet. + SELECT leases FROM lease6_stat WHERE subnet_id = sid AND lease_type = 0 AND state = 0 INTO lease_count; + IF lease_count IS NULL THEN + lease_count := 0; + END IF; + + -- Compare. Return immediately if the limit is surpassed. + IF lease_limit <= lease_count THEN + RETURN CONCAT('address limit ', lease_limit, ' for subnet ID ', sid, ', current lease count ', lease_count); + END IF; + END IF; + SELECT json_cast(user_context)->'ISC'->'limits'->'subnet'->'prefix-limit' INTO lease_limit; + IF lease_limit IS NOT NULL THEN + -- Get the lease count for this client class. + SELECT leases FROM lease6_stat WHERE subnet_id = sid AND lease_type = 2 AND state = 0 INTO lease_count; + IF lease_count IS NULL THEN + lease_count := 0; + END IF; + + -- Compare. Return immediately if the limit is surpassed. + IF lease_limit <= lease_count THEN + RETURN CONCAT('prefix limit ', lease_limit, ' for subnet ID ', sid, ', current lease count ', lease_count); + END IF; + END IF; + + RETURN ''; +END; +$$ LANGUAGE plpgsql; + +-- Update the schema version number. +UPDATE schema_version + SET version = '13', minor = '0'; + +-- This line concludes the schema upgrade to version 13. + -- Commit the script transaction. COMMIT; diff --git a/src/share/database/scripts/pgsql/dhcpdb_drop.pgsql b/src/share/database/scripts/pgsql/dhcpdb_drop.pgsql index c4247437c2..27bb3114bf 100644 --- a/src/share/database/scripts/pgsql/dhcpdb_drop.pgsql +++ b/src/share/database/scripts/pgsql/dhcpdb_drop.pgsql @@ -177,3 +177,28 @@ DROP FUNCTION IF EXISTS lease4Upload(); DROP FUNCTION IF EXISTS lease6Upload(); DROP FUNCTION IF EXISTS gmt_epoch(input_time TIMESTAMP WITH TIME ZONE); + +-- lease limiting tables and functions +DROP TABLE IF EXISTS lease4_stat_by_client_class; +DROP TABLE IF EXISTS lease6_stat_by_client_class; +DROP FUNCTION IF EXISTS lease4_AINS_lease4_stat(); +DROP FUNCTION IF EXISTS lease4_AUPD_lease4_stat(); +DROP FUNCTION IF EXISTS lease4_ADEL_lease4_stat(); +DROP FUNCTION IF EXISTS lease6_AINS_lease6_stat(); +DROP FUNCTION IF EXISTS lease6_AUPD_lease6_stat(); +DROP FUNCTION IF EXISTS lease6_ADEL_lease6_stat(); +DROP FUNCTION IF EXISTS lease4_AINS_lease4_stat_by_client_class(); +DROP FUNCTION IF EXISTS lease4_AUPD_lease4_stat_by_client_class(); +DROP FUNCTION IF EXISTS lease4_ADEL_lease4_stat_by_client_class(); +DROP FUNCTION IF EXISTS lease6_AINS_lease6_stat_by_client_class(); +DROP FUNCTION IF EXISTS lease6_AUPD_lease6_stat_by_client_class(); +DROP FUNCTION IF EXISTS lease6_ADEL_lease6_stat_by_client_class(); +DROP FUNCTION IF EXISTS func_lease4_AINS(); +DROP FUNCTION IF EXISTS func_lease4_AUPD(); +DROP FUNCTION IF EXISTS func_lease4_ADEL(); +DROP FUNCTION IF EXISTS func_lease6_AINS(); +DROP FUNCTION IF EXISTS func_lease6_AUPD(); +DROP FUNCTION IF EXISTS func_lease6_ADEL(); +DROP FUNCTION IF EXISTS checkLease4Limits(); +DROP FUNCTION IF EXISTS checkLease6Limits(); +DROP FUNCTION IF EXISTS isJsonSupported(); diff --git a/src/share/database/scripts/pgsql/upgrade_011_to_012.sh.in b/src/share/database/scripts/pgsql/upgrade_011_to_012.sh.in index 6bdeeda619..00a02dcea2 100644 --- a/src/share/database/scripts/pgsql/upgrade_011_to_012.sh.in +++ b/src/share/database/scripts/pgsql/upgrade_011_to_012.sh.in @@ -104,4 +104,3 @@ UPDATE schema_version COMMIT; EOF - diff --git a/src/share/database/scripts/pgsql/upgrade_012_to_013.sh.in b/src/share/database/scripts/pgsql/upgrade_012_to_013.sh.in new file mode 100644 index 0000000000..e29197449a --- /dev/null +++ b/src/share/database/scripts/pgsql/upgrade_012_to_013.sh.in @@ -0,0 +1,657 @@ +#!/bin/sh + +# Copyright (C) 2022 Internet Systems Consortium, Inc. ("ISC") +# +# This Source Code Form is subject to the terms of the Mozilla Public +# License, v. 2.0. If a copy of the MPL was not distributed with this +# file, You can obtain one at http://mozilla.org/MPL/2.0/. + +# shellcheck disable=SC1091 +# SC1091: Not following: ... was not specified as input (see shellcheck -x). + +# Exit with error if commands exit with non-zero and if undefined variables are +# used. +set -eu + +# shellcheck disable=SC2034 +# SC2034: ... appears unused. Verify use (or export if used externally). +prefix="@prefix@" + +# Include utilities. Use installed version if available and +# use build version if it isn't. +if [ -e @datarootdir@/@PACKAGE_NAME@/scripts/admin-utils.sh ]; then + . "@datarootdir@/@PACKAGE_NAME@/scripts/admin-utils.sh" +else + . "@abs_top_builddir@/src/bin/admin/admin-utils.sh" +fi + +VERSION=$(pgsql_version "$@") + +if [ "$VERSION" != "12.0" ]; then + printf 'This script upgrades 12.0 to 12.0. ' + printf 'Reported version is %s. Skipping upgrade.\n' "${VERSION}" + exit 0 +fi + +psql "$@" >/dev/null <<EOF +START TRANSACTION; + +-- This line starts the schema upgrade to version 13. + +-- JSON functions -- + +-- Helper function that avoids a casting error when the string +-- presumed to be in JSON format, is empty. +CREATE OR REPLACE FUNCTION json_cast(IN json_candidate TEXT) +RETURNS JSON +AS \$\$ +BEGIN + IF LENGTH(json_candidate) = 0 THEN + RETURN '{}'::json; + END IF; + RETURN json_candidate::json; +END; +\$\$ LANGUAGE plpgsql; + +-- Function that establishes whether JSON functions are supported. +-- They should be provided with PostgreSQL >= 9.4. +CREATE OR REPLACE FUNCTION isJsonSupported() +RETURNS BOOLEAN +AS \$\$ +BEGIN + IF get_session_value('json_supported') IS NULL THEN + IF (SELECT proname FROM pg_proc WHERE proname = 'json_extract_path') = 'json_extract_path' THEN + PERFORM set_session_value('kea.json_supported', true); + ELSE + PERFORM set_session_value('kea.json_supported', false); + END IF; + END IF; + RETURN get_session_value('kea.json_supported'); +END +\$\$ LANGUAGE plpgsql; + +-- Schema changes related to lease limiting start here. -- + +-- Recreate the triggers that update the leaseX_stat tables as stored procedures. -- + +CREATE OR REPLACE FUNCTION lease4_AINS_lease4_stat(IN new_state BIGINT, + IN new_subnet_id BIGINT) +RETURNS VOID +AS \$\$ +BEGIN + IF new_state = 0 OR new_state = 1 THEN + -- Update the state count if it exists. + UPDATE lease4_stat SET leases = leases + 1 + WHERE subnet_id = new_subnet_id AND state = new_state; + + -- Insert the state count record if it does not exist. + IF NOT FOUND THEN + INSERT INTO lease4_stat VALUES (new_subnet_id, new_state, 1); + END IF; + END IF; +END; +\$\$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION lease4_AUPD_lease4_stat(IN old_state BIGINT, + IN old_subnet_id BIGINT, + IN new_state BIGINT, + IN new_subnet_id BIGINT) +RETURNS VOID +AS \$\$ +BEGIN + IF old_subnet_id != new_subnet_id OR old_state != new_state THEN + IF old_state = 0 OR old_state = 1 THEN + -- Decrement the old state count if record exists. + UPDATE lease4_stat + SET leases = GREATEST(leases - 1, 0) + WHERE subnet_id = old_subnet_id AND state = old_state; + END IF; + + IF new_state = 0 OR new_state = 1 THEN + -- Increment the new state count if record exists. + UPDATE lease4_stat SET leases = leases + 1 + WHERE subnet_id = new_subnet_id AND state = new_state; + + -- Insert new state record if it does not exist. + IF NOT FOUND THEN + INSERT INTO lease4_stat VALUES (new_subnet_id, new_state, 1); + END IF; + END IF; + END IF; +END; +\$\$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION lease4_ADEL_lease4_stat(IN old_state BIGINT, + IN old_subnet_id BIGINT) +RETURNS VOID +AS \$\$ +BEGIN + IF old_state = 0 OR old_state = 1 THEN + -- Decrement the state count if record exists. + UPDATE lease4_stat + SET leases = GREATEST(leases - 1, 0) + WHERE subnet_id = old_subnet_id AND old_state = state; + END IF; +END; +\$\$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION lease6_AINS_lease6_stat(IN new_state BIGINT, + IN new_subnet_id BIGINT, + IN new_lease_type BIGINT) +RETURNS VOID +AS \$\$ +BEGIN + IF new_state = 0 OR new_state = 1 THEN + -- Update the state count if it exists. + UPDATE lease6_stat SET leases = leases + 1 + WHERE subnet_id = new_subnet_id AND lease_type = new_lease_type + AND state = new_state; + + -- Insert the state count record if it does not exist. + IF NOT FOUND THEN + INSERT INTO lease6_stat VALUES (new_subnet_id, new_lease_type, new_state, 1); + END IF; + END IF; +END; +\$\$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION lease6_AUPD_lease6_stat(IN old_state BIGINT, + IN old_subnet_id BIGINT, + IN old_lease_type BIGINT, + IN new_state BIGINT, + IN new_subnet_id BIGINT, + IN new_lease_type BIGINT) +RETURNS VOID +AS \$\$ +BEGIN + IF old_subnet_id != new_subnet_id OR + old_lease_type != new_lease_type OR + old_state != new_state THEN + IF old_state = 0 OR old_state = 1 THEN + -- Decrement the old state count if record exists. + UPDATE lease6_stat + SET leases = GREATEST(leases - 1, 0) + WHERE subnet_id = old_subnet_id AND lease_type = old_lease_type + AND state = old_state; + END IF; + + IF new_state = 0 OR new_state = 1 THEN + -- Increment the new state count if record exists + UPDATE lease6_stat SET leases = leases + 1 + WHERE subnet_id = new_subnet_id AND lease_type = new_lease_type + AND state = new_state; + + -- Insert new state record if it does not exist + IF NOT FOUND THEN + INSERT INTO lease6_stat + VALUES (new_subnet_id, new_lease_type, new_state, 1); + END IF; + END IF; + END IF; +END; +\$\$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION lease6_ADEL_lease6_stat(IN old_state BIGINT, + IN old_subnet_id BIGINT, + IN old_lease_type BIGINT) +RETURNS VOID +AS \$\$ +BEGIN + IF old_state = 0 OR old_state = 1 THEN + -- Decrement the state count if record exists + UPDATE lease6_stat + SET leases = GREATEST(leases - 1, 0) + WHERE subnet_id = old_subnet_id AND lease_type = old_lease_type + AND state = old_state; + END IF; +END; +\$\$ LANGUAGE plpgsql; + +-- Create tables that contain the number of active leases. -- + +CREATE TABLE lease4_stat_by_client_class ( + client_class VARCHAR(128) NOT NULL PRIMARY KEY, + leases BIGINT NOT NULL +); + +CREATE TABLE lease6_stat_by_client_class ( + client_class VARCHAR(128) NOT NULL, + lease_type BIGINT NOT NULL, + leases BIGINT NOT NULL, + PRIMARY KEY (client_class, lease_type), + CONSTRAINT fk_lease6_stat_by_client_class_lease_type FOREIGN KEY (lease_type) + REFERENCES lease6_types (lease_type) +); + +-- Create procedures to be called for each row in after-event triggers for +-- INSERT, UPDATE and DELETE on lease tables. + +CREATE OR REPLACE FUNCTION lease4_AINS_lease4_stat_by_client_class(IN new_state BIGINT, + IN new_user_context TEXT) +RETURNS VOID +AS \$\$ +DECLARE + class VARCHAR(128); +BEGIN + -- Only state 0 is needed for lease limiting. + IF new_state = 0 THEN + -- Dive into client classes. + FOR class IN SELECT * FROM JSON_ARRAY_ELEMENTS(json_cast(new_user_context)->'ISC'->'client-classes') LOOP + SELECT TRIM('"' FROM class) INTO class; + + -- Upsert to increment the lease count. + UPDATE lease4_stat_by_client_class SET leases = leases + 1 + WHERE client_class = class; + IF NOT FOUND THEN + INSERT INTO lease4_stat_by_client_class VALUES (class, 1); + END IF; + END LOOP; + END IF; +END; +\$\$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION lease4_AUPD_lease4_stat_by_client_class(IN old_state BIGINT, + IN old_user_context TEXT, + IN new_state BIGINT, + IN new_user_context TEXT) +RETURNS VOID +AS \$\$ +DECLARE + old_client_classes TEXT; + new_client_classes TEXT; + class VARCHAR(128); + length INT; + i INT; +BEGIN + SELECT json_cast(old_user_context)->'ISC'->'client-classes' INTO old_client_classes; + SELECT json_cast(new_user_context)->'ISC'->'client-classes' INTO new_client_classes; + + IF old_state != new_state OR old_client_classes != new_client_classes THEN + -- Check if it's moving away from a counted state. + IF old_state = 0 THEN + -- Dive into client classes. + FOR class IN SELECT * FROM JSON_ARRAY_ELEMENTS(json_cast(old_client_classes)) LOOP + -- Decrement the lease count if the record exists. + UPDATE lease4_stat_by_client_class SET leases = GREATEST(leases - 1, 0) + WHERE client_class = class; + END LOOP; + END IF; + + -- Check if it's moving into a counted state. + IF new_state = 0 THEN + -- Dive into client classes. + FOR class IN SELECT * FROM JSON_ARRAY_ELEMENTS(json_cast(new_client_classes)) LOOP + -- Upsert to increment the lease count. + UPDATE lease4_stat_by_client_class SET leases = leases + 1 + WHERE client_class = class; + IF NOT FOUND THEN + INSERT INTO lease4_stat_by_client_class VALUES (class, 1); + END IF; + END LOOP; + END IF; + END IF; +END; +\$\$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION lease4_ADEL_lease4_stat_by_client_class(IN old_state BIGINT, + IN old_user_context TEXT) +RETURNS VOID +AS \$\$ +DECLARE + class VARCHAR(128); +BEGIN + -- Only state 0 is accounted for in lease limiting. + IF old_state = 0 THEN + -- Dive into client classes. + FOR class IN SELECT * FROM JSON_ARRAY_ELEMENTS(json_cast(old_user_context)->'ISC'->'client-classes') LOOP + SELECT TRIM('"' FROM class) INTO class; + + -- Decrement the lease count if the record exists. + UPDATE lease4_stat_by_client_class SET leases = GREATEST(leases - 1, 0) + WHERE client_class = class; + END LOOP; + END IF; +END; +\$\$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION lease6_AINS_lease6_stat_by_client_class(IN new_state BIGINT, + IN new_user_context TEXT, + IN new_lease_type BIGINT) +RETURNS VOID +AS \$\$ +DECLARE + client_classes TEXT; + class VARCHAR(128); + length INT; + i INT; +BEGIN + -- Only state 0 is needed for lease limiting. + IF new_state = 0 THEN + -- Dive into client classes. + FOR class IN SELECT * FROM JSON_ARRAY_ELEMENTS(json_cast(new_user_context)->'ISC'->'client-classes') LOOP + SELECT TRIM('"' FROM class) INTO class; + + -- Upsert to increment the lease count. + UPDATE lease6_stat_by_client_class SET leases = leases + 1 + WHERE client_class = class AND lease_type = new_lease_type; + IF NOT FOUND THEN + INSERT INTO lease6_stat_by_client_class VALUES (class, new_lease_type, 1); + END IF; + END LOOP; + END IF; +END; +\$\$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION lease6_AUPD_lease6_stat_by_client_class(IN old_state BIGINT, + IN old_user_context TEXT, + IN old_lease_type BIGINT, + IN new_state BIGINT, + IN new_user_context TEXT, + IN new_lease_type BIGINT) +RETURNS VOID +AS \$\$ +DECLARE + old_client_classes TEXT; + new_client_classes TEXT; + class VARCHAR(128); + length INT; + i INT; +BEGIN + SELECT json_cast(old_user_context)->'ISC'->'client-classes' INTO old_client_classes; + SELECT json_cast(new_user_context)->'ISC'->'client-classes' INTO new_client_classes; + + IF old_state != new_state OR old_client_classes != new_client_classes OR old_lease_type != new_lease_type THEN + -- Check if it's moving away from a counted state. + IF old_state = 0 THEN + -- Dive into client classes. + FOR class IN SELECT * FROM JSON_ARRAY_ELEMENTS(json_cast(old_client_classes)) LOOP + -- Decrement the lease count if the record exists. + UPDATE lease6_stat_by_client_class SET leases = GREATEST(leases - 1, 0) + WHERE client_class = class AND lease_type = old_lease_type; + END LOOP; + END IF; + + -- Check if it's moving into a counted state. + IF new_state = 0 THEN + -- Dive into client classes. + FOR class IN SELECT * FROM JSON_ARRAY_ELEMENTS(json_cast(new_client_classes)) LOOP + -- Upsert to increment the lease count. + UPDATE lease6_stat_by_client_class SET leases = leases + 1 + WHERE client_class = class AND lease_type = new_lease_type; + IF NOT FOUND THEN + INSERT INTO lease6_stat_by_client_class VALUES (class, new_lease_type, 1); + END IF; + END LOOP; + END IF; + END IF; +END; +\$\$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION lease6_ADEL_lease6_stat_by_client_class(IN old_state BIGINT, + IN old_user_context TEXT, + IN old_lease_type BIGINT) +RETURNS VOID +AS \$\$ +DECLARE + client_classes VARCHAR(1024); + class VARCHAR(128); + length INT; + i INT; +BEGIN + -- Only state 0 is accounted for in lease limiting. But check both states to be consistent with lease6_stat. + IF old_state = 0 THEN + -- Dive into client classes. + FOR class IN SELECT * FROM JSON_ARRAY_ELEMENTS(json_cast(old_user_context)->'ISC'->'client-classes') LOOP + SELECT TRIM('"' FROM class) INTO class; + + -- Decrement the lease count if the record exists. + UPDATE lease6_stat_by_client_class SET leases = GREATEST(leases - 1, 0) + WHERE client_class = class AND lease_type = old_lease_type; + END LOOP; + END IF; +END; +\$\$ LANGUAGE plpgsql; + +-- Recreate the after-event triggers for INSERT, UPDATE and DELETE on lease tables to call the -- +-- stored procedures above in pairs of two: for client classes and for subnets. -- + +DROP TRIGGER IF EXISTS stat_lease4_insert ON lease4; + +CREATE OR REPLACE FUNCTION func_lease4_AINS() +RETURNS trigger AS \$lease4_AINS\$ +BEGIN + IF isJsonSupported() = true THEN + PERFORM lease4_AINS_lease4_stat_by_client_class(NEW.state, NEW.user_context); + END IF; + PERFORM lease4_AINS_lease4_stat(NEW.state, NEW.subnet_id); + RETURN NULL; +END; +\$lease4_AINS\$ LANGUAGE plpgsql; + +CREATE TRIGGER lease4_AINS AFTER INSERT ON lease4 + FOR EACH ROW EXECUTE PROCEDURE func_lease4_AINS(); + +DROP TRIGGER IF EXISTS stat_lease4_update ON lease4; + +CREATE OR REPLACE FUNCTION func_lease4_AUPD() +RETURNS trigger AS \$lease4_AUPD\$ +BEGIN + IF isJsonSupported() = true THEN + PERFORM lease4_AUPD_lease4_stat_by_client_class(OLD.state, OLD.user_context, NEW.state, NEW.user_context); + END IF; + PERFORM lease4_AUPD_lease4_stat(OLD.state, OLD.subnet_id, NEW.state, NEW.subnet_id); + RETURN NULL; +END; +\$lease4_AUPD\$ LANGUAGE plpgsql; + +CREATE TRIGGER lease4_AUPD AFTER UPDATE ON lease4 + FOR EACH ROW EXECUTE PROCEDURE func_lease4_AUPD(); + +DROP TRIGGER IF EXISTS stat_lease4_delete ON lease4; + +CREATE OR REPLACE FUNCTION func_lease4_ADEL() +RETURNS trigger AS \$lease4_ADEL\$ +BEGIN + IF isJsonSupported() = true THEN + PERFORM lease4_ADEL_lease4_stat_by_client_class(OLD.state, OLD.user_context); + END IF; + PERFORM lease4_ADEL_lease4_stat(OLD.state, OLD.subnet_id); + RETURN NULL; +END; +\$lease4_ADEL\$ LANGUAGE plpgsql; + +CREATE TRIGGER lease4_ADEL AFTER DELETE ON lease4 + FOR EACH ROW EXECUTE PROCEDURE func_lease4_ADEL(); + +DROP TRIGGER IF EXISTS stat_lease6_insert ON lease6; + +CREATE OR REPLACE FUNCTION func_lease6_AINS() +RETURNS trigger AS \$lease6_AINS\$ +BEGIN + IF isJsonSupported() = true THEN + PERFORM lease6_AINS_lease6_stat_by_client_class(NEW.state, NEW.user_context, NEW.lease_type); + END IF; + PERFORM lease6_AINS_lease6_stat(NEW.state, NEW.subnet_id, NEW.lease_type); + RETURN NULL; +END; +\$lease6_AINS\$ LANGUAGE plpgsql; + +CREATE TRIGGER lease6_AINS AFTER INSERT ON lease6 + FOR EACH ROW EXECUTE PROCEDURE func_lease6_AINS(); + +DROP TRIGGER IF EXISTS stat_lease6_update ON lease6; + +CREATE OR REPLACE FUNCTION func_lease6_AUPD() +RETURNS trigger AS \$lease6_AUPD\$ +BEGIN + IF isJsonSupported() = true THEN + PERFORM lease6_AUPD_lease6_stat_by_client_class(OLD.state, OLD.user_context, OLD.lease_type, NEW.state, NEW.user_context, NEW.lease_type); + END IF; + PERFORM lease6_AUPD_lease6_stat(OLD.state, OLD.subnet_id, OLD.lease_type, NEW.state, NEW.subnet_id, NEW.lease_type); + RETURN NULL; +END; +\$lease6_AUPD\$ LANGUAGE plpgsql; + +CREATE TRIGGER lease6_AUPD AFTER UPDATE ON lease6 + FOR EACH ROW EXECUTE PROCEDURE func_lease6_AUPD(); + +DROP TRIGGER IF EXISTS stat_lease6_delete ON lease6; + +CREATE OR REPLACE FUNCTION func_lease6_ADEL() +RETURNS trigger AS \$lease6_ADEL\$ +BEGIN + IF isJsonSupported() = true THEN + PERFORM lease6_ADEL_lease6_stat_by_client_class(OLD.state, OLD.user_context, OLD.lease_type); + END IF; + PERFORM lease6_ADEL_lease6_stat(OLD.state, OLD.subnet_id, OLD.lease_type); + RETURN NULL; +END; +\$lease6_ADEL\$ LANGUAGE plpgsql; + +CREATE TRIGGER lease6_ADEL AFTER DELETE ON lease6 + FOR EACH ROW EXECUTE PROCEDURE func_lease6_ADEL(); + +-- Create functions that return an empty TEXT if all limits allow for more leases, or otherwise a +-- TEXT in one of the following JSON formats detailing the limit that was reached: +-- { "limit-type": "client-class", "name": foo, "lease-type": "address", "limit": 2, "count": 2 } +-- { "limit-type": "subnet", "id": 1, "lease-type": "IA_PD", "limit": 2, "count": 2 } +-- The following format for user_context is assumed: +-- { "ISC": { "limits": { "client-classes": [ { "name": "foo", "address-limit": 2, "prefix-limit": 1 } ], +-- "subnet": { "id": 1, "address-limit": 2, "prefix-limit": 1 } } } } + +CREATE OR REPLACE FUNCTION checkLease4Limits(user_context TEXT) +RETURNS TEXT +AS \$\$ +DECLARE + class TEXT; + name VARCHAR(255); + sid INT; + lease_limit INT; + lease_count INT; +BEGIN + -- Dive into client class limits. + FOR class IN SELECT * FROM JSON_ARRAY_ELEMENTS(json_cast(user_context)->'ISC'->'limits'->'client-classes') LOOP + SELECT TRIM('"' FROM (json_cast(class)->'name')::text) INTO name; + SELECT json_cast(class)->'address-limit' INTO lease_limit; + + IF lease_limit IS NOT NULL THEN + -- Get the lease count for this client class. + SELECT leases FROM lease4_stat_by_client_class INTO lease_count WHERE client_class = name; + IF lease_count IS NULL THEN + lease_count := 0; + END IF; + + -- Compare. Return immediately if the limit is surpassed. + IF lease_limit <= lease_count THEN + RETURN CONCAT('address limit ', lease_limit, ' for client class "', name, '", current lease count ', lease_count); + END IF; + END IF; + END LOOP; + + -- Dive into subnet limits. + SELECT json_cast(user_context)->'ISC'->'limits'->'subnet'->'id' INTO sid; + SELECT json_cast(user_context)->'ISC'->'limits'->'subnet'->'address-limit' INTO lease_limit; + + IF lease_limit IS NOT NULL THEN + -- Get the lease count for this client class. + SELECT leases FROM lease4_stat WHERE subnet_id = sid AND state = 0 INTO lease_count; + IF lease_count IS NULL THEN + lease_count := 0; + END IF; + + -- Compare. Return immediately if the limit is surpassed. + IF lease_limit <= lease_count THEN + RETURN CONCAT('address limit ', lease_limit, ' for subnet ID ', sid, ', current lease count ', lease_count); + END IF; + END IF; + + RETURN ''; +END; +\$\$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION checkLease6Limits(user_context TEXT) +RETURNS TEXT +AS \$\$ +DECLARE + class TEXT; + name VARCHAR(255); + sid INT; + lease_limit INT; + lease_count INT; +BEGIN + -- Dive into client class limits. + FOR class IN SELECT * FROM JSON_ARRAY_ELEMENTS(json_cast(user_context)->'ISC'->'limits'->'client-classes') LOOP + SELECT TRIM('"' FROM (json_cast(class)->'name')::text) INTO name; + SELECT json_cast(class)->'address-limit' INTO lease_limit; + + IF lease_limit IS NOT NULL THEN + -- Get the address count for this client class. + SELECT leases FROM lease6_stat_by_client_class WHERE client_class = name AND lease_type = 0 INTO lease_count; + IF lease_count IS NULL THEN + lease_count := 0; + END IF; + + -- Compare. Return immediately if the limit is surpassed. + IF lease_limit <= lease_count THEN + RETURN CONCAT('address limit ', lease_limit, ' for client class "', name, '", current lease count ', lease_count); + END IF; + END IF; + + SELECT json_cast(class)->'prefix-limit' INTO lease_limit; + IF lease_limit IS NOT NULL THEN + -- Get the prefix count for this client class. + SELECT leases FROM lease6_stat_by_client_class WHERE client_class = name AND lease_type = 2 INTO lease_count; + IF lease_count IS NULL THEN + lease_count := 0; + END IF; + + -- Compare. Return immediately if the limit is surpassed. + IF lease_limit <= lease_count THEN + RETURN CONCAT('prefix limit ', lease_limit, ' for client class "', name, '", current lease count ', lease_count); + END IF; + END IF; + END LOOP; + + -- Dive into subnet limits. + SELECT json_cast(user_context)->'ISC'->'limits'->'subnet'->'id' INTO sid; + SELECT json_cast(user_context)->'ISC'->'limits'->'subnet'->'address-limit' INTO lease_limit; + IF lease_limit IS NOT NULL THEN + -- Get the lease count for this subnet. + SELECT leases FROM lease6_stat WHERE subnet_id = sid AND lease_type = 0 AND state = 0 INTO lease_count; + IF lease_count IS NULL THEN + lease_count := 0; + END IF; + + -- Compare. Return immediately if the limit is surpassed. + IF lease_limit <= lease_count THEN + RETURN CONCAT('address limit ', lease_limit, ' for subnet ID ', sid, ', current lease count ', lease_count); + END IF; + END IF; + SELECT json_cast(user_context)->'ISC'->'limits'->'subnet'->'prefix-limit' INTO lease_limit; + IF lease_limit IS NOT NULL THEN + -- Get the lease count for this client class. + SELECT leases FROM lease6_stat WHERE subnet_id = sid AND lease_type = 2 AND state = 0 INTO lease_count; + IF lease_count IS NULL THEN + lease_count := 0; + END IF; + + -- Compare. Return immediately if the limit is surpassed. + IF lease_limit <= lease_count THEN + RETURN CONCAT('prefix limit ', lease_limit, ' for subnet ID ', sid, ', current lease count ', lease_count); + END IF; + END IF; + + RETURN ''; +END; +\$\$ LANGUAGE plpgsql; + +-- Update the schema version number. +UPDATE schema_version + SET version = '13', minor = '0'; + +-- This line concludes the schema upgrade to version 13. + +-- Commit the script transaction. +COMMIT; + +EOF diff --git a/src/share/database/scripts/pgsql/wipe_data.sh.in b/src/share/database/scripts/pgsql/wipe_data.sh.in index 154ddb0aa0..088b802e24 100644 --- a/src/share/database/scripts/pgsql/wipe_data.sh.in +++ b/src/share/database/scripts/pgsql/wipe_data.sh.in @@ -72,6 +72,8 @@ DELETE FROM lease4_stat; DELETE FROM lease6; DELETE FROM lease6_stat; DELETE FROM logs; +DELETE FROM lease4_stat_by_client_class; +DELETE FROM lease6_stat_by_client_class; -- Config Backend tables DELETE FROM dhcp4_audit; |