Spaces:
Sleeping
Sleeping
| 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 --- | |
| def home(): | |
| return jsonify({"status": "ok", "message": "SQL Compiler API Ready"}) | |
| 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) |