{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# JupyterLite `xeus-sqlite` Kernel Demo\n",
        "\n",
        "The [`jupyterlite/xeus-sqlite-kernel`](https://github.com/jupyterlite/xeus-sqlite-kernel) wraps the original [`jupyter-xeus/xeus-sqlite`](https://github.com/jupyter-xeus/xeus-sqlite/) kernel for use in JupyterLite.\n",
        "\n",
        "Original kernel docs can be found [here](https://xeus-sqlite.readthedocs.io/en/latest/).\n",
        "\n",
        "The kernel provides cell magic for command line database operations, and native execution of SQL code against a connected database."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Creating a Database\n",
        "\n",
        "Line magic is used to create an in-memory database:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "trusted": true
      },
      "outputs": [],
      "source": [
        "%CREATE example_db.db"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Currently, there is no ability to:\n",
        "\n",
        "- save the database to browser storage;\n",
        "- export the database;\n",
        "- load a database from browser storage;\n",
        "- load a database from a URL;\n",
        "- load a database from the desktop;\n",
        "- connect to a remote sqlite database file."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Create and Populate Tables\n",
        "\n",
        "Tables are created and populated using SQL:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {
        "trusted": true
      },
      "outputs": [],
      "source": [
        "CREATE TABLE players (Name STRING, Class STRING, Level INTEGER, Hitpoints INTEGER)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 3,
      "metadata": {
        "trusted": true
      },
      "outputs": [],
      "source": [
        "INSERT INTO players (Name, Class, Level, Hitpoints) VALUES (\"Martin Splitskull\", \"Warrior\", 3, 40)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "metadata": {
        "trusted": true
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "<table>\n",
              "<tr>\n",
              "<th>rowcount</th>\n",
              "</tr>\n",
              "<tr>\n",
              "<td>1</td>\n",
              "</tr>\n",
              "</table>"
            ],
            "text/plain": [
              "+----------+\n",
              "| rowcount |\n",
              "+----------+\n",
              "| 1        |\n",
              "+----------+"
            ]
          },
          "execution_count": 4,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "SELECT COUNT(*) as rowcount FROM players"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Only one command can be executed from within a single code cell:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 5,
      "metadata": {
        "trusted": true
      },
      "outputs": [],
      "source": [
        "INSERT INTO players (Name, Class, Level, Hitpoints) VALUES (\"Sir Wolf\", \"Cleric\", 2, 20);\n",
        "\n",
        "-- The following will not be inserted\n",
        "INSERT INTO players (Name, Class, Level, Hitpoints) VALUES (\"Sylvain, The Grey\", \"Wizard\", 1, 10);"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 6,
      "metadata": {
        "trusted": true
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "<table>\n",
              "<tr>\n",
              "<th>Name</th>\n",
              "<th>Level</th>\n",
              "<th>Hitpoints</th>\n",
              "</tr>\n",
              "<tr>\n",
              "<td>Martin Splitskull</td>\n",
              "<td>3</td>\n",
              "<td>40</td>\n",
              "</tr>\n",
              "<tr>\n",
              "<td>Sir Wolf</td>\n",
              "<td>2</td>\n",
              "<td>20</td>\n",
              "</tr>\n",
              "</table>"
            ],
            "text/plain": [
              "+-------------------+-------+-----------+\n",
              "| Name              | Level | Hitpoints |\n",
              "+-------------------+-------+-----------+\n",
              "| Martin Splitskull | 3     | 40        |\n",
              "+-------------------+-------+-----------+\n",
              "| Sir Wolf          | 2     | 20        |\n",
              "+-------------------+-------+-----------+"
            ]
          },
          "execution_count": 6,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "SELECT Name, Level, Hitpoints FROM players;"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 7,
      "metadata": {
        "trusted": true
      },
      "outputs": [],
      "source": [
        "INSERT INTO players (Name, Class, Level, Hitpoints) VALUES (\"Sylvain, The Grey\", \"Wizard\", 1, 10);"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 8,
      "metadata": {
        "trusted": true
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "<table>\n",
              "<tr>\n",
              "<th>Name</th>\n",
              "<th>Level</th>\n",
              "<th>Hitpoints</th>\n",
              "</tr>\n",
              "<tr>\n",
              "<td>Martin Splitskull</td>\n",
              "<td>3</td>\n",
              "<td>40</td>\n",
              "</tr>\n",
              "<tr>\n",
              "<td>Sir Wolf</td>\n",
              "<td>2</td>\n",
              "<td>20</td>\n",
              "</tr>\n",
              "<tr>\n",
              "<td>Sylvain, The Grey</td>\n",
              "<td>1</td>\n",
              "<td>10</td>\n",
              "</tr>\n",
              "</table>"
            ],
            "text/plain": [
              "+-------------------+-------+-----------+\n",
              "| Name              | Level | Hitpoints |\n",
              "+-------------------+-------+-----------+\n",
              "| Martin Splitskull | 3     | 40        |\n",
              "+-------------------+-------+-----------+\n",
              "| Sir Wolf          | 2     | 20        |\n",
              "+-------------------+-------+-----------+\n",
              "| Sylvain, The Grey | 1     | 10        |\n",
              "+-------------------+-------+-----------+"
            ]
          },
          "execution_count": 8,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "SELECT Name, Level, Hitpoints FROM players;"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Querying Tables\n",
        "\n",
        "A full range of SQL query commands are supported, including aggregation operations:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "trusted": true
      },
      "outputs": [],
      "source": [
        "SELECT SUM (Level) FROM players"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Grouping also works:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 11,
      "metadata": {
        "trusted": true
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "<table>\n",
              "<tr>\n",
              "<th>Level</th>\n",
              "<th>Total Hitpoints</th>\n",
              "</tr>\n",
              "<tr>\n",
              "<td>3</td>\n",
              "<td>40</td>\n",
              "</tr>\n",
              "<tr>\n",
              "<td>2</td>\n",
              "<td>20</td>\n",
              "</tr>\n",
              "<tr>\n",
              "<td>1</td>\n",
              "<td>10</td>\n",
              "</tr>\n",
              "</table>"
            ],
            "text/plain": [
              "+-------+-----------------+\n",
              "| Level | Total Hitpoints |\n",
              "+-------+-----------------+\n",
              "| 3     | 40              |\n",
              "+-------+-----------------+\n",
              "| 2     | 20              |\n",
              "+-------+-----------------+\n",
              "| 1     | 10              |\n",
              "+-------+-----------------+"
            ]
          },
          "execution_count": 11,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "SELECT Level, SUM(Hitpoints) AS `Total Hitpoints`\n",
        "FROM players\n",
        "GROUP BY Level\n",
        "ORDER BY `Total Hitpoints` DESC;"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Charting Using Vega\n",
        "\n",
        "The `jupyter-xeus/xeus-sqlite` kernel also bundles Vega charting components.\n",
        "\n",
        "Vega charts can be generated by piping the result of a SQL query into a Vega line magic command."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 12,
      "metadata": {
        "trusted": true
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "<table>\n",
              "<tr>\n",
              "<th>Level</th>\n",
              "<th>Hitpoints</th>\n",
              "</tr>\n",
              "<tr>\n",
              "<td>3</td>\n",
              "<td>40</td>\n",
              "</tr>\n",
              "<tr>\n",
              "<td>2</td>\n",
              "<td>20</td>\n",
              "</tr>\n",
              "<tr>\n",
              "<td>1</td>\n",
              "<td>10</td>\n",
              "</tr>\n",
              "</table>"
            ],
            "text/plain": [
              "+-------+-----------+\n",
              "| Level | Hitpoints |\n",
              "+-------+-----------+\n",
              "| 3     | 40        |\n",
              "+-------+-----------+\n",
              "| 2     | 20        |\n",
              "+-------+-----------+\n",
              "| 1     | 10        |\n",
              "+-------+-----------+"
            ]
          },
          "execution_count": 12,
          "metadata": {},
          "output_type": "execute_result"
        },
        {
          "data": {
            "application/vnd.vegalite.v3+json": {
              "$schema": "https://vega.github.io/schema/vega-lite/v4.json",
              "config": {
                "axis": {
                  "grid": true
                }
              },
              "data": {
                "values": [
                  {
                    "Hitpoints": "name",
                    "Level": "name"
                  },
                  {
                    "Hitpoints": "40",
                    "Level": "3"
                  },
                  {
                    "Hitpoints": "20",
                    "Level": "2"
                  },
                  {
                    "Hitpoints": "10",
                    "Level": "1"
                  }
                ]
              },
              "encoding": {
                "x": {
                  "field": "Level",
                  "type": "quantitative"
                },
                "y": {
                  "field": "Hitpoints",
                  "type": "quantitative"
                }
              },
              "height": 200,
              "mark": {
                "type": "circle"
              },
              "width": 100
            },
            "image/png": "[encoded data, 6948 characters]"
          },
          "execution_count": 12,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "%XVEGA_PLOT\n",
        "    X_FIELD Level\n",
        "    Y_FIELD Hitpoints\n",
        "    MARK circle\n",
        "    WIDTH 100\n",
        "    HEIGHT 200\n",
        "    <>\n",
        "    SELECT Level, Hitpoints FROM players"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Database Administration\n",
        "\n",
        "Several line magics are defined to support database administration"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 15,
      "metadata": {
        "trusted": true
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "The table players exists."
            ]
          },
          "execution_count": 15,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "%TABLE_EXISTS players"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 16,
      "metadata": {
        "trusted": true
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "The table npcs doesn't exist."
            ]
          },
          "execution_count": 16,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "%TABLE_EXISTS npcs"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 17,
      "metadata": {
        "trusted": true
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "Magic header string: SQLite format 3\n",
              "Page size bytes: 4096\n",
              "File format write version: 1\n",
              "File format read version: 1\n",
              "Reserved space bytes: 0\n",
              "Max embedded payload fraction 64\n",
              "Min embedded payload fraction: 32\n",
              "Leaf payload fraction: 32\n",
              "File change counter: 4\n",
              "Database size pages: 2\n",
              "First freelist trunk page: 0\n",
              "Total freelist trunk pages: 0\n",
              "Schema cookie: 1\n",
              "Schema format number: 4\n",
              "Default page cache size bytes: 0\n",
              "Largest B tree page number: 0\n",
              "Database text encoding: 1\n",
              "User version: 0\n",
              "Incremental vaccum mode: 0\n",
              "Application ID: 0\n",
              "Version valid for: 4\n",
              "SQLite version: 3032003\n"
            ]
          },
          "execution_count": 17,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "%GET_INFO"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Connecting to a Different Database\n",
        "\n",
        "Creating a new database will connect the kernel to the new database instance."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 18,
      "metadata": {
        "trusted": true
      },
      "outputs": [],
      "source": [
        "%CREATE potato.db "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 19,
      "metadata": {
        "trusted": true
      },
      "outputs": [],
      "source": [
        "CREATE TABLE potaters(production INTEGER)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 20,
      "metadata": {
        "trusted": true
      },
      "outputs": [],
      "source": [
        "INSERT INTO potaters (production) VALUES (7)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 21,
      "metadata": {
        "trusted": true
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "<table>\n",
              "<tr>\n",
              "<th>production</th>\n",
              "</tr>\n",
              "<tr>\n",
              "<td>7</td>\n",
              "</tr>\n",
              "</table>"
            ],
            "text/plain": [
              "+------------+\n",
              "| production |\n",
              "+------------+\n",
              "| 7          |\n",
              "+------------+"
            ]
          },
          "execution_count": 21,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "SELECT * FROM potaters"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "The original database is lost:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 23,
      "metadata": {
        "trusted": true
      },
      "outputs": [
        {
          "ename": "Error",
          "evalue": "no such table: players",
          "output_type": "error",
          "traceback": [
            "Error: no such table: players"
          ]
        }
      ],
      "source": [
        "SELECT Name, Level, Hitpoints FROM players;"
      ]
    }
  ],
  "metadata": {
    "kernelspec": {
      "display_name": "SQLite",
      "language": "sql",
      "name": "SQLite"
    },
    "language_info": {
      "file_extension": ".sqlite3-console",
      "mimetype": "text/x-sqlite3-console",
      "name": "sqlite3",
      "version": "0.4.0"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 4
}