[R-sig-DB] SQL generics

[David James]

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 using 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 using r-project.org
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>

	[[alternative HTML version deleted]]