Add support for executing SQL chunks

[JakobGM]

Hi!

I have been digging through the documentation in order to understand how to use R.nvim to execute SQL chunks, without success. Is this something you already support or would consider adding support for (I might have missed something here).

My motivation is that I love expressing the initial data transformation in the form of SQL chunks, which are then post-processed in the tidyverse ecosystem before being plotted in ggplot. It looks something like this:

```{r}
library(DBI)
db = dbConnect(RSQLite::SQLite(), dbname = "sql.sqlite")
```

```{sql, output.var="trials", connection=db}
SELECT * FROM trials
```

```{r}
trials %>% as_tibble() %>% ggplot(...) + ...

Thanks in advance!

• Jakob

PS: Thanks for the great plugin! I used Nvim-R extensively during 2018 and 2019, and I'm happy to return to this ecosystem seeing that things have been keeping up to date with Neovim's migration to lua-based plugins 🙇

[PMassicotte]

It is not implemented. I am not sure how the engine does that (parsing the SQL string and turn in an R expression and send it with dbSendQuery()?

[JakobGM]

It is not implemented. I am not sure how the engine does that (parsing the SQL string and turn in an R expression and send it with dbSendQuery()?

I found the definition of the SQL engine in the knitr repo here. Based on this line it seems like you are correct, it does indeed use DBI::dbSendQuery()! There are a of bells and whistles in the knitr implementation, like auto-detection of UPDATE statements, but personally I would mainly be grateful for SELECT support, i.e. the restricted functionality described in the documentation.

[PMassicotte]

That should not be that difficult to implement. I will give it a try this weekend when I have some time :)

[JakobGM]

That should not be that difficult to implement. I will give it a try this weekend when I have some time :)

That is very kind of you 🙇 It goes without saying, but don't feel bad if you end up not having the time or energy to look into it!

PS: FYI, I'm going on a three week holiday on Saturday, so I might not be able to thoroughly test the feature before April.