cyclist_name

Simple single column partition key. Demonstrates inserting, updating, and deleting data.

SOURCE '0_create_keyspace.cql';

// START-droptble
DROP TABLE IF EXISTS cycling.cyclist_name;
// END-droptble

// START-pk_at_start
USE cycling;
CREATE TABLE cyclist_name (
  id UUID PRIMARY KEY, lastname text, firstname text
);
// END-pk_at_start

DROP TABLE IF EXISTS cycling.cyclist_name;

// START-pk_at_end
USE cycling;
CREATE TABLE cyclist_name (
  id UUID, lastname text, firstname text, PRIMARY KEY (id)
);
// END-pk_at_end

DROP TABLE IF EXISTS cycling.cyclist_name;

// START-keyspace_included
CREATE TABLE cycling.cyclist_name (
  id UUID, lastname text, firstname text, PRIMARY KEY (id)
);
// END-keyspace_included

DROP TABLE IF EXISTS cycling.cyclist_name;

// Create a table with a simple partition key
// START-simple
CREATE TABLE IF NOT EXISTS cycling.cyclist_name (
  id UUID PRIMARY KEY,
  lastname text,
  firstname text
);
// END-simple

// START-uuid
INSERT INTO cycling.cyclist_name (
  id
) VALUES (
  uuid()
);
// END-uuid

// START-batch
BEGIN BATCH

  INSERT INTO cycling.cyclist_name (
    id, lastname, firstname
  ) VALUES (
    6d5f1663-89c0-45fc-8cfd-60a373b01622,'HOSKINS', 'Melissa'
  );

  INSERT INTO cycling.cyclist_name (
    id, lastname, firstname
  ) VALUES (
    38ab64b6-26cc-4de9-ab28-c257cf011659,'FERNANDES', 'Marcia'
  );

  INSERT INTO cycling.cyclist_name (
    id, lastname, firstname
  ) VALUES (
    9011d3be-d35c-4a8d-83f7-a3c543789ee7,'NIEWIADOMA', 'Katarzyna'
  );

  INSERT INTO cycling.cyclist_name (
    id, lastname, firstname
  ) VALUES (
    95addc4c-459e-4ed7-b4b5-472f19a67995,'ADRIAN', 'Vera'
  );

APPLY BATCH;
// END-batch

TRUNCATE cycling.cyclist_name;

// Insert data with TTL and timestamp
// START-insertstampttl
INSERT INTO cycling.cyclist_name (
  id, lastname, firstname
) VALUES (
  6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47, 'KRUIKSWIJK', 'Steven'
)
USING TTL 86400
AND TIMESTAMP 123456789;
// END-insertstampttl

// START-insertifnotexists
INSERT INTO cycling.cyclist_name (
  id, lastname, firstname
) VALUES (
  c4b65263-fe58-4846-83e8-f0e1c13d518f, 'RATTO', 'Rissella'
) 
IF NOT EXISTS; 
// END-insertifnotexists


DROP INDEX IF EXISTS fn_prefix;

// START-fn_prefix
CREATE CUSTOM INDEX IF NOT EXISTS fn_prefix
ON cycling.cyclist_name (firstname)
USING 'org.apache.cassandra.index.sasi.SASIIndex';
// END-fn_prefix

TRUNCATE cycling.cyclist_name;

// Insert 6 rows of cyclists
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (5b6962dd-3f90-4c93-8f61-eabfa4a803e2, 'VOS','Marianne');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (e7cd5752-bc0d-4157-a80f-7523add8dbcd, 'VAN DER BREGGEN','Anna');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (e7ae5cf3-d358-4d99-b900-85902fda9bb0, 'FRAME','Alex');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (220844bf-4860-49d6-9a4b-6b5d3a79cbfb, 'TIRALONGO','Paolo');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47, 'KRUIKSWIJK','Steven');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (fb372533-eb95-4bb4-8685-6ef61e994caa, 'MATTHEWS', 'Michael');

