Skip to content

Instantly share code, notes, and snippets.

@marcin-chwedczuk
Created March 21, 2015 10:31
Show Gist options
  • Save marcin-chwedczuk/3d5e6d5316cc6ea05235 to your computer and use it in GitHub Desktop.
Save marcin-chwedczuk/3d5e6d5316cc6ea05235 to your computer and use it in GitHub Desktop.
Game of live in T-SQL
use TSQL2012;
if exists (select * from sys.schemas where name = 'gol')
begin
drop function gol.global_variable;
drop procedure gol.print_board;
drop procedure gol.init_board;
drop procedure gol.iteration;
drop procedure gol.iterate;
drop table gol.globals;
drop table gol.boards;
drop table gol.nums;
drop schema gol;
end;
go
create schema gol;
go
create table gol.globals (
name nvarchar(16) not null primary key,
value int not null
);
create table gol.nums (
num int not null primary key
);
with
digits as (
select
digit
from
(values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as D(digit)
)
insert into
gol.nums
select
D3.digit * 100 + D2.digit * 10 + D1.digit
from
digits D1
cross join digits D2
cross join digits D3;
create table gol.boards (
ver int not null,
x int not null,
y int not null,
alive int not null,
constraint boards_primary_key
primary key(ver, x, y)
);
insert into
gol.globals(name, value)
values
('WIDTH', 10),
('HEIGHT', 10);
go
create function gol.global_variable (
@name nvarchar(16)
)
returns int
as
begin
return (select value from gol.globals where name = @name)
end;
go
create procedure gol.init_board
as
begin
declare @WIDTH int = gol.global_variable('WIDTH');
declare @HEIGHT int = gol.global_variable('HEIGHT');
declare @INIT_VERSION int = 0;
with
row_indexes as (
select n.num from gol.nums n where n.num < @HEIGHT
),
col_indexes as (
select n.num from gol.nums n where n.num < @WIDTH
)
insert into
gol.boards (ver, x, y, alive)
select
@INIT_VERSION as ver,
C.num as y,
R.num as x,
case
-- percent of dead cells = 200 / 256
when cast(newid() as binary(1)) < 200 then 0
else 1
end as alive
from
row_indexes R
cross join col_indexes C;
end;
go
create procedure gol.print_board
as
begin
declare @WIDTH int = gol.global_variable('WIDTH');
declare @HEIGHT int = gol.global_variable('HEIGHT');
print '';
print replicate('-', 1 + @WIDTH*2);
declare @it as cursor;
set @it = cursor for
select
y,
case alive
when 0 then '- '
else 'x '
end as "alive"
from
gol.boards
where
ver = (select max(x.ver) from gol.boards x)
order by
y asc, x asc;
declare @row as nvarchar(max) = '', @last_y as int = 0;
declare @y as int, @value as nchar(2);
open @it;
fetch next from @it into @y, @value;
while @@fetch_status = 0
begin
if @last_y <> @y
begin
print concat(' ', @row);
set @row = '';
set @last_y = @y;
end;
set @row = concat(@row, @value);
fetch next from @it into @y, @value;
end;
print concat(' ', @row);
close @it;
deallocate @it;
print replicate('-', 1 + @WIDTH*2);
print '';
end;
go
create procedure gol.iteration
as
begin
declare @lastVersion int = (select max(ver) from gol.boards);
declare @currVersion int = @lastVersion + 1;
declare @WIDTH int = gol.global_variable('WIDTH');
declare @HEIGHT int = gol.global_variable('HEIGHT');
-- N8 N1 N2
-- N7 B N3
-- N6 N5 N4
with
B as (
select
C.alive as "alive",
C.x as "x",
((C.x + 1) % @WIDTH) as "x1",
((C.x - 1 + @WIDTH) % @WIDTH) as "xm1",
C.y as "y",
((C.y + 1) % @HEIGHT) as "y1",
((C.y - 1 + @HEIGHT) % @HEIGHT) as "ym1"
from
gol.boards C
where
C.ver = @lastVersion
),
N as (
select
B.alive,
B.x,
B.y,
(N1.alive + N2.alive + N3.alive + N4.alive + N5.alive + N6.alive + N7.alive + N8.alive) as "neighbors"
from
B
inner join gol.boards N1
on N1.ver = @lastVersion and N1.x = B.x and N1.y = B.ym1
inner join gol.boards N2
on N2.ver = @lastVersion and N2.x = B.x1 and N2.y = B.ym1
inner join gol.boards N3
on N3.ver = @lastVersion and N3.x = B.x1 and N3.y = B.y
inner join gol.boards N4
on N4.ver = @lastVersion and N4.x = B.x1 and N4.y = B.y1
inner join gol.boards N5
on N5.ver = @lastVersion and N5.x = B.x and N5.y = B.y1
inner join gol.boards N6
on N6.ver = @lastVersion and N6.x = B.xm1 and N6.y = B.y1
inner join gol.boards N7
on N7.ver = @lastVersion and N7.x = B.xm1 and N7.y = B.y
inner join gol.boards N8
on N8.ver = @lastVersion and N8.x = B.xm1 and N8.y = B.ym1
)
insert into
gol.boards(ver, x, y, alive)
select
@currVersion,
N.x,
N.y,
case
when N.neighbors < 2 then 0
when (N.neighbors in (2,3)) and N.alive = 1 then 1
when N.neighbors > 3 then 0
when N.neighbors = 3 and N.alive = 0 then 1
else N.alive
end as "alive"
from
N;
end;
go
create procedure gol.iterate (
@maxIterations int = 10
)
as
begin
declare @i int = 0;
exec gol.init_board;
exec gol.print_board;
while (@i < @maxIterations)
begin
print concat('===== ITERATION ', cast(@i as nvarchar(max)), ' ======');
exec gol.iteration;
exec gol.print_board;
set @i = @i + 1;
end;
end;
go
exec gol.iterate
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment