Synchronisation de tables InnoDB

Version imprimable

Bonjour à tous :)

Je travail sur un SGDB relationnel MySQL en InnoDB. J'ai une table produit contenant des produits avec un id_produit unique propre à chacun d'entre eux. Elle est définit comme suit :

Code:

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
CREATE TABLE IF NOT EXISTS `ps_product` (
  `id_product` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_supplier` int(10) UNSIGNED DEFAULT NULL,
  `id_manufacturer` int(10) UNSIGNED DEFAULT NULL,
  `id_category_default` int(10) UNSIGNED DEFAULT NULL,
  `id_shop_default` int(10) UNSIGNED NOT NULL DEFAULT '1',
  `id_tax_rules_group` int(11) UNSIGNED NOT NULL,
  `on_sale` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  `online_only` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  `ean13` varchar(13) DEFAULT NULL,
  `upc` varchar(12) DEFAULT NULL,
  `ecotax` decimal(17,6) NOT NULL DEFAULT '0.000000',
  `quantity` int(10) NOT NULL DEFAULT '0',
  `minimal_quantity` int(10) UNSIGNED NOT NULL DEFAULT '1',
  `price` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `wholesale_price` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `unity` varchar(255) DEFAULT NULL,
  `unit_price_ratio` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `additional_shipping_cost` decimal(20,2) NOT NULL DEFAULT '0.00',
  `reference` varchar(32) DEFAULT NULL,
  `supplier_reference` varchar(32) DEFAULT NULL,
  `location` varchar(64) DEFAULT NULL,
  `width` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `height` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `depth` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `weight` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `out_of_stock` int(10) UNSIGNED NOT NULL DEFAULT '2',
  `quantity_discount` tinyint(1) DEFAULT '0',
  `customizable` tinyint(2) NOT NULL DEFAULT '0',
  `uploadable_files` tinyint(4) NOT NULL DEFAULT '0',
  `text_fields` tinyint(4) NOT NULL DEFAULT '0',
  `active` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  `redirect_type` enum('','404','301','302') NOT NULL DEFAULT '',
  `id_product_redirected` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `available_for_order` tinyint(1) NOT NULL DEFAULT '1',
  `available_date` date NOT NULL DEFAULT '0000-00-00',
  `condition` enum('new','used','refurbished') NOT NULL DEFAULT 'new',
  `show_price` tinyint(1) NOT NULL DEFAULT '1',
  `indexed` tinyint(1) NOT NULL DEFAULT '0',
  `visibility` enum('both','catalog','search','none') NOT NULL DEFAULT 'both',
  `cache_is_pack` tinyint(1) NOT NULL DEFAULT '0',
  `cache_has_attachments` tinyint(1) NOT NULL DEFAULT '0',
  `is_virtual` tinyint(1) NOT NULL DEFAULT '0',
  `cache_default_attribute` int(10) UNSIGNED DEFAULT NULL,
  `date_add` datetime NOT NULL,
  `date_upd` datetime NOT NULL,
  `advanced_stock_management` tinyint(1) NOT NULL DEFAULT '0',
  `pack_stock_type` int(11) UNSIGNED NOT NULL DEFAULT '3',
  PRIMARY KEY (`id_product`),
  KEY `product_supplier` (`id_supplier`),
  KEY `product_manufacturer` (`id_manufacturer`,`id_product`),
  KEY `id_category_default` (`id_category_default`),
  KEY `indexed` (`indexed`),
  KEY `date_add` (`date_add`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

Je doit créer une autre table d'objets info_produit propres à tel ou tel produit de la première table.
J'aimerai que si ma table produit à des produits effacé, ma table info_produit soit synchronisée en fonction. J'aimerai aussi que si les id_produit de la table produit venait à changer, il soit mis à jour dans la table info_produit. Seuls impératifs, je ne peut pas modifier la table produit et je ne peut pas faire cette synchronisation lors de l'altération de la table produit.

Est ce possible ?

04/10/2016, 10h23
al1_24

Tout cela devrait être pris en charge par les clause ON DELETE CASCADE et ON UPDATE CASCADE de la définition d'un clé étrangère (FOREIGN KEY).
04/10/2016, 17h49
aieeeuuuuu

Bonjour,

Quel est le besoin derrière tout ça ? pourquoi ne pas simplement faire une vue ?
05/10/2016, 03h41
Artemus24

Salut BeRoots.

Si je comprends bien, vous n'avez pas le droit de toucher à la table produit.

Pour synchroniser vos deux tables, le mieux est de faire un trigger "delete" pour la suppression et un trigger "insert" pour la création.
--> https://dev.mysql.com/doc/refman/5.5...er-syntax.html

Et comme le dit "aieeeuuuuu", pour quelle raison avez-vous besoin de dupliquer des produits dans une autre tables ?
Une view est en effet une solution. Il n'y a aucune mise à jour, et vous extrayez ce dont vous avez besoin.

Le mieux est de nous expliquer la finalité de votre problème et non la solution que vous envisagez de faire.

@+
16/10/2016, 21h13
BeRoots

Bonjour et merci pour vos réponse. La méthode ON DELETE & ON UPDATE CASCADE me va très bien.
Pour plus d'informations, en fait j'ai ma table produit et j'ai une table info_produit pour un module. Je ne recopie pas les produits dans cette table mais juste l'id du produit si je créé une info sur ce denier. Mon soucis est que dans ce module je ne suis pas mettre de ce qui ce fait sur le produit réel. En fait si un produit est créé, on peut créé une info ou ne pas le faire. Mon module gère tout cela et je veut juste évité d'avoir à verifier à chaque instance de ce module si des produit on été ajouter ou modifier pour verifier que la corélation avec la table ce ce module soit parraine ;)

Encore merci pour vos conseils ;)

Je reviens vers vous pour un petit soucis avec mes CASCADE :ptdr:

En fait la première contrainte que j'utilise est parfaite car elle me permet bien de faire le ménage dans ma table product_productextras (voir ci-dessous) si un produit venait à être supprimé.
Par contre si cette suppression d'un produit entraine la suppression d'un id_pei dans ma table product_productextras, j'aimerai que ma seconde table (product_productextras_lang) soit aussi nettoyer de tout tuple ayant cet id_pei.
Comme vous pouvez le voir j'ai essayer de refaire une contrainte de même style mais apparement avec celle-ci je peut plus faire d'update sur cette table product_productextras_lang :(
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 CREATE TABLE IF NOT EXISTS `product_productextras` ( `id_pei` INT UNSIGNED NOT NULL AUTO_INCREMENT, `id_product` int(10) UNSIGNED NOT NULL, PRIMARY KEY (`id_pei`), CONSTRAINT `fk_productextras_id_product` FOREIGN KEY (`id_product`) REFERENCES `product`(`id_product`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `product_productextras_lang` ( `id_pei` INT UNSIGNED NOT NULL AUTO_INCREMENT, `id_lang` int(10) unsigned NOT NULL, `notice` text DEFAULT NULL, `about` text DEFAULT NULL, PRIMARY KEY (`id_pei`, `id_lang`), CONSTRAINT `fk_productextras_id_pei` FOREIGN KEY (`id_pei`) REFERENCES `product_productextras`(`id_pei`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Si quelqu'un à une idée ;)
Merci d'avance.

Les foreigns keys CASCADED n'activent pas d'autre trigger donc ta modélisation ne peut pas fonctionner :

https://dev.mysql.com/doc/refman/5.6...eign-keys.html

Pourquoi avoir mis id_pei avec un AUTO_INCREMENT alors que la valeur de ce champs est défini par product_productextras ?

Tu vas devoir trouver un autre modèle, une solution simple serait de référencer id_product de ps_product dans ta seconde table.
Mais comme product_productextras est en pratique une table pivot sans réel intérêt, le plus élégant est de procéder de la manière suivante :
Code:

1 2 3 4 5 6 7 8 9 10 11 CREATE TABLE IF NOT EXISTS `product_extras` ( `id_pei` INT UNSIGNED NOT NULL AUTO_INCREMENT, `id_product` int(10) UNSIGNED NOT NULL, `id_lang` int(10) unsigned NOT NULL, `notice` text DEFAULT NULL, `about` text DEFAULT NULL, PRIMARY KEY (`id_pei`), UNIQUE KEY (`id_product`,`id_lang`), CONSTRAINT `fk_productextras_id_product` FOREIGN KEY (`id_product`) REFERENCES `ps_product` (`id_product`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

03/11/2016, 09h07
escartefigue

Outre la judicieuse remarque de ddoumeche, j'ajoute qu'en fonction du volume dans la nouvelle table, il est sans doute préférable de remplacer le delete cascade par un delete set null, et de traiter la suppression réelle par batch en heures creuses

Salut à tous.

La première contrainte que vous avez définie entre la table mère "product" et la table fille "product_productextras" fonctionnera parfaitement.

Citation:

Envoyé par BeRoots

Par contre si cette suppression d'un produit entraîne la suppression d'un id_pei dans ma table product_productextras, j'aimerai que ma seconde table (product_productextras_lang) soit aussi nettoyer de tout tuple ayant cet id_pei.

Rien ne vous empêche de créer une contrainte de type clef étrangère entre la table mère "product_productextras" et la table fille "product_productextras_lang" sur la colonne "id_pei".

Voici un exemple, sur l'effet du cascade entre trois tables liées :

Code:

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
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
SET collation_connection = latin1_general_ci
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `product`
--------------
 
--------------
CREATE TABLE IF NOT EXISTS `product`
( `id_product`   integer UNSIGNED NOT NULL auto_increment,
  `lib`          varchar(255)     NOT NULL,
  PRIMARY KEY (`id_product`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `product` (`id_product`,`lib`) values
  (101, 'un'),(102, 'deux'),(103, 'trois'),(104, 'quatre')
--------------
 
--------------
select * from product order by id_product
--------------
 
+------------+--------+
| id_product | lib    |
+------------+--------+
|        101 | un     |
|        102 | deux   |
|        103 | trois  |
|        104 | quatre |
+------------+--------+
--------------
DROP TABLE IF EXISTS `product_productextras`
--------------
 
--------------
CREATE TABLE IF NOT EXISTS `product_productextras`
( `id_pei`       integer UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_product`   integer UNSIGNED NOT NULL,
  PRIMARY KEY (`id_pei`),
  CONSTRAINT `fk_01_id_product` FOREIGN KEY (`id_product`) REFERENCES `product` (`id_product`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `product_productextras` (`id_product`) values (101),(102),(103),(101),(102),(104)
--------------
 
--------------
select * from product_productextras order by id_pei, id_product
--------------
 
+--------+------------+
| id_pei | id_product |
+--------+------------+
|      1 |        101 |
|      2 |        102 |
|      3 |        103 |
|      4 |        101 |
|      5 |        102 |
|      6 |        104 |
+--------+------------+
--------------
DROP TABLE IF EXISTS `product_productextras_lang`
--------------
 
--------------
CREATE TABLE IF NOT EXISTS `product_productextras_lang`
( `id_pei`    integer unsigned NOT NULL,
  `id_lang`   integer unsigned NOT NULL,
  `notice`    text DEFAULT NULL,
  `about`     text DEFAULT NULL,
  PRIMARY KEY (`id_pei`, `id_lang`),
  CONSTRAINT `fk_02_id_pei` FOREIGN KEY (`id_pei`) REFERENCES `product_productextras` (`id_pei`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `product_productextras_lang` (`id_pei`,`id_lang`,`notice`,`about`) values
  (1, 33, 'alpha',   ''),  (1, 45, 'beta',  ''),
  (2, 26, 'gamma',   ''),  (2, 33, 'delta', ''),
  (3, 33, 'epsilon', ''),  (3, 45, 'zeta',  ''),
  (4, 26, 'eta',     ''),  (4, 33, 'theta', ''),
  (5, 33, 'iota',    ''),  (5, 45, 'kappa', ''),
  (6, 26, 'lambda',  ''),  (6, 33, 'mu',    '')
--------------
 
--------------
select * from product_productextras_lang order by id_pei, id_lang
--------------
 
+--------+---------+---------+-------+
| id_pei | id_lang | notice  | about |
+--------+---------+---------+-------+
|      1 |      33 | alpha   |       |
|      1 |      45 | beta    |       |
|      2 |      26 | gamma   |       |
|      2 |      33 | delta   |       |
|      3 |      33 | epsilon |       |
|      3 |      45 | zeta    |       |
|      4 |      26 | eta     |       |
|      4 |      33 | theta   |       |
|      5 |      33 | iota    |       |
|      5 |      45 | kappa   |       |
|      6 |      26 | lambda  |       |
|      6 |      33 | mu      |       |
+--------+---------+---------+-------+
--------------
delete from `product`
where id_product = 102
--------------
 
--------------
commit
--------------
 
--------------
select * from product                      order by id_product
--------------
 
+------------+--------+
| id_product | lib    |
+------------+--------+
|        101 | un     |
|        103 | trois  |
|        104 | quatre |
+------------+--------+
--------------
select * from product_productextras        order by id_pei, id_product
--------------
 
+--------+------------+
| id_pei | id_product |
+--------+------------+
|      1 |        101 |
|      3 |        103 |
|      4 |        101 |
|      6 |        104 |
+--------+------------+
--------------
select * from product_productextras_lang   order by id_pei, id_lang
--------------
 
+--------+---------+---------+-------+
| id_pei | id_lang | notice  | about |
+--------+---------+---------+-------+
|      1 |      33 | alpha   |       |
|      1 |      45 | beta    |       |
|      3 |      33 | epsilon |       |
|      3 |      45 | zeta    |       |
|      4 |      26 | eta     |       |
|      4 |      33 | theta   |       |
|      6 |      26 | lambda  |       |
|      6 |      33 | mu      |       |
+--------+---------+---------+-------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

Citation:

Envoyé par BeRoots

... mais apparemment avec celle-ci je peux plus faire d'update sur cette table product_productextras_lang

Pour modifier votre table "product_productextras_lang", vous devez impérativement avoir la valeur de la colonne "id_pei" déjà présente dans la table "product_productextras".

De même, pour modifier votre table "product_productextras", vous devez impérativement avoir la valeur de la colonne "id_product" déjà présente dans la table "product".

Sans cela, l'insertion ou la modification ne peut pas se faire car c'est un problème d'intégrité.

Inversement, je remets en cause l'existence de la table intermédiaire `product_productextras` qui n'apporte rien de probant dans votre modélisation des données.
Pourquoi ne pas fusionner la table `product_productextras` avec la table `product_productextras_lang` ?
Autrement dit, la colonne `id_pei` ne sert à rien car elle peut-être remplacé par `id_product`.

@+