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

The SQL interface in LeanXcale is exposed to PHP through the ODBC driver, so you’ll need to install it first:
2.1. ODBC Linux Installation
The next steps show how to install the LeanXcale ODBC Driver for Linux. This procedure was tested on Ubuntu 20.04 LTS and is expected to run on other distributions.
-
Install the prerequisite packages:
sudo apt-get install unixodbc unixodbc-dev
The
unixodbc-dev
package is used by some extensions that need to be compiled before being installed. -
Download and unpack the ODBC Connector from the Drivers page.
-
Make sure that all required dependencies are met:
ldd libodbclx.so
For Ubuntu 20.04 the required libprotobuf_c can be installed if missing in the following way:
wget http://archive.ubuntu.com/ubuntu/pool/universe/p/protobuf-c/libprotobuf-c-dev_1.3.3-1_amd64.deb apt install -y ./libprotobuf-c-dev_1.3.3-1_amd64.deb
-
Copy the file
libodbclx.so
into the/opt/leanxcale/odbc/client
directory:mkdir -p /opt/leanxcale/odbc/client cp libodbclx.so /opt/leanxcale/odbc/client
Make sure that the PHP user has read and execute permissions for
libodbclx.so
.For example:
chmod +rx www-data /opt/leanxcale/odbc/client/libodbclx.so
-
Create the file
$HOME/.odbcinst.ini
with the following details:[LeanXcaleODBCDriver] Description = LeanXcale ODBC Driver for Linux Driver = /opt/leanxcale/odbc/client/libodbclx.so UsageCount = 1
-
Create the file
$HOME/.odbc.ini
with these details:[LeanXcaleTests] Driver = LeanXcaleODBCDriver Description = Sample Database to be used for tests Trace = No Server = 123.45.67.89 # IP / hostname of your LeanXcale instance Port = 1529 Database = dbtest UserName = user1 Password = pass4user1 Encoding = WCHAR_T
By default, ODBC driver managers are configured to use hidden versions of the If you store these configuration files elsewhere, then you must set the following environment variables:
The driver manager then must be able to locate these files. |
2.2. ODBC Windows Installation
2.2.1. Installing the ODBC Connector
-
Download the ODBC Connector for Windows from the Drivers page
-
Run the installer
lx-odbc-setup-1.6.6.exe
-
The default installation directory is
C:\Program Files\Leanxcale\lx-odbc-driver
-
If asked to install the Visual C++ redistributable, then follow the default setup guide by simply clicking Next as needed.
2.2.2. Registry Keys
The setup adds the following relevant keys to the registry:

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI]
[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers]
"LX DRIVER(x64)"="Installed"
[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\LX DRIVER(x64)]
"Driver"="[TARGETDIR]LXODBC.DLL"
"Setup"="[TARGETDIR]LXODBCSETUP.DLL"
"APILevel"="1"
"ConnectFunctions"="YYN"
"DriverODBCVer"="03.00"
"FileUsage"="0"
"SQLLevel"="3"
2.2.3. ODBC Data Source Administrator
After the driver is installed, add the data source with the ODBC Data Source Administrator from the User DSN tab:.
-
Click Add
-
Select LX DRIVER(x64) from the driver list
With the correct driver selected, you can now configure the Data Source Name properties:

