SQLRemote: Implicit synch of connected objects

Question

I'm looking for the best way to synchronise foreign key relations with SQLRemote. So having e.g. this two tables

CREATE TABLE  table1(
    id integer not null default autoincrement,
    name varchar(40) NOT NULL,
    primary key (id)
);


CREATE TABLE table_items(
    id integer NOT NULL default autoincrement,
    name CHAR(40) NOT NULL,
    table_1_id integer NOT NULL,
    cl1 bit default 0,
    cl2 bit default 0,
    FOREIGN KEY ( table_1_id )
    REFERENCES table_1( id ),
 PRIMARY KEY (id));

Implementing maybe an invoice with invoice items. cl1 and cl2 shall trigger which client shall this dataset by synched with. But now I'm wondering, what's is the best way to synch also corresponding data of table_1.

A first approach would be from my mind to put also cl1 and cl2 fields into table_1 and add them to pubcliation so publication looks little like:

CREATE PUBLICATION sync_cl1 (
    TABLE table_1,
    TABLE table_items
    WHERE cl1 = 1
);

CREATE PUBLICATION sync_cl2 (
    TABLE table_1,
    TABLE table_items
    WHERE cl1 = 2
);

Is there any better way doing this maybe with integrated integrity check?

Answer 1

It appears that this can be done with subqueries inside publications.

From documentation:

CREATE PUBLICATION SalesRepData (
TABLE SalesReps
TABLE Customers
   SUBSCRIBE BY rep_key
TABLE Contacts
SUBSCRIBE BY (SELECT rep_key
   FROM Customers
   WHERE Contacts.cust_key = Customers.cust_key )
);

This would catch all customer contacts for sales rep's customer.

Quiet important seems to be, that this needs to be updated once the connection has been updated -- e.g. a sales rep is getting new customers. This needs to be done with an BEFORE trigger for UPDATE/INSERT/DELETE.