Implement domains

CREATE DOMAIN public.ip_port AS integer
	CONSTRAINT ip_port_check CHECK (((VALUE >= 1) AND (VALUE <= 65535)));
CREATE DOMAIN public.ip_protocol AS smallint
	CONSTRAINT ip_protocol_check CHECK (((VALUE >= 0) AND (VALUE <= 255)));

Perhaps follow the JSON implementation

Also, see the CreateColumn example

Testing the custom type

To test, perhaps select the following minimal example:

from sqlalchemy import Table, Metadata, Column, Integer, String
from sqlalchemy import schema

metadata = MetaData()

table = Table('mytable', MetaData(),
        Column('x', Integer, info={"special":True}, primary_key=True),
        Column('y', String(50)),
        Column('z', String(20), info={"special":True})
    )

metadata.create_all(conn)

…and in a test, we might do the following:

from unittest import TestCase
from pycroft.model.types import IpPort

class IpPortTestCase(TestCase):
    def setUp(self):
        # TODO set up this.conn
        this.conn = None
        this.metadata = MetaData()

        table = Table(
            'mytable', this.metadata,
            Column('x', Integer, primary_key=True),
            Column('z', IpPort)
        )

    def safe_create_all(self):
        try:
            this.metadata.create_all(this.conn)
        except OperationalError:
            this.fail("Could not create table with custom type")
        finally:
            pass  # TODO rollback stuff

    def test_value_set(self):
        # TODO add a row with that value and retrieve it again
        pass

    def test_value_filter(self):
        # TODO add two rows with that value and filter after the one
        pass

    def test_constraint(self):
        # TODO try to add something violating the check constraints
        pass

Perhaps the following should suffice:

class IpPort(sqltypes.TypeEngine):
    """Represent our custom IpPort type.
    """

    __visit_name__ = "Test What should go here"


ischema_names["ip_port"] = IpPort

[0/3] Constraints/Features

OutsideIpAddress.ip_address is /32,v4

--- OutsideIPAddress is /32v4
CREATE TABLE public."OutsideIPAddress" (
    nat_domain integer NOT NULL,
    ip_address inet NOT NULL,
    owner integer,
    CONSTRAINT "OutsideIPAddress_ip_address_host"
        CHECK (((family(ip_address) = 4) AND (masklen(ip_address) = 32)))
);

Realizable as follows:

__table_args__ = (
    CheckConstraint(
        '(((family(ip_address) = 4) AND (masklen(ip_address) = 32)))',
        name='outside_ip_is_host_and_v4',
    ),
)

OutsideIpAddress.ip is /32,v4

--- DhcpHostReservation refs /32v4
ALTER TABLE public."DHCPHostReservation"
    ADD CONSTRAINT "DHCPHostReservation_ip_check" CHECK (((family(ip) = 4) AND (masklen(ip) = 32))) NOT VALID;

Forwarding.protocol $∈ \{6, 17, 33, 132\}$

--- Forwarding protocol in ${6,17,33,132}$
ALTER TABLE public."Forwarding"
    ADD CONSTRAINT "Forwarding_protocol_port_check" CHECK (
CASE
    WHEN ((outside_port IS NOT NULL) OR (inside_port IS NOT NULL)) THEN ((protocol)::smallint = ANY (ARRAY[6, 17, 33, 132]))
    ELSE NULL::boolean
END) NOT VALID;

InsideNetwork.(gateway << ip_network)

--- Gateway << InsideNetwork
ALTER TABLE public."InsideNetwork"
    ADD CONSTRAINT "InsideNetwork_gateway_check" CHECK ((gateway << ip_network)) NOT VALID;

InsideNetwork.ip_network is /32,v4

--- in_net is v4
ALTER TABLE public."InsideNetwork"
    ADD CONSTRAINT "InsideNetwork_ip_network_family_check" CHECK ((family(ip_network) = 4)) NOT VALID;

InsideNetwork.ip_network disjoint

