--- index.cgi 2009/04/13 17:55:51 2 +++ index.cgi 2009/04/15 15:26:38 13 @@ -14,14 +14,35 @@ require 'config.pl' if -e 'config.pl'; +my $table = param('table') || 'log'; 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,59 +51,14 @@ - - console.info('ready'); -}); - - |; -my $dbh = DBI->connect( $dsn, $user, '', { RaiseError => 1 } ) || die $DBI::errstr; - +my $group_by = param('group_by'); if ( param('where_operator') && length( param('where_value') ) > 0 ) { my $where_value = param('where_value'); @@ -90,6 +66,7 @@ param('where_value',''); } + my $c = join(',', @columns); my $sql = "select $c from $table"; @@ -105,11 +82,20 @@ $sql .= ' where ' . join(' and ', @w); } + +$sql .= ' group by ' . $group_by if $group_by; $sql .= ' order by ' . param('order_by') if param('order_by'); $sql .= ' limit ? offset ?'; push @data, ( $limit, $offset ); +my $sql_html = $sql; +{ + my @d = @data; + $sql_html =~ s{\?}{dump( shift @d )}ge; +} +print qq|

$sql_html
\n\r\n\r|;
+
 my $t = time();
 
 my $sth = $dbh->prepare( $sql );
@@ -118,13 +104,9 @@
 
 $t = time() - $t;
 
-my $sql_html = $sql;
-$sql_html =~ s{\?}{dump( shift @data )}ge;
-
-print qq|$sql_html
|, $sth->rows, qq| rows in $t s|;
+print $sth->rows, qq| rows in $t s

|; - -@columns = @{ $sth->{NAME} }; +@columns = @{ $sth->{NAME} } if $#columns == 0 && $columns[0] eq '*'; print qq||; @@ -150,6 +132,8 @@ qq|

| , start_form( -id => 'sql' ) + , qq|[x]| + , qq|select| , checkbox_group( -name => 'columns', -values => [ @columns ], -defaults => [ @columns ] ) @@ -161,6 +145,16 @@ , popup_menu( -name => 'where_column', -values => [ @columns ] ), , popup_menu( -name => 'where_operator', -values => [ 'not like', 'like', '!=', '=' ]) , textfield( -name => 'where_value' ) + , qq| + + + +