Round-trip every modelled type in a shared dialect conformance test

[nbenn]

The dialects' round-trip tests list their types by hand (sqlr.sqlite, sqlr.postgres), so a type nobody listed is not exercised. Running 28 type variants through the SQLite dialect one at a time (render, execute on an in-memory database, reflect, sqlr_diff()) finds 9 that do not come back as written:

Type	Renders as	Reflects as
sqlr_integer_type(bytes = 1L)	TINYINT	sqlr_other("tinyint")
sqlr_int(unsigned = TRUE)	INTEGER	sqlr_int()
sqlr_char()	TEXT	sqlr_text()
sqlr_blob(16)	BLOB	sqlr_blob()
sqlr_binary_type(size = 16L, fixed = TRUE)	BLOB	sqlr_blob()
sqlr_time(precision = 3)	TIME	sqlr_time()
sqlr_time(with_timezone = TRUE)	TIME	sqlr_time()
sqlr_timestamp(precision = 3)	TIMESTAMP	sqlr_timestamp()
sqlr_timestamp(with_timezone = TRUE)	TIMESTAMP	sqlr_timestamp()

The first is the reflection bug in nbenn/sqlr.sqlite#1. The other eight lose information in the renderer, before the database sees it.

Script

library(sqlr)
library(sqlr.sqlite)

types <- alist(
  sqlr_integer_type(bytes = 1L), sqlr_smallint(), sqlr_int(), sqlr_bigint(),
  sqlr_int(unsigned = TRUE), sqlr_real(), sqlr_double(), sqlr_numeric(),
  sqlr_numeric(10), sqlr_numeric(10, 2), sqlr_char(3), sqlr_char(),
  sqlr_varchar(255), sqlr_text(), sqlr_blob(), sqlr_blob(16),
  sqlr_binary_type(size = 16L, fixed = TRUE), sqlr_boolean(), sqlr_date(),
  sqlr_time(), sqlr_time(precision = 3), sqlr_time(with_timezone = TRUE),
  sqlr_timestamp(), sqlr_timestamp(precision = 3),
  sqlr_timestamp(with_timezone = TRUE), sqlr_json(), sqlr_json(binary = TRUE),
  sqlr_uuid()
)

round_trip <- function(type) {
  con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
  on.exit(DBI::dbDisconnect(con))

  tbl <- sqlr_table("t", sqlr_column("x", type))
  for (stmt in sqlr_render(tbl, sqlite())) DBI::dbExecute(con, stmt)
  sqlr_diff(tbl, sqlr_reflect(con)@tables[[1L]])
}

diffs <- lapply(types, function(e) round_trip(eval(e)))
names(diffs) <- vapply(types, deparse1, character(1L))
Filter(length, diffs)

Proposal

Core should ship the list of types, covering every type class and each parameter it carries, and a test helper that a dialect runs against a live connection: one column per type, render, execute, reflect, and expect an empty sqlr_diff(). A dialect that cannot represent a type declares it with a reason, the way DBItest's tweaks do, and the helper skips it citing that reason. A type added to the IR is then checked in every dialect without anyone adding it to a test, and each of the eight above is either fixed in the SQLite dialect or declared.

This is the first piece of the planned DBItest-style conformance suite.