From b1ecd2e7adde68817e0cfedcba944799c8107c84 Mon Sep 17 00:00:00 2001 From: Marcin Siodelski Date: Fri, 1 Apr 2016 09:49:06 +0200 Subject: [PATCH] [4302] Additional indexes added to search for hosts in MySQL. --- src/bin/admin/scripts/mysql/dhcpdb_create.mysql | 9 +++++++++ .../tests/generic_host_data_source_unittest.cc | 11 +++++++++++ src/lib/dhcpsrv/testutils/schema_mysql_copy.h | 7 +++++++ 3 files changed, 27 insertions(+) diff --git a/src/bin/admin/scripts/mysql/dhcpdb_create.mysql b/src/bin/admin/scripts/mysql/dhcpdb_create.mysql index b3d5c4e758..bd159d547c 100755 --- a/src/bin/admin/scripts/mysql/dhcpdb_create.mysql +++ b/src/bin/admin/scripts/mysql/dhcpdb_create.mysql @@ -406,6 +406,15 @@ CREATE UNIQUE INDEX key_dhcp4_identifier_subnet_id ON hosts (dhcp_identifier ASC DROP INDEX key_dhcp6_identifier_subnet_id ON hosts; CREATE UNIQUE INDEX key_dhcp6_identifier_subnet_id ON hosts (dhcp_identifier ASC , dhcp_identifier_type ASC , dhcp6_subnet_id ASC); +# Create index to search for reservations using IP address and subnet id. +# This unique index guarantees that there is only one occurence of the +# particular IPv4 address for a given subnet. +CREATE UNIQUE INDEX key_dhcp4_ipv4_address_subnet_id ON hosts (ipv4_address ASC , dhcp4_subnet_id ASC); + +# Create index to search for reservations using address/prefix and prefix +# length. +CREATE INDEX key_dhcp6_address_prefix_len ON ipv6_reservations (address ASC , prefix_len ASC); + # Create a table mapping host identifiers to their names. Values in this # table are used as a foreign key in hosts table to guarantee that only # identifiers present in host_identifier_type table are used in hosts diff --git a/src/lib/dhcpsrv/tests/generic_host_data_source_unittest.cc b/src/lib/dhcpsrv/tests/generic_host_data_source_unittest.cc index b15712456c..4818d03d9b 100644 --- a/src/lib/dhcpsrv/tests/generic_host_data_source_unittest.cc +++ b/src/lib/dhcpsrv/tests/generic_host_data_source_unittest.cc @@ -842,6 +842,17 @@ void GenericHostDataSourceTest::testAddDuplicate4() { // Then try to add it again, it should throw an exception. ASSERT_THROW(hdsptr_->add(host), DuplicateEntry); + + // This time use a different host identifier and try again. + // This update should be rejected because of duplicated + // address. + ASSERT_NO_THROW(host->setIdentifier("01:02:03:04:05:06", "hw-address")); + ASSERT_THROW(hdsptr_->add(host), DuplicateEntry); + + // Modify address to avoid its duplication and make sure + // we can now add the host. + ASSERT_NO_THROW(host->setIPv4Reservation(IOAddress("192.0.2.3"))); + EXPECT_NO_THROW(hdsptr_->add(host)); } void GenericHostDataSourceTest::testAddr6AndPrefix(){ diff --git a/src/lib/dhcpsrv/testutils/schema_mysql_copy.h b/src/lib/dhcpsrv/testutils/schema_mysql_copy.h index 4d988ee81c..8656d6d496 100755 --- a/src/lib/dhcpsrv/testutils/schema_mysql_copy.h +++ b/src/lib/dhcpsrv/testutils/schema_mysql_copy.h @@ -266,6 +266,13 @@ const char* create_statement[] = { "ON hosts " "(dhcp_identifier ASC , dhcp_identifier_type ASC , dhcp6_subnet_id ASC)", + "CREATE UNIQUE INDEX key_dhcp4_ipv4_address_subnet_id " + "ON hosts " + "(ipv4_address ASC, dhcp4_subnet_id ASC)", + + "CREATE INDEX key_dhcp6_address_prefix_len " + "ON ipv6_reservations (address ASC , prefix_len ASC)", + "CREATE TABLE IF NOT EXISTS host_identifier_type (" "type TINYINT PRIMARY KEY NOT NULL," "name VARCHAR(32)" -- 2.47.2