Proposed FileMan SQL/ODBC Interface (SQLI)

This Document describes an initiative in progress at the San Francisco Information Resource Management Field Office of the Department of Veterans Affairs. It was written under contract to DVA by Jule Meyn of Jule Meyn Consulting, Inc. and submitted to all vendors of FileMan to SQL products and other interested parties as a Request For Comments. SQLI may represent a major milestone in the development of FileMan.

• History
• FileMan and the Relational Model
• Normality of Projected FileMan Tables
• SQL Interfaces
• Problems
• Proposed Solutions
• Goals of SQLI
• Components of SQLI
• Proposed Schedule

• M Global Files
• Example of SQLI Global Structure
• Naming Conventions
• Entity-Relationship Diagram
• SQLI Tables
• FM_SCHEMA
• FM_DATA_TYPE
• Matching Data Types to SQL
• FM_DOMAIN FM_KEY_FORMAT
• FM_OUTPUT_FORMAT
• FM_TABLE
• FM_TABLE_ELEMENT
• FM_COLUMN
• Multiples
• Pointers
• Variable Pointers
• Computed Fields
• Set-of-codes Fields
• Boolean Fields
• FM_PRIMARY_KEY
• FM_FOREIGN_KEY
• FM_KEY_WORD
• Recommendations for Vendors

Background

History

FileMan is a mature database management system (DBMS) which serves over 100 Department of Veteran Affairs (DVA) medical installations. Implemented in the MUMPS programming language (M), it has reached a very high degree of development, demonstrating excellent performance, reliability and scalability. Developed and maintained by the DVA's San Francisco Information Resources Management Field Office (SFIRMFO), FileMan is in the public domain and has more installations outside the DVA than within, in medical and non-medical applications. In the first 20 release versions, FileMan was a host based system, available on-line only to users through terminals on the host computer or cluster. Version 21 provides a DBMS server interface (DBS) which sets the stage for extending access to non-host users on local and wide area networks.

FileMan and the Relational Model

In the relational model:

1. A database is a set of named schemas.
2. A schema is a set of named relations (tables, or files)
3. A relation consists of a set of attributes and a set of tuples (records or rows).
4. An attribute (field or column) consists of an attribute name and a domain (data type).
5. A domain is a defined set of values
6. A tuple is a set of values such that for each attribute there exists a value which is a member of the set defined by the domain of that attribute.
7. Each attribute is atomic (scalar), that is, repeating fields are not permitted.
8. Schemas names are unique by database, relation names by schema, and attribute names by relation.

Any database that conforms to constraints I through VIII is a relational database. Because every element is a set, tuples are unordered and unique within relations, and attributes are unordered and unique within tuples. Because tuples are unique there must be at least one set of attributes in each tuple which is unique within the relation. One such set is chosen to be the primary key of the relation.

From an operational point of view, FileMan is hierarchic, not relational. It supports a data type, "Multiple", which permits the nesting of tables within tables recursively. Its paradigm is the single argument version of the M $ORDER command, where the path to any data item is top-down and left-to-right in a directed graph. But a point by point comparison of the contents of FileMan data dictionaries ^DIC and ^DD with the constraints enumerated above tells a different story:

1. Assume each FileMan database to be a named schema.
2. ^DD defines a set of named files (relations), each with a set of fields
3. Each file represents a set of fields (attributes) and a global containing field values (tuples)
4. Each field is uniquely named and has a specific data type
5. Each data type represents a closed set of possible field values (domain)
6. The represented global contains one value for each field for each set of keys
7. Repeating values are not supported
8. The schema is named, field names are unique by file. File names are not unique across all files in ^DD (file numbers are)

If fields with multiple data type are ignored, with the sole exception of constraint VIII, FileMan data dictionaries define a fully relational database.

The analysis above is amply confirmed by successful efforts to manage FileMan databases with the SQL language, which was designed specifically to implement the relational model. Before using SQL, FileMan files must be projected as relational tables.

Normality of Projected FileMan Tables

