Cachalot DB - Very Fast Transactional Database for .NET Applications - Part 1

What is Cachalot DB?

Cachalot DB is a very fast, open source, NO SQL, fully transactional database for .NET applications. It is distributed, it scales linearly with the number of nodes. On a single node, you can durably insert fifty thousand objects per second on a modest system. A powerful LINQ provider is available as well as an administration console. It can also be used as a very powerful, transactional, distributed cache with unique features.

The fully open source code is available at:

• https://github.com/usinesoft/Cachalot

Precompiled binaries and full documentation are available at:

• https://github.com/usinesoft/Cachalot/releases/latest

The client code is available as nuget package at nuget.org.

To install: Install-Package Cachalot.Client

Much more detail in the next sections, but…

Show Me Some Code First

Let’s prepare our business objects for database storage.

We start with a toy web site which allows to rent homes between individuals.

A simple description of a real estate property would be:

public class Home
{
       public string CountryCode { get; set; }
       public string Town { get; set; }
       public string Adress { get; set; }
       public string Owner { get; set; }
       public string OwnerEmail { get; set; }
       public string OwnerPhone { get; set; }
       public int Rooms { get; set; }
       public int Bathrooms { get; set; }
       public int PriceInEuros { get; set; }
}

The first requirement for a business object is to have a primary key. As there is no “natural” one in this case, we will add a numeric Id.

public class Home
{
       [PrimaryKey(KeyDataType.IntKey)]
       public int Id { get; set; }

       °°°
}

Now the object can be stored in the database.

The first step is to instantiate a Connector which needs a client configuration. More on the configuration later but, for now, it needs to contain the list of servers in the cluster. To start, only one runs locally.

The configuration is usually read from an external file. For the moment, let’s build it manually.

var config = new ClientConfig
{
      Servers = {new ServerConfig {Host = "localhost", Port = 4848}}
};

using (var connector = new Cachalot.Linq.Connector(config))
{
      var homes = connector.DataSource<Home>();
      // the rest of the code goes here
}

One last step before storing an object in the database. We need to generate a unique value for the primary key. Multiple unique values can be generated with a single call.

Unlike other databases, you do not need to explicitly create a unique value generator. First call with a new generator name will automatically create it.

var ids = connector.GenerateUniqueIds("home_id", 1);

var home = new Home
{
       Id = ids[0],
       Adress = "14 rue du chien qui fume",
       Bathrooms = 1,
       CountryCode = "FR",
       PriceInEuros = 125,
       Rooms = 2,
       Town = "Paris"
};

homes.Put(home);

Now your first object is safely stored in the database.

For the moment, you can only retrieve it by primary key. That can be done in two equivalent ways.

var reloaded = homes[home.Id];

Or with a LINQ expression.

var reloaded = homes.First(h => h.Id == home.Id);

The first one is faster as there is no need to parse the expression tree.

In most relational databases, we use two distinct operations: INSERT and UPDATE. In Cachalot DB, only one operation is exposed: PUT.

It will insert new items (new primary key) and will update existing items.

You probably have higher expectations from a modern database than simply storing and retrieving objects by primary key. And you are right.

Other Types of Indexes and How to Use Them

Three characteristics of an index need to be understood.

1. Index Data Type

In order to be indexable in Cachalot DB, a .NET property needs to be convertible either to Int64 or string.

Using integer type makes the search slightly faster.

Automatic conversion to Int64 is provided for:

• All numeric types
• DateTime and DateTimeOffset
• Enumerated types
• Boolean

Conversion to string is done by calling ToString() on the property value.

2. Index Type

Three types of indexed properties are available:

• Primary key (the only mandatory one)
• Unique key: zero or more can be defined on type
• Index key: zero or more can be defined on type

3. Ordered Index

On any index, we can apply the equality operator.

If an index is declared as “ordered”, all the comparisons operators can equally be applied: <, <=, >, >=

This type of index is essential for most modern systems but be aware that it has a cost because the ordered indexes must always be sorted.

Massive insert/update operations (DataStore.PutMany method) are well optimized. After a threshold is reached (50 items by default), the operation is treated like a “bulk insert”. Ordered indexes are sorted only once, at the end.

More Code. Adding Indexes to the Real Estate Property

public class Home
{
       [PrimaryKey(KeyDataType.IntKey)]
       public int Id { get; set; }

       [Index(KeyDataType.StringKey)]
       public string CountryCode { get; set; }

       [Index(KeyDataType.StringKey)]
       public string Town { get; set; }
       public string Adress { get; set; }
       public string Owner { get; set; }
       public string OwnerEmail { get; set; }
       public string OwnerPhone { get; set; }

       [Index(KeyDataType.IntKey, or dered:true)]
       public int Rooms { get; set; }

       [Index(KeyDataType.IntKey)]
       public int Bathrooms { get; set; }

       [Index(KeyDataType.IntKey, ordered:true)]
       public decimal PriceInEuros { get; set; }
}

With an object indexed like this, you can now do some useful queries:

var results = homes.Where(p => p.PriceInEuros <= 200 &&
p.Rooms > 1 &&
p.Town == "Paris").Take(10);

The query is, of course, executed server-side including the take operator. At most, ten objects are sent to the client through the network.

The “Contains” extension method is also supported.

var towns = new[] {"Paris", "Nice"};
var one  = homes.First(p => p.PriceInEuros < 150 && towns.Contains(p.Town));

This is equivalent to the SQL:

SELECT * from HOME where PriceInEuros < 150 and Town IN ("Paris", "Nice")

Another use of the “Contains” extension, which does not have any equivalent in traditional SQL, is explained in the next section.

Indexing Collection Properties

Let’s enrich our business object. It would be useful to have the list of available dates on each home.

Adding this new property enables some interesting features.

This is a collection property and it can be indexed the same way as the scalar properties.

[Index(KeyDataType.IntKey)]
public List<DateTime> AvailableDates { get; set; } = new List<DateTime>();

We would like to be able to search for homes available on a specific date.

var availableNextWeek = homes.Where(
       p => p.Town == "Paris" &&
       p.AvailableDates.Contains(DateTime.Today.AddDays(7))
       ).ToList();

This has no direct equivalent in the classical SQL databases. It conveniently replaces most of the uses for the classical JOIN operator.

You may need to dynamically create a query. For example, in a search screen, you add criteria to restrict your results. This can be done by chaining Where methods.

var query = homes.Where(p => p.Town == "Paris");
query = query.Where(p => p.PriceInEuros < 200);
query = query.Where(p => p.Rooms > 1);
query = query.Where(p => p.AvailableDates.Contains(DateTime.Today));
var result = query.ToList();

This is equivalent to a single query where all criteria are joined with the && (AND) operator.

This is only a brief introduction. The full user guide, including an administration section is available at:

• https://github.com/usinesoft/Cachalot/blob/master/Doc/CachalotUserGuide.pdf

The second part in the series is available here 

History

• 4^th February, 2019: Initial version