[Dbix-class] Issue converting select/insert from DBI to DBIx::Class
Hi all,
I've been working on converting much of my DBI code to DBIx::Class, and
am a tad bit stuck.
This particular issue is one that I've worked on for quite a few hours,
while testing out different combinations I've found in the great number
of docs. I'm at the point now where I could use an experienced set of eyes.
My original code:
my $insert_sth = $dbh-prepare(
INSERT INTO aggregate_daily
( UserName, AcctDate, ConnNum,
ConnTotDuration, ConnMaxDuration, ConnMinDuration,
InputOctets, OutputOctets, NASIPAddress
)
SELECT UserName, '$day', COUNT(*), SUM(AcctSessionTime),
MAX(AcctSessionTime), MIN(AcctSessionTime),
SUM(AcctInputOctets),
SUM(AcctOutputOctets), NASIPAddress
FROM radacct
WHERE AcctStopTime like '$day%'
GROUP BY UserName,NASIPAddress
) or die $DBI::errstr;
...and the new code (albeit unfinished, as I am just trying to get the
basics of a select down first). Although it runs without error, I have
no indication that I'm doing things properly. When I dump the $rs with
Data::Dumper, I do not see the expected data.
Can someone let me know if I'm on the right track?
my $rs = $schema-resultset( 'Radacct' )-search( undef, {
select = [
'username',
{ count = 'radacctid' },
{ sum = 'acctsessiontime' },
{ max = 'acctsessiontime' },
{ min = 'acctsessiontime' },
{ sum = 'acctinputoctets' },{
{
sum = 'acctoutputoctets' },
'nasipaddress',
],
group_by = [ qw/ username nasipaddress / ],
as = [ 'daily' ],
});
Steve
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Issue converting select/insert from DBI to DBIx::Class
On Tue, Dec 22, 2009 at 09:28, Steve Bertrand st...@ibctech.ca wrote:
Hi all,
I've been working on converting much of my DBI code to DBIx::Class, and
am a tad bit stuck.
This particular issue is one that I've worked on for quite a few hours,
while testing out different combinations I've found in the great number
of docs. I'm at the point now where I could use an experienced set of eyes.
My original code:
my $insert_sth = $dbh-prepare(
INSERT INTO aggregate_daily
( UserName, AcctDate, ConnNum,
ConnTotDuration, ConnMaxDuration, ConnMinDuration,
InputOctets, OutputOctets, NASIPAddress
)
SELECT UserName, '$day', COUNT(*), SUM(AcctSessionTime),
MAX(AcctSessionTime), MIN(AcctSessionTime),
SUM(AcctInputOctets),
SUM(AcctOutputOctets), NASIPAddress
FROM radacct
WHERE AcctStopTime like '$day%'
GROUP BY UserName,NASIPAddress
) or die $DBI::errstr;
...and the new code (albeit unfinished, as I am just trying to get the
basics of a select down first). Although it runs without error, I have
no indication that I'm doing things properly. When I dump the $rs with
Data::Dumper, I do not see the expected data.
Can someone let me know if I'm on the right track?
my $rs = $schema-resultset( 'Radacct' )-search( undef, {
select = [
'username',
{ count = 'radacctid' },
{ sum = 'acctsessiontime' },
{ max = 'acctsessiontime' },
{ min = 'acctsessiontime' },
{ sum = 'acctinputoctets' }, {
{
sum = 'acctoutputoctets' },
'nasipaddress',
],
group_by = [ qw/ username nasipaddress / ],
as = [ 'daily' ],
});
Steve
The first problem is that you're ignore AcctDate in the select and
AcctStopTime in the where clauses. Second is the 'as' element is
unneeded.
Does that help?
--
Thanks,
Rob Kinyon
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
[Dbix-class] for update + foreign key
Hello,
I'm having a bit of a problem and hope you can help me with this.
I have 2 tables, one with the foreign key to another (say):
CREATE TABLE a (
id INTEGER NOT NULL PRIMARY KEY,
data VARCHAR,
b_id INTEGER REFERENCES b(id)
);
CREATE TABLE b (
id INTEGER NOT NULL PRIMARY KEY,
data VARCHAR
);
Then I'm trying to use DBIX (through catalyst) in the following way:
sub new {
my $res =
$db_conn-resultset('a')-search({id=$id},{for='update'})-single;
$self-{_data} = $res;
...
}
sub b {
...
return \%($self-{_data}-b-getcolumns);
}
However when this code is executed the following 2 queries are issued to the
DB:
SELECT * FROM a WHERE id=? FOR UPDATE;
SELECT * FROM b WHERE id=?;
note the absence of 'FOR UPDATE' in the second query.
My question is if there is a way to force 'FOR UPDATE' to the second query,
or have it inherit from the original query?
PS: I'm using postgresql with autocommit=0 if that makes any difference.
Thanks in advance,
Roman
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] for update + foreign key
On Tue, Dec 22, 2009 at 10:37, Roman Tarakanov romantaraka...@gmail.com wrote:
Then I'm trying to use DBIX (through catalyst) in the following way:
DBIC, not DBIX. DBIX is a namespace. DBIC is a project.
sub new {
my $res =
$db_conn-resultset('a')-search({id=$id},{for='update'})-single;
$self-{_data} = $res;
...
}
You're storing the row in $self-{_data}, not the resultset. There's
no way to get from the row back to the resultset that generated it,
nor does that really make sense to be able to do.
sub b {
...
return \%($self-{_data}-b-getcolumns);
}
However when this code is executed the following 2 queries are issued to the
DB:
SELECT * FROM a WHERE id=? FOR UPDATE;
SELECT * FROM b WHERE id=?;
note the absence of 'FOR UPDATE' in the second query.
My question is if there is a way to force 'FOR UPDATE' to the second query,
or have it inherit from the original query?
One solution could be:
sub b {
...
return \%{
$db_conn-resultset('b')-search({id=$self-{_data}-b_id},{for='update'})-single-getcolumns
};
}
Rob
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Issue converting select/insert from DBI to DBIx::Class
Rob Kinyon wrote:
The first problem is that you're ignore AcctDate in the select and
AcctStopTime in the where clauses. Second is the 'as' element is
unneeded.
Thanks Rob, you set me on another tangent, so I now have this working
code which simulates exactly what I had before (which hopefully doesn't
get mangled):
my $daily_fetch_rs = $schema-resultset( 'Radacct' )-search(
{
'username' = 'test3',
'acctstoptime' = { like = $day% },
},{
select = [
'username',
{ count = 'radacctid' },
{ sum = 'acctsessiontime' },
{ max = 'acctsessiontime' },
{ min = 'acctsessiontime' },
{ sum = 'acctinputoctets' },
{ sum = 'acctoutputoctets' },
'nasipaddress',
],
group_by = [ qw/ username nasipaddress / ],
as = [ qw/
UserName
ConnNum
ConnTotDuration
ConnMaxDuration
ConnMinDuration
InputOctets
OutputOctets
NASIPAddress
/,
],
});
$daily_fetch_rs-result_class(
'DBIx::Class::ResultClass::HashRefInflator'
);
my $agg_table = $schema-resultset( 'DailyAgg' );
while ( my $daily_entry = $daily_fetch_rs-next() ) {
$daily_entry-{ AcctDate } = $day;
$agg_table-create( $daily_entry );
}
Does that help?
Yep! Thanks ;)
Steve
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
