USE test;

# On node_1
connection node_1;
CREATE TABLE networks (
`tenant_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`id` varchar(36) COLLATE utf8_unicode_ci NOT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`status` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
`admin_state_up` tinyint(1) DEFAULT NULL,
`shared` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE ports (
`tenant_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`id` varchar(36) COLLATE utf8_unicode_ci NOT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`network_id` varchar(36) COLLATE utf8_unicode_ci NOT NULL,
`mac_address` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
`admin_state_up` tinyint(1) NOT NULL,
`status` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
`device_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`device_owner` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `network_id` (`network_id`),
CONSTRAINT `ports_ibfk_1` FOREIGN KEY (`network_id`) REFERENCES networks (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE subnets (
`tenant_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`id` varchar(36) COLLATE utf8_unicode_ci NOT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`network_id` varchar(36) COLLATE utf8_unicode_ci DEFAULT NULL,
`ip_version` int(11) NOT NULL,
`cidr` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
`gateway_ip` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
`enable_dhcp` tinyint(1) DEFAULT NULL,
`shared` tinyint(1) DEFAULT NULL,
`ipv6_ra_mode` enum('slaac','dhcpv6-stateful','dhcpv6-stateless') COLLATE utf8_unicode_ci DEFAULT NULL,
`ipv6_address_mode` enum('slaac','dhcpv6-stateful','dhcpv6-stateless') COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `network_id` (`network_id`),
CONSTRAINT `subnets_ibfk_1` FOREIGN KEY (`network_id`) REFERENCES networks (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `ipallocations` (
`port_id` varchar(36) COLLATE utf8_unicode_ci DEFAULT NULL,
`ip_address` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
`subnet_id` varchar(36) COLLATE utf8_unicode_ci NOT NULL,
`network_id` varchar(36) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`ip_address`,`subnet_id`,`network_id`),
KEY `port_id` (`port_id`),
KEY `subnet_id` (`subnet_id`),
KEY `network_id` (`network_id`),
CONSTRAINT `ipallocations_ibfk_1` FOREIGN KEY (`port_id`) REFERENCES `ports` (`id`) ON DELETE CASCADE,
CONSTRAINT `ipallocations_ibfk_2` FOREIGN KEY (`subnet_id`) REFERENCES `subnets` (`id`) ON DELETE CASCADE,
CONSTRAINT `ipallocations_ibfk_3` FOREIGN KEY (`network_id`) REFERENCES `networks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO networks VALUES ('f37aa3fe-ab99-4d0f-a566-6cd3169d7516','f37aa3fe-ab99-4d0f-a566-6cd3169d7516','MyNet','ACTIVE',0,0);
INSERT INTO ports VALUES ('','f37aa3fe-ab99-4d0f-a566-6cd3169d7516','','f37aa3fe-ab99-4d0f-a566-6cd3169d7516','fa:16:3e:e3:cc:bb',1,'DOWN','f37aa3fe-ab99-4d0f-a566-6cd3169d7516','network:router_gateway');
INSERT INTO subnets VALUES ('f37aa3fe-ab99-4d0f-a566-6cd3169d7516','f37aa3fe-ab99-4d0f-a566-6cd3169d7516','ext-subnet','f37aa3fe-ab99-4d0f-a566-6cd3169d7516',4,'10.25.0.0/24','10.25.0.4',0,1,NULL,NULL);
INSERT INTO ipallocations VALUES ('f37aa3fe-ab99-4d0f-a566-6cd3169d7516','10.25.0.17','f37aa3fe-ab99-4d0f-a566-6cd3169d7516','f37aa3fe-ab99-4d0f-a566-6cd3169d7516');
select * from ports where ports.id = 'f37aa3fe-ab99-4d0f-a566-6cd3169d7516';
tenant_id	id	name	network_id	mac_address	admin_state_up	status	device_id	device_owner
	f37aa3fe-ab99-4d0f-a566-6cd3169d7516		f37aa3fe-ab99-4d0f-a566-6cd3169d7516	fa:16:3e:e3:cc:bb	1	DOWN	f37aa3fe-ab99-4d0f-a566-6cd3169d7516	network:router_gateway
connection node_2;
select * from ports where ports.id = 'f37aa3fe-ab99-4d0f-a566-6cd3169d7516';
tenant_id	id	name	network_id	mac_address	admin_state_up	status	device_id	device_owner
	f37aa3fe-ab99-4d0f-a566-6cd3169d7516		f37aa3fe-ab99-4d0f-a566-6cd3169d7516	fa:16:3e:e3:cc:bb	1	DOWN	f37aa3fe-ab99-4d0f-a566-6cd3169d7516	network:router_gateway
connection node_1;
DELETE FROM ports WHERE ports.id = 'f37aa3fe-ab99-4d0f-a566-6cd3169d7516';
select * from networks;
tenant_id	id	name	status	admin_state_up	shared
f37aa3fe-ab99-4d0f-a566-6cd3169d7516	f37aa3fe-ab99-4d0f-a566-6cd3169d7516	MyNet	ACTIVE	0	0
select * from ports;
tenant_id	id	name	network_id	mac_address	admin_state_up	status	device_id	device_owner
select * from subnets;
tenant_id	id	name	network_id	ip_version	cidr	gateway_ip	enable_dhcp	shared	ipv6_ra_mode	ipv6_address_mode
f37aa3fe-ab99-4d0f-a566-6cd3169d7516	f37aa3fe-ab99-4d0f-a566-6cd3169d7516	ext-subnet	f37aa3fe-ab99-4d0f-a566-6cd3169d7516	4	10.25.0.0/24	10.25.0.4	0	1	NULL	NULL
select * from ipallocations;
port_id	ip_address	subnet_id	network_id
select * from ports;
tenant_id	id	name	network_id	mac_address	admin_state_up	status	device_id	device_owner

# On node_2
connection node_2;
select * from networks;
tenant_id	id	name	status	admin_state_up	shared
f37aa3fe-ab99-4d0f-a566-6cd3169d7516	f37aa3fe-ab99-4d0f-a566-6cd3169d7516	MyNet	ACTIVE	0	0
select * from ports;
tenant_id	id	name	network_id	mac_address	admin_state_up	status	device_id	device_owner
select * from subnets;
tenant_id	id	name	network_id	ip_version	cidr	gateway_ip	enable_dhcp	shared	ipv6_ra_mode	ipv6_address_mode
f37aa3fe-ab99-4d0f-a566-6cd3169d7516	f37aa3fe-ab99-4d0f-a566-6cd3169d7516	ext-subnet	f37aa3fe-ab99-4d0f-a566-6cd3169d7516	4	10.25.0.0/24	10.25.0.4	0	1	NULL	NULL
select * from ipallocations;
port_id	ip_address	subnet_id	network_id
select * from ports;
tenant_id	id	name	network_id	mac_address	admin_state_up	status	device_id	device_owner
connection node_1;
drop table ipallocations;
drop table subnets;
drop table ports;
drop table networks;
disconnect node_2;
disconnect node_1;