--- index.cgi 2009/04/18 23:14:41 23
+++ index.cgi 2009/12/08 20:08:29 32
@@ -12,9 +12,11 @@
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' ],
@@ -30,7 +32,7 @@
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 @_;
@@ -50,6 +52,13 @@
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)" );
@@ -76,7 +85,7 @@
}, shift @data, # extract where
'group by', join(',', @group_by), qq{
order by count($group_by) desc
- limit 10
+ limit $limit
}
);
@@ -86,21 +95,23 @@
my $sth = $dbh->prepare( $sql );
$sth->execute( @data );
$t = time() - $t;
- print qq|$t
|, join(qq| | |, @cols), qq| |
|;
+ 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|$n | |, join(qq| | |, @row), qq| |
|;
}
print qq|
|;
- print qq|$sql
|;
+ print $sth->rows, qq| rows in $t s|;
exit;
}
print q|
-
+
+
SQL Web Session
@@ -136,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();
@@ -153,39 +159,10 @@
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><$cell>|, @_ )
- . qq|$cell>
|
- ;
-
-}
-
-print table_row( 'th', @columns );
-
-while ( my @row = $sth->fetchrow_array ) {
- print table_row( 'td', @row );
-}
-
print
- qq|
|
- , start_form( -id => 'sql', -class => 'fixed' )
+ start_form( -id => 'sql', -class => 'fixed' )
, qq||
, qq||
@@ -206,7 +183,7 @@
-
+
|
, qq||
@@ -224,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><$cell>|, @_ )
+ . qq|$cell>
|
+ ;
+
+}
+
+print table_row( 'th', @columns );
+
+while ( my @row = $sth->fetchrow_array ) {
+ print table_row( 'td', @row );
+}
+print qq|
|
, qq||
;