PHP

PHP

1. Two interfaces to access the same data

SQL-vs-NoSQL

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

  1. A 2003 ANSI SQL interface: It is powerful and easy to use, but can be slow. It is most suitable for complex queries (e.g. multijoin queries)

  1. A proprietary key-value interface called KiVi. It is fast and provides all functionality except for JOINs. It is most suitable for simpler queries where the overhead of SQL would be too costly (e.g. updates or inserts)

Both interfaces interact with the same data, and can even be part of the same transactions.

2. SQL

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 shows how to install the LeanXcale ODBC Driver for Linux. This procedure has been tested in Ubuntu 20.04 LTS, but it should work on other distributions too.

  1. Install prerequisite packages:

    sudo apt-get install unixodbc unixodbc-dev

    unixodbc-dev package is used by some extensions that needs to be compiled to be installed.

  2. Download the ODBC Connector from the Drivers page and unpack it.

  3. Copy the file libodbclx.so into /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
  4. Create the file $HOME/.odbcinst.ini with this content:

    [LeanXcaleODBCDriver]
    Description = LeanXcale ODBC Driver for Linux
    Driver = /opt/leanxcale/odbc/client/libodbclx.so
    UsageCount = 1
  5. Create the file $HOME/.odbc.ini with this content:

    [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 odbc.ini and odbcinst.ini configuration files (named .odbc.ini and .odbcinst.ini) located in the home directory.

If you store these configuration files elsewhere, you must set these environment variables:

  • Set ODBCINI to the full path and file name of the odbc.ini file.

  • Set ODBCSYSINI to the full path of the directory that contains the odbcinst.ini file.

The driver manager will then be able to locate the files.

2.2. ODBC Windows Installation

2.2.1. Installing the ODBC Connector

  1. Download the ODBC Connector for Windows from the Drivers page

  2. Run the installer lx-odbc-setup-1.5.4.exe

  3. The default installation directory is C:\Program Files\Leanxcale\lx-odbc-driver

  4. If asked to install Visual C++ redistributable, follow the setup guide; just click Next as needed.

2.2.2. Registry Keys

The setup adds the relevant keys to the registry:

Registry Editor
[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

When the driver has been installed, you can add the data source from the ODBC Data Source Administrator.

The datasource can be added from the User DSN tab:

  1. Click Add

    ODBC DSA
  2. Select LX DRIVER(x64) from the driver list

    ODBC DSA

With the driver selected, you can configure the Data Source Name properties:

ODBC DSA
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

disable

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

no

TraceFile

Location of error log file

%temp%/lxodbc

Encoding

unicode

ANSI Encoding

auto-search

The Windows ODBC driver is an ANSI driver, but it supports unicode data.

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

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();
}
Updating a Record:
try {
    $updateSQL =
        "UPDATE persons
         SET city='Madrid'
         WHERE city='San Francisco'";
    $numAffectedRows = $pdoConnection->exec($updateSQL);
} catch (PDOException $e) {
    echo "Problem updating: " . $e->getMessage();
}
Deleting a Record:
try {
    $deleteSQL =
        "DELETE FROM persons
         WHERE city='New York'";
    $numAffectedRows = $pdoConnection->exec($deleteSQL);
} catch (PDOException $e) {
    echo "Problem deleting: " . $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

KiVi

3.1. Install

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

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

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

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

    sudo ln -s /lib/x86_64-linux-gnu/lxphpapi-7.2-1.5.4.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 our examples we will use these parameters:

  • IP / hostname: 123.45.67.89

  • KiVi port: 44000

We will connect using this piece of code:

$uid = "user123";
$metaadress = "123.45.67.89!44000";

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);
Updating a Record:
$tupleBuilder->addTuple($tuple);
$tupleBuilder->add(9, 75);
$tuple = $tupleBuilder->build($collection);

$people->update($tuple);
Deleting a Record:
$key = $keyBuilder->buildKey($collection);

$collection->delete($key);
Delete with a Filter:
$it = $people.find()
        .first(20);

foreach ($it as $key => $value) {
  $people.delete($value);
}

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.