{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "# RefgetDBAgent Tutorial\n\nThis tutorial shows you how to use `RefgetDBAgent` to manage a PostgreSQL database of sequence collections for building a seqcol API server.\n\n<div class=\"admonition success\">\n  <p class=\"admonition-title\">Learning objectives</p>\n  <ul>\n    <li>Connect to a PostgreSQL database with RefgetDBAgent</li>\n    <li>List and retrieve sequence collections</li>\n    <li>Compare sequence collections</li>\n    <li>Use specialized sub-agents for different operations</li>\n  </ul>\n</div>\n\n## Prerequisites\n\nThis tutorial requires a running PostgreSQL database. Set up environment variables before running:\n\n```bash\nexport POSTGRES_HOST=localhost\nexport POSTGRES_DB=refget\nexport POSTGRES_USER=postgres\nexport POSTGRES_PASSWORD=yourpassword\n```\n\nOr use the demo setup:\n\n```bash\ncd repos/refget\nbash deployment/demo_up.sh  # Starts postgres + loads demo data\n```\n\n<div class=\"admonition note\">\n  <p class=\"admonition-title\">Note</p>\n  <p>For most users, the <strong>CLI</strong> (<code>refget admin</code>) or <strong>RefgetStore</strong> (local file-based storage) are simpler alternatives. Use RefgetDBAgent when you need direct database access for building a seqcol API server.</p>\n</div>"
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-01-23T02:05:48.739196Z",
     "iopub.status.busy": "2026-01-23T02:05:48.738970Z",
     "iopub.status.idle": "2026-01-23T02:05:49.128480Z",
     "shell.execute_reply": "2026-01-23T02:05:49.128013Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connected to database via: Engine(postgresql://seqcol:***@localhost/seqcol)\n"
     ]
    }
   ],
   "source": [
    "# Initialize the database agent\n",
    "# Requires POSTGRES_* environment variables to be set\n",
    "from refget.agents import RefgetDBAgent\n",
    "\n",
    "agent = RefgetDBAgent()\n",
    "print(f\"Connected to database via: {agent.engine}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Querying collections\n",
    "\n",
    "With the demo setup, one sequence collection is pre-loaded. List the available collections:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-01-23T02:05:49.180636Z",
     "iopub.status.busy": "2026-01-23T02:05:49.180235Z",
     "iopub.status.idle": "2026-01-23T02:05:49.204210Z",
     "shell.execute_reply": "2026-01-23T02:05:49.203852Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Available collections: {'pagination': {'page': 0, 'page_size': 50, 'total': 1}, 'results': [SequenceCollection(digest='XZlrcEGi6mlopZ2uD8ObHkQB1d0oDwKk', names_digest='Fw1r9eRxfOZD98KKrhlYQNEdSRHoVxAG', sorted_name_length_pairs_digest='zjM1Ie9m0zFbqsAnZ6jAJSXuFpKTr40J', sequences_digest='0uDQVLuHaOZi1u76LjV__yrVUIz9Bwhr', sorted_sequences_digest='KgWo6TT1Lqw6vgkXU9sYtCU9xwXoDt6M', lengths_digest='cGRMZIb3AVgkcAfNv39RN7hnT5Chk7RX', name_length_pairs_digest='B9MESWM8k-hK_OeQK8bZNAG74pLY0Ujq')]}\n"
     ]
    }
   ],
   "source": [
    "# List available collections\n",
    "collections = agent.seqcol.list_by_offset()\n",
    "print(f\"Available collections: {collections}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Retrieving a collection\n",
    "\n",
    "Retrieve a specific collection by its digest:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-01-23T02:05:49.205467Z",
     "iopub.status.busy": "2026-01-23T02:05:49.205356Z",
     "iopub.status.idle": "2026-01-23T02:05:49.214289Z",
     "shell.execute_reply": "2026-01-23T02:05:49.213913Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collection: {'lengths': [8, 4, 4], 'names': ['chrX', 'chr1', 'chr2'], 'sequences': ['SQ.iYtREV555dUFKg2_agSJW6suquUyPpMw', 'SQ.YBbVX0dLKG1ieEDCiMmkrTZFt_Z5Vdaj', 'SQ.AcLxtBuKEPk_7PGE_H4dGElwZHCujwH6'], 'sorted_sequences': ['SQ.AcLxtBuKEPk_7PGE_H4dGElwZHCujwH6', 'SQ.YBbVX0dLKG1ieEDCiMmkrTZFt_Z5Vdaj', 'SQ.iYtREV555dUFKg2_agSJW6suquUyPpMw'], 'name_length_pairs': [{'length': 8, 'name': 'chrX'}, {'length': 4, 'name': 'chr1'}, {'length': 4, 'name': 'chr2'}]}\n"
     ]
    }
   ],
   "source": [
    "# Retrieve a collection by digest\n",
    "digest = \"XZlrcEGi6mlopZ2uD8ObHkQB1d0oDwKk\"\n",
    "collection = agent.seqcol.get(digest, return_format=\"level2\")\n",
    "print(f\"Collection: {collection}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Comparing collections\n",
    "\n",
    "Compare two sequence collections to identify their similarities and differences. In this demo, we'll compare a collection to itself (showing a perfect match):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-01-23T02:05:49.215443Z",
     "iopub.status.busy": "2026-01-23T02:05:49.215334Z",
     "iopub.status.idle": "2026-01-23T02:05:49.223962Z",
     "shell.execute_reply": "2026-01-23T02:05:49.223574Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Comparison result: {'attributes': {'a_only': [], 'b_only': [], 'a_and_b': ['lengths', 'name_length_pairs', 'names', 'sequences', 'sorted_sequences']}, 'array_elements': {'a_count': {'lengths': 3, 'name_length_pairs': 3, 'names': 3, 'sequences': 3, 'sorted_sequences': 3}, 'b_count': {'lengths': 3, 'name_length_pairs': 3, 'names': 3, 'sequences': 3, 'sorted_sequences': 3}, 'a_and_b_count': {'lengths': 3, 'name_length_pairs': 3, 'names': 3, 'sequences': 3, 'sorted_sequences': 3}, 'a_and_b_same_order': {'lengths': True, 'name_length_pairs': True, 'names': True, 'sequences': True, 'sorted_sequences': True}}}\n"
     ]
    }
   ],
   "source": [
    "# Compare a collection with itself (demonstrates the comparison feature)\n",
    "digest = \"XZlrcEGi6mlopZ2uD8ObHkQB1d0oDwKk\"\n",
    "comparison = agent.compare_digests(digest, digest)\n",
    "print(f\"Comparison result: {comparison}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Sub-agents\n\nThe RefgetDBAgent provides access to specialized sub-agents:\n\n| Sub-agent | Purpose | Key Methods |\n|-----------|---------|-------------|\n| `agent.seqcol` | Sequence collection operations | `add_from_fasta_file()`, `get()`, `list_by_offset()` |\n| `agent.seq` | Individual sequence operations | `get()`, `add()`, `list()` |\n| `agent.fasta_drs` | FASTA file DRS object management | `get()`, `add()`, `add_access_method()` |\n| `agent.pangenome` | Pangenome operations | `get()`, `add()`, `add_from_fasta_pep()` |\n| `agent.attribute` | Attribute array operations | `get()`, `list()`, `search()` |\n\n<div class=\"admonition success\">\n  <p class=\"admonition-title\">Summary</p>\n  <ul>\n    <li><code>RefgetDBAgent()</code> connects to PostgreSQL using <code>POSTGRES_*</code> environment variables</li>\n    <li><code>agent.seqcol.list_by_offset()</code> and <code>agent.seqcol.get(digest)</code> for querying collections</li>\n    <li><code>agent.compare_digests(a, b)</code> compares two collections</li>\n    <li>Sub-agents provide specialized operations: <code>seqcol</code>, <code>seq</code>, <code>fasta_drs</code>, <code>pangenome</code>, <code>attribute</code></li>\n  </ul>\n</div>"
  }
 ],
 "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.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}