Tuesday, May 19, 2009

Automatic SVN Updates for Offsite Backup

SubversionMy company has something of an offsite backup strategy. We have two external hard drives which are manually swapped out on a daily basis and someone takes the inactive one home.

We're a pretty small company and that works fine. Of course, while said person is in the office, both drives (the current drive and the offsite backup) are onsite. We also have a data center where we send our really important stuff too.

I've been doing my part to help in disaster recovery by keeping my own copy of the company SVN repository on an external drive at my house. The annoying thing was that I was always attaching the drive to my laptop and copying it over manually.

That process grew irritating so I installed TortoiseSVN on my home server and now I can just update the SVN folder and our other offsite backup is up to date. But then, I got a little tired of doing that too!

I decided to automate the entire process. First, I needed to connect to the office VPN (which is an OpenVPN server). Then, I needed to update the two repositories. Finally, I needed to disconnect from the office VPN.

The OpenVPN was really the hard part because if I opened a connection on the command line, I couldn't close the connection without sending an F4 keypress. I didn't want to write a windows app for this 'cause I should really be able to do this with a batch file.

Turns out, OpenVPN has a windows service and I can start those command line, so here's my script:
@echo off

echo net start "OpenVPN Service" >> %esg%\tempGetESGSVN.bat
echo ipconfig /flushdns >> %esg%\tempGetESGSVN.bat
echo svn up %esg%\Contractor >> %esg%\tempGetESGSVN.bat
echo svn up %esg%\Internal >> %esg%\tempGetESGSVN.bat
echo net stop "OpenVPN Service" >> %esg%\tempGetESGSVN.bat
echo del %esg%\tempGetESGSVN.bat /F >> %esg%\tempGetESGSVN.bat

%esg%\tempGetESGSVN.bat >> %esg%\GetESGSVN.log

@echo on

This batch file looks a little funny and that's because it is. Basically, it's creating another batch file which executes all of the steps and then deletes itself. I did this 'cause it made it a little easier for me to log. The batch file it creates looks like this:
net start "OpenVPN Service"
ipconfig /flushdns
svn up %esg%\Contractor
svn up %esg%\Internal
net stop "OpenVPN Service"
del %esg%\tempGetESGSVN.bat /F

Nota bene, if you don't have a command line SVN client installed, you can use Slik Subversion.

Monday, May 18, 2009

Conditional Check Constraints on SQL Server

SQL ServerI was on Stack Overflow the other day (something I'm just getting into . . . not sure I care that much for it yet, but we'll see. I'm definitely enjoying it more now that I'm not restricted on my behaviors because I lack reputation points).

In any event, someone asked me a question about conditional check constraints. The consensus seemed to be that you couldn't do it so I figured I'd post the solution on my blog as well.

Basically, the thought was that you couldn't put the check constraint on a subquery or an aggregate so you had to use a trigger only. Well, that's not technically true since you can write a function to do the aggregating for you. I'm not sure I like the idea, but it is in fact possible and I'm looking forward to reading all of your comments about the topic.

So, here's my script that comprises an entire test including a test table, the function, the constraint, some inserts, the results, and cleaning up:
CREATE TABLE CheckConstraint
Name VARCHAR(50),
RecordStatus TINYINT


SELECT @ret = COUNT(*) FROM CheckConstraint WHERE Id = @Id AND RecordStatus = 1;
RETURN @ret;


ALTER TABLE CheckConstraint
ADD CONSTRAINT CheckActiveCountConstraint CHECK (dbo.CheckActiveCount(Id) <= 1 OR RecordStatus <> 1);

INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2);
INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2);
INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2);
INSERT INTO CheckConstraint VALUES (1, 'No Problems', 1);

INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 1);
INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 2);
INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 1);
-- Msg 547, Level 16, State 0, Line 14
-- The INSERT statement conflicted with the CHECK constraint "CheckActiveCountConstraint". The conflict occurred in database "TestSchema", table "dbo.CheckConstraint".
INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 2);

SELECT * FROM CheckConstraint;
-- Id Name RecordStatus
-- ---- ------------ ------------
-- 1 No Problems 2
-- 1 No Problems 2
-- 1 No Problems 2
-- 1 No Problems 1
-- 2 Oh no! 1
-- 2 Oh no! 2

ALTER TABLE CheckConstraint
DROP CONSTRAINT CheckActiveCountConstraint;

DROP FUNCTION CheckActiveCount;
DROP TABLE CheckConstraint;

Tuesday, May 12, 2009

Extension Method to Imitate Upto() in Ruby

