Package {frostlake}

DBI driver for the Frostlake database engine

Description

frostlake() returns the DBI driver object. Hand it to dbConnect together with the coordinates of a running Frostlake DatabaseHttpServer; every DBI verb then works on the connection. One HTTP POST per statement carries the SQL and a session id the server issues on first contact, so session state (current database/schema, transactions) persists across statements.

Usage

frostlake()

Value

A FrostlakeDriver object for dbConnect.

Examples

drv <- frostlake()
drv

# The engine types dbWriteTable() creates columns with, for each R type
DBI::dbDataType(drv, data.frame(id = 1:2, name = c("a", "b"), day = Sys.Date()))

## Not run: 
# Everything below needs a running Frostlake DatabaseHttpServer
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"))
dbGetQuery(con, "SELECT id, name FROM people WHERE id = ?", params = list(1L))

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

## End(Not run)

DBI methods for Frostlake

Description

Implementations of the standard DBI generics for FrostlakeDriver, FrostlakeConnection and FrostlakeResult. Each follows its generic's contract — see the corresponding DBI help page — with these engine-specific notes:

• dbConnect applies the DSN's database and schema eagerly with USE DATABASE/USE SCHEMA, so an unknown database fails at connect. A plain name travels bare whatever its case and resolves case-insensitively, as on Snowflake; wrap the value in double quotes itself for an exact-case match.

• Every request after the first names the connection's session, and once the engine reports newSession (0.1.0 and later do) it also sends requireSession, so a session the engine no longer holds is refused instead of being replaced by a fresh one. The statement did not run: when the lost session held nothing of its own, a fresh session takes the connection's database and schema and the statement is sent once more; when it held a transaction or context (USE, SET, ALTER SESSION, a temporary object), that raises frostlake_session_lost, a subclass of frostlake_error, and the next statement starts afresh on the connection's scope. dbDisconnect releases the session with DELETE /api/sessions/{id}, which rolls back a transaction left open; against an older engine it sends a ROLLBACK instead.

• dbSendQuery/dbGetQuery/dbExecute accept params: unnamed elements fill ? placeholders positionally, named elements fill @name/:name placeholders, all inlined client-side. Placeholders inside string literals, quoted identifiers, $$...$$ bodies and comments are left untouched; :: is always the cast operator. A positional value no ? consumed is an error. With placeholders and no params, execution waits for dbBind, which may carry equal-length vectors and runs the statement once per index. multi_statement_count declares how many statements the text carries (0 for any number) for engines that enforce the session's statement count; queries materialize the first result set and affected-row counts sum across all of them. A Snowflake Scripting block (text opening with DECLARE, or with a BEGIN that starts a block) owns its ? placeholders, which reach the engine untouched. An empty statement is sent too, for the engine to refuse. A refusal the engine reports raises frostlake_sql_error, a subclass of frostlake_error.

• dbExecute and dbGetRowsAffected use the affected-row count engines from 0.1.0 report per result. Against an older engine they sum every “number of rows ...” column of a single-row result — the rule the engine's JDBC driver applies — so UPDATE (which always answers a second “multi-joined” column) and MERGE (one column per action) report their true counts. DDL and SELECT report 0.

• Result columns map by declared type and actual values: integral NUMBER \to integer, widening to double and then to the exact digit string when a value does not fit; scaled NUMBER and FLOAT \to double; BOOLEAN \to logical; DATE \to Date; TIMESTAMP* \to POSIXct labelled UTC (a trailing wire offset is honoured, so the instant never depends on the client machine's timezone; a column with a fraction finer than a microsecond stays character); TIME \to difftime; BINARY \to a list of raw vectors; everything else, VARIANT/OBJECT/ARRAY included, arrives as character.

• dbWriteTable creates columns via dbDataType (override with field.types) and inserts multi-row VALUES batches; column names are quoted, so they round-trip through dbReadTable exactly.

Value

Each method returns what its DBI generic specifies:

• dbConnect: a FrostlakeConnection; dbSendQuery (and so dbSendStatement): a FrostlakeResult.

• dbFetch, dbReadTable and dbColumnInfo: a data frame. The column information has one row per result column, with name, type, precision, scale, length and nullable.

• dbGetRowsAffected: the number of rows the statement changed, 0 for a query or DDL; dbGetRowCount: the number of rows fetched so far; dbGetStatement: the statement text.

• dbListTables and dbListFields: a character vector; dbDataType: the engine type names as a character vector, named by column for a data frame.

• dbIsValid, dbHasCompleted and dbExistsTable: a single logical.

• dbQuoteIdentifier, dbQuoteString and dbQuoteLiteral: an SQL object.

• dbGetInfo: a named list — the driver and client versions for the driver; db.version, dbname, username, host and port for a connection.

• dbAppendTable: the number of rows written, invisibly; dbBind: the result, invisibly.

• dbDisconnect, dbUnloadDriver, dbBegin, dbCommit, dbRollback, dbClearResult, dbWriteTable and dbRemoveTable: TRUE, invisibly.

• show: prints a one-line description and returns NULL, invisibly.

See Also

frostlake for connecting.

Examples

# Type mapping needs no server
DBI::dbDataType(frostlake(), data.frame(flag = TRUE, n = 1L, x = 2.5, s = "x"))

## Not run: 
# Everything below needs a running Frostlake DatabaseHttpServer
con <- DBI::dbConnect(frostlake(), url = "frostlake://localhost:18082/MY_DB")
res <- DBI::dbSendQuery(con, "SELECT 1 AS one")
DBI::dbColumnInfo(res)
DBI::dbFetch(res)
DBI::dbClearResult(res)
DBI::dbDisconnect(con)

## End(Not run)