# # -*-Perl-*- 
# 
# SQLEditor 
#
# $Id: SQLEditor.pm,v 1.2 1996/10/07 11:03:23 dmh Exp $
#
# $Log: SQLEditor.pm,v $
# Revision 1.2  1996/10/07  11:03:23  dmh
# Set env vars for /nfs/sybase
#
# Revision 1.1  1996/10/07  10:57:57  sybase
# Initial revision
#
#

package Tk::SQLEditor;

use Carp;
#use strict qw(vars);
use Sybase::DBlib;
require Tk::Toplevel;
require Tk::Dialog;
require Tk::Text;


@Tk::SQLEditor::ISA = qw(Tk::Toplevel);
Tk::Widget->Construct('SQLEditor');


sub messageHandler {

    my ($db, $message, $state, $severity, $text, $server, $procedure, $line)= @_;

    # Don't display 'informational' messages:
    if ($severity > 10)
    {
	print STDERR ("**Sybase message ", $message, ", Severity ", $severity,
	       ", state ", $state);
	print STDERR ("\nServer `", $server, "'") if defined ($server);
	print STDERR ("\nProcedure `", $procedure, "'") if defined ($procedure);
	print STDERR ("\nLine ", $line) if defined ($line);
	print STDERR ("\n    ", $text, "\n\n");

# &dbstrcpy returns the command buffer.

	if(defined($db))
	{
	    my ($lineno, $cmdbuff) = (1, undef);

	    $cmdbuff = &Sybase::DBlib::dbstrcpy($db);
	       
	    foreach $row (split (/\n/, $cmdbuff))
	    {
		print STDERR (sprintf ("%5d", $lineno ++), "> ", $row, "\n");
	    }
	}
    }
    elsif ($message == 0)
    {
	print STDERR ($text, "\n");
    }
    
    0;
}

sub errorHandler {

    my ($db, $severity, $error, $os_error, $error_msg, $os_error_msg)= @_;

    # Check the error code to see if we should report this.
    if ($error != SYBESMSG) {
	print STDERR ("Sybase error: ", $error_msg, "\n");
	print STDERR ("OS Error: ", $os_error_msg, "\n") if defined ($os_error_msg);
    }

    INT_CANCEL;
}

# mal3A


