Mailing-List: contact cayenne-user-help@incubator.apache.org; run by ezmlm
Precedence: bulk
Reply-To: cayenne-user@incubator.apache.org
Received-SPF: pass (asf.osuosl.org: domain of hefest@gmail.com designates
 64.233.166.179 as permitted sender)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
        s=beta; d=gmail.com;
        h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
        b=QACPa178vCfo2Dx6Rl+YVCNoaeHwailMdpj9790rwUiuFAtbWjX0dR6AjML/w8PryurHkjNjgor0DMg4AaYgIPs3mwASM4YBiwJ3Jl04tzkR2QiQROB1PsBXleLOJ0Lj93RhmrwWQS4Xpid4UhqC4WktFmfnSQgSRWxtLGkgf2Q=
Message-ID: <d487eb8e0608250812l201dad3fj5054ab5df729cdc4@mail.gmail.com>
Date: Fri, 25 Aug 2006 17:12:13 +0200
From: "Tomi NA" <hefest@gmail.com>
To: cayenne-user@incubator.apache.org
Subject: temporal database generation
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I've been tasked with designing a system, including it's database to
capture data which changes through time but shouldn't overwrite
existing values. I've also ran into other people describing these
kinds of databases in their work, referring to them as "temporal
databases".
Here's why I'm writing to the cayenne list about it. I've been
thinking...say you started with a regular database model (a cayenne
model or an existing db model) like you would on any project. The
modeler could conceivably generate the whole thing: a pair of
"valid_from" - "valid_to" fields in every table, code which would
transform an update command into a relevant update/insert command pair
and finally, code which would allow transparent access to the most
current information stored in the database.
The generated API would be almost identical to the one cayenne
generates now for a "plain" database, the only exception being the
possibility to define a timestamp (maybe at the context level) which
would allow you to do the same (SELECT) queries, but effectively
moving back in time to the desired moment.

Concievably, you could do something like this:
DataContext dc = ...;
dc.setPointInTime(beginningOfTheYear);
// prints out the number of employees on 2006-01-01
System.out.println(dc.performQuery(new SelectQuery(Employee.class)).size());
dc.setPointInTime(now);
// prints out the number of employees on 2006-08-25
System.out.println(dc.performQuery(new SelectQuery(Employee.class)).size());

Obviously, a database like this would grow possibly very fast so it
might not be a suitable design where space is a major concern, but I
can think of a lot of applications where this would be a fantastic
thing to have out of the box.

I'd appreciate anyone with an opinion on this to comment.

TIA,
t.n.a.