{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Setting up"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Throughout this talk, we will play with the _world_ database used in the documentation of [doobie](https://tpolecat.github.io/doobie), a most convenient JDBC wrapper for Scala. You can find instructions [here](https://tpolecat.github.io/doobie/docs/01-Introduction.html) for the set-up of a postgres database server and the installation of the _world_ database. The script [common.sc](./common.sc) contains several utilities as well as a ready-to-use doobie transactor for issuing SQL queries to the world database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\u001b[32mimport \u001b[39m\u001b[36m$file.$     \n",
       "\u001b[39m\n",
       "\u001b[32mimport \u001b[39m\u001b[36mcommon._\n",
       "\u001b[39m\n",
       "\u001b[32mimport \u001b[39m\u001b[36mdoobie._, doobie.implicits._\u001b[39m"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import $file.common\n",
    "import common._\n",
    "import doobie._, doobie.implicits._"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Test the server!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\u001b[36mres1\u001b[39m: \u001b[32mInt\u001b[39m = \u001b[32m1\u001b[39m"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql\"select 1\".query[Int].unique.transact(xa).unsafeRunSync"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Variation  1. Plain and direct SQL "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's say that we need to query the database to know the names of the capitals with a population larger than eight million people, together with their country names. The most direct way to bring this data into memory is to write an SQL query: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\u001b[36msqlLargeCapitals\u001b[39m: \u001b[32mQuery0\u001b[39m[(\u001b[32mString\u001b[39m, \u001b[32mString\u001b[39m)] = doobie.util.query$Query$$anon$3@65cd9960"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "val sqlLargeCapitals: Query0[(String, String)] = \n",
    "    sql\"\"\"\n",
    "        | select C.name, X.name \n",
    "        | from city as C, country as X \n",
    "        | where C.id = X.capital and C.population > 8000000\"\"\".stripMargin\n",
    "        .query[(String, String)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A value of type `Query0[T]` represents a SQL query which will return rows of type `T`. In order to execute this query, we have to compile it into a JDBC program. For instance, using `to[List]` we will obtain a `ConnectionIO[List[T]]` _program_, a pure value. In turn, this program has to be compiled into an input/output monad, e.g. `cats.effect.IO`, which is also a pure program, although at a lower-level of abstraction. Finally, the interpreter `unsafeRunSync` of the IO program launches the rockets and the database is accessed!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "50 millis\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "\u001b[36mres5\u001b[39m: \u001b[32mList\u001b[39m[(\u001b[32mString\u001b[39m, \u001b[32mString\u001b[39m)] = \u001b[33mList\u001b[39m(\n",
       "  (\u001b[32m\"Jakarta\"\u001b[39m, \u001b[32m\"Indonesia\"\u001b[39m),\n",
       "  (\u001b[32m\"Seoul\"\u001b[39m, \u001b[32m\"South Korea\"\u001b[39m),\n",
       "  (\u001b[32m\"Ciudad de M\\u00e9xico\"\u001b[39m, \u001b[32m\"Mexico\"\u001b[39m),\n",
       "  (\u001b[32m\"Moscow\"\u001b[39m, \u001b[32m\"Russian Federation\"\u001b[39m)\n",
       ")"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sqlLargeCapitals\n",
    "    .to[List]            // ConnectionIO[List[(String, String)]]\n",
    "    .transact(xa)        // IO[List[(String, String)]]\n",
    "    .unsafeRunSync       // List[(String, String)]\n",
    "    .timed(1)            // measure average execution time in milliseconds\n",
    "    .millis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Scala",
   "language": "scala",
   "name": "scala"
  },
  "language_info": {
   "codemirror_mode": "text/x-scala",
   "file_extension": ".sc",
   "mimetype": "text/x-scala",
   "name": "scala",
   "nbconvert_exporter": "script",
   "version": "2.13.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}