File size: 7,063 Bytes
b555842 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 | """
gui/pages/sql_query.py — 💬 SQL Query (legacy)
Render function: render(ctx)
ctx keys: ov, runs, tax, lin, age, avg_lin_j, avg_age_j, tax_mult,
plan_ms, exec_ms, synth_ms, plan_pct, exec_pct, synth_pct
"""
import subprocess
import streamlit as st
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots
from gui.config import PROJECT_ROOT, DB_PATH, LIVE_API, WF_COLORS, PL
from gui.db import q, q_safe, q1
from gui.helpers import fl, _human_energy, _human_water, _human_carbon, _gauge_html, _bar_gauge_html
try:
import requests as _req
_REQUESTS_OK = True
except ImportError:
_REQUESTS_OK = False
class _req:
@staticmethod
def get(*a, **kw): raise RuntimeError("requests not installed")
try:
import yaml as _yaml
_YAML_OK = True
except ImportError:
_YAML_OK = False
def render(ctx: dict):
ov = ctx["ov"]
runs = ctx["runs"]
tax = ctx["tax"]
avg_lin_j = ctx["avg_lin_j"]
avg_age_j = ctx["avg_age_j"]
tax_mult = ctx["tax_mult"]
plan_ms = ctx["plan_ms"]
exec_ms = ctx["exec_ms"]
synth_ms = ctx["synth_ms"]
plan_pct = ctx["plan_pct"]
exec_pct = ctx["exec_pct"]
synth_pct = ctx["synth_pct"]
lin = ctx["lin"]
age = ctx["age"]
st.title("💬 SQL Query")
st.caption(f"Ad-hoc SELECT queries against `{DB_PATH.name}` · results exportable as CSV")
QUERY_LIBRARY = {
"— pick a preset —": "",
"Energy by category": (
"SELECT tc.category, r.workflow_type, COUNT(*) AS runs,\n"
" ROUND(AVG(r.total_energy_uj)/1e6,4) AS avg_energy_j,\n"
" ROUND(AVG(r.dynamic_energy_uj)/1e6,4) AS avg_dynamic_j\n"
"FROM runs r\n"
"JOIN experiments e ON r.exp_id=e.exp_id\n"
"LEFT JOIN task_categories tc ON e.task_name=tc.task_id\n"
"GROUP BY tc.category, r.workflow_type ORDER BY tc.category"
),
"Tax breakdown by task": (
"SELECT tc.category, e.task_name,\n"
" ROUND(AVG(ots.linear_dynamic_uj/1e6),4) AS linear_j,\n"
" ROUND(AVG(ots.agentic_dynamic_uj/1e6),4) AS agentic_j,\n"
" ROUND(AVG(ots.orchestration_tax_uj/1e6),4) AS tax_j,\n"
" ROUND(AVG(ots.tax_percent),2) AS tax_pct\n"
"FROM orchestration_tax_summary ots\n"
"JOIN runs rl ON ots.linear_run_id=rl.run_id\n"
"JOIN experiments e ON rl.exp_id=e.exp_id\n"
"LEFT JOIN task_categories tc ON e.task_name=tc.task_id\n"
"GROUP BY tc.category, e.task_name"
),
"Energy per token by model": (
"SELECT e.model_name, e.provider,\n"
" ROUND(AVG(r.energy_per_token*1000),4) AS avg_mj_per_token,\n"
" COUNT(*) AS runs\n"
"FROM runs r JOIN experiments e ON r.exp_id=e.exp_id\n"
"WHERE r.total_tokens>0\n"
"GROUP BY e.model_name, e.provider ORDER BY avg_mj_per_token"
),
"Carbon by provider · region": (
"SELECT e.provider, e.country_code,\n"
" ROUND(SUM(r.carbon_g)*1000,3) AS total_carbon_mg,\n"
" ROUND(SUM(r.water_ml),2) AS total_water_ml,\n"
" COUNT(*) AS runs\n"
"FROM runs r JOIN experiments e ON r.exp_id=e.exp_id\n"
"GROUP BY e.provider, e.country_code ORDER BY total_carbon_mg DESC"
),
"Sample counts per run": (
"SELECT r.run_id, r.workflow_type, e.task_name,\n"
" COUNT(DISTINCT es.sample_id) AS energy_samples,\n"
" COUNT(DISTINCT cs.sample_id) AS cpu_samples\n"
"FROM runs r\n"
"JOIN experiments e ON r.exp_id=e.exp_id\n"
"LEFT JOIN energy_samples es ON r.run_id=es.run_id\n"
"LEFT JOIN cpu_samples cs ON r.run_id=cs.run_id\n"
"GROUP BY r.run_id ORDER BY r.run_id DESC LIMIT 20"
),
"Recent runs": (
"SELECT r.run_id, r.workflow_type, e.task_name, e.provider,\n"
" ROUND(r.total_energy_uj/1e6,4) AS energy_j,\n"
" ROUND(r.duration_ns/1e9,2) AS duration_s,\n"
" r.total_tokens, r.ipc\n"
"FROM runs r JOIN experiments e ON r.exp_id=e.exp_id\n"
"ORDER BY r.run_id DESC LIMIT 30"
),
"Sustainability report": (
"SELECT e.provider, tc.category,\n"
" ROUND(SUM(r.carbon_g),4) AS total_carbon_g,\n"
" ROUND(SUM(r.water_ml),2) AS total_water_ml,\n"
" COUNT(*) AS runs\n"
"FROM runs r JOIN experiments e ON r.exp_id=e.exp_id\n"
"LEFT JOIN task_categories tc ON e.task_name=tc.task_id\n"
"GROUP BY e.provider, tc.category"
),
}
_preset = st.selectbox("Preset queries", list(QUERY_LIBRARY.keys()), key="sql_preset")
_default_sql = QUERY_LIBRARY.get(_preset, "")
_sql_input = st.text_area("SQL (SELECT only)", value=_default_sql, height=150,
key="sql_input",
placeholder="SELECT * FROM runs LIMIT 10")
_col_r, _col_l = st.columns([2, 1])
with _col_r:
_sql_run = st.button("▶ Run query", type="primary", key="sql_run")
with _col_l:
_row_limit = st.number_input("Row limit", 10, 10000, 500, step=100, key="sql_limit")
if _sql_run:
_cleaned = _sql_input.strip()
_upper = _cleaned.upper()
_bad = [kw for kw in ["DROP","DELETE","UPDATE","INSERT","ALTER","CREATE",
"REPLACE","ATTACH"] if kw in _upper]
if _bad:
st.error(f"Blocked keywords: {', '.join(_bad)}. SELECT only.")
elif not _cleaned:
st.warning("Enter a SQL query first.")
else:
if "LIMIT" not in _upper:
_cleaned = f"SELECT * FROM ({_cleaned}) _q LIMIT {int(_row_limit)}"
_result, _sql_err = q_safe(_cleaned)
if _sql_err:
st.error(f"SQL Error: {_sql_err}")
elif _result.empty:
st.info("Query returned 0 rows.")
else:
st.success(f"✓ {len(_result):,} rows")
st.dataframe(_result, use_container_width=True, hide_index=True)
st.download_button("⬇ Download CSV", data=_result.to_csv(index=False),
file_name="alems_query.csv", mime="text/csv",
key="sql_dl")
# ══════════════════════════════════════════════════════════════════════════════
# PAGE: SETTINGS
# ══════════════════════════════════════════════════════════════════════════════
|