Getting started with Simpletools\Cassandra

Simpletools\Cassandra has been designed to provide you with all benefits of Apache Cassandra, but with simplified and flexible PHP methods.

Requirements

Simpletools\Cassandra requires PHP version >= 5.3.0

Simpletools\Cassandra requires Cassandra PHP Extension version >= 1.3.2

Setup

Simpletools\Cassandra can be used as a standalone class and we have included a majority of examples with this approch in mind. However, if you are using Simpletools\Mvc alongside, our examples can be simplified. Click here to view the Simpletools\Cassandra snippets for usage inside a model.

Installation

To see how to install \Simpletools framework please click here.

Configuration

To enable Simpletools\Cassandra, you need to define your cluster settings:

use Simpletools\Db\Cassandra\Client;

Client::cluster([
	'default'   =>  true,
	'name'      => 'CLUSTER_NAME',
	'hosts'     => ['127.0.0.1'],
	'username'  => 'USERNAME',
	'password'  => 'PASSWORD,
	'port'      => 'PORT',
	'keyspace'  => 'KEYSPACE'
]);

\Simpletools\Db\Cassandra\Query

Simpletools\Db\Cassandra\Query is an easy to use component allowing you to easily interact with your Cassandra database.

SELECT

To select you first need to create a new instance of \Simpletools\Db\Cassandra\Query:

use Simpletools\Db\Cassandra\Query;

$query = (new Query('TABLE'))
	->client(new Client('CLIENT')) // optional - if this isn't set, it will use the default (set in the cluster config)
	->keyspace('KEYSPACE') // optional - if this isn't set, it will use the default (set in the cluster config)

To retrieve only a set of columns, simply use the columns() method:

$query->columns('COLUMN_1','COLUMN_2','COLUMN_3'); // example 1
$query->columns([
	'*',
	'COLUMN_1'	=> 'json | json:object | json:array',
	'COLUMN_2'	=> function($value){
		return json_decode($value)
	}
]); // example 2

To include a WHERE statement to your query use (all columns in the WHERE statement MUST be included in the PRIMARY index):

$query->where('COLUMN_1','VALUE_1');

You can then add an AND by using:

$query->also('COLUMN_2', 'IN', ['VALUE_2','VALUE_3','VALUE_4']);

To include a SORT BY statement to your query use (all columns in the SORT BY statement MUST be included in the PRIMARY index):

$query->sort('COLUMN_1','VALUE_1');

To include a GROUP BY statement to your query use (all columns in the GROUP BY statement MUST be included in the PRIMARY index):

$query->group('COLUMN_1','VALUE_1');

To view the generated query while developing or to store in logs use:

echo $query->getQuery();
/* Output: SELECT COLUMN_1, COLUMN_2 FROM KEYSPACE.TABLE WHERE COLUMN_1 = VALUE_1 AND COLUMN_2 IN ('VALUE_2','VALUE_3','VALUE_4') */

Once you are ready, you can then run the query with the following methods:

$query->fetch(); // fetch the first row
$query->fetchAll(); // fetch all rows (array of objects)
echo $query->length(); // returns the amount of results (int)

PLEASE NOE: The fetchAll() method is not needed if you loop on the $query variable. The plugin is smart enough to know that you would like to fetch all results at this point. An example of this is below.

A complete example of a SELECT query would look like (to catch insert errors, we suggest adding a try catch block around this example):

use Simpletools\Db\Cassandra\Query;

$query = (new Query('TABLE'))
	->client(new Client('CLIENT')) // optional - if this isn't set, it will use the default (set in the cluster config)
	->keyspace('KEYSPACE') // optional - if this isn't set, it will use the default (set in the cluster config)
	->columns('COLUMN_1','COLUMN_2','COLUMN_3')
	->where('COLUMN_1','VALUE_1')
	->also('COLUMN_2','IN',['VALUE_2','VALUE_3','VALUE_4']);
echo "Query: " . $query->getQuery();
/* Output: SELECT COLUMN_1,COLUMN_2 FROM KEYSPACE.TABLE WHERE COLUMN_1 = VALUE_1 AND COLUMN_2 IN ('VALUE_2','VALUE_3','VALUE_4') */

if ($query->length())
	foreach ($query as $row)
		echo json_encode($row) . "\n";
/* Output: {"COLUMN_1":"VALUE_1","COLUMN_2":"VALUE_2"} */
/* Output: {"COLUMN_1":"VALUE_1","COLUMN_2":"VALUE_3"} */
/* Output: {"COLUMN_1":"VALUE_1","COLUMN_2":"VALUE_4"} */

INSERT

To insert you first need to create a new instance of \Simpletools\Db\Cassandra\Query:

use Simpletools\Db\Cassandra\Query;

