You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafficcontrol.apache.org by GitBox <gi...@apache.org> on 2021/01/12 19:30:38 UTC
[GitHub] [trafficcontrol] mattjackson220 commented on a change in pull request #5415: Adding check to make sure that you cannot add two servers with identical content
mattjackson220 commented on a change in pull request #5415:
URL: https://github.com/apache/trafficcontrol/pull/5415#discussion_r555918047
##########
File path: traffic_ops/app/db/migrations/2021010900000000_server_ip_profile_trigger_update.sql
##########
@@ -0,0 +1,73 @@
+/*
+ Licensed under the Apache License, Version 2.0 (the "License");
+ you may not use this file except in compliance with the License.
+ You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+*/
+
+-- +goose Up
+-- +goose StatementBegin
+CREATE OR REPLACE FUNCTION after_ip_address_table()
+ RETURNS TRIGGER
+AS
+$$
+DECLARE
+ server_count BIGINT;
+ server_id BIGINT;
+ server_profile BIGINT;
+ new_profile BIGINT;
+BEGIN
+ SELECT profile INTO new_profile from server s WHERE s.id = NEW.server;
+ WITH server_ips AS (
+ SELECT s.id as sid, ip.interface, i.name, ip.address, s.profile, ip.server
+ FROM server s
+ JOIN interface i
+ on i.server = s.ID
+ JOIN ip_address ip
+ on ip.Server = s.ID and ip.interface = i.name
+ WHERE i.monitor = true AND ip.service_address = true
+ )
+ SELECT count(distinct(sip.sid)), sip.sid, sip.profile
+ INTO server_count, server_id, server_profile
+ FROM server_ips sip
+ WHERE (sip.server <> NEW.server AND sip.address = NEW.address AND sip.profile = new_profile)
+ GROUP BY sip.sid, sip.profile;
+
+ IF server_count > 0 THEN
+ RAISE EXCEPTION 'ip_address is not unique across the server [id:%] profile [id:%], [%] conflicts',
+ server_id,
+ server_profile,
+ server_count;
+ END IF;
+ RETURN NEW;
+END;
+$$ LANGUAGE PLPGSQL;
+-- +goose StatementEnd
+
+CREATE TRIGGER after_create_ip_address_trigger
+ AFTER INSERT
+ ON ip_address
+ FOR EACH ROW
+EXECUTE PROCEDURE after_ip_address_table();
+
+CREATE TRIGGER after_update_ip_address_trigger
+ BEFORE UPDATE
Review comment:
should be `AFTER`
##########
File path: traffic_ops/app/db/migrations/2021010900000000_server_ip_profile_trigger_update.sql
##########
@@ -0,0 +1,73 @@
+/*
+ Licensed under the Apache License, Version 2.0 (the "License");
+ you may not use this file except in compliance with the License.
+ You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+*/
+
+-- +goose Up
+-- +goose StatementBegin
+CREATE OR REPLACE FUNCTION after_ip_address_table()
+ RETURNS TRIGGER
+AS
+$$
+DECLARE
+ server_count BIGINT;
+ server_id BIGINT;
+ server_profile BIGINT;
+ new_profile BIGINT;
+BEGIN
+ SELECT profile INTO new_profile from server s WHERE s.id = NEW.server;
+ WITH server_ips AS (
+ SELECT s.id as sid, ip.interface, i.name, ip.address, s.profile, ip.server
+ FROM server s
+ JOIN interface i
+ on i.server = s.ID
+ JOIN ip_address ip
+ on ip.Server = s.ID and ip.interface = i.name
+ WHERE i.monitor = true AND ip.service_address = true
+ )
+ SELECT count(distinct(sip.sid)), sip.sid, sip.profile
+ INTO server_count, server_id, server_profile
+ FROM server_ips sip
+ WHERE (sip.server <> NEW.server AND sip.address = NEW.address AND sip.profile = new_profile)
Review comment:
looks like this does allow the same ip/profile combo if the netmask on the ip address is different. i dont think we want to allow that either so we will have to strip the netmask off before checking equality here
##########
File path: traffic_ops/app/db/migrations/2021010900000000_server_ip_profile_trigger_update.sql
##########
@@ -0,0 +1,73 @@
+/*
+ Licensed under the Apache License, Version 2.0 (the "License");
+ you may not use this file except in compliance with the License.
+ You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+*/
+
+-- +goose Up
+-- +goose StatementBegin
+CREATE OR REPLACE FUNCTION after_ip_address_table()
+ RETURNS TRIGGER
+AS
+$$
+DECLARE
+ server_count BIGINT;
+ server_id BIGINT;
+ server_profile BIGINT;
+ new_profile BIGINT;
+BEGIN
+ SELECT profile INTO new_profile from server s WHERE s.id = NEW.server;
+ WITH server_ips AS (
+ SELECT s.id as sid, ip.interface, i.name, ip.address, s.profile, ip.server
+ FROM server s
+ JOIN interface i
+ on i.server = s.ID
+ JOIN ip_address ip
+ on ip.Server = s.ID and ip.interface = i.name
+ WHERE i.monitor = true AND ip.service_address = true
+ )
+ SELECT count(distinct(sip.sid)), sip.sid, sip.profile
+ INTO server_count, server_id, server_profile
+ FROM server_ips sip
+ WHERE (sip.server <> NEW.server AND sip.address = NEW.address AND sip.profile = new_profile)
+ GROUP BY sip.sid, sip.profile;
+
+ IF server_count > 0 THEN
+ RAISE EXCEPTION 'ip_address is not unique across the server [id:%] profile [id:%], [%] conflicts',
+ server_id,
+ server_profile,
+ server_count;
+ END IF;
+ RETURN NEW;
+END;
+$$ LANGUAGE PLPGSQL;
+-- +goose StatementEnd
+
+CREATE TRIGGER after_create_ip_address_trigger
+ AFTER INSERT
+ ON ip_address
+ FOR EACH ROW
+EXECUTE PROCEDURE after_ip_address_table();
+
+CREATE TRIGGER after_update_ip_address_trigger
+ BEFORE UPDATE
+ ON ip_address
+ FOR EACH ROW
+ WHEN (NEW.address <> OLD.address)
+EXECUTE PROCEDURE after_ip_address_table();
+
Review comment:
should we drop the old before_ip_address triggers?
##########
File path: traffic_ops/app/db/migrations/2021010900000000_server_ip_profile_trigger_update.sql
##########
@@ -0,0 +1,73 @@
+/*
+ Licensed under the Apache License, Version 2.0 (the "License");
+ you may not use this file except in compliance with the License.
+ You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+*/
+
+-- +goose Up
+-- +goose StatementBegin
+CREATE OR REPLACE FUNCTION after_ip_address_table()
+ RETURNS TRIGGER
+AS
+$$
+DECLARE
+ server_count BIGINT;
+ server_id BIGINT;
+ server_profile BIGINT;
+ new_profile BIGINT;
+BEGIN
+ SELECT profile INTO new_profile from server s WHERE s.id = NEW.server;
+ WITH server_ips AS (
+ SELECT s.id as sid, ip.interface, i.name, ip.address, s.profile, ip.server
+ FROM server s
+ JOIN interface i
+ on i.server = s.ID
+ JOIN ip_address ip
+ on ip.Server = s.ID and ip.interface = i.name
+ WHERE i.monitor = true AND ip.service_address = true
+ )
+ SELECT count(distinct(sip.sid)), sip.sid, sip.profile
+ INTO server_count, server_id, server_profile
+ FROM server_ips sip
+ WHERE (sip.server <> NEW.server AND sip.address = NEW.address AND sip.profile = new_profile)
Review comment:
the `new_profile` variable could be deleted and have the `SELECT` just moved in here instead
##########
File path: traffic_ops/app/db/migrations/2021010900000000_server_ip_profile_trigger_update.sql
##########
@@ -0,0 +1,73 @@
+/*
+ Licensed under the Apache License, Version 2.0 (the "License");
+ you may not use this file except in compliance with the License.
+ You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+*/
+
+-- +goose Up
+-- +goose StatementBegin
+CREATE OR REPLACE FUNCTION after_ip_address_table()
+ RETURNS TRIGGER
+AS
+$$
+DECLARE
+ server_count BIGINT;
+ server_id BIGINT;
+ server_profile BIGINT;
+ new_profile BIGINT;
+BEGIN
+ SELECT profile INTO new_profile from server s WHERE s.id = NEW.server;
+ WITH server_ips AS (
+ SELECT s.id as sid, ip.interface, i.name, ip.address, s.profile, ip.server
+ FROM server s
+ JOIN interface i
+ on i.server = s.ID
+ JOIN ip_address ip
+ on ip.Server = s.ID and ip.interface = i.name
+ WHERE i.monitor = true AND ip.service_address = true
Review comment:
if we limit it to only blocking when the ip address is a service address and the monitoring is true, then we probably need to add more triggers to check this again right? if the service address is changed then its covered by these since its in the ip_address table but we will need to add triggers for the interface table for if monitor is changed. might be worth a discussion if we want to allow duplicates if they are unmonitored (id say we probably want to block that case too)
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org