--- index.cgi 2009/04/13 22:12:38 5 +++ index.cgi 2009/04/18 23:21:40 24 @@ -9,20 +9,95 @@ 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'; +$table = param('table') || $table; my @columns = param('columns'); @columns = ('*') unless @columns; -my $table = param('table') || 'log'; -my $limit = param('limit') || 1000; +$limit = param('limit') || $limit; my $offset = param('offset') || 0; my @where_parts = param('where_parts'); -print header, q| +my $dbh = DBI->connect( $dsn, $user, '', { RaiseError => 1 } ) || die $DBI::errstr; + +sub where_from_parts { + return unless @_; + my @where_parts = @_; + + warn "# where_from_parts ",dump( @where_parts ); + + my @w; + my @data; + foreach ( @where_parts ) { + my ( $w,$v ) = split(/\?\t/,$_,2); + push @w, "$w ?"; + push @data, $v; + } + unshift @data, ' where ' . join(' and ', @w); + warn "# ",dump( @data ); + return @data; +} + +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 @data = where_from_parts( @where_parts ); + + my $sql = join("\n", + 'select', join(',', @cols), qq{ + from $table + $join + }, shift @data, # extract where + '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( @data ); + $t = time() - $t; + print qq|$t
|, join(qq| | |, @cols), qq| | |
---|---|---|
$n | |, join(qq| | |, @row), qq| |
$sql
|;
+ exit;
+}
+
+print q|
@@ -30,80 +105,14 @@
-
- var tag = e.originalTarget.tagName;
-
- var col_nr = e.originalTarget.cellIndex;
-
- var column = $('table#results th:nth-child(' + ( col_nr + 1 ) + ')').text();
- var where_operator = '=';
- var where_value = window.getSelection().getRangeAt(0).cloneContents().textContent;
- if ( where_value.length == 0 )
- where_value = e.originalTarget.textContent;
- else
- where_value = '%' + where_value + '%';
-
- console.debug('click on ', this, e,
- e.originalTarget,
- column, where_operator, where_value
- );
-
- if ( tag == 'TH' ) {
- console.info('header', column);
- $('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);
- }
-
- $('form#sql').addClass('visible');
- };
-
- $('table#results').bind('mouseup', click_on_cell);
-
- $('#status').bind('click', function() {
- $('form#sql').toggleClass('visible');
- });
-
- console.info('ready');
-});
-
-
|;
-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');
@@ -117,16 +126,9 @@
my $sql = "select $c from $table";
my @data;
-if ( @where_parts ) {
- my @w;
- foreach ( @where_parts ) {
- my ( $w,$v ) = split(/\?\t/,$_,2);
- push @w, "$w ?";
- push @data, $v;
- }
- $sql .= ' where ' . join(' and ', @w);
-}
-
+my @where = where_from_parts( @where_parts );
+$sql .= shift @where;
+push @data, @where;
$sql .= ' group by ' . $group_by if $group_by;
$sql .= ' order by ' . param('order_by') if param('order_by');
@@ -151,54 +153,41 @@
print $sth->rows, qq| rows in $t s|;
-
@columns = @{ $sth->{NAME} } if $#columns == 0 && $columns[0] eq '*';
-print qq|