tail -f findings.out

I’ve found that I often create several views that are variations on an initial one, providing a slightly different convenient slice of the data as the need arises. But when a view is created the syntax actually stored can be quite different than what you put in. The parser reorganizes and rewrites to make everything explicit. While better for performance in running the view, this has the unfortunate side effect of transmuting a once readable statement into a forest of parentheses and backticks that can and does induce tears.

First an example view that I created using the handy world database:

This is a view of the 10 cites with the lowest population in the Central Luzon region of Asia. Now let’s see what actually got stored. To get a view’s create statement you can use the MySQL Query Browser and right click the view name shown in the right under Schemata, selecting “Copy SQL to Clipboard”. Or you can run a query like:

Here’s the result for the view above:

I added the line breaks for a modicum of readablilty, the statement is stored without them. I sure don’t want to edit that version in making a variant! This example isn’t actually too bad, but for real world complicated views, it really is quite difficult to read views exported in this way. I often try to store the original create view query just to avoid this pain. But sometimes I only have the stored syntax.

I’ve created a Bash function to make this whole process less painful:

Now to get a cleaned up view you can call this function followed by the database and view names:

To get the create statements for multiple views you can use a loop. For example:

If you need some more flexibility than this I implemented it in Python after an initial fit of rage caused by trying to get various levels of nested strings properly escaped in Bash. That ended up being overly long and unnecessary, and a cooler head prevailed. But the script is here on GitHub if you want it. It creates the same file although without using sed and with some error handling.

The MySQLdb Python module implements the Python DB API for MySQL. I’ve written about its use before. MySQL issues warning messages in a number of circumstances and PEP 249 (which specifies the Python DB API) describes a Warning error message to be included.

One issue I ran into recently was how to catch warnings thrown by this module when running queries. Oftentimes tutorials or forum discussions that cover warnings in the context of MySQLdb describe how to filter them (they can clog up script output). But in a recent case I wanted to grab and check the warning, logging a dependent result. I had hoped this clean implementation would work in a method used for all calls to the MySQL DB:

But the warnings just went right through. Instead I needed the warnings module’s assistance:

This catches the warnings and raises them as errors, although their class is still correct, allowing a clean implementation to call the above code (wrapped into a method called do_query):

Today I needed to alter the content of a comment made on a Trac ticket. I didn’t anticipate this would be a difficult task, but I wasn’t too familiar with Trac’s DB schema. So to save you a few consternated queries, here’s the spoiler. Comments on tickets aren’t kept within their own table (as you might expect) but are instead stored in the ticket_change table.

In my particular case the Trac DB was MySQL, so the examples I’ll provide will be MySQL queries. If you have an SQLite-backed Trac the operations should be the same, you’ll just need to use SQLite’s syntax.

Say the ticket with the naughty comment is #1234. To see all comments on this ticket:

And to update a comment:

Happy revisionizing.

MySQL’s “order by” keyword allows the order in which result rows are returned to be defined. This is quite useful in a wide variety of circumstances. You can specify any number of columns whose values you want to order the results by, as well as what sorting order to use (desc or asc; asc is default). However, you may also run into situations in which you want certain groups within the resultset displayed in a certain order based on particular values in the results, not just according to alphanumeric sorting. This is easy to achieve by use of the equal operator or the field keyword within the order by clause.

Some examples are in order. I’ll be using the world database. The following shows country names and a language spoken therein for European countries that speak Arabic, Dutch, and/or Greek:

This returns:

But what if we want Dutch speaking countries first? We can’t use order by Language desc or asc since Dutch results are in the middle of either sorting. Instead:

This orders the results with records having Language = “Dutch” first, followed by all the others ordered by their Language value. And this is the desired result:

You can specify as many values in the order by clause as you want, each also allowing an asc or desc modifier. If you have a number of values in one field you want to specify, the field keyword can save you some typing:

Returning the customized ordering:

Sometimes you just want a database to run some queries against. Any reasonable set of tables with data pertaining to something not terribly complicated will do. Perhaps you have an urge to tease out the intricacies of correlated subqueries, or you need to test some complicated pattern matching functions. MySQL provides a database dump for such recreation, called “world”. The world database contains global population data from Statistics Finland. The data itself is several years old, but it works fine as general test fodder.

Since I normally only use it on my own machines for testing, I’ve found setting it up to be a fixed process. So I created this alias for such situations:

This pulls down the world database dump, unzips it, creates a DB to hold it, imports it, then removes the temporary files. You’ll be prompted twice for the password of the root MySQL user. You could also alter it to include the password if you are so inclined. Add the above alias line to ~/.bashrc, reload it, and you can have a test DB up and waiting within seconds anytime you want.