Inspect Object Locking with T-SQL

I recently came across a scenario whereby we wanted to programmatically, via T-SQL, inspect locking behaviour on objects in a particular database when operations were being performed on given data (actually just a SELECT (WITH (NOLOCK) and DELETE statement, on a table). The requirement was to get a direct view on whether the table was having an exclusive lock thrust upon it, or whether shared access was still in play (and get an insight into whether we were in the realms of row-level locking or not).

This turns out to be relatively easy through the use of the inbuilt Dynamic Management Views; specifically the sys.dm_tran_locks view, coupled with a cheeky join to sys.objects for information to identify the object being referenced (this is my own interpretation, from scratch, but you can easily find many similar examples so pick what floats your boat!):

/*
	Interrogate the lock types at database level (can see the lock type on a table when another statement is running)
	-------------------------------------------------------------------------------------------------------------------

	1) For information on the different lock modes in SQL Server visit the following resources:
		a) https://technet.microsoft.com/en-us/library/ms175519(v=sql.105).aspx
		b) http://lockergnome.com/2010/01/26/what-are-the-different-lock-modes-in-sql-server/
		c) https://logicalread.com/sql-server-lock-modes-mc03/ - Haven't actually read this one yet but looked intriguing enough to include as a 'bonus' :o)
	2) An interesting aside into the WITH (NOLOCK) hint: http://sqlsoldier.net/wp/sqlserver/thetruthaboutnolockhints
	3) The difference by IX and X locks: http://database.ittoolbox.com/groups/technical-functional/sql-server-l/difference-between-x-and-ix-lock-151903
*/
SELECT 
tl.resource_type AS [ResourceType]
, CASE 
	WHEN tl.request_mode = 'Sch-S'
	THEN 'Schema Stability (ensure entity cannot be dropped)'
	WHEN tl.request_mode = 'IS'
	THEN 'Intent Shared'
	WHEN tl.request_mode = 'IX'
	THEN 'Intent Exclusive (exclusive lock to subordinate resource in the lock hierarchy - table can still be accessed in part)'
	WHEN tl.request_mode = 'X'
	THEN 'Exclusive (full, exclusive access to the resource)'
	ELSE tl.request_mode
END AS [LockType]
, so.[object_id] AS [ObjectId] 
, so.name AS [ObjectName]
, so.type_desc AS [TypeDesc]
FROM sys.dm_tran_locks tl
	-- JOIN to sys.objects to get identifiable information on the resource marked with a lock
	INNER JOIN sys.objects so ON tl.resource_associated_entity_id = so.object_id
-- Show lock information for the database in scope (this could be parameterised - this could be placed in an stored procedure, for example)
WHERE tl.resource_database_id = DB_ID();

In the comments above the code snippet, I’ve included a couple of links to detail the various lock types (information that can be shown in the request_mode view column) that you may encounter. Just run the query whilst a transaction that you want to inspect (for locking activity) is in progress, and voila, you’ll get an idea as to how the resources are being accessed and what impact this is having locking wise.

As a quick illustration let’s knock up a test database and table, using the following script which incorporates a very trivial setup:

-- Test script for checking out the dm_tran_locks test script

-- 1) Create the test database
CREATE DATABASE [LockTestDatabase];
GO

USE [LockTestDatabase];

-- 2) Create the LockTest table within the LockTestDatabase database (super noddy example for illustration only)
CREATE TABLE [dbo].[LockTest]
(
	Id				INT				PRIMARY		KEY	IDENTITY(1, 1)	NOT NULL
	, SomeInfo		NVARCHAR(20)									NOT NULL
);

-- 3) Create some simple test data
DECLARE
@Counter		INT = 1;

WHILE (@Counter <= 250000)		-- Want a non-trivial amount of data so queries take a couple of seconds to complete
BEGIN

	PRINT (@Counter);

	INSERT INTO [dbo].[LockTest]
	(
		SomeInfo
	)
	VALUES
	(
		'Information' + CONVERT(NVARCHAR(6), @Counter)
	);

	SET 
	@Counter += 1;

END;

The insertion of test records takes a short while so it’s possible to run the dm_tran_locks test script and obtain the following results:

LockTest Table Lock State during Insert.

LockTest Table Lock State during Insert.

After the test insertions are complete, attempt to run a SELECT statement using the WITH (NOLOCK) hint:

SELECT 
lt.Id
, lt.SomeInfo
FROM dbo.LockTest lt WITH (NOLOCK);

You should hopefully observe a shared resource lock, for schema stability only, that essentially just prevents the resource from being dropped whilst information is being requested from the table:

LockTest Table Lock State during SELECT (WITH (NOLOCK)).

LockTest Table Lock State during SELECT (WITH (NOLOCK)).

I’m not going into any kind of detailed explanation with regards to the lock types here, I’m just proving that observing the lock types being applied is easily possible. Lastly, attempt a deletion (wrapped in a rollback, so it can easily be re-run):

BEGIN TRANSACTION

	DELETE
	FROM dbo.LockTest;

ROLLBACK TRANSACTION

A lock is applied that indicates exclusive access to the resource (due to the nature of the delete here, as all rows are being removed), aka table:

LockTest Table Lock State during Deletion.

LockTest Table Lock State during Deletion.

This gives you an insight into how straightforward it is to get an impression on how your SQL objects are being influenced, locking wise, by your code. If anyone has any suggestions on further reading, more useful snippets, etc. then please add a comment below. Thanks, all.

T-SQL Grouping Set Showcase

Greetings all,

In the interim, whilst I juggle my time to get more in-depth posts rolled out (like the one surrounding Alexa skills), I back-tracked once again to look at overlooked features from the distant past of SQL 2008. I’ve chosen the topic of GROUPING SETS as the basis of this post; which basically boils down to a really nice way to return multiple levels of aggregation in a single result set.

A quick run through then – the following example illustrates a sample database with four tables; Staff, Perk, HolidayRequest and SalaryPayment. The HolidayRequest table has a computed column (based on ‘DateFrom’ and ‘DateTo’) that specifies how many days holiday have been requested (per request) and the SalaryPayment table has an ‘Amount’ column (salary per month). These represent our key aggregation fields. I haven’t actually referenced all columns in the samples provided; I just wanted to give the examples a little bit of extra padding (so they feel more complete).

The following T-SQL shows aggregations targeting total holiday days requested grouped by staff name, department and department and job title (see the GetHolidayRequestDaysSumInfo stored procedure in the full listing for further details):

