Guest User

SQL Server Minesweeper

a guest
Dec 4th, 2012
651
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /* Reproduced from my post here: http://e-e.com/Q_26487697.html#a33724604
  2.    (c) 2012 RichardTheKiwi
  3.    License to use freely */
  4.  
  5. -- the most basic component, the Square.
  6. if object_id('Square') is not null
  7. drop table Square
  8. GO
  9. create table Square(
  10.     row int,
  11.     col int,
  12.     mine bit default(0), -- this square is a mine
  13.     neighbouring_mines tinyint, -- how many neighbours are mines
  14.     opened bit default(0), -- 1 for opened
  15.     flagged bit default(0),
  16.     suspected bit default(0),
  17.     primary key clustered(row,col) -- good for column travel given row
  18. );
  19. create index ix_Square_colrow on Square(col,row); -- travel by row given column
  20. create index ix_Square_opened on Square(opened,mine); -- game ending test
  21.  
  22. -- very basic procedure for showing the mine field
  23. -- @what is very flexible (and open to SQL injection, but who cares)
  24. if object_id('ShowMineField') is not null
  25. drop proc ShowMineField
  26. GO
  27. create proc ShowMineField
  28. @what varchar(1000)
  29. as
  30. declare @sql nvarchar(max)
  31. declare @cols nvarchar(max)
  32. select @cols = coalesce(@cols+',','') + '['+convert(varchar(10),col)+']'
  33. from (select distinct col from Square) M
  34. set @sql = '
  35. select ' + @cols + '
  36. from (select row,col,what=' + @what + ' from Square) p
  37. pivot (max(what) for col in (' + @cols + ')) pv
  38. order by row'
  39. --print (@sql)
  40. exec (@sql)
  41. GO
  42.  
  43. -- shows the game view of the mine field
  44. if object_id('ShowMineField_Game') is not null
  45. drop proc ShowMineField_Game
  46. GO
  47. create proc ShowMineField_Game
  48. AS
  49. exec ShowMineField 'case
  50.     when opened&mine=1 then ''X''
  51.     when opened=1 then coalesce(convert(char(1),nullif(neighbouring_mines,0)),'''')
  52.     when flagged=1 then ''(B)''
  53.     when suspected=1 then ''(?)''
  54.     else ''<>'' end'
  55. GO
  56.  
  57. -- shows where the mines are
  58. if object_id('ShowMineField_Mines') is not null
  59. drop proc ShowMineField_Mines
  60. GO
  61. create proc ShowMineField_Mines
  62. AS
  63. -- uncomment this line to CHEAT. or DEBUG
  64. --exec ShowMineField 'convert(char(1),mine) + ''/'' + convert(char(1),neighbouring_mines)'
  65. GO
  66.  
  67. -- shows all details about all square
  68. if object_id('ShowMineField_Info') is not null
  69. drop proc ShowMineField_Info
  70. GO
  71. create proc ShowMineField_Info
  72. AS
  73. -- uncomment this line to CHEAT. or DEBUG
  74. --exec ShowMineField '
  75. --  convert(char(1),mine) +
  76. --  convert(char(1),neighbouring_mines) +
  77. --  convert(char(1),opened) +
  78. --  convert(char(1),flagged) +
  79. --  convert(char(1),suspected)'
  80. GO
  81.  
  82. -- check to see if the game has ended
  83. if object_id('GameEnded') is not null
  84. drop function GameEnded
  85. GO
  86. create function GameEnded() returns varchar(20) as
  87. begin
  88. if exists (select * from Square where opened&mine=1)
  89.     return '** You lost'
  90. if (select count(*)-sum(1*opened)-sum(1*mine) from Square) = 0
  91.     return '** You won'
  92. return null
  93. end
  94. GO
  95.  
  96. -- shows all details about all square
  97. if object_id('ShowGameStatus') is not null
  98. drop proc ShowGameStatus
  99. GO
  100. create proc ShowGameStatus
  101. @message varchar(100) = ''
  102. AS
  103. if dbo.gameEnded()>'' set @message = dbo.gameEnded()
  104. select
  105.     Opened=sum(1*opened),
  106.     Flagged=sum(1*flagged),
  107.     [Total Mines]=sum(1*mine),
  108.     [Total Squares]=count(*),
  109.     [Message]=@message
  110. from Square
  111. exec ShowMineField_Game
  112. exec ShowMineField_Mines
  113. exec ShowMineField_Info
  114. GO
  115.  
  116. -- function to generate a new mine field
  117. if object_id('GenerateMineField') is not null
  118. drop proc GenerateMineField
  119. GO
  120. create proc GenerateMineField
  121. @rows int,
  122. @cols int,
  123. @mines int
  124. AS
  125. -- clear the current field
  126. truncate table Square;
  127. -- generate entire field including mines
  128. ;with generator as (
  129.     select row=r.number, col=c.number, rn = row_number() over (order by newid())
  130.     from master..spt_values r
  131.     inner join master..spt_values c on c.type='P' and c.number between 1 and @cols
  132.     where r.type='P' and r.number between 1 and @rows
  133. )
  134. insert Square (row, col, mine)
  135. select row, col, case when rn<=@mines then 1 else 0 end
  136. from generator;
  137.  
  138. -- one-off counting of neighbouring mines for each mine
  139. ;with mines as (
  140.     select a.row, a.col, c=count(*)
  141.     from Square a
  142.     inner join Square b on abs(a.row-b.row)<=1 and abs(a.col-b.col)<=1
  143.         and not (a.col=b.col and a.row=b.row)
  144.         and b.mine=1
  145.     group by a.row, a.col)
  146. update m
  147. set neighbouring_mines=coalesce(b.c,0)
  148. from Square m
  149. left join mines b on m.row=b.row and m.col=b.col
  150.  
  151. -- show game board
  152. exec ShowGameStatus 'Ready';
  153. GO
  154.  
  155. -- helper function to format a reference to a cell in RC notation
  156. if object_id('formatStatus') is not null
  157. drop function formatStatus
  158. GO
  159. create function formatStatus(@row int, @col int, @message varchar(100)) returns varchar(50) as
  160. begin
  161. return ('R' + convert(varchar(10),@row) + 'C' + convert(varchar(10),@col) + ': ' + @message)
  162. end
  163. GO
  164.  
  165. -- this toggles a cell between unmarked, safe or suspected
  166. if object_id('MarkCell') is not null
  167. drop proc MarkCell
  168. GO
  169. create proc MarkCell @row int, @col int AS
  170. if dbo.gameEnded()>'' begin exec ShowGameStatus; return; end;
  171.  
  172. update Square set
  173.     flagged = case when flagged|suspected=1 then 0 else 1 end,
  174.     suspected = case when flagged=1 then 1 else 0 end
  175. where row=@row and col=@col and opened=0
  176. if @@rowcount=0
  177. begin
  178.     declare @status varchar(100)
  179.     set @status = dbo.formatStatus(@row,@col,'is not valid for marking')
  180.     exec ShowGameStatus @status
  181. end
  182. else
  183.     exec ShowGameStatus;
  184. GO
  185. if exists (select * from sys.synonyms where name='m')
  186. drop synonym m;
  187. create synonym m for MarkCell
  188. GO
  189.  
  190. -- this opens up a cells next to already opened cells, that are
  191. -- next to opened cells with 0 neighbouring mines
  192. if object_id('OpenZeroCounters') is not null
  193. drop proc OpenZeroCounters
  194. GO
  195. create proc OpenZeroCounters AS
  196. set nocount on;
  197. select a=1 into #tmp;
  198. while @@rowcount>0
  199.     update a
  200.     set opened=1, flagged=0, suspected=0
  201.     from Square a
  202.     inner join Square b on abs(a.row-b.row)<=1 and abs(a.col-b.col)<=1
  203.         and b.opened=1 and b.neighbouring_mines=0
  204.     where a.opened=0
  205. exec ShowGameStatus;
  206. GO
  207.  
  208. -- this opens up a cell to reveal the big question: mine or no mine
  209. if object_id('OpenCell') is not null
  210. drop proc OpenCell
  211. GO
  212. create proc OpenCell @row int, @col int AS
  213. if dbo.gameEnded()>'' begin exec ShowGameStatus; return; end;
  214.  
  215. update Square
  216.     set opened=1, flagged=0, suspected=0
  217. where row=@row and col=@col and opened=0
  218. if @@rowcount=0
  219. begin
  220.     declare @status varchar(100)
  221.     set @status = dbo.formatStatus(@row,@col,'is not valid for opening')
  222.     exec ShowGameStatus @status
  223. end
  224. else
  225.     exec OpenZeroCounters
  226. GO
  227. if exists (select * from sys.synonyms where name='o')
  228. drop synonym o;
  229. create synonym o for OpenCell
  230. GO
  231.  
  232. -- if there are as many neighbouring mines as flagged,
  233. -- we take a big step and open up all unflagged neighbours
  234. if object_id('QuickClear') is not null
  235. drop proc QuickClear
  236. GO
  237. create proc QuickClear @row int, @col int AS
  238. if dbo.gameEnded()>'' begin exec ShowGameStatus; return; end;
  239.  
  240. update c
  241. set opened=1
  242. from Square a
  243. inner join Square c on abs(a.row-c.row)<=1 and abs(a.col-c.col)<=1 and c.flagged=0
  244. where a.row=@row and a.col=@col
  245.     and a.opened=1 -- only on opened cells, I think this is right(?)
  246.     and a.neighbouring_mines = (
  247.         select count(*)
  248.         from Square b
  249.         where abs(a.row-b.row)<=1 and abs(a.col-b.col)<=1
  250.         and b.flagged=1)
  251. if @@rowcount=0
  252. begin
  253.     declare @status varchar(100)
  254.     set @status = dbo.formatStatus(@row,@col,'is not valid for clearing')
  255.     exec ShowGameStatus @status
  256. end
  257. else
  258.     exec OpenZeroCounters
  259. GO
  260. if exists (select * from sys.synonyms where name='c')
  261. drop synonym c;
  262. create synonym c for QuickClear
  263. GO
  264.  
  265. exec GenerateMineField 50,50,10; -- this and open a single took 21s to clear
  266. exec m 10, 5;
  267. exec o 10, 1;
  268. exec c 9, 4;
RAW Paste Data

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×