try the following. -ml
-- put this in <file> and run using 'cqlsh -f <file>
DROP KEYSPACE latest;
CREATE KEYSPACE latest WITH replication = {
'class': 'SimpleStrategy',
'replication_factor' : 1
};
USE latest;
CREATE TABLE file (
parentid text, -- row_key, same for each version
id text, -- column_key, same for each version
contenttype map<timestamp, text>, -- differs by version, version is the
key to the map
PRIMARY KEY (parentid, id)
);
update file set contenttype = contenttype + {'2011-03-04':'pdf1'} where
parentid = 'd1' and id = 'f1';
update file set contenttype = contenttype + {'2011-03-05':'pdf2'} where
parentid = 'd1' and id = 'f1';
update file set contenttype = contenttype + {'2011-03-04':'pdf3'} where
parentid = 'd1' and id = 'f2';
update file set contenttype = contenttype + {'2011-03-05':'pdf4'} where
parentid = 'd1' and id = 'f2';
select * from file where parentid = 'd1';
-- returns:
-- parentid | id | contenttype
------------+----+--------------------------------------------------------------------------
-- d1 | f1 | {'2011-03-04 00:00:00-0500': 'pdf1', '2011-03-05
00:00:00-0500': 'pdf2'}
-- d1 | f2 | {'2011-03-04 00:00:00-0500': 'pdf3', '2011-03-05
00:00:00-0500': 'pdf4'}
-- use an app to pop off the latest version from the map
-- map other varying fields using the same technique as used for contenttype
On Tue, Sep 3, 2013 at 2:31 PM, Vivek Mishra <mishra.v...@gmail.com> wrote:
> create table file(id text , parentid text,contenttype text,version
> timestamp, descr text, name text, PRIMARY KEY(id,version) ) WITH CLUSTERING
> ORDER BY (version DESC);
>
> insert into file (id, parentid, version, contenttype, descr, name) values
> ('f2', 'd1', '2011-03-06', 'pdf', 'f2 file', 'file1');
> insert into file (id, parentid, version, contenttype, descr, name) values
> ('f2', 'd1', '2011-03-05', 'pdf', 'f2 file', 'file1');
> insert into file (id, parentid, version, contenttype, descr, name) values
> ('f1', 'd1', '2011-03-05', 'pdf', 'f1 file', 'file1');
> insert into file (id, parentid, version, contenttype, descr, name) values
> ('f1', 'd1', '2011-03-04', 'pdf', 'f1 file', 'file1');
> create index on file(parentid);
>
>
> select * from file where id='f1' and parentid='d1' limit 1;
>
> select * from file where parentid='d1' limit 1;
>
>
> Will it work for you?
>
> -Vivek
>
>
>
>
> On Tue, Sep 3, 2013 at 11:29 PM, Vivek Mishra <mishra.v...@gmail.com>wrote:
>
>> 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
>>>>>>> > >
>>>>>>> > >
>>>>>>> > >
>>>>>>> > >
>>>>>>> >
>>>>>>> >
>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>
