Getting started with Simpletools\Cassandra

Requirements

Simpletools\Cassandra requires PHP version >= 5.3.0

Simpletools\Cassandra requires Cassandra PHP Extension version >= 1.3.2

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'
]);

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();

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();

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();

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();

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();