Upgrade Notes

Required actions and information relating to upgrading to Stroom version 7.6.

Warning

Please read this section carefully in case any of it is relevant to your Stroom instance.

Java Version

Stroom v7.6 requires Java 21. This is the same java version as Stroom v7.5. Ensure the Stroom and Stroom-Proxy hosts are running the latest patch release of Java v21.

Configuration File Changes

Stroom’s `config.yml`

The following changes have been made to the configuration file.

Added Property Trees

The following cache configuration property trees have been added.

appPermissionIdCache.*
docTypeIdCache.*
userAppPermissionsCache.*
userInfoByUuidCache.*

Removed Property Trees

The following cache configuration property trees have been removed.

userAppPermissionsCache.*
userByDisplayNameCache.*

Stroom-Proxy’s `config.yml`

No changes have been made configuration file.

Database Migrations

When Stroom boots for the first time with a new version it will run any required database migrations to bring the database schema up to the correct version.

Warning

It is highly recommended to ensure you have a database backup in place before booting stroom with a new version. This is to mitigate against any problems with the migration. It is also recommended to test the migration against a copy of your database to ensure that there are no problems when you do it for real.

On boot, Stroom will ensure that the migrations are only run by a single node in the cluster. This will be the node that reaches that point in the boot process first. All other nodes will wait until that is complete before proceeding with the boot process.

It is recommended however to use a single node to execute the migration. To avoid Stroom starting up and beginning processing you can use the migrage command to just migrate the database and not fully boot Stroom. See migrage command for more details.

Warning

If you are upgrading from a previous v7.6 beta release you will need to run the following SQL.

update analytics_schema_history set checksum = '-86554219' where version = '07.06.00.405';

update processor_schema_history set checksum = '-175036745' where version = '07.06.00.305';

This release migrates all the existing document and application permission grants into new tables (prefixed by permission_). The legacy tables doc_permission and app_permission have been left untouched to allow migrated permissions to be compared against the previous state. In some future version of Stroom these tables will be removed.

Migration Scripts

For information purposes only, the following are the database migrations that will be run when upgrading to 7.6.0 from the previous minor version.

Note, the legacy module will run first (if present) then the other module will run in no particular order.

Module `stroom-activity`

Script `V07_06_00_200__activity_pre_migration_checks.java`

Path: stroom-activity/stroom-activity-impl-db/src/main/java/stroom/activity/impl/db/migration/V07_06_00_200__activity_pre_migration_checks.java

It is not possible to display the content here. The file can be viewed on : GitHub

Script `V07_06_00_205__activity_user_uuid.sql`

Path: stroom-activity/stroom-activity-impl-db/src/main/resources/stroom/activity/impl/db/migration/V07_06_00_205__activity_user_uuid.sql

-- ------------------------------------------------------------------------
-- Copyright 2023 Crown Copyright
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
-- ------------------------------------------------------------------------

-- Stop NOTE level warnings about objects (not)? existing
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

DROP PROCEDURE IF EXISTS V07_06_00_205__activity_user_uuid;

DELIMITER $$

CREATE PROCEDURE V07_06_00_205__activity_user_uuid ()
BEGIN
    DECLARE object_count integer;

    SELECT COUNT(1)
    INTO object_count
    FROM information_schema.columns
    WHERE table_schema = database()
    AND table_name = 'activity'
    AND column_name = 'user_uuid';

    IF object_count = 0 THEN
        ALTER TABLE activity ADD COLUMN user_uuid varchar(255) NOT NULL;

        SELECT COUNT(1)
        INTO object_count
        FROM information_schema.tables
        WHERE table_schema = database()
        AND table_name = 'stroom_user';

        IF object_count = 1 THEN
            SET @sql_str = CONCAT(
                'UPDATE activity a, stroom_user s ',
                'SET a.user_uuid = s.uuid ',
                'WHERE a.user_id = s.name');
            PREPARE stmt FROM @sql_str;
            EXECUTE stmt;
        END IF;

        ALTER TABLE activity DROP COLUMN user_id;

    END IF;
END $$

DELIMITER ;

CALL V07_06_00_205__activity_user_uuid;

DROP PROCEDURE IF EXISTS V07_06_00_205__activity_user_uuid;

SET SQL_NOTES=@OLD_SQL_NOTES;

-- vim: set shiftwidth=4 tabstop=4 expandtab:

Module `stroom-analytics`

Script `V07_06_00_405__execution_schedule_run_as_user_uuid.sql`

Path: stroom-analytics/stroom-analytics-impl-db/src/main/resources/stroom/analytics/impl/db/migration/V07_06_00_405__execution_schedule_run_as_user_uuid.sql

-- ------------------------------------------------------------------------
-- Copyright 2023 Crown Copyright
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
-- ------------------------------------------------------------------------

-- Stop NOTE level warnings about objects (not)? existing
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

DROP PROCEDURE IF EXISTS V07_06_00_405__execution_schedule_run_as_user_uuid;

DELIMITER $$

CREATE PROCEDURE V07_06_00_405__execution_schedule_run_as_user_uuid ()
BEGIN
    DECLARE object_count integer;

    SELECT COUNT(1)
    INTO object_count
    FROM information_schema.columns
    WHERE table_schema = database()
    AND table_name = 'execution_schedule'
    AND column_name = 'run_as_user_uuid';

    IF object_count = 0 THEN
        ALTER TABLE execution_schedule ADD COLUMN run_as_user_uuid varchar(255) DEFAULT NULL;

        -- The now legacy doc_permission table may be removed at some later point
        -- in which case we don't have to do anything
        SELECT COUNT(1)
        INTO object_count
        FROM information_schema.tables
        WHERE table_schema = database()
        AND table_name = 'doc_permission';

        IF object_count = 1 THEN
            SET @sql_str = CONCAT(
                'UPDATE execution_schedule es ',
                'INNER JOIN ( ',
                '    SELECT DISTINCT ',
                '        dp.doc_uuid, ',
                '        FIRST_VALUE(dp.user_uuid) ',
                '            OVER (PARTITION BY dp.doc_uuid ORDER BY dp.id DESC) latest_owner_uuid ',
                '    FROM doc_permission dp ',
                '    WHERE dp.permission = "Owner" ',
                ') as dpv on dpv.doc_uuid = es.doc_uuid ',
                'SET es.run_as_user_uuid = dpv.latest_owner_uuid;');
            PREPARE stmt FROM @sql_str;
            EXECUTE stmt;
        END IF;

    END IF;
END $$

DELIMITER ;

CALL V07_06_00_405__execution_schedule_run_as_user_uuid;

DROP PROCEDURE IF EXISTS V07_06_00_405__execution_schedule_run_as_user_uuid;

SET SQL_NOTES=@OLD_SQL_NOTES;

-- vim: set shiftwidth=4 tabstop=4 expandtab:

Module `stroom-annotation`

Script `V07_06_00_100__annotation_pre_migration_checks.java`

Path: stroom-annotation/stroom-annotation-impl-db/src/main/java/stroom/annotation/impl/db/migration/V07_06_00_100__annotation_pre_migration_checks.java

It is not possible to display the content here. The file can be viewed on : GitHub

Script `V07_06_00_105__annotation_uuid.sql`

Path: stroom-annotation/stroom-annotation-impl-db/src/main/resources/stroom/annotation/impl/db/migration/V07_06_00_105__annotation_uuid.sql

-- ------------------------------------------------------------------------
-- Copyright 2023 Crown Copyright
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
-- ------------------------------------------------------------------------

-- Stop NOTE level warnings about objects (not)? existing
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

DROP PROCEDURE IF EXISTS V07_06_00_105_annotation;

DELIMITER $$

CREATE PROCEDURE V07_06_00_105_annotation ()
BEGIN
    DECLARE object_count integer;

    SELECT COUNT(1)
    INTO object_count
    FROM information_schema.columns
    WHERE table_schema = database()
    AND table_name = 'annotation'
    AND column_name = 'uuid';

    IF object_count = 0 THEN
        ALTER TABLE `annotation`
        ADD COLUMN `uuid` varchar(255) NOT NULL;
        UPDATE `annotation` set `uuid` = MID(UUID(),1,36);
        CREATE UNIQUE INDEX `annotation_uuid` ON `annotation` (`uuid`);
    END IF;
