Automatic SVN Updates for Offsite Backup

My 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.

Conditional Check Constraints on SQL Server

I 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
(
  Id TINYINT,
  Name VARCHAR(50),
  RecordStatus TINYINT
)
GO

CREATE FUNCTION CheckActiveCount(
  @Id INT
) RETURNS INT AS BEGIN
  
  DECLARE @ret INT;
  SELECT @ret = COUNT(*) FROM CheckConstraint WHERE Id = @Id AND RecordStatus = 1;
  RETURN @ret;

END;
GO

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;

Extension Method to Imitate Upto() in Ruby

Last 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

Stored Procedure to Unpivot a Table

When 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:

CREATE PROCEDURE UnpivotTable
(
  @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
DECLARE @query VARCHAR(MAX);

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

    UNION ALL

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

-- execute the query
EXEC(@query);

For the sake of example, I'm going to need a test table. Here's the one I'm using:

CREATE TABLE Pals
(
  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

I 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:

CREATE FUNCTION ConvertFromBase
(
    @value AS VARCHAR(MAX),
    @base AS BIGINT
) RETURNS BIGINT AS BEGIN

    -- 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) * 
                         (CHARINDEX
                            (SUBSTRING(@value, LEN(@value) - @index, 1)
                            , @characters) - 1
                         ),
               @index = @index + 1;

    -- return the result
    RETURN @result;

END

Here's a test query:

SELECT
   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

Yesterday, 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:

CREATE FUNCTION ConvertToBase
(
    @value AS BIGINT,
    @base AS INT
) RETURNS VARCHAR(MAX) AS BEGIN

    -- 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;

END

Here is a test query:

SELECT
   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