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

Re: Speed. My guess is that given an indexed fixed-width CHAR field I should be able to get acceptable speed on lookups. All I need to do is decide on the maximum total number of objects and the maximum number of versions and I should be able to pick a field width. I'll be using MySQL and my memory is that MySQL indexes CHARs just as well as it does INTs.

Re: Querying. I think you misunderstood (or I did). In my planned implementation the current version would always exist without an appeneded ",version". Thus, selecting the current version is just:

SELECT * FROM foo WHERE id = "1024"

No sub-select required! And selecting version N is simply:

SELECT * FROM foo WHERE id = "1024,N"

Now, I may still need an extra column to store the current version number but as long as it isn't needed on every select and every join I'll be happy.

I think you're correct about collecting aggregate information across versions being hard. But I don't anticipate needing to do that very often, if at all.

-sam

Replies are listed 'Best First'.
Re: Re: Re: Re: Version Control in Database Applications
by perrin (Chancellor) on Jun 11, 2002 at 01:18 UTC
    But how do you make an old version current? Change it's primary key? Nasty. And if you don't do that, you'll have to know which version is live, and that means your queries won't be so simple becuase they'll need to look up which version is live first and won't be able to assume it's a simple key with no appended number. Sounds like a tangle to me. I would go with the two-part key.
[reply]
      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

[reply]
[d/l]
        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]

In Section Meditations