{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Managing Query Templates\n",
    "\n",
    "This notebook shows how you can manage query templates with the ThanoSQL library. We will cover how to create, update, delete, and list query templates. Stored query templates can then be used to create new queries.\n",
    "First, initialize a ThanoSQL client with your API token and engine URL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from thanosql import ThanoSQL\n",
    "\n",
    "client = ThanoSQL(\n",
    "    api_token=\"THANOSQL_API_TOKEN\",\n",
    "    engine_url=\"THANOSQL_ENGINE_URL\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Listing Query Templates"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can get the list of stored query templates using the ThanoSQL library. The `list()` function can be used without any parameters or with optional parameters. To list only table templates which name contains some keyword(s), use the `search` parameter. By default, results are ordered based on recency. In order to change this ordering, use the `order_by` parameter. There are three options: `recent` (default), `name_asc` (A to Z), and `name_desc` (Z to A). You can also specify the `offset` and `limit` of the results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query_templates = client.query.template.list(search=\"search_keyword\", offset=1, limit=10, order_by=\"name_asc\")\n",
    "for query_template in query_templates:\n",
    "    print(query_template.name, query_template.query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also choose to only show query templates with a certain name with `get()`. You just need to specify the `name`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query_template = client.query.template.get(\"query_template_name\")\n",
    "print(f\"Query template ID: {query_template.id}\")\n",
    "print(f\"Query template name: {query_template.name}\")\n",
    "print(f\"Query template string: {query_template.query}\")\n",
    "print(f\"Query template parameters: {query_template.parameters}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating Query Templates"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can create query templates using the library. You simply need to name the template and write what the template string is. After sending the request, the template will be stored in your workspace database; an ID, date of creation, and date of update will be included, and the list of parameters will be automatically inferred."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query_template = client.query.template.create(name=\"my_query_template_1\", query=\"SELECT * FROM my_table_1\")\n",
    "print(\"Parameters:\")\n",
    "for param in query_template.parameters:\n",
    "    print(param)\n",
    "print(f\"ID: {query_template.id}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also create an empty template, with or without name specified. The template will be automatically named as `query_template_{maximum_available_unique_number + 1}`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "client.query.template.create()  # this will create query_template_1 with empty contents"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By default, calling `create()` will save the query template to database. If you simply want to check your template by dry running it without saving to database, you can use the `dry_run` option. By default, `dry_run` is turned off. When turned on, only `name`, `query`, and `parameters` will be displayed upon successful creation of a query template, which will not be saved."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Updating Query Templates"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is possible to update query templates using the client. You can update both the name and contents of the template. If you only want to update either the name or contents, no need to specify the other one. Only `current_name` is required in any case. The same validation process will apply after an update call is made; parameters will be inferred from the template. The difference is that there is no `dry_run` option, as it only makes sense to update templates saved to database. Also, once set, empty/none values are not allowed for either `new_name` or `query`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# updates the name only\n",
    "client.query.template.update(current_name=\"my_query_template_1\", new_name=\"my_query_template_1_updated\")\n",
    "\n",
    "# updates the contents only\n",
    "client.query.template.update(current_name=\"my_query_template_1_updated\", query=\"SELECT * FROM {{table_name}}\")\n",
    "\n",
    "# updates the name and contents at the same time\n",
    "client.query.template.update(current_name=\"my_query_template_1_updated\", new_name=\"my_query_template_1_updated_again\", query=\"SELECT * FROM {{table_name}}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Deleting Query Templates"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can remove query templates that you no longer use."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "client.query.template.delete(\"my_query_template_1_updated_again\")"
   ]
  }
 ],
 "metadata": {
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}