ARM Database Schema Change Tracking

The ARM database schema for EFT v6.5 has undergone many changes, and each is described below. The database version number appears in the installer during upgrade.

When you upgrade from v6.3 or v6.4 to version 6.5, each of the changes, from version 0.0.0.0 to the released version, will be made to the database.

ARM Schema Update Version 0.0.0.0 to 1.0.0.0

Applies to: SQL Server, Oracle

Change Type: Multiple, see below for specific changes

Description:

This upgrade modifies the tables to use the nchar/nvarchar data type to allow persistence of various languages within the database. This upgrade also resolves issues with databases created by earlier versions of EFT.

Converting existing data to the new data types should be considered a significant upgrade process. Please consult the EFT help topics Upgrading the EFT Database and Upgrading Large Databases for additional information.

Note that for the most part the upgrade script may be re-executed multiple times in the case that an error must be resolved by manual intervention.

SQL Server Upgrade

  1. When upgrading databases earlier than EFT 6.4, the upgrade process will increase the size of the following columns to support storage of IPv6 addresses:
  2. The upgrade will then proceed with changing all char and varchar columns to nchar and nvarchar. Be aware that this process drops the majority of the objects (other than the tables) prior to converting the data types and then recreates them afterwards. The upgrade uses the following process to migrate to the nchar/nvarchar data type:
    1. Drop all stored procedures
    2. Drop all functions
    3. Drop all indexes
    4. Drop all constraints
    5. For each table to be converted:
      1. Verify if the table has already been converted by checking the data type of one of the columns to be converted. If it is already nvarchar the table is assumed to have been upgraded and is skipped.
      2. Create a staging table stage_<OriginalTableName>
      3. In batches of 500,000, insert data from the source table into the staging table by selecting batches based on the primary key column
      4. If no errors occur during the data migration, drop the original table and rename the staging table to the original
    6. Recreate the constraints (primary keys and foreign keys)
    7. Recreate the indexes
    8. Recreate the functions
    9. Recreate the stored procedures

      This step also resolves an issue in the sp_GetInboundTransfersInfo stored procedure. In earlier versions of the database this procedure was missing an ORDER BY clause. This clause is now included in the procedure definition so the issue is resolved when this procedure is recreated.

  3. Finally, the upgrade process will create a new View called vw_ProtocolCommands.

