{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Managing Views\n",
    "\n",
    "This notebook shows how you can manage views with the ThanoSQL library. We will cover how to create views, get the list of stored views, and delete views.\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 Views"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can get the list of views in your workspace using the `list()` function. By default, views from all schemas will be shown, but you can change this behavior by specifying `schema`. You can also specify `offset` and `limit` (the default values are 0 and 100, respectively)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "views = client.view.list()\n",
    "for view in views:\n",
    "    print(view.name, view.table_schema)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By default, the output will only include the name and schema of the view. In order to include more information, namely the list of columns the view has and its definition, you have to enable the `verbose` option."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "views = client.view.list(verbose=True)\n",
    "for view in views:\n",
    "    print(view.columns, view.definition)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also show the details (name, schema, columns, definition) of a specific view using the `get()` function. Aside from the view `name`, you can also specify the schema the view belongs to. While this is not required for views in the default (`public`) schema, this is necessary for views in other schemas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "view = client.view.get(name=\"my_view_in_schema_1\", schema=\"schema_1\")\n",
    "view"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating Views"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Currently, there is no direct API for creating views. However, it is possible to do through raw query using the query API."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"CREATE VIEW my_view_1 AS SELECT column_2, column_3 FROM my_table_1\"\n",
    "\n",
    "client.query.execute(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Deleting Views"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also delete views from your workspace using the client. Similar to `get`, you do not need to specify a schema if the view is stored in the `public` schema. For views in other schemas, you have to pass in the schema name as well."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "client.view.delete(\"my_view_1\")\n",
    "client.view.delete(name=\"my_view_in_schema_1\", schema=\"schema_1\")"
   ]
  }
 ],
 "metadata": {
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}