Upgrading repository

Things to keep in mind when upgrading

For Dataedo to work, all components must be in the same major version. So the Desktop in version 25.2 will work with Portal 25.2.2 and Repository in 25.2 but will not connect to a repository in version 25.1.

We recommend creating a backup of the repository database before upgrading in case of any issues - these most commonly occur if a network connection is lost during the upgrade.

You can also create a copy of the folder Dataedo Portal is running from. If Dataedo Portal is running from a virtual machine, it may be easier to create a snapshot of it before the upgrade process.

Upgrading Dataedo Portal

To upgrade Dataedo Portal, connect to the server running Dataedo Portal and run the installer.

When asked for a location/instance, choose the same option you've selected during installation. The default instance is C:\Dataedo Web.

If unsure where your Dataedo Portal is running from, open the IIS Manager, navigate to and select the Dataedo site, then click "Explore" in the top right corner to open the folder in File Explorer.

If you've correctly chosen the instance, the installer will show an option to update Dataedo Portal.

If you don't see this option, restart the installer and choose the path correctly. Do not choose the "Install" option! Selecting this option will create another instance of Dataedo, which may cause issues later.

After installation, Dataedo Portal will warn about the repository being in an older version. The "Upgrading repository" step below will resolve this.

Upgrading the Desktop application

To upgrade Dataedo Desktop, install the new version on each user's PC.

Upgrading Repository

To upgrade your repository, you'll need a user with db_owner role in the database and the default schema set to 'dbo'. We recommend you backup the database before continuing.

Upgrading the repository from the Desktop

The easiest way to upgrade your repository is through the Dataedo Desktop app. After connecting from Dataedo Desktop, you will be shown a warning cautioning about possible issues with Dataedo caused by upgrading - you can safely ignore these if you've already installed Dataedo Portal in the current version.

When asked, choose the "Detach Web" option twice.

After the upgrade process finishes, both Dataedo Portal and Desktop should be able to connect to the repository.

Grant Admin role script (for Dataedo 23.2 and newer)

If you're upgrading from a version older than 23.2, you may need to grant the Admin role to your user. To do this, connect to the repository database and run the following script:

