Versioning code in db

[eluser]louis w[/eluser]
I am in the planning stages for an app which lets users enter data for a website. I would like to save versions of an item to allow for rollback and seeing who did what.

I am looking for any advise you can share on the best ways to do this. Are there any preexisting CI libraries to allow for versioning?

An example of an app that does this would be the 37 Signals Writeboard. You can see here:
Password: testing

One way to do it is to include a parent_id and time created in the table record. Lets say your table is called content_blocks. When user first creates a content_block you write it to the table without a parent_id (default to 0 to indicate that it is the first entry) when someone edits that content_block instead of overwriting the record you write a new one with parent_id corresponding the id of the first record. All edits thereafter contain the parent_id of the original content_block so if you want to view them all you query by that (WHERE id = x OR parent_id = x ORDER_BY time_created).
In order to pull the most recent content_block to display on the website you can do something like this (WHERE content_type = 'front_page' ORDER_BY time_created LIMIT 1)

Another way to do it is using two tables : content_blocks and content_blocks_prev. The content_blocks table holds the last revision and if a block gets updated the row moves from content_blocks to content_blocks_prev with an additional date field.

I think handling versions in the model is by far the neatest solution. See: for an example. It might be quite difficult to implement similar behaviour in CI.

Or here:

[eluser]Glen Swinfield[/eluser]
I would probably use an xml file in this instance to kep track of changes in one file - it would be much simpler than related database rows.

You could have one hundred edits to a document -all linked by a parent ID to each other? Not the best use of database resources. keep it all in one XML file.

I don't think it matters that much if you store it in a database table or in a xml file. The versions are garbage until you need them. Using xml file(s) only moves the "wasted" resources from database to disc space

I suppose storing them in a seperate table as xwero suggested allows for better indexing and more efficient queries when retrieving only the most recent revision if that's what you'll be most often. mysql tables can handle hundreds of thousands of records without batting an eyelash. I don't see the advantage of storing them in xml.

[eluser]Glen Swinfield[/eluser]
My reasons for suggesting an xml file were not just about storage, the database option (that Expression Engine uses for its template revisions I think) requires quite a few DB transactions to perform a fairly simple task - and you have to deal with the possibilty of multiple people editing the same content at the same time. This can all be easily resolved by using a file and in the end you have a single document that provides a rolling log of all activity and the current version. This can be easily archived/un archived as required/ transform with xslt and so on.

I am not suggesting that you should or could not use a database, just that sometimes databases seem to be the default solution to problems that can be answered in other ways. Databases provide functionality to quickly order, search and query volumes of data - but this doesn't seem to be the primary purpose of your system.

That said - xwero's DB solution seems like the most practical/scalable.

I was thinking a bit more about this and I might ammend xwero's solution a bit. Instead of doing any moving of 1 row from main table to archive table (which is slightly more complicated and could create race conditions as Glen's suggested) You simply write the record to both tables at the same time. Updating in the content table and inserting in the archive table. This way your most recent revision is in the archive table and requires no joins, unions, or otherwise to query. This could all be done inside the model under a single update method.

[eluser]louis w[/eluser]
Interesting approaches, thanks. I need to think about this a little bit more. I am a bit worried about writing a row to the db every time someone hits save - it could really add up. But it really would be the simplest way.

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  

  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.