View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0002694SymmetricDSBugpublic2016-07-27 09:112019-04-26 16:51
Reportermf Assigned Toelong  
Prioritynormal 
Status closedResolutionduplicate 
Product Version3.7.36 
Target Version3.9.9Fixed in Version3.9.9 
Summary0002694: (lookup)Transform and LoadFilter on DELETE
DescriptionI have a scenario, with a table, that is "cloned" between two databases and after every operation (INSERT, UPDATE, DELETE) a load filter jumps in and tries to (post)process the changed data.

That data transfer is handled by a TRANSFORM_TABLE with transform_point "LOAD" and column_polcy "IMPLIED" *but* with 3 entries for TRANSFORM_COLUMN, too. All three TRANSFORM_COLUMN entries serve for the same task, oding a "lookup" transform_type for a virtual column - but with different transform_expressions for INSERT, UPDATE and DELETE (as the columns to use for the lookup differ with OLD_ prefix or without).

And because of the same reason, the Loadfilter (with load_filter_type "SQL") is tripled, too. One for each case of "filter_on_update", "filter_on_insert" and "filter_on_delete" - as the Query addresses a stored procedure in the target DB with parameters to be filled from OLD_ or new values.

Everything works as expected besides the DELETE operation. In the call to the LoadFilter, the virtual column isn't properly filled into the parameter map for the write script. It is successfully transformed, but contained in org.jumpmind.symmetric.io.data.transform.TransformedData#targetNewValueByIncludeOnType - a map which isn't transferred to the parameter Map for the loadFilter (as there's no NEW in a DELETE). So, my org.jumpmind.symmetric.load.SQLDatabaseWriterFilter#getVariablesMap contains the KEY of the virtual column, but with NULL value, what makes my load filter struggle....
Steps To ReproduceImagine a table named "RELATIONS" with the following columns:

ID, CHILD_IDENT, PARENT_IDENT, INSERT_DATE

Theres a TRANSFORM_TABLE

INSERT INTO SYM_TRANSFORM_TABLE (
    transform_id, source_node_group_id, target_node_group_id, transform_point,
    SOURCE_SCHEMA_NAME, source_table_name,
    target_schema_name, target_table_name,
    update_action, delete_action,
    transform_order, column_policy, update_first,
    last_update_time, last_update_by, create_time
) VALUES (
    'STRUKTUR_clone', 'interbase', 'postgres', 'LOAD',
    NULL, 'RELATIONS',
    'interbase', 'RELATIONS',
    'UPD_ROW', 'DEL_ROW',
    1, 'IMPLIED', 0,
    CURRENT_TIMESTAMP, 'mf', CURRENT_TIMESTAMP);

and a TRANSFORM_COLUMN for every operation, here's the one for DELETE (include_on = 'D'):

INSERT INTO sym_transform_column (
    transform_id, include_on, target_column_name, source_column_name, pk, transform_type, transform_expression, transform_order, create_time, last_update_by, last_update_time)
VALUES
    ('STRUKTUR_clone',
        'D',
        'virt_structure_id',
        null,
        0,
        'lookup',
        'SELECT sps.id FROM webapps_base.salespartnerstructure sps JOIN webapps_base.salespartner sp ON sps.fk_salespartner_parent = sp.id JOIN webapps_base.salespartner sc ON sps.fk_salespartner_child = sc.id WHERE sc.ident = :OLD_CHILD_IDENT AND sp.ident = :OLD_PARENT_IDENT AND validfrom = CAST(:OLD_INSERT_DATE AS date);',
        1, CURRENT_TIMESTAMP, 'mf', CURRENT_TIMESTAMP);


The LOAD_FILTER line looks like this one:

INSERT INTO sym_load_filter (
    load_filter_id, load_filter_type, source_node_group_id,
    target_node_group_id, target_schema_name, target_table_name,
    filter_on_update, filter_on_insert, filter_on_delete,
    after_write_script,
    create_time, last_update_by, last_update_time,
    load_filter_order, fail_on_error)
VALUES
    ('STRUKTUR_clone#DELETE',
        'SQL', 'interbase', 'postgres', 'interbase', 'RELATIONS',
        0, 0, 1,
        'SELECT * FROM webapps_base.salespartner_structure_struktur_change_postprocess(:SYM_DATA_EVENT_TYPE, CAST(:OLD_ID AS integer), CAST(:OLD_VIRT_STRUCTURE_ID AS BIGINT), CAST(:OLD_INSERT_DATE AS date));',
        CURRENT_TIMESTAMP, 'mf', CURRENT_TIMESTAMP,
     1, 1);

And the OLD_VIRT_STRUCTURE_ID is always NULL, as VIRT_STRUCTURE_ID is.
Additional InformationIs there an error in my expectations or my use case?

Thanks in advance
Markus
Tagstransformation

Relationships

duplicate of 0003605 closedelong Transforms should return an old value for delete and new value for insert and updates 
related to 0003624 closedchenson Some situations can cause a transformed value to be written to the old column value instead of the new column value. 

Activities

There are no notes attached to this issue.

Issue History

Date Modified Username Field Change
2016-07-27 09:11 mf New Issue
2019-04-24 13:04 elong Tag Attached: transformation
2019-04-26 16:50 elong Relationship added duplicate of 0003605
2019-04-26 16:50 elong Relationship added related to 0003624
2019-04-26 16:51 elong Assigned To => elong
2019-04-26 16:51 elong Status new => closed
2019-04-26 16:51 elong Resolution open => duplicate
2019-04-26 16:51 elong Fixed in Version => 3.9.9