View source: R/initExtension.R
initExtension | R Documentation |
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"))
db |
A |
extension |
The extension to load. |
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).
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
charindex, leftstr, ltrim, padc, padl, padr, proper, replace, replicate, reverse, rightstr, rtrim, strfilter, trim
stdev, variance, mode, median, lower_quartile, upper_quartile
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")
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.