mrs_plugin/db_schema/mysql_rest_service_metadata.msm.project/development/sections/150-20_procedures_functions.sql [322:395]:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
        RESIGNAL;
    END;

    -- target_interval must be either M (60s or 1min) H (60*60s or 1 hour) or D (24*60*60s or 1 day)
    IF target_interval = 'M' THEN
        SET max_interval = 60;
        SET time_point_format = '%Y-%m-%d %H:%i:00';
        SET compress_older_than = DATE_SUB(time, INTERVAL 4 HOUR);
    ELSEIF target_interval = 'H' THEN
        SET max_interval = 60*60;
        SET time_point_format = '%Y-%m-%d %H:00:00';
        SET compress_older_than = DATE_SUB(time, INTERVAL 24 HOUR);
    ELSEIF target_interval = 'D' THEN
        SET max_interval = 24*60*60;
        SET time_point_format = '%Y-%m-%d 00:00:00';
        SET compress_older_than = DATE_SUB(time, INTERVAL 1 DAY);
    ELSE
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid value for target_interval, must be M, H or D.', MYSQL_ERRNO = 5400;
    END IF;
    SET before_time = DATE_FORMAT(compress_older_than, time_point_format);

    START TRANSACTION;
    -- generate aggregation query
    OPEN cur1;

    SET details_query = '';
    variable_loop: LOOP
        FETCH cur1 INTO attr_name, attr_non_resettable, attr_column;
        IF done THEN
            LEAVE variable_loop;
        END IF;
        IF attr_non_resettable THEN
             IF attr_column IS NOT NULL THEN
                SET direct_query = CONCAT(direct_query, 'MAX(', attr_column, ') as ', attr_column, ', ');
                SET direct_columns = CONCAT(direct_columns, attr_column, ',');
            ELSE
                SET details_query = CONCAT(details_query, quote(attr_name), ',MAX(details->''$.', attr_name, '''), ');
            END IF;
        ELSE
            IF attr_column IS NOT NULL THEN
                SET direct_query = CONCAT(direct_query, 'SUM(', attr_column, ') as ', attr_column, ', ');
                SET direct_columns = CONCAT(direct_columns, attr_column, ',');
            ELSE
                SET details_query = CONCAT(details_query, quote(attr_name), ',SUM(details->''$.', attr_name, '''), ');
             END IF;
        END IF;
    END LOOP;
    SET details_query = CONCAT('JSON_OBJECT(', SUBSTR(details_query, 1, GREATEST(0, LENGTH(details_query)-2)), ') as details');

    CLOSE cur1;
    DROP TABLE IF EXISTS `aggregated`;
    CREATE TEMPORARY TABLE `aggregated` LIKE mysql_rest_service_metadata.router_status;

    -- aggregate rows with interval < target_interval at the same time
    SET @query = CONCAT('INSERT INTO `aggregated` ',
        '(id, router_id, `timespan`, status_time, ', direct_columns, '`details`)',
        ' SELECT min(rs.id) as id, rs.router_id, ', max_interval, ' as `timespan`, ',
        'DATE_FORMAT(rs.status_time, ', quote(time_point_format), ') as status_time_rounded, ',
        direct_query, details_query,
        ' FROM mysql_rest_service_metadata.router_status rs JOIN mysql_rest_service_metadata.router r ON rs.router_id=r.id WHERE r.version=', quote(router_version),
        ' AND rs.status_time < ', quote(before_time), ' AND rs.`timespan` < ', max_interval);
    SET @query = CONCAT(@query, ' GROUP BY rs.router_id, DATE_FORMAT(rs.status_time, ',quote(time_point_format),') ORDER BY status_time_rounded ASC');

    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    DELETE FROM mysql_rest_service_metadata.router_status r
        WHERE r.router_id=router_id AND r.status_time < before_time AND r.`timespan` < max_interval;

    INSERT INTO mysql_rest_service_metadata.router_status SELECT * FROM `aggregated`;

    COMMIT;
END%%
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -



