Cube Database - SQL Support

Overview

Cube database now supports standard SQL syntax in addition to CQL! Use familiar SQL commands for database operations.

Supported SQL Statements

✅ SELECT - Query Data

-- Select all columns
SELECT * FROM users.profiles WHERE id = 'user-1';

-- Select specific columns
SELECT name, email FROM users.profiles WHERE id = 'user-1';

-- Multiple conditions
SELECT * FROM users.profiles WHERE id = 'user-1' AND status = 'active';

✅ INSERT - Add Data

-- Insert a single row
INSERT INTO users.profiles (id, name, email, age) 
VALUES ('user-1', 'Alice Johnson', 'alice@example.com', '30');

-- Insert with quotes handling
INSERT INTO messages.inbox (id, subject, body) 
VALUES ('msg-1', 'Hello World', 'This is a test message');

✅ UPDATE - Modify Data

-- Update single column
UPDATE users.profiles SET age = '31' WHERE id = 'user-1';

-- Update multiple columns
UPDATE users.profiles 
SET name = 'Alice J.', email = 'alice.j@example.com', age = '31' 
WHERE id = 'user-1';

✅ DELETE - Remove Data

-- Delete by primary key
DELETE FROM users.profiles WHERE id = 'user-1';

-- Delete with multiple conditions
DELETE FROM sessions.active WHERE user_id = 'user-1' AND expired = 'true';

✅ CREATE TABLE - Define Schema

-- Create table with primary key
CREATE TABLE users.profiles (
    id TEXT PRIMARY KEY,
    name TEXT,
    email TEXT,
    age TEXT
);

-- Primary key on first column (implicit)
CREATE TABLE products.inventory (
    sku TEXT,
    name TEXT,
    price TEXT,
    stock TEXT
);

✅ DROP TABLE - Remove Table

DROP TABLE users.profiles;

✅ DESCRIBE - Show Schema

DESCRIBE users.profiles;
DESC users.profiles;  -- Short form

✅ SHOW TABLES - List Tables

SHOW TABLES;

Using SQL via REST API

Execute Single SQL Statement

curl -X POST http://localhost:8080/api/v1/sql/execute \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT * FROM users.profiles WHERE id = '\''user-1'\''"
  }'

Response:

{
  "success": true,
  "message": "Query executed successfully",
  "rows": [
    {
      "id": "user-1",
      "name": "Alice Johnson",
      "email": "alice@example.com",
      "age": "30"
    }
  ],
  "rowCount": 1
}

Execute Batch SQL Statements

curl -X POST http://localhost:8080/api/v1/sql/batch \
  -H "Content-Type: application/json" \
  -d '{
    "statements": [
      "CREATE TABLE test.users (id TEXT PRIMARY KEY, name TEXT)",
      "INSERT INTO test.users (id, name) VALUES ('\''1'\'', '\''Alice'\'')",
      "INSERT INTO test.users (id, name) VALUES ('\''2'\'', '\''Bob'\'')",
      "SELECT * FROM test.users WHERE id = '\''1'\''"
    ]
  }'

Response:

{
  "success": true,
  "total": 4,
  "successful": 4,
  "failed": 0,
  "results": [
    {
      "sql": "CREATE TABLE...",
      "success": true,
      "message": "Table created: test.users"
    },
    {
      "sql": "INSERT INTO...",
      "success": true,
      "message": "Row inserted: 1",
      "rowsAffected": 1
    },
    ...
  ]
}

SQL Examples

Example 1: User Management

-- Create users table
CREATE TABLE myapp.users (
    id TEXT PRIMARY KEY,
    username TEXT,
    email TEXT,
    created_at TEXT
);

-- Insert users
INSERT INTO myapp.users (id, username, email, created_at) 
VALUES ('u1', 'alice', 'alice@example.com', '2024-01-15');

INSERT INTO myapp.users (id, username, email, created_at) 
VALUES ('u2', 'bob', 'bob@example.com', '2024-01-16');

-- Query users
SELECT * FROM myapp.users WHERE id = 'u1';
SELECT username, email FROM myapp.users WHERE id = 'u2';

-- Update user
UPDATE myapp.users SET email = 'alice.new@example.com' WHERE id = 'u1';

-- Delete user
DELETE FROM myapp.users WHERE id = 'u2';

Example 2: Product Catalog

-- Create products table
CREATE TABLE shop.products (
    sku TEXT PRIMARY KEY,
    name TEXT,
    price TEXT,
    category TEXT,
    stock TEXT
);

-- Add products
INSERT INTO shop.products (sku, name, price, category, stock) 
VALUES ('LAPTOP-001', 'MacBook Pro', '2499.99', 'Electronics', '10');

INSERT INTO shop.products (sku, name, price, category, stock) 
VALUES ('MOUSE-001', 'Wireless Mouse', '29.99', 'Accessories', '50');

-- Query products
SELECT * FROM shop.products WHERE sku = 'LAPTOP-001';

-- Update stock
UPDATE shop.products SET stock = '8' WHERE sku = 'LAPTOP-001';

-- Update price
UPDATE shop.products SET price = '2299.99' WHERE sku = 'LAPTOP-001';

