Delete rows

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.data_types import (
    DataAPISet,
    DataAPITimestamp,
    DataAPIVector,
)
from astrapy.ids import UUID

insert_result = my_table.insert_many(
    [
        {
            "match_id": "fight4",
            "round": 1,
            "winner": "Victor",
            "score": 18,
            "when": DataAPITimestamp.from_string(
                "2024-11-28T11:30:00Z",
            ),
            "fighters": DataAPISet([
                UUID("0193539a-2770-8c09-a32a-111111111111"),
                UUID('019353e3-00b4-83f9-a127-222222222222'),
            ]),
            "m_vector": DataAPIVector([0.4, -0.6, 0.2]),
        },
        {"match_id": "fight5", "round": 1, "winner": "Adam"},
        {"match_id": "fight5", "round": 2, "winner": "Betta"},
        {"match_id": "fight5", "round": 3, "winner": "Caio"},
        {"match_id": "fight7", "round": 1, "winner": "Joy"},
        {"match_id": "fight7", "round": 2, "winner": "Kevin"},
        {"match_id": "fight7", "round": 3, "winner": "Lauretta"},
    ],
)

# Delete a single row (full primary key specified):
my_table.delete_many({"match_id": "fight4", "round": 1})

# Delete part of a partition (inequality on the
# last-mentioned 'partitionSort' column):
my_table.delete_many({"match_id": "fight5", "round": {"$gte": 5}})

# Delete a whole partition (leave 'partitionSort' unspecified):
my_table.delete_many({"match_id": "fight7"})

# empty the table entirely with empty filter (CAUTION):
my_table.delete_many({})

import { CreateTableDefinition, DataAPIClient, InferTablePrimaryKey, InferTableSchema, timestamp, uuid, vector } 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, and then infer the type.
// For information about table typing and definitions, 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;

type TableSchema = InferTableSchema<typeof TableDefinition>;

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

  // Insert some rows in an unordered fashion.
  await table.insertMany([
    { matchId: 'fight4', round: 1, winner: 'Victor' },
    { matchId: 'fight5', round: 1, winner: 'Adam' },
    { matchId: 'fight5', round: 2, winner: 'Betta' },
    { matchId: 'fight5', round: 3, winner: 'Caio' },
    { matchId: 'fight7', round: 1, winner: 'Joy' },
    { matchId: 'fight7', round: 2, winner: 'Kevin' },
    { matchId: 'fight7', round: 3, winner: 'Lauretta' },
  ]);

// Use deleteOne and deleteMany to remove rows from a table.

// deleteOne examples (with 'find' to demonstrate that the row was deleted)

  // Find rows where 'matchId' is 'fight7'
  await table.find({ matchId: 'fight7' }).toArray().then(rs => console.log(rs.length));

  // Delete one of the 'fight7' rows
  await table.deleteOne({ matchId: 'fight7', round: 2 });

  // Find the 'fight7' rows again
  // The deleted row should not be returned
  await table.find({ matchId: 'fight7' }).toArray().then(rs => console.log(rs.length));

  // Attempt to delete the same 'fight7' row again
  // Although the operation succeeds, nothing happens if the row was already deleted
  await table.deleteOne({ matchId: 'fight7', round: 2 });

  // Find the 'fight7' rows once again
  // The same rows are returned as the previous attempt because the row was already deleted
  await table.find({ matchId: 'fight7' }).toArray().then(rs => console.log(rs.length));

// deleteMany examples

  // To use deleteMany to delete one row, specify the full primary key
  await table.deleteMany({ matchId: 'fight4', round: 1 });

  // To delete part of a partition, use an inequality operator on the table's final 'partitionSort' column
  // This example deletes all rows where 'matchId' is 'fight5' and 'round' is greater than or equal to 5
  await table.deleteMany({ matchId: 'fight5', round: { $gte: 5 } });

  // To delete an entire partition, do not specify 'partitionSort' columns
  // This example deletes all rows where 'matchId' is 'fight6'
  await table.deleteMany({ matchId: 'fight7' });

  // To delete all rows in the table, pass an empty filter
  await table.deleteMany({});

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