Preveting overlap CIDR for both IPv4 and IPv6

gistfile1.txt

"""Add SP and trigger for Mysql and add gist for Postgresql

Revision ID: 23c280e37c2c
Revises: 30107ab6a3ee
Create Date: 2016-05-26 13:49:36.552012

"""
from alembic import op
from neutron.db.migration.alembic_migrations import replaceable_obj

# revision identifiers, used by Alembic.
revision = '23c280e37c2c'
down_revision = '30107ab6a3ee'


add_stored_procedure_for_mysql = replaceable_obj.ReplaceableObject(
    "cidr_overlap(cidr1 varchar(30), ip_version_1 tinyint, "
    "cidr2 varchar(30), ip_version_2 tinyint)",
    """
    RETURNS TINYINT
    BEGIN
    DECLARE bitmask INT;
    IF (ip_version_1 = 6 AND ip_version_2 = 6) THEN
    RETURN
        inet6_aton(substring_index(cidr1, '/', 1)) & ~(pow(2,
            (32 - least(cast(substring_index(cidr1, '/', -1) as signed integer),
             cast(substring_index(cidr2, '/', -1) as signed integer)))) - 1) =
        inet6_aton(substring_index(cidr2, '/', 1)) & ~(pow(2,
            (32 - least(cast(substring_index(cidr1, '/', -1) as signed integer),
            cast(substring_index(cidr2, '/', -1) as signed integer)))) - 1);
    ELSE
    SET bitmask = pow(
        2,
        (32 - least(
            cast(substring_index(cidr1, '/', -1) as signed integer),
            cast(substring_index(cidr2, '/', -1) as signed integer)
        ))
    ) - 1;

    RETURN
        inet_aton(substring_index(cidr1, '/', 1)) & ~bitmask =
        inet_aton(substring_index(cidr2, '/', 1)) & ~bitmask;
    END IF;
    END
    """
)

add_trigger_for_mysql = replaceable_obj.ReplaceableObject(
    "no_overlap_cidr_subnets",
    """
    BEFORE INSERT ON subnets
    FOR EACH ROW
    BEGIN
      DECLARE msg VARCHAR(200);
      IF (EXISTS(SELECT * FROM subnets WHERE network_id = NEW.network_id
        AND cidr_overlap(cidr, ip_version, NEW.cidr, NEW.ip_version))) THEN
        SET msg = CONCAT(
            'inserted subnet ', NEW.cidr,
            ' conflicts with existing subnets');
        SIGNAL sqlstate '45000'
            SET MESSAGE_TEXT = msg;
      END IF;
    END
    """
)


def upgrade():
    if op.get_bind().engine.name == 'mysql':
        op.create_sp(add_stored_procedure_for_mysql)
        op.create_trigger(add_trigger_for_mysql)
    elif op.get_bind().engine.name == 'postgresql':
        if op.get_bind().engine.dialect.server_version_info >= (9, 4, 0):
            # (namnh): This feature is available for Postgresql 9.4 and
            # higher versions.
            op.execute("CREATE EXTENSION btree_gist")
            op.execute("ALTER TABLE subnets ADD CONSTRAINT "
                       "no_overlap_cidr_subnets EXCLUDE USING "
                       "gist((cast(cidr AS CIDR)) inet_ops WITH &&, "
                       "network_id WITH =)")
        else:
            return
    else:
        return