Because FileMan doesn't support repeating fields, all projected tables will be at least truly relational, or first normal form (1NF). Because DVA programming standards require that applications use the pointer data type to eliminate redundancy, functional dependencies among non-key columns, if any, are rare; this implies third normal form (3NF). Almost all primary key elements are system generated integers, guaranteeing zero functional dependency among key columns and irreducibility of the primary key. Therefore, most DVA tables will be projected in Boyce-Codd (BCNF), fifth (5NF) or higher Normal Form.

SQL Interfaces

Even before Version 21 was released, several commercial vendors had implemented SQL interfaces to the FileMan database, some of them with well developed TCP/IP interfaces to Microsoft's Open DataBase Connectivity (ODBC) tool. Access from Microsoft SQL Server products (Access, Excel, etc.), Lotus 123, Paradox and others has been shown. Oracle Corporation has used its Transparent Gateway product to provide ad hoc query capability joining FileMan databases with DB2 and ODBC compliant ones across Wide Area Networks.

All current implementations of M support the SQL language and ODBC, either directly or using third party applications.

Problems

Each vendor maps FileMan into its own SQL data dictionary directly from M files which are under the control of SFIRMFO. FileMan data dictionary files are reasonably stable, but they are subject to extension and modification by the DVA at any time causing vendor software to execute from out-dated mapping.

Some features of the DBMS are hidden from the vendors, among them, column level security screening. Inability to support such features may impede implementation of vendor systems for security sensitive FileMan applications.

Each vendor has it's own methodology for generating SQL identifiers for FileMan objects. Inconsistent naming makes it hard to build SQL applications that are portable across FileMan sites using different vendors.

FileMan is, and has always been, a developing DBMS. The development team at SFIRMFO needs to insulate vendors from the details of data-dictionary structure so they can continue freely to enhance their product.

Proposed Solutions

In March 1995, at the direction of the Office of the CIO of the Department of Veterans Affairs, a goal was established for Software Services to provide ODBC/SQL access to the Distributed Hospital Computer Program (DHCP) database. This feature will benefit sites intending to emply commercial relational database tools for ad hoc queries and, through ODBC, to provide a seamless interface between Commercial Off-The-Shelf Software (COTS) and DHCP.

As a first step toward this goal the SFIRMFO team will implement an SQL Interface (SQLI) which projects in simple form all the information required to define FileMan in purely relational terms. SQLI will relieve vendors of the necessity of "hacking" the M globals to determine certain parameters that are not explicit in FileMan and will isolate vendor code from proposed changes in the FileMan data dictionary.

SQLI will publish DVA Standard SQL identifiers for each table and column, project FileMan data types as SQL data types, functions and domains, and provide an "impure" semaphore to trigger vendor remapping when an object in the data dictionary changes. SQLI will be implemented as an integral part of DHCP.

The target date for completion of SQLI is March 15, 1997. It will be available for delivery to all Veterans Administration sites in April, 1997.

Goals of SQLI

• Project FileMan application files as a DVA standard set of SQL tables
• Standardize SQL identifiers across all DVA sites
• Provide unambiguous column data types and other parameters
• Provide a DVA standard for projection of non-relational data types to SQL
• Define DVA business rules for remote data retrieval and update.
• Hide outdated or otherwise worthless structures
• Provide update triggers to enable dynamic remapping

Components of SQLI

• A set of routines which generate and maintain the SQLI database
• The SQLI database containing all data necessary to map DHCP to SQL
• A set of routines, referenced in the SQLI database, containing implementation strategies for FileMan domains, formats and functions.
• Complete SQLI system and user documentation

Proposed Schedule

Table 1. SQLI Milestones

The remainder of this document is a proposed specification for SQLI. It is written in the present tense because, when the RFC period has expired, the review process is complete, and the product delivered, conformance to this document will be, in part, the definition of completion.

Proposed SQLI Specifications

M Global Files

