1

I'm using PHP (Yii-framework) and MySQL database on my site.

Users can post and edit some articles. How can I store every edit version like here in SO? (Like Wikipedia, this site is collaboratively edited, and all edits are tracked)

What sould I use and how? Database structure? Some libraries? What keywords should I use to google? :)

UPD. And how to display changes between revisions, like here in SO?

Thanks

Bojangles
  • 99,427
  • 50
  • 170
  • 208
Lari13
  • 1,850
  • 10
  • 28
  • 55
  • 3
    Searching for `php mysql version control` gives this: http://stackoverflow.com/questions/4127252/basic-version-control-for-mysql-table – Felix Kling Mar 23 '11 at 08:39
  • This article has some interesting thoughts on versioning and why ActiveRecord isn't good for it. The idea is to store the metadata separate from the content in the DB: http://kore-nordmann.de/blog/why_active_record_sucks.html – thaddeusmt Mar 24 '11 at 17:08

2 Answers2

2

I'd use your current database structure (if you have one, otherwise make one with all the fields you need), but add 1 or two extra columns, the most important being the revision number. This can simply be incremented each time a revision is made. This can be tricky to code cleanly into the database INSERT command (note, use INSERT not UPDATE because you want to keep the old post).

Another way would be to have a column storing the time the update was submitted (maybe just "created time"?), and from that you can glean how many updates there have been (count the rows), and what order they are in (order by date).

Bojangles
  • 99,427
  • 50
  • 170
  • 208
  • Should I store whole text in new revision even if changed only one string or word? How to show differences between revisions like here in SO? – Lari13 Mar 23 '11 at 08:54
  • 1
    It'd definitely be easier to store the whole text. To show differences, take a look at http://stackoverflow.com/questions/321294/highlight-the-difference-between-two-strings-in-php, specifically http://pear.php.net/package/Text_Diff. – Bojangles Mar 23 '11 at 11:16
0

You're looking for a PHP ORM with audit logging capabilities. What is the ORM embedded within Yii framework?

I know that Symfony uses Doctrine and has a dedicated Versionable Plugin that does exactly what you want.

Maybe the "logable_behavior" Yii skeleton can help you.

Brian Clozel
  • 56,583
  • 15
  • 167
  • 176
  • I'm using plain old DAO, not ActiveRecord or other ORM – Lari13 Mar 23 '11 at 09:10
  • Maybe it's time to think about it then. If you need audit logging now, you may need i18n and other things already implemented in yii activerecord behaviors or ORM plugins. – Brian Clozel Mar 23 '11 at 09:39