END $$

DELIMITER ;

CALL V07_06_00_105_annotation;

DROP PROCEDURE IF EXISTS V07_06_00_105_annotation;

SET SQL_NOTES=@OLD_SQL_NOTES;

-- vim: set shiftwidth=4 tabstop=4 expandtab:

Script `V07_06_00_110__annotation_entry.sql`

Path: stroom-annotation/stroom-annotation-impl-db/src/main/resources/stroom/annotation/impl/db/migration/V07_06_00_110__annotation_entry.sql

-- ------------------------------------------------------------------------
-- Copyright 2023 Crown Copyright
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
-- ------------------------------------------------------------------------

-- Stop NOTE level warnings about objects (not)? existing
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

DROP PROCEDURE IF EXISTS V07_06_00_110__annotation_entry;

DELIMITER $$

CREATE PROCEDURE V07_06_00_110__annotation_entry ()
BEGIN
    DECLARE object_count integer;

    SELECT COUNT(1)
    INTO object_count
    FROM information_schema.columns
    WHERE table_schema = database()
    AND table_name = 'annotation_entry'
    AND column_name = 'entry_user_uuid';

    IF object_count = 0 THEN
        ALTER TABLE annotation_entry ADD COLUMN entry_user_uuid varchar(255) DEFAULT NULL;
        ALTER TABLE annotation_entry ADD COLUMN entry_time_ms bigint NOT NULL;

        SELECT COUNT(1)
        INTO object_count
        FROM information_schema.tables
        WHERE table_schema = database()
        AND table_name = 'stroom_user';

        IF object_count = 1 THEN
            -- Change create user names to entry user uuids.
            SET @sql_str = CONCAT(
                'UPDATE annotation_entry a, stroom_user s ',
                'SET a.entry_user_uuid = s.uuid ',
                'WHERE a.create_user = s.name');
            PREPARE stmt FROM @sql_str;
            EXECUTE stmt;

            -- Move all create times to entry times.
            SET @sql_str = CONCAT(
                'UPDATE annotation_entry a ',
                'SET a.entry_time_ms = a.create_time_ms');
            PREPARE stmt FROM @sql_str;
            EXECUTE stmt;

            -- Change all assignment entries to reference user UUID instead of name.
            SET @sql_str = CONCAT(
                'UPDATE annotation_entry a, stroom_user s ',
                'SET a.data = s.uuid ',
                'WHERE a.type = "Assigned" AND a.data = s.name');
            PREPARE stmt FROM @sql_str;
            EXECUTE stmt;
        END IF;

        ALTER TABLE annotation_entry DROP COLUMN version;
        ALTER TABLE annotation_entry DROP COLUMN create_time_ms;
        ALTER TABLE annotation_entry DROP COLUMN create_user;
        ALTER TABLE annotation_entry DROP COLUMN update_time_ms;
        ALTER TABLE annotation_entry DROP COLUMN update_user;

    END IF;
END $$

DELIMITER ;

CALL V07_06_00_110__annotation_entry;

DROP PROCEDURE IF EXISTS V07_06_00_110__annotation_entry;

SET SQL_NOTES=@OLD_SQL_NOTES;

-- vim: set shiftwidth=4 tabstop=4 expandtab:

Module `stroom-processor`

Script `V07_06_00_300__processor_filter_pre_migration_checks.java`

Path: stroom-processor/stroom-processor-impl-db/src/main/java/stroom/processor/impl/db/migration/V07_06_00_300__processor_filter_pre_migration_checks.java

It is not possible to display the content here. The file can be viewed on : GitHub

Script `V07_06_00_305__processor_filter_run_as_user_uuid.sql`

Path: stroom-processor/stroom-processor-impl-db/src/main/resources/stroom/processor/impl/db/migration/V07_06_00_305__processor_filter_run_as_user_uuid.sql

-- ------------------------------------------------------------------------
-- Copyright 2023 Crown Copyright
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
-- ------------------------------------------------------------------------

