DF_FILE_IS_SYSTEM_FILE

Level

Table

Supported by

All Drivers

Type

Boolean, permanent

Access

Read / Write

Values

True
False

Remarks

A system table is a table with a single record, usually used to store global counters. When a system table is opened, the first and only record is automatically loaded into the file buffer.

Database drivers are not required to enforce that system tables have one record and one record only. If a program creates a record in a system table that already contains a record, the driver will not generate an error. At this moment, no database driver, including the driver for the Embedded Database, enforces this rule.

System tables are typically used to record global information that is static for an application’s installation. Information like company name, installed modules, and configuration settings is often stored in a system table. The DataDictionary class supports a mechanism to create auto-increment columns by using a system table to store the last assigned number via the Define_Auto_Increment command.

Using a system table to assign auto-increment values in a record locking environment can restrict concurrency. The system table record will be involved, and locked, in every transaction in which records using the auto-increment feature are created. This results in a system in which only one record-creating transaction can be handled at any given moment. The system table acts as a “transaction serializer” in such an environment. To make optimal use of the record locking capabilities, one could consider alternative mechanisms to assign auto-increment values. Possibilities include using the backend's auto-increment features (if present) or moving to a table that uses one record per auto-increment value. This is not an issue when using the Embedded Database, which uses table locking. The DataFlex Pervasive.SQL Driver supports both table and record locking, while the DataFlex SQL Drivers use record locking.

This attribute can only be set inside a Structure_Start ... Structure_End operation. For the DataFlex SQL Drivers, it is possible to set this attribute outside a structure operation. This support is intended to be used when opening tables without intermediate files. Be aware that since you can only set this attribute after the table is open, the automatic find that normally happens when a system table is opened does not take place when setting the attribute in this way.

Procedure ShowSystemTables
    Handle hTable
    Boolean bSystemTable
    String sTable
    Integer iStatus
    Move 0 To hTable
    Repeat
        Get_Attribute DF_FILE_NEXT_USED of hTable To hTable
        If (hTable > 0) Begin
            Open hTable
            Get_Attribute DF_FILE_IS_SYSTEM_FILE Of hTable To bSystemTable
            Get_Attribute DF_FILE_LOGICAL_NAME Of hTable To sTable
            Get_Attribute DF_FILE_STATUS Of hTable To iStatus
            If (bSystemTable) ;
                Showln sTable " is a system table and its buffer is " (If(iStatus = DF_FILE_ACTIVE, "active", "NOT active"))
            Else ;
                Showln sTable " is NOT a system table and its buffer is " (If(iStatus = DF_FILE_ACTIVE, "active", "NOT active"))
            Close hTable
        End
    Until (hTable = 0)
End_Procedure // ShowSystemTables

The sample procedure above will open every table in the file list and check if it is a system table. Next to that, it will show the status of the table’s (global) record buffer.

Procedure CreateSystemTable
    Handle hTable
    Integer iColumn
    Handle hoWorkspace
    String sPath
    String sOrigFolder
    //*** Make sure table comes in first folder of datapath by making that folder current
    Get phoWorkspace Of ghoApplication To hoWorkspace
    Get psDataPath Of hoWorkspace To sPath
    Get PathAtIndex Of hoWorkspace sPath 1 To sPath
    Get_Current_Directory To sOrigFolder
    Set_Directory sPath
    //*** Create physical table contacts
    Move 0 To hTable
    Structure_Start hTable "DATAFLEX"
    Set_Attribute DF_FILE_PHYSICAL_NAME Of hTable To "SysTable"
    Set_Attribute DF_FILE_IS_SYSTEM_FILE Of hTable To True
    Create_Field hTable At iColumn
    Set_Attribute DF_FIELD_NAME Of hTable iColumn To "CompanyName"
    Set_Attribute DF_FIELD_TYPE Of hTable iColumn To DF_ASCII
    Set_Attribute DF_FIELD_LENGTH Of hTable iColumn To 50
    Move 0 To iColumn
    Create_Field hTable At iColumn
    Set_Attribute DF_FIELD_NAME Of hTable iColumn To "OrderNumber"
    Set_Attribute DF_FIELD_TYPE Of hTable iColumn To DF_BCD
    Set_Attribute DF_FIELD_LENGTH Of hTable iColumn To 10
    Structure_End hTable
    Set_Directory sOrigFolder
    //*** Add to file list and generate fd
    Move 0 To hTable
    Get_Attribute DF_FILE_NEXT_EMPTY Of hTable To hTable
    If (hTable > 0) Begin
        Set_Attribute DF_FILE_ROOT_NAME Of hTable To "SysTable"
        Set_Attribute DF_FILE_DISPLAY_NAME Of hTable To "System table sample"
        Set_Attribute DF_FILE_LOGICAL_NAME Of hTable To "SysTable"
        Open hTable
        //*** Generate fd
        Get psDDSRCPath Of hoWorkspace To sPath
        Get PathAtIndex Of hoWorkspace sPath 1 To sPath
        If (Right(sPath, 1) <> Sysconf(Sysconf_Dir_Separator)) ;
            Move (sPath - Sysconf(Sysconf_Dir_Separator)) To sPath
        Move (sPath - "SysTable.fd") To sPath
        Output_Aux_File DF_AUX_FILE_FD For hTable To sPath
        //*** Create the record!
        Clear hTable
        Begin_Transaction
        Set_Field_Value hTable 1 To "Enter company name here..."
        SaveRecord hTable
        End_Transaction
        Close hTable
    End
End_Procedure // CreateSystemTable

This sample procedure creates a system table called SysTable, places it in the file list, and creates one (and only) record.