%% change <login>  to your login name

cqlsh
                       
describe keyspaces

DROP KEYSPACE <login>;   

CREATE KEYSPACE <login> WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 3};

USE <login>;

CREATE TABLE timeline (
    userid uuid,
    posted_month int,
    posted_time uuid,
    body text,
    posted_by text,
    PRIMARY KEY (userid, posted_month, posted_time) ) 
WITH compaction = { 'class' : 'LeveledCompactionStrategy' };

describe table timeline;

CREATE TABLE clicks ( 
  userid uuid, 
  url text, 
  date timestamp,
  name text,
  PRIMARY KEY (userid, url) );

INSERT INTO clicks (userid, url, date, name) 
VALUES (3715e600-2eb0-11e2-81c1-0800200c9a66, 'http://apache.org', '2013-10-09', 'Mary') 
USING TTL 86400;

SELECT TTL (name) from clicks WHERE url = 'http://apache.org' ALLOW FILTERING;

======================

CREATE TABLE users (
  user_id text PRIMARY KEY, 
  first_name text, 
  last_name text, 
  emails set<text> );

INSERT INTO users (user_id, first_name, last_name, emails) 
VALUES('frodo', 'Frodo', 'Baggins', {'f@baggins.com', 'baggins@gmail.com'});

UPDATE users SET emails = emails + {'fb@friendsofmordor.org'} 
WHERE user_id = 'frodo';

SELECT user_id, emails FROM users WHERE user_id = 'frodo';

UPDATE users SET emails = emails - {'fb@friendsofmordor.org'} 
WHERE user_id = 'frodo';

UPDATE users SET emails = {} WHERE user_id = 'frodo';

========================

ALTER TABLE users ADD top_places list<text>;

UPDATE users SET top_places = [ 'rivendell', 'rohan' ] 
WHERE user_id = 'frodo';

UPDATE users SET top_places = [ 'the shire' ] + top_places 
WHERE user_id = 'frodo';


UPDATE users SET top_places = top_places + [ 'mordor' ] 
WHERE user_id = 'frodo'; 

SELECT user_id, top_places FROM users WHERE user_id = 'frodo';

UPDATE users SET top_places[2] = 'riddermark' 
WHERE user_id = 'frodo';

DELETE top_places[3] FROM users WHERE user_id = 'frodo';

UPDATE users SET top_places = top_places - ['riddermark'] 
WHERE user_id = 'frodo';

=========================

ALTER TABLE users ADD todo map<timestamp, text>;

UPDATE users SET todo = { '2012-9-24' : 'enter mordor',
'2012-10-2 12:00' : 'throw ring into mount doom' } 
WHERE user_id = 'frodo';

SELECT user_id, todo FROM users WHERE user_id = 'frodo';

UPDATE users SET todo['2012-10-2 12:00'] = 
'throw my precious into mount doom'
WHERE user_id = 'frodo';

INSERT INTO users (user_id, todo) VALUES ('frodo', { 
'2013-9-22 12:01' : 'birthday wishes to Bilbo', 
'2013-10-1 18:00' : 'Check into Inn of Prancing Pony' });

DELETE todo['2013-9-22 12:01'] FROM users 
WHERE user_id = 'frodo';

==========================

CREATE TYPE person (
    first TEXT,
    last TEXT
);

CREATE TABLE contacts (
    id INT,
    name person,
    address TUPLE<TEXT, TEXT, INT>,
    emails SET<TEXT>,
    apps LIST<TEXT>,
    phones MAP<TEXT, TEXT>,
    PRIMARY KEY (id)
);

INSERT INTO contacts (id, name, address, emails, apps, phones)
VALUES (
    1,
    {first: 'Irena', last: 'Holubova'},
    ('Malostranske namesti', 'Praha', 11800),
    {'holubova@ksi.mff.cuni.cz'},
    ['WhatsApp', 'Messenger'],
    {'work' : '+420951554316'}
);

INSERT INTO contacts (id, name, emails, apps, phones)
VALUES (
    2,
    {first: 'Pavel', last: 'Contos'},
    {'contos@ksi.mff.cuni.cz', 'pavel.contos@eli-beams.eu'},
    ['Viber', 'iMessage'],
    {'work' : '+420999999999', 'fax': '+420999333999'}
);

SELECT * FROM contacts;

==========================

CREATE TABLE users2 (
    username text PRIMARY KEY,
    firstname text,
    lastname text,
    birth_year int,
    country text
);
CREATE INDEX ON users2(birth_year);

SELECT * FROM users2;

SELECT firstname, lastname FROM users2 
WHERE birth_year = 1981;

SELECT firstname, lastname 
FROM users2 
WHERE birth_year = 1981 AND country = 'FR';

SELECT firstname, lastname 
FROM users2 
WHERE birth_year = 1981 AND country = 'FR' 
ALLOW FILTERING;