Versioning idea

How to:

Add additional fields to the content table called version and active. When a user goes to edit a page the sql query finds the content identified by a content id with the active flag set. When a user needs to roll back, just present a list of all the documents that have that content id and then set active the document they choose to roll back to.

While that would work, it is very inefficient in storage-- if I only change the page title of a 3k document, I’m still making a 3k copy everytime I save the page/change "teh" to "the", etc.

Real versioning usually only stores the changes between versions-- i.e. something like this:



Changed to:


The versioning for the changes might be stored thusly:


As you can see, it takes a lot less space.

Quote from: Mark at Sep 12, 2005, 05:53 PM

Add additional fields to the content table called version and active. When a user goes to edit a page the sql query finds the content identified by a content id with the active flag set. When a user needs to roll back, just present a list of all the documents that have that content id and then set active the document they choose to roll back to.

I believe a more efficient approach, in terms of database space and the ability to track specific changes, would be to store only the differences (i.e. deltas) in a separate version table. In fact having a version repository, much like a simplified CVS or subversion system, would also allow versioning of any other resource stored by the CMS, from TV content, to module/snippet/chunk/plugin source. We could even provide an API, to allow versioning of user-defined data from a custom database table.

But even if we didn’t get as advanced as storing only the deltas right away, I believe it would still be more efficient to store versioned elements in a separate table, rather than binding and replicating rows in the same table. The entire site would be affected by the slower query performance as versioning history increased; more rows = slower query time.

Quote from: OpenGeek at Sep 12, 2005, 07:52 PM

I believe a more efficient approach, in terms of database space and the ability to track specific changes, would be to store only the differences (i.e. deltas) in a separate version table.

Great Ideas!

My first post was written just to spark some brainstorming about this feature and was never intended to be "the definitive solution". It was just a collection of ideas that others could build on (or just comment on).

From an implementation standpoint, it seems like full version (as opposed to the differences only version) of versioning would be far simpler. I agree that more database records means more query time and therefore more page load time. However, it also stands to reason that the extra processing to interpret all the version changes on the original would have an impact on page load time. MySQL runs its queries VERY efficiently.

In fact I did a test a month back or so on retrieval speeds of different types of queries and on different types of data fields. The results were very interesting. But for the sake of THIS discussion, I’ll just say that my sample database of 300,000+/- records showed very little in the way of sluggishness - especially when the queries were written to be more specific. A query for a record with "active" checked is pretty specific and shouldn’t affect query speed TOO much. At least from my experience.

I would however, attach the version/active fields to the specific data field and not the document as a whole. Again, I completely agree that storing only the differences would be more efficient from a database storage standpoint, but from a practical standpoint I’m not convinced it’s the better overall way to go. Seems to me like limiting how many older versions are kept - or better, how old of older versions are kept might be a good way to keep the data pruned.

I totally agree at this point, that full versions would be simpler (and a quicker approach to getting a solution for versioning in place), but I still believe isolating the versioned data to a separate table is important, so performance is only affected when absolutely necessary (i.e. when acting on or considering the historical data). Granted, joining to another table can sometimes be more of a performance issue than querying a single table, but with historical relationships being maintained in a single table, joining to aliased copies of the same table would be required anyway.

Also, indexes on fields such as active can make huge differences in query performance, something I’d like to see more attention paid to moving forward when designing/refactoring our data structures.

And I love the idea of offering optional time or quantity quotas for the versioned data. Option 1 - Version expiration date or time to live, Option 2 - Limit version count (i.e. max versions allowed per subject data field).

Hello All,
I am working out a method to set up versioning in the site-content section of the site and wanted to bounce some ideas. I know that database query is a big issue with versions that may cause a problem with approaching versioning with using deltas so I am going the route of making complete copies of versions in a separate table. My idea is as follows:

