{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Managing Table Templates\n",
    "\n",
    "This notebook shows how you can manage and utilize table templates with the ThanoSQL library. We will cover how to create, update, delete, and list table templates. Stored table templates can then be used to create new tables.\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 Table Templates"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can get the list of stored table 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)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "table_templates = client.table.template.list(search=\"search_keyword\", order_by=\"name_asc\")\n",
    "for table_template in table_templates:\n",
    "    print(table_template.name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By default, `list()` includes all versions of stored table templates. To show only the latest versions of each template, set the `latest` parameter."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "client.table.template.list(latest=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also choose to only show table templates with a certain name and version with `get()`. While the parameter `name` is required, `version` is optional. When not set, all versions of the table template with name specified will be listed. `get()` also returns the list of all available versions, regardless of whether `version` is specified or not."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "res = client.table.template.get(name=\"table_template_name\", version=\"1.0\")\n",
    "for table_template in res[\"table_templates\"]:\n",
    "    print(table_template.name, table_template.version)\n",
    "for version in res[\"versions\"]:\n",
    "    print(version)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating Table Templates"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can create table templates using the library. However, a few extra imports are needed to construct the table object. You need to first construct `Column` objects and `Constraint` objects, if applicable. Optionally, you can also specify a version number and compatibility type. If they are not specified, a default value of `1.0` and `ignore` will be used."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from thanosql.resources import BaseColumn, Constraints, ForeignKey, PrimaryKey, TableObject, Unique\n",
    "\n",
    "\n",
    "# construct list of columns\n",
    "columns = [BaseColumn(type=\"integer\", name=\"column_1\"), BaseColumn(type=\"varchar\", name=\"column_2\"), BaseColumn(type=\"double precision\", name=\"column_3\"), BaseColumn(type=\"timestamp\", name=\"column_4\")]\n",
    "\n",
    "# construct unique columns (optional)\n",
    "unique = Unique(name=\"my_unique_1\", columns=[\"column_2\"])\n",
    "\n",
    "# construct primary key (optional but recommended)\n",
    "primary_key = PrimaryKey(name=\"my_pk_1\", columns=[\"column_1\"])\n",
    "\n",
    "# construct foreign keys (requires another existing table -- not table template)\n",
    "foreign_key = ForeignKey(name=\"my_fk_1\", reference_schema=\"public\", reference_column=\"another_column_1\", reference_table=\"another_table\", column=\"column_3\")\n",
    "\n",
    "# assemble everything into a table object\n",
    "table_object = TableObject(columns=columns, constraints=Constraints(unique=[unique], primary_key=primary_key, foreign_keys=[foreign_key]))\n",
    "\n",
    "# create the table template using ThanoSQL client, with default version and compatibility\n",
    "res = client.table.template.create(name=\"my_table_template_1\", table_template=table_object)\n",
    "res"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Updating Table Templates"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is not possible to update a table template once it is saved. \"Updating\" a table template is instead done by creating a new template with the same name but higher version. Consequently, no two table templates have the same name and version: there can be multiple table templates with the same name, but each has to have a unique version. This is done to maintain compatibility. The ThanoSQL convention of versioning is \"x.y\", where x ranges from 1 to 9 and y ranges from 0 to 9.\n",
    "\n",
    "For example, if we want to update the table template we just created by removing all constraints except for the primary key, we can do the following:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "table_object_updated = TableObject(columns=columns, constraints=Constraints(primary_key=primary_key))\n",
    "\n",
    "res = client.table.template.create(name=\"my_table_template_1\", table_template=table_object_updated, version=\"2.0\")\n",
    "res"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Deleting Table Templates"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can remove table templates that you no longer use. Similar to listing table templates of a certain name, you have to provide a target name, but version is optional. If `version` is not specified, all versions of table templates with the target `name` will be removed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "client.table.template.delete(name=\"my_table_template\", version=\"1.0\")  # will only delete version 1.0 of \"my_table_template\"\n",
    "client.table.template.delete(\"my_table_template_1\")  # will delete all table templates which name is \"my_table_template_1\""
   ]
  }
 ],
 "metadata": {
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}