-- Holiday request days (summed), grouped by staff name (two daves are cobbled together here)
SELECT 
s.FirstName + ' ' + s.LastName AS [StaffName]
, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
FROM dbo.Staff s
	INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
GROUP BY s.FirstName + ' ' + s.LastName;

-- Holiday request days (summed), grouped by department
SELECT 
s.Department
, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
FROM dbo.Staff s
	INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
GROUP BY Department;

-- Holiday request days (summed), grouped by department and job title
SELECT
s.Department
, s.JobTitle
, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
FROM dbo.Staff s
	INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
GROUP BY s.Department, s.JobTitle;

The results look like this:

Holiday Request Group Queries.

Holiday Request Group Queries.

With traditional GROUP BY statements you need to split aggregations, so you can only perform one per result set. This is where GROUPING SETS come in handy; here is a single statement that returns the results from the statements above, but in a single result set (again, see the GetHolidayRequestDaysSumInfoUsingGroupingSets for more information, within the full listing):

-- Holiday request days (summed), group sets included for staff name, department and department and job title
SELECT 
s.FirstName + ' ' + s.LastName AS [StaffName]
, s.Department
, s.JobTitle
, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
FROM dbo.Staff s
	INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
GROUP BY GROUPING SETS
(
	(s.FirstName + ' ' + s.LastName)
	, (s.Department)
	, (s.Department, s.JobTitle)
);

Again, here are the super fabulous results from this particular query:

Holiday Request Grouping Set Queries.

Holiday Request Grouping Set Queries.

Pretty neat! Just to nail the point home here are a few extra examples focusing on the summing of salary payments, grouped by various facets (individual statements, for starters, as shown in the GetSalarySumInfo procedure):

-- Salary sum info grouped by staff name (two daves are, of course, grouped by this)
SELECT 
s.FirstName + ' ' + s.LastName AS [StaffName]
, SUM(sp.Amount) AS [TotalPay]
FROM dbo.Staff s
	INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
GROUP BY s.FirstName + ' ' + s.LastName;

-- Salary sum info grouped by job title
SELECT 
s.JobTitle
, SUM(sp.Amount) AS [TotalPay]
FROM dbo.Staff s
	INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
GROUP BY s.JobTitle;

-- Salary sum info grouped by department
SELECT 
s.Department
, SUM(sp.Amount) AS [TotalPay]
FROM dbo.Staff s
	INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
GROUP BY s.Department

-- Salary sum info grouped by perk name
SELECT 
p.PerkName
, SUM(sp.Amount) AS [TotalPay]
FROM dbo.Staff s
	INNER JOIN dbo.Perk p ON s.Perk = p.Id
	INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
GROUP BY p.PerkName;

-- Salary sum info grouped by department and job title
SELECT
s.Department
, s.JobTitle
, SUM(sp.Amount) AS [TotalPay]
FROM dbo.Staff s
	INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
GROUP BY s.Department, s.JobTitle;

Results from our individual statements look a little like this:

Salary Group Queries.

Salary Group Queries.

Lastly, here’s the equivalent, single, query using GROUPING SETS (see GetSalarySumInfoUsingGroupingSets in the full listing):

-- Salary sum info grouped by staff name, department, job title, perk name and department/job title, in a single result set
SELECT 
s.FirstName + ' ' + s.LastName AS [StaffName]
, s.Department
, s.JobTitle
, p.PerkName
, SUM(sp.Amount) AS [TotalPay]
FROM dbo.Staff s
	INNER JOIN dbo.Perk p ON s.Perk = p.Id
	INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
GROUP BY GROUPING SETS
(
	(s.FirstName + ' ' + s.LastName)
	, (s.JobTitle)
	, (s.Department)
	, (p.PerkName)
	, (s.Department, s.JobTitle)
);
Salary Grouping Set Queries.

Salary Grouping Set Queries.

A great little language feature that I have largely overlooked in the past. I do have one or two reservations over performance, but a first inspection of the query plan for the ‘salary’ based example suggests that some marginal gains are on the table, using the stock setup I have (note the table spool, also):

Grouping Sets Performance.

Grouping Sets Performance.

Feels to me like something that deserves further investigation.

Here is the complete listing, if you wish to create the tables with the sample data referenced in this post (procedures, and all, bundled in). Thanks for tuning in!

/* 

	Lewis Grint - 18/03/2017

	GROUPING SETS WORKING SAMPLE 
	-------------------------------------------------
	1) This example uses four mock database tables (created internally by the script, no work required :op). Run this on a test database of your choice.
		- Staff.
		- Perk.
		- HolidayRequest.
		- SalaryPayment.
	2) Utility, mock data is also inserted by this script (table drop statements, if required, can be found just below this header).
	3) The sample data is then aggregated using GROUP BY via two traditionally structured stored procedures (GetHolidayRequestDaysSumInfo and GetSalarySumInfo). Data is returned (for each grouping) using a separate result set.
	4) Stored procedure variants are then presented that using GROUPING SETS - All GROUP BY variations are returned in a single result set (for illustration purposes only, not saying my grouping selections are actually that helpful in the real world!).

*/

/*
-- Drop table statements, as and when required
DROP TABLE dbo.HolidayRequest;
DROP TABLE dbo.SalaryPayment;
DROP TABLE dbo.Staff;
DROP TABLE dbo.Perk;

*/

-- Habitual setting of preferred QUOTED_IDENTIFIER/ANSI_NULL configuration (even if it's not important for this example)
SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_NULLS ON;
GO

-- Utility variables for gathering up the correct StaffId, when required (and payment dates)
DECLARE
@StaffId							INT
, @FebPayDate						DATE = '2017-02-28'
, @JanPayDate						DATE = '2017-01-31'
, @DecPayDate						DATE = '2016-12-31';

-- 1) Create sample tables to illustrate how GROUPING SETS work

-- Perk table added for nothing more than extra kicks. A staff member can select a company perk (referenced from the Staff table)
IF (OBJECT_ID('dbo.Perk', 'U') IS NULL)
BEGIN

	CREATE TABLE dbo.Perk
	(
		Id							INT				PRIMARY KEY		IDENTITY(1, 1)	NOT NULL
		, PerkName					NVARCHAR(30)									NOT NULL
		, INDEX IX_Perk_PerkName	NONCLUSTERED (PerkName)					
	);

END;

-- NOTE: I won't be including more indexes from this point forward, to try and keep the example light(er)