When a news article/blog post is created it is stored in modx_site_content as it currently is, the only difference is that a new column now exists labeled version. Version is binary [1/0] and is not accessed when content is read on the site so need to change anything on the presentation. Version is accessed when someone goes to edit content, where modx now checks to see if the Version flag is set. if it is then the edit flag queries the version table for exiting versions of the document, which are stored by date. This could allow for a similar control as what is seen in Writeboard by 37 signals where the versions are listed and can be compared. The document can then be saved to overwrite the content without creating a new version (writing directly to the modx_site_content table) or by adding a version (first a copy of the previous content to the version table [where create date now acts as the version date and identifier] then populating the modx_site_content with the new content. This method limits version based queries only to editors.
The idea could be extended to template variables based on how much versioning is needed [ie. adding the versioned column to the modx_site_contentvalues and creating version table for template variables and adding logic to the versioning system to look for version flag in the modx_site_contentvalues table] .
Just wanted to hear your thoughts.
Cheers .

I think the approach to versioning should be as generalized as possible, i.e. not restricted to the site_content table etc. One of the main reasons for versioning are logging (what was published when and by whom) and rollback. The latter may affect not only content but the site as a whole, i.e. including all tvs/chunks/plugins/snippets/modules/user-accounts etc etc. So I think doing it like Jason suggested it would be the best way to go, i.e. making versioning a part of the core architecture. (But this, for sure, will also have an impact on performance )
Being able to "back up" documents is IMO a different issue which could be solved by a clone of site_content and a relation to the original via docIDs.

just my 0.02...

FYI, romdg’s post got me inspired to get this part worked out, and what I’ve come up with, revised from what I had already designed I believe will perform quite well. Let me try and break this down into concise bullet points:

• Versioning happens only to content-producing elements (i.e. templates, TV’s, chunks, snippets, plugins, etc.)
• Versions don’t apply to resources (i.e. documents, weblinks, web services, Ajax services, etc.) directly
• There are effectively four ways to define a baseline content item:
[list]

• Each element defines a default content object that has a baseline version of the content
• Elements can define context-specific content overrides (i.e. one chunk can have different content based on where it is requested from, for instance, a menu snippet might behave differently when used in the mgr context vs. the web context), which also sets a new baseline content version
• Resources can override the default content object for each element, effectively creating a new baseline version as well
• Each baseline content item can further split itself based on Culture, creating new baselines, once again

[*] From each baseline, every subsequent version of content is stored only as a diff, which can be patched back to the baseline content item up to any specific revision number
[*] A current snapshot of the content revision that is considered the HEAD revision for a piece of content (the revision which is effectively published to the site) is stored by baseline content item and culture; this is the base revision patched with all subsequent diffs up to the indicated revision number.
[*] You can define future revisions (or drafts) that are not patched into the current content snapshots until indicated
[/list]

This approach should make it very flexible and easy to work with, though there is always room for improvement.

Also, two other issues which are related to the backup vs. versioning argument:

• A dedicated table to maintain a consolidated search index, built from snapshots of rendered output from resources (i.e. documents or pages), and optionally divided by context and culture, will address optimization of search amongst a sea of normalized tables used to efficiently achieve versioning
• Defining a more robust approach to static page caching, perhaps in conjunction with the dedicated search index, based on some of the work tillda has been doing on caching HTML files, is definitely on the agenda.

Hey Jason,
Thanks for weighing in on versioning, as the approach you suggests most definitely looks promising. I think I was unclear in what versions I was talking about. From a subversion/cvs standpoint your statements are 100% right on. But I was referring to the actual content in the CMS. The (templates, TV’s, chunks, snippets, plugins, etc) build a structure for our data, but I am interested in maintaining versions of the content itself as seen on 37 Signals Writeboard. As the *content* tables house the actual document the themplates, snippets, and plugins provide tools to format, arrange, and display the data. I am conjuring up a solution to provide copy versions and in turn a approval mechanism on what is published to the live site. Chunks house flat data and would benefit from content version control as well. Sorry if I was unclear, but you are absolutely right that there is a need to define version control in the tool itself.

Cheers,
Gregg