I’ve been working with database publishing to child tables, and it isn’t working the way I expect it to.
We have a content type that has two child tables, each of which can have a variable number of rows. I’ve set up database publishing as described in the documentation, with CONTENTID and SEQ as the key in destination child tables, and $db.action set to “r”. The problem I’m seeing is that when I delete entries from the child table in the content editor, the corresponding rows don’t get deleted from the content database child tables during publishing. Now that I think about it, how would it know to delete these child rows? Although it deletes a child row if it needs to update it, it won’t know that the child row should just be removed and not replaced. I guess it doesn’t go into the destination table and delete all rows for that parent, before inserting new ones for the new children (if needed).
I can’t believe this is a bug, so there must be something I’m missing. Is there something else I need to configure in DB publishing, or the content editor to make this work correctly?
I did have that attribute set to “n”, so I changed it to “y”. I’m still getting the same result - old rows are not removed from the child tables, even if the data has disappeared from the content item.
Here are the attributes I have for my tabledef nodes:
Unfortunately, I’m not sure that I have that much too add here.
Kathleen, the removal of child rows does work exactly as you describe: it removes all of the child rows and then adds the ones back in that are still present.
The “order” of the operations is important: you have to update the child rows after the parent (or the child rows will get deleted when the parent is updated).
We’ve also seen issues when there are NO child rows in the incoming data (from Rhythmyx). Sometimes it doesn’t delete the child rows in the database when this happens, but I’m a little fuzzy on the details.
I’ll ask around the office and see if anybody has any suggestions about this, but it would help if you can show us the XML that the Assembler is generating.
It’s the removal of all child rows that doesn’t seem to be happening. When I look at the publishing log, I see SQL update statements for the existing rows, but no deletion statements.
FYI - I opened a tech support ticket about this yesterday.
I attempted to attach the XML that’s generated by the assembler (which I also sent to tech support), but got an “invalid file” message (not sure how attachments work in this forum. I can’t figure out how to include XML in my post - I tried wrapping it in the CODE tags, and I can’t see the tags themselves (because, naturally, HTML is interpreting them). I will post it if someone gives me a pointer to how to do so.
We’ll figure out how to let you post XML documents, but you can always zip them and attach the .zip file. If you want to include XML inline you can escape it (e.g. <tag> ) or use the “HTML” button ( it looks like a pair of angle brackets).
In any event I’ll take a look at the template you sent to support and post some comments.
Looking at your template, I can see that you have 4 tables:
WEBINAR (I assume this is the parent)
WEBINAR_EVENTS
WEBINAR_REG
WEBINAR_SUBAPPLICATIONS
I don’t see any foreign key definitions: these are mandatory for the DB Publisher to work properly. You either have to define the foreign keys in the database and the run the TD Tool again, or fix up the XML from the TD Tool to include foreign key references.
Second, I believe that you have to have the Parent table defined first. Currently it’s the 3rd table of the 4.
My suggestion is that you try this with just 2 tables (the parent and 1 child) until you get it working. After that, you can add in the other tables.
Many thanks for pointing that out, Dave. I added the foreign keys, confirmed that the parent table appears first in the template XML, and now it’s updating the child rows correctly!
I now see this a note about this in the Implementation Guide section on Database Publishing setup, but I must have moved my eyes too quickly over the sentence that says that Content ID must be a “primary key/foreign key”. I picked up on the fact that the SEQ column is required (partly because of the picture accompanying the text), but missed the foreign key requirement.
One additional question (actually three questions…):
I would also like to set up database publishing so that when I move something to the state “Unpublished”, it triggers a deletion of parent and child rows from the publish database.
Is this set up via a separate database publishing template, with $db.action=“d”?
How do I make sure the child rows also get deleted (presumably before the parent row is deleted)?
How do I hook that template into the Unpublish state transition?
Unpublishing for DB Publishing is just like Unpublishing for File Publishing.
The DB Publisher will delete the Parent and Child Rows when the item is “unpublished”.
Just as with File publishing, there are 2 parts: In the workflow, the item transitions to a new state, and then the publishing edition runs, causing the web site/database to get updated.
It’s possible to hook these 2 processes together (which we call “Publish Now”). The basic strategy is to run an incremental edition through a workflow action, so that the items on the site are not out of date.
It doesn’t matter if you publishing or unpublishing, the process is triggered the same way.