# Cube Database - SQL Quick Reference

## Data Definition Language (DDL)

### CREATE TABLE
```sql
CREATE TABLE keyspace.table (
    column1 TYPE PRIMARY KEY,
    column2 TYPE,
    column3 TYPE
);
```

**Example:**
```sql
CREATE TABLE users.profiles (
    id TEXT PRIMARY KEY,
    name TEXT,
    email TEXT,
    age TEXT
);
```

### DROP TABLE
```sql
DROP TABLE keyspace.table;
```

### DESCRIBE
```sql
DESCRIBE keyspace.table;
DESC keyspace.table;  -- Short form
```

### SHOW TABLES
```sql
SHOW TABLES;
```

---

## Data Manipulation Language (DML)

### SELECT
```sql
-- All columns
SELECT * FROM keyspace.table WHERE id = 'value';

-- Specific columns
SELECT col1, col2, col3 FROM keyspace.table WHERE id = 'value';

-- Multiple conditions
SELECT * FROM keyspace.table WHERE id = 'value' AND status = 'active';
```

### INSERT
```sql
INSERT INTO keyspace.table (col1, col2, col3) 
VALUES ('val1', 'val2', 'val3');
```

### UPDATE
```sql
-- Single column
UPDATE keyspace.table SET col1 = 'new_value' WHERE id = 'value';

-- Multiple columns
UPDATE keyspace.table 
SET col1 = 'val1', col2 = 'val2' 
WHERE id = 'value';
```

### DELETE
```sql
DELETE FROM keyspace.table WHERE id = 'value';
```

---

## REST API

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

### Execute Batch
```bash
curl -X POST http://localhost:8080/api/v1/sql/batch \
  -H "Content-Type: application/json" \
  -d '{
    "statements": [
      "INSERT INTO users (id, name) VALUES ('\''1'\'', '\''Alice'\'')",
      "INSERT INTO users (id, name) VALUES ('\''2'\'', '\''Bob'\'')"
    ]
  }'
```

---

## Common Patterns

### User CRUD
```sql
-- Create
CREATE TABLE app.users (id TEXT PRIMARY KEY, username TEXT, email TEXT);

-- Insert
INSERT INTO app.users (id, username, email) 
VALUES ('u1', 'alice', 'alice@example.com');

-- Read
SELECT * FROM app.users WHERE id = 'u1';

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

-- Delete
DELETE FROM app.users WHERE id = 'u1';
```

### Product Management
```sql
-- Create catalog
CREATE TABLE shop.products (
    sku TEXT PRIMARY KEY,
    name TEXT,
    price TEXT,
    stock TEXT
);

-- Add product
INSERT INTO shop.products (sku, name, price, stock) 
VALUES ('SKU-001', 'Widget', '19.99', '100');

-- Update price
UPDATE shop.products SET price = '17.99' WHERE sku = 'SKU-001';

-- Update stock
UPDATE shop.products SET stock = '95' WHERE sku = 'SKU-001';
```

### Session Management
```sql
-- Create sessions
CREATE TABLE auth.sessions (
    session_id TEXT PRIMARY KEY,
    user_id TEXT,
    expires_at TEXT
);

-- Create session
INSERT INTO auth.sessions (session_id, user_id, expires_at) 
VALUES ('sess-123', 'user-1', '2024-12-31');

-- Check session
SELECT * FROM auth.sessions WHERE session_id = 'sess-123';

-- Delete expired
DELETE FROM auth.sessions WHERE session_id = 'sess-123';
```

---

## Supported Data Types

- **TEXT** - String data (default/recommended)
- **INTEGER** - Integer numbers (stored as TEXT)
- **FLOAT** - Floating point (stored as TEXT)
- **BOOLEAN** - true/false (stored as TEXT)

---

## Syntax Rules

1. **Keywords** - Case insensitive (SELECT = select)
2. **Strings** - Use single quotes: `'value'` or double quotes: `"value"`
3. **Semicolons** - Optional but recommended: `SELECT * FROM users;`
4. **Keyspace** - Optional, defaults to `default`: `users.profiles` or just `profiles`
5. **WHERE clause** - Required for SELECT, UPDATE, DELETE
6. **Primary Key** - First column or specify `PRIMARY KEY`

---

## Common Mistakes

❌ **Forgot WHERE clause**
```sql
SELECT * FROM users  -- Missing WHERE
```
✅ **Always use WHERE for queries**
```sql
SELECT * FROM users WHERE id = 'user-1'
```

❌ **Unquoted values**
```sql
SELECT * FROM users WHERE id = user-1  -- No quotes
```
✅ **Quote string values**
```sql
SELECT * FROM users WHERE id = 'user-1'
```

❌ **Wrong column count**
```sql
INSERT INTO users (id, name) VALUES ('1', 'Alice', 'alice@example.com')
-- 2 columns, 3 values
```
✅ **Match columns and values**
```sql
INSERT INTO users (id, name, email) VALUES ('1', 'Alice', 'alice@example.com')
```

---

## Migration Cheatsheet

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

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

### From Cassandra
```sql
-- Cassandra CQL (works as-is)
SELECT * FROM users.profiles WHERE id = 'user-1';

-- Cube SQL (identical)
SELECT * FROM users.profiles WHERE id = 'user-1';
```

---

## Performance Tips

1. ✅ Always use WHERE with primary key
2. ✅ Batch related operations
3. ✅ Use specific column selection when possible
4. ✅ Keep keyspace.table naming consistent

---

## Quick Examples

```sql
-- Simple CRUD
CREATE TABLE test.data (id TEXT PRIMARY KEY, value TEXT);
INSERT INTO test.data (id, value) VALUES ('1', 'hello');
SELECT * FROM test.data WHERE id = '1';
UPDATE test.data SET value = 'world' WHERE id = '1';
DELETE FROM test.data WHERE id = '1';

-- With multiple columns
CREATE TABLE test.users (id TEXT PRIMARY KEY, name TEXT, email TEXT, age TEXT);
INSERT INTO test.users (id, name, email, age) VALUES ('u1', 'Alice', 'alice@example.com', '30');
SELECT name, email FROM test.users WHERE id = 'u1';
UPDATE test.users SET name = 'Alice J.', age = '31' WHERE id = 'u1';

-- Batch operations
INSERT INTO test.users (id, name, email) VALUES ('u2', 'Bob', 'bob@example.com');
INSERT INTO test.users (id, name, email) VALUES ('u3', 'Charlie', 'charlie@example.com');
UPDATE test.users SET email = 'bob.new@example.com' WHERE id = 'u2';
DELETE FROM test.users WHERE id = 'u3';
```

---

## Need Help?

- Full Guide: `SQL_GUIDE.md`
- API Docs: `README.md`
- Examples: Run `mvn exec:java -Dexec.mainClass="com.cube.examples.SQLExamples"`
- Tests: `./test-sql-api.sh`

---

**Cube Database - Now with SQL Support!** 🎉