Category Archives: Code

Software Development

sp_WhoLock – a T-SQL stored proc combining sp_who and sp_lock to show who’s locking what and how much

Run this query, or remove the comments to actually create a stored proc.
Note that SQL pre-2008 requires a field to be commented out of the query, as noted inline.

/*
--Create Procedure WhoLock
--AS

if object_id('tempdb..#locksummary') is not null Drop table #locksummary
if object_id('tempdb..#lock') is not null Drop table #lock
create table #lock (    spid int,    dbid int,    objId int,    indId int,    Type char(4),    resource nchar(32),    Mode char(8),    status char(6))
Insert into #lock exec sp_lock
if object_id('tempdb..#who') is not null Drop table #who
create table #who (     spid int, ecid int, status char(30),
            loginame char(128), hostname char(128),
            blk char(5), dbname char(128), cmd char(16)
            --
            , request_id INT --Remove this line for SQL 2005 or earlier
            --
         )
Insert into #who exec sp_who
Print '-----------------------------------------'
Print 'Lock Summary for ' + @@servername  + ' (excluding tempdb):'
Print '-----------------------------------------' + Char(10)
Select     left(loginame, 28) as loginame, 
    left(db_name(dbid),128) as DB,
    left(object_name(objID),30) as object,
    max(mode) as [ToLevel],
    Count(*) as [How Many],
    Max(Case When mode= 'X' Then cmd Else null End) as [Xclusive lock for command],
    l.spid, hostname
into #LockSummary
from #lock l join #who w on l.spid= w.spid
where dbID != db_id('tempdb') and l.status='GRANT'
group by dbID, objID, l.spid, hostname, loginame

Select * from #LockSummary order by [ToLevel] Desc, [How Many] Desc, loginame, DB, object

Print '--------'
Print 'Who is blocking:'
Print '--------' + char(10)
SELECT p.spid
,convert(char(12), d.name) db_name
, program_name
, p.loginame
, convert(char(12), hostname) hostname
, cmd
, p.status
, p.blocked
, login_time
, last_batch
, p.spid
FROM      master..sysprocesses p
JOIN      master..sysdatabases d ON p.dbid =  d.dbid
WHERE     EXISTS (  SELECT 1
          FROM      master..sysprocesses p2
          WHERE     p2.blocked = p.spid )

Print '--------'
Print 'Details:'
Print '--------' + char(10)
Select     left(loginame, 30) as loginame,  l.spid,
    left(db_name(dbid),15) as DB,
    left(object_name(objID),40) as object,
    mode ,
    blk,
    l.status
from #lock l join #who w on l.spid= w.spid
where dbID != db_id('tempdb') and blk <>0
Order by mode desc, blk, loginame, dbID, objID, l.status

Although this is a bare metal approach to investigating sql contention, the query does show you actual login names, so it enables you to join up technical issues with affected/affecting customers.

F# on Mac and Linux

is not, in 2012, in a great state. There are 2 not-great options and I add here a third, also not-great option. It has the advantage of working, and not requiring you to learn emacs. (Jump straight to F# with Sublime)

F# on MonoDevelop

The F# language bindings for MonoDevelop broke after version 2.4. You can get MonoDevelop version 2.4 by consulting the wayback machine for the MonoDevelop Download page at July 2011. You can then use the fsharpbindings and instructions from http://functional-variations.net/monodevelop/.
Personally I fould MonoDeveloper before 2.6 more prone to crashing, and I couldn’t get 2.4 working with FSharp.

F# on Aquamacs

If you’re an emacs fan, then you won’t need my help to find and install the f# mode for it.
For emacs newbies on the Mac with a day to burn on the learning curve, a couple of the answers on stackover gives you some clue as to how to get fsharp working:
http://stackoverflow.com/questions/1210125/is-there-any-f-ide-that-works-over-mono
http://stackoverflow.com/questions/2120533/how-to-setup-aquamacs-for-clojure-development


F# with Sublime Text 2 (Quick and Dirty)


This was the best I could do in 2 hours:

  1. Get Sublime Text 2
  2. In the Sublime text menu : Tools – Build System – New Build System
    {
    	"cmd": ["/usr/bin/fsharpcandrun", "$file"]
    }

    and save as fsharp.sublime-build .

  3. At a terminal command line, use your favourite editor to save this file as /usr/bin/fsharpcandrun :
    #!/bin/bash
    fsharpc "$1" && nameafterquote=${1#\"} && exe=${nameafterquote%.*}.exe && mono "$exe" && echo done
  4. Make it executable, again from a terminal command line:
    sudo chmod a+x /usr/bin/fsharpcandrun

    The password that sudo asks your for is your login password.

  5. If your new favourite editor is in fact Sublime, you can save files in directories requiring sudo-ed permissions by putting a command line alias for sublime in your ~/.bash_profile script:
    alias edit='open -a /Applications/Sublime\ Text\ 2.app/Contents/MacOS/Sublime\ Text\ 2'

    and typing edit foo.txt to open Sublime

  6. Choose Tool — build system — fsharp in Sublime
  7. Now, <f7> your file. It should compile and run. At least, it did for me.
  8. Get some kind of syntax colouring by creating a .fs file, and then choosing View — Syntax — Open all with current extension as … OCaml. This is because F# is near enough to OCaml for the colouring to be pretty good.

I wouldn’t call this great, but it will do for the moment. It only “builds” a single file, which is hardly a build system. But it does give you syntax colouring and compile-and-run for learning F#.

The next step forwards might be to modify the bash script to fsharpc a list of files, or all files in the directory of the file to be run.

Web Forms – Mocking HttpSession

With thanks to http://stackoverflow.com/users/603670/ben-barreth
at http://stackoverflow.com/questions/1981426/how-do-i-mock-fake-the-session-object-in-asp-net-web-forms

[SetUp]
public void SetUpHttpSessionMock()
{
HttpWorkerRequest _wr = new SimpleWorkerRequest("/dummyWorkerRequest", @"c:\inetpub\wwwroot\dummy", "default.aspx", null, new StringWriter());
HttpContext.Current = new HttpContext(_wr);
HttpSessionStateContainer sessionContainer = new HttpSessionStateContainer("id", new SessionStateItemCollection(), new HttpStaticObjectsCollection(), 10, true, HttpCookieMode.AutoDetect, SessionStateMode.InProc, false);
SessionStateUtility.AddHttpSessionStateToContext(HttpContext.Current, sessionContainer);
}

Refactoring a static class with hard-coded dependencies for testability

is not that hard. You can add a factory method to the static class to create the dependency, and change the factory method at test-time.
Here’s an example:

public static class WithDependencies
{
	public static string MethodWithDependencies()
	{
		using (var thing = new HardCodedThing())
		{
			return DoSomething();
		}
	}
}

which can be turned into:

public static class WithDependencies
{
    public static Func<HardCodedThing> CreateHardCodedThing = () => new HardCodedThing();

	public static void MethodWithDependencies()
	{
		using (var thing = CreateHardCodedThing())
		{
			DoSomething();
		}
	}
}

With this code in your test:

[TestFixture]
public class WhenDoingSomething
{
	private Mock<HardCodedThing> mockThing;

	[SetUp]
	public void SetUpMockThing()
	{
		// mockThing.Setup( ...  ) ... etc ...
	}

	public void Given_hardcodedthing_does_X_should_get_Y()
	{
		//Arrange
		WithDependencies.CreateHardCodedThing = () => mockThing.Object;
		//Act
		var result= WithDependencies.MethodWithDependencies();
		//Assert
		Assert.AreEqual("Y", result);
	}
}

Code Kata One as Code – Supermarket Pricing

The code kata on supermarket pricing is one we wanted to do because we have some interest in pricing rules. However, it’s written as a design exercise (which is a good thing), whereas we still wanted to do some coding.
So :

The Checkout Pricing Kata

Some things in supermarkets have simple prices: this can of beans costs £0.20. Other things have more complex prices. For example:
• three for a £ (so what’s the price if I buy 4, or 5?)
• £1.99/pound (so what does 4 ounces cost?)
• buy two, get one free (so does the third item have a price?)
Here’s an example stock list with pricing and rules

Stock Pricing
Baked Beans: 20p per can, with a three-for-two offer.
Bananas: £1 per Kg
Bagged Bananas: £1.20 per bag
Beer: £1.50 per bottle, with a three-for-£4 offer
Bagels: £3 per dozen or £2.00 per half-dozen or 50p each
Kitchen Roll: 50p each
Beer ‘n’ Beans Cleanup Offer: Buy 3 cans of beans, 3 bottles of beer and get one free kitchen roll (not combinable with any other offer).
Beans ‘n’ Bagel Breakfast Offer: Get 6 cans of beans and a dozen bagels for £3.50 (not combinable with any other offer).

Checkout Pricing

Write something which, given a list of items purchased, will print out a priced and itemised receipt, with weights shown where relevant and all discount rules correctly applied. The customer should not be able to get a better price by re-organising or splitting up the shopping basket. The receipt should help the uncertain customer to see this.
In TDD style, do it by writing code to pass tests for a list of increasingly complex requirements.

Example shopping baskets

• 1 can of beans, and 1 bottle of beer
• 6 can of beans, and 3 bottles of beer
• 5 cans of beans and 1.4 kg of loose bananas
• 3kg of bananas and 7 bagels
• 4 cans of beans, 3 bottles of beer and a kitchen roll
• 10 cans of beans, 15 bagels, 4 bottles of beer, 2 kitchen rolls, 3.5 kg loose plus 1 bag of bananas.

I leave to the retail stategists amongst you the question, “Should it always be impossible for the customer to get a cheaper price by adding something to their basket?”