www.carfield.com.hk

NLBean.java

2001-12-08T16:00:00Z

// 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);
                    }
            }
        }
    }
}

NLEngine.java

2001-12-08T16:00:00Z

// nlbean.NLEngineLocal.java
//
// Copyright 1997, 1999 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;
import java.rmi.*;
import java.rmi.server.UnicastRemoteObject;

public class NLEngine implements Serializable {

    public NLEngine() {

        // Create a DBInfo object and initialize it with the current
        // database properties:
        dbinfo = new DBInfo();

        nlp = new NLP(dbinfo);

     //   lexicon_data = nlp.lexicon_data;
    }

    public void addDB(String name, String user, String passwd, String tbls) {
        if (numDB < 4) {
            databaseName[numDB] = name;
            userName[numDB] = user;
            password[numDB] = passwd;
            tableList[numDB] = tbls;
            numDB++;
        }
    }

    public void initDB() {
        for (int d=0; d<numDB; d++) {
            if (databaseName[d]!=null) {
                String tl[] = Util.parseStrings(tableList[d]);
                if (tl!=null) {
                    for (int i=0; i<tl.length; i++) {
                        try {
                            String cols[] = DBInterface.getColumnNames(tl[i],
                                                                       databaseName[d],
                                                                       userName[d],
                                                                       password[d]);
                            if (cols!=null) {
                               // lexicon_data.addTableName(tl[i]);
                                dbinfo.addTable(tl[i], databaseName[d], userName[d], password[d], cols);
                               // if (lexicon_data!=null) {
                               //     for (int ii=0; ii<cols.length; ii++) {
                               //         System.out.println("adding column name to lexicon: " + cols[ii]);
                               //         lexicon_data.addColumnName(cols[ii]);
                              //      }
                              //  } else {
                              //      System.out.println("null lexicon_data");
                              //  }
                            }
                        } catch (Exception e) { }
                    }
                }
            }
        }

        dbinfo.debug();
    }

    //                     NLP data and methods:

    public void parse(String s) {

        // Remove any commas from the input text:
        for (int i=0; i<20; i++) {
            int idx = s.indexOf(",");
            if (idx>-1) {
                s = s.substring(0,idx) + s.substring(idx+1);
            } else {
                break;
            }
        }
        // Remove any periods from the input text:
        for (int i=0; i<10; i++) {
            int idx = s.indexOf(".");
            if (idx>-1) {
                s = s.substring(0,idx) + s.substring(idx+1);
            } else {
                break;
            }
        }

        // remove extra spaces and convert to lower case:
        s = " " + Util.removeExtraSpaces(s).toLowerCase() + " ";

        // before calling the NLP class parse method, we
        // need to replace synonyms:

        numSynReplacements = 0;
        for (int i=0; i<numSynonyms; i++) {
            // allow for multiple uses of the same synonym:
            for (int repeat=0; repeat<4; repeat++) {
                int idx = s.indexOf(synonymDescription[i]);
                if (idx>-1) {
                    s = s.substring(0,idx+1) +
                        synonymColumnName[i] + 
                        s.substring(idx + synonymDescription[i].length() - 1);
                    syns[numSynReplacements] = synonymColumnName[i];
                    origs[numSynReplacements] = synonymDescription[i];
                    numSynReplacements++;
                } else {
                    break;
                }
            }
        }

        // remove extra spaces (just to make sure!):
        s = Util.removeExtraSpaces(s);

        nlp.parse(s);
    }

    // See if a word is "known" (this includes table and column names):

   // public boolean goodWord(String s) {
   //     if (lexicon_data.wordInLexicon(s.toLowerCase())) return true;
   //     return false;
  //  }

    public String getSQL() {
        return nlp.getSQL();
    }
    private int num_rows_from_database = 0;
    public String [] breakLines(String s) {
        String [] ret = new String[40];
        int count = 0;
        num_rows_from_database = 0;
        try {
            StringReader sr = new StringReader(s);
            BufferedReader br = new BufferedReader(sr);
            while (true) {
                String s2 = br.readLine();
                if (s2 == null || s2.length() < 1)  break;
                num_rows_from_database++;
                // change for InstantDB: comma separated terms:
                int index = 0;
                while (true) {
                    index = s2.indexOf(",");
                    if (index == -1) {
                        if (count > 38) break;
                        ret[count++] = s2.trim();
                        break;
                    } else {
                        if (count > 38) break;
                        ret[count++] = s2.substring(0, index);
                        s2 = s2.substring(index + 1);
                    }
                }
            }
            String [] ret2 = new String[count];
            for (int i=0; i<count; i++) ret2[i] = ret[i];
            return ret2;
        } catch (Exception e) { }
        return null;
    }

