The Software Purist |

Archive for November 2015

So, I ran into a subtle registration bug in the development of Compucator.com, so it became an interesting opportunity to talk about some of the technical details.

As you probably are aware, I’m very much against the typical copy-paste programming we often see during development. Most likely, if you need to copy-paste something, probably it should be refactored. That being said, sometimes it’s a useful tool. The problem with doing such is that it’s also an error-prone tool.

The situation is that I was adding fields to a table in the SQL database. It was a table related to user information, so there were fields, such as address, zip code, phone number, email, etc…

Some of the fields were meant to be marked, as UNIQUE. For example, you might argue that email should be unique to prevent duplicate registrations. Obviously, the account name must be unique. However, it seems when I was doing this I accidentally made a copy-paste error and applied the UNIQUE attribute to the zip code field.

This is not the actual code, but imagine it looks like this:


CREATE TABLE Details
(
...
name VARCHAR(100) UNIQUE NOT NULL,
...
zipCode VARCHAR(10) UNIQUE NOT NULL
...
);

The real issue here is that because the zipCode was set as UNIQUE, when two users registered with the same zip, the second one failed and was not able to register. Going through the logs, I realized this occurred once, so I had to fix it.

Theoretically, I should’ve caught this during my unit tests. Unfortunately, I missed having any test cases where there were duplicate zip codes which is how it made it all the way to production, so definitely lesson learned here.

Furthermore, I wanted to correct this programmatically, as there were multiple unique constraints on this table. I didn’t want to take any risks of deleting the wrong constraint, and this change needed to make it to my SQL update scripts so any other test databases received the same update. The code would normally look something like:

ALTER TABLE Details
DROP CONSTRAINT UQ_Details_zipCode;

However, SQL Server will automatically generate names for constraints that I don’t explicitly give names to. So, the names were things like UQ_Details_xxxxxxxxxxx, where the name was unfortunately not meaningful.

Unfortunately, this still proved to be some complicated SQL code, so it seems the easiest thing to do was something like the following:


DECLARE @command nvarchar(1024)
DECLARE @tableName nvarchar(256) = N'Details'
DECLARE @columnName nvarchar(256) = N'zipCode'

SELECT @command = 'ALTER TABLE ' + OBJECT_NAME(so.parent_obj) + ' DROP CONSTRAINT ' + so.name
FROM sysobjects so
WHERE OBJECT_NAME(so.parent_obj) = @tableName
AND so.xtype = 'UQ';

print @command;

--execute (@Command)

NB: I tend to print out the command to make sure it’s doing what I think before I execute it.

I then just added a few lines to the script to re-add the constraints for fields that were actually supposed to have them.

Perhaps there’s a better solution, but this seemed cleanest. Has anyone run into any similar issues? Feel free to share your experiences.

No tags

Nov/15

23

Re-launch

Hello all. I know it’s been a long hiatus. Other projects and interests got in the way, but I’m ready to pick the blog back up, dust it off.

So, the first thing you might wonder if what I’ve been up to. I’m pleased to announce the launch of my new site Compucator.com. It’s a Software training site that I have developed and just launched. The first course is up and is a complete course on the C++ STL library, focusing on STL containers. It has lessons and exercises. I intend to create a part II of this course if there is interest. I’m also open to suggestions on other courses, including C++ and otherwise.

As far as the site itself, I’m pleased to offer a $5 OFF discount code. If you use the code “Launch” at checkout, you’ll receive this discount. The code is valid until the end of the year.

Now, that being said, I’m happy to continue offering free content on this site and to continue blogging. I have a list of ideas for new blog posts. But, what are you guys interested in?

· ·

Theme Design by devolux.nh2.me