-- in_nets are disjoint
ALTER TABLE ONLY public."InsideNetwork"
    ADD CONSTRAINT "InsideNetwork_nat_domain_ip_network_excl" EXCLUDE USING gist (nat_domain WITH =, ip_network inet_ops WITH &&);

PKey-Constraints

FKey-Constraints

Uniq-Constraints

Translation.inside_network disjoint

--- Translation in_nets excluded
ALTER TABLE ONLY public."Translation"
    ADD CONSTRAINT "Translation_Inside_excl" EXCLUDE USING gist (nat_domain WITH =, inside_network inet_ops WITH &&);

DHCPHostReservation.inside_network exists

reasoning

This realizes the weak fkey =DHCPHostReservation.inside_network<<=InsideNetwork

src

CREATE FUNCTION public."DHCPHostReservation_InsideNetwork_exists"() RETURNS trigger
    LANGUAGE plpgsql STABLE STRICT LEAKPROOF
    AS $$BEGIN
	IF NOT EXISTS(SELECT FROM "InsideNetwork" WHERE nat_domain = NEW.nat_domain AND ip_network >> NEW.ip) THEN
		RAISE EXCEPTION 'InsideNetwork contains no ip_network for IP % in nat_domain %', NEW.ip, NEW.nat_domain USING ERRCODE = 'integrity_constraint_violation';
	END IF;
	RETURN NEW;
END$$;
CREATE CONSTRAINT TRIGGER "DHCPHostReservation_InsideNetwork_exists"
    AFTER INSERT OR UPDATE OF ip ON public."DHCPHostReservation"
    DEFERRABLE INITIALLY IMMEDIATE
    FOR EACH ROW EXECUTE PROCEDURE public."DHCPHostReservation_InsideNetwork_exists"();

InsideNetwork del/up/trunc

reasoning

These are effectively cascades to DHCPHostReservation, Translation. This is necessary because

DHCPHostReservation.ip << InsideNetwork.ip_network
Translation.inside_network << InsideNetwork.ip_network

are weak couplings instead of FKeys.

src

CREATE FUNCTION public."InsideNetwork_delete"() RETURNS trigger
    LANGUAGE plpgsql STRICT LEAKPROOF
    AS $$BEGIN
	DELETE FROM "DHCPHostReservation" WHERE nat_domain = OLD.nat_domain AND ip << OLD.Ip_network;
	DELETE FROM "Translation" WHERE nat_domain = OLD.nat_domain AND inside_network <<= OLD.ip_network;
	RETURN NULL;
END$$;
CREATE TRIGGER "InsideNetwork_delete" AFTER DELETE ON public."InsideNetwork" FOR EACH ROW EXECUTE PROCEDURE public."InsideNetwork_delete"();

CREATE FUNCTION public."InsideNetwork_truncate"() RETURNS trigger
    LANGUAGE plpgsql STRICT LEAKPROOF
    AS $$BEGIN
	TRUNCATE "DHCPHostReservation";
	TRUNCATE "Translation";
	RETURN NULL;
END$$;
CREATE TRIGGER "InsideNetwork_truncate" AFTER TRUNCATE ON public."InsideNetwork" FOR EACH STATEMENT EXECUTE PROCEDURE public."InsideNetwork_truncate"();

CREATE FUNCTION public."InsideNetwork_update"() RETURNS trigger
    LANGUAGE plpgsql STRICT LEAKPROOF
    AS $$BEGIN
	DELETE FROM "DHCPHostReservation" WHERE nat_domain = OLD.nat_domain AND ip << OLD.ip_network AND NOT ip << NEW.ip_network;
	DELETE FROM "Translation" WHERE nat_domain = OLD.nat_domain AND inside_network <<= OLD.ip_network AND NOT inside_network <<= NEW.ip_network;
	RETURN NULL;
END$$;
CREATE TRIGGER "InsideNetwork_update" AFTER UPDATE OF ip_network ON public."InsideNetwork" FOR EACH ROW EXECUTE PROCEDURE public."InsideNetwork_update"();

∀Translation ∃InsideNetwork

reasoning

This realizes the weak fkey Translation.inside_network<<=InsideNetwork