    public String toEnglish(String r) {
        System.out.println("NLEngineLocal.toEnglish(" + r + ")");
        return nlp.toEnglish(breakLines(r), syns, origs,
                             numSynReplacements, num_rows_from_database);
    }

    public void createResultSet(String sql_query, String database,
                                String user, String password) {
        //SQL.createResultSet(sql_query, database, user, password);
    }

    public String [] getColumnNames(String sql_query, String database,
                                    String user, String password) {
        try {
            return DBInterface.getColumnNames(sql_query, database,
                                              user, password);
        } catch (Exception e) { }
        return null;
    }

    public String getRows(String sql_query, String database,
                          String user, String password) {
        try {
            return DBInterface.query(sql_query, database,
                                     user, password);
        } catch (Exception e) { }
        return null;
    }

    public  DBInfo dbinfo;
    private NLP nlp;

    // Allow developers to define a few synonyms for a particular
    // application:
    private String synonymColumnName[] = new String[11];
    private String synonymDescription[] = new String[11];
    private int numSynonyms = 0;

    // For use in generating nglish output for queries:
    private String syns[] = new String[11];
    private String origs[] = new String[11];
    private int numSynReplacements=0;

    public void clearSynonyms() {
        numSynonyms = 0;
    }

    public void addSynonym(String column, String description) {
        if (numSynonyms<10) {
            synonymColumnName[numSynonyms] = column;
            synonymDescription[numSynonyms] = " " + description + " ";
            numSynonyms++;
        }
    }

    // for four properties each for up to five databases:
    private String databaseName[] = new String[5];
    private String userName[] = new String[5];
    private String password[] = new String[5];
    private String tableList[] = new String[5];
    private int numDB=0;

}

SmartDate.java

2000-05-28T16:00:00Z

// nlbean.SmartDate.java
//
// Copyright 1997, 1999 Mark Watson.
//

package nlbean;

import java.text.*;
import java.util.*;
import java.io.*;

