1

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?

brevleq
  • 2,081
  • 10
  • 53
  • 97

1 Answers1

2

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

Raffaele
  • 20,627
  • 6
  • 47
  • 86
  • 1
    I would optimize `select empresa.CNPJ` with `select distinct empresa.CNPJ` in case there's n-n relationship in general. – Boris Pavlović Sep 02 '14 at 11:44
  • @Raffaele Isn't subselect route an inefficient approach? – brevleq Sep 02 '14 at 18:33
  • No. Later, if you find that your production setup suffers from performance problems and a hardware upgrade can't be afforded, you may try to speed things up by using routing tables / routing columns (it means you have to somehow digest the routing yourself and add columns to store the node/lookup key) – Raffaele Sep 02 '14 at 21:59