Class tables¶
Class Table Columns¶
Class tables are not added by the Connector on deployment. Class tables are added by the developer using the Connector for those M‑Files classes that will be used in the application. See the functional description for the creation and use of the class tables.
Table name: The name of the class table is defined in column TableName in the MFClass table. The Connector creates a default name for all tables. These names can be edited.
Column name: The columns in the class tables are automatically assigned on creation. The column name is defined in the MFProperty table in the column ‘ColumnName’. These names can be edited.
Column definitions: Several special columns are automatically created. The column order also has a specific arrangement. See “Table columns” for further detail.
Class tables and their columns¶
All of the columns defined in the MFClassProperty Table for the specified class will be included in the Class Table with the data types defined above.
Where a property has an ID column and a name column (for example Customer and Customer_ID) the name column is incidental and does not have to be updated when changes are made. Only the ID has to be updated. The name column will be automatically refreshed from the metadata.
MF ad hoc columns¶
M‑Files allows the addition of ad hoc properties. Also, when a property is dropped from the metadata definition in M‑Files and already has values on an object, the property will retain its value.
When the Connector finds an additional property on an object that is not part of the metadata card, a column is added to the end of the class table with the column name and data type definitions as previously described.
Non‑Connector columns¶
It is possible to add columns to the class table that will be ignored by the Connector but are available for processing in the application. These columns must have a prefix of MX_ (for example MX_SAGE_Code).
Notwithstanding the ability to add additional columns to the Connector tables (following the convention above), it is recommended to create additional tables for custom applications that are cross‑referenced to the Connector tables, rather than adding columns to Connector tables.
Special columns¶
Required (NOT NULL)
If the metadata is a required property as defined in the MFClassProperty table then the column will be created with a NOT NULL constraint.
Column
The columns that will be created for the table is derived from the following:
Default column
Derived from the MFClassProperty Table.
Ad hoc columns
Non‑Connector columns
Default columns¶
The default columns are created on every class table irrespective of the mapping of metadata for the specific class. These columns must not be dropped or renamed. They are located in different sections of the table.
Default columns |
Description |
Special Application |
Updatable |
|---|---|---|---|
ID |
Identity column for Table |
Unique ID |
Identity=Yes, SQL Only |
GUID |
MF GUID for object |
Creating URL links to record; SQL cannot update this item |
From M-Files, SQL read only |
MX_User_ID |
SQL User ID |
External applications can use this column to show SQL user ID for the record |
SQL only |
Created |
MF Created date in UTC datetime format |
Automated by Connector, no need to specify when creating new record in SQL |
From M-Files, SQL Read Only |
MF_Last_Modified |
MF last modified date in UTC datetime format |
Automated by Connector, no need to specify when creating new record in SQL |
From M-Files, SQL Read Only |
MF_Last_Modified_By |
MF user name |
From MF to SQL; Automated; do not specify when updating or creating record in SQL |
From M-Files, SQL Read Only |
MF_Last_Modified_By_ID |
MF user id |
From MF to SQL; Automated; do not specify when updating or creating record in SQL |
From M-Files, SQL Read Only |
Class |
MF Class |
For information only, not required to be updated |
From M-Files |
Class_ID |
MF ClassID |
Use Class_ID when combining several class tables into one view in application; Can also be used to move a record from one class to another |
Updatable |
Workflow |
MF Workflow |
For information only, not required to be updated |
From M-Files |
Workflow_ID |
MF Workflow_ID |
Always include workflow ID when inserting or updating the state |
Updatable |
State |
MF State |
For information only, not required to be updated |
From M-Files |
State_ID |
MF State_ID |
Used to update or insert a state |
Updatable |
LastModified |
SQL last modified Local time format |
This column is automated SQL Only |
|
Process_ID |
SQL process ID |
Indicator to show status of process of record as per process table. Default value is 0 |
Flag |
ObjID |
MF Internal ID |
Leave blank when new records is created in SQL |
From M-Files, SQL Read Only |
ExternalID |
MF External ID |
MF allows objects to have external ID, when the external ID is used, it is not longer possible for a user to search for a record in Mfiles by the internal ID. The internal ID is also no longer displayed in the Metadata Card. |
Updatable |
IsSingleFile |
MF Single File |
Show status of the Single File property in M-Files. Default value is 0 (multifile) |
From M-Files, Updatable |
FileCount |
MF File Count |
Show count of files included in the object |
From M-Files; SQL Read Only |
MFVersion |
MF Version |
Automated. Also used by the Connector to identify synchronization conflicts |
From M-Files, SQL Read Only |
Deleted |
MF Deleted |
Deletion Status of record in MF |
From M-Files |
Update_ID |
SQL history log ID |
ID of history log when record was last updated |
SQL only, Read only |
The following sample statement lists all properties in the vault and the relationship to the classes.
SELECT * FROM [dbo].[MFClassProperty]
AS [mcp]INNER
JOIN [dbo].[MFClass] AS [mc]ON [mc].[ID] = [mcp].[MFClass_ID]
INNER JOIN [dbo].[MFProperty] AS [mp]ON [mp].[ID] = [mcp].[MFProperty_ID]
ORDER BY mc.mfid, mp.mfid asc
The following standard properties are included by default on every class table:
Name or title
Created
Last modified
Last modified by
Created by
Process_id values¶
ID |
Name |
Description |
|---|---|---|
1 |
Update |
Set by user to show record to be updated |
2 |
Synchronization Error |
Set by Connector to show synchronization errors |
3 |
SQLError |
Set by Connector to show record with SQL error |
4 |
MFError |
Set by Connector to show record with MF error |
By default, the Process_ID on the class table is 0.
Refer to error management for more information on handling errors.
Refer to Update ClassTable records for more information on the use of these process id’s.
Indexes on class tables¶
The use of indexes on class tables can materially improve the efficiency and processing of objects, especially in larger tables.
Indexes are applied based on how columns are used in the tables. The Connector provides flexibility regarding the use of indexes.
We recommend at least setting indexes on the objid and external_id columns. In some cases you may choose to index these columns in combination with others to further improve performance.
MFSettings includes a default setting for the creation of indexes. This setting was introduced in version 4.6.16.57. By default the automatic creation of indexes is set to 0 (do not create indexes).
When this setting is set to 1 then spMFCreateTable will automatically create indexes on objid and external_id.
SELECT * FROM dbo.MFSettings AS ms WHERE name = 'CreateUniqueClassIndexes'
UPDATE ms
SET value = '1'
FROM dbo.MFSettings AS ms
WHERE name = 'CreateUniqueClassIndexes'
Execute the procedure spMFSetUniqueIndexes to set indexes on all existing class tables, rather than recreating the tables from scratch.
EXEC spmfsetuniqueindexes
Object change history¶
The MFObjectChangeHistory table include the object change history across all classes
MFSQL allows for extracting the change history for specific objects and for specific properties of these objects.
The data is shown as a row for each version and property change. The table includes data across all classes but will only show the data that has previously been extracted using the related procedures.
The table MFObjectChangeHistoryUpdateControl is used for setting up the properties and tables to be included in the get history process.
Object Versions¶
The class tables contain the full object detail including properties of an object. The Connector also provides the capability of extracting only the object version from M‑Files. This significantly improves the efficiency of determining the latest object version and the status of an object.
The MFAuditHistory table contains the object version detail of all classes where the data has been extracted from M-Files.