Highlighting code lines in evaluated SQL-expressions

[thohan88]

I find highlighting lines to be a really useful feature of xaringan, but discovered that it's currently not working for evaluated SQL-expressions. Asterix (* ) appears to work in non-evaluated expressions, but #<< does not. Both throw an error in evaluated SQL-expressions.

Example Rmd-file:

---
output:
  xaringan::moon_reader:
    lib_dir: libs
    nature:
      highlightStyle: github
      highlightLines: true
      countIncrementalSlides: false
---

```{r include=FALSE}
library(DBI)
con <- dbConnect(RSQLite::SQLite(), dbname = ":memory:")
foo <- dbExecute(con, "DROP TABLE IF EXISTS packages")
foo <- dbExecute(con, "CREATE TABLE packages (id INTEGER, name TEXT)")
foo <- dbExecute(con, "INSERT INTO packages VALUES (1, 'readr'), (2, 'readxl'), (3, 'haven')")
```

## Works - but expression is not evaluated
```sql
* SELECT * FROM packages
```

## Does not work
```sql
SELECT * FROM packages #<<
```

## Throws an error
```{sql, connection = con}
* SELECT * FROM packages
```

## Throws an error
```{sql, connection = con}
SELECT * FROM packages #<<
```

Session info

sessioninfo::session_info()
#> - Session info ---------------------------------------------------------------
#>  setting  value                       
#>  version  R version 4.0.2 (2020-06-22)
#>  os       Windows 10 x64              
#>  system   x86_64, mingw32             
#>  ui       RTerm                       
#>  language (EN)                        
#>  collate  Norwegian Bokmål_Norway.1252
#>  ctype    Norwegian Bokmål_Norway.1252
#>  tz       Europe/Paris                
#>  date     2021-04-22                  
#> 
#> - Packages -------------------------------------------------------------------
#>  package     * version date       lib source        
#>  backports     1.1.7   2020-05-13 [1] CRAN (R 4.0.0)
#>  bit           4.0.4   2020-08-04 [1] CRAN (R 4.0.2)
#>  bit64         4.0.2   2020-07-30 [1] CRAN (R 4.0.2)
#>  blob          1.2.1   2020-01-20 [1] CRAN (R 4.0.2)
#>  cli           2.4.0   2021-04-05 [1] CRAN (R 4.0.5)
#>  crayon        1.3.4   2017-09-16 [1] CRAN (R 4.0.2)
#>  DBI         * 1.1.0   2019-12-15 [1] CRAN (R 4.0.2)
#>  digest        0.6.25  2020-02-23 [1] CRAN (R 4.0.2)
#>  ellipsis      0.3.1   2020-05-15 [1] CRAN (R 4.0.2)
#>  evaluate      0.14    2019-05-28 [1] CRAN (R 4.0.2)
#>  fs            1.5.0   2020-07-31 [1] CRAN (R 4.0.2)
#>  glue          1.4.2   2020-08-27 [1] CRAN (R 4.0.4)
#>  highr         0.8     2019-03-20 [1] CRAN (R 4.0.2)
#>  htmltools     0.5.1.1 2021-01-22 [1] CRAN (R 4.0.3)
#>  knitr         1.32    2021-04-14 [1] CRAN (R 4.0.5)
#>  lifecycle     0.2.0   2020-03-06 [1] CRAN (R 4.0.2)
#>  magrittr      1.5     2014-11-22 [1] CRAN (R 4.0.2)
#>  memoise       1.1.0   2017-04-21 [1] CRAN (R 4.0.2)
#>  pillar        1.4.6   2020-07-10 [1] CRAN (R 4.0.2)
#>  pkgconfig     2.0.3   2019-09-22 [1] CRAN (R 4.0.2)
#>  purrr         0.3.4   2020-04-17 [1] CRAN (R 4.0.2)
#>  Rcpp          1.0.5   2020-07-06 [1] CRAN (R 4.0.2)
#>  reprex        2.0.0   2021-04-02 [1] CRAN (R 4.0.5)
#>  rlang         0.4.10  2020-12-30 [1] CRAN (R 4.0.3)
#>  rmarkdown     2.7     2021-02-19 [1] CRAN (R 4.0.5)
#>  RSQLite       2.2.0   2020-01-07 [1] CRAN (R 4.0.2)
#>  rstudioapi    0.11    2020-02-07 [1] CRAN (R 4.0.2)
#>  sessioninfo   1.1.1   2018-11-05 [1] CRAN (R 4.0.2)
#>  stringi       1.4.6   2020-02-17 [1] CRAN (R 4.0.0)
#>  stringr       1.4.0   2019-02-10 [1] CRAN (R 4.0.2)
#>  styler        1.3.2   2020-02-23 [1] CRAN (R 4.0.3)
#>  tibble        3.0.3   2020-07-10 [1] CRAN (R 4.0.2)
#>  vctrs         0.3.2   2020-07-15 [1] CRAN (R 4.0.2)
#>  withr         2.4.1   2021-01-26 [1] CRAN (R 4.0.3)
#>  xfun          0.22    2021-03-11 [1] CRAN (R 4.0.5)
#>  yaml          2.2.1   2020-02-01 [1] CRAN (R 4.0.2)

Supporting this may not be high priority, but thought I would report it since I already created another SQL-related issue (#307).

Once again, thank you for all your great work.

[cderv]

I think this highlighting feature from remark is tricky. Let me explain :

In remarkjs this feature is made to highlight some code block in mardkown. When using .md as remarkjs expect this code block won't be executed - so it does not matter if this is valid code for the language or not.
That is why having a * (1st example ☝️) works when you use a non evaluated sql chunk, because it is directly remarkjs support (see https://github.com/gnab/remark/wiki/Configuration#highlighting)

xaringan will add support for {{ ... }} and ... #<< for knitr code chunk. These are replaced by * in source chunk for remark to work. That is why using #<< not in a knitr chunk does not work. (2nd example ☝️ ). This two syntax options have been chosen because it is valid R code, so adding those in the chunk is not creating issue with parsing and execution.

This is a bit shown in https://slides.yihui.org/xaringan/#29

About the third example, I think this is not working because {{ ... }}or# <<or*` are not valid SQL code. So parsing will fail hence the error.

So yes for now it won't work with SQL chunk, and any other engine where thos syntax are not valid code. This is because the tokens are not remove from the code before execution.

Maybe we could add a highlight.input chunk option to mimic highlight.output chunk option to pass a numeric vector to highlight in all kind of engine ?

Other solution would be to find another token supported by SQL.

[gadenbuie]

Maybe we could add a highlight.input chunk option to mimic highlight.output chunk option to pass a numeric vector to highlight in all kind of engine ?

I like this idea and was thinking the same thing, although I would propose we call it highlight.source.

[cderv]

Oh yeah this is a better name :)

It is not as easy as I thought to cover all cases (like collapse = TRUE) but I got a working branch for the SQL example - I'll share it so you have a look