Hi!
I've done versioning with dbic too, but I decided not to store the version
number and the datetime because that would be redundant.
In my case every request (table name is fw_request for the example, I have
three different request tables) has an id_fw_request (the database primary
key), a datetime field (req_datetime) and a req_id (number which is filled by
the catalyst app, either from an oracle sequence if it's a new request or with
the value from the current request if a new version is requested).
I made a view with all requests ordered by req_id and req_datetime and use the
rank function to generate the version number:
For me (Oracle 10g) the view query looks like this:
SELECT id_fw_request, RANK() OVER (PARTITION BY req_id ORDER BY req_datetime)
AS version
FROM nacadm.fw_request
Then I have another view which gives me the datetime of the latest version per
req_id:
SELECT req_id, MAX(req_datetime) req_datetime_latest
FROM nacadm.fw_request
GROUP BY req_id
Both views are used in a normal dbic table class and joined as needed to the
request table class (from package NAC::Model::DBIC::Table::FW_Request):
__PACKAGE__->has_one( view_version =>
'NAC::Model::DBIC::Table::View_FW_Request_Version', undef, { proxy => [qw/
version /] });
__PACKAGE__->has_one( view_latest =>
'NAC::Model::DBIC::Table::View_FW_Request_Latest', {
'foreign.req_id' => 'self.req_id',
'foreign.req_datetime_latest' => 'self.req_datetime',
},
{
proxy => [qw/ req_datetime_latest /],
}
);
If you join and/or prefetch view_latest, you will only get the latest version
per req_id, because the join is defined as a full join only the latest versions
will be returned, because of the proxying you can directly access 'version'
from the request.
If you have more questions write to the list or join us at irc.perl.org
#dbix-class.
-Alex
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:dbix-class-
> [EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
> Sent: Tuesday, October 17, 2006 7:08 PM
> To: [email protected]
> Subject: [Dbix-class] Hi All, First Question
>
> Hi All,
> 've got a table, called estimate,containing 4 fields : id, ref,
> revision,
> status.
> id is the primary key,
> ref is the number of the estimate in format (number/year)
> revision is a int that specifies the revision of the estimate.
> Now... in table can be more than one rows with same ref but several
> revision numbers
> I've done something like resultset({ %search },{ group_by => [qw /ref
> /]}
> but in some cases this give me back the row with the lowest id for that
> group.
> Example:
> 1,1/2006,0,0 <--- this is the row returned by the query
> 2,1/2006,1,0
> 3,1/2006,3,0
> 4,1/2006,4,0
>
> but... i need the one with id = 4
> Any help on doing it?
> Thanks in advance
> Tiziano
>
>
>
>
> _______________________________________________
> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
> Searchable Archive: http://www.mail-archive.com/dbix-
> [EMAIL PROTECTED]/
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
T-Systems Austria GesmbH Rennweg 97-99, 1030 Wien
Handelsgericht Wien, FN 79340b
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
Notice: This e-mail contains information that is confidential and may be
privileged.
If you are not the intended recipient, please notify the sender and then delete
this e-mail immediately.
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
_______________________________________________
List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
Wiki: http://dbix-class.shadowcatsystems.co.uk/
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
Searchable Archive: http://www.mail-archive.com/[email protected]/
