---
title: "Views, Comments, and Labels"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Views, Comments, and Labels}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

```{r, include = FALSE}
# Evaluate chunks only where the ducklake DuckDB extension is already
# installed. The probe never downloads anything, so building this vignette
# needs no network access.
ducklake_available <- ducklake::ducklake_extension_available()
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  eval = ducklake_available
)
# Use a unique temp directory for this vignette to avoid conflicts during R CMD check
vignette_temp_dir <- file.path(tempdir(), "views_vignette")
dir.create(vignette_temp_dir, showWarnings = FALSE, recursive = TRUE)
knitr::opts_knit$set(root.dir = vignette_temp_dir)
```

```{r setup, message=FALSE}
library(ducklake)
library(dplyr)

attach_ducklake("views_lake", lake_path = vignette_temp_dir)

with_transaction(
  create_table(mtcars, "cars"),
  author = "Data Engineer",
  commit_message = "Add the Motor Trend car data"
)
```

Tables hold data. A lake also holds three kinds of things about the data:
views, which store a query; comments, which document a table or a column;
and variable labels, which the lake stores as comments. All three live in
the catalog, so they travel with the lake and every client of it can read
them.

## Views

A view is a stored query. Reading it runs the query against the tables as
they are now, so a view is never stale. `create_view()` takes a dplyr
pipeline built on lake tables and stores its SQL. Like any change to the
lake, it can be committed with an author and a message:

```{r create_view}
with_transaction(
  get_ducklake_table("cars") |>
    filter(mpg > 25) |>
    select(mpg, cyl, wt) |>
    create_view("v_efficient_cars"),
  author = "Data Engineer",
  commit_message = "Define the efficient cars everyone reports on"
)

get_ducklake_table("v_efficient_cars") |> collect()
```

`get_ducklake_table()` reads views and tables alike, and further verbs
pipe onto a view as they would onto a table:

```{r view_verbs}
get_ducklake_table("v_efficient_cars") |>
  count(cyl)
```

Views are versioned. Creating, replacing, or dropping one is a snapshot in
the lake's history, like any change to a table:

```{r view_snapshots}
list_table_snapshots("v_efficient_cars")
```

### When a view is the right tool

Reach for a view when the logic belongs to the lake rather than to you: a
team's shared definition of an analysis population, a filter every report
must apply, or a derived layer that is cheap enough to compute on read and
so need not be stored. Every client sees the same definition, whether it
connects from R, Python, or plain SQL, and a change to the definition is
recorded in the snapshot history. A validation rule is logic of this kind:
`vignette("data-checks")` stores each rule as a view of the rows that break
it.

Two cases call for something else. An exploratory pipeline that only you
use is better kept in your R script under version control, where it can
change freely without adding snapshots to a shared lake. And a result that
should be materialized, because it is expensive to compute or because
downstream users need a fixed dataset, belongs in a table: `create_table()`
or `replace_table()` on the same pipeline.

Drop a view when its logic is no longer needed:

```{r drop_view}
drop_view("v_efficient_cars")
```

For logic a view cannot hold, such as a parameterized SQL macro, DuckDB SQL
is the escape hatch:
`DBI::dbExecute(get_ducklake_connection(), "CREATE MACRO ...")`.

## Table and column comments

Comments live in the catalog, so the documentation travels with the data
instead of in a sidecar file:

```{r comments}
set_table_comment("cars", "Motor Trend road tests of 1973-74 models")
set_column_comments(
  "cars",
  mpg = "Miles per US gallon",
  wt = "Weight (1000 lbs)"
)

get_table_comments("cars")
```

## Variable labels

If your data carries variable labels, they survive the lake.
`create_table()` stores each column's `label` attribute as a column
comment, and `collect()` puts the attribute back, so label-aware tools such
as gtsummary and gt behave as if the data never left R. Data imported with
haven from SAS, SPSS, or Stata arrives with these labels already. For a
data frame built in R, the labelled package sets them:

```{r labels, eval = ducklake_available && requireNamespace("labelled", quietly = TRUE)}
library(labelled)

df_visits <- data.frame(subject = c("S1", "S2"), sbp = c(128, 141)) |>
  set_variable_labels(
    subject = "Subject identifier",
    sbp = "Systolic blood pressure (mmHg)"
  )

with_transaction(
  create_table(df_visits, "visits"),
  author = "Data Engineer",
  commit_message = "Add the visit measurements"
)

df_collected <- get_ducklake_table("visits") |> collect()
var_label(df_collected)
```

The same round trip works without labelled: a label is the `label`
attribute of a column, `attr(df_visits$sbp, "label") <- "..."` sets it,
and `get_table_comments("visits")` shows what was stored. Labels also
follow columns through pipelines. A table derived with `create_table()`
from a lake table keeps the comments of the columns it selects, so a
silver or gold layer stays labelled without any extra step.

```{r cleanup}
detach_ducklake("views_lake")
```