Introduction to FairCom DB Standalone

FairCom DB Standalone technology has always allowed you to link your application with a single-user or multi-user Standalone version of the FairCom Database Engine. This engine is similar to the core engine used by FairCom DB, except that in the Standalone case, there is no intelligent process (the database Server) to add advanced features like Transaction Processing, replication, hot backups, etc. These items might not be needed for all use cases, and therefore FairCom has made a significant effort to add many useful features into the Standalone technology. FairCom is committed to maintaining the Standalone technology and we welcome feedback from our valued customers if you have ideas for ways to improve this technology for your specific use cases. Simply contact your local FairCom Support team with any suggestions.

FairCom DB Standalone supports these basic models:

• Single-user (also referred to as NOTFORCE)
• Single-user with TRANPROC 
• Multi-user (also referred to as FPUTFGET)
• Multi-user multi-threaded (serialized multi-threading)

This book details the new features and support available in the FairCom DB Standalone operational model database. 

Standalone Performance Comparison - V10 to V12/V13

FairCom has performed internal benchmark testing using the FairCom DB multi-threaded test program ctmtap.exe. This program was executed on a high-end Windows Server 2019 environment comparing the performance gains from beginning with V10. FairCom also compared performance of the Standalone multi-user multi-threaded mode (#define FPUTFGET) against a FairCom DB database server. FairCom DB Server test was conducted with the ctmtap.exe client and the FairCom DB Server both running on the same machine, so shared memory communication protocol was used. For the Standalone multi-user ctmtap.exe test the data was stored on the local machine. By testing in this manner, we eliminated network overhead for both models. 

The test parameters involved inserting 1 million records, stopping the application (and the FairCom DB Server when operating in the client/server model), followed by restarting the application to then read the 1 million records. The takeaways to note from this graph include: 

• The Standalone Multi-User model has improved in both add and read performance beginning with V10. Adds are up to 3 times faster, and reads are up to 5 times faster. 
• The FairCom DB Server has also improved noticeably in performance from V10. Adds are close to 3 times faster, and reads are slightly faster. There was a slight dip in read performance with V11, but that performance is now restored with V12. 

Note 1 - There are substantial performance gains shown when moving from the Standalone Multi-User model to the FairCom DB Server model. Adds are showing 12 times faster (comparing V12 FPUTFGET adds of 13,517 per second to 168,900) and reads are showing 1.7 times faster (comparing V12 FPUTFGET reads of 95,074 per second to 168,996). 

Note 2 - The FairCom Professional Services team is available to help analyze your application performance. 

Note 3 - The ctmtap program was not modified to leverage any of the programmatic enhancement in V12 that can further drive performance. The performance enhancements discussed above are from only recompiling and relinking the ctmtap application with V12. 

Note 4 - While it might not be possible to use the FairCom DB Server in every environment, it should be noted it does provide better performance than the Standalone model. 

Note 5 - Your application performance gains may vary. This test application inserts data as fast as possible into the database. There are many variables that come into play when assessing application performance. 

Note 6 - The FairCom DB Server is available in a linkable model where it can be linked directly to your application code, thereby simplifying application deployment, which is one of the most common reasons we hear for using the Standalone model. For more information on this Embedded Server Model, contact FairCom. 

FairCom DB Standalone

Additional enhancements available with this V12/V13 upgrade include the following: 

Performance – As discussed above, V12/V13 has a meaningful performance gain over prior standalone versions by simply upgrading. See the Performance section for details on application changes you can utilize to drive additional performance gains. 

Encryption – See the Encryption section for the ability to utilize FairCom encryption for data at rest. 

JSON – See the section titled JSON Fields for FairCom’s new JSON support available within our NAV (NoSQL APIs like ISAM and CTDB) that also includes index support. 

Unicode – See the Unicode section for how to utilize Unicode support in your FairCom Standalone applications. 

Alter Table – See the Alter Table section for information about performing alter tables over FairCom DB Standalone data files. This chapter also includes links to the Hot Alter Table support, which makes application downtime during schema changes almost disappear. This feature minimizes the impact to your customer when you roll out changes to your database schema.

Partitioned Files – See the Partitioned Files section for how to transparently break large files into smaller files by an expression defined using the FairCom expression engine. For example, easily remove expired records by date range either permanently, or take them off-line into an archive mode that can be added back into the database at a later date. By removing data that isn’t immediately needed, you reduce the size of your files, which improves performance. It’s faster to query smaller files.

FairCom DB Standalone SQL Service - New Add-on

Perhaps the most powerful new feature available to FairCom Standalone customers is FairCom DB Standalone SQL Service, which provides a SQL service for the two Multi-User Standalone models. This product allows you to leverage SQL APIs over your existing c-tree data. Your data can be accessed by your multi-user standalone application or by any application using a SQL API such as:

• ODBC
• JDBC
• Direct SQL using C or C++
• Interactive SQL
• Python
• .NET 
• Node.js
• PHP

FairCom DB Standalone SQL Service adds the following to your existing c-tree based application:

• FairCom DB SQL Engine running as a Windows Service
• FairCom SQL Explorer – A powerful graphic utility for managing your data. 
• ctsqlimp.exe - A command-line tool for making existing ISAM files available to the FairCom DB Standalone SQL Service. 

These components allow your Standalone data to be accessed by SQL applications such as Excel, reporting apps, and business intelligence applications. 

This special engine does NOT provide ISAM server or other full server capabilities to your application.

Add SQL to Existing c-tree Data

SQL applications can access your c-tree data while your standalone app is running:

• Expose your data as read-write SQL through ODBC and JDBC.
• Use any of the vast number of third-party SQL tools for:
  • Queries
  • Analytics
  • Dashboards
  • Reports
  • Machine learning

FairCom SQL Explorer 

The built-in FairCom SQL Explorer opens up a world of possibilities with SQL access to your c-tree data:

• Learn SQL through point-and-click
• Create standalone tables and indexes
• Insert, update, and delete data
• Query data
• Edit, save, load, and run SQL scripts
• Debug SQL scripts one step at a time
• Look up errors

Build SQL Queries through Point & Click

The FairCom SQL Explorer built-in Query Builder helps you navigate your data:

• Point and click to create queries
• Watch the SQL statement evolve as you point and click
• Watch the data results come in as you point and click
• Easily create complex SQL queries:
  • Joins
  • Aggregates
  • Sorts

Manage all DB Objects

Point and click to create, drop, rename, clone, and secure:

• Tables 
• Indexes
• Views
• Synonyms
• Procedures
• Triggers

Create tables with Point & Click

• Create tables available in both SQL and FairCom DB Standalone applications
• Watch the create table statement evolve as you point and click
• Point and click to discover all table creation options:
  • Table attributes
  • Field attributes
  • Table constraints

Run any SQL Query 

• Easily view the results in a nice resizable data grid
• Easily scroll through the results
• View the database query plan
• Optimize queries

Edit, run, and debug SQL Scripts

• Create and edit a script
• Run the entire script
• Step through statements one-at-a-time
• Run the selected statement
• Load and save scripts

Manage data

• Display records
• Insert records
• Edit records
• Delete records

For more information about this powerful new add-on, consult the FairCom DB Standalone SQL Service V12 Developer's Guide.

LOCLIB Support

The FairCom DB Local Library Model (LOCLIB), provides local library support, and client connections to remote servers, extending the reach of your application potential.

The FairCom DB Local Library Model is a hybrid model that integrates the Standalone model with the Standard Client/Server model in one library. This gives you the ability to access data on a FairCom DB Server and on a local drive using one of the FairCom DB Standalone models at the same time. This model is especially popular on systems where speed and efficiency can be gained by using the local drive in lieu of taking the network hit to access a server. Temporary work files, extracted server data, local database information, and special "on the fly" index files are typically candidates for local disk access. Applications simply invoke a "switch" function call to direct the focus of the next operation.

Another common use case is to support both a FairCom DB Standalone model and a FairCom Server model from the same application code base. By utilizing the Local Library Model, you can use the Standalone model for smaller application installations (typically 15 users and under) while easily transitioning a customer to the Server model for larger installations, all from your same application binary. A common approach for this support is to implement an application switch that instructs the application to start using the FairCom Server model. Then install and start the FairCom Server to complete the transition from Standalone to the Server model. 

Benefits 

• Provide fast local data storage and retrieval without adding extra network traffic. 
• With heterogeneous support, this model provides a convenient way to port data between different architectures. An application may read data from one platform and write it to another. All necessary byte flipping is handled by FairCom DB within the communication logic. (Given appropriate schema resources.)

The following FairCom APIs support LOCLIB: 

• ISAM API - See Local Library Support
• C API - See Switching c-tree instances
• C++ API - See Switching c-tree instances

During program execution, your logic must establish a separate FairCom DB instance for each local and/or FairCom Server connection with the RegisterCtree() instance function. The five FairCom DB instance functions documented in the function reference section are:

It is possible to have multiple instances of both local and/or Server-based FairCom DB files in the same application. FairCom DB distinguishes between local and Server calls based on the USERPRF_LOCLIB "user profile" mode provided in the extended FairCom DB initialization calls, InitCTreeXtd(), InitISAMXtd(), and OpenISAMXtd(). When the profile mode is OR-ed with USERPRF_LOCLIB, the initialization call establishes a local connection. For implementation instructions and a working example of this logic, consult the sample program ctlxmg discussed in ctlxmg - LOCLIB Sample Program.

Performance

This chapter documents additional features that may be used with FairCom DB Standalone V12/V13 to drive additional application performance. 

The performance shown in the graph above did not utilize any of the performance enhancing features discussed in this chapter, so imagine what you can achieve when you put it all together. 

Smaller Indexes Using Compressed Key Storage

FairCom DB indexing has always been optimized for the fastest possible data access. Indexes are composed of keys. However, not all keys are composed the same. For example, consider indexing based on file paths. Path strings are highly variable in length. Indexing data based on this type of key definition requires defining a key length of the absolute possible maximum while most keys are substantially less. This introduces much wasted space into an index structure as b-tree indexes are constructed of fixed-sized nodes for efficiency of reading and writing. Each node read or write is generally performed as a single I/O operation, thus the more keys per node, the better the I/O throughput of reading keys in an index. Further, large variably sized keys force a large index node size to enforce a three key per node minimum. FairCom has addressed this wasted space challenge for greatly reduced index sizes, leading to less I/O and ultimately, increased performance.  

In FairCom DB, we obtained considerable index performance for large key lengths. A new Variable-Length Key Compression ("Vlen Keys") demonstrated up to 6x reduction in storage space and up to 28x faster performance in testing with the FairCom DB Server. The ideal scenario for this level of performance gain involves a reasonable length field (perhaps 32 bytes or larger) that will be roughly ½ of the maximum field length or less for most records.

By default, FairCom DB indexes store keys as fixed-length values. This requires few CPU cycles however, increases storage space and resulting I/O. Taking advantage of the fact CPU processing is orders of magnitude faster than I/O, we created a new compressed, variable-length index structure significantly reducing storage space along with a concomitant reduction of I/O when reading and updating index data.

This effort required advanced optimizations to our Index Node/Page handling. Now we have the ability to store more index entries per Node/Page, resulting in more "bang for the buck" on each disk I/O. These internal improvements do not affect your existing application code. To take advantage of this new advanced index compression, simply rebuild indexes with the new settings detailed in the sections that follow.

Variable-Length Key Performance Results

Substantial improvements in performance were seen when using key compression. The following tests were conducted using the FairCom DB Server on a table with 1 million records and a single index over a 1,000-character field that contained a file name with long paths. 

• Insert column is the time in seconds to insert 1,000,000 records. 
• Read column is the time in seconds to read 100,000 random records. 
• Update column is the time in seconds to update 100,000 random records. 
• Delete column is the time in seconds to delete 100,000 records (no duplicates). 
• Rebuild column is the time in seconds to rebuild the full 1,000,000 record table.

FairCom DB API API Compression

New index node types provide options for reducing index size. [01/2024]The new NAV (FairCom DB API) index modes are as follows:

• Variable-Length Keys with Compression: CTINDEX_VARIABLE index mode provides variable length keys with padding compression to squeeze more capacity into each page block. Recommended for indexes over variable length data.
• RLE Compression: CTINDEX_VARIABLE_RLE index mode uses variable length keys with a simple RLE key compression of bytes 0x0, 0x20, 0x30 resulting in shorter index entries and smaller index files. Recommended for indexes over variable length data with multiple segments, binary data, or large numeric data types (8 byte integers).

Example

The following example demonstrates how to use one of these modes: 

pIndex = ctdbAddIndex(hTableCustMast, "cm_custnumb_idx", CTINDEX_VARIABLE_RLE, NO, NO);

For more information, see ctdbAddIndex in the FairCom DB API documentation. 

ISAM API Compression

[01/2024]New "Alternative Key Types" have been added to support additional compression options. These key types support the compression of indexes with the ISAM API:

• KTYP_VLENGTH (0x608) -  Eliminates the key padding bytes on disk without the high performance costs of the legacy COL_SUFFIX key compression. This is suggested for indexes over variable length fields that may have a lot of variation in the length such as street address, and filesystem paths. Anyone using COL_SUFFIX (8) should investigate this improved alternative.
• KTYP_VLENGTH_SRLE (0xE00) - Simple RLE compression without the high performance costs of legacy key compression. This is suggested for indexes over fields that may have a lot of repeating binary 0, ASCII space, or ASCII 0 values. This may be beneficial for keys over many data types such as 8 byte integers, binary data, or other variable length data.

These KTYP_ values are bits that can be set in the IIDX.ikeytyp for each index. 

See the section in the FairCom ISAM for C Developer's Guide titled IIDX Structure.

Utilities to Confirm Index Compression Modes

The file information utility, ctinfo, displays index compression modes as included in the key type when displaying index information. 

Example

IIDX #2 { 

        /* key length              */ 4, 
        /* key type                */ 2048, (0x0800 = KTYP_KEYCOMPSRLE) 
        /* duplicate flag          */ 0, 
        /* null key flag           */ 0, 
        /* empty character         */ 0, 
        /* number of segments      */ 1, 
        /* r-tree symbolic index   */ cm_custnumb_idx, 
        /* alternate index name    */ 0000000000000000, 
        /* alternate collating seq */ 0000000000000000, 
        /* alternate pad byte      */ 0000000000000000, 

}; 

