DB maintenance?

[falon]

Hello @benningm , I see crontab task, but nothing really active. Older items are never deleted from Accounting plugin tables...

Maybe do you think something like this?

sub cron {
    use feature "switch";
    my $self = shift;
    my $server = shift;

    my @tasks=qw(hourly daily weekly monthly);
    my $sql_period;
    my @match_time_pattern;

    foreach my $task ( @tasks ) {
        given ($task) {
                when ('hourly') {
                        $sql_period = 'HOUR';
                        @match_time_pattern = ('%Y-%m-%d-%H','%F-%H');
                }
                when ('daily') {
                        $sql_period = 'DAY';
                        @match_time_pattern = ('%Y-%m-%d', '%F', '%Y-%m-%d-%H','%F-%H');
                }
                when ('weekly') {
                        $sql_period = 'WEEK';
                        @match_time_pattern = ('%Y-%m-%d', '%F', '%Y-%m-%d-%H','%F-%H');
                }
                when ('monthly') {
                        $sql_period = 'MONTH';
                        @match_time_pattern = ('%Y-%m');
                }
        }
        if( grep { $_ eq $task } @_ ) {
        # do tasks
                if (grep { $_ eq $self->time_pattern } @match_time_pattern ) {
                        my $dbh = $self->_db_handle;
                        foreach my $field ( @{$self->_fields} ) {
                                my $table_name = $self->get_table_name($field);
                                $server->log( 3, "Starting $task maintenance task for $field");
                                my $sql = "DELETE from $table_name WHERE time < NOW() - INTERVAL 1 $sql_period";
                                my $ndel = $dbh->do($sql);
                                $server->log(3, "$task task for $field terminated. $ndel rows deleted.");
                        }
                }
                else {
                        $server->log(3, "There is no $task task to be executed for the configured time pattern ".$self->time_pattern);
                }
        }
    }
    $server->log(3, 'Cron for plugin '.$self->name.' terminated.');
}

in Accounting.pm. I'm not sure how do you really would like to manage these expiration tasks. But at the current release (2.03) no task runs and tables grown forever and ever. Obviously the above expiration tasks do nothing if time pattern like %V or %W has configured. As you can see only a subset of date format is supported. The mechanism could be improved for more sophisticated needs.