$query = (new Query('TABLE'))
	->client(new Client('CLIENT')) // optional - if this isn't set, it will use the default (set in the cluster config)
	->keyspace('KEYSPACE') // optional - if this isn't set, it will use the default (set in the cluster config)

Use the set() method to specify the data for the insert:

$query->set([
	'COLUMN_1'=>'VALUE_1',
	'COLUMN_2'=>'VALUE_2',
	'COLUMN_3'=>'VALUE_3'
]);

If you only want to insert, if the row doesn't already exist, use insertIgnore() instead of set():

$query->insertIgnore([
	'COLUMN_1'=>'VALUE_1',
	'COLUMN_2'=>'VALUE_2',
	'COLUMN_3'=>'VALUE_3'
]);

To use Cassandra's built in expiry feature, simply use expires():

$query->expires(10); // specify expiry time in seconds

To view the generated query while developing or to store in logs use:

echo $query->getQuery();
/* Output: INSERT INTO KEYSPACE.TABLE (COLUMN_1,COLUMN_2,COLUMN_3) VALUES("VALUE_1","VALUE_2","VALUE_3") */

Once you are ready, you can then run the query with run():

$query->run();

A complete example of a INSERT query would look like (to catch insert errors, we suggest adding a try catch block around this example):

use Simpletools\Db\Cassandra\Query;

$query = (new Query('TABLE'))
	->client(new Client('CLIENT')) // optional - if this isn't set, it will use the default (set in the cluster config)
	->keyspace('KEYSPACE') // optional - if this isn't set, it will use the default (set in the cluster config)
	->set([
		'COLUMN_1'=>'VALUE_1',
		'COLUMN_2'=>'VALUE_2',
		'COLUMN_3'=>'VALUE_3'
	])
	->run();

UPDATE

To update you first need to create a new instance of \Simpletools\Db\Cassandra\Query:

use Simpletools\Db\Cassandra\Query;

$query = (new Query('TABLE'))
	->client(new Client('CLIENT')) // optional - if this isn't set, it will use the default (set in the cluster config)
	->keyspace('KEYSPACE') // optional - if this isn't set, it will use the default (set in the cluster config)

Use the update() method to specify columns/data to update:

$query->update([
	'COLUMN_1'=>'VALUE_1',
	'COLUMN_3'=>'VALUE_3'
]);

To view the generated query while developing or to store in logs use:

echo $query->getQuery();
/* Output: INSERT INTO KEYSPACE.TABLE (COLUMN_1,COLUMN_2,COLUMN_3) VALUES("VALUE_1","VALUE_2","VALUE_3") */

Once you are ready, you can then run the query with run():

$query->run();

A complete example of a UPDATE query would look like (to catch insert errors, we suggest adding a try catch block around this example):

use Simpletools\Db\Cassandra\Query;

$query = (new Query('TABLE'))
	->client(new Client('CLIENT')) // optional - if this isn't set, it will use the default (set in the cluster config)
	->keyspace('KEYSPACE') // optional - if this isn't set, it will use the default (set in the cluster config)
	->update([
		'COLUMN_1' => 'VALUE_1',
		'COLUMN_3' => 'VALUE_3'
	])
	->run();

DELETE

To update you first need to create a new instance of \Simpletools\Db\Cassandra\Query:

use Simpletools\Db\Cassandra\Query;

$query = (new Query('TABLE'))
	->client(new Client('CLIENT')) // optional - if this isn't set, it will use the default (set in the cluster config)
	->keyspace('KEYSPACE') // optional - if this isn't set, it will use the default (set in the cluster config)

Use the delete() method to specify columns/data to update:

$query->delete('COLUMN_1', 'VALUE_1');

To view the generated query while developing or to store in logs use:

echo $query->getQuery();
/* Output: DELETE FROM KEYSPACE.TABLE WHERE COLUMN_1='VALUE_1' */

Once you are ready, you can then run the query with run():

$query->run();

A complete example of a DELETE query would look like (to catch insert errors, we suggest adding a try catch block around this example):

use Simpletools\Db\Cassandra\Query;

$query = (new Query('TABLE'))
	->client(new Client('CLIENT')) // optional - if this isn't set, it will use the default (set in the cluster config)
	->keyspace('KEYSPACE') // optional - if this isn't set, it will use the default (set in the cluster config)
	->delete('COLUMN_1', 'VALUE_1')
	->run();

\Simpletools\Db\Cassandra\Query for \Simpletools\Mvc

Great news! If you are using Simpletools\Mvc alongside Simpletools\Db\Cassandra, our \Query examples located above can be simplified for usage inside models.

If you are using \Query inside a \Mvc\Model, you simply need to extend your model class:

use Simpletools\Db\Cassandra\Model;

/*
* OPTIONAL SETTINGS:
* these can be changed on a model basis
* if these arn't defined here, default cluster settings will be used
*/
const CLUSTER = "CLUSTER_NAME";
const KEYSPACE = "KEYSPACE_NAME";

