Managers Cassandra Wrapper

Tuesday, September 27, 2016 12:40 AM

Using the Yee Cassandra Wrapper

Pre-Requisits

In order to use the Cassandra Wrapper you need to have the respective Cassandra Drivers installed on your system. Cassandra Drivers for PHP can be found here: https://github.com/datastax/php-driver Please read the documentation carefully on how to install drivers on your OS.

Initialization

To utilize this functionality you must instantiate the Database Manager. You would typically pass all connection parameters to the Yee Constructor as demonstated below. You may pass as many Databases to the constructor as required by your application.

require 'Yee/Yee.php';
\Yee\Yee::registerAutoloader();
$app = new \Yee\Yee(
    array(
        'database' => array(
                'db1' => array(
                        'database.type'     => 'cassandra',
                        'database.seeds'    => array( '192.168.0.0' ),
                        'database.port'     => 3306,
                        'database.keyspace' => 'keyspace_name',
                        'database.user'     => 'database_user',
                        'database.pass'     => 'database_pass'
                )
        )
    )
); 

new \Yee\Managers\DatabaseManager();

Doing so will automatically create respective Cassandra Object Instances in Yee. The database manager will not actually connect to each single database when the database manager is instantiated, but rather connects when the specific database is to be used and on demand. This allows instantiation of multiple databases across your Yee Application with no actual overhead when connecting to databases. Prior to using a database you must get an Instance from Yee $app = \Yee\Yee::getInstance();, which holds the database object(s) for you, and keeps them accessible across your app.

Next, prepare your data, and call the necessary methods.

Insert Query

Simple example

$data = Array ("login" => "admin",
               "firstName" => "John",
               "lastName" => 'Doe'
);

$result = $app->db['db1']->insert('users', $data);

if($result)
    echo 'user was created.';
Query produced: INSERT INTO users (login, firstName, lastName) VALUES ('admin', 'John', 'Doe');

Insert query with options

$data = Array(
        'login' => 'admin',
        'firstName' => 'John',
        'lastName' => 'Doe'
);

$options = Array(
        'if not exists',
        'ttl' => 86400
);

$result = $app->db['db1']->insert ('users', $data, $options);

if ($result)
    echo 'user was created.';
else
    echo 'insert failed'
Query produced: INSERT INTO users (login, firstName, lastName) VALUES ('admin', 'John', 'Doe') IF NOT EXISTS USING TTL 86400;

Available Options for Insert

  • 'if not exists'
  • 'ttl' => (int)

  • 'timestamp' => (int)

    All options are case insensitive

    Options which can be used in an insert query without failure:

    1. If not exists
    2. If not exists + TTL
    3. Timestamp
    4. TTL
    5. Timestamp + TTL

Update Query

Simple example

$data = Array (
    'firstName' => 'Bobby',
    'lastName' => 'Tables'
);

$app->db['db1']->where ('id', 1);

if ($app->db['db1']->update ('users', $data))
    echo 'Record was updated';
else
    echo 'update failed';
Query produced: UPDATE users SET firstName = 'Bobby', lastName = 'Tables' WHERE id = 1;

Update query with options

$data = Array(
    'firstName' => 'Bobby',
    'lastName' => 'Tables'
);

$options = Array(
    'timestamp' => 1474542896,
    'if' => ['age', 20]
);

$result = $db['db1']->where('username', 'Bob92')->update('users', $data, $options);

if($result)
    echo 'User updated';
else
    echo 'update failed';
Query produced: UPDATE users USING TIMESTAMP 1474542896 SET firstName = 'Bobby', lastName = 'Tables' WHERE username = 'Bob92' IF age = 20;

Available options for Update

  • 'if exists'
  • 'ttl' => (int)
  • 'timestamp' => (int)
  • 'if' => ['column_name' => 'value'], ['column_name' => value] ...

All options are case insensitive

Select Query

Examples of all parameters select/get function can take

$users = $app->db['db1']->get('users'); //returns an Array with all users 
$users = $app->db['db1']->get('users', 10); //returns an Array with 10 users
$users = $app->db['db1']->get('users', null, 'name'); //returns an Array with all names of the users
$users = $app->db['db1']->get('users', null, '*', 10); //returns an Array with all users stored as 10 users per page (inner array)

Limit the amount of results which will be returned.

$users = $app->db['db1']->get("users", 10);
if ($users != null)
    foreach ($users as $user) { 
        print_r ($user);
    }
Query produced: SELECT * FROM users LIMIT 10;

Select custom columns set.

$cols = Array ("id", "name", "email");
$users = $app->db['db1']->get("users", null, $cols);
if ($users != null)
    foreach ($users as $user) { 
        print_r ($user);
    }
Query produced: SELECT id, name, email FROM users;

Get results in pages.

$users_pages = $app->db['db1']->get("users", null, '*', 50);

if ($users_pages != null)
    foreach ($users_pages as $page) {
        foreach ($page as $user){
            print_r ($user);
        }
    }
Query produced: SELECT * FROM users;

Select just one row.

$app->db['db1']->where ("id", 1);
$user = $app->db['db1']->getOne ("users");
echo $user['id'];
Query produced: SELECT * FROM users WHERE id = 1 LIMIT 1;

Delete Query

Simple example

$app->db['db1']->where('id', 1)->delete('users');
Query produced: DELETE FROM users WHERE id = 1;

Delete custom columns set.

$cols = Array ("name", "email");
$app->db['db1']->where('id', 1)->delete("users", $cols);
Query produced: DELETE name, email FROM users WHERE id = 1;

Delete query with options.

$options = Array(
    'if exists'
);

$result = $app->db['db1']->where('id', 1)->delete("users", null, $options);

if ($result)
    echo 'Deletion was successful';
else
    echo 'Deletion failed';
Query produced: DELETE FROM users WHERE id = 1 IF EXISTS;

Available options for Delete

  • 'if exists'
  • 'timestamp' => (int)
  • 'if' => ['column_name' => 'value'], ['column_name' => value] ...

All options are case insensitive

Raw Query

Simple example

$cql = 'SELECT * FROM users WHERE id = 1';

$users = $app->$db['db1']->rawQuery($cql);

foreach ($users as $user) {
    print_r ($user);
}

Where Method

This method allows you to specify where parameters of the query.

Using multiple where

$app->db['db1']->where ('id', 1);
$app->db['db1']->where ('login', 'admin');
$results = $app->db['db1']->get ('users');
Query produced: SELECT * FROM users WHERE id = 1 AND login = 'admin';

Chaining where methods

$results = $app->db['db1']
        ->where('id', 1)
        ->where('login', 'admin')
        ->get('users');
Query produced: SELECT * FROM users WHERE id = 1 AND login = 'admin';

Using different operators

$results = $app->db['db1']
        ->where ('age', 20, '>')
        ->where ('name', ['John', 'Bob'], 'in');
$results = $app->db['db1']->get ('users');
Query produced: SELECT * FROM users WHERE age > 20 AND name in ('John', 'Bob');

Using where with an option

$results = $app->db['db1']
    ->where ('age', 20, '>=')
    ->where ('name', ['John', 'Bob'], 'in', 'allow filtering');
$results = $app->db['db1']->get ('users');
Query produced: SELECT * FROM users WHERE age >= 20 AND name in ('John', 'Bob') ALLOW FILTERING;

Using complex where

$results = $app->db['db1']
    ->where ('id', 1)
    ->where (['name','age'], [['John', 'Bob'], [20, 21]], 'in', 'allow filtering');
$results = $app->db['db1']->get ('users');
Query produced: SELECT * FROM users WHERE id = 1 AND (name, age) in (('John', 'Bob'), (20, 21)) ALLOW FILTERING;

Available option for Where

  • 'allow filtering'

This option is case insensitive

This option can be placed in any of the where methods as 4th parameter

In case this option is used multiple times, only once it will be placed in the query

Available operators

  • '='
  • '>'
  • '<'
  • '<='
  • '>='
  • 'in'
  • 'conatins'
  • 'contains key'

Return data types

Data types returned from select/get queries accoding to the column types

Column Type Return Type
varchar string
bigint integer
decimal float
double float
float float
int integer
varint string
timestamp PHP DateTime object
blob string
uuid string
timeuuid string
inet string

Decimal and Double column types have a loss of precision.

Insert data types

Data types used to insert data according to column types

Column Type Insert Type
varchar string
bigint integer
decimal float
double float
float float
int integer
varint string
timestamp string
blob string
uuid null/string
timeuuid null/string
inet string

If uuid and timeuuid is set to null, it will auto-generate the value itself

If uuid and timeuuid is set to a string (uuid/timeuuid type), it will save the string instead.

Other tools

Get the insert 'uuid'

$data = Array(
    'id' => null,
    'firstName' => 'Alex',
    'lastName' => 'Brown'
);

$app->db['db1']->insert('users', $data)
$id = $app->db['db1']->uuid();

echo $id;

Notes

  • SELECT queries have a limitation of 5000 rows. However, if more than 5000 items must be retrived, they can be placed in pages.
  • There is no limitation to the number of items which can be placed in a page.

$app->db['db1']->get('users', null, '*', 10000);

  • In case 'ALLOW FILTERING' option is used, consider choosing a limit if possible to reduce the memory used for the query.
  • Update queries can only update the columns which are not declared as PRIMARY KEY.
  • Deleting custom column set cannot be accomplished if they delared as PRIMARY KEY

This wrapper is not a full implementation of the Cassandra Driver provided by DataStax, but a functional implementation which aims at easing the use of Cassandra. The provided functionality is sufficient for most use cases, however if you need further methods implemented in order to achieve a specific goal, please contact us - we are happy to help!