// View output
CAPTURE 'select_all_from_cycling_initial.results';
// START-select_initial
SELECT *
FROM cycling.cyclist_name;
// END-select_initial
CAPTURE OFF;

CAPTURE 'select_marianne_from_cyclist_name.results';
// START-select_Marianne
SELECT *
FROM cycling.cyclist_name
WHERE firstname = 'Marianne';
// END-select_Marianne
CAPTURE OFF;

CAPTURE 'select_like_M_from_cyclist_name.results';
// START-select_like_M_start
SELECT *
FROM cycling.cyclist_name
WHERE firstname LIKE 'M%';
// END-select_like_M_start
CAPTURE OFF;

// START-no_match_upper
SELECT * FROM cycling.cyclist_name WHERE firstname = 'MARIANNE';
// END-no_match_upper

// START-no_match_lower
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE 'm%';
// END-no_match_lower

// START-no_match_equality
SELECT * FROM cycling.cyclist_name WHERE firstname = 'M%';
SELECT * FROM cycling.cyclist_name WHERE firstname = '%M';
SELECT * FROM cycling.cyclist_name WHERE firstname = '%M%';
SELECT * FROM cycling.cyclist_name WHERE firstname = 'm%';
// END-no_match_equality

DROP INDEX IF EXISTS fn_contains;

// START-fn_contains
CREATE CUSTOM INDEX IF NOT EXISTS fn_contains
ON cycling.cyclist_name (firstname)
USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = { 'mode': 'CONTAINS' };
// END-fn_contains

// These examples generate an error if they are run before the fn_contains index
// is created
// START-like
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%m%';
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%m%' ALLOW FILTERING;
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%M%';
// END-like

CAPTURE 'select_marianne_allow_filtering_from_cyclist_name.results';
// START-select_marianne_allow_filtering
SELECT *
FROM cycling.cyclist_name
WHERE firstname = 'Marianne'
ALLOW FILTERING;
// END-select_marianne_allow_filtering
CAPTURE OFF;

CAPTURE 'select_like_M_partial_from_cyclist_name.results';
// START-select_like_M_partial
SELECT *
FROM cycling.cyclist_name
WHERE firstname LIKE '%M%';
// END-select_like_M_partial
CAPTURE OFF;

CAPTURE 'select_arianne_from_cyclist_name.results';
// START-select_arianne
SELECT *
FROM cycling.cyclist_name
WHERE firstname LIKE '%arianne';
// END-select_arianne
CAPTURE OFF;

CAPTURE 'select_arian_from_cyclist_name.results';
// START-select_arian_name
SELECT *
FROM cycling.cyclist_name
WHERE firstname LIKE '%arian%';
// END-select_arian_name
CAPTURE OFF;

CAPTURE 'select_mar_from_cyclist_name.results';
// START-select_greater_Mar
SELECT *
FROM cycling.cyclist_name
WHERE firstname > 'Mar'
ALLOW FILTERING;
// END-select_greater_Mar
CAPTURE OFF;

// These examples do not return a match when they are run before the
// fn_suffix_allcase index is created
// START-no_match_partial
SELECT * FROM cycling.cyclist_name WHERE firstname = 'MariAnne' ALLOW FILTERING;
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%m%';
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%M';
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE 'm%';
// END-no_match_partial

DROP INDEX IF EXISTS fn_suffix_allcase;

// START-fn_suffix_allcase
CREATE CUSTOM INDEX IF NOT EXISTS fn_suffix_allcase
ON cycling.cyclist_name (firstname)
USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = {
  'mode': 'CONTAINS',
  'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer',
  'case_sensitive': 'false'
};
// END-fn_suffix_allcase

CAPTURE 'select_like_m_lower_from_cyclist_name.results';
// START-select_like_m_lower
SELECT *
FROM cycling.cyclist_name
WHERE firstname LIKE '%m%';
// END-select_like_m_lower
CAPTURE OFF;