// Utility class to handle dates in fairly arbitrary natural language formats:
class SmartDate implements Serializable {
  private java.util.Date date = null;
  private Calendar calendar = null;
  public SmartDate(String s) {
    System.out.println("\n*****  SmartDate(" + s + ")");
    // Try to create a 'SimpleDate' parse string for this string:
    SimpleDateFormat sdf;
    try {
      if (date == null) {
	sdf = new SimpleDateFormat("MMM d yyyy");
	try {
	  date = sdf.parse(s);
	} catch (Exception pe) { date = null; }
      }
      if (date == null) {
	sdf = new SimpleDateFormat("yyyy MM dd hh mm ss");
	try {
	  date = sdf.parse(s);
	} catch (Exception pe) { date = null; }
      }
      if (date == null) {
	sdf = new SimpleDateFormat("yyyy mm dd");
	try {
	  date = sdf.parse(s);
	} catch (Exception pe) { date = null; }
      }
      if (date == null) {
	sdf = new SimpleDateFormat("yy mm dd");
	try {
	  date = sdf.parse(s);
	} catch (Exception pe) { date = null; }
      }
      if (date == null) {
	sdf = new SimpleDateFormat("EEE MMM d yyyy");
	try {
	  date = sdf.parse(s);
	} catch (Exception pe) { date = null; }
      }
      if (date == null) {
	sdf = new SimpleDateFormat("EEE MMM d ''yy");
	try {
	  date = sdf.parse(s);
	} catch (Exception pe) { date = null; }
      }
      if (date == null) {
	sdf = new SimpleDateFormat("yyy-mm-dd hh:mm:ss");
	try {
	  date = sdf.parse(s);
	} catch (Exception pe) { date = null; }
      }
      if (date == null) {
	sdf = new SimpleDateFormat("MMM d ''yy");
	try {
	  date = sdf.parse(s);
	} catch (Exception pe) { date = null; }
      }
    } catch (Exception e) {
      System.out.println("Parse error: " + s + ", " + e);
    }
    if (date!=null) {
      calendar = GregorianCalendar.getInstance();
      calendar.setTime(date);
      //System.out.print("ERA: " + calendar.get(Calendar.ERA) + ". ");
      System.out.print("YEAR: " + calendar.get(Calendar.YEAR) + ". ");
      System.out.print("MONTH: " + calendar.get(Calendar.MONTH) + ". ");
      //System.out.print("WEEK_OF_YEAR: " + calendar.get(Calendar.WEEK_OF_YEAR) + ". ");
      //System.out.print("WEEK_OF_MONTH: " + calendar.get(Calendar.WEEK_OF_MONTH) + ". ");
      System.out.print("DATE: " + calendar.get(Calendar.DATE) + ". ");
      System.out.print("DAY_OF_MONTH: " + calendar.get(Calendar.DAY_OF_MONTH) + ". ");
      //System.out.print("DAY_OF_YEAR: " + calendar.get(Calendar.DAY_OF_YEAR) + ". ");
      //System.out.print("DAY_OF_WEEK: " + calendar.get(Calendar.DAY_OF_WEEK) + ". ");
      //System.out.print("DAY_OF_WEEK_IN_MONTH: "
      //                   + calendar.get(Calendar.DAY_OF_WEEK_IN_MONTH) + ". ");
      //System.out.print("AM_PM: " + calendar.get(Calendar.AM_PM) + ". ");
      //System.out.print("HOUR: " + calendar.get(Calendar.HOUR) + ". ");
      //System.out.print("HOUR_OF_DAY: " + calendar.get(Calendar.HOUR_OF_DAY) + ". ");
      //System.out.print("MINUTE: " + calendar.get(Calendar.MINUTE) + ". ");
      //System.out.print("SECOND: " + calendar.get(Calendar.SECOND) + ". ");
      //System.out.print("MILLISECOND: " + calendar.get(Calendar.MILLISECOND) + ". ");
      System.out.println("");
    } else {
      System.out.println("Parse error: " + s);
    }
  }
  public int getYear() {
    if (date!=null && calendar!=null) {
      return calendar.get(Calendar.YEAR);
    }
    return 0;
  }
  public int getMonth() {
    if (date!=null && calendar!=null) {
      return calendar.get(Calendar.MONTH);
    }
    return 0;
  }
  public int getDayOfMonth() {
    if (date!=null && calendar!=null) {
      return calendar.get(Calendar.DAY_OF_MONTH);
    }
    return 0;
  }
  public long getMilliseconds() {
    if (date!=null && calendar!=null) {
      return calendar.get(Calendar.MILLISECOND);
    }
    return 0;
  }
  public boolean valid() {
    if (calendar==null) return false;
    if (calendar.get(Calendar.MILLISECOND)!=0) return true;
    if (calendar.get(Calendar.YEAR)!=0) return true;
    if (calendar.get(Calendar.DAY_OF_MONTH)!=0) return true;
    if (calendar.get(Calendar.DATE)!=0) return true;
    return false;
  }
  public String toString() {
    if (valid()==false) return "<not valid date>";
    //return getMonth() + "/" + getDayOfMonth()+1 + "/" + getYear();
    int month = getMonth() + 1;
    return getYear() + "-" + month + "-" + getDayOfMonth();
    //SimpleDateFormat formatter
    //  = new SimpleDateFormat ("mm/dd/yyyy");
    //return formatter.format(date);
  }
}

nlbean.jar

2000-05-28T16:00:00Z

nlbean.jrl

2000-05-28T16:00:00Z

readme.txt

2000-05-28T16:00:00Z

Thank you for downloading the new version 4 of the NLBean.

This version is released under an Open Source license agreement
and is free for private and commercial use.

Peter Hearty kindly gave me permission to include the compiled
version of his VERY COOL pure Java database program InstantDB.

Please note that InstantDB is only free for non-commercial
use! Peter charges a very reasonable fee for commercial
use of InstantDB. Please visit his web site at
http://instantdb.enhydra.org/ for details. Thanks Peter!
(The directories 'jdbc' and 'db' contain Peter's compiled
code and the directory 'database' contains a small test
database created with the nlbean.MakeTestDB program.)

The source code and compiled files for the NLBean are in
the directory 'nlbean'.

