Skip to content

Issue with Linq to CQL with Cassandra 1.2 #102

Closed
eplowe opened this Issue Jan 7, 2013 · 9 comments

2 participants

@eplowe
eplowe commented Jan 7, 2013

This is actually a two fold problem, but of the same underlying nature. When you create CF's now and you don't wrap the CF name or column names in double quotes they will default to lower case. If you want mixed case you need to wrap the names in double quotes. All examples are coming from the FluentCassandra.Sandbox project.

First Problem

db.ExecuteNonQuery(@"
                CREATE COLUMNFAMILY Posts (
                KEY ascii PRIMARY KEY,
                Title text,
                Body text,
                Author text,
                PostedOn timestamp
                );");

Will result in the following output from DESCRIBE KEYSPACE command in cqlsh:

CREATE TABLE posts (
  key ascii PRIMARY KEY,
  author text,
  body text,
  postedon timestamp,
  title text
) WITH
  bloom_filter_fp_chance=0.010000 AND
  caching='KEYS_ONLY' AND
  comment='' AND
  dclocal_read_repair_chance=0.000000 AND
  gc_grace_seconds=864000 AND
  read_repair_chance=0.100000 AND
  replicate_on_write='true' AND
  compaction={'class': 'SizeTieredCompactionStrategy'} AND
  compression={'sstable_compression': 'SnappyCompressor'};

This creates a problem when doing something like:

var post = db.GetColumnFamily("Posts").Get(key).FirstOrDefault(); 

As the CQL that will be generated is SELECT * FROM Posts which will result in an unconfigured columnfamily error. Now since this was generated with straight CQL the argument is that it is on the developer to pay attention. In this case, yes.. but if the CQL statement changed to:

db.ExecuteNonQuery(@"
                CREATE COLUMNFAMILY "Posts" (
                KEY ascii PRIMARY KEY,
                Title text,
                Body text,
                Author text,
                PostedOn timestamp
                );");

It would still fail. The CQL generator should always wrap CF names in double quotes:

SELECT * FROM "Posts"

Second problem

Using the API to create a CF vs CQL.

 // create column family using API
 keyspace.TryCreateColumnFamily(new CassandraColumnFamilySchema
 {
       FamilyName = "Tags",
       KeyValueType = CassandraType.AsciiType,
       ColumnNameType = CassandraType.Int32Type,
       DefaultColumnValueType = CassandraType.UTF8Type
 });

Will result in the following output from DESCRIBE KEYSPACE command in cqlsh:

CREATE TABLE "Tags" (
  "KEY" ascii,
  column1 int,
  value text,
  PRIMARY KEY ("KEY", column1)
) WITH COMPACT STORAGE AND
  bloom_filter_fp_chance=0.010000 AND
  caching='KEYS_ONLY' AND
  comment='' AND
  dclocal_read_repair_chance=0.000000 AND
  gc_grace_seconds=864000 AND
  read_repair_chance=0.100000 AND
  replicate_on_write='true' AND
  compaction={'class': 'SizeTieredCompactionStrategy'} AND
  compression={'sstable_compression': 'SnappyCompressor'};

The Key will be wrapped in double quotes and this line of code:

 // query using CQL-LINQ
 dynamic tags = (
      from t in tagsFamily
      where t.Key == key
      select t).FirstOrDefault();

Will result in SELECT * FROM Tags WHERE KEY = 'first-blog-post' which result in an unconfigured columnfamily error.

My suggestion is that the CqlQueryGenerator should apply double quotes around CF names and column names

@eplowe
eplowe commented Jan 7, 2013

Also, in the method CreateFirstPost() it seems that using the FluentCassandra API to insert records into a CQL created table do not work. No errors are thrown but no data is inserted.

@nberardi
nberardi commented Jan 7, 2013

There is a known problem with the RPC vs CQL that has nothing to do with FluentCassandra.

When you insert a table through CQL the table name becomes all lowercase. When you insert through RPC the table name maintains the correct case.

This may result in the issues that you are talking about, because Cassandra is case sensitives with table names.

@nberardi nberardi was assigned Jan 7, 2013
@eplowe
eplowe commented Jan 7, 2013

@nberardi Understood. But with the current implementation of CQL it still needs to be escaped with double quotes to maintain case. If you create a CF, say Tags, via RPC:

keyspace.TryCreateColumnFamily(new CassandraColumnFamilySchema
{
    FamilyName = "Tags",
    KeyValueType = CassandraType.AsciiType,
    ColumnNameType = CassandraType.Int32Type,
    DefaultColumnValueType = CassandraType.UTF8Type
});

its definition is "Tags" via DESCRIBE KEYSPACE -- when you issue CQL against that CF using LINQ to CQL:

var tagsFamily = db.GetColumnFamily("Tags");
// query using CQL-LINQ
dynamic tags = (
    from t in tagsFamily
    where t.Key == key
    select t).FirstOrDefault();

it needs to be escaped properly. e.g. SELECT * FROM "Tags" instead of SELECT * FROM Tags which will throw an error. I understand it's not a problem with FluentCassandra but it is something that should be supported.

As for the issue of data not inserting using the API against a CQL created CF. I tried creating one with all lower case thinking that might be the issue and received the same result.

http://cassandra.apache.org/doc/cql3/CQL.html

Also, contrarily to unquoted identifiers and keywords, quoted identifiers are case sensitive ("My Quoted Id" is different from "my quoted id"). A fully lowercase quoted identifier that matches [a-zA-Z0-9_] is equivalent to the unquoted identifier obtained by removing the double-quote (so "myid" is equivalent to myid and to myId but different from "myId").*

@nberardi
nberardi commented Jan 7, 2013

What are we talking about CQL or CQL3? And what happens if you use quotes in the old CQL? This is very easy to add, but if the old CQL doesn't support it then we have a lot of work to do to split out the LINQ rendering.

@eplowe
eplowe commented Jan 7, 2013

@nberardi CQL 3. Yeah, CQL 2 does not like the double quotes at all.

@eplowe
eplowe commented Jan 7, 2013

@nberardi WRT to the issue of the thrift api not inserting data into the "Posts" CF in the Sandbox project. I got it to work doing the following when the CQL type was set to CQL 3 on the connection:

 db.ExecuteNonQuery(@"
    CREATE COLUMNFAMILY ""Posts"" (
    ""KEY"" ascii PRIMARY KEY,
    ""Title"" text,
    ""Body"" text,
    ""Author"" text,
    ""PostedOn"" timestamp
) WITH COMPACT STORAGE;");

You can definitely mix and match but it seems you can't really utilize the old thrift api against CQL 3 created CFs. As it is, if you use the thrift API to create CFs it appends WITH COMPACT STORAGE.

@nberardi
nberardi commented Jan 7, 2013

So whats the plan for this? What needs to be changed to get the desired effect you guys are looking for? Just quotes around the CF name?

@eplowe
eplowe commented Jan 7, 2013
@nberardi
nberardi commented Jan 7, 2013

Since this is CQL3 only this is not an easy task. Because we are going to need to split the LINQ interpreter.

@nberardi nberardi closed this in 6edd84c Jan 30, 2013
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.