Spaces:

GamerC0der
/

db

Sleeping

File size: 10,213 Bytes

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/&lt;uuid&gt;/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())

@app.route("/")
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,
    )

@app.route("/health")
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,
    })

@app.route("/api/db", methods=["POST"])
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"})

@app.route("/api/<db_uuid>/query", methods=["POST"])
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)