Jan, The solution you gave works spot on, but there is one more requirement I forgot to mention. Following is my table structure
CREATE TABLE file ( id text, contenttype text, createdby text, createdtime timestamp, description text, name text, parentid text, version timestamp, PRIMARY KEY (id, version) ) WITH CLUSTERING ORDER BY (version DESC); The query (select * from file where id = 'xxx' limit 1;) provided solves the problem of finding the latest version file. But I have one more requirement of finding all the latest version files having parentid say 'yyy'. Please suggest how can this query be achieved. Dawood On Tue, Sep 3, 2013 at 12:43 AM, dawood abdullah <muhammed.daw...@gmail.com>wrote: > In my case version can be timestamp as well. What do you suggest version > number to be, do you see any problems if I keep version as counter / > timestamp ? > > > On Tue, Sep 3, 2013 at 12:22 AM, Jan Algermissen < > jan.algermis...@nordsc.com> wrote: > >> >> On 02.09.2013, at 20:44, dawood abdullah <muhammed.daw...@gmail.com> >> wrote: >> >> > Requirement is like I have a column family say File >> > >> > create table file(id text primary key, fname text, version int, >> mimetype text, content text); >> > >> > Say, I have few records inserted, when I modify an existing record >> (content is modified) a new version needs to be created. As I need to have >> provision to revert to back any old version whenever required. >> > >> >> So, can version be a timestamp? Or does it need to be an integer? >> >> In the former case, make use of C*'s ordering like so: >> >> CREATE TABLE file ( >> file_id text, >> version timestamp, >> fname text, >> .... >> PRIMARY KEY (file_id,version) >> ) WITH CLUSTERING ORDER BY (version DESC); >> >> Get the latest file version with >> >> select * from file where file_id = 'xxx' limit 1; >> >> If it has to be an integer, use counter columns. >> >> Jan >> >> >> > Regards, >> > Dawood >> > >> > >> > On Mon, Sep 2, 2013 at 10:47 PM, Jan Algermissen < >> jan.algermis...@nordsc.com> wrote: >> > Hi Dawood, >> > >> > On 02.09.2013, at 16:36, dawood abdullah <muhammed.daw...@gmail.com> >> wrote: >> > >> > > Hi >> > > I have a requirement of versioning to be done in Cassandra. >> > > >> > > Following is my column family definition >> > > >> > > create table file_details(id text primary key, fname text, version >> int, mimetype text); >> > > >> > > I have a secondary index created on fname column. >> > > >> > > Whenever I do an insert for the same 'fname', the version should be >> incremented. And when I retrieve a row with fname it should return me the >> latest version row. >> > > >> > > Is there a better way to do in Cassandra? Please suggest what >> approach needs to be taken. >> > >> > Can you explain more about your use case? >> > >> > If the version need not be a small number, but could be a timestamp, >> you could make use of C*'s ordering feature , have the database set the new >> version as a timestamp and retrieve the latest one with a simple LIMIT 1 >> query. (I'll explain more when this is an option for you). >> > >> > Jan >> > >> > P.S. Me being a REST/HTTP head, an alarm rings when I see 'version' >> next to 'mimetype' :-) What exactly are you versioning here? Maybe we can >> even change the situation from a functional POV? >> > >> > >> > > >> > > Regards, >> > > >> > > Dawood >> > > >> > > >> > > >> > > >> > >> > >> >> >
