|
swint002
Starting Member
3 Posts |
 Posted - 01/15/2008 : 08:10:41
|
Forget about solving it automatically.
Just enjoy it:
/*
********************** SQL Sudoku **********************
©2008 Steve Wint : steve.wint@yahoo.com
http://www.geocities.com/swint002/index.htm
Note:
execute sudoku in "SQL Query Analyzer" or "SQL Server Management Studio"
execute "sudoku h" for help
execute "xit" to quit
*/
create proc dbo.Sudoku
@cmd varchar(20)=''
as
begin
set nocount on
print 'Welcome to SQL Sudoku.'
print ''
if(@cmd = 'h' or @cmd = 'help')
begin
--Print Rules
print 'To play, simply enter the grid position of the piece you want to fill and the value and press "F5"'
print 'Example: To enter the value "5" into position B2 enter "B2 5" then press the "F5" button to execute.'
print 'To clear a value just enter the grid position and press "F5".'
print 'If at anytime you need the board redrawn, without starting over, enter "DrawGrid" and press "F5"'
print 'If you need to see how you are doing, enter "HowAmIDoing" and press "F5"'
print 'If you want to Reset the game, enter "Reset_Game" and press "F5"'
print 'If you wish to exit the game type "Xit" and press the "F5" button.'
print ''
print 'For game play rules and additional information please visit "http://en.wikipedia.org/wiki/Sudoku"'
print '' print '©2008 Steve Wint'
return
end
declare @d char(1), @sql varchar(8000)
set @d='1'
if rtrim(@cmd)!='' and isnumeric(@cmd)=1
begin
if convert(int, @cmd) between 1 and 4
set @d = convert(int, @cmd)
end
set @sql = 'if exists(select 1 from dbo.sysobjects where xtype = ''P'' and name = ''Xit'')
exec dbo.Xit 0'
exec(@sql)
if not exists(select 1 from dbo.sysobjects where xtype='U' and name='tsudoku')
create table dbo.tsudoku(
suid int identity(1, 1) not null,
sval int not null,
dval int null,
uval int null
)
--Functions & Procedures
set @sql = 'create function dbo.senc(@ival int)
returns int
as
begin
return @ival^0x13F8E97A
end'
exec(@sql)
set @sql='create proc dbo.Xit
@bye int = 1
as
begin
set nocount on
print ''this is a reference shell''
end'
exec(@sql)
set @sql='create proc dbo.init_dval
@d int = 1
as
begin
set nocount on
declare @i int, @r int, @c int, @m int
update dbo.tsudoku set
dval = sval
select @i=0, @m=rand()*4, @r=1, @c=(rand()*8)+1
while @r<10
begin
while @i<(@m*@d)+3
begin
update dbo.tsudoku set
dval = dbo.senc(replace(convert(char(9),dbo.senc(dval)), convert(char(1), dbo.get_val_at_pos(2, @r, @c)),''0''))
where suid=@r
select @i=@i+1, @c=(convert(int, (rand()*9))%9)+1
end
select @r=@r+1, @i=0, @m=rand()*4
end
update tsudoku set uval = dval
end'
exec(@sql)
set @sql='create proc dbo.init_sval
@d int = 3 -->Set Difficulty:1-4
as
begin
set nocount on
truncate table dbo.tsudoku
insert into dbo.tsudoku(sval) values(dbo.senc(0x153E6D75))
insert into dbo.tsudoku(sval) values(dbo.senc(0x105DE7AD))
insert into dbo.tsudoku(sval) values(dbo.senc(0x3A8325D1))
insert into dbo.tsudoku(sval) values(dbo.senc(0x257FE08C))
insert into dbo.tsudoku(sval) values(dbo.senc(0x2A8C6A30))
insert into dbo.tsudoku(sval) values(dbo.senc(0x20AE56B3))
insert into dbo.tsudoku(sval) values(dbo.senc(0x1D58F11D))
insert into dbo.tsudoku(sval) values(dbo.senc(0x0839062E))
insert into dbo.tsudoku(sval) values(dbo.senc(0x3399DE4E))
declare @i int, @move int, @rand_count int
select @i=0, @rand_count = rand()*100+20
while @i<@rand_count
begin
set @move = convert(int, rand()*6)%6+1
if @move%2=1
begin -- Update Rows
update dbo.tsudoku set
dval = sval
where suid between convert(int, ((convert(float, @move)*3/2)-0.5)) and convert(int, ((convert(float, @move)*3/2)+1.5))
update a set
a.sval = b.dval
from dbo.tsudoku a
inner join dbo.tsudoku b
on
a.suid = convert(int, ((convert(float, @move)*3/2)-0.5))+b.suid%3
where a.suid between convert(int, ((convert(float, @move)*3/2)-0.5)) and convert(int, ((convert(float, @move)*3/2)+1.5))
and b.suid between convert(int, ((convert(float, @move)*3/2)-0.5)) and convert(int, ((convert(float, @move)*3/2)+1.5))
end
else --Update Cols
begin
declare @r int
set @r=convert(int, (10*rand()))%2+1
update dbo.tsudoku set
sval = dbo.senc(replace(convert(varchar(9),dbo.senc(sval)),
substring(convert(varchar(9), dbo.senc(sval)), convert(int, (convert(float, @move)*3/2)-2), 3),
reverse(substring(convert(varchar(9), dbo.senc(sval)), convert(int, (convert(float, @move)*3/2)-2), 3))))
from dbo.tsudoku a
update dbo.tsudoku set
sval = dbo.senc(replace(convert(varchar(9),dbo.senc(sval)),
substring(convert(varchar(9), dbo.senc(sval)), convert(int, (convert(float, @move)*3/2)-@r), 2),
reverse(substring(convert(varchar(9), dbo.senc(sval)), convert(int, (convert(float, @move)*3/2)-@r), 2))))
from dbo.tsudoku a
end
set @i=@i+1
end
exec dbo.init_dval @d
end'
exec(@sql)
set @sql='create function dbo.get_val_at_pos(@type int, @r int, @c int)
returns int
as
begin
declare @ret int, @inline char(9)
set @ret = 0
select @inline = right(space(9)+rtrim(ltrim(convert(char(9), case @type when 1 then dbo.senc(sval) when 2 then dbo.senc(dval) else dbo.senc(uval) end))),9)
from tsudoku where suid = @r
if @inline is not null
set @ret = convert(int, substring(@inline, @c, 1))
return @ret
end'
exec(@sql)
set @sql='create function dbo.make_line(@x char(9))
returns varchar(47)
as
begin
declare @i int, @ret varchar(47), @inline char(9)
select @i=1, @ret='''', @inline = right(space(9)+rtrim(ltrim(@x)),9)
while @i<10
begin
set @ret = @ret + ''[ '' + case when convert(int, substring(@inline, @i, 1))=0 then '' '' else substring(@inline, @i, 1) end +
'' ]'' + case when @i in(3, 6) then ''|'' else '''' end
set @i=@i+1
end
return @ret
end'
exec(@sql)
set @sql='create proc dbo.show_moves
as
begin
set nocount on
declare @r int, @c int, @val int, @moved tinyint
select @r=1, @moved=0
while @r<10
begin
set @c=1
while @c<10
begin
set @val=dbo.get_val_at_pos(3, @r, @c)
if dbo.get_val_at_pos(2, @r, @c)!=@val
begin
set @moved=@moved+1
if @moved=1
print ''Your Moves''
print char(65+@c-1)+convert(char(1),@r)+'': ''+convert(char(1), @val)
end
set @c=@c+1
end
set @r=@r+1
end
end'
exec(@sql)
set @sql='create proc dbo.DrawGrid
@xit int=0
as
begin
set nocount on
declare @r char(9), @suid int, @i int
declare cur cursor forward_only read_only for
select suid, dbo.senc(uval) from dbo.tsudoku
order by suid
open cur
fetch next from cur into @suid, @r
print ''SQL Sudoku''
print ''''
print '' A B C | D E F | G H I ''
print '' -----------------------------------------------''
while @@fetch_status = 0
begin
print convert(char(1), @suid) + '') ''+dbo.make_line(@r)
if @suid in(3,6)
print '' ===============================================''
fetch next from cur into @suid, @r
end
close cur
deallocate cur
print '' -----------------------------------------------''
print '' A B C | D E F | G H I ''
print ''''
print ''"Xit" to quit. "Sudoku help" for instructions.''
print ''''
exec dbo.show_moves
if @xit=1
exec dbo.Xit
end'
exec(@sql)
set @sql='create proc dbo.set_val
@r tinyint, @c tinyint, @val int
as
begin
set nocount on
if dbo.get_val_at_pos(2, @r, @c)=0 and @val between 0 and 9
begin
declare @inline char(9)
select @inline = isnull(right(space(9)+rtrim(ltrim(convert(char(9), dbo.senc(uval)))),9), '''')
from tsudoku where suid=@r
update tsudoku set
uval=dbo.senc(convert(int, left(@inline, @c-1) + convert(char(1), @val) + right(@inline, 9-@c)))
where suid=@r
end
else
print ''Invalid Entry!''
end'
exec(@sql)
set @sql='create proc dbo.make_moves
as
begin
set nocount on
declare @r tinyint, @c tinyint, @move varchar(200)
set @r=1
while @r<10
begin
set @c=0
while @c<9
begin
set @move = ''create proc dbo.'' + char(65+@c) + convert(char(1), @r)+
'' @val int=0''+char(13)+char(10)+
''as''+char(13)+char(10)+
''set nocount on''+char(13)+char(10)+
''declare @ret int''+char(13)+char(10)+
''exec dbo.set_val '' + convert(char(1), @r)+
'',''+convert(char(1), @c+1)+
'',@val ''+char(13)+char(10)+
''exec dbo.check_win @ret out''+char(13)+char(10)+
''exec dbo.DrawGrid @ret''
exec(@move)
set @c=@c+1
end
set @r=@r+1
end
end'
exec(@sql)
set @sql='create proc dbo.HowAmIDoing
@redraw_grid bit = 1
as
begin
set nocount on
declare @r int, @c int, @val int, @efound int
select @r=1, @efound = 0
while @r<10
begin
set @c=1
while @c<10
begin
set @val=dbo.get_val_at_pos(3, @r, @c)
if @val!=0 and dbo.get_val_at_pos(1, @r, @c)!=@val
begin
set @efound=@efound+1
if(@efound=1)
print ''Error(s) found at:''
print char(65+@c-1)+convert(char(1),@r)
end
set @c=@c+1
end
set @r=@r+1
end
if @efound = 0
print ''No errors found yet.''
if @redraw_grid=1
exec dbo.DrawGrid
end'
exec(@sql)
set @sql='create proc dbo.check_win @ret int=0 out
as
begin
set nocount on
if exists(select 1 from dbo.tsudoku a left join dbo.tsudoku b on a.suid=b.suid and a.sval=b.uval where b.suid is null)
begin
declare @inline char(9), @zcount int
set @zcount = 0
declare cur cursor forward_only read_only for
select dbo.senc(a.uval) from dbo.tsudoku a
inner join dbo.tsudoku b on a.suid=b.suid and a.sval!=b.uval
open cur
fetch next from cur into @inline
while @@fetch_status=0 and @zcount=0
begin
set @inline = replace(isnull(right(space(9)+rtrim(ltrim(@inline)),9), ''''), '' '', ''0'')
if charindex(''0'', @inline)>0
set @zcount=@zcount+1
fetch next from cur into @inline
end
close cur
deallocate cur
if @zcount=0 --everything is filled in but there are errors
begin
exec dbo.HowAmIDoing 0
end
set @ret = 0
return
end
else
print ''!!!!!!!!!! You Win!! :-) !!!!!!!!!!''
set @ret = 1
end'
exec(@sql)
set @sql='create proc dbo.Reset_Game
as
begin
set nocount on
update dbo.tsudoku set
uval = dval
exec dbo.DrawGrid
end'
exec(@sql)
set @sql='alter proc dbo.Xit
@bye int = 1
as
begin
set nocount on
declare @r int, @c int, @name varchar(2), @sql nvarchar(2000)
set @r=1
while @r<10
begin
set @c=0
while @c<9
begin
set @name=char(65+@c)+convert(varchar(1), @r)
set @sql=''if exists(select 1 from dbo.sysobjects where xtype=''''p'''' and name=''''''+@name+'''''') drop proc dbo.'' + @name
exec(@sql)
set @c=@c+1
end
set @r=@r+1
end
if exists(select 1 from dbo.sysobjects where xtype=''P'' and name=''check_win'')
drop proc dbo.check_win
if exists(select 1 from dbo.sysobjects where xtype=''P'' and name=''DrawGrid'')
drop proc dbo.DrawGrid
if exists(select 1 from dbo.sysobjects where xtype=''P'' and name=''HowAmIDoing'')
drop proc dbo.HowAmIDoing
if exists(select 1 from dbo.sysobjects where xtype=''P'' and name=''init_dval'')
drop proc dbo.init_dval
if exists(select 1 from dbo.sysobjects where xtype=''P'' and name=''init_sval'')
drop proc dbo.init_sval
if exists(select 1 from dbo.sysobjects where xtype=''P'' and name=''make_moves'')
drop proc dbo.make_moves
if exists(select 1 from dbo.sysobjects where xtype=''P'' and name=''Reset_Game'')
drop proc dbo.reset_game
if exists(select 1 from dbo.sysobjects where xtype=''P'' and name=''set_val'')
drop proc dbo.set_val
if exists(select 1 from dbo.sysobjects where xtype=''P'' and name=''show_moves'')
drop proc dbo.show_moves
if exists(select 1 from dbo.sysobjects where xtype=''FN'' and name=''get_val_at_pos'')
drop function dbo.get_val_at_pos
if exists(select 1 from dbo.sysobjects where xtype=''FN'' and name=''make_line'')
drop function dbo.make_line
if exists(select 1 from dbo.sysobjects where xtype=''U'' and name=''tsudoku'')
drop table dbo.tsudoku
if exists(select 1 from dbo.sysobjects where xtype=''FN'' and name=''senc'')
drop function dbo.senc
drop proc dbo.Xit
if @bye=1
print ''Goodbye.''
end'
exec(@sql)
exec('exec dbo.make_moves')
set @sql = 'exec dbo.init_sval ' + @d
exec(@sql)
exec('exec dbo.DrawGrid')
end
|
 |
|
So Duko bank holiday challenge
