Manual - CQLSÍ

CQLSÍ - Cassandra Query Language Simple Interface (SÍ means Yes in Catalan).

Introduced in Catalonia Framework v.1.1.014 it can be used with all the Framework or with the minimal package for CQLSÍ

What is CQLSÍ

CQLSÍ is a simple interface, a wrapper, for working with CQL from PHP without Thrift.
After many problems with Thrift, fails trying different third party code solutions and compiling existing uncompilable PDO drivers, and Thrift being declared legacy by DataStax in favor of CQL for Cassandra 1.2 and 2.0, etc... I decided to create a painless simple way to interface with CQL while nothing better comes to the scene.
Basically CQLSÍ executes cqlsh through bash, captures the output and provides the data to the Catalonia Framework's Db abstraction layer, so switching from MySqli to Cassandra is just changing config file to use Db::TYPE_CONNECTION_CASSANDRA_CQLSI instead of Db::TYPE_CONNECTION_MYSQLI or Db::TYPE_CONNECTION_POSTGRE

Pros and Cons

The pros

The cons

Launching many queries at once

If you have to send several INSERT or UPDATE queries at once, you can group them in a single $s_cql string and you will save the time of connecting every time.
For example, this code:
$s_cql_long_query = '';
for ($i_userid=3000;$i_userid<4000;$i_userid++) {
    $s_cql_long_query .= "INSERT INTO users (user_id, fname, lname, longtext) VALUES ( ".$i_userid.", 'Carles', 'X', 'whatever');";
This code creates 1,000 inserts, in my modest and busy laptop takes 2.9599 seconds to process the inserts. This time includes all the time, so the cost of the execution of cqlsh, the time of establishing the connection, the time to process the cql and do the 1,000 inserts by Cassandra and the time to close the connection, parse the result and assembly the abstraction layer data Array.
A single insert takes 0.6536 seconds and 10 inserts send as a single $s_sql take 0.6992 seconds, so the most expensive is to launch the cqlsh command, and stablish the connection.

Simultaneous queries have to be of he same type: Write. It is not recommended although is possible to send many INSERT and a final SELECT at the end and process everything at once (execute with queryRead).


CQLSÍ does not require you to install anything, you just need to have cqlsh client installed in the system.

This is tipically done for Ubuntu/Debian by:

Edit /etc/apt/sources.list and add:
deb stable main

As descrived in

Add the key:

curl -L | sudo apt-key add -

Then install the cqlsh tool and if you want Cassandra (although you don't need to have it running on the webserver)

sudo apt-get update
sudo apt-get install python-cql=1.0.10-1
sudo apt-get install dsc=1.0.10 cassandra=1.0.10

Look for upgrades, just in case a new version has been releases:

sudo apt-get upgrade
Ubuntu on Amazon EC2 will return: The following packages have been kept back, showing cassandra and not updating.
To force the newest version to be installed do:
sudo apt-get install cassandra
If Cassandra fails to restart after upgrading, probably has a problem with logs from old version.
If your installation is new you can simple ignore the old logs and everything will work:
root@ip-10-10-10-10:/var/lib/cassandra/commitlog# mkdir old_logs
root@ip-10-10-10-10:/var/lib/cassandra/commitlog# mv *.log old_logs/
root@ip-10-10-10-10:/var/lib/cassandra/commitlog# service cassandra start
Please keep in mind that cqlsh has to be the version 4.1.0 at least to work with CQLSÍ.

Probably your system does not allow the webserver's user (www-data) to create history in home folder. This is required for bash, so probably you will have to do:

sudo mkdir /var/www/.cassandra
sudo chmod 777 /var/www/.cassandra
Or similar, so bash can create cqlsh_history file.

Working with different clusters / keyspaces

You can define as many Db objects as you want, each one configured to use a different cluster or keyspace.
Take this sample init/boostrap.php custom file:
$st_other_config1['database'] = Array(  'read'  => Array(   'servers'   => Array(0 => Array('connection_type'   => Db::TYPE_CONNECTION_CASSANDRA_CQLSI,
                                                                                            'connection_method' => Db::CONNECTION_METHOD_TCPIP,
                                                                                            'server_hostname'   => '',
                                                                                            'server_port'       => Db::PORT_DEFAULT_CASSANDRA,
                                                                                            'username'          => 'www_cassandra',
                                                                                            'password'          => 'yourpassword',
                                                                                            'database'          => 'mykeyspace',
                                                                                            'client_encoding'   => 'utf8'
                                        'write' => Array(   'servers'   => Array(0 => Array('connection_type'   => Db::TYPE_CONNECTION_CASSANDRA_CQLSI,
                                                                                            'connection_method' => Db::CONNECTION_METHOD_TCPIP,
                                                                                            'server_hostname'   => '',
                                                                                            'server_port'       => Db::PORT_DEFAULT_CASSANDRA,
                                                                                            'username'          => 'www_cassandra',
                                                                                            'password'          => 'yourpassword',
                                                                                            'database'          => 'mykeyspace',
                                                                                            'client_encoding'   => 'utf8'


$o_db_cassandra = new Db($st_other_config1['database']);
$s_cql = "INSERT INTO users (user_id, fname, lname, longtext) VALUES ( 1714, 'Carles', 'Mateo', 'whatever...');";
$st_result = $o_db_cassandra->queryWrite( $s_cql);
You can change the keyspace used for the next queries:
$o_db->setDatabaseOrKeyspace('cataloniasample', Db::CONNECTION_READ);
$o_db->setDatabaseOrKeyspace('cataloniasample', Db::CONNECTION_WRITE);
It is not recommended, but is possible to use different keyspaces for Read and Write.

In order to insert apostrophe, that is saved using '' and avoid injection use Db::prepareInsert(), for example:
$i_user_id = prepareInsert($i_user_id, self::DATA_TYPE_INT, self::TYPE_CONNECTION_CASSANDRA_CQLSI);
$s_fname = prepareInsert($s_fname, self::DATA_TYPE_STRING, self::TYPE_CONNECTION_CASSANDRA_CQLSI);
$s_lname = prepareInsert($s_lname, self::DATA_TYPE_STRING, self::TYPE_CONNECTION_CASSANDRA_CQLSI);
$s_longtext = prepareInsert($s_longtext, self::DATA_TYPE_STRING, self::TYPE_CONNECTION_CASSANDRA_CQLSI);
$s_cql = "INSERT INTO users (user_id, fname, lname, longtext) VALUES ( $i_user_id, '$s_fname', '$s_lname', '$s_longtext');";
$st_result = $o_db_cassandra->queryWrite($s_cql);

Working with data

For queries that return data, like SELECT $o_db->queryRead($s_cql) must be used.
For queries that INSERT, UPDATE data, or ALTER the Schema, $o_db->queryWrite($s_cql) must be used.
For MySql and Postgre this has another meaning (going to primary or secondaries), but for CQLSÍ it is required by the wrapper, as the error handling is performed in a different way for insertion commands and data fetching commands.

The enter characters will be returned as \n, so you will have a string like:
Null data types will be provided as "null" string.
Fully UTF-8 characters are supported, so accents, and other special chars codified by default with LANG=ca_ES.UTF-8.

Let's see a sample code to fetch data:
$st_results = $o_db_cassandra->queryRead('SELECT * FROM users LIMIT 10;');
$i_row_num = 0;
$s_first_data = '';
if ($st_results['result']['status'] == Db::QUERY_RESULT_STATUS_EXECUTED && $st_results['result']['error'] == 0) {
    echo '<table border="1">';
    foreach($st_results['data'] as $i_row=>$st_data) {
        echo '<tr>';
        foreach($st_data as $s_key => $s_value) {
            if ($i_row_num == 1) {
                // Write headers
                echo '<th>'.$s_key.'</th>';
                $s_first_data .= '<td>'.$s_value.'</td>';
            } else {
                echo '<td>'.$s_value.'</td>';
        echo '</tr>';
        if ($i_row_num == 1) {
            echo '<tr>'.$s_first_data.'</tr>';
    echo '</table>';
} else {
    echo 'Error: '.$st_results['result']['error_description'];
This code in init/bootstrap.php produces this output:

In case of error echo 'Error: '.$st_results['result']['error_description']; will output:

Enable debug

CQLSÍ generates temp files under /tmp.
Those are the .sh files executed and .cqlsi with the commands send to cqlsh.
If you need to debug and see what is the exact error you get, you can indicate to CQLSÍ to do not delete those files after use.

For example:
$s_cql = "CREATE TABLE IF NOT EXISTS test (userid int,
                                           firstname text,
                                           lastname text,
                                           tele set<text>,
                                           emails set<text>,
                                           skills list<text>,
                                           todos map<timestamp,text>,
                     PRIMARY KEY (userid) );";


$st_result = $o_db_cassandra->queryWrite($s_cql);

Common errors

Back to Main Manual page