277294.ijbsn.asia: NLBean.java

Sat Dec 08 16:00:00 GMT 2001

NLBean

// mwa.products.NLBean.java
//
// Copyright 1997, Mark Watson.
//

package nlbean;
import java.net.*;
import java.io.*;
import java.awt.*;
import java.awt.event.*;
import java.awt.image.*;
import java.text.*;
import java.util.*;
import java.beans.*;
import java.sql.*;
import java.util.Vector;

public class NLBean extends Panel implements Serializable {

protected NLEngine engine = null;

public NLBean() {
        super();
        reset();
    }

private void writeObject(java.io.ObjectOutputStream s)
        throws java.io.IOException {
        System.out.println("Beginning writeObject");
        s.defaultWriteObject();
        System.out.println("Ending writeObject");
    }

private void resetExamples() {
        choiceChanged = false;
        choice.removeAll();
        choice.addItem("Examples                                        ");
        for (int i=(examples.length - 1); i>= 0; i--) {
            choice.insert(examples[i], 1);
        }
    }

private void resetSynonyms() {
        engine.clearSynonyms();
        for (int i=0; i<synonyms.length; i++) {
            addSynonym(synonyms[i]);
        }
    }

//              Set up USER INTERFACE:

private void reset() {
        engine = new NLEngine();
        AWTsetup();
    }

private void AWTsetup() {
        Frame help_frame = new Frame();
        help = new Help(help_frame);

setFont(new Font("Dialog", Font.PLAIN, 12));
        setLayout(null);

Label l1 = new Label("Natural Language Database Access");
        l1.setFont(new Font("Dialog", Font.BOLD, 28));
        add(l1);
        l1.setBounds(2, 1, 600, 34);

list1 = new java.awt.List(3, false);
        for (int i=0; i<databaseNames.length; i++) list1.add(databaseNames[i]);
        list2 = new java.awt.List(3, false);
        list3 = new java.awt.List(3, false);

add(list1); add(list2); add(list3);
        list1.setBounds(2, 40, 220, 90);
        list2.setBounds(232, 40, 170, 90);
        list3.setBounds(412, 40, 170, 90);
        list1.addMouseListener(new MouseSelect1());
        list2.addMouseListener(new MouseSelect2());
        list3.addMouseListener(new MouseSelect3());

Button q_button = new Button("Do query");
        q_button.addMouseListener(new MouseQuery());
        add(q_button);  q_button.setBounds(2, 140, 160, 30);
        Button help_button = new Button("Help");
        help_button.addMouseListener(new MouseHelp());
        add(help_button); help_button.setBounds(172, 140, 40, 30);

Label label22 = new Label("Query:");
        label22.setFont(new Font("Dialog", Font.BOLD, 14));
        add(label22);  label22.setBounds(2, 180, 60, 22);  label22.setVisible(true);

//        inputText = new SmartTextField("list Salary where EmpName equals Mark", 64);
        inputText = new TextField("list Salary where EmpName equals Mark", 64);
        add(inputText);  inputText.setBounds(80, 180, 500, 22);

choice = new Choice();
        choiceChanged = false;
        choice.addItem("Examples                                        ");
        for (int i=(examples.length - 1); i>=0; i--) choice.insert(examples[i], 1);
        choice.addItemListener(new ChoiceListener());
        add(choice);  choice.setBounds(2, 210, 582, 25);

Label label23 = new Label("Generated SQL:");
        label23.setFont(new Font("Dialog", Font.BOLD, 12));
        add(label23);   label23.setBounds(2, 240, 120, 30);
        sqlText = new TextArea("",1,80,TextArea.SCROLLBARS_NONE);
        sqlText.setEditable(false);
        add(sqlText);  sqlText.setBounds(130, 240, 455, 40);

outputText = new TextArea("NLBean(tm) natural language interface\nCopyright 1997, Mark Watson.  All rights reserved.\n", 8, 74);
        add(outputText);  outputText.setBounds(2, 285, 582, 150);

Label l1x = new Label("NLBean Copyright 1997-1999 by Mark Watson.  www.markwatson.com");
        l1x.setFont(new Font("Dialog", Font.ITALIC, 14));
        add(l1x);
        l1x.setBounds(5, 442, 540, 16);

// * * *
        list1_last_selection=-1; list2_last_selection=-1; list3_last_selection=-1;
        setBounds(20, 20, 590, 464);
        setupHelper();

}

private void setupHelper() {

if (loadSynonyms) {
            for (int i=0; i<synonyms.length; i++) addSynonym(synonyms[i]);
        }

if (loadDB) {
            for (int i=0; i<databaseNames.length; i++) {
                engine.addDB(databaseNames[i], userNames[i], passwords[i], tableLists[i]);
            }
        }
        engine.initDB();
    }

// Suggest spelling corrections, or other words:

// synchronized private String[] suggestedWords(String a_word) {
    //     return engine.suggestedWords(a_word);
    // }

synchronized private void putText(String s, boolean replace_flag) {
        if (replace_flag==false) {
            outputText.append(s);
        } else {
            outputText.setText(s);
        }
    }

synchronized private String inText(String new_val, boolean set_flag) {
        if (set_flag) {
            inputText.setText(new_val);
            return "";
        }
        return inputText.getText();
    }

synchronized private void query() {
        System.out.println("Entering query()");

sqlText.setText("");

String a_query = inText("", false);
        System.out.println("query(): a_query=" + a_query);
        String sql_query=null;
        if (a_query.startsWith("SELECT") || a_query.startsWith("select")) {
            sql_query = a_query;
        } else {
            engine.parse(a_query);
            sql_query = engine.getSQL();
        }

if (sql_query==null) {
            System.out.println("No SQL for " + a_query);
            return;
        }
        System.out.println("SQL query: " + sql_query);

sqlText.setText(sql_query);

try {
            engine.createResultSet(sql_query, databaseNames[0], userNames[0], passwords[0]);
            putText("Query results:\n", false);
            String data = engine.getRows(sql_query, databaseNames[0], userNames[0], passwords[0]);
            putText(engine.toEnglish(data) + "\n", false);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

private boolean usingAWT = false;

private boolean loadSynonyms = true;
    private boolean loadDB = true;

private void addSynonym(String def_string) {
        int pos = def_string.indexOf("=");
        String description = def_string.substring(0,pos);
        String column = def_string.substring(pos+1);
        if (engine!=null) {
            engine.addSynonym(column, description);
        }
    }

// Use an inner classes for mouse event handling:
    class MouseQuery extends MouseAdapter implements Serializable {
        // The magic: access the public query method in the
        // containing class:
        synchronized public void mouseReleased(MouseEvent mevt) {
            query();
        }
    }

// Use an inner classes for mouse event handling:
    class MouseSelect1 extends MouseAdapter implements Serializable {
        synchronized public void mouseReleased(MouseEvent mevt) {
            if (list1_last_selection != list1.getSelectedIndex()) {
                list1_last_selection = list1.getSelectedIndex();
                String s="";
                if (list1_last_selection >= 0 && list1_last_selection < tableLists.length) {
                    s = tableLists[list1_last_selection];
                }
                System.out.println("s=" + s);
                String temp [] = Util.parseStrings(s);
                list2.removeAll();  list3.removeAll();
                for (int i=0; i<temp.length; i++) list2.addItem(temp[i]);
            }

}
    }

// Use an inner classes for mouse event handling:
    class MouseSelect2 extends MouseAdapter implements Serializable {
        synchronized public void mouseReleased(MouseEvent mevt) {
            if (list2_last_selection != list2.getSelectedIndex()) {
                list2_last_selection = list2.getSelectedIndex();
                list3.removeAll();
                String sel1 [] = list1.getSelectedItems();
                if (sel1!=null) {
                    if (sel1.length>0) {
                        String sel2 [] = list2.getSelectedItems();
                        if (sel2!=null) {
                            if (sel2.length>0) {
                                String user="";
                                String pass="";
                                if (list1_last_selection >= 0 && list1_last_selection < userNames.length) {
                                    user = userNames[list1_last_selection];
                                }
                                try {

String cols[] = engine.getColumnNames(sel2[0],
                                                                          sel1[0], user, pass);

if (cols!=null) {
                                        for (int j=0; j<cols.length; j++) {
                                            list3.addItem(cols[j]);
                                        }
                                    }
                                } catch (Exception e) { }

}
                        }
                    }
                }
            }
        }
    }

// Use an inner classes for mouse event handling:
    class MouseSelect3 extends MouseAdapter implements Serializable {
        synchronized public void mouseReleased(MouseEvent mevt) {
            if (list3_last_selection != list3.getSelectedIndex()) {
                System.out.println("TESTING 3rd list selection");
                list3_last_selection = list3.getSelectedIndex();
                String [] sel1x = list1.getSelectedItems();
                if (sel1x!=null) {
                    if (sel1x.length>0) {
                        String sel2 [] = list2.getSelectedItems();
                        String sel3 [] = list3.getSelectedItems();
                        if (sel2!=null && sel3!=null) {
                            if (sel3.length>0) {
                                String user="";
                                String pass="";
                                if (list1_last_selection >= 0 && list1_last_selection < userNames.length) {
                                    user = userNames[list1_last_selection];
                                    pass = passwords[list1_last_selection];
                                }
                                try {

String r = engine.getRows("SELECT " + sel3[0] + " FROM " + sel2[0],
                                                              sel1x[0],
                                                              user,
                                                              pass);
                                    if (r==null)  return;
                                    putText(r + "\n", false);
                                } catch (Exception e) { }
                            }
                        }
                    }
                }
            }
        }
    }

private Choice choice;
    transient private boolean choiceChanged = false;

class ChoiceListener implements ItemListener {
        public void itemStateChanged(ItemEvent ie) {
            System.out.println("choice menu: " + ie.paramString());
            System.out.println("choice menu: " + (String)ie.getItem());
            String sel = (String)ie.getItem();
            if (sel.equals("Examples"))  return;
            inText(sel, true);
            if (choiceChanged==false) {
                choice.remove(0);
                choiceChanged=true;
            }
            System.out.println("choice menu: <returning>");
        }
    }

private Help help;

class MouseHelp extends MouseAdapter implements Serializable {
        public void mouseReleased(MouseEvent mevt) {
            help.setVisible(true);
        }
    }

public static  void main(String args[]) {
        Frame f = new Frame();
        NLBean sc = new NLBean();
        f.add(sc);
        f.setTitle("NLBean version 4.0");
        f.pack();
        f.show();
        f.addWindowListener(new java.awt.event.WindowAdapter() {
            public void windowClosing(WindowEvent e) {
                System.exit(0);
            }
        });
    }

private TextArea outputText;
    private /* Smart */ TextField inputText;
    private TextArea sqlText;

private java.awt.List list1, list2, list3;
    private int list1_last_selection, list2_last_selection,  list3_last_selection;

// properties for specifying a few sample natural language
    // queries to place at the beginning of the 'Example'
    // 'choice' control.  NOTE: this program automatically
    // adds additional examples that it generates from examining
    // the field names of the database tables.

private String [] examples = {
        "list email address where name equals Mark",
        "list salary where employee name equals Mark",
        "list salary where hire date is after 1993/1/5 and employee name equals Mark",
        "list name, phone number, and email address where name equals Mark",
        "list employee name, salary, and hire date where hire date is after January 10, 1993",
        "list salary where hire date is after January 1, 1993 or employee name equals Carol",
        "list product name where cost is less than $20"
    };

// Set up for synonyms:
    private String [] synonyms = {
        "employee name=EmpName",
        "hire date=HireDate",
        "phone number=PhoneNumber",
        "email address=Email",
        "product name=productname",
        "products=productname",
        "product=productname"
    };

// for four properties each for up to three databases:

//    Demo database information
    private String [] databaseNames = {"jdbc:idb:database/nlbean.prp"};
    private String [] userNames = {"Admin"};
    private String [] passwords = {"sammy"};
    private String [] tableLists= {"NameTable;products;Employees"};

//
    //            Inner class to handle text input field:
    //

transient protected int startWordPos=0, stopWordPos=0;

class SmartTextField extends TextField implements Serializable {
        public SmartTextField(String s, int width) {
            super(s, width);
            helper();
        }
        public SmartTextField(String s) {
            super(s);
            helper();
        }
        public SmartTextField() {
            super();
            helper();
        }
        private void helper() {
            addMouseListener(new MouseText());
            addKeyListener(new MyKeyAdapter());
            setEditable(true);
            setFont(new Font("default", Font.PLAIN, 12));
            words = new String[20];
            for (int i=0; i<20; i++) words[i]="";
            num_words=0;
        }

transient private String words[] = null;
        transient int num_words;

public String getWord(int charPos) {
            startWordPos=stopWordPos=-1;
            String s = getText();
            // find start of word:
            int start = charPos-1;
            try {
                while (start >= 0 && s.charAt(start)!=' ') {
                    start--;
                }
                start++;
            } catch (Exception e) {}
            // find the end of word:
            int stop=charPos;
            try {
                while (stop < s.length() && s.charAt(stop)!=' ') {
                    stop++;
                }
            } catch (Exception e) {}
            if (start<0) start=0;
            //if (stop>s.length()-1) stop=s.length()-1;
            System.out.println("getWord(): start=" + start + ", stop=" + stop);
            //if (stop > s.length() - 2) stop = s.length() - 1;
            int stp = stop;
            if (stop>my_char_pos) {
                stop=my_char_pos;
                System.out.println("    stop reset to " + stop);
            }
            if (start < stop) {
                startWordPos=start;
                stopWordPos=stp;

System.out.println("getWord() returning: |" + s.substring(start, stop) + "|");

return s.substring(start, stop);
            }
            else return "";
        }

public void setUpperCase(int charPos) {
            String s = getText();
            // find start of word:
            int start = charPos-1;
            try {
                while (start >= 0 && s.charAt(start)!=' ') {
                    start--;
                }
                start++;
            } catch (Exception e) {}
            // find the end of word:
            int stop=charPos;
            try {
                while (stop < s.length() && s.charAt(stop)!=' ') {
                    stop++;
                }
            } catch (Exception e) {}
            if (start<0) start=0;
            if (stop>s.length()) stop=s.length();
            if (stop>my_char_pos) stop=my_char_pos;
            if (start < stop) {
                StringBuffer sb = new StringBuffer(s);
                for (int i=start; i<stop; i++) {
                    char ch = sb.charAt(i);
                    if (ch >= 'a' && ch <= 'z') ch += 'A' - 'a';
                    sb.setCharAt(i, ch);
                }
                setText(new String(sb));
                setCaretPosition(stop);
            }
        }

transient private int charWidth = -1;
        transient private FontMetrics fm = null;

public void paint(Graphics g) {
            super.paint(g);
            if (fm==null) {
                fm = g.getFontMetrics();
            }
        }

private int getPixelWidth(String s) {
            if (fm==null) return 1;
            return fm.stringWidth(s);
        }

transient private int my_char_pos=0;

public int getCharPos(int pixel) {
            String s = getText();
            my_char_pos=-1;
            if (getPixelWidth(s) < pixel)  return -1;  // to right of text
            int len = s.length();
            for (int i=1; i<len; i++) {
                if (getPixelWidth(s.substring(0, i)) > pixel - 12)  {
                    my_char_pos=i;
                    return i;
                }
            }
            my_char_pos = len-1;
            return len-1;
        }

// Handle mouse events over the input text window:
        class MouseText extends MouseAdapter implements Serializable {
            public void mousePressed(MouseEvent mevt) {
                int x = mevt.getX();
                int charPos = getCharPos(x);
                if (charPos<0)  return;
                my_char_pos = 1000; // special for mouse click only!
                String word = getWord(charPos);

System.out.println("MouseText.mousePressed(): getWord returned: " + word);

if (word.length() < 1) return;
                System.out.println("x=" + x + ", charPos=" + charPos +
                                   ", word=" + word);
            }
        }

class MyKeyAdapter extends KeyAdapter implements Serializable {
            public void keyTyped(KeyEvent ke) {
                if (ke.getKeyChar()==KeyEvent.VK_SPACE ||
                    ke.getKeyChar()==KeyEvent.VK_COMMA ||
                    ke.getKeyChar()==KeyEvent.VK_PERIOD)
                    {
                        // Handle new word:
                        //System.out.println("New word. all text:" + getText());
                        int charPos = getCaretPosition();
                        my_char_pos = charPos;
                        System.out.println("** charPos=" + charPos);
                        String word = getWord(charPos);
                        System.out.println("** word =" + word);
                    }
            }
        }
    }
}

(google search) (amazon search)
second