macgyver

[caption id="attachment_781" align="alignright" width="216" caption="All I need are these everyday objects — a toothpick, some liquor, a gun with no bullets, bullets, and three of my MacGyver writers. "][/caption]

So it's come to this, a blog meme post. Every so often in the hallowed world of writing content for free to be distributed to people that you don't know for no particular reason other than hearing your fingers clickity-clack, you meet someone else doing the same thing and they challenge you. Considering that this is a hobby built upon the sturdy foundation of honor, glory, and red bull, you must rise to meet this challenge, and write a themed blog post. David Stein started this and it continued to Brent Ozar who has called out my name in the darkness, to answer the call to write about: My MacGyver Moment.

Now I've written about my favorite MacGyver Moment before in a previous post toolmaker, the time that I wrote an automated PL/SQL to T-SQL translation program. In that post though the MacGyver moment played a small roll and didn't get the attention it deserved, so I will go into its history and explain it in greater detail.

The seed is planted

[caption id="attachment_782" align="alignleft" width="300" caption="This is how MacGyver would solve most of his problems in the future."][/caption]

It was my last semester in college, I had finished up all the required course work and just needed to take some 400-level electives to finish off my degree. I looked through the catalog and found a course that looked interesting, "Language Design and Implementation." The course seemed exciting for a few reasons, the professor had worked as a professional compiler writer for years and was a decided brilliant and fun teacher, the course work included building a compiler, and compiling source code was a part of the development chain that I had a theoretical understanding of but was still somewhat of a blackbox. I signed up for the course, it was fun, it was hard, I built a compiler (for a made up language called LITTLE targeting the JVM), and I learned a lot. I learned about parsers, tokenizers, compiler optimizations, bootstrapping, and a hundred other interesting concepts. The value that I took away from the course was a litany of problem solving techniques and an appreciation for what runs under the gcc hood.

The problem

My company sold some software. Doesn't seem like a problem at first, in fact, it's how the company does all those little things like pay my salary, so it's actually a good thing. The problem was that the software was written to run on an Oracle backend and we sold it to an organization that only had a SQL Server backend. There we sit looking at a mountain of PL/SQL code that does everything from execute a simple search to closing out and actualizing a fiscal year. 60,000 LoC representing tens of man-years of effort. Take this and put it in an extremely aggressive timeframe for porting and limited resources... suddenly the problem is easy to understand.

Bubblegum, paperclips, and string functions

60,000 lines of PL/SQL needed to become T-SQL, looks like compiling code to me. I knew my source language and my target language, time to write up a parser, tokenizer, emitter, etc. Then I noticed something while scanning through the PL/SQL, the problem domain was constrained, although PL/SQL can be constructed in a number of permutations all syntactically different, this was not the case. This PL/SQL was very uniform in construction, I didn't need all the pieces parts of an actual compiler. I didn't need to tokenizer and parse into abstract syntax trees for emission into the target language, I could just cheat, there were only 3-4 syntactic structures present in the source file.

I began writing the translator, after quieting that part of my brain that was telling me I was wasting time, just trying to avoid the unpleasant task of translating this by hand. Hackity hack hack, code is complete, hack up some nifty progress bars, run run run... translation complete! I looked at it, not quite perfect but I would estimate that the success rate on translation was around 99%.

The lesson

Sometimes hacking up a kludge to transmogrify data is the best course of action. Shutting up the part of your brain that doubts your ability, or the part telling you to do something the "right way" is the best way forward. Learning something for no other reason than that it seems interesting can provide you with the tools necessary to solve thorny problems down the road. Sometimes you really can defuse the bomb with a rubber band, #2 pencil, and thumbtack.