SQL

    -- Script for creating a new group named Administrators, granting it user management privilege
    -- and adding YOUR_LOGIN_HERE to the group.
    -- Before running, change YOUR_LOGIN_HERE to your login below.

    DECLARE @custom_login NVARCHAR(1024);
    SET @custom_login = 'YOUR_LOGIN_HERE';

    -- Validate that the login exists
    IF NOT EXISTS (SELECT 1 FROM [dbo].[licenses] WHERE [login] = @custom_login)
    BEGIN
        RAISERROR('Login ''%s'' not found in [licenses] table. Update @custom_login before running.', 16, 1, @custom_login);
        RETURN;
    END

    -- Create group 'Administrators' if it does not exist
    IF COL_LENGTH('dbo.user_groups', 'type') IS NOT NULL
    BEGIN
        INSERT INTO [dbo].[user_groups] (
            [name], [type], [default], creation_date, last_modification_date
        )
        SELECT 'Administrators', 'MANUAL', 0, GETDATE(), GETDATE()
        WHERE NOT EXISTS (
            SELECT 1 FROM [dbo].[user_groups] WHERE [name] = 'Administrators'
        );
    END
    ELSE
    BEGIN
        INSERT INTO [dbo].[user_groups] (
            [name], [default], creation_date, last_modification_date
        )
        SELECT 'Administrators', 0, GETDATE(), GETDATE()
        WHERE NOT EXISTS (
            SELECT 1 FROM [dbo].[user_groups] WHERE [name] = 'Administrators'
        );
    END

    -- Grant repository management roles to the 'Administrators' group
    IF NOT EXISTS (SELECT 1 FROM [permissions] WHERE user_type = 'GROUP' AND user_group_id = (SELECT user_group_id FROM [dbo].[user_groups] WHERE [name] = 'Administrators') AND object_type = 'REPOSITORY')
        INSERT INTO [dbo].[permissions] (
            user_type, user_group_id, object_type, role_id, creation_date, last_modification_date
        )
        VALUES
            ('GROUP', (SELECT user_group_id FROM [dbo].[user_groups] WHERE [name] = 'Administrators'), 'REPOSITORY', 5, GETDATE(), GETDATE());

    -- Add the specified user to the 'Administrators' group
    IF NOT EXISTS (
        SELECT 1 FROM [dbo].[users_user_groups]
        WHERE user_id = (SELECT license_id FROM [dbo].[licenses] WHERE [login] = @custom_login)
        AND user_group_id = (SELECT user_group_id FROM [dbo].[user_groups] WHERE [name] = 'Administrators')
    )
        INSERT INTO [dbo].[users_user_groups] (
            user_id, user_group_id, creation_date, last_modification_date
        )
        VALUES (
                (SELECT license_id FROM [dbo].[licenses] WHERE [login] = @custom_login),
                (SELECT user_group_id FROM [dbo].[user_groups] WHERE [name] = 'Administrators'),
                GETDATE(), GETDATE()
            );

    -- Refresh cache tables
    TRUNCATE TABLE [dbo].[cache_area_ancestors];

    INSERT INTO cache_area_ancestors (area_id, ancestor_id, domain_id)
    SELECT area_id, parent_area_id, domain_id
        FROM areas
        WHERE parent_area_id IS NOT NULL;

    INSERT INTO cache_area_ancestors (area_id, ancestor_id, domain_id)
    SELECT a1.area_id, a2.ancestor_id, a1.domain_id
    FROM cache_area_ancestors a1
        INNER JOIN cache_area_ancestors a2 ON a1.ancestor_id = a2.area_id;

    -- Truncate only the permission cache tables that exist in this repository version
    DECLARE @sql NVARCHAR(MAX) = N'';

    SELECT @sql = @sql + N'TRUNCATE TABLE [dbo].' + QUOTENAME(t.name) + N';' + CHAR(13) + CHAR(10)
    FROM (VALUES
            ('cache_permissions_repository'),
            ('cache_permissions_repository_full'),
            ('cache_permissions_databases'),
            ('cache_permissions_databases_full'),
            ('cache_permissions_areas'),
            ('cache_permissions_areas_full'),
            ('cache_permissions_domains'),
            ('cache_permissions_domains_full')
        ) AS t(name)
    WHERE OBJECT_ID(N'dbo.' + t.name, N'U') IS NOT NULL;

    EXEC sp_executesql @sql;

    -- Populate cache_permissions_repository (legacy table, older versions only)
    IF OBJECT_ID(N'dbo.cache_permissions_repository', N'U') IS NOT NULL
        EXEC sp_executesql N'
    INSERT INTO cache_permissions_repository (user_id)
    SELECT user_id
    FROM (
            -- Permissions for users
            SELECT p.user_id
            FROM dbo.permissions AS p
                    INNER JOIN dbo.roles AS r ON p.role_id = r.role_id
                    INNER JOIN dbo.role_actions AS ra ON r.role_id = ra.role_id
            WHERE object_type = ''REPOSITORY''
            AND action_code = ''DOCUMENTATION_VIEW''
            AND p.user_id IS NOT NULL

            UNION ALL

            -- Permissions for user groups
            SELECT uug.user_id
            FROM dbo.permissions AS p
                    INNER JOIN dbo.roles AS r ON p.role_id = r.role_id
                    INNER JOIN dbo.role_actions AS ra ON r.role_id = ra.role_id
                    INNER JOIN dbo.users_user_groups AS uug ON p.user_group_id = uug.user_group_id
            WHERE object_type = ''REPOSITORY''
            AND action_code = ''DOCUMENTATION_VIEW''
        ) AS t
    GROUP BY user_id;';

    -- Populate cache_permissions_repository_full
    -- Every column other than user_id is an action code, so the column list is read from the actual table
    DECLARE @columns NVARCHAR(MAX) = N'';
    DECLARE @maxColumns NVARCHAR(MAX) = N'';
    DECLARE @caseColumns NVARCHAR(MAX) = N'';

    SELECT
        @columns = @columns + N', ' + QUOTENAME(c.name),
        @maxColumns = @maxColumns + N', MAX(' + QUOTENAME(c.name) + N') AS ' + QUOTENAME(c.name),
        @caseColumns = @caseColumns + N', CASE WHEN action_code = N''' + REPLACE(c.name, N'''', N'''''') + N''' THEN 1 ELSE 0 END AS ' + QUOTENAME(c.name)
    FROM sys.columns AS c
    WHERE c.object_id = OBJECT_ID(N'dbo.cache_permissions_repository_full', N'U')
    AND c.name <> N'user_id'
    ORDER BY c.column_id;

    SET @sql = N'
    INSERT INTO cache_permissions_repository_full (user_id' + @columns + N')
    SELECT user_id' + @maxColumns + N'
    FROM (
            -- Permissions for users
            SELECT p.user_id' + @caseColumns + N'
            FROM dbo.permissions AS p
                    INNER JOIN dbo.roles AS r ON p.role_id = r.role_id
                    INNER JOIN dbo.role_actions AS ra ON r.role_id = ra.role_id
            WHERE object_type = ''REPOSITORY''
            AND p.user_id IS NOT NULL

            UNION ALL

            -- Permissions for user groups
            SELECT uug.user_id' + @caseColumns + N'
            FROM dbo.permissions AS p
                    INNER JOIN dbo.roles AS r ON p.role_id = r.role_id
                    INNER JOIN dbo.role_actions AS ra ON r.role_id = ra.role_id
                    INNER JOIN dbo.users_user_groups AS uug ON p.user_group_id = uug.user_group_id
            WHERE object_type = ''REPOSITORY''
        ) AS t
    GROUP BY user_id;';

    EXEC sp_executesql @sql;

PostgreSQL

-- Script for creating a new group named Administrators, granting it user management privilege
-- and adding YOUR_LOGIN_HERE to the group.
-- Before running, change YOUR_LOGIN_HERE to your login below.

DO $$
DECLARE
    custom_login   text := 'YOUR_LOGIN_HERE';
    v_user_id      integer;
    v_group_id     integer;
    v_table        text;
    v_columns      text;
    v_aggregates   text;
BEGIN
    -- Validate that the login exists
    SELECT license_id INTO v_user_id FROM licenses WHERE login = custom_login;

    IF v_user_id IS NULL THEN
        RAISE EXCEPTION 'Login ''%'' not found in licenses table. Update custom_login before running.', custom_login;
    END IF;

    -- Create group 'Administrators' if it does not exist
    SELECT user_group_id INTO v_group_id FROM user_groups WHERE name = 'Administrators';

    IF v_group_id IS NULL THEN
        IF EXISTS (
            SELECT 1 FROM pg_attribute
            WHERE attrelid = to_regclass('user_groups') AND attname = 'type' AND NOT attisdropped
        ) THEN
            INSERT INTO user_groups (name, type, "default", creation_date, last_modification_date)
            VALUES ('Administrators', 'MANUAL', false, now(), now())
            RETURNING user_group_id INTO v_group_id;
        ELSE
            INSERT INTO user_groups (name, "default", creation_date, last_modification_date)
            VALUES ('Administrators', false, now(), now())
            RETURNING user_group_id INTO v_group_id;
        END IF;
    END IF;

    -- Grant repository management roles to the 'Administrators' group
    IF NOT EXISTS (
        SELECT 1 FROM permissions
        WHERE user_type = 'GROUP' AND user_group_id = v_group_id AND object_type = 'REPOSITORY'
    ) THEN
        INSERT INTO permissions (user_type, user_group_id, object_type, role_id, creation_date, last_modification_date)
        VALUES ('GROUP', v_group_id, 'REPOSITORY', 5, now(), now());
    END IF;

    -- Add the specified user to the 'Administrators' group
    IF NOT EXISTS (
        SELECT 1 FROM users_user_groups WHERE user_id = v_user_id AND user_group_id = v_group_id
    ) THEN
        INSERT INTO users_user_groups (user_id, user_group_id, creation_date, last_modification_date)
        VALUES (v_user_id, v_group_id, now(), now());
    END IF;

    -- Refresh cache tables
    TRUNCATE TABLE cache_area_ancestors;

    INSERT INTO cache_area_ancestors (area_id, ancestor_id, domain_id)
    SELECT area_id, parent_area_id, domain_id
    FROM areas
    WHERE parent_area_id IS NOT NULL;

    INSERT INTO cache_area_ancestors (area_id, ancestor_id, domain_id)
    SELECT a1.area_id, a2.ancestor_id, a1.domain_id
    FROM cache_area_ancestors a1
        INNER JOIN cache_area_ancestors a2 ON a1.ancestor_id = a2.area_id;

    -- Truncate only the permission cache tables that exist in this repository version
    FOREACH v_table IN ARRAY ARRAY[
        'cache_permissions_repository',
        'cache_permissions_repository_full',
        'cache_permissions_databases',
        'cache_permissions_databases_full',
        'cache_permissions_areas',
        'cache_permissions_areas_full',
        'cache_permissions_domains',
        'cache_permissions_domains_full'
    ] LOOP
        IF to_regclass(v_table) IS NOT NULL THEN
            EXECUTE format('TRUNCATE TABLE %I', v_table);
        END IF;
    END LOOP;

    -- Populate cache_permissions_repository (legacy table, older versions only)
    IF to_regclass('cache_permissions_repository') IS NOT NULL THEN
        INSERT INTO cache_permissions_repository (user_id)
        SELECT user_id
        FROM (
                -- Permissions for users
                SELECT p.user_id
                FROM permissions AS p
                        INNER JOIN roles AS r ON p.role_id = r.role_id
                        INNER JOIN role_actions AS ra ON r.role_id = ra.role_id
                WHERE p.object_type = 'REPOSITORY'
                AND ra.action_code = 'DOCUMENTATION_VIEW'
                AND p.user_id IS NOT NULL

                UNION ALL

                -- Permissions for user groups
                SELECT uug.user_id
                FROM permissions AS p
                        INNER JOIN roles AS r ON p.role_id = r.role_id
                        INNER JOIN role_actions AS ra ON r.role_id = ra.role_id
                        INNER JOIN users_user_groups AS uug ON p.user_group_id = uug.user_group_id
                WHERE p.object_type = 'REPOSITORY'
                AND ra.action_code = 'DOCUMENTATION_VIEW'
            ) AS t
        GROUP BY user_id;
    END IF;

    -- Populate cache_permissions_repository_full
    -- Every column other than user_id is an action code, so the column list is read from the actual table
    SELECT string_agg(format('%I', a.attname), ', ' ORDER BY a.attnum),
        string_agg(format('bool_or(t.action_code = %L)::%s AS %I',
                            a.attname, format_type(a.atttypid, a.atttypmod), a.attname), ', ' ORDER BY a.attnum)
    INTO v_columns, v_aggregates
    FROM pg_attribute AS a
    WHERE a.attrelid = to_regclass('cache_permissions_repository_full')
    AND a.attnum > 0
    AND NOT a.attisdropped
    AND a.attname <> 'user_id';

    EXECUTE format($sql$
        INSERT INTO cache_permissions_repository_full (user_id, %s)
        SELECT t.user_id, %s
        FROM (
                -- Permissions for users
                SELECT p.user_id, ra.action_code
                FROM permissions AS p
                        INNER JOIN roles AS r ON p.role_id = r.role_id
                        INNER JOIN role_actions AS ra ON r.role_id = ra.role_id
                WHERE p.object_type = 'REPOSITORY'
                AND p.user_id IS NOT NULL

                UNION ALL

                -- Permissions for user groups
                SELECT uug.user_id, ra.action_code
                FROM permissions AS p
                        INNER JOIN roles AS r ON p.role_id = r.role_id
                        INNER JOIN role_actions AS ra ON r.role_id = ra.role_id
                        INNER JOIN users_user_groups AS uug ON p.user_group_id = uug.user_group_id
                WHERE p.object_type = 'REPOSITORY'
            ) AS t
        GROUP BY t.user_id
    $sql$, v_columns, v_aggregates);

    RAISE NOTICE 'User % added to group Administrators (user_group_id = %) and permission cache refreshed.', custom_login, v_group_id;
END
$$;