// START-alter
ALTER TABLE cycling.cyclist_name
ADD age int;

UPDATE cycling.cyclist_name
SET age = 23
WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;

INSERT INTO cycling.cyclist_name (
  id, age, firstname, lastname
) VALUES (
  8566eb59-07df-43b1-a21b-666a3c08c08a, 18, 'Marianne', 'DAAE'
);
// END-alter

CAPTURE 'select_name_and_age_filter_from_cyclist_name.results';
// START-select_name_and_age_filter
SELECT *
FROM cycling.cyclist_name
WHERE firstname = 'Marianne'
  AND age > 20
ALLOW FILTERING;
// END-select_name_and_age_filter
CAPTURE OFF;

// Undo the changes in the alter section
DELETE FROM cycling.cyclist_name
WHERE id = 8566eb59-07df-43b1-a21b-666a3c08c08a;
ALTER TABLE cycling.cyclist_name
DROP age;

// Delete data in specified columns from a row, changes cyclist Alex Frame to null in name columns
// START-deletecolumndata
DELETE firstname, lastname
FROM cycling.cyclist_name 
WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0;
// END-deletecolumndata

// View output
CAPTURE 'cyclist_name_delete_firstname_and_lastname.results';
SELECT *
FROM cycling.cyclist_name;
CAPTURE OFF;

// Delete an entire row, deletes cyclist Alex Frame altogether
CAPTURE 'cyclist_name_delete_applied_true.results';
// START-deleteentirerow
DELETE FROM cycling.cyclist_name 
WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0
IF EXISTS;
// END-deleteentirerow
CAPTURE OFF;

// View output
CAPTURE 'cyclist_name_delete_entire_row.results';
SELECT *
FROM cycling.cyclist_name;
CAPTURE OFF;

// Delete a row based on a static column condition, attempts to delete cyclist Michael Matthews but lastname does not match 
CAPTURE 'cyclist_name_delete_condition.results';
// START-deletecondition
DELETE FROM cycling.cyclist_name 
WHERE id = fb372533-eb95-4bb4-8685-6ef61e994caa 
IF firstname = 'Michael'
  AND lastname = 'Smith';
// END-deletecondition
CAPTURE OFF;

// View output
SELECT *
FROM cycling.cyclist_name;

// Delete a row with a WHERE clause, deletes cyclist Steven Kruikswijk
// START-deleterowhere
DELETE FROM cycling.cyclist_name 
WHERE id = 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47;
// END-deleterowhere

// View output
CAPTURE 'cyclist_name_delete_row_where.results';
SELECT *
FROM cycling.cyclist_name;
CAPTURE OFF;

// Delete rows using an IN clause on a primary key, deletes cyclists Marianne and Paolo
// START-deletein
DELETE FROM cycling.cyclist_name 
WHERE id IN (
  5b6962dd-3f90-4c93-8f61-eabfa4a803e2, 220844bf-4860-49d6-9a4b-6b5d3a79cbfb
);
// END-deletein

// View output
CAPTURE 'cyclist_name_delete_in.results';
SELECT *
FROM cycling.cyclist_name;
CAPTURE OFF;

// START-delete_lastname
DELETE lastname
FROM cycling.cyclist_name
WHERE id = c7fceba0-c141-4207-9494-a29f9809de6f;
// END-delete_lastname

// Reinsert data 
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (5b6962dd-3f90-4c93-8f61-eabfa4a803e2, 'VOS','Marianne');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (e7cd5752-bc0d-4157-a80f-7523add8dbcd, 'VAN DER BREGGEN','Anna');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (e7ae5cf3-d358-4d99-b900-85902fda9bb0, 'FRAME','Alex');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (220844bf-4860-49d6-9a4b-6b5d3a79cbfb, 'TIRALONGO','Paolo');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47, 'KRUIKSWIJK','Steven');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (fb372533-eb95-4bb4-8685-6ef61e994caa, 'MATTHEWS', 'Michael');