Process All Files Forward and Backward

Frequently, the newest and most relevant data is appended at the end of data files. Reading records backwards from the end of the file toward the beginning is the fastest way to process this data. 

FairCom DB has always been able to read records backwards using an index, or when resources are not enabled on a data file. For example, you can create an index on RECBYT and use it to read records backwards in the order they are stored in the data file. This is fast, but it is even faster to read records directly out of a data file without using an index.

This enhancement allows FairCom DB to read records backwards directly out of a data file without using an index. It only applies to data files containing fixed-length records. Data files containing variable-length records still require an index to read records in reverse order.

Limitations

• Data files defined as variable-length still require an index to read records in reverse order.

Database Internals

Before this enhancement, FairCom DB did not allow reading a data file in reverse physical order if that file contained resource records. For example, calling LSTREC() on such a file would fail with error 48 (FMOD_ERR).

FairCom DB imposed this restriction because resource records can span more than one fixed-length record block in the data file. Thus, scanning backward through a data file could cause the record pointer to end up in the body of a resource record. This is not a problem when reading records in forward order because the start of the resource includes its length, which makes it easy for FairCom DB to skip over the resource record.

FairCom DB eliminated this limitation by creating a list of resource records that contains the offset and length of each one. This list is constructed on the first backward scan call. When a resource is added, deleted, or updated, the resource list is refreshed. 