SQLI is implemented as a set of FileMan files within a single M global with no multiples (sub-files) except word processing fields. Because no functional dependencies exist in any file except on a simple primary key, a mapping of SQLI to SQL will be fully relational, in 5th normal form (5NF). Hereafter, the files of SQLI will be called tables, and the fields, columns. The words file and field are understood to refer to FileMan data dictionary globals.

The organization of SQLI tables mirrors SQL2 standard Data Definition Language syntax. Additional syntax has been added to support the definition of M global structures, virtual columns, key and output formats and other objects outside the scope of the SQL standard. This is reflected in the table names (actual names are prefixed by FM_ to avoid keyword conflicts in mapping applications). See Entity-Relationship Diagram (1.3).

Example of SQLI Global Structure

The M global structure of FM_TABLE_ELEMENT (1.4.7) will look like:

^DMSQ("E",E_ID,0)=E_NAME^E_TABLE^E_TYPE^E_DESC
              ,"B",E_NAME,E_ID)=""
              ,"C",E_TABLE,E_ID)=""
              ,"CA",E_TABLE,E_TYPE,E_ID)=""

Column E_TABLE in this structure is a FileMan pointer data type which is a foreign key to a row in the FM_TABLE table. E_TYPE is a set-of-codes data type; codes are C for column, P for primary key and F for foreign key. Primary keys are named in this structure, although they aren't in the SQL standard. FM_TABLE_ELEMENT is indexed by E_NAME, E_TABLE and (E_TABLE, E_TYPE).

Naming Conventions

