[feat]: Right Click on Table - Generate DELETE statement

[millerjp]

This should be done after #539 but is a lot simpler.

https://cassandra.apache.org/doc/latest/cassandra/developing/cql/dml.html#delete_statement

The main points are:

• You can delete entire rows or just specific columns
• You must ALWAYS include the Partition Key(s) in the delete, but you dont have to include the cluster columns i.e range deletes

Here are several examples of DELETE statements in Cassandra for different scenarios:

Examples

-- Users table (Simple primary key)
CREATE TABLE users (
    user_id uuid PRIMARY KEY,
    name text,
    email text,
    age int
);

-- Products table (Compound primary key)
CREATE TABLE products (
    category text,
    product_id uuid,
    name text,
    price decimal,
    PRIMARY KEY (category, product_id)
);

-- Orders table (Compound primary key with clustering column)
CREATE TABLE orders (
    user_id uuid,
    order_id uuid,
    order_date timestamp,
    total decimal,
    PRIMARY KEY ((user_id), order_date)
);

-- Notifications table (Compound primary key with clustering column)
CREATE TABLE notifications (
    user_id int,
    post_date timestamp,
    notification_type text,
    message text,
    PRIMARY KEY ((user_id), post_date)
);

Deleting Based on Partition Keys (Without Clustering Columns)

-- Delete all users with a specific user_id
DELETE FROM users WHERE user_id = 123e4567-e89b-12d3-a456-426614174000;

-- Delete all products in the 'Electronics' category
DELETE FROM products WHERE category = 'Electronics';

-- Delete all orders for a specific user
DELETE FROM orders WHERE user_id = 123e4567-e89b-12d3-a456-426614174000;

-- Delete all notifications for a user_id
DELETE FROM notifications WHERE user_id = 1;

Deleting Specific Rows Based on Primary Keys (Including Clustering Columns)

-- Delete a specific user
DELETE FROM users WHERE user_id = 123e4567-e89b-12d3-a456-426614174000;

-- Delete a specific product in the Electronics category
DELETE FROM products WHERE category = 'Electronics' AND product_id = 123e4567-e89b-12d3-a456-426614174001;

-- Delete an order for a specific user on a specific date
DELETE FROM orders WHERE user_id = 123e4567-e89b-12d3-a456-426614174000 AND order_date = '2024-11-03 12:00:00';

-- Delete a specific notification for a user at a specific time
DELETE FROM notifications WHERE user_id = 1 AND post_date = '2024-11-01 10:00:00';

Deleting Specific Columns

-- Delete the 'age' column for a specific user in the Users table
DELETE age FROM users WHERE user_id = 123e4567-e89b-12d3-a456-426614174000[5];

-- Delete the 'price' column for a specific product in the Electronics category
DELETE price FROM products WHERE category = 'Electronics' AND product_id = 123e4567-e89b-12d3-a456-426614174001[5];

-- Delete the 'total' column for a specific order
DELETE total FROM orders WHERE user_id = 123e4567-e89b-12d3-a456-426614174000 AND order_id = 50554d6e-29bb-11e5-b345-feff819cdc9f[5];

-- Delete the 'notification_type' column for a specific notification
DELETE notification_type FROM notifications WHERE user_id = 1 AND post_date = '2024-11-01 10:00:00';