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()));';
View as plain text