[feat]: Right Click CREATE/ALTER/DROP Counter Tables

[millerjp]

Description

Add functionality to create/alter/drop a counter table via right-click context menu on the tree. Also, add a new grouping under keyspaces to list counter tables.

As a database administrator, I want to create, alter and drop counter tables through a GUI interface so that I can easily manage counter tables without writing CQL manually and review the generated CQL before it gets applied.

A Counter Table in Cassandra is a specialized table designed for tracking incremental counts. Key characteristics include:

• Designed specifically for maintaining numeric counters
• Supports atomic increment and decrement operations
• Cannot contain regular columns alongside counter columns
• Useful for tracking metrics like page views, user interactions, or event frequencies
• Provides high-performance, distributed counting mechanism

Example Use Case: Tracking total number of views for articles, tracking user interactions, or maintaining real-time analytics counters.

Important Consideration:

• Counter tables are append-only and have specific limitations compared to regular tables.

Table Meta Data

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

There is a lot of extra meta data users can add to a table create statmenet - compaction, caching etc.. These need to be handled. The approach here is important as it will be reused in other places.

CREATE TABLE system_auth.roles (
    role text PRIMARY KEY,
    can_login boolean,
    is_superuser boolean,
    member_of set<text>,
    salted_hash text
) WITH additional_write_policy = '99p'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = 'role definitions'
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '16', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 7776000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99p';

All of those values after the WITH statments are optional and Cassandra will set them to defaults if the users do not specify them as party of the create statment eg

CREATE TABLE system_auth.roles (
    role text PRIMARY KEY,
    can_login boolean,
    is_superuser boolean,
    member_of set<text>,
    salted_hash text
) WITH  gc_grace_seconds = 8000000;

Cassandra will add the defaults for the other fields automatically. However, the problem here is that the options available differ between Cassandra versions so its hard to know what to display to users. Additonally, what are the field options they can choose from?

The approach I was thinking is we could discover these values dynamically by looking at one of the system tables eg system.local - this would be ok for the defaults to have in there for users to select. And then allow them to add other key value pairs as they want or delete the ones that we prepopulate.

