Execute multiple SQL statements (read from file) in R using DBI and RSQLite

Question

I have the following example code:

library(DBI)
library(RSQLite)

conn <- DBI::dbConnect(RSQLite::SQLite(), "test.sqlite" )

########
# ## This is the original code when reading from the file which is 
# ## for convenience commented out here.
# sql <- readLines("test.sql")
# sql <- paste0(sql, collapse = "  ")
########

sql <- "CREATE TABLE `bemovi_mag_25__mean_density_per_ml` ( 
   `timestamp` NUMERIC, 
   `date` NUMERIC, 
   `species` NUMERIC, 
   `composition_id` NUMERIC, 
   `bottle` NUMERIC, 
   `temperature_treatment` NUMERIC, 
   `magnification` NUMERIC, 
   `sample` NUMERIC, 
   `density` NUMERIC 
 );  CREATE INDEX idx_bemovi_mag_25__mean_density_per_ml_timetamp on bemovi_mag_25__mean_density_per_ml(timestamp); 
 CREATE INDEX idx_bemovi_mag_25__mean_density_per_ml_bottle on bemovi_mag_25__mean_density_per_ml(bottle); 
 CREATE INDEX idx_bemovi_mag_25__mean_density_per_ml_timestamp_bottle on bemovi_mag_25__mean_density_per_ml(timestamp, bottle);"

DBI::dbExecute(conn, sql)
DBI::dbDisconnect(conn)

When running the DBI::dbExecute(), I get the following warning:

> DBI::dbExecute(conn, sql)
[1] 0
Warning message:
Ignoring remaining part of query: CREATE INDEX idx_bemovi_mag_25__mean_density_per_ml_timetamp on bemovi_mag_25__mean_density_per_ml(timestamp); 
 CREATE INDEX idx_bemovi_mag_25__mean_density_per_ml_bottle on bemovi_mag_25__mean_density_per_ml(bottle); 
 CREATE INDEX idx_bemovi_mag_25__mean_density_per_ml_timestamp_bottle on bemovi_mag_25__mean_density_per_ml(timestamp, bottle); 

I understand why this is happening (because of the ;), but my question is:

Is there a DBI command I am missing which will execute the sql code correctly, or do I have to split the sql statement as follows:

sql <- strsplit(sql, ";")
lapply(
  sql[[1]],
  function(s) {DBI::dbExecute(conn, s)}
)

Answer 1

Unfortunately, this is an ongoing Issue with DBI and RSQlite: https://github.com/r-dbi/RSQLite/issues/313

For now you probably should use your workaround.