How to dump SQL for all views in a mysql database

I often generate a fair number of views while developing reporting SQL, and have off an on looked for a way to easily save their source code to a file. mysqldump doesn't directly support extracting only views, but with a little command line trickery and a query against INFORMATION_SCHEMA you can make it do the right thing:

mysql -u username INFORMATION_SCHEMA
      --skip-column-names --batch
      -e "select table_name from tables where table_type = 'VIEW'
          and table_schema = 'database'"
      | xargs mysqldump -u username database
      > views.sql

The skip-column-names and batch options produce output with just one view name per line, which is what xargs needs as input. Be sure to replace both occurences of username and database with appropriate values, and add -h for remote hosts and -p if the user requires a password. Here's a one-line example for user root with no password on localhost, with a database named "foo":

mysql -u root INFORMATION_SCHEMA --skip-column-names --batch -e "select table_name from tables where table_type = 'VIEW' and table_schema = 'foo'" | xargs mysqldump -u root foo > views.sql

transpose 2-dimensional Javascript array in one line

There are quite a few libraries that implement a transpose function for arrays, but sometimes you don't want the hassle of installing them or the added dependencies. I'm sure I'm not the first to use this approach but most snippets found by a quick search are too verbose to comfortably drop in for a single use.
Update April 2014: It occurred to me the other day that the magic mentioned below is unneeded, as map already provides the column indexes as the second argument. I've updated the gist so the following remark is no longer relevant. The most magical bit here is to use Object.keys() on the first row of the array which yields an array of column indexes, which we can then use map() on to extract each column in turn. Love to see a terser implementation if you've found one.

Reading Excel files into SSIS on a 64 bit OS

Update: Daniel Esser noted the same solution in his post SSIS Goodie #1: Excel + SSIS + 64 bit = DTS_E_OLEDB_EXCEL_NOT_SUPPORTED ? noting a classic code suck for why the Excel Source doesn't work:

It seems that Microsoft implemented an hard if-statement which throws an exception if DTS-Engine runs in 64 bit environment

SSIS has a built in "Excel Source" for reading data out of Excel files, but it has a variety of problems in a 64-bit environment (such as Windows Server 2008), the main one being that the source relies on the Excel Connection Manager, which in turn relies on the old JET engine OLE DB driver, which doesn't run in 64-bit mode. Googling for something like ssis excel 64 bit will give you lots of results and a variety of advice, mostly falling into these three flavors:

• run the package in 32-bit mode
• use the newer ACE OLE DB driver (released with Office 2010), and
• create an SSIS package using the SQL Server Import Wizard (that then can't be edited using BIDS)
• connect indirectly to the Excel file via SQL Server Update: Hrvoje clarifies that he's specifically tackling the situation where you cannot install a driver which is bit-size-compatible with your BIDS package.

I'll let you decide for yourself if any of those solutions are acceptable, but they are not in fact the only solution. Here's how I've done it instead:

1. download and install the ACE OLE DB driver
2. create an OLE DB Connection Manager (not an Excel Connection Manager) using that driver and referencing the Excel file
3. create an OLE DB Source (not an Excel Source) using that connection manager

This approach seems to be working fine and avoids most of the drawbacks of any of the other three approaches. Here's a screen shot of the Connection Manager Connect settings.

And the All tab, here the Extended Properties are important, Excel 8.0 covers older (.XLS) files, Excel 12.0 is used for newer (.XLSX) files. HDR indicates whether the first row contains column names, my spreadsheet didn't but yours may.

The sadly broken zoom function in Visio 2010

I've been using Visio for years to do a variety of software related diagramming tasks (and occasional home improvement projects), it really seems to be the only credible product in the niche between graphic programs like Illustrator, presentation tools like PowerPoint, and full fledged CAD or CASE systems. Anyhoo after a long break from using any Windows apps at all I just started using Visio 2010 for some database design work and was baffled/annoyed by the fact that it randomly adjusts the zoom level to something other than what I set it at, e.g. on my main monitor if I attempt to set it to 100% it changes the value to 104%. Thinking it had to be a bug I discovered that this is actually intended behavior, and there is no trivial work-around. Instead the only "easy" option is to create a macro that executes this VBA command

ThisDocument.ZoomBehavior = visZoomVisioExact

which you'll then have to run by hand every time you open the file (any if you want this behavior to be global you'll have to add the macro to every type of file). If you want this to happen automatically you're actually invited to create your own COM add-in. Just so you can zoom. Seriously Microsoft? Think the guys at The Omni Group would ever suggest that to a user?

Server-Side View Source for Rails

I often find, particularly early in projects, that I need to spend more time looking at the HTML source of a page rather than the page itself, e.g. when trying to get the correct javascript or css files included. All browsers offer some sort of "view page source" option, but this opens another window, and there's no way to directly refresh the page as you update it. After a few iterations I'm usually left with more than one source window open and no easy way to tell which is the latest. This problem had me thinking about a server side view source solution, and thanks to Rails controllers' after_filter it turns out to just take one line:

Throw this in a controller and instead of the normal pages you'll see the source in your browser, e.g.

Covers my basic need: no extra clicks to refresh, no getting lost in multiple popup windows, and since it's one line easy to turn on/off by commenting out. However in one important regard it's step backwards from Firefox's View Page Source: it lacks syntax highlighting. I had a couple thoughts about how to tackle this, and Google revealed some earlier attempts to pretty-print HTML in Ruby. I toyed with this approach for a few minutes before deciding it was heading way down the too-much-complexity path, particularly for a hacky development-only feature. Taking another look at that blog post something struck me--there's nicely formatted code right there in the post, thanks to Alex Gorbatchev's client-side javascript/css syntax highlighter. Alex kindly offers a hosted version of the needed files, which allows for extending the one line approach without adding any dependencies.

Which yields the much prettier result

Since it's no longer so easy to turn on and off via comments (Ruby's lack of a multi-line comment form is one of my main gripes with the language) I threw in a hack to turn it on and off by tacking VS onto the end of the URL (it should usually be harmless to do this as an extra param, e.g. ?VS or &VS).