Menu

Search

GlobalSCAPE Knowledge Base


Is there a change document for the ARM database schema?


GlobalSCAPE 5
EFT Express (SMB) & Enterprise

THE INFORMATION IN THIS ARTICLE APPLIES TO:

  • EFT Server, version 6.0 and later

QUESTION

Is there a change document for the ARM database schema?

ANSWER

The ARM schema rarely changes. Currently, there is no auto-versioning of the schema, so you are prompted to upgrade the schema every time you upgrade EFT, even if the schema hasn't changed. Below is a list of changes from ARM schema version 0.0.0.0 to present, from newest to oldest. The database version number appears in the installer during upgrade.

Below that is a list of ARM tables (i.e., the schema). You can also view the database details using SQL Server Management Studio.

ARM Schema Update Version 17.0.0.0  (EFT v7.4.11)

This update applies a variety of fixes, such as correcting the "Activity - All Transfers" report to not show SFTP transactions with the filename twice.

ARM Schema Update Version 15.0.0.0  (EFT v7.4.7)

This update adds Advanced Workflow Event Rules steps to the database for use by Insight.

ARM Schema Update Version 12.1.0.0  (EFT v7.4.2)

This update corrects wrong variables types in "SP_INSERT_TBL_OUTLOOKREPORT" procedure for Oracle.

ARM Schema Update Version 11.1.0.0  (EFT v7.3.5)

This update extends the EventRules table EventName column to 250 characters (from 50 characters).

ARM Schema Update Version 11.0.0.0  (EFT v7.3.3)

This update adds support for Workspaces Outlook Add-in auditing and reporting.

  • New Table: tbl_OutlookReport
  • New Procedure: sp_Insert_tbl_OutlookReport
  • New Index: IX_tbl_OutlookReport_TransactionDate
  • New Foreign Key: FK_tbl_OutlookReport_ParentTransID

The following table was added:

tbl_OutlookReport - Create Workspaces Outlook Add-in auditing table

The following procedure was added:

sp_Insert_tbl_OutlookReport - Create or alter the procedure sp_Insert_tbl_OutlookReport

Refer to ARM tables below for details of the new table.

ARM Schema Update Version 10.0.0.0  (EFT v7.1)

This upgrade is needed to support Workspaces auditing;

  • Four tables have been added: tbl_WorkspaceActions, tbl_WorkspaceParticipants, lu_WorkspaceActions, lu_WorkspacesParticipantStates;

  • Two new stored procedures have been added: sp_Insert_tbl_WorkspaceActions and sp_Insert_tbl_WorkspacePart;

  • The width of the version column of tbl_Schema_Version has been increased to support the new, longer version number;

ARM Schema Update Version 9.2.0.0 (EFT v7.0.3)

  • Makes sure that the details column of the Event action table gets properly updated;

ARM Schema Update Version 9.0.0.0 to 9.1.0.0

Applies to: SQL Server, Oracle

Change Type: Stored Procedure Modification

Description: Added Event actions’ parameters that weren’t being audited by ARM.

ARM Schema Update Version 9.0.0.0 (EFT v7.0.3)

Applies to: SQL Server, Oracle

Change Type: Table Change, Stored Procedure Modification, Function Modification

Description: This upgrade is needed to support HA reporting functionality.

  • A NodeName column has been added to:

  • A NodeName input has been included in:

Additionally, to better record the beginning and ending of Event Rule actions, a Time_Stamp_End had been added to tbl_Actions, sp_Insert_tbl_Actions has been updated and a new stored procedure sp_Update_tbl_Actions_Finished has been added.

ARM Schema Update Version 8.0.0.0 (EFT v7.0)

Applies to: Oracle

Change Type: Index Creation

Description:

Due to errors in earlier versions of the Oracle database creation scripts the following indexes may not have been created in Oracle databases:

  • IX_tbl_EventRules_EventGUID

  • IX_tbl_ProtocolCommands_Other

  • IX_tbl_ProtocolCommands_Command_IsInternal

