Converting FileMan Files into InterSystems IRIS Classes
Note:
This feature is available and fully supported only for existing users. InterSystems recommends against starting new projects with FileMan.
InterSystems IRIS® data platform provides a utility to convert applications based on FileMan files into InterSystems IRIS classes, thus providing object and SQL access to the data. Specifically, this utility (the FileMan Mapping Utility) generates InterSystems IRIS class definitions that map the data. This page assumes that the FileMan globals (^DD and ^DIC) are already loaded into the desired namespace in your system.
Overview of the FileMan Mapping Utility
FileMan enables you to quickly and easily create custom class mappings for your FileMan files. The classes created by the mapping utility enable you to access the file data via object access and via SQL. You can map just one, many, or all FileMan files in a namespace. For each file, you can control details such as the following:
-
Which fields are mapped
-
Whether the classes are read-only or have write access
-
Formats for the names of the generated classes and properties (tables and fields)
-
The superclass list for the generated classes
-
Whether word-processing fields are mapped as list collections or as child tables
System Requirements
You need a working FileMan installation.
Specifying and Modifying the Default Settings
FileMan uses many settings that control how it generates classes. It is worthwhile to review these settings, modify them to values that you use most of the time, and save these as the default settings.
To specify or modify the default settings, use a command like the following:
Set ^%SYS("sql","fm2class",setting) = value
Where setting is an internal setting name as shown in the previous list, and value is the value to assign to it.
When you use the methods in $SYSTEM.OBJ.FM2Class, you can pass in an array of the following format:
arrayName(setting) = value
For example:
set fmsettings("display")=1
set fmsettings("package")="VISTA"
set fmsettings("superClasses")="%XML.Adaptor"
set fmsettings("wpIsList")= 1
The available settings are as follows:
childTableNameFormat
Specifies the format of the generated child table names. For this setting, specify a string that uses the following keywords along with any characters that are valid to use as table names:
-
<FILENAME> — Replaced with the name of the FileMan file
-
<FILENUMBER> — Replaced with the file number
-
<PARFILENAME> — Replaced with the name of the parent file
-
<PARFILENUMBER> — Replaced with the file number of the parent file
If you use <FILENAME> or <PARFILENUMBER>, any decimal place characters in the file number are converted to underscore characters.
Some examples of this setting:
-
SUB_<FILENAME> — In this example, the table name of a child table is the string SUB_followed by the name of the file. For example: SUB_ACCESSIBLE_FILE
-
f<PARFILENUMBER>c<FILENUMBER> — In this example, the table name of a child table is the string f, followed by the number of the parent file, followed by c, followed by the number of this file. For example: f200c200_032
-
<FILENAME> — In this example, the child table name is simply the same as the filename.
ClassParameters
Specifies class parameters and values for an OS object, in JSON format.
compile
Specifies whether to compile the classes after creating them. Permitted values are as follows:
compileQSpec
Specifies any class compiler qualifiers and/or flags.
defineVASITE
Creates a new field VA_SITE in the table, to store the Station ID of the VA site. This field can be useful for telling the source of the data when joining data from multiple VA or FileMan servers. Permitted values are as follows:
deleteQSpec
Specifies any class deletion qualifiers and/or flags.
display
Controls how the results are displayed. Permitted values are as follows:
expandPointers
Specifies whether the utility creates additional computed properties to expand the pointer field. Permitted values are as follows:
-
0 — The utility does not add any computed properties to expand the pointer field.
-
1 — The utility adds a computed property that expands the pointer field and is equal to the NAME (.01) field in the referenced file.
-
2 — The utility adds a computed property that expands the pointer field and is equal to the NAME (.01) field in the referenced file. The naming conventions used for this option is the pointer field will be named after the referenced file with “_ID” appended to the field name.
-
3 – This option is the same as 2 except that the name of the reference field will be <Field>_f<pointed-to-file-id#>ID.
expandSetOfCodes
Specifies whether to define an expanded SOC (SetOfCodes) table for each Set Of Codes field that is mapped. Permitted values are as follows:
-
1 — For each Set of Codes field that is mapped to a class, the utility will generate a read-only class/table that maps to the CODE and MEANING of the Set Of Codes. The name of the table is <tablename>_SOC_<fieldname>. The SOC table has two fields. CODE maps to the name of the CODE and MEANING is the external meaning for the CODE. This table is mapped directly to the ^DD global, so any updates to the definition in the ^DD global will be immediately reflected in the data returned by the table.
-
2 — These SOC class/tables will not be created. This is the default.
extendedMapping
Specifies a string to be inserted into the global name in the map definitions for extended mapping purposes. For example, you might specify ["SD"] and your global is mapped as ^["SD"]LR(...) instead of ^LR(...).
This can be any valid string that can be used for extended global mapping and must include the [...] or |...| brackets.
fieldNameFormat
Specifies how the utility will generate the SQL field name. Permitted values are as follows:
-
Exact — The SQL field name will use exactly the same case as the FileMan field name. For example, FileMan field 'DEA EXPIRATION DATE' becomes SQL field name 'DEA_EXPIRATION_DATE'.
-
Upper — Letters in the FileMan field name are folded to upper case to generate the SQL field name. For example, FileMan field 'DEA expiration date' becomes SQL field name 'DEA_EXPIRATION_DATE'.
-
Lower — Letters in the FileMan field name are folded to lower case to generate the SQL field name. For example, FileMan field 'DEA EXPIRATION DATE' becomes SQL field name 'dea_expiration_date'.
-
Pascal — The first letter in the identifier and the first letter of each subsequent concatenated word are capitalized. Also, spaces and underscored are removed. For example, FileMan field 'DEA EXPIRATION DATE' becomes the SQL field name 'DeaExpirationDate'.
-
Camel — Thee first letter of an identifier is lowercase and the first letter of each subsequent concatenated word is capitalized. Also, spaces and underscored are removed. For example, FileMan field 'DEA EXPIRATION DATE' becomes the SQL field name 'deaExpirationDate'.
ienFieldName
Specifies the name of the IEN field, which is IEN by default.
JSONAdaptor
Enables JSON Adapter functionality for use with mapped classes. The value of JSONAdaptor is a JSON-format string of class parameters and values. Permitted parameters are as follows:
For details about JSONAdaptor parameters, see %JSON.AdaptorOpens in a new tab in the class reference.
ComputedAlwaysJSONINCLUDE allows specification of the %JSONINCLUDE property parameter for properties defined as Calculated and SqlComputed.
For best results, the following settings are recommended:
logFile
Specifies the name of the file into which the utility should log output. Type a filename.
nameLength
Specifies the maximum length of property names, foreign key names, and trigger names produced by this utility. The default is 180, which corresponds to an increase in the length of InterSystems IRIS class member names in a recent release. Use this option if you want to keep the names of these items at the previous shorter maximum (31 characters).
owner
Specifies the username to use as the owner of the classes created. The default is the current value in $Username.
package
Specifies the name of the package to create the classes in.
readonly
Specifies whether the generated classes should be read-only. Permitted values are as follows:
recursion
Controls whether sub-files and pointer are also mapped. Permitted values are as follows:
-
0 — No recursion. Only this file is mapped. No sub-files or pointers are mapped.
-
1 — Partial recursion. The file is mapped, along with one level of sub-files and pointers.
-
2 — Full recursion. The file is mapped, along with all sub-files and pointers. This is the default.
requiredType
Controls which required FileMan fields are defined as required properties. Permitted values are as follows:
retainClass
Specifies whether to recreate the entire class if it already exists. Permitted values are as follows:
-
0 — The utility deletes and recreates the class, which means that SQL privileges and any add-ons to the class are lost.
-
1 — The utility recreates the properties, storage, indexes, foreign keys, and so on, rather than the entire class. Notes:
Note:
-
You must have run the FM2Class utility at least once with the InterSystems IRIS 2010.2 or higher before manually adding your own items to the class definition; this is necessary because earlier versions do not save the required metadata needed for this feature.
-
After mapping a class, if you move the class to another namespace or another system, and attempt to map it again from the new location with the RetainClass set to 1, you must also manually move/copy the ^oddFMD global too, because this global stores the metadata required by the RetainClass setting.
-
The RetainClass setting is not meant to work with SOC classes produced when you set expandSetOfCodes to 1; that is, if you add your own parameters, properties, indexes, and so on to these classes, those changes are not retained, even if RetainClass is set to 1.
strictData
Specifies whether the generated classes include STRICTDATA=1 in the definitions of any properties of type %Library.FilemanDateOpens in a new tab and %Library.FilemanTimeStampOpens in a new tab.
This STRICTDATA parameter affects the LogicalToOdbc() and LogicalToDisplay() methods for these data types. When STRICTDATA=0, the default, the methods will contain the same code they did previously. When STRICTDATA=1, the code in the LogicalToFormat() methods that handles invalid Logical date and time values is removed. Use STRICTDATA=1 if your database contains invalid or incomplete date or time values, and you do not want assumptions made about what the correct data should be.
superClasses
Specifies the superclass list for each of the mapped classes. Specify a string with a comma-separated list of class names.
tableNameFormat
Specifies the format of the generated table name. For this setting, specify a string that uses the following keywords along with any characters that are valid to use as table names:
If you use <FILENAME>, any decimal place characters in the file number are converted to underscore characters.
See the examples for childTableNameFormat.
variablePointerValueField
Specifies how to handle variable pointer fields. Permitted values are as follows:
-
1 — For each variable pointer field mapped, FM2Class will also create a computed property that expands the variable pointer field value and is equal to the .01 field in the referenced file. This field will be named <Variable_Pointer_Field_VALUE>.
For example, an FM variable pointer field WHO is defined to point to either the EMPLOYEE file or the PATIENT file. By default, FM2Class creates two fields, one that points to the EMPLOYEE file and another one that points to the PATIENT file. Only one of these fields will be non-NULL. If WHO is an EMPLOYEE, the WHO_EMPLOYEE field will contain the ID/IEN of the EMPLOYEE. If WHO is a PATIENT, WHO_PATIENT will contain the ID/IEN of the PATIENT.
FM2Class also creates a third field, WHO_VALUE, which computes to the .01 field of the EMPLOYEE record if the WHO points to an EMPLOYEE, or the .01 field of the PATIENT record if WHO points to a PATIENT.
-
0 — FM2Class does not create this third field.
wpIsList
Specifies how to map word-processing fields. Permitted values are as follows:
-
0 — Convert as child tables.
-
1 — Convert as list collections.
-
2 — Convert as both list collections and child tables.
-
3 — Convert as a single long string field.
Mapping FileMan Files
Use methods in %SYSTEM.OBJ.FM2ClassOpens in a new tab, as follows:
The arguments are as follows:
-
fmSettings is an optional array passed by reference with any settings you would like the utility to use, as described earlier in this section.
-
classCount is optional and is also passed by reference. It returns the number of classes created by the mapper utility.
-
fileNumber is the FileMan file number of the file you want to map.
-
fmFields, if defined, limits the fields in the file that are mapped. This is an array of the form fmFields(file-number,field-number). Any required fields and fields defined in this array are mapped in the class definition. If this array is empty or not defined, all fields in the file are mapped. This array is passed by reference.
-
fileList is the FileMan file numbers of the files you want to map. Specify a comma-delimited list of file number or ranges of file numbers. Or specify an array of file numbers passed by reference.
The class %SYSTEM.OBJ.FM2ClassOpens in a new tab also provides the methods Version() and GetVersion().
Next Steps
After using the conversion utility, you should verify that you have SQL access to the generated classes. To do so, use Management Portal. At a minimum, check that you can do the following:
It may also be necessary to create global and routine mappings in the namespace. Check the routines used in your generated classes to be sure that all of them are available in this namespace.
Comparison of a FileMan File to an InterSystems IRIS Class Definition
To orient yourself, you may find it helpful to compare a simple FileMan file to the resulting InterSystems IRIS class definitions.
Consider the simple example of a file called PostalCode. If we look at the file attributes for this file (via the FileMan Data Dictionary Utility), we see the cross-references and indexes for this file. For example:
The same utility also shows us the definitions of the fields in this file. In this case, the titles of the fields are as follows (when listed alphabetically)
-
City
-
City Abbrevation
-
City Key
-
County
-
Inactive Date
-
Mail Code
-
Preferred City Key
-
State
-
Unique Key (VA)
The FileMan Data Dictionary Utility also shows that the PostalCode file includes two pointers:
If we map this file, five classes are created. This includes a class to represent PostalCode, as well as classes to which this class refers. In Studio, we would see the following, if we had specified TEST as the package name:
The class definition for TEST.POSTALCODE is too long to show, but the details are summarized here:
-
The DATECREATED class parameter is set equal to the time when the class was created or updated.
-
The FILEMANFILENAME parameter is set equal to "POSTAL CODE" and the FILEMANFILENUMBER parameter is set equal to 5.12. These two parameters indicate the FileMan file from which this class was mapped.
-
The class includes the following properties:
-
CITY
-
CITYABBREVIATION
-
CITYKEY
-
COUNTY
-
IEN
-
INACTIVEDATE
-
MAILCODE
-
PREFERREDCITYKEY
-
STATE
-
UNIQUEKEYVA
The definitions of these properties are derived from the definitions of the corresponding fields. Also note that the FileMan Internal Entry Number is represented explicitly as a property.
Descriptive text is carried over and is used as comments. For example:
/// FileMan Field Label: 'STATE' FileMan Field Number: '3'
/// This field contains a pointer to the State File to represent the state
/// associated with this Postal Code.
Property STATE As TEST.STATE [ SqlColumnNumber = 6, SqlFieldName = STATE ];
Several of these properties (like this one) are references to the other generated classes. This representation addresses the cross-references defined in the PostalCode file.
-
This utility creates foreign key constraints for each pointer field created. The foreign key constraint simply references the ID of the referenced table.
In this example, the class includes the following foreign keys to represent the pointer references in the PostalCode file:
-
FKeyCOUNTY(COUNTY), which references TEST.COUNTYCODE()
-
FKeySTATE(STATE), which references TEST.STATE()
Also see the section “Mapping of Variable Pointer Fields,” later in this section.
-
The class includes one index:
Index IDKeyIndex On IEN
-
Finally, the class includes three triggers that act when filing is performed through the FileMan filer:
-
BeforeDeleteFiling
-
BeforeInsertFiling
-
BeforeUpdateFiling
Mapping Details
This section contains notes about how the methods in $SYSTEM.OBJ.FM2Class generate InterSystems IRIS class definitions.
Mapping of FileMan Datatype Fields
The FileMan mapping utility supports conversion of certain field types, as follows:
-
BOOLEAN
-
LABEL REFERENCE
-
TIME
-
YEAR
-
UNIVERSAL TIME
-
FT POINTER
-
FT DATE
-
RATIO
Supporting InterSystems IRIS Datatype Classes
The following InterSystems IRIS datatype classes exist to enable support of certain field types:
Note:
The conversion methods of %Library.FilemanTimeStampUTCOpens in a new tab assume a full FileMan 22.2 run-time environment is installed, and that the DUZ(2) variable is defined and references ro INSTITUTION that includes defined COUNTRY and LOCATION TIMEZONE values.
The main purpose of these datatype classes is to provide internal (logical) and external (display/ODBC) values for the type. They are not set up with the SQL engine to be compatible with other date/time/timestamp types. For example, %Library.FilemanTimeOpens in a new tab will not compare properly with a %TimeOpens in a new tab type in a query, because the logical values of the two types are different. The logical value for these three types are strings, and they use string collation t force the compared value to be a string.
If you want to do something like compare a %Library.FilemanTimeOpens in a new tab field with the current time, you must first convers the FileMan time field to a %TimeOpens in a new tab format so the comparison is valid. An example of how to do this is:
SELECT ... FROM ... WHERE CAST(%external(FM_TIME_FIELD) AS TIME) > CURRENT_TIME
Supported FileMan Field Types
BOOLEAN Fields
A field defined as a BOOLEAN data type can have only two entry choices:
| Internal |
External |
| 1 |
YES |
| 0 |
NO |
LABEL REFERENCE Fields
A field defined as a LABEL REFERENCE data type is designed to store a tag and routine entry of the format, <TAG>^<ROUTINE>. It is stored as a free-text field.
LABEL REFERENCE fields map to InterSystems IRIS as %Library.StringOpens in a new tab.
TIME Fields
A field defined as a TIME data type can accept many of the date/time entries, but only stores the time portion. For example:
-
Internal — 150943
-
External — 15:09:43
TIME fields map to InterSystems IRIS as %Library.FilemanTimeOpens in a new tab.
YEAR Fields
A field defined as a YEAR data type can accept many of the date entries, but only stores the year portion. For example:
-
Internal — 3160000
-
External — 2016
YEAR fields map to InterSystems IRIS as %Library.FilemanYearOpens in a new tab.
UNIVERSAL TIME Fields
A field defined as a UNIVERSAL TIME data type can accept many of the date/time entries. It stores the data/time in a format with the local time, including an indicator showing the offset from Universal Time. The first 14 characters of the internal storage of the UNIVERSAL TIME data type are exactly like the current DATE/TIME data type that includes seconds. The three characters in positions 15, 16, and 17 indicate the UTC time offset in 5–minute increments. For example:
In this example, (440–500)/12=-5. This is a negative five hour offset from UTC.
UNIVERSAL TIME fields map to InterSystems IRIS as %Libary.FilemanTimestampUTC.
FT POINTER Fields
A field defined as a FT POINTER data type works like the POINTER data type, but internally stores the free text that was returned from the pointed-to value. For example:
-
Internal — PATCH,USER
-
External — PATCH,USER
FT POINTER fields map to InterSystems IRIS as %Library.StringOpens in a new tab.
FT DATE Fields
A field defined as a FT DATE data type works like the DATE/TIME data type, but internally stores the free text that was input by the user to determine the date. For example:
-
Internal — T-1
-
External — T-1
FT DATE fields map to InterSystems IRIS as %Library.StringOpens in a new tab.
RATIO Fields
A field defined as a RATIO data type is designed to accept two numbers with a colon character (:) between the two numbers. It is formatted and stored like a mathematical ration. For example:
-
Internal — 1:14
-
External — 1:14
RATIO fields map to InterSystems IRIS as %Library.StringOpens in a new tab.
Mapping of Variable Pointer Fields
Each variable pointer field in the file that is mapped to a InterSystems IRIS class defines a property in the class that is marked with the SqlComputed keyword. For each file to which this variable pointer field refers, an additional property is created in the class, and this property is marked with the SqlComputed and Calculated keywords. If VariablePointerFieldName is the name of the variable pointer field, and PointerFileName is the name of the file to which it points, the added property is VariablePointerFieldNamePointerFileName; the SQL field name for this property is VariablePointerFieldName_PointerFileName.
For example, suppose the file ABC includes a variable pointer field called VP. The VP field can point to the Red, White, or Blue files. This creates the following properties in the class definition:
| Property |
SqlFieldName |
Details |
| VP |
|
SQL computed upon INSERT and UPDATE. Stored in the map definition. Contains the internal value for the variable pointer field. Usually something like "41;DIC(40.7,", which means it points to row 41 in the 40.7 file. |
| VPRed |
VP_Red |
A computed field that is null unless the VP field for this row points to the Red file. To update the value of VP via SQL INSERT or UPDATE to point to a row in the Red table/file, set the value of VP_Red to the ID of that row. |
| VPWhite |
VP_White |
A computed field that is null unless the VP field for this row points to the White file. To update the value of VP via SQL INSERT or UPDATE to point to a row in the White table/file, set the value of VP_White to the ID of that row. |
| VPBlue |
VP_Blue |
A computed field that is null that is NULL unless the VP field for this row points to the Blue file. To update the value of VP via SQL INSERT or UPDATE to point to a row in the Blue table/file, set the value of VP_Blue to the ID of that row. |
In all cases, the VP field is computed and stored automatically.
In addition to defining the three reference fields VP_Red, VP_White, and VP_Blue, the utility also creates three foreign key constraints in the ABC class for these references. This is done to ensure referential integrity is maintained between the files.
Mapping of New-Style Cross-References
This utility converts new-style cross-references to index maps if the cross-reference is defined by simple set/kill logic. This allows the InterSystems IRIS query optimizer to choose a new-style cross-reference index (if one exists) and increase query performance in some cases.
Mapping of Cross-References That Use DUZ(9)
FM2Class maps a cross-reference (original or new style) that was previously not mapped because the cross-reference subscripts included a reference to the variable DUZ(9) or the subscript had the form +$G(varname), where varname represents any variable reference.
If you have such cross-reference definitions, make sure that the DUZ(9) variable, or possibly any variable referenced by +$G(varname) is defined in any process running SQL queries against tables with indexes mapped from these cross-references. To set DUZ(9) for a process connecting via xDBC, use $SYSTEM.SQL.SetServerInitCode().
