INSERT INTO cycling.country_flag (
  country, cyclist_name, flag
) VALUES (
  'Belgium', 'Jacques', 1
);

INSERT INTO cycling.country_flag (
  country, cyclist_name
) VALUES (
  'Belgium', 'Andre'
);

INSERT INTO cycling.country_flag (
  country, cyclist_name, flag
) VALUES (
  'France', 'Andre', 2
);

INSERT INTO cycling.country_flag (
  country, cyclist_name, flag
) VALUES (
  'France', 'George', 3
);

INSERT INTO cycling.country_flag (
  country, cyclist_name, flag
) VALUES (
  'Belgium', 'Jacques', 1
);

INSERT INTO cycling.country_flag (
  country, cyclist_name
) VALUES (
  'Belgium', 'Andre'
);

INSERT INTO cycling.country_flag (
  country, cyclist_name, flag
) VALUES (
  'France', 'Andre', 2
);

INSERT INTO cycling.country_flag (
  country, cyclist_name, flag
) VALUES (
  'France', 'George', 3
);

CREATE CUSTOM INDEX cyclist_name_sasi_idx ON cycling.country_flag (cyclist_name)
  USING 'org.apache.cassandra.index.sasi.SASIIndex';

DESCRIBE INDEX cycling.cyclist_name_sasi_idx;

DROP INDEX IF EXISTS cycling.cyclist_name_sasi_idx;

SELECT * FROM cycling.country_flag;

SELECT count(cyclist_name) FROM cycling.country_flag
 WHERE country='Belgium';

SELECT DISTINCT country FROM cycling.country_flag;

SELECT * FROM cycling.country_flag WHERE cyclist_name LIKE 'Jac%';

SELECT * FROM cycling.country_flag WHERE cyclist_name LIKE 'Jac%' AND country = 'USA';

SELECT * FROM cycling.country_flag WHERE cyclist_name LIKE 'jac%';

SELECT * FROM cycling.country_flag;

CREATE TABLE IF NOT EXISTS cycling.country_flag (
  country text,
  cyclist_name text,
  flag int STATIC,
  PRIMARY KEY (country, cyclist_name)
);

DESCRIBE TABLE cycling.country_flag;

DROP TABLE IF EXISTS cycling.country_flag;
index-si-alter-add-dynamic-text-field
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Drop table country_flag"

tag="table-create[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::table-create[]
CREATE TABLE IF NOT EXISTS cycling.country_flag (
  country text,
  cyclist_name text,
  flag int STATIC,
  PRIMARY KEY (country, cyclist_name)
);
// end::table-create[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Create table country_flag"

tag="table-describe-base[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::table-describe-base[]
DESCRIBE TABLE cycling.country_flag;
// end::table-describe-base[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Described table."

tag="index-drop[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::index-drop[]
DROP INDEX IF EXISTS cycling.cyclist_name_sasi_idx;
// end::index-drop[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Drop index cyclist_name_sasi"

tag="index-create-cyclist-name-sasi[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::index-create-cyclist-name-sasi[]
CREATE CUSTOM INDEX cyclist_name_sasi_idx ON cycling.country_flag (cyclist_name)
  USING 'org.apache.cassandra.index.sasi.SASIIndex';
// end::index-create-cyclist-name-sasi[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Create index cyclist_name_sasi"

tag="index-describe-cyclist-name-sasi-idx[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::index-describe-cyclist-name-sasi-idx[]
DESCRIBE INDEX cycling.cyclist_name_sasi_idx;
// end::index-describe-cyclist-name-sasi-idx[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Described index cyclist_name_sasi_idx."

tag="data-insert[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::data-insert[]
INSERT INTO cycling.country_flag (
  country, cyclist_name, flag
) VALUES (
  'Belgium', 'Jacques', 1
);

INSERT INTO cycling.country_flag (
  country, cyclist_name
) VALUES (
  'Belgium', 'Andre'
);

INSERT INTO cycling.country_flag (
  country, cyclist_name, flag
) VALUES (
  'France', 'Andre', 2
);

INSERT INTO cycling.country_flag (
  country, cyclist_name, flag
) VALUES (
  'France', 'George', 3
);
// end::data-insert[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Insert data into table country_flag"

# Query all
tag="select-all[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-all[]
SELECT * FROM cycling.country_flag;
// end::select-all[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Select all from table country_flag"

# Query cyclist name LIKE
tag="select-cyclist-name-like[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-cyclist-name-like[]
SELECT * FROM cycling.country_flag WHERE cyclist_name LIKE 'Jac%';
// end::select-cyclist-name-like[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Query cyclist name LIKE"

# Query country flag cyclist name LIKE error
tag="select-cyclist-name-like-error[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-cyclist-name-like-error[]
SELECT * FROM cycling.country_flag WHERE cyclist_name LIKE 'jac%';
// end::select-cyclist-name-like-error[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Query cyclist name LIKE error"

# Query country flag cyclist name and country
tag="select-cyclist-name-like-and-country[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-cyclist-name-like-and-country[]
SELECT * FROM cycling.country_flag WHERE cyclist_name LIKE 'Jac%' AND country = 'USA';
// end::select-cyclist-name-like-and-country[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Query cyclist name LIKE and country"

# Truncate data without dropping the table
tag="table-truncate[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::table-truncate[]
TRUNCATE cycling.country_flag;
// end::table-truncate[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Truncate table country_flag"

# Query again to view data truncated from table
tag="select-from-country-flag-emptytable[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-from-country-flag-emptytable[]
SELECT * FROM cycling.country_flag;
// end::select-from-country-flag-emptytable[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Select from empty table country_flag"

# Insert data again
tag="data-insert-again[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::data-insert-again[]
INSERT INTO cycling.country_flag (
  country, cyclist_name, flag
) VALUES (
  'Belgium', 'Jacques', 1
);

INSERT INTO cycling.country_flag (
  country, cyclist_name
) VALUES (
  'Belgium', 'Andre'
);

INSERT INTO cycling.country_flag (
  country, cyclist_name, flag
) VALUES (
  'France', 'Andre', 2
);

INSERT INTO cycling.country_flag (
  country, cyclist_name, flag
) VALUES (
  'France', 'George', 3
);
// end::data-insert-again[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Insert data into table country_flag again"

# Query DISTINCT country values from all rows
tag="select-country-distinct[]"
echo "// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-country-distinct[]
SELECT DISTINCT country FROM cycling.country_flag;
// end::select-country-distinct[]
//" >> $results_table
echo "// end::$tag" >> $results_table
echo "Get distinct country values"

# Query DISTINCT country values from all rows
tag="select-count-belgium[]"
echo "// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-count-belgium[]
SELECT count(cyclist_name) FROM cycling.country_flag
 WHERE country='Belgium';
// end::select-count-belgium[]
//" >> $results_table
echo "// end::$tag" >> $results_table
echo "Get count of cyclists from Belgium"

TRUNCATE cycling.country_flag;