Minesweeper

As a fun exercise I decided to program Minesweeper in T-SQL and published the code on http://www.simple-talk.com/sql/t-sql-programming/minesweeper-in-t-sql/

As I learned most of my SQL skills answering questions and reading solutions on Experts-Exchange I think this is the right place to ask the folling question:

Are there any things you would have done differently and why?

Cheers,

Auke
LVL 9
auke_tAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cyberkiwiCommented:
I like the fact that it runs on SQL 2000 as well, but that does limit the power of T-SQL come 2008.
Leave the question open a few days, I will have more of an opinion then once I have done something similar.
0
cyberkiwiCommented:
This is how I would have done it.
Each procedure is compact and does only what it needs to.

Only 1 table is really needed.
-- the most basic component, the Square.
if object_id('Square') is not null
drop table Square
GO
create table Square(
	row int,
	col int,
	mine bit default(0), -- this square is a mine
	neighbouring_mines tinyint, -- how many neighbours are mines
	opened bit default(0), -- 1 for opened
	flagged bit default(0),
	suspected bit default(0),
	primary key clustered(row,col) -- good for column travel given row
);
create index ix_Square_colrow on Square(col,row); -- travel by row given column
create index ix_Square_opened on Square(opened,mine); -- game ending test

-- very basic procedure for showing the mine field
-- @what is very flexible (and open to SQL injection, but who cares)
if object_id('ShowMineField') is not null
drop proc ShowMineField
GO
create proc ShowMineField
@what varchar(1000)
as
declare @sql nvarchar(max)
declare @cols nvarchar(max)
select @cols = coalesce(@cols+',','') + '['+convert(varchar(10),col)+']'
from (select distinct col from Square) M
set @sql = '
select ' + @cols + '
from (select row,col,what=' + @what + ' from Square) p
pivot (max(what) for col in (' + @cols + ')) pv
order by row'
--print (@sql)
exec (@sql)
GO

-- shows the game view of the mine field
if object_id('ShowMineField_Game') is not null
drop proc ShowMineField_Game
GO
create proc ShowMineField_Game
AS
exec ShowMineField 'case
	when opened&mine=1 then ''X''
	when opened=1 then coalesce(convert(char(1),nullif(neighbouring_mines,0)),'''')
	when flagged=1 then ''(B)''
	when suspected=1 then ''(?)''
	else ''<>'' end'
GO

-- shows where the mines are
if object_id('ShowMineField_Mines') is not null
drop proc ShowMineField_Mines
GO
create proc ShowMineField_Mines
AS
-- uncomment this line to CHEAT. or DEBUG
--exec ShowMineField 'convert(char(1),mine) + ''/'' + convert(char(1),neighbouring_mines)'
GO

-- shows all details about all square
if object_id('ShowMineField_Info') is not null
drop proc ShowMineField_Info
GO
create proc ShowMineField_Info
AS
-- uncomment this line to CHEAT. or DEBUG
--exec ShowMineField '
--	convert(char(1),mine) +
--	convert(char(1),neighbouring_mines) +
--	convert(char(1),opened) +
--	convert(char(1),flagged) +
--	convert(char(1),suspected)'
GO

-- check to see if the game has ended
if object_id('GameEnded') is not null
drop function GameEnded
GO
create function GameEnded() returns varchar(20) as
begin
if exists (select * from Square where opened&mine=1)
	return '** You lost'
if (select count(*)-sum(1*opened)-sum(1*mine) from Square) = 0
	return '** You won'
return null
end
GO

-- shows all details about all square
if object_id('ShowGameStatus') is not null
drop proc ShowGameStatus
GO
create proc ShowGameStatus
@message varchar(100) = ''
AS
if dbo.gameEnded()>'' set @message = dbo.gameEnded()
select
	Opened=sum(1*opened),
	Flagged=sum(1*flagged),
	[Total Mines]=sum(1*mine),
	[Total Squares]=count(*),
	[Message]=@message
from Square
exec ShowMineField_Game
exec ShowMineField_Mines
exec ShowMineField_Info
GO

-- function to generate a new mine field
if object_id('GenerateMineField') is not null
drop proc GenerateMineField
GO
create proc GenerateMineField
@rows int,
@cols int,
@mines int
AS
-- clear the current field
truncate table Square;
-- generate entire field including mines
;with generator as (
	select row=r.number, col=c.number, rn = row_number() over (order by newid())
	from master..spt_values r 
	inner join master..spt_values c on c.type='P' and c.number between 1 and @cols
	where r.type='P' and r.number between 1 and @rows
)
insert Square (row, col, mine)
select row, col, case when rn<=@mines then 1 else 0 end
from generator;

-- one-off counting of neighbouring mines for each mine
;with mines as (
	select a.row, a.col, c=count(*)
	from Square a
	inner join Square b on abs(a.row-b.row)<=1 and abs(a.col-b.col)<=1
		and not (a.col=b.col and a.row=b.row)
		and b.mine=1
	group by a.row, a.col)
update m
set neighbouring_mines=coalesce(b.c,0)
from Square m
left join mines b on m.row=b.row and m.col=b.col

-- show game board
exec ShowGameStatus 'Ready';
GO

-- helper function to format a reference to a cell in RC notation
if object_id('formatStatus') is not null
drop function formatStatus
GO
create function formatStatus(@row int, @col int, @message varchar(100)) returns varchar(50) as
begin
return ('R' + convert(varchar(10),@row) + 'C' + convert(varchar(10),@col) + ': ' + @message)
end
GO

-- this toggles a cell between unmarked, safe or suspected
if object_id('MarkCell') is not null
drop proc MarkCell
GO
create proc MarkCell @row int, @col int AS
if dbo.gameEnded()>'' begin exec ShowGameStatus; return; end;

update Square set
	flagged = case when flagged|suspected=1 then 0 else 1 end,
	suspected = case when flagged=1 then 1 else 0 end
where row=@row and col=@col and opened=0
if @@rowcount=0
begin
	declare @status varchar(100)
	set @status = dbo.formatStatus(@row,@col,'is not valid for marking')
	exec ShowGameStatus @status
end
else
	exec ShowGameStatus;
GO
if exists (select * from sys.synonyms where name='m')
drop synonym m;
create synonym m for MarkCell
GO

-- this opens up a cells next to already opened cells, that are
-- next to opened cells with 0 neighbouring mines
if object_id('OpenZeroCounters') is not null
drop proc OpenZeroCounters
GO
create proc OpenZeroCounters AS
set nocount on;
select a=1 into #tmp;
while @@rowcount>0
	update a
	set opened=1, flagged=0, suspected=0
	from Square a
	inner join Square b on abs(a.row-b.row)<=1 and abs(a.col-b.col)<=1
		and b.opened=1 and b.neighbouring_mines=0
	where a.opened=0
exec ShowGameStatus;
GO

-- this opens up a cell to reveal the big question: mine or no mine
if object_id('OpenCell') is not null
drop proc OpenCell
GO
create proc OpenCell @row int, @col int AS
if dbo.gameEnded()>'' begin exec ShowGameStatus; return; end;

update Square
	set opened=1, flagged=0, suspected=0
where row=@row and col=@col and opened=0
if @@rowcount=0
begin
	declare @status varchar(100)
	set @status = dbo.formatStatus(@row,@col,'is not valid for opening')
	exec ShowGameStatus @status
end
else
	exec OpenZeroCounters
GO
if exists (select * from sys.synonyms where name='o')
drop synonym o;
create synonym o for OpenCell
GO

-- if there are as many neighbouring mines as flagged,
-- we take a big step and open up all unflagged neighbours
if object_id('QuickClear') is not null
drop proc QuickClear
GO
create proc QuickClear @row int, @col int AS
if dbo.gameEnded()>'' begin exec ShowGameStatus; return; end;

update c
set opened=1
from Square a
inner join Square c on abs(a.row-c.row)<=1 and abs(a.col-c.col)<=1 and c.flagged=0
where a.row=@row and a.col=@col
	and a.opened=1 -- only on opened cells, I think this is right(?)
	and a.neighbouring_mines = (
		select count(*)
		from Square b
		where abs(a.row-b.row)<=1 and abs(a.col-b.col)<=1
		and b.flagged=1)
if @@rowcount=0
begin
	declare @status varchar(100)
	set @status = dbo.formatStatus(@row,@col,'is not valid for clearing')
	exec ShowGameStatus @status
end
else
	exec OpenZeroCounters
GO
if exists (select * from sys.synonyms where name='c')
drop synonym c;
create synonym c for QuickClear
GO

exec GenerateMineField 50,50,10; -- this and open a single took 21s to clear
exec m 10, 5;
exec o 10, 1;
exec c 9, 4;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cyberkiwiCommented:
I suspect I have been too eager to use

abs(a.row-b.row)<=1 and abs(a.col-b.col)<=1

when the index-friendly form of

a.row between b.row-1 and b.row+1
and
a.col between b.col-1 and b.col+1

would have worked better.  But the main work is done using SET based operations, and should turn out a lot faster than using cell-by-cell loops.
0
auke_tAuthor Commented:
Nice job Cyberkiwi,

I like the way you use the master..spt_values to create the grid. Filling the mines at the same time is fun! (but too bad you can't generate the same grid)

I've got mixed feelings about the @what part. Very flexible indeed, but I tried to stay clear of things I wouldn't use in a production enviroment. It's setting a bad example! ;-)

I had the same urging you had to use abs(), but came to the same conclusion as you did.

I chose to put the mines in a different table as I like to split sensitive data. Much easier if your security is on table level than on field level. For that same reason I didn't put the neighbouring_mines in the table either, and felt too much like cheating! :-) But putting everything in a single table should be faster indeed.

At first I used @@rowcount as well, but as I heard other people have trouble with @@rowcount in production enviroments (I think due to triggers or bad coding) I decided to replace it.

I'll leave the question open for a few more days and see if other experts have anything to add!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.