Database and Continuous Integration

Ben Scheirman wrote a post on the flaws and irritation of dealing with database scripts for a project and looked to Ruby on Rails for inspiration. After I commented on his blog about what our team does, he sent me a email with some questions:

Thanks for the comment on my blog...  I'm wondering:  what tool do you use to create the scripts?

Do you do a drop, re-add?  or a if not exists, then create?

Also, how do you order these?

I was thinking of doing a rails-style migration scheme like this:

0001.Items.Table.sql
0002.Categories.Table.sql
0003.Items.TestData.sql
0004.Items.Categories.FK.sql

but I'm concerned about multiple developers adding migrations and having them collide.  Ideas?

My Response:
Order is an interesting problem. For table changes, we use the "if not exists, then create" strategy. For Views, Functions, Sproc we drop and re-create (including permissions).

What we've discovered is that order only applies to table mods. Our sprocs and views run after the table updates, but we don't have sprocs calling other sprocs (generally) so they don't have order dependency.

As for a tool, we used a SQL project in VS 2003. It would create a .cmd file with all files in the project using an algorithm based on file extension. .tbl are scripted first, then .vw, then .prc, and then finally .sql (or were .sql first, I don't remember). This worked well until VS 2005 dropped the feature that created a command file.

So…we wrote a Nant script that traverses the \sql directory and performs the same logic as the command file. It works, but it is not ideal.

I did see a demo of the new "Data Dude" (VS 2005 Edition for DB Professionals). It was impressive how they solved this problem. They introduced a "build" step that creates a script file of all the changes taking into account dependencies and refactorings (yes, refactorings in the database). Very cool stuff, but I haven't worked with it personally yet. L

I like the ideas behind Rails but do you think your developers are going to do it? They probably have access to the database and will just run the script themselves. I'm asking because we ran into that problem on my current project and the team decided to remove our rights to the testing database. The only way to get scripts on the test server were to have the CI server execute them during the automated build. This forced us to make sure our scripts were in the code repository (I hate typing SourceSafe) and ran without error.

All that being said, I’m still looking for a better solution.

Tag, I'm it - 5 Things you don't know about me

I got tagged twice. Rob Bogue was first, then I was going to tag Ben Scheirman but he beat me to it.  Anyway here goes…

 

SQL is a B*tch

After buying my first house after graduating from college, I adopted a black lab mix puppy from the Animal Rescue League. Since I'm a geek and didn't think that "Visual Basic", "IIS", or I wasn't going to name her, "Java". So I named her "Sequel". I had spell it out because the vet didn't know how to pronounce,  "S" "Q" "L".

 

At the time I didn't realize the Sequel was a term meaning, "the next one" and every non-geek I ran into thought she was the second dog and assumed something bad happened to first dog.

 

"You shot me!"

As a restless tween, I had a Crossman (no relation to my wife) BB gun to shoot it in the backyard of my parent's house. I left my BB gun in the family room next to the sliding glass door for quick access.

 

On a hot summer day, I was hanging out with my sister, Jody, in the family room. Since it was a hot summer day, was hanging out shirtless. My sister was joking around and picked up my BB gun and pointed it at me. Well the next thing I know,  I hear a POP and my shoulder is numb.  I glance over to my right shoulder and there is a small stream of blood. I looked up to my sister and said, "You shoot me!"

 

It didn't hurt, but the BB was wedged under my skin and my sister had to drive me to the doctors office to get the BB removed. To this day she says she didn't know it was loaded.

 

During the summer of 2001, my girlfriend of 3 years broke up with me. I don't think she envisioned herself married to a tech guy and got a little stir-crazy. I don't hold any ill will.

 

I figured I would have some extra free time without a girlfriend and wanted to build a website of my own. So I came up with a concept called "The Summer Of Tim". It was based on an episode of Seinfield where George declared he was taking the summer off.

 

I needed a website to network with other young, single professionals in Des Moines. The concept was to create a website that people could join that would award points for meeting up at local events.  The site was a success and I was interviews by a sexy reporter named,  Jody Crossman,  from the Des Moines Register.  The story she wrote was also picked up by USA Today.

 

I guess Jody and I hit it off because we are now married with a daughter, Sadie.

 

That's kinda creepy

My sister's  name was Jody Lynn Gifford until she got married.  My wife's name after we got married,  Jody Lynn Gifford.

 

Speaking of names...

My middle name is Jerron. I don't understand where it came from, but it a unique middle name for a guy with the first name of "Tim".

 

Now I get to tag a few people. I’m going to pick Nick Parker, Javier Lozano, Levi Rosol, and Josh West.

VS 2005 & NAnt Integration

 I ran into an issue today that was driving me crazy. I upgraded the version of Rhino Mocks (I love Rhino Mocks!). I thought it would be easy. Download the latest version and drop it into our \lib folder, re-build and “BAM, it’s just that easy”…or so I thought.

After I added the reference, my workstation built correctly and so did the other team members. But when Cruise Control tried to build the project, the build failed! I don’t like to be “the guy” who breaks the build. Since the build server uses the msbuild nant task and VS 2005 uses msbuild (and magic) there is a disconnect between the build processes and I’ve never been able to integrate Nant into VS 2005 in a way that didn’t make the team grumble.

I couldn’t take it anymore so I did some research and found a way to integrate NAnt with Visual Studio 2005. It still isn’t seamless, but I don’t hate it…yet. One gotcha I discovered, you’ll need to name your build file “default.build” for nant.exe to pick it up.

I still wasn’t able to figure out what was causing the build to break, but at least I didn’t check-in and break the rest of team.

Now if I could find a way to run a .cmd or .bat file from the Solution Explorer….GRRR!

Is there an easier way? Should I be using msbuild for my build scripts over nant?

Sans Consolas

I’m concerned about the new security setup in Vista. Now that Vista allows developers to develop without being an administrator on a workstation, I fear the IT/Networking/Security Departments!

Why do I fear them? Because they resist change. Each time I request to have a new anything (font, VS Add-In, or tool) installed on my workstation I have to:

·         Submit a request to my team leader/manager/supervisor

·         After approval, forward the request to the IT Department for review/approval

·         Wait…

·         Respond to questions about the request

·         Wait…

·         Defend the request after it is refused

·         Re-submit the request to the next level of management (Once I had to request approval from the CIO! By the way, CIOs don’t like to be involved in what tools are installed on developer workstations.)

·         Wait…

·         This is typically when a meeting is called between someone in IT, myself, and a mid to upper level manager

·         Finally, get approval to download and install the software

I can understand if I had to go through this process the first couple times I made a request for new software, but I’ve done this a half dozen times and there still isn’t a level of trust between the IT department and myself or the development team.

Today I requested to have the Consolas font installed, we’ll see what happens…

6 x 2 + 1

Our team just started on a +1 sprint and it is going very well. I should explain what a 6 x 2 + 1 is. We normally work in 2 week sprints and deliver production level code at the end of every sprint. We’ve been working in this cycle for close to 2 years and haven’t had a chance to “fix” any past decisions and have accumulated some technical debt.

So the new formula is to work for six sprints (two weeks each) on stories determined by the customer team, but then taking one week to work on technical stories created by development team. Thus 6 iterations x 2 weeks + 1 week. The team is very excited about being able to pay down some of our technical debt. Most of stories we’ve known about and wanted to work on for a long time, but couldn’t squeeze them into the normal two week sprints. This gives a little time cleaning up after ourselves.

I don’t think we’ll be doing the plus one sprints forever, but for a new project it makes sense to plan for it until the team hits its rhythm.

How does your team take care of cleaning up your technical debt? Is it planned for? Is it ever cleaned up? Or do you never make a mistake on the design or the architecture? J

Tragic News

Tuesday morning I got some tragic news from my friends as Two Rivers Marketing. A plane that Two Rivers had chartered for a business trip crashed a few miles outside South Bend, Indiana. On the plane were five people, four of whom I had the privilege of working with during my six months at their company.

One of them was a very good friend of mine. Actually Eric Jacobs was much more than a friend. He was friend, a father, an advocate and a leader in our community.

When my wife and I moved back to Des Moines, Eric wanted me to come to Two Rivers. He was adamant about it. In a company that had been burnt by consultants in the past, he put his reputation on the line a recommended me to the senior partners. I was flattered and I tried like crazy to make sure that he didn't tarnish his reputation by recommending me. Eric was always there to encourage me to take on a new challenge. He believed in me and that helped me believe in myself.

As a leader in the community, Eric took the reigns of the Iowa .NET User Group and has been leading us since its inception. He always joked that since he couldn't code very well, he had to find another way to contribute. He came up with the idea of linking our members with leaders of other non-profit organizations and having us create websites for them. We would never be comfortable taking the credit himself, but he was the guy ordering the pizza every month. He was the guy sending the email reminding people about the meeting. He was the guy updating the website. He was the guy!

As a father and husband, Eric was my role model. I was always amazed at his ability to raise four boys with his wife, Heather. When my family and I went through a personal tragedy, Eric and his family had gone through something similar and he helped me through it. When my wife became pregnant with our first child, he let me know what to expect and made me less anxious about becoming a father.

I couple weeks ago, Eric called and wanted to get together for lunch. Unfortunately our schedules didn't mesh and we were never able to get together. I'm so sorry we couldn't see each other one last time. God bless you Eric, you touched the lives of many. You will be missed.

Heartland Developers Conference 2006

I've been in Omaha for the last couple days at the Heartland Developers Conference. It was a great opportunity to catch up with a bunch of people.

It was nice to see Jeff Brand again. With his new responsibilites at Microsoft, Jeff doesn't make it down to Des Moines to hang out.

I also ran into Dru Sellers. I first met Dru a couple weeks earlier at Tulsa Tech Fest. Dru does PodcastStudio.net with Jeff Julian. Thursday afternoon after our presentation, Nick Parker and I sat down and did our first podcast with them. It will be released sometime over the next few months. It was a good interview and Dru and Jeff did a great job at keeping the conversation flowing.

We had a good time Thursday night after the first day of sessions. Nick Parker, Matt Ring, Mike Vesey, Ken Sherman, Dave Havenridge, Phil Stanislav, and Matt Milner had dinner at Omaha Prime in the Old Market. The steaks were great, but I'm still searching for a shrimp cocktail that compares to St. Elmo's in Indianapolis, IN.

It was fun and I can't wait to do it again next year!