DevTeam

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 du DocumentValidator

Très peu utilisé, 3 vraies occurences.

<pre>

       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)

</pre>

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>

h3. Liste des rôles, des utilisateurs par projet

<pre><code class="sql">

 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;

</code></pre>

<pre>

                            projet                            |          role           |    firstname    |         lastname

--------------------------------------------------------------+-------------------------+-----------------+--------------------------

 A2IMP                                                        | Developer               | David           | Delavennat

 A2IMP                                                        | Manager                 | Herve           | Ballans

...

</pre>