-- Staff table, which for the sake of our example contains first name, last name, job title, department and a fk to 'perks'
IF (OBJECT_ID('dbo.Staff', 'U') IS NULL)
BEGIN

	CREATE TABLE dbo.Staff
	(
		Id							INT				PRIMARY KEY		IDENTITY(1, 1)	NOT NULL
		, FirstName					NVARCHAR(50)									NOT NULL
		, LastName					NVARCHAR(50)									NOT NULL
		, JobTitle					NVARCHAR(50)									NOT NULL
		, Department				NVARCHAR(50)									NOT NULL
		, Perk						INT												NOT NULL
		, CONSTRAINT FK_Staff_Perk	FOREIGN KEY (Perk) REFERENCES dbo.Perk (Id)
	);

END;

-- SalaryPayment table, which contains multiple rows for each staff member (one for each salary payment)
IF (OBJECT_ID('dbo.SalaryPayment', 'U') IS NULL)
BEGIN

	CREATE TABLE dbo.SalaryPayment
	(
		Id							INT				PRIMARY KEY		IDENTITY(1, 1)	NOT NULL
		, StaffId					INT												NOT NULL
		, PaymentDate				DATE											NOT NULL
		, Amount					MONEY
		, CONSTRAINT FK_SalaryPayment_Staff			FOREIGN KEY (StaffId) REFERENCES dbo.Staff (Id)
	);

END;

-- HolidayRequest table, which contains multiple rows for each staff member (one for each holiday request)
IF (OBJECT_ID('dbo.HolidayRequest', 'U') IS NULL)
BEGIN

	CREATE TABLE dbo.HolidayRequest
	(
		Id							INT				PRIMARY KEY		IDENTITY(1, 1)	NOT NULL
		, StaffId					INT												NOT NULL
		, DateFrom					DATE											NOT NULL
		, DateTo					DATE											NOT NULL
		-- Final column is computed based on the difference between the start and end date (requested), in days. Simple, but fits for the purposes of the example (obviously, doesn't take into account weekends, bank holiday, etc.)
		, NumberOfDaysRequested		AS (DATEDIFF(DAY, DateFrom, DateTo) + 1)
		, CONSTRAINT FK_HolidayRequest_Staff		FOREIGN KEY (StaffId) REFERENCES dbo.Staff (Id)
	);

END;

--2) Insert test data into each table

-- Perk table test data
IF NOT EXISTS
(
	SELECT *
	FROM dbo.Perk p
)
BEGIN

	INSERT INTO dbo.Perk
	(
		PerkName
	)
	VALUES 
	(
		'Free ice cream'
	)
	,
	(
		'Free parking'
	)
	,
	(
		'Beer on arrival'
	)
	,
	(
		'Pat on the back'
	);

END;

-- Staff table test data (including perk info)
IF NOT EXISTS
(
	SELECT *
	FROM dbo.Staff s
)
BEGIN

	INSERT INTO dbo.Staff
	(
		FirstName
		, LastName
		, JobTitle
		, Department
		, Perk	
	)
	VALUES
	(
		'Steve'
		, 'Stevenson'
		, 'Head Honcho'
		, 'Ivory Tower'
		, 
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Free ice cream'
		)
	)
	,
	(
		'Marie'
		, 'Pritchard'
		, 'Team Manager'
		, 'Ivory Tower'
		, 
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Free parking'
		)
	)
	,
	(
		'Judy'
		, 'Dench'
		, 'Team Manager'
		, 'Island Retreat'
		, 
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Free ice cream'
		)
	)
	,
	(
		'Dave'
		, 'Dodger'
		, 'Chief Work Dodger'
		, 'Store Cupboard'
		, 
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Beer on arrival'
		)
	)
	-- There's another Dave Dodger at the company, but he is actually a pretty useful chap
	,
	(
		'Dave'
		, 'Dodger'
		, 'Hard Worker'
		, 'Store Cupboard'
		, 
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Pat on the back'
		)
	)
	,
	(
		'Bob'
		, 'Boots'
		, 'Handle Cranker'
		, 'Main Office'
		,
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Pat on the back'
		)
	)
	,
	(
		'Janet'
		, 'Timms'
		, 'Handle Cranker'
		, 'Main Office'
		, 
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Pat on the back'
		)
	);

END;

-- SalaryPayment table test data
IF NOT EXISTS
(
	SELECT *
	FROM dbo.SalaryPayment sp
)
BEGIN
	
	-- Steve Stevenson | Head Honcho
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Steve'
		AND s.LastName = 'Stevenson'
		AND s.JobTitle = 'Head Honcho'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 5580.50
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 5240.50
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 5580.50
	);

	-- Marie Pritchard | Team Manager
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Marie'
		AND s.LastName = 'Pritchard'
		AND s.JobTitle = 'Team Manager'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 2500.75
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 2425.15
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 2425.15
	);

	-- Judy Dench | Team Manager
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Judy'
		AND s.LastName = 'Dench'
		AND s.JobTitle = 'Team Manager'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 2495.75
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 2400.15
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 2400.15
	);

	-- Dave Dodger | Chief Work Dodger
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Dave'
		AND s.LastName = 'Dodger'
		AND s.JobTitle = 'Chief Work Dodger'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 2122.90
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 2105.20
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 2105.20
	);

	-- Dave Dodger | Hard Worker
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Dave'
		AND s.LastName = 'Dodger'
		AND s.JobTitle = 'Hard Worker'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 2115.50
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 2100.50
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 2100.50
	);

	-- Bob Boots | Handle Cranker
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Bob'
		AND s.LastName = 'Boots'
		AND s.JobTitle = 'Handle Cranker'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 2100.00
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 2039.50
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 2039.50
	);

	-- Janet Timms | Handle Cranker
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Janet'
		AND s.LastName = 'Timms'
		AND s.JobTitle = 'Handle Cranker'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 2100.00
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 2039.50
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 2039.50
	);
	
END;