RubyLast month, I wrote a blog post about using an extension method to imitate the ruby method times(). I recently found another cool method in ruby that I wanted to avail to the C# public.

Now, before I show you the implementation, I'll just say that it doesn't do anything the for loop wouldn't do on its own. I think the method has two benefits. One, I think it looks clean because it's basically a fluent way to create a for loop. Two, I like the fact that you can pass an action to the method.

In Ruby, the implementation looks like this:
5.upto(10){ |i| puts i; }

# output
# 5
# 6
# 7
# 8
# 9
# 10
I added the following extension method to one of my utility extension method libraries:
public static void UpTo(this int start, int upto, Action<int> action)
for (int i = start; i <= upto; i++) action(i);
Here are the test cases and the results:
[TestCase(4, 12)]
[TestCase(-10, 10)]
[TestCase(10, 5)]
public void test_upto_iterator(int from, int upto)
int test = 0;
from.UpTo(upto, num =>
Console.WriteLine("Index: {0}", num);
test += num;

int expected = 0;
for (int i = from; i <= upto; i++)
expected += i;

Assert.AreEqual(expected, test);

// ***** ESG.Utilities.System.Tests.IntegerExtensionTests.test_upto_iterator(-3,2)
// Index: -3
// Index: -2
// Index: -1
// Index: 0
// Index: 1
// Index: 2

// ***** ESG.Utilities.System.Tests.IntegerExtensionTests.test_upto_iterator(4,8)
// Index: 4
// Index: 5
// Index: 6
// Index: 7
// Index: 8

Tuesday, May 5, 2009

Stored Procedure to Unpivot a Table

PivotWhen I first started blogging, I wrote a post about this 3 liner (well, technically) stored procedure to unpivot data. Well, I was new to blogging and I didn't really have all of the great tools that I have now, so it was a pretty crappy post.

Well, I was just flipping through my archives looking at the lack of SQL Server posts that I have and thought, "I really should rewrite that one and use a good highlighter and post some sample usage."

So, I did, but before I get to the stored procedure, why don't I explain a little bit about what unpivoting is? Sometimes you don't know what kind of data you're going to be collecting so you can't really create a third normal schema to store these data. Instead, you use something called an Attribute Value Pair table (or AVP for short).

This table ends up being tall instead of wide, but you can store any data you please in there. Problem is, how do you get to a full and meaningful record? Well, the process to take tall data and make it wide is called pivoting. If, however, you have wide tables and you need to seed this AVP table, that process is called unpivoting.

I had to do this several times and I didn't really care to write a view every time I had to unpivot, so I wrote a sproc instead. Here's that stored procedure and it should work on all SQL Server 2008 and SQL Server 2005:
@tableName AS VARCHAR(512),
@whereClause AS VARCHAR(2000) = NULL,
@commonColumns AS VARCHAR(2000) = NULL
) AS
-- ==========================================================
-- Author: Patrick Caldwell
-- Create date: 2007/12/19
-- Description: this procedure unpivots data in a table
-- specified in the parameters with an optional
-- where clause. every line has been commented.
-- the commonColumns variable stores a list of
-- columns that should be on every row.
-- ==========================================================

-- a variable in which to store the query

-- build the query
SELECT @query = ISNULL(@query + ' ', '') + QueryLine
SELECT QueryLine = 'SELECT ' +
ISNULL(@commonColumns + ', ', '') +
'ColumnName = ''' + COLUMN_NAME + ''', ' +
'ColumnValue = CAST([' + COLUMN_NAME + '] AS NVARCHAR(4000)) ' +
'FROM ' + @tableName +
ISNULL(' WHERE ' + @whereClause, '') +


ISNULL(@commonColumns + ', ', '') +
'FROM ' + @tableName + ' ' +
'WHERE 1 = 0'
) AS lines;

-- execute the query
For the sake of example, I'm going to need a test table. Here's the one I'm using:
Id TINYINT IDENTITY(1, 1), -- so what if I only have 255 pals?
FirstName VARCHAR(128),
LastName VARCHAR(128),
Gender CHAR(1)

INSERT INTO Pals VALUES ('Lauren', 'Woody', 'F'); -- no, really . . . F
INSERT INTO Pals VALUES ('James', 'Brechtel', '?');
INSERT INTO Pals VALUES ('Ryan', 'McGarty', 'M');
And finally, here are some example usages:
EXEC UnpivotTable @tableName = 'dbo.pals'
ColumnName ColumnValue
---------- -----------
Id 1
Id 2
Id 3
FirstName Lauren
FirstName James
FirstName Ryan
LastName Woody
LastName Brechtel
LastName McGarty
Gender F
Gender ?
Gender M

EXEC UnpivotTable @tableName = 'dbo.pals', @commonColumns = 'Id'
Id ColumnName ColumnValue
---- ---------- -----------
1 Id 1
2 Id 2
3 Id 3
1 FirstName Lauren
2 FirstName James
3 FirstName Ryan
1 LastName Woody
2 LastName Brechtel
3 LastName McGarty
1 Gender F
2 Gender ?
3 Gender M

EXEC UnpivotTable @tableName = 'dbo.pals',
@commonColumns = 'Id',
@whereClause = 'Gender = ''F'''
Id ColumnName ColumnValue
---- ---------- -----------
1 Id 1
1 FirstName Lauren
1 LastName Woody
1 Gender F