src

CREATE FUNCTION public."Translation_InsideNetwork_exists"() RETURNS trigger
    LANGUAGE plpgsql STRICT
    AS $$BEGIN
	IF NOT EXISTS(SELECT FROM "InsideNetwork" WHERE nat_domain = NEW.nat_domain AND ip_network >>= NEW.inside_network) THEN
		--RAISE EXCEPTION 'No corresponding InsideNetwork for Translation: %', NEW USING ERRCODE = 'integrity_constraint_violation';
		RAISE EXCEPTION integrity_constraint_violation USING DETAIL = FORMAT('No corresponding %I for %I: %s', 'InsideNetwork', 'Translation', NEW), TABLE = 'Translation';
	END IF;
	RETURN NEW;
END$$;
CREATE CONSTRAINT TRIGGER "Translation_InsideNetwork_exists"
    AFTER INSERT OR UPDATE OF inside_network ON public."Translation"
    DEFERRABLE INITIALLY IMMEDIATE
    FOR EACH ROW EXECUTE PROCEDURE public."Translation_InsideNetwork_exists"();

Forwarding needs a translation (unused)

--- TODO hold on a sec, this is not even used!
CREATE FUNCTION public."Forwarding_Translation_exists"() RETURNS trigger
    LANGUAGE plpgsql
    AS $$BEGIN
	IF NOT EXISTS(SELECT FROM "Translation" WHERE nat_domain = NEW.nat_domain AND outside_address = NEW.outside_address AND inside_network >> NEW.inside_address) THEN
		RAISE EXCEPTION 'No corresponding Translation exists for Forwarding: %', NEW USING ERRCODE = 'integrity_constraint_violation';
	END IF;
	RETURN NEW;
END$$;
'col2 > col3 + 5', name='check1'

Translation del/up (unused)

--- UNUSED
CREATE FUNCTION public."Translation_delete"() RETURNS trigger
    LANGUAGE plpgsql STRICT LEAKPROOF
    AS $$BEGIN
	DELETE FROM "Forwarding" WHERE nat_domain = OLD.nat_domain AND inside_address <<= OLD.inside_network;
	RETURN NULL;
END$$;

--- UNUSED
CREATE FUNCTION public."Translation_update"() RETURNS trigger
    LANGUAGE plpgsql STRICT LEAKPROOF
    AS $$BEGIN
	DELETE FROM "Forwarding" WHERE nat_domain = OLD.nat_domain AND inside_address <<= OLD.inside_network AND NOT inside_address <<= NEW.inside_network;
	RETURN NULL;
END$$;

Translation truncate

reasoning?

This realizes a cascade to Forwarding – but wouldn’t this make more sense on InsideNetwork or OutsideIpAddress? Or anything to which we have a weak relationship?

src

CREATE FUNCTION public."Translation_truncate"() RETURNS trigger
    LANGUAGE plpgsql STRICT LEAKPROOF
    AS $$BEGIN
	TRUNCATE "Forwarding";
	RETURN NULL;
END$$;
CREATE TRIGGER "Translation_truncate" AFTER TRUNCATE ON public."Translation" FOR EACH STATEMENT EXECUTE PROCEDURE public."Translation_truncate"();
--- FKEY-Constraints…

lukasjuhrich/nat.org

Implement domains

Testing the custom type

[0/3] Constraints/Features

OutsideIpAddress.ip_address is /32,v4

OutsideIpAddress.ip is /32,v4

Forwarding.protocol $∈ \{6, 17, 33, 132\}$

InsideNetwork.(gateway << ip_network)

InsideNetwork.ip_network is /32,v4

InsideNetwork.ip_network disjoint

PKey-Constraints

FKey-Constraints

Uniq-Constraints

Translation.inside_network disjoint

DHCPHostReservation.inside_network exists

reasoning

src

InsideNetwork del/up/trunc

reasoning

src

∀Translation ∃InsideNetwork

reasoning

src

Forwarding needs a translation (unused)

Translation del/up (unused)

Translation truncate

reasoning?

src