mrs_plugin/db_schema/mysql_rest_service_metadata.msm.project/releases/updates/mysql_rest_service_metadata_4.0.0_to_4.0.1.sql [287:360]:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
        RESIGNAL;
    END;

    -- target_interval must be either M (60s or 1min) H (60*60s or 1 hour) or D (24*60*60s or 1 day)
    IF target_interval = 'M' THEN
        SET max_interval = 60;
        SET time_point_format = '%Y-%m-%d %H:%i:00';
        SET compress_older_than = DATE_SUB(time, INTERVAL 4 HOUR);
    ELSEIF target_interval = 'H' THEN
        SET max_interval = 60*60;
        SET time_point_format = '%Y-%m-%d %H:00:00';
        SET compress_older_than = DATE_SUB(time, INTERVAL 24 HOUR);
    ELSEIF target_interval = 'D' THEN
        SET max_interval = 24*60*60;
        SET time_point_format = '%Y-%m-%d 00:00:00';
        SET compress_older_than = DATE_SUB(time, INTERVAL 1 DAY);
    ELSE
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid value for target_interval, must be M, H or D.', MYSQL_ERRNO = 5400;
    END IF;
    SET before_time = DATE_FORMAT(compress_older_than, time_point_format);

    START TRANSACTION;
    -- generate aggregation query
    OPEN cur1;

    SET details_query = '';
    variable_loop: LOOP
        FETCH cur1 INTO attr_name, attr_non_resettable, attr_column;
        IF done THEN
            LEAVE variable_loop;
        END IF;
        IF attr_non_resettable THEN
             IF attr_column IS NOT NULL THEN
                SET direct_query = CONCAT(direct_query, 'MAX(', attr_column, ') as ', attr_column, ', ');
                SET direct_columns = CONCAT(direct_columns, attr_column, ',');
            ELSE
                SET details_query = CONCAT(details_query, quote(attr_name), ',MAX(details->''$.', attr_name, '''), ');
            END IF;
        ELSE
            IF attr_column IS NOT NULL THEN
                SET direct_query = CONCAT(direct_query, 'SUM(', attr_column, ') as ', attr_column, ', ');
                SET direct_columns = CONCAT(direct_columns, attr_column, ',');
            ELSE
                SET details_query = CONCAT(details_query, quote(attr_name), ',SUM(details->''$.', attr_name, '''), ');
             END IF;
        END IF;
    END LOOP;
    SET details_query = CONCAT('JSON_OBJECT(', SUBSTR(details_query, 1, GREATEST(0, LENGTH(details_query)-2)), ') as details');

    CLOSE cur1;
    DROP TABLE IF EXISTS `aggregated`;
    CREATE TEMPORARY TABLE `aggregated` LIKE mysql_rest_service_metadata.router_status;

    -- aggregate rows with interval < target_interval at the same time
    SET @query = CONCAT('INSERT INTO `aggregated` ',
        '(id, router_id, `timespan`, status_time, ', direct_columns, '`details`)',
        ' SELECT min(rs.id) as id, rs.router_id, ', max_interval, ' as `timespan`, ',
        'DATE_FORMAT(rs.status_time, ', quote(time_point_format), ') as status_time_rounded, ',
        direct_query, details_query,
        ' FROM mysql_rest_service_metadata.router_status rs JOIN mysql_rest_service_metadata.router r ON rs.router_id=r.id WHERE r.version=', quote(router_version),
        ' AND rs.status_time < ', quote(before_time), ' AND rs.`timespan` < ', max_interval);
    SET @query = CONCAT(@query, ' GROUP BY rs.router_id, DATE_FORMAT(rs.status_time, ',quote(time_point_format),') ORDER BY status_time_rounded ASC');

    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    DELETE FROM mysql_rest_service_metadata.router_status r
        WHERE r.router_id=router_id AND r.status_time < before_time AND r.`timespan` < max_interval;

    INSERT INTO mysql_rest_service_metadata.router_status SELECT * FROM `aggregated`;

    COMMIT;
END%%
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -



