SQL generics
The main reason I think it would be useful is that currently every implementation has to implement something like sqlCreateTable and sqlInsertInto themselves. It's actually slightly difficult to get correct (because of escaping rules etc), so currently I think many of the implementations fail under different circumstances. Hadley
On Wed, Oct 23, 2013 at 7:31 AM, David James <daj025 at gmail.com> wrote:
Hi, I can see an SQL generation tool as a handy thing to have, but probably best implemented in a separate package. Regards, David On Tue, Oct 22, 2013 at 1:59 PM, Hadley Wickham <h.wickham at gmail.com> wrote:
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/
_______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB at r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-db
Chief Scientist, RStudio http://had.co.nz/