Parallel Data Processing Scales Performance

Divide and Conquer Multiple Defined Fixed-Length File Regions

When performing a full-table scan of a large data file, a significant speed-up can be achieved by running multiple threads to process different parts (regions) of the data file simultaneously.

In V12 and later, GetFileRegions() divides the data record offsets for a specified number of regions for a fixed-length record data file. The caller specifies the number of regions and the function populates an array of offset-length pairs. Each offset-length pair defines a region, which is simply a set of records in the data file. An application can process a data file's records in parallel by assigning a different thread to process each region.

GetFileRegions()

More Batch Operations Improve Remote Performance

FairCom DB Batches provide a high capacity means to operate on multiple records at once greatly reducing network traversal time. The result for applications is higher performance. FairCom DB batches can be used in all basic database operations:

• Add
• Get
• Delete
• Update

V12 enhances batch operations in all areas as described below.

Batch Filters and Range Support Added to FairCom DB API Batch API

The efficiency of using ctdbSetBatch() has been greatly enhanced. Previously, several calls were required to set up a batch, run it, and tear it down. With multi-user standalone applications, most record access API calls access a remote data file usually across a network mount. Now you can set up a batch, run it, and tear it down with far fewer remote data access calls. Previously a batch could take seven calls to the FairCom Database Engine and now it can be done in as few as two calls. 

Note This feature is a Behavior Change.

Batch-aware versions have been added:

ctdbSetBatchFilter()

ctdbSetBatchRangeOn()

ctdbSetBatchRangeOff()

Performing a Batch Lookup (Pseudo Code)

Performing a batch lookup in the past might have looked like this:

Now it can look like this:

The reduction of several network calls has proven in our testing to greatly improve the throughput capabilities with c-tree batch support. 

Batch column filtering for advanced record retrieval

Using the recently introduced FairCom DB API field mask support, it is now possible to filter columns in batch operations.

In ISAM, set the fldmask member of the PKEYREQ2 structure to be used as a "request" for the BATSETX call, using the BAT_EXTENSIONS mode. The fldmask must point to an array of ULONG sorted in ascending order containing the field number (DODA position) composing the partial record. It is also necessary to set the nfields member to indicate how many entries belong to the fldmask array. 

Batch mode BAT_LOK_ONE behavior change

BAT_LOK_ONE (a locking mode available with DoBatchXtd()) changes the locking strategy for a batch from holding locks for the result set until the batch is completed to only holding a record lock while the record is read.

Before this modification, BAT_LOK_ONE was ignored unless BAT_PHYS was in effect. 

