REDMINE study¶
- Table of contents
- REDMINE study
Ensemble de requêtes SQL
Compréhension de l'usage de REDMINE¶
Cherchons à comprendre les transitions par tracker
select COUNT(*) AS total, trackers.name, old_st.name AS "from_status", new_st.name AS "to_status"
FROM journals j, journal_details jd, issues, issue_statuses old_st, issue_statuses new_st, trackers
WHERE issues.id = j.journalized_id
AND j.id = jd.journal_id
AND issues.tracker_id = trackers.id
AND prop_key = 'status_id'
AND old_st.id = old_value::integer AND new_st.id = value::integer
GROUP BY trackers.name, from_status, to_status ORDER BY name, total DESC;
Cas Feedback et WaitForValidation¶
Le statut Feedback est peu utilisé. Il est l'est surtout pour le tracker PRODUCTION (cf. annexe )
Le statut WaitForProduction l'est beaucoup plus.
Cas du DocumentValidator¶
Très peu utilisé, 3 vraies occurences.
projet | role | firstname | lastname
---------------------+-------------------------+-----------+------------------
Common à sable | Documentation Validator | Herve | Ballans (U-PSUD)
Direction technique | Documentation Validator | Sandrine | Couturier
Helioviewer | Documentation Validator | Eric | Buchlin
Pixie | Documentation Validator | Anne | Philippon
(4 rows)
Cas NewDev¶
select COUNT(*) as total, t.name, u.login
FROM issues i, trackers t, users u
WHERE i.author_id = u.id AND t.id = i.tracker_id AND t.id = 4
GROUP BY t.name, login
ORDER BY total DESC, login;
total | name | login
-------+--------+----------
74 | NewDev | kdassas
60 | NewDev | scaminad
53 | NewDev | palinger
30 | NewDev | ccossou
24 | NewDev | gwang
20 | NewDev | hballans
14 | NewDev |
7 | NewDev | cpidanci
6 | NewDev | svingata
5 | NewDev | ebuchlin
5 | NewDev | esoubrie
5 | NewDev | mmebsout
3 | NewDev | bhasnoun
3 | NewDev | mdexet
2 | NewDev | ilajili
1 | NewDev | aarondel
1 | NewDev | abeelen
1 | NewDev | cruizdeg
1 | NewDev | naghanim
Annexes¶
Transitions de status par tracker¶
total | name | from_status | to_status
-------+---------------------------+------------------------+------------------------
260 | Action | New | Closed
43 | Action | New | In Progress
34 | Action | In Progress | Closed
30 | Action | New | Resolved
18 | Action | Resolved | Closed
15 | Action | In Progress | Waiting For Validation
9 | Action | New | Waiting For Validation
8 | Action | Waiting For Validation | Closed
3 | Action | In Progress | Resolved
3 | Action | New | Rejected / Cancelled
2 | Action | Waiting For Validation | Resolved
2 | Action | Resolved | In Progress
1 | Action | Feedback | Resolved
1 | Action | Waiting For Validation | Rejected / Cancelled
1 | Action | In Progress | Feedback
1 | Action | Closed | In Progress
1 | Action | Rejected / Cancelled | Closed
1 | Action | Resolved | Waiting For Validation
1 | Action | New | Feedback
1 | Action | Closed | Resolved
1 | Action | In Progress | Rejected / Cancelled
1 | Action | In Progress | New
47 | Bug | New | Closed
14 | Bug | New | Waiting For Validation
13 | Bug | New | In Progress
13 | Bug | Waiting For Validation | In Progress
6 | Bug | New | Rejected / Cancelled
6 | Bug | In Progress | Waiting For Validation
5 | Bug | Resolved | Closed
4 | Bug | New | Resolved
3 | Bug | In Progress | Closed
3 | Bug | Waiting For Validation | Closed
2 | Bug | Waiting For Validation | Resolved
1 | Bug | Resolved | In Progress
1 | Change request CR | New | In Progress
1 | Change request CR | New | Feedback
3 | Configuration | New | Waiting For Validation
2 | Configuration | Waiting For Validation | Closed
1 | Design | New | Closed
53 | Documentation | New | In Progress
47 | Documentation | New | Closed
46 | Documentation | In Progress | Closed
11 | Documentation | In Progress | Waiting For Validation
9 | Documentation | Waiting For Validation | In Progress
4 | Documentation | New | Waiting For Validation
3 | Documentation | Waiting For Validation | Resolved
3 | Documentation | Waiting For Validation | Closed
2 | Documentation | Resolved | In Progress
2 | Documentation | Closed | In Progress
2 | Documentation | Resolved | Closed
1 | Documentation | In Progress | Resolved
1 | Documentation | New | Rejected / Cancelled
22 | Feature | New | Closed
14 | Feature | New | In Progress
9 | Feature | Resolved | Closed
5 | Feature | In Progress | Waiting For Validation
5 | Feature | Waiting For Validation | Resolved
5 | Feature | In Progress | Resolved
5 | Feature | New | Waiting For Validation
4 | Feature | New | Resolved
4 | Feature | Waiting For Validation | In Progress
3 | Feature | Resolved | In Progress
1 | Feature | In Progress | Closed
1 | Feature | Waiting For Validation | Closed
1 | Feature | New | Feedback
14 | Infrastructure | New | Closed
7 | Infrastructure | Resolved | Closed
6 | Infrastructure | New | In Progress
4 | Infrastructure | In Progress | Resolved
3 | Infrastructure | New | Resolved
2 | Infrastructure | In Progress | Closed
1 | Infrastructure | New | Rejected / Cancelled
1 | Infrastructure | In Progress | Waiting For Validation
1 | Infrastructure | Waiting For Validation | Closed
9 | Meeting | New | Closed
4 | Meeting | New | In Progress
3 | Meeting | In Progress | Closed
78 | NewDev | New | Closed
54 | NewDev | New | In Progress
27 | NewDev | Resolved | Closed
18 | NewDev | In Progress | Closed
15 | NewDev | In Progress | Waiting For Validation
14 | NewDev | New | Resolved
13 | NewDev | Waiting For Validation | In Progress
13 | NewDev | In Progress | Resolved
10 | NewDev | New | Waiting For Validation
8 | NewDev | New | Rejected / Cancelled
7 | NewDev | Waiting For Validation | Closed
5 | NewDev | Waiting For Validation | Resolved
2 | NewDev | Resolved | Waiting For Validation
2 | NewDev | Resolved | In Progress
1 | NewDev | Feedback | Resolved
1 | NewDev | In Progress | Feedback
1 | NewDev | Waiting For Validation | Rejected / Cancelled
1 | NewDev | New | Feedback
1 | NewDev | In Progress | New
2 | Non-conformance NC | Resolved | Closed
2 | Non-conformance NC | In Progress | Closed
2 | Non-conformance NC | New | In Progress
2 | Non-conformance NC | In Progress | Waiting For Validation
1 | Non-conformance NC | Closed | Resolved
1 | Non-conformance NC | In Progress | Resolved
1 | Non-conformance NC | Waiting For Validation | Closed
1 | Non-conformance NC | New | Closed
21 | Production | New | Closed
6 | Production | New | Feedback
5 | Production | In Progress | Closed
4 | Production | New | In Progress
3 | Production | Feedback | Closed
3 | Production | Closed | In Progress
2 | Production | Resolved | Closed
2 | Production | New | Resolved
1 | Production | New | Waiting For Validation
2 | Request for Deviation RFD | New | In Progress
1 | Request for Deviation RFD | In Progress | Waiting For Validation
1 | Request for Deviation RFD | Resolved | Closed
1 | Request for Deviation RFD | Waiting For Validation | Closed
1 | Request for Deviation RFD | In Progress | Closed
3 | Support | New | Waiting For Validation
3 | Support | New | Closed
3 | Support | New | In Progress
2 | Support | Waiting For Validation | In Progress
1 | Support | Feedback | Closed
1 | Support | Resolved | Closed
1 | Support | Waiting For Validation | Closed
1 | Support | In Progress | Waiting For Validation
19 | Test | New | Closed
10 | Test | New | In Progress
9 | Test | Resolved | Closed
6 | Test | In Progress | Closed
6 | Test | New | Resolved
4 | Test | In Progress | Resolved
1 | Test | Resolved | Waiting For Validation
1 | Test | New | Rejected / Cancelled
1 | Test | Closed | In Progress
1 | Test | Waiting For Validation | Closed
Cas de Feedback¶
total | name | from_status | to_status
-------+-------------------+-------------+-----------
1 | Action | New | Feedback
1 | Action | In Progress | Feedback
1 | Action | Feedback | Resolved
1 | Change request CR | New | Feedback
1 | Feature | New | Feedback
1 | NewDev | New | Feedback
1 | NewDev | Feedback | Resolved
1 | NewDev | In Progress | Feedback
6 | Production | New | Feedback
3 | Production | Feedback | Closed
1 | Support | Feedback | Closed
Répartition des trackers par usage (en pourcentage)¶
redmine_default=# select count(t.name)*100 / ( SELECT count(*) from issues i ) AS total, t.name from issues i, trackers t where i.tracker_id = t.id GROUP BY name ORDER BY total DESC;
total | name
-------+---------------------------
41 | Action
20 | NewDev
10 | Documentation
7 | Bug
4 | Production
4 | Feature
3 | Test
2 | Infrastructure
1 | Meeting
1 | Support
0 | Configuration
0 | Change request CR
0 | Request for Deviation RFD
0 | Non-conformance NC
0 | Design
Voir les permissions associées à un rôle.¶
Les permissions sont dénormalisées, elles sont stockées dans un champs texte selon un format de clefs ruby
select roles.name, roles.permissions from roles where name = 'Admin' OR name = 'Manager';
name | permissions
---------+-------------------------------------
Admin | --- +
| - :add_project +
| - :edit_project +
...
Liste des rôles, des utilisateurs par projet¶
SELECT projects.name as projet, roles.name as role, users.firstname, users.lastname
FROM users, members, projects, member_roles, roles
WHERE members.user_id = users.id
AND members.project_id = projects.id
AND members.id = member_roles.member_id
AND roles.id = member_roles.role_id
AND users.status=1
ORDER BY projects.name;
projet | role | firstname | lastname
--------------------------------------------------------------+-------------------------+-----------------+--------------------------
A2IMP | Developer | David | Delavennat
A2IMP | Manager | Herve | Ballans
...