table-race-results

INSERT INTO cycling.race_results (
  race_name, race_history, top_three
) VALUES (
  'Race for Peace',
    { 2000, 2005, 2010 },
  { 1:'Nelson Mandela', 2:'Gandhi', 3:'Goofy' }
);

INSERT INTO cycling.race_results (
  race_name, race_history, top_three
) VALUES (
  'Race for Hollywood',
    { 1980, 1981, 1985, 2020 },
  { 1:'Harrison Ford', 2:'Mark Hamill', 3:'Carrie Fisher' }
);

INSERT INTO cycling.race_results (
  race_name, race_history, top_three
) VALUES (
  'Race for Humanity',
    { 1980, 1981, 1985 },
  { 1:'Peter Sellers', 2:'Errol Flynn', 3:'Harrison Ford' }
);

  CREATE INDEX IF NOT EXISTS race_history_full_idx
    ON cycling.race_results ( FULL(race_history) );

  CREATE INDEX IF NOT EXISTS race_history_full_idx
    ON cycling.race_results ( FULL(race_history) );

  CREATE INDEX IF NOT EXISTS race_history_full_idx
    ON cycling.race_results ( FULL(race_history) );

  CREATE INDEX IF NOT EXISTS top_three_full_idx
    ON cycling.race_results ( FULL(top_three) );

  CREATE INDEX IF NOT EXISTS top_three_full_idx
    ON cycling.race_results ( FULL(top_three) );

  CREATE INDEX IF NOT EXISTS top_three_full_idx
    ON cycling.race_results ( FULL(top_three) );

DESCRIBE INDEX cycling.race_history_full_idx;

DESCRIBE INDEX cycling.top_three_full_idx;

DROP INDEX IF EXISTS cycling.race_history_full_idx;
// end::index-drop-top-three-values-idx[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Dropped index race_history_full_idx."

if [ $index_version = "v1" ]; then
  tag="index-create-top-three-full-idx-v1[]"
  echo -e "\n// tag::$tag" >> $results_table
  $cqlsh -e "
  // tag::index-create-top-three-full-idx-v1[]
  CREATE INDEX IF NOT EXISTS top_three_full_idx
    ON cycling.race_results ( FULL(top_three) );
  // end::index-create-top-three-full-idx-v1[]
  " >> $results_table
  echo "// end::$tag" >> $results_table
  echo "Created index top_three_full_idx v1."

    tag="index-create-race-history-full-idx-v1[]"
  echo -e "\n// tag::$tag" >> $results_table
  $cqlsh -e "
  // tag::index-create-race-history-full-idx-v1[]
  CREATE INDEX IF NOT EXISTS race_history_full_idx
    ON cycling.race_results ( FULL(race_history) );
  // end::index-create-race-history-full-idx-v1[]
  " >> $results_table
  echo "// end::$tag" >> $results_table
  echo "Created indexrace_history_full_idx v1."

elif [ $index_version = "v2" ]; then
 tag="index-create-top-three-full-idx-v2[]"
  echo -e "\n// tag::$tag" >> $results_table
  $cqlsh -e "
  // tag::index-create-top-three-full-idx-v2[]
  CREATE INDEX IF NOT EXISTS top_three_full_idx
    ON cycling.race_results ( FULL(top_three) );
  // end::index-create-top-three-full-idx-v2[]
  " >> $results_table
  echo "// end::$tag" >> $results_table
  echo "Created index top_three_full_idx v2."

    tag="index-create-race-history-full-idx-v2[]"
  echo -e "\n// tag::$tag" >> $results_table
  $cqlsh -e "
  // tag::index-create-race-history-full-idx-v2[]
  CREATE INDEX IF NOT EXISTS race_history_full_idx
    ON cycling.race_results ( FULL(race_history) );
  // end::index-create-race-history-full-idx-v2[]
  " >> $results_table
  echo "// end::$tag" >> $results_table
  echo "Created indexrace_history_full_idx v2."

elif [ $index_version = "v3" ]; then
 tag="index-create-top-three-full-idx-v3[]"
  echo -e "\n// tag::$tag" >> $results_table
  $cqlsh -e "
  // tag::index-create-top-three-full-idx-v3[]
  CREATE INDEX IF NOT EXISTS top_three_full_idx
    ON cycling.race_results ( FULL(top_three) );
  // end::index-create-top-three-full-idx-v3[]
  " >> $results_table
  echo "// end::$tag" >> $results_table
  echo "Created index top_three_full_idx v3."

    tag="index-create-race-history-full-idx-v3[]"
  echo -e "\n// tag::$tag" >> $results_table
  $cqlsh -e "
  // tag::index-create-race-history-full-idx-v3[]
  CREATE INDEX IF NOT EXISTS race_history_full_idx
    ON cycling.race_results ( FULL(race_history) );
  // end::index-create-race-history-full-idx-v3[]
  " >> $results_table
  echo "// end::$tag" >> $results_table
  echo "Created indexrace_history_full_idx v3."
fi

tag="describe-top-three-full-idx[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::index-describe-top-three-full-idx[]
DESCRIBE INDEX cycling.top_three_full_idx;
// end::index-describe-top-three-full-idx[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Described index top_three_full_idx."

tag="describe-race-history-full-idx[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::index-describe-race-history-full-idx[]
DESCRIBE INDEX cycling.race_history_full_idx;
// end::index-describe-race-history-full-idx[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Described index race_history_full_idx."

tag="data-insert-one[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::data-insert-one[]
INSERT INTO cycling.race_results (
  race_name, race_history, top_three
) VALUES (
  'Race for Humanity',
    { 1980, 1981, 1985 },
  { 1:'Peter Sellers', 2:'Errol Flynn', 3:'Harrison Ford' }
);
// end::data-insert-one[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Inserted one data item."

tag="data-insert-all[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::data-insert-all[]
INSERT INTO cycling.race_results (
  race_name, race_history, top_three
) VALUES (
  'Race for Peace',
    { 2000, 2005, 2010 },
  { 1:'Nelson Mandela', 2:'Gandhi', 3:'Goofy' }
);

INSERT INTO cycling.race_results (
  race_name, race_history, top_three
) VALUES (
  'Race for Hollywood',
    { 1980, 1981, 1985, 2020 },
  { 1:'Harrison Ford', 2:'Mark Hamill', 3:'Carrie Fisher' }
);
// end::data-insert-all[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Inserted all data items."

# Query all data
tag="select-all[]"
echo "// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-all[]
SELECT * FROM cycling.race_results;
// end::select-all[]
//" >> $results_table
echo "// end::$tag" >> $results_table
echo "Executed select all."

# NOTE: This fails on a frozen collection.
# Query map - find info for races held in 1984
tag="select-race-history-contains-1984[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-race-history-contains-1984[]
SELECT * FROM cycling.race_results WHERE race_history CONTAINS 1984;
// end::select-race-history-contains-1984[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Executed select race history contains 1984."

#tag="select-race-history-contains-1984[]"
#echo -e "\n// tag::$tag" >> $results_table
#echo -e "
# ERROR MESSAGE:
# InvalidRequest: Error from server: code=2200 [Invalid query]
# message="Column 'race_history' has an index but does not support the operators
# specified in the query. If you want to execute this query despite the
# performance unpredictability, use ALLOW FILTERING"
#" >> $results_table
#echo "// end::$tag" >> $results_table
#echo "Write error into results file."

# Query map - find info for top 3
tag="select-top-three-full[]"
echo -e "\n// tag::$tag" >> $results_table
$cqlsh -e "
// tag::select-top-three-full[]
SELECT * FROM cycling.race_results
  WHERE top_three = { 1:'Peter Sellers', 2:'Errol Flynn', 3:'Harrison Ford' };
// end::select-top-three-full[]
" >> $results_table
echo "// end::$tag" >> $results_table
echo "Executed select race history contains 1984."

DROP INDEX IF EXISTS cycling.top_three_full_idx;

SELECT * FROM cycling.race_results;

SELECT * FROM cycling.race_results WHERE race_history CONTAINS 1984;

SELECT * FROM cycling.race_results
  WHERE top_three = { 1:'Peter Sellers', 2:'Errol Flynn', 3:'Harrison Ford' };

CREATE TABLE IF NOT EXISTS cycling.race_results (
  race_name text PRIMARY KEY,
  race_history frozen<set<int>>,
  top_three frozen<map<int, text>>
);

DESCRIBE TABLE cycling.race_results;

DROP TABLE IF EXISTS cycling.race_results;

table-race-results

Was this helpful?

Give Feedback