Example 3: Session Management

-- Create sessions table
CREATE TABLE auth.sessions (
    session_id TEXT PRIMARY KEY,
    user_id TEXT,
    ip_address TEXT,
    user_agent TEXT,
    expires_at TEXT
);

-- Create session
INSERT INTO auth.sessions (session_id, user_id, ip_address, user_agent, expires_at) 
VALUES ('sess-abc123', 'user-1', '192.168.1.100', 'Mozilla/5.0', '2024-01-20');

-- Query session
SELECT * FROM auth.sessions WHERE session_id = 'sess-abc123';

-- Update session expiry
UPDATE auth.sessions SET expires_at = '2024-01-21' WHERE session_id = 'sess-abc123';

-- Delete expired session
DELETE FROM auth.sessions WHERE session_id = 'sess-abc123';

SQL vs CQL

Both syntaxes are supported and work identically:

Using SQL in CubeShell

CubeShell automatically detects SQL vs CQL syntax:

$ ./run-shell.sh

cube> SELECT * FROM users.profiles WHERE id = 'user-1';
✓ Found 1 row
[displays table]

cube> INSERT INTO users.profiles (id, name, email) VALUES ('u2', 'Bob', 'bob@example.com');
✓ Row inserted: u2

cube> UPDATE users.profiles SET name = 'Bob Smith' WHERE id = 'u2';
✓ Row updated: u2

cube> DELETE FROM users.profiles WHERE id = 'u2';
✓ Row deleted: u2

Advanced SQL Features

Keyspace Handling

-- Explicit keyspace
SELECT * FROM myapp.users WHERE id = 'user-1';

-- Default keyspace (assumes 'default')
SELECT * FROM users WHERE id = 'user-1';

Multiple Conditions

-- AND conditions
SELECT * FROM users.profiles 
WHERE id = 'user-1' AND status = 'active';

-- Multiple updates
UPDATE users.profiles 
SET name = 'Alice', email = 'alice@example.com', status = 'active' 
WHERE id = 'user-1';

Column Selection

-- All columns
SELECT * FROM users.profiles WHERE id = 'user-1';

-- Specific columns
SELECT id, name FROM users.profiles WHERE id = 'user-1';

-- Multiple columns
SELECT id, name, email, age FROM users.profiles WHERE id = 'user-1';

SQL Compatibility

Supported Features

✅ SELECT with WHERE clause
✅ INSERT with explicit columns
✅ UPDATE with SET clause
✅ DELETE with WHERE clause
✅ CREATE TABLE with column definitions
✅ PRIMARY KEY constraints
✅ Multiple column updates
✅ AND conditions in WHERE
✅ Single and double quotes for strings

Not Yet Supported

❌ JOIN operations
❌ GROUP BY / HAVING
❌ ORDER BY (use application-level sorting)
❌ LIMIT / OFFSET
❌ Subqueries
❌ Aggregate functions (COUNT, SUM, AVG)
❌ OR conditions in WHERE
❌ Transactions (BEGIN/COMMIT)

Testing SQL Support

Test Script

#!/bin/bash

API="http://localhost:8080/api/v1/sql/execute"

# Test CREATE TABLE
curl -X POST $API -H "Content-Type: application/json" \
  -d '{"sql": "CREATE TABLE test.users (id TEXT PRIMARY KEY, name TEXT)"}'

# Test INSERT
curl -X POST $API -H "Content-Type: application/json" \
  -d '{"sql": "INSERT INTO test.users (id, name) VALUES ('\''1'\'', '\''Alice'\'')"}'

# Test SELECT
curl -X POST $API -H "Content-Type: application/json" \
  -d '{"sql": "SELECT * FROM test.users WHERE id = '\''1'\''"}'

# Test UPDATE
curl -X POST $API -H "Content-Type: application/json" \
  -d '{"sql": "UPDATE test.users SET name = '\''Alice J.'\'' WHERE id = '\''1'\''"}'

# Test DELETE
curl -X POST $API -H "Content-Type: application/json" \
  -d '{"sql": "DELETE FROM test.users WHERE id = '\''1'\''"}'

Performance Notes

• SQL statements are translated to CQL internally
• Performance is identical to using CQL directly
• No performance overhead for SQL translation
• Both SQL and CQL use the same storage engine

Migration from Other Databases

From MySQL/PostgreSQL

-- MySQL/PostgreSQL
SELECT * FROM users WHERE id = 1;

-- Cube (use quotes for values)
SELECT * FROM users WHERE id = '1';

From Cassandra

-- Cassandra CQL
SELECT * FROM users.profiles WHERE id = 'user-1';

-- Cube SQL (same syntax!)
SELECT * FROM users.profiles WHERE id = 'user-1';

Summary

✅ Standard SQL syntax - Use familiar SQL commands
✅ REST API support - Execute SQL via HTTP
✅ CubeShell integration - SQL works in interactive shell
✅ Batch execution - Run multiple statements at once
✅ No performance penalty - SQL translates to efficient CQL
✅ Easy migration - Bring SQL knowledge to Cube

Cube database now speaks SQL! 🎉