/[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

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

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

revision 28 by dpavlin, Thu Dec 7 19:56:41 2006 UTC revision 82 by dpavlin, Mon Jan 29 21:43:06 2007 UTC
# Line 12  use base qw/SQLSession::Action Jifty::Ac Line 12  use base qw/SQLSession::Action Jifty::Ac
12    
13  use SQLSession::Model::DatabaseCollection;  use SQLSession::Model::DatabaseCollection;
14  use DBI;  use DBI;
15    use Time::HiRes qw/time/;
16    
17  use Data::Dump qw/dump/;  use Data::Dump qw/dump/;
18    
# Line 61  sub validate_sql_query { Line 62  sub validate_sql_query {
62          }          }
63  }  }
64    
 use SQL::ReservedWords;  
   
65  sub canonicalize_sql_query {  sub canonicalize_sql_query {
66          my $self = shift;          my $self = shift;
67          my $sql = shift;          my $sql = shift;
68    
69          my @words = SQL::ReservedWords->words;          warn "<<<< original SQL:\t$sql";
   
         my $w = join('|', @words);  
70    
71          warn "original SQL:\t$sql";          sub sql_uc {
72                    my ($prefix,$sql,$suff) = @_;
73                    return uc($sql).$suff if (! $prefix || $prefix eq '');
74                    return "\n" . uc($sql) . $suff;
75            }
76    
77          $sql =~ s/\b($w)\b/uc($1)/egis;          $sql =~ s/(\s*)(select|from|inner\s+join|order\s+by|where|group\s+by|having|limit|offset)(\s+)/sql_uc($1,$2,$3)/egis;
78    
79          warn "canonicalize SQL:\t$sql";          warn ">>>> canonicalize SQL:\n$sql";
80    
81          return $sql;          return $sql;
82  }  }
# Line 91  sub take_action { Line 92  sub take_action {
92            
93      # Custom action code      # Custom action code
94    
95          my $database = $self->argument_value('database');          my $database = $self->argument_value('database') || $self->form_value('database');
96          warn "database: $database\n";          warn "database: $database\n";
97    
98          my $db = SQLSession::Model::Database->new;          my $db = SQLSession::Model::Database->new;
# Line 101  sub take_action { Line 102  sub take_action {
102          my $dbh = DBI->connect( $db->dsn, $db->login, $db->passwd ) ||          my $dbh = DBI->connect( $db->dsn, $db->login, $db->passwd ) ||
103                  $self->result->error("Can't connect to " . $db->dsn . " as " . $db->login . "<br/>" . $DBI::errstr) && return 0;                  $self->result->error("Can't connect to " . $db->dsn . " as " . $db->login . "<br/>" . $DBI::errstr) && return 0;
104    
105          $dbh->do("SET client_encoding = UTF8");          $dbh->do("SET client_encoding = UTF8") if ($db->dsn =~ /Pg/);
106    
107          my $sql = $self->argument_value('sql_query');          my $sql = $self->argument_value('sql_query');
108    
109          warn "SQL: $sql\n";          warn "SQL: $sql\n";
110    
111            my $t = time();
112    
113          my $sth = $dbh->prepare( $sql ) ||          my $sth = $dbh->prepare( $sql ) ||
114                  $self->result->error( $dbh->errstr() ) && return 0;                  $self->result->error( $dbh->errstr() ) && return 0;
115    
116          $sth->execute() ||          $sth->execute() ||
117                  $self->result->error( $sth->errstr() ) && return 0;                  $self->result->error( $sth->errstr() ) && return 0;
118                    
119      $self->result->message('Query produced ' . (          $self->result->message('Query produced ' . (
120                  $sth->rows == 0 ?       'no results' :                  $sth->rows == 0 ?       'no results' :
121                  $sth->rows == 1 ?       'single row' :                  $sth->rows == 1 ?       'single row' :
122                                                          $sth->rows . ' rows'                  sprintf("%d rows in %.2fs", $sth->rows, time() - $t)
123          )) if ($sth->rows >= 0);          )) if ($sth->rows >= 0);
124    
125          $self->result->content( sth => $sth );          $self->result->content( sth => $sth );
Legend:
Removed from v.28  
changed lines
  Added in v.82
  ViewVC Help
Powered by ViewVC 1.1.26