This upgrade checks for the presence of these indexes and creates them, if necessary.

ARM Schema Update Version 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.

ARM Schema Update Version 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 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 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 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 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 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:
  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:
  7. Finally, the upgrade process will create a new View called vw_ProtocolCommands

ARM Tables

The tables created during installation are listed below. Because all of the EFT modules and features are available during the trial, all of the tables below are created, even if you do not activate that module/feature.

tbl_Actions - Logs Actions performed when Event Rules are processed

  • Time_stamp_end added in EFT v7.0
  • ActionGUID added in EFT v7.4.11

tbl_AdminActions - Logs Actions performed by administrators in EFT

tbl_AS2Actions - Contains information about separate Actions for each AS2 transaction:

tbl_AS2Files - Contains information about files transferred via AS2:

tbl_AS2Transactions - Contains details of AS2 Transactions:

  • NodeName added in EFT v7.0

tbl_Authentications - Logs authentication attempts for administrators and users per Site

 tbl_AWESteps

tbl_ClientOperations - Logs upload/download/create/etc Actions performed by EFT Event Rules when acting as a client

tbl_CustomCommands - Logs details of custom commands being executed.  These are typically launched by Event Rules

tbl_EventRules - Logs details of Event Rules that have been processed

  • EventName extended to 250 characters, from 50 characters in v7.3.5 and later

tbl_EventRuleTransfers - Logs Event Rule Transfer failures, if configured in the administration interface, on the Server > Logs tab > Diagnostic Logging Settings > Configure.

tbl_Groups - Provides plain text group name for reporting (part of db normalization)

tbl_OutlookReport - Provides information for creating Outlook reports(added in EFT v7.3.3)

tbl_PCIViolations - Logs PCI violations for PCI DSS compliance testing reports

tbl_ProtocolCommands - Logs detailed client commands sent for various protocols (ftp, http, etc)

tbl_SAT_Emails - Logs the notification e-mails sent by the SAT module

tbl_SAT_Files - Logs the files uploaded by the SAT module

tbl_SAT_Transactions - Audits transactions managed by the Secure Ad Hoc Transfer (SAT) module.

(NodeName added in EFT v7.0)

tbl_Schema_Version - Maintains the current version of the ARM schema

  • Version changed from [nchar](7) to [nchar](20) in EFT v7.1

tbl_ServerInternalEvents

tbl_SocketConnections - Logs details (IP addresses, port numbers, etc) of individual socket connections for various protocols (FTP, HTTP, etc).

tbl_Transactions - Provides transaction references to correlate data from various ARM tables

  • NodeName added in EFT v7.0

tbl_WorkspaceActions – Logs actions performed to Workspaces (added in EFT v7.1)

tbl_WorkspaceParticipants – Logs details about Workspace participants (added in EFT v7.1)

lu_WorkspaceActions – Lookup table for workspace action values (added in EFT v7.1)

                [ID] [int] IDENTITY(1,1) NOT NULL,

                [ActionValue] tinyint NOT NULL,

                [ActionName] nvarchar(20) NOT NULL

Workspace action values and names:

0 – Unknown

1 – Create

2 – Update

3 – Invite

4 – Remove

5 – Delete

lu_WorkspacesParticipantStates – Lookup table for workspace participant states (added in EFT v7.1)

                [ID] [int] IDENTITY(1,1) NOT NULL,

                [StateValue] tinyint NOT NULL,

                [StateName] nvarchar(20) NOT NULL

Workspace participants StateValues and StateNames:

0 – Unknown

1 – Invited

2 – Registered

3 – Joined

4 – Removed


Also In This Category


On a scale of 1-5, please rate the helpfulness of this article


Not Helpful
Very Helpful
Optionally provide private feedback to help us improve this article...

Thank you for your feedback!


Comments require login or registration.

Details
Last Modified: Last Month
Last Modified By: kmarsh
Type: FAQ
Article not rated yet.
Article has been viewed 11K times.
Options
Find Similar