Creare le seguenti tabelle:
| ## ## Product ##CREATE TABLE IF NOT EXISTS `sync_prod` ( `id` int(11) NOT NULL AUTO_INCREMENT, `op` char(1) NOT NULL, `product_id` int(11) NOT NULL, `product_sku` varchar(64) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `sync_prod_unique` (`op`,`product_id`,`product_sku`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1; |
| ## ## order item ##CREATE TABLE IF NOT EXISTS `sync_orditem` ( `id` int(11) NOT NULL AUTO_INCREMENT, `op` char(1) NOT NULL, `order_id` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `sync_orditem_unique` (`op`,`order_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1; |
| ## ## User ##CREATE TABLE IF NOT EXISTS `sync_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `op` char(1) NOT NULL, `user_id` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `sync_user_unique` (`op`,`user_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1; |
Trigger da attivare su MySql per le tabelle appena create:
| CREATE TRIGGER product_insert AFTER INSERT ON jos_vm_product FOR EACH ROW INSERT INTO sync_prod (op,product_id,product_sku) VALUES (‘I’,NEW.product_id,NEW.product_sku);CREATE TRIGGER product_delete AFTER DELETE ON jos_vm_product FOR EACH ROW INSERT INTO sync_prod (op,product_id,product_sku) VALUES (‘D’,OLD.product_id,OLD.product_sku);DELIMITER $$ CREATE TRIGGER product_update AFTER UPDATE ON jos_vm_product FOR EACH ROW BEGIN IF NEW.product_in_stock=OLD.product_in_stock THEN REPLACE INTO sync_prod (op,product_id,product_sku) VALUES (‘U’,NEW.product_id,NEW.product_sku); END IF; END$$ DELIMITER ; |
| CREATE TRIGGER orderitem_insert AFTER INSERT ON jos_vm_order_item FOR EACH ROW INSERT INTO sync_orditem (op,order_id) VALUES (‘I’,NEW.order_id);CREATE TRIGGER orderitem_delete AFTER DELETE ON jos_vm_order_item FOR EACH ROW INSERT INTO sync_orditem (op,order_id) VALUES (‘D’,OLD.order_id);DELIMITER $$ CREATE TRIGGER orderitem_update AFTER UPDATE ON jos_vm_order_item FOR EACH ROW BEGIN REPLACE INTO sync_orditem (op,order_id) VALUES (‘U’,NEW.order_id); END$$ DELIMITER ; |
| CREATE TRIGGER user_insert AFTER INSERT ON jos_vm_user_info FOR EACH ROW INSERT INTO sync_user (op,user_id) VALUES (‘I’,NEW.user_id);CREATE TRIGGER user_delete AFTER DELETE ON jos_vm_user_info FOR EACH ROW INSERT INTO sync_user (op,user_id) VALUES (‘D’,OLD.user_id);DELIMITER $$ CREATE TRIGGER user_update AFTER UPDATE ON jos_vm_user_info FOR EACH ROW BEGIN REPLACE INTO sync_user (op,user_id) VALUES (‘U’,NEW.user_id); END$$ DELIMITER ; |
Esempio di sync_prod:
| id | op | product_id | product_sku |
|---|---|---|---|
| 1 | D | 1 | ZZZ |
| 2 | I | 810 | A011M |
| 3 | I | 811 | P22 |
Esempio di sync_orditem:
| id | op | order_id |
|---|---|---|
| 1 | I | 1 |
| 2 | U | 1 |
| 3 | D | 1 |
Query per riempire tabelle prima installazione:
| INSERT INTO sync_user (op, user_id) SELECT ‘U’,ui.user_id FROM jos_vm_user_info ui,jos_users u,jos_vm_shopper_vendor_xref rf WHERE address_type =’BT’ AND ui.user_id=u.id AND rf.user_id=u.id GROUP BY ui.user_id INSERT INTO sync_prod (op,product_id,product_sku) SELECT distinct ‘U’,product_id,product_sku from jos_vm_productINSERT INTO sync_orditem (op,order_id) SELECT distinct ‘U’,order_id from jos_vm_order_item oppure INSERT INTO sync_orditem (op,order_id) SELECT DISTINCT ‘U’, order_id FROM jos_vm_orders WHERE left( FROM_UNIXTIME( cdate ) , 4 ) = ‘2012’ ORDER BY order_id |