Friday, November 4, 2011

Cassandra CQL PHP Part 2

I have explained the way to execute CQL Queries from PHP via PHPCassa in my previous blogpost here and tutorial on cql queries here

It was difficult for me to remember the syntax of update and select CQL queries for Cassandra. Hence I wrote wrappers for them which are listed below

Update Counter Query:
/*
  * $counterMap => Associative array of CounterColumn names and corresponding value for given $key
  * Ex: UPDATE TestCounterFamily SET 'counter'='counter'+1, 'counter2'='counter2'+3 WHERE KEY='test'
*/ 
function generateUpdateCounterQuery($columnFamily, $counterMap, $key) 
{
  $colArgs = "";
  foreach($counterMap as $counter => $value)
  {
      $colArgs=$colArgs."'".$counter."'='".$counter."'+".$value.", ";
  }
  $colArgs = rtrim($colArgs,", ");
  return "UPDATE ".$columnFamily." SET ".$colArgs." WHERE KEY='".$key."'";
}

Update Standard Column Query
/*
  * $columnMap => Associative array of Column names and corresponding value for given $key
  * Ex: UPDATE TestColumnFamily SET 'name'='abc', 'country'='IN' WHERE KEY='test'
*/ 
function generateUpdateStandardColumnQuery($columnFamily, $columnMap, $rowKey) 
{
  $colArgs = "";
  foreach($columnMap as $columnKey => $value)
  {
     $colArgs=$colArgs."'".$columnKey."'='".$value."', ";
  }
  $colArgs = rtrim($colArgs,", ");
  return "UPDATE ".$columnFamily." SET ".$colArgs." WHERE KEY='".$rowKey."'";  
}

Select Column Query
/*
 * $column = Column to be selected from Cassandra $columnFamily
 * Ex: SELECT 'name' FROM TestColumnFamily WHERE KEY='test'(Updated)
 */ 
function generateSelectQuery($columnFamily, $column, $key, $limit='-1', $reversed='0', $range=false) 
{
  if($limit=='-1')
  {
     if($reversed == '1' && $range)
     {
       return "SELECT REVERSED '".$column."' FROM ".$columnFamily." WHERE KEY = '".$key."'";
     }
     return "SELECT '".$column."' FROM ".$columnFamily." WHERE KEY = '".$key."'";
  }
  if($reversed=='0')
  { 
    return "SELECT FIRST ".$limit." '".$column."' FROM ".$columnFamily." WHERE KEY = '".$key."'";
  }
  return "SELECT FIRST ".$limit." REVERSED '".$column."' FROM ".$columnFamily." WHERE KEY = '".$key."'";
}

Generate Range Queries
function selectColumnRange($columnFamily, $from, $to, $key, $limit='-1', $reversed='0') 
{
 return $this->generateSelectQuery($columnFamily ,$from."'".".."."'".$to, $key, $limit, $reversed, $range=true);
}

Execute Selects and Updates
function executeCQLSelect($query, $compression=cassandra_Compression::NONE) 
{
  $resultSet = $raw->client->execute_cql_query($query, $compression);
  return $resultSet;
}

function executeCQLUpdate($query, $compression=cassandra_Compression::NONE) 
{
  $raw->client->execute_cql_query($query, $compression);
}

Sample ColumnMap array would be like
$columnMap = array("name"=>"test", "country"=>"IN")

My solution to test ResultSet returned by Select Query
function isResultNull($resultSet)
{
      return (isset($resultSet->rows[0]) && $resultSet->rows[0] instanceof cassandra_CqlRow && count($resultSet->rows[0]->columns) > 0);
}

Hope this helps :)