{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Virtual file system support\n",
    "\n",
    "The Pyodide kernel mounts a virtual `/drive` directory which allows access to files that\n",
    "are provided by JupyterLite."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```{warning}\n",
    "**NOTE:** The virtual file system **does not work when in private mode under Firefox** and you'll get permission errors. It will also fail to work if you **hard refresh** the page.\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pathlib import Path"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The kernel will be start in a location in the virtual drive."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Path.cwd()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Directories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "a_dir = Path(\"dir\")\n",
    "a_dir.exists()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "New paths should appear almost immediately in the file tree."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "a_dir.mkdir(exist_ok=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### List directory content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sorted(Path.cwd().glob(\"*\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Remove"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Contents should disappear immediately from the _file browser_ when removed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "a_dir.exists() and a_dir.rmdir()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sorted(Path.cwd().glob(\"*\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Reading/writing file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "txt = Path(\"test.txt\")\n",
    "txt.write_text(\"Hello! I write this from the kernel worker\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "txt.read_text()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from PIL import Image\n",
    "\n",
    "img = Path(\"pil_color.png\")\n",
    "Image.new(\"RGB\", (60, 30), color=(73, 109, 137)).save(img)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.display import Image, Markdown, display\n",
    "\n",
    "Image(data=img, format=\"png\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Rename\n",
    "\n",
    "Files can be renamed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "txt.exists()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "txt.rename(\"test_rename.txt\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "txt.exists()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sorted(Path.cwd().glob(\"*\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Stat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for path in sorted(Path.cwd().glob(\"*\")):\n",
    "    display(Markdown(f\"- [{path}]({path.name})\" \"\\n\" f\"  - `{path.stat()}`\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Complex IO\n",
    "\n",
    "Some libraries don't _fully_ work as expected yet, such as\n",
    "[sqlite3](https://docs.python.org/3/library/sqlite3.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_movies(path):\n",
    "    path.unlink(missing_ok=True)\n",
    "    con = sqlite3.connect(path)\n",
    "    cur = con.cursor()\n",
    "    with con:\n",
    "        cur.execute(\"CREATE TABLE movie(title, year, score)\")\n",
    "        cur.execute(\n",
    "            \"\"\"\n",
    "            INSERT INTO movie VALUES\n",
    "                ('Monty Python and the Holy Grail', 1975, 8.2),\n",
    "                ('And Now for Something Completely Different', 1971, 7.5)\n",
    "            \"\"\"\n",
    "        )\n",
    "    con.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def query_movies(path):\n",
    "    con = sqlite3.connect(path)\n",
    "    cur = con.cursor()\n",
    "    res = cur.execute(\"SELECT * FROM movie\").fetchall()\n",
    "    con.close()\n",
    "    return res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "db = Path(\"tutorial.db\")\n",
    "try:\n",
    "    create_movies(db)\n",
    "    assert False, \"if we got here, something awesome happened!\"\n",
    "except sqlite3.DatabaseError as err:\n",
    "    print(err)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For now, one can work in a separate folder:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tmp_db = Path(\"/tmp/tutorial.db\")\n",
    "create_movies(tmp_db)\n",
    "display(*query_movies(tmp_db))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And _copy_ (_not_ rename) the file back out, and then _reading_ works fine."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import shutil\n",
    "\n",
    "db.unlink(missing_ok=True)\n",
    "shutil.copy2(tmp_db, db)\n",
    "display(*query_movies(db))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By extension, SQLite databases hosted from the server will also be _readable_ with\n",
    "`sqlite3`, such as the classic\n",
    "[northwind example](https://github.com/jpwhite3/northwind-SQLite3)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "con = sqlite3.connect(\"data/northwind.sqlite3\")\n",
    "cur = con.cursor()\n",
    "display(*cur.execute(\"SELECT LastName, FirstName FROM Employees\").fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or even with [`pandas`](https://pandas.pydata.org/docs/user_guide/io.html#sql-queries):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import base64\n",
    "\n",
    "import IPython\n",
    "import pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pandas.read_sql_query(\"SELECT * from Employees\", con, \"EmployeeID\")\n",
    "df[\"Photo\"] = df[\"Photo\"].apply(\n",
    "    lambda raw: f\"\"\"<img src=\"data:image/png;base64,{base64.b64encode(raw).decode(\"utf-8\")}\"/>\"\"\"\n",
    ")\n",
    "IPython.display.HTML(df.T.to_html(escape=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```{note}\n",
    "Future work could potentially expose reasonably-sized queries against _huge_ databases\n",
    "with techniques like [`sql.js-httpvfs`](https://github.com/phiresky/sql.js-httpvfs): see [the original post](https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-github-pages).\n",
    "```"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python (Pyodide)",
   "language": "python",
   "name": "python"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "python",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}