Binary Record API

The Binary Record API is ISAM. It provides direct control over binary records.

You directly control all reading, writing, finding, saving, positioning, navigating, locking and transacting at the record level.

You can store any binary data in a record.

The database can automatically index non-nested segments in each record. A segment is a field or part of a field. Segments can overlap.

You can write callbacks in C and C++ to extract fields out of nested binary structures so the database can automatically index them.

You can find records by indexed values, and navigate across binary records in index order and in data order — at extreme velocities.

This is useful because you can write algorithms to process data in custom ways that no other database can.

NAV API

The NAV API provides direct control over fields in relational rows — with great ease and high velocity.

You directly control all reading, writing, finding, saving, positioning, navigating, locking and transacting of rows in tables.

You can store column data in a row.

The database automatically indexes columns.

You can find rows by column values, and navigate across relational rows in index and data order.

This is useful because the NAV API makes it very easy to manage relational data while providing complete control to process data in custom ways that no other database can.

ANSI SQL API

SQL provides easy, traditional access to relational data.

You can run SQL queries over the same binary data created and managed by the NAV, ISAM and Low-level APIs. Thus, you can combine the productivity of SQL with the power of binary.

You can use SQL to create, index and query tables.

You can automatically leverage an optimized join engine for fast filtered joins.

You can create and use stored procedures, functions and triggers.

This is useful because it maximizes developer productivity. Use SQL when it is the best fit, and use the NoSQL APIs when they are the best fit.

Plug-ins

You can safely extend the database using plug-ins.

Plug-ins are dynamic link libraries that run when the database runs.

Your compiled code runs in the same process as the database with full and independent access to all its functionality.

Your plug-in runs continuously and can do anything. It is a client of the database that can process data at unprecedented speeds.

This is useful for building server-side processes, adding communication protocols, embedding app servers, etc.

FairCom ships plug-ins that provide the following services:

• HTTP App Server for its browser-based applications.
• MQTT Broker for sending, brokering and receiving MQTT messages, as well as automatically persisting data sent over MQTT.
• REST API that provides full access to all database functionality: creating databases, tables and indexes, as well as lookup, querying, inserting, updating and deleting records.
• OPC UA client for integrating with factory data.
• Automatic data aggregation for real-time analytics.
• ThingWorx connector for automatically populating ThingWorx with collected data.

Callbacks

The database can call your C and C++ code synchronously or asynchronously at all critical points of data processing.

This allows your code to do anything in response to data changes, such as populating custom indexes, converting data types, implementing proprietary encryption and compression, replicating data, notifying other applications, etc.

Client Libraries

Client libraries for the database exists in all major languages, including Java, Python, C, C++, C#, Visual Basic, JavaScript (Node.js and browser), PHP, etc.

These drivers make it easy for you to do everything with the database in your favorite language.

Because these NoSQL APIs remote control the database, they make the database an extension of your favorite language. It is the opposite of stored procedures, where you have to write special code that the database runs. Instead, you write code in your application that forces the database to do exactly what you want in the way you want. The drivers allow you to "remote control" the database to accomplish things that no other database can do.

You directly control all reading, writing, finding, saving, positioning, navigating, locking and transacting at the row level.

You can also connect to multiple databases at the same time, which allows you to simultaneously remote control multiple databases. For example, you can look up a record in one database, use a column value to look up a set of matching records in another database, take the matching records and filter data in a query against another database. Thus, you can easily create and manage sharded data across databases with unprecedented control. This is vital for high-performance clustered applications because a one-size-fits-all database cluster from other database vendors cannot process data anywhere near as effectively as a custom algorithm.

SQL drivers make it easy for you to run SQL from your application.

You can use SQL and NoSQL drivers simultaneously in your application to get the best of both worlds.

Java & C# in SQL

You can use Java and C# to write SQL triggers, functions and stored procedures.

• Triggers run code before and after data changes to specific tables.
• SQL queries can call functions.
• Applications can call stored procedures through SQL, ODBC and JDBC.

This is useful because Java and C# allow you to do anything in response to a database event.

Machine Data Types

Store and process native CPU data types at machine speed.

Run machine data types across different CPU architectures with endian compatibility.

Leverage the following machine types:

• 2 GB Byte Arrays
• Boolean
• Signed Byte
• Unsigned Byte
• Signed 2-byte Integer
• Unsigned 2-byte Integer
• Signed 4-byte Integer
• Unsigned 4-byte Integer
• Signed 8-byte Integer
• Unsigned 8-byte Integer
• Binary Float
• Binary Double

Use derived types that are implemented as native machine types:

• JSON up to 2 GB
• String up to 64 K - both fixed and variable length
• Large String up to 2 GB
• Currency - 32 digits of decimal precision and 4 digits of scale
• Decimal Floating Point Number - 32 digits of decimal precision and 127 digits of scale
• Date
• Time with millisecond precision
• DateTime with millisecond precision

