Template::Plugin::JavaSQL - Help generate Java from database schemas.

	<sql:
		query="select foo as f, bar as b from some_table"
	>

	or

	<sql:
		table="some_table"
	>

	[% USE Java %]
	[% Use JavaSQL %]
	...
	String query =
	"select [% JavaSQL.columnNames.join(", ") %] from [% JavaSQL.tables.join(", ") %]";
	[% IF JavaSQL.where %]
	query += " where [% JavaSQL.where %] ";
	[% ELSE %]
	query += " where 0=0 ";
	[% END %]
	[% FOREACH JavaSQL.columns %]
	if (${varName}Set) { query += " and $name = ?"; }
	[% END %]
	[% IF JavaSQL.order %]
	query += "order by [% JavaSQL.order %]";
	[% END %]
	stmt = myConnection.prepareStatement(query);

package Template::Plugin::JavaSQL;

use strict;
use base qw(Template::Plugin);
use Carp;
use DBI;
use Template::Plugin::Java::Constants qw/:all/;
use Template::Plugin::Java::Utils qw/parseOptions sqlType2JavaType/;

use constant QUERY => qr/
		from
		\s*(.*?)\s*	# $1 = tables
		(?:where
	 		 	\s*(.*?)\s*	# $2 = condition
				(?:order\s*by
				\s*(.*)\s*$	# $3 = order by clause
				)?
		)?
		\s*$
/xi;

my $dbh = DBI->connect_cached (
	$ENV{DBI_DSN},
	$ENV{DBI_USER},
	$ENV{DBI_PASS},
	{RaiseError => 1}
);

sub new {
	my $class = shift;
	my $self  = bless {}, ref $class || $class;

	if (UNIVERSAL::isa($_[0], 'Template::Context')) {
		$self->{context} = shift;
	} elsif (UNIVERSAL::isa($_[0], 'HASH')) {
		my $args = shift;
		@$self{ keys %$args } = values %$args;
	}

	if (@_ % 2 == 0) {
		my %more = @_;
		@$self{ keys %more } = values %more;
	}

# Now process the sql context if any.
	my $vars = $self->context->stash->get('variables');
	my $spec = delete $vars->{'sql:'};

	return unless $spec;

	my $query;
	if (exists $spec->{query}) {
		$query = $spec->{query};
	} elsif (exists $spec->{table}) {
		$query = "select * from ".$spec->{table};
	}

	my $sth = $dbh->prepare_cached($query);
	$sth->execute;

	my $columns = $sth->{NAME_lc};

	my $result;
	@$result{ @$columns } =
		map { { 'java:type' => sqlType2JavaType $_ } }
			map { $dbh->type_info($_)->{TYPE_NAME} }
				@{ $sth->{TYPE} };
	
	$sth->finish;

	$self->{columns} = [sort @$columns];
	$self->parseQuery($query);

	$result = parseOptions($result);

	@{$self->{column2var}}{ sort @$columns } = sort keys %$result;
	@{$self->{var2column}}{ sort keys %$result } = sort @$columns;

# Place the variable info from the table back into main variables hash.
	@$vars{ keys %$result } = values %$result;

# If only one table used in query, check whether all columns are used (this
# makes it updatable in the underlying templates). Ideally, we should be
# checking if all the primary keys are being used.
#
# Also, this can be overridden in the context or constructor, which is not a
# good idea.
	if (!exists $self->{updatable} && $self->tableCount == 1) {
		my $test_sth = $dbh->prepare_cached (
			"select * from ".$self->tables->[0]
		);

		if ($test_sth->{NUM_OF_FIELDS} == $sth->{NUM_OF_FIELDS}) {
			$self->{updatable} = TRUE;
		}
	} elsif (exists $spec->{updatable}) {
		$self->{updatable} = $spec->{updatable};
	}

	return $self;
}

sub context { $_[0]->{context} }

sub query   { $_[0]->{query}   }

sub where   { $_[0]->{where}   }

sub tables  { $_[0]->{tables}  }

sub tableCount { scalar @{$_[0]->{tables}} }

sub updatable { $_[0]->{updatable} }

sub order   { $_[0]->{order}   }

sub columnNames { $_[0]->{columns} }

sub isColumn {
	my ($self, $name) = @_;
	$name ||= $self->{context}->stash->get('name');
	  
	scalar grep { $name eq $_ } @{$_[0]->{columns}}
		and return TRUE;
	scalar grep { $self->varToColumn($name) eq $_ }
					@{$_[0]->{columns}}
		and return TRUE;

	return FALSE;
}

sub varToColumn { return $_[0]->{var2column}{$_[1]} }

sub columnToVar { return $_[0]->{column2var}{$_[1]} }

sub columnCount { scalar @{$_[0]->{columns}} }

sub columns {
	my $self    = shift;
	my $vars    = $self->context->stash->get('variables');

	my $position= 1;

	return [ map {
		my $key  = $_;
		my $var  = $self->{column2var}{$key};
		my $type = $vars->{$var}{'java:type'};

		{
			name	=> $key,
			varName => $var,
			capName	=> ucfirst $var,
			type	=> $type,
			capType	=> ucfirst $type,
			value   => $vars->{$var},
			initializer =>
				Template::Plugin::Java->initializer($type),
			position=> $position++
		}
	} @{ $self->{columns} } ];
}

sub parseQuery {
	my ($self, $query) = @_;

	$query =~ /@{[QUERY]}/;

	$self->{query}  = $query;
	$self->{tables} = [ split /\s*,\s*/, $1 ];
	$self->{where}  = $2;
	$self->{order}  = $3;
}

1;

__END__