1-- basic statements 2// c-style comment 3/* 4 5 multiline comment 6 7*/ 8CREATE TABLE cycling.cyclist_name ( id UUID PRIMARY KEY, lastname text, firstname text ); 9INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (5b6962dd-3f90-4c93-8f61-eabfa4a803e2, 'VOS','Marianne'); 10SELECT * FROM cycling.cyclist_name; 11SELECT lastname, firstname FROM /* a comment */ cycling.cyclist_name WHERE id = 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 /* multiline comment 12 13*/ -- comment; 14; 15 16CREATE TABLE cycling.cyclist_category ( category text, points int, id UUID, lastname text, PRIMARY KEY (category, points)) WITH CLUSTERING ORDER BY (points DESC); 17CREATE TABLE cycling.race_winners (race_name text, race_position int, cyclist_name FROZEN<fullname>, PRIMARY KEY (race_name, race_position)); 18CREATE TABLE cycling.cyclist_races ( id UUID PRIMARY KEY, lastname text, firstname text, races list<FROZEN <race>> ); 19INSERT INTO cycling.cyclist_races (id, lastname, firstname, races) VALUES (5b6962dd-3f90-4c93-8f61-eabfa4a803e2, 'VOS', 'Marianne', [ {race_title:'Rabobank 7-Dorpenomloop Aalburg',race_date:'2015-05-09',race_time:'02:58:33'},{race_title:'Ronde van Gelderland',race_date:'2015-04-19',race_time:'03:22:23'} 20]); 21 22INSERT INTO cycling.cyclist_races (id, lastname, firstname, races) VALUES (e7cd5752-bc0d-4157-a80f-7523add8dbcd, 'VAN DER BREGGEN', 'Anna', [ {race_title:'Festival Luxembourgeois du cyclisme feminin Elsy Jacobs - Prologue - Garnich > Garnich',race_date:'2015-05-01',race_time:'08:13:00'},{race_title:'Festival Luxembourgeois du cyclisme feminin Elsy Jacobs - Stage 2 - Garnich > Garnich',race_date:'2015-05-02',race_time:'02:41:52'},{race_title:'Festival Luxembourgeois du cyclisme feminin Elsy Jacobs - Stage 3 - Mamer > Mamer',race_date:'2015-05-03',race_time:'02:31:24'} ]); 23 24SELECT * FROM cycling.cyclist_races; 25 26SELECT lastname, races FROM cycling.cyclist_races WHERE id = e7cd5752-bc0d-4157-a80f-7523add8dbcd; 27 28INSERT 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$$); 29 30CREATE USER IF NOT EXISTS sandy WITH PASSWORD 'Ride2Win@' NOSUPERUSER; 31CREATE USER chuck WITH PASSWORD 'Always1st$' SUPERUSER; 32ALTER USER sandy SUPERUSER; 33LIST USERS; 34DROP USER IF EXISTS chuck; 35CREATE ROLE IF NOT EXISTS team_manager WITH PASSWORD = 'RockIt4Us!'; 36CREATE ROLE sys_admin WITH PASSWORD = 'IcanDoIt4ll' AND LOGIN = true AND SUPERUSER = true; 37ALTER ROLE sys_admin WITH PASSWORD = 'All4one1forAll' AND SUPERUSER = false; 38GRANT sys_admin TO team_manager; 39GRANT team_manager TO sandy; 40LIST ROLES; 41LIST ROLES OF sandy; 42REVOKE sys_admin FROM team_manager; 43REVOKE team_manager FROM sandy; 44DROP ROLE IF EXISTS sys_admin; 45GRANT MODIFY ON KEYSPACE cycling TO team_manager; 46GRANT DESCRIBE ON ALL ROLES TO sys_admin; 47GRANT AUTHORIZE ALL KEYSPACES TO sys_admin; 48REVOKE SELECT ON ALL KEYSPACES FROM team_manager; 49REVOKE EXECUTE ON FUNCTION cycling.fLog(double) FROM team_manager; 50LIST ALL PERMISSIONS OF sandy; 51LIST ALL PERMISSIONS ON cycling.cyclist_name OF chuck; 52CREATE MATERIALIZED VIEW cyclist_by_age AS SELECT age, birthday, name, country FROM cyclist_mv WHERE age is NOT NULL AND cid IS NOT NULL PRIMARY KEY (age, cid); 53CREATE MATERIALIZED VIEW cyclist_by_country AS SELECT age, birthday, name, country FROM cyclist_mv WHERE country is NOT NULL AND cid IS NOT NULL PRIMARY KEY (country, cid); 54CREATE MATERIALIZED VIEW cyclist_by_birthday AS SELECT age, birthday, name, country FROM cyclist_mv WHERE birthday is NOT NULL AND cid IS NOT NULL PRIMARY KEY (birthday, cid); 55DROP MATERIALIZED VIEW cyclist_by_age; 56INSERT INTO cycling.calendar (race_id, race_name, race_start_date, race_end_date) VALUES (200, 'placeholder', '2015-05-27', '2015-05-27') USING TIMESTAMP 123456789; 57 58CREATE FUNCTION IF NOT EXISTS cycling.left (column TEXT,num int) 59RETURNS NULL ON NULL INPUT 60RETURNS text 61LANGUAGE javascript AS $$ 62 column.substring(0,num) 63$$; 64 65CREATE OR REPLACE FUNCTION cycling.fLog (input double) 66CALLED ON NULL INPUT 67RETURNS double LANGUAGE java AS 68'return Double.valueOf(Math.log(input.doubleValue()));'; 69