In V12 and later, BAT_LOK_ONE is active when BAT_PHYS and/or BAT_RET_REC or BAT_RET_FIX are part of the batch request mode. 

Other retrieval strategies such as BAT_RET_POS cannot be used with BAT_LOK_ONE, and the batch request will fail with a BTRQ_ERR (430).  

BAT_EXTENSIONS new BATSETX (DoBatchXtd) mode

These new features have been implemented in the FairCom ISAM API:

1. Column filtering (i.e. return partial record based on specific column). Review FairCom DB API - Field mask support for more details. 
2. Record filtering integrated in the BATSETX calls (record filtering was already possible but required separate C/S round trips). More details on Data Filters can be found in the FairCom DB Developer's Guide in Data Filters.
3. Index ranges integrated in the BATSETX (index ranges were already supported but required separated C/S round trips). More details on index ranges can be found in the FairCom DB Developer's Guide in Index Ranges.

To implement the above functionality in the ISAM layer, the BAT_EXTENSIONS mode has been used. Prior to this modification, that mode returned NSUP_ERR. This mode can be now used in the "mode" parameter of the BATSETX call.

Note BAT_EXTENSIONS cannot be OR'd with other modes, which results in an error.

When "mode" is set to BAT_EXTENSIONS, the "request parameter" is interpreted as a PKEYREQ2 structure (or a series of LONG depending on the first two LONGS which are common to the PKEYREQ2).

typedef struct pkeyreq2 {
 ULONG batchmode;  /* batch mode                                           */
 ULONG batchmode2; /* batch mode2 (future use)                             */
 LONG btotal;      /* total in set                                         */
 LONG bavail;      /* number available                                     */
 LONG breturn;     /* number returned                                      */
 COUNT siglen;     /* significant key len (target/utarget size in bytes)   */
 COUNT nsegs;      /* number of segments in range                          */
 COUNT nfields;    /* number of entries in fieldmask                       */
 pTEXT target;     /* partial key target/lower-range in standalone it must */
                   /* point to a buffer as large as the key (ctnum->length)*/
 pTEXT utarget;    /* upper-range                                          */
 pNINT operators;  /* operators array for range                            */
 pULONG fldmask;   /* array of field number (DODA position)                */
 pTEXT filter;     /* filter string in case of filter                      */
} PKEYREQ2;

Changes compared to the PKEYREQ used when BAT_EXTENSIONS is not in use:

• batchmode; The Batch mode that used to be the "mode" parameter of BATSETX.
• batchmode2; Reserved for future use.
• siglen; The significant portion of the target keys in bytes.
• nsegs; For Range Use, this is the number of segments specified in the target keys and, as a consequence, the number of operators. Set to 0 if range is not established by the call.
• nfields; For Field Mask support, this is the number of fields specified in the field mask array.
• target; For Range Use, this is the partial key target/lower-range. In standalone it must point to a buffer at minimum as large as the key in use.
• utarget; For Range Use, this is the upper range. In standalone it must point to a buffer at minimum as large as the key in use.
• operators; For Range Use, this is the Operators array for range; set to NULL if range is not established with this call.
• fldmask; For Field Mask support, this is the array that specifies the field numbers (DODA position) of the field composing the partial record returned. Set to NULL if not in use.
• filter; For Filter support, this is the data filter to establish. Set to NULL if no filter should be established, in which case any active filter may apply.

The use of BAT_EXTENSIONS is not compatible with the following modes:

• BAT_DEL
• BAT_UPD
• BAT_INS
• BAT_RPOS
• BAT_KEYS

Encryption

FairCom DB supports encryption of data, index, and transaction log files. Once encrypted, it is impossible for a user to "dump" or "inspect" data at rest. Encryption is now also supported in standalone record access models, adding confidentiality to application data. 

FairCom's advanced file encryption supports several industry-standard encryption algorithms, including AES.

FairCom DB encryption requires two levels of encryption keys. A key is randomly generated on each physical file creation, both data and index files. This key is used to encrypt the actual data contents at rest. This file-specific key is in turn encrypted by a global master key and only this file-specific encrypted key is stored as a resource in it’s associated file. Thus, the master key must be presented at runtime to "unlock" physical file-specific encryption keys to decrypt data.

It is imperative to protect this master key. If the master key is lost all data becomes inaccessible. Master keys should be either randomly machine-generated or constructed with complex pass phrases that can’t be known by external third parties.

FairCom provides methods to interface with external key management systems precluding any individual's knowledge of the master key. Additional functions and utilities are available for periodic key rotation to further thwart intrusion attempts over time.

There are three steps to enable advanced file encryption to your application:

1. Create and secure your master password file. This file is created with the ctcpvf utility. Choose a long passphrase or use a password generator. The maximum size of this phrase is 64 bytes (one character = one byte). A ctsrvr.pvf file is created and under Windows, this file is protected by the Windows DPAPI. On Linux/Unix it is AES encrypted on disk. 
2. On application startup, you’ll need to present the initially provided passphrase which is then compared to the secured ctsrvr.pvf file contents. Once a correct passphrase is presented, encryption is transparent to the application. Any encrypted files are transparently read with no additional steps. An attempt to open encrypted files without a correct master password phrase results in error 606. For standalone applications the CTREE_ADVANCED_ENCRYPTION environment variable can be set to YES for your local process command shell.  
3. To enable encryption call SetEncryption() with your selected encryption algorithm. All files created from that point forward are encrypted. To disable encryption, use a NULL cipher mode in the SetEncryption() call. It is not necessary to encrypt all files. And, you can choose different advanced encryption algorithms for different files. 

A ctencrypt utility is also provided to periodically change or   rotate the master password file and re encrypt file specific keys.

To encrypt or decrypt existing files, the ctcmpcif compact utility, includes options to do so. 

Learn More

To learn more about encryption, see the following links:

• SetEncryption() - Enable at application level
• ctcpvf - create and manage master key
• ctencrypt - rotate master keys
• ctcmpcif - encrypt/decrypt existing files
• Environment Variables required for standalone

JSON Fields

JSON is rapidly becoming the new standard for storing complex data because of its compatibility with JavaScript, Python, and other popular object-oriented languages.