These standard machine types are directly supported and optimized by C compilers, and they are compatible with the native types of most programming languages.

SQL Data Types

Use a wide-variety of SQL data types:

• CHAR
• NCHAR
• VARCHAR
• NVARCHAR
• LVARCHAR
• BINARY
• VARBINARY
• LVARBINARY
• BIT
• TINYINT
• SMALLINT
• INT
• BIGINT
• REAL
• FLOAT
• DOUBLE
• DECIMAL
• MONEY
• DATE
• TIME
• TIMESTAMP

The benefit is that SQL types are internally implemented as machine data types. This makes it easy for SQL to query data created and processed in the NoSQL APIs. And, it allows NoSQL APIs to process data a machine speed.

Binary Structure Indexes

Automatically index segments of a binary structure as if they were SQL columns.

Automatically index bytes at absolute and variable positions.

Treat a sequence of bytes as a native data type, such as a Signed Integer, Unsigned Integer, BCD, etc.

Independently collate each segment in ascending or descending order.

Automatically flip bytes in an index to enable fast leading wildcard lookups, such as using "*son" to find "Anderson", "Carlson", etc.

Create a custom collating sequence that automatically transforms indexed bytes into desired binary values, such as lower case with no diacritics collated in a custom sort order.

More Information

Index Segment Concepts

Index Segment Modes

Conditional Indexes

Control which records are included in an index by simply using a filter that works like a SQL WHERE expression.

This greatly speeds queries because indexes contain a smaller set of items.

You can create many filtered indexes on a table for fast pre-filtered queries.

Because extra indexes slow down inserts, updates and deletes, you can use deferred indexing to update these indexes asynchronously.

More Information

Conditional Index Documentation

Column Indexes

Quickly, easily and automatically index one or more columns like all SQL databases.

SQL makes it easy for anyone to create a process, and query tables and indexes.

Indexes created in SQL can be used in lower-level APIs, and vice-versa.

One benefit is that you can easily create tables and indexes in SQL and use the powerful NAV API to process data with precise control at high speed with predictable performance.

Dynamic Link

Eliminate latency between the application and database because they both run in the same process on the same server.

Eliminate separate database installation because the database is linked directly into the application.

Easily upgrade database to new versions by simply deploying a new dynamic library file.

The entire database is embedded in a single file, which is a DLL on Windows, an SO on Linux and Unix, and a DyLib on macOS.

External applications can also communicate with any embedded database when you enable the optional external communication protocols.

An embedded database allows a single application to scale to tens of thousands of ACID transactions per second on a single server.

It is easy to run multiple applications on the same server, and each application contains its own database.

An embedded database excels at running massively parallel analytics.

An embedded database is ideal for microservices because each app server has its own database linked directly into it. This eliminates latency, greatly simplifies deployment, and allows an application to scale horizontally to millions of transactions per second by simply adding more app servers. Each app server can use FairCom's built-in replication to keep its data in sync with other app servers and with central databases. Each app server can also query and modify data on central databases and each other. The architectural possibilities are limitless.

Shared Memory

Shared memory is the fastest way to communicate between an application and a database running in separate processes on the same server.

This makes it easy for multiple applications to use the same database and for the same application to use multiple databases on the same server.

FairCom makes it easy to run multiple databases on the same server without the need for containers or virtual machines.

To make it easy to connect to the database using the fastest protocols, the database server automatically connects using shared memory for on-server applications and TCP/IP for remote off-server applications.

The database can scale to hundreds of thousands of ACID transactions per second on a single high-end server.

It eliminates the performance, license, security and administration costs of virtualization and containerization.

This option is ideal for high velocity multitenant solutions that need to isolate customer data and maximize hardware utilization.

It is ideal for high-velocity microservices because multiple instances of a microservice can connect to their own database on the same server, which eliminates network latency.

It is ideal for high-velocity, real-time, mission-critical enterprise applications.

TCP/IP

FairCom DB uses TCP/IP to connect with applications running on separate servers.

FairCom DB drivers automatically connect to the database using the fastest protocol. When applications and the database are running on the same server, the driver connects using shared memory. When they run on separate servers, it connects through TCP/IP. This makes connections easy to manage, and it provides maximum flexibility and scalability for application and database deployments.

Applications running in separate servers can connect to many databases running on separate servers.

Data can be sharded across servers for virtually unlimited scalability.

This option makes it easy to scale horizontally.

It is ideal for cloud deployments.

Multiple servers can scale to millions of ACID transactions per second (TPS).

Atomic Transactions

FairCom DB provides atomicity with and without transaction logs. With transaction logs, it provides full ACID compliance. Without transaction logs, it provides atomicity and isolation with a Continuum of Control over durability that trades a small risk of losing some data for significant increases in performance.

