Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /* Reproduced from my post here: http://e-e.com/Q_26487697.html#a33724604
- (c) 2012 RichardTheKiwi
- License to use freely */
- -- 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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement