Versioning code in db

[eluser]webthink[/eluser]
Don't underestimate the ability of mysql to easily handle millions and millions of records.

[eluser]xwero[/eluser]
If you are that afraid you will run out database connections you could use files instead. Each of them unique by the revision number and/or user_id and grouped by their name, you could add the date too. The file you are going to show by default is the file with the highest revision number. Wrapping the content in xml tags seems overkill to me.

Databases have a locking mechanism build in so you don't have to worry about that. If you go for the files approach you serve the latest available revision.

Which way you choose you can use ajax to poll if someone else is working on the same document and if they are finished you could show a message so you are able to see the changes the others made before you add your own revision.

You update the content, the date and the username if needed for the tables_content and you do a insert select query to move the previous content. Those two queries are affecting two rows. That will be done in miliseconds. I don't think you have to worry about that.

[eluser]louis w[/eluser]
What about comparing versions to tell the user what has changed? Any advice on this matter? Has anyone does this before in a cms?

[eluser]Daniel Eriksson[/eluser]
Who-did-what and rollbacks are two entirely different use cases.

A simple logging facility is all you need to know who-did-what. Implementing rollbacks is usually something pretty domain-specific and much harder to get right.

/Daniel

[eluser]esra[/eluser]
I'm not sure if this applies in your case. For the content table in my current project, I treat non-null content_id (id), language_id, and revision columns in my content table as a combined primary key. This allows multiple instances of content to exist for each supported language as well as multiple revisions of content for each language instance of content. The content table includes common content columns which remain the same for all content records. Language-specific records for all revisions are stored in a separate table (content_data)using content_data_id and content_id (fk) as primary keys. This approach was used in order to support the MySQL MYISAM engine.

The above might not work with some of the Active Record or ORM solutions mentioned on the wiki or in forum posts. The above approach could be adapted for those by the treating content_id (id), language_id (fk), and revision as a unique key, assuming the INNODB engine is used (referential integrity then applies). Most of the other database engines for CI support referential integrity (via foreign and unique keys), so it should be possible to use the same approach for those database servers. I'm seriously thinking about refactoring my code to use this approach rather that using three columns as a primary key in order to use one of the third-party Active Record solutions.

Within my content table, I use the parent_id field (Adjacency List model) to assign content into generic categories for administration reasons only, allowing the content to be reused as needed by various controllers (blogs, articles, faqs, help, frontpages, products, etc.). Separate nested set tables are used to map content to the hierarchies for the various flavors of content. Those tables usually include id, content_id (fk), lft, and rgt columns and no actual content is stored in those tables (only the hierarchies). This allows any content record to be used by any module and among multiple modules. It also allows new tables to be created for new flavors of content on-the-fly since the column names are identical.