import subprocess import sys import uuid import os import random from flask import Flask, request, jsonify from flask_cors import CORS app = Flask(__name__) CORS(app) # --- DATASET GENERATORS --- def get_small_dataset(): return """ -- SMALL DATASET: School Management CREATE TABLE Teachers ( id INT PRIMARY KEY, name VARCHAR(50), subject VARCHAR(30) ); INSERT INTO Teachers VALUES (1, 'Mr. Smith', 'Math'), (2, 'Ms. Johnson', 'Science'), (3, 'Mrs. Davis', 'History'); CREATE TABLE Students ( id INT PRIMARY KEY, name VARCHAR(50), grade INT, teacher_id INT ); INSERT INTO Students VALUES (101, 'Alice', 5, 1), (102, 'Bob', 5, 1), (103, 'Charlie', 6, 2), (104, 'Diana', 6, 2), (105, 'Evan', 5, 3); """ def get_medium_dataset(): return """ -- MEDIUM DATASET: E-Commerce Store CREATE TABLE Products ( id INT PRIMARY KEY, name VARCHAR(50), price DECIMAL(10,2), stock INT ); INSERT INTO Products VALUES (1, 'Laptop', 999.99, 10), (2, 'Mouse', 25.50, 50), (3, 'Keyboard', 45.00, 30), (4, 'Monitor', 150.00, 15), (5, 'Headphones', 80.00, 20); CREATE TABLE Customers ( id INT PRIMARY KEY, name VARCHAR(50), city VARCHAR(50) ); INSERT INTO Customers VALUES (1, 'John Doe', 'New York'), (2, 'Jane Smith', 'London'), (3, 'Mike Ross', 'New York'), (4, 'Rachel Zane', 'Toronto'); CREATE TABLE Orders ( id INT PRIMARY KEY, customer_id INT, product_id INT, quantity INT, date VARCHAR(20) ); INSERT INTO Orders VALUES (1001, 1, 1, 1, '2023-01-01'), (1002, 2, 3, 2, '2023-01-02'), (1003, 1, 2, 1, '2023-01-03'), (1004, 3, 1, 1, '2023-01-04'), (1005, 4, 5, 1, '2023-01-05'), (1006, 2, 2, 5, '2023-01-05'); """ def get_large_dataset(): # Generates a synthetic log dataset sql = """ -- LARGE DATASET: Web Server Traffic CREATE TABLE Users ( user_id INT PRIMARY KEY, username VARCHAR(50), country VARCHAR(50) ); INSERT INTO Users VALUES (1, 'admin', 'USA'), (2, 'guest', 'UK'), (3, 'power_user', 'Canada'), (4, 'bot_01', 'Russia'), (5, 'test_acc', 'India'); CREATE TABLE ServerLogs ( log_id INT PRIMARY KEY, user_id INT, endpoint VARCHAR(50), status_code INT, response_time_ms INT ); """ # Generate 100 rows of log data endpoints = ['/home', '/login', '/api/data', '/dashboard', '/logout', '/404'] statuses = [200, 200, 200, 404, 500, 301] values = [] for i in range(1, 101): uid = random.randint(1, 5) ep = random.choice(endpoints) sc = random.choice(statuses) rt = random.randint(20, 500) values.append(f"({i}, {uid}, '{ep}', {sc}, {rt})") sql += f"INSERT INTO ServerLogs VALUES {', '.join(values)};" return sql # --- HELPERS --- def detect_db_type(sql_code): """ Simple heuristic to auto-detect dialect. Defaults to SQLite (most permissive for playgrounds). """ code_upper = sql_code.upper() # MySQL Specifics if "AUTO_INCREMENT" in code_upper or "SHOW TABLES" in code_upper or "UNSIGNED" in code_upper: return "mysql" # PostgreSQL Specifics if "RETURNING" in code_upper or "SERIAL" in code_upper or "::" in code_upper or "ILIKE" in code_upper: return "postgres" # Default return "sqlite" def run_command(command, input_text=None, timeout=15): try: run_process = subprocess.run( command, input=input_text, capture_output=True, text=True, timeout=timeout ) output = run_process.stdout if run_process.stderr: # Filter out common CLI noise errs = [line for line in run_process.stderr.split('\n') if not line.startswith('Note:') and line.strip()] if errs: output += "\n--- System Messages ---\n" + "\n".join(errs) return output except subprocess.TimeoutExpired: return "Error: Execution timed out (15s limit)." except Exception as e: return f"System Error: {str(e)}" # --- ROUTES --- @app.route('/') def home(): return jsonify({"status": "ok", "message": "SQL Compiler API Ready"}) @app.route('/api/run-sql', methods=['POST']) def run_sql(): sql_code = request.form.get('code', '') dataset_size = request.form.get('dataset', 'small') # small, medium, large if not sql_code: return jsonify({'output': 'No SQL code provided'}), 400 # 1. Select Dataset init_sql = "" if dataset_size == 'small': init_sql = get_small_dataset() elif dataset_size == 'medium': init_sql = get_medium_dataset() elif dataset_size == 'large': init_sql = get_large_dataset() # 2. Auto-detect DB Type db_type = detect_db_type(sql_code) session_id = uuid.uuid4().hex[:8] output = "" try: # --- SQLITE --- if db_type == 'sqlite': cmd = ["sqlite3", ":memory:"] header = ".mode column\n.headers on\n" full_script = header + init_sql + sql_code output = run_command(cmd, input_text=full_script) # --- POSTGRESQL --- elif db_type == 'postgres': schema_name = f"sess_{session_id}" run_command(["psql", "-h", "localhost", "-U", "playground", "-d", "playground", "-c", f"CREATE SCHEMA {schema_name};"]) try: full_script = f"SET search_path TO {schema_name};\n{init_sql}\n{sql_code}" output = run_command(["psql", "-h", "localhost", "-U", "playground", "-d", "playground"], input_text=full_script) finally: run_command(["psql", "-h", "localhost", "-U", "playground", "-d", "playground", "-c", f"DROP SCHEMA {schema_name} CASCADE;"]) # --- MYSQL --- elif db_type == 'mysql': db_name = f"sess_{session_id}" run_command(["mysql", "-h", "localhost", "-u", "playground", "-e", f"CREATE DATABASE {db_name};"]) try: # MySQL doesn't support "INSERT INTO ... VALUES (...), (...)" syntax exactly same way if mixed with other logic sometimes, # but standard SQL is fine. # Note: We pipe specific settings to make output pretty full_script = f"USE {db_name};\n{init_sql}\n{sql_code}" output = run_command(["mysql", "-h", "localhost", "-u", "playground", "-t"], input_text=full_script) finally: run_command(["mysql", "-h", "localhost", "-u", "playground", "-e", f"DROP DATABASE {db_name};"]) except Exception as e: output = f"Server Error: {str(e)}" return jsonify({'output': output, 'detected_engine': db_type}) if __name__ == '__main__': app.run(host="0.0.0.0", port=7860)