Concepts
Concepts about the FairCom database engine
Concepts about the FairCom database engine
FairCom DB is optimal for mission-critical, time-sensitive applications, such as microservices, server applications, batch analytics, and more. It supports N-tier, client-server, server-side, and embedded architectures
FairCom DB is a modern, full-featured database supporting JSON documents, JSON APIs, SQL, caching, key-value, data uniformity and flexibility, indexing, full-text search, analytics, time series, messaging, data change tracking, data replication, high availability, disaster recovery, and multi-statement ACID transactions.
FairCom DB provides multiple database technologies in one product, with key-value performance, JSON flexibility, and SQL capability. FairCom's all-in-one solution eliminates the need for specialty database servers in your applications and reduces license, storage, management, and development costs.
FairCom DB manages data as records in tables. Each record is simultaneously a JSON document, a SQL row, a key-value, and a binary value. Each record in a table has the same structure, which you define in advance. Within that fixed structure, you can use JSON and variant fields to store any data structure and value in any record. This provides the ideal balance between enforced uniformity and flexibility.
FairCom DB provides JSON, SQL, and record buffer APIs for operational and analytic applications. You can use each API simultaneously over the same data. Applications need multiple APIs over the same data to reduce development costs, implement evolving application requirements, and meet performance requirements.
JSON is ideal for easy, low-cost development, data flexibility, and rapidly changing applications. SQL is best for analytics, joining data, and querying unindexed data. FairCom's record buffer APIs provide the fastest possible performance for batch analytics and consistent, repeatable, low-latency operations. For example, when SQL is not speedy enough, you can use the JSON DB API for more control. When JSON DB is not fast enough, you can use FairCom's record buffer APIs for complete low-level control to create the ideal data processing technique for any use case.
The multimodel engine in FairCom DB is unique in that it natively supports JSON, SQL, and binary data in the same record. FairCom DB makes this possible by storing, indexing, and processing all data as binary records. A binary record is a C structure consisting of FairCom's data types. FairCom DB automatically maps the data types in each API to and from FairCom's data types, which include strings, integers, numbers, arrays, booleans, dates, times, JSON, variants, and so forth. Most of these types are native to C for fast data processing.
The FairCom JSON engine views and processes each binary record as a JSON document. The FairCom SQL engine treats the same record as a SQL row. The FairCom record buffer engine processes the same record as an in-memory C structure.
All FairCom engines and APIs work transparently over the same data, allowing you to choose the API that best meets your needs.
The JSON DB API queries data using JSON and returns data as JSON. It posts JSON requests over HTTP to an endpoint and receives JSON responses.
The JSON DB API provides JSON actions to manage all database operations:
Create, alter, delete, list, and describe:
Insert, update, and delete records.
Run SQL queries and statements without JDBC and ODBC drivers.
Run SQL queries and statements without JDBC and ODBC drivers.
Run SQL statements under transaction control and return the results as JSON.
Query data using JSON DB actions optimized for specific use cases:
Each JSON DB action automatically runs within a transaction. Multiple JSON DB actions can also run within a multi-statement transaction. All JSON DB transactions are ACID-compliant.
Each top-level property in a JSON document automatically maps to a field in a record. Because a FairCom DB field is strongly typed, mapping the field to a JSON property makes the JSON property strongly typed, allowing JSON properties to support additional data types, such as dates, times, timestamps, and binary data.
JSON objects and arrays map to native JSON fields, which support all the flexible and dynamic capabilities of JSON.
JSON strings map to FairCom string fields, which have user-defined maximum lengths up to 65,500 bytes or 2 GB.
JSON numbers map to FairCom number fields as 8-, 16-, 32-, and 64-bit integers; 32- and 64-bit IEEE binary floating point numbers; and 32-digit, fixed-point, decimal numbers.
JSON strings containing an ISO 8601 formatted date/time map to date, time, and timestamp fields.
JSON strings containing base64 or hexadecimal characters map to binary fields.
JSON
nullor a missing property maps to the FairCom null value.
The SQL API queries data using ANSI-92 SQL statements, which return data as recordsets. SQL is ideal for joining data across many tables, running analytic queries and reports, and allowing external applications to query data using JDBC and ODBC.
FairCom provides JDBC and ODBC drivers that support major programming languages and third-party SQL software.
FairCom also provides a fast, Direct SQL API for C programs to send SQL statements to FairCom DB and receive back results.
The JSON DB API can also run SQL queries and SQL statements and return the results as JSON. The JSON DB API also provides built-in efficient connection pooling.
FairCom's ISAM and CTDB APIs are its record buffer APIs.
Ideal use cases for record buffer APIs:
Microservices need high-speed caching. Embed FairCom DB in each microservice process for high-speed caching that supports key-value lookups, SQL queries, JSON ease of use, and record-buffer processing.
Batch processing and analytic programs must bring code to the data for maximum data processing speed. Use your favorite programming languages to run batch applications on the same computer as FairCom DB, leveraging shared-memory access to data.
Server applications need extreme performance and predictable low latency. Use FairCom's record-buffer API for in-memory data processing of persisted data.
Record buffer APIs achieve unrivaled speed for two reasons:
They load binary data directly from storage into application memory and back.
They provide complete control over every aspect of record processing.
Loading binary data from storage into a buffer is fast because there are no extra data processing steps, such as converting binary values into UTF-8 or JSON. A record buffer API loads binary data as-is from storage directly into application memory. The application has direct read and write access to the bytes in the buffer. The application can use FairCom's CTDB API to get and set strongly typed field data in the buffer. The application uses a record buffer API to save the buffer directly to storage.
General-purpose data processing algorithms in database engines cannot compete with special-purpose algorithms. The record buffer APIs allow your application to implement low-level data processing algorithms that control every aspect of reading and writing data, managing locks, managing transactions, saving record positions, jumping back to saved positions without needing an index lookup, skipping records, filtering records, etc. They can pack multiple binary buffers into a single save operation for exceptionally fast inserts.
Because the record buffer APIs fetch each record directly into the application's memory, they perform best when applications run on the same computer as the FairCom database.
Record Buffer APIs walk records in table and index order. Walking records forward or backward in index order automatically returns records in sorted order. Record buffer APIs can automatically filter records to match partial keys, complete keys, key ranges, and expressions similar to SQL WHERE clauses. They can look up records using keys. They can look up the closest matching record as a starting point for walking records forward and backward.
FairCom DB runs on all major operating systems (Linux, Windows, macOS, Unix, and so forth) and hardware platforms (Intel, Arm, 64-bit, 32-bit). It runs in the public cloud, private cloud, data center, office, laptop, and Raspberry Pi.
A computer may run one or more instances of the FairCom DB server. Each instance has its own set of communication ports. FairCom DB uses a TCP/IP port and a shared memory name for its SQL and record buffer APIs. It also uses a different TCP/IP port for its JSON APIs, which run over HTTP and WebSocket.
Each FairCom DB server may contain zero or more databases. A database is a security container that manages its objects.
Each database may contain zero or more objects, which include tables, indexes, and stored procedures. Each table may contain any number of records.
Each table may have up to 500 indexes, which include b-tree and full-text indexes. You may partition a table into multiple files.
A record contains up to 2500 fields. All records in a table have the same set of fields. A record has a maximum size of 2 GB.
A field may contain data or be NULL. A field has a data type. FairCom DB supports all major data types, including JSON, string, binary, integer, binary floating-point number, decimal fixed-point number, date, time, and timestamp.
A b-tree index can index all types of FairCom fields. It can also index JSON properties in a JSON field. You can use it for sorts, lookups, and queries. It supports equality, inequality, partial-key, and range constraints. It can look up records by key. It can move the record position to the closest matching key value. It can return records in ascending or descending sort order. It allows cursors to walk records forward and backward. For queries, it can include or exclude records that fall within a range of keys, match a partial or complete key, or are greater than or less than a key.
A full-text index can index all FairCom string, JSON, and binary fields. It finds records that have specific words and phrases in a field. A word is a series of characters not interrupted by white space or punctuation. A phrase is a series of words that are near each other. Because a full-text index can search for phrases, it is effective at querying a JSON field for a specific key-value pair, individual words, and phrases.
FairCom DB is a transactional database. All operations occur within an ACID-compliant transaction by default. While ACID transactions are a fundamental part of FairCom's database engine, it provides options for partial ACID compliance. ACID performs the following:
It allows multiple operations to be processed atomically (all-or-nothing).
It ensures operations across tables leave records in a consistent state.
It ensures queries run at a point in time so they are isolated from changes made by concurrent processes.
It guarantees data is safely and durably persisted — even during server failures.
While ACID transactions are vital for mission-critical applications, they slow performance. Some data and operations need faster performance and less ACID compliance.
The SQL and JSON DB APIs automatically run each action inside an ACID transaction. They also allow an application to run multiple statements in an ACID transaction. The record buffer APIs provide complete control over atomicity, consistency, isolation, and durability. This control allows an application to achieve the desired balance between performance and ACID compliance.
FairCom DB allows each table to run with a specific level of ACID compliance regardless of the API. For partial ACID compliance and faster performance, you can create tables that delay the durability of writes to the transaction log. For faster performance, you can create persisted tables that use in-memory transactions to provide isolation and consistency without transaction logs. For even faster performance, you can create persisted tables that support no transactions and have no transaction logs. For the fastest performance, you can create in-memory tables that have no durability, no transactions, and no transaction logs. For each increase in performance, there is an increased risk of losing or corrupting data during a server failure.
N-tier and client-server applications run FairCom DB as a server. They use the JSON DB and SQL APIs because they are optimal for this use case.
FairCom DB can be embedded directly into an application as a dynamically loaded library (.dll, .so, .dylib). An application can achieve unparalleled performance because the database server runs in the same process as the application and because it directly loads data to and from application memory.
Microservices, server applications, and desktop applications often embed FairCom DB.
Microservices embed FairCom DB as a local cache that persists data locally and bidirectionally replicates data changes to other microservice instances.
Server applications, such as stock market and backup software, embed FairCom DB to process data with unrivaled throughput and low latency.
Desktop applications, such as a productivity application or an IDE, embed FairCom DB because it can efficiently store multiple tables and indexes in a single file saved by a user as a document.
FairCom DB is an enterprise-class database server you can embed inside an application. Thus, authorized external applications can simultaneously interact with the same data as the parent application. For example, third-party SQL tools can query and modify data in the embedded database. Web applications can use the FairCom JSON APIs to do the same. Server-side batch and analytic applications can use the FairCom record buffer APIs to do the same.
FairCom DB scales vertically up to hundreds of thousands of transactions per second on one computer. It can support the performance requirements of almost all applications at a lower cost and faster performance.
In contrast, databases that scale horizontally must share data across multiple servers, so they perform slower because queries can only run as fast as the network can transfer data between the servers. The typical network speed of modern data centers is 10Gbits/s, which is many times slower than the bus speed of a current server, which is 512Gbits/s.
FairCom DB stores each table and index in separate files on the operating system. While the database is running, it has exclusive access to these files. An authorized application can detach table and index files and attach them to another database or move them to another location.
FairCom DB can store multiple tables and indexes in the same file to distribute an entire database efficiently as a single file. This feature is critical for applications that save a user-created document as a single file, such as a tax application, CAD system, or backup software.
Primary Key
It is best practice for each table to have a primary key to identify each record in a table. The database can only uniquely identify records for updates, deletes, and joins when a table has a primary key.
All FairCom APIs support primary keys. Using an identity field for the primary key is the best practice because the server automatically increments it as it inserts new records and because the key does not change when users update field values. The JSON DB API follows this best practice and automatically creates the "id" field as a 64-bit integer identity field with a unique index. The other FairCom APIs require you to define the primary key. Other fields can also be designated as the primary key.
Secondary indexes
A table may have many secondary indexes for quickly finding and sorting records by other keys. Be aware that each secondary index increases the time it takes to insert, update, and delete records.
FairCom DB provides optimistic and pessimistic record locking.
All FairCom APIs can use optimistic locking for record updates, which provides the best scalability.
SQL automatically locks records as needed, which provides good scalability.
The record buffer APIs provide complete control over all locking, which includes optimistic locks and a wide variety of pessimistic record locking mechanisms.
All types of record locks are compatible across all APIs, allowing data to be processed safely by all APIs simultaneously.
Optimistic Locks
The FairCom database supports optimistic locking in all APIs: JSON DB, SQL, CTDB, and ISAM. Use optimistic locking for maximum scalability and performance. Optimistic locking allows an application to read a record without locking a record. This feature improves application performance as concurrent users increase.
Optimistic locking requires a changeid field to be in a table. This field identifies when a record changes. This field is typically named changeid. You may assign the changeid attribute to any field. You may designate only one field in a table as a changeid field.
When a record is inserted or updated, the server sets the changeid field to the current transaction number. The value of the changeid identifies which transaction changed a record. Each change to a record is guaranteed to have a new transaction number. Thus, the value of the changeid field changes with each update to a record.
When an application updates a record, it includes the changeid field and sets it to the value it previously read. The server checks the value of the changeid field. If the value in the record matches the value in the update, the record has not changed, and the server applies the update. If the changeid value in the record does not match, another user has changed the record, and the server returns an appropriate error.
When an application wants to force an update without change protection, it omits the changeid field from the update or assigns NULL to the changeid field. The server unconditionally updates the record without checking for changes made by other users.
Optimistic locking does not conflict with pessimistic locking because the server always returns an error when another user has put a read or write lock on the record.
Pessimistic Locks
The FairCom database supports pessimistic locking in SQL, CTDB, and ISAM APIs. Use pessimistic locking when concurrent users are likely to update the same record and need the server to notify them when another user has locked the record before they read a record with update intent. A pessimistic lock on a record overrides the optimistic locking technique.
FairCom supports two types of pessimistic locks: read and write locks.
A read lock on a record prevents other users from changing it while allowing others to read it. Use a read lock to ensure a transaction can update a record without interference from other users. Read locks serialize write activity on locked records, which slows database writes.
A write lock on a record prevents other users from writing or reading the record. Use a write lock for batch operations that need exclusive access to records. Write locks serialize all read and write activity on locked records, which slows database performance.
Read-and-write locks slow server performance because they require the server to acquire and release record locks.
Pessimistic locks may cause a deadlock, where two processes prevent each other from acquiring locks. The FairCom server detects deadlocks, chooses one user to fail, and returns an error to that user.
When you use JSON to represent data, each JSON document is like a record in a table: it records information about a thing. A database stores and manages records so you can quickly find information and provide correct answers to questions.
JSON's strength and weakness is that it is completely flexible in how it represents data. Each JSON document may contain completely different data and structures with no restrictions on the labels and data types it uses to identify data. JSON also does not include important data types, such as date, time, timestamp, and binary data types, and does not distinguish between integers and floating-point numbers. These issues make it difficult for queries to get good answers from JSON data.
What allows a query to return correct answers?
Data queries return logically correct answers to queries, such as "What is the current balance on my account?" A consistent structure is necessary to query and index data accurately; otherwise, queries return incorrect answers.
For queries to work, data must be labeled and typed and have a predefined, uniform structure with required fields. A FairCom table meets these requirements. FairCom DB uses the consistent structure of its tables to index data correctly, ensure inserts and updates follow your rules, and answer queries accurately. In contrast, JSON data with inconsistent structure cannot provide correct answers to questions.
Querying JSON is problematic because properties may have unexpected data types, required properties may be missing, and properties may have inconsistent names. These problems prevent data from being indexed accurately and prevent queries from returning predictable, logically meaningful results.
Why can't a search return correct answers?
Search is another technology that can answer questions, and it works well on JSON documents with inconsistent structure.
You can search non-uniform JSON data using full-text indexes. A full-text index finds words, phrases, and key-value pairs located anywhere in JSON documents and strings. A full-text index is good at searching JSON documents and returning matches sorted by relevance. Unlike queries, searches do not return logically correct answers to specific questions; instead, they return the best matches they can find to vague questions.
A database that allows JSON records to have any structure can use full-text indexes to find information but cannot use queries and traditional indexes to answer questions correctly.
A JSON database can answer questions correctly and quickly only if it provides certain guarantees that JSON documents have a uniform structure that can be indexed using traditional B-tree indexes.
How can we bring structure to JSON documents without sacrificing flexibility?
FairCom DB brings structure to JSON by mapping top-level JSON properties to fields in a table. The table field name is the JSON property name. The field value is the property value. The field type maps to a JSON data type.
The FairCom JSON field, maps to any JSON value, typically a JSON object or array, but may be a JSON string, number, Boolean, or null.
The FairCom date, time, and timestamp fields map to a JSON string containing an ISO 8601 formatted date/time.
The FairCom binary field maps to a JSON string containing base64 or hexadecimal characters.
A
NULLvalue in a field maps to a JSONnullvalue or a missing JSON property.
Because FairCom table fields are strongly typed and create uniformly structured records, the top-level structure of JSON records are strongly typed and have a uniform structure, enabling JSON to provide accurate answers to queries.
The uniform structure allows you to create a B-tree index on each field in the table and on specific JSON properties in a JSON field. You can also create full-text indexes on JSON and text fields. These indexes allow you to answer questions quickly across millions of records.
FairCom also allows you to create dynamic, flexible data models within the structured table because you can include JSON fields in a table. A JSON document in a JSON field can be as flexible as you want. Within a JSON field, you may optionally use a JSON schema to constrain JSON structure to have any desired level of uniformity.
How can table data map to and from JSON data?
PERSON as a table and as JSON
This example shows how tabular data maps to JSON and vice-versa.
Fields in a table are top-level properties in JSON, and vice-versa. The field name is the property name. The field value is the property value. A JSON field in a table becomes a nested document in JSON. Because table fields are strongly typed, this makes the corresponding JSON properties strongly typed.
"id" must be a non-nullable, 64-bit integer, unique primary key identifier of the record.
"name" must be a non-nullable, variable-length string with a maximum length of 50 bytes.
"rank" must be an integer number or null.
"email" must be a JSON value or null.
Because of limitations in JSON, the DB API must return some field types as JSON strings, such as date, time, timestamp, numbers, id, etc. To help an application know how to process the real type of data embedded in JSON results, the "fieldDefs" property identifies the type of each property. For example, the "id" property is a 64-bit integer number field in the table. In the JSON DB API, it can be a number or a string. You can configure the JSON DB API to embed numbers in strings to be compatible with JSON parsers that cannot support huge numbers.
Note
The JSON DB API treats a missing JSON property and a JSON property with a null value as the same thing.