'DBI' Backend for the 'Frostlake' Database Engine

A 'DBI'-compliant driver for 'Frostlake', a SQL engine emulating core 'Snowflake' features. Speaks the engine's HTTP protocol against a running 'DatabaseHttpServer': one POST per statement with a session id the server issues on first contact, so session state (current database/schema, transactions) persists across statements. Query results arrive as data frames with Snowflake types mapped to R types.

frostlake-R

A DBI backend for Frostlake, speaking the engine's HTTP protocol against a running DatabaseHttpServer. Plain R package frostlake: transport is curl, parsing is jsonlite, the surface is the standard DBI generics — so dbConnect, dbGetQuery, dbWriteTable and everything that rides on DBI work as they do on any other backend.

Engine version

Requires a Frostlake engine 0.2.0 or newer. Ask a running server which one it is with SELECT CURRENT_VERSION() — every release answers it. The driver versions independently of the engine: it speaks the HTTP protocol, not the jar, so this is a floor rather than a lockstep pin. Recovering from a lost session and releasing the session on disconnect need engine 0.1.0; see Session lifetime.

Usage

library(DBI)

con <- dbConnect(frostlake::frostlake(),
                 host = "localhost", port = 18082,
                 dbname = "MY_DB", schema = "PUBLIC")
# or: dbConnect(frostlake::frostlake(), url = "frostlake://localhost:18082/MY_DB?schema=PUBLIC")

dbExecute(con, "CREATE TABLE people (id INTEGER, name VARCHAR)")
dbExecute(con, "INSERT INTO people VALUES (?, ?)", params = list(1L, "Ada"))   # 1
dbGetQuery(con, "SELECT id, name FROM people WHERE id = @id", params = list(id = 1L))

dbWriteTable(con, "SCORES", data.frame(id = 1:3, score = c(9.5, 8, 7)))
dbReadTable(con, "SCORES")

dbWithTransaction(con, {
  dbExecute(con, "UPDATE people SET name = 'Ada L.' WHERE id = 1")
})

dbDisconnect(con)

Failed statements raise a condition of class frostlake_error carrying the engine's message. A statement the engine refused also carries frostlake_sql_error, so tryCatch(…, frostlake_sql_error = …) tells a refused statement from an unreachable server or a failed request; a session the engine lost along with a transaction or context carries frostlake_session_lost (see Session lifetime). The text is sent as written — an empty statement included, which the engine refuses with its own message. dbConnect applies the database and schema eagerly, so an unknown database fails at connect rather than on the first statement. A plain dbname/schema value is sent bare and resolves case-insensitively, as on Snowflake — dbname = "my_db" finds MY_DB; wrap the value in double quotes itself (dbname = '"my_db"') to match an exact-case name.

Bind values

