TextMate Sql Formatter Command

Oct 27 2009 Published by under Database,English,Mac,OS X,Ruby,TextMate

Recently, I am doing some heavy database migrations, so I spend a lot of time on playing with SQL again. It’s fun as always. But there are some SQL files are quite long and messy. There is no problem on running, but it’s really painful to look at and do any changes.

So I went out and tried to find any SQL Formatters. There are quite a few and I’ve tasted them as many as I can.

At the end, I found myself really enjoy using Instant SQL Formatter from Gudu Software. It has a free online sql tidy tool and it’s very powerful:

Instant SQL Formatter is a free online sql tidy tool, actually, it not only can beautify your sql but also can turn your formatted sql into html code, so you can post coloured sql code in your blog, forum,wiki and any website easily. In addition to beautifying SQL code, this sql tool can translate SQL code into C#, Java, PHP, DELPHI and other program languages. Another useful feature is find out all database objects such as table, column, function in sql by selecting output format to list database object.

Here is the Free online Tool and here are some examples you can see. Quite impressive! To format your SQL, you just need paste the sql in the textarea, choose the database and output format then press “Format SQL”, you’ll get the result right away.

Every SQL file looks great now. But after tens of copy & paste and copy & paste between TextMate and browser, it feels not as smooth as I want. I think I should find a better way.

Gudu software does provide some desktop version even add-ins, sadly there are all Windows based. It’s not a option for me.

After reading the page source of their free online tool, here is the result:

#!/usr/bin/env ruby

require 'net/http'

url = 'http://www.dpriver.com/cgi-bin/ppserver'
url = URI.parse(url)
http = Net::HTTP.new(url.host, url.port)

query = "<sqlpp_request><dbvendor>mysql</dbvendor><outputfmt>SQL</outputfmt><inputsql>#{ENV['TM_SELECTED_TEXT']}</inputsql><formatoptions><keywordcs>Uppercase</keywordcs><identifiercs>Lowercase</identifiercs><functioncs>InitCap</functioncs><lnbrwithcomma>after</lnbrwithcomma><liststyle>stack</liststyle><salign>sleft</salign><quotechar>\"</quotechar></formatoptions></sqlpp_request>"

header = {
  'Referer' => 'http://www.dpriver.com/pp/sqlformat.htm',
  'User-Agent' => 'Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US; rv: Firefox/3.0.1'

resp, data = http.post(url.path, query, header)

puts resp.error! unless data
formatted_sql = data[/<formattedsql>.*<\/formattedsql>/m].gsub(/<\/?.*>/, '')

puts formatted_sql

Download TextMate SqlFormatter Command

How to install?

After save to your own disk, unzip it, just open those tmCommand files and add them into your TextMate. The shortcut key has been set to Command+Shift+F for now.

As you can see there are two tmCommand files. The “SQL Formatter – Stack” will format you sql with every fields has its own line, the “No Stack” one will put all the fields into one line. Try them and you’ll see the difference.

How to use it?

Open TextMate,  select the SQL query you want to format and press Command+Shift+F. The beautiful formatted SQL will replace the selected SQL “instantly”!


The command is using free online Instant SQL Formatter, so the internet is required.

What’s next?

As I said before, SQL Pretty Printer is really powerful. I really should create a complete TextMate Bundle instead of just one command. But before doing this, I should get the permission from them now.

Any update will be posted here, hopefully soon.

Enjoy the SQL Formatter Command and stay tuned for more.

10 responses so far

TextMate Shortcuts Desktop

Aug 23 2009 Published by under Apple,Mac,OS X,Ruby,TextMate

I’ve been using this TextMate shortcuts Desktop Wallpaper for two years already, it’s really helpful, no matter how long have you used TextMate. And, did we missed anything new about TextMate in the past two or three years? I don’t think so.

Snow Leopard is here already, but we still know nothing about TextMate 2.

Download ↓

TextMate Keyboard Shortcuts Desktop Wallpaper 1280 x 800

TextMate Keyboard Shortcuts Desktop Wallpaper 1920 x 1200

Source: Scott Boms

One response so far

Coda 1.5 entering limited private beta

Jul 08 2008 Published by under Apple,English,Mac,OS X,Ruby,TextMate

One of my favorite editor on Mac is Coda, the most important part for me is in their tagline:

Compare with my another favorite editor, TextMate, every time I open it, I will open three more windows at the same time:

  • Terminal for running application
  • Terminal for mysql
  • Browser for view my pages and documents

Coda has all of these and even more:

Text editor + Transmit + CSS editor + Terminal + Books + More = Whoah.

But, on another side, leaking of version control and no powerful bundles as TextMate keep a lot of Ruby developers out of it.

Things may change:

After a lot of work, we’re finally ready to work with some people to test Coda 1.5 — the next major release of Coda — and prepare it for the general public.

To enter Coda 1.5 limited private beta, go to:
If registration is still available, the registration link will be in the upper-right corner of the page. (If there’s no link, we’re full.)

So what’s new in Coda 1.5?

  • Better Search and Replace?
  • Source Control Support (SVN or Git)?
  • Custom Books? Add more Ruby Document in it?
  • Better Support for more than one languages mix together in one file?

Stay tuned. :)

