--- title: "Column types and coercion" output: rmarkdown::html_vignette vignette: > %\VignetteIndexEntry{Column types and coercion} %\VignetteEngine{knitr::rmarkdown} %\VignetteEncoding{UTF-8} resource_files: - figures/logo.svg - figures/logo-dark.svg ---

```{r setup, include = FALSE} knitr::opts_chunk$set(collapse = TRUE, comment = "#>") ``` harbouR maps SeaTable's column types onto predictable R types, so the tibble you get back is analysis-ready, not raw JSON. This vignette is the reference. ## The mapping table The table below is derived from `hb_column_types()` so it cannot drift out of sync with the code: ```{r mapping} library(harbouR) knitr::kable(hb_column_types()) ``` Three of its rows are not column types of their own. `percent`, `dollar` and `euro` are formats of a number column: SeaTable reports such a column as type `number`, with the format in its `data`. They are listed so that a table carrying them as a type still reads as numbers. ## What the rows look like `hb_example_rows()` is a small hand-written frame covering the common types. The bundled `.dtable` fixture is the exhaustive one - it carries a column of nearly every type - and the last section reads it. ```{r samples} samples <- hb_example_rows("Samples") samples ``` ### Scalars ```{r scalars} str(samples[, c("Name", "Concentration", "Status", "Collected")]) ``` * `text` / `single-select` come back as `character`. A single-select value is not checked against the column's options when you write it: SeaTable adds an unknown name as a new option, so a typo becomes one. * `number` becomes a `double` (caveat: SeaTable's numeric column is 64-bit; large integers may round - if precision matters store them as text). * `checkbox` becomes `logical`. A box that was never ticked reads as `FALSE`, as SeaTable shows it. When you write, harbouR sends the JSON `true` or `false` SeaTable needs: a number is ticked unless it is 0, and a string is read as `as.logical()` reads it, so `"TRUE"`, `"true"` and `"T"` tick the box and `"FALSE"`, `"false"` and `"F"` clear it. Any other non-empty string, `NA` and `NaN` are sent as empty, the string with a warning. * `date` always becomes `POSIXct`, in UTC. In a date-only column - one whose format has no time - the value is midnight UTC on the date SeaTable shows, whatever the server's time zone, so `as.Date()` gives that calendar date. A date-time column holds an instant: a server sends it with its UTC offset and harbouR converts it to UTC, while a `.dtable` stores it without one and harbouR reads it as UTC. A column that reports no format is read as a date-time. Written to a date-only column, a `Date` is sent as itself and a date-time as the date it shows in its own time zone, except one at exactly midnight UTC, which keeps its UTC date, so a cell read and written back keeps its day. ### List-columns These types always come back as list-columns, even when every row has one value or none. That is the type-stability rule: the same column has the same R type for every row. ```{r list-types} subset(hb_column_types(), is_list)$seatable ``` ```{r tags} samples$Tags ``` Flatten with `tidyr::unnest` when you need a long form: ```{r unnest, eval = requireNamespace("tidyr", quietly = TRUE)} tidyr::unnest(samples[, c("Name", "Tags")], cols = "Tags") ``` ### File and image columns ```{r files} samples$Reports[[1]] ``` Each entry is a list of file objects with `name`, `size`, `type` and `url` - ready to be downloaded via `hb_download_file()`. ### Read-only columns harbouR leaves columns of these types out of every write, so you can round-trip a tibble through harbouR without writing back computed values by accident. ```{r read-only} subset(hb_column_types(), read_only)$seatable ``` Most of them are computed or maintained by SeaTable. Links are kept through SeaTable's link endpoints rather than by writing the cell, and a button carries no data. A `digital-sign` cell is left out for another reason: a signature should come from the signer, not from a script. ## Empty tables A table with no rows still comes back as a tibble with every column in its correct type: ```{r empty} empty <- hb_example_rows("Samples")[0, ] empty str(empty, max.level = 1) ``` Every column keeps the type it would have had with rows present - `Concentration` is still a double, `Collected` still a `POSIXct`, and `Tags` still a list-column - so downstream code that expects a schema does not have to special-case the empty result. ## Every type at once `hb_example_rows()` is deliberately small. The bundled `.dtable` fixture carries a column of nearly every type SeaTable defines, so it is the better place to see the whole mapping at work: ```{r fixture} base <- hb_read_dtable( system.file("extdata", "example.dtable", package = "harbouR") ) schema <- hb_list_columns(base, "Samples") knitr::kable(schema[, c("name", "type", "editable")]) ``` ```{r fixture-types} data <- hb_read_table(base, "Samples") vapply(data, function(col) class(col)[[1L]], character(1)) ``` ## Next steps * `vignette("harbouR")` - the five-minute end-to-end story. * `vignette("explorer-app")` - exploring a base interactively.