---
title: "Connection Management"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Connection Management}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

```{r, include = FALSE}
knitr::opts_chunk$set(collapse = TRUE, comment = "#>")
```

## The Problem

Database connections in R are stateful and can be easy to lose track of:

```r
con <- DBI::dbConnect(duckdb::duckdb(), dbdir = "data.duckdb")
tbl1 <- dplyr::tbl(con, "table1")
# ... 50 lines later: Is the connection still valid? Who closed it?
```

## dbProject's Solution

An R6 class that centralizes your connection and pinned tables:
```{r project-demo}
library(dbProject)

project_path <- tempfile("dbproject-")
proj <- dbProject$new(path = project_path)
expression_data <- data.frame(gene = c("A", "B"), count = c(10, 20))
proj$pin_write(expression_data, "expression_data")

# Later (even after R restart):
proj$reconnect()
my_tbl <- proj$pin_read("expression_data")
my_tbl
```

## DBI Compatibility

dbProject works alongside DBI, not instead of it:

| Approach | When to Use |
|----------|-------------|
| DBI directly | Quick scripts, one-off analysis |
| dbProject | Multi-session work, centralized management |

Both approaches get automatic reconnection via the `dbData` base class.

## Convenience Features

dbProject includes small convenience functions to help manage and organize
database connections in R.

`DBI::dbListTables()` is useful when you just need the table names in a
connection:

```{r dbi-list-tables}
con <- proj$get_conn()
DBI::dbWriteTable(
  con,
  "sample_metadata",
  data.frame(sample = c("sample1", "sample2"), n = c(100, 120)),
  overwrite = TRUE
)
DBI::dbExecute(
  con,
  "CREATE OR REPLACE TEMPORARY TABLE current_batch AS
   SELECT * FROM sample_metadata"
)
DBI::dbExecute(
  con,
  "CREATE OR REPLACE VIEW sample_summary AS
   SELECT sample, n FROM sample_metadata"
)

DBI::dbListTables(con)
```

`dbList()` keeps the same DBI connection but groups the results by table type,
which makes it easier to distinguish persistent tables from temporary tables and
views:

```{r dbproject-list-tables}
dbList(con)
```

## Core Concepts

### Mutable State (R6)

```r
proj <- dbProject$new(path = "my_analysis/")

# These modify the same object - no reassignment needed
proj$disconnect()
proj$reconnect()
proj$pin_write(my_tbl, "results")
```

### Centralized Management

```r
proj
#> ── dbProject ──────────────────────────────────────────
#> ✓ Connected
#> ── Board Content ──────────────────────────────────────
#>   name              type
#>   expression_data   tbl
#> ── Database Content ───────────────────────────────────
#> ℹ Tables: expression_raw, cell_types
```

### Automatic Reconnection

The empty extract `[]` method on all dbverse objects auto-reconnects if the connection is stale:

```r
mat[]  # Can be either a DBI connection or a dbProject connection
```

## Working with Pins

```r
proj$pin_write(my_tbl, "results")   # Save lazy table
my_tbl <- proj$pin_read("results") # Restore reference
proj$pin_delete("old_results")     # Clean up
```

```{r cleanup, include=FALSE}
proj$disconnect()
unlink(project_path, recursive = TRUE)
```