Spaces:
Sleeping
Sleeping
| import os | |
| import time | |
| import uuid | |
| import psutil | |
| import subprocess | |
| import pymysql | |
| from flask import Flask, jsonify, request, abort, render_template_string | |
| app = Flask(__name__) | |
| MYSQL_CONFIG = { | |
| "host": os.getenv("MYSQL_HOST", "localhost"), | |
| "port": int(os.getenv("MYSQL_PORT", 3306)), | |
| "user": os.getenv("MYSQL_USER", "root"), | |
| "password": os.getenv("MYSQL_PASSWORD", "password"), | |
| } | |
| MIN_DB_MB = 500 | |
| MAX_DB_MB = 5000 | |
| TOTAL_MAX_MB = 25000 | |
| start_time = time.time() | |
| db_registry = {} | |
| def ensure_mysql_running(): | |
| try: | |
| conn = pymysql.connect( | |
| host=MYSQL_CONFIG["host"], | |
| port=MYSQL_CONFIG["port"], | |
| user=MYSQL_CONFIG["user"], | |
| password=MYSQL_CONFIG["password"] | |
| ) | |
| conn.close() | |
| except Exception: | |
| try: | |
| subprocess.Popen(["mysqld_safe"], stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL) | |
| time.sleep(5) | |
| except Exception: | |
| try: | |
| subprocess.Popen(["service", "mysql", "start"], stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL) | |
| time.sleep(5) | |
| except Exception: | |
| pass | |
| ensure_mysql_running() | |
| DASHBOARD_TEMPLATE = """ | |
| <!doctype html> | |
| <html> | |
| <head> | |
| <title>MySQL DB Manager Dashboard</title> | |
| <style> | |
| body { font-family: Arial, sans-serif; margin: 20px; background-color: #121212; color: #e0e0e0; } | |
| h1, h2 { margin-bottom: 0.3em; color: #ffffff; } | |
| .section { margin-bottom: 2em; } | |
| table { border-collapse: collapse; width: 100%; margin-top: 0.5em; background-color: #1e1e1e; color: #e0e0e0; } | |
| th, td { border: 1px solid #333; padding: 6px 8px; font-size: 14px; } | |
| th { background: #2a2a2a; text-align: left; color: #fff; } | |
| code { background: #2a2a2a; padding: 2px 4px; color: #f5c518; } | |
| .small { font-size: 12px; color: #aaa; } | |
| input[type="text"], input[type="number"] { padding: 4px; background: #1e1e1e; color: #e0e0e0; border: 1px solid #333; } | |
| button { padding: 6px 10px; cursor: pointer; background: #1565c0; color: #fff; border: none; border-radius: 3px; } | |
| button:hover { background: #1e88e5; } | |
| .error { color: #ff5252; } | |
| a, a:visited { color: #90caf9; } | |
| </style> | |
| </head> | |
| <body> | |
| <h1>MySQL DB Manager Dashboard</h1> | |
| <div class="section"> | |
| <h2>System & Storage</h2> | |
| <p><b>Uptime:</b> {{ uptime_seconds }} seconds</p> | |
| <p><b>CPU usage:</b> {{ system_stats.cpu.usage_percent }}% (cores: {{ system_stats.cpu.cores_detected }})</p> | |
| <p><b>RAM usage:</b> {{ '%.2f' % system_stats.ram.used_gb }} GB / {{ '%.2f' % system_stats.ram.total_gb }} GB ({{ system_stats.ram.usage_percent }}%)</p> | |
| <p><b>Disk usage:</b> {{ '%.2f' % system_stats.disk.used_gb }} GB / {{ '%.2f' % system_stats.disk.total_gb }} GB ({{ system_stats.disk.usage_percent }}%)</p> | |
| <p><b>DB Allocated Size:</b> {{ total_allocated_mb }} MB / {{ total_max_mb }} MB</p> | |
| </div> | |
| <div class="section"> | |
| <h2>Create New Database</h2> | |
| <form id="create-form"> | |
| <label>Database size (MB, {{ min_db_mb }}-{{ max_db_mb }}):</label> | |
| <input type="number" id="size_mb" value="{{ min_db_mb }}" min="{{ min_db_mb }}" max="{{ max_db_mb }}"> | |
| <button type="submit">Create</button> | |
| <span class="small">This reserves capacity; size is not strictly enforced by MySQL itself.</span> | |
| <div class="error" id="create-error"></div> | |
| <div id="create-success" class="small"></div> | |
| </form> | |
| </div> | |
| <div class="section"> | |
| <h2>Existing Databases</h2> | |
| {% if dbs %} | |
| <table> | |
| <tr> | |
| <th>UUID</th> | |
| <th>DB Name</th> | |
| <th>Allocated Size (MB)</th> | |
| <th>Created At (unix)</th> | |
| <th>Example Query Endpoint</th> | |
| </tr> | |
| {% for db in dbs %} | |
| <tr> | |
| <td><code>{{ db.uuid }}</code></td> | |
| <td><code>{{ db.name }}</code></td> | |
| <td>{{ db.size_mb }}</td> | |
| <td>{{ db.created_at }}</td> | |
| <td><code>/api/{{ db.uuid }}/query</code></td> | |
| </tr> | |
| {% endfor %} | |
| </table> | |
| {% else %} | |
| <p>No databases created yet.</p> | |
| {% endif %} | |
| </div> | |
| <div class="section"> | |
| <h2>API Reference</h2> | |
| <ul> | |
| <li><code>GET /health</code> – returns uptime and basic health info</li> | |
| <li><code>POST /api/db</code> – create a new DB. Body JSON: <code>{"size_mb": 500}</code></li> | |
| <li><code>POST /api/<uuid>/query</code> – run a SQL query on that DB. Body JSON: <code>{"query": "SELECT 1"}</code></li> | |
| </ul> | |
| </div> | |
| <script> | |
| document.getElementById('create-form').addEventListener('submit', async function(e) { | |
| e.preventDefault(); | |
| const sizeMb = parseInt(document.getElementById('size_mb').value, 10); | |
| const errEl = document.getElementById('create-error'); | |
| const okEl = document.getElementById('create-success'); | |
| errEl.textContent = ''; | |
| okEl.textContent = ''; | |
| try { | |
| const res = await fetch('/api/db', { | |
| method: 'POST', | |
| headers: {'Content-Type': 'application/json'}, | |
| body: JSON.stringify({ size_mb: sizeMb }) | |
| }); | |
| const data = await res.json(); | |
| if (!res.ok) { | |
| errEl.textContent = data.error || 'Error'; | |
| } else { | |
| okEl.textContent = 'Created DB ' + data.db_name + ' with UUID ' + data.uuid + | |
| '. Endpoint: /api/' + data.uuid + '/query'; | |
| setTimeout(() => location.reload(), 1000); | |
| } | |
| } catch (e) { | |
| errEl.textContent = 'Request failed'; | |
| } | |
| }); | |
| </script> | |
| </body> | |
| </html> | |
| """ | |
| def get_mysql_connection(database=None): | |
| cfg = MYSQL_CONFIG.copy() | |
| if database: | |
| cfg["database"] = database | |
| return pymysql.connect( | |
| host=cfg["host"], | |
| port=cfg["port"], | |
| user=cfg["user"], | |
| password=cfg["password"], | |
| database=cfg.get("database"), | |
| autocommit=False, | |
| cursorclass=pymysql.cursors.DictCursor, | |
| ) | |
| def get_system_stats(): | |
| cpu_percent = psutil.cpu_percent(interval=0.1) | |
| cpu_count = psutil.cpu_count(logical=False) or psutil.cpu_count() | |
| vm = psutil.virtual_memory() | |
| ram_total_gb = vm.total / (1024**3) | |
| ram_used_gb = (vm.total - vm.available) / (1024**3) | |
| ram_percent = vm.percent | |
| disk = psutil.disk_usage("/") | |
| disk_total_gb = disk.total / (1024**3) | |
| disk_used_gb = disk.used / (1024**3) | |
| disk_percent = disk.percent | |
| return { | |
| "cpu": {"usage_percent": cpu_percent, "cores_detected": cpu_count}, | |
| "ram": {"total_gb": ram_total_gb, "used_gb": ram_used_gb, "usage_percent": ram_percent}, | |
| "disk": {"total_gb": disk_total_gb, "used_gb": disk_used_gb, "usage_percent": disk_percent}, | |
| } | |
| def total_allocated_mb(): | |
| return sum(db["size_mb"] for db in db_registry.values()) | |
| def dashboard(): | |
| system_stats = get_system_stats() | |
| uptime_seconds = int(time.time() - start_time) | |
| dbs = [{"uuid": k, "name": v["db_name"], "size_mb": v["size_mb"], "created_at": int(v["created_at"])} for k, v in db_registry.items()] | |
| return render_template_string( | |
| DASHBOARD_TEMPLATE, | |
| system_stats=system_stats, | |
| uptime_seconds=uptime_seconds, | |
| total_allocated_mb=total_allocated_mb(), | |
| total_max_mb=TOTAL_MAX_MB, | |
| min_db_mb=MIN_DB_MB, | |
| max_db_mb=MAX_DB_MB, | |
| dbs=dbs, | |
| ) | |
| def health(): | |
| uptime_seconds = int(time.time() - start_time) | |
| try: | |
| conn = get_mysql_connection() | |
| cur = conn.cursor() | |
| cur.execute("SELECT 1") | |
| cur.fetchone() | |
| cur.close() | |
| conn.close() | |
| db_status = "ok" | |
| except Exception as e: | |
| db_status = f"error: {e}" | |
| return jsonify({ | |
| "status": "ok", | |
| "uptime_seconds": uptime_seconds, | |
| "db_status": db_status, | |
| "total_allocated_mb": total_allocated_mb(), | |
| "total_max_mb": TOTAL_MAX_MB, | |
| }) | |
| def create_db(): | |
| data = request.get_json(silent=True) or {} | |
| size_mb = data.get("size_mb") | |
| if not isinstance(size_mb, (int, float)): | |
| return jsonify({"error": "size_mb must be a number"}), 400 | |
| size_mb = int(size_mb) | |
| if size_mb < MIN_DB_MB or size_mb > MAX_DB_MB: | |
| return jsonify({"error": f"size_mb must be between {MIN_DB_MB} and {MAX_DB_MB} MB"}), 400 | |
| if total_allocated_mb() + size_mb > TOTAL_MAX_MB: | |
| return jsonify({"error": "Total allocated size would exceed 25GB limit"}), 400 | |
| db_uuid = str(uuid.uuid4()) | |
| db_name = f"db_{db_uuid.replace('-', '')[:16]}" | |
| try: | |
| conn = get_mysql_connection() | |
| conn.autocommit(True) | |
| cur = conn.cursor() | |
| cur.execute(f"CREATE DATABASE `{db_name}`") | |
| cur.close() | |
| conn.close() | |
| except Exception as e: | |
| ensure_mysql_running() | |
| try: | |
| conn = get_mysql_connection() | |
| conn.autocommit(True) | |
| cur = conn.cursor() | |
| cur.execute(f"CREATE DATABASE `{db_name}`") | |
| cur.close() | |
| conn.close() | |
| except Exception as e2: | |
| return jsonify({"error": f"Failed to create database: {e2}"}), 500 | |
| db_registry[db_uuid] = {"db_name": db_name, "size_mb": size_mb, "created_at": time.time()} | |
| return jsonify({"uuid": db_uuid, "db_name": db_name, "size_mb": size_mb, "query_endpoint": f"/api/{db_uuid}/query"}) | |
| def db_query(db_uuid): | |
| if db_uuid not in db_registry: | |
| abort(404, description="Unknown database UUID") | |
| data = request.get_json(silent=True) or {} | |
| query = data.get("query") | |
| if not query: | |
| return jsonify({"status": "error", "error": "Missing 'query' in JSON body"}), 400 | |
| db_name = db_registry[db_uuid]["db_name"] | |
| try: | |
| conn = get_mysql_connection(database=db_name) | |
| cur = conn.cursor() | |
| cur.execute(query) | |
| rows = cur.fetchall() if cur.description else None | |
| try: | |
| conn.commit() | |
| except Exception: | |
| conn.rollback() | |
| cur.close() | |
| conn.close() | |
| return jsonify({"status": "ok", "rows": rows}) | |
| except Exception as e: | |
| return jsonify({"status": "error", "error": str(e)}), 500 | |
| if __name__ == "__main__": | |
| app.run(host="0.0.0.0", port=7860) |