Skip to content

Instantly share code, notes, and snippets.

@snoyes
Created December 12, 2022 21:46
Show Gist options
  • Save snoyes/ff3a2eb46df147f133c59c55aff85e1e to your computer and use it in GitHub Desktop.
Save snoyes/ff3a2eb46df147f133c59c55aff85e1e to your computer and use it in GitHub Desktop.
In MySQL. Not setting any speed records, and some of this syntax is deprecated.
DROP TABLE IF EXISTS day12, graph;
SET NAMES binary;
CREATE TABLE day12 (
rowNum int DEFAULT 1,
colNum int auto_increment,
cell char(1),
primary key (rowNum, colNum)
) DEFAULT CHARSET=binary ENGINE=MyISAM;
CREATE TRIGGER day12_ai BEFORE INSERT ON day12 FOR EACH ROW SET
@row := @row + (NEW.cell = '\n'),
-- Deliberately violating primary key so that '\n' will be discarded
NEW.rowNum = IF(NEW.cell = '\n', 1, NEW.rowNum),
NEW.colNum = IF(new.cell = '\n', 1, NEW.colNum)
;
SET @row := 1;
LOAD DATA INFILE
-- 'c:/programdata/mysql/mysql server 8.0/uploads/day12example.txt'
'c:/programdata/mysql/mysql server 8.0/uploads/day12.txt'
IGNORE INTO TABLE day12 FIELDS TERMINATED BY '' LINES TERMINATED BY '' (cell) SET rownum = @row;
CREATE TABLE graph (index(rowNum, colNum), index(neighborRow, neighborCol)) AS
SELECT parent.*, neighbor.rowNum AS neighborRow, neighbor.colNum AS neighborCol, neighbor.cell AS neighborCell
FROM day12 AS parent
JOIN day12 AS neighbor ON (neighbor.rowNum, neighbor.colNum) IN (
(parent.rowNum+1, parent.colNum),
(parent.rowNum - 1, parent.colNum),
(parent.rowNum, parent.colNum + 1),
(parent.rowNum, parent.colNum - 1)
)
AND ORD(ELT(FIELD(parent.cell, 'S', 'E', parent.cell), 'a', 'z', parent.cell)) + 1
>= ORD(ELT(FIELD(neighbor.cell, 'S', 'E', neighbor.cell), 'a', 'z', neighbor.cell))
;
-- UNION DISTINCT would prevent the use of having to keep my own @visited set and use FIND_IN_SET,
-- but only if depth is not included in the select list, and then I wouldn't know what the depth is
WITH RECURSIVE cte AS (
SELECT *,
@found := FALSE AS found,
CAST(@visited := CONCAT(rowNum,':',colNum,',',neighborRow,':',neighborCol) AS char(32767)),
1 AS depth
FROM graph
WHERE cell = 'S'
OR cell = 'a' -- for part 2
UNION ALL
SELECT graph.*,
@found := @found OR graph.neighborCell = 'E',
@visited := CONCAT(@visited, ',', graph.neighborRow, ':', graph.neighborCol),
depth + 1
FROM cte
JOIN graph ON
cte.neighborRow = graph.rowNum AND cte.neighborCol = graph.colNum
AND NOT FIND_IN_SET(CONCAT(graph.neighborRow, ':', graph.neighborCol), @visited)
WHERE NOT @found
)
SELECT depth FROM cte WHERE neighborCell = 'E';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment