calendar

Using clustering columns to display data.
SOURCE 'keyspace-create-simple.cql';
SOURCE 'keyspace-use.cql';

// tag::drop-table[]
DROP TABLE IF EXISTS cycling.calendar;
// end::drop-table[]

// NOT A QUERY, JUST A TABLE FOR QUERIES
// CREATE TABLE WITH LIST FOR UPDATE
// The SELECT statements that use this table can be found below

// tag::listColumn[]
CREATE TABLE IF NOT EXISTS cycling.calendar (
  race_id int,
  race_name text,
  race_start_date timestamp,
  race_end_date timestamp,
  PRIMARY KEY (
    race_id, race_start_date, race_end_date
  )
) WITH CLUSTERING ORDER BY (
  race_start_date DESC, race_end_date DESC
);
// end::listColumn[]

// tag::insert-complex-string[]
INSERT INTO cycling.calendar 
  (race_id, race_start_date, race_end_date, race_name) 
   VALUES
  (201, '2015-02-18', '2015-02-22', $$Women's Tour of New Zealand$$);
// end::insert-complex-string[]

// tag::insert-data[]
INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  100, 'Giro d''Italia', '2015-05-09', '2015-05-31'
);

INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  101, 'Criterium du Dauphine', '2015-06-07', '2015-06-14'
);

INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  102, 'Tour de Suisse', '2015-06-13', '2015-06-21'
);

INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  103, 'Tour de France', '2015-07-04', '2015-07-26'
);

INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  100, 'Giro d''Italia', '2014-05-08', '2014-05-30'
);

INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  101, 'Criterium du Dauphine', '2014-06-06', '2014-06-13'
);

INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  102, 'Tour de Suisse', '2014-06-12', '2014-06-20'
);

INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  103, 'Tour de France', '2014-07-03', '2014-07-25'
);

INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  100, 'Giro d''Italia', '2013-05-07', '2014-05-29'
);

INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  101, 'Criterium du Dauphine', '2013-06-05', '2013-06-12'
);

INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  102, 'Tour de Suisse', '2013-06-11', '2013-06-19'
);

INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  103, 'Tour de France', '2013-07-02', '2013-07-24'
);
// end::insert-data[]

CAPTURE 'select_all_from_calendar_allow_filtering.results';
// tag::select_start_date[]
SELECT * 
FROM cycling.calendar 
WHERE race_start_date = '2015-06-13' 
ALLOW FILTERING;
// end::select_start_date[]
CAPTURE OFF;

// tag::select_with_in[]
SELECT * 
FROM cycling.calendar 
WHERE race_id IN (101, 102, 103); 
// end::select_with_in[]

CAPTURE 'select_all_from_calendar_with_date_range.results';
// tag::select_with_range[]
SELECT * 
FROM cycling.calendar 
WHERE race_id = 101
  AND race_start_date >= '2014-05-27' 
  AND race_start_date < '2017-06-16';
// end::select_with_range[]
CAPTURE OFF;

// tag::paging_off[]
PAGING OFF;
// end::paging_off[]

CAPTURE 'select_all_from_calendar_with_in_and_order.results';
// tag::select_with_in_and_order[]
SELECT *
FROM cycling.calendar
WHERE race_id IN (100, 101, 102)
ORDER BY race_start_date DESC;
// end::select_with_in_and_order[]
CAPTURE OFF;

CAPTURE 'select_all_from_calendar_with_allow_filtering.results';
// tag::select_with_allow_filtering[]
SELECT * 
FROM cycling.calendar 
WHERE race_name IN ('Giro d''Italia','Tour de Suisse') 
ALLOW FILTERING;
// end::select_with_allow_filtering[]

CAPTURE 'select_all_from_calendar_with_in_and_asc_order.results';
// tag::select_with_in_and_asc_order[]
SELECT *
FROM cycling.calendar
WHERE race_id IN (100, 101, 102)
ORDER BY race_start_date ASC;
// end::select_with_in_and_asc_order[]
CAPTURE OFF;

CAPTURE 'select_all_from_calendar_with_two_ins.results';
// tag::select_with_two_ins[]
SELECT *
FROM cycling.calendar
WHERE race_id IN (100, 101, 102)
  AND (race_start_date, race_end_date)
  IN (('2015-05-09', '2015-05-31'), ('2015-05-06', '2015-05-31'));
// end::select_with_two_ins[]
CAPTURE OFF;

CAPTURE 'select_all_from_calendar_with_in_and_range.results';
// tag::select_with_in_and_range[]
SELECT *
FROM cycling.calendar
WHERE race_id IN (100, 101, 102)
  AND (race_start_date, race_end_date) >= ('2015-05-09', '2015-05-24');
// end::select_with_in_and_range[]
CAPTURE OFF;

// tag::insert_with_TTL[]
INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  200, 'placeholder', '2015-05-27', '2015-05-27'
)
USING TTL 200;
// end::insert_with_TTL[]

CAPTURE 'select_ttl_from_calendar.results';
// tag::select_with_TTL[]
SELECT TTL(race_name)
FROM cycling.calendar
WHERE race_id = 200;
// end::select_with_TTL[]
CAPTURE OFF;

// tag::update_with_TTL[]
UPDATE cycling.calendar 
USING TTL 300 
SET race_name = 'Tour de France - Stage 12' 
WHERE race_id = 200 
  AND race_start_date = '2015-05-27' 
  AND race_end_date = '2015-05-27';
// end::update_with_TTL[]

CAPTURE 'select_ttl_after_update_from_calendar.results';
SELECT TTL(race_name)
FROM cycling.calendar
WHERE race_id = 200;
CAPTURE OFF;

// tag::update_TTL_0[]
UPDATE cycling.calendar 
USING TTL 0
SET race_name = 'Tour de France - Stage 12' 
WHERE race_id = 200 
  AND race_start_date = '2015-05-27' 
  AND race_end_date = '2015-05-27';
// end::update_TTL_0[]

// tag::delete-200[]
DELETE
FROM cycling.calendar
WHERE race_id = 200;
// end::delete-200[]

// tag::insert_with_escape_characters[]
INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  201, 'Women''s Tour of New Zealand', '2015-02-18', '2015-02-22'
);
// end::insert_with_escape_characters[]

// tag::delete-201[]
DELETE
FROM cycling.calendar
WHERE race_id = 201;
// end::delete-201[]

// tag::insert_with_dollar_characters[]
INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  201, $$Women's Tour of New Zealand$$, '2015-02-18', '2015-02-22'
);
// end::insert_with_dollar_characters[]

DELETE
FROM cycling.calendar
WHERE race_id = 201;