View Issue Details

IDProjectCategoryView StatusLast Update
0001570SymmetricDSImprovementpublic2019-04-26 09:29
Reporterboris_pavlovicAssigned Tochenson 
Prioritynormal 
Status closedResolutionfixed 
Product Version3.5.13 
Target VersionFixed in Version3.8.0 
Summary0001570: Add custom_before_insert/update/delete_text to trigger for help with routing dependent rows
DescriptionPlease look at the description given at: https://sourceforge.net/p/symmetricds/discussion/739236/thread/4d666f07/

In the answer given by Eric Long (https://sourceforge.net/u/erilong/profile/) it says that CUSTOM_ON_UPDATE_TEXT which touches the table referenced by the FK would help with INSERT and UPDATE. Indeed, the affected row referenced by the FK is routed to the target node. The problem is the order. First the row containing the FK is extracted in SYM_DATA and then the row referenced by the FK.

Loading on target is failing because of the FK violation.
Additional InformationThe problem could be fixed by parameterized switching of the order of extraction and CUSTOM_ON_X_TEXT. I'm currently working on the solution.
Tagsdata sync

Relationships

related to 0000777 new Dependency on child table for syncing additional data 

Activities

boris_pavlovic

2014-02-10 08:20

reporter  

transitive_dependencies_improvement.patch (531,723 bytes)

boris_pavlovic

2014-02-10 08:22

reporter   ~0000472

Here's a preliminary idea how the problem could be solved. I think that it would be better to add three columns to the TRIGGER table specifying the ordering of execution of extraction and custom_on_X_text. Usually we want the custom text to be executed before the extraction for INSERT and UPDATE and after for DELETE.

boris_pavlovic

2014-02-12 05:59

reporter  

bug_fixed_http___www_symmetricds_org_issues_view_php_id=1570.patch (523,339 bytes)

boris_pavlovic

2014-02-12 06:00

reporter   ~0000476

Here's a definitive patch which introduces three columns that define the ordering of the extraction and customization of triggers. By default extraction takes precedence.

boris_pavlovic

2014-02-17 06:03

reporter   ~0000480

I've just realized that this is not going to work. For tables that have a foreign key on other tables that have to be touched the customization has to be run before the extract, but for the tables that are referenced by other ones through a foreign key the customization should be run after the extract. The problem is when the same table has both a foreign key and its id is referenced by a foreign key from another table.

For example

MY_SYSTEM(id_system, xid_site (foreign key on MY_SITE(id_site)))
MY_SITE(id_site, xid_company(foreign key on MY_COMPANY(id_company)))
MY_COMPANY(id_company)
MY_ADDRESS(id_address, xid_site(foreign key on MY_SITE(id_site)))

we need to customize on insert, update, delete

before extract for MY_COMPANY, so we first insert its row referenced by MY_SITE
after extract for MY_ADDRESS, so we first insert MY_SITE

I'm working on the new version of the patch.

boris_pavlovic

2014-02-21 07:59

reporter  

bug_fixed_http___www_symmetricds_org_issues_view_php_id=1570__custom_on_insert_update_dele.patch (488,160 bytes)

boris_pavlovic

2014-02-21 08:00

reporter   ~0000491

Here's a patch introducing custom_on_(insert/update/delete)_text_before extraction leaving the default one to be executed after the extraction

Issue History

Date Modified Username Field Change
2014-02-10 02:55 boris_pavlovic New Issue
2014-02-10 08:20 boris_pavlovic File Added: transitive_dependencies_improvement.patch
2014-02-10 08:22 boris_pavlovic Note Added: 0000472
2014-02-12 05:59 boris_pavlovic File Added: bug_fixed_http___www_symmetricds_org_issues_view_php_id=1570.patch
2014-02-12 06:00 boris_pavlovic Note Added: 0000476
2014-02-12 08:30 chenson Target Version => 3.6.0
2014-02-13 10:33 elong Relationship added related to 0000777
2014-02-17 06:03 boris_pavlovic Note Added: 0000480
2014-02-21 07:59 boris_pavlovic File Added: bug_fixed_http___www_symmetricds_org_issues_view_php_id=1570__custom_on_insert_update_dele.patch
2014-02-21 08:00 boris_pavlovic Note Added: 0000491
2014-03-03 10:29 chenson Assigned To => chenson
2014-03-03 10:29 chenson Status new => assigned
2014-04-18 13:04 chenson Category Bug => Improvement
2014-04-18 13:04 chenson Target Version 3.6.0 => 3.7.0
2014-12-30 14:25 chenson Target Version 3.7.0 =>
2019-04-24 12:51 elong Tag Attached: data sync
2019-04-26 09:29 elong Priority urgent => normal
2019-04-26 09:29 elong Status assigned => closed
2019-04-26 09:29 elong Resolution open => fixed
2019-04-26 09:29 elong Fixed in Version => 3.8.0
2019-04-26 09:29 elong Summary Routing based on FK join table not working => Add custom_before_insert/update/delete_text to trigger for help with routing dependent rows