ps-sync/ps-sync-producer/sql/products.sql

-- ----------------------------
-- Triggers structure for table products
-- ----------------------------
DROP TRIGGER IF EXISTS `sync$products_i`;
delimiter ;;
CREATE DEFINER = `root`@`%` TRIGGER `sync$products_i` AFTER INSERT ON `products` FOR EACH ROW begin
	  declare v_table_name varchar(64) default 'products';
    declare v_method_type varchar(6) default 'insert';
    declare v_data_key text;
    declare v_data text;
    declare v_priority int default 1;

    set v_data_key=concat('{"pr_id": ', new.pr_id, '}');
    select concat(
      concat('{'),
      -- varchar
      concat('"pr_title": ', case when new.pr_title is null then 'null' else concat('"', replace(new.pr_title, '"', '\\"'), '"') end),
      concat(',"pr_code": ', case when new.pr_code is null then 'null' else concat('"', replace(new.pr_code, '"', '\\"'), '"') end),
      concat(',"pr_spec": ', case when new.pr_spec is null then 'null' else concat('"', replace(new.pr_spec, '"', '\\"'), '"') end),
      concat(',"pr_unit": ', case when new.pr_unit is null then 'null' else concat('"', replace(new.pr_unit, '"', '\\"'), '"') end),
      concat(',"pr_brand": ', case when new.pr_brand is null then 'null' else concat('"', replace(new.pr_brand, '"', '\\"'), '"') end),
      concat(',"pr_cmpcode": ', case when new.pr_cmpcode is null then 'null' else concat('"', replace(new.pr_cmpcode, '"', '\\"'), '"') end),
      concat(',"pr_cmpuuid": ', case when new.pr_cmpuuid is null then 'null' else concat('"', replace(new.pr_cmpuuid, '"', '\\"'), '"') end),
      concat(',"pr_sourceapp": ', case when new.pr_sourceapp is null then 'null' else concat('"', replace(new.pr_sourceapp, '"', '\\"'), '"') end),
      concat(',"pr_kind": ', case when new.pr_kind is null then 'null' else concat('"', replace(new.pr_kind, '"', '\\"'), '"') end),
      concat(',"pr_kinden": ', case when new.pr_kinden is null then 'null' else concat('"', replace(new.pr_kinden, '"', '\\"'), '"') end),
      concat(',"pr_pbrand": ', case when new.pr_pbrand is null then 'null' else concat('"', replace(new.pr_pbrand, '"', '\\"'), '"') end),
      concat(',"pr_pbranden": ', case when new.pr_pbranden is null then 'null' else concat('"', replace(new.pr_pbranden, '"', '\\"'), '"') end),
      concat(',"pr_pbranduuid": ', case when new.pr_pbranduuid is null then 'null' else concat('"', replace(new.pr_pbranduuid, '"', '\\"'), '"') end),
      concat(',"pr_pcmpcode": ', case when new.pr_pcmpcode is null then 'null' else concat('"', replace(new.pr_pcmpcode, '"', '\\"'), '"') end),
      concat(',"pr_attachment": ', case when new.pr_attachment is null then 'null' else concat('"', replace(new.pr_attachment, '"', '\\"'), '"') end),
      concat(',"pr_encapsulation": ', case when new.pr_encapsulation is null then 'null' else concat('"', replace(new.pr_encapsulation, '"', '\\"'), '"') end),
      concat(',"pr_packaging": ', case when new.pr_packaging is null then 'null' else concat('"', replace(new.pr_packaging, '"', '\\"'), '"') end),
      concat(',"pr_cmpimg": ', case when new.pr_cmpimg is null then 'null' else concat('"', replace(new.pr_cmpimg, '"', '\\"'), '"') end),
      concat(',"pr_goodsnover": ', case when new.pr_goodsnover is null then 'null' else concat('"', replace(new.pr_goodsnover, '"', '\\"'), '"') end),
      concat(',"pr_goodstaxno": ', case when new.pr_goodstaxno is null then 'null' else concat('"', replace(new.pr_goodstaxno, '"', '\\"'), '"') end),
      concat(',"pr_taxpre": ', case when new.pr_taxpre is null then 'null' else concat('"', replace(new.pr_taxpre, '"', '\\"'), '"') end),
      concat(',"pr_taxprecon": ', case when new.pr_taxprecon is null then 'null' else concat('"', replace(new.pr_taxprecon, '"', '\\"'), '"') end),
      -- bit(1), smallint(6), int(11), bigint(20), double
      concat(',"pr_enuu": ', case when new.pr_enuu is null then 'null' else new.pr_enuu end),
      concat(',"pr_useruu": ', case when new.pr_useruu is null then 'null' else new.pr_useruu end),
      concat(',"pr_minpack": ', case when new.pr_minpack is null then 'null' else new.pr_minpack end),
      concat(',"pr_minorder": ', case when new.pr_minorder is null then 'null' else new.pr_minorder end),
      concat(',"pr_leadtime": ', case when new.pr_leadtime is null then 'null' else new.pr_leadtime end),
      concat(',"pr_ltinstock": ', case when new.pr_ltinstock is null then 'null' else new.pr_ltinstock end),
      concat(',"pr_reserve": ', case when new.pr_reserve is null then 'null' else new.pr_reserve end),
      concat(',"pr_price": ', case when new.pr_price is null then 'null' else new.pr_price end),
      concat(',"pr_sourceid": ', case when new.pr_sourceid is null then 'null' else new.pr_sourceid end),
      concat(',"pr_kindid": ', case when new.pr_kindid is null then 'null' else new.pr_kindid end),
      concat(',"pr_pbrandid": ', case when new.pr_pbrandid is null then 'null' else new.pr_pbrandid end),
      concat(',"pr_issale": ', case when new.pr_issale is null then 'null' else new.pr_issale end),
      concat(',"pr_ispurchase": ', case when new.pr_ispurchase is null then 'null' else new.pr_ispurchase end),
      concat(',"pr_isshow": ', case when new.pr_isshow is null then 'null' else new.pr_isshow end),
      concat(',"pr_ispubsale": ', case when new.pr_ispubsale is null then 'null' else new.pr_ispubsale end),
      concat(',"pr_standard": ', case when new.pr_standard is null then 'null' else new.pr_standard end),
      concat(',"pr_matchstatus": ', case when new.pr_matchstatus is null then 'null' else new.pr_matchstatus end),
      concat(',"pr_matchsize": ', case when new.pr_matchsize is null then 'null' else new.pr_matchsize end),
      concat(',"pr_downloadstatus": ', case when new.pr_downloadstatus is null then 'null' else new.pr_downloadstatus end),
      concat(',"pr_maxdelivery": ', case when new.pr_maxdelivery is null then 'null' else new.pr_maxdelivery end),
      concat(',"pr_mindelivery": ', case when new.pr_mindelivery is null then 'null' else new.pr_mindelivery end),
      concat(',"pr_isbreakup": ', case when new.pr_isbreakup is null then 'null' else new.pr_isbreakup end),
      -- datetime
      concat(',"pr_erpdate": ', case when new.pr_erpdate is null then 'null' else concat('"', replace(new.pr_erpdate, '"', '\\"'), '"') end),
      concat(',"pr_create_time": ', case when new.pr_create_time is null then 'null' else concat('"', replace(new.pr_create_time, '"', '\\"'), '"') end),
      concat(',"pr_tostandard": ', case when new.pr_tostandard is null then 'null' else concat('"', replace(new.pr_tostandard, '"', '\\"'), '"') end),
      concat(',"pr_manufacturedate": ', case when new.pr_manufacturedate is null then 'null' else concat('"', replace(new.pr_manufacturedate, '"', '\\"'), '"') end),
      -- text
      -- json
      concat('}')
    ) into v_data;
    call sync$enqueue_message(v_table_name, v_method_type, v_data_key, v_data, v_priority);
