I wanted to document that I updated the aggregate stored procedure to include the new GOTD base. The newly deployed version is included here. I told gwh21 to re-update after the change to remove those hunts from loot aggregations as well.
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `mhhunthelper`.`aggregate`(agg_filter varchar(255))
agg: BEGIN
-- Updated Sep 27, 2020 - initial release
DECLARE one_year int DEFAULT 60*60*24*300; -- This is 300 days to avoid event overlapping
DECLARE start_time int;
DECLARE end_time int;
DECLARE minimum_seen int;
DECLARE last_updated int;
DECLARE code_name varchar(255) ;
DECLARE table_suffix varchar(255);
SELECT COALESCE (f.start_time, UNIX_TIMESTAMP() - dynamic_start, one_year), COALESCE(f.end_time, UNIX_TIMESTAMP()),
COALESCE (f.minimum_seen, 2), f.code_name INTO start_time, end_time, minimum_seen, code_name
FROM filters f
WHERE f.code_name = agg_filter COLLATE utf8_unicode_ci
AND (f.updated = 1
OR (f.last_update < f.end_time
OR f.last_update IS NULL OR f.end_time IS NULL)
);
IF code_name IS NULL THEN
SELECT CONCAT('Leaving, no work for ', COALESCE (agg_filter, '')) as debug;
LEAVE agg;
END IF;
SET table_suffix = CONCAT('_', code_name);
IF code_name = 'all_time' THEN
SET table_suffix = '';
END IF;
-- Create the temp tables
IF code_name <> 'all_time' THEN
CALL exec_query(CONCAT('CREATE TABLE IF NOT EXISTS mhhunthelper.drops', table_suffix, ' LIKE mhhunthelper.drops;')); -- Note how bad this explodes if drops doesn't exist
CALL exec_query(CONCAT('CREATE TABLE IF NOT EXISTS mhhunthelper.attractions', table_suffix, ' LIKE mhhunthelper.attractions;')); -- Note how bad this explodes if attractions doesn't exist
END IF;
CALL exec_query(CONCAT('DROP TABLE IF EXISTS mhhunthelper.drops', table_suffix, '_temp;'));
CALL exec_query(CONCAT('CREATE TABLE mhhunthelper.drops', table_suffix, '_temp LIKE mhhunthelper.drops;'));
CALL exec_query(CONCAT('ALTER TABLE mhhunthelper.drops', table_suffix, '_temp AUTO_INCREMENT=1;'));
CALL exec_query(CONCAT('DROP TABLE IF EXISTS mhhunthelper.attractions', table_suffix, '_temp;'));
CALL exec_query(CONCAT('CREATE TABLE mhhunthelper.attractions', table_suffix, '_temp LIKE mhhunthelper.attractions;'));
CALL exec_query(CONCAT('ALTER TABLE mhhunthelper.attractions', table_suffix, '_temp AUTO_INCREMENT=1;'));
-- Technically this can join with the filters table but we've already dealt with that when confirming the filter exists
CALL exec_query(CONCAT('INSERT INTO mhhunthelper.drops', table_suffix, '_temp (location_id, loot_id, cheese_id, stage_id, total_drops, drop_count, min_amt, max_amt)
SELECT h.location_id, hl.loot_id, h.cheese_id, hs.stage_id, SUM(hl.amount), count(hl.amount), min(hl.amount), max(hl.amount)
FROM mhhunthelper.hunts h
INNER JOIN mhhunthelper.hunt_loot hl
ON h.id = hl.hunt_id
LEFT JOIN mhhunthelper.hunt_stage hs
ON h.id = hs.hunt_id
WHERE h.extension_version >= 11107
AND h.base_id not in (3150, 3364)
AND h.`timestamp` >= ', start_time, '
AND h.`timestamp` <= ', end_time, '
GROUP BY h.location_id, hl.loot_id, h.cheese_id, hs.stage_id
HAVING COUNT(DISTINCT h.id) >= ', minimum_seen, ';'));
CALL exec_query(CONCAT('UPDATE mhhunthelper.drops', table_suffix, '_temp s
INNER JOIN (
SELECT COUNT(DISTINCT h.id) AS total_hunts, h.cheese_id, h.location_id, hs.stage_id, SUM(h.caught) as total_catches
FROM mhhunthelper.hunts h
LEFT JOIN mhhunthelper.hunt_stage hs ON h.id = hs.hunt_id
WHERE h.extension_version >=11107 AND h.base_id not in (3150, 3364) AND h.timestamp >= ', start_time, ' AND h.timestamp <= ', end_time, '
GROUP BY h.location_id, hs.stage_id, h.cheese_id
) a ON s.location_id = a.location_id AND s.cheese_id = a.cheese_id AND s.stage_id <=> a.stage_id
SET s.total_hunts = a.total_hunts, s.total_catches = a.total_catches;'));
CALL exec_query(CONCAT('INSERT INTO mhhunthelper.attractions', table_suffix, '_temp (location_id, mouse_id, cheese_id, stage_id, attracted_hunts)
SELECT h.location_id, h.mouse_id, h.cheese_id, hs.stage_id, COUNT(DISTINCT h.id)
FROM mhhunthelper.hunts h
LEFT JOIN mhhunthelper.hunt_stage hs
ON h.id = hs.hunt_id
WHERE h.`timestamp` >= ', start_time, '
AND h.`timestamp` <= ', end_time, '
GROUP BY h.location_id, hs.stage_id, h.mouse_id, h.cheese_id
HAVING h.mouse_id IS NOT NULL AND COUNT(DISTINCT h.id) >= ', minimum_seen, ' ;'));
CALL exec_query(CONCAT('UPDATE mhhunthelper.attractions', table_suffix, '_temp s2
INNER JOIN (
SELECT COUNT(DISTINCT h.id) AS total_hunts, h.cheese_id, h.location_id, hs.stage_id
FROM mhhunthelper.hunts h
LEFT JOIN mhhunthelper.hunt_stage hs ON h.id = hs.hunt_id
WHERE 1=1 AND h.timestamp > ', start_time, ' AND h.timestamp < ', end_time, '
GROUP BY h.location_id, hs.stage_id, h.cheese_id
) AS s ON s.cheese_id = s2.cheese_id AND s.location_id = s2.location_id AND s.stage_id <=> s2.stage_id
SET s2.total_hunts = s.total_hunts, s2.rate = ROUND(s2.attracted_hunts/s.total_hunts*10000);'));
START TRANSACTION;
CALL exec_query(CONCAT('RENAME TABLE mhhunthelper.drops', table_suffix, ' TO mhhunthelper.drops', table_suffix, '_old;'));
CALL exec_query(CONCAT('RENAME TABLE mhhunthelper.drops', table_suffix, '_temp TO mhhunthelper.drops', table_suffix, ';'));
COMMIT;
START TRANSACTION;
CALL exec_query(CONCAT('RENAME TABLE mhhunthelper.attractions', table_suffix, ' TO mhhunthelper.attractions', table_suffix, '_old;'));
CALL exec_query(CONCAT('RENAME TABLE mhhunthelper.attractions', table_suffix, '_temp TO mhhunthelper.attractions', table_suffix, ';'));
COMMIT;
CALL exec_query(CONCAT('DROP TABLE IF EXISTS mhhunthelper.drops', table_suffix, '_old;'));
CALL exec_query(CONCAT('DROP TABLE IF EXISTS mhhunthelper.attractions', table_suffix, '_old;'));
UPDATE mhhunthelper.filters f set f.updated = 0, f.last_update = UNIX_TIMESTAMP() WHERE f.code_name = code_name COLLATE utf8_general_ci;
END
AardWolf
commented
2 years ago
I wanted to document that I updated the aggregate stored procedure to include the new GOTD base. The newly deployed version is included here. I told gwh21 to re-update after the change to remove those hunts from loot aggregations as well.