/[SQLSession]/trunk/lib/SQLSession/Action/DoSQL.pm
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Contents of /trunk/lib/SQLSession/Action/DoSQL.pm

Parent Directory Parent Directory | Revision Log Revision Log


Revision 28 - (show annotations)
Thu Dec 7 19:56:41 2006 UTC (17 years, 4 months ago) by dpavlin
File size: 2383 byte(s)
dodana biljeĆĄka uz SQL query, malo CSS-a
1 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 use DBI;
15
16 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 render as 'textarea',
24 ajax validates,
25 ajax canonicalizes,
26 is mandatory;
27
28 param database =>
29 label is 'Database',
30 render as 'Select',
31 # available are qw( test foo bar );
32 available are defer {
33 my $dbs = SQLSession::Model::DatabaseCollection->new;
34 $dbs->unlimit;
35 [{
36 display_from => 'name',
37 value_from => 'id',
38 collection => $dbs,
39 }];
40 };
41
42 };
43
44 sub sticky_on_success { 1 }
45 sub sticky_on_failure { 1 }
46
47 =head2 validate_sql_query
48
49 Can't be empty!
50
51 =cut
52
53 sub validate_sql_query {
54 my $self = shift;
55 my $value = shift;
56
57 if ( $value =~ m/^\s+;*\s*$/s ) {
58 return $self->validation_error( sql_query => 'You need to type in SQL query' );
59 } else {
60 return $self->validation_ok('sql_query');
61 }
62 }
63
64 use SQL::ReservedWords;
65
66 sub canonicalize_sql_query {
67 my $self = shift;
68 my $sql = shift;
69
70 my @words = SQL::ReservedWords->words;
71
72 my $w = join('|', @words);
73
74 warn "original SQL:\t$sql";
75
76 $sql =~ s/\b($w)\b/uc($1)/egis;
77
78 warn "canonicalize SQL:\t$sql";
79
80 return $sql;
81 }
82
83 =head2 take_action
84
85 Execute SQL query on database
86
87 =cut
88
89 sub take_action {
90 my $self = shift;
91
92 # Custom action code
93
94 my $database = $self->argument_value('database');
95 warn "database: $database\n";
96
97 my $db = SQLSession::Model::Database->new;
98 $db->load_by_cols( id => $database ) ||
99 $self->result->error("Can't find database $database");
100
101 my $dbh = DBI->connect( $db->dsn, $db->login, $db->passwd ) ||
102 $self->result->error("Can't connect to " . $db->dsn . " as " . $db->login . "<br/>" . $DBI::errstr) && return 0;
103
104 $dbh->do("SET client_encoding = UTF8");
105
106 my $sql = $self->argument_value('sql_query');
107
108 warn "SQL: $sql\n";
109
110 my $sth = $dbh->prepare( $sql ) ||
111 $self->result->error( $dbh->errstr() ) && return 0;
112
113 $sth->execute() ||
114 $self->result->error( $sth->errstr() ) && return 0;
115
116 $self->result->message('Query produced ' . (
117 $sth->rows == 0 ? 'no results' :
118 $sth->rows == 1 ? 'single row' :
119 $sth->rows . ' rows'
120 )) if ($sth->rows >= 0);
121
122 $self->result->content( sth => $sth );
123
124 return 1;
125 }
126
127 1;
128

  ViewVC Help
Powered by ViewVC 1.1.26