Converting Hexadecimal or Binary to Decimal on SQL Server 2008 and 2005

Binary ShirtI just finished a blog post about Converting Decimal to Hexadecimal with T-SQL on SQL Server 2008 and 2005 and it got me thinking, "What if you have a hexadecimal string or a binary string and you want to convert it back to decimal?"

Well, in my previous article, I provided a SQL custom function that would allow you to convert from base 10 (decimal) to any other base you wanted. That way, you wouldn't need a Dec2Bin and a Dec2Hex function. Similarly, I don't think you should have both a Bin2Dec and a Hex2Dec to convert from binary or hexadecimal to decimal.

So, I wrote a custom function that converts from any base to base 10 and it looks a little like this:

-- just some variables
DECLARE @characters CHAR(36),
@result BIGINT,
@index SMALLINT;

-- initialize our charater set, our result, and the index
SELECT @characters = '0123456789abcdefghijklmnopqrstuvwxyz',
@result = 0,
@index = 0;

-- make sure we can make the base conversion. there can't
-- be a base 1, but you could support greater than base 36
-- if you add characters to the @charater string
IF @base < 2 OR @base > 36 RETURN NULL;

-- while we have characters to convert, convert them and
-- prepend them to the result. we start on the far right
-- and move to the left until we run out of digits. the
-- conversion is the standard (base ^ index) * digit
WHILE @index < LEN(@value)
SELECT @result = @result + POWER(@base, @index) *
(SUBSTRING(@value, LEN(@value) - @index, 1)
, @characters) - 1
@index = @index + 1;

-- return the result
RETURN @result;

Here's a test query:
dbo.ConvertFromBase('110010110', 2) -- 406
,dbo.ConvertFromBase('120001', 3) -- 406
,dbo.ConvertFromBase('626', 8) -- 406
,dbo.ConvertFromBase('406', 10) -- 406
,dbo.ConvertFromBase('196', 16) -- 406
,dbo.ConvertFromBase('ba', 36); -- 406

Converting Decimal to Hexadecimal with T-SQL on SQL Server 2008 and 2005

Hexadecimal ShirtYesterday, I was doing a little maintenance on our internal bugtracker.net installation. Bug tracker uses the first column of a result set to determine the background color of the issue. I wanted to create a nice fade effect between green and red, but that requires that I convert a decimal number to hexadecimal in SQL Server.

I didn't really want to write a Dec2Hex conversion function for SQL Server because I could very well end up needing a Dec2Bin function as well (if I ever wanted to convert decimal to binary in SQL), so I just wrote a custom function to convert a decimal number to any base between 2 and 36. The same SQL custom function can convert decimal numbers to hexidecimal or decimal to binary (if you need to go the other way, look here to convert any base to decimal in SQL). Here's what that looks like:
@value AS BIGINT,
@base AS INT

-- some variables
DECLARE @characters CHAR(36),
@result VARCHAR(MAX);

-- the encoding string and the default result
SELECT @characters = '0123456789abcdefghijklmnopqrstuvwxyz',
@result = '';

-- make sure it's something we can encode. you can't have
-- base 1, but if we extended the length of our @character
-- string, we could have greater than base 36
IF @value < 0 OR @base < 2 OR @base > 36 RETURN NULL;

-- until the value is completely converted, get the modulus
-- of the value and prepend it to the result string. then
-- devide the value by the base and truncate the remainder
WHILE @value > 0
SELECT @result = SUBSTRING(@characters, @value % @base + 1, 1) + @result,
@value = @value / @base;

-- return our results
RETURN @result;

Here is a test query:
dbo.ConvertToBase(406, 2) -- 110010110
,dbo.ConvertToBase(406, 3) -- 120001
,dbo.ConvertToBase(406, 8) -- 626
,dbo.ConvertToBase(406, 10) -- 406
,dbo.ConvertToBase(406, 16) -- 196
,dbo.ConvertToBase(406, 36); -- ba