moritz has asked for the wisdom of the Perl Monks concerning the following question:

The other day I thought about writing a web application that manages material data. It seems straight forward: set up a database, store material name and a bit of meta data, properties + values + units, set up a CRUD interface, and be done.

As always, reality is a poor approximation to my mental model, and things are more complicated: the properties of the materials need to be versioned.

No big deal, just a linear revision number with the associated values. But so far I haven't found a clean way to deal with versioned properties in object mappers like DBIx::Class or Rose::DB::Object.

Ideally the object that represents a versioned object (or an object with versioned properties) should ...

  1. allow a revision to be selected as the default
  2. default to the default/current revision, and easily allows to inspect other versions
  3. create a new revision, and store it as default whenever whenever a property is changed
  4. (optionally allow the history to be changed - don't know yet if that's a good idea)
  5. optionally track revision control meta data (who changed the revisions, and when?). Tracking changes to which revision is considered default isn't necessary.

Most search queries against the database would only query the current revision, but some would also query all available revisions.

The more I think about the proble, the more I'm sure that such a version aware db wrapper is desirable in many situations, but I haven't found anything useful on cpan and freshmeat.

Now I seek my fellow monk's wisdom and ideas:

(For a while I thought about building it the other way round - storing the data with git, and build a database engine on top of that, but I discarded it as being to much work).

At the moment speed isn't all that important, but it should still be scalable - no O(nē) lookups if possible ;)

Replies are listed 'Best First'.
Re: ORM with version control?
by Corion (Patriarch) on Feb 06, 2008 at 14:56 UTC

    Don't do this stuff in your ORM. Do that stuff in the database by using triggers. That way, you get the versioning even when you bypass the ORM (untested but taken from here):

    CREATE TABLE foo (
    $definitions
);

CREATE TABLE foo_versions (
    username  VARCHAR(8) DEFAULT current_user(),
    timestamp DATETIME DEFAULT NOW(),
    action CHAR(1), -- IUD
    $definitions -- stores the values BEFORE the action
);

CREATE TRIGGER foo_update BEFORE update ON foo
FOR EACH ROW
INSERT INTO foo_versions ('U',$columns);

CREATE TRIGGER foo_delete BEFORE delete ON foo
FOR EACH ROW
INSERT INTO foo_versions ('D',$columns);

CREATE TRIGGER foo_insert BEFORE update ON foo
FOR EACH ROW
INSERT INTO foo_versions ('I',$columns);

    [download]

    Of course, your versions table(s) will grow quickly, but as you'll only ever append to that table, you can truncate it at convenient times.

[reply]
[d/l]
Re: ORM with version control?
by locked_user sundialsvc4 (Abbot) on Feb 06, 2008 at 16:06 UTC

    One way to do this would be to define the material-properties table with three values for its primary-key:   material-ID and property-id and version-number.

    To obtain the “current” properties of the material, you link on material-ID-plus-version. If you need to look at prior versions, link on material-ID alone.

    In a typical ORM-setup, you would probably define one object to return “the current properties only” and a separate object, albeit referencing the same underlying SQL table, which will return “all versions of all properties.” The latter object would most-certainly be defined as read-only.

    I respectfully dissent with the admonition to “use triggers,” out of concerns that triggers can be very time-consuming in many implementations. (But I say that having given the prior posting “only a passing glance,” not a careful and thorough consideration, and my dissent should be considered in that light.)

Re: ORM with version control?
by Tabari (Monk) on Feb 11, 2008 at 16:24 UTC
    Hello

    The following is a purely DB related answer to the question.

    I am also inclined to avoid the use of triggers. What follows is an idea I had on the issue, but I did not realize it yet in practice. Moreover, it depends on possibilities of your DB :

    Keep one table containing all objects and their history, so primary key should somehow involve the history, I would actually prefer to create a new unique id for each modification to the original object.
    Keep a field in the table to indicate that a specific version is default or current.
    Create a materialized view and let your DB do the work of updating.

    Admittingly, the work of adapting the view when the underlying table changes is not avoided, but this seems to be less cumbersome in maintenance than adapting triggers

    Tabari
[reply]
Re: ORM with version control?
by Anonymous Monk on Jun 26, 2008 at 15:05 UTC
    I've made tables that have id, version as the primary key, plus a "current" field. Then I have a view that only selects the "current" items, and use whichever is appropriate. Something like this- 
    create table all_properties (
  id int primary key; // Same across all different versions!
  vers int primary key;
  current boolean;
  created_by char(40);
  created_on date;
  property_name, property_value, etc....
)

create view current_properties as
 select * from all_properties where current=boolean;

    [download]
    Then you don't get the storage hit and headaches that materialized views can give you, and it's rather clean conceptually.

    You probably still want a trigger to be sure "current" is only true for one version.

    (I cheat a bit by letting "vers" be null for the current version, and not having a "current" field, which works OK only if you want the most recent version to always be the default one- which isn't your situation.)

[reply]
[d/l]

Back to Seekers of Perl Wisdom