--- index.cgi 2009/04/13 22:12:38 5 +++ index.cgi 2009/04/18 12:08:07 17 @@ -11,17 +11,39 @@ our $dsn = 'DBI:Pg:dbname=syslog'; our $user = 'dpavlin'; +our $table = 'log'; require 'config.pl' if -e 'config.pl'; +$table = param('table') || $table; my @columns = param('columns'); @columns = ('*') unless @columns; -my $table = param('table') || 'log'; my $limit = param('limit') || 1000; 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) + from $table + group by $group_by + order by count($group_by) desc + limit 10 + }); + $sth->execute; + print header, qq||; + while ( my @row = $sth->fetchrow_array ) { + my ( $n, $c ) = @row; + $n = 'NULL' unless defined $n; + print qq||; + } + print qq|
count$group_by
$c$n
|; + exit; +} + print header, q| @@ -30,80 +52,14 @@ - - |; -my $dbh = DBI->connect( $dsn, $user, '', { RaiseError => 1 } ) || die $DBI::errstr; - - -my $group_by = param('add_group_by'); - -if ( $group_by ) { - @columns = ( "count($group_by)", $group_by ); - my $g; - foreach my $c ( @where_parts, $group_by ) { - $c =~ s/\s.+$//; - $g->{$c}++ if length($c) > 0; - } - $group_by = join( ',', keys %$g ); - print "# $group_by g = ",dump( $g ); - param('where_value',''); - param('group_by', $group_by); - param('columns', [ @columns ], [ @columns ]); - param('order_by', $columns[0] . ' desc'); -} else { - $group_by = param('group_by'); -} - +my $group_by = param('group_by'); if ( param('where_operator') && length( param('where_value') ) > 0 ) { my $where_value = param('where_value'); @@ -151,6 +107,13 @@ 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 '*'; @@ -178,6 +141,8 @@ qq|| , start_form( -id => 'sql' ) + , qq|[x]| + , qq|| , checkbox_group( -name => 'columns', -values => [ @columns ], -defaults => [ @columns ] ) @@ -189,10 +154,16 @@ , popup_menu( -name => 'where_column', -values => [ @columns ] ), , popup_menu( -name => 'where_operator', -values => [ 'not like', 'like', '!=', '=' ]) , textfield( -name => 'where_value' ) + , qq| + + + +
+ + | , qq|| , textfield( -name => 'group_by' ) - , submit( -name => 'add_group_by' ) , qq|| , textfield( -name => 'order_by' )