My bad. I did miss out to read "latest version" part.
-Vivek
On Tue, Sep 3, 2013 at 11:20 PM, dawood abdullah
<muhammed.daw...@gmail.com>wrote:
> I have tried with both the options creating secondary index and also tried
> adding parentid to primary key, but I am getting all the files with
> parentid 'yyy', what I want is the latest version of file with the
> combination of parentid, fileid. Say below are the records inserted in the
> file table:
>
> insert into file (id, parentid, version, contenttype, description, name)
> values ('f1', 'd1', '2011-03-04', 'pdf', 'f1 file', 'file1');
> insert into file (id, parentid, version, contenttype, description, name)
> values ('f1', 'd1', '2011-03-05', 'pdf', 'f1 file', 'file1');
> insert into file (id, parentid, version, contenttype, description, name)
> values ('f2', 'd1', '2011-03-05', 'pdf', 'f1 file', 'file1');
> insert into file (id, parentid, version, contenttype, description, name)
> values ('f2', 'd1', '2011-03-06', 'pdf', 'f1 file', 'file1');
>
> I want to write a query which returns me second and last record and not
> the first and third record, because for the first and third record there
> exists a latest version, for the combination of id and parentid.
>
> I am confused If at all this is achievable, please suggest.
>
> Dawood
>
>
>
> On Tue, Sep 3, 2013 at 10:58 PM, Vivek Mishra <mishra.v...@gmail.com>wrote:
>
>> create secondary index over parentid.
>> OR
>> make it part of clustering key
>>
>> -Vivek
>>
>>
>> On Tue, Sep 3, 2013 at 10:42 PM, dawood abdullah <
>> muhammed.daw...@gmail.com> wrote:
>>
>>> 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
>>>>> > >
>>>>> > >
>>>>> > >
>>>>> > >
>>>>> >
>>>>> >
>>>>>
>>>>>
>>>>
>>>
>>
>