Some of you may have noticed a large jump in version number.
Version 4 is a major cleanup of the NLBean: it needed it!
(I had originally written the NLBean quite quickly for my own
use, and the old source code was very ugly.) Anyway, I had a
lot of experimental code in teh NLBean, which I have removed.

There is still some more cleanup work to be done on the
source code, so look for a future update with a small
version number change.

To run the NLBean, use the run.bat file. There are several
example queries in a selection list. If you are typing
a query and the word that you just typed changes to all
uppercase characters, that means that the NLBean does not
understand that word; click the mouse on the upper case word
to get a popup window with suggested alternative words.

The c.bat command file can be used to re-compile everything,
but I included compiled class files so you will not have to
do this until you edit the source.

The NLBean is packaged up as a demo. The file nlbean.NLBean.java
contains a hardwired database, usename, and password. In a future
version, I will use a small config file to change the database setup
so that you don't have to edit the code to use another database system.

Enjoy!

-Mark Watson www.markwatson.com

trace.log

2000-05-28T16:00:00Z

DBInfo.java

2000-05-10T16:00:00Z

// nlbean.DBInfo.java
//
// Copyright 1997, 1998, 19999 Mark Watson.
//

package nlbean;

import java.net.*;
import java.io.*;
import java.awt.*;
import java.awt.event.*;
import java.beans.*;
import java.sql.*;

public class DBInfo implements Serializable {
    public DBInfo() {
        numTables=0;
        //tables = new DBTable[10]; // arbitrary maximum
        tableNames = new String[10];
        columnNames = new String[10][];
        databaseNames = new String[10];
        userNames = new String[10];
        passwords = new String[10];
        temp = new String[20];
    }

    public void clearTables() { numTables=0; }

    public void addTable(String a_name, String data_base,
                         String user_name, String a_password,
                         String col_names[])
    {
        if (numTables >= 8) {
            System.out.println("Too many tables");
            System.exit(1);
        }
        tableNames[numTables] = a_name;
        columnNames[numTables] = col_names;
        databaseNames[numTables] = data_base;
        userNames[numTables] = user_name;
        passwords[numTables] = a_password;
        numTables++;
    }

    public void debug() {
        for (int i=0; i<numTables; i++) {
            System.out.print("Table " + tableNames[i] + ": " +
                             databaseNames[i] + ", " +
                             userNames[i] + ": ");
            for (int j=0; j<columnNames[i].length; j++) {
                System.out.print(columnNames[i][j]);
                if (j!=columnNames[i].length-1) System.out.print(", ");
            }
            System.out.println("");
        }
    }

    public boolean isTable(String s) {
        for (int i=0; i<numTables; i++) {
            if (tableNames[i].equalsIgnoreCase(s)) return true;
        }
        return false;
    }

    public boolean isColumn(String s) {
        for (int i=0; i<numTables; i++) {
            for (int j=0; j<columnNames[i].length; j++) {
                if (columnNames[i][j].equalsIgnoreCase(s)) return true;
            }
        }
        return false;
    }

    public String[] findColumnName(String cn) { // return table names
        int count=0;
        for (int i=0; i<numTables; i++) {
            for (int j=0; j<columnNames[i].length; j++) {
                if (cn.equalsIgnoreCase(columnNames[i][j])) {
                    temp[count++] = tableNames[i];
                }
            }
        }
        if (count==0)  return null;
        String ret[] = new String[count];
        for (int i=0; i<count; i++) ret[i] = temp[i];
        return ret;
    }

    //public DBTable tables[] = null;
    public String tableNames[] = null;
    public String columnNames[][] = null;
    public String databaseNames[] = null;
    public String userNames[] = null;
    public String passwords[] = null;

    public int numTables = 0;
    protected String temp[] = null;
}

DBInterface.java

2000-05-10T16:00:00Z

// DBInterface.java
// Static class for easy access to
// ODBC compliant databases via JDBC.
// (From my UML book)
// Copyright Mark Watson, 1997

// 2 public static methods: Query and Update

// Note: JDCB and ODBC driver initialization
// occurs automatically the first time either
// Query or Update is called.

// Note on URL specifying database driver:
// If you install the JavaSoft JDK1.1, and
// set up ODBC, use the driver url="jdbc:odbc:XXX"
// where XXX is the name of the database as
// defined when you run the ODBC control panel
// (in Windows 95/98/NT).