end
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table products
-- ----------------------------
DROP TRIGGER IF EXISTS `sync$products_u`;
delimiter ;;
CREATE DEFINER = `root`@`%` TRIGGER `sync$products_u` AFTER UPDATE ON `products` FOR EACH ROW begin
	declare v_table_name varchar(64) default 'products';
    declare v_method_type varchar(6) default 'update';
    declare v_data_key text;
    declare v_data text;
    declare v_priority int default 1;

    set v_data_key=concat('{"pr_id": ', old.pr_id, '}');
    select concat(
      concat('{'),
      -- varchar
      concat('"pr_title": ', case when new.pr_title is null then 'null' else concat('"', replace(new.pr_title, '"', '\\"'), '"') end),
      concat(',"pr_code": ', case when new.pr_code is null then 'null' else concat('"', replace(new.pr_code, '"', '\\"'), '"') end),
      concat(',"pr_spec": ', case when new.pr_spec is null then 'null' else concat('"', replace(new.pr_spec, '"', '\\"'), '"') end),
      concat(',"pr_unit": ', case when new.pr_unit is null then 'null' else concat('"', replace(new.pr_unit, '"', '\\"'), '"') end),
      concat(',"pr_brand": ', case when new.pr_brand is null then 'null' else concat('"', replace(new.pr_brand, '"', '\\"'), '"') end),
      concat(',"pr_cmpcode": ', case when new.pr_cmpcode is null then 'null' else concat('"', replace(new.pr_cmpcode, '"', '\\"'), '"') end),
      concat(',"pr_cmpuuid": ', case when new.pr_cmpuuid is null then 'null' else concat('"', replace(new.pr_cmpuuid, '"', '\\"'), '"') end),
      concat(',"pr_sourceapp": ', case when new.pr_sourceapp is null then 'null' else concat('"', replace(new.pr_sourceapp, '"', '\\"'), '"') end),
      concat(',"pr_kind": ', case when new.pr_kind is null then 'null' else concat('"', replace(new.pr_kind, '"', '\\"'), '"') end),
      concat(',"pr_kinden": ', case when new.pr_kinden is null then 'null' else concat('"', replace(new.pr_kinden, '"', '\\"'), '"') end),
      concat(',"pr_pbrand": ', case when new.pr_pbrand is null then 'null' else concat('"', replace(new.pr_pbrand, '"', '\\"'), '"') end),
      concat(',"pr_pbranden": ', case when new.pr_pbranden is null then 'null' else concat('"', replace(new.pr_pbranden, '"', '\\"'), '"') end),
      concat(',"pr_pbranduuid": ', case when new.pr_pbranduuid is null then 'null' else concat('"', replace(new.pr_pbranduuid, '"', '\\"'), '"') end),
      concat(',"pr_pcmpcode": ', case when new.pr_pcmpcode is null then 'null' else concat('"', replace(new.pr_pcmpcode, '"', '\\"'), '"') end),
      concat(',"pr_attachment": ', case when new.pr_attachment is null then 'null' else concat('"', replace(new.pr_attachment, '"', '\\"'), '"') end),
      concat(',"pr_encapsulation": ', case when new.pr_encapsulation is null then 'null' else concat('"', replace(new.pr_encapsulation, '"', '\\"'), '"') end),
      concat(',"pr_packaging": ', case when new.pr_packaging is null then 'null' else concat('"', replace(new.pr_packaging, '"', '\\"'), '"') end),
      concat(',"pr_cmpimg": ', case when new.pr_cmpimg is null then 'null' else concat('"', replace(new.pr_cmpimg, '"', '\\"'), '"') end),
      concat(',"pr_goodsnover": ', case when new.pr_goodsnover is null then 'null' else concat('"', replace(new.pr_goodsnover, '"', '\\"'), '"') end),
      concat(',"pr_goodstaxno": ', case when new.pr_goodstaxno is null then 'null' else concat('"', replace(new.pr_goodstaxno, '"', '\\"'), '"') end),
      concat(',"pr_taxpre": ', case when new.pr_taxpre is null then 'null' else concat('"', replace(new.pr_taxpre, '"', '\\"'), '"') end),
      concat(',"pr_taxprecon": ', case when new.pr_taxprecon is null then 'null' else concat('"', replace(new.pr_taxprecon, '"', '\\"'), '"') end),
      -- bit(1), smallint(6), int(11), bigint(20), double
      concat(',"pr_enuu": ', case when new.pr_enuu is null then 'null' else new.pr_enuu end),
      concat(',"pr_useruu": ', case when new.pr_useruu is null then 'null' else new.pr_useruu end),
      concat(',"pr_minpack": ', case when new.pr_minpack is null then 'null' else new.pr_minpack end),
      concat(',"pr_minorder": ', case when new.pr_minorder is null then 'null' else new.pr_minorder end),
      concat(',"pr_leadtime": ', case when new.pr_leadtime is null then 'null' else new.pr_leadtime end),
      concat(',"pr_ltinstock": ', case when new.pr_ltinstock is null then 'null' else new.pr_ltinstock end),
      concat(',"pr_reserve": ', case when new.pr_reserve is null then 'null' else new.pr_reserve end),
      concat(',"pr_price": ', case when new.pr_price is null then 'null' else new.pr_price end),
      concat(',"pr_sourceid": ', case when new.pr_sourceid is null then 'null' else new.pr_sourceid end),
      concat(',"pr_kindid": ', case when new.pr_kindid is null then 'null' else new.pr_kindid end),
      concat(',"pr_pbrandid": ', case when new.pr_pbrandid is null then 'null' else new.pr_pbrandid end),
      concat(',"pr_issale": ', case when new.pr_issale is null then 'null' else new.pr_issale end),
      concat(',"pr_ispurchase": ', case when new.pr_ispurchase is null then 'null' else new.pr_ispurchase end),
      concat(',"pr_isshow": ', case when new.pr_isshow is null then 'null' else new.pr_isshow end),
      concat(',"pr_ispubsale": ', case when new.pr_ispubsale is null then 'null' else new.pr_ispubsale end),
      concat(',"pr_standard": ', case when new.pr_standard is null then 'null' else new.pr_standard end),
      concat(',"pr_matchstatus": ', case when new.pr_matchstatus is null then 'null' else new.pr_matchstatus end),
      concat(',"pr_matchsize": ', case when new.pr_matchsize is null then 'null' else new.pr_matchsize end),
      concat(',"pr_downloadstatus": ', case when new.pr_downloadstatus is null then 'null' else new.pr_downloadstatus end),
      concat(',"pr_maxdelivery": ', case when new.pr_maxdelivery is null then 'null' else new.pr_maxdelivery end),
      concat(',"pr_mindelivery": ', case when new.pr_mindelivery is null then 'null' else new.pr_mindelivery end),
      concat(',"pr_isbreakup": ', case when new.pr_isbreakup is null then 'null' else new.pr_isbreakup end),
      -- datetime
      concat(',"pr_erpdate": ', case when new.pr_erpdate is null then 'null' else concat('"', replace(new.pr_erpdate, '"', '\\"'), '"') end),
      concat(',"pr_create_time": ', case when new.pr_create_time is null then 'null' else concat('"', replace(new.pr_create_time, '"', '\\"'), '"') end),
      concat(',"pr_tostandard": ', case when new.pr_tostandard is null then 'null' else concat('"', replace(new.pr_tostandard, '"', '\\"'), '"') end),
      concat(',"pr_manufacturedate": ', case when new.pr_manufacturedate is null then 'null' else concat('"', replace(new.pr_manufacturedate, '"', '\\"'), '"') end),
      -- text
      -- json
      concat('}')
    ) into v_data;
    call sync$enqueue_message(v_table_name, v_method_type, v_data_key, v_data, v_priority);
end
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table products
-- ----------------------------
DROP TRIGGER IF EXISTS `sync$products_d`;
delimiter ;;
CREATE DEFINER = `root`@`%` TRIGGER `sync$products_d` AFTER DELETE ON `products` FOR EACH ROW begin
	declare v_table_name varchar(64) default 'products';
    declare v_method_type varchar(6) default 'delete';
    declare v_data_key text;
    declare v_data text;
    declare v_priority int default 1;

    set v_data_key=concat('{"pr_id": ', old.pr_id, '}');
    call sync$enqueue_message(v_table_name, v_method_type, v_data_key, v_data, v_priority);
end
;;
delimiter ;