sub Populate {

    my($this, $args) = @_;

    &dbmsghandle (\&messageHandler);
    &dberrhandle (\&errorHandler);

    #################
    # 
    # get args
    # 
    #################

    $this->SUPER::Populate($args);

    $this->{'loginame'} = delete $args->{'-user'} || 'badger';
    $this->{'loginpassword'} = delete $args->{'-password'} || 'badgerset';
    $this->{'loginserver'} = delete $args->{'-server'} ||'SYBASE';
    $this->{'db'} = delete $args->{'-usedb'} ||'rat';   

    #################
    # 
    # Defaults
    # 
    #################
    
    $this->{'rowcount'} = 0;

    my $labelbigfont = '*times*-r*20*';
    my @mainframepack = qw(-side top -fill both -expand yes -pady 3 -padx 3 -ipady 3 -ipadx 3);
    my @textpack = qw(-side top -expand yes -fill both -padx 3);
    my @buttonpack = qw(-side left -expand yes -padx 10 -pady 5);

    #################
    # 
    # Two main frames upper & lower
    # 
    #################

    my $upperframe = $this->Frame(-relief => 'sunken' , -borderwidth => 2);
    $upperframe->pack(qw(-side top -fill both -pady 3 -padx 3 -ipady 3 -ipadx 3));

    my $lowerframe = $this->Frame(-relief => 'sunken' , -borderwidth => 2);
    $lowerframe->pack(@mainframepack);


    #################
    # 
    # Label
    # 
    #################

    my $toplabel = $upperframe->Frame;
    $toplabel->pack( -side => 'top', -fill => 'x', -ipady => '3');

    $toplabel->Label (-text => 'Edit:', -font => $labelbigfont)
	->pack(-side => 'left', -ipadx => '10');


    #################
    # 
    # Upper editing portion.
    # 
    #################

    my $u_frame = $upperframe->Frame();
    $u_frame->pack(qw(-side top -fill both -padx 3));

    $this->{'uptext'} = $u_frame->Text(-relief => 'sunken', 
			 -bd => '2', 
			 -setgrid => 'true', 
			 -wrap => 'word',
			 -width => 60,
			 -height => 8);

    my $v_scroll = $u_frame->Scrollbar(-command => ['yview', $this->{'uptext'}]);

    $this->{'uptext'}->configure(-yscrollcommand => ['set', $v_scroll]);
    $v_scroll->pack(-side => 'right', -fill => 'y');
    
    $this->{'uptext'}->pack(-side => 'left', -expand => 'yes', -fill => 'both');
    $this->{'uptext'}->focus;


    #################
    # 
    # bindings
    # 
    #################

    my $testsqlstr = qq{select sample, ASP_type, ASP_reason
from samples
where plate = "mal3A"};

    $this->{'uptext'}->bind('<Control-Return>', sub {&submit($this, $this->{'uptext'})});
    $this->{'uptext'}->bind('<Delete>', Backspace);
    
#     $this->{'uptext'}->bind('<Delete>', sub {$this->{'uptext'}->markSet('insert', 'current - 1 chars');
# 					     $this->{'uptext'}->see('current - 1 chars');
#					 });


#     $this->{'uptext'}->bind('<Control-a>', sub {$this->{'uptext'}->markSet('insert', 'linestart');
# 						$this->{'uptext'}->see('1.0');
# 						});

    #################
    # 
    # Upper Buttons
    # 
    #################

    my $upperb = $upperframe->Frame();
    $upperb->pack(-side => 'top');

    my $submit=$upperb->Button( -text => 'Submit', -command => [\&submit, $this, $this->{'uptext'}]);
    $submit->pack(@buttonpack);

    my $test=$upperb->Button( -text => 'Test', 
			     -command => sub {$this->appendText($this->{'uptext'}, $testsqlstr);
					 $this->enableText($this->{'uptext'}) });
    $test->pack(@buttonpack);

    my $clear=$upperb->Button( -text => 'Clear', -command => sub {$this->{'uptext'}->delete('1.0', 'end') });
    $clear->pack(@buttonpack);
    
    my $close=$upperb->Button( -text => 'Close', -command => sub { $this->withdraw} );
    $close->pack(@buttonpack);

    #################
    # 
    # Lower results with row count
    # 
    #################

    my $botlabel = $lowerframe->Frame;
    $botlabel->pack( -side => 'top', -fill => 'x', -ipady => '3');

    $botlabel->Label (-text => 'Results:', -font => $labelbigfont)
	->pack(-side => 'left', -ipadx => '10');


    my $rclabel = $botlabel->Frame(-relief => 'sunken', -borderwidth => 2);
    $rclabel->pack( -side => 'right', -pady => '3', -padx => '10');

    $rclabel->Label (-textvariable => \$this->{'rowcount'}, -font => '*times*-r*20*')
	->pack(-side => 'right');

    $rclabel->Label (-text => 'Rows: ', -font => '*times*-r*20*')
	->pack(-side => 'right');


#     $this->{'toggleb'} = $botlabel->Button(-textvariable => \$this->{'restoggle'}, 
# 					   -command => [\&resToggle, $this, $this->{'toggleb'}]);
#     $this->{'toggleb'}->pack (-side => 'right');


    #################
    # 
    # Lower results section
    # 
    # Two text widgets one for title.
    # 
    #################

    my $l_frame = $lowerframe->Frame();
    $l_frame->pack(@textpack);


    $this->{'titletext'} = $l_frame->Text(-relief => 'sunken', -bd => '2', -setgrid => 'true', 
				       -wrap => 'none', -state => 'disabled', -width => 60,
				       -height => 1, -foreground => 'midnightblue');

    $this->{'titletext'}->pack(-side => 'top', -fill => 'both');

    $this->{'lotext'} = $l_frame->Text(-relief => 'sunken', -bd => '2', -setgrid => 'true', 
				       -wrap => 'none', -state => 'disabled', -width => 60,
				       -height => 15);

    my $v_scroll = $l_frame->Scrollbar(-command => ['yview', $this->{'lotext'}]);
    my $h_scroll = $l_frame->Scrollbar(-orient => 'horizontal', -command => sub {
	$this->{'lotext'}->xview(@_);
	$this->{'titletext'}->xview(@_);
    });

    $this->{'titletext'}->configure(-xscrollcommand => ['set', $h_scroll]);    
    $this->{'lotext'}->configure(-yscrollcommand => ['set', $v_scroll],
				 -xscrollcommand => ['set', $h_scroll]);
    
    $v_scroll->pack(-side => 'right', -fill => 'y');
    $h_scroll->pack(-side => 'bottom', -fill => 'x');
    

    $this->{'lotext'}->pack(-side => 'left', -expand => 'yes', -fill => 'both');


    #################
    # 
    # Lower Buttons
    # 
    #################

    my $lowerb = $lowerframe->Frame();
    $lowerb->pack(-side => 'top');

    my $clear=$lowerb->Button( -text => 'Clear', -command => sub {$this->clearText($this->{'lotext'})});
    $clear->pack(@buttonpack);
    
    my $save=$lowerb->Button( -text => 'Save As..', -command => sub {} );
    $save->pack(@buttonpack);
    
    my $print=$lowerb->Button( -text => 'Print..', -command => sub {} );
    $print->pack(@buttonpack);
       
}

