--- trunk/lib/SQLSession/Action/DoSQL.pm	2006/12/03 13:28:48	13
+++ trunk/lib/SQLSession/Action/DoSQL.pm	2007/01/30 14:08:47	84
@@ -12,6 +12,7 @@
 
 use SQLSession::Model::DatabaseCollection;
 use DBI;
+use Time::HiRes qw/time/;
 
 use Data::Dump qw/dump/;
 
@@ -21,11 +22,11 @@
 	param sql_query =>
 		label is 'SQL',
 		render as 'textarea',
-		is mandatory,
 		ajax validates,
-		ajax canonicalizes;
+		ajax canonicalizes,
+		is mandatory;
 	
-	param database =>
+	param on_database =>
 		label is 'Database',
 		render as 'Select',
 #		available are qw( test foo bar );
@@ -34,7 +35,7 @@
 			$dbs->unlimit;
 			[{
 				display_from => 'name',
-				value_from => 'name',
+				value_from => 'id',
 				collection => $dbs,
 			}];
 		};
@@ -61,19 +62,21 @@
 	}
 }
 
-use SQL::ReservedWords;
-
 sub canonicalize_sql_query {
 	my $self = shift;
 	my $sql = shift;
 
-	my @words = SQL::ReservedWords->words;
+	warn "<<<< original SQL:\t$sql";
 
-	my $w = join('|', @words);
+	sub sql_uc {
+		my ($prefix,$sql,$suff) = @_;
+		return uc($sql).$suff if (! $prefix || $prefix eq '');
+		return "\n" . uc($sql) . $suff;
+	}
 
-	warn "canonicalize SQL: $sql";
+	$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;
 
-	$sql =~ s/\b($w)\b/uc($1)/egis;
+	warn ">>>> canonicalize SQL:\n$sql";
 
 	return $sql;
 }
@@ -89,30 +92,34 @@
     
     # Custom action code
 
-	my $database = $self->argument_value('database');
-	warn "database: $database\n";
+	my $on_database = $self->argument_value('on_database') || $self->form_value('on_database');
+	warn "on_database: $on_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 $db = SQLSession::Model::Database->new;
+	$db->load_by_cols( id => $on_database ) ||
+		$self->result->error("Can't find database $on_database");
 
 	my $dbh = DBI->connect( $db->dsn, $db->login, $db->passwd ) ||
 		$self->result->error("Can't connect to " . $db->dsn . " as " . $db->login . "<br/>" . $DBI::errstr) && return 0;
 
+	$dbh->do("SET client_encoding = UTF8") if ($db->dsn =~ /Pg/);
+
 	my $sql = $self->argument_value('sql_query');
 
 	warn "SQL: $sql\n";
 
+	my $t = time();
+
 	my $sth = $dbh->prepare( $sql ) ||
 		$self->result->error( $dbh->errstr() ) && return 0;
 
 	$sth->execute() ||
 		$self->result->error( $sth->errstr() ) && return 0;
 	
-    $self->result->message('Query produced ' . (
+	$self->result->message('Query produced ' . (
 		$sth->rows == 0 ?	'no results' :
 		$sth->rows == 1 ?	'single row' :
-							$sth->rows . ' rows'
+		sprintf("%d rows in %.2fs", $sth->rows, time() - $t)
 	)) if ($sth->rows >= 0);
 
 	$self->result->content( sth => $sth );