I'm working on a contrib module that will be used as an enterprise report writer for drupal and other external databases and XML feeds. Some of my users are interested in the capability to store ad-hoc queries and then write reports off of them, (kind of like Crystal Reports).
My question is two fold: 1. Do people think its untenable to store ad hoc sql queries either in the drupal site files structure, or in the db? Right now my module assumes that all SQL that will be used for reporting is stored on the file system. I did this because for most enterprise reporting systems I feel it would be advantageous to be able to version control the SQL that is used in these reports. But ad hoc reporting really means writing your own queries, right? So do you think it's wrong to store them on file system or in db? Is it irresponsible to provide such a feature? 2. The connection string (with passwords). Right now I have the site admins hack in connection strings for external databases into settings.php in the sites folder. That probably won't work for a true ad hoc reporting solution, since we may want users to be able to connect to databases from the UI? Is it untenable to store these using variable_set? Are there any ideas for securing them inside the drupal DB? I realize that there will be many opinions on this topic, but I really want to get a feel for the opinions and the why of seasoned developers. Opinions that talk about risk trade offs would be much more welcome than simple, "no you should never do that" statements. More info on the project can be found at: http://drupal.org/project/forena Thanks for any input you all are willing to provide. Dave
