{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Establish a database connection and retrieve the inventory and User settings\n",
    "\n",
    "Internally, geoslurp uses the python sqlalchemy tools to establish a connection with the database. The information provided is therefore similar (hostname+port, username, password). This information can be provided directly to the constructor of [GeoslurpConnector](../references/geoslurp.db.html#geoslurp.db.connector.GeoslurpConnector). Alternatively, the convenience function [geoslurpConnect](../references/geoslurp.db.html#geoslurp.db.geoslurpdb.geoslurpConnect)  uses [local settings](../installation.html#local-configuration-settings)  from the file ``.geoslurp_lastused.yaml`` in the users's home. This has the advantage that possible sensitive information (logins, passwords) does not need to be stored in notebooks such as these.\n",
    "\n",
    "## Read-only user versus priviliged user\n",
    "For the majority of the query operations, it is not needed to have write privileges to the database tables. For this purpose, it is recommended to access the database with a readonly user to avoid making unwanted changes to the tables. How this is done is shown below."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using geoslurpConnect\n",
    "There are [several ways to specify passwords](../installation.html#specifying-passwords) for use with the convenience function geoslurpConnect, some which are more secure than others."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Connect to a database using the settings in ${HOME}/.geoslurp_lastused.yaml\n",
    "from geoslurp.db import geoslurpConnect\n",
    "\n",
    "#connect to the database with the normal user as specified in ${HOME}/.geoslurp_lastused.yaml\n",
    "dbcon=geoslurpConnect(readonlyuser=False)\n",
    "\n",
    "#Connect to a database with the readonly user as specified in ${HOME}/.geoslurp_lastused.yaml\n",
    "dbconReadOnly=geoslurpConnect()\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Alternatively one can use the class [GeoslurpConnector](../references/geoslurp.db.html#geoslurp.db.connector.GeoslurpConnector) and provide all connections details manually\n",
    "This will **not** consult the file `${HOME}/.geoslurp_lastused.yaml` and can look like:\n",
    "\n",
    "`from geoslurp.db import GeoslurpConnector\n",
    "dbcon=GeoslurpConnector(host=\"geoslurphost\",user=\"yourusername\")`\n",
    "\n",
    "The above will prompt for a password but if this is not desired one can non-interactively initiate a connection as:\n",
    "\n",
    "`dbcon=GeoslurpConnector(host=\"geoslurphost\",user=\"slurpy\",passwd=\"supersecretpassword\")`\n",
    "\n",
    "Note that this **not recommended** for code which is likely to be shared later, such as for example jupyter notebooks such as this.\n",
    "\n",
    "## Loading the Dataset Inventory\n",
    "The [Inventory](../references/geoslurp.db.html#geoslurp.db.inventory.Inventory) class is coupled to the *admin.inventory* table in the database, and contains information on registered datasets. One can load the data the table by initializing a Inventory class instance with a database connection such as obtained from above. Entries (i.e.) can be requested or iterated over as shown below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "globalgis gshhs_c None roelof 2019-02-27 09:47:02.441119 (0, 0, 0) None None {'GSHHGversion': [2, 3, 7]}\n",
      "found 150 registered datasets\n"
     ]
    }
   ],
   "source": [
    "from geoslurp.db import Inventory\n",
    "\n",
    "\n",
    "inventory=Inventory(dbcon)\n",
    "\n",
    "entry=inventory[\"globalgis.gshhs_c\"]\n",
    "print(entry.scheme, entry.dataset, entry.pgfunc,entry.owner,entry.lastupdate,entry.version,entry.cache,entry.datadir,entry.data)\n",
    "\n",
    "dsets=[ds.dataset for ds in inventory]\n",
    "print(\"found %d registered datasets\"%len(dsets))\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setting and getting usersettings from the database\n",
    "\n",
    "In contrast to settings which are specific for a host and user, the database also contains a table, *admin.settings* which contains settings which are specific to the server and user. These can be loaded by initializing a [Settings](../references/geoslurp.db.html#geoslurp.db.settings.Settings) class. Note that the read-only user does not have any settings stored in the database and has no read permission to the table.\n",
    "\n",
    "### Default settings\n",
    "The *admin.settings* table has a *default* entry which contains settings which are valid for all users, unless they are overruled by the user themselves.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "succesfully set and synchronize an user setting\n"
     ]
    }
   ],
   "source": [
    "from geoslurp.db import Settings\n",
    "import random\n",
    "\n",
    "conf=Settings(dbcon)\n",
    "\n",
    "#to show the user settings:\n",
    "#conf.show()\n",
    "\n",
    "# try setting an entry\n",
    "testvalue=random.random()\n",
    "conf[\"test\"]=testvalue\n",
    "#synchronize the settings with the database\n",
    "conf.update()\n",
    "\n",
    "#load the settings from the database\n",
    "conf2=Settings(dbcon)\n",
    "if testvalue == conf2[\"test\"]:\n",
    "    print(\"succesfully set and synchronize an user setting\")\n",
    "\n",
    "#clean up after ourselves\n",
    "del conf[\"test\"]\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Storing and retrieving Authentication details\n",
    "To enable non-interactive downloading,  users may need to access webservices which require authentication details. These authentication details are also stored in the settings table of the database, but as this is sensitive information, the authentication data is encrypted in the database. The encryption is based on the user's password, **which is unknown to the database** (only the hash is known to the database). Consequently, the authentication details can only be decrypted on clients. The implication of this is that **it is not possible to retrieve the authentication data, when a user forgets their password**. \n",
    "\n",
    "The [Credentials](../references/geoslurp.db.html#geoslurp.db.settings.Credentials) is a named tuple which is used to store the authentication details."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "succesfully stored and retrieved authentication credentials\n"
     ]
    }
   ],
   "source": [
    "#store new authentication details\n",
    "from geoslurp.db import Credentials\n",
    "import string\n",
    "\n",
    "\n",
    "passw=random.choice(string.ascii_letters)\n",
    "\n",
    "\n",
    "testauth=Credentials(alias=\"servicex\",user=\"donny\",passw=passw)\n",
    "\n",
    "conf.updateAuth(testauth)\n",
    "\n",
    "conf2=Settings(dbcon)\n",
    "cred2=conf2.authCred(\"servicex\")\n",
    "if cred2.passw == passw:\n",
    "    print(\"succesfully stored and retrieved authentication credentials\")\n",
    "\n",
    "#clean up\n",
    "conf.delAuth(\"servicex\")\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "pyrr",
   "language": "python",
   "name": "pyrr"
  },
  "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.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}