View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0001570 | SymmetricDS | Improvement | public | 2014-02-10 07:55 | 2019-04-26 13:29 |
| Reporter | boris_pavlovic | Assigned To | chenson | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.5.13 | ||||
| Target Version | 3.8.0 | Fixed in Version | 3.8.0 | ||
| Summary | 0001570: Add custom_before_insert/update/delete_text to trigger for help with routing dependent rows | ||||
| Description | Please 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 Information | The problem could be fixed by parameterized switching of the order of extraction and CUSTOM_ON_X_TEXT. I'm currently working on the solution. | ||||
| Tags | data sync | ||||
| related to | 0000777 | new | Dependency on child table for syncing additional data |
|
|
|
|
|
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. |
|
|
|
|
|
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. |
|
|
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. |
|
|
|
|
|
Here's a patch introducing custom_on_(insert/update/delete)_text_before extraction leaving the default one to be executed after the extraction |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2014-02-10 07:55 | boris_pavlovic | New Issue | |
| 2014-02-10 13:20 | boris_pavlovic | File Added: transitive_dependencies_improvement.patch | |
| 2014-02-10 13:22 | boris_pavlovic | Note Added: 0000472 | |
| 2014-02-12 10:59 | boris_pavlovic | File Added: bug_fixed_http___www_symmetricds_org_issues_view_php_id=1570.patch | |
| 2014-02-12 11:00 | boris_pavlovic | Note Added: 0000476 | |
| 2014-02-12 13:30 | chenson | Target Version | => 3.6.0 |
| 2014-02-13 15:33 | elong | Relationship added | related to 0000777 |
| 2014-02-17 11:03 | boris_pavlovic | Note Added: 0000480 | |
| 2014-02-21 12: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 13:00 | boris_pavlovic | Note Added: 0000491 | |
| 2014-03-03 15:29 | chenson | Assigned To | => chenson |
| 2014-03-03 15:29 | chenson | Status | new => assigned |
| 2014-04-18 17:04 | chenson | Category | Bug => Improvement |
| 2014-04-18 17:04 | chenson | Target Version | 3.6.0 => 3.7.0 |
| 2014-12-30 19:25 | chenson | Target Version | 3.7.0 => |
| 2019-04-24 16:51 | elong | Tag Attached: data sync | |
| 2019-04-26 13:29 | elong | Priority | urgent => normal |
| 2019-04-26 13:29 | elong | Status | assigned => closed |
| 2019-04-26 13:29 | elong | Resolution | open => fixed |
| 2019-04-26 13:29 | elong | Fixed in Version | => 3.8.0 |
| 2019-04-26 13: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 |