Created
December 12, 2022 21:46
-
-
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.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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