Atomicity with Transaction Logs

Atomic transactions ensure one or more insert, update and delete operations all succeed or all fail as a group. This is a cornerstone of ACID compliance.

This option is ideal for applications that process complex multi-record transactions within and across tables that must all succeed or all fail as a group.

By default, the FairCom DB database maintains transaction logs, and its tables log changes to them. These tables have atomic transactions.

Transaction logs provide many benefits:

• Atomic data operations
• Queries that are isolated from changes made by other users
• Highly durable data
• Point-in-time restores
• Ability to replicate data without affecting database performance

Atomicity Without Transaction Logs

FairCom provides a unique option to configure individual tables not to use transaction logs and to still have atomic and isolated transactions. This valuable ability is made possible by FairCom DB's pre-image technology, which provides atomicity and isolation without the performance cost of transaction logs.

This technology is useful when you need extreme performance along with isolation and atomicity. This comes with the risk that an abnormal server termination may lose some data and some indexes may need to be rebuilt. FairCom DB gives you options to mitigate this risk by forcing the table and its indexes to write all changes immediately to disk while caching them in RAM for high performance queries. FairCom provides another option to mitigate this risk by flushing the file's cache to disk on a regular schedule or on demand when the application has finished saving critical data.

FairCom DB's pre-image feature gives you the ability to have extreme write performance, atomicity and isolation along with precise control over the level of durability – without the performance cost of transaction logs.

FairCom DB's pre-image technology provides the best features of Multiversion Concurrency Control (MVCC) found in PostgreSQL without the cost of periodically running cleanup processes to remove outdated data from indexes and tables. It works similarly to Oracle Database's UNDO and REDO logs, but it does not have the performance cost of writing logs to disk. It provides faster performance than Cassandra while providing atomicity and isolation that Cassandra cannot offer.

Custom Isolation

Performance speeds up as isolation decreases. Thus, FairCom DB's NoSQL APIs default to no isolation for maximum performance, and they allow you to add locks as needed to create your desired level of isolation.

• When you want no isolation, do nothing special because this is the default.
• When you want read isolation, apply read locks to the records you read and no other process can modify them.
• When you want write isolation, apply write locks to records you write and no other process can modify them.
• You can default to strong isolation by requiring all processes to obtain write locks before modifying records and read locks before reading records.
• You can guarantee complete isolation by opening a table in exclusive or read-only modes.
• You can ignore locking to read a write-locked record (i.e. perform a dirty read).
• You can cause API functions to block automatically until a record is free to be read.
• You can automatically lock all records you read, temporarily suspend automatic locking and resume automatic locking with simple function calls.

FairCom SQL supports two of the four SQL isolation levels:

• Read Committed
• Repeatable Read

SQL clients can specify either option per connection. Through configuration, FairCom DB can globally limit SQL isolation to Read Uncommitted only or both.

SQL uses locks to enforce isolation to ensure the SQL and NoSQL APIs work seamlessly over the same data.

SQL Read Committed

Read Committed is the default Isolation Level for SQL queries in FairCom DB. It ensures a SQL query sees no data changes made by concurrent users. It allows a query to see previous uncommitted data changes made by its own transaction. If the query is repeated in the same transaction, it will include committed transactions from other users who subsequently inserted, deleted or updated rows. It will never include uncommitted changes from other users.

SQL uses locks to enforce isolation to ensure the SQL and NoSQL APIs work well together over the same data. Thus, when Read Committed Isolation is not fast enough for an operation, you can use the NoSQL APIs to control locks more precisely for maximal speed. Also, when you need more isolation, you can achieve the precise amount you want with the NoSQL APIs.

SQL clients can specify Read Committed Isolation when they connect. Through configuration, FairCom DB can globally limit SQL Isolation to be Read Committed.

Read Committed is the default Isolation Level for many SQL databases including SQL Server, Oracle, DB2, PostgreSQL, etc.

SQL Repeatable Read

SQL Repeatable Read is an optional Isolation level for SQL queries in FairCom DB. It ensures a query sees no data changes made by concurrent users. It allows a query to see previous uncommitted data changes made by its own transaction. And unlike Read Committed Isolation, if the query is repeated in the same transaction, it will not include rows that were subsequently updated or deleted by concurrent users, but it will still include committed inserts from concurrent users. It will never include uncommitted changes from other users.

SQL uses locks to enforce isolation to ensure the SQL and NoSQL APIs work well together over the same data. Thus, when Repeatable Read Isolation is not fast enough for an operation, you can use the NoSQL APIs to control locks more precisely for maximal speed. Also, when you need more isolation, you can achieve the precise amount you want with the NoSQL APIs.

SQL clients can specify Repeatable Read Isolation when they connect. Through configuration, FairCom DB can globally limit SQL Isolation to be Repeatable Read.