Query JSON from FairCom DB Standalone SQL Service

JSON support in SQL provides the ability to read/write JSON field types mapped into VARCHAR (if the size is less than 64K) or LONGVARCHAR. 

Use JSON in Your FairCom DB API Applications

New FairCom DB API Functions for JSON

• ctdbGetFieldAsJSON()
• ctdbSetFieldAsJSON()
• ctdbAddJSONSegment()
• ctdbSetJSONSegmentField()

New FairCom DB API Error Code

CTDBRET_FIXJSON = 4168 /* JSON field cannot reside in fixed record portion */

New FairCom DB API Data Type

typedef pTEXT   CTJSON, ctMEM *pCTJSON;

Using JSON Data Types in Your ISAM Applications

In the FairCom DB Standalone SQL Service, SQL treats each CT_JSON field as an LVARCHAR column. At this time, SQL cannot use indexes created on a CT_JSON field.

JSON Data Type

To create a table that contains a CT_JSON field, specify CT_JSON as the field type in the DODA. When you add a record that contains a CT_JSON field value, store it in CT_4STRING format, with the first four bytes of the field containing the length of the JSON value, followed by the JSON value.

JSON Indexing

To create an index on a CT_JSON field, set the index attributes in the IIDX and ISEG structures that you pass to CREIFIL(). Before calling CREIFIL(), call the PUTKSEGDEF() function to set the CT_JSON index attributes that will be used for the CT_JSON index in the CREIFIL() call. The CT_JSON index attributes include the key segment length, the c-tree data type of the key value, and the JSON key name that is being indexed.

The PUTKSEGDEF() function can be used to specify CT_JSON index attributes for a call to CREIFIL():

1. Declare and initialize an array of pointers to ctKSEGDEF structures.
2. Pass this array of pointers to PUTKSEGDEF(filno,segno,pkdef) by setting filno to ctKSEGcreidx, setting segno to the number of pointers in the array, and setting pkdef to the address of the array. Note that the array you pass must contain a pointer for every key segment that precedes the CT_JSON key segment. A pointer can be NULL if no extended index information is to be supplied for that key segment.

Example

In this example, the supplied IFIL definition (pifil) has two indexes of one key segment each. The example code sets the first extended segment info pointer to NULL and the second to a ctKSEGDEF structure initialized with the CT_JSON index attributes.

 ISEG myseg[] = {
  { 8,  8, SRLSEG},
  { 3,  4, SCHSEG}
 };
 IIDX myidx[] = {
  {8, 0,  NO, NO, 0, 1, myseg, "$ROWID$"},
  {4, 0,  NO, NO, 0, 1, myseg+1, "doc_id"}
 };
 IFIL  mydat = {
  "qajson",
  -1,
  20,
  0,
  ctFIXED | ctTRNLOG,
  3,
  0,
  ctTRNLOG,
  myidx
 };
 ctKSEGDEF sd;
 pctKSEGDEF pksegs[2];

 /* No extended segment info for first index. */
 pksegs[0] = NULL;

 /* Set the extended segment info for the second segment. */
 memset(&sd,0,sizeof(sd));
 sd.kseg_type = ctKSEG_TYPE_JSON;
 /* use source type from schema */
 sd.kseg_styp = ctKSEG_STYP_PROVIDED;
 /* set key segment length */
 sd.kseg_ssiz = 4;
 /* set c-tree data type of key value */
 sd.kseg_comp = CT_INT4;
 /* set the json key name that is being indexed */
 strcpy(sd.kseg_desc,"id");
 pksegs[1] = sd;

 rc = CREIFIL(pifil);

The name of the JSON key to index is stored in the kseg_desc field of the ctKSEGDEF structure. However, this field is limited to ctKSEGDLEN (32) bytes in the ctKSEGDEF structure definition. To overcome this limit, the ctKSEGcreidx usage of PUTKSEGDEF() accepts an array of pointers to ctKSEGDEF structures instead of an array of ctKSEGDEF structures so that an application can allocate a buffer to hold the CT_JSON index attributes that is larger than the ctKSEGDEF structure when the JSON key name exceeds the length of the field as defined in the ctKSEGDEF structure. As long as kseg_desc is NULL-terminated, the larger kseg_desc value can be passed to PUTKSEGDEF() in this manner.

The ctKSEGcreidx information that is passed to PUTKSEGDEF() is stored in the c-tree library until the next call to an ISAM level index create function: CREIFIL(), CREIFILX(), CREIFILX8(), PRMIIDX(), PRMIIDX8(), TMPIIDX(), TMPIIDXX(), TMPIIDXX8(). The recommended usage is to call PUTKSEGDEF() with filno of ctKSEGcreidx immediately before the index create call.

For an extended key segment type (kseg_type) of ctKSEG_TYPE_JSON, a call to GETKSEGDEF() is able to return to the caller a larger amount of ctKSEGDEF data than the size of the ctKSEGDEF structure. A call to GETKSEGDEF() for a kseg_type of ctKSEG_TYPE_JSON should set kseg_ssiz to the size of the supplied buffer. If GETKSEGDEF() finds that the data is larger than the supplied buffer size, it returns -VBSZ_ERR and returns the required length in the kseg_rsv1 field. For example:

 ctKSEGDEF sd;
 NINT rc,bufsiz;

 sd.kseg_type = ctKSEG_TYPE_JSON;
 sd.kseg_ssiz = bufsiz;
 if ((rc = GETKSEGDEF(keyno,i,&sd)) < 0) {
  if (rc == -VBSZ_ERR) {
   /* Buffer too small. sd.kseg_rsv1 holds the required size. */
  }
 }

This new behavior of PUTKSEGDEF() and GETKSEGDEF() is intended to be used to send and receive attributes for other extended key segment types in the future.

JSON Error Code

Error code 1122: 

JSON_KTFM_ERR: CT_JSON key transformation failed. Check sysiocod for details. See CTJSON_* definitions.

sysiocod values that may be returned in case of error 1122:

