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Í
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
DESCRIBE KEYSPACES;Use, instead:
SELECT * FROM system.schema_keyspaces;
$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');"; } $o_db_cassandra->queryWrite($s_cql_long_query);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.
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 http://debian.datastax.com/community stable main
As descrived in http://www.datastax.com/docs/1.0/install/install_deb
Add the key:
curl -L http://debian.datastax.com/debian/repo_key | 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 upgradeUbuntu on Amazon EC2 will return: The following packages have been kept back, showing cassandra and not updating.
sudo apt-get install cassandraIf Cassandra fails to restart after upgrading, probably has a problem with logs from old version.
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 startPlease 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/.cassandraOr similar, so bash can create cqlsh_history 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' => '127.0.0.1', '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' => '127.0.0.1', '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.
$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);
$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) { $i_row_num++; 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:
$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) );"; $o_db_cassandra->setKeepCqlFiles(true); $st_result = $o_db_cassandra->queryWrite($s_cql);