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 EFT installer prompts you to upgrade the ARM schema when you upgrade EFT, if the schema has 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. On the Confirm Database Upgrade page, if there are updates to the database, review the warning message, click I understand, then click Install. (If there are no changes to be made to the database, the check box will not appear.)
If there are any errors to repair, you can repair them on the database now, and then click Reanalyze to retest.
The wizard will upgrade the database (if needed) and the specified EFT components. On the final page of the wizard, select the check boxes, as needed, then click Finish.
Below that is a list of ARM tables (i.e., the schema). You can also view the database details using SQL Server Management Studio.
You might also want to review the information in the help file for your version of EFT, such as "Upgrading the EFT Database" and "Upgrading a Large Database." The "Upgrading a Large Database" topic includes instructions for manually upgrading the database "out of band" instead of during installation.
This update applies a variety of fixes, such as correcting the "Activity - All Transfers" report to not show SFTP transactions with the filename twice.
This update adds Advanced Workflow Event Rules steps to the database for use by Insight.
Allows Workspace path to be missing in "tbl_WorkspaceActions" table
Fixed purge script
This update corrects wrong variables types in "SP_INSERT_TBL_OUTLOOKREPORT" procedure for Oracle.
This update extends the EventRules table EventName column to 250 characters (from 50 characters).
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.
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;
Applies to: SQL Server, Oracle
Change Type: Stored Procedure Modification
Description: Added Event actions’ parameters that weren’t being audited by ARM.
Applies to: SQL Server, Oracle
Change Type: Table Change, Stored Procedure Modification, Function Modification
Description: This upgrade is needed to support HA reporting functionality.
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.
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.
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.
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.
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.
Applies to: SQL Server, Oracle
Change Type: Table Change
Description: This upgrade removes the unused tbl_ResultCodes table.
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.
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'.
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
- 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:
- 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:
- Finally, the upgrade process will create a new View called vw_ProtocolCommands.
Oracle Upgrade
- 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.
- 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:
- As of EFT 6.5 the original TBL_SCHEMAVERSION table has been
deprecated and is dropped by the upgrade process if present.
- 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.
- 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.
- 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:
- 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:
- ActionStatus - Success: File was received.sent, MDN received/sent; Failure: Action failed to send/receive file or MDN
- ActionType:
- AT_INBOUND_TRANSACTION - recipient identifies the sender by AS2 ID
- AT_OUTBOUND_TRANSACTION - sender sends the message to the recipient's AS2 server
- AT_RECEIVE_FILE - receive file
- AT_SEND_FILE - send file
- AT_SEND_MDN - recipient sends service message MDN, notifying the fact of receiving main file
- AT_RECEIVE_MDN - receive of the MDN message
- AT_MDN_VERIFICATION - verification of the MDN message
- FileID: Key for linking to tbl_AS2Files
- AS2 protocol stages of work:
- 1st record. Initialize inbound transaction
- 2nd record. Send file
- 3rd record. Recipient sends service message MDN
tbl_AS2Files
- Contains information about files transferred via AS2:
Refer to https://help.globalscape.com/help/gs_com_api/COM_Enum_Reference.htm for ContentType values.
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