{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "39453dd1",
   "metadata": {},
   "source": [
    "# What's in the Database?\n",
    "\n",
    "Below is a simple count of what is in the database. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "a3d24b6c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import all the python db table reflections \n",
    "from snowexsql.data import SiteData, PointData, LayerData, ImageData\n",
    "\n",
    "# Import the function to get access\n",
    "from snowexsql.db import get_db\n",
    "\n",
    "\n",
    "def get_all_distinct(attribute):\n",
    "    \"\"\"\n",
    "    Function to count all the unique & valid entries of a column \n",
    "    in a table. \n",
    "    \"\"\"\n",
    "    final = {}\n",
    "    final['Total'] = 0\n",
    "\n",
    "    # Loop over all the tables\n",
    "    tables = [PointData, LayerData, ImageData]\n",
    "    for tbl_cls in tables:\n",
    "        \n",
    "        # Grab the column from the table class\n",
    "        obj_att = getattr(tbl_cls, attribute)\n",
    "        \n",
    "        # Count all the distinct and non-None values\n",
    "        result = session.query(obj_att).filter(obj_att != None).distinct().count()\n",
    "        \n",
    "        # Save the result and keep track of the total\n",
    "        final[tbl_cls.__name__] = result\n",
    "        final['Total'] += result \n",
    "\n",
    "    return final \n",
    "\n",
    "\n",
    "def print_result(count_dict):\n",
    "    \"\"\"\n",
    "    Simple function to print out the counts nicely\n",
    "    \"\"\"\n",
    "    \n",
    "    print('\\nDB Table Breakdown:')\n",
    "    for k in ['PointData', 'LayerData', 'ImageData']:\n",
    "        print(f'\\t* {k} = {count_dict[k]}')\n",
    "       "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f597e21d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get connection to the DB\n",
    "db_name = 'db.snowexdata.org/snowex'\n",
    "engine, session = get_db(db_name, credentials='./credentials.json')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c512d63f",
   "metadata": {},
   "source": [
    "## DOIS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "350ad9b0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Published Datasets: 6\n",
      "\n",
      "DB Table Breakdown:\n",
      "\t* PointData = 2\n",
      "\t* LayerData = 2\n",
      "\t* ImageData = 2\n"
     ]
    }
   ],
   "source": [
    "dois = get_all_distinct('doi')\n",
    "print(f'Published Datasets: {dois[\"Total\"]}')\n",
    "print_result(dois)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02057c62",
   "metadata": {},
   "source": [
    "## Datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "7ba72325",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unique Data types: 19\n",
      "\n",
      "DB Table Breakdown:\n",
      "\t* PointData = 3\n",
      "\t* LayerData = 9\n",
      "\t* ImageData = 7\n"
     ]
    }
   ],
   "source": [
    "data_types = get_all_distinct('type')\n",
    "print(f'Unique Data types: {data_types[\"Total\"]}')\n",
    "print_result(data_types)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f85f6c8e",
   "metadata": {},
   "source": [
    "## Instruments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "94502078",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unique Instruments: 8\n",
      "\n",
      "DB Table Breakdown:\n",
      "\t* PointData = 5\n",
      "\t* LayerData = 1\n",
      "\t* ImageData = 2\n"
     ]
    }
   ],
   "source": [
    "instruments = get_all_distinct('instrument')\n",
    "print(f'Unique Instruments: {instruments[\"Total\"]}')\n",
    "print_result(instruments)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22b847d7",
   "metadata": {},
   "source": [
    "## Pits"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "1ed2a027",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unique Pits: 155\n"
     ]
    }
   ],
   "source": [
    "pits = session.query(SiteData.site_id, SiteData.date).distinct().count()\n",
    "print(f'Unique Pits: {pits}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "ad860ec0",
   "metadata": {},
   "outputs": [],
   "source": [
    "session.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "96d735a8",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}