--- index.cgi 2009/04/15 15:26:38 13 +++ index.cgi 2009/04/18 14:18:48 22 @@ -9,41 +9,74 @@ use Data::Dump qw/dump/; use Time::HiRes qw/time/; +print qq{Content-type: text/html\r\n\r\n}; + our $dsn = 'DBI:Pg:dbname=syslog'; our $user = 'dpavlin'; +our $table = 'log'; +our $limit = 1000; + +our $group_by_join = { + feed_id => [ 'feeds', 'id', 'title', 'link', 'timestamp' ], +}; require 'config.pl' if -e 'config.pl'; -my $table = param('table') || 'log'; +$table = param('table') || $table; my @columns = param('columns'); @columns = ('*') unless @columns; -my $limit = param('limit') || 1000; +$limit = param('limit') || $limit; my $offset = param('offset') || 0; my @where_parts = param('where_parts'); my $dbh = DBI->connect( $dsn, $user, '', { RaiseError => 1 } ) || die $DBI::errstr; -if ( my $group_by = param('add_group_by') ) { - my $sth = $dbh->prepare(qq{ - select $group_by,count($group_by) +if ( my $group_by = param('lookup_col') ) { + + my @cols = ( $group_by, "count($group_by)" ); + my @group_by = ( $group_by ); + + my $join = ''; + my @join = @{ $group_by_join->{$group_by} } if defined $group_by_join->{$group_by}; + if ( @join ) { + warn "## join ",dump( @join ); + my $join_table = shift @join; + my $col = shift @join; + $join = qq{ join $join_table on $table.$group_by = $join_table.$col }; + my @join_cols = map { $join_table . '.' . $_ } @join; + push @cols, @join_cols; + push @group_by, @join_cols; + } + + my $sql = join("\n", + 'select', join(',', @cols), qq{ from $table - group by $group_by + $join + }, 'group by', join(',', @group_by), qq{ order by count($group_by) desc limit 10 - }); + } + ); + + warn "# join SQL: $sql\n"; + + my $t = time(); + my $sth = $dbh->prepare( $sql ); $sth->execute; - print header, qq||; + $t = time() - $t; + print qq|$t
count$group_by
|; while ( my @row = $sth->fetchrow_array ) { - my ( $n, $c ) = @row; + my $n = shift @row; $n = 'NULL' unless defined $n; - print qq||; + print qq||; } print qq|
|, join(qq||, @cols), qq|
$c$n
$n|, join(qq||, @row), qq|
|; + print qq|$sql|; exit; } -print header, q| +print q| @@ -106,6 +139,14 @@ print $sth->rows, qq| rows in $t s|; +#my @types = map { scalar $dbh->type_info($_)->{TYPE_NAME} } @{ $sth->{TYPE} }; +my $types = dump( $sth->{TYPE} ); +print qq{ + +}; + @columns = @{ $sth->{NAME} } if $#columns == 0 && $columns[0] eq '*'; print qq||; @@ -130,15 +171,16 @@ print qq|
| - , start_form( -id => 'sql' ) + , start_form( -id => 'sql', -class => 'fixed' ) - , qq|[x]| + , qq|| + , qq|| , qq|| , checkbox_group( -name => 'columns', -values => [ @columns ], -defaults => [ @columns ] ) , qq|| - , textfield( -name => 'from', -value => $table, -default => 'log' ) + , textfield( -name => 'from', -value => $table, -default => $table ) , qq|| , checkbox_group( -name => 'where_parts', -values => [ @where_parts ], -defaults => [ @where_parts ] ) @@ -147,7 +189,7 @@ , textfield( -name => 'where_value' ) , qq| - +
@@ -160,7 +202,7 @@ , textfield( -name => 'order_by' ) , qq|| - , textfield( -name=> 'limit', -default => 1000, -size => 4 ) + , textfield( -name=> 'limit', -default => $limit, -size => 4 ) , qq|| , textfield( -name=> 'offset', -default => 0, -size => 4 )