software solutions / project leadership / agile coaching and training

More AutoHotKey goodness (database edition)

Posted on February 28, 2013

I posted a few years back about some of the macros that I have in my AutoHotKey file. Since then I’ve added some new things that are worth mentioning.

Inner joins

The project that I’m on involves a lot of database work. I’m writing queries all the time, and this means typing the same inner joins over and over. I would rather let AutoHotKey do that for me.

Here’s a query that I write often:

select * from igs.accounts a
inner join igs.enrollments e on e.accountid = a.accountid
inner join igs.intentions i on i.enrollmentid = e.enrollmentid
where a.utilitylineofbusinessid = 5

It’s incredibly painful to type those inner joins over and over (it was even painful writing it for this blog post), so I use AutoHotKey to do it.

:*:ea]::inner join igs.enrollments e on e.accountid = a.accountid
:*:ae]::inner join igs.accounts a on a.accountid = e.accountid

:*:ie]::inner join igs.intentions i on i.enrollmentid = e.enrollmentid
:*:ei]::inner join igs.enrollments e on e.enrollmentid = i.enrollmentid

:*:wcpa]::where utilitylineofbusinessid = 5

:*:ssf]::
SendEvent select * from `
return

So now to type that same query, here is what I type:

ssf]igs.accounts a
ea]
ie]
wcpa]

I have macros for virtually every inner join I would ever do in the database so that I don’t ever have to type one out. If I find one that I missed, I go back and add it.

This makes writing queries so much easier. Once you find ways to do mundane things faster, it makes you do everything faster because your typing is keeping up with your brain.

Switching databases

We have several database environments just like most of you, which means that I’m switching between database servers all the time in SQL Server Management Studio. Again, I can pick up the mouse and click 5 times in dialogs and toolbars to switch. Or I can do “Alt-q ch Ctrl-u i”. But I would rather do it with one keystroke.

#^1::
Send !qch
Send thedatabaseserver
Sleep 200
Send {Enter}
Sleep 100
Send ^ui{Enter}
return

Boom! Ctrl-Win-1 and I’ve switched to my database server and selected my database (the “Send ^ui{Enter}” line does that – Ctrl-U selects the database dropdown and “i” selects my database because my database name starts with “i”). I have shortcuts for all of my database environments so that I can do even less with the mouse than ever.

Commonly used queries

There are certain queries that I use a lot (e.g. select the top 1000 rows from our log table). So I wrote a macro for that.

:*:seq]::select top 1000 DateCreated d, * from igs.ServiceExceptions order by DateCreated desc

Typing is slow

Typing is slow (and I’m a faster typist than most), so anything repetitive that I do often I want to turn into a macro or keyboard shortcut. Hopefully some of these ideas will give you ideas of how you can improve your productivity.

1 Comment »

  1. Just wanted to thank you for sharing. From your post a few years back, which I just read today, I tried

    CapsLock::
    Send {End}
    return

    and it works! Maybe it is a no-brainer for most but since I am a beginner I appreciate a lot that you shared your file.

    sam — March 13, 2013 @ 4:23 pm

Leave a comment





SERVICES
SOFTWARE SOLUTIONS
I have over 10 years of software development experience on several different platforms (mostly Ruby and .NET). I recognize that software is expensive, so I'm always trying to find ways to speed up the software development process, but at the same time remembering that high quality is essential to building software that stands the test of time.
PROJECT LEADERSHIP
I have experience leading and architecting large Agile software projects and coordinating all aspects of a project's lifecycle. Whether you're looking for technical expertise or someone to lead all aspects of an Agile project, I have proven experience from multiple projects in different environments that can help make your project a success.
AGILE COACHING
I believe that Agile processes and tools should be applied with common sense. I've spent the last 6 years working on Agile projects as a consultant in many different environments, both in leadership roles and as a practitioner doing the work. I can help you find out how Agile can work best in your organization, not just apply a prescriptive process.
TEST DRIVEN DEVELOPMENT TRAINING
TDD Boot Camp is a hands-on, three day, comprehensive training course that will teach you all of the skills, tools, frameworks that you will need to use test-driven development to develop real world .NET applications. If you're not looking for something that intensive, check out the the half-day version.
Have any questions? Contact me for more information.
PRESENTATIONS
The Business of You: 10 Steps For Running Your Career Like a Business
From CONDG 2012, Stir Trek 2014
From Stir Trek 2013, DogFoodCon 2013
From Stir Trek 2012, QA or the Highway 2014
(presented with Brandon Childers, Chris Hoover, Laurel Odronic, and Lan Bloch from IGS Energy) from Path to Agility 2012
(presented with Paul Bahler and Kevin Chivington from IGS Energy)
From CodeMash 2011
An idea of how to make JavaScript testable, presented at Stir Trek 2011. The world of JavaScript frameworks has changed greatly since then, but I still agree with the concepts.
A description of how test-driven development works along with some hands-on examples.
From CodeMash 2010
From CodeMash 2010