From 90841f377e1ecbeb3529aece09dd710341dc6332 Mon Sep 17 00:00:00 2001
From: Marcin Siodelski <marcin@isc.org>
Date: Fri, 26 Aug 2016 11:13:42 +0200
Subject: [PATCH] [4562] Updated MySQL upgrade scripts to schema version 5.0.

---
 configure.ac                                  |   1 +
 src/bin/admin/tests/mysql_tests.sh.in         |  73 +++++++++--
 src/lib/dhcpsrv/mysql_connection.h            |   4 +-
 src/share/database/scripts/mysql/.gitignore   |   1 +
 src/share/database/scripts/mysql/Makefile.am  |   2 +
 .../scripts/mysql/dhcpdb_create.mysql         |   4 +-
 .../scripts/mysql/upgrade_4.1_to_5.0.sh.in    | 114 ++++++++++++++++++
 7 files changed, 186 insertions(+), 13 deletions(-)
 create mode 100644 src/share/database/scripts/mysql/upgrade_4.1_to_5.0.sh.in

diff --git a/configure.ac b/configure.ac
index 609cf8a8c8..1117c5b0e4 100644
--- a/configure.ac
+++ b/configure.ac
@@ -1570,6 +1570,7 @@ AC_CONFIG_FILES([compatcheck/Makefile
                  src/share/database/scripts/mysql/upgrade_2.0_to_3.0.sh
                  src/share/database/scripts/mysql/upgrade_3.0_to_4.0.sh
                  src/share/database/scripts/mysql/upgrade_4.0_to_4.1.sh
+                 src/share/database/scripts/mysql/upgrade_4.1_to_5.0.sh
                  src/share/database/scripts/pgsql/Makefile
                  src/share/database/scripts/pgsql/upgrade_1.0_to_2.0.sh
                  src/share/database/scripts/pgsql/upgrade_2.0_to_3.0.sh
diff --git a/src/bin/admin/tests/mysql_tests.sh.in b/src/bin/admin/tests/mysql_tests.sh.in
index 7343914d39..9e6b96ed93 100644
--- a/src/bin/admin/tests/mysql_tests.sh.in
+++ b/src/bin/admin/tests/mysql_tests.sh.in
@@ -133,7 +133,7 @@ EOF
 
     # Second table: hosts
     mysql -u$db_user -p$db_password $db_name >/dev/null 2>&1 <<EOF
-    SELECT host_id, dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id, dhcp6_subnet_id, ipv4_address, hostname, dhcp4_client_classes, dhcp6_client_classes FROM hosts;
+    SELECT host_id, dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id, dhcp6_subnet_id, ipv4_address, hostname, dhcp4_client_classes, dhcp6_client_classes, dhcp4_next_server, dhcp4_server_hostname, dhcp4_boot_file_name FROM hosts;
 EOF
     ERRCODE=$?
     assert_eq 0 $ERRCODE "hosts table is missing or broken. (returned status code %d, expected %d)"
@@ -147,14 +147,14 @@ EOF
 
     # Fourth table: dhcp4_options
     mysql -u$db_user -p$db_password $db_name >/dev/null 2>&1 <<EOF
-    SELECT option_id, code, value, formatted_value, space, persistent, dhcp_client_class, dhcp4_subnet_id, host_id FROM dhcp4_options;
+    SELECT option_id, code, value, formatted_value, space, persistent, dhcp_client_class, dhcp4_subnet_id, host_id, scope_id FROM dhcp4_options;
 EOF
     ERRCODE=$?
     assert_eq 0 $ERRCODE "dhcp4_options table is missing or broken. (returned status code %d, expected %d)"
 
     # Fifth table: dhcp6_options
     mysql -u$db_user -p$db_password $db_name >/dev/null 2>&1 <<EOF
-    SELECT option_id, code, value, formatted_value, space, persistent, dhcp_client_class, dhcp6_subnet_id, host_id FROM dhcp6_options;
+    SELECT option_id, code, value, formatted_value, space, persistent, dhcp_client_class, dhcp6_subnet_id, host_id, scope_id FROM dhcp6_options;
 EOF
     ERRCODE=$?
     assert_eq 0 $ERRCODE "dhcp6_options table is missing or broken. (returned status code %d, expected %d)"
@@ -193,8 +193,8 @@ mysql_upgrade_test() {
 
     assert_str_eq "1.0" ${version} "Expected kea-admin to return %s, returned value was %s"
 
-    # Ok, we have a 1.0 database. Let's upgrade it to 4.1
-    ${keaadmin} lease-upgrade mysql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir 
+    # Ok, we have a 1.0 database. Let's upgrade it to 5.0
+    ${keaadmin} lease-upgrade mysql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir
     ERRCODE=$?
 
     assert_eq 0 $ERRCODE "kea-admin lease-upgrade mysql returned non-zero status code %d, expected %d"
@@ -286,7 +286,7 @@ EOF
     assert_eq 0 $ERRCODE "procedure text fetch for lease4DumpData failed. (returned status code %d, expected %d)"
     count=`echo $text | grep -ic "order by l\.address"`
     assert_eq 1 $count "lease4DumpData doesn't have order by clause. (returned count %d, expected %d)"
-   
+
     # verify  lease6DumpData has order by lease address
     qry="show create procedure lease6DumpData"
     text=`mysql_execute "${qry}"`
@@ -295,10 +295,65 @@ EOF
     count=`echo $text | grep -ic "order by l\.address"`
     assert_eq 1 $count "lease6DumpData doesn't have order by clause. (returned count %d, expected %d)"
 
-    # Verify upgraded schemd reports version 4.1.
-    version=$(${keaadmin} lease-version mysql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir) 
-    assert_str_eq "4.1" ${version} "Expected kea-admin to return %s, returned value was %s"
+    # verify that host_identifier_type table exists.
+    qry="select count(*) from host_identifier_type";
+    count=`mysql_execute "${qry}"`
+    ERRCODE=$?
+    assert_eq 0 $ERRCODE "select from host_identifier_type failed. (expected status code %d, returned %d)"
+    assert_eq 3 "$count" "host_identifier_type does not contain correct number of entries. (expected count %d, returned %d)"
+
+    # verify that foreign key fk_host_identifier_type exists
+    qry="show create table hosts";
+    text=`mysql_execute "${qry}"`
+    count=`echo $text | grep -ic "fk_host_identifier_type"`
+    ERRCODE=$?
+    assert_eq 0 $ERRCODE "show create table hosts failed. (expected status code %d, returned %d)"
+    assert_eq 2 "$count" "show create table hosts did not return correct number of fk_host_identifier_type instances. (expected %d, returned %d)"
+
+    # verify that dhcp_option_scope table exists.
+    qry="select count(*) from dhcp_option_scope";
+    count=`mysql_execute "${qry}"`
+    ERRCODE=$?
+    assert_eq 0 $ERRCODE "select from dhcp_option_scope failed. (expected status code %d, returned %d)"
+    assert_eq 4 "$count" "dhcp_option_scope does not contain correct number of entries. (expected %d, returned %d)"
+
+    # verify that dhcp4_options table includes scope_id
+    qry="select scope_id from dhcp4_options";
+    count=`mysql_execute "${qry}"`
+    ERRCODE=$?
+    assert_eq 0 $ERRCODE "select scope_id from dhcp4_options failed. (expected status code %d, returned %d)"
+
+    # verify that dhcp6_options table includes scope_id
+    qry="select scope_id from dhcp6_options";
+    count=`mysql_execute "${qry}"`
+    ERRCODE=$?
+    assert_eq 0 $ERRCODE "select scope_id from dhcp6_options failed. (expected status code %d, returned %d)"
+
+    # verify that hosts table has columns holding values for DHCPv4 fixed fields
+    qry="select dhcp4_next_server, dhcp4_server_hostname, dhcp4_boot_file_name from hosts";
+    count=`mysql_execute "${qry}"`
+    ERRCODE=$?
+    assert_eq 0 $ERRCODE "select dhcp4_next_server, dhcp4_server_hostname, dhcp4_boot_file_name failed. (expected status code %d, returned %d)"
+
+    # verify that dhcp4_subnet_id is unsigned
+    qry="show columns from hosts like 'dhcp4_subnet_id'"
+    text=`mysql_execute "${qry}"`
+    ERRCODE=$?
+    assert_eq 0 $ERRCODE "show columns from hosts like 'dhcp4_subnet_id' failed. (expected status code %d, returned %d)"
+    count=`echo $text | grep -ic unsigned`
+    assert_eq $count 1 "dhcp4_subnet_id is not of unsigned type. (returned count %d, expected %d)"
+
+    # verify that dhcp6_subnet_id is unsigned
+    qry="show columns from hosts like 'dhcp6_subnet_id'"
+    text=`mysql_execute "${qry}"`
+    ERRCODE=$?
+    assert_eq 0 $ERRCODE "show columns from hosts like 'dhcp6_subnet_id' failed. (expected status code %d, returned %d)"
+    count=`echo $text | grep -ic unsigned`
+    assert_eq 1 $count "dhcp6_subnet_id is not of unsigned type. (expected count %d, returned %d)"
 
+    # Verify upgraded schema reports version 5.0
+    version=$(${keaadmin} lease-version mysql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir)
+    assert_str_eq "5.0" ${version} "Expected kea-admin to return %s, returned value was %s"
 
     # Let's wipe the whole database
     mysql_wipe
diff --git a/src/lib/dhcpsrv/mysql_connection.h b/src/lib/dhcpsrv/mysql_connection.h
index 4f84731bcd..6340e7e572 100644
--- a/src/lib/dhcpsrv/mysql_connection.h
+++ b/src/lib/dhcpsrv/mysql_connection.h
@@ -40,8 +40,8 @@ extern const int MLM_MYSQL_FETCH_FAILURE;
 
 /// @name Current database schema version values.
 //@{
-const uint32_t MYSQL_SCHEMA_VERSION_MAJOR = 4;
-const uint32_t MYSQL_SCHEMA_VERSION_MINOR = 2;
+const uint32_t MYSQL_SCHEMA_VERSION_MAJOR = 5;
+const uint32_t MYSQL_SCHEMA_VERSION_MINOR = 0;
 
 //@}
 
diff --git a/src/share/database/scripts/mysql/.gitignore b/src/share/database/scripts/mysql/.gitignore
index 6d7bb04ccd..24f5157980 100644
--- a/src/share/database/scripts/mysql/.gitignore
+++ b/src/share/database/scripts/mysql/.gitignore
@@ -2,3 +2,4 @@
 /upgrade_2.0_to_3.0.sh
 /upgrade_3.0_to_4.0.sh
 /upgrade_4.0_to_4.1.sh
+/upgrade_4.1_to_5.0.sh
diff --git a/src/share/database/scripts/mysql/Makefile.am b/src/share/database/scripts/mysql/Makefile.am
index 89400c963d..4bde2fcf3a 100644
--- a/src/share/database/scripts/mysql/Makefile.am
+++ b/src/share/database/scripts/mysql/Makefile.am
@@ -7,5 +7,7 @@ sqlscripts_DATA += upgrade_1.0_to_2.0.sh
 sqlscripts_DATA += upgrade_2.0_to_3.0.sh
 sqlscripts_DATA += upgrade_3.0_to_4.0.sh
 sqlscripts_DATA += upgrade_4.0_to_4.1.sh
+sqlscripts_DATA += upgrade_4.1_to_5.0.sh
+
 
 EXTRA_DIST = ${sqlscripts_DATA}
diff --git a/src/share/database/scripts/mysql/dhcpdb_create.mysql b/src/share/database/scripts/mysql/dhcpdb_create.mysql
index fefebb53e5..3665d9a4f1 100644
--- a/src/share/database/scripts/mysql/dhcpdb_create.mysql
+++ b/src/share/database/scripts/mysql/dhcpdb_create.mysql
@@ -477,8 +477,8 @@ ALTER TABLE hosts ADD COLUMN dhcp4_boot_file_name VARCHAR(128) NULL;
 
 # Update the schema version number
 UPDATE schema_version
-SET version = '4', minor = '2';
-# This line concludes database upgrade to version 4.2.
+SET version = '5', minor = '0';
+# This line concludes database upgrade to version 5.0.
 
 # Notes:
 #
diff --git a/src/share/database/scripts/mysql/upgrade_4.1_to_5.0.sh.in b/src/share/database/scripts/mysql/upgrade_4.1_to_5.0.sh.in
new file mode 100644
index 0000000000..6aae5a4e93
--- /dev/null
+++ b/src/share/database/scripts/mysql/upgrade_4.1_to_5.0.sh.in
@@ -0,0 +1,114 @@
+#!/bin/sh
+
+# Include utilities. Use installed version if available and
+# use build version if it isn't.
+if [ -e @datarootdir@/@PACKAGE_NAME@/scripts/admin-utils.sh ]; then
+    . @datarootdir@/@PACKAGE_NAME@/scripts/admin-utils.sh
+else
+    . @abs_top_builddir@/src/bin/admin/admin-utils.sh
+fi
+
+VERSION=`mysql_version "$@"`
+
+if [ "$VERSION" != "4.1" ]; then
+    printf "This script upgrades 4.1 to 5.0. Reported version is $VERSION. Skipping upgrade.\n"
+    exit 0
+fi
+
+mysql "$@" <<EOF
+
+# Update index used for searching DHCPv4 reservations by identifier and subnet id.
+# This index is now unique (to prevent duplicates) and includes DHCPv4 subnet
+# identifier.
+DROP INDEX key_dhcp4_identifier_subnet_id ON hosts;
+CREATE UNIQUE INDEX key_dhcp4_identifier_subnet_id ON hosts (dhcp_identifier ASC , dhcp_identifier_type ASC , dhcp4_subnet_id ASC);
+
+# Update index used for searching DHCPv6 reservations by identifier and subnet id.
+# This index is now unique to prevent duplicates.
+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 UNIQUE 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
+# table.
+CREATE TABLE IF NOT EXISTS host_identifier_type (
+    type TINYINT PRIMARY KEY NOT NULL,   # Lease type code.
+    name VARCHAR(32)                     # Name of the lease type
+) ENGINE = INNODB;
+
+START TRANSACTION;
+INSERT INTO host_identifier_type VALUES (0, "hw-address"); # Non-temporary v6 addresses
+INSERT INTO host_identifier_type VALUES (1, "duid");       # Temporary v6 addresses
+INSERT INTO host_identifier_type VALUES (2, "circuit-id"); # Prefix delegations
+COMMIT;
+
+# Add a constraint that any identifier type value added to the hosts
+# must map to a value in the host_identifier_type table.
+ALTER TABLE hosts
+    ADD CONSTRAINT fk_host_identifier_type FOREIGN KEY (dhcp_identifier_type)
+    REFERENCES host_identifier_type (type);
+
+# Store DHCPv6 option code as 16-bit unsigned integer.
+ALTER TABLE dhcp6_options MODIFY code SMALLINT UNSIGNED NOT NULL;
+
+# Subnet identifier is unsigned.
+ALTER TABLE dhcp4_options MODIFY dhcp4_subnet_id INT UNSIGNED NULL;
+ALTER TABLE dhcp6_options MODIFY dhcp6_subnet_id INT UNSIGNED NULL;
+
+# Scopes associate DHCP options stored in dhcp4_options and
+# dhcp6_options tables with hosts, subnets, classes or indicate
+# that they are global options.
+CREATE TABLE IF NOT EXISTS dhcp_option_scope (
+    scope_id TINYINT UNSIGNED PRIMARY KEY NOT NULL,
+    scope_name VARCHAR(32)
+) ENGINE = INNODB;
+
+START TRANSACTION;
+INSERT INTO dhcp_option_scope VALUES (0, "global");
+INSERT INTO dhcp_option_scope VALUES (1, "subnet");
+INSERT INTO dhcp_option_scope VALUES (2, "client-class");
+INSERT INTO dhcp_option_scope VALUES (3, "host");
+COMMIT;
+
+# Add scopes into table holding DHCPv4 options
+ALTER TABLE dhcp4_options ADD COLUMN scope_id TINYINT UNSIGNED NOT NULL;
+ALTER TABLE dhcp4_options
+    ADD CONSTRAINT fk_dhcp4_option_scope FOREIGN KEY (scope_id)
+    REFERENCES dhcp_option_scope (scope_id);
+
+# Add scopes into table holding DHCPv6 options
+ALTER TABLE dhcp6_options ADD COLUMN scope_id TINYINT UNSIGNED NOT NULL;
+ALTER TABLE dhcp6_options
+    ADD CONSTRAINT fk_dhcp6_option_scope FOREIGN KEY (scope_id)
+    REFERENCES dhcp_option_scope (scope_id);
+
+# Add UNSIGNED to reservation_id
+ALTER TABLE ipv6_reservations
+    MODIFY reservation_id INT UNSIGNED NOT NULL AUTO_INCREMENT;
+
+# Add columns holding reservations for siaddr, sname and file fields
+# carried within DHCPv4 message.
+ALTER TABLE hosts ADD COLUMN dhcp4_next_server INT UNSIGNED NULL;
+ALTER TABLE hosts ADD COLUMN dhcp4_server_hostname VARCHAR(64) NULL;
+ALTER TABLE hosts ADD COLUMN dhcp4_boot_file_name VARCHAR(128) NULL;
+
+# Update the schema version number
+UPDATE schema_version
+SET version = '5', minor = '0';
+# This line concludes database upgrade to version 5.0.
+
+EOF
+
+RESULT=$?
+
+exit $?
-- 
2.47.2