-- Stop NOTE level warnings about objects (not)? existing
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

DROP PROCEDURE IF EXISTS V07_06_00_305__processor_filter_run_as_user_uuid;

DELIMITER $$

CREATE PROCEDURE V07_06_00_305__processor_filter_run_as_user_uuid ()
BEGIN
    DECLARE object_count integer;

    SELECT COUNT(1)
    INTO object_count
    FROM information_schema.columns
    WHERE table_schema = database()
    AND table_name = 'processor_filter'
    AND column_name = 'run_as_user_uuid';

    IF object_count = 0 THEN
        ALTER TABLE processor_filter ADD COLUMN run_as_user_uuid varchar(255) DEFAULT NULL;

        SELECT COUNT(1)
        INTO object_count
        FROM information_schema.tables
        WHERE table_schema = database()
        AND table_name = 'doc_permission';

        IF object_count = 1 THEN
            SET @sql_str = CONCAT(
                'UPDATE processor_filter pf ',
                'INNER JOIN ( ',
                '    SELECT DISTINCT ',
                '        dp.doc_uuid, ',
                '        FIRST_VALUE(dp.user_uuid) '
                '            OVER (PARTITION BY dp.doc_uuid ORDER BY dp.id DESC) latest_owner_uuid ',
                '    FROM doc_permission dp ',
                '    WHERE dp.permission = "Owner" ',
                ') as dpv on dpv.doc_uuid = pf.uuid ',
                'SET pf.run_as_user_uuid = dpv.latest_owner_uuid; ');
            PREPARE stmt FROM @sql_str;
            EXECUTE stmt;
        END IF;

    END IF;
END $$

DELIMITER ;

CALL V07_06_00_305__processor_filter_run_as_user_uuid;

DROP PROCEDURE IF EXISTS V07_06_00_305__processor_filter_run_as_user_uuid;

SET SQL_NOTES=@OLD_SQL_NOTES;

-- vim: set shiftwidth=4 tabstop=4 expandtab:

Module `stroom-security`

Script `V07_06_00_800__app_permission.sql`

Path: stroom-security/stroom-security-impl-db/src/main/resources/stroom/security/impl/db/migration/V07_06_00_800__app_permission.sql

-- ------------------------------------------------------------------------
-- Copyright 2024 Crown Copyright
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
-- ------------------------------------------------------------------------

-- Stop NOTE level warnings about objects (not)? existing
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

DROP TABLE IF EXISTS `permission_app`;
DROP TABLE IF EXISTS `permission_app_id`;