• All SQLI routine names start with DMSQ.
• ^DIC and ^DD file numbers for SQLI are in the range 1.521 through 1.52199.
• All SQLI tables are stored in M global root ^DMSQ(
• Each table is stored under a one or two character mnemonic subscript.
  e.g.: ^DMSQ("S") contains the schema table, and ^DMSQ("DT") contains data types

Entity-Relationship Diagram

This diagram shows the functional relationships between SQLI tables .

Figure 1. Entity-Relationship Diagram

Each link represents a -equijoinii between a column and a primary key. One-to-many relationships are indicated by M, one-to-one by 1. Arrows indicate the direction one many.

The relationship between FM_DATA_TYPE and FM_OUTPUT_FORMAT may be overridden in either FM_DOMAIN or FM_COLUMN.

FM_FOREIGN_KEY is one-to-many with FM_COLUMN, one-to-one with FM_PRIMARY_KEY and FM_TABLE.

SQLI Tables

Column names followed by a asterisk ("*") are required to be NOT NULL. Each table is subscripted by a single integer column called in FileMan an Internal Entry Number, or IEN. In SQL terms, each IEN in the following table is either a primary key, or a foreign key to a different table.

M code in the table uses the following symbols as place-holders:

Table 2. Place-holder Symbols in M Code

Table 3. Directory to Tables

FM_SCHEMA

SQLI is conceptually organized by application, or "package". Each application group will have its own schema, for instance, SQLI itself is identified in schema DATA_DICTIONARY.

Table 4. FM_SCHEMA Columns

FM_SCHEMA is indexed by S_NAME, which is unique by DVA site.

FM_DATA_TYPE

Matching Data Types to SQL

FileMan data types map easily to SQL although date, time and date-time have unique internal representations, and pointers, when directly referenced have the column attributes of the referenced identifier. SQLI provides a set of special domains which provide conversion of FileMan internal constructs to recognized SQL (and standard M) data types. Specifically, the three date-time valued domains will cast FileMan internal values to M $HOROLOG base values.

Table 5. FileMan Data Type Projection

Table FM_DATA_TYPE is a simple list of SQL standard data types represented by the custom FileMan domains in FM_DOMAIN..

Table 6. FM_DATA_TYPE Columns

FM_DATA_TYPE is indexed by D_NAME.

FM_DOMAIN

Table 7. FM_DOMAIN Columns

The M expressions and executable code fragments use the symbols {I} and {B} to represent internal and base values respectively. Vendors may use these fragments in code generators by replacing the symbols with vendor specific variable names. Code fragments will use standard FileMan utility routines and will preserve all variables except the one representing the output symbol.

FM_DOMAIN is indexed by DM_NAME and DM_TABLE.

FM_KEY_FORMAT

Key formats are used for index table primary keys to specify conversion of the storage values of a column to an internal value when the two differ. They must be used for "lossy" conversions such as Soundex and lower-to-upper case conversions. In FileMan, subscript length is limited to 30 characters, so a standard key format, LONG_CHARACTER, will be supplied and installed for all index primary keys with maximum length greater than 30 characters.

Table 8. FM_KEY_FORMAT Columns

FM_KEY_FORMAT is indexed by KF_NAME and KF_DATA_TYPE.

FM_OUTPUT_FORMAT

Output formats are used to specify the default output format for data types, domains and columns. Domain output format overrides data type, and column output format overrides domain.

Table 9. FM_OUTPUT_FORMAT Columns

FM_OUTPUT_FORMAT is indexed by OF_NAME and OF_DATA_TYPE.

FM_TABLE

FileMan files, cross-reference structures and multiples (sub-files), including word processing fields, are projected into SQLI tables. Only FileMan default (regular) index types are projected; MUMPS, trigger, key-word and other types are not.

Table 10. FM_TABLE Columns

FM_TABLE is indexed by T_NAME, T_SCHEMA, T_MASTER_TABLE and (T_SCHEMA, T_NAME). T_NAME is unique by T_SCHEMA.

FM_TABLE_ELEMENT

In SQL Data Definition Language (DDL) a table is defined by CREATE TABLE <table-name> (table-element-commalist). Table elements include columns, foreign keys and primary keys. Columns and foreign keys are named objects whose names must be unique by table.

SQLI names both primary and foreign keys and gives them domains. Domains of column type elements have the data types shown in Table 6., but domains of primary and foreign keys have a special data type, PRIMARY_KEY. Every foreign key to a given table has the same domain as the primary key of that table. While not supported by SQL, this convention makes entity relationships more explicit and should help vendors maintain referential integrity constraints during mapping.

FM_TABLE_ELEMENT contains the two essential elements of an attribute in the relational model: attribute-name (E_NAME) and domain (E_DOMAIN). Elements not defined in the relational model, but necessary for physical mapping and formatting are contained in FM_COLUMN (1.4.8), FM_PRIMARY_KEY (1.4.9) and FM_FOREIGN_KEY (1.4.10).

Table 11. FM_TABLE_ELEMENT Columns

FM_TABLE_ELEMENT is indexed by E_NAME, FM_DOMAIN, E_TABLE, E_TYPE and (E_TABLE, E_TYPE).

FM_COLUMN

Multiples

The projection of FileMan fields into columns is non-trivial, to say the least. FileMan supports the concept of fields representing multiples, or sub-files, while the SQL standard recognizes only scalar, or atomic, columns. In SQLI, all fields of multiple data type are decomposed into tables. FileMan's word processing multiple is also projected as a column for vendors who support a HUGE_CHARACTER or MEMO data type.

Pointers

The pointer data type conforms to SQL's foreign key constraint, and is projected as such in SQLI. FileMan, however, allows direct reference to a pointer field, returning the text value of the primary identifier of the row reached by recursively following the pointer chain until the identifier is not itself a pointer. This usage is projected in SQLI by giving pointers a numeric or integer domain and an output format which uses DBS to return the value.

Variable Pointers

The variable pointer data type is not relationally atomic, the only true violation of the relational model in FileMan. In SQLI variable pointer fields are normalized by decomposition into one simple pointer column set (1.4.8.2) for each possible prefix value. They have a character domain and an output format to return the FileMan display value independent of source.

Computed Fields

Projection of computed fields is complicated mildly by the fact that SQL DDL syntax supports only base data, while Data Manipulation Language supports expressions. For vendors who support virtual columns in their data dictionaries, FileMan computed fields are projected as columns flagged as such, permitting the vendor to retrieve their values using DBS.

Set-of-codes Fields

An output format is provided for each set-of-codes value to display the long form.

Boolean Fields

Boolean fields will be supported by domain logic which returns a value of 0 for false or 1 for true.

Table 12. FM_COLUMN Columns

FM_COLUMN is indexed by C_TABLE_ELEMENT, C_PARENT and C_TABLE, and by C_FILE by C_FIELD.

FM_PRIMARY_KEY

Primary key definitions are in a separate table because all FileMan multiples are projected as tables with more than one primary key element. Although primary keys are not ordered in SQL, FileMan keys and M globals in general are; thus the sequence number.

Primary keys represent the subscripts of FileMan files, which aren't, in general, reflected in the data dictionary. By default, they are system generated integers, but with design effort they may have any data type. In SQLI, every subscript is represented by a unique column whose IEN is contained in P_CLM_ELEMENT below.

Table 13. FM_PRIMARY_KEY Columns

FM_PRIMARY_KEY is indexed by P_TBL_ELEMENT and (P_TBL_ELEMENT, P_SEQUENCE).

FM_FOREIGN_KEY

SQLI projects one or more foreign keys for every instance of a pointer or variable pointer data type, and one for each ancestor table. Most contain only a single column, but tables with grandfather or older tables contain multiple key columns.

FM_FOREIGN_KEY matches columns in the referencing table to primary key elements in the referenced table. If required, the foreign key sequence number can be obtained from the primary key element row through F_PK_ELEMENT.

The referenced table is obtained from the domain of F_TBL_ELEMENT.

Table 14. FM_FOREIGN_KEY Columns

FM_FOREIGN_KEY is indexed by F_TBL_ELEMENT.

FM_KEY_WORD

Table 15. FM_KEY_WORD columns

FM_KEY_WORD is indexed by KW_KEY_WORD.

Recommendations for Vendors

Use Object Names Published by SQLI

Consistent naming of columns and tables is a major goal of the project. Among the benefits is portability of queries across DVA sites, regardless of DVA vendor.

Use SQLI to Optimize Queries

FileMan DBS provides complete value retrieval and update services for vendors, and is the safest way to reference the database but the nature of the relational model requires a more general approach to parsing and optimization. Therefor, vendors are encouraged to parse the files and extract internal values with optimized M code generated using the structural and statistical data in SQLI. Exceptions where the vendor should use DBS are noted in sections 1.5.5, 1.5.6 and 1.5.7 below.

Use SQLI for DVA Specific Data Formatting

SQLI publishes M strategies to present data in DVA default formats. Vendors are encouraged to use these as their own default formats. In particular, set-of-code and pointer data types are supported by output formats projected in SQLI.

Use SQLI to Synchronize Your Data Dictionary

Column T_VERSION_FM is incremented to a new version number whenever the SQLI projection of the table changes. If vendors keep track of the version number last mapped, the vendor's data-dictionary can easily be synchronized with SQLI whenever the table is to be referenced by SQL.

Use FileMan DBS to Project Computed Fields

Although computed fields can be implemented by executing the internal M code of the data dictionary, SFIRMFO cannot guarantee preservation of variables or, indeed, the integrity of the executable code. Only DBS is safe for these calculations.

Use FileMan DBS to Retrieve Security-flagged Fields

Sites are responsible for the security of their data. Applications which violate that security must be removed from the system.

Use FileMan DBS for Insert, Update and Delete

DVA business rules are far too complex to emulate. Using DBS is the only approved way to modify the databases.

Deficiencies

FileMan Constructs Not Projected by SQLI

• Triggers, key-word indices and MUMPS type indices maintained by hand written M code are absent from SQLI. Only the default indices created automatically for primary identifiers and regular cross-references created in FileMan's DI UTILITY - CROSS REFERENCE utility are supported.
• Certain tables used by FileMan internally are not projected.
• It is possible that a FileMan field may contain a pointer to a file not actually present at a given site. If so, that field will not be projected in SQLI, preserving referential integrity.