-- HolidayRequest table test data
IF NOT EXISTS
(
	SELECT *
	FROM dbo.HolidayRequest hr
)
BEGIN

	-- For ease, everyone has three holiday requests
	
	-- Steve Stevenson | Head Honcho
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Steve'
		AND s.LastName = 'Stevenson'
		AND s.JobTitle = 'Head Honcho'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-01-30'
		, '2017-02-03'
	)
	,
	(
		@StaffId
		, '2017-05-22'
		, '2017-05-26'
	)
	,
	(
		@StaffId
		, '2017-07-24'
		, '2017-07-28'
	);

	-- Marie Pritchard | Team Manager
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Marie'
		AND s.LastName = 'Pritchard'
		AND s.JobTitle = 'Team Manager'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-03-23'
		, '2017-03-24'
	)
	,
	(
		@StaffId
		, '2017-06-12'
		, '2017-06-16'
	)
	,
	(
		@StaffId
		, '2017-08-14'
		, '2017-08-14'
	);

	-- Judy Dench | Team Manager
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Judy'
		AND s.LastName = 'Dench'
		AND s.JobTitle = 'Team Manager'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-03-24'
		, '2017-03-25'
	)
	,
	(
		@StaffId
		, '2017-06-12'
		, '2017-06-15'
	)
	,
	(
		@StaffId
		, '2017-08-15'
		, '2017-08-16'
	);

	-- Dave Dodger | Chief Work Dodger
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Dave'
		AND s.LastName = 'Dodger'
		AND s.JobTitle = 'Chief Work Dodger'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-03-20'
		, '2017-03-24'
	)
	,
	(
		@StaffId
		, '2017-03-27'
		, '2017-03-31'
	)
	,
	(
		@StaffId
		, '2017-04-03'
		, '2017-04-07'
	);

	-- Dave Dodger | Hard Worker
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Dave'
		AND s.LastName = 'Dodger'
		AND s.JobTitle = 'Hard Worker'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-03-31'
		, '2017-03-31'
	)
	,
	(
		@StaffId
		, '2017-07-31'
		, '2017-07-31'
	)
	,
	(
		@StaffId
		, '2017-09-25'
		, '2017-09-25'
	);

	-- Bob Boots | Handle Cranker
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Bob'
		AND s.LastName = 'Boots'
		AND s.JobTitle = 'Handle Cranker'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-05-15'
		, '2017-05-19'
	)
	,
	(
		@StaffId
		, '2017-07-10'
		, '2017-07-12'
	)
	,
	(
		@StaffId
		, '2017-10-11'
		, '2017-10-13'
	);

	-- Janet Timms | Handle Cranker
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Janet'
		AND s.LastName = 'Timms'
		AND s.JobTitle = 'Handle Cranker'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-04-10'
		, '2017-04-11'
	)
	,
	(
		@StaffId
		, '2017-06-21'
		, '2017-06-23'
	)
	,
	(
		@StaffId
		, '2017-11-13'
		, '2017-11-17'
	);

END;
GO

-- Create utility stored procedures, just because :op

-- Basic example using the HolidayRequest table
IF (OBJECT_ID('dbo.GetHolidayRequestDaysSumInfo', 'P') IS NOT NULL)
BEGIN

	DROP PROCEDURE dbo.GetHolidayRequestDaysSumInfo;

END;
GO

CREATE PROCEDURE dbo.GetHolidayRequestDaysSumInfo
AS
BEGIN

	-- Holiday request days (summed), grouped by staff name (two daves are cobbled together here)
	SELECT 
	s.FirstName + ' ' + s.LastName AS [StaffName]
	, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
	FROM dbo.Staff s
		INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
	GROUP BY s.FirstName + ' ' + s.LastName;

	-- Holiday request days (summed), grouped by department
	SELECT 
	s.Department
	, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
	FROM dbo.Staff s
		INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
	GROUP BY Department;

	-- Holiday request days (summed), grouped by department and job title
	SELECT
	s.Department
	, s.JobTitle
	, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
	FROM dbo.Staff s
		INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
	GROUP BY s.Department, s.JobTitle;

END;
GO

IF (OBJECT_ID('dbo.GetHolidayRequestDaysSumInfoUsingGroupingSets', 'P') IS NOT NULL)
BEGIN

	DROP PROCEDURE dbo.GetHolidayRequestDaysSumInfoUsingGroupingSets;

END;
GO

CREATE PROCEDURE dbo.GetHolidayRequestDaysSumInfoUsingGroupingSets
AS
BEGIN

	-- Holiday request days (summed), group sets included for staff name, department and department and job title
	SELECT 
	s.FirstName + ' ' + s.LastName AS [StaffName]
	, s.Department
	, s.JobTitle
	, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
	FROM dbo.Staff s
		INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
	GROUP BY GROUPING SETS
	(
		(s.FirstName + ' ' + s.LastName)
		, (s.Department)
		, (s.Department, s.JobTitle)
	);

END;
GO

-- A couple more stored prodecures, focusing on the SalaryPayment table this time
IF (OBJECT_ID('dbo.GetSalarySumInfo', 'P') IS NOT NULL)
BEGIN

	DROP PROCEDURE dbo.GetSalarySumInfo;

END;
GO

CREATE PROCEDURE dbo.GetSalarySumInfo
AS
BEGIN

	-- Salary sum info grouped by staff name (two daves are, of course, grouped by this)
	SELECT 
	s.FirstName + ' ' + s.LastName AS [StaffName]
	, SUM(sp.Amount) AS [TotalPay]
	FROM dbo.Staff s
		INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
	GROUP BY s.FirstName + ' ' + s.LastName;

	-- Salary sum info grouped by job title
	SELECT 
	s.JobTitle
	, SUM(sp.Amount) AS [TotalPay]
	FROM dbo.Staff s
		INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
	GROUP BY s.JobTitle;

	-- Salary sum info grouped by department
	SELECT 
	s.Department
	, SUM(sp.Amount) AS [TotalPay]
	FROM dbo.Staff s
		INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
	GROUP BY s.Department

	-- Salary sum info grouped by perk name
	SELECT 
	p.PerkName
	, SUM(sp.Amount) AS [TotalPay]
	FROM dbo.Staff s
		INNER JOIN dbo.Perk p ON s.Perk = p.Id
		INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
	GROUP BY p.PerkName;

	-- Salary sum info grouped by department and job title
	SELECT
	s.Department
	, s.JobTitle
	, SUM(sp.Amount) AS [TotalPay]
	FROM dbo.Staff s
		INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
	GROUP BY s.Department, s.JobTitle;

END;
GO

IF (OBJECT_ID('dbo.GetSalarySumInfoUsingGroupingSets', 'P') IS NOT NULL)
BEGIN

	DROP PROCEDURE dbo.GetSalarySumInfoUsingGroupingSets;

END;
GO

