dbColumnInfo() should return SQL Data Types

[krlmlr]

OP #388 by @ankane.

It's a good idea, my current focus is on dbi3, happy to review a PR.

CC @gavril0.

[bpvgoncalves]

Hello.
I gave this one a try by calling SQLite's 'sqlite3_column_decltype()' function and it seems to be working as expected by returning the declared type or 'NA' if no type was declared.

My main concerns are:

• this change may break someone else's code if they are expecting (and checking) that 'dbColumnInfo()' only returns 2 columns;
• since by default SQLite does not enforce an actual SQL data type is declared (or even that a type is declared at all), the returned values might seem nonsense as in the last columns on the example below.

db <- DBI::dbConnect(RSQLite::SQLite(), extended_types = TRUE)

# Prepare example db
DBI::dbExecute(db, "CREATE TABLE tmp (col_int integer,
                                      col_real real,
                                      col_text text,
                                      col_date date,
                                      col_none,
                                      col_dummy foo,
                                      col_test bar)")
#> [1] 0
DBI::dbExecute(db, "INSERT INTO tmp VALUES (1, 8.8, 'a', '2025-05-23', 'b', 3.5, TRUE)")
#> [1] 1

# Check types
res <- DBI::dbSendQuery(db, "SELECT * FROM tmp")
print(DBI::dbColumnInfo(res))
#>        name      type .declared_type
#> 1   col_int   integer        INTEGER
#> 2  col_real    double           REAL
#> 3  col_text character           TEXT
#> 4  col_date      Date           date
#> 5  col_none character             NA
#> 6 col_dummy    double            foo
#> 7  col_test   integer            bar

# Cleanup
DBI::dbClearResult(res)
DBI::dbDisconnect(db)
rm(res, db)

^{Created on 2025-05-23 with reprex v2.1.1}

I've done a local devtools::check() and it is passing with only 1 NOTE (unrelated to the changes).

── R CMD check results ───────────────────────────────────────────────────────────── RSQLite 2.3.11.9001 ────
Duration: 3m 46.2s

❯ checking for future file timestamps ... NOTE
unable to verify current time

0 errors ✔ | 0 warnings ✔ | 1 note ✖

R CMD check succeeded

If this is still a desired feature and you are happy with the output, I'm more than happy to open a PR.