On Saturday, I participated in Rails Day 2006. It's a 24-hour contest between 1-3 person teams to develop cool Ruby on Rails web apps. I set out to build a visual database query building system (that doesn't require knowledge of SQL) that also allows people to post and share these queries with other users of the system. This would allow people to quickly and easily build queries and share them with others.
The itch I was trying to scratch is that many people in the nationwide network of non-profits I work for use Access to create databases for the data they're working with because they can easily create queries to run on that data. I would rather they put their data in our centralized MySQL server for many (hopefully obvious) reasons.
However, asking them to learn SQL is a bit much, and connecting Access (or OpenOffice.org Base) to MySQL is a bit daunting, especially since there is no way in hades I'm going to open up the MySQL port to the Internet (so they would need to setup a VPN client, an ODBC data source, Access connections to all the tables, etc.).
If I had a web app that just gave them the visual query builder piece, it would be pretty easy. Combine that with the collaborative ability to share the queries, and you've got a big win in terms of empowering people to help themselves and help others work with the database.
Anyway, I built Emerald (as I called the tool) during Rails Day. I don't have it hosted anywhere, unfortunately, so it's not easy to try out. (You could check it out from Rails Day's svn repo and run it locally on your computer, if you really wanted to.) The app, as it stands right now, is not a solution to the problem I set out to solve. If anything, it's proof that a) ActiveRecord (Rails' ORM database access layer) is not a good tool for this sort of job, i.e. generic database access that adapts itself to your schema, not the other way around or b) I don't really know what I'm doing. :)
ActiveRecord proved very tricky to adapt to this purpose. One of the mantras of Rails is "convention over configuration" and usually it's very nice. For example, if you have a table whose name is the plural form of the class you're instantiating (let's say the table is called "people") and the primary key's name is just "id", then your model class looks like this:
class Person < ActiveRecord::Base endThat's it. You now have full ORM access to your "people" table using instances of Person objects. It's very handy.
However, when trying to adapt it to work with existing database schema, the convention was almost never correct, and namespace conflicts abounded. These are possibly not insurmountable problems, but in a 24 hour contest, it caused me to have to spend way too much time adapting the tool to the job. This became rather frustrating by the end of the day.
I was fully aware of the fact that I was trying to use ActiveRecord in a non-standard way, so these difficulties were not entirely unexpected. I wanted to push the boundaries a little of both my knowledge of Ruby (and Rails) and what was possible to coax ActiveRecord into doing. It was a labor of love, as I am a big fan of Ruby, Rails, and ActiveRecord. Overall, it was a very fun experience and I can't wait for next year! (But I'll probably pick a project that's more suited to ActiveRecord's purpose.)
