Mist Enhanced In-Memory MySQL Database

package main

import (
    "fmt"
    "github.com/abbychau/mist"
)

func main() {
    engine := mist.NewSQLEngine()
    
    // 1. Enhanced Data Types & Constraints
    engine.Execute(`CREATE TABLE companies (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL UNIQUE,
        founded_date DATE,
        type ENUM('startup', 'corporation', 'nonprofit') DEFAULT 'startup',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )`)
    
    // 2. Foreign Key Relationships with Actions
    engine.Execute(`CREATE TABLE employees (
        id INT AUTO_INCREMENT PRIMARY KEY,
        company_id INT NOT NULL,
        email VARCHAR(255) NOT NULL UNIQUE,
        name VARCHAR(100),
        hire_date DATE,
        status ENUM('active', 'inactive', 'terminated') DEFAULT 'active',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
    )`)
    
    // 3. Data with Enhanced Types
    engine.Execute("INSERT INTO companies (name, founded_date, type) VALUES ('Tech Corp', '2020-01-15', 'startup')")
    engine.Execute("INSERT INTO employees (company_id, email, name, hire_date, status) VALUES (1, '[email protected]', 'John Smith', '2024-01-01', 'active')")
    
    // 4. Automatic Timestamp Updates
    engine.Execute("UPDATE employees SET name = 'John A. Smith' WHERE id = 1")
    // ^ This automatically updates the updated_at field
    
    // 5. GROUP BY with Enhanced Aggregates
    result, _ := engine.Execute(`
        SELECT 
            c.type,
            COUNT(e.id) as employee_count,
            AVG(DATEDIFF(CURDATE(), e.hire_date)) as avg_tenure_days
        FROM companies c
        LEFT JOIN employees e ON c.id = e.company_id
        GROUP BY c.type
        HAVING employee_count > 0
    `)
    
    fmt.Println("Company statistics by type:")
    mist.PrintResult(result)
    
    // 6. Referential Integrity in Action
    // Foreign key constraints prevent orphaned records:
    // This would fail: engine.Execute("INSERT INTO employees (company_id, email, name) VALUES (999, '[email protected]', 'Test User')")
    
    // CASCADE DELETE: Deleting a company removes all its employees
    // engine.Execute("DELETE FROM companies WHERE id = 1") // Would cascade to employees
    
    // 7. UNIQUE Constraints prevent duplicates
    // This would fail: engine.Execute("INSERT INTO employees (company_id, email, name) VALUES (1, '[email protected]', 'Another John')")
    
    // 8. ENUM Validation
    // This would fail: engine.Execute("UPDATE employees SET status = 'invalid_status' WHERE id = 1")
    
    fmt.Println("\n✅ All enhanced features working correctly!")
    fmt.Println("🔑 Foreign key constraints ensure data integrity")
    fmt.Println("📅 DATE types handle proper date validation")
    fmt.Println("🎯 ENUM types enforce valid values")
    fmt.Println("🔒 UNIQUE constraints prevent duplicates")
    fmt.Println("⏰ Timestamps update automatically")
    fmt.Println("📊 GROUP BY works with all aggregate functions")
}

package main

import (
    "fmt"
    "log"
    "github.com/abbychau/mist"
)

func main() {
    engine := mist.NewSQLEngine()

    // Setup test table
    engine.Execute(`CREATE TABLE accounts (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50) NOT NULL,
        balance DECIMAL(10,2) NOT NULL DEFAULT 0.00
    )`)

    engine.Execute("INSERT INTO accounts (name, balance) VALUES ('Alice', 1000.00)")
    engine.Execute("INSERT INTO accounts (name, balance) VALUES ('Bob', 500.00)")

    fmt.Println("=== Basic Transactions ===")

    // Basic transaction with commit
    engine.Execute("START TRANSACTION")
    engine.Execute("UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice'")
    engine.Execute("UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob'")
    engine.Execute("COMMIT") // Changes are permanent

    fmt.Println("=== Nested Transactions ===")

    // Nested transactions with selective rollback
    engine.Execute("START TRANSACTION") // Level 1
    engine.Execute("UPDATE accounts SET balance = balance - 50 WHERE name = 'Alice'")

    engine.Execute("BEGIN") // Level 2 (nested)
    engine.Execute("UPDATE accounts SET balance = balance + 200 WHERE name = 'Bob'")
    engine.Execute("INSERT INTO accounts (name, balance) VALUES ('Charlie', 300.00)")
    engine.Execute("ROLLBACK") // Rollback level 2 only (Charlie gone, Bob's +200 undone)

    engine.Execute("COMMIT") // Commit level 1 (Alice's -50 remains)

    fmt.Println("=== Savepoints ===")

    // Advanced savepoint usage
    engine.Execute("START TRANSACTION")
    engine.Execute("UPDATE accounts SET balance = balance - 25 WHERE name = 'Alice'")

    engine.Execute("SAVEPOINT sp1") // Create savepoint
    engine.Execute("UPDATE accounts SET balance = balance + 75 WHERE name = 'Bob'")
    engine.Execute("INSERT INTO accounts (name, balance) VALUES ('David', 150.00)")

    engine.Execute("SAVEPOINT sp2") // Another savepoint
    engine.Execute("UPDATE accounts SET balance = balance * 1.1 WHERE name = 'Alice'") // 10% bonus

    // Rollback to sp1 (undoes David insert, Bob +75, and Alice bonus)
    engine.Execute("ROLLBACK TO SAVEPOINT sp1")

    engine.Execute("RELEASE SAVEPOINT sp1") // Clean up savepoint
    engine.Execute("COMMIT") // Commit remaining changes

    // Query final state
    result, _ := engine.Execute("SELECT name, balance FROM accounts ORDER BY name")
    fmt.Println("\nFinal account balances:")
    mist.PrintResult(result)

    fmt.Println("\n=== Transaction Error Handling ===")

    // Demonstrate transaction isolation
    engine.Execute("START TRANSACTION")
    engine.Execute("UPDATE accounts SET balance = 999999 WHERE name = 'Alice'")

    // In a real scenario, you might check business rules here
    // and rollback if they fail
    engine.Execute("ROLLBACK") // Undo the unrealistic balance

    fmt.Println("✅ Transaction features:")
    fmt.Println("  🔄 Nested transactions with independent rollback")
    fmt.Println("  📍 Named savepoints for fine-grained control")
    fmt.Println("  🔒 Full ACID properties within memory scope")
    fmt.Println("  🛡️ Automatic state restoration on rollback")
    fmt.Println("  🎯 Thread-safe transaction management")
}

