--- index.cgi 2009/04/18 13:55:20 19 +++ index.cgi 2009/12/08 20:08:29 32 @@ -9,10 +9,14 @@ 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 $database = ''; # if not in $dsn our $user = 'dpavlin'; our $table = 'log'; our $limit = 1000; +our $passwd = ''; our $group_by_join = { feed_id => [ 'feeds', 'id', 'title', 'link', 'timestamp' ], @@ -28,9 +32,34 @@ my @where_parts = param('where_parts'); -my $dbh = DBI->connect( $dsn, $user, '', { RaiseError => 1 } ) || die $DBI::errstr; +my $dbh = DBI->connect( $dsn . $database, $user, $passwd, { RaiseError => 1 } ) || die $DBI::errstr; + +sub where_from_parts { + return unless @_; + my @where_parts = @_; -if ( my $group_by = param('add_group_by') ) { + 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; +} + +sub sql_html { + my @d = @_; + my $sql_html = shift @d; + $sql_html =~ s{\?}{dump( shift @d )}ge; + return $sql_html; +} + +if ( my $group_by = param('lookup_col') ) { my @cols = ( $group_by, "count($group_by)" ); my @group_by = ( $group_by ); @@ -47,13 +76,16 @@ push @group_by, @join_cols; } + my @data = where_from_parts( @where_parts ); + my $sql = join("\n", 'select', join(',', @cols), qq{ from $table $join - }, 'group by', join(',', @group_by), qq{ + }, shift @data, # extract where + 'group by', join(',', @group_by), qq{ order by count($group_by) desc - limit 10 + limit $limit } ); @@ -61,23 +93,25 @@ my $t = time(); my $sth = $dbh->prepare( $sql ); - $sth->execute; + $sth->execute( @data ); $t = time() - $t; - print header, qq|$t|; + print qq||, sql_html( $sql, @data ), qq||; + print qq|
|, join(qq||, @cols), 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 qq|$sql|; + print $sth->rows, qq| rows in $t s|; exit; } -print header, q| - +print q| + + SQL Web Session @@ -103,16 +137,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'); @@ -120,12 +147,7 @@ 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|; +print qq||, sql_html( $sql, @data ), qq|
\n|; my $t = time(); @@ -137,41 +159,13 @@ 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||; - -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|[x]| + , qq|| + , qq|| , qq|| , checkbox_group( -name => 'columns', -values => [ @columns ], -defaults => [ @columns ] ) @@ -186,10 +180,10 @@ , textfield( -name => 'where_value' ) , qq| - +
- + | , qq|| @@ -207,7 +201,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|| ;