[R-sig-DB] SQL generics

[Hadley Wickham]

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 using 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 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
>
>



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