Hi all -

I've been working on a lite ORM and database abstraction package for R.
Formatting complex queries by hand has always been an error-prone hassle, so
I've tried to do away with that as much as possible, instead, using R
objects to represent elements of a database system (statements, clauses,
operators, functions, fields, tables, etc.). R5 classes have made the
development of this package somewhat easier than it would have been in the
past (in my view, at least).

Then an expression like:
query(some_table$some_field)$where(some_table$some_field != NA &
some_table$some_other_field %in% c(1, 2, 3))
would generate SQL like:
SELECT `database`.`some_table`.`some_field` AS `some_field` WHERE
`database`.`some_table`.`some_field` IS NOT NULL AND
`database`.`some_table`.`some_other_field` IN (1, 2, 3);

The basic features:
- Abstraction of tables and fields to R5 objects and use of R functions
where a clear parallel in SQL exists (e.g. %in% >- IN, unique >- DISTINCT).
- Result proxying (by a data.frame-like object), which allows on-demand
fetching and live updates to results (where appropriate).
- Session management.

It's my feeling that most R users care more about using existing tables than
creating new ones, so introspection is more of a focus than is defining new
relations.

It's reasonably far along, and I've been using it for my own projects for
some time. However, certainly much syntax is not supported, and I've only
written a compiler for MySQL. At this point I'd welcome anyone interesting
in contributing or offering advice. The code is (I think) designed as to
support the addition of more complex logic at any stage (generation,
preparation, compilation, formatting) withouth becoming spaghettized. I've
written a reasonably-sized test suite that covers most of the functionality.

The code is hosted on github - https://github.com/rtirrell/databasr. Feel
free to fork, critique and/or use. I've taken some pains to ensure that
interface will remain relatively stable.

Thanks!

--
Robert Tirrell | rpt@stanford.edu | (607) 437-6532
Program in Biomedical Informatics | Butte Lab | Stanford University

	[[alternative HTML version deleted]]