// START-comment
ALTER TABLE cycling.cyclist_name
ADD comment text;
// END-comment

// START-update_set_comment
UPDATE cycling.cyclist_name
SET comment = 'Rides hard, gets along with others, a real winner'
WHERE id = fb372533-eb95-4bb4-8685-6ef61e994caa;

UPDATE cycling.cyclist_name
SET comment = 'Rides fast, does not get along with others, a real dude'
WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;
// END-update_set_comment

DROP INDEX IF EXISTS cycling.stdanalyzer_idx;

// START-stdanalyzer_idx
CREATE CUSTOM INDEX IF NOT EXISTS stdanalyzer_idx
ON cycling.cyclist_name (comment) USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = {
  'mode': 'CONTAINS',
  'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
  'analyzed': 'true',
  'tokenization_skip_stop_words': 'and, the, or',
  'tokenization_enable_stemming': 'true',
  'tokenization_normalize_lowercase': 'true',
  'tokenization_locale': 'en'
};
// END-stdanalyzer_idx

SOURCE 'cyclist_name-queries.cql';

// Undo UPDATE statements
UPDATE cycling.cyclist_name
SET comment = NULL
WHERE id = fb372533-eb95-4bb4-8685-6ef61e994caa;

UPDATE cycling.cyclist_name
SET comment = NULL
WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;

// START-update
UPDATE cycling.cyclist_name
SET comment = 'Rides hard, gets along with others, a real winner'
WHERE id = fb372533-eb95-4bb4-8685-6ef61e994caa 
IF EXISTS;
// END-update

// START-multcolumns
UPDATE cycling.cyclist_name
SET
  firstname = 'Marianne',
  lastname = 'VOS'
WHERE id = 88b8fd18-b1ed-4e96-bf79-4280797cba80;
// END-multcolumns

// START-records
UPDATE cycling.cyclist_name
SET firstname = NULL
WHERE id IN (
  5b6962dd-3f90-4c93-8f61-eabfa4a803e2,
  fb372533-eb95-4bb4-8685-6ef61e994caa
);
// END-records

// START-upsert
UPDATE cycling.cyclist_name
SET
  firstname = 'Anna',
  lastname = 'VAN DER BREGGEN' 
WHERE id = e7cd5752-bc0d-4157-a80f-7523add8dbcd;
// END-upsert

// START-if
UPDATE cycling.cyclist_name
SET comment = 'Rides hard, gets along with others, a real winner'
WHERE id = fb372533-eb95-4bb4-8685-6ef61e994caa 
IF comment = NULL;
// END-if

// START-fail
UPDATE cycling.cyclist_name
SET comment = 'Rides hard, gets along with others, a real winner'
WHERE id = fb372533-eb95-4bb4-8685-6ef61e994cac 
IF comment = 'Rides hard, gets along with others, a real winner';
// END-fail

// START-count_lastname
SELECT COUNT(lastname)
FROM cycling.cyclist_name;
// END-count_lastname

// START-count_rows
SELECT COUNT(*)
FROM cycling.cyclist_name;
// END-count_rows

// START-insert_Roxxane
INSERT INTO cycling.cyclist_name (
  id, lastname, firstname
) VALUES (
  4647f6d3-7bd2-4085-8d6c-1229351b5498, 'KNETEMANN', 'Roxxane'
)
IF NOT EXISTS;
// END-insert_Roxxane

// START-update_Roxxane
UPDATE cycling.cyclist_name
SET firstname = 'Roxane'
WHERE id = 4647f6d3-7bd2-4085-8d6c-1229351b5498
IF firstname = 'Roxxane';
// END-update_Roxxane

DELETE FROM cycling.cyclist_name
WHERE id = 4647f6d3-7bd2-4085-8d6c-1229351b5498;