As I continue to upgrade ConsulTracker and work on my new (not-yet announced) project, I often find myself wanting a nice CRUD-like interface, something akin to what I’ve heard Django has. While less masochistic developers might go for the “real deal” of Django, I chose to begin adding admin-style CRUD pages for TurboGears, the Python web framework with which I’m most familiar.
Now, TurboGears already has something called “FastData”, but I’ve found it somewhat limiting. What I started with, then, is a set of requirements:
There should be a (HTML) table view of the (SQL) table which shows all rows of the (SQL) table.
- The columns shown for each table must be customizable with a minimum of custom code.
- When a column contains a foreign key, something “useful” must show up in that column (e.g. not the numeric “id” column of the foreign table)
- The table should have the capability of displaying columns from foreign-key’d tables as native columns. (This is kind of like creating an SQL view containing columns from multiple tables.)
- The table should have a link for editing each row, a link for marking a row for deletion, and a button for actually deleting the marked items. This is really a pet peeve of mine. GET-style links should not modify data in a well-designed web application. That can lead to all sorts of nastiness, especially when dealing with web spiders that follow all links. So I’ll keep all the data-modifying stuff in buttons.
From these requirements, I determined that I needed some kind of information, call it “CRUD metadata” or crudmeta for short, which contained the following information for each SQLObject model I want to view:
- The fields to include in the table (note that these can be sub-fields, as in “address.street1”, etc.)
- The “name” of each row in the table—this is for displaying the row as a “foreign key” in another table.
There should be an “edit” view which allows for the following functionality:
- Each SQLObject property refrenced in “crudmeta” should have a place on the form.
- The fields in the form should make sense for their data type. This means:
- TextFields for StringCol, IntCol, FloatCol, etc. (with data validation)
- CalendarDate[Time]Picker for Date[Time]Col
- Select fields for ForeignKey – populated with the “name” from crudmeta, of course!
- Tables for MultiJoin fields with the similar functionality as the “Table View” above.
- AJAX-y goodness to allow you to “drill down” in the object hierarchy without losing your context.
Note that some (most) of these fields have TurboGears 0.9 widgets-style names. Not a coincidince. I want to reuse as much as possible from existing TurboGears development so as to avoid “re-inventing the wheel.”
Status & next steps
As of today (2006–02-27), the basic functionality is there. It’s not pretty, it doesn’t handle errors well, it needs refactoring badly, and it mixes model and view a bit from MVC, but it exists. My current plan is to clean it up a bit this week and (hopefully) make it available by next weekend. To whet your appetite, here is an (abbreviated) model from my new application with the CRUD annotations present. (Currently, this is all the custom code you need in order to create the above-described interface.)
‘address.city’, ‘address.state’, ‘address.zip’,
(‘distributionCenter’, ‘Distribution Center’),
name=StringCol(default=uniqueValue(‘Store’, ‘name’, ‘store’),