package nlbean;

import java.sql.*;
import jdbc.*;

public class DBInterface
{
    // url=database driver name + database name
    static public String query(String a_query, String url,
                               String user_id, String passwd) {
        String results = "";
        System.out.println("++ query: " + a_query);
        
        try {

            doInit();

            // Connect to the JDBC driver:
            Connection con =
                DriverManager.getConnection(url, user_id, passwd);
            checkConnection(con.getWarnings()); // connection OK?

            Statement stmt = con.createStatement();

            // Submit a query:
            ResultSet rs = null;
            try {
                rs = stmt.executeQuery(a_query);
            } catch (SQLException se) {
                System.out.println("NO result set");
            }

            if (rs != null) {
                // Display all columns and rows from the result set
                results = resultSetToString(rs);

                // Close the result set
                rs.close();
            }

            // Close the statement
            stmt.close();

            // Close the connection
            con.close();
        }
        catch (SQLException ex) {
            while (ex != null) {
                System.out.println("SQL error message:  " + ex.getMessage());
                ex = ex.getNextException();
                System.out.println("");
                results = ex.getMessage();
            }
        }
        catch (java.lang.Exception ex) {
            ex.printStackTrace();
            results = ex.getMessage();
        }
        return results;
    }

    static public String update(String a_query, String url,
                                String user_id, String passwd) {
        System.out.println("\n\n++++++++++++++++++++++\nUpdate: " +
                           a_query + "\n");
        String results = "";

        try {

            doInit();

            // Find a driver:
            Connection con =
                DriverManager.getConnection(url, user_id, passwd);
            checkConnection(con.getWarnings()); // connection OK?

            Statement stmt = con.createStatement();

            try {
                int n = stmt.executeUpdate(a_query);
                results = "UPDATE affected " + n + " rows.";
            } catch (SQLException se) {
                results = "UPDATE had no effect on database.";
            }

            // Close the statement
            stmt.close();

            // Close the connection
            con.close();

        }
        catch (SQLException ex) {
            while (ex != null) {
                System.out.println("SQL error message:  " + ex.getMessage());
                ex = ex.getNextException();
                System.out.println("");
                results = ex.getMessage();
            }
        }
        catch (java.lang.Exception ex) {
            ex.printStackTrace();
            results = ex.getMessage();
        }
        System.out.println("DBInterface.Select() results: " + results);
        return results;
    }

    static private boolean needToInit = true;

    static private void doInit() {
        if (needToInit) {
            try {
                // Load the JDBC driver
                Class.forName("jdbc.idbDriver");
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                //DriverManager.setLogStream(System.out); // uncomment for debug printout
            } catch (Exception e) {
                System.out.println("Could not set up JDBC: " + e);
            }
            needToInit=false;
        }
    }


    static private String resultSetToString(ResultSet rs) throws SQLException {
        int i;
        StringBuffer outputText = new StringBuffer();
        int numCols = rs.getMetaData().getColumnCount();
        boolean more = rs.next();
        while (more) {
            for (i=1; i<=numCols; i++) {
                if (i > 1) outputText.append(",");
                outputText.append(rs.getString(i));
            }
            if (i!=numCols)  outputText.append("\n");
            more = rs.next();
        }
        return new String(outputText);
    }

    static public String [] getColumnNames(String table_name, String url, String user_id, String passwd) {
        String ret [] = null;
        try {

            doInit();

            // Connect to the JDBC driver:
            Connection con =
                DriverManager.getConnection(url, user_id, passwd);
            checkConnection(con.getWarnings()); // connection OK?

            Statement stmt = con.createStatement();

            // Submit a query:
            ResultSet rs = null;
            try {
                rs = stmt.executeQuery("select * from " + table_name);
            } catch (SQLException se) {
                System.out.println("NO result set");
                return null;  // error return
            }

            StringBuffer outputText = new StringBuffer();
            ResultSetMetaData meta = rs.getMetaData();
            int numCols = meta.getColumnCount();
            ret = new String[numCols];
            for (int i=0; i<numCols; i++) {
                ret[i] = meta.getColumnLabel(i + 1);
            }
            rs.close();
            stmt.close();
            con.close();
        } catch (Exception e) {
            System.out.println("Error getting column names: " + e);
            return null;
        }
        return ret;
    }


