Search code examples
rsqliter-markdownrsqlitegenerated-columns

Update SQLite version used by RSQLite

I'm building a database in SQLite using R and Rmarkdown. Both the CRAN and development RSQLite packages are using SQLite version 3.30.1. Recently (1/2020) SQLite released v 3.31.1 which supports the creation of generated columns within tables. This would really "tie the room together". If anyone knows how to update the version used by RSQLite, I'm all ears. I'm running windows 10 and R version 3.5.

Ive downloaded SQLite v3.31.1 from https://www.sqlite.org/download.html. I'm not capable of building the data structures from the command line. I have used SQLite studio, however, its running on SQLite v 3.24.

I found this on https://www.r-project.org/nosvn/pandoc/RSQLite.html

 latest <- "http://sqlite.org/2014/sqlite-amalgamation-3080600.zip"
 tmp <- tempfile()
 download.file(latest, tmp)
 unzip(tmp, exdir = "src/sqlite", junkpaths = TRUE)
 unlink("src/sqlite/shell.c")

I updated the URL : 

 latest <- "https://sqlite.org/2020/sqlite-amalgamation-3310100.zip"

Downloaded fine and threw no errors, but I still get 

 > RSQLite::rsqliteVersion()
   header  library 
   "3.30.1" "3.30.1"
 select sqlite_version();
 3.30.1

Thanks in advance for any help

Solution

  • I have upgraded sqlite to 3.31.1 in the GitHub version, does it work for you?

    # install.packages("devtools")
devtools::install_github("r-dbi/RSQLite")

    Upgrading the sqlite source requires running data-raw/upgrade.R and tweaking regexp.c in the RSQLite source tree.