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 | r...@stanford.edu | (607) 437-6532 Program in Biomedical Informatics | Butte Lab | Stanford University [[alternative HTML version deleted]] ______________________________________________ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