#define CTJSON_NOT_JSON  -1038 /* Not a valid JSON object */
#define CTJSON_NOT_OBJECT  -1039 /* Valid JSON, but not an object */
#define CTJSON_UNSUPPORTED_TYPE -1040 /* The specified key data type is not supported */

Limitations to JSON Key Segments

1. A CT_JSON key segment does not support the DSCSEG, ALTSEG, or ENDSEG key segment modes. Attempting to create an index that contains a CT_JSON key segment that includes any of these key segment modes fails with error NSUP_ERR.
2. CT_JSON key segments that use CT_STRING as the c-tree data type are always padded with null (0x0) bytes. We don't support setting a different key segment padding character on JSON key segments.
3. A CT_JSON key segment can specify any of the following c-tree data types: CT_BOOL, CT_CHAR, CT_CHARU, CT_INT2, CT_INT2U, CT_INT4, CT_INT4U, CT_INT8, CT_DFLOAT, CT_STRING. Note that CT_INT8U is not supported, because JSON only supports a signed 8-byte integer data type.
4. Remember that the JSON field indexing behaves as follows: if the JSON data type of the value that is being indexed is not compatible with the underlying c-tree data type specified in the key segment definition, that value is treated as NULL and is not indexed. Some examples:

• If the value is a string and the c-tree data type is an integer, the value is not indexed.
• If the value is an integer and the c-tree data type is CT_INT2 but the value is out of range for a signed two-byte integer, the value is not indexed.

JSON Data Type Support

V11.8 and later added JSON as a new data type, called CT_JSON. It is physically stored in a table using the CT_4STRING format, which can support strings up to 2 GB in length. 

It is available in all APIs with varying levels of support: SQL, NAV, ISAM, and Low-Level.

The NAV API allows you to index specific JSON properties using FairCom’s path language. This allows you to find records containing specific property values or ranges of property values. And it allows you to navigate records in sorted order using JSON property values.

In SQL and ISAM, you can read and write the string value stored in a JSON field but you cannot extract the values in JSON properties and treat them as columns. You cannot also use indexes created by the NAV API. 

Unicode

FairCom DB Standalone provides support for Unicode. This support includes:

• Storing Unicode data
• Indexing Unicode data
• Recognizing Unicode file names

This support is in addition to our ordinary string support, not an either/or approach.

Additionally, FairCom DB Standalone provides extended key segment support, which will allow support for other types of complex key segments in the future.

When building your FairCom DB Standalone library, you are prompted whether to include Unicode support, similar to the following: 

       ---- FairCom c-tree Plus UniCode Support ----
This version of c-tree Plus provides support for UNICODE field types.
Do you want to support UniCode field types? (Y)es (N)o (D)efaults- [N]:

Simply choose “Y”es to include this support. 

Learn More

17. FairCom DB Standalone handles Unicode exactly the same as it is handled by FairCom DB. You can learn more in the Unicode chapter of the FairCom ISAM for C Developer Guide.

Note There are a number of locations within the FairCom documentation where additional information may be included for use with the FairCom DB Server. This information can be ignored if you are using the FairCom DB Standalone models.

References to "Mirrored File Names" in the Unicode chapters are not available in the FairCom DB Standalone models. 

Also note references to SQL support in this book require either the use of the optional FairCom DB Standalone SQL Service package, or use of the FairCom DB Server engine.

Alter Table

The Alter Table function allows you to change a table's schema, for example if you need to update an existing database to accommodate a new release. FairCom DB Standalone supports FairCom's Hot Alter Table functionality. This feature allows you to make schema changes on-the-fly. Now you can roll out schema changes on production systems with a downtime of seconds instead of hours. 

Altering a Table

The FairCom DB API alter table function allows the modification of table, field and index properties after a table has been created, and possibly already populated with data.

The usual steps to perform an alter table operation are:

• Add, insert, delete or edit fields
• Add, edit or delete indexes
• Alter the table by calling ctdbAlterTable()

Hot Alter Table

FairCom's Hot Alter Table (sometimes called "Dynamic Alter Table," "Real Time Alter," and "Online Alter") can dramatically increase efficiency and minimize downtime when rolling out changes to your application data schema. Hot Alter Table allows the developer to provide "rolling upgrades" to your product deployed in the field. This means that, when your customer installs upgrades to your schema, they will not have to go through a lengthy file migration process, which previously could require many hours of downtime. Instead, they can use the new Hot Alter Table feature to upgrade records upon read or write.

The conventional Alter Table statement converts all records in the affected table at the time the statement is issued, working its way through the table processing one record at-a-time. It must create a new table, convert every record in the file and copy it into the new file, re-index, etc. This process can be time consuming if the table contains a large number of records. The table must remain locked, and therefore inaccessible to the application, during this process, resulting in a lengthy downtime when users cannot be connected to the database.

Updates On-the-Fly

Hot Alter Table allows records to be automatically converted to a new format as they are read so you can effectively roll out schema changes on-the-fly. Your customers can upgrade their database without the need to make all of their users disconnect from the database during a time consuming change. Application and database updates that previously required hours to accomplish can now be completed in seconds.

Multiple Schemas

When using Hot Alter Table, the table is allowed to contain records with different schemas: the original schema, a new schema requested by an Alter Table, and potentially more schemas requested by additional Alter Table statements.

Each record contains a schema ID that indicates which schema applies to the record (S0=the original schema, S1=the first altered schema, S2=the next altered schema, etc.). 

Conversion “On-the-Fly”

If someone is reading a record that does not use the current schema, it is converted on-the-fly to make it appear current. The current schema version is presented back to the reader, regardless of the version stored on disk.

Reduced Time of Exclusive Access

Although exclusive access to the file is required when Alter Table is requested (dynamic or otherwise), the Hot Alter Table feature will dramatically reduce the time spent in that state. The new functionality simply updates the record converter information in a resource. It does not have to update any records.

 

Learn More

You can learn more about Hot Alter Table in the FairCom ISAM for C Developer Guide.

Note There are a number of locations within the FairCom documentation where additional information may be included for use with the FairCom DB Server. This information can be ignored if you are using the FairCom DB Standalone models. 
Also note references to SQL support in this book require either the use of the optional FairCom DB Standalone SQL Service package, or use of the FairCom DB Server engine.

