update sqlite table from mysql

Here's a quick snippet to copy a mysql table into an sqlite database:

ssformat dbi:mysql:name_of_database:table=name_of_table:username=root sqlite_database.db

If the table already exists in the sqlite database, its schema is left untouched. Otherwise, a rough-and-ready schema is set up. Dbi syntax information here.

ssformat is from the coopy toolbox.

Compare tables online

You can now compare tables online on the COOPY website. Compare CSV files, Excel spreadsheets, Access and Sqlite tables. MySQL tables can be compared by the downloadable tool, but not from the website, which is restricted for security reasons to comparing files.

One use for this service is as follows. Suppose you're working with public data, and have made some improvements to it (fixed typos etc). You'd like to send those changes "upstream" to the source of the data. If you compare the original data and your version with COOPY's table comparison utility, then a page is created for you presenting the differences in a clear way, along with an easy way to apply those differences. Pointing "upstream" to that is more likely to get a better welcome than emailing a big blob of who-knows-what in a format the data may not be maintained in.

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.