Pastebin launched a little side project called VERYVIRAL.com, check it out ;-) Want more features on Pastebin? Sign Up, it's FREE!
Guest

SQL Server Minesweeper

By: a guest on Dec 4th, 2012  |  syntax: T-SQL  |  size: 7.43 KB  |  views: 233  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
This paste has a previous version, view the difference. Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  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;