• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

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
0
auke_t
Asked:
auke_t
  • 3
1 Solution
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now