{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "introduction",
   "metadata": {},
   "source": [
    "# Exploring cratebank with DuckDB\n",
    "\n",
    "This notebook queries cratebank's public Parquet files directly—no account or local dataset download is required. It starts with dataset health, then looks at Cargo unit wall time, samply compiler-phase samples, memory, cache behavior, build settings, emitted artifacts, and one build timeline.\n",
    "\n",
    "Cargo durations are wall-clock measurements per unit. Samply phase values are CPU-weighted sample counts. Keep those quantities separate."
   ]
  },
  {
   "cell_type": "code",
   "id": "dependencies",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import importlib.util\n",
    "import subprocess\n",
    "import sys\n",
    "\n",
    "required = [\"duckdb\", \"pandas\", \"matplotlib\"]\n",
    "missing = [name for name in required if importlib.util.find_spec(name) is None]\n",
    "if missing:\n",
    "    if subprocess.run(\n",
    "        [sys.executable, \"-m\", \"pip\", \"--version\"],\n",
    "        stdout=subprocess.DEVNULL,\n",
    "        stderr=subprocess.DEVNULL,\n",
    "    ).returncode != 0:\n",
    "        subprocess.check_call([sys.executable, \"-m\", \"ensurepip\", \"--upgrade\"])\n",
    "    subprocess.check_call([sys.executable, \"-m\", \"pip\", \"install\", \"-q\", *missing])\n",
    "\n",
    "print(\"dependencies ready\")"
   ]
  },
  {
   "cell_type": "code",
   "id": "connect",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from urllib.request import urlopen\n",
    "\n",
    "import duckdb\n",
    "import matplotlib.pyplot as plt\n",
    "from IPython.display import display\n",
    "\n",
    "INSTALL_SQL = \"https://raw.githubusercontent.com/PowderworksCode/cratebank/main/docs/install.sql\"\n",
    "con = duckdb.connect()\n",
    "con.execute(urlopen(INSTALL_SQL).read().decode(\"utf-8\"))\n",
    "print(\"cratebank views installed\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "health-intro",
   "metadata": {},
   "source": [
    "## Dataset health\n",
    "\n",
    "Start by checking how many builds and units are available, the observation window, and how many units were withheld by the privacy filter."
   ]
  },
  {
   "cell_type": "code",
   "id": "health-query",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "health = con.sql(\"\"\"\n",
    "    SELECT\n",
    "        count(*) AS builds,\n",
    "        min(try_cast(timestamp AS TIMESTAMPTZ)) AS first_build,\n",
    "        max(try_cast(timestamp AS TIMESTAMPTZ)) AS latest_build,\n",
    "        sum(units) AS public_units,\n",
    "        sum(units_withheld) AS withheld_units,\n",
    "        sum(artifacts) AS artifact_files,\n",
    "        round(sum(artifact_bytes) / 1048576.0, 2) AS artifact_mib,\n",
    "        count(DISTINCT rustc_version) AS compiler_versions,\n",
    "        count(DISTINCT org_id) AS identified_orgs,\n",
    "        count(DISTINCT machine_id) AS identified_machines\n",
    "    FROM sessions\n",
    "\"\"\").df()\n",
    "display(health)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "wall-time-intro",
   "metadata": {},
   "source": [
    "## Package wall time\n",
    "\n",
    "Summing unit durations measures accumulated unit wall-seconds, not end-to-end build time: units can overlap. The median is useful alongside the total so frequently observed packages do not dominate silently."
   ]
  },
  {
   "cell_type": "code",
   "id": "wall-time-query",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "package_wall = con.sql(\"\"\"\n",
    "    SELECT\n",
    "        package,\n",
    "        count(*) AS observations,\n",
    "        round(sum(duration), 2) AS total_unit_wall_s,\n",
    "        round(median(duration), 3) AS median_unit_wall_s,\n",
    "        round(max(duration), 3) AS max_unit_wall_s\n",
    "    FROM units\n",
    "    WHERE duration IS NOT NULL\n",
    "    GROUP BY package\n",
    "    ORDER BY total_unit_wall_s DESC\n",
    "    LIMIT 20\n",
    "\"\"\").df()\n",
    "display(package_wall)\n",
    "\n",
    "ax = package_wall.sort_values(\"total_unit_wall_s\").plot.barh(\n",
    "    x=\"package\", y=\"total_unit_wall_s\", legend=False, figsize=(9, 7)\n",
    ")\n",
    "ax.set(title=\"Accumulated Cargo unit wall time\", xlabel=\"wall-seconds\", ylabel=\"\")\n",
    "plt.tight_layout()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "phase-intro",
   "metadata": {},
   "source": [
    "## Where rustc spends CPU samples\n",
    "\n",
    "This view uses serial rustc threads only. Parallel codegen threads are intentionally separate because combining them would mix distinct thread classes."
   ]
  },
  {
   "cell_type": "code",
   "id": "phase-query",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "phase_share = con.sql(\"\"\"\n",
    "    SELECT\n",
    "        phase,\n",
    "        sum(samples) AS samples,\n",
    "        round(100.0 * sum(samples) / sum(sum(samples)) OVER (), 1) AS share_pct\n",
    "    FROM phases\n",
    "    WHERE thread = 'serial'\n",
    "    GROUP BY phase\n",
    "    ORDER BY samples DESC\n",
    "\"\"\").df()\n",
    "display(phase_share)\n",
    "\n",
    "ax = phase_share.sort_values(\"share_pct\").plot.barh(\n",
    "    x=\"phase\", y=\"share_pct\", legend=False, figsize=(8, 5)\n",
    ")\n",
    "ax.set(title=\"Serial rustc CPU sample share\", xlabel=\"percent of samples\", ylabel=\"\")\n",
    "plt.tight_layout()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "settings-intro",
   "metadata": {},
   "source": [
    "## Build settings in the wild\n",
    "\n",
    "These rows come from scrubbed rustc command lines. Path values and full command lines are never published."
   ]
  },
  {
   "cell_type": "code",
   "id": "settings-query",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "settings = con.sql(\"\"\"\n",
    "    SELECT flag, value, count(*) AS units\n",
    "    FROM unit_flags\n",
    "    GROUP BY flag, value\n",
    "    ORDER BY units DESC, flag, value\n",
    "    LIMIT 30\n",
    "\"\"\").df()\n",
    "display(settings)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "memory-cache-intro",
   "metadata": {},
   "source": [
    "## Memory and cache behavior\n",
    "\n",
    "RSS is sampled externally every 50 ms, so these are observed peaks rather than allocator measurements. Cargo freshness and optional sccache deltas make cold, warm, and compiler-cache-assisted builds distinguishable."
   ]
  },
  {
   "cell_type": "code",
   "id": "memory-cache-query",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "memory = con.sql(\"\"\"\n",
    "    SELECT\n",
    "        package, crate, crate_type,\n",
    "        round(max(peak_rss_bytes) / 1048576.0, 1) AS peak_rss_mib,\n",
    "        round(max(wall_s), 2) AS max_wall_s\n",
    "    FROM compiler_units\n",
    "    WHERE peak_rss_bytes IS NOT NULL\n",
    "    GROUP BY package, crate, crate_type\n",
    "    ORDER BY peak_rss_mib DESC\n",
    "    LIMIT 20\n",
    "\"\"\").df()\n",
    "display(memory)\n",
    "\n",
    "cache = con.sql(\"\"\"\n",
    "    SELECT cargo_cache_state, compiler_cache, count(*) AS builds,\n",
    "           sum(compiler_cache_hits) AS cache_hits,\n",
    "           sum(compiler_cache_misses) AS cache_misses\n",
    "    FROM sessions\n",
    "    GROUP BY cargo_cache_state, compiler_cache\n",
    "    ORDER BY builds DESC\n",
    "\"\"\").df()\n",
    "display(cache)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "artifacts-intro",
   "metadata": {},
   "source": [
    "## What each unit produced\n",
    "\n",
    "Cargo reports compiler outputs, and cratebank measures their sizes locally. Only path-free output kinds and byte counts are published; filenames and target-directory paths are discarded."
   ]
  },
  {
   "cell_type": "code",
   "id": "artifacts-query",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "artifact_sizes = con.sql(\"\"\"\n",
    "    SELECT\n",
    "        u.package,\n",
    "        a.output,\n",
    "        round(sum(a.bytes) / 1048576.0, 2) AS total_mib,\n",
    "        count(*) AS files\n",
    "    FROM artifacts AS a\n",
    "    JOIN units AS u\n",
    "      ON u.run_id = a.run_id\n",
    "     AND u.index = a.unit_index\n",
    "    GROUP BY u.package, a.output\n",
    "    ORDER BY total_mib DESC\n",
    "    LIMIT 25\n",
    "\"\"\").df()\n",
    "display(artifact_sizes)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "timeline-intro",
   "metadata": {},
   "source": [
    "## Inspect one build timeline\n",
    "\n",
    "Cargo records concurrency and whole-machine CPU on different clocks. They are plotted on separate axes without pretending that samples at the same row index happened together."
   ]
  },
  {
   "cell_type": "code",
   "id": "timeline-query",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "candidate = con.sql(\"\"\"\n",
    "    SELECT run_id\n",
    "    FROM timeline\n",
    "    GROUP BY run_id\n",
    "    ORDER BY count(*) DESC\n",
    "    LIMIT 1\n",
    "\"\"\").fetchone()\n",
    "\n",
    "if candidate is None:\n",
    "    print(\"No timeline rows are available yet.\")\n",
    "else:\n",
    "    run_id = candidate[0]\n",
    "    concurrency = con.execute(\"\"\"\n",
    "        SELECT t, active, waiting, inactive\n",
    "        FROM timeline\n",
    "        WHERE run_id = ? AND active IS NOT NULL\n",
    "        ORDER BY t\n",
    "    \"\"\", [run_id]).df()\n",
    "    cpu = con.execute(\"\"\"\n",
    "        SELECT t, cpu_pct\n",
    "        FROM timeline\n",
    "        WHERE run_id = ? AND cpu_pct IS NOT NULL\n",
    "        ORDER BY t\n",
    "    \"\"\", [run_id]).df()\n",
    "\n",
    "    fig, axes = plt.subplots(2, 1, figsize=(10, 7), sharex=True)\n",
    "    if not concurrency.empty:\n",
    "        concurrency.plot(x=\"t\", y=[\"active\", \"waiting\", \"inactive\"], ax=axes[0])\n",
    "    axes[0].set(title=f\"Cargo concurrency — {run_id}\", ylabel=\"units\")\n",
    "    if not cpu.empty:\n",
    "        cpu.plot(x=\"t\", y=\"cpu_pct\", ax=axes[1], legend=False)\n",
    "    axes[1].set(title=\"Whole-machine CPU\", xlabel=\"seconds since build start\", ylabel=\"percent\")\n",
    "    plt.tight_layout()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "next-steps",
   "metadata": {},
   "source": [
    "## Keep exploring\n",
    "\n",
    "All views join through `run_id`; artifacts and dependency edges additionally join to Cargo units through their unit indices, while phases and unit flags join to compiler units through `unit_key`. Useful next cuts include critical paths, compiler version, profile, target, feature set, machine class, and CI versus local builds. Treat the anonymous contributions as observational data rather than a representative sample of all Rust builds."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}