In an aviarc workflow, how do I delete rows from from a dataset after they have been removed from the database?

Question

Here's the workflow flow I'm using.

<atomic-commit>
   <dataset name="foo"/>
</atomic-commit>

<dataset-iterator dataset="foo">
    <create-row dataset="hist-foo"/>
    <mark-row-created dataset="hist-foo"/>
</dataset-iterator>

So basically, after dataset foo is updated, I want to record the remaining foo entries in another history table. But when I delete rows from the foo table, the rows still remain in the dataset and therefore get added to hist-foo. I've tried to add a post-workflow to the foo databroker's delete action like this:

<workflow>
     <delete-row dataset="{$context.commit-dataset-name}"/>
</workflow>

However I get an error when the delete action is called.

Also, after the first atomic commit, the foo dataset doesn't keep deleted row actions, so I can't identify which rows from deleted from the dataset.

What actions are you performing to delete the rows before the atomic-commit? — savs, Sep 14 '12 at 04:47
I'm using as an action on the screen. Then later the dataset is saved when the screen is submitted. The query that is run when rows are deleted does not actually delete the row from the database, it actually preforms an update query and sets an is-deleted flag. — Jim, Sep 14 '12 at 04:59
Perhaps I'm missing something, but why do you want to record the rows that *weren't* changed in the history table? Have they been created/updated also? — Adam Sharp, Sep 16 '12 at 23:17
It's just recording that the rows still exist, so later an audit screen can retrieve foo rows based on hist-foo. — Jim, Sep 18 '12 at 00:41

score 3 · Accepted Answer · edited Sep 16 '12 at 23:12

The simplest solution for this situation would be to sift the marked-deleted rows into a separate dataset. Unfortunately this is a little long when using only built-in commands.

<dataset name="deleted-foo" databroker="..."/>

<dataset-iterator dataset="foo">
    <if test="row-marked-deleted" value1="foo">
        <then>
            <create-row dataset="deleted-foo"/>
            <copy-row from-dataset="foo" to-dataset="deleted-foo"/>
            <mark-row-deleted dataset="deleted-foo"/>
        </then>
    </if>
</dataset-iterator>

<!-- Keeping in mind that you can't delete rows from a dataset
     which is being iterated over. -->
<dataset-iterator dataset="deleted-foo">
    <dataset-reset dataset="foo" no-current-row="y"/>
    <!-- Assuming rows have a field 'id' which uniquely IDs them -->
    <set-current-row-by-field dataset="foo" field="id" value="{$deleted-foo.id}"/>

    <if test="dataset-has-current-row" value1="foo">
        <then>
            <delete-row dataset="foo"/>
        </then>
    </if>
</dataset-iterator>

<atomic-commit>
    <dataset name="deleted-foo"/>
    <dataset name="foo"/>
</atomic-commit>

<dataset-iterator dataset="foo"> 
    <create-row dataset="hist-foo"/> 
    <mark-row-created dataset="hist-foo"/> 
</dataset-iterator>

An alternate solution would be to do the history recording at the same time as the inserts/updates were run, for example by running multiple statements within the operations or by having insert/update triggers set up if those are available.

Thanks, I re-organized the workflow to take advantage of triggers. — Jim, Sep 18 '12 at 00:38

Vlad · Answer 2 · 2012-09-17T03:12:50.187

1

I think that in the answer from Tristan you don't necessarily need to commit "deleted-foo" dataset as you don't mark its rows with any commit flag.

A bit further - I would personally move those operations into pre- and post- commit workflows of the databroker. You'd capture all rows marked as deleted in pre-commit workflow and then delete rows from the foo dataset and populate history dataset in the post-commit workflow.

edited Sep 17 '12 at 03:12

answered Sep 14 '12 at 04:57

Vlad

385
3
10

+1 for pre-/post-workflows. Also, deleted-foo is marked with a commit action, see the first iterator: ``. – Adam Sharp Sep 16 '12 at 23:10
Hmm, good point. I did overlook it. Then in this case I don't see the point of committing both "foo" and "deleted-foo" datasets. Wouldn't that call attempt to undertake the exact same operations? – Vlad Sep 17 '12 at 03:14
No, because that's what the second dataset-iterator pass is being used for: after copying out the rows marked for deletion, now remove them from `foo` (which couldn't be done in the first pass due to not being able to delete rows in the dataset you're iterating over). Result is that committing `deleted-foo` performs delete actions, and then committing `foo` now performs only create/update actions. – Adam Sharp Sep 17 '12 at 03:37

In an aviarc workflow, how do I delete rows from from a dataset after they have been removed from the database?

2 Answers2