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:

include::../odbc/index.adoc

2.1. Quick Start

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

This driver is in the final stages of development. It will be available very soon.

Coming soon

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 you connect, read, insert and update 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 for 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:

$metaadress = "123.45.67.89!44000";

echo "Connect to $metaadress\n";

lxOpen($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:

$tablename = "person";
$fields = [
  "personId" => "BIGINT",
  "dni" => "VARCHAR",
  "name" => "VARCHAR",
  "lastName" => "VARCHAR",
  "address" => "VARCHAR",
  "phone" => "VARCHAR",
  "email" => "VARCHAR",
  "comment" => "VARCHAR",
  "birthday" => "DATE",
  "weight" => "FLOAT",
  "height" => "FLOAT",
  "photo" => "BLOB"
];

$people = lxCreateTable($tablename,$fields);

// Non unique
$people.createIndex("phoneIdx", false);

//Unique
$people.createIndex("dniIdx", true);

3.2.3. Inserting, updating and deleting

Inserting one Record:
$person = [
  "personId" => "1",
  "dni" => "123456789A",
  "name" => "John",
  "lastName" => "Doe",
  "comment" => "No comments",
  "address" => "Mulholland Drive",
  "phone" => "555333695",
  "email" => "johndoe@nowhere.no",
  "birthday" => "1970-01-01",
  "weight" => "70",
  "height" => "179"
];

$people.insert($person);
Insert using a Sequence:
$seqname = "seq";
$startpoint = 2;
$incrementby = 1;
$lxseq = lxCreateSequence($seqname,$startpoint,$incrementby);

$nextval = $lxseq.nextVal();

$person = [
  "personId" => $nextval,
  "dni" => "912345678B",
  "name" => "Jane",
  "lastName" => "Doe",
  "address" => "Mulholland Drive",
  "phone" => "0034698765432",
  "email" => "janedoe@lx.com",
  "birthday" => "1967-01-01",
  "weight" => "70",
  "height" => "185"
];
$people.insert($person);
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 = [
  "personId" => $nextval,
  "name" => "Jim",
  "lastName" => "Doe",
  "address" => "Mulholland Drive",
  "birthday" => "2008-01-01",
];

$properties = [
  "autocommit" => "no"
]
lxSetProperties($properties);

$people.insert($person);

$pic = fopen("/tmp/pic.jpg", "r");
if ($pic) {
  $people.addBlob("photo");

  while (($búfer = fgets($pic, 4096)) !== false) {
    $people.addBlobData($búfer);
  }
  if (!feof($pic)) {
      echo "Error: fallo inesperado de fgets()\n";
  }
  fclose($pic);
  $people.addBlobDone();
}

lxCommit();

$properties = [
  "autocommit" => "yes"
]
lxSetProperties($properties);
Updating a Record:
$person = [
  "personId" => "1",
  "weight" => "75"
];

$people.update($person);
Deleting a Record:
$person = [
  "personId" => "1"
];

$people.delete($person);
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:
$people = new LxCollection($tablename);

$person = [
  "personId" => "1"
];
$it = $people.getByKey($person);

foreach ($it as $key => $value) {
  echo ("$key: \n");
  print_r($value);
}
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);
}