Truncate all Tables in a MySQL Database

UPDATE: I've written a new post with an updated script that encourages safer password handling. You should go there instead.

Here is a handy script to truncate all the tables in a MySQL database. I use this sometimes to wipe out an entire database before loading in some production data to replicate a customer problem:



This script isn't perfect -- it should really prompt the user for his password so that these passwords don't get into bash history logs. In a later post I'm going to talk about using this command in conjunction with some command line MySQL command to load a remote database locally for testing.

Rails Polymorphic better_nested_set

better_nested_set is a great Rails plugin for representing hierarchical data. Recently I worked on a problem where a message board feature needed to be created that could not only have multiple message trees but also needed to have multiple message trees referring to different model types. Instead of splitting these up into multiple tables, one for each model type, the acts_as_nested_set :scope parameter along with polymorphic associations can be used to store all the messages in a single table. Here is some sample code:



By combining better_nested_set and polymorphic associations the amount of work needed to introduce a new discussable type is reduced by reducing the number of models and number of tables.

Massage Rails better_nested_set full_set into a hierarchy - JSON

better_nested_set is a great Rails plugin for working with certain types of hierarchical data. The typical example where better_nested_set is used is for a threaded message board. better_nested_set will allow you to pull in a message and all its descendants with a single full_set call. The data that is returned includes a level field that allows you to indent the messages to indicate the appropriate parental relationships.

Sometimes, however, it may be necessary to take that full_set and turn it into a hierarchical data structure. For example when working with Ext JS you may want to feed this data to a tree control that needs a hierarchical JSON representation of the data. Here is some sample code that will do the necessary data massaging:

Rails Reporting, Raw SQL, and ActiveRecord

Most of the time when you dump some data out using Rails you are only dumping out a page of data at a time into an XHTML representaton. This is often done with some kind of pagination and it all works pretty well and speed doesn't become an issue. When doing reporting, however, you may be providing a much larger number of records to the user in the form of an Excel document or CSV file that the user will use third party software to do number crunching on. In this case the process of Rails reconstituting all of those model objects can really slow things down and you will likely need to resort to writing raw SQL and avoiding ActiveRecord. You don't, however, have to abandon ActiveRecord completely as it has some very helpful methods for constructing the SQL queries you will issue. Some of these methods are private but you can invoke them using the send method. Here is an example that uses ActiveRecord to append where and order clauses to the manually constructed sql statement:



A few notes...When you call select_all you are returned an array of hashes with the column names as keys. If you call execute you will get an array of arrays and you will have to use column positions to index into the array (probably a bit faster than select_all on producing that record set). As of Rails 2.0 the record set that select_all returns can be used as the :collection parameter in a partial. Lastly, in addition to the :add_conditions! and :add_order! methods there is a very handy :construct_finder_sql as well which accepts the same options your would pass to a regular finder.

Capistrano for Web Site Deployment -- Non Rails

Capistrano is an amazing tool for deploying Rails applications - and like they say when you have a hammer everything looks like a nail...so when I was tasked with developing a way to deploy a simple marketing web site for my company I looked first to Capistrano. I wanted to make it so that non-developers could get set up correctly and then do a "cap deploy" to deploy the current state of the web site. The Capistrano documentation is not great, so I googled around for how I might be able to deploy a non Rails application and found this. That link will lead you to a great blog entry by Pat Nakijima that explains how to deploy a non Rails application with Capistrano. Pat's version of this even sets up the Apache conf file and restarts the apache server using apachectl. I pared down his version because our conf file was going to remain fairly static so I wasn't worried about updating it or restarting Apache for changes to take effect. Also, with the web server hosting many different virtual host I wouldn't want to get stuck if someone had changed any of the conf files and Apache didn't restart correctly...

So here is my minimal version of deploying a simple web site with Capistrano (basically Pat's code with Apache related stuff removed and a micro task that removes the Capfile and config/ directories from the release):

Rails Send Data Through a Pipe with IO.popen

While having trouble getting Unix zip to play nice with Ruby's Tempfile I found that it was much easier to just avoid a temp file altogether by using a pipe create with IO.popen. Example code below shows how you might zip up a directory and send it back to the user from a controller:

Minify Rails Javascript and CSS with YUI Compressor

Here is a simple rake task to minify your javascript and css in a rails application using the YUI Compressor. You will need to have java set up correctly on the system and have the YUI Compressor jar in the lib directory of your Rails application to run this code.



You could drop this in lib/tasks/minify.rake and run it when deploying to production. If deploying with Capistrano, you might put it in the :after_update_code task:



This technique should work fine with Rails asset caching (explained here by the guys at maintainable software). Couple that with gzip compression by your web server and you should get dramatically reduced download times for your javascript and CSS.

A couple of caveats --

First, some javascript may not minify without problems. For example read this post by Andrew Dupont which talks about compressing prototype. I didn't seem to have a problem using the YUI Compressor with prototype but I haven't thoroughly tested it yet.

Second, I would prefer if the code above placed the minified version in different output directories. (Primarily so you don't accidentally check your minified versions into your SCM system.) Originally I did this putting the minified code in public/javascriptx and public/stylesheetx, respectively. Then I looked into how to get rails to easily look in different directories for javascript and css files. I found the JAVASCRIPTS_DIR and STYLESHEETS_DIR constants and thought I was all set -- then I then ran into the problem that changing:



in an initializer didn't really work as expected. Due to what looks to me like a bug in Rails 2.0 the files are still fetched from the original locations in spite of changing these constants - so I decided to stop messing with it and watch the Super Bowl.

If you don't like messing with Java to do something like this and are only concerned with compressing javascript then I would recommend reading this article by the guys at maintainable software. They give an example of using jsmin.rb for javascript minification.