--
-- Create the application permission id table
--
CREATE TABLE IF NOT EXISTS `permission_app_id` (
  `id` tinyint UNSIGNED NOT NULL AUTO_INCREMENT,
  `permission` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `permission_app_id_permission_idx` (`permission`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

--
-- Add app permission names into the app permission id table.
--
INSERT INTO `permission_app_id` (`permission`)
SELECT DISTINCT(permission)
FROM app_permission;

--
-- Create the new application permission table.
--
CREATE TABLE IF NOT EXISTS `permission_app` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_uuid` varchar(255) NOT NULL,
  `permission_id` tinyint UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  KEY `permission_app_user_uuid` (`user_uuid`),
  UNIQUE KEY `permission_app_user_uuid_permission_id_idx` (`user_uuid`,`permission_id`),
  CONSTRAINT `permission_app_user_uuid` FOREIGN KEY (`user_uuid`) REFERENCES `stroom_user` (`uuid`),
  CONSTRAINT `permission_app_permission_id` FOREIGN KEY (`permission_id`) REFERENCES `permission_app_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

--
-- Copy permission values to the new table.
--
INSERT INTO permission_app (user_uuid, permission_id)
SELECT ap.user_uuid, pai.id
FROM app_permission ap
JOIN permission_app_id pai
ON (pai.permission = ap.permission);

SET SQL_NOTES=@OLD_SQL_NOTES;

-- vim: set shiftwidth=4 tabstop=4 expandtab:

Script `V07_06_00_900__doc_permission.sql`

Path: stroom-security/stroom-security-impl-db/src/main/resources/stroom/security/impl/db/migration/V07_06_00_900__doc_permission.sql

-- ------------------------------------------------------------------------
-- Copyright 2024 Crown Copyright
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
-- ------------------------------------------------------------------------

-- Stop NOTE level warnings about objects (not)? existing
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

DROP TABLE IF EXISTS `permission_doc`;
DROP TABLE IF EXISTS `permission_doc_id`;
DROP TABLE IF EXISTS `permission_doc_create`;
DROP TABLE IF EXISTS `permission_doc_type_id`;

--
-- Create the permission id table
--
CREATE TABLE IF NOT EXISTS `permission_doc_id` (
  `id` tinyint UNSIGNED NOT NULL,
  `permission` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `permission_doc_id_permission_idx` (`permission`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

--
-- Add permission names into the id table.
--
INSERT INTO `permission_doc_id` (`id`, `permission`)
VALUES
(10, "Use"),
(20, "Read"),
(30, "Update"),
(40, "Delete"),
(50, "Owner");

--
-- Create the new permission table.
--
CREATE TABLE IF NOT EXISTS `permission_doc` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_uuid` varchar(255) NOT NULL,
  `doc_uuid` varchar(255) NOT NULL,
  `permission_id` tinyint UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  KEY `permission_doc_user_uuid` (`user_uuid`),
  KEY `permission_doc_doc_uuid` (`doc_uuid`),
  UNIQUE KEY `permission_doc_user_uuid_doc_uuid_idx` (`user_uuid`,`doc_uuid`),
  CONSTRAINT `permission_doc_user_uuid` FOREIGN KEY (`user_uuid`) REFERENCES `stroom_user` (`uuid`),
  CONSTRAINT `permission_doc_permission_id` FOREIGN KEY (`permission_id`) REFERENCES `permission_doc_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

--
-- Copy permission values to the new table.
--
INSERT INTO permission_doc (user_uuid, doc_uuid, permission_id)
SELECT dp.user_uuid, dp.doc_uuid, MAX(pdi.id)
FROM doc_permission dp
JOIN permission_doc_id pdi
ON (pdi.permission = dp.permission)
WHERE dp.permission IN ("Owner", "Delete", "Update", "Read", "Use")
GROUP BY dp.user_uuid, dp.doc_uuid;

--
-- Modify the permission names.
--
UPDATE `permission_doc_id`
SET `permission` = "View"
WHERE `permission` = "Read";

UPDATE `permission_doc_id`
SET `permission` = "Edit"
WHERE `permission` = "Update";

--
-- Create the document type id table
--
CREATE TABLE IF NOT EXISTS `permission_doc_type_id` (
  `id` tinyint UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `permission_doc_type_id_type_idx` (`type`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

--
-- Add document type names into the doc type id table.
--
INSERT INTO `permission_doc_type_id` (`type`)
SELECT DISTINCT(SUBSTRING(permission, 10))
FROM doc_permission
WHERE permission LIKE "Create - %";

--
-- Create the new document create permission table.
--
CREATE TABLE IF NOT EXISTS `permission_doc_create` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_uuid` varchar(255) NOT NULL,
  `doc_uuid` varchar(255) NOT NULL,
  `doc_type_id` tinyint UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  KEY `permission_doc_create_user_uuid` (`user_uuid`),
  KEY `permission_doc_create_doc_uuid` (`doc_uuid`),
  KEY `permission_doc_create_doc_type_id` (`doc_type_id`),
  UNIQUE KEY `permission_doc_create_user_uuid_doc_uuid_doc_type_id_idx` (`user_uuid`,`doc_uuid`, `doc_type_id`),
  CONSTRAINT `permission_doc_create_user_uuid` FOREIGN KEY (`user_uuid`) REFERENCES `stroom_user` (`uuid`),
  CONSTRAINT `permission_doc_create_doc_type_id` FOREIGN KEY (`doc_type_id`) REFERENCES `permission_doc_type_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

--
-- Copy document create permission values to the new table.
--
INSERT INTO permission_doc_create (user_uuid, doc_uuid, doc_type_id)
SELECT dp.user_uuid, dp.doc_uuid, pdti.id
FROM doc_permission dp
JOIN permission_doc_type_id pdti
ON (pdti.type = SUBSTRING(dp.permission, 10))
WHERE dp.permission LIKE "Create - %";

SET SQL_NOTES=@OLD_SQL_NOTES;

-- vim: set shiftwidth=4 tabstop=4 expandtab:

Script `V07_06_00_905__user_display_name.sql`

Path: stroom-security/stroom-security-impl-db/src/main/resources/stroom/security/impl/db/migration/V07_06_00_905__user_display_name.sql

-- ------------------------------------------------------------------------
-- Copyright 2024 Crown Copyright
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
-- ------------------------------------------------------------------------

-- Stop NOTE level warnings about objects (not)? existing
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

-- Idempotent
-- Ensure we always have a display_name value
UPDATE `stroom_user`
SET `display_name` = `name`
WHERE `display_name` IS NULL
OR LENGTH(`display_name`) = 0;

-- Idempotent
-- Now make the col non null so can rely on a value being there
ALTER TABLE `stroom_user` MODIFY `display_name` VARCHAR(255) NOT NULL;

SET SQL_NOTES=@OLD_SQL_NOTES;

-- vim: set shiftwidth=4 tabstop=4 expandtab:

Script `V07_06_00_910__user_add_deleted_col.sql`

Path: stroom-security/stroom-security-impl-db/src/main/resources/stroom/security/impl/db/migration/V07_06_00_910__user_add_deleted_col.sql

-- ------------------------------------------------------------------------
-- Copyright 2020 Crown Copyright
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
-- ------------------------------------------------------------------------

-- Stop NOTE level warnings about objects (not)? existing
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

-- --------------------------------------------------

-- An archive of the last known values of name, display_name, full_name and is_group for a given
-- uuid. No constraint on name to allow for stroom_user records being deleted and re-used with
-- a different uuid.
CREATE TABLE IF NOT EXISTS `stroom_user_archive` (
  `id` int NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `display_name` varchar(255) NOT NULL,
  `full_name` varchar(255) DEFAULT NULL,
  `is_group` tinyint NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `stroom_user_archive_uuid_idx` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Idempotent
-- Populate the new table based on what we currently have in the stroom_user table
INSERT INTO stroom_user_archive (
    uuid,
    name,
    display_name,
    full_name,
    is_group)
SELECT
    su.uuid,
    su.name,
    su.display_name,
    su.full_name,
    su.is_group
FROM stroom_user su
ON DUPLICATE KEY UPDATE
    uuid = su.uuid,
    name = su.name,
    display_name = su.display_name,
    full_name = su.full_name,
    is_group = su.is_group;

SET SQL_NOTES=@OLD_SQL_NOTES;

-- vim: set tabstop=4 shiftwidth=4 expandtab:

Script `V07_06_00_915__drop_foreign_keys_to_stroom_user.sql`

Path: stroom-security/stroom-security-impl-db/src/main/resources/stroom/security/impl/db/migration/V07_06_00_915__drop_foreign_keys_to_stroom_user.sql

-- ------------------------------------------------------------------------
-- Copyright 2020 Crown Copyright
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
-- ------------------------------------------------------------------------

-- Stop NOTE level warnings about objects (not)? existing
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

-- --------------------------------------------------

DELIMITER $$

-- --------------------------------------------------

DROP PROCEDURE IF EXISTS security_run_sql_v1 $$

-- DO NOT change this without reading the header!
CREATE PROCEDURE security_run_sql_v1 (
    p_sql_stmt varchar(1000)
)
BEGIN

    SET @sqlstmt = p_sql_stmt;

    SELECT CONCAT('Running sql: ', @sqlstmt);

    PREPARE stmt FROM @sqlstmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END $$

-- --------------------------------------------------

DROP PROCEDURE IF EXISTS security_drop_constraint_v1 $$

-- e.g. security_drop_constraint_v1('MY_TABLE', 'MY_FK', 'FOREIGN KEY');
--      security_drop_constraint_v1('MY_TABLE', 'MY_UNIQ_IDX', 'INDEX');
--      security_drop_constraint_v1('MY_TABLE', 'PRIMARY', 'INDEX');
-- DO NOT change this without reading the header!
CREATE PROCEDURE security_drop_constraint_v1 (
    p_table_name varchar(64),
    p_constraint_name varchar(64),
    p_constraint_type varchar(64) -- e.g. FOREIGN KEY | UNIQUE
)
BEGIN
    DECLARE object_count integer;

    SELECT COUNT(1)
    INTO object_count
    FROM information_schema.table_constraints
    WHERE table_schema = database()
    AND table_name = p_table_name
    AND constraint_name = p_constraint_name;

    IF object_count = 0 THEN
        SELECT CONCAT(
            'Constraint ',
            p_constraint_name,
            ' does not exist on table ',
            database(),
            '.',
            p_table_name);
    ELSE
        CALL security_run_sql_v1(CONCAT(
            'alter table ', database(), '.', p_table_name,
            ' drop ', p_constraint_type, ' ', p_constraint_name));
    END IF;
END $$

DELIMITER ;

-- --------------------------------------------------

CALL security_drop_constraint_v1(
    'app_permission',
    'app_permission_user_uuid',
    'FOREIGN KEY');

CALL security_drop_constraint_v1(
    'doc_permission',
    'doc_permission_fk_user_uuid',
    'FOREIGN KEY');

-- --------------------------------------------------

DROP PROCEDURE IF EXISTS security_run_sql_v1;

DROP PROCEDURE IF EXISTS security_drop_constraint_v1;

-- --------------------------------------------------

SET SQL_NOTES=@OLD_SQL_NOTES;

-- vim: set tabstop=4 shiftwidth=4 expandtab:

Last modified February 11, 2025: Merge branch '7.7' into 7.8 (9d9eb5e)

Upgrade Notes

Warning

Java Version

Configuration File Changes

Stroom’s config.yml

Added Property Trees

Removed Property Trees

Stroom-Proxy’s config.yml

Database Migrations

Warning

Warning

Migration Scripts

Module stroom-activity

Script V07_06_00_200__activity_pre_migration_checks.java

Script V07_06_00_205__activity_user_uuid.sql

Module stroom-analytics

Script V07_06_00_405__execution_schedule_run_as_user_uuid.sql

Module stroom-annotation

Script V07_06_00_100__annotation_pre_migration_checks.java

Script V07_06_00_105__annotation_uuid.sql

Script V07_06_00_110__annotation_entry.sql

Module stroom-processor

Script V07_06_00_300__processor_filter_pre_migration_checks.java

Script V07_06_00_305__processor_filter_run_as_user_uuid.sql

Module stroom-security

Script V07_06_00_800__app_permission.sql

Script V07_06_00_900__doc_permission.sql

Script V07_06_00_905__user_display_name.sql

Script V07_06_00_910__user_add_deleted_col.sql

Script V07_06_00_915__drop_foreign_keys_to_stroom_user.sql

Stroom’s `config.yml`

Stroom-Proxy’s `config.yml`

Module `stroom-activity`

Script `V07_06_00_200__activity_pre_migration_checks.java`

Script `V07_06_00_205__activity_user_uuid.sql`

Module `stroom-analytics`

Script `V07_06_00_405__execution_schedule_run_as_user_uuid.sql`

Module `stroom-annotation`

Script `V07_06_00_100__annotation_pre_migration_checks.java`

Script `V07_06_00_105__annotation_uuid.sql`

Script `V07_06_00_110__annotation_entry.sql`

Module `stroom-processor`

Script `V07_06_00_300__processor_filter_pre_migration_checks.java`

Script `V07_06_00_305__processor_filter_run_as_user_uuid.sql`

Module `stroom-security`

Script `V07_06_00_800__app_permission.sql`

Script `V07_06_00_900__doc_permission.sql`

Script `V07_06_00_905__user_display_name.sql`

Script `V07_06_00_910__user_add_deleted_col.sql`

Script `V07_06_00_915__drop_foreign_keys_to_stroom_user.sql`