Oracle Upgrade

  1. EFT now includes a View in the database. Originally the database account created for use by EFT did not include the CREATE VIEW privilege. This privilege must be granted to the account prior to upgrading to this version of the database. This upgrade will attempt to create a test view to ensure the account has the privilege before proceeding further into the upgrade process.
  2. When upgrading databases earlier than EFT 6.4 the upgrade process will increase the size of the following columns to support storage of IPv6 addresses:
  3. As of EFT 6.5 the original TBL_SCHEMAVERSION table has been deprecated and is dropped by the upgrade process if present.
  4. In EFT 6.3 the SQL scripts had an issue where the TBL_PCIVIOLATIONS was defined twice. Once with the ID column called PCIVIOLATIONID and later with it called PCIVIOLATIONSID. Unfortunately this led to some issues down the road. To be consistent with the SQL Server table definitions we ultimately need to ensure the column name is PCIVIOLATIONID. If the incorrect column name is detected then it is renamed. Note that the trigger used to insert the sequence number is dropped and recreated later as part of the Unicode upgrade process. Additionally, the table may have been created without the PK_TBL_PCIVIOLATIONS primary key. If this is detected then the primary key is created.
  5. Earlier scripts created a table called TBL_ADMINCOMMANDS. This table is not used by EFT; if detected it is dropped, as is the associated sequence TBL_ADMINCOMMANDS_SEQ.
  6. The upgrade will then proceed with changing all char and varchar columns to nchar and nvarchar. Be aware that this process drops the majority of the objects (other than the tables) prior to converting the data types and then recreates them afterwards. The upgrade uses the following process to migrate to the nchar/nvarchar data type:
    1. Drop all stored procedures
    2. Drop all functions
    3. Drop all indexes
    4. Drop all constraints
    5. For each table to be converted:
      1. Determine if the table has already been converted by checking the data type of one of the columns to be converted. If it is already nvarchar, the table is assumed to have been upgraded and is skipped.
      2. Create a staging table STAGE_<OriginalTableName>
      3. Copy the data into the staging table using an INSERT INTO/SELECT FROM statement
      4. If no errors occurred, drop the original table and rename the staging table to the original table name.
    6. Create triggers on each table for id column generation
    7. Recreate the primary keys
    8. Recreate the foreign keys
    9. Recreate the indexes
    10. Recreate the stored procedures
    11. Since the above process drops and recreates many of the database objects, it inherently resolves the following problems that may be present in existing databases:
      1. Removes the FK_TBL_ADMINCOMMANDS_TRANSID foreign key if present. The corresponding table is no longer used.
      2. Removes the TBL_ADMINCOMMAND_TRG trigger if present. The corresponding table is no longer used.
      3. Removes the PK_TBL_ADMINCOMMANDS index if present. The corresponding table is no longer used.
      4. Removes the PK_TBL_SCHEMAVERSION index if present. The corresponding table is no longer used.
      5. Resolves an issue in the SP_GETINBOUNDTRANSFERSINFO stored procedure. In earlier versions of the database this procedure was missing an ORDER BY clause.
      6. Earlier versions of the database may be missing the following indexes, which will be created during the upgrade process:
        • IX_TBL_CLIENTOPS_TXNID
        • IX_TBL_AUTH_TIME_STAMP
        • IX_TBL_CLIENTOPS_TIME_STAMP
        • IX_TBL_CUSCMDS_TIME_STAMP
        • IX_TBL_AUTH_TRANSACTIONID
  7. Finally, the upgrade process will create a new View called vw_ProtocolCommands

ARM Schema Update Version 1.0.0.0 to 2.0.0.0

Applies to: SQL Server only

Change Type: User Account Modification

Description:

SQL Server databases created using the EFT version 6.3 database creation scripts contained a defect in which the EFT database user account was created with its default schema set to a non-existent schema. The schema had the same name as the username.

Later versions of the EFT database creation scripts set the database user account's default schema to 'dbo' which is more standard. Additionally, the user account was created as a 'db_owner' which results in the various database objects being created in the dbo schema anyway.

To resolve this inconsistency, this upgrade will determine if the database user account's default schema has the same name as the account and if so set the default schema to 'dbo'.

ARM Schema Update Version 2.0.0.0 to 3.0.0.0

Applies to: SQL Server only

Change Type: Stored Procedure Modification

Description:

This upgrade recreates the sp_Insert_tbl_Groups stored procedure to resolve an issue in which the procedure failed to obtain the newly generated identity value after executing the sp_Insert_tbl_Authentications stored procedure.

ARM Schema Update Version 3.0.0.0 to 4.0.0.0

Applies to: SQL Server, Oracle

Change Type: Table Change

Description:

This upgrade removes the unused tbl_ResultCodes table.

ARM Schema Update Version 4.0.0.0 to 5.0.0.0

Applies to: SQL Server, Oracle

Change Type: Function Modification, Stored Procedure Modification

Description:

This upgrade recreates the f_TransferResult and f_CommandProtocolError functions and the sp_GetInboundTransfersInfo procedure to resolve an issue in which aborted transfers were not always appearing correctly in the Status Viewer.

ARM Schema Update Version 5.0.0.0 to 6.0.0.0

Applies to: None

Change Type: None

Description:

This upgrade was replaced by a later upgrade and is left as a placeholder to maintain proper versioning. This upgrade has no effect on the database.

ARM Schema Update Version 6.0.0.0 to 7.0.0.0

Applies to: SQL Server, Oracle

Change Type: Function Modification, Stored Procedure Modification

Description:

This upgrade recreates the f_TransferResult function and the sp_GetInboundTransfersInfo procedure to resolve an issue by which finished HTTP/S downloads were not appearing in the Status Viewer or reports.