[feat]: Right Click on Table - INSERT

[millerjp]

We need a wizard approach for generating INSERT CQL statments.

Do not display this on counter tables as they should use a different right click action (see #700 ). Also see #702 as its related also

The goal here is to have a wizard essentially generating the CQL with valid syntax with placeholders in the generated text for them to update/generate the CQL text prior to executing.

This will be quite complex, particulalry when handling Collections and UDT types

See: https://cassandra.apache.org/doc/latest/cassandra/developing/cql/dml.html#insert-statement

Requirements

• All Primary Key column values are mandatory and must be provided
• Allow users to insert into all tables, including counter tables
• Present users some kind of UX allowing them to them to select the columns they want to insert and enter values
• Generate the CQL with. Make sure to get the ' correct based on the types.
• Include the comments (as below) in the CQL statement generated showing the column data type
• Allow users to INSERT using timestampe i.e setting the write time clientside. Let them optionally enter a unixtime stamp
• Allow users to set the TTL for the INSERT
• Allow users to choose the consistency level and add this to the CQL and default to LOCAL_ONE
• Support Insert using JSON (https://cassandra.apache.org/doc/stable/cassandra/cql/json.html#insert-json). Let users select the option to generate the INSERT as JSON
• Support Light Weight Transaactions as part of the insert statment

Examples

Below are CQL INSERT examples covering the various Cassandra data types. When generating the CQL with the placeholder for users to enter the values in the text editor, make sure to get the ' correct based on the types, also add the comments showing the data types

Basic Native Types

CREATE TABLE comprehensive_types (
    id uuid PRIMARY KEY,
    ascii_col ascii,
    bigint_col bigint,
    blob_col blob,
    boolean_col boolean,
    counter_col counter,
    date_col date,
    decimal_col decimal,
    double_col double,
    duration_col duration,
    float_col float,
    inet_col inet,
    int_col int,
    smallint_col smallint,
    text_col text,
    time_col time,
    timestamp_col timestamp,
    timeuuid_col timeuuid,
    tinyint_col tinyint,
    varchar_col varchar,
    varint_col varint
);

INSERT INTO comprehensive_types (
    id,                    -- UUID type
    ascii_col,            -- ASCII string
    bigint_col,           -- 64-bit signed long
    blob_col,             -- Binary data
    boolean_col,          -- true/false
    date_col,             -- Date without time
    decimal_col,          -- Variable-precision decimal
    double_col,           -- 64-bit floating point
    duration_col,         -- Duration value
    float_col,            -- 32-bit floating point
    inet_col,             -- IP address
    int_col,              -- 32-bit signed integer
    smallint_col,         -- 16-bit signed integer
    text_col,             -- UTF-8 string
    time_col,             -- Time without date
    timestamp_col,        -- Date and time
    timeuuid_col,         -- Version 1 UUID
    tinyint_col,          -- 8-bit signed integer
    varchar_col,          -- UTF-8 string
    varint_col            -- Arbitrary-precision integer
) VALUES (
    123e4567-e89b-12d3-a456-426614174000,    -- UUID
    'ASCII string',                           -- ASCII
    9223372036854775807,                      -- BIGINT
    0x0123456789abcdef,                       -- BLOB
    true,                                     -- BOOLEAN
    '2024-11-03',                            -- DATE
    123.456,                                  -- DECIMAL
    123.456789,                              -- DOUBLE
    'P2DT3H4M',                              -- DURATION
    123.45,                                  -- FLOAT
    '192.168.1.1',                           -- INET
    2147483647,                              -- INT
    32767,                                   -- SMALLINT
    'UTF8 text content',                     -- TEXT
    '08:12:54.123',                         -- TIME
    '2024-11-03 12:00:00.123+0000',         -- TIMESTAMP
    50554d6e-29bb-11e5-b345-feff819cdc9f,   -- TIMEUUID
    127,                                     -- TINYINT
    'Variable character content',            -- VARCHAR
    123456789012345678901234567890          -- VARINT
);

Collection Types

-- User-Defined Types
CREATE TYPE address (
    street text,          -- UTF-8 string
    city text,            -- UTF-8 string
    state text,           -- UTF-8 string
    postal_code text,     -- UTF-8 string
    country text,         -- UTF-8 string
    coordinates frozen<map<text, double>>,  -- Frozen map of string to 64-bit float
    last_verified timestamp                 -- Timestamp with timezone
);

CREATE TYPE contact_info (
    email text,                         -- UTF-8 string
    phone text,                         -- UTF-8 string
    social_media map<text, text>,       -- Map of UTF-8 string to UTF-8 string
    preferred_contact text              -- UTF-8 string
);

CREATE TABLE user_activity (
    user_id uuid PRIMARY KEY,           -- UUID type
    username text,                      -- UTF-8 string
    email_addresses list<text>,         -- List of UTF-8 strings
    login_timestamps list<timestamp>,   -- List of timestamps
    visited_locations list<frozen<address>>,  -- List of frozen UDT
    achievement_scores list<int>,       -- List of 32-bit integers
    session_ids list<timeuuid>          -- List of Version 1 UUIDs
);

INSERT INTO user_activity (
    user_id,                -- UUID type
    username,               -- UTF-8 string
    email_addresses,        -- List<text>
    login_timestamps,       -- List<timestamp>
    visited_locations,      -- List<frozen<address>>
    achievement_scores,     -- List<int>
    session_ids            -- List<timeuuid>
) VALUES (
    123e4567-e89b-12d3-a456-426614174000,    -- UUID
    'john_doe',                               -- UTF-8 string
    ['[email protected]', '[email protected]'],  -- List of UTF-8 strings
    ['2024-11-03 10:00:00', '2024-11-03 15:30:00'],  -- List of timestamps
    [{
        street: '123 Main St',
        city: 'New York',
        state: 'NY',
        postal_code: '10001',
        country: 'USA',
        coordinates: {'lat': 40.7128, 'lng': -74.0060},
        last_verified: '2024-11-03 12:00:00'
    }],                                       -- List containing UDT
    [100, 200, 300, 400],                     -- List of 32-bit integers
    [50554d6e-29bb-11e5-b345-feff819cdc9f, 
     50554d6e-29bb-11e5-b345-feff819cdc9g]    -- List of TimeUUIDs
);

User-Defined Types

-- First create the UDT
CREATE TYPE address (
    street text,
    city text,
    postal_code text,
    country text
);

-- Create table using UDT
CREATE TABLE user_profiles (
    id uuid PRIMARY KEY,
    name text,
    home_address FROZEN<address>
);

-- Insert using UDT
INSERT INTO user_profiles (
    id,
    name,
    home_address
) VALUES (
    uuid(),
    'John Doe',
    {
        street: '123 Main St',
        city: 'New York',
        postal_code: '10001',
        country: 'USA'
    }
);

Complex Nested Collections

-- Table with nested collections
INSERT INTO advanced_example (
    id,
    nested_map,
    nested_list_set
) VALUES (
    uuid(),
    {
        'locations': {
            street: '123 Main St',
            city: 'Boston'
        },
        'contacts': {
            phone: '555-0123',
            email: '[email protected]'
        }
    },
    [{'tag1', 'tag2'}, {'tag3', 'tag4'}]
);

TTL examples

INSERT INTO user_profiles (id, name, email) 
VALUES (123e4567-e89b-12d3-a456-426614174000, 'Alice', '[email protected]') 
USING TTL 3600;

Using TIMESTAMP example

INSERT INTO user_activity (
    user_id,                -- UUID type
    action,                -- UTF-8 string
    timestamp              -- Timestamp
) VALUES (
    123e4567-e89b-12d3-a456-426614174000,    -- UUID
    'login',
    toTimestamp(1604311200000)              -- Epoch value as timestamp
) USING TIMESTAMP 1604311200000;            -- Epoch value as insert timestamp

Write Consistency Levels

1. ALL - A write must be written to the commit log and memtable on all replica nodes in the cluster for that partition. This provides the highest consistency and the lowest availability.

2. EACH_QUORUM - A write must be written to the commit log and memtable on a quorum of replica nodes in each datacenter. This level ensures strong consistency across datacenters.

3. QUORUM - A write must be written to the commit log and memtable on a quorum of replica nodes. This level balances consistency and availability, ensuring strong consistency if some level of node failure is acceptable.

4. LOCAL_QUORUM - A write must be written to the commit log and memtable on a quorum of replica nodes within the local datacenter. This reduces consistency for higher availability within a single datacenter.

5. ONE - A write must be written to the commit log and memtable of at least one replica node. This is the default level and provides high availability at the cost of consistency.

6. TWO - A write must be written to the commit log and memtable of at least two replica nodes. This level provides slightly higher consistency than ONE.

7. THREE - A write must be written to the commit log and memtable of at least three replica nodes. This level provides a bit more consistency than TWO.

8. LOCAL_ONE - A write must be written to the commit log and memtable of at least one replica node within the local datacenter. This provides high availability within the local datacenter at the cost of consistency.

9. ANY - A write can be considered successful with acknowledgment from any replica node. This level provides the highest availability, sacrificing consistency. It's useful in situations where data loss is acceptable if no replicas are available.

Insert Using JSON

https://cassandra.apache.org/doc/stable/cassandra/cql/json.html

You can do an insert into Cassandra using JSON to map the values for columns as key values eg

INSERT INTO mytable JSON '{ "\"myKey\"": 0, "value": 0}' DEFAULT UNSET;

Where it gets complex is around the JSON encoding for the data types

• Data Preservation: Without DEFAULT UNSET, any columns omitted from the JSON payload will be set to null (creating tombstones). When doing insert as JSON gives users the ability to select DEFAULT UNSET (always there by default) or DEFAULT NULL. If they select DEFAULT NULL display a warning saying "Any column omitted from the JSON map will be set to NULL"
• Column Names: Case-sensitive column names must be surrounded with double quotes in the JSON string9. For example:
  sql
  INSERT INTO mytable JSON '{ ""myColumn"": "value", "regular_column": 123 }';

Also, the handling of UDTs needs to be looked at carefully eg

CREATE TYPE address (
    employee_id int,
    residence_address text,
    office_address text,
    city text
);

CREATE TABLE employee (
    employee_id int PRIMARY KEY,
    name text,
    address frozen<address>,
    salary text
);


INSERT INTO employee JSON '{
    "employee_id": 1234,
    "name": "Akhil",
    "address": {
        "employee_id": 1234,
        "residence_address": "65A Block",
        "office_address": "75D Block",
        "city": "Goa"
    },
    "salary": "12000"
}' DEFAULT UNSET;

[millerjp]

@mhmdkrmabd one thing. You know you added the uuid() function option for uuid data types on the insert. Make sure to look at the other data type functions (https://cassandra.apache.org/doc/5.0/cassandra/developing/cql/functions.html) theres a few in there like uuid(), now(), current_timestamp(), current_date() etc..