150 lines
5.3 KiB
Perl
150 lines
5.3 KiB
Perl
# ====================================================================
|
|
# Gossamer Threads Module Library - http://gossamer-threads.com/
|
|
#
|
|
# GT::SQL::Monitor
|
|
# Author: Jason Rhinelander
|
|
# CVS Info : 087,071,086,086,085
|
|
# $Id: Monitor.pm,v 1.7 2008/12/05 01:28:49 brewt Exp $
|
|
#
|
|
# Copyright (c) 2004 Gossamer Threads Inc. All Rights Reserved.
|
|
# ====================================================================
|
|
#
|
|
|
|
package GT::SQL::Monitor;
|
|
use strict;
|
|
use vars qw/@EXPORT_OK $CSS/;
|
|
use Carp qw/croak/;
|
|
use GT::CGI qw/:escape/;
|
|
require Exporter;
|
|
@EXPORT_OK = qw/query/;
|
|
|
|
use constant CSS => <<'CSS';
|
|
<style type="text/css">
|
|
.sql_monitor td {
|
|
border-bottom: 1px solid rgb(128, 128, 128);
|
|
border-right: 1px solid rgb(128, 128, 128);
|
|
padding: 2px;
|
|
}
|
|
.sql_monitor th {
|
|
border-bottom: 2px solid rgb(128, 128, 128);
|
|
border-right: 1px solid rgb(128, 128, 128);
|
|
padding: 2px;
|
|
}
|
|
table.sql_monitor {
|
|
border-collapse: collapse;
|
|
border-left: 2px solid rgb(128, 128, 128);
|
|
border-top: 2px solid rgb(128, 128, 128);
|
|
border-bottom: 2px solid rgb(128, 128, 128);
|
|
border-right: 2px solid rgb(128, 128, 128);
|
|
}
|
|
.sql_monitor pre {
|
|
margin-bottom: 0px;
|
|
margin-top: 0px;
|
|
}
|
|
</style>
|
|
CSS
|
|
|
|
|
|
sub query {
|
|
# -----------------------------------------------------------------------------
|
|
# Handles the 'SQL Monitor' function of various Gossamer Threads products.
|
|
# Takes a hash of options:
|
|
# table - any GT::SQL table object
|
|
# style - the style to use - 'tab', 'text' or 'html'; defaults to 'text'
|
|
# html - ('tab' or 'text' mode) whether values should be HTML escaped and the whole thing surrounded by a <pre> tag
|
|
# query - the query to run
|
|
# css - if defined, the value will be used for the CSS in 'html' style; otherwise _css() is used
|
|
# Returned is a hash reference containing:
|
|
# db_prefix - the database prefix currently in use
|
|
# style - the value of the 'style' option
|
|
# query - the query performed
|
|
# rows - the number of rows returned by the query, or possibly the number of rows affected
|
|
# results - a scalar reference to the result of the query, if a SELECT/SHOW/sp_*
|
|
# error - set to 1 if an error occurred
|
|
# error_connect - set to an error message if the database connection failed
|
|
# error_prepare - set to an error message if the prepare failed
|
|
# error_execute - set to an error message if the execute failed
|
|
#
|
|
my %opts = @_;
|
|
|
|
$opts{table} and $opts{query} or croak "query() called without table and/or query options";
|
|
|
|
$opts{table}->connect or return { error => 1, error_connect => $GT::SQL::error };
|
|
|
|
my %ret = (
|
|
db_prefix => $opts{table}->{connect}->{PREFIX},
|
|
style => $opts{style},
|
|
query => $opts{query}
|
|
);
|
|
|
|
my $sth = $opts{table}->prepare($opts{query}) or return { %ret, error => 1, error_prepare => $GT::SQL::error };
|
|
my $rv = $sth->execute or return { %ret, error => 1, error_execute => $GT::SQL::error };
|
|
|
|
my $names = $sth->row_names;
|
|
|
|
$ret{rows} = $sth->rows || 0;
|
|
|
|
if ($opts{query} =~ /^\s*(SELECT|DESCRIBE|SHOW|EXPLAIN|sp_)/i) {
|
|
my $table = '';
|
|
my $data = $sth->fetchall_arrayref;
|
|
if ($opts{style} and $opts{style} eq 'html') {
|
|
$table .= defined $opts{css} ? $opts{css} : CSS;
|
|
$table .= qq|<table class="sql_monitor">\n|;
|
|
$table .= " <tr>\n";
|
|
$table .= join '', map ' <th><pre>' . html_escape($_) . "</pre></th>\n",
|
|
@$names;
|
|
$table .= " </tr>\n";
|
|
for (@$data) {
|
|
$table .= " <tr>\n";
|
|
for (@$_) {
|
|
my $val = html_escape($_);
|
|
$val .= "<br />" unless $val =~ /\S/;
|
|
$table .= qq| <td><pre>$val</pre></td>\n|;
|
|
}
|
|
$table .= " </tr>\n";
|
|
}
|
|
$table .= "</table>";
|
|
}
|
|
elsif ($opts{style} and $opts{style} eq 'tabs') {
|
|
$table = $opts{html} ? '<pre>' : '';
|
|
for (@$data) {
|
|
my @foo = map html_escape($_), @$_;
|
|
$table .= join("\t", $opts{html} ? (map defined $_ ? html_escape($_) : '', @$_) : @$_) . "\n";
|
|
}
|
|
$table .= "</pre>" if $opts{html};
|
|
}
|
|
else { # style = 'text'
|
|
my @max_width = (0) x @$names;
|
|
for ($names, @$data) {
|
|
for my $i (0 .. $#$_) {
|
|
my $width = length $_->[$i];
|
|
$max_width[$i] = $width if $width > $max_width[$i];
|
|
}
|
|
}
|
|
$table = join('+', '', map("-" x ($_ + 2), @max_width), '') . "\n";
|
|
$table .= '|';
|
|
for my $i (0 .. $#$names) {
|
|
$table .= sprintf " %-$max_width[$i]s |", $names->[$i];
|
|
}
|
|
$table .= "\n";
|
|
$table .= join('+', '', map("-" x ($_ + 2), @max_width), '') . "\n";
|
|
for (@$data) {
|
|
$table .= '|';
|
|
for my $i (0 .. $#$names) {
|
|
$table .= sprintf " %-$max_width[$i]s |", $_->[$i];
|
|
}
|
|
$table .= "\n";
|
|
}
|
|
$table .= join('+', '', map("-" x ($_ + 2), @max_width), '') . "\n";
|
|
$table = "<pre>" . html_escape($table) . "</pre>" if $opts{html};
|
|
}
|
|
$ret{results} = \$table;
|
|
}
|
|
else {
|
|
$ret{results} = "Rows affected: $ret{rows}";
|
|
}
|
|
|
|
return \%ret;
|
|
}
|
|
|