{

 "cells": [

  {

   "cell_type": "code",

   "execution_count": null,

   "metadata": {},

   "outputs": [],

   "source": [

    "import re\n",

    "import csv\n",

    "import pandas\n",

    "import sqlite3\n",

    "import random\n",

    "import json\n",

    "import itertools\n",

    "import numpy as np\n",

    "from sumeval.metrics.rouge import RougeCalculator\n",

    "rouge = RougeCalculator(stopwords=False, lang=\"en\")\n",

    "\n",

    "from utils import *"

   ]

  },

  {

   "cell_type": "code",

   "execution_count": null,

   "metadata": {},

   "outputs": [],

   "source": [

    "db_file = 'mimic_db/mimic.db'\n",

    "model = query(db_file)\n",

    "(db_meta, db_tabs, db_head) = model._load_db(db_file)"

   ]

  },

  {

   "cell_type": "code",

   "execution_count": null,

   "metadata": {},

   "outputs": [],

   "source": [

    "headerDic = []\n",

    "for tb in db_head:\n",

    "    for hd in db_head[tb]:\n",

    "        headerDic.append('.'.join([tb, hd]).lower())\n",

    "# print(headerDic)\n",

    "tableDic = []\n",

    "for tb in db_head:\n",

    "    tableDic.append(tb.lower())\n",

    "# print(tableDic)"

   ]

  },

  {

   "cell_type": "code",

   "execution_count": null,

   "metadata": {},

   "outputs": [],

   "source": [

    "def parse_sql(sql):\n",

    "    \n",

    "    sqlForm = {}\n",

    "    \n",

    "    arr = re.split('where', sql)\n",

    "    qlead = re.split('from', arr[0])\n",

    "    qagg = re.split('\\s', qlead[0])\n",

    "    qagg = list(filter(None, qagg))\n",

    "    if qagg[1] == 'count' or qagg[1] == 'min' or qagg[1] == 'max' or qagg[1] == 'avg':\n",

    "        sqlForm['sel'] = qagg[1]\n",

    "    else:\n",

    "        sqlForm['sel'] = ''\n",

    "        \n",

    "    itm = []\n",

    "    for wd in qagg:\n",

    "        if wd in headerDic:\n",

    "            itm.append(wd)\n",

    "    sqlForm['agg'] = itm\n",

    "    \n",

    "    itm = []\n",

    "    qtab = re.split('\\s', qlead[1])\n",

    "    qtab = list(filter(None, qtab))\n",

    "    for wd in qtab:\n",

    "        if wd in tableDic:\n",

    "            itm.append(wd)\n",

    "    sqlForm['tab'] = itm\n",

    "        \n",

    "    qtail = re.split('and', arr[-1])\n",

    "    itm = []\n",

    "    for cond in qtail:\n",

    "        cond = re.split('\\s', cond)\n",

    "        cond = list(filter(None, cond))\n",

    "        if len(cond) > 2:\n",

    "            condVal = ' '.join(cond[2:])\n",

    "            condVal = re.split('\\\"|\\s', condVal)\n",

    "            condVal = ' '.join(list(filter(None, condVal)))\n",

    "            itm.append(cond[:2] + [condVal])\n",

    "    sqlForm['cond'] = sorted(itm)\n",

    "    \n",

    "    return sqlForm\n",

    "\n",

    "fp = open('generated_sql/output.json', 'r')\n",

    "outGen = []\n",

    "outTtt = []\n",

    "for line in fp:\n",

    "    line = json.loads(line)\n",

    "    gen = re.split('<stop>', line['sql_pred'])[0]\n",

    "    sqlG = parse_sql(gen)\n",

    "    outGen.append(sqlG)\n",

    "    \n",

    "    ttt = line['sql_gold']\n",

    "    sqlT = parse_sql(ttt)\n",

    "    outTtt.append(sqlT)\n",

    "#     print(sqlG)\n",

    "#     print(sqlT)\n",

    "#     break\n",

    "fp.close()"

   ]

  },

  {

   "cell_type": "code",

   "execution_count": null,

   "metadata": {},

   "outputs": [],

   "source": [

    "cnt = 0\n",

    "for k in range(len(outGen)):\n",

    "    if outGen[k] == outTtt[k]:\n",

    "        cnt += 1\n",

    "print('Overall logic form accuracy: {}'.format(cnt/1000))"

   ]

  },

  {

   "cell_type": "markdown",

   "metadata": {},

   "source": [

    "## Aggregation"

   ]

  },

  {

   "cell_type": "code",

   "execution_count": null,

   "metadata": {},

   "outputs": [],

   "source": [

    "cnt = 0\n",

    "for k in range(len(outGen)):\n",

    "    if outGen[k]['sel'] == outTtt[k]['sel']:\n",

    "        cnt += 1\n",

    "print('Break-down accuracy on AGGREGATION OPERATION: {}'.format(cnt/1000))"

   ]

  },

  {

   "cell_type": "code",

   "execution_count": null,

   "metadata": {},

   "outputs": [],

   "source": [

    "cnt = 0\n",

    "for k in range(len(outGen)):\n",

    "    if outGen[k]['agg'] == outTtt[k]['agg']:\n",

    "        cnt += 1\n",

    "print('Break-down accuracy on AGGREGATION COLUMN: {}'.format(cnt/1000))"

   ]

  },

  {

   "cell_type": "markdown",

   "metadata": {},

   "source": [

    "## Table"

   ]

  },

  {

   "cell_type": "code",

   "execution_count": null,

   "metadata": {},

   "outputs": [],

   "source": [

    "cnt = 0\n",

    "for k in range(len(outGen)):\n",

    "    if outGen[k]['tab'] == outTtt[k]['tab']:\n",

    "        cnt += 1\n",

    "print('Break-down accuracy on TABLE: {}'.format(cnt/1000))"

   ]

  },

  {

   "cell_type": "markdown",

   "metadata": {},

   "source": [

    "## Condition"

   ]

  },

  {

   "cell_type": "code",

   "execution_count": null,

   "metadata": {},

   "outputs": [],

   "source": [

    "cnt = 0\n",

    "for k in range(len(outGen)):\n",

    "    arrG = [wd[0] for wd in outGen[k]['cond']]\n",

    "    arrT = [wd[0] for wd in outTtt[k]['cond']]\n",

    "    if arrG == arrT:\n",

    "        cnt += 1\n",

    "print(cnt)"

   ]

  },

  {

   "cell_type": "code",

   "execution_count": null,

   "metadata": {},

   "outputs": [],

   "source": [

    "cnt = 0\n",

    "for k in range(len(outGen)):\n",

    "    arrG = [wd[:2] for wd in outGen[k]['cond']]\n",

    "    arrT = [wd[:2] for wd in outTtt[k]['cond']]\n",

    "    if arrG == arrT:\n",

    "        cnt += 1\n",

    "print('Break-down accuracy on CONDITION COLUMN AND OPERATION: {}'.format(cnt/1000))"

   ]

  },

  {

   "cell_type": "code",

   "execution_count": null,

   "metadata": {},

   "outputs": [],

   "source": [

    "cnt = 0\n",

    "for k in range(len(outGen)):\n",

    "    arrG = [wd[:3] for wd in outGen[k]['cond']]\n",

    "    arrT = [wd[:3] for wd in outTtt[k]['cond']]\n",

    "    if arrG == arrT:\n",

    "        cnt += 1\n",

    "print('Break-down accuracy on CONDITION VALUE: {}'.format(cnt/1000))"

   ]

  }

 ],

 "metadata": {

  "kernelspec": {

   "display_name": "Python 3",

   "language": "python",

   "name": "python3"

  },

  "language_info": {

   "codemirror_mode": {

    "name": "ipython",

    "version": 3

   },

   "file_extension": ".py",

   "mimetype": "text/x-python",

   "name": "python",

   "nbconvert_exporter": "python",

   "pygments_lexer": "ipython3",

   "version": "3.6.7"

  }

 },

 "nbformat": 4,

 "nbformat_minor": 2

}