editing mysql tables in gnumeric, or excel, or openoffice, or sqlite, or emacs, ...

I've been working on a diff/patch/scm toolchain for data (coopy). It shouldn't be so much fun, but it is. One thing I'm enjoying right now is finally being able to edit data where I want to edit it. For example, in the past when I wanted to make small fixes to a mysql table, I'd fire up a mysql-specific GUI. No longer. Now I edit the mysql table as a spreadsheet or as text. For example, here we convert a table called "legal_structures" in database "dcp_development" into a spreadsheet called "legal.xls":

ssformat dbi:mysql:dcp_development:username=root:table=legal_structures legal.xls

Now we open legal.xls in gnumeric (or any spreadsheet program).

Maybe we reformat a little to get a good view:

Now, I make a so, so vital change to the capitalization of Non-profit:

That's better. Now to send the change to mysql. First, a sanity check, to make sure I've changed what I thought I did, and nothing else:

ssdiff dbi:mysql:dcp_development:username=root:table=legal_structures legal.xls

This reports:

# tdiff version 0.3
@@@ legal_structures
= |id=7|name=Non-profit Corporation->Non-Profit Corporation|

Good, that's it. I'm getting tired of typing that big dbi: reference to mysql, so let's take a second to make a reference file for it, say legal_mysql.json:

{
"type": "mysql",
"database": "dcp_development",
"username": "root",
"table": "legal_structures"
}

Now I send the change to mysql:

ssdiff --apply legal_mysql.json legal.xls

Done! Just to be sure, let's see if the change got made:

ssformat legal_mysql.json | grep Non

This reports:

7,"Non-Profit Corporation",0,"2010-03-01 16:36:34","2010-03-01 16:36:34"

Good, that has the right capitalization.

Now, time goes on, and changes happen in the database from some other source. I want to edit the table in gnumeric again. I can avoid having to redo the formatting I did (bold header, column widths) if I pull in the changes as diffs, rather than regenerating the spreadsheet:

ssdiff --apply legal.xls legal_mysql.json

This makes me ridiculously happy. And ssformat/ssdiff support .csv files (or a custom .csvs text format for sets of tables), so I can use a text editor when that is appropriate. This is the most comfortable way I've found to edit mysql, sqlite, and Excel tables in emacs.

The interface is definitely a bit geeky though. I look forward to improving that.