CODEWITHSUNDEEP
javadatabasesynchronizationdata-synchronizationsymmetricds
brevleq
brevleq

Reputation: 2141

SymmetricDS: Which approach should I use to synchronize specific tables?

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:

entity-diagram

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?

Upvotes: 1

Views: 730

Answers (1)

Raffaele
Raffaele

Reputation: 20885

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).

Upvotes: 2

Related Questions