CREATE PROCEDURE dbo.GetSalarySumInfoUsingGroupingSets
AS
BEGIN

	-- Salary sum info grouped by staff name, department, job title, perk name and department/job title, in a single result set
	SELECT 
	s.FirstName + ' ' + s.LastName AS [StaffName]
	, s.Department
	, s.JobTitle
	, p.PerkName
	, SUM(sp.Amount) AS [TotalPay]
	FROM dbo.Staff s
		INNER JOIN dbo.Perk p ON s.Perk = p.Id
		INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
	GROUP BY GROUPING SETS
	(
		(s.FirstName + ' ' + s.LastName)
		, (s.JobTitle)
		, (s.Department)
		, (p.PerkName)
		, (s.Department, s.JobTitle)
	);

END;
GO

-- Execute stored procedures to inspect the results

-- Holiday aggregation results (grouping sets stored procedure variant returns all data in a single result set)
EXEC dbo.GetHolidayRequestDaysSumInfo;
EXEC dbo.GetHolidayRequestDaysSumInfoUsingGroupingSets;

-- Salary aggregation results (grouping sets stored procedure variant returns all data in a single result set)
EXEC dbo.GetSalarySumInfo;
EXEC dbo.GetSalarySumInfoUsingGroupingSets;

OpenCover UI – Unit Test Code Coverage

A little sideline post to tide everyone over (as I’m still working on the Alexa piece, which I want to do proper justice to when it’s released). I’ve been messing around with a few rough and ready projects and wanted to get an idea of how to dig into code coverage, in respect of Unit Tests.

I’m currently using Visual Studio 2015 Community Edition and from what I gather no built-in support exists for non-enterprise editions, at the moment. The first hit I found was for the OpenCover UI extension; so I thought I’d take it for a spin to see what it’s made of:

Stack Overflow OpenCover UI Mention

Just so that you can get a feel for where I am at, here is an image outlining a home-brew project that shows some Unit Tests in play:

Unit Test Structure.

Unit Test Structure.

Nothing too miraculous here, I’m just using the standard Unit Testing framework and a little Moq for kicks. To follow this up, I then grabbed hold of the OpenCover UI (.vsix extension) from here and installed it:

OpenCover UI VS Marketplace Link

Let’s roll on from here with some ‘off the cuff’ observations, rather than in-depth review of features, etc. This serves as simply my first impressions and, ultimately, an insight into if we can get the coverage metrics I am after quickly and easily. For starters, you’ll notice a new context menu for ‘OpenCover’ when Visual Studio boots up:

OpenCover Menu.

OpenCover Menu.

I have quickly shoved the inbuilt Test Explorer window next to the OpenCover variant; they appear to offer a similar ‘look and feel’, in addition to functional grouping options (the default Test Explorer windows appears to have a few more options, in fact). The OpenCover Test Explorer oddly doesn’t have a ‘Run’ or ‘Run All’ tests buttons, on the face of it anyway (or debugging options). Right clicking a test gives a ‘Cover with OpenCover’ context menu option…guess I’ll see what that does now!

Cover with OpenCover Context Menu Option.

Cover with OpenCover Context Menu Option.

At this point I hit the following, immediate, explosion:

Open Cover EXE Error.

OpenCover EXE Error.

You then get prompted to hunt down the relevant .exe file. As I was fishing around for this I decided to go back to the trusty Stack Overflow, to see what wisdom could be uncovered. This was the first hit, which outlined that a configuration file, with set content, needed to be stuffed in with the solution content:

Further Stack Overflow Wisdom

Further comment sniffing did highlight, under Tools > Options, that additional configuration should be performed (i.e. the .exe path should be specified):

Open Cover VS Options.

Open Cover VS Options.

I decided that hunting on NuGet might be the best way to expose an .exe file here (i.e. getting something dropped into a packages directory, which I can easily pick up). So, I followed the hunch by adding this package (just to the ‘tests’ project, for starters, as I wasn’t sure which projects needed targeting):

OpenCover Nuget Package.

OpenCover Nuget Package.

I don’t feel as if we’ve fallen into a rabbit hole just yet, but at this point, I’ve started to wonder if ‘storms’ are on the horizon! Hopefully, we won’t have to tread too much further to get this machine churning. Installing the NuGet package had the desired effect, I have now got the .exe I was looking for lingering in a ‘Tools’ directory, under the OpenCover folder within packages, which I’ve setup in the Visual Studio Options section:

OpenCover EXE Path Configured.

OpenCover EXE Path Configured.

This shouldn’t be marked down as ‘ideal’ configuration, of course; we’re more leaning towards a ‘just get it working’ stance.

The moment of truth…..right clicking and selecting ‘Cover with OpenCover’ now….success! Well, good things appear to have happened anyway, let’s have a quick review to see if we can make sense of it (code with incomplete XML comments is about to be on show, so apologies about that!). I only ran the one test by the way:

Unit Test Code Coverage.

Unit Test Code Coverage.

First observation; it did seem to take a good few seconds before all of the lines covered (green dots) and not covered (red dots) seemed to be highlighted correctly, nothing too catastrophic in this, however. As far as the unit testing specific code goes, here you can clearly see which tests I ran in this instance, the UI pointers are very self-explanatory. One additional observation, it looks like it could be a touch tricky to pick out breakpoints amongst the code coverage markers, but I don’t see this as a big issue at the moment (I’ll have to see how I feel after extended use). In fact, the OpenCover Results window has an option for enabling/disabling these markers, so we’re all good.

You’ll notice that the unit test method denoted here is placing the ‘AddItemCleansingMappingElementToConfiguration’ method under test, so I am keen to see what lines we hit (or ‘covered’) within the targeted method:

Code Actually Under Test Covered.

Code Actually Under Test Covered.

The idea here is that the XML configuration passed to this method is, in fact, malformed so the statement where the ‘addSuccessful’ variable is set to true is not hit (an exception is triggered, and caught, by the preceding line of code); which mirrors the indicator provided by OpenCover, nice! I call this a success!

I’m now going to run this across the board and see what floats to the surface.

Ah, look at this! For starters, OpenCover has highlighted a problem with one of my unit tests in a very solid, visual way (I was wondering why no lines of this test were covered, until I realised I omitted the ‘Test Method’ attribute!):

Missing Test Method Attribute.

Missing Test Method Attribute.

A couple of quirks that deserve to be noted; firstly, I did have to run the ‘Cover with OpenCover’ command twice to register coverage on all tests (some seemed to be omitted from the process, but then included on the second run through). Also, tests that are geared to expect exceptions to be thrown are always marked with their closing brace as ‘not covered’ (I’m assuming that an exception being thrown legitimately causes the final line to never be hit, therefore not covered, which in my head is expected behaviour – it would be good if there was a way to disregard these instances):

Unit Test Missing Coverage.

Unit Test Missing Coverage.