V13 Partitioned Files

A partitioned file is composed of a host data file and its associated indexes combined with a rule. The rule determines how to map the partition key value (e.g., a date, or invoice number, or some other characteristic) into a particular partition member of the host. The host file does not contain any actual data, but serves as a logical file that appears to contain all the data.

A partition member file has the same definition as the host, but only holds data whose partition key maps to the member. For example, customer orders may be partitioned by calendar quarter. All orders booked in the same quarter are stored in the same member. A member is composed of a standard c-tree data file and its associated indexes.

To add data to the partitioned file, simply call an add routine for the host. The code will add the record in the proper partition member, creating the partition member if necessary. Rewriting a data record may move the record between partitions if the partition key entry for the record is changed. Under transaction control, such a delete/add record operation is done atomically.

Searching the partitioned file in key order is fairly straightforward and efficient if the key is the partition key (the key used to determine which partition should contain the record). Searches on other, non-partition, keys are less efficient than normal because the record may exist in any of the partition members. The more active partition members there are in the logical file, the less efficient the non-partition key search will be.

It is possible to manage the partition members so that archiving or purging partitions is very quick and efficient.

Highlights

• Maintain data in separate c-tree Data/Index files, while enjoying access from a single host file.
• Rapidly purge or archive individual member files.
• Partitioned files are easily implemented at the ISAM and NAV development levels with a single additional API call.  
• Easily partition an existing linked file with SQL.
• Use all standard FairCom DB data searches on the entire file or directly on a member file.

Example

For an example showing how to create partitioned files, see Create an ISAM Partitioned File on the FairCom support web site. 

Learn More

To learn more about partitioned file support., see Partitioned Files in the FairCom ISAM for C Developer Guide.

Note There are a number of locations within the FairCom documentation where additional information may be included for use with the FairCom DB Server. This information can be ignored if you are using the FairCom DB Standalone models. 
References to "Partitioned File Security – File Password support" within the Partition File chapter are not available in the FairCom DB Standalone models. 
Also note references to SQL support in this book require either the use of the optional FairCom DB Standalone SQL Service package, or use of the FairCom DB Server engine.

V13 Insert Only File Mode

Create tamper-proof tables - when you need a tamper-proof historical record, such as tracking user actions or collecting time series data from a device, you can use the ctInsertOnly file mode to create an insert-only table that prevents records from being deleted or updated—even if you grant a user update or delete privileges on the table. Users with insert and read privileges can still insert and read records from an insert-only table. See File Mode Relations.

V13 Rebuild and Compact Utilities for Encryption

FairCom DB v13 adds rebuild and compact utilities for data encryption. See the discussion on "Encryption" on the Compact and Rebuild pages. 

V13 ctREADFIL - read-only file mode

When using Unix systems with the Standalone Multi-User operational model, if a process has a file open in ctSHARED mode, another process attempting to open the file in ctREADFIL | ctSHARED mode fails with error 920 (and vice versa). 

This is by design, because in Standalone MultiUser mode the ctREADFIL | ctSHARED mode is converted to ctREADFIL by removing ctSHARED from the file mode, and ctREADFIL opens are not compatible with the ctSHARED filemode. This is because ctREADFIL caches index node reads, so the Standalone Multi-User library does not permit other opens with write access when the file is open with ctREADFIL. 

We introduced a compile-time option: #define ctBEHAV_FPG_SHARED_READFIL causes a ctREADFIL | ctSHARED open to be treated as a shared open with read-only access. This means that since other processes are allowed to open the file for write access, this type of shared read-only open does not enable caching.

Building Your Application

The process of upgrading your c-tree V6 (first released in 1992) or newer application to FairCom DB is very straight forward:

1. No function call changes have been made during this time period. So simply recompile and relink your application.
2. Application data and index files are forward compatible. So simply open your c-tree V6 and later files as normal and enjoy full read/write access without any conversion.
3. If you are updating c-tree V4 and older applications, please contact FairCom for the upgrade steps. There are only a couple of extra steps to consider.

The remainder of this section provides additional information on upgrading to FairCom DB Standalone.

New Footprint

In an effort to simplify working with FairCom DB Standalone, we have moved the standalone code out to its own package, with its own footprint: 

• build_sdk – The mtmake.exe build utility for creating the FairCom DB Standalone libraries is located here. 
• sql_service_eval – FairCom DB V12 includes access to an optional module that provides SQL access to your existing FairCom DB Standalone data. This directory contains an evaluation version of this new support. 
• tools – This directory contains command-line tools for maintaining your application data. 
• license.htm – This file contains a link to the FairCom DB Standalone Development license. 
• ReadMe.htm – This file contains a link to the FairCom DB Standalone Quick Start guide. 

Compiling Your Application

FairCom DB includes mtmake.exe (mtmake on Linux/Unix) located in \<your installation folder>\build_sdk\build for building the Standalone libraries. Once you follow the prompts to build your library of choice, execute mk.bat (mk on Linux/Unix) to build your project. For more details, see mtmake.

Include Files

The include files needed for linking your client applications have been consolidated into the following folders:

• \<your installation folder>\build_sdk\include
• \<your installation folder>\build_sdk\build\ <your arbitrary directory specified in mtmake.exe>

Libraries

The FairCom DB Standalone library to link with your application will be located in 

\<your installation folder>\build_sdk\build\<your arbitrary directory specified in mtmake.exe>\obj\release 

(or it will be located in debug if you choose the default when executing mk.bat)

Compiling Examples

For examples of how to compile your client application, consult the ctree.mak file created when you execute mtmake.exe.

Required Libraries 

The following libraries are required when building C/C++ applications. 

Linux:

Windows:

Increase PAGE_SIZE to Improve Performance

Operating systems have a default file block size they read and write data in. Most recent Windows operating systems are 4K. Other operating systems will vary. Setting the index page size to match the OS, or multiples of the OS file block size can improve I/O performance. FairCom Database supports setting the page size from 512 bytes up to a maximum of 64K (65,536). This setting for FairCom DB Standalone is controlled by the sect parameter in the InitISAM(), InitISAMXtd(), or OpenISAM() function call, using the following formula: 

