CQL Snippets for AxonOps Workbench

[millerjp]

This idea (thanks @netudima ) outlines a comprehensive implementation strategy for adding CQL snippet functionality to AxonOps Workbench. The feature will allow users to create, associate, and share Cassandra Query Language snippets with specific database objects, enhancing productivity and collaboration for Cassandra developers.

Implementing CQL snippets in AxonOps Workbench will significantly enhance the user experience for Cassandra developers by providing an organized system for storing, sharing, and reusing query patterns. By structuring the implementation around context-aware associations with database objects, users will benefit from more intuitive access to relevant snippets directly tied to their workflow.

The file-based storage approach using .cql files in a structured directory system provides flexibility for future enhancements while maintaining compatibility with version control systems and team sharing workflows. This implementation aligns with industry best practices for code snippet management while addressing the specific needs of Cassandra developers working with CQL.

Snippet Storage Architecture

The foundation of the CQL snippets feature relies on an organized file system structure that maps to Cassandra's hierarchical data model and stored in the workspace for the connection.

/snippets
  /_workspace  # Global snippets (not cluster-specific)
    system_queries.cql
    cross_cluster_utils.cql
  
  /clusters
    /{cluster_name}  # e.g. "prod_aws", "staging_gcp"
      /_general      # Cluster-wide snippets
        backup_scripts.cql
        cluster_health_checks.cql
      
      /keyspaces
        /{keyspace_name}  # e.g. "user_data"
          /_general      # Keyspace-level snippets
            crud_operations.cql
            ttl_management.cql
          
          /tables
            /{table_name}  # e.g. "user_profiles"
              time_series_queries.cql
              index_optimizations.cql

This structure allows for:

• Context-specific snippets associated with specific keyspaces and tables
• General snippets that apply to an entire keyspace
• Global snippets that aren't tied to specific database objects
• Can be checkedinto version control as with other workspace files

Each snippet should be stored as an individual CQL file with appropriate metadata headers to facilitate organization and searchability.

Snippet Metadata and Format

For optimal snippet management, each .cql file should contain both the query and relevant metadata in a standardized format:

-- TITLE: Get recent user transactions
-- DESCRIPTION: Retrieves the most recent transactions for a specific user with timestamp sorting
-- TAGS: transactions, user_data, performance
-- CREATED_BY: John Smith
-- CREATED_DATE: 2025-04-21
-- LAST_MODIFIED: 2025-04-28
-- ASSOCIATED_WITH: users_keyspace.transactions

SELECT * FROM users_keyspace.transactions
WHERE user_id = ?
ORDER BY transaction_time DESC
LIMIT 20;

This metadata approach provides:

• Descriptive information for snippet listings
• Searchable content through tags
• Tracking of creation and modification history
• Context for where the snippet is intended to be used

UI Implementation

The AxonOps Workbench interface should integrate snippets in a way that maintains context and provides easy access.

Sidebar Panel for Snippets

• A dedicated "Snippets" panel in the main UI sidebar
• Hierarchical view that mirrors the Cassandra structure (keyspaces → tables)
• Search functionality with filters for tags, names, and content

Context-Aware Snippet Access

• Right-click options on keyspace/table objects to view associated snippets
• Option to "Create Snippet for [selected object]" in relevant contexts
• Snippet suggestions based on current context when writing CQL

Snippet Editor

• Syntax highlighting for CQL (pretty much the same as what we do in the enhanced console CQL input)
• Metadata form fields to capture required information
• Preview functionality to test snippets before saving - with warnings if they test it and its possibly damaging or changes data

Core Snippet Operations

Creating Snippets

The creation workflow should support multiple entry points:

1. From the Cassandra object explorer:

   • Right-click on a keyspace/table
   • Select "Create New Snippet"
   • Pre-populated association metadata based on selection

2. From existing queries:

   • Highlight CQL in query editor
   • Right-click and select "Save as Snippet"
   • Choose association (global, keyspace, table,view, index etc..)

3. From the Snippets panel:

   • "New Snippet" button
   • Choose association from dropdown lists

Editing and Management

Provide comprehensive management capabilities:

• Double-click to open snippet in editor
• Version history for tracking changes
• Options to rename, duplicate, or delete snippets
• Ability to change snippet associations

Sharing Capabilities

Enable collaboration through:

• Export/import functionality for individual snippets or collections
• Right click on a query in the enhanced console and add as snippet
• Integration with version control systems (through workspace)

Integration with Cassandra Tree Explorer

For seamless workflow integration, connect snippets with the existing Cassandra object explorer:

• Visual indicators for objects that have associated snippets
• Quick snippet count badges next to keyspace/table names
• Filter option to show only objects with snippets
• Preview panel to see snippet content without opening editor
• Ability to run snippet (with info warning based on what its doing)

Search and Discovery

Implement robust search capabilities:

• Full-text search across snippet content and metadata
• Tag-based filtering system
• Recent/favorite snippets section
• Sorting options (alphabetical, recently used, created date)

Destructive CQL Operations Detection and User Warning System

Identification of Destructive CQL Keywords

To implement safety checks for CQL snippets in AxonOps Workbench, we must identify keywords and command patterns indicating data modification or schema changes.

Core Destructive Verbs

1. DELETE

   • Removes column data or entire rows
   • Example: DELETE FROM users WHERE id = ?

2. DROP

   • Schema destruction commands:
     • DROP KEYSPACE (deletes entire database)
     • DROP TABLE/DROP MATERIALIZED VIEW
     • DROP INDEX/DROP TYPE

3. TRUNCATE

   • Irreversibly deletes all data from a table

4. INSERT/UPDATE

   • Modifies data, though non-destructive to schema
   • Requires warnings due to data mutation risks

5. ALTER

   • Schema modifications:
     • ALTER KEYSPACE (changes replication strategy
     • ALTER TABLE (modifies columns/properties)
     • ALTER TYPE (changes user-defined types)

6. BATCH

   • Atomic execution of multiple DML statements
   • High risk due to combined operations

Schema/Object Creation Keywords

1. CREATE

   • CREATE TABLE/CREATE KEYSPACE (schema changes)
   • CREATE INDEX (impacts query performance)

2. GRANT/REVOKE

   • Permission changes affecting access control

Secondary Indicators

• IF EXISTS/IF NOT EXISTS clauses often accompany destructive operations
• USING TIMESTAMP/USING TTL in mutations
• WHERE clauses in DELETE/UPDATE (scope of impact)

---

Implementation Strategy

Keyword Detection Logic

DESTRUCTIVE_KEYWORDS = {
    "DELETE", "DROP", "TRUNCATE", "INSERT", "UPDATE", 
    "ALTER", "BATCH", "CREATE", "GRANT", "REVOKE"
}

def contains_destructive_operation(cql: str) -> bool:
    normalized = cql.upper().split()
    return any(keyword in normalized for keyword in DESTRUCTIVE_KEYWORDS)

Warning Messaging System

When destructive keywords are detected:

UI Alert Template

⚠️ Notice: This operation may modify data or schema
The snippet you’re about to run includes commands that can change data or database structure. Please review before executing.
Confirm execution only if you understand these consequences.

---

Edge Case Handling

1. Comment False Positives
   • Ignore keywords in -- or /* */ comments via regex filtering:

   import re
   
   def sanitize_cql(cql: str) -> str:
       return re.sub(r"--.*|\/\*.*?\*\/", "", cql, flags=re.DOTALL)