I have recently started working with ScrumPM plugin for Redmine. Redmine is a flexible project management web application, while ScrumPM is plugin for Scrum projects management. It is a very interesting plugin, providing capabilities like: user stories backlog, sprint and task management, burndown charts. This post is based on ScrumPM version 0.1.4.
Internally, tasks are mapped to Redmine’s issues, sprints are mapped to Redmine’s versions. However, user stories aren’t mapped to any know entities. ScrumPM stores these information in a database table called user_stories.
In the project I am working in nowadays, I have the following projects hierarchy:
- Main project
- Subproject 1
- Subproject 2
- Subproject 3
- Subproject 3.1
- Subproject 3.2
ScrumPM provides an independent user stories backlog for each project. By default, the first user story created in project Subproject 3.2 will have id 1. In project Subproject 2, user story ids will start from 1 as well. There is even another problem: if you have two stories (ids 1 and 2) and remove story 1, your next story will have id 2 and you will end up with duplicated user story ids.
A user story id must be unique within all related projects. So, my goal here was to provide a unique user story id generation process for all sub-projects of Main project. Since I don’t know anything about Ruby yet and didn’t have enough time to learn it, I went through a SQL approach.
In order to achieve my goal, my first step was understanding ScrumPM’s source code. The following code snippet contains plugin code to generate a new user story id (file controllers/user_stories_controller.rb):
43 44 45 46 | @user_story = UserStory.new(params[:user_story]) @user_story.project_id = @project.id last_us =UserStory.find(:first, :conditions => ["project_id = ?",@project.id], :order => "us_number DESC") @user_story.us_number = last_us.nil? ? 1 : last_us.us_number + 1 |
As you can see, line 45 searches for project’s last created user story. If there are no stories, id one is used (as in line 46). Otherwise, next integer is used as id.
My second step was looking for master project (Main project). Projects hierarchical data are represented by Redmine in database using a nested set model (you can find more information about this model in this MySQL article). The following SQL query searches for master project (parameter ? should be project.id):
1 | SELECT parent.id FROM projects AS node , projects AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.id = ? AND parent.parent_id IS NULL; |
The third step was being able to list all related projects (Main project’s sub-projects). The following SQL query does it (parameter ? should be Main project’s id):
1 | SELECT node.id FROM projects AS node, projects AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = ? |
Having all sub-projects listed, the next natural step was listing all user stories for related projects and getting next user story id. The following code snippet contains the complete solution:
1 | @user_story.us_number = ActiveRecord::Base.connection.select_one('SELECT IFNULL(MAX(us_number), 0) + 1 AS count FROM user_stories WHERE project_id IN (SELECT node.id FROM projects AS node, projects AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = (SELECT parent.id FROM projects AS node , projects AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.id = ' + @project.id.to_s + ' AND parent.parent_id IS NULL) ORDER BY node.lft)')["count"] |
As you can see above, my solution will get the next user story id taking in account all existing sub-projects backlogs. Besides, if you remove any story, you will not be caught in a duplicated id situation.