millerjp

CREATE KEYSPACE bookmarks WITH replication = {'class': 'SimpleStrategy', 'replication_factor':'1'};
USE bookmarks;

millerjp

CREATE TABLE bookmark_by_user_video (
 user_id text,
 tvshow_id text,
 season int,
 continue_watching boolean,
 video_id text,
 created timestamp,
 time_in_video int,
 PRIMARY KEY ((user_id, video_id))
);

millerjp

INSERT INTO bookmark_by_user_video (user_id, tvshow_id, season, continue_watching, video_id, created, time_in_video) VALUES ('johnny', 'Baywatch', 1, true, '666QAZ', '2013-12-25 15:12:51', 20);
INSERT INTO bookmark_by_user_video (user_id, tvshow_id, season, continue_watching, video_id, created, time_in_video) VALUES ('johnny', 'Knight Rider', 3, false, '666WSX', '2013-12-26 15:12:51', 30);
INSERT INTO bookmark_by_user_video (user_id, tvshow_id, season, continue_watching, video_id, created, time_in_video) VALUES ('patrick', 'A-Team', 5, true, '666EDC', '2013-12-27 15:12:51', 40);
INSERT INTO bookmark_by_user_video (user_id, tvshow_id, season, continue_watching, video_id, created, time_in_video) VALUES ('hayato', 'Thundercats', 1, false, '666RFV', '2013-12-28 15:12:51', 50);

millerjp

SELECT * FROM bookmark_by_user_video ;

 user_id | video_id | continue_watching | created                  | season | time_in_video | tvshow_id
---------+----------+-------------------+--------------------------+--------+---------------+--------------
 patrick |   666EDC |              True | 2013-12-27 15:12:51+0100 |      5 |            40 |       A-Team
  johnny |   666QAZ |              True | 2013-12-25 15:12:51+0100 |      1 |            20 |     Baywatch
  johnny |   666WSX |             False | 2013-12-26 15:12:51+0100 |      3 |            30 | Knight Rider
  hayato |   666RFV |             False | 2013-12-28 15:12:51+0100 |      1 |            50 |  Thundercats

millerjp

SELECT video_id, created, time_in_video FROM bookmark_by_user_video WHERE user_id = 'johnny' AND video_id = '666QAZ';

 video_id | created                  | time_in_video
----------+--------------------------+---------------
   666QAZ | 2013-12-25 15:12:51+0100 |            20

millerjp

CREATE TABLE bookmark_by_user_continue_watching (
	user_id text,
	tvshow_id text,
	season int,
	continue_watching boolean,
	video_id text,
	created timestamp,
	time_in_video int,
	PRIMARY KEY ((user_id, continue_watching), video_id)
);

millerjp

INSERT INTO bookmark_by_user_continue_watching (user_id, tvshow_id, season, continue_watching, video_id, created, time_in_video) VALUES ('johnny', 'Baywatch', 1, true, '666QAZ', '2013-12-25 15:12:51', 20);
INSERT INTO bookmark_by_user_continue_watching (user_id, tvshow_id, season, continue_watching, video_id, created, time_in_video) VALUES ('johnny', 'Knight Rider', 3, false, '666WSX', '2013-12-26 15:12:51', 30);
INSERT INTO bookmark_by_user_continue_watching (user_id, tvshow_id, season, continue_watching, video_id, created, time_in_video) VALUES ('patrick', 'A-Team', 5, true, '666EDC', '2013-12-27 15:12:51', 40);
INSERT INTO bookmark_by_user_continue_watching (user_id, tvshow_id, season, continue_watching, video_id, created, time_in_video) VALUES ('hayato', 'Thundercats', 1, false, '666RFV', '2013-12-28 15:12:51', 50);

millerjp

SELECT * FROM bookmark_by_user_continue_watching ;

 user_id | continue_watching | video_id | created                  | season | time_in_video | tvshow_id
---------+-------------------+----------+--------------------------+--------+---------------+--------------
  johnny |              True |   666QAZ | 2013-12-25 15:12:51+0100 |      1 |            20 |     Baywatch
  johnny |             False |   666WSX | 2013-12-26 15:12:51+0100 |      3 |            30 | Knight Rider
 patrick |              True |   666EDC | 2013-12-27 15:12:51+0100 |      5 |            40 |       A-Team
  hayato |             False |   666RFV | 2013-12-28 15:12:51+0100 |      1 |            50 |  Thundercats

millerjp

SELECT video_id, created, time_in_video FROM bookmark_by_user_continue_watching WHERE user_id = 'johnny' and continue_watching = true ;

 video_id | created                  | time_in_video
----------+--------------------------+---------------
   666QAZ | 2013-12-25 15:12:51+0100 |            20

millerjp

CREATE TABLE bookmark_by_user_tvshow_and_season (
	user_id text,
	tvshow_id text,
	season int,
	continue_watching boolean,
	video_id text,
	created timestamp,
	time_in_video int,
	PRIMARY KEY ((user_id, tvshow_id, season), video_id)
);