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';

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

my @columns = param('columns');
@columns = ('*') unless @columns;
my $table = param('table') || 'log';
my $limit = param('limit') || 1000;
my $offset = param('offset') || 0;

my @where_parts = param('where_parts');

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">

$(document).ready( function() {

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

</script>
</head>
<body>
|;

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, param('order_by'), $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 ]);
} else {
        $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>|;


@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|<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|<label for=group_by>group by</label>|
        , textfield( -name => 'group_by' )
        , submit( -name => 'add_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
15	dpavlin	2	require 'config.pl' if -e 'config.pl';
16
17	dpavlin	1	my @columns = param('columns');
18			@columns = ('*') unless @columns;
19			my $table = param('table') \|\| 'log';
20			my $limit = param('limit') \|\| 1000;
21			my $offset = param('offset') \|\| 0;
22
23	dpavlin	2	my @where_parts = param('where_parts');
24
25	dpavlin	1	print header, q\|
26
27			<html>
28			<head>
29	dpavlin	2	<title>SQL Web Session</title>
30	dpavlin	1	<link rel="stylesheet" type="text/css" href="style.css">
31			<!-- http://jqueryjs.googlecode.com/files/jquery-1.3.2.min.js -->
32			<script type="text/javascript" src="jquery-1.3.2.min.js"></script>
33			<script type="text/javascript">
34
35			$(document).ready( function() {
36
37			function click_on_cell(e) {
38
39			var tag = e.originalTarget.tagName;
40
41			var col_nr = e.originalTarget.cellIndex;
42
43			var column = $('table#results th:nth-child(' + ( col_nr + 1 ) + ')').text();
44			var where_operator = '=';
45			var where_value = window.getSelection().getRangeAt(0).cloneContents().textContent;
46			if ( where_value.length == 0 )
47			where_value = e.originalTarget.textContent;
48			else
49			where_value = '%' + where_value + '%';
50
51			console.debug('click on ', this, e,
52			e.originalTarget,
53			column, where_operator, where_value
54			);
55
56			if ( tag == 'TH' ) {
57			console.info('header', column);
58	dpavlin	3	$('form#sql input[name=order_by]').attr('value', where_value + ' desc');
59	dpavlin	1	} else if ( tag = 'TD' ) {
60			console.info('column', column, where_operator, where_value);
61			$('form#sql input[name=where_value]').attr('value', where_value);
62			$('form#sql select[name=where_column]').attr('options').selectedIndex = col_nr;
63	dpavlin	3	$('form#sql input[name=add_group_by]').attr('value', column).css('display','block');
64	dpavlin	1	} else {
65			console.error('unknown click on ', tag, e);
66			}
67
68	dpavlin	2	$('form#sql').addClass('visible');
69	dpavlin	1	};
70
71			$('table#results').bind('mouseup', click_on_cell);
72
73	dpavlin	2	$('#status').bind('click', function() {
74			$('form#sql').toggleClass('visible');
75			});
76
77	dpavlin	1	console.info('ready');
78			});
79
80			</script>
81			</head>
82			<body>
83			\|;
84
85			my $dbh = DBI->connect( $dsn, $user, '', { RaiseError => 1 } ) \|\| die $DBI::errstr;
86
87
88	dpavlin	3	my $group_by = param('add_group_by');
89
90			if ( $group_by ) {
91			@columns = ( "count($group_by)", $group_by );
92			my $g;
93			foreach my $c ( @where_parts, param('order_by'), $group_by ) {
94			$c =~ s/\s.+$//;
95			$g->{$c}++ if length($c) > 0;
96			}
97			$group_by = join( ',', keys %$g );
98			print "# $group_by g = ",dump( $g );
99			param('where_value','');
100			param('group_by', $group_by);
101			param('columns', [ @columns ], [ @columns ]);
102			} else {
103			$group_by = param('group_by');
104			}
105
106
107	dpavlin	1	if ( param('where_operator') && length( param('where_value') ) > 0 ) {
108	dpavlin	2	my $where_value = param('where_value');
109			push @where_parts, param('where_column') . ' ' . param('where_operator') . " ?\t$where_value";
110			param('where_value','');
111	dpavlin	1	}
112
113	dpavlin	3
114	dpavlin	2	my $c = join(',', @columns);
115	dpavlin	1
116	dpavlin	2	my $sql = "select $c from $table";
117			my @data;
118
119			if ( @where_parts ) {
120			my @w;
121			foreach ( @where_parts ) {
122			my ( $w,$v ) = split(/\?\t/,$_,2);
123			push @w, "$w ?";
124			push @data, $v;
125			}
126			$sql .= ' where ' . join(' and ', @w);
127			}
128
129	dpavlin	3
130			$sql .= ' group by ' . $group_by if $group_by;
131	dpavlin	2	$sql .= ' order by ' . param('order_by') if param('order_by');
132			$sql .= ' limit ? offset ?';
133
134			push @data, ( $limit, $offset );
135
136	dpavlin	3	my $sql_html = $sql;
137			{
138			my @d = @data;
139			$sql_html =~ s{\?}{dump( shift @d )}ge;
140			}
141			print qq\|<code id="status">$sql_html<br>\n\r\n\r\|;
142
143	dpavlin	2	my $t = time();
144
145	dpavlin	1	my $sth = $dbh->prepare( $sql );
146
147			$sth->execute( @data );
148
149	dpavlin	2	$t = time() - $t;
150
151	dpavlin	3	print $sth->rows, qq\| rows in $t s</code>\|;
152	dpavlin	2
153
154	dpavlin	3	@columns = @{ $sth->{NAME} } if $#columns == 0 && $columns[0] eq '*';
155	dpavlin	2
156	dpavlin	1	print qq\|<table id="results">\|;
157
158			my $counter = 0;
159			sub table_row {
160			my $cell = shift;
161			my $class = $counter++ % 2 == 0 ? ' class=o' : '';
162			return
163			qq\|<tr $class><$cell>\|
164			. join( qq\|</$cell><$cell>\|, @_ )
165			. qq\|</$cell></tr>\|
166			;
167
168			}
169
170			print table_row( 'th', @columns );
171
172			while ( my @row = $sth->fetchrow_array ) {
173			print table_row( 'td', @row );
174			}
175
176			print
177			qq\|</table>\|
178			, start_form( -id => 'sql' )
179
180			, qq\|<label for=columns>select</label>\|
181	dpavlin	2	, checkbox_group( -name => 'columns', -values => [ @columns ], -defaults => [ @columns ] )
182	dpavlin	1
183			, qq\|<label for=from>from</label>\|
184			, textfield( -name => 'from', -value => $table, -default => 'log' )
185
186			, qq\|<label for=where>where</label>\|
187	dpavlin	2	, checkbox_group( -name => 'where_parts', -values => [ @where_parts ], -defaults => [ @where_parts ] )
188	dpavlin	1	, popup_menu( -name => 'where_column', -values => [ @columns ] ),
189	dpavlin	2	, popup_menu( -name => 'where_operator', -values => [ 'not like', 'like', '!=', '=' ])
190	dpavlin	1	, textfield( -name => 'where_value' )
191
192	dpavlin	3	, qq\|<label for=group_by>group by</label>\|
193			, textfield( -name => 'group_by' )
194			, submit( -name => 'add_group_by' )
195
196	dpavlin	1	, qq\|<label for=order_by>order by</label>\|
197	dpavlin	2	, textfield( -name => 'order_by' )
198	dpavlin	1
199			, qq\|<label for=limit>limit</label>\|
200			, textfield( -name=> 'limit', -default => 1000, -size => 4 )
201
202			, qq\|<label for=offset>offset</label>\|
203			, textfield( -name=> 'offset', -default => 0, -size => 4 )
204
205			, submit( -name => 'execute', -value => 'execute' )
206
207			, end_form
208
209
210			, qq\|</body></html>\|
211			;