So what about the actual code ‘under test’ and the metrics provided to show how much of it has been covered? In instances whereby code had been highlighted as not covered (spot checks only, of course), I have to say it appears accurate and has been useful in flagging areas I should really have tested.

As for the actual report metrics, it is exactly what I was after when I started on my way down this road. You get to see the percentage of code coverage at project, class and member level (along with ‘Sequence Points’ visited against the total count of possible points):

OpenCover Metrics Report.

OpenCover Metrics Report.

Sequence points don’t tie directly to ‘lines’, as outlined here. You’ll notice that this is a link detailing a ‘Report Generator’, which uses XML extracted using OpenCover directly. To finish up, I’ll follow the steps outlined on Stack Overflow again (got to love it, especially if you need information on the double!):

Using the Report Generator

The Report Generator can be downloaded by using NuGet again:

OpenCover Report Generator on NuGet.

OpenCover Report Generator on NuGet.

The Report Generator source code itself can be downloaded from this link.

It looks like you can create a custom report via C#, by implementing an interface, etc. For now, I’m going to do a simple run through using the command line interface. This is the command (after a bit of trial and error) that got me the XML report, for starters:

"C:\Source\Utility Applications\DesktopManager\packages\OpenCover.4.6.519\tools\OpenCover.Console.exe" -register:user -target:"C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\mstest.exe" -targetargs:"/noisolation /testcontainer:\"C:\Source\Utility Applications\DesktopManager\DesktopManager.Tests\bin\Debug\DesktopManager.Tests.dll\" /resultsfile:C:\Reports\MSTest\.trx" -mergebyhash -output:C:\Reports\MSTest\projectCoverageReport.xml

This was just a case of specifying locations for the OpenCover.console.exe, the mstest.exe and the location of the ‘Tests’ dll for my specific application. XML file in hand, I trigerred this command to generate the final report resources:

"C:\Source\Utility Applications\DesktopManager\packages\ReportGenerator.2.5.2\tools\ReportGenerator.exe" -reports:"C:\Reports\MSTest\projectCoverageReport.xml" -targetdir:"C:\Reports\CodeCoverage"

In a few quick steps you’ll have a set of HTML ‘reports’, as you can see here:

HTML Reports Generated.

HTML Reports Generated.

Let’s finish up with a couple of examples illustrating the outputs:

Index Report.

Index Report.

Configuration Helper Report Details.

Configuration Helper Report Details.

Configuration Helper Report Details.

Configuration Helper Report Line Coverage.

I think that brings us to a close. This seems like pretty powerful stuff; but, I think I’ll need more time to go through some of the outputs and try this with a larger project. I hope this has been fun and/or useful.

Thanks all!

SQL Server Management Studio…Customising those themes!

bear-brush-ssms-cust-themes-post

Howdy all,

I am a bit of a customisation nut; pretty much with any software package I pick up I have the uncontrollable urge to dig straight into the options and get on the hunt for how to ‘theme it up’!

The one that probably upsets me the most is poor old SQL Server Management Studio (SSMS). SSMS has long lagged behind, considering sharing the Visual Studio shell, on the inclusion of a dark theme. I ended up going on a scrummage around as it seems pretty incredible that this isn’t available yet. This is what I managed to dig up:

SQL Server Management Studio Dark/Black Theme

Back in April of 2016, this was the news at the time:

“We currently have the dark theme disabled as we need to do a lot more UI enhancements across all the SSMS dialogs and wizards to make this work properly. We will be working on this and true high DPI support in the coming months.”

The options on offer at the moment, by default, are a snooze fest at best:

Image showing standard colour themes in SSMS.

SSMS Standard Colour Themes.

By way of example, here is the light theme doing its thing:

Image showing the SSMS Light Theme.

SSMS Light Theme.

Alas, it sounds like we might be waiting a while longer for true dark theme support but…if you simply what to customise the code window you can (as in Visual Studio) work some magic in the interim, as follows (yes, the other dockable portions of the UI do not change, but it’s a start).

As a first step, you need to go in search of themes or, more specifically, custom made ‘.vssettings’ files. This is the resource I have used in the past and in this example:

Studio Styles

There are a tonne of pretty decent themes on this site. Here are some that immediately interested me, on first glance (as I’m a dark theme psycho):

  1. Son of Obsidian
  2. Smooth
  3. IR Black
  4. Visual Blend
  5. EaseEye
  6. Electric Energy
  7. Sublime Text 2 – New Edition
  8. Colorful Darkness

If anyone has a good recommendation for other places to go and seek out themes please leave a comment below.

Once you have downloaded any .vssettings files you are interested in (for SQL Server 2016 I have been using the VS 2015 files, without issues thus far), place them in a location that is easily accessible. Now, you need to fly on over to SSMS where the process of importing a .vssettings file is incredibly easy.

Start by navigating to Tools > Import and Export Settings, as below:

Image showing the Import and Export settings on the Tools menu in SSMS.

Import and Export Settings.

Next up, check the option entitled ‘Import selected environment settings’ from the following dialog, and click Next:

Image showing a dialog in SSMS allowing importing of custom themes.

Import Selected Environment Settings.

Next up, you can save your current settings to a .vssettings file, if you choose. This is a pretty solid bet if you have imported a new theme and have made customisations to it yourself, as you will want an easy way to rollback should your eyes be offended by whatever you import next 😉 ! If you like living on the edge there is always the ‘No, just import new settings, overwriting my current settings’ option. Pick the option that makes you feel good, then click Next:

Image showing how to save the current settings in SSMS.

Save Current Theme.

Here comes the meat in the customisation sandwich; browse to the .vssettings file location (where you have stashed one or more settings files) and pick one of your choosing, then click Next:

Image showing the selection of a new settings file.

Select New Settings File.

Check the dialog that pops up next for any warnings and, if you are happy, proceed by clicking Finish.

Image showing the confirm and finish SSMS theme dialog.

Confirm and Finish.

Finally, you should get a confirmation that the import process has completed successfully (or not, but I haven’t seen an outright failure yet!):

Image showing a theme import complete dialog in SSMS.

SSMS Theme Import Complete.

You should, all being well, see an immediate update to the theming in the code window, as shown:

Image showing the SSMS smooth theme in action.

SSMS Smooth Theme.

That’s all there is to it! Again, if anyone has a good resource for themes please let me know, I’m always on the lookout for the cool and unusual.

As an added bonus, here are a few ‘live action’ shots from the bear cave as I was constructing this post:

Thanks for reading, catch you next time!

