SymmetricDS: Which approach should I use to synchr

Question

I'm testing SymmetricDS and I'm having some doubt about which approach I should use to synchronize some specific tables. I have two application, the first is a ERP and the second is a PDV. Some tables can be synchronized to all PDV databases, however, in some tables the row should synchronize to a specific PDV instance:

In this diagram, the red rectangles show which table need synchronize, and the green arrow show the column where we can identify which SymmetricDS instance will synchronize. My question is: which approach I should use to do this (bsh, subselect, lookuptable, etc) and how I do it?

Answer 1

You need a subselect router (actually you need three). I only show you the SQL for Funcionario, then you'll be able to figure out the configuration for Cadastro and Funcionario_funcao by yourself:

insert into SYM_ROUTER 
  (router_id, source_node_group_id, target_node_group_id, router_type, 
    router_expression, create_time, last_update_time)
values
  ('erp_to_pdv__funcionario', 'erp', 'pdv', 'subselect', 
    'c.external_id in (
        select empresa.CNPJ
          from cadastro join empresa
            on cadastro.id_empresa = empresa.id_empresa
          where cadastro.CPFCGC=:ID_CADASTRO
     )', 
    current_timestamp, current_timestamp);

When a record from the Funcionario table is routed, this router picks all the nodes with external_id equal to the Empresa.CNPJ linked to the given Funcionario (I assumed Funcionario.id_cadastro to be the reference to Cadastro.CPFCGC)

Note I used c.external_id, but you may want to use c.node_id (depends on what you put into Empresa.CNPJ).