Earlier today I was watching a video from The Great Courses series. The lecturer was Arthur T. Benjamin of Harvey Mudd College. He was discussing strategies to solve a 3 x 3 lights out puzzle. It inspired me to write a T-SQL version of the game.

USE Experiment

/** Thanks to Professor Arthur T. Benjamin of Harvey Mudd Univserity for the inspiration. */

IF OBJECT_ID(‘lightsout’) IS NOT NULL DROP TABLE lightsout;–Technically Merlin.

CREATE TABLE lightsout

(

column1 BIT,

column2 BIT,

column3 BIT,

rownumber INT IDENTITY(1,1)

)

GO

We’ll use a trigger to enforce the one button push rule as well as to update the adjacent cell with their bitwise negation.

CREATE TRIGGER onebutton ON lightsout

INSTEAD OF UPDATE

AS

BEGIN

–Trigger to assure a single button push

IF @@ROWCOUNT > 1 OR

(SELECT TOP 1

ABS(

CAST(d.column1 AS INT)-CAST(i.column1 AS INT)

+ CAST(d.column2 AS INT)-CAST(i.column1 AS INT)

+ CAST(d.column3 AS INT)-CAST(i.column1 AS INT)

)

FROM deleted d

INNER JOIN

inserted i

ON d.rownumber = i.rownumber

) > 1

BEGIN

PRINT ‘Rule Break.’

ROLLBACK

END

ELSE

–Triggers to update on update

IF (SELECT TOP 1 rownumber FROM deleted) = 1

BEGIN

IF (SELECT TOP 1 deleted.column1 ^ inserted.column1

FROM deleted

INNER JOIN

inserted

ON deleted.rownumber = inserted.rownumber) <> 0

BEGIN

UPDATE l SET column1 = ~(l.column1) FROM lightsout l WHERE rownumber = 1;

UPDATE l SET column1 = ~(l.column1) FROM lightsout l WHERE rownumber = 2;

UPDATE l SET column2 = ~(l.column2) FROM lightsout l WHERE rownumber = 1;

END

IF (SELECT TOP 1 deleted.column2 ^ inserted.column2

FROM deleted

INNER JOIN

inserted

ON deleted.rownumber = inserted.rownumber)<>0

BEGIN

UPDATE l SET column1 = ~(l.column1) FROM lightsout l WHERE rownumber = 1;

UPDATE l SET column2 = ~(l.column2) FROM lightsout l WHERE rownumber = 1;

UPDATE l SET column2 = ~(l.column2) FROM lightsout l WHERE rownumber = 2;

UPDATE l SET column3 = ~(l.column3) FROM lightsout l WHERE rownumber = 1;

END

IF (SELECT TOP 1 deleted.column3 ^ inserted.column3

FROM deleted

INNER JOIN

inserted

ON deleted.rownumber = inserted.rownumber)<>0

BEGIN

UPDATE l SET column3 = ~(l.column3) FROM lightsout l WHERE rownumber = 1;

UPDATE l SET column3 = ~(l.column3) FROM lightsout l WHERE rownumber = 2;

UPDATE l SET column2 = ~(l.column2) FROM lightsout l WHERE rownumber = 1;

END

END

IF (SELECT TOP 1 rownumber FROM deleted) = 2

BEGIN

IF (SELECT TOP 1 deleted.column1 ^ inserted.column1

FROM deleted

INNER JOIN

inserted

ON deleted.rownumber = inserted.rownumber)<>0

BEGIN

UPDATE l SET column1 = ~(l.column1) FROM lightsout l WHERE rownumber = 1;

UPDATE l SET column1 = ~(l.column1) FROM lightsout l WHERE rownumber = 2;

UPDATE l SET column2 = ~(l.column2) FROM lightsout l WHERE rownumber = 2;

UPDATE l SET column1 = ~(l.column1) FROM lightsout l WHERE rownumber = 3;

END

IF (SELECT TOP 1 deleted.column2 ^ inserted.column2

FROM deleted

INNER JOIN

inserted

ON deleted.rownumber = inserted.rownumber)<>0

BEGIN

UPDATE l SET column2 = ~(l.column2) FROM lightsout l WHERE rownumber = 1;

UPDATE l SET column1 = ~(l.column1) FROM lightsout l WHERE rownumber = 2;

UPDATE l SET column2 = ~(l.column2) FROM lightsout l WHERE rownumber = 2;

UPDATE l SET column3 = ~(l.column3) FROM lightsout l WHERE rownumber = 2;

UPDATE l SET column2 = ~(l.column2) FROM lightsout l WHERE rownumber = 3;

END

IF (SELECT TOP 1 deleted.column3 ^ inserted.column3

FROM deleted

INNER JOIN

inserted

ON deleted.rownumber = inserted.rownumber)<>0

BEGIN

UPDATE l SET column3 = ~(l.column3) FROM lightsout l WHERE rownumber = 1;

UPDATE l SET column3 = ~(l.column3) FROM lightsout l WHERE rownumber = 2;

UPDATE l SET column2 = ~(l.column2) FROM lightsout l WHERE rownumber = 2;

UPDATE l SET column3 = ~(l.column3) FROM lightsout l WHERE rownumber = 3;

END

END

IF (SELECT TOP 1 rownumber FROM deleted) = 3

BEGIN

IF (SELECT TOP 1 deleted.column1 ^ inserted.column1

FROM deleted

INNER JOIN

inserted

ON deleted.rownumber = inserted.rownumber)<>0

BEGIN

UPDATE l SET column1 = ~(l.column1) FROM lightsout l WHERE rownumber = 3;

UPDATE l SET column1 = ~(l.column1) FROM lightsout l WHERE rownumber = 2;

UPDATE l SET column2 = ~(l.column2) FROM lightsout l WHERE rownumber = 3;

END

IF (SELECT TOP 1 deleted.column2 ^ inserted.column2

FROM deleted

INNER JOIN

inserted

ON deleted.rownumber = inserted.rownumber)<>0

BEGIN

UPDATE l SET column1 = ~(l.column1) FROM lightsout l WHERE rownumber = 3;

UPDATE l SET column2 = ~(l.column2) FROM lightsout l WHERE rownumber = 3;

UPDATE l SET column2 = ~(l.column2) FROM lightsout l WHERE rownumber = 2;

UPDATE l SET column3 = ~(l.column3) FROM lightsout l WHERE rownumber = 3;

END

IF (SELECT TOP 1 deleted.column3 ^ inserted.column3

FROM deleted

INNER JOIN

inserted

ON deleted.rownumber = inserted.rownumber)<>0

BEGIN

UPDATE l SET column3 = ~(l.column3) FROM lightsout l WHERE rownumber = 3;

UPDATE l SET column3 = ~(l.column3) FROM lightsout l WHERE rownumber = 2;

UPDATE l SET column2 = ~(l.column2) FROM lightsout l WHERE rownumber = 3;

END

END

END

GO

Let’s insert some random bits.

–Random bits

INSERT INTO lightsout

SELECT CAST(ROUND(RAND(),0) AS BIT) column1

,CAST(ROUND(RAND(),0) AS BIT) column2

,CAST(ROUND(RAND(),0) AS BIT) column3

UNION ALL

SELECT CAST(ROUND(RAND(),0) AS BIT) column1

,CAST(ROUND(RAND(),0) AS BIT) column2

,CAST(ROUND(RAND(),0) AS BIT) column3

UNION ALL

SELECT CAST(ROUND(RAND(),0) AS BIT) column1

,CAST(ROUND(RAND(),0) AS BIT) column2

,CAST(ROUND(RAND(),0) AS BIT) column3