<HTML >
<HEAD>
<META http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Exchange Server">
<!-- converted from rtf -->
<style><!-- .EmailQuote { margin-left: 1pt; padding-left: 4pt; border-left: #800000 2px solid; } --></style>
</HEAD>
<BODY >
<DIV>
<font face="Arial, sans-serif" size="2">
<div>Hi Matt,</div>
<div> </div>
<div>Thanks for the response, however neither solutions are really satisfactory.</div>
<div> </div>
<div>I'll give you a summary of what ive been doing and how I've tackled it. </div>
<div> </div>
<div><font face="Arial, sans-serif">Essentially Ive been<font face="Arial, sans-serif"> creating a reporting framework to allow users to create reports on various datasets held in sqlite databases. </font></font></div>
<div>The idea is that users can create reports on the raw data or based on existing reports.</div>
<div> </div>
<div> </div>
<div>So <font face="Arial, sans-serif">the base reporting module looks as follows, </font></div>
<div><font face="Arial, sans-serif">this is then subclassed by a base report for each db table</font></div>
<div><font face="Arial, sans-serif">Which in turn is subclassed by user created reports.</font></div>
<div>----------------------------------------<font face="Arial, sans-serif">-----------------------------</font></div>
<div>package Reporting::View;</div>
<div>use Moose::role;</div>
<div> </div>
<div>has 'schema' => ( is => 'rw',lazy=>1, default => sub {</div>
<div> my $self = shift;</div>
<div> return Reporting::DB->connect('dbi:SQLite:'.$self->dbSource);</div>
<div>});</div>
<div> </div>
<div><font face="Arial, sans-serif">#</font></div>
<div><font face="Arial, sans-serif"># default resultset, reports can manipulate</font></div>
<div><font face="Arial, sans-serif">#</font></div>
<div>has 'resultset' => ( is => 'rw',lazy=>1, default => sub {</div>
<div> my $self = shift;</div>
<div> return $self->schema->resultset($self->dbTable);</div>
<div>});</div>
<div> </div>
<div><font face="Arial, sans-serif">#</font></div>
<div><font face="Arial, sans-serif"># post processes the resultset</font></div>
<div><font face="Arial, sans-serif">#</font></div>
<div>has 'processedResultset' => ( is =>'rw', lazy=>1, default => sub {</div>
<div> my $self = shift;</div>
<div> my $rs = $self->resultset;</div>
<div> </div>
<div><font face="Arial, sans-serif"> # apply search /paging</font></div>
<div><font face="Arial, sans-serif"> # build whereclaus from cgi params</font></div>
<div><font face="Arial, sans-serif"> my $whereClause = map { ... } $self->cgi->params;</font></div>
<div> $rs = $rs-><b>subselect</b>({ <font face="Arial, sans-serif">$</font>where<font face="Arial, sans-serif">C</font>laus }.. { paging..} ); # search on resultset</div>
<div> </div>
<div><font face="Arial, sans-serif"> #</font></div>
<div><font face="Arial, sans-serif"> # add comments to report </font></div>
<div><font face="Arial, sans-serif"> $self->schema->storage->dbh->do(qq/ATTACH DATABASE "comments.db" as comments/);</font></div>
<div> $rs->result_class->might_have( comments => 'Reporting::Comments', { join cla<font face="Arial, sans-serif">u</font>s }); </div>
<div> $rs = $rs-><b>subselect</b>({ comments_fields}, { join => comments }.. ); # get comments for the report</div>
<div> .. Other post processing that may add/remove columns<font face="Arial, sans-serif"> like delta between dates..</font></div>
<div> return $rs;</div>
<div>});</div>
<div> </div>
<div> </div>
<div><font face="Arial, sans-serif">#</font></div>
<div># called by Template Toolkit</div>
<div>#</div>
<div>sub createReport {</div>
<div> my $rs $self->processedResultset;</div>
<div> my @cols = $rs-><b>columns</b><font face="Arial, sans-serif"><b>();</b></font></div>
<div> </div>
<div> my @rows;</div>
<div> while (my @row = $rs->cursor->next) {</div>
<div> push @rows, \@row;</div>
<div> }</div>
<div> # return a table object that can be rendered into html</div>
<div> return Table->new(\@cols, \@rows); </div>
<div>}</div>
<div> </div>
<div> </div>
<div>=================================================</div>
<div>A bas<font face="Arial, sans-serif">e</font> report that displays raw data from Foo.db </div>
<div>------------------------------------------------</div>
<div>package Reporting::View::Foo;</div>
<div> </div>
<div>use Moose;</div>
<div>with Reporting::View;</div>
<div> </div>
<div>sub dbSource { 'Foo' };</div>
<div>sub dbTable { 'FooTable' };</div>
<div>sub reportName {' Raw foo report' };</div>
<div> </div>
<div> </div>
<div>=================================================</div>
<div>A sub report that displays only some data from Raw report</div>
<div>------------------------------------------------</div>
<div> </div>
<div>package Reporting::View::Foo<font face="Arial, sans-serif">::View1;</font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div>use Moose;</div>
<div>extends Reporting::View::Foo<font face="Arial, sans-serif">;</font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div>sub reportName {' sub report of foo'};</div>
<div> </div>
<div>around 'resultset' => sub { </div>
<div> my ($next,$self) = @_;</div>
<div> $self->$next</div>
<div> ->search( { col1 => { in =>[]} }, { select => [ col<font face="Arial, sans-serif">1 as xxx col2 as yyy</font> ] });</div>
<div>}</div>
<div> </div>
<div> </div>
<div>=================================================</div>
<div>A sub report that <font face="Arial, sans-serif">displays View1 using customised col names</font></div>
<div>------------------------------------------------</div>
<div> </div>
<div>package Reporting::View::Foo::<font face="Arial, sans-serif">Raw::View1::Custom</font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div>use Moose;</div>
<div>extends <font face="Arial, sans-serif">'</font>Reporting::View::Foo<font face="Arial, sans-serif">::Raw::View1';</font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div>sub reportName {' sub report of foo'};</div>
<div> </div>
<div>around 'resultset' => sub { </div>
<div> my ($next,$self) = @_;</div>
<div> $self->$next</div>
<div> -><b>subselect</b>( { <font face="Arial, sans-serif">xxx</font> => { in =>[<font face="Arial, sans-serif">..</font>]} }, { select => [ <font face="Arial, sans-serif">'</font>col<font face="Arial, sans-serif">1 as foo', </font> <font face="Arial, sans-serif">'col2
as bar'</font>] });</div>
<div>}</div>
<div> </div>
<div> </div>
<div>=================================================</div>
<div>A sub report that displays <font face="Arial, sans-serif">customer report in a different layout</font></div>
<div>------------------------------------------------<font face="Arial, sans-serif">----------------------</font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div>package Reporting::View::Foo::Raw<font face="Arial, sans-serif">::View1::Custom::Alternate</font>;</div>
<div> </div>
<div>use Moose;</div>
<div>extends <font face="Arial, sans-serif">'</font>Reporting::View::Foo<font face="Arial, sans-serif">::Raw::View1::Custom';</font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div>sub reportName {' sub report of foo'};</div>
<div> </div>
<div>around 'resultset' => sub { </div>
<div> my ($next,$self) = @_;</div>
<div> $self->$next</div>
<div> -><font face="Arial, sans-serif"><b>roundMins(</b></font><font face="Arial, sans-serif">30, 'time', @groupbycols</font>}<font face="Arial, sans-serif"> #adjusts time value to 30 min intervals</font></div>
<div><font face="Arial, sans-serif"> -><b>groupBy</b>('count(*)','count', @groupbycols} #group by time col to show counts by 30 min interval</font></div>
<div><font face="Arial, sans-serif"> -><b>pivot</b>('time','0','count,@groupbycols; #turn the time col into multiple columns</font></div>
<div><font face="Arial, sans-serif">}</font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div><font face="Arial, sans-serif">----------------------------------------------------</font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div><font face="Arial, sans-serif">The essense of this is to have a db that looks like </font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div><font face="Arial, sans-serif">| Col1 | Col2 | Time |</font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div><font face="Arial, sans-serif">And from it, create reports that looks like </font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div><font face="Arial, sans-serif">| Col1 | Col2 | Time | Count |</font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div><font face="Arial, sans-serif">And then something like this.</font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div><font face="Arial, sans-serif">| myCol1 | myCol2 | 00:00 | 00:30 | 01:00 | … | 23:30 | </font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div><font face="Arial, sans-serif">All the user needs to do to create the report is create a module with relavent around 'resultset' method - later this may become a yaml file.</font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div><font face="Arial, sans-serif">-------------------------------------------------------</font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div><font face="Arial, sans-serif">Ive managed to get all this working - but ive had to hack the following</font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div><font face="Arial, sans-serif">DBIx::Class::Storage::DBI </font></div>
<div><font face="Arial, sans-serif">- created a <b>selectSQL()</b> method that returns the current SQL string and</font></div>
<div><font face="Arial, sans-serif">- allows passing of '<b>compound' </b>key for doing Unions/Excepts etc.</font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div><font face="Arial, sans-serif">DBIx::Class::Resultset </font></div>
<div><font face="Arial, sans-serif">- created <b>subselect()</b> that uses the selectSQL() above to populate the 'from' key.</font></div>
<div><font face="Arial, sans-serif">- added <b>'compound'</b> key to search so that you can supply compound => 'UNION => selectstatement'';</font></div>
<div><font face="Arial, sans-serif">- added <b>columns() </b>to retrieve the columns from the current resultset.</font></div>
<div><font face="Arial, sans-serif">- added <b>groupBy(), pivot()</b> and a couple of other helper methods that do useful SQL transformations</font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div><font face="Arial, sans-serif">------------------------------------------------------------</font></div>
<div><font face="Arial, sans-serif">It all ends up with some rather scary SQL - but it works rather nicely ;-)</font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div><font face="Arial, sans-serif" size="3">S<font face="Arial, sans-serif">ELECT "only_in", "db_server", "project", "00:00", "01:00", "02:00", "03:30", "04:00", "04:30", "05:00", "05:30", "06:00", "06:30", "07:00", "17:30", "19:00", "21:00", "21:30", "22:30"
FROM (SELECT "only_in", "db_server", "project", sum("00:00") as "00:00", sum("01:00") as "01:00", sum("02:00") as "02:00", sum("03:30") as "03:30", sum("04:00") as "04:00", sum("04:30") as "04:30", sum("05:00") as "05:00", sum("05:30") as "05:30", sum("06:00")
as "06:00", sum("06:30") as "06:30", sum("07:00") as "07:00", sum("17:30") as "17:30", sum("19:00") as "19:00", sum("21:00") as "21:00", sum("21:30") as "21:30", sum("22:30") as "22:30" FROM (SELECT "only_in", "db_server", "project", (case when time = "00:00"
then count end) as '00:00', (case when time = "01:00" then count end) as '01:00', (case when time = "02:00" then count end) as '02:00', (case when time = "03:30" then count end) as '03:30', (case when time = "04:00" then count end) as '04:00', (case when time
= "04:30" then count end) as '04:30', (case when time = "05:00" then count end) as '05:00', (case when time = "05:30" then count end) as '05:30', (case when time = "06:00" then count end) as '06:00', (case when time = "06:30" then count end) as '06:30', (case
when time = "07:00" then count end) as '07:00', (case when time = "17:30" then count end) as '17:30', (case when time = "19:00" then count end) as '19:00', (case when time = "21:00" then count end) as '21:00', (case when time = "21:30" then count end) as '21:30',
(case when time = "22:30" then count end) as '22:30' FROM (SELECT "only_in", "db_server", "project", "time", count(*) as count FROM (SELECT "only_in", "db_server", "project", "time" FROM (SELECT '2008-11-13' as only_in, "db_server", "project", "time" FROM (SELECT
"db_server", "project", "time" FROM (SELECT db_server, project, strftime("%H:%M", strftime("%H:00",time), "+" || (strftime("%M",time)*1/30*30) || " minutes") as time FROM DBusage me WHERE ( strftime('%H',time) IS NOT NULL ) GROUP BY db_server, project, time)
me EXCEPT SELECT db_server, project, strftime("%H:%M", strftime("%H:00",time), "+" || (strftime("%M",time)*1/30*30) || " minutes") as time FROM old.DBusage me WHERE ( strftime('%H',time) IS NOT NULL ) GROUP BY db_server, project, time) me) me UNION SELECT '2008-11-11'
as only_in, "db_server", "project", "time" FROM (SELECT "db_server", "project", "time" FROM (SELECT db_server, project, strftime("%H:%M", strftime("%H:00",time), "+" || (strftime("%M",time)*1/30*30) || " minutes") as time FROM old.DBusage me WHERE ( strftime('%H',time)
IS NOT NULL ) GROUP BY db_server, project, time) me EXCEPT SELECT db_server, project, strftime("%H:%M", strftime("%H:00",time), "+" || (strftime("%M",time)*1/30*30) || " minutes") as time FROM DBusage me WHERE ( strftime('%H',time) IS NOT NULL ) GROUP BY db_server,
project, time) me) me GROUP BY db_server, project, time) me) me GROUP BY db_server, project) me WHERE ( ( ( ( ( db_server LIKE '%CDDB%' ) OR ( project LIKE '%CDDB%' ) ) ) ) ) LIMIT 100 </font></font></div>
<div><font size="3"> </font></div>
<div><font face="Arial, sans-serif">-----------------------------------------------------------</font></div>
<div><font face="Arial, sans-serif">Since our version 0.8 is broken (no useful errors), ive done this using 0.70050. </font></div>
<div><font face="Arial, sans-serif">My code is pretty hacky so not worth submitting but If you are interested in what I did I can provide more info. </font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div><font face="Arial, sans-serif">Regards, Tom</font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div><font face="Arial, sans-serif"> </font></div>
<div> </div>
<div> </div>
<div>> -----Original Message-----</div>
<div>> From: Matt S Trout [<a href="mailto:dbix-class@trout.me.uk"><font color="#0000FF"><u>mailto:dbix-class@trout.me.uk</u></font></a>] </div>
<div>> Sent: 12 November 2008 20:40</div>
<div>> To: DBIx::Class user and developer list</div>
<div>> Subject: Re: [Dbix-class] DBIx::Class - Trying to get column </div>
<div>> names from a $rs or $row</div>
<div>> </div>
<div>> On Tue, Oct 14, 2008 at 07:59:44PM +0100, Howe, Tom (IT) wrote:</div>
<div>> > Hi, first post here</div>
<div>> ></div>
<div>> > I have a question regarding getting column names from a </div>
<div>> DBIx ::Class $resultset or $row.</div>
<div>> ></div>
<div>> > I have a DBIx::Class resultset which I'll call $rset</div>
<div>> ></div>
<div>> > I need to get the column names of the resultset in the </div>
<div>> correct order.</div>
<div>> ></div>
<div>> > I was advised by a coleague to use $rset->result_source->columns;</div>
<div>> ></div>
<div>> > This have me the column names of the table that was the </div>
<div>> original source of the resultset which initially worked ok.</div>
<div>> ></div>
<div>> > However, now that I am doing some selects on the table the </div>
<div>> resultset </div>
<div>> > only contains a subset of the table columns</div>
<div>> ></div>
<div>> > I tried doing</div>
<div>> ></div>
<div>> > my $row = $rset->first;</div>
<div>> > %row = $row->get_columns;</div>
<div>> </div>
<div>> my @cols = grep { exists $row{$_} } $rset->result_source->columns;</div>
<div>> </div>
<div>> or, really, if you're supplying a subset of columns, just </div>
<div>> hang onto the array you supplied for that.</div>
<div>> </div>
<div>> > my @cols = keys %row;</div>
<div>> ></div>
<div>> > But now I dont have them in the correct order.</div>
<div>> ></div>
<div>> > Is there anyway to get an (ordered) list of the column </div>
<div>> names from the </div>
<div>> > $resultset or the $row</div>
<div>> </div>
<div>> --</div>
<div>> Matt S Trout Need help with your Catalyst or </div>
<div>> DBIx::Class project?</div>
<div>> Technical Director </div>
<div>> <a href="http://www.shadowcat.co.uk/catalyst/"><font color="#0000FF"><u>http://www.shadowcat.co.uk/catalyst/</u></font></a></div>
<div>> Shadowcat Systems Ltd. Want a managed development or </div>
<div>> deployment platform?</div>
<div>> <a href="http://chainsawblues.vox.com/"><font color="#0000FF"><u>http://chainsawblues.vox.com/</u></font></a> </div>
<div>> <a href="http://www.shadowcat.co.uk/servers/"><font color="#0000FF"><u>http://www.shadowcat.co.uk/servers/</u></font></a></div>
<div>> </div>
<div>> _______________________________________________</div>
<div>> List: <a href="http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class"><font color="#0000FF"><u>http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class</u></font></a></div>
<div>> IRC: irc.perl.org#dbix-class</div>
<div>> SVN: <a href="http://dev.catalyst.perl.org/repos/bast/DBIx-Class/"><font color="#0000FF"><u>http://dev.catalyst.perl.org/repos/bast/DBIx-Class/</u></font></a></div>
<div>> Searchable Archive: </div>
<div>> <a href="http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk"><font color="#0000FF"><u>http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk</u></font></a></div>
<div>> </div>
<div> </div>
</font>
</DIV>
<DIV>
<HR>
</DIV>
<P CLASS="BulletedList" STYLE="MARGIN: 0in 0in 0pt; TEXT-INDENT: 0in; mso-list: none; tab-stops: .5in"><SPAN STYLE="FONT-SIZE: 8pt; COLOR: gray; mso-bidi-font-family: Arial"><FONT FACE="Arial" COLOR="gray" SIZE="1">NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error.</FONT></SPAN></P>
<DIV>
</DIV></BODY></HTML>