class Example extends Model
...

SELECT

An example SELECT statement would be ("TABLE_NAME" should be replaced with the name of the table that you want to query):

$query = $this->TABLE_NAME('COLUMN_1','COLUMN_2','COLUMN_3')
 ->where('COLUMN','VALUE');

$result = $query->fetchAll();
...

INSERT

An example INSERT statement would be ("TABLE_NAME" should be replaced with the name of the table that you want to query):

$this->TABLE_NAME()
	->set([
			'COLUMN_1' => 'VALUE_1',
			'COLUMN_2' => 'VALUE_2',
			'COLUMN_3' => 'VALUE_3',
	])
	->run();

UPDATE

An example UPDATE statement would be ("TABLE_NAME" should be replaced with the name of the table that you want to query):

$this->TABLE_NAME()
	->update([
			'COLUMN_1' => 'VALUE_1',
			'COLUMN_3' => 'VALUE_3'
	])
	->run();

DELETE

An example DELETE statement would be ("TABLE_NAME" should be replaced with the name of the table that you want to query):

$this->TABLE_NAME()
	->delete('COLUMN','VALUE')
	->run();

\Simpletools\Db\Cassandra\Type\Uuid

Simpletools\Db\Cassandra\Type\Uuid can be used to generate and prepare UUID's for use in queries.

PLEASE NOTE: query examples below, assume you are using \Simpletools\Mvc and the code is being used inside of a \Simpletools\Mvc\Model. However, the \Uuid plugin can be used in the same way, when using the plugin as a standalone class.

To generate a new UUID, you first need to create a new instance of \Simpletools\Db\Cassandra\Type\Uuid:

use Simpletools\Db\Cassandra\Type\Uuid;

$uuid = new Uuid());
echo $uuid;
/* Output: 971aa1e4-8395-11e9-bc42-526af7764f64 */

The generated UUID can then be used in an INSERT query:

$this->TABLE_NAME()
	->set([
			'COLUMN_1' => $uuid,
			'COLUMN_2' => 'VALUE_2',
			'COLUMN_3' => 'VALUE_3',
	])
	->run();

\Simpletools\Db\Cassandra\Type\AutoIncrement

Simpletools\Db\Cassandra\Type\AutoIncrement can be used to generate and maintain auto increment id's for use in queries. Unfortunately, there is no built in Cassandra method to do this for you. Therefore, we have put together a plugin to handle this for you.

Setup

To start using \Simpletools\Db\Cassandra\Type\AutoIncrement, you first need to setup a table in cassandra. This table is used to store the counters. This can be done very easily by using the following built-in setup() method. This only needs to be run once, and should be removed from your code, after the tables have been created for you.

use Simpletools\Db\Cassandra\Type\AutoIncrement;

AutoIncrement::settings([
	'cluster'   => 'CLUSTER_NAME',
	'keyspace'  => 'KEYSPACE',
	'table'     => 'counter_table'
]);

AutoIncrement::setup();

Usage

PLEASE NOTE: \AutoIncrement returns id's as BigInt's. Therefore, you need to ensure that the column data type in Cassandra is set to "BIGINT". Alternatively, you can cast the returned id, to a standard integer, before you use it in \Query.

use Simpletools\Db\Cassandra\Type\AutoIncrement;

AutoIncrement::settings([
	'cluster'   => 'CLUSTER_NAME',
	'keyspace'  => 'KEYSPACE',
	'table'     => 'counter_table'
]);

$id = new AutoIncrement('COUNTER_NAME'));
echo $id->value();
/* Output: 1 */

PLEASE NOTE: query examples below, assume you are using \Simpletools\Mvc and the code is being used inside of a \Simpletools\Mvc\Model. However, the \AutoIncrement plugin can be used in the same way, when using the plugin as a standalone class.

The generated id can then be used in an INSERT query:

$this->TABLE_NAME()
	->set([
		'COLUMN_1' => $id,
		'COLUMN_2' => 'VALUE_2',
		'COLUMN_3' => 'VALUE_3',
	])
	->run();

\Simpletools\Db\Cassandra\Batch

Simpletools\Db\Cassandra\Batch can be used to combine multiple write queries, to be run in order. If one or more queries in the batch are invalid or fail to run, none of the queries will be run. This is useful in cases where you need to ensure a query has been run successfully, before going on the run the following query.

An example batch query would be:

use Simpletools\Db\Cassandra\Query;

$batch = new Simpletools\Db\Cassandra\Batch();
$b = $batch
    ->add(
        (new Query('user'))->set([
            'COLUMN_1' => 'VALUE_1',
            'COLUMN_2' => 'VALUE_2'
        ])
    )
    ->add(
        (new Query('user'))->delete('COLUMN_1','VALUE_1')
    );

echo $b->getQuery();

OR

$b->run();