How to deal with long running transactions?

Question

I'm developing a Content Management System (CMS) and I would like to include a "Save Changes" button. I mean: a user performs several changes in the database, but those changes are still not available. Just when the user presses the "Save Changes" button, those changes are "visible" for the website.

I have thinking in "SQL transactions". But I have understood that "SQL transaction" were designed to be used in a short space of time. And in this particular case the user may keep the transaction open for some time (one hour, two hours...).

How can deal with this kind of issues? I'm using PHP and MySQL?

Answer 1

If you're storing a history of changes (drafts) with a flag to mark the publicly viewable state then your submit changes button can just set all the state on the page to be the publicly viewable state. You can use a transaction for this since it would serialize all the changes. This is especially useful if you implement a system for rolling back such state changes to previous configurations.

A more verbose explanation would be that if you had tables like:

articles(id, url)
article_titles(id, article, text, timestamp)
article_body(id, article, text, timestamp)
article_history(id, article, title, body, timestamp)

Then when the title is changed it just inserts a new record. When the body is changed or saved periodically it just inserts a new body record. Now when you hit publish it inserts into article_history and when displaying publicly to the user the renderer grabs the most recent article_history record and the corresponding title and body.

(Note, this is just an example to try to relate to what you described. I'm not saying this is a correct design for a CMS or good practice).

Answer 2

There are lots of content management systems and I have only used a couple, but I think what content management systems frequently do is maintain two databases or filesystems, one for you to edit content and the other for what the users see. I know the content management system I used to use a lot (Vignette, now OpenText) did that. There are two separate parts, Management/Preview and Delivery. Management is where you do your editing, and when you want to let users see the content, you publish to Delivery. Publishing involves copying from the Management database to the Delivery database.

This means that you could save changes many times before publishing, or never publish at all. For the content admins, it is much easier to use a system that lets you save and come back to your work later and publish when you are ready.

Answer 3

Think of SQL transactions as groups of SQL queries ran together in one connection to maintain database consistency, eg. when removing data, remove also any linked entries from relation tables to keep the database clean. If a problem occurs, you can use ROLLBACK to revert the changes in current SQL transaction. PostgreSQL can take care of consistency by itself and can do a lot more with larger projects.

You can log a history of changes or you can add another column to the table (text _draft). Visitors would still see original text, but while you would be logged as admin, you could check for any data in this column and display this instead of original text. By opening the two regularly and in anonymous window you could easily see the changes.

Answer 4

One of the approaches is versioning. You'd design your content table with version and active column. Every time you auto-save you bump the version but don't mark row as active. Once you hit Save you will bump version and publish the content. Gives you complete history at the cost of big tables. I think WordPress uses the same approach.