SQL scripts to close Redmine solved issues

Redmine is probably the best open source ticketing application. We have been using it for some years and had no problem at all.

Though you can define your own workflows and status transitions I miss a feature: automatically closing issues that have been solved during a certain number of days.

There exists a plugin to do so, but if you don’t want to deploy third-party plugins or you just want to customize the way you close the issues, here you have a couple of MySQL Scripts to automate this task (you can program a cron entry for that).

The first script retrieves all the issues ids that have been in solved status during more than the specified number of days

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
CREATE DEFINER=`root`@`%` PROCEDURE `sp_close_resolved_redmine`(
IN `in_days` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
declare v_id integer;
declare v_finished bool;
declare res_issues cursor for
SELECT id
FROM redmine.issues
WHERE status_id = 3
AND TIMESTAMPDIFF(DAY, updated_on, CURRENT_TIMESTAMP) > in_days;
declare continue handler for not found set v_finished = true;
open res_issues;
res_issues: loop
fetch res_issues into v_id;
if v_finished = true then
leave res_issues;
end if;
call sp_cierra_issue_redmine(v_id);
end loop res_issues;
close res_issues;
END
CREATE DEFINER=`root`@`%` PROCEDURE `sp_close_resolved_redmine`( IN `in_days` INT ) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN declare v_id integer; declare v_finished bool; declare res_issues cursor for SELECT id FROM redmine.issues WHERE status_id = 3 AND TIMESTAMPDIFF(DAY, updated_on, CURRENT_TIMESTAMP) > in_days; declare continue handler for not found set v_finished = true; open res_issues; res_issues: loop fetch res_issues into v_id; if v_finished = true then leave res_issues; end if; call sp_cierra_issue_redmine(v_id); end loop res_issues; close res_issues; END
CREATE DEFINER=`root`@`%` PROCEDURE `sp_close_resolved_redmine`(
IN `in_days` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    declare v_id integer;
    declare v_finished bool;
    declare res_issues cursor for
    SELECT id
    FROM redmine.issues
    WHERE status_id = 3
    AND TIMESTAMPDIFF(DAY, updated_on, CURRENT_TIMESTAMP) > in_days;
    declare continue handler for not found set v_finished = true;

    open res_issues;
    res_issues: loop
        fetch res_issues into v_id;
        if v_finished = true then
            leave res_issues;
        end if;
        call sp_cierra_issue_redmine(v_id);
    end loop res_issues;
    close res_issues;
END

The second script is invoked by the one above passing the issue id to be closed

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
CREATE DEFINER=`root`@`%` PROCEDURE `sp_cierra_issue_redmine`(
IN `in_id` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
declare v_id integer;
declare v_author_id integer;
declare v_assigned_to integer;
declare v_status integer;
declare v_id_journal integer;
select author_id, assigned_to_id, status_id into v_author_id, v_assigned_to, v_status
from redmine.issues
where id = in_id;
#Update issue status and assign to author
update redmine.issues
set assigned_to_id = v_author_id,
status_id = 5,
updated_on = current_timestamp,
closed_on = current_timestamp
where id = in_id;
#Create a new journal for the issue
insert into redmine.journals (journalized_id, journalized_type, user_id, notes, created_on)
values (in_id, 'Issue', 1, 'Automatically closed after 7 days solved', current_timestamp);
#Get the journal id we have just inserted
select max(id) into v_id_journal
from redmine.journals
where id = in_id;
#Create two new entries in journal details, one for assigned_id and another one for status_id
insert into redmine.journal_details (journal_id, property, prop_key, old_value, `value`)
values (v_id_journal, 'attr', 'assigned_to_id', v_assigned_to, v_author_id);
insert into redmine.journal_details (journal_id, property, prop_key, old_value, `value`)
values (v_id_journal, 'attr', 'status_id', v_status, 5);
END
CREATE DEFINER=`root`@`%` PROCEDURE `sp_cierra_issue_redmine`( IN `in_id` INT ) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN declare v_id integer; declare v_author_id integer; declare v_assigned_to integer; declare v_status integer; declare v_id_journal integer; select author_id, assigned_to_id, status_id into v_author_id, v_assigned_to, v_status from redmine.issues where id = in_id; #Update issue status and assign to author update redmine.issues set assigned_to_id = v_author_id, status_id = 5, updated_on = current_timestamp, closed_on = current_timestamp where id = in_id; #Create a new journal for the issue insert into redmine.journals (journalized_id, journalized_type, user_id, notes, created_on) values (in_id, 'Issue', 1, 'Automatically closed after 7 days solved', current_timestamp); #Get the journal id we have just inserted select max(id) into v_id_journal from redmine.journals where id = in_id; #Create two new entries in journal details, one for assigned_id and another one for status_id insert into redmine.journal_details (journal_id, property, prop_key, old_value, `value`) values (v_id_journal, 'attr', 'assigned_to_id', v_assigned_to, v_author_id); insert into redmine.journal_details (journal_id, property, prop_key, old_value, `value`) values (v_id_journal, 'attr', 'status_id', v_status, 5); END
CREATE DEFINER=`root`@`%` PROCEDURE `sp_cierra_issue_redmine`(
                IN `in_id` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    declare v_id            integer;
    declare v_author_id     integer;
    declare v_assigned_to   integer;
    declare v_status        integer;
    declare v_id_journal    integer;               

    select author_id, assigned_to_id, status_id into v_author_id, v_assigned_to, v_status
      from redmine.issues
     where id = in_id;

    #Update issue status and assign to author
    update redmine.issues
       set assigned_to_id = v_author_id,
           status_id = 5,
           updated_on = current_timestamp,
           closed_on = current_timestamp
     where id = in_id;
     
    #Create a new journal for the issue
    insert into redmine.journals (journalized_id, journalized_type, user_id, notes, created_on)
                          values (in_id, 'Issue', 1, 'Automatically closed after 7 days solved', current_timestamp);  
    
    #Get the journal id we have just inserted
    select max(id) into v_id_journal
      from redmine.journals
     where id = in_id;
    
    #Create two new entries in journal details, one for assigned_id and another one for status_id
    insert into redmine.journal_details (journal_id, property, prop_key, old_value, `value`)
                                 values (v_id_journal, 'attr', 'assigned_to_id', v_assigned_to, v_author_id);     

    insert into redmine.journal_details (journal_id, property, prop_key, old_value, `value`)
                                 values (v_id_journal, 'attr', 'status_id', v_status, 5);     
END

Leave a Reply

Your email address will not be published. Required fields are marked *