Create an index

from astrapy import DataAPIClient
client = DataAPIClient("TOKEN")
database = client.get_database("API_ENDPOINT")

from astrapy.constants import SortMode
from astrapy.info import (
    CreateTableDefinition,
    ColumnType,
)

my_table = database.create_table(
    "games",
    definition=(
        CreateTableDefinition.builder()
        .add_column("match_id", ColumnType.TEXT)
        .add_column("round", ColumnType.TINYINT)
        .add_vector_column("m_vector", dimension=3)
        .add_column("score", ColumnType.INT)
        .add_column("when", ColumnType.TIMESTAMP)
        .add_column("winner", ColumnType.TEXT)
        .add_set_column("fighters", ColumnType.UUID)
        .add_partition_by(["match_id"])
        .add_partition_sort({"round": SortMode.ASCENDING})
        .build()
    ),
)

from astrapy.info import TableIndexOptions

# create an index on a column
my_table.create_index(
    "score_index",
    column="score",
)

# create an index on a textual column, specifying indexing options
my_table.create_index(
    "winner_index",
    column="winner",
    options=TableIndexOptions(
        ascii=False,
        normalize=True,
        case_sensitive=False,
    ),
)

import { CreateTableDefinition, DataAPIClient, SomeRow } from '@datastax/astra-db-ts';

// Instantiate the client and connect to the database
const client = new DataAPIClient();
const db = client.db(process.env.CLIENT_DB_URL!, { token: process.env.CLIENT_DB_TOKEN! });

// Create table schema using bespoke Data API table definition syntax.
// For information about table definition and data types, see the documentation for createTable.
const TableDefinition = <const>{
  columns: {
    matchId: 'text'
    round: 'tinyint',
    mVector: { type: 'vector', dimension: 3 },
    score: 'int',
    when: 'timestamp',
    winner: 'text',
    fighters: { type: 'set', valueType: 'uuid' },
  },
  primaryKey: {
    partitionBy: ['matchId'],
    partitionSort: { round: 1 },
  },
} satisfies CreateTableDefinition;

(async function () {
    // Create an untyped table if a 'games' table doesn't already exist
  const table = await db.createTable<SomeRow>('games', { definition: TableDefinition, ifNotExists: true });

  // Create a secondary index on the 'score' column with default options.
  // Errors if a 'score_idx' index already exists in the working keyspace.
  await table.createIndex('score_idx', 'score');

  // Create a secondary index on the 'winner' column with case-insensitivity
  // Because 'ifNotExists: true', the command does not throw an error
  // if the working keyspace already has an index named 'winner_idx'.
  await table.createIndex('winner_idx', 'winner', {
    options: {
      caseSensitive: false,
    },
    ifNotExists: true,
  });

  // Case insensitive indexes ignore case when querying.
  // Insert a row with upper case and lower case characters,
  // and then query the row.
  // findOne returns a match because 'winner_idx' is case-insensitive.
  await table.insertOne({ matchId: '01', round: 0, winner: 'Gray Tist' });
  await table.findOne({ winner: 'gray tist' }).then(console.log);

  // Create a vector index on the 'mVector' column with cosine similarity (default).
  // Errors if an 'm_vector_idx' index already exists in the working keyspace.
  await table.createVectorIndex('m_vector_idx', 'mVector');

  // Create a vector index on the 'mVector' column with dot-product similarity.
  // Because 'ifNotExists: true', the command does not throw an error
  // if the working keyspace already has an index named 'm_vector_idx'.
  await table.createVectorIndex('m_vector_idx', 'mVector', {
    options: {
      metric: 'dot_product',
    },
    ifNotExists: true,
  });

  // Drop the index so you can recreate it with different options.
  await db.dropTableIndex('m_vector_idx');

  // Create the vector index with dot-product similarity and a source model.
  // For accurate searches, use a source model and metric that are compatible with your vectors.
  await table.createVectorIndex('m_vector_idx', 'mVector', {
    options: {
      metric: 'dot_product',
      sourceModel: 'ada002',
    },
  });

  // Vector indexes allow you to perform vector searches.
  // Insert a row with a vector, and then run a vector search on the table.
  await table.insertOne({ matchId: '01', round: 0, mVector: [0.2, -0.3, -0.5] });
  await table.findOne({}, { sort: { mVector: [0.2, -0.3, -0.5] } }).then(console.log);

  // Get detailed information about the indexes
  // Returns information like [{ name: 'score_idx', definition: { column: 'score', options: {} } }, ...]
  await table.listIndexes().then(console.log);

  // Get index names only.
  // Returns information like ['score_idx', 'winner_idx', 'm_vector_idx', ...]
  await table.listIndexes({ nameOnly: true }).then(console.log);

  // Drop an index from a database's working keyspace without checking if the index exists.
  // If there is no match, the command succeeds but does nothing.
  // If there is a match, the named index is deleted.
  await db.dropTableIndex('score_idx');

  // Drop an index from a database's working keyspace if the index exists.
  // Errors if there is no match.
  await db.dropTableIndex('score_idx', { ifExists: true });

  // Uncomment the following line to drop the table and any related indexes.
  // await table.drop();
})();