Default SQL data type for character values

[krlmlr]

https://github.com/rstats-db/DBI/pull/97/files/c4a8b60bf4b9d4661838af135a24c7f31590ddd5#r61682461

What is the most sensible default here?

• VARCHAR(n) depends on data
• TEXT may mean overhead by storing all character values in external BLOBS

CC @hadley @hannesmuehleisen

[hadley]

I'm pretty sure the default should be TEXT

[beanumber]

SQLite doesn't support VARCHAR(n), but it will gloss over it. So why not default to VARCHAR(n) since that will work in both MySQL and PostgreSQL?

[hannes]

OK I think DBI should choose CLOB for characters, because SQL 2008 says so (ISO/IEC 9075-2:2008(E), Section 6.1)

[krlmlr]

@hannesmuehleisen: How many DBMS actually support this? MySQL and PostgreSQL don't. (There seems to be a newer standard (2011), I doubt that much has changed there.)

TEXT is supported by SQLite, MySQL, and PostgreSQL, but not by SQL Server. I'll play it safe and keep it simple here -- return "TEXT".

[jimhester]

I don't think TEXT is playing it safe, It is not part of SQL-92 standard, is deprecated in SQL-Server and not supported in Oracle databases either.

If you are worried about new data not fitting into a VARCHAR(n) column, you could use something like the following, which would make the field width one power of two greater than it takes to hold the current data.

varchar_data_type <- function(x) {
  size <- 2L^(floor(log2(max(nchar(as.character(x))))) + 1L)
  paste0("VARCHAR(", size, ")")
}
varchar_data_type("t")
#> [1] "VARCHAR(2)"
varchar_data_type("te")
#> [1] "VARCHAR(4)"
varchar_data_type("test")
#> [1] "VARCHAR(8)"

[krlmlr]

@jimhester: This is only the fallback, DBI drivers are free to override. I think it will be difficult to find a solution that works in all cases, so TEXT may be just as good as any other option. What am I missing?

[jimhester]

I agree it is difficult to find a good default, but I think the default should at least be part of the SQL 92 specification, this is explicitly mentioned in ?dbDataType.

The default method determines the SQL type of an R object according to the SQL 92 specification, which may serve as a starting point for driver implementations.

TEXT is not part of the standard and not implemented in a number of widely used databases.

Probably the most compatible solution would be just to use VARCHAR(255), this is very widely compatible, just about every database should support it and SQLite even translates it explicitly to its TEXT data type.

I agree this default is not terribly important because drivers can define their own method, but TEXT does not seem like a proper default.