PHP

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 PHP 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.

You’ll also need to install php-odbc packages:

sudo apt-get install php7.4-odbc

You should change php7.4-odbc with the version of PHP you’re using.

Note that you cand find a PDO LX ODBC module along with the odbc package. You will need to install the extension pdo_lxodbc.so If you want to use this instead of standard PDO ODBC extension.

sudo cp pdo_lxodbc.so.7.4 $(php-config --extension-dir)/pdo_lxodbc.so
echo "extension=pdo_lxodbc.so" > pdo_lxodbc.ini
sudo mv pdo_lxodbc.ini /etc/php/7.4/mods-available
sudo ln -s /etc/php/7.4/mods-available/pdo_lxodbc.ini $(php-config --ini-dir)/20-pdo_lxodbc.ini

Then you have to select the lxodbc PDO’s driver when connecting through the PDO:

$pdoConnection = new PDO("lxodbc:$dsn");

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 file in previous steps:

$dsn = 'LeanXcale';

try {
    $pdoConnection = new PDO("odbc:$dsn");

    // I want PDO to throw exceptions
    $pdoConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Problem while connecting to the database: " . $e->getMessage());
}

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 PHP will automatically close the connection when your script ends, but you can close it by hand if you need so:

$pdoConnection = null;

2.2.2. Creating a table

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

$createTableSQL = "CREATE TABLE persons (
            socialsecurity VARCHAR(15),
            firstname VARCHAR(40),
            lastname VARCHAR(40),
            city VARCHAR(50),
            PRIMARY KEY (socialsecurity)
        )";
$stm = $pdoConnection->exec($createTableSQL);

2.2.3. Inserting, updating and deleting

Inserting one Record:

Inserting one row is a very easy task:

$insertSQL = "INSERT INTO persons (socialsecurity, firstname, lastname, city)
         VALUES
         ('728496923-J', 'John', 'Smith', 'San Francisco')";
$stm = $pdoConnection->query($insertSQL);

Or with prepared statements:

$insertSQL = "INSERT INTO persons (socialsecurity, firstname, lastname, city)
         VALUES
         (:ss, :fn, :ln, :city)";
$stm = $pdoConnection->prepare($insertSQL);
$stm->execute([
  ':ss'    => '7691241241-Z',
  ':fn'   => 'Carl',
  ':ln'   => 'Parson',
  ':city' => 'New York'
]);

Updating a Record:

$updateSQL = "UPDATE persons SET city='Madrid' WHERE city='San Francisco'";
$numAffectedRows = $pdoConnection->exec($updateSQL);

Deleting a Record:

$deleteSQL = "DELETE FROM persons WHERE city='New York'";
$numAffectedRows = $pdoConnection->exec($deleteSQL);

2.2.4. Reading and scanning Data

$selectSQL = "SELECT * FROM persons WHERE city='Madrid'";
$stm = $pdoConnection->query($selectSQL);
$results = $stm->fetchAll();

foreach ($results as $row) {
  echo "\nSocial security number: " . $row['SOCIALSECURITY'];
  echo "\nFirst name: " . $row['FIRSTNAME'];
  echo "\nLast name: " . $row['LASTNAME'];
  echo "\nCity: " . $row['CITY'] . "\n";
}

3. KiVi

3.1. Install

Download the Leanxcale Development Libraries from the Drivers page and unpack it.

Copy the file 20-lxphpapi.ini into the PHP’s ini directory, usually at /etc/php/7.X/cli/conf.d. Check php --ini for additional ini files.

sudo cp 20-lxphpapi.ini /etc/php/7.X/cli/conf.d/

If instead of running your application from the command line, you’re using PHP with Nginx, the configuration file must be copied to /etc/php/7.X/fpm/conf.d. If you’re using Apache you should copy the file to /etc/php/7.X/apache2/conf.d.

You must replace 7.X with the version of PHP you’re using, such as 7.4.

Copy all the libraries from the package to your system path or add them to LD_LIBRARY_PATH. In Ubuntu you could do:
```
sudo cp *.so* /lib/x86_64-linux-gnu
```
You need to be sure that the user that runs PHP has read and execution permissions to all the libraries copied.

