Proposal for an SQL Database Editor

Daniel S. Wilkerson; 24 August 2005

Problem

The problem in general is that a team wants a bug-tracking database, however no one wants to write the application because no one can agree on how the UI should work etc. I for one don't like a web UI, but we want it to be networked, etc. You end up with general one-size-fits-all solutions that don't fit anyone well at all.

Solution

However, a team generally can agree on a basic schema for the information that is important to them; therefore it is straightforward to just set up an SQL database with that schema. Everyone who wants to use the database simply writes their own view onto the database for how they want to see the data. We then allow viewing and editing this database in a way very similar to the way the filesystem is viewed and edited in emacs. This is simple and I think would be a great "80% solution" to the bug-tracking database problem. In particular we get full integration with the editing process, something I have never seen in any bug tracking system.

Design

The user experience is that we have an editor for editing documents like emacs but we can also edit views onto an SQL database. The following basic modes are required:

Table mode: The basic UI is that a buffer in emacs maps to a view. One or maybe two lines of screen are displayed per table row. If an entry is say a large block of text, it is truncated. Think of this view like emacs dired mode.
Row mode: In Table mode select a row by say hitting 'f' or 'enter' (as in dired mode). You are now in a mode that edits an entire row. It should look like emacs rmail mode where there are name/value pairs. A block of text also works fine in this mode.

Each buffer should probably just have a select statement associated with it that you can edit and re-run for various special purpose effects. It would be nice to have some key-binding for copying the name/value pairs on the row that the cursor is on; you can then switch to another buffer, retrieve the associated select statement with another key-binding, paste in the name/value pairs and refresh the view. Thus, if you are on the table of developers, you can pick up name=fred, go to the open bugs view and paste that into the 'where' clause and get all the bugs assigned to fred.

The conflict model is that of CVS: you check in and check out from the database. If a row has changed in the database and also locally, you have to resolve the conflict in some special mode just for doing that; it would probably display three rows: the new database one, yours, and the mixture of the two you want to keep. To allow rapid refresh from the database you perhaps keep both the new row and the database row side by side (you might hide the other by default, but they are both there) and only have to resolve conflicts when you check in instead of immediately upon an update.

Implementation strategies

1 - Write an emacs major mode. Karl says this is hard since emacs is optimized for documents. Note that there is the possibility of using PyMacs instead of elisp.

2 - Write our own editor; Scott says to use Qt. I also want the editor to have a terminal mode so I can use it over the network in a terminal, that is, emacs -nw. The problem is I want to use one editor for everything so it would have to duplicate all the functionality of emacs that we use.

Acknowledgments

Thanks to Karl Chen and Scott McPeak for discussions on this problem and the proposed solution.