package main

import (
    "fmt"
    "github.com/abbychau/mist"
)

func main() {
    engine := mist.NewSQLEngine()
    
    // Setup initial data
    engine.Execute("CREATE TABLE orders (id INT, customer VARCHAR(50), amount FLOAT)")
    
    // Start recording all queries
    engine.StartRecording()
    
    // Execute business logic queries
    engine.Execute("INSERT INTO orders VALUES (1, 'Alice', 299.99)")
    engine.Execute("INSERT INTO orders VALUES (2, 'Bob', 149.50)")
    engine.Execute("UPDATE orders SET amount = 249.99 WHERE id = 1")
    engine.Execute("SELECT customer, SUM(amount) FROM orders GROUP BY customer")
    
    // Stop recording
    engine.EndRecording()
    
    // Analyze recorded queries
    queries := engine.GetRecordedQueries()
    fmt.Printf("Business logic executed %d queries:\n", len(queries))
    
    for i, query := range queries {
        fmt.Printf("%d. %s\n", i+1, query)
    }
    
    // Use for debugging, testing, or audit logs
    // Perfect for understanding query execution patterns
}

# Start interactive mode
./mist -i

# Create table with enhanced features:
mist> CREATE TABLE companies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    type ENUM('startup', 'corporation') DEFAULT 'startup',
    founded DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Table 'companies' created successfully.

mist> CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    hire_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
);
Table 'employees' created successfully.

# Insert data with enhanced types:
mist> INSERT INTO companies (name, type, founded) VALUES ('Tech Corp', 'startup', '2020-01-15');
1 row inserted.

mist> INSERT INTO employees (company_id, email, hire_date) VALUES (1, '[email protected]', '2024-01-01');
1 row inserted.

# UPDATE automatically updates timestamp:
mist> UPDATE employees SET email = '[email protected]' WHERE id = 1;
1 row updated. (updated_at automatically set)

# GROUP BY with aggregates:
mist> SELECT c.type, COUNT(e.id) as employee_count FROM companies c LEFT JOIN employees e ON c.id = e.company_id GROUP BY c.type;
+-----------+----------------+
| type      | employee_count |
+-----------+----------------+
| startup   | 1              |
+-----------+----------------+

-- Enhanced data types and constraints
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    username VARCHAR(50) NOT NULL UNIQUE,
    full_name VARCHAR(100),
    birth_date DATE,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Foreign key relationships with CASCADE actions
CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    department_id INT,
    hire_date DATE NOT NULL,
    salary DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL
);

-- Complex JOINs with enhanced GROUP BY
SELECT 
    d.name AS department,
    u.status,
    COUNT(e.id) AS employee_count,
    AVG(e.salary) AS avg_salary,
    MIN(e.hire_date) AS earliest_hire,
    MAX(e.hire_date) AS latest_hire
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
LEFT JOIN users u ON e.user_id = u.id
GROUP BY d.name, u.status
HAVING COUNT(e.id) > 0
ORDER BY avg_salary DESC;

-- Date operations and ENUM validation
INSERT INTO users (email, username, full_name, birth_date, status) 
VALUES ('[email protected]', 'johnsmith', 'John Smith', '1985-06-15', 'active');

-- UPDATE automatically updates timestamp
UPDATE users SET full_name = 'John A. Smith' WHERE username = 'johnsmith';

-- Foreign key constraints ensure referential integrity
-- This would succeed: 
INSERT INTO employees (user_id, department_id, hire_date, salary) 
VALUES (1, 1, '2024-01-15', 75000.00);

-- This would fail due to foreign key constraint:
-- INSERT INTO employees (user_id, department_id, hire_date, salary) 
-- VALUES (999, 1, '2024-01-15', 75000.00);

-- CASCADE DELETE: Deleting a user cascades to employee records
-- SET NULL: Deleting a department sets employee department_id to NULL
DELETE FROM users WHERE id = 1; -- Cascades to employees table

-- Advanced Transaction Control
START TRANSACTION;
  INSERT INTO users (email, username, full_name) VALUES ('[email protected]', 'manager1', 'Jane Manager');

  SAVEPOINT before_dept_changes;
    INSERT INTO departments (name, manager_id) VALUES ('Engineering', LAST_INSERT_ID());

    BEGIN; -- Nested transaction
      INSERT INTO employees (user_id, department_id, hire_date, salary) VALUES (2, 1, '2024-01-01', 120000);
      UPDATE employees SET salary = salary * 1.1 WHERE department_id = 1; -- 10% raise
    ROLLBACK; -- Rollback nested transaction (no raises applied)

  -- Keep department but rollback to before department changes if needed
  -- ROLLBACK TO SAVEPOINT before_dept_changes;

  RELEASE SAVEPOINT before_dept_changes;
COMMIT; -- Commit all remaining changes