iptables

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `DEDUPLICATE`()
BEGIN
        SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
        START transaction;

        TRUNCATE ips_list;
        TRUNCATE net_list;
        INSERT INTO ips_list SELECT * FROM list WHERE MASK=32;
        INSERT INTO net_list SELECT * FROM list WHERE MASK<>32;
        TRUNCATE list;

        INSERT INTO list (CIDR)
        SELECT CONCAT(A, '.', B, '.', C, '.0/25') FROM ips_list WHERE D < 128 GROUP BY C, B, A HAVING COUNT(D) > 120
        UNION
        SELECT CONCAT(A, '.', B, '.', C, '.128/25') FROM ips_list WHERE D > 128 GROUP BY C, B, A HAVING COUNT(D) > 120
        UNION
        SELECT CONCAT(A, '.', B, '.', C, '.0/28') FROM ips_list WHERE D >= 0 and D <= 16 GROUP BY C, B, A HAVING COUNT(D) = 16
        union
        SELECT CONCAT(A, '.', B, '.', C, '.16/28') FROM ips_list WHERE D >= 16 and D <= 32 GROUP BY C, B, A HAVING COUNT(D) = 16
        union
        SELECT CONCAT(A, '.', B, '.', C, '.32/28') FROM ips_list WHERE D >= 32 and D <= 48 GROUP BY C, B, A HAVING COUNT(D) = 16
        union
        SELECT CONCAT(A, '.', B, '.', C, '.48/28') FROM ips_list WHERE D >= 48 and D <= 64 GROUP BY C, B, A HAVING COUNT(D) = 16
        union
        SELECT CONCAT(A, '.', B, '.', C, '.64/28')FROM ips_list WHERE D >= 64 and D <= 80 GROUP BY C, B, A HAVING COUNT(D) = 16
        union
        SELECT CONCAT(A, '.', B, '.', C, '.80/28') FROM ips_list WHERE D >= 80 and D <= 96 GROUP BY C, B, A HAVING COUNT(D) = 16
        union
        SELECT CONCAT(A, '.', B, '.', C, '.96/28') FROM ips_list WHERE D >= 96 and D <= 112 GROUP BY C, B, A HAVING COUNT(D) = 16
        union
        SELECT CONCAT(A, '.', B, '.', C, '.112/28') FROM ips_list WHERE D >= 112 and D <= 128 GROUP BY C, B, A HAVING COUNT(D) = 16
        union
        SELECT CONCAT(A, '.', B, '.', C, '.128/28') FROM ips_list WHERE D >= 128 and D <= 144 GROUP BY C, B, A HAVING COUNT(D) = 16
        union
        SELECT CONCAT(A, '.', B, '.', C, '.144/28') FROM ips_list WHERE D >= 144 and D <= 160 GROUP BY C, B, A HAVING COUNT(D) = 16
        union
        SELECT CONCAT(A, '.', B, '.', C, '.160/28') FROM ips_list WHERE D >= 160 and D <= 176 GROUP BY C, B, A HAVING COUNT(D) = 16
        union
        SELECT CONCAT(A, '.', B, '.', C, '.176/28') FROM ips_list WHERE D >= 176 and D <= 192 GROUP BY C, B, A HAVING COUNT(D) = 16
        union
        SELECT CONCAT(A, '.', B, '.', C, '.192/28') FROM ips_list WHERE D >= 192 and D <= 208 GROUP BY C, B, A HAVING COUNT(D) = 16
        union
        SELECT CONCAT(A, '.', B, '.', C, '.208/28') FROM ips_list WHERE D >= 208 and D <= 224 GROUP BY C, B, A HAVING COUNT(D) = 16
        union
        SELECT CONCAT(A, '.', B, '.', C, '.224/28') FROM ips_list WHERE D >= 224 and D <= 240 GROUP BY C, B, A HAVING COUNT(D) = 16
        union
        SELECT CONCAT(A, '.', B, '.', C, '.240/28') FROM ips_list WHERE D >= 240 and D <= 256 GROUP BY C, B, A HAVING COUNT(D) = 16;

        INSERT INTO net_list SELECT * FROM list WHERE MASK<>32;
        TRUNCATE list;

        INSERT INTO list (CIDR)
        select t2.IP FROM net_list t1 left join ips_list t2 on (t2.IP_N BETWEEN t1.IP_MIN_N and t1.IP_MAX_N) where t2.IP_N is not null GROUP BY t2.IP;

        delete from ips_list where ips_list.IP_N IN (SELECT IP_N FROM list);
        TRUNCATE list;

        TRUNCATE public.blocklist;
        insert into public.blocklist select A, B, C, D, MASK, IP, IP_N, IP_MIN, IP_MIN_N, IP_MAX, IP_MAX_N from net_list;
        insert into public.blocklist select A, B, C, D, MASK, IP, IP_N, IP_MIN, IP_MIN_N, IP_MAX, IP_MAX_N from ips_list;
        delete from public.blocklist where IP='0.0.0.0';

        COMMIT;
END ;;
DELIMITER ;