{
  "cells": [
    {
      "metadata": {
        "trusted": false
      },
      "id": "874eddd3",
      "cell_type": "code",
      "source": "import pandas as pd\nimport fitsio\nimport tqdm\nimport sqlite3\nimport os\nimport sys\nimport glob",
      "execution_count": 1,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": false
      },
      "id": "00149803",
      "cell_type": "code",
      "source": "os.system(\"rm dessci.db && touch dessci.db\")",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": false
      },
      "id": "b77a0567",
      "cell_type": "code",
      "source": "def _load_table(table):\n    fnames = glob.glob(table + \"*.fits\")\n    with sqlite3.connect(\"dessci.db\") as conn:\n        for fname in tqdm.tqdm(fnames, desc=table):\n            d = fitsio.read(fname)\n            df = pd.DataFrame(d)\n            df.to_sql(\"y6a2_\" + table, conn, if_exists='append', index=False)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": false
      },
      "id": "a600ab7f",
      "cell_type": "code",
      "source": "_load_table(\"proctag\")",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": false
      },
      "id": "33c6705b",
      "cell_type": "code",
      "source": "_load_table(\"miscfile\")",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": false
      },
      "id": "5a547563",
      "cell_type": "code",
      "source": "_load_table(\"file_archive_info\")",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": false
      },
      "id": "2e6f20ac",
      "cell_type": "code",
      "source": "with sqlite3.connect(\"dessci.db\") as conn:\n    conn.execute(\"create index idx_pfw_attempt_id_y6a2_miscfile on y6a2_miscfile (pfw_attempt_id)\")",
      "execution_count": 2,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": false
      },
      "id": "5a41a022",
      "cell_type": "code",
      "source": "with sqlite3.connect(\"dessci.db\") as conn:\n    conn.execute(\"create index idx_filename_y6a2_miscfile on y6a2_miscfile (filename)\")",
      "execution_count": 7,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": false
      },
      "id": "67e863ea",
      "cell_type": "code",
      "source": "with sqlite3.connect(\"dessci.db\") as conn:\n    conn.execute(\"create index idx_filename_y6a2_file_archive_info on y6a2_file_archive_info (filename)\")",
      "execution_count": 8,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": false
      },
      "id": "b1211ab2",
      "cell_type": "code",
      "source": "with sqlite3.connect(\"dessci.db\") as conn:\n    conn.execute(\"create index idx_pfw_attempt_id_y6a2_proctag on y6a2_proctag (pfw_attempt_id)\")",
      "execution_count": 3,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": false
      },
      "id": "aa23c309",
      "cell_type": "code",
      "source": "with sqlite3.connect(\"dessci.db\") as conn:\n    cur = conn.cursor()\n    cur.execute(\n\"\"\"\n\nEXPLAIN QUERY PLAN  select\n    t.*\nfrom\n    y6a2_proctag t,\n    y6a2_miscfile m\nwhere\n    t.tag = 'Y6A2_COADD'\n    and t.pfw_attempt_id = m.pfw_attempt_id \nlimit 10\n\"\"\"\n)\n    print(list(cur.fetchall()))",
      "execution_count": 5,
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": "[(4, 0, 0, 'SCAN t'), (8, 0, 0, 'SEARCH m USING COVERING INDEX idx_pfw_attempt_id_y6a2_miscfile (PFW_ATTEMPT_ID=?)')]\n"
        }
      ]
    },
    {
      "metadata": {
        "trusted": false
      },
      "id": "dc436637",
      "cell_type": "code",
      "source": "with sqlite3.connect(\"dessci.db\") as conn:\n    cur = conn.cursor()\n    cur.execute(\n\"\"\"\n\nselect\n    t.*\nfrom\n    y6a2_proctag t,\n    y6a2_miscfile m\nwhere\n    t.tag = 'Y6A2_COADD'\n    and t.pfw_attempt_id = m.pfw_attempt_id \nlimit 10\n\"\"\"\n)\n    print(list(cur.fetchall()))",
      "execution_count": 6,
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": "[('Y6A2_COADD', '2020-09-16T11:32:42.000000000', 'MJOHNS44', 2840551), ('Y6A2_COADD', '2020-09-16T11:32:42.000000000', 'MJOHNS44', 2840551), ('Y6A2_COADD', '2020-09-16T11:32:42.000000000', 'MJOHNS44', 2840551), ('Y6A2_COADD', '2020-09-16T11:32:42.000000000', 'MJOHNS44', 2840551), ('Y6A2_COADD', '2020-09-16T11:32:42.000000000', 'MJOHNS44', 2840551), ('Y6A2_COADD', '2020-09-16T11:32:42.000000000', 'MJOHNS44', 2840551), ('Y6A2_COADD', '2020-09-16T11:32:42.000000000', 'MJOHNS44', 2840551), ('Y6A2_COADD', '2020-09-16T11:32:42.000000000', 'MJOHNS44', 2840551), ('Y6A2_COADD', '2020-09-16T11:32:42.000000000', 'MJOHNS44', 2840551), ('Y6A2_COADD', '2020-09-16T11:32:42.000000000', 'MJOHNS44', 2840551)]\n"
        }
      ]
    },
    {
      "metadata": {
        "trusted": false
      },
      "id": "341078cd",
      "cell_type": "code",
      "source": "with sqlite3.connect(\"dessci.db\") as conn:\n    cur = conn.cursor()\n    cur.execute(\n\"\"\"\nEXPLAIN QUERY PLAN select\n    fai.filename || fai.compression as filename,\n    fai.path as path,\n    m.tilename\nfrom\n    y6a2_proctag t,\n    y6a2_miscfile m,\n    y6a2_file_archive_info fai\nwhere\n    t.tag = 'Y6A2_COADD'\n    and t.pfw_attempt_id = m.pfw_attempt_id \n    and m.filetype='coadd_tiff'\n    and m.filename=fai.filename\n\"\"\"\n)\n    for row in cur:\n        print(row)",
      "execution_count": 9,
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": "(6, 0, 0, 'SCAN t')\n(10, 0, 0, 'SEARCH m USING INDEX idx_pfw_attempt_id_y6a2_miscfile (PFW_ATTEMPT_ID=?)')\n(19, 0, 0, 'SEARCH fai USING INDEX idx_filename_y6a2_file_archive_info (FILENAME=?)')\n"
        }
      ]
    },
    {
      "metadata": {
        "trusted": false
      },
      "id": "ba41f865",
      "cell_type": "code",
      "source": "%%time \n\nwith sqlite3.connect(\"dessci.db\") as conn:\n    cur = conn.cursor()\n    cur.execute(\n\"\"\"\nselect\n    fai.filename || fai.compression as filename,\n    fai.path as path,\n    m.tilename\nfrom\n    y6a2_proctag t,\n    y6a2_miscfile m,\n    y6a2_file_archive_info fai\nwhere\n    t.tag = 'Y6A2_COADD'\n    and t.pfw_attempt_id = m.pfw_attempt_id \n    and m.filetype='coadd_tiff'\n    and m.filename=fai.filename\n\"\"\"\n)\n    print(len([row for row in cur]))",
      "execution_count": 13,
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": "9534\nCPU times: user 1.7 s, sys: 2.17 s, total: 3.87 s\nWall time: 6.04 s\n"
        }
      ]
    },
    {
      "metadata": {
        "trusted": false
      },
      "id": "0f7a4fca",
      "cell_type": "code",
      "source": "",
      "execution_count": null,
      "outputs": []
    }
  ],
  "metadata": {
    "gist": {
      "id": "",
      "data": {
        "description": "sqlite-desdm",
        "public": true
      }
    },
    "kernelspec": {
      "name": "conda-env-des-y6-dev-py",
      "display_name": "Python [conda env:des-y6-dev]",
      "language": "python"
    },
    "language_info": {
      "name": "python",
      "version": "3.10.6",
      "mimetype": "text/x-python",
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "pygments_lexer": "ipython3",
      "nbconvert_exporter": "python",
      "file_extension": ".py"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}