{
 "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 ([coming soon](https://github.com/twosigma/beakerx/issues/5827)).\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": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%defaultDatasource jdbc:h2:mem:db1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "drop table if exists color;\n",
    "CREATE TABLE color (\n",
    "  id int(11) NOT NULL,\n",
    "  name varchar(45) NOT NULL,\n",
    "  code varchar(10),\n",
    "  PRIMARY KEY (id)\n",
    ");\n",
    "\n",
    "INSERT INTO color (id, name, code) VALUES (1001,'AliceBlue','#F0F8FF');\n",
    "INSERT INTO color (id, name, code) VALUES (1002,'AntiqueWhite','#FAEBD7');\n",
    "INSERT INTO color (id, name, code) VALUES (1003,'Aqua','#00FFFF');\n",
    "INSERT INTO color (id, name, code) VALUES (1004,'Aquamarine','#7FFFD4');\n",
    "INSERT INTO color (id, name, code) VALUES (1005,'Azure','#F0FFFF');\n",
    "INSERT INTO color (id, name, code) VALUES (1006,'Beige','#F5F5DC');\n",
    "INSERT INTO color (id, name, code) VALUES (1007,'Bisque','#FFE4C4');\n",
    "INSERT INTO color (id, name, code) VALUES (1008,'Black','#000000');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM color WHERE name LIKE 'A%';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Autotranslation of results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "SELECT * into ${colorTable} from color;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "%%javascript\n",
    "var tableStart = \"<table border>\"\n",
    "var tableTemplate = (id, value) => `<tr><td>${id}</td><td>${value}</td></tr>`\n",
    "var tableFinish = \"</table>\"\n",
    "beakerx.displayHTML(this, tableStart + \n",
    "                          beakerx.colorTable.values.reduce((tmp, item) => tmp + tableTemplate(...item), \"\") +\n",
    "                          tableFinish)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Autocomplete"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "SELECT * FROM color WHE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "INSERT INTO color (id, name, code) VA"
   ]
  }
 ],
 "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
}