Changer le type d'identifiant

**merlin867** · 28/09/2016, 23h51

Bonjour à tous,

Je suis le webmestre d'une application php sur la généalogie. Pour faire bref, les divers identifications de personnes, familles, enfants, évenements sont constitué d'un nombre + une lettre servant à déterminer le type d'identification.

Par exemple une personne aura 12345I Le I étant pour Individu
Une famille aura 12345U etc...
Vous comprenez le principe jusqu'ici!

La beauté de ce logiciel est que l'on peut choisir d'avoir l'identifiant en préfixe OU en suffixe... Donc la lettre est soit en avant ou soit à la fin... Mais pas un ET l'autre!!!
Moi j'ai joué avec en mode suffixe durant une bonne année et là tout d'un coup sans que je m'en rend compte, le logiciel est passé en mode préfixe... Alors je vous laisse imaginer la pagaille que cela fait lors que je dois "ajuster" d'anciens enregistrement avec le logiciels... Ca fait plein de bug.

La solution serait de faire passer tout les identifiants avec la lettre devant. Mais faire ça un par un serait un travail d'une vie!!! J'imagine que je pourrais utiliser une requête pour faire le travail colonne par colonne. Je ne suis vraiment pas assez compétent pour faire cette requête... Mais je peux la faire sur chaque colonne nécessaire.

