# 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
```sql
-- 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
```sql
-- 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
```sql
-- 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
```sql
-- 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
```sql
-- 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
```sql
DROP TABLE users.profiles;
```

### ✅ DESCRIBE - Show Schema
```sql
DESCRIBE users.profiles;
DESC users.profiles;  -- Short form
```

### ✅ SHOW TABLES - List Tables
```sql
SHOW TABLES;
```

---

## Using SQL via REST API

### Execute Single SQL Statement

```bash
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:**
```json
{
  "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

```bash
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:**
```json
{
  "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

```sql
-- 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

```sql
-- 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

```sql
-- 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:

| Operation | SQL | CQL |
|-----------|-----|-----|
| **Select** | `SELECT * FROM users.profiles WHERE id = 'user-1'` | `SELECT * FROM users.profiles WHERE id = 'user-1'` |
| **Insert** | `INSERT INTO users.profiles (id, name) VALUES ('1', 'Alice')` | `INSERT INTO users.profiles (id, name) VALUES ('1', 'Alice')` |
| **Update** | `UPDATE users.profiles SET name = 'Bob' WHERE id = '1'` | `UPDATE users.profiles SET name='Bob' WHERE id='1'` |
| **Delete** | `DELETE FROM users.profiles WHERE id = '1'` | `DELETE FROM users.profiles WHERE id='1'` |

---

## Using SQL in CubeShell

CubeShell automatically detects SQL vs CQL syntax:

```bash
$ ./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

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

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

### Multiple Conditions

```sql
-- 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

```sql
-- 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

```bash
#!/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

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

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

### From Cassandra

```sql
-- 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!** 🎉