Ayo wrote:
> Inserting a new release and its first revision is done as explained with the
> following pseudocode:
>
> $rid = INSERT INTO releases DEFAULT VALUES RETURNING id;
> $rev = INSERT INTO revisions (type, uid) VALUES('r', $uid) RETURNING id;
> INSERT INTO releases_rev VALUES($rev, $rid, $title, $date);
> INSERT INTO releases_lang VALUES ($rev, $lang1), ($rev, $lang2);
> UPDATE releases SET current = $rev;
>
>
> Inserting a new revision of an existing release is quite similar, except that
> there is no INSERT INTO query for the releases table, and $rid is already known.
>
> This system has worked quite well, but has one problem: every client that wants
> to insert a new revision will have to manually copy over all releases_rev
> columns and releases_lang rows of the previous revision, even if all they want
> to change is just one single column. This in itself is not a problem, but
> whenever a new column or table is added to the database, all clients will have
> to be updated again so that they copy over the new information correctly, which
> is very prone to errors.
I'd say that, like every other CS problem, it can be solved by adding
another layer of abstraction; only that instead of adding stored
procedures as you suggest, add a new relation that sits between
releases_rev and releases_lang (say release_msg). Then you don't
translate a revision -- you translate a message, to which many revisions
can be pointing.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.