{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQL Kernel\n",
    "SQL (Structured Query Language) is one of the oldest and most popular languages for database access.\n",
    "BeakerX has first-class support for SQL, including syntax highlighting, autocompletion, and autotranslation to JavaScript (and more languages [coming](https://github.com/twosigma/beakerx/issues/5039)).\n",
    "Most of the languages in BeakerX have their own SQL APIs, and you can use them as well.\n",
    "\n",
    "In order to be completely self-contained, this demo notebook uses an in-memory H2 database that's created and populated here before we query it.\n",
    "Normally you would connect to an existing database by putting its JDBC configuration\n",
    "in the `%defaultDatasource` magic.  You can access more than one database from the same notebook with the `%datasources` magic.\n",
    "\n",
    "For example, if you want to connect to a MySQL running locally then use\n",
    "`jdbc:mysql://localhost/dbName`, where dbName would be replaced as appropriate.\n",
    "\n",
    "The BeakerX SQL kernel comes with drivers for H2, PostgreSQL, MySQL, Derby, HyperSQL, SQLite, Microsoft SQL Server, and Amazon Redshift built in.\n",
    "To access another database, you can load the JDBC driver with the `%classpath add jar` magic first."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%defaultDatasource jdbc:h2:mem:db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP TABLE IF EXISTS cities;\n",
    "CREATE TABLE cities(\n",
    "    zip_code varchar(5),\n",
    "    latitude float,\n",
    "    longitude float,\n",
    "    city varchar(100),\n",
    "    state varchar(2),\n",
    "    county varchar(100),\n",
    "    PRIMARY KEY (zip_code),\n",
    ") AS SELECT\n",
    "    zip_code,\n",
    "    latitude,\n",
    "    longitude,\n",
    "    city,\n",
    "    state,\n",
    "    county\n",
    "FROM CSVREAD('../resources/data/UScity.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM cities WHERE state = 'NY'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Autotranslation of results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * INTO ${citiesTable} FROM cities WHERE longitude BETWEEN -80 and -79;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "%%javascript\n",
    "var tableStart = \"<table border>\"\n",
    "var tableTemplate = (zip, lat, long, city, state) => `<tr><td>${city}</td><td>${state}</td></tr>`\n",
    "var tableFinish = \"</table>\"\n",
    "beakerx.displayHTML(this, tableStart +\n",
    "                          beakerx.citiesTable.values.reduce((tmp, item) => tmp + tableTemplate(...item), \"\") +\n",
    "                          tableFinish)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Autocomplete"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM cities WHE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "INSERT INTO cities (zip_code, city, state) VAL"
   ]
  }
 ],
 "metadata": {
  "beakerx_kernel_parameters": {
   "datasources": "chemistry=jdbc:h2:mem:chemistry",
   "defaultDatasource": "jdbc:h2:mem:db1"
  },
  "kernelspec": {
   "display_name": "SQL",
   "language": "SQL",
   "name": "sql"
  },
  "language_info": {
   "codemirror_mode": "sql",
   "file_extension": ".sql",
   "mimetype": "",
   "name": "SQL",
   "nbconverter_exporter": "",
   "version": ""
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}