Page size = sect X 128 bytes

To utilize a 4K page size, set sect to 32. 

Recommendations

You should set your c-tree sect setting to create a page size at least the OS file block size, nothing smaller. For example, if you set c-tree's PAGE_SIZE to 2K, then on Windows you are reading 4K at the OS level and throwing away 2K every time.

For most applications, 8K to 32K is a fairly good general rule of thumb. If you are looking to fully optimize your application, and if your files are fairly large (see below), you might gain performance by increasing this setting, in multiples of your operating system page size value. 

It is difficult to define "fairly large files" because of all the different ways c-tree provides for accessing your files. The best method for tuning this value is to try a few different sizes with your precise application and a copy of your data. For example, increase from 4K to 64K in increments of your OS file block size looking for the optimal performance. 

Changing page size

Changing the application index page size is a major maintenance task and should be done carefully after a full, reliable backup. The procedures below represent the best practices for this operation.

Rebuilding Files

Recommendation: If you are considering changing your page size setting, be sure to rebuild ALL of your index files.

If you are reducing your PAGE_SIZE setting, and you miss rebuilding an index, and its value is set larger than your current sect setting, then you will get an error 40 (KSIZ_ERR, Index node size too large) when this file is opened and it will need to be rebuilt to resolve this error.

If your index file(s) have a page size set less than your current page size setting in ctsrvr.cfg, then you will be wasting memory. The file will open fine, because a smaller index page size will fit in the larger page size in memory. 

Recommendation: Experiment on copies of your data and server folder. When you are confident with the results, back up your source data and then change the live production files.

Warning Changing the page size is a major maintenance task and should be done carefully and with a full reliable backup. Practice on a copy of your data and other folders until you are confident with the results. Notice that a file created with a larger page size cannot be opened by an application with a smaller page size. 

Follow these procedures to rebuild the affected files:

1. Make a good backup of your application data.
   1. Move this copy to a different machine from your live system. Keep a clean copy in case you need to start over. 

2. Change the sect setting within your application code and recompile and relink the application. 
3. Delete the current index files for your data files.
4. Rebuild the indexes for your fixed-length data files using the ctrbldif utility including the -sectors parameter (which is -256 in the example below, generating a 32K page size):

./ctrbldif (server directory)/data/ctreeSQL.dbs/MyData.dat -256

Recall the formula above: Page size = sect X 128 bytes. 

page size    sect
4096		32
8192		64
16384		128
32768		256
65536		512

5. For variable-length files, rebuild the indexes using ctcmpcif.exe as follows: 

ctcmpcif.exe <your application data directory>\your_file.dat 256

Note It is acceptable to use ctcmpcif.exe to rebuild both fixed- and variable-length files. The ctcmpcif.exe utility creates a new copy of the data file by copying all the active records into a new file, and then calling ctrbldif.exe to generate a new index. ctcmpcif.exe is required to ensure the deleted space management index is properly built within the variable-length data file. Fixed-length files don’t have any internal indexes, therefore calling ctrlbdif.exe alone is faster.

6. Launch your application and confirm you are able to access each of your data files. 

V7 to V12/V13 Upgrade Considerations

FairCom DB Standalone is very backward compatible. Data files from c-tree V6 and newer can still be ready fine with the latest FairCom DB release without any change. The following small list of items should be considered as you begin your Standalone update.

V12/V13 Upgrade 

FairCom DB V12 can still read non-huge files (c-tree V6 formatted files), so an application file conversion is only required if you desire to use any of the attributes that require the extended header, such as HUGE files, segmented files, and partitioned file support.

Click the following for a list of features that rely on the "extended header support".

V9 Upgrade

When moving forward, be aware that in V9, extended headers are now included by default on all new files created by FairCom DB.

V7 Upgrade

V7 introduced HUGE, segmented and partitioned file support, which require an extended file header. Here are steps to convert V6 files to support the new extended header:

1. Convert your existing V6 data files with the ctcv67 conversion utility. This utility converts existing c-tree Plus data files to the extended format introduced in V7. Extended header support offers HUGE file support and segmented files.
2. Use the Xtd8 file creation functions to create new Extended files.
3. For data files supporting HUGE file support, remember to update your duplicate index keys in the IFIL definitions to include an additional 4 bytes for the associated record position.
4. FairCom has cleaned up the required #include files. All of the 5+ c-tree include files (e.g., ctoptn.h, ctstrc.h, etc.) can be removed from your application and replaced with ctreep.h. 
5. Note that the ctreep.h header file automatically includes the ctv6v7.h header.

V4 and Earlier

c-tree V4 data and index files (last shipped in 1992) are not compatible with c-tree/FairCom DB V6 and later. There is a simple file conversion process that can migrate V4 files to V6 and later format. Please contact FairCom for details.

Error 920 on Unix systems

In FairCom V11 on Unix systems, in the Multiuser (FPUTFGET) operational model, if a process had a c-tree file open in ctSHARED mode, another process attempting to open the file in ctREADFIL | ctSHARED mode failed with error 920, and vice-versa.

This is by design because, in FPUTFGET mode, the ctREADFIL | ctSHARED mode is converted to ctREADFIL by removing ctSHARED from the file mode. ctREADFIL opens are not compatible with the ctSHARED filemode because ctREADFIL caches index node reads, so the FPUTFGET c-tree library does not permit other opens with write access when the file is open with ctREADFIL.

A new compile-time option, #define ctBEHAV_FPG_SHARED_READFIL, can be specified in ctoptn.h when compiling the FPUTFGET library. This option causes a ctREADFIL | ctSHARED open to be treated as a shared open with read-only access. Note that this means that, since other processes are allowed to open the file for write access, this type of shared read-only open does not enable optimizations of caching reads of index nodes.

Note that this option is off at compile time by default.

The bullets below list the compatible file open modes, where r is ctREADFIL, rs is ctREADFIL | ctSHARED, s is ctSHARED, and x is ctEXCLUSIVE.

Original behavior:

• r and rs are compatible
• s is compatible with s
• x is compatible with none

New behavior:

• r is compatible with r
• rs and s are compatible
• x is compatible with none

Affected Components: FPUTFGET library