同步 CustDB 示例中的订单

业务规则

ULOrder 表的业务规则如下：

只下载未批准或状态为空的订单。
在统一数据库和远程数据库中都可以修改订单。
每个远程数据库仅包含指派给雇员的订单。

下载

可以在统一数据库中插入、删除，或更新订单。对应于这些操作的脚本如下所示：

download_cursor download_cursor 脚本中的第一个参数是上次下载的时间戳。它用于确保仅下载那些自上次同步以来在远程数据库或统一数据库中进行过修改的行。第二个参数是雇员 ID。它用于确定下载哪些行。

CustDB 的 download_cursor 脚本如下所示：

CALL ULOrderDownload( {ml s.last_table_download}, {ml s.username} )

CustDB 的 ULOrderDownload 过程如下所示：

CREATE PROCEDURE ULOrderDownload ( IN LastDownload timestamp, IN EmployeeID integer )
BEGIN`
  SELECT o.order_id, o.cust_id, o.prod_id, o.emp_id, o.disc, o.quant, o.notes, o.status
    FROM ULOrder o, ULEmpCust ec
    WHERE o.cust_id = ec.cust_id
    AND ec.emp_id = EmployeeID
    AND ( o.last_modified >= LastDownload
    OR ec.last_modified >= LastDownload)
    AND ( o.status IS NULL  OR  o.status != 'Approved' )
    AND ( ec.action IS NULL )
END

download_delete_cursor CustDB 的 download_delete_cursor 脚本如下所示：

SELECT o.order_id, o.cust_id, o.prod_id, o.emp_id, o.disc, o.quant, o.notes, o.status FROM ULOrder o, dba.ULEmpCust ec WHERE o.cust_id = ec.cust_id AND ( ( o.status = ''Approved'' AND o.last_modified >= {ml s.last_table_download} ) OR ( ec.action = ''D'' ) ) AND ec.emp_id = {ml s.username}

上载

可以在远程数据库中插入、删除或更新订单。对应于这些操作的脚本如下所示：

upload_insert CustDB 的 upload_insert 脚本如下所示：

INSERT INTO ULOrder ( order_id, cust_id, prod_id, emp_id, disc, quant, notes, status ) VALUES( {ml r.order_id, r.cust_id, r.prod_id, r.emp_id, r.disc, r.quant, r.notes, r.status } )

upload_update CustDB 的 upload_update 脚本如下所示：

UPDATE ULOrder SET cust_id = {ml r.cust_id}, prod_id = {ml r.prod_id}, emp_id = {ml r.emp_id}, disc = {ml r.disc}, quant = {ml r.quant}, notes = {ml r.notes}, status = {ml r.status} WHERE order_id = {ml r.order_id}

upload_delete CustDB 的 upload_delete 脚本如下所示：
DELETE FROM ULOrder WHERE order_id = {ml r.order_id}

upload_fetch CustDB 的 upload_fetch 脚本如下所示：

SELECT order_id, cust_id, prod_id, emp_id, disc, quant, notes, status FROM ULOrder WHERE order_id = {ml r.order_id}

upload_old_row_insert CustDB 的 upload_old_row_insert 脚本如下所示：

INSERT INTO ULOldOrder ( order_id, cust_id, prod_id, emp_id, disc, quant, notes, status ) VALUES( {ml r.order_id, r.cust_id, r.prod_id, r.emp_id, r.disc, r.quant, r.notes, r.status } )

upload_new_row_insert CustDB 的 upload_new_row_insert 脚本如下所示：

INSERT INTO ULNewOrder ( order_id, cust_id, prod_id, emp_id, disc, quant, notes, status ) VALUES( {ml r.order_id, r.cust_id, r.prod_id, r.emp_id, r.disc, r.quant, r.notes, r.status } )

冲突解决

resolve_conflict CustDB 的 resolve_conflict 脚本如下所示：

CALL ULResolveOrderConflict

CustDB 的 ULResolveOrderConflict 过程如下所示：

CREATE PROCEDURE ULResolveOrderConflict()
BEGIN
  -- approval overrides denial
  IF 'Approved' = (SELECT status FROM ULNewOrder) THEN
    UPDATE ULOrder o
    SET o.status = n.status, o.notes = n.notes
    FROM ULNewOrder n
    WHERE o.order_id = n.order_id;
  END IF;
  DELETE FROM ULOldOrder;
  DELETE FROM ULNewOrder;
END