MySQL solution to AoC 2022 Day 7

day07.sql

CREATE TABLE `day07` (
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `parentId` int unsigned DEFAULT NULL,
    `name` varchar(20) DEFAULT NULL,
    `size` int unsigned DEFAULT NULL,
    PRIMARY KEY (`id`)
);

CREATE TRIGGER `day07_bi` BEFORE INSERT ON `day07` FOR EACH ROW SET NEW.parentId = @curdir;

CREATE TRIGGER `day07_ai` AFTER INSERT ON `day07` FOR EACH ROW SET @curdir = CASE
    WHEN NEW.name = '..' THEN (SELECT parentId FROM day07 WHERE id = NEW.parentId)
    WHEN NEW.name IS NULL OR NEW.size IS NOT NULL THEN @curdir
    ELSE NEW.id
    END;

SET @curdir = NULL;
LOAD DATA INFILE 'c:/ProgramData/MySQL/MySQL Server 8.0/Uploads/day07.txt' INTO TABLE day07 (@line)
    SET name = IF(
        @line LIKE '$ cd %' OR @line RLIKE '^[0-9]',
        SUBSTRING_INDEX(@line, ' ', -1),
        NULL
    ),
    size = IF(
        @line RLIKE '^[0-9]',
        SUBSTRING_INDEX(@line, ' ', 1),
        NULL
    );

DELETE FROM day07 WHERE name = '..' OR name IS NULL;

SELECT SUM(totalSize) AS part1 FROM (
    WITH RECURSIVE cte AS (
        SELECT id, size, id AS top, size IS NULL AS isdir FROM day07 AS t 
        UNION ALL 
        SELECT t.id, t.size, cte.top, t.size IS NULL FROM day07 AS t JOIN cte ON t.parentId = cte.id
    ) 
    SELECT top, isdir, SUM(size) AS totalSize FROM cte GROUP BY top HAVING isDir AND totalSize <= 100000
) dt;

SELECT MIN(totalSize) AS part2 FROM (
    WITH RECURSIVE cte AS (
        SELECT id, size, id AS top, size IS NULL AS isdir FROM day07 AS t 
        UNION ALL 
        SELECT t.id, t.size, cte.top, t.size IS NULL FROM day07 AS t JOIN cte ON t.parentId = cte.id
    ) 
    SELECT top, isdir, SUM(size) AS totalSize FROM cte GROUP BY top
) dt
WHERE totalSize > (SELECT 30000000 - (70000000 - SUM(size)) FROM day07);