Establishing connection to DBI:mysql:student ...
Creating the parser...
Reading table names...
    Table names:

XS_Tk__Callback_Call error:Undefined subroutine &main::column_names_to_parser_st
r called at parser.pl line 38.

Tk::Error: Undefined subroutine &main::column_names_to_parser_str called at pars
er.pl line 38.
 Parse::RecDescent::BEGIN at grammar_func.pl line 52
 main::load_parser at parser.pl line 38
 main::load_data at splash.pl line 81
 Tk::After::once at C:/Dwimperl/perl/site/lib/Tk/After.pm line 90
 [once,[{},after#27,10,once,[\&main::load_data]]]
 ("after" script)

##</code><code>##

# sq-hal.pl
use strict;

# Required Modules
use Tk;
use Tk::Table;
use Tk::Text;
use Tk::Photo;
use Tk::Balloon;

# Global variables
use vars qw ( $user_input );      # English query typed by the user
use vars qw ( $win_sq_hal );      # the main window for SQGNL
use vars qw ( $parser );          # SQGNL parser which contains all the grammar
use vars qw ( $results );         # results of SQGNL parsing of the English query
use vars qw ( $save_parser );     # whether parser need to be required at the end (0 or 1)
use vars qw ( @learn_strs );      # temporary hold new grammar to be learned
use vars qw ( $results_table );   # table to display database records
use vars qw ( $status_text );     # status bar text to be displayed
use vars qw ( $default_status_text );  # default status bar text
use vars qw ( $txt_output );      # text area where the output of SQGNL is displayed

# SQGNL configuration variables
use vars qw ( $user );            # user name to login to the database
use vars qw ( $passwd );          # password for the database login
use vars qw ( $db_source );       # database source
use vars qw ( $db_type );         # type of database
use vars qw ( $learn_enabled );   # enable/disable learning grammar (0 - disable, 1 - enable)
use vars qw ( $rows_displayed );  # number of rows from the results query to be displayed to the user

use vars qw ( $config_file_found ); # status of the configuration file (0 or 1)

# Modular level variables
my $txt_input;       # text area for users to type the query

# Combine all the required files and
# load the subroutines from various files
do "configure.pl";     # configuration window definitions

load_config();         # load the configuration data from a file

do "parser.pl";        # SQ-HAL parser definitions
do "splash.pl";        # splash sceen definitions
do "database.pl";      # definitions for various database functionalities

# if the configuration file is not found then
# (either due to first-time running or confgureation file got deleted)
# show the SQGNL confiugration window to get required info
if ($config_file_found == 0) { show_config(); }

do "login.pl";         # get database login password

save_config();         # user name might have changed when login screen is
                       # called. So save the new configuration data

do "db_structure.pl";       # window definition to show/get database structure
do "create_sql.pl";         # window definition to create SQL statements manually
do "learning.pl";           # window definition to display new grammar to be learnt
do "relationships.pl";      # window definition to display/get table relationships

### Show the splash screen while loading the parser ###
### as the parser may take some time to load        ###
show_splash();

### Create and show the main SQGNL window ###
create_main_window();

# Display the spalsh screen and load parser grammar in the background
sub create_main_window
{
    $win_sq_hal = MainWindow->new;    # create the main SQGNL window
    $win_sq_hal->appname("SQGNL");
    $win_sq_hal->title("SQGNL: The Natural Language to SQL Translator");

    ### maximize and position the SQGNL main window ###
    my $w =  $win_sq_hal->screenwidth()-10;   # window width = screen width
    my $h =  $win_sq_hal->screenheight()-100; # window height = screen height
    $win_sq_hal->geometry("${w}x${h}+0+20");

    ### define and place window controls ###

    my $tooltip = $win_sq_hal->Balloon( -statusbar => $status_text );

    my $win_sq_hal1 = $win_sq_hal->Frame( -relief => 'flat',
                                          -borderwidth => 10)
                                  ->pack( -ipadx => 10,
                                          -fill => 'both',
                                          -expand => 1);

    $win_sq_hal1->Label( -text => "Type your question below:",
                         -anchor => "sw")
                 ->pack( -fill => "x");

    ### user input text area -----------------------------------------------
    $txt_input = $win_sq_hal1->Scrolled( 'Text', -scrollbars => 'e',
                                          -height => 2,
                                          -wrap => "word")
                                  ->pack( -side => "top",
                                          -fill => "x",
                                          -expand => 0);

    $tooltip->attach($txt_input,
                     -msg => "Type your English query here and then press Tranlate button.");

    my $fra_buttons1 = $win_sq_hal1->Frame->pack( -side => 'top',
                                              -fill => 'x',
                                              -expand => 0 );

    ### button to activate translate the English query ---------------------
    my $cmd_translate  = $fra_buttons1->Button( -text => "Translate the query to SQL",
                                              -command => \&parse_input)
                                      ->pack( -side => "left",
                                              -ipadx => 10,
                                              -anchor => "ne");

    $tooltip->attach($cmd_translate,
                     -msg => "Translate the English query into SQL.");

    ### button to clear the content in the query text area -----------------
    my $cmd_clear = $fra_buttons1->Button( -text => "Clear",
                                           -command =>
                                           sub
                                           {
                                               ### delete everything in the text area ###
                                               $txt_input->delete("1.0", "end");
                                           }
                                         )
                                ->pack( -side => "left",
                                        -ipadx => 10,
                                        -padx => 10,
                                        -anchor => "ne");

    $tooltip->attach($cmd_clear,
                     -msg => "Clear the text in the English query area.");

    ### button to bring up the create_sql window ---------------------------
    my $cmd_create_sql = $fra_buttons1->Button( -text => "Create your own SQL",
                                             -command => \&show_create_sql)
                                     ->pack( -side => "right",
                                             -ipadx => 10,
                                             -anchor => "ne");

    $tooltip->attach($cmd_create_sql,
                     -msg => "Bring up the window where you can create your own SQL statements with ease.");

    my $fra_output = $win_sq_hal1->Frame->pack( -side => 'top',
                                              -fill => 'x',
                                              -expand => 0 );

    ### text area to display output results --------------------------------
    $txt_output = $fra_output->Scrolled( 'Text', -scrollbars => 'e',
                                        -height => 3,
                                        -wrap => "word")
                                ->pack( -side => "left",
                                        -anchor => "nw",
                                        -fill => "x",
                                        -expand => 1);

    $tooltip->attach($txt_output,
                     -msg => "Translated SQL statments are displayed here.
You may can modify this and press execute button\n to see the results of the SQL statment");

    ### button to execute SQL in the txt_output area -----------------------
    my $cmd_exec = $fra_output->Button( -text => "Execute SQL",
                                        -command =>
                                        sub
                                        {
                                            ### copy the content of the txt_output        ###
                                            ### (SQL statement) to the varaiable $results ###
                                            $results = $txt_output->get("1.0", "end");

                                            ### execute SQL and show the results ###
                                            if ( $results ne "")
                                            {
                                                show_data();
                                            }
                                        }
                                      )
                                ->pack( -side => "right",
                                        -ipadx => 10,
                                        -pady => 10,
                                        -anchor => "ne");

    $tooltip->attach($cmd_exec,
                     -msg => "Execute the SQL statment and display the results.");

    ### $status_text bar text area -----------------------------------------
    $default_status_text = "SQ-HAL: The Natural Language to SQL Translator";
    $status_text = $default_status_text;
    $win_sq_hal->Label( -textvariable => \$status_text,
                        -relief => "sunken",
                        -anchor => "nw",
                        -borderwidth => 2)
                ->pack( -side => "top",
                        -fill => "x",
                        -padx => 10,
                        -expand => 0);

    my $fra_buttons2 = $win_sq_hal1->Frame
                                   ->pack( -side => "bottom");

    ### button to bring up the database structure window -------------------
    my $cmd_database = $fra_buttons2->Button( -text => "Database",
                                             -underline => 1,
                                             -command => \&show_database)
                                     ->pack( -fill => "x",
                                             -ipadx => 20,
                                             -padx => 10,
                                             -pady => 5,
                                             -side => "left",
                                             -expand => 0);

    $tooltip->attach($cmd_database,
                     -msg => "Display the current database structure.");

    ### button to bring up the configuration window ------------------------
    my $cmd_config = $fra_buttons2->Button( -text => "Configure",
                                           -underline => 2,
                                           -command => \&show_config)
                                   ->pack( -fill => "x",
                                           -side => "left",
                                           -ipadx => 20,
                                           -padx => 10,
                                           -pady => 5,
                                           -expand => 0);

    $tooltip->attach($cmd_config,
                     -msg => "Configure SQ-HAL.");

    ### button to exit to the system ---------------------------------------
    my $cmd_exit = $fra_buttons2->Button( -text => "Exit",
                                          -underline => 1,
                                          -command => \&exit_sq_hal )
                                  ->pack( -fill => "x",
                                          -ipadx => 40,
                                          -padx => 10,
                                          -pady => 5,
                                          -side => "left");

    $tooltip->attach($cmd_exit,
                     -msg => "End SQ-HAL and Exit to the system.");

    ### table to display results from the SQL statements -------------------
    $results_table = $win_sq_hal1->Table( -rows => 1,
                                     -columns => 1,
                                     -scrollbars => "se",
                                     -relief => "groove",
                                     -borderwidth => 2,
                                     -fixedrows => 1)
                             ->pack( -side => "top",
                                     -fill => "both",
                                     -expand => 1);

    $tooltip->attach($results_table,
                     -msg => "Display data retrieved from the database.");

    ### exit the program when destroying this main window ##################
    $win_sq_hal->bind("<Destroy>", \&exit_sq_hal);

    ### set the focus to the query entering area ###
    $txt_input->focus;

    ### display  this window and start handling events ###
    MainLoop;
}

# parse user input and show results
sub parse_input
{
    ### update the statusbar text ###
    $status_text = "Translating the English statement to a SQL statement...";
    $win_sq_hal->update();

    ### the input and output files and commented lines below ###
    ### are used for testing purposes only                   ###
    #my $inputFile = "data.txt";
    #my $outputFile = "output.txt";

    #open(DATA, "< $inputFile")  || die $!;
    #open(OUT, "> $outputFile")  || die $!;

    #while (<DATA>)
    #{
    #    if (!/^#/ && !/^[\s]*\n/)       # Ignore commented lines and empty lines
    #    {
    #        print "> ";
    #        sleep 1;
    #        print;

    ### copy the English query to the variable $user_input ###
    $user_input = $txt_input->get("1.0", "end");

    ### remove special characters from the input ###
    $user_input =~ s/[:.'?!]//g;

    ### translate the user query to SQL ###
    eval{ $results = $parser->translate("\L$user_input"); };

    ### clear the current content of the output area and insert new translated SQL ###
    $txt_output->delete("1.0", "end");
    $txt_output->insert("end", $results);

    $_ = $results;

    ### if the first word of the results is "SELECT" then it is an     ###
    ### SQL statement.  Otherwise it is and untranslated error message ###
    if (/^SELECT/)
    {
        ### display the SQL statement in bule colour ###
        $txt_output->configure( -foreground => "blue" );

        ### execute SQL and show the results ###
        show_data();

        ### if there are anything to be leart, then display the learning window ###
        if ($#learn_strs >= 0) { show_learn(); }
    }
    else   ### English query not translated into SQL ###
    {
        ### if the learning is enabled, then add this English query ###
        ### to the query list that to be learnt                     ###
        if ($learn_enabled)
        {
            $learn_strs[++$#learn_strs] = "\L$user_input";
        }

        ### display the error message in red ###
        $txt_output->configure( -foreground => "red" );
    }

    ### update window controls ###
    $win_sq_hal->update();

              ### save the results in the outupt file ###
              #print OUT $user_input, $results, "\n";
    #    }
    #}


    ### close all the open files ###
    #close(DATA);
    #close(OUT);

    ### update the statusbar with default text ###
    $status_text = $default_status_text;
    $win_sq_hal->update();
}

# retrieve data from the database and display on to the screen
sub show_data()
{
    ### change the mouse icon to be busy icon ###
    $win_sq_hal->Busy;

    ### update statusbar text ###
    $status_text = "Retrieving data from the database...";
    $txt_output->configure( -foreground => "blue" );
    $win_sq_hal->update();

    ### execute the SQL results                    ###
    ### this will update the results table as well ###
    execute_sql( $results );

    ### update status bar text back to default ###
    $status_text = $default_status_text;

    ### change the mouse icon back to normal ###
    $win_sq_hal->Unbusy;
}


### used as a flag to determine the exit function is called once ###
### multiple calls to the function is posible if the user press  ###
### exit button as well as destorying the window calls the func. ###
my $already_exited = 0;

# exit SQGNL by disconnecting from the database and saving the parser
sub exit_sq_hal
{
    ### do not repeat this subroutine twice ###
    if ($already_exited) { return }
    $already_exited = 1;

    ### change the mouse icon to be busy icon ###
    $win_sq_hal->Busy;

    ### disconnect the current database connection ###
    disconnect_from_db();

    ### if required, save the parser to a file ###
    if ($save_parser)
    {
        ### update statusbar text
        $status_text = "Saving the parser to a file.  Please wait...";
        $win_sq_hal->update;
        save_parser();
    }

    ### save database structure to a file ###
    #save_db_info();

    ### exit to the system ###
    exit;
}

##</code><code>##

# parser.pl

use strict;

# Global variables
use vars qw( %table_columns );
use vars qw( %table_relationships );

# Modular level variables

### Following variables used to test this parser only ###
my $dataFile    = "data1.txt";      ### Input Data file (for testing)     ###
my $outputFile  = "output.txt";    ### Output results file (for testing) ###
my $debug_on = 0;                  ### Enable testing of this parser     ###
my $trace_on = 0;                  ### Enable tracing the parser output  ###
my $grammar;                       ### SQGNL parser grammar             ###
my $parser_file;                   ### name of the file which contains parser object ###

# combine table names into parser accepted string
sub load_parser
{
    $parser_file = "sq_hal_${db_type}_${db_source}_${user}";

    ### replace invalid names in the file name ###
    $parser_file =~ s/[\\\/:*?<>|]//g;

    ### if parser file not found then load grammar ###
    if (!eval{require "${parser_file}.pm"})
    {
        print "Creating the parser...\n";

        ### Create the grammar ###
        $grammar = do "grammar.pl" or warn "Bad Grammar!";

        ### Replace table_names ###
        my $tables_str = table_names_to_parser_str();
        $grammar =~ s/TABLES/$tables_str/;

        ### Replace column names ###
        my $columns_str = column_names_to_parser_str();
        $grammar =~ s/FIELDS/$columns_str/;
    }
    else
    {
        print "Loading the parser from the file '${parser_file}.pm'...\n";
        do "grammar_func.pl";
    }

    ### initialize variables such as database structure, etc. ###
    initialize_vars();

    use Parse::RecDescent;

    ## Enable tracing of the parser ###
    if ($trace_on)  { $RD_TRACE = 1; }

    ### Load the parser from the file or               ###
    ### create the parser if the file is not available ###
    $parser = eval{require "${parser_file}.pm"}
        ? $parser_file->new()
        : Parse::RecDescent->new($grammar)
        or warn "Bad Parser!";

    ### if the parser file is not found, then the parser need to be saved ###
    if (!eval{require "${parser_file}.pm"})
    {
        $save_parser = 1;
    }

    ### if testing this parser only then do the following code ###
    if ($debug_on)
    {
        ### Open the input data file
        open(DATA, "< $dataFile")  || die $!;
        open(OUT, "> $outputFile")  || die $!;

        $| = 1;

        ### Parse each line of data ###
        while (<DATA>)
        {
            if (!/^#/ && !/^[\s]*\n/)    ### Ignore commented or empty lines ###
            {
                print "> ";
                #sleep 1;
                print;

                ### Translate the grammar ###
                my $SQL = $parser->translate("\L$_");

                ### Print the translated output to the screen and output file ###
                print "$_$SQL\n";
                print OUT "> $_$SQL\n";
            }
            else
            {
                print OUT "$_";
            }
        }

        ### Close files ##
        close(DATA);
        close(OUT);

        ### Exit to the system ###
        exit;
    }
}

# save the parser to a file
sub save_parser()
{
    print "Saving the parser to the file '${parser_file}.pm'...";

    ### if exist, then delete the parser file ###
    if (eval{require "${parser_file}.pm"})
    {
        eval { unlink "${parser_file}.pm"; };
    }

    ### save the parser to file ###
    eval { $parser->Save($parser_file); };

    ### do not need to save the parser in the near future again ###
    $save_parser = 0;
}

# learn new rule by the parser
sub extend_parser
{
    my ($rule, $str) = ($_[0], $_[1]);  ### parser rule and the new learn string ###

    my $grammar = qq{ $rule : $str };   ### new grammar to be learn ###

    $parser->Extend($grammar);          ### extend the parser grammar ###

    #$save_parser = 1;   ### parser has been changed and therefore need to save ###

    ### print the newly learn grammar ###
    print "Learn grammar:\n$grammar\n";
}

# initialize table relationships and table-column relationships
sub initialize_vars
{
    @table_columns{get_table_names()} = ();

    foreach my $table (keys(%table_columns))
    {
        my %tmp;

        @tmp{get_column_names($table)} = ();
        foreach (keys(%tmp))
        {
            $tmp{$_} = "1";
        }

        $table_columns{$table} = { %tmp };
    }

    ### load the table relationships from the file ###
    if (open(DB, "${parser_file}.db"))
    {
        ### read each of the table relationship ###
        while (<DB>)
        {
            chomp($_);
            next unless s/^(.*?):\s*//;

            my $tbl1 = qq{$1};

            for my $field ( split /;/ )
            {
                my ($tbl2, $val) = split(",", $field);
                $table_relationships{ qq{$tbl1} }{ qq{$tbl2} } = qq{$val};
            }
        }

        close(DB);  ### close the file ###
    }
}

# combine table names into parser accepted string
sub save_db_info()
{
    open(DB, "> ${parser_file}.db");

    ### save each table relationship to a file
    foreach my $tbl1 (keys(%table_relationships))
    {
        print DB "$tbl1:";

        foreach (keys(%{$table_relationships{$tbl1}}))
        {
            print DB "$_,", $table_relationships{$tbl1}{$_},";" ;
        }
        print DB "\n";
    }

    close(DB);   ### close the file ###
}

# combine table names into parser accepted string
sub table_names_to_parser_str()
{
    print "Reading table names...\n";

    ### Get table names for the current database ###
    %table_columns = ();
    @table_columns{get_table_names()} = ();

    print "    Table names: ", join(", ", keys(%table_columns)), "\n\n";

    ### Create the parser recognised string ###
    my $tables_str = "";
    foreach my $table ( keys(%table_columns) )
    {
        ### table words need to be lower case ###
        my $table_words = "\L${table}?";
        $tables_str .= "/${table_words}/{'${table}'}|";
    }
    chop($tables_str);  ### Remove the last '|' character ###

    return $tables_str;
}

# combine all table columns into parser accepted string
sub column_names_to_parser_str()
{
    use Lingua::EN::Inflect ':ALL';

    ### read column names for each table ###
    my $columns_str = "";
    foreach ( keys(%table_columns) )
    {
        if ( $_ )
        {
            print "Read column names for '$_'...\n";

            my $current_table = $_;

            ### Get the column names for the given table ###
            my @columns = get_column_names($_);

            print "    Column names: ", join(", ", @columns), "\n\n";

            ### Create parser recognised string ###
            foreach my $column (@columns)
            {
                ### column words need to be lower case ###
                my $column_words =  "\L" . PL_N($column) . "|$column";
                $columns_str .= "/${column_words}/{'${column}'}|";
            }
         }
    }
    chop($columns_str);   ### Remove the last "|" character ###

    return $columns_str;
}

1;    ### so the 'do' command succeeds ###

##</code><code>##

# splash.pl

use strict;

# Modular level variables
use vars qw( $win_splash );      # window handle for this splash screen
my $PARSER_FILE  = "parser.pl";  # name of the parser file
my $splash_shown = 0;            # splash screen is already shown or not
my $splash_info;                 # message to be displayed on the splash screen

# Display the spalsh screen and load parser grammar in the background
sub show_splash
{
    ### do not show the splash screen if already shown ###
    ### we do not want to load the grammar twice       ###
    if ($splash_shown == 1)    {  return 0; }

    $splash_shown = 1;    ### splash screen has been shown ###

    ### create splash screen  ###
    $win_splash = MainWindow->new();

    ### do no show the title bar for the splash screen ###
    $win_splash->overrideredirect(1);

    $win_splash->configure( -background => "white");
    $win_splash->configure( -borderwidth => 1);

    ### center this window ###
    my $h = 270;    ### window height ###
    my $w = 360;    ### window width  ###
    my $x =  int(($win_splash->screenwidth()-$w)/2);      ### x position ###
    my $y =  int(($win_splash->screenheight()-100-$h)/2); ### y position ###
    $win_splash->geometry("${w}x${h}+${x}+${y}");

    ### create window controls ###
    my $img_sq_hal = $win_splash->Photo( -file => 'splash.gif');

    ### SQGNL image ###
    $win_splash->Label( -borderwidth => 0,
                        -image => $img_sq_hal )->pack;

    ### display area for splash screen messages ###
    $win_splash->Label( -textvariable => \$splash_info,
                        -background => "white")
                ->pack( -fill => "x",
                        -side => "top");

    ### Exit button - to exit to the system ###
    $win_splash->Button( -text => "  Exit  ",
                         -background => "white",
                         -borderwidth => 0,
                         -command => sub { exit })
                 ->pack( -fill => "x",
                         -side => "top");

    ### show spalsh screen ###
    $win_splash->update();
    $win_splash->raise();

    # start loading the parser immediately after displaying the splash screen ###
    $win_splash->after(10, \&load_data);

    MainLoop;
}

# Load initial data and the parser at startup
sub load_data
{
    ### connect to the database ###
    $splash_info = 'Connecting to the database...';
    $win_splash->update();

    ### if unsuccessful database connection, then exit the program ###
    if (! connect_to_db($db_type, $db_source, $user, $passwd ))
    {
        exit;
    }

    ### load SQGNL parser ###
    $splash_info = 'Loading SQ-HAL parser...';
    $win_splash->update();
    load_parser();

    ### Destroy the splash screen ###
    $win_splash->destroy();
}

1;  # so the 'do' command succeeds