DevTeam

{{toc}}

h1. REDMINE study

{{toc}}

Ensemble de requêtes SQL

h2. Compréhension de l'usage de REDMINE

Cherchons à comprendre les transitions par tracker

<pre><code class="sql">
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;
</code></pre>

h3. 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.

h3. Cas NewDev

<pre><code class="sql">
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;
</code></pre>

<pre>
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

</pre>

h2. Annexes

h3. Transitions de status par tracker

<pre>
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
</pre>

h3. Cas de Feedback

<pre>
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

</pre>

h3. Répartition des trackers par usage (en pourcentage)

<pre>
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

</pre>

h3. 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

<pre><code class="sql">
select roles.name, roles.permissions from roles where name = 'Admin' OR name = 'Manager';
</code></pre>

<pre>
name | permissions
---------+-------------------------------------
Admin | --- +
| - :add_project +
| - :edit_project +
...
</pre>