Where clause from another table (SQL Remote)

Baron · ‎2022 Nov 03

Is it possible to define articles in a publication and let the where clause refer to columns from a foreign table?

For example I have these 2 tables:

create or replace table employees (
empid int, 
withprovision int, 
empname varchar(10), 
departid int);

create or replace table orders (
orderid int, 
empid int);

I want to let sql remote replicate only the employees who make provision along with their orders, also I want to refer the subscription by from a foreign table.

My scripts looks like this:

create publication dba.mypub (table employees where withprovision = 1 subscribe by departid, table orders where exists (select 1 from employees where withprovision = 1 and orders.empid = employees.empid subscribe by ????);

Table employees works exactly as I want (where clause and subscribe by do what I need).

Table orders: the where clause acts as where 1 = 2 and I don't know how to write the subscribe by...

Is this generally possible with SQL Remote?

regdomaratzki · ‎2022 Nov 03

A subscribe by subquery should do the trick here.

CREATE TABLE cons.employees (
        empid       bigint NOT NULL DEFAULT GLOBAL AUTOINCREMENT(1000000),
        withprov    integer NOT NULL,
        empname     char(64) NOT NULL,
        deptid      integer NOT NULL,
        PRIMARY KEY (empid)
);

CREATE TABLE cons.orders (
        orderid     bigint NOT NULL DEFAULT GLOBAL AUTOINCREMENT(1000000),
        empid       bigint NOT NULL references cons.employees,
        textcol     char(64) NULL,
        PRIMARY KEY (orderid)
);

CREATE PUBLICATION cons.p1 (
        TABLE cons.employees where withprov=1 subscribe by deptid,
        TABLE cons.orders subscribe by ( select deptid 
`                                          from cons.employees 
                                          where cons.employees.empid = cons.orders.empid 
                                            and cons.employees.withprov = 1 ) 
);

With a subscribe by subquery, you now need to think about what happens when the parent table (employees in this case) is updated in such a way that will change what rows remote database will have in the child table (orders in this case). dbremote will take care of migrating rows on the employee table, because there is an operation in the transaction on that row, but there is no entry in the transaction log for the orders table. You need to write triggers with update publication statements that will insert or delete rows at remote sites on the orders table when you make changes to the withprov or deptid columns.

CREATE TRIGGER bu_deptid_employees BEFORE UPDATE OF deptid ORDER 1 ON cons.employees 
REFERENCING OLD AS old_row NEW AS new_row FOR EACH ROW
BEGIN
  UPDATE cons.orders PUBLICATION cons.p1
     OLD SUBSCRIBE BY old_row.deptid
     NEW SUBSCRIBE BY new_row.deptid
   WHERE cons.orders.empid = new_row.empid
     AND new_row.deptid = 1;
END;

CREATE TRIGGER bu_withprov_employee BEFORE UPDATE OF withprov ORDER 2 ON cons.employees 
REFERENCING OLD AS old_row NEW AS new_row FOR EACH ROW
BEGIN
  UPDATE cons.orders PUBLICATION cons.p1
     OLD SUBSCRIBE BY NULL
     NEW SUBSCRIBE BY new_row.deptid
   WHERE cons.orders.empid = new_row.empid
     AND new_row.withprov = 1;
  UPDATE cons.orders PUBLICATION cons.p1
     OLD SUBSCRIBE BY new_row.deptid
     NEW SUBSCRIBE BY NULL 
   WHERE cons.orders.empid = new_row.empid
     AND new_row.withprov = 0;     
END;

Disclaimer: These two triggers don't work well together when a single update changes both the withprov column and the deptid column. This should be a single trigger that ensures you don't insert or delete the same rows twice. I'll leave it as an exercise to the user to make a single trigger that does this properly.

Reg

VolkerBarth · ‎2022 Nov 03

One approach is to use a join within your ~~WHERE~~SUBSCRIBE BY subquery, i.e. for say, table order_items, you would use something like

subscribe by
   (select e.departid
    from order_items oi
       inner join orders o on oi.order_id = o.id
       inner join employees e on o.empid = e.empid
    where e.withprovision = 1)

In other words: You can use the same subscribe by condition for all dependent tables but have to join them to the table the subscribe by relates to.

FWIW, this is also (and naturally in a better fashion) documented in the docs:

Disjoint Data Partitions.

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My Account

My Account

Where clause from another table (SQL Remote)

Know the answer?

Need more details?

Accepted Solutions (0)

Answers (2)

Answers (2)