SQLSession/Action/DoSQL.pm

use strict;
use warnings;

=head1 NAME

SQLSession::Action::DoSQL

=cut

package SQLSession::Action::DoSQL;
use base qw/SQLSession::Action Jifty::Action/;

use SQLSession::Model::DatabaseCollection;
use DBI;

use Data::Dump qw/dump/;

use Jifty::Param::Schema;
use Jifty::Action schema {

        param sql_query =>
                label is 'SQL',
                render as 'textarea',
                is mandatory,
                ajax validates;
        
        param database =>
                label is 'Database',
                render as 'Select',
#               available are qw( test foo bar );
                available are defer {
                        my $dbs = SQLSession::Model::DatabaseCollection->new;
                        $dbs->unlimit;
                        [{
                                display_from => 'name',
                                value_from => 'name',
                                collection => $dbs,
                        }];
                };

};

sub sticky_on_success { 1 }
sub sticky_on_failure { 1 }

=head2 validate_sql_query

Can't be empty!

=cut

sub valdate_sql_query {
        my $self = shift;
        my $value = shift || return $self->validation_error( sql_query => 'You need to type in SQL query' );
        return $self->validation_ok('sql_query');
}

=head2 take_action

Execute SQL query on database

=cut

sub take_action {
    my $self = shift;
    
    # Custom action code

        my $database = $self->argument_value('database');
        warn "database: $database\n";

        my $dbs = SQLSession::Model::DatabaseCollection->new;
        $dbs->limit( column => 'name', value => $database );
        my $db = $dbs->first || $self->result->error("Can't find database $database");

        my $dbh = DBI->connect( $db->dsn, $db->login, $db->passwd ) ||
                $self->result->error("can't connect to ", $db->dsn, " as ", $db->login, " ", $DBI::errstr);

        my $sql = $self->argument_value('sql_query');

        warn "SQL: $sql\n";

        my $sth = $dbh->prepare( $sql ) ||
                $self->result->error( $dbh->errstr() );

        $sth->execute() ||
                $self->result->error( $sth->errstr() );
        
    $self->result->message('Query produced ' . (
                $sth->rows == 0 ?       'no results' :
                $sth->rows == 1 ?       'single row' :
                                                        $sth->rows . ' rows'
        )) if ($sth->rows >= 0);

    return 1;
}

1;

1	dpavlin	5	use strict;
2			use warnings;
3
4			=head1 NAME
5
6			SQLSession::Action::DoSQL
7
8			=cut
9
10			package SQLSession::Action::DoSQL;
11			use base qw/SQLSession::Action Jifty::Action/;
12
13			use SQLSession::Model::DatabaseCollection;
14	dpavlin	7	use DBI;
15
16	dpavlin	5	use Data::Dump qw/dump/;
17
18			use Jifty::Param::Schema;
19			use Jifty::Action schema {
20
21			param sql_query =>
22			label is 'SQL',
23	dpavlin	7	render as 'textarea',
24			is mandatory,
25			ajax validates;
26	dpavlin	5
27			param database =>
28			label is 'Database',
29			render as 'Select',
30			# available are qw( test foo bar );
31			available are defer {
32			my $dbs = SQLSession::Model::DatabaseCollection->new;
33			$dbs->unlimit;
34	dpavlin	6	[{
35	dpavlin	5	display_from => 'name',
36			value_from => 'name',
37			collection => $dbs,
38			}];
39			};
40
41			};
42
43	dpavlin	7	sub sticky_on_success { 1 }
44			sub sticky_on_failure { 1 }
45
46			=head2 validate_sql_query
47
48			Can't be empty!
49
50			=cut
51
52			sub valdate_sql_query {
53			my $self = shift;
54			my $value = shift \|\| return $self->validation_error( sql_query => 'You need to type in SQL query' );
55			return $self->validation_ok('sql_query');
56			}
57
58	dpavlin	5	=head2 take_action
59
60	dpavlin	7	Execute SQL query on database
61
62	dpavlin	5	=cut
63
64			sub take_action {
65			my $self = shift;
66
67			# Custom action code
68
69	dpavlin	7	my $database = $self->argument_value('database');
70			warn "database: $database\n";
71	dpavlin	5
72	dpavlin	7	my $dbs = SQLSession::Model::DatabaseCollection->new;
73			$dbs->limit( column => 'name', value => $database );
74			my $db = $dbs->first \|\| $self->result->error("Can't find database $database");
75	dpavlin	5
76	dpavlin	7	my $dbh = DBI->connect( $db->dsn, $db->login, $db->passwd ) \|\|
77			$self->result->error("can't connect to ", $db->dsn, " as ", $db->login, " ", $DBI::errstr);
78
79			my $sql = $self->argument_value('sql_query');
80
81			warn "SQL: $sql\n";
82
83			my $sth = $dbh->prepare( $sql ) \|\|
84			$self->result->error( $dbh->errstr() );
85
86			$sth->execute() \|\|
87			$self->result->error( $sth->errstr() );
88
89			$self->result->message('Query produced ' . (
90			$sth->rows == 0 ? 'no results' :
91			$sth->rows == 1 ? 'single row' :
92			$sth->rows . ' rows'
93			)) if ($sth->rows >= 0);
94
95			return 1;
96	dpavlin	5	}
97
98			1;
99