--- index.cgi 2009/04/14 12:43:07 6 +++ index.cgi 2009/04/18 23:54:30 26 @@ -9,20 +9,96 @@ 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 $limit + } + ); + + warn "# join SQL: $sql\n"; + + my $t = time(); + my $sth = $dbh->prepare( $sql ); + $sth->execute( @data ); + $t = time() - $t; + print qq|$sql|; + print qq||; + while ( my @row = $sth->fetchrow_array ) { + my $n = shift @row; + $n = 'NULL' unless defined $n; + print qq||; + } + print qq|
|, join(qq||, @cols), qq|
$n|, join(qq||, @row), qq|
|; + print $sth->rows, qq| rows in $t s|; + exit; +} + +print q| @@ -30,80 +106,14 @@ - - function click_on_cell(e) { - - 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 +127,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,56 +154,41 @@ print $sth->rows, qq| rows in $t s|; - @columns = @{ $sth->{NAME} } if $#columns == 0 && $columns[0] eq '*'; -print qq||; - -my $counter = 0; -sub table_row { - my $cell = shift; - my $class = $counter++ % 2 == 0 ? ' class=o' : ''; - return - qq|<$cell>| - . join( qq|<$cell>|, @_ ) - . qq|| - ; - -} - -print table_row( 'th', @columns ); - -while ( my @row = $sth->fetchrow_array ) { - print table_row( 'td', @row ); -} - print - qq|
| - , start_form( -id => 'sql' ) + start_form( -id => 'sql', -class => 'fixed' ) - , qq|close| + , qq|| + , 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 ] ) , 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' ) , qq|| - , textfield( -name=> 'limit', -default => 1000, -size => 4 ) + , textfield( -name=> 'limit', -default => $limit, -size => 4 ) , qq|| , textfield( -name=> 'offset', -default => 0, -size => 4 ) @@ -208,7 +196,36 @@ , submit( -name => 'execute', -value => 'execute' ) , end_form + ; +#my @types = map { scalar $dbh->type_info($_)->{TYPE_NAME} } @{ $sth->{TYPE} }; +my $types = dump( $sth->{TYPE} ); +print qq{ + +}; + +print qq||; + +my $counter = 0; +sub table_row { + my $cell = shift; + my $class = $counter++ % 2 == 0 ? ' class=o' : ''; + return + qq|<$cell>| + . join( qq|<$cell>|, @_ ) + . qq|| + ; + +} + +print table_row( 'th', @columns ); + +while ( my @row = $sth->fetchrow_array ) { + print table_row( 'td', @row ); +} +print qq|
| , qq|| ;