Manuale Atlantis Evo

Operazioni preliminari

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

E' stato utile?