--- index.cgi 2009/04/15 15:26:38 13
+++ index.cgi 2009/04/18 14:05:13 20
@@ -11,35 +11,66 @@
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|
count | $group_by |
---|
|;
+ $t = time() - $t;
+ print header, qq|$t|, join(qq| | |, @cols), qq| |
|;
while ( my @row = $sth->fetchrow_array ) {
- my ( $n, $c ) = @row;
+ my $n = shift @row;
$n = 'NULL' unless defined $n;
- print qq|$c | $n |
|;
+ print qq|$n | |, join(qq| | |, @row), qq| |
|;
}
print qq|
|;
+ print qq|$sql
|;
exit;
}
@@ -106,6 +137,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||;
@@ -132,13 +171,13 @@
qq|
|
, start_form( -id => 'sql' )
- , qq|[x]|
+ , 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 +186,7 @@
, textfield( -name => 'where_value' )
, qq|
-
+
@@ -160,7 +199,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 )