I am implementing database publishing for the first time and the basic function is working well. I have two questions about $db.action.
Can one combine values? i.e. “ru”, to update existing rows if they exist and insert new ones if they don’t? If not, must one create separate templates with different $db.action values?
The description of “d” is curious: “Deletes the row if the content exists”. Does this mean that it will delete items returned in your content list from the database? It seems like that would be a fairly rare use case, but most likely, one would want to delete content from the database which has been purged from the CMS, or removed from a given folder. How would one do these things? I’m guessing that the “Publish”/“Unpublish Then Publish” value chosen for the edition somehow will play into this, but I’m not sure how.
One note on having separate templates with different $db.action values:
We had separate templates for publish and unpublish, as described in the 6.5.2 documentation. It meant a lot of maintenance of redundant code, since the templates were otherwise identical. Just recently, we implementing a change to the binding for $db.action, to use the item filter from the content list to determine what action to take. The binding value for $db.action is:
if ($sys.params.sys_itemfilter[0] == "unpublish") {"d";} else {"r";}
This allowed us to combine our publishing and unpublishing templates, simplifying maintenance.
I did a bit of playing around with this today; we are on v6.7 and there’s a “Behavior” field with a radio button group w/ values of “Publish” or “Unpublish Then Publish”, when I choose the latter, this deletes items from the database which have been removed/purged from the folder in the CMS, so that seems to solve the problem in the 2nd question I had in my post. Your snippet gives me an idea though; I hadn’t thought of writing a conditional in here. If I can somehow check if the row already exists and return “u” if it does, but “n” it it doesn’t, that would accomplish the ideal behavior. Thanks Kathleen.
In general, “r” is the $db.action you’ll want to use most of the time. As the Implementation Guide indicates, “u” is for very specific use cases and will not work properly if there are any child tables being implemented.
“r” - “Replace” Inserts a row for the content. Deletes it first if it already exists. This is the default value when the binding is created, and is the assumed value when the value is missing or invalid.
“n” - “New” Inserts a row for the content if it does not already exist.
“u” - “Update” Updates the row for the content if it already exists.
“d” - “Delete” Deletes the row for the content if it exists.
NOTE: The “u” option should not be used if a Content Type published using this Template includes complex child data. The “r” option should be used instead.
As you mentioned, using the “Unpublish, then Publish” setting for an Edition will result in the system generating appropriate entries (with a “d” action) for any items archived in the CMS.
Micheal,
It seems to me that the behavior of “r” would be Ok for certain systems, but I’ve worked with many systems (and suspect I will encounter this with the client I’m working with) where deleting and re-inserting rows cause integrity constaint violations when other rows in other tables have foreign key relationships with these exiting rows. I suppose given the knowledge that “u” is preferred, as we design solutions that involve the CMS exporting data, we should make sure to design data models that allow for this behavior, but off the top of my head, the only thing I could think of to get around this is to turn off all integrity constaints (presuming you can do this in SQL Server), then turn them back on after the publishing is done.
I’ve found that “r” and “u” (in 6.5.2 at least) has some weird behavior that isn’t documented correctly and the settings I described there were the best I could come up with, at least for the situation I have at my client.