c-treeACE

Binary Record API

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

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

You can store non-nested binary data in a record.

The database can automatically index multiple segments of bytes in the record.

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 the database automatically indexes segments of binary data.

NAV API

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

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

You can store field data in a record.

The database automatically indexes fields.

You can find records by field values, and navigate across relational records in index and data order.

This is useful because the database allows you to work at the field level and it automatically handles the indexing of fields.

ANSI SQL API

SQL provides easy traditional access to relational data.

You can run SQL queries over binary data to 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 SQL makes data easy to manage, and you can use SQL to query indexes of binary data managed by the other APIs.

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.

This is useful for building custom clustering, adding communication protocols, embedding app servers, etc.

Callbacks

The database can call your C and C++ code synchronously or asynchronously at all critical points during 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 C, C++, Java, C#, Visual Basic, Node.JS JavaScript, Python, PHP, etc.

With these you can do everything with the database using your favorite language.

You can run SQL.

You can directly control all reads, writes, finding, saving, positioning, navigating, locking, and transacting at the row level.

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

The database stores and processes native CPU data types at machine speed.

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

Leverage the following machine types:

• Byte arrays up to 2GB
• 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
• Decimal FloatBinary Float
• Binary Double
• Scaled BCD

Use derived types that are implemented as native machine types, such as Money, CurrencyDate, Time, DateTime, JSONASCII strings up to 2 GBUTF-8 strings up to 2 GBUTF-16 strings up to 2 GB.

The benefit is that 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, 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 lower-level APIs.

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 is 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 very 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

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 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 Mac OS.

A single application and database can 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.

It is ideal for microservices that process tens of thousands of transactions per second because each microservice always has its own database linked directly into it, which eliminates latency, and greatly simplifies deployment.

It excels at running massively parallel analytics.

Even when the database is linked with an application, other, external applications can simultaneously communicate with the database when you enable the optional external communication protocols of shared memory and TCP/IP.

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 very 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 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

TCP/IP can connect databases with applications running on separate servers.

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.

This provides maximum flexibility and scalability for application and database deployments.

Many 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

Atomicity With Transaction Logs

ACID atomic transactions ensure one or more insert, update, and delete operations all succeed or all fail as a group without compromising any aspect of full 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 c-treeACE database maintains transaction logs and its tables log changes to them. These tables have atomic transactions.

Atomicity Without Transaction Logs

In a c-treeACE database that uses transaction logs, it is possible to configure a table not to use transaction logs and to still have atomic, isolated transactions. This unique ability is made possible by c-treeACE’s preimage technology, which provides atomicity and isolation without the performance cost of transaction logs.

This technology is useful when you need isolation and atomicity with extreme performance. This comes with the risk that an abnormal server termination may lose some data and may require rebuilding indexes. c-treeACE gives you the control to mitigate this risk by forcing the table and its indexes to write all changes immediately to disk while still caching them in RAM for high performance queries. You can also mitigate the risk by flushing the file’s cache to disk at key points or on a regular schedule.

c-treeACE’s preimage feature gives you the ability to have extreme write performance with atomicity, isolation and precise control over the level of durability.

c-treeACE’s preimage technology uses the best features of Multiversion Concurrency Control (MVCC) 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.

Custom Isolation

Once you turn on query isolation in a database by enabling transaction logging, you can selectively turn off query isolation for specific tables by disabling transaction logging for those tables.

Disabling transaction logging on specific tables is useful when tables are only accessed by one single-threaded user, such as a temporary table created for one user. It is also useful for tables that are never or rarely modified because there are no changes that require isolation. It is also useful when a table receives only inserts and you can afford to lose a few records if the server crashed.

Using Locks to Manage Isolation

Using the NAV API, you can control each read and write lock on each record (or entire tables) to create the ideal level of isolation during database reads and writes. Performance slows as isolation increases.

Read locks prevent other users from getting write locks on those records to ensure they cannot modify records while you are reading them. You can also use read locks to prepare a set of records for a batch update or delete.

Write locks prevent others from getting read or write locks on a record. You apply a write lock immediately before you intend to update or delete a record.

Implementing SQL Isolation Levels

The NAV API makes it easy to achieve various levels of Isolation.

To achieve Read Committed Isolation, you simply put a table under transaction control. The database automatically and quickly isolates reads without using read locks. When a table is not under transaction control, you need to apply read locks to each record you read.

To achieve Repeatable Read Isolation, you can apply a read lock to every record you read.

To achieve Serializable Isolation, you can apply a read lock to every record in a table.

Each increasing level of isolation further slows database performance.

You control which tables are placed under transactional control and you can use locks in creative ways to maximize performance and achieve the precise amount of isolation you require.

More Information

• Read and Write Locks
• ACID Isolation Levels

SQL Read Committed

SQL Read Committed is the default Isolation Level for SQL queries in c-treeACE. It ensures a SQL query in a transaction sees no data changes made by concurrent users, but if the query is repeated it will include committed transactions from other users who added, deleted, or updated rows.

This isolation level is faster than SQL Repeatable Read because it requires fewer locks.

More Information

• Set Transaction Isolation

SQL Repeatable Read

SQL Repeatable Read queries is an optional Isolation level for SQL queries in c-treeACE. It ensures a query in a transaction sees no data changes made by concurrent users, but if the query is repeated it will include committed transactions from other users who added or deleted rows, but not rows that were updated.

This isolation level is faster than using the NAV API to lock all records to create a serializable view of the data where nothing can change between repeated queries in the same transaction.

More Information

• Set Transaction Isolation