sub enableText {

    my ($this, $textw)=@_;
    $textw->configure( -state => 'normal' );
}

sub disableText {

    my ($this, $textw)=@_;
    $textw->configure( -state => 'disabled' );
}

sub clearText {

    my ($this, $textw)=@_; 

    $this->enableText($textw);
    $textw->delete('1.0', 'end');
    $this->disableText($textw);

}

sub putText {

    my ($this, $textw, $string)=@_;
    
    $this->clearText($textw);
    $this->appendText($textw, $string);

}

sub appendText {

    my ($this, $textw, $string)=@_;

    $this->enableText($textw);
    $textw->insert('end', $string);
    $this->disableText($textw);

}

sub getText {

    my ($this, $textw)=@_;
    return $textw->get('1.0', 'end');
}


sub insertWithTags {

    my($this, $textw, $str, @args) = @_;


    $this->enableText($textw);

    my($tag, $i);
        
    my $start = $textw->index('end');
    $textw->insert('end', $str);

    foreach $tag ($textw->tag('names', $start)) {
	$textw->tag('remove', $tag, $start, 'insert');
    }

    foreach $i (@args) {
	$textw->tag('add', $i, $start, 'insert');
    }

    $this->disableText($textw);

} 


sub show {

    my($this) = @_;
    $this->Popup(); 

} 

sub notifyWait {

    my($this) = @_;

    $this->configure(-cursor => 'watch');
    $this->{'uptext'}->configure(-cursor => 'watch');
    $this->{'lotext'}->configure(-cursor => 'watch');
    $this->update;

}

sub notifyActive {

    my($this) = @_;

    $this->{'uptext'}->configure(-cursor => 'xterm');
    $this->{'lotext'}->configure(-cursor => 'xterm');
    $this->configure(-cursor => 'top_left_arrow');
    $this->update;

}


sub sybLogin {

   my ($this)=@_;

   $ENV{'SYBASE'} = '/nfs/sybase/server';
   $this->{'dbh'} = Sybase::DBlib->dblogin($this->{'loginame'}, 
					   $this->{'loginpassword'},
					   $this->{'loginserver'});
 
   $this->{'dbh'}->dbuse($this->{'db'});
   # Check for login error - HOW?
   
   return 0;

}



sub formatTable {

    my ($this, $tableref, $tablestr_ref, $titlestr_ref) = @_;

    my $colnum = $this->{'dbh'}->dbnumcols;
    my ($i,$j, $width, $maxwidth,  @widths);

    # 
    # Determine the max column extent for
    # each col by checking every value.
    # 
    for($j=0; $j < $colnum; $j++) {
	$maxwidth = 0;
	for ($i=0; $i <= $#{$tableref}; $i++) {   	
	    $width = length($$tableref[$i]->[$j]);
	    if ($width>$maxwidth) {
		$maxwidth=$width;
	    }
	}
	$widths[$j] = $maxwidth;
    }

    # 
    # Check col headings
    # 
    for ($i=1; $i <= $colnum; $i++) {

	my $colname = $this->{'dbh'}->dbcolname($i);

	my ($first, $rest) = ($colname =~ /^(\S)(.*)/);
	$first =~ tr/a-z/A-Z/;
	$colname = "$first$rest";

	my $collen = $widths[$i-1];
	my $colnamelen = length($colname);
	
	my $width = ($colnamelen > $collen) ? $colnamelen : $collen;
	$widths[$i-1] = $width;
	
	$$titlestr_ref .= sprintf(" %-${width}s ", $colname);

    }
    $$titlestr_ref .= "\n";

    # 
    # Add the values
    # 
    for ($i=0; $i <= $#{$tableref}; $i++) {
	for($j=0; $j <= $#{$$tableref[$i]}; $j++) {
	    $width = $widths[$j];    
	    $$tablestr_ref .= sprintf(" %-${width}s ", $$tableref[$i]->[$j]);
	}
	$$tablestr_ref .= "\n";
    }

    return $#{$tableref};

}


sub submit {

    my ($this, $textw)=@_;

    # Wait cursor..
    $this->notifyWait();


    if ($this->sybLogin()) {
	warn "SqlEditor: Failed to login to server\n";
	return;
    }

    # 
    # Run the Query...
    # 
    my $ref = $this->{'dbh'}->sql($this->getText($this->{'uptext'}));


    # 
    # Format the table string...
    # 
    my $tablestr = '';
    my $titlestr = '';
    $this->{'rowcount'} = $this->formatTable($ref, \$tablestr, \$titlestr);

    
    # 
    # Display the table...
    # 
    $this->putText($this->{'titletext'}, $titlestr);
    $this->putText($this->{'lotext'}, $tablestr);


    $this->notifyActive();

}


sub clearAll {

    my ($this)=@_;

    $this->clearText($this->{'uptext'});
    $this->clearText($this->{'lotext'});


}


1;