les type d'identifiant sont: I, U, S
Le nombre de chiffre pour chaque peut varier, allant de 1 à 6 (le plus gros que j'ai pu voir)

Dans le fond ce que je voudrais c'est de pouvoir ramener la lettre préfixe lorsqu'elle est en suffixe et ne pas y toucher quand elle est déjà en préfixe.
Moi je vais m'occuper par la suite de faire rouler la requête pour chaque colonne où c'est nécessaire.

exemple de type de donnée acutellement:
99945I
99951I
99953I
99961I
99963I
999I
I1003
I1005
I1012
I1014
I1015
I1023
I1041

J'espère avoir été assez clair... Des fois quand on en dit trop, on mélange les cartes!!

UN gros merci,
Francois

**vttman** · 29/09/2016, 09h51

[Edit] j'oublie souvent les politesses

Bonjour François,

Je propose pour démarrer ce code ...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
SELECT chp1, concat( substr(chp1,length(chp1),1) , substr(chp1,1,length(chp1)-1)) FROM `matable` where chp1 REGEXP '^[0,9]'

REGEXP '^[0,9]' => Sélection Chaine commençant par un chiffre
substr(chp1,length(chp1),1) => je récupère le dernier caractère (normalement alphabétique)
substr(chp1,1,length(chp1)-1) => je récupère la partie "numérique"
concat( substr(chp1,length(chp1),1) , substr(chp1,1,length(chp1)-1)) => Je concatène le caractère alphabétique à la partie "numérique"

à tester et me dire ...

**escartefigue** · 29/09/2016, 10h53

Bonjour,

Si vous voulez vous affranchir de ce genre de contingences, respectez les formes normales et les règles de modélisation (ou demandez à votre fournisseur de base de données de le faire)

Si cet identifiant est votre id primaire, il doit être asémantique afin d'être stable, donc dépourvu de toute signification, donc les notions I/U/S sont à évacuer de l'identifiant

Que cet identifiant soit votre id primaire ou pas, il doit, comme tout attribut du SI, être atomique, si vous avez 2 informations à connaitre, le chrono et le type, alors créez 2 colonnes distinctes
Ainsi vous les assemblerez comme vous souhaitez et vous ne serez plus impactés par les changements de format d'échange avec les tiers.

**SQLpro** · 29/09/2016, 12h00

Envoyé par merlin867

Bonjour à tous,

Je suis le webmestre d'une application php sur la généalogie. Pour faire bref, les divers identifications de personnes, familles, enfants, évenements sont constitué d'un nombre + une lettre servant à déterminer le type d'identification.

Par exemple une personne aura 12345I Le I étant pour Individu
Une famille aura 12345U etc...
Vous comprenez le principe jusqu'ici!

La beauté de ce logiciel

ça n'est pas beau, c'est juste une grosse connerie qui viole la première forme normale et vous posera de multiples problèmes de performances et vous obligera à élaborer des requêtes complexes qui n'aurons jamais la possibilité de bénéficier des index !
D'ailleurs la requête donnée par notre cycliste (vttman) ne peut pas être optimisée

À lire sur le sujet : http://fsmrel.developpez.com/basesre...sation/?page=2

A +

**vttman** · 29/09/2016, 13h06

Envoyé par SQLpro

ça n'est pas beau, c'est juste une grosse connerie qui viole la première forme normale et vous posera de multiples problèmes de performances et vous obligera à élaborer des requêtes complexes qui n'aurons jamais la possibilité de bénéficier des index !
D'ailleurs la requête donnée par notre cycliste (vttman) ne peut pas être optimisée

À lire sur le sujet : http://fsmrel.developpez.com/basesre...sation/?page=2

A +

Parfois je me borne à répondre strictement au besoin exprimé ... en un coup de pédale c'est vrai,
et c'est sans doute un défaut ... mais s'il faut à chaque fois tout rependre de zéro : mcd, mpd, utilité d'une clé primaire, d'une contrainte d'intégrité, d'un index, bon ça risque
de faire fuir le tout venant ...

**escartefigue** · 29/09/2016, 13h17

Envoyé par vttman

Parfois je me borne à répondre strictement au besoin exprimé ... en un coup de pédale c'est vrai,
et c'est sans doute un défaut ... mais s'il faut à chaque fois tout rependre de zéro : mcd, mpd, utilité d'une clé primaire, d'une contrainte d'intégrité, d'un index, bon ça risque
de faire fuir le tout venant ...

Il y a deux approches
- la solution immédiatement applicable à moindre frais sur le court terme, sans remise en cause de l'existant
- la solution plus en profondeur, en commençant par se demander pourquoi on en est là, et qui peut aller jusqu'à remettre en cause l'architecture de la solution ou la structure de la base de données

Dans le cas où comme ici, les fondamentaux ne sont pas respectés, la deuxième voix est à privilégier, la première ne serait qu'un pansement sur une jambe de bois

Le demandeur peut (doit) se faire aider, si la solution dépasse son niveau de compétence ou ses attributions.

**vttman** · 29/09/2016, 13h29

Envoyé par escartefigue

Il y a deux approches
- la solution immédiatement applicable à moindre frais sur le court terme, sans remise en cause de l'existant
- la solution plus en profondeur, en commençant par se demander pourquoi on en est là, et qui peut aller jusqu'à remettre en cause l'architecture de la solution ou la structure de la base de données

Dans le cas où comme ici, les fondamentaux ne sont pas respectés, la deuxième voix est à privilégier, la première ne serait qu'un pansement sur une jambe de bois

Le demandeur peut (doit) se faire aider, si la solution dépasse son niveau de compétence ou ses attributions.

Je suis d'accord avec ça évidemment ... si le projet est professionnel !

Dans le cas où une personne se débrouille et se fait plaisir ... je tente de l'aider et plus ... si on se connait ...

Dans le cas d'un forum je me place plutôt dans l'instant et non le moyen ou long terme.
Je propose, le forumeur dispose. Je demande, des forumeurs répondent et je fais mon marché ...

Mais bon faut-il que je relise la charte du forum ? J'ai peut-être quitté la route et je roule dans le fossé !?

**escartefigue** · 29/09/2016, 13h43

Envoyé par vttman

Dans le cas où une personne se débrouille et se fait plaisir ... je tente de l'aider et plus ... si on se connait ...

L'aider à se faire plaisir par exemple ?

Envoyé par vttman

Mais bon faut-il que je relise la charte du forum ? J'ai peut-être quitté la route et je roule dans le fossé !?

Plus sérieusement, non, c'est seulement une histoire d'approche (sans connotation ni rapport avec le plaisir évoqué plus haut

)
J'illustre par l'exemple suivant :
On trouve régulièrement des questions sur ce forum pour demander comment "boucher les trous" dans les n° identifiants auto-incrémentés, suite à des suppression, ou comment les renuméroter
On peut répondre techniquement à cette question, sans se demander pourquoi
On peut aussi creuser et se rendre compte que le plus souvent, le demandeur utilise à tort la valeur d'identifiant pour identifier un ordre supposé d'arrivée... et là c'est le drame

**vttman** · 29/09/2016, 13h47

Envoyé par escartefigue

L'aider à se faire plaisir par exemple ?

Plus sérieusement, non, c'est seulement une histoire d'approche (sans connotation ni rapport avec le plaisir évoqué plus haut

)
J'illustre par l'exemple suivant :
On trouve régulièrement des questions sur ce forum pour demander comment "boucher les trous" dans les n° identifiants auto-incrémentés, suite à des suppression, ou comment les renuméroter
On peut répondre techniquement à cette question, sans se demander pourquoi
On peut aussi creuser et se rendre compte que le plus souvent, le demandeur utilise à tort la valeur d'identifiant pour identifier un ordre supposé d'arrivée... et là c'est le drame

Je n'ai rien à redire

c'est fort juste !

**Artemus24** · 29/09/2016, 17h19

Salut à tous.

Envoyé par vttman

Parfois je me borne à répondre strictement au besoin exprimé ... en un coup de pédale c'est vrai, et c'est sans doute un défaut ...

Alors nous sommes deux à pratiquer ce défaut.

Envoyé par Escartefigue

Dans le cas où comme ici, les fondamentaux ne sont pas respectés, la deuxième voix est à privilégier, la première ne serait qu'un pansement sur une jambe de bois

Je comprends ton coté perfectionniste Escartefigue, mais vu que j'ai fait pas mal de service, le client ne demande pas de refaire à fond un produit qui fonctionne même d'une manière bancale.
Et si tu viens lui changer ses petits habitudes, il va te ruer dans les brancards.
Ce qu'il demande, c'est de résoudre son problème et rien de plus !

En tout cas, vous êtes hors sujet car vous ne répondez pas à la question posée par merlin867.

Voici ce que je propose :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`           integer unsigned NOT NULL auto_increment primary key,
  `identifiant`  varchar(255)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test` (`identifiant`) values
  ('99945I'), ('99951I'), ('99953I'), ('99961I'), ('99963I'), ('999I'), ('I1003'), ('I1005'), ('I1012'), ('I1014'), ('I1015'), ('I1023'), ('I1041')
--------------
 
--------------
select * from test
--------------
 
+----+-------------+
| id | identifiant |
+----+-------------+
|  1 | 99945I      |
|  2 | 99951I      |
|  3 | 99953I      |
|  4 | 99961I      |
|  5 | 99963I      |
|  6 | 999I        |
|  7 | I1003       |
|  8 | I1005       |
|  9 | I1012       |
| 10 | I1014       |
| 11 | I1015       |
| 12 | I1023       |
| 13 | I1041       |
+----+-------------+
--------------
select id, right(identifiant, 1) as lettre, left(identifiant, length(identifiant) - 1) as chiffre
from test having lettre REGEXP '^[a-z,A-Z]$'
--------------
 
+----+--------+---------+
| id | lettre | chiffre |
+----+--------+---------+
|  1 | I      | 99945   |
|  2 | I      | 99951   |
|  3 | I      | 99953   |
|  4 | I      | 99961   |
|  5 | I      | 99963   |
|  6 | I      | 999     |
+----+--------+---------+
--------------
alter table `test`  add column lettre  char(01)         NOT NULL after identifiant
--------------
 
--------------
alter table `test`  add column chiffre integer unsigned NOT NULL after lettre
--------------
 
--------------
update test set lettre  = case when identifiant regexp '^[0-9].*$'
                               then right(identifiant, 1)
                               else  left(identifiant, 1)
                               end,
                chiffre = case when identifiant regexp '^[0-9].*$'
                               then cast( left(identifiant, length(identifiant) - 1) as unsigned)
                               else cast(right(identifiant, length(identifiant) - 1) as unsigned)
                               end
--------------
 
--------------
update test set identifiant = concat(right(identifiant, 1), left(identifiant, length(identifiant) - 1))
where identifiant regexp '^[0-9].*$'
--------------
 
--------------
select * from test
--------------
 
+----+-------------+--------+---------+
| id | identifiant | lettre | chiffre |
+----+-------------+--------+---------+
|  1 | I99945      | I      |   99945 |
|  2 | I99951      | I      |   99951 |
|  3 | I99953      | I      |   99953 |
|  4 | I99961      | I      |   99961 |
|  5 | I99963      | I      |   99963 |
|  6 | I999        | I      |     999 |
|  7 | I1003       | I      |    1003 |
|  8 | I1005       | I      |    1005 |
|  9 | I1012       | I      |    1012 |
| 10 | I1014       | I      |    1014 |
| 11 | I1015       | I      |    1015 |
| 12 | I1023       | I      |    1023 |
| 13 | I1041       | I      |    1041 |
+----+-------------+--------+---------+
--------------
describe test
--------------
 
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| identifiant | varchar(255)     | NO   |     | NULL    |                |
| lettre      | char(1)          | NO   |     | NULL    |                |
| chiffre     | int(10) unsigned | NO   |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

@+

**vttman** · 29/09/2016, 17h42

Envoyé par Artemus24

Salut à tous.

En tout cas, vous êtes hors sujet car vous ne répondez pas à la question posée par merlin867.

@+

ça c'est encore à voir

De toute façon merlin867 corrigerait si ça ne convenait pas ... une fois qu'on a des pistes/indications c'est plus facile ... normalement.

**merlin867** · 29/09/2016, 21h53

Bonjour!!

Merci à ceux qui propose des solutions!

Je ne l'ai peut-être pas mentionné mais je ne suis pas l'auteur du logiciel. Je ne fais que l'utiliser et l'améliorer au mieux des mes connaissances. Devoir changer toute la structure de la base donnée est hors de mes compétences et hors de mon temps disponible. Et puis, c'est le seul logiciel à faire ce qu'il fait. Il n'est peut-être pas parfait et c'est comme ça, c'est tout. Il faut vivre avec.

Pour ce qui est de vos suggestions de code. Je suis présentement sur la route et je vais tester ça dès mon retour à la maison demain soir. J'ai vraiment hâte de voir si avec cela, je vais pouvoir régler mon "petit" problème.

Merci
Francois

**Artemus24** · 30/09/2016, 01h06

@ vttman : merci pour le '-1' !

**vttman** · 30/09/2016, 08h43

Artemus24, je ne mets pas de -1 ... par contre je peux faire des erreurs de clics

Qu'est ce que je fais alors ? Je clique sur le -1 pour tenter de l'enlever et si c'est pas moi ça va faire -2 ?

**merlin867** · 01/10/2016, 13h54

Envoyé par Artemus24

Salut à tous.

Voici ce que je propose :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`           integer unsigned NOT NULL auto_increment primary key,
  `identifiant`  varchar(255)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test` (`identifiant`) values
  ('99945I'), ('99951I'), ('99953I'), ('99961I'), ('99963I'), ('999I'), ('I1003'), ('I1005'), ('I1012'), ('I1014'), ('I1015'), ('I1023'), ('I1041')
--------------
 
--------------
select * from test
--------------
 
+----+-------------+
| id | identifiant |
+----+-------------+
|  1 | 99945I      |
|  2 | 99951I      |
|  3 | 99953I      |
|  4 | 99961I      |
|  5 | 99963I      |
|  6 | 999I        |
|  7 | I1003       |
|  8 | I1005       |
|  9 | I1012       |
| 10 | I1014       |
| 11 | I1015       |
| 12 | I1023       |
| 13 | I1041       |
+----+-------------+
--------------
select id, right(identifiant, 1) as lettre, left(identifiant, length(identifiant) - 1) as chiffre
from test having lettre REGEXP '^[a-z,A-Z]$'
--------------
 
+----+--------+---------+
| id | lettre | chiffre |
+----+--------+---------+
|  1 | I      | 99945   |
|  2 | I      | 99951   |
|  3 | I      | 99953   |
|  4 | I      | 99961   |
|  5 | I      | 99963   |
|  6 | I      | 999     |
+----+--------+---------+
--------------
alter table `test`  add column lettre  char(01)         NOT NULL after identifiant
--------------
 
--------------
alter table `test`  add column chiffre integer unsigned NOT NULL after lettre
--------------
 
--------------
update test set lettre  = case when identifiant regexp '^[0-9].*$'
                               then right(identifiant, 1)
                               else  left(identifiant, 1)
                               end,
                chiffre = case when identifiant regexp '^[0-9].*$'
                               then cast( left(identifiant, length(identifiant) - 1) as unsigned)
                               else cast(right(identifiant, length(identifiant) - 1) as unsigned)
                               end
--------------
 
--------------
update test set identifiant = concat(right(identifiant, 1), left(identifiant, length(identifiant) - 1))
where identifiant regexp '^[0-9].*$'
--------------
 
--------------
select * from test
--------------
 
+----+-------------+--------+---------+
| id | identifiant | lettre | chiffre |
+----+-------------+--------+---------+
|  1 | I99945      | I      |   99945 |
|  2 | I99951      | I      |   99951 |
|  3 | I99953      | I      |   99953 |
|  4 | I99961      | I      |   99961 |
|  5 | I99963      | I      |   99963 |
|  6 | I999        | I      |     999 |
|  7 | I1003       | I      |    1003 |
|  8 | I1005       | I      |    1005 |
|  9 | I1012       | I      |    1012 |
| 10 | I1014       | I      |    1014 |
| 11 | I1015       | I      |    1015 |
| 12 | I1023       | I      |    1023 |
| 13 | I1041       | I      |    1041 |
+----+-------------+--------+---------+
--------------
describe test
--------------
 
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| identifiant | varchar(255)     | NO   |     | NULL    |                |
| lettre      | char(1)          | NO   |     | NULL    |                |
| chiffre     | int(10) unsigned | NO   |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

@+