Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options

ORM with version control?

by moritz (Cardinal)
on Feb 06, 2008 at 14:13 UTC ( #666557=perlquestion: print w/replies, xml ) Need Help??

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:

  • Is there already a reusable implementation of such a system?
  • If not, do you think it is implementable with reasonable effort?
  • Is it possible to build such a system on top of an ordinary DB engine (let's say postgres) without too much performance penalty?
  • How would you implement it? On top of a ORM wrapper? Or as a new ORM wrapper? or somehow completely different?

(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 (Pope) 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);

    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.

Re: ORM with version control?
by 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

    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

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;
    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.)

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://666557]
Approved by Arunbear
Front-paged by Arunbear
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (4)
As of 2019-12-07 11:49 GMT
Find Nodes?
    Voting Booth?
    Strict and warnings: which comes first?

    Results (160 votes). Check out past polls.