viewvc.cgi/sql-web-session/index.cgi

#!/usr/bin/perl

use warnings;
use strict;

use CGI qw/:standard/;
use CGI::Carp qw/fatalsToBrowser/; # FIXME remove for production
use DBI;
use Data::Dump qw/dump/;
use Time::HiRes qw/time/;

our $dsn    = 'DBI:Pg:dbname=syslog';
our $user   = 'dpavlin';
our $table  = 'log';
our $limit  = 1000;

require 'config.pl' if -e 'config.pl';

$table  = param('table') || $table;
my @columns = param('columns');
@columns = ('*') unless @columns;
$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)
                from $table
                group by $group_by
                order by count($group_by) desc
                limit 10
        });
        $sth->execute;
        print header, qq|<table><tr><th>count</th><th>$group_by</th><tr>|;
        while ( my @row = $sth->fetchrow_array ) {
                my ( $n, $c ) = @row;
                $n = 'NULL' unless defined $n;
                print qq|<tr><td>$c</td><td><a href="#">$n</a></td></tr>|;
        }
        print qq|</table>|;
        exit;
}

print header, q|

<html>
<head>
<title>SQL Web Session</title>
<link rel="stylesheet" type="text/css" href="style.css">
<!-- http://jqueryjs.googlecode.com/files/jquery-1.3.2.min.js -->
<script type="text/javascript" src="jquery-1.3.2.min.js"></script>

<script type="text/javascript" src="sql-editor.js"></script>

</head>
<body>
|;

my $group_by = param('group_by');

if ( param('where_operator') && length( param('where_value') ) > 0 ) {
        my $where_value = param('where_value');
        push @where_parts, param('where_column') . ' ' . param('where_operator') . " ?\t$where_value";
        param('where_value','');
}


my $c = join(',', @columns);

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);
}


$sql .= ' group by ' . $group_by if $group_by;
$sql .= ' order by ' . param('order_by') if param('order_by');
$sql .= ' limit ? offset ?';

push @data, ( $limit, $offset );

my $sql_html = $sql;
{
        my @d = @data;
        $sql_html =~ s{\?}{dump( shift @d )}ge;
}
print qq|<code id="status">$sql_html<br>\n\r\n\r|;

my $t = time();

my $sth = $dbh->prepare( $sql );

$sth->execute( @data );

$t = time() - $t;

print $sth->rows, qq| rows in $t s</code>|;

#my @types = map { scalar $dbh->type_info($_)->{TYPE_NAME} } @{ $sth->{TYPE} };
my $types = dump( $sth->{TYPE} );
print qq{
<script type="text/javascript">
var column_type = $types ;
</script>
};

@columns = @{ $sth->{NAME} } if $#columns == 0 && $columns[0] eq '*';

print qq|<table id="results">|;

my $counter = 0;
sub table_row {
        my $cell = shift;
        my $class = $counter++ % 2 == 0 ? ' class=o' : '';
        return
                  qq|<tr $class><$cell>|
                . join( qq|</$cell><$cell>|, @_ )
                . qq|</$cell></tr>|
                ;

}

print table_row( 'th', @columns );

while ( my @row = $sth->fetchrow_array ) {
        print table_row( 'td', @row );
}

print
        qq|</table>|
        , start_form( -id => 'sql' )

        , qq|<a href="#" onclick="\$('form#sql').toggleClass('visible'); return false;" class=close title="hide sql editor">[x]</a>|

        , qq|<label for=columns>select</label>|
        , checkbox_group( -name => 'columns', -values => [ @columns ], -defaults => [ @columns ] )

        , qq|<label for=from>from</label>|
        , textfield( -name => 'from', -value => $table, -default => 'log' )

        , qq|<label for=where>where</label>|
        , 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|
                <span>
                <input type=button name=add_group_by>
                <input type=button name=close_group_by value="[x]" disabled=1>
                <div id="lookup"></div>
                <span>
        |

        , qq|<label for=group_by>group by</label>|
        , textfield( -name => 'group_by' )

        , qq|<label for=order_by>order by</label>|
        , textfield( -name => 'order_by' )

        , qq|<label for=limit>limit</label>|
        , textfield( -name=> 'limit', -default => 1000, -size => 4 )

        , qq|<label for=offset>offset</label>|
        , textfield( -name=> 'offset', -default => 0, -size => 4 )

        , submit( -name => 'execute', -value => 'execute' )

        , end_form


        , qq|</body></html>|
        ;
1	dpavlin	1	#!/usr/bin/perl
2
3			use warnings;
4			use strict;
5
6			use CGI qw/:standard/;
7			use CGI::Carp qw/fatalsToBrowser/; # FIXME remove for production
8			use DBI;
9			use Data::Dump qw/dump/;
10	dpavlin	2	use Time::HiRes qw/time/;
11	dpavlin	1
12			our $dsn = 'DBI:Pg:dbname=syslog';
13			our $user = 'dpavlin';
14	dpavlin	17	our $table = 'log';
15	dpavlin	18	our $limit = 1000;
16	dpavlin	1
17	dpavlin	2	require 'config.pl' if -e 'config.pl';
18
19	dpavlin	17	$table = param('table') \|\| $table;
20	dpavlin	1	my @columns = param('columns');
21			@columns = ('*') unless @columns;
22	dpavlin	18	$limit = param('limit') \|\| $limit;
23	dpavlin	1	my $offset = param('offset') \|\| 0;
24
25	dpavlin	2	my @where_parts = param('where_parts');
26
27	dpavlin	10	my $dbh = DBI->connect( $dsn, $user, '', { RaiseError => 1 } ) \|\| die $DBI::errstr;
28
29			if ( my $group_by = param('add_group_by') ) {
30			my $sth = $dbh->prepare(qq{
31			select $group_by,count($group_by)
32			from $table
33			group by $group_by
34			order by count($group_by) desc
35			limit 10
36			});
37			$sth->execute;
38			print header, qq\|<table><tr><th>count</th><th>$group_by</th><tr>\|;
39			while ( my @row = $sth->fetchrow_array ) {
40			my ( $n, $c ) = @row;
41			$n = 'NULL' unless defined $n;
42	dpavlin	15	print qq\|<tr><td>$c</td><td><a href="#">$n</a></td></tr>\|;
43	dpavlin	10	}
44			print qq\|</table>\|;
45			exit;
46			}
47
48	dpavlin	1	print header, q\|
49
50			<html>
51			<head>
52	dpavlin	2	<title>SQL Web Session</title>
53	dpavlin	1	<link rel="stylesheet" type="text/css" href="style.css">
54			<!-- http://jqueryjs.googlecode.com/files/jquery-1.3.2.min.js -->
55			<script type="text/javascript" src="jquery-1.3.2.min.js"></script>
56	dpavlin	7
57	dpavlin	10	<script type="text/javascript" src="sql-editor.js"></script>
58	dpavlin	7
59	dpavlin	1	</head>
60			<body>
61			\|;
62
63	dpavlin	10	my $group_by = param('group_by');
64	dpavlin	1
65			if ( param('where_operator') && length( param('where_value') ) > 0 ) {
66	dpavlin	2	my $where_value = param('where_value');
67			push @where_parts, param('where_column') . ' ' . param('where_operator') . " ?\t$where_value";
68			param('where_value','');
69	dpavlin	1	}
70
71	dpavlin	3
72	dpavlin	2	my $c = join(',', @columns);
73	dpavlin	1
74	dpavlin	2	my $sql = "select $c from $table";
75			my @data;
76
77			if ( @where_parts ) {
78			my @w;
79			foreach ( @where_parts ) {
80			my ( $w,$v ) = split(/\?\t/,$_,2);
81			push @w, "$w ?";
82			push @data, $v;
83			}
84			$sql .= ' where ' . join(' and ', @w);
85			}
86
87	dpavlin	3
88			$sql .= ' group by ' . $group_by if $group_by;
89	dpavlin	2	$sql .= ' order by ' . param('order_by') if param('order_by');
90			$sql .= ' limit ? offset ?';
91
92			push @data, ( $limit, $offset );
93
94	dpavlin	3	my $sql_html = $sql;
95			{
96			my @d = @data;
97			$sql_html =~ s{\?}{dump( shift @d )}ge;
98			}
99			print qq\|<code id="status">$sql_html<br>\n\r\n\r\|;
100
101	dpavlin	2	my $t = time();
102
103	dpavlin	1	my $sth = $dbh->prepare( $sql );
104
105			$sth->execute( @data );
106
107	dpavlin	2	$t = time() - $t;
108
109	dpavlin	3	print $sth->rows, qq\| rows in $t s</code>\|;
110	dpavlin	2
111	dpavlin	14	#my @types = map { scalar $dbh->type_info($_)->{TYPE_NAME} } @{ $sth->{TYPE} };
112			my $types = dump( $sth->{TYPE} );
113			print qq{
114			<script type="text/javascript">
115			var column_type = $types ;
116			</script>
117			};
118
119	dpavlin	3	@columns = @{ $sth->{NAME} } if $#columns == 0 && $columns[0] eq '*';
120	dpavlin	2
121	dpavlin	1	print qq\|<table id="results">\|;
122
123			my $counter = 0;
124			sub table_row {
125			my $cell = shift;
126			my $class = $counter++ % 2 == 0 ? ' class=o' : '';
127			return
128			qq\|<tr $class><$cell>\|
129			. join( qq\|</$cell><$cell>\|, @_ )
130			. qq\|</$cell></tr>\|
131			;
132
133			}
134
135			print table_row( 'th', @columns );
136
137			while ( my @row = $sth->fetchrow_array ) {
138			print table_row( 'td', @row );
139			}
140
141			print
142			qq\|</table>\|
143			, start_form( -id => 'sql' )
144
145	dpavlin	12	, qq\|<a href="#" onclick="\$('form#sql').toggleClass('visible'); return false;" class=close title="hide sql editor">[x]</a>\|
146	dpavlin	6
147	dpavlin	1	, qq\|<label for=columns>select</label>\|
148	dpavlin	2	, checkbox_group( -name => 'columns', -values => [ @columns ], -defaults => [ @columns ] )
149	dpavlin	1
150			, qq\|<label for=from>from</label>\|
151			, textfield( -name => 'from', -value => $table, -default => 'log' )
152
153			, qq\|<label for=where>where</label>\|
154	dpavlin	2	, checkbox_group( -name => 'where_parts', -values => [ @where_parts ], -defaults => [ @where_parts ] )
155	dpavlin	1	, popup_menu( -name => 'where_column', -values => [ @columns ] ),
156	dpavlin	2	, popup_menu( -name => 'where_operator', -values => [ 'not like', 'like', '!=', '=' ])
157	dpavlin	1	, textfield( -name => 'where_value' )
158	dpavlin	10	, qq\|
159			<span>
160			<input type=button name=add_group_by>
161			<input type=button name=close_group_by value="[x]" disabled=1>
162	dpavlin	12	<div id="lookup"></div>
163	dpavlin	10	<span>
164			\|
165	dpavlin	1
166	dpavlin	3	, qq\|<label for=group_by>group by</label>\|
167			, textfield( -name => 'group_by' )
168
169	dpavlin	1	, qq\|<label for=order_by>order by</label>\|
170	dpavlin	2	, textfield( -name => 'order_by' )
171	dpavlin	1
172			, qq\|<label for=limit>limit</label>\|
173			, textfield( -name=> 'limit', -default => 1000, -size => 4 )
174
175			, qq\|<label for=offset>offset</label>\|
176			, textfield( -name=> 'offset', -default => 0, -size => 4 )
177
178			, submit( -name => 'execute', -value => 'execute' )
179
180			, end_form
181
182
183			, qq\|</body></html>\|
184			;