    static private boolean checkConnection(SQLWarning warning)
        throws SQLException  {
        boolean ret = false;
        if (warning != null) {
            System.out.println("\n *** Warning ***\n");
            ret = true;
            while (warning != null) {
                System.out.println("Message " + warning.getMessage());
                warning = warning.getNextWarning();
            }
        }
        return ret;
    }
}

MakeTestDB.java

2000-05-10T16:00:00Z

package nlbean;

import java.io.*;
import java.util.*;
import jdbc.*;

public class MakeTestDB {

    static public void main(String [] args) {
        if (args.length < 1) {
            System.out.println("usage: 'java nlbean.MakeTestDB delete' to recreate database");
            System.out.println("usage: 'java nlbean.MakeTestDB add' to add test data to the database");
            System.out.println("usage: 'java nlbean.MakeTestDB list' to list the database");
            System.exit(1);
        }
        if (args[0].equalsIgnoreCase("delete")) {
            String s;
            /** NameTable */
            try {
                s = "Drop TABLE NameTable";
                s = DBInterface.update(s, "jdbc:idb:database/nlbean.prp", "", "");
            } catch (Exception e) { }
            s = "Create TABLE NameTable (name char(30), email char(35), " +
                "PhoneNumber char(30), misc char(40))";
            s = DBInterface.update(s, "jdbc:idb:database/nlbean.prp", "", "");
            System.out.println(s);
            /** products table */
            try {
                s = "Drop TABLE products";
                s = DBInterface.update(s, "jdbc:idb:database/nlbean.prp", "", "");
            } catch (Exception e) { }
            s = "Create TABLE products (ID LONG, productname char(40), " +
                "cost FLOAT)";
            s = DBInterface.update(s, "jdbc:idb:database/nlbean.prp", "", "");
            System.out.println(s);
            /** Employees table */
            try {
                s = "Drop TABLE Employees";
                s = DBInterface.update(s, "jdbc:idb:database/nlbean.prp", "", "");
            } catch (Exception e) { }
            s = "Create TABLE Employees (ID LONG, EmpName char(40), " +
                "HireDate char(20), Salary FLOAT)";
            s = DBInterface.update(s, "jdbc:idb:database/nlbean.prp", "", "");
            System.out.println(s);
        } else if (args[0].equalsIgnoreCase("add")) {
            /** Employees */
            String s =
                "INSERT into Employees VALUES (1, 'mark', '1994-1/-', 22000.0)";
            System.out.println(s);
            s = DBInterface.update(s, "jdbc:idb:database/nlbean.prp", "", "");
            s = "INSERT into Employees VALUES (2, 'carol', '1994-2-10', 23000.0)";
            System.out.println(s);
            s = DBInterface.update(s, "jdbc:idb:database/nlbean.prp", "", "");

            /** products */
            s = "INSERT into products VALUES (1, 'WebPix', 7.0)";
            System.out.println(s);
            s = DBInterface.update(s, "jdbc:idb:database/nlbean.prp", "", "");
            s = "INSERT into products VALUES (2, 'DBmaster', 19.0)";
            System.out.println(s);
            s = DBInterface.update(s, "jdbc:idb:database/nlbean.prp", "", "");

            /** NameTable */
            s = "INSERT into NameTable VALUES ('mark', 'markw@markwatson.com', '123 45678', 'test2')";
            System.out.println(s);
            s = DBInterface.update(s, "jdbc:idb:database/nlbean.prp", "", "");
            s = "INSERT into NameTable VALUES ('carol', 'carol@markwatson.com', '123 45678', 'test2')";
            System.out.println(s);
            s = DBInterface.update(s, "jdbc:idb:database/nlbean.prp", "", "");

        } else if (args[0].equalsIgnoreCase("list")) {
            String s;
            s = "Select Employees.EmpName from Employees";
            s = DBInterface.query(s, "jdbc:idb:database/nlbean.prp", "", "");
            System.out.println(s); 
            s = "Select * from products";
            s = DBInterface.query(s, "jdbc:idb:database/nlbean.prp", "", "");
            System.out.println(s); 
            s = "Select * from NameTable";
            s = DBInterface.query(s, "jdbc:idb:database/nlbean.prp", "", "");
            System.out.println(s); 
        } else {
            System.out.println("unknown command line argument: " + args[0]);
        }
    }
}

NLP.java