Class::DBI and Query with JOIN'S + Paging support

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks, I am currently porting a webapp to the wonderfull Catalyst MVC framework. I also decided to port the original (very clumsy) DBI code to use Class::DBI/Class::DBI::Sweet instead, i like it very much, get the job done and fast ! My problem is the following, this particular application use a old schema that was not designed by me and that i cannot change. One of the function require me to use a very large query with multiples LEFT JOIN:

select
  event.sid, event.cid, event.timestamp, signature.sig_name, iphdr.ip_
+src, iphdr.ip_dst,
  icmphdr.icmp_type, icmphdr.icmp_code, icmphdr.icmp_csum, icmphdr.icm
+p_id,
 icmphdr.icmp_seq,
  udphdr.udp_sport, udphdr.udp_dport, udphdr.udp_len, udphdr.udp_csum,
  tcphdr.tcp_sport, tcphdr.tcp_dport, tcphdr.tcp_seq, tcphdr.tcp_ack, 
+tcphdr.tcp_off,
  tcphdr.tcp_res, tcphdr.tcp_flags, tcphdr.tcp_win, tcphdr.tcp_csum, t
+cphdr.tcp_urp,
  sensor.hostname, sensor.interface, data.data_payload
from event
  where event.sid = 1
  left join signature on signature.sig_id = event.signature
  left join iphdr on iphdr.sid = event.sid and iphdr.cid = event.cid
  left join icmphdr on icmphdr.sid = event.sid and icmphdr.cid = event
+.cid
  left join udphdr on udphdr.sid = event.sid and udphdr.cid = event.ci
+d
  left join tcphdr on tcphdr.sid = event.sid and tcphdr.cid = event.ci
+d
  left join sensor on sensor.sid = event.sid
  left join data on data.sid = event.sid and data.cid = event.cid
order by
  timestamp desc, event.sid desc, event.cid desc
[download]

the WHERE part come from a form and can have numerous options set by the user. I used to do this with straight DBI + regex +placeholders and it was a pain in the ass to manage. Now i would like to do it in Class::DBI instead. I came across the following recipe on the CDBI wiki: http://wiki.class-dbi.com/index.cgi?UsingJoins http://wiki.class-dbi.com/index.cgi?DirectlyExecuteSql I undertand i can do this with SQL::Abstract (SQL::Abstract::Limit in my particular case since i need paging). Anyone can give me some tips, advices, best practices ? I am also wondering how can i integrate Data::Page to work with this query ? Is that possible ? Thanks a lot ! PS: Pardon for my not-so-perfect english

Edit: g0n - added code tags, linkified links

Comment on Class::DBI and Query with JOIN'S + Paging support Download Code

Replies are listed 'Best First'.
Re: Class::DBI and Query with JOIN'S + Paging support by pajout (Curate) on Oct 04, 2005 at 17:26 UTC
I use DBI with PostgreSQL for some years. I think, that your question is too general. My experience is shrinkable to following points: 1/ Imagine your final sql queries. And resolve, what should be in perl and what in db (as view, for instance). Observe db-related performance optimization dependencies. 2/ Think about dynamic and static part of queries 3/ Develop own helper functions for building queries on the fly - parameters binding is strongly recommended. 4/ Think about how many rows is the real maximum of query. 5/ If the result of query is too long, think about user interface - how to list. 6/ Play with sql parts limiting result rows (offset and limit in PostgreSQL) and with pulling datas from db (1 row, n rows, all rows) into perl and, potentially, with attributes of $sth - but be aware, some of them are db dependent. Typically, I use offset, but not limit in sql-string. But I do not pull all rows from db, only actually needed, and something as $sth->{ROWS} says me count of all rows. Thats all, I am sorry for that so general answer and my english, too :)	[reply]
Re: Class::DBI and Query with JOIN'S + Paging support by snoopy (Curate) on Oct 05, 2005 at 02:15 UTC
At a guess all the left joins are there just to pull in related data for use by the application. If so, these relationships can be setup via Class::DBI, greatly simplifying this query: 1. The cdbi construct method is very nifty for refactoring existing SQL queries. The code might then look something like: `my $query = 'select event.* from event where sid = 1 order by +timestamp desc, event.sid desc, event.cid desc'; my $sth = MyClass::db_Main->prepare( $query ); $sth->execute; my @events; # # Fetch and construct each object # while (my $row = $sth->fetchrow_hashref()) { push(@events, MyClass::Event->construct($row)); } # # Process these objects # foreach my $event (@events) { my $signature = $event->signature; print "Signature Name=".$signature->sig_name."\n"; ## whatever .... }` [download] 2. You may want to use the setup_table method from Class::DBI::Xx. (eg Class::DBI::Pg for postgres) to load column definitions and primary keys from the database. This will ensure that your your columns definitions match the database and SQL wildcards (select event.) can safely be used in conjuction with Class::DBI::construct: Read more... (1088 Bytes) 3. By "Paging Support"* Do you mean display only the nth screen-full of data? If so, the SQL offset and limit clauses are your friends: `my $page_size = 24; my $page_num = 3; my $offset = ($page_num - 1) * $page_size; my $query = "select * from event where sid = 1 order by timestamp desc +, event.sid desc, event.cid desc OFFSET $offset LIMIT $page_size";` [download]	[reply] [d/l] [select]