[#2786] changes to are-scripts-in-sync.py CI script

8 files changed, 294 insertions, 169 deletions

diff --git a/src/share/database/scripts/mysql/.gitignore b/src/share/database/scripts/mysql/.gitignore
index c3c5062886..d6056d5a66 100644
--- a/src/share/database/scripts/mysql/.gitignore
+++ b/src/share/database/scripts/mysql/.gitignore
@@ -25,4 +25,5 @@
 /upgrade_014_to_015.sh
 /upgrade_015_to_016.sh
 /upgrade_016_to_017.sh
+/upgrade_017_to_018.sh
 /wipe_data.sh
diff --git a/src/share/database/scripts/mysql/upgrade_016_to_017.sh.in b/src/share/database/scripts/mysql/upgrade_016_to_017.sh.in
index 8a44660301..f3951c4907 100644
--- a/src/share/database/scripts/mysql/upgrade_016_to_017.sh.in
+++ b/src/share/database/scripts/mysql/upgrade_016_to_017.sh.in
@@ -66,140 +66,6 @@ ALTER TABLE dhcp6_subnet ADD COLUMN pd_allocator TEXT NULL;
 ALTER TABLE dhcp6_shared_network ADD COLUMN allocator TEXT NULL;
 ALTER TABLE dhcp6_shared_network ADD COLUMN pd_allocator TEXT NULL;
 
--- Extend lease4 client_id to 255 bytes.
-ALTER TABLE lease4
-    MODIFY COLUMN client_id VARBINARY(255);
-
--- Extend hosts dhcp_identifier to 255 bytes.
-ALTER TABLE hosts
-    MODIFY COLUMN dhcp_identifier VARBINARY(255) NOT NULL;
-
--- Extend hosts relay_id to 255 bytes.
-ALTER TABLE lease4
-    MODIFY COLUMN relay_id VARBINARY(255) DEFAULT NULL;
-
--- Extend hosts remote_id to 255 bytes.
-ALTER TABLE lease4
-    MODIFY COLUMN remote_id VARBINARY(255) DEFAULT NULL;
-
--- Extend lease6 duid to 130 bytes.
-ALTER TABLE lease6
-    MODIFY COLUMN duid VARBINARY(130);
-
-UPDATE lease6 SET duid = UNHEX('000000') WHERE duid = UNHEX('00');
-
--- Drop and create lease4Upload stored procedure with 255 bytes long client_id.
-DROP PROCEDURE IF EXISTS lease4Upload;
-
--- Create a procedure that inserts a v4 lease from memfile data.
-DELIMITER $$
-CREATE PROCEDURE lease4Upload(
-    IN address VARCHAR(15),
-    IN hwaddr VARCHAR(20),
-    IN client_id VARCHAR(255),
-    IN valid_lifetime INT UNSIGNED,
-    IN expire BIGINT UNSIGNED,
-    IN subnet_id INT UNSIGNED,
-    IN fqdn_fwd TINYINT,
-    IN fqdn_rev TINYINT,
-    IN hostname VARCHAR(255),
-    IN state INT UNSIGNED,
-    IN user_context TEXT
-)
-BEGIN
-    INSERT INTO lease4 (
-        address,
-        hwaddr,
-        client_id,
-        valid_lifetime,
-        expire,
-        subnet_id,
-        fqdn_fwd,
-        fqdn_rev,
-        hostname,
-        state,
-        user_context
-    ) VALUES (
-        INET_ATON(address),
-        UNHEX(REPLACE(hwaddr, ':', '')),
-        UNHEX(REPLACE(client_id, ':', '')),
-        valid_lifetime,
-        FROM_UNIXTIME(expire),
-        subnet_id,
-        fqdn_fwd,
-        fqdn_rev,
-        REPLACE(hostname, '&#x2c', ','),
-        state,
-        REPLACE(user_context, '&#x2c', ',')
-    );
-END $$
-DELIMITER ;
-
--- Drop and create lease6Upload stored procedure with 130 bytes long duid.
-DROP PROCEDURE IF EXISTS lease6Upload;
-
--- Create a procedure that inserts a v6 lease from memfile data.
-DELIMITER $$
-CREATE PROCEDURE lease6Upload(
-    IN address VARCHAR(39),
-    IN duid VARCHAR(130),
-    IN valid_lifetime INT UNSIGNED,
-    IN expire BIGINT UNSIGNED,
-    IN subnet_id INT UNSIGNED,
-    IN pref_lifetime INT UNSIGNED,
-    IN lease_type TINYINT,
-    IN iaid INT UNSIGNED,
-    IN prefix_len TINYINT UNSIGNED,
-    IN fqdn_fwd TINYINT,
-    IN fqdn_rev TINYINT,
-    IN hostname VARCHAR(255),
-    IN hwaddr VARCHAR(64),
-    IN state INT UNSIGNED,
-    IN user_context TEXT,
-    IN hwtype SMALLINT,
-    IN hwaddr_source INT UNSIGNED
-)
-BEGIN
-    INSERT INTO lease6 (
-        address,
-        duid,
-        valid_lifetime,
-        expire,
-        subnet_id,
-        pref_lifetime,
-        lease_type,
-        iaid,
-        prefix_len,
-        fqdn_fwd,
-        fqdn_rev,
-        hostname,
-        hwaddr,
-        state,
-        user_context,
-        hwtype,
-        hwaddr_source
-    ) VALUES (
-        address,
-        UNHEX(REPLACE(duid, ':', '')),
-        valid_lifetime,
-        FROM_UNIXTIME(expire),
-        subnet_id,
-        pref_lifetime,
-        lease_type,
-        iaid,
-        prefix_len,
-        fqdn_fwd,
-        fqdn_rev,
-        REPLACE(hostname, '&#x2c', ','),
-        UNHEX(REPLACE(hwaddr, ':', '')),
-        state,
-        REPLACE(user_context, '&#x2c', ','),
-        hwtype,
-        hwaddr_source
-    );
-END $$
-DELIMITER ;
-
 -- Update the schema version number.
 UPDATE schema_version
     SET version = '17', minor = '0';
diff --git a/src/share/database/scripts/mysql/upgrade_017_to_018.sh.in b/src/share/database/scripts/mysql/upgrade_017_to_018.sh.in
new file mode 100644
index 0000000000..eb2d59af63
--- /dev/null
+++ b/src/share/database/scripts/mysql/upgrade_017_to_018.sh.in
@@ -0,0 +1,197 @@
+#!/bin/sh
+
+# Copyright (C) 2023 Internet Systems Consortium, Inc. ("ISC")
+#
+# This Source Code Form is subject to the terms of the Mozilla Public
+# License, v. 2.0. If a copy of the MPL was not distributed with this
+# file, You can obtain one at http://mozilla.org/MPL/2.0/.
+
+# shellcheck disable=SC1091
+# SC1091: Not following: ... was not specified as input (see shellcheck -x).
+
+# Exit with error if commands exit with non-zero and if undefined variables are
+# used.
+set -eu
+
+# shellcheck disable=SC2034
+# SC2034: ... appears unused. Verify use (or export if used externally).
+prefix="@prefix@"
+
+# Include utilities. Use installed version if available and
+# use build version if it isn't.
+if test -f "@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
+
+# Check version.
+version=$(mysql_version "${@}")
+if test "${version}" != "17.0"; then
+    printf 'This script upgrades 17.0 to 18.0. '
+    printf 'Reported version is %s. Skipping upgrade.\n' "${version}"
+    exit 0
+fi
+
+# Get the schema name from database argument. We need this to
+# query information_schema for the right database.
+for arg in "${@}"
+do
+    if ! printf '%s' "${arg}" | grep -Eq '^\-\-'
+    then
+        schema="$arg"
+        break
+    fi
+done
+
+# Make sure we have the schema.
+if [ -z "$schema" ]
+then
+    printf "Could not find database schema name in cmd line args: %s\n" "${*}"
+    exit 255
+fi
+
+mysql "$@" <<EOF
+-- This line starts the schema upgrade to version 18.
+
+-- Extend lease4 client_id to 255 bytes.
+ALTER TABLE lease4
+    MODIFY COLUMN client_id VARBINARY(255);
+
+-- Extend hosts dhcp_identifier to 255 bytes.
+ALTER TABLE hosts
+    MODIFY COLUMN dhcp_identifier VARBINARY(255) NOT NULL;
+
+-- Extend hosts relay_id to 255 bytes.
+ALTER TABLE lease4
+    MODIFY COLUMN relay_id VARBINARY(255) DEFAULT NULL;
+
+-- Extend hosts remote_id to 255 bytes.
+ALTER TABLE lease4
+    MODIFY COLUMN remote_id VARBINARY(255) DEFAULT NULL;
+
+-- Extend lease6 duid to 130 bytes.
+ALTER TABLE lease6
+    MODIFY COLUMN duid VARBINARY(130);
+
+UPDATE lease6 SET duid = UNHEX('000000') WHERE duid = UNHEX('00');
+
+-- Drop and create lease4Upload stored procedure with 255 bytes long client_id.
+DROP PROCEDURE IF EXISTS lease4Upload;
+
+-- Create a procedure that inserts a v4 lease from memfile data.
+DELIMITER $$
+CREATE PROCEDURE lease4Upload(
+    IN address VARCHAR(15),
+    IN hwaddr VARCHAR(20),
+    IN client_id VARCHAR(255),
+    IN valid_lifetime INT UNSIGNED,
+    IN expire BIGINT UNSIGNED,
+    IN subnet_id INT UNSIGNED,
+    IN fqdn_fwd TINYINT,
+    IN fqdn_rev TINYINT,
+    IN hostname VARCHAR(255),
+    IN state INT UNSIGNED,
+    IN user_context TEXT
+)
+BEGIN
+    INSERT INTO lease4 (
+        address,
+        hwaddr,
+        client_id,
+        valid_lifetime,
+        expire,
+        subnet_id,
+        fqdn_fwd,
+        fqdn_rev,
+        hostname,
+        state,
+        user_context
+    ) VALUES (
+        INET_ATON(address),
+        UNHEX(REPLACE(hwaddr, ':', '')),
+        UNHEX(REPLACE(client_id, ':', '')),
+        valid_lifetime,
+        FROM_UNIXTIME(expire),
+        subnet_id,
+        fqdn_fwd,
+        fqdn_rev,
+        REPLACE(hostname, '&#x2c', ','),
+        state,
+        REPLACE(user_context, '&#x2c', ',')
+    );
+END $$
+DELIMITER ;
+
+-- Drop and create lease6Upload stored procedure with 130 bytes long duid.
+DROP PROCEDURE IF EXISTS lease6Upload;
+
+-- Create a procedure that inserts a v6 lease from memfile data.
+DELIMITER $$
+CREATE PROCEDURE lease6Upload(
+    IN address VARCHAR(39),
+    IN duid VARCHAR(130),
+    IN valid_lifetime INT UNSIGNED,
+    IN expire BIGINT UNSIGNED,
+    IN subnet_id INT UNSIGNED,
+    IN pref_lifetime INT UNSIGNED,
+    IN lease_type TINYINT,
+    IN iaid INT UNSIGNED,
+    IN prefix_len TINYINT UNSIGNED,
+    IN fqdn_fwd TINYINT,
+    IN fqdn_rev TINYINT,
+    IN hostname VARCHAR(255),
+    IN hwaddr VARCHAR(64),
+    IN state INT UNSIGNED,
+    IN user_context TEXT,
+    IN hwtype SMALLINT,
+    IN hwaddr_source INT UNSIGNED
+)
+BEGIN
+    INSERT INTO lease6 (
+        address,
+        duid,
+        valid_lifetime,
+        expire,
+        subnet_id,
+        pref_lifetime,
+        lease_type,
+        iaid,
+        prefix_len,
+        fqdn_fwd,
+        fqdn_rev,
+        hostname,
+        hwaddr,
+        state,
+        user_context,
+        hwtype,
+        hwaddr_source
+    ) VALUES (
+        address,
+        UNHEX(REPLACE(duid, ':', '')),
+        valid_lifetime,
+        FROM_UNIXTIME(expire),
+        subnet_id,
+        pref_lifetime,
+        lease_type,
+        iaid,
+        prefix_len,
+        fqdn_fwd,
+        fqdn_rev,
+        REPLACE(hostname, '&#x2c', ','),
+        UNHEX(REPLACE(hwaddr, ':', '')),
+        state,
+        REPLACE(user_context, '&#x2c', ','),
+        hwtype,
+        hwaddr_source
+    );
+END $$
+DELIMITER ;
+
+-- Update the schema version number.
+UPDATE schema_version
+    SET version = '18', minor = '0';
+
+-- This line concludes the schema upgrade to version 18.
+
+EOF
diff --git a/src/share/database/scripts/pgsql/.gitignore b/src/share/database/scripts/pgsql/.gitignore
index 465cd32b1d..b5dbcaf549 100644
--- a/src/share/database/scripts/pgsql/.gitignore
+++ b/src/share/database/scripts/pgsql/.gitignore
@@ -19,4 +19,5 @@
 /upgrade_013_to_014.sh
 /upgrade_014_to_015.sh
 /upgrade_015_to_016.sh
+/upgrade_016_to_017.sh
 /wipe_data.sh
diff --git a/src/share/database/scripts/pgsql/dhcpdb_create.pgsql b/src/share/database/scripts/pgsql/dhcpdb_create.pgsql
index 7aa29edeee..e7662639ac 100644
--- a/src/share/database/scripts/pgsql/dhcpdb_create.pgsql
+++ b/src/share/database/scripts/pgsql/dhcpdb_create.pgsql
@@ -5617,13 +5617,13 @@ UPDATE schema_version
 
 UPDATE lease6 SET duid = E'\\x000000' WHERE duid = E'\\x00';
 
--- Set schema 16.0 version.
+-- Set 16.0 schema version.
 UPDATE schema_version
     SET version = '16', minor = '0';
 
 -- Schema 16.0 specification ends here.
 
--- This line starts the schema upgrade to version 16.
+-- Upgrade to schema 16.0 begins here:
 
 -- Add the allocator column to the DHCPv4 tables.
 ALTER TABLE dhcp4_subnet ADD COLUMN allocator TEXT DEFAULT NULL;
@@ -5637,11 +5637,11 @@ ALTER TABLE dhcp6_subnet ADD COLUMN pd_allocator TEXT DEFAULT NULL;
 ALTER TABLE dhcp6_shared_network ADD COLUMN allocator TEXT DEFAULT NULL;
 ALTER TABLE dhcp6_shared_network ADD COLUMN pd_allocator TEXT DEFAULT NULL;
 
--- Update the schema version number.
+-- Set 17.0 schema version.
 UPDATE schema_version
-    SET version = '16', minor = '0';
+    SET version = '17', minor = '0';
 
--- This line concludes the schema upgrade to version 16.
+-- Schema 17.0 specification ends here.
 
 -- Commit the script transaction.
 COMMIT;
diff --git a/src/share/database/scripts/pgsql/upgrade_015_to_016.sh.in b/src/share/database/scripts/pgsql/upgrade_015_to_016.sh.in
index e48acf52d2..4603c2fef8 100644
--- a/src/share/database/scripts/pgsql/upgrade_015_to_016.sh.in
+++ b/src/share/database/scripts/pgsql/upgrade_015_to_016.sh.in
@@ -56,8 +56,6 @@ UPDATE schema_version
 
 -- Schema 16.0 specification ends here.
 
-UPDATE lease6 SET duid = E'\\\\x000000' WHERE duid = E'\\\\x00';
-
 -- Commit the script transaction.
 COMMIT;
 
diff --git a/src/share/database/scripts/pgsql/upgrade_016_to_017.sh.in b/src/share/database/scripts/pgsql/upgrade_016_to_017.sh.in
new file mode 100644
index 0000000000..51016c9d77
--- /dev/null
+++ b/src/share/database/scripts/pgsql/upgrade_016_to_017.sh.in
@@ -0,0 +1,52 @@
+#!/bin/sh
+
+# Copyright (C) 2023 Internet Systems Consortium, Inc. ("ISC")
+#
+# This Source Code Form is subject to the terms of the Mozilla Public
+# License, v. 2.0. If a copy of the MPL was not distributed with this
+# file, You can obtain one at http://mozilla.org/MPL/2.0/.
+
+# shellcheck disable=SC1091
+# SC1091: Not following: ... was not specified as input (see shellcheck -x).
+
+# Exit with error if commands exit with non-zero and if undefined variables are
+# used.
+set -eu
+
+# shellcheck disable=SC2034
+# SC2034: ... appears unused. Verify use (or export if used externally).
+prefix="@prefix@"
+
+# Include utilities. Use installed version if available and
+# use build version if it isn't.
+if test -f "@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=$(pgsql_version "$@")
+
+if [ "$VERSION" != "16.0" ]; then
+    printf 'This script upgrades 16.0 to 17.0. '
+    printf 'Reported version is %s. Skipping upgrade.\n' "${VERSION}"
+    exit 0
+fi
+
+psql "$@" >/dev/null <<EOF
+START TRANSACTION;
+
+-- Upgrade to schema 17.0 begins here:
+
+UPDATE lease6 SET duid = E'\\\\x000000' WHERE duid = E'\\\\x00';
+
+-- Set 17.0 schema version.
+UPDATE schema_version
+    SET version = '17', minor = '0';
+
+-- Schema 17.0 specification ends here.
+
+-- Commit the script transaction.
+COMMIT;
+
+EOF
diff --git a/src/share/database/scripts/utils/are-scripts-in-sync.py b/src/share/database/scripts/utils/are-scripts-in-sync.py
index 72bf38a533..399ef56a2b 100755
--- a/src/share/database/scripts/utils/are-scripts-in-sync.py
+++ b/src/share/database/scripts/utils/are-scripts-in-sync.py
@@ -86,12 +86,10 @@ def filter_the_noise(file, text, is_upgrade_script):
             result.append(i)
 
     if not first_delimiter_found:
-        print(f'ERROR: Expected delimiter "{first_delimiter}" in file {file}, but not found.', file=sys.stderr)
-        sys.exit(3)
+        result.append(f'WARNING: Expected delimiter "{first_delimiter}" in file {file}, but not found.')
 
     if not second_delimiter_found:
-        print(f'ERROR: Expected delimiter "{second_delimiter}" in file {file}, but not found.', file=sys.stderr)
-        sys.exit(4)
+        result.append(f'WARNING: Expected delimiter "{second_delimiter}" in file {file}, but not found.')
 
     return result
 
@@ -104,8 +102,11 @@ def diff(dhcpdb_create_script, upgrade_script):
     :param upgrade_script: the path to the upgrade script
     :type upgrade_script: str
 
-    :return: True if there is a difference, False otherwise
-    :type: bool
+    :return: tuple containing the diffed output and a boolean which indicates
+    whether the diffed output is on the latest upgrade script. Only that diff
+    output results in an error due to the unreliability of this script to output
+    a relevant diff on the other scripts.
+    :type: (string, bool)
     '''
     with open(dhcpdb_create_script, encoding='utf-8') as create_file:
         create_text = create_file.readlines()
@@ -113,17 +114,19 @@ def diff(dhcpdb_create_script, upgrade_script):
     with open(upgrade_script, encoding='utf-8') as upgrade_file:
         upgrade_text = upgrade_file.readlines()
 
-    # PostgreSQL upgrade scripts need the $ delimiters escaped as opposed to
-    # create scripts. So escape them in the create script for the duration of
-    # this diff so that they don't come up in the diff (or so that they do
-    # come up if they are not correctly escaped in the upgrade script).
+    # As opposed to PostgreSQL create scripts, upgrade scripts need the
+    # backslashes and the $ delimiters escaped. So escape them in the create
+    # script for the duration of this diff so that they don't come up in the
+    # diff (or so that they do come up if they are not correctly escaped in the
+    # upgrade script).
     if dhcpdb_create_script.endswith('.pgsql'):
+        create_text = [i.replace(r'\\', r'\\\\') for i in create_text]
         create_text = [i.replace('$', r'\$') for i in create_text]
 
     latest_upgrade_script = find_last_file_in_same_directory_starting_with(upgrade_script, 'upgrade_')
     if latest_upgrade_script is None:
-        print('Warning: could not find latest upgrade script.', file=sys.stderr)
-        return 0
+        print('ERROR: could not find latest upgrade script.', file=sys.stderr)
+        sys.exit(3)
 
     # Removes portions of the script which are always different: the beginning
     # and the end.
@@ -168,14 +171,7 @@ def diff(dhcpdb_create_script, upgrade_script):
             output = output + 'WARNING: There is a small chance of false errors on this pair of scripts.\n'
         output = output + ''.join(sanitized_diff)
 
-    # Only print if we have something to print to avoid a newline.
-    if len(output) > 0:
-        print(output)
-
-    # Only report errors on the latest upgrade script. For all other upgrade
-    # scripts, there is a chance of false errors caused by incorrect matching of
-    # lines. Assume no diff so that CI doesn't complain.
-    return len(output) > 0 and upgrade_script == latest_upgrade_script
+    return output, upgrade_script == latest_upgrade_script
 
 
 def execute(command):
@@ -194,7 +190,7 @@ def execute(command):
         output, error = p.communicate()
     if error:
         print('ERROR:', error, file=sys.stderr)
-        sys.exit(1)
+        sys.exit(2)
     return output.strip()
 
 
@@ -278,26 +274,40 @@ def main(parameters):
             # Get the latest upgrade script.
             latest_upgrade_script = find_last_file_in_same_directory_starting_with(i, 'upgrade_')
             if latest_upgrade_script is None:
-                print('Warning: could not find latest upgrade script.', file=sys.stderr)
+                print('WARNING: could not find latest upgrade script.', file=sys.stderr)
                 continue
             pairs.add((i, latest_upgrade_script))
         elif basename.startswith('upgrade_'):
             # Get the dhcpdb_create script.
             dhcpdb_create = find_last_file_in_same_directory_starting_with(i, 'dhcpdb_create')
             if dhcpdb_create is None:
-                print('Warning: could not find dhcpdb_create script.', file=sys.stderr)
+                print('WARNING: could not find dhcpdb_create script.', file=sys.stderr)
                 continue
             pairs.add((dhcpdb_create, i))
     pairs = sorted(pairs)
 
     # Do the diff.
-    diff_found = False
+    output_for_latest = ''
+    output_for_other_than_latest = ''
     for create, update in pairs:
-        diff_found |= diff(create, update)
+        output, is_latest = diff(create, update)
+        if is_latest:
+            output_for_latest += output
+        else:
+            output_for_other_than_latest += output
+
+    # Only print if we have something to print to avoid a newline.
+    # Also don't clutter output with lines that doesn't cause CI failure if
+    # there are lines that cause CI failure.
+    if len(output_for_latest):
+        print(output_for_latest)
+    elif len(output_for_other_than_latest):
+        print(output_for_other_than_latest)
 
-    # For any diff, return 1 so that CI complains.
-    # For no diff, return 0 to appease CI.
-    return int(diff_found)
+    # Only report errors on the latest upgrade script. For all other upgrade
+    # scripts, there is a chance of false errors caused by incorrect matching of
+    # lines. Assume no diff in that case so that CI doesn't complain.
+    return len(output_for_latest) != 0
 
 
 if __name__ == '__main__':