.NET (C#)

1. Two interfaces to access the same data

LeanXcale is a SQL database with NoSQL characteristics. As a relational key-value database, LeanXcale provides access to the same data through two independent APIs:

A 2003 ANSI SQL interface that is powerful and easy to use, but can be slow, so is most suitable for complex queries (e.g., multijoin queries).
A proprietary key-value interface, called KiVi, is fast and provides all functionality except for join operations, so it is most suitable for simpler queries where the overhead of SQL is too costly (e.g., updates and inserts).

Both interfaces interact with the same data and can be part of the same transaction.

2. SQL

2.1. Prerequisites

The SQL interface in LeanXcale is exposed to C# through our ODBC connector, so you’ll need to install it first. Please, refer to the documentation to install the ODBC connector for Windows or Linux and then come back here to continue configuring the connection.

2.2. Quick Start

2.2.1. Connecting to LeanXcale

The first thing to do is connect to the database using the same DSN name we put in odbc.ini/.odbc.ini file in previous steps:

      string connetionString = "DSN=LeanXcale";
      OdbcConnection connection = new OdbcConnection(connetionString);
      connection.Open();

Keep in mind that if you are connecting to a LeanXcale database working with security, you will need some changes in your ODBC configuration. Check them here.

Remember that C# will automatically close the connection when your script ends, but you can close it by hand if you need so:

      connection.Close();

2.2.2. Creating a table

To create a new table in LeanXcale, you can use the following code:

      command = new OdbcCommand("CREATE TABLE persons (socialsecurity VARCHAR(15),"
            + "firstname VARCHAR(40), lastname VARCHAR(40), city VARCHAR(50),"
            + "PRIMARY KEY(socialsecurity))", connection);

      command.ExecuteNonQuery();

2.2.3. Inserting, updating and deleting

Inserting one Record:

Inserting one row is a very easy task:

      command = new OdbcCommand("INSERT INTO persons (socialsecurity, firstname, lastname, city)"
               + "VALUES ('728496923-J', 'John', 'Smith', 'San Francisco')", connection);

      command.ExecuteNonQuery();

Or with prepared statements:

      command = new OdbcCommand("INSERT INTO persons (socialsecurity, firstname, lastname, city)"
             + "VALUES (?, ?, ?, ?)", connection);
      command.Prepare();
      command.Parameters.Add("@ss", OdbcType.VarChar).Value = "7691241241-Z";
      command.Parameters.Add("@fn", OdbcType.VarChar).Value = "Carl";
      command.Parameters.Add("@ln", OdbcType.VarChar).Value = "Parson";
      command.Parameters.Add("@ct", OdbcType.VarChar).Value = "New York";

      command.ExecuteNonQuery();
      command.Parameters.Clear();

Updating a Record:

      command = new OdbcCommand("UPDATE persons"
               + " SET city = 'Madrid'"
               + " WHERE city = 'San Francisco'", connection);

      command.ExecuteNonQuery();

Deleting a Record:

      command = new OdbcCommand("DELETE FROM persons"
               + " WHERE city = 'New York'", connection);

      command.ExecuteNonQuery();

2.2.4. Reading and scanning Data

      command = new OdbcCommand("SELECT * FROM persons"
               + " WHERE city = 'Madrid'", connection);

      OdbcDataReader reader = command.ExecuteReader();
      while (reader.Read())
      {
        Console.WriteLine("Social security number: {0}", reader[0]);
        Console.WriteLine("First name: {0}", reader[1]);
        Console.WriteLine("Last name: {0}", reader[2]);
        Console.WriteLine("City: {0}", reader[3]);
      }
      reader.Close();

3. KiVi

3.1. Install

You can install the NuGet package using the Visual Studio NuGet Package Manager. You can open it right-clicking over your project, then clicking "Manage NuGet Packages…":

Once the NuGet Package Manager opens, you can click on the gear icon to add our repository to the project:

On the screen that appears, you can see your current repositories. To add the LeanXcale .NET repository, click on the green (+) icon at the top right:

That will create a new row, and you have to put the LeanXcale Repository and https://nexus.leanxcale.com/repository/nuget-hosted/ as values and click the Update and OK buttons:

Once you’ve added the LeanXcale Repository to Visual Studio, choose it in the package manager at the top-right and then install the version that matches your LeanXcale version by clicking on the arrow at the side of the number as in this image:

If Visual Studio warns you that it’s about to make changes to your solution, just click the "OK" button:

If you prefer installing the driver from the Package Manager command line or the .NET CLI, you can do it too:

// Package Manager
PM> Install-Package LeanXcale.KiVi.NET.Driver -Version 1.9.9

// .NET CLI
> dotnet add package LeanXcale.KiVi.NET.Driver --version 1.9.9

If you prefer downloading the driver by hand to install it by other means, you can do it in our Drivers page, looking for .NET (C#) KiVi Direct API (nupkg).

After you’ve installed the NuGet package, you must install the platform specific driver. We have drivers for Windows and Linux.

3.1.1. Windows Installation

Download the .NET (C#) KiVi Direct API for Windows from our Drivers page and execute the file:

It will install the Visual C++ Redistributable libraries if you don’t have them installed:

Just click on "I agree" and then on "Install".

If you get a screen like this, don’t worry and click "Close", as it means that you already have the libraries installed:

Visual C++ Redistributable x64 Already installed

After that, the Windows driver for LeanXcale starts to install. Just click "Next" on the following screens accepting the default values:

To finish the installation process, click "Close".

You will need to add the installation path (C:\Program Files\Leanxcale\lx-odbc-driver if you didn’t change it) to the PATH system environment variable. To do so, open the Control Panel and go to the Environment Variables dialog:

Double-click over "Path" in "System variables":

And add the new value:

3.1.2. Linux Installation

Download Leanxcale Development Libraries from the Drivers page and unpack it.
Copy the libraries libphpcpp.so, libckvapi.so and libcppkvapi.so to your system path or add them to LD_LIBRARY_PATH. In Ubuntu you could do:
```
sudo cp libphpcpp* /lib/x86_64-linux-gnu
sudo cp libckvapi* /lib/x86_64-linux-gnu
sudo cp libcppkvapi* /lib/x86_64-linux-gnu
```

3.2. Quick Start

With this C# driver you’ll be using the LeanXcale KiVi proprietary key-value interface, so you get the full speed while reading, inserting and updating information without the SQL overhead. You’ll be using the KiVi .NET Direct API. You can read the API here.

The first thing you’ll need to do to start using the API in your application is start "using" it:

using lxapi;

3.2.1. Connecting and starting a session

In order to connect to the KiVi API for LeanXcale you’ll need some basic information:

If you are using LeanXcale as a Cloud Service, you can find the IP or hostname for your instance in the LeanXcale Cloud Console.
If you’re using LeanXcale on-premise, you can use the information from your installation.
In case you have enabled cryptography, you have to ensure that the application process have access to the user’s certificate.

In our examples we will use these parameters:

IP / hostname: 123.45.67.89
Lxis port: 9876

We will connect using this piece of code:

      Connection conn = Connection.connect("lx://" + ip + ":" + port + "/db@APP;kvnat=auto;user=" + uid);

In case you are connecting to a LeanXcale database with Security, you must provide the connection with a valid path to an application certificate.

Connection conn = Connection.connect("lx://" + ip + ":" + port + "/db@APP;kvnat=auto;user=" + uid + ";cert=/path/to/certificate/file.kcf");

Learn how to generate your certificate here.

Note that it is only possible to have one active network connection with the KiVi API. However many Connection instances may share the same network connection.

3.2.2. Creating a table and its indexes

To create a new table in LeanXcale and add indexes to it, you can use the following code:

      using (Session session = conn.session())
      {
        string[] fnames = { "personId", "dni", "name", "lastName", "address", "phone",
                        "email", "comment", "birthday", "weight", "height", "photo" };
        session.createTable(tablename, "l[k]sssssssyffm", fnames);

        // Non unique
        ushort[] phoneposition = { 5 };
        session.createIndex(tablename, "phoneidx", 1, phoneposition);

        //Unique
        ushort[] dniposition = { 1 };
        session.createIndex(tablename, "dniidx", 1, dniposition, (int)IndexCreationFlags.IDXUNIQ);
      }

The variable ufmt defines the structure of the table. To see how you can define the structure of your tables, give a look at the section KiVi Tuple Format below.

As you can see in the API documentation, you’re specifying in the parameters:

the name of the table
the name of the index to create
the number of columns to index
the position of columns to index
an optional parameter with the index type. If left unspecified, it will be a standard index. The other index types you can specify are constants defined in IndexCreationFlags.

3.2.3. Inserting, updating and deleting

Inserting one Record:

      TupleBuilder builder;
      using (Session session = conn.session())
      {
        using (Table table = session.table(tablename))
        {
          int jimbirthday = (int)(new DateTimeOffset(new DateTime(2010, 12, 12)).ToUnixTimeSeconds() / 86400);
          object[] jimrow = { 0, null, "Jim", "Doe", "No comments",
                        "Mulholland Drive", null, null, jimbirthday, 50, 3.26, null };
          table.insert(jimrow);

          // With Builder      
          builder = table.tupleBuilder();

          int johnbirthday = (int)(new DateTimeOffset(new DateTime(1975, 1, 1)).ToUnixTimeSeconds() / 86400);
          object[] johnrow = { 1, "123456789A", "John", "Doe", "No comments",
                        "Mulholland Drive", "555333695", "johndoe@nowhere.no", johnbirthday, 70, 1.79, null };
          builder.add(johnrow);
          table.insert(builder);

Insert using a Sequence:

          session.createSequence(seqname, 100, 2, 10);// start at 2, increment by 10

          ulong next_value = session.seqNextValue(seqname);
          Console.WriteLine("Next value: {0}", next_value);
          // reuse builder
          builder.add(0, next_value).add(1, "912345678B").add(2, "Jane");
          int janebirthday = (int)(new DateTimeOffset(new DateTime(1972, 10, 7)).ToUnixTimeSeconds() / 86400);
          builder.add(6, "0034698765432").add(7, "janedoe@lx.com").add(8, janebirthday)
                 .add(9, 75).add(10, 1.79);

          table.upsert(builder);

Updating a Record:

          builder.add(tableTuple);
          builder.add(9, 62.7); // change weight

          table.update(builder);

Deleting a Record:

        Console.WriteLine("simpleDelete");
        key = keyBuilder.add(0, 2).buildKey(table);

        table.delete(key);
        Console.WriteLine("simpleDelete end");

Delete with a Filter:

        using (Scan scan = new ScanProperties(table).maxrows(2).buildScan())
        {
          scan.begin();
          lxapi.Tuple tpl = scan.next();
          while (tpl != null)
          {
            table.delete(keyBuilder.add(0, tpl.getLong(0)));
            tpl = scan.next();
          }
        }

3.2.4. Reading and scanning Data

Getting a Data Row directly by Key:

        using (Table table = session.table(tablename))
        {

          keyBuilder = table.keyBuilder();

          lxapi.Tuple tableTuple = table.get(keyBuilder.add(0, 2));
          for (int inx = 0; inx < table.info.numOfFields() - 1; inx++)
          {
            if (!tableTuple.isNull(inx) && !tableTuple.isIgnore(inx))
            {
              Console.WriteLine("Field #{0} -> {1}", inx, tableTuple.getString(inx));
            }
          }

Simple SCAN with basic options:

        Table table = session.table(tablename);

        using (Scan scanmaxrows = new ScanProperties(table).maxrows(2).buildScan())
        {
          scanmaxrows.begin();
          lxapi.Tuple tuple = scanmaxrows.next();
          int count = 0;
          while (tuple != null)
          {
            count++;
            tuple = scanmaxrows.next();
          }
          if (count != 2)
          {
            throw new Exception("Not the expected num of rows but " + count);
          }
        }

Scan a Table using a Secondary Index:

        using (lxapi.Index index = table.index("dniidx"))
        {
          lxapi.Tuple min = index.tupleBuilder().add(0, "111111111Q").build(index);

          using (Scan scan = new ScanProperties(index).skRange(min, null, 0).buildScan()) { }
        }

Advanced Finding and Filtering

The following example show how to define a pipeline with a filter.

        Expression expr = Expression.fieldFloat(9).op(Expression.Op.KVLE, new Expression((double)70));

        using (Scan scan = new ScanProperties(table).addPredicate(expr).maxrows(10).buildScan()) { }

Project:

The project may also include operations over the fields to be retrieved.

        ushort[] projection = { 1, 2, 3 };// dni, name, lastname
        using (Scan scan = new ScanProperties(table).project(projection).buildScan()) { }

Aggregations:

First, you need to define an array of fields(or expressions) to be used as the group by key (if any), and then the list of aggregation expressions.

        Expression[] groupbyexprs = new Expression[3];
        groupbyexprs[0] = Expression.fieldStr(3);
        groupbyexprs[1] = Expression.fieldFloat(10).op(Expression.Op.KVMUL, new Expression((double)100));
        groupbyexprs[2] = Expression.aggr(Expression.Op.KVMAX, Expression.fieldFloat(9));

        using (Scan scan = new ScanProperties(table).groupBy(groupbyexprs).buildScan()) { }

3.3. Appendix A: KiVi Tuple format

While using KiVi, each row that you insert is called a Tuple. When you define the structure of the table, you’re also defining the format for the tuples that will be inserted on the table.

You can specify tuple formats using format strings. A format string uses one character per field, showing its type, followed optionally by options enclosed in [].

For example, we could have this tuple format variable:

$ufmt = "l[k0]sssyffm";

Here we have the definition of a table with 8 fields whose first field is a TLong that’s also the first (0) primary key for the table, followed by three Tstr fields, one Tdate, two Tfloat and one Tmem field.

3.3.1. KiVi Field Types

Type	Char to use	Spec
Tbool	b	int8_t (boolean)
Tbyte	c	int8_t
Tshort	h	int16_t
Tenum	e	int16_t (static name enumeration)
Tint	i	int32_t
Ttime	t	int32_t (total nb of seconds)
Tdate	y	int32_t (days since epoch)
Tfloat	f	float
Tstr	s	string field
Tdec	r	decimals (and big integers)
Tjson	j	JSON text
Tmem	m	raw bytes
Tlong	l	int64_t
Tts	w	int64_t (time stamp in microseconds since epoch)
Tdouble	d	double

Type

Char to use

Spec

Tbool

int8_t (boolean)

Tbyte

int8_t

Tshort

int16_t

Tenum

int16_t (static name enumeration)

Tint

int32_t

Ttime

int32_t (total nb of seconds)

Tdate

int32_t (days since epoch)

Tfloat

float

Tstr

string field

Tdec

decimals (and big integers)

Tjson

JSON text

Tmem

raw bytes

Tlong

int64_t

Tts

int64_t (time stamp in microseconds since epoch)

Tdouble

double

3.3.2. KiVi Field Options

Field options can be written together (within '[]') or separated by commas or a space:

Option	Description
!	adds the following text up to the end of the options as a user spec for the field. Its meaning is up to the user. Must be the last option.
B	use the field for bloom filtering in the table.
b	ask for a blob field. Valid only for string, memory, and JSON fields. Data may be placed in external storage, or not. See also x.
c	flags the field as desired for column formats.
D	flags the field as gone; implementation might actually remove the data for the field, or not. Tuples still carry the field, perhaps as a null value, for consistency.
d	flags the field as a delta field for adding values. Delta decimals require a number.number option too.
dm	flags the field as a delta field for min value.
dM	flags the field as a delta field for max value.
e	flags the (string) field as enumerable at run time (dictionary)
i	flags the (string) field as case insensitive
k	flags the field as a key field. The position in the key (counting from 0) should follow right after the option (no spaces). If no position is given, the first unused one is used.
ks	flags the field as a key field that is a split-point field. The position in the key (counting from 0) should follow right after the option (no spaces). Split fields must be at the end of the key.
h	flags the field as hashed one to compute the value of a hash field. It must be a key field.
H	flags the field as a hash field computed from hashed fields. It must be a key field.
l	shows that a string field uses the locale with the name that follows from the flag up to the end of the options or the start of the user spec option. Thus, it must be the last option but for the user spec.
n	flags the field as non-null
u	flags the (string) field as always upper case. But see the note below.
x	ask for a blob field with external storage. Valid only for string, memory, and JSON fields. Like b, but data will always be placed in external storage.
number	Sets the maximum size desired for a string or memory field, or the number of decimal positions for decimals. The first character not a digit terminates this option.
number.number	A number with a decimal sets the maximum number of digits for a decimal and the desired number of decimal positions. This is to be used for delta decimal fields, in most other cases decimals may shrink and grow in size without much trouble.

Option

Description

adds the following text up to the end of the options as a user spec for the field. Its meaning is up to the user. Must be the last option.

use the field for bloom filtering in the table.

ask for a blob field. Valid only for string, memory, and JSON fields. Data may be placed in external storage, or not. See also x.

flags the field as desired for column formats.

flags the field as gone; implementation might actually remove the data for the field, or not. Tuples still carry the field, perhaps as a null value, for consistency.

flags the field as a delta field for adding values. Delta decimals require a number.number option too.

flags the field as a delta field for min value.

flags the field as a delta field for max value.

flags the (string) field as enumerable at run time (dictionary)

flags the (string) field as case insensitive

flags the field as a key field. The position in the key (counting from 0) should follow right after the option (no spaces). If no position is given, the first unused one is used.

flags the field as a key field that is a split-point field. The position in the key (counting from 0) should follow right after the option (no spaces). Split fields must be at the end of the key.

flags the field as hashed one to compute the value of a hash field. It must be a key field.

flags the field as a hash field computed from hashed fields. It must be a key field.

shows that a string field uses the locale with the name that follows from the flag up to the end of the options or the start of the user spec option. Thus, it must be the last option but for the user spec.

flags the field as non-null

flags the (string) field as always upper case. But see the note below.

ask for a blob field with external storage. Valid only for string, memory, and JSON fields. Like b, but data will always be placed in external storage.

number

Sets the maximum size desired for a string or memory field, or the number of decimal positions for decimals. The first character not a digit terminates this option.

number.number

A number with a decimal sets the maximum number of digits for a decimal and the desired number of decimal positions. This is to be used for delta decimal fields, in most other cases decimals may shrink and grow in size without much trouble.