Coding Snippets in Sublime Text 3

Hello,

This is my coup de grâce into coding snippets, which completes this mini-series (for now, at least). I wanted to do a tiny bit more Python, so began investigating which IDE to look into next:

Stackoverflow – Which Python IDE
Best Python IDE

The Eric IDE project caught my eye initially, and definitely looks like an incredibly interesting prospect. However, I find myself inexorably drawn to try out Sublime Text 3, only because I seem to have crossed paths with a fair few people using it. If anyone is interested in checking out the Eric IDE I’ve included a set of links below for further investigation:

Prerequisites

Eric IDE Prerequisites Listed
Python
PyQt

Downloads

Eric IDE Sourceforge Download

For now, let’s focus our efforts on Sublime Text 3…

Sublime Text 3

To get started, you can navigate to the Sublime Text 3 downloads page and grab the appropriate package for your OS. After reading up, there appears to be a project accessible on GitHub called Anaconda which ‘turns Sublime Text 3 into a full featured Python development IDE’. In order to utilise this functionality, you’ll first need (well, this is the easiest way from the options listed) Package Control, which is essentially a Package Manager for Sublime.

Here are a few basic notes for getting Anaconda setup with Sublime Text 3:

Image showing setup notes on installing Anaconda for Sublime Text 3.

Notes on setting up Anaconda.

In addition, this blog post looks like a strong starting point for investigation into setting up Sublime Text 3 for Python development:

Sublime Text as Python IDE

Just to prove the point, here is Anaconda showing its presence via a context menu in the IDE:

Image showing Anaconda in use within Sublime Text 3.

Anaconda in use.

I’ve taken a few additional steps here. For starters, I wanted to run my code directly within Sublime, which requires configuring the correct Build System, as follows:

Image showing how to setup the Python Build System in Sublime Text 3.

Setting Python Build System.

After performing the Build System configuration you can decide on whether you want to use syntax-only style checks or physically run your code on build (‘Tools > Build With‘):

Image showing the Sublime Build With options.

Sublime Build With.

So, as far as setup goes, that covers it. On to snippets…

Snippets in Sublime

To the main event, how do you create snippets in Sublime Text 3?

Creating the snippet itself couldn’t be simpler, just navigate to ‘Tools > Developer > New Snippet…’ and you’ll be presented with the following:

Image showing the default setup for a snippet in Sublime Text 3.

Snippet Default Setup.

The idea behind creating a snippet is conceptually similar to how you would create them for use within Visual Studio. With the content element (again, we are dealing with an XML format) you’ll find a CDATA definition; snippet code goes between the square brackets. Placeholders can be defined within the code snippet using the $ symbol followed by an incremental number, starting at one (depending on how many placeholders you have of course). The tabTrigger element defines the shortcut that needs to be typed into the IDE code window to bring the snippet into scope. Additional metadata can also be provided to specify the scope of a certain code snippet (e.g. just Python) and a friendly, textual description (stored in the aptly named description element). It sticks pretty close to other snippet creation conventions I’ve seen elsewhere, it’s certainly a cinch to pick up. Here is my first stab at a completed snippet, with comments left in line for reference:

<snippet>
	<content><![CDATA[
def saySomething(name):
	print("$1", name)
]]></content>
	<!-- Optional: Set a tabTrigger to define how to trigger the snippet -->
	<tabTrigger>ss</tabTrigger>
	<!-- Optional: Set a scope to limit where the snippet will trigger -->
	<scope>source.python</scope>
    <description>Say Something Snippet</description>
</snippet>

Snippet files are saved using the .sublime-snippet extension and for the purposes of a quick demonstration I’ve saved mine in the default location of ‘C drive > Users > MY_USER > AppData > Roaming > Sublime Text 3 > Packages > User’, this would require a bit more thought if you were creating more snippets (if you have organisational OCD such as I do). Initial reading does suggest that they should be saved under the ‘User’ directory regardless.

Within Sublime you will then need to save a file with a .py extension, ready to actually construct some Python code! The next two images show the newly created ‘say-something.sublime-snippet’ being brought into scope by keying in ‘ss’ and then hitting tab. As before, placeholders can be tabbed through for fast snippet completion:

Image showing the snippet before use.

Snippet before use.

Image showing the snippet after use.

Snippet after use.

Placeholder values, within snippets, can accept defaults by using the following convention:

<snippet>
	<content><![CDATA[
def saySomething(name):
	print("${1:Hello}", name)
	print (${2:5} + ${3:5})
]]></content>
	<!-- Optional: Set a tabTrigger to define how to trigger the snippet -->
	<tabTrigger>ss</tabTrigger>
	<!-- Optional: Set a scope to limit where the snippet will trigger -->
	<scope>source.python</scope>
    <description>Say Something Snippet</description>
</snippet>

That’s it, mini-series completed! As mentioned before I have a gargantuan mound of topics to cover, some just bubbling over in my mind and others sitting as draft posts ready for completion, so there is plenty of content on its way. Until the next time, have a great weekend!

Coding Snippets (Live Templates) in IntelliJ IDEA (OMG Java!)

Good evening folks!

This is somewhat of an experimental post. I’ve written this purely ‘in the heat of battle’, whilst I was trying to illustrate how to do this. A little bit of a departure from my usual posts; whereby I research a subject and then give you a condensed and refined product. Why did I do this? Just an interesting experiment in all honesty. On the subject of the word honest, this style of post is a chance to be authentic and more in the moment, even if I do trip up here and there and expose myself as the occasional fool; it’s a chance to see my internal monologue as I go about trying to learn something on the fly. I hope you enjoy…

I have an amazing, now completely out of control and rather feral, list of topics in my ‘to blog about’ list. Finding myself in the mood to be a ‘completionist’ however, I really should make something of this coding snippets mini-series and pack a few more of these in before I call it quits.

Today I’m looking at IntelliJ IDEA, a JetBrains IDE for coding in Java (and more than just that, if the tag line is anything to go by). This IDE has been selected out of the magic hat just because I wanted to go for something that I haven’t heard too much about before, rather than a name more frequently bandied about, such as the NetBeans IDE. This received a solid mention in Robert C. Martins ‘The Clean Coder’, which I’ve just finished digesting, so it seems most worthy of an intellectual punt!

Getting your hands on IntelliJ IDEA

To get mitts on this IDE is simple enough. I started by going here:

Choose your Edition