* Unless you want to use more than one window. Which is totally cool.

No responses yet

Keyboard navigation for TextMate stacktraces

Jun 29 2008 Published by under Apple,English,Mac,OS X,Ruby,TextMate

Keyboard navigation for TextMate stacktraces

Put this script at the end of /Applications/TextMate.app/Contents/SharedSupport/Support/script/webpreview.js.

document.addEventListener('keypress', function(e){
    var key = e.keyCode
    if (key != 63233 && key != 63232) return
    links = document.getElementsByTagName('a')
    for (var i = 1; i < links.length; i++)
        if (links[i].title == 'focused') break
    if (i == links.length) i = 0
    links[i].title = null
    if (key == 63233){
        if (i == (links.length - 1)) i = 1
        else i += 1
    if (key == 63232){
        if (i <= 1) i = links.length - 1
        else i -= 1
    links[i].title = 'focused'

Incredibly useful when I am running my tests!

No responses yet

(Re)Enable TextMate Ruby Bundle

May 11 2008 Published by under English,Mac,Ruby,Software

We all love TextMate, by all means. It is the best friend for Ruby Developers.

After our project upgraded to Rails 2.0+ at LearnHub.com, I noticed that there are some very powerful Ruby Bundle Commands just stopped working. Especially these two:

  • Run Focused Unit Test

This is an awesome lovely command in Ruby Bundle, absolutely my favorite! It allows you to run a single unit test out of a whole suite of tests by simply positioning your cursor into the test you’d like to run.

  • Run Rake Task

This is another cool command too. It shows a popup window which list all the available rake tasks you have and it will show a pretty HTML result window after you choose from the list.

This thing really bothers me a lot. I could be fine for the second one, as I do have terminal windows open all the time. But I really really miss the first one, as our test files get longer very soon.

I have to make it work!

After couple of hours trying today, I’ve pretty much got the right reason and the simple solution, for now.

The reason is that TextMate includes its own version of builder.rb, which is conflicting with Rails 2.0′ version of builder.rb.

The simple solution:

  1. Open TextMate Bundlers Editor
  2. Find Ruby -> Run Focused Unit Test
  4. Change to RUBYLIB=”$TM_BUNDLE_SUPPORT/RakeMate”
  5. Reload Bundlers

That’s it! And apply the same changing method to other commands such as “Run Rake Task” as well.

And just want to point out the alternate solution is:

mv /Applications/TextMate.app/Contents/SharedSupport/Support/lib/Builder.rb /Applications/TextMate.app/Contents/SharedSupport/Support/lib/Builder.rb.bak

It’s up to you to choose one of them.

Hope Google can pickup this post quickly so we can save somebody else sometime too. :)


3 responses so far