Parameters are inlined client-side. Unnamed elements of params fill ? placeholders positionally, in order; named elements fill @name/:name placeholders by name (a @/: prefix on the element's own name is ignored, and a name may repeat). The two styles mix in one statement.

Placeholders inside string literals, quoted identifiers, $$…$$ bodies and --/////* */ comments are left alone; :: is always the cast operator, and a marker directly after the end of an operand — a name, number or $variable, a closing ), ] or }, the closing quote of a literal or quoted identifier, or a ? — is never a bind, so stage references (COPY INTO t FROM @my_stage), VARIANT paths (v:address:city, {'a':1}:a, '…':a, ?:a), casts (v::date) and scripting assignment (LET x := 1) pass through untouched. A marker after a space still binds (WHERE v = :a). A named element may go unused, but a positional value that no ? consumed is an error rather than a silent drop. With placeholders and no params, dbSendQuery defers execution until dbBind; bound vectors of equal length run the statement once per index (dbAppendTable semantics).

A Snowflake Scripting block — text that opens with DECLARE, or with a BEGIN that starts a block rather than a transaction — owns its ? placeholders: they are a cursor's parameters, bound by OPEN … USING, so they reach the engine untouched. Bind values into a block by name (:name).

Result types

The R type of each column is decided once from the values it actually carries, so it holds for every row (the engine reports a plain INTEGER as NUMBER(38,0), so the type has to follow the data, not the precision):

• integral NUMBER → integer, widening to double past .Machine$integer.max and to the exact digit string past 2^53 (beyond ±2^63 the JSON parser overflows to double — a known limitation);
• scaled NUMBER, FLOAT → double; BOOLEAN → logical;
• DATE → Date; TIME → difftime;
• TIMESTAMP* → POSIXct labelled UTC — a trailing wire offset (… +0200) is honoured, so the instant never depends on the client machine's timezone. A POSIXct holds microseconds; a column carrying a finer fraction (0.1.0+ engines send up to nine digits) stays character rather than rounding the nanoseconds away;
• BINARY → a list of raw vectors (NULL elements for SQL NULLs);
• everything else, VARIANT/OBJECT/ARRAY included, arrives as character.

An unparseable cell drops its whole column to character, so no row silently lies.

Column metadata

dbColumnInfo reports name, type, precision, scale, length and nullable. length is the declared width of a text (characters) or binary (bytes) column — the type's maximum, 16777216, for an unbounded one, and the number the account reports as both that column's precision and its display size. Every other type, and every server that predates the field, reads NA_integer_ rather than 0.

Update counts

Engines from 0.1.0 report each result's affected-row count outright, and dbExecute/dbGetRowsAffected use that answer — so a query whose column merely happens to be named like a count is not mistaken for DML. Against an older engine the count is read from the result itself: DML answers a single-row result whose count columns are named "number of rows …" — one column for INSERT/DELETE, a second (always-zero) "number of multi-joined rows updated" column for UPDATE, one column per action for MERGE — and the driver sums every "number of rows …" column, the rule the engine's own JDBC driver applies. Either way UPDATE and MERGE report their true counts, and DDL and SELECT report 0. On 0.1.0+ engines DDL answers a one-row status message (Table T successfully created.), which dbGetQuery shows and dbExecute ignores.

Multi-statement requests

An HTTP session starts expecting one statement per request, as on Snowflake, and newer engines refuse a request carrying more. To run several statements in one text, declare the count — exact, or 0 for any number:

dbGetQuery(con, "SELECT 1 AS a; SELECT 2 AS b", multi_statement_count = 2)
dbExecute(con, "INSERT INTO t VALUES (1); INSERT INTO t VALUES (2), (3)",
          multi_statement_count = 0)   # 3

The parameter rides dbSendQuery/dbGetQuery/dbExecute (older engines ignore it). Queries materialize the first result set; affected-row counts sum across all of them.

Session lifetime

A connection is one session on the engine, and the session is where the current database and schema, session variables, ALTER SESSION settings, temporary tables and an open transaction live. The engine ends a session after 30 minutes idle, when it is released, or when the server restarts. From engine 0.1.0 on every answer says whether the session it ran in is new (newSession), and the driver takes the first answer that names a session as the sign of which kind of engine it is talking to.

• What is sent. Every request after the first names the session. To an engine that reports newSession it also sends requireSession: true, so a session the engine no longer holds is refused (HTTP 404) instead of being quietly replaced by a fresh one in which the statement would run somewhere else. An older engine is sent neither that field nor the release below.
• After a lost session. The refused statement did not run. If the lost session held nothing a fresh one lacks, the driver starts a fresh session, puts the connection's database and schema back on it, and sends the statement once more; if that is refused too, it raises. If the lost session held an open transaction, or context set up with USE, SET/UNSET, ALTER SESSION, a temporary object or a CREATE/DROP of a database or schema, running the statement again could put it somewhere its author did not intend, so the driver raises frostlake_session_lost instead, saying which. Either way the connection stays usable: its next statement starts a fresh session on the connection's database and schema. A transaction dbBegin() opened is over by then — the engine rolled it back when it let the session go — so dbRollback() has nothing left to do and succeeds, dbCommit() raises frostlake_session_lost, and dbWithTransaction() reports the lost session itself.
• Disconnect. dbDisconnect() sends DELETE /api/sessions/{id}, which ends the session and rolls back a transaction it left open. It is best effort, bounded by the connection's timeout or five seconds, whichever is shorter, and never raises; disconnecting again only warns. An engine older than 0.1.0 has no such endpoint, so there an open transaction gets a ROLLBACK instead and the session lingers until the engine's idle expiry. A dbConnect() whose database or schema the engine refuses releases the session it had started before raising.

tryCatch(
  dbWithTransaction(con, dbExecute(con, "INSERT INTO acc VALUES (2)")),
  frostlake_session_lost = function(e) {
    # the transaction and anything set up on the session are gone; start the
    # unit of work over -- the connection itself is fine
  })

Running the tests

The integration tests boot a real server from the engine's compiled classes:

export JAVA_HOME=/path/to/jdk17            # 17+; the engine's class-file floor
export FROSTLAKE_CLASSPATH="/path/to/frostlake-db.jar:<engine deps>"
Rscript -e 'testthat::test_local("path/to/frostlake-R")'

Without FROSTLAKE_CLASSPATH the engine-backed tests report as skipped — never as passed — so a run with no server cannot be mistaken for a green suite. The unit tests (substitution, literals, wire parsing driven from wire-shaped JSON, quoting, DSN, and session handling over a scripted transport) still run and cover most of the driver on their own. R CMD check passes clean (Status: OK).

check/testkit-suites.R replays the engine's language-neutral test corpus through the driver. Point FL_CORPUS at the testkit directory of a frostlake checkout (an absolute path) and the test run replays it too, against the engine configured as above; without FL_CORPUS the corpus reports as skipped:

FL_CORPUS=/path/to/frostlake/engine/src/test/resources/testkit \
  Rscript -e 'testthat::test_local("path/to/frostlake-R")'

Protocol

One POST /api/execute per statement with { sql, sessionId, autoCommit } (plus multiStatementCount when declared, and requireSession once the engine has shown it reports newSession); the server issues the sessionId on first contact and the connection echoes it back, so session state (current database/schema, transactions) persists across statements. GET /api/health backs dbConnect's reachability check, and DELETE /api/sessions/{id} releases the session on dbDisconnect (see Session lifetime). (There is no connection pooling, and the protocol carries no credentials, so authentication is not available over this transport.)