--- index.cgi 2009/04/13 17:55:51 2
+++ index.cgi 2009/04/13 22:12:38 5
@@ -55,11 +55,12 @@
if ( tag == 'TH' ) {
console.info('header', column);
- $('form#sql input[name=order_by]').attr('value', where_value);
+ $('form#sql input[name=order_by]').attr('value', where_value + ' desc');
} else if ( tag = 'TD' ) {
console.info('column', column, where_operator, where_value);
$('form#sql input[name=where_value]').attr('value', where_value);
$('form#sql select[name=where_column]').attr('options').selectedIndex = col_nr;
+ $('form#sql input[name=add_group_by]').attr('value', column).css('display','block');
} else {
console.error('unknown click on ', tag, e);
}
@@ -84,12 +85,33 @@
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');
+}
+
+
if ( param('where_operator') && length( param('where_value') ) > 0 ) {
my $where_value = param('where_value');
push @where_parts, param('where_column') . ' ' . param('where_operator') . " ?\t$where_value";
param('where_value','');
}
+
my $c = join(',', @columns);
my $sql = "select $c from $table";
@@ -105,11 +127,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
|;
-@columns = @{ $sth->{NAME} };
+@columns = @{ $sth->{NAME} } if $#columns == 0 && $columns[0] eq '*';
print qq|
\n\r\n\r|;
+
my $t = time();
my $sth = $dbh->prepare( $sql );
@@ -118,13 +149,10 @@
$t = time() - $t;
-my $sql_html = $sql;
-$sql_html =~ s{\?}{dump( shift @data )}ge;
-
-print qq|$sql_html
|;
+print $sth->rows, qq| rows in $t s
|, $sth->rows, qq| rows in $t s