initExtension function

Add useful extension functions

Several extension functions are included in the RSQLite package. When enabled via initExtension(), these extension functions can be used in SQL queries. Extensions must be enabled separately for each connection.

initExtension(db, extension = c("math", "regexp", "series", "csv", "uuid"))

Arguments

• db: A SQLiteConnection object to load these extensions into.
• extension: The extension to load.

Details

The "math" extension functions are written by Liam Healy and made available through the SQLite website (https://www.sqlite.org/contrib). This package contains a slightly modified version of the original code. See the section "Available functions in the math extension" for details.

The "regexp" extension provides a regular-expression matcher for POSIX extended regular expressions, as available through the SQLite source code repository (https://sqlite.org/src/file?filename=ext/misc/regexp.c). SQLite will then implement the A regexp B operator, where A is the string to be matched and B is the regular expression.

The "series" extension loads the table-valued function generate_series(), as available through the SQLite source code repository (https://sqlite.org/src/file?filename=ext/misc/series.c).

The "csv" extension loads the function csv() that can be used to create virtual tables, as available through the SQLite source code repository (https://sqlite.org/src/file?filename=ext/misc/csv.c).

The "uuid" extension loads the functions uuid(), uuid_str(X) and uuid_blob(X) that can be used to create universally unique identifiers, as available through the SQLite source code repository (https://sqlite.org/src/file?filename=ext/misc/uuid.c).

Available functions in the math extension

• Math functions: acos, acosh, asin, asinh, atan, atan2, atanh, atn2, ceil, cos, cosh, cot, coth, degrees, difference, exp, floor, log, log10, pi, power, radians, sign, sin, sinh, sqrt, square, tan, tanh
• String functions: charindex, leftstr, ltrim, padc, padl, padr, proper, replace, replicate, reverse, rightstr, rtrim, strfilter, trim
• Aggregate functions: stdev, variance, mode, median, lower_quartile, upper_quartile

Examples

library(DBI)
db <- RSQLite::datasetsDb()

# math
RSQLite::initExtension(db)
dbGetQuery(db, "SELECT stdev(mpg) FROM mtcars")
sd(mtcars$mpg)

# regexp
RSQLite::initExtension(db, "regexp")
dbGetQuery(db, "SELECT * FROM mtcars WHERE carb REGEXP '[12]'")

# series
RSQLite::initExtension(db, "series")
dbGetQuery(db, "SELECT value FROM generate_series(0, 20, 5);")

dbDisconnect(db)

# csv
db <- dbConnect(RSQLite::SQLite())
RSQLite::initExtension(db, "csv")
# use the filename argument to mount CSV files from disk
sql <- paste0(
  "CREATE VIRTUAL TABLE tbl USING ",
  "csv(data='1,2', schema='CREATE TABLE x(a INT, b INT)')"
)
dbExecute(db, sql)
dbGetQuery(db, "SELECT * FROM tbl")

# uuid
db <- dbConnect(RSQLite::SQLite())
RSQLite::initExtension(db, "uuid")
dbGetQuery(db, "SELECT uuid();")
dbDisconnect(db)