Property | Description | Sample Values | Comment |
---|---|---|---|
Data Source |
Identifier for the datasource |
ODBC logical name |
|
Description |
User friendly description of the database |
||
Database name |
Identifier for the database |
||
SSL mode |
SSL mode to use for connection |
|
Not yet supported |
Host |
LeanXcale server DNS or IP |
||
Port |
LeanXcale server access port |
||
User |
Login to database |
||
Password |
Database password |
||
Trace |
Error reporting level |
|
|
TraceFile |
Location of error log file |
|
|
Encoding |
|
||
ANSI Encoding |
|
The Windows ODBC driver is an ANSI driver that also supports Unicode data. |
-
Install
php-odbc
packages:
sudo apt-get install php7.4-odbc
You should change |
2.3. Quick Start
2.3.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:
$dsn = 'LeanXcaleTests';
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());
}
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.3.2. Creating a table
To create a new table in LeanXcale, you can use the following code:
try {
$createTableSQL =
"CREATE TABLE persons (
socialsecurity VARCHAR(15),
firstname VARCHAR(40),
lastname VARCHAR(40),
city VARCHAR(50),
PRIMARY KEY (socialsecurity)
)";
$stm = $pdoConnection->exec($createTableSQL);
} catch (PDOException $e) {
echo "Problem creating table: " . $e->getMessage();
}
2.3.3. Inserting, updating and deleting
Inserting one Record:
Inserting one row is a very easy task:
try {
$insertSQL =
"INSERT INTO persons (socialsecurity, firstname, lastname, city)
VALUES
('728496923-J', 'John', 'Smith', 'San Francisco')";
$stm = $pdoConnection->query($insertSQL) ;
} catch (PDOException $e) {
echo "Problem inserting a row: " . $e->getMessage();
}
Or with prepared statements:
try {
$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'
]);
} catch (PDOException $e) {
echo "Problem inserting a row: " . $e->getMessage();
}
2.3.4. Reading and scanning Data
try {
$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";
}
} catch (PDOException $e) {
echo "Problem selecting all rows: " . $e->getMessage();
}
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
. Checkphp --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.6.6.so
into the PHP’s extension directory:sudo ln -s /lib/x86_64-linux-gnu/lxphpapi-7.2-1.6.6.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 runphp-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
orsudo 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 our examples we will use these parameters:
-
IP / hostname:
123.45.67.89
-
Lxis port:
9876
We will connect using this piece of code:
$uid = "user123";
$metaadress = "123.45.67.89:9876";
echo "Connect to $metaadress\n";
lxDial($uid, $metaadress);
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:
$tid = lxBegin();
$tablename = "person";
$ufmt = "l[k0]sssssssyffm";
$fnames = [
"personId",
"dni",
"name",
"lastName",
"address",
"phone",
"email",
"comment",
"birthday",
"weight",
"height",
"photo"
];
lxNewTbl($tid, $tablename, $ufmt, $fnames);
// Non unique
lxNewIdx($tid, $tablename,"phoneIdx", false);
//Unique
lxNewIdx($tid, $tablename,"phoneIdx", true);
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:
$tid = lxBegin();
$people = new LxCollection($tid, $tablename);
$tupleBuilder = new LxTupleBuilder($people);
$person = [
1,
"123456789A",
"John",
"Doe",
"No comments",
"Mulholland Drive",
"555333695",
"johndoe@nowhere.no",
"1970-01-01",
70,
179
];
$tupleBuilder->addValues($person);
$tuple = $tupleBuilder->build($people);
$people->insert($tuple);
Insert using a Sequence:
$seqname = "seq";
$startpoint = 2;
$incrementby = 1;
$lxseq = lxCreateSequence($seqname,$startpoint,$incrementby);
$nextval = $lxseq.nextVal();
$person = [
$nextval,
"912345678B",
"Jane",
"Doe",
"Mulholland Drive",
"0034698765432",
"janedoe@lx.com",
"1967-01-01",
70,
185
];
$tupleBuilder->addValues($person);
$tuple = $tupleBuilder->build($people);
$people->insert($tuple);
Insert a Record containing a BLOB read from a file:
Note that you will need to disable autocommit before inserting a blob.
$nextval = $lxseq.nextVal();
$person = [
$nextval,
null,
"Jim",
"Doe",
"Mulholland Drive",
null,
null,
"2008-01-01"
];
$properties = [
"autocommit" => "no"
];
lxSetProperties($properties);
$tupleBuilder->addValues($person);
$tuple = $tupleBuilder->build($people);
$people->insert($tuple);
$pic = fopen("/tmp/pic.jpg", "r");
if ($pic) {
$people.addBlob("photo");
while (($bufer = fgets($pic, 4096)) !== false) {
$people.addBlobData($bufer);
}
if (!feof($pic)) {
echo "Error: fallo inesperado de fgets()\n";
}
fclose($pic);
$people.addBlobDone();
}
lxEnd($tid);
$properties = [
"autocommit" => "yes"
];
lxSetProperties($properties);
3.2.4. Reading and scanning Data
Getting a Data Row directly by Key:
$keyfmt = "l[k]";
$keyBuilder = new LxTupleBuilder($keyfmt);
$person = [
"personId" => "1"
];
$keyBuilder->addValues($person);
$key = $keyBuilder->buildKey($collection);
$tuple = $collection->get($key);
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:
$people = new LxCollection($tablename);
$it = $people.find()
.min(0)
.max(2);
// Max 20 results
$it = $people.find()
.first(20);
foreach ($it as $key => $value) {
echo ("$key: \n");
print_r($value);
}
Scan a Table using a Secondary Index:
$it = $people.find()
.index("dniIdx")
.indexMin("111111111Q");
foreach ($it as $key => $value) {
echo ("$key: \n");
print_r($value);
}
Advanced Finding and Filtering:
The following example show how to define a pipeline with a filter.
$pipeline = [
['$match' => [
'$and' => [
['height' => 179],
['weight' => ['$gte' => 65, '$lte' => 80]],
]
]]
];
$it = $people->aggregateCursor($pipeline);
foreach ($it as $key => $value) {
echo ("$key: \n");
print_r($value);
}
Project:
The project may also include operations over the fields to be retrieved.
$pipeline = [
['$match' => [
'$and' => [
['height' => 179],
['weight' => ['$gte' => 65, '$lte' => 80]],
]
]],
['$project' => [
'dni' => '$dni',
'name' => '$name',
'lastname' => '$lastname'
]]
];
$it = $people->aggregateCursor($pipeline);
foreach ($it as $key => $value) {
echo ("$key: \n");
print_r($value);
}
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.
$pipeline = [
['$group' => [
'_id' => [
'address' => '$address',
],
'namecount' => ['$count' => '$_id']
]],
];
$it = $people->aggregateCursor($pipeline);
foreach ($it as $key => $value) {
echo ("$key: \n");
print_r($value);
}
$pipeline = [
['$match' => [
"address" => "Mulholland Drive"
]],
['$group' => [
'_id' => [
'address' => '$address',
],
'namecount' => ['$count' => '$_id']
]],
['$match' => [
"namecount" => 3
]],
];
$it = $people->aggregateCursor($pipeline);
foreach ($it as $key => $value) {
echo ("$key: \n");
print_r($value);
}
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 |
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. |