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

Hello

Question I"m stumped on here, I can't see any way to represent my database via Class::DBI.

My database holds summaries of unique network transactions, split into monthly, trans per day tables, within yearly schemas. Each row is actually a summary of any identical transactions that happened for that day with a count column

There are a few lookup tables for strings that otherwise might repeat in the transaction record like username -> user id etc. Those lookup tables are also held within the yearly schema for each year 2007.usernames etc. (so yes small amount of repeat data per schema).

I'm writing a cgi to allow queries on these over a date range using about any combination of criteria from the transaction. Ex , date, username, item requested, request method etc

I can't figure out how to represent this via Class::DBI because of the multiple tables holding transaction summaries.

Class::DBI seems to only support something like 

Transactions
------------
One Huge Table

usernames
----------
One Huge Table

When it's
------------
2004.jan_trans_daily
2004.feb_trans_daily
2004.usernames
..
2007.jan_trans_daily
2007.feb_trans_daily
2007.usernames

[download]
etc

Can someone enlighten me if there's a clean way to do this? Or is it better to just manually code all the sql.

thank you in advance
K

Replies are listed 'Best First'.
Re: Class::DBI and data split multiple tables
by perrin (Chancellor) on Mar 20, 2007 at 18:53 UTC
    It's difficult to follow what you're describing here. Are you saying that depending on which date range is being searched, a different table name and schema should be used? If so, load them all up as separate Class::DBI classes and choose which one to run the search against in your CGI. If this isn't what you meant, maybe you could show us an example of the SQL you would write to do this by hand?
[reply]
      >>Are you saying that depending on which date range is being searched, a different table name and schema should be used? thank you for the reply

      Yes exactly, it will most often be multiple tables selected from, not just one. Somtimes it will be multiple years (schemas)

      >>If so, load them all up as separate Class::DBI classes and choose which one to run the search against in your CGI.

      Well they'll likely be searching a range not a singular date. Find transactions from Dec 3 2006 -> jan 3 2007 where this this and this but not this. etc Off the cuff sql like: 

      (select date, conn_id, cust_id from 2007.jan_trans where date in ( '20
+07-1-3' ) and cust_id=3)
 UNION ALL 
(select date, conn_id, cust_id from 2006.dec_trans where date in ( '20
+06-12-3') and cust_id=3 )

      [download]

      And actually more often it will probably be about 1-6 month ranges.

      So that's what I'm struggling to figure out how to do in Class::DBI. I'm trying to avoid all the normal manual sql work etc.

      thanks

[reply]
[d/l]
        You have a couple of options. You could make your code smart enough to call multiple Class::DBI classes and add up the results in perl. Or, you could make your database hide this complexity. Using views would do it, or some kind of partitioning like MySQL 5.1 supports.

        You could also do your own SQL generation, but Class::DBI will be useless to you then. In general, Class::DBI is not good for reporting queries. It's really more useful for manipulating individual database rows.

[reply]
Re: Class::DBI and data split multiple tables
by snoopy (Curate) on Mar 21, 2007 at 02:35 UTC
    One possibility is to encapsulate at the DBI level, say using DBIx::AnyDBD. 
    package NetworkClass;

use base qw(Class::DBI);

use DBIx::AnyDBD;
our $dbh;

sub Db_Main {    
    $dbh ||= DBIx::AnyDBD->connect("dbi:Oracle:network", 
        "user", "pass", {}, "NetWorkDBD");
}

    [download]

    Under the hood you class NetWorkDBD does the serious work; managing your farm of real DB connections.

    Simple sql queries are translated into a series of actual SQL queries. Results are aggregated and returned. 

    package NetWorkDBD;

use SQL::Parser;

sub new {
    my ($dsn, $user, $pass, $atts,$pkg) = @_;


#    ...

}

sub selectrow {
    my $self = shift;
    my $sql = shift;
    my @params = @_;
# ...
}

    [download]
    You then have the option of direct use of the DBI handle and/or adding a Class::DBI layer.
[reply]
[d/l]
[select]
Re: Class::DBI and data split multiple tables
by agianni (Hermit) on Mar 20, 2007 at 19:09 UTC
    If you have the ability to, you could consider putting together a view that combines all of the transaction tables into one and write a Class::DBI class for that view instead of the individual tables. 
    split//,q{john hurl, pest caretaker}and(map{print @_[$_]}(join(q{},map
+{sprintf(qq{%010u},$_)}(2**2*307*4993,5*101*641*5261,7*59*79*36997,13
+*17*71*45131,3**2*67*89*167*181))=~/\d{2}/g));

    [download]
[reply]
[d/l]
      Hmmm I could possibly yes, that might get pretty ugly on the view I'll have to think about it thanks.
[reply]

Back to Seekers of Perl Wisdom