in reply to Re: Re: Re: Re: Version Control in Database Applications
in thread Version Control in Database Applications

Yes, of course my system requires that I update the key to implement revert. For example, let's say there are 10 versions of foo #1024: (1024, "1024,1" .. "1025,9"). To revert to version 7 I do:

DELETE FROM Foo WHERE id = "1024" OR id = 1024,9" OR id = "1024,8";
UPDATE Foo SET id = "1024" where id = "1024,7";

[download]

What's wrong here? You say it's "nasty" but I don't see anything particularly bad about it.

The thing is, reversion is an uncommon operation. It can be a little hairy and nothing bad will happen. There will only be one place in the code handling reversion. However, linking between objects in join tables and general querying will happen all over the place. If that requires two columns per table for every join I know we'll have problems.

-sam

Replies are listed 'Best First'.
Re: Re: Re: Re: Re: Re: Version Control in Database Applications
by perrin (Chancellor) on Jun 11, 2002 at 03:18 UTC
    I say it's nasty because if you have referential integrity constraints on other tables pointing to this, you won't be able to simply delete where id = "1024". Also, doesn't your sample lose all versions after 7? I expect people would want to keep those.

    I still don't think multi-column keys are a big deal, especially if you hide them with a view. However, now I'm wondering if versions are really what you're after. What most people really mean when they say they want versioning of content is the ability to have a live version and an in-progress version. They couldn't care less about going back to old versions but they want to be able to put edits to the content through some kind of workflow process before making it live. If that's your situation, maybe you can get away with something simpler.

[reply]
      Referential integrity constraints? Probably not. Most likely this will be a MySQL database. Does MySQL even have views? It didn't last time I worked with it, but I'm not fully up to speed on new developments.

      Yes, this implementation would make reversion irreversable. I guess that might not be desirable although that's a better question for the clients than for me. I could implement a reversible reversion by creating a new version as a copy of the old one. That should be pretty easy to implement considering that the checkout routine will already need a cloning capability.

      I think in this case my users really will want full versioning capabilities. The system they're using now has them and I think they've gotten pretty used to it. However, given what a pain this is to implement I think I can make a pretty strong argument that going with something simpler could save them a lot of money in development.

      Thanks for the help,
      -sam

[reply]
        You are probably better served by using PostgreSQL instead of MySQL. MySQL is not a very good database for a multiuser aplications (lack of decent locking, referential integrity, etc.). I know MySQL has come a long way since I last looked at a year or two ago (I think they may finally have referential integrity), but I still think it does even come close to PostgreSQL in terms of ACID compliance.

        UPDATE: Apparently MySQL now has some weird form of row level locking for some table types.
[reply]

In Section Meditations