[R-sig-DB] SQL generics

[Hadley Wickham]

Hi all,

I wonder if it might be useful to start developing a new set of
generics for SQL generation. A reasonable target would be the subset
of SQL-92 grammar defined for minimal ODBC compliance:
http://msdn.microsoft.com/en-us/library/ms711725(v=vs.85).aspx

That would lead to the following functions which dispatch on the
connection and and return a SQL vector as output (possibly of length >
1). Default methods would be SQL-92 compatible, and provide a
reference implementation for package authors to see how to best deal
with escaping.

* `sqlCreateTable(conn, table, fields, ...)`
* `sqlDeleteFrom(conn, table, where, ...)`
* `sqlDropTable(conn, table, ...)`
* `sqlInsertInto(conn, table, values, ...)`
* `sqlUpdate(conn, table, values, ...)`

Note that I have deliberately not included `sqlSelect()` in this list,
because the grammar of the select statement is so much more
complicated in general, and varies considerably more between
databases. I'd suggest not including it in a first pass.

With `dbSendQuery()`, these could also be used to provide default
implementations of `dbWriteTable()`, and `dbDropTable()`.
Implementators would almost always override `dbWriteTable()` to
provide a more efficient implementation for bulk data loading.

What do you think?

Hadley

-- 
Chief Scientist, RStudio
http://had.co.nz/