| 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 |
|
| 11 |
our $dsn = 'DBI:Pg:dbname=syslog'; |
| 12 |
our $user = 'dpavlin'; |
| 13 |
|
| 14 |
my @columns = param('columns'); |
| 15 |
@columns = ('*') unless @columns; |
| 16 |
my $table = param('table') || 'log'; |
| 17 |
my $order_by = param('order_by') || 'timestamp desc'; |
| 18 |
my $limit = param('limit') || 1000; |
| 19 |
my $offset = param('offset') || 0; |
| 20 |
|
| 21 |
print header, q| |
| 22 |
|
| 23 |
<html> |
| 24 |
<head> |
| 25 |
<title>SQL Session</title> |
| 26 |
<link rel="stylesheet" type="text/css" href="style.css"> |
| 27 |
<!-- http://jqueryjs.googlecode.com/files/jquery-1.3.2.min.js --> |
| 28 |
<script type="text/javascript" src="jquery-1.3.2.min.js"></script> |
| 29 |
<script type="text/javascript"> |
| 30 |
|
| 31 |
$(document).ready( function() { |
| 32 |
|
| 33 |
function click_on_cell(e) { |
| 34 |
|
| 35 |
var tag = e.originalTarget.tagName; |
| 36 |
|
| 37 |
var col_nr = e.originalTarget.cellIndex; |
| 38 |
|
| 39 |
var column = $('table#results th:nth-child(' + ( col_nr + 1 ) + ')').text(); |
| 40 |
var where_operator = '='; |
| 41 |
var where_value = window.getSelection().getRangeAt(0).cloneContents().textContent; |
| 42 |
if ( where_value.length == 0 ) |
| 43 |
where_value = e.originalTarget.textContent; |
| 44 |
else |
| 45 |
where_value = '%' + where_value + '%'; |
| 46 |
|
| 47 |
console.debug('click on ', this, e, |
| 48 |
e.originalTarget, |
| 49 |
column, where_operator, where_value |
| 50 |
); |
| 51 |
|
| 52 |
if ( tag == 'TH' ) { |
| 53 |
console.info('header', column); |
| 54 |
} else if ( tag = 'TD' ) { |
| 55 |
console.info('column', column, where_operator, where_value); |
| 56 |
$('form#sql input[name=where_value]').attr('value', where_value); |
| 57 |
$('form#sql select[name=where_column]').attr('options').selectedIndex = col_nr; |
| 58 |
} else { |
| 59 |
console.error('unknown click on ', tag, e); |
| 60 |
} |
| 61 |
|
| 62 |
}; |
| 63 |
|
| 64 |
$('table#results').bind('mouseup', click_on_cell); |
| 65 |
|
| 66 |
console.info('ready'); |
| 67 |
}); |
| 68 |
|
| 69 |
</script> |
| 70 |
</head> |
| 71 |
<body> |
| 72 |
|; |
| 73 |
|
| 74 |
my $dbh = DBI->connect( $dsn, $user, '', { RaiseError => 1 } ) || die $DBI::errstr; |
| 75 |
|
| 76 |
my @data = ( $limit, $offset ); |
| 77 |
|
| 78 |
my $c = join(',', @columns); |
| 79 |
my $sql = "select $c from $table "; |
| 80 |
if ( param('where_operator') && length( param('where_value') ) > 0 ) { |
| 81 |
$sql .= " where " . param('where_column') . ' ' . param('where_operator') . ' ?'; |
| 82 |
unshift @data, param('where_value'); |
| 83 |
} |
| 84 |
$sql .= " limit ? offset ?"; |
| 85 |
|
| 86 |
print qq|<code>$sql<br>|, dump( @data ), qq|</code>|; |
| 87 |
|
| 88 |
my $sth = $dbh->prepare( $sql ); |
| 89 |
|
| 90 |
$sth->execute( @data ); |
| 91 |
|
| 92 |
@columns = @{ $sth->{NAME} }; |
| 93 |
|
| 94 |
print qq|<table id="results">|; |
| 95 |
|
| 96 |
my $counter = 0; |
| 97 |
sub table_row { |
| 98 |
my $cell = shift; |
| 99 |
my $class = $counter++ % 2 == 0 ? ' class=o' : ''; |
| 100 |
return |
| 101 |
qq|<tr $class><$cell>| |
| 102 |
. join( qq|</$cell><$cell>|, @_ ) |
| 103 |
. qq|</$cell></tr>| |
| 104 |
; |
| 105 |
|
| 106 |
} |
| 107 |
|
| 108 |
print table_row( 'th', @columns ); |
| 109 |
|
| 110 |
while ( my @row = $sth->fetchrow_array ) { |
| 111 |
print table_row( 'td', @row ); |
| 112 |
} |
| 113 |
|
| 114 |
print |
| 115 |
qq|</table>| |
| 116 |
, start_form( -id => 'sql' ) |
| 117 |
|
| 118 |
, qq|<label for=columns>select</label>| |
| 119 |
, checkbox_group( -name => 'columns', -values => [ @columns ], -default => [ @columns ] ) |
| 120 |
|
| 121 |
, qq|<label for=from>from</label>| |
| 122 |
, textfield( -name => 'from', -value => $table, -default => 'log' ) |
| 123 |
|
| 124 |
, qq|<label for=where>where</label>| |
| 125 |
, popup_menu( -name => 'where_column', -values => [ @columns ] ), |
| 126 |
, popup_menu( -name => 'where_operator', -values => [ 'like', 'not like', '=', '!=' ]) |
| 127 |
, textfield( -name => 'where_value' ) |
| 128 |
|
| 129 |
, qq|<label for=order_by>order by</label>| |
| 130 |
, textfield( -name => 'order_by' -value => $order_by ) |
| 131 |
|
| 132 |
, qq|<label for=limit>limit</label>| |
| 133 |
, textfield( -name=> 'limit', -default => 1000, -size => 4 ) |
| 134 |
|
| 135 |
, qq|<label for=offset>offset</label>| |
| 136 |
, textfield( -name=> 'offset', -default => 0, -size => 4 ) |
| 137 |
|
| 138 |
, submit( -name => 'execute', -value => 'execute' ) |
| 139 |
|
| 140 |
, end_form |
| 141 |
|
| 142 |
|
| 143 |
, qq|</body></html>| |
| 144 |
; |
Parent Directory
| 
Revision Log