Update Tool

The Update Tool has been revised. The following sections provide an overview of the most important changes. For more information, seeUpdate Tool.

Updating the database

X4 BPMS uses a database and requires a database connection via JDBC.

Setting up the database

The following information is required for the connection:

• JDBC URL (for example, jdbc:postgres:…)

• Database user

• Password

The JDBC URL is database specific and must reference the correct schema for X4.

Preparing the database

The following instructions for PostgreSQL, SQL Server and Oracle can be used to set up the database.

The following placeholders are used in the instructions:

• ${schemaName}: Name of the schema for the X4 tables (for example, X4SERVER)

• ${databaseName}: Name of the database (for SQL Server)

• ${userName}: User name

• ${userPassword}: The user's password

PostgreSQL

CREATE USER ${userName} WITH PASSWORD '${userPassword}';
CREATE SCHEMA ${schemaName} AUTHORIZATION ${userName};
-- Grant privileges
GRANT CONNECT ON DATABASE ${databaseName} TO ${userName};
GRANT USAGE ON SCHEMA ${schemaName} TO ${userName};
GRANT CREATE ON SCHEMA ${schemaName} TO ${userName};
ALTER USER ${userName} SET search_path TO ${schemaName}, public;

After the database is successfully created, you can connect to the database using the following JDBC URL:

jdbc:postgresql://<host>:5432/${databaseName}

SQL Server

-- Switch to master database to create the server login
USE "master";
CREATE LOGIN ${userName}
  WITH PASSWORD = '${userPassword}',
       DEFAULT_DATABASE = ${databaseName},
       CHECK_EXPIRATION=OFF,
       CHECK_POLICY=OFF;
-- Switch to X4 database and create a user mapped to the login
USE ${databaseName};
-- optional: create a schema if it does not exist
CREATE SCHEMA ${schemaName};
CREATE USER ${userName}
   FOR LOGIN ${userName}
  WITH DEFAULT_SCHEMA = ${schemaName};
ALTER ROLE db_datareader ADD MEMBER ${userName};
ALTER ROLE db_datawriter ADD MEMBER ${userName};
ALTER ROLE db_ddladmin ADD MEMBER ${userName};

After the database is successfully created, the following JDBC URL can be used to connect to the database (an additional parameter such as encrypt=false may be required):

jdbc:sqlserver://<host>:1433;databaseName=${databaseName}

Oracle

CREATE USER "${userName}" IDENTIFIED BY "${userPassword}";
-- Grant Privileges --
GRANT UNLIMITED TABLESPACE TO ${userName};
GRANT CREATE SESSION TO ${userName};
GRANT CREATE TABLE TO ${userName};
GRANT CREATE SEQUENCE TO ${userName};
GRANT CREATE TRIGGER TO ${userName};
GRANT SELECT ON SYS.DBA_RECYCLEBIN TO ${userName};

After the database is successfully created, the following JDBC URL can be used to connect to the database (depending on the version and setup of the database):

jdbc:oracle:thin:@<host>:1521:<sid>
jdbc:oracle:thin:@//<host>:1521/<serviceName

Deploying a schema

Run the following command from a command prompt:

java -jar de.softproject.x4.update-7.5.0.jar --run-database-update --jdbc-url <jdbc-url> --jdbc-user <user> --jdbc-password <password>

Migrating the existing database

If an older version of the X4 BPMS (for example, 7.4.x) is already in use, the existing schema can be updated. During migration, a distinction must be made as to whether the schema was created with or without Liquibase.

Migrating a schema created with Liquibase

Since the available schema depends on how it was installed, we cannot provide a general migration script here.

However, the basic points are identical in all scenarios:

• Ensure that the dedicated database user has similar rights and privileges as described in the previous chapter.

• If the Liquibase control tables (i.e. databasechangelog , databasechangeloglock) are present in the target schema, you can do the following:

java -jar de.softproject.x4.update-7.5.0.jar --run-database-update --jdbc-url <jdbc-url> --jdbc-user <usr> --jdbc-password <pwd>

• If they are in a separate schema (for example, schema_a), you must:

  • Ensure that the database user is able to update the data in these tables

  • Add the --liquibase-schema_a parameter to the command line

Migrating a schema created without Liquibase

A scheme created without Liquibase usually contains more tables than are required to operate X4. However, all required tables are available and compatible.

If the database user has all the necessary rights and privileges, the migration is simple:

java -jar de.softproject.x4.update-7.5.0.jar --run-database-update --jdbc-url <jdbc-url> --jdbc-user <usr> --jdbc-password <pwd>

Troubleshooting

Before executing the migration tool, ensure that the specific database requirements are met and follow the instructions provided for your database system.

PostgreSQL

If PostgreSQL is used as the X4 database, perform the following steps before running the migration tool:

Prerequisites

• An active connection to the X4 database as a PostgreSQL superuser.

• Authorization to execute SQL scripts within the active connection.

• Knowledge of the specific database user name that X4 uses to establish the connection.

• Knowledge of the target schema name where the X4 tables are stored.

Instructions:

1. Replace the following placeholders in the SQL script beneath.

   • ${schemaName}: The name of the schema for X4 tables, e.g. X4SERVER.

   • ${userName}: The user name used by X4 to connect to the database.

2. Execute the adjusted SQL script beneath as PostgreSQL superuser.

SQL Script:

-- Grant create priviledge on the X4 schema.
GRANT CREATE ON SCHEMA ${schemaName} TO ${userName};
-- Set X4 schema as first lookup schema for the user.
ALTER USER ${userName} SET search_path TO ${schemaName}, public;
-- Transfer ownership of the X4 schema.
ALTER SCHEMA ${schemaName} OWNER TO ${userName};
-- Transfer ownership of all x4 tables.
DO $$
DECLARE
    tbl RECORD;
BEGIN
    FOR tbl IN 
        SELECT tablename 
        FROM pg_tables 
        WHERE schemaname = '${schemaName}'
    LOOP
        EXECUTE 'ALTER TABLE ${schemaName}.' || quote_ident(tbl.tablename) || ' OWNER TO ${userName}';
    END LOOP;
END;
$$;

MSSQL

If MSSQL is used as X4 database, ensure that the following requirements are met
and follow the instrunction beneath them, before the migration tool gets executed.

Requirements:

• An existing connection to the X4 database as an SQL Server administrator.

• SQL scripts can be executed in the existing connection.

• The user which X4 uses to establish the connection to the X4 database is known.

Instructions:

1. Replace the following placeholder in the SQL script beneath.

   • ${userName}: The user name used by X4 to connect to the database.

2. Execute the adjusted SQL script beneath as SQL Server database adminstrator.

-- Add user to db_ddladmin role.
ALTER ROLE db_ddladmin ADD MEMBER ${userName};

Oracle

If Oracle is used as X4 database, ensure that the following requirements are met
and follow the instrunction beneath them, before the migration tool gets executed.

Requirements:

• An existing connection to the Oracle database as an administrator.

• SQL scripts can be executed in the existing connection.

• The user which X4 uses to establish the connection to the X4 database is known.

Instructions:

1. Replace the following placeholder in the SQL script beneath.

   • ${userName}: The user name used by X4 to connect to the database.

2. Execute the adjusted SQL script beneath as Oracle database adminstrator.

-- Add grant privileges to the X4 user.
GRANT CREATE TABLE TO ${userName};
GRANT CREATE SEQUENCE TO ${userName};
GRANT CREATE TRIGGER TO ${userName};
GRANT SELECT ON SYS.DBA_RECYCLEBIN TO ${userName};