There you’ll find links to a Community and Ultimate edition (with Ultimate having a free, thirty-day trial). As I’m not doing a review per se on this software I won’t dig into the particulars of each edition too heavily. For anyone wondering what the different offerings entail you can navigate to the Compare Edition page and have a gander. What I will say is that language support beyond Java is most certainly a thing (.Net languages are out, as you may expect) and if you’re using Git you appear to be golden in relation to the Community edition; only Ultimate supports TFS.

Let’s get this install done and figure out a) how to write some Java and b) encapsulate something into a snippet.

Installing IntelliJ IDEA

Installing the product is, as you would expect, a next…next…next affair. I opted for the desktop shortcut (I have a messy desktop) and rigged it so that anything with a .java file extension would be associated with this IDE. On running the application you get prompted to import previous, custom settings. I’ve got none so an easy choice for me. After reading those pesky ‘policies’ (that we all read in minute detail of course!) you get, which is a nice touch, an opportunity to pick a theme (for those who know me, its dark themes all the way) and customise/install plugins. Again, I’m not really doing a review but I really liked the ease of the setup process here; being accustomed to Visual Studio for the main part I can often see times when (despite all of the functionality it does offer, I’m not bashing on it) it’s heavyweight nature does show through, not always for the best.

So, we’re ready to rock and roll; being presented with the following screen:

IntelliJ Start Screen.

IntelliJ Start Screen.

Making that Magic Coding Snippet!

Ok, so far, so good. I’m hoping that (although I’ve gazed at a fair bit of Java through my time) not having written a single line of Java is not going to be a ‘blocker’ to success ;-). The remit of this blog post is just to show how to create a code snippet, no matter how simple that is. Besides, I have read and digested the following books, so I’m feeling ready:

Googling the Error Message.

Googling the Error Message.

Copying and Pasting from Stack Overflow.

Copying and Pasting from Stack Overflow.

Create New Project seems like the way to go! I proceeded using the following tutorials by the way, should you wish to follow along:

IntelliJ IDEA Documentation

My first issue appeared to be the fact that I didn’t have the Software Development Kit installed (JDK in this instance), so I navigated here to grab one, for 64 bit Windows in my case:

JDK Downloads

Time for a cup of tea at this point; find a suitable beverage for yourself as you continue to read through, you’ve earned it by hanging on this long :-). On a side note, whoever bought me this mug needs to take a trip to the opticians. I do wonder if I could rock a twirled moustache too:

Someone needs an Optician.

Someone needs an Optician.

I downloaded and installed this package without any bother. Going back to create a new project again, I was able to (yes, I Stack Overflowed my way to glory here – I rather idiotically selected the ‘jre’ folder) hit New and navigate to the root JDK folder under my default Java install directory, as follows:

JDK Directory Selection in the IntelliJ IDEA.

JDK Directory Selection in the IntelliJ IDEA.

This brings me to a promising point on hitting next…I can make a command line application which, right now for me, is grade-a territory; that will do nicely thank you very much (I know where I stand with a Main() method after all):

Create a Command Line Application in IntelliJ.

Create a Command Line Application in IntelliJ.

After being nosey on Java rules of Package Naming, and naming my actual project, I was presented with this amazing sight:

IntelliJ Simple Command Line Application.

IntelliJ Simple Command Line Application.

As you can probably see the project name is ‘FizzBuzz’ related, so I’m going to write code to solve this classic problem and get a code snippet in along the way.

Ten minutes later I have this, which apart from questionable string handling, does the job I’m after (just needed to figure out how to print content to the output window and convert integers to string; refrained from googling here and just went on an IntelliSense rampage, which equals an ‘apologies if this is incorrect’). The output, without any unit tests to back it up, gives me expected ‘Fizz’ for multiples of three, ‘Buzz’ for multiples of five, ‘FizzBuzz’ for multiples of three and five, or just the number for everything else.

package lg.snippet.testing;

public class Main {

    public static void main(String[] args) {
        fizzBuzzerTest();
    }

    private static void fizzBuzzerTest() {
        for (int j = 1; j <= 100; j++) {
            System.out.println(evaluateInput(j));
        }
    }

    private static String evaluateInput(int fizzBuzzInput) {
        String fizzBuzzString = "";

        if (fizzBuzzInput % 3 == 0) {
            fizzBuzzString = "Fizz";
        }

        if (fizzBuzzInput % 5 == 0) {
            fizzBuzzString += "Buzz";
        }

        if (fizzBuzzString == "") {
            fizzBuzzString = Integer.toString(fizzBuzzInput);
        }

        return fizzBuzzString;
    }
}
FizzBuzz Command Line Output.

FizzBuzz Command Line Output.

That’s the best piece of Java I will fashion today, happy for it to be ripped to shreds by all the experts out there.

So, although this has been somewhat of a ramble, we are now ready to fashion a code snippet. I’m going to make the snippet encapsulate the fizzBuzzerTest method and supporting evaluateInput method, as a complete package that can be dropped anywhere. The way this is achieved, after a little research, is via Live Templates.

To get started, navigate to File > Settings and select the Live Templates section under Editor. In this next screenshot, I’m creating a new custom Template Group to act as a repository for my own weird and wonderful snippets:

Live Template Options.

Live Template Options.

After creating the custom template group you can repeat the process (with the new group highlighted) to add a new Live Template. The template itself has a few configuration options that need to have valid settings before you can proceed. Firstly, define what ‘contexts’ this Live Template can operate within; for the purposes of this little example only Java is applicable:

Defining the Live Template Context.

Defining the Live Template Context.

I gave my Live Template an abbreviation (allowing easy accessing from the editor), a Description (the one you’ll see in this screen shot I amend before saving, as it was blooming terrible!) as well as the actual code that defines the template. I’ve placed the variable COUNT into my template, which allows the template user to switch out the value when the template is brought into scope (with a default value as shown here; the dialog for managing this being accessible via the Edit Variables button). Variables must be wrapped in ‘$’ symbols and default literals must be wrapped in double quotes in order for this to work (another google-fest here!):

Completing the Live Template Definition.

Completing the Live Template Definition.

Remit basically complete…we’ve done snippets (well, Live Templates) in Java via IntelliJ IDEA! The next two screen shots show the template in use, achieved by hitting tab to generate the snippet after typing the abbreviation. We are also illustrating the place holder for the COUNT variable being editable. The variables can be tabbed through, as you would expect, for fast template completion:

Before Live Template Usage.

Before Live Template Usage.

After Live Template Usage.

After Live Template Usage.

There’s our random walkabout of IntelliJ IDEA and Live Templates done and dusted, mission complete. Cheers all, until the next time happy coding!