Create a symbolic link to lxphpapi-7.2-1.9.9.so into the PHP’s extension directory:

sudo ln -s /lib/x86_64-linux-gnu/lxphpapi-7.2-1.9.9.so  /usr/lib/php/20190902/lxphpapi.so

If you’re not sure where your PHP extensions directory is, you can use the php-config command that’s inside the php-dev package and run php-config --extension-dir.

If you’re using our PHP extension from Nginx (FPM) or Apache, you’ll need to restart the PHP service to enable the extension. In Ubuntu this is done running sudo service php7.X-fpm restart or sudo service apache2 restart.

If everything went ok, you should see our lxphpcppapi extension (between others) correctly installed:

user@host:~$ php -m
[PHP Modules]
[...]
lxphpcppapi
[...]

3.2. Quick Start

With this PHP 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.

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:

$connstr = "lx://3aa41ecf534cc50a2c75.lxc-db.com:9876/db@APP;CERT=/home/user/certificate.kcf";
echo "Connect to $connstr\n";

$connection = lxconnect($connstr);

As you can see, if your LeanXcale instance is configuring with security, you will have to provide a valid path to a certificate. Learn how to generate it here.

Note that it is only possible to have one active session with the KiVi API.

In the next examples we will not include the session creation, but this is needed in all of them to be able to connect to LeanXcale.

3.2.2. Creating a table and its indexes

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

$session = $connection->session();

$tablename = "person";
$ufmt = "l[k0]sssssssyff";

$fnames = [
  "personId",
  "dni",
  "name",
  "lastName",
  "address",
  "phone",
  "email",
  "comment",
  "birthday",
  "weight",
  "height"
];

$session->createTable($tablename, $ufmt, $fnames);

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.

3.2.3. Inserting, updating and deleting

Inserting one Record:

$session = $connection->session();

$collection = $session->table($tablename);
$tupleBuilder = $collection->tupleBuilder();

$rowJohn = [
  1,
  "123456789A",
  "John",
  "Doe",
  "No comments",
  "Mulholland Drive",
  "555333695",
  "johndoe@nowhere.no",
  "1970-01-01",
  70,
  179
];
echo "Add values to tuple\n";
$tupleBuilder->addValues($rowJohn);
echo "Insert tuple\n";
$collection->insert($tupleBuilder);
echo "End session\n";
$session->end();

Insert using a Sequence:

// TODO

Insert a Record containing a BLOB read from a file:

Note that you will need to disable autocommit before inserting a blob.

// TODO

Updating a Record:

$tupleBuilder->addTuple($tuple);
$tupleBuilder->add(9, 75);

$collection->update($tupleBuilder);

Deleting a Record:

$collection = $session->table($tablename);
$keyBuilder = $collection->keyBuilder();

$keyValues = [1];
$keyBuilder->addValues($keyValues);

$collection->remove($keyBuilder);

Delete with a Filter:

// TODO

3.2.4. Reading and scanning Data

Getting a Data Row directly by Key:

$collection = $session->table($tablename);
$keyBuilder = $collection->keyBuilder();

$keyValues = [1];
$keyBuilder->addValues($keyValues);
$tuple = $collection->get($keyBuilder);

if ($tuple->isNull()) {
  throw new Exception('Tuple is null');
}
$fno = 2;
$fieldValue = $tuple->get($fno);
if (strcmp($fieldValue, "John") !== 0) {
  throw new Exception("Not the expected value but {$fieldValue}");
}

Simple SCAN with basic options:

// TODO

Scan a Table using a Secondary Index:

// TODO

Advanced Finding and Filtering

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

// TODO

Project:

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

// TODO

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.

// TODO

3.3. Error Checking

Each command executed through the PHP-KiVi extension returns an integer with the return code. When the command is executed successfully, the return code will be 0. When the command ends with errors, the return code will be a negative number. So you can use this piece of code and the lxLastError() function to know if something went wrong and why:

    $resultInsert = $people->insert($tuple);

    if ($resultInsert < 0) {
      echo "Error inserting data into the table ($resultInsert): " . lxLastError();
      return;
    }

3.4. 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.4.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.4.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.