In terms of the fields themselves there is some validation we will need to support. Most of them are just strings and ints but we need to handle validating values make sense, so theres a few fields (https://cassandra.apache.org/doc/stable/cassandra/cql/ddl.html#create-table-general-options) we can have input validation on.

Its entirely possible that there will be options on the tables that we have missed or are legacy etc.. This is why we allow for users to enter their own key:values themselves. Its important with Alter that we only send in the changed values and allow users to add new ones based one the same approach we take for create.

Limitations of Counter Tables in Cassandra

Cassandra's counter tables have several critical restrictions that developers must carefully consider:

Structural Limitations

• Exclusive Column Types: A table with counter columns can only contain counter columns
• Counter columns cannot be part of the PRIMARY KEY
• All non-counter columns must be defined as part of the primary key

Operational Constraints

• No Indexing: Counter columns cannot be indexed
• No Deletion: Counter columns cannot be deleted or re-added
• No Time-to-Live (TTL): Cassandra rejects using TTL or timestamps with counter columns

Consistency and Performance Challenges

• Counter update operations are not idempotent
• Updates require reading from and writing to multiple cluster nodes, which can increase network traffic and latency
• Inefficient partition counting (queries count cells at O(N) complexity)

Unique Behavioral Characteristics

• A counter does not exist until first incremented/decremented
• The first increment/decrement is made as if the prior value was 0
• Deletion is only guaranteed to work the first time

Important Consideration: While counters provide high availability and fault tolerance, they come with significant trade-offs in flexibility and performance.

Create Counter Tables

A dialog is displayed allowing users to choose their options for creating the counter table. Counter tables are contextual to a keyspace, so the option to Create Counter Table should be displayed when right-clicking on a keyspace.

UI Mockup

[Create Counter Table]
------------------------
Table Name: <keyspacename>.[_______]
Error msg: <validation message appears here>

Add Primary Key
- Partion Key: <UI allowing Users to add partition key columns>
- Clustering Column: <UI allowing Users to add clustering columns>

Add Counter Columns
<UI allowing Users to add counter columns>

[Review CQL] [Cancel]
------------------------

Technical Requirements

Dialog Flow

1. User right-clicks on a keyspace node in tree
2. "Create Counter Table" option appears in context menu
3. Clicking opens a dialog (as above)
4. [Review CQL] button is not clickable until a valid selection has been made

Counter Table Name Validation

• Must be unique under keyspace
• Allowed characters: alphanumeric and underscores
• No spaces or special characters permitted
• Real-time validation with error messages

Add Primary Key

• A UI to allow users to define the primary key columns for the counter table
• Can have multuple partition key columns
• Can have multuple clustering key columns
• The order of partition key columns and clustering columns can be moved around
• The order decided upon correspose to the order in the CQL

Add Counter Column

• A UI to allow users to add counter columns
• Counter columns are always of type counter
• Require a unique column name

CQL Preview & Execution

• Display generated CQL
• Allow manual editing of generated CQL
• Execute button to run the statement
• Show execution results/errors

Example CQL Outputs

CREATE TABLE mykeyspace.page_views (
    page_id text PRIMARY KEY,
    view_count counter
);

CREATE TABLE mykeyspace.user_interactions (
    user_id text,
    interaction_type text,
    count counter,
    PRIMARY KEY ((user_id), interaction_type)
);

CREATE TABLE mykeyspace.user_interactions1 (
    user_id text,
    interaction_type text,
    count counter,
    count2 counter,
    PRIMARY KEY ((user_id, interaction_type))
);

CREATE TABLE mykeyspace.user_interactions2 (
    user_id text,
    user_name text,
    interaction_type text,
    count counter,
    count2 counter,
    count3 counter,
    PRIMARY KEY ((user_id, interaction_type), user_name)
);

Error Handling

• Invalid table name
• CQL execution errors in Enhanced Console
• Must have at least 1 counter column

Acceptance Criteria

1. Right-click context menu shows "Create Counter Table" option
2. Dialog appears with all specified fields
3. Real-time validation of table name
4. CQL preview shows correct statement
5. CQL execution works as expected
6. Proper error handling and user feedback

Drop Counter Table

A dialog is displayed with a warning and the user can see the CQL before it gets applied.

UI Mockup

[Drop Counter Table]
------------------------
<warning message goes here>

Table Name: <keyspace name>.<table name>

CQL:
DROP TABLE <keyspace name>.<table name>;

[Execute] [Cancel]
------------------------

Technical Requirements

1. User right-clicks on a counter table node in tree
2. "Drop Counter Table" option appears in context menu
3. Provides clear warning about irreversible action

Acceptance Criteria

1. Right-click context menu shows "Drop Counter Table" option
2. Dialog appears with warning
3. CQL preview shows correct statement
4. CQL execution works as expected
5. Proper error handling

Alter Counter Table

A dialog is displayed allowing modification of counter table structure.

UI Mockup

[Alter Counter Table]
------------------------
Table Name: <keyspace name>.<table name>

Modify Counter Columns:
<UX for adding/removing counter columns>

[Review CQL] [Cancel]
------------------------

Technical Requirements

1. User right-clicks on a counter table node in tree
2. "Alter Counter Table" option appears in context menu
3. Allows adding new counter columns
4. Provides CQL preview before execution

Example CQL Outputs

ALTER TABLE mykeyspace.page_views ADD COLUMN unique_views counter;

Acceptance Criteria

1. Right-click context menu shows "Alter Counter Table" option
2. Dialog allows column modifications
3. CQL preview shows correct statement
4. CQL execution works as expected
5. Proper error handling
6. Its possible to drop the counter columns so there are no counter columns left. In this case, the table should no longer be under the counter tables tree as the criteria for being in there is that there is a counter column on the table

Dependencies

• Access to cluster metadata
• Validation against existing table structures