Cuando damos de alta un nuevo usuario en el Redmine, lo usual es que nos pidan que le agreguemos a los mismo proyectos y con los mismos roles que algún usuario específico.
Hasta ahora lo haciamos mirando en la propia herramienta los proyectos a los que estaba asociado ese usuario en concreto, y uno a uno lo agregábamos con los mismos roles.
A partir de ahora se puede hacer en la propia BBDDs:
En este ejemplo nos piden que agreguemos al usuario jmrelo a los mismos proyectos y con los mismos roles que dmaza y rgarrote:
Obtenemos los ids de esos usuarios:
mysql> select * from users where login="dmaza";
+----+-------+------------------------------------------+-----------+----------+-----------------+-------------------+-------+--------+---------------------+----------+----------------+---------------------+---------------------+------+
| id | login | hashed_password | firstname | lastname | mail | mail_notification | admin | status | last_login_on | language | auth_source_id | created_on | updated_on | type |
+----+-------+------------------------------------------+-----------+----------+-----------------+-------------------+-------+--------+---------------------+----------+----------------+---------------------+---------------------+------+
| 13 | dmaza | 9b53b2097a54c0324b98cea1136f7a40fd47e35b | Daniel | Maza | dmaza@luismido.com | 0 | 0 | 1 | 2011-06-28 12:40:18 | es | 1 | 2009-03-10 14:35:57 | 2011-06-28 12:40:18 | NULL |
+----+-------+------------------------------------------+-----------+----------+-----------------+-------------------+-------+--------+---------------------+----------+----------------+---------------------+---------------------+------+
1 row in set (0.00 sec)
mysql> select * from users where login="rgarrote";
+----+----------+------------------------------------------+-----------+----------+--------------------+-------------------+-------+--------+---------------------+----------+----------------+---------------------+---------------------+------+
| id | login | hashed_password | firstname | lastname | mail | mail_notification | admin | status | last_login_on | language | auth_source_id | created_on | updated_on | type |
+----+----------+------------------------------------------+-----------+----------+--------------------+-------------------+-------+--------+---------------------+----------+----------------+---------------------+---------------------+------+
| 25 | rgarrote | dc0bbc7553741a04bb7bd07d70b45b77ce4a3f03 | Rafael | Garrote | rgarrote@luismido.com | 0 | 0 | 1 | 2011-09-13 17:09:37 | es | 1 | 2009-03-11 00:07:41 | 2011-09-13 17:09:37 | NULL |
+----+----------+------------------------------------------+-----------+----------+--------------------+-------------------+-------+--------+---------------------+----------+----------------+---------------------+---------------------+------+
1 row in set (0.00 sec)
mysql> select * from users where login="jmerelo";
+-----+---------+------------------------------------------+-----------+----------+-------------------+-------------------+-------+--------+---------------------+----------+----------------+---------------------+---------------------+------+
| id | login | hashed_password | firstname | lastname | mail | mail_notification | admin | status | last_login_on | language | auth_source_id | created_on | updated_on | type |
+-----+---------+------------------------------------------+-----------+----------+-------------------+-------------------+-------+--------+---------------------+----------+----------------+---------------------+---------------------+------+
| 109 | jmerelo | 6213fed2eba4c79b1ac5b08adfe2c187445d79d4 | Julio | Merelo | jmerelo@luismido.com | 0 | 0 | 1 | 2011-09-13 10:11:55 | es | 1 | 2011-09-13 10:03:31 | 2011-09-13 10:11:55 | NULL |
+-----+---------+------------------------------------------+-----------+----------+-------------------+-------------------+-------+--------+---------------------+----------+----------------+---------------------+---------------------+------+
Obtenemos los proyectos en los que ambos usuarios concretos participan, con los datos que nos interesan:
mysql> SELECT project_id, role_id FROM members WHERE user_id=13 OR user_id=25 GROUP BY project_id;
+------------+---------+
| project_id | role_id |
+------------+---------+
| 3 | 4 |
| 4 | 5 |
| 5 | 4 |
| 6 | 3 |
| 56 | 4 |
| 57 | 4 |
| 59 | 3 |
| 62 | 4 |
| 69 | 5 |
| 73 | 5 |
| 75 | 4 |
| 76 | 3 |
| 77 | 3 |
| 78 | 4 |
| 79 | 3 |
| 81 | 3 |
| 82 | 3 |
| 83 | 4 |
| 86 | 3 |
| 88 | 4 |
| 100 | 3 |
| 102 | 6 |
| 103 | 4 |
| 104 | 4 |
| 105 | 4 |
| 107 | 4 |
| 108 | 6 |
| 110 | 6 |
| 112 | 4 |
| 113 | 4 |
| 118 | 4 |
| 119 | 3 |
| 120 | 4 |
| 121 | 3 |
| 122 | 3 |
| 124 | 4 |
| 125 | 3 |
| 127 | 4 |
| 128 | 3 |
| 130 | 4 |
| 131 | 3 |
| 132 | 3 |
| 133 | 4 |
| 137 | 3 |
| 139 | 3 |
| 140 | 3 |
| 141 | 4 |
| 142 | 3 |
| 143 | 3 |
| 146 | 3 |
| 147 | 3 |
| 152 | 3 |
| 153 | 3 |
| 154 | 4 |
| 156 | 3 |
| 159 | 3 |
| 160 | 3 |
| 163 | 3 |
| 164 | 3 |
| 167 | 3 |
| 170 | 3 |
| 172 | 3 |
| 173 | 3 |
| 174 | 3 |
| 175 | 4 |
| 176 | 3 |
| 177 | 3 |
| 178 | 3 |
| 180 | 3 |
| 181 | 3 |
| 183 | 3 |
| 184 | 3 |
| 185 | 3 |
| 186 | 3 |
| 190 | 3 |
| 191 | 3 |
| 192 | 3 |
| 193 | 3 |
| 195 | 3 |
| 196 | 3 |
| 197 | 3 |
| 198 | 3 |
| 199 | 3 |
| 200 | 3 |
| 201 | 3 |
| 203 | 3 |
+------------+---------+
86 rows in set (0.00 sec)
De esta forma nos saca todos los proyectos en los que participan bien solo uno de ellos o los dos, y con el role que tenga dmaza, porque está ordenado de forma ascendente. Nos vale ya que el usuario jmerelo lo agregaremos a los mismos proyectos con un solo role.
Creamos un script en php para realizar las inserciones:
<?php
# Datos de conexión a la BBDD
$conexion = mysql_connect("localhost", "xxxxxxx", "xxxxxxxx");
mysql_select_db("redmine", $conexion);
# Querys
$queEmp1 = "SELECT project_id, role_id FROM members WHERE user_id=13 OR user_id=25 GROUP BY project_id;";
$resEmp1 = mysql_query($queEmp1, $conexion) or die(mysql_error());
$totEmp1 = mysql_num_rows($resEmp1);
if ($totEmp1> 0) {
while ($rowEmp1 = mysql_fetch_assoc($resEmp1)) {
$queEmp2 = "INSERT INTO members(user_id, project_id, role_id, created_on) VALUES(109, ".$rowEmp1['project_id'].", ".$rowEmp1['role_id'].", '2011-09-14 14:58:53');";
$resEmp2 = mysql_query($queEmp2, $conexion) or die(mysql_error());
}
}
?>
Lo ejecutamos accediendo a la dirección en la que lo hemos guardado y comprobamos que se han realizado bien tanto por web como en la BBDD:
mysql> select * from members where user_id=109;
+------+---------+------------+---------+---------------------+-------------------+
| id | user_id | project_id | role_id | created_on | mail_notification |
+------+---------+------------+---------+---------------------+-------------------+
| 3262 | 109 | 3 | 4 | 2011-09-14 14:58:53 | 0 |
| 3263 | 109 | 4 | 5 | 2011-09-14 14:58:53 | 0 |
| 3264 | 109 | 5 | 4 | 2011-09-14 14:58:53 | 0 |
| 3265 | 109 | 6 | 3 | 2011-09-14 14:58:53 | 0 |
| 3266 | 109 | 56 | 4 | 2011-09-14 14:58:53 | 0 |
| 3267 | 109 | 57 | 4 | 2011-09-14 14:58:53 | 0 |
| 3268 | 109 | 59 | 3 | 2011-09-14 14:58:53 | 0 |
| 3269 | 109 | 62 | 4 | 2011-09-14 14:58:53 | 0 |
| 3270 | 109 | 69 | 5 | 2011-09-14 14:58:53 | 0 |
| 3271 | 109 | 73 | 5 | 2011-09-14 14:58:53 | 0 |
| 3272 | 109 | 75 | 4 | 2011-09-14 14:58:53 | 0 |
| 3273 | 109 | 76 | 3 | 2011-09-14 14:58:53 | 0 |
| 3274 | 109 | 77 | 3 | 2011-09-14 14:58:53 | 0 |
| 3275 | 109 | 78 | 4 | 2011-09-14 14:58:53 | 0 |
| 3276 | 109 | 79 | 3 | 2011-09-14 14:58:53 | 0 |
| 3277 | 109 | 81 | 3 | 2011-09-14 14:58:53 | 0 |
| 3278 | 109 | 82 | 3 | 2011-09-14 14:58:53 | 0 |
| 3279 | 109 | 83 | 4 | 2011-09-14 14:58:53 | 0 |
| 3280 | 109 | 86 | 3 | 2011-09-14 14:58:53 | 0 |
| 3281 | 109 | 88 | 4 | 2011-09-14 14:58:53 | 0 |
| 3282 | 109 | 100 | 3 | 2011-09-14 14:58:53 | 0 |
| 3283 | 109 | 102 | 6 | 2011-09-14 14:58:53 | 0 |
| 3284 | 109 | 103 | 4 | 2011-09-14 14:58:53 | 0 |
| 3285 | 109 | 104 | 4 | 2011-09-14 14:58:53 | 0 |
| 3286 | 109 | 105 | 4 | 2011-09-14 14:58:53 | 0 |
| 3287 | 109 | 107 | 4 | 2011-09-14 14:58:53 | 0 |
| 3288 | 109 | 108 | 6 | 2011-09-14 14:58:53 | 0 |
| 3289 | 109 | 110 | 6 | 2011-09-14 14:58:53 | 0 |
| 3290 | 109 | 112 | 4 | 2011-09-14 14:58:53 | 0 |
| 3291 | 109 | 113 | 4 | 2011-09-14 14:58:53 | 0 |
| 3292 | 109 | 118 | 4 | 2011-09-14 14:58:53 | 0 |
| 3293 | 109 | 119 | 3 | 2011-09-14 14:58:53 | 0 |
| 3294 | 109 | 120 | 4 | 2011-09-14 14:58:53 | 0 |
| 3295 | 109 | 121 | 3 | 2011-09-14 14:58:53 | 0 |
| 3296 | 109 | 122 | 3 | 2011-09-14 14:58:53 | 0 |
| 3297 | 109 | 124 | 4 | 2011-09-14 14:58:53 | 0 |
| 3298 | 109 | 125 | 3 | 2011-09-14 14:58:53 | 0 |
| 3299 | 109 | 127 | 4 | 2011-09-14 14:58:53 | 0 |
| 3300 | 109 | 128 | 3 | 2011-09-14 14:58:53 | 0 |
| 3301 | 109 | 130 | 4 | 2011-09-14 14:58:53 | 0 |
| 3302 | 109 | 131 | 3 | 2011-09-14 14:58:53 | 0 |
| 3303 | 109 | 132 | 3 | 2011-09-14 14:58:53 | 0 |
| 3304 | 109 | 133 | 4 | 2011-09-14 14:58:53 | 0 |
| 3305 | 109 | 137 | 3 | 2011-09-14 14:58:53 | 0 |
| 3306 | 109 | 139 | 3 | 2011-09-14 14:58:53 | 0 |
| 3307 | 109 | 140 | 3 | 2011-09-14 14:58:53 | 0 |
| 3308 | 109 | 141 | 4 | 2011-09-14 14:58:53 | 0 |
| 3309 | 109 | 142 | 3 | 2011-09-14 14:58:53 | 0 |
| 3310 | 109 | 143 | 3 | 2011-09-14 14:58:53 | 0 |
| 3311 | 109 | 146 | 3 | 2011-09-14 14:58:53 | 0 |
| 3312 | 109 | 147 | 3 | 2011-09-14 14:58:53 | 0 |
| 3313 | 109 | 152 | 3 | 2011-09-14 14:58:53 | 0 |
| 3314 | 109 | 153 | 3 | 2011-09-14 14:58:53 | 0 |
| 3315 | 109 | 154 | 4 | 2011-09-14 14:58:53 | 0 |
| 3316 | 109 | 156 | 3 | 2011-09-14 14:58:53 | 0 |
| 3317 | 109 | 159 | 3 | 2011-09-14 14:58:53 | 0 |
| 3318 | 109 | 160 | 3 | 2011-09-14 14:58:53 | 0 |
| 3319 | 109 | 163 | 3 | 2011-09-14 14:58:53 | 0 |
| 3320 | 109 | 164 | 3 | 2011-09-14 14:58:53 | 0 |
| 3321 | 109 | 167 | 3 | 2011-09-14 14:58:53 | 0 |
| 3322 | 109 | 170 | 3 | 2011-09-14 14:58:53 | 0 |
| 3323 | 109 | 172 | 3 | 2011-09-14 14:58:53 | 0 |
| 3324 | 109 | 173 | 3 | 2011-09-14 14:58:53 | 0 |
| 3325 | 109 | 174 | 3 | 2011-09-14 14:58:53 | 0 |
| 3326 | 109 | 175 | 4 | 2011-09-14 14:58:53 | 0 |
| 3327 | 109 | 176 | 3 | 2011-09-14 14:58:53 | 0 |
| 3328 | 109 | 177 | 3 | 2011-09-14 14:58:53 | 0 |
| 3329 | 109 | 178 | 3 | 2011-09-14 14:58:53 | 0 |
| 3330 | 109 | 180 | 3 | 2011-09-14 14:58:53 | 0 |
| 3331 | 109 | 181 | 3 | 2011-09-14 14:58:53 | 0 |
| 3332 | 109 | 183 | 3 | 2011-09-14 14:58:53 | 0 |
| 3333 | 109 | 184 | 3 | 2011-09-14 14:58:53 | 0 |
| 3334 | 109 | 185 | 3 | 2011-09-14 14:58:53 | 0 |
| 3335 | 109 | 186 | 3 | 2011-09-14 14:58:53 | 0 |
| 3336 | 109 | 190 | 3 | 2011-09-14 14:58:53 | 0 |
| 3337 | 109 | 191 | 3 | 2011-09-14 14:58:53 | 0 |
| 3338 | 109 | 192 | 3 | 2011-09-14 14:58:53 | 0 |
| 3339 | 109 | 193 | 3 | 2011-09-14 14:58:53 | 0 |
| 3340 | 109 | 195 | 3 | 2011-09-14 14:58:53 | 0 |
| 3341 | 109 | 196 | 3 | 2011-09-14 14:58:53 | 0 |
| 3342 | 109 | 197 | 3 | 2011-09-14 14:58:53 | 0 |
| 3343 | 109 | 198 | 3 | 2011-09-14 14:58:53 | 0 |
| 3344 | 109 | 199 | 3 | 2011-09-14 14:58:53 | 0 |
| 3345 | 109 | 200 | 3 | 2011-09-14 14:58:53 | 0 |
| 3346 | 109 | 201 | 3 | 2011-09-14 14:58:53 | 0 |
| 3347 | 109 | 203 | 3 | 2011-09-14 14:58:53 | 0 |
+------+---------+------------+---------+---------------------+-------------------+
86 rows in set (0.00 sec)
Vemos que hay el mismo nº de registros. (86)
« Linux