/*
 * @(#)Ksdb.java	0.2 00/08/04
 *
 * Copyright 2000 by Carnegie Mellon, All rights reserved.
 */

package ksdb;
import java.io.*;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.*;
import javax.swing.*;

import edu.cmu.lti.kantoo.analyzer.*;
import edu.cmu.lti.kantoo.network.*;
import edu.cmu.lti.kantoo.shared.*;


/**
 * Interface to the KANTOO Sentence Database
 */
public class Ksdb {
  // These functions are not thread-safe!
  // They are 'process-safe'...that is, two distinct Ksdb objects that are
  // connected to the same database will not interfere with each other, even
  // if they live in separate threads and are used concurrently.
  // However, two threads should not have access to the same Ksdb object;
  // this will cause Bad Things<tm> to happen.


  /**
   * The actual connection to the SQL Database
   */
  private SQLConnection sql;

  /**
   * User connecting to KSDB
   */
  private int user;

  /**
   * User name connecting to KSDB
   */
  private String userName;

  /**
   * How long to wait for timeouts
   */
  private int timeout;

  /**
   * List of context-sensitive words...useful for identifying sentences
   * that rely on context.
   */
  private static String[] ContextWords;


  private int progressInterval = 0;

  /**
   * Establishes a KSDB connection. Authenticates user to KSDB.
   * @param  userName     Name of user connecting.
   * @throws SQLException if database is not available or user is invalid.
   */
  public Ksdb(String userName) throws SQLException {
    PropertyManager properties = new PropertyManager(this);
    this.progressInterval = Integer.parseInt( properties.get("progress.interval"));

    // Connect to SQL server
    this.timeout = Integer.parseInt( properties.get("timeout"));
    this.sql = new SQLConnection(new SQLConnectionConfig( properties, "KSDB"));
    sql.connect();

    // Authenticate the user now
    if (userName == null)
      throw new SQLException("No user specified!");
    this.userName = userName;
    user = sql.selectInt("id FROM User WHERE name="
			 + quote( userName.toLowerCase()));
    if (user == 0)
      throw new SQLException("User name=" + userName
			     + " doesn't exist in KSDB.");

    // Initialize the context-sensitive words
    StringTokenizer entries
      = new StringTokenizer( properties.get( "ContextWords"));
    ContextWords = new String[ entries.countTokens()];
    for (int i = 0; i < ContextWords.length; i++)
      ContextWords[i] = entries.nextToken();
  }


  /**
   * Wraps quotes around string
   * @param  string
   * @return string in quotes
   */
  public static String quote(String string) {
    return SQLConnection.quote( string);
  }

  /**
   * This merely sends an SQL SHOW command to the KSDB, and returns the
   * result. The predicate is attached to 'show'
   *
   * @param  predicate    Predicate of show.
   * @return              Result set, set to query result
   */
  public ResultSet show(String predicate) {
    sql.lock( timeout);
    ResultSet result = sql.show(predicate);
    sql.unlock();
    return result;
  }

  /**
   * This merely sends an SQL SELECT command to the KSDB, and returns the
   * result. The predicate is attached to 'select'.
   *
   * @param  predicate    Predicate of select.
   * @return              Result set, set to query result
   */
  public ResultSet select(String predicate) {
    sql.lock( timeout);
    ResultSet result = sql.select(predicate);
    sql.unlock();
    return result;
  }

  /**
   * This merely sends an SQL SELECT command to the KSDB, and returns the
   * result; as a single integer. The predicate is attached to 'select'.
   *
   * @param  predicate    Predicate of select.
   * @return              Result
   */
  public int selectInt(String predicate) {
    sql.lock( timeout);
    int result = sql.selectInt(predicate);
    sql.unlock();
    return result;
  }

  /**
   * This merely sends an SQL SELECT command to the KSDB, and returns the
   * result; as a timestamp. The predicate is attached to 'select'.
   *
   * @param predicate     Predicate of select.
   * @return              Result
   */
  public Timestamp selectTimestamp(String predicate) {
    sql.lock( timeout);
    Timestamp result = sql.selectTimestamp(predicate);
    sql.unlock();
    return result;
  }

  /**
   * This merely sends an SQL SELECT command to the KSDB, and returns the
   * result; as a single string. The predicate is attached to 'select'.
   *
   * @param predicate     Predicate of select.
   * @return              Result
   */
  public String selectString(String predicate) {
    sql.lock( timeout);
    String result = sql.selectString(predicate);
    sql.unlock();
    return result;
  }


  /// Insertion

  /**
   * Inserts an element into the Analyzer or Generator Version table.
   * @param analyzer If true, use Analyzer table, otherwise use Generator table
   * @param name     Name of Analyzer/Geneartor Version
   * @param language Language being used
   * @return         ID of version in KSDB
   */
  private int insertVersion(boolean analyzer, String name, String language) {
    String table = analyzer ? "Analyzer_Version" : "Generator_Version";
    int id = sql.insertID(table, "name=" + name + " AND language=" + language,
			  name + ", " + language + ", NULL");
    sql.updateTimestamp( table, id);
    return id;
  }

  /**
   * Inserts an element into the Context table.
   * @param document Document of context being inserted
   * @param position Index of sentence in document
   * @param sentence Sentence being inserted
   */
  private void insertContext(int document, int position, int sentence) {
    if (sql.insert("Context", "document=" + document +
		   " AND position=" + position,
		   document + ", " + position + ", " + sentence))
      sql.updateRefcount("Sentence", sentence, null);
  }

  /**
   * Inserts an element into the Analysis node.
   * @param sentence    Sentence being inserted
   * @param version     Analyzer version ID
   * @param interlingua IR being inserted
   */
  private void insertAnalysis(int sentence, int version, int interlingua) {
    if (sql.insertTimestamp("Analysis", "sentence=" + sentence
			    + " AND version=" + version
			    + " AND interlingua<=>" + interlingua,
			    sentence+ ", " + version + ", " + interlingua))
      sql.updateRefcount( "Interlingua", interlingua, null);
  }

  /**
   * Inserts an element into the Generation node.
   * @param interlingua IR being inserted
   * @param version     Generator version ID
   * @param translation Translation being inserted
   */
  private void insertGeneration(int interlingua, int version,int translation) {
    if (sql.insertTimestamp("Generation", "interlingua=" + interlingua
			    + " AND version=" + version
			    + " AND translation=" + translation,
			    interlingua + ", " + version + ", "+translation))
      sql.updateRefcount( "Translation", translation, null);
    sql.delete("Generation WHERE interlingua=" + interlingua
	       + " AND version=" + version
	       + " AND translation<>" + translation);
  }


  /// Deletion

  /**
   * Deletes a suite
   * @param suite Which suite to delete
   */
  private void deleteSuiteAux(int suite) {
    deleteSuiteDocument("suite=" + suite);
    sql.delete("Suite WHERE id=" + suite);
  }

  /**
   * Deletes all suite-documents that satisfy key
   * @param key Which items to delete
   */
  private void deleteSuiteDocument(String key) {
    sql.delete("Suite_Document WHERE " + key);
  }

  /**
   * Deletes a document
   * @param document Which document to delete
   */
  private void deleteDocumentAux(int document) {
    deleteContexts("document=" + document);
    deleteSuiteDocument("document=" + document);
    sql.delete("Document WHERE id=" + document);
  }

  /**
   * Deletes all contexts that satisfy key
   * @param key Which contexts to delete
   */
  private void deleteContexts(String key) {
    // First delete all Sentences
    ResultSet rs = sql.select("sentence FROM Context WHERE " + key);
    while (SQLConnection.next(rs))
      deleteSentence( SQLConnection.getInt(rs, 1));
    sql.delete("Context WHERE " + key);
  }

  /**
   * Deletes a sentence
   * @param sentence Which sentence to delete
   */
  private void deleteSentence(int sentence) {
    if (sql.deleteRefcount("Sentence", sentence))
      deleteAnalyses("sentence=" + sentence);
  }

  /**
   * Deletes all analyses that satisfy key
   * @param key Which analyses to delete
   */
  private void deleteAnalyses(String key) {
    // First delete all Interlinguas
    ResultSet rs = sql.select("interlingua FROM Analysis WHERE " + key);
    while (SQLConnection.next(rs))
      deleteInterlingua( SQLConnection.getInt(rs, 1));
    sql.delete("Analysis WHERE " + key);
  }

  /**
   * Deletes the interlingua
   * @param interlingua Which interlingua to delete
   */
  private void deleteInterlingua(int interlingua) {
    if (interlingua == 0) return;
    if (sql.deleteRefcount("Interlingua", interlingua))
      deleteGenerations("interlingua=" + interlingua);
  }

  /**
   * Deletes all generations that satisfy key
   * @param key Which generations to delete
   */
  private void deleteGenerations(String key) {
    // First delete all translations
    ResultSet rs = sql.select("translation FROM Generation WHERE " + key);
    while (SQLConnection.next(rs))
      deleteTranslation( SQLConnection.getInt(rs, 1));
    sql.delete("Generation WHERE " + key);
  }

  /**
   * Deletes the translation
   * @param translation Which translation to delete
   */
  private void deleteTranslation(int translation) {
    sql.deleteRefcount("Translation", translation);
  }


  /// Sentence lock management

  /**
   * Handy routine for reporting errors regarding sentence locks
   * @param  obtain       Whether we are getting a lock or releasing it
   * @param  write        Whether the user was attempting a write lock or not
   * @param  has          Whether user has lock or lacks it
   * @param  existing     Read/Write lock that already existed
   * @param  otherUser    User that had locked database
   * @throws SQLException always (never returns)
   */
  private void reportLockProblem(boolean obtain, boolean write, boolean has,
				 boolean existing, int otherUser)
    throws SQLException {
    String otherName = sql.selectString("name FROM User WHERE id=" +otherUser);
    throw new SQLException("Cannot " + (obtain ? "obtain" : "relinquish") + ' '
			   + (write ? "write" : "read")
			   + " lock for " + userName + " because " + otherName
			   + (has ? " has " : " has not ")
			   + (existing ? "write" : "read")
			   + "-locked database sentences!");
  }

  /**
   * Updates the locks for a user
   * @param user  ID of user
   * @param read  New value of read lock
   * @param write New value of write lock
   */
  private void updateUserLock(int user, boolean read, boolean write) {
    String set = sql.quote( (read && write) ? "read,write" :
			    read ? "read" : write ? "write" : "");
    sql.update("User SET sentence_lock=" + set + " WHERE id=" + user);
  }

  /**
   * This obtains a lock on the sentences in the db. The write-flag is a
   * boolean value; if true, it indicates a write lock; otherwise it
   * indicates a read lock.
   * <p>
   * If a read-lock is requested, the database should not be write-locked by
   * anyone else. If a write-lock is requested, the database should not be
   * read-locked or write-locked by anyone else.
   * <p>
   * Only returns if database were successfully locked...throws error
   * otherwise. If an error is thrown, no sentences are locked.
   * <p>
   * <b>Pre-Conditions:</b> The user is valid, and the query is a valid SQL
   * query. If read-locking, the database is not are write-locked by anyone,
   * including the user, and the database is already read-locked
   * by the user. If write-locking, the database is not write-locked by anyone
   * and the database is already read-locked by the user, but nobody else.<p>
   * <b>Post-Conditions:</b> The database is read- or write-locked.
   * <b>Invariant:</b> No changes to the rest of the database.
   * 
   * @param  write        If true, sentences may be edited. Otherwise
   *                      sentences may only be browsed.
   * @throws SQLException if lock cannot be obtained
   */
  public void lockSentences(boolean write) throws SQLException {
    sql.lock( timeout);
    try {
      boolean readLock = (sql.selectInt("id FROM User WHERE id=" + user
				 + " AND sentence_lock LIKE '%read%'") != 0);
      boolean writeLock = (sql.selectInt("id FROM User WHERE id=" + user
				  +" AND sentence_lock LIKE '%write%'") != 0);
      // First make sure our locks are correct

      // Bail if anyone has a write lock on the database
      int otherUser = sql.selectInt("id FROM User"
				    + " WHERE sentence_lock LIKE '%write%'");
      if (otherUser != 0)
	reportLockProblem( true, write, true, true, otherUser);

      // Now check read locks
      ResultSet users = sql.select("id FROM User WHERE id<>" + user
				    + " AND sentence_lock LIKE '%read%'");
      if (write) {
	// Make sure no one else has read locks.
	if (users.next()) 
	  reportLockProblem( true, write, true, false, users.getInt(1));
	if (!readLock)
	  reportLockProblem( true, write, false, false, user);

      } else // make sure user has not already read-locked database
	// No, it's ok for the user to read-lock sentences twice.
	//	if (readLock)
	//	  reportLockProblem( true, write, true, false, user);

      // OK, We can have our locks!
      if (write) writeLock = true;
      else readLock = true;
      updateUserLock( user, readLock, writeLock);
    } finally {sql.unlock();}
  }


  /**
   * Unlocks the database. The write-flag is a boolean value; if true, it
   * indicates a write lock; otherwise it indicates a read lock.
   * <p>
   * <b>Pre-Conditions:</b> The user is valid. Also, if
   * read-unlocking, the database is not write-locked by the user.<p>
   * <b>Post-Conditions:</b> All sentences are unlocked.<p>
   * <b>Invariant:</b> No changes to the rest of the database.
   *
   * @param  write        If true, write lock is being released; 
   *                      otherwise read lock is being released
   * @throws SQLException if lock cannot be released
   */
  public void unlockSentences(boolean write) throws SQLException {
    sql.lock( timeout);

    try {
      boolean readLock = (sql.selectInt("id FROM User WHERE id=" + user
				 + " AND sentence_lock LIKE '%read%'") != 0);
      boolean writeLock = (sql.selectInt("id FROM User WHERE id=" + user
				  +" AND sentence_lock LIKE '%write%'") != 0);
      if (write) writeLock = false;
      else readLock = false;
      updateUserLock( user, readLock, writeLock);
    } finally {sql.unlock();}
  }


  /// Suite maintainance

  /**
   * Adds the suite to the KSDB.
   * <p>
   * <b>Pre-Conditions:</b> No suite exists in the database with
   * the same name.<p>
   * <b>Post-Conditions:</b> The suite is added to the database with
   * a unique ID.<p>
   * <b>Invariant:</b> No other data is affected
   *
   * @param  name         Name of new suite
   * @param  description  Description of new suite. May be NULL
   * @return              ID of new suite
   * @throws SQLException if suite already exists in database
   */
  public int newSuite(String name, String description) 
    throws SQLException {
    String qName = quote( name);
    String qDescription = quote( description);

    // Bail if suite already exists in database; insert otherwise
    if (sql.selectInt("id FROM Suite WHERE name=" + qName) != 0)
      throw(new SQLException("Suite name=" + name
			     + " already exists in KSDB."));

    int suite = 0;
    sql.lock( timeout);
    try {suite = sql.insertID("Suite", "name=" + qName
			      + " AND description=" + qDescription,
			      qName + ", " + qDescription);
    } finally {sql.unlock();}
    return suite;
  }

  /**
   * Removes suite from the database.
   * 
   * @param  suite        Which suite to delete
   * @throws SQLException if suite doesn't exist
   */
  public void deleteSuite(int suite) throws SQLException {
    try {
      sql.lock( timeout);
      deleteSuiteAux( suite);
    } finally {sql.unlock();}
  }

  /**
   * If flag is on, makes sure that the document is associated with the
   * suite. If the flag is off, makes sure that the document is not
   * associated with the suite.
   * <p>
   * <b>Pre-Conditions:</b> The document and suite are valid.<p>
   * <b>Post-Conditions:</b> If flag is on, makes sure that the document is
   * associated with the suite. If the flag is off, makes sure that the
   * document is not associated with the suite.<p>
   * a unique ID.<p>
   * <b>Invariant:</b> No other data is affected
   *
   * @param suite    Suite being affected
   * @param document Document being affected
   * @param flag     true if suite should contain document, false otherwise
   */
  public void suiteContainsDocument(int suite, int document, boolean flag) {
    if (flag) sql.insert("Suite_Document", "suite=" + suite
			 + " AND document=" + document,
			 suite + ", " + document);
    else sql.delete("Suite_Document WHERE suite=" + suite
		    + " AND document=" + document);
  }


  /// Document insertion

  /**
   * Adds the document to the KSDB. The title is the title of the
   * document. The file contains the sentences of the document, 1 sentence
   * per line, in proper order. The language is the ID of the source
   * language of the document.
   * <p>
   * <b>Pre-Conditions:</b> The file exists and is readable. Also, no document
   * exists in the database with the same file. And the user and language
   * are valid.<p>
   * <b>Post-Conditions:</b> The document now exists in the database and has a
   * unique ID.<p>
   * <b>Invariant:</b> No other documents or sentences are affected.
   *
   * @param  language     Language document is in
   * @param  file         File containing document
   * @param  title        Title of document...may be NULL
   * @return              ID of new document
   * @throws IOException  if file cannot be read
   * @throws SQLException if document already exists in database
   *                      or language doesn't exist in database
   */
  public int newDocument(int language, File file, String title) 
    throws IOException, SQLException {
    return newDocument( language, file, title, getSentences( file));
  }

  /**
   * Returns sentences from file
   * @param  file        File containing sentences
   * @return list of sentences
   * @throws IOException if file cannot be read
   */
  private List getSentences(File file) throws IOException {
    BufferedReader in = new BufferedReader(new FileReader( file));
    List sentences = new ArrayList();
    String text = in.readLine();
    do {sentences.add( text);
    } while ((text = in.readLine()) != null);
    return sentences;
  }

  /**
   * Like the above, but lets the user provide the sentences for the document.
   * <p>
   * <b>Pre-Conditions:</b> No document exists in the database with the
   * same file. And the user and language are valid.<p>
   * <b>Post-Conditions:</b> The document now exists in the database and has a
   * unique ID.<p>
   * <b>Invariant:</b> No other documents or sentences are affected.
   *
   * @param  language     Language document is in
   * @param  file         File containing document
   * @param  title        Title of document...may be NULL
   * @param  sentences    Sentences that make up document
   * @return              ID of new document
   * @throws SQLException if document already exists in database
   *                      or language doesn't exist in database
   */
  public int newDocument(int language, File file, String title, List sentences)
    throws SQLException {
    // Verify language
    if (sql.selectString("name FROM Source_Language WHERE id=" + language)
	== null)
      throw new SQLException("Language id="
			     + language + " doesn't exist in KSDB.");

    String qTitle = quote( title);
    String qFile = (file == null) ? null : quote( file.getName().toString());

    // Bail if document already exists in database; insert otherwise
    if (sql.selectInt("id FROM Document WHERE language="
		      + language + " AND filename=" + qFile) != 0)
      throw(new SQLException("Document language=" + language + " file=" + qFile
			     + " already exists in KSDB."));

    int document = 0;
    sql.lock( timeout);
    try {
      document = sql.insertID("Document", "language=" + language
			      + " AND filename=" + qFile,
			      language + ", " + qFile + ", " + qTitle);
      loadDocument( document, language, sentences);
    } finally {sql.unlock();}
    return document;
  }

  /**
   * Inserts contents of file in as a new document.
   * @param document      Document to load
   * @param language      Language of sentences
   * @param sentences     Sentences to put into document
   */
  private void loadDocument(int document, int language, List sentences) {
    int line = 1;
    int sentence = -1;
    for (Iterator iterator = sentences.iterator(); iterator.hasNext();) {
      String text = (String) iterator.next();
      if (text == null) continue;
      String qText = quote( text);

      // set sentenceContext to either the last sentence or NULL
      String previous = reliesOnContext( text) && (sentence != -1)
	? String.valueOf( sentence) : "NULL";

      // insert sentence and context
      sentence = sql.insertID("Sentence", "BINARY text=" + qText
			      + " AND language=" + language
			      + " AND previous<=>" + previous,
			      language + ", " + qText + ", " + previous
			      + ", 0, NULL, NULL");
      insertContext( document, line, sentence);
      line++;
    }
  }

  /**
   * Indicates if the sentence can be translated independently of context
   * @param sentence sentence to be considered
   * @return         true if sentence relies on context for proper translation.
   */
  private static boolean reliesOnContext(String sentence) {
    // Search for occurrences of context-sensetive words in sentence
    String s = sentence.toLowerCase();
    // make into property!!!
    for (int p = 0; p < ContextWords.length; p++)
      for (int i = s.indexOf( ContextWords[p]); i != -1;
	   i = s.indexOf( ContextWords[p], i + 1))
	if (((i == 0) || !Character.isLetter( s.charAt( i - 1))) &&
	    ((s.length() == i + ContextWords[p].length()) ||
	     !Character.isLetter( s.charAt( i + ContextWords[p].length()))))
	  return true;
    return false;
  }


  /// Document removal

  /**
   * Removes document from the database.
   * <p>
   * <i>This method is not lock-safe! It does not ensure that
   * the database is write-locked by the user.</i>
   * 
   * @param  document     Which document to delete
   * @throws SQLException if document doesn't exist
   */
  public void deleteDocument(int document) throws SQLException {
    verifyDocument( document);
    try {
      sql.lock( timeout);
      deleteDocumentAux( document);
    } finally {sql.unlock();}
  }

  /**
   * Fixes the language from a document. Can be used to verify document exists
   * @param  document     Which document
   * @return              language 
   * @throws SQLException if document doesn't exist
   */
  private void verifyDocument(int document) throws SQLException {
    if (sql.selectInt("language FROM Document where id=" + document) == 0)
      throw new SQLException("Document id=" + document
			     + " doesn't exist in KSDB.");
  }



  /// Modification routines

  /**
   * This function should be called when the contents of a document have
   * been edited and saved/committed back to the document's associated file.
   * This function is optimal...it does not delete/recreate sentences
   * in the document that did not change. It merely adds the 'new' sentences
   * to the KSDB and removes the old ones.
   * <p>
   * <i>This method is not lock-safe! It does not ensure that
   * the database is write-locked by the user.</i>
   * <p>
   * <b>Pre-Conditions:</b> The document exists in the database. And the
   * database is write-locked to the user.
   * <b>Post-Conditions:</b> The contents of the document once again agree
   * with the associated file.<p>
   * <b>Invariant:</b> No changes to other documents, or sentences that are
   * not in this document.
   * 
   * @param  document     Document being modified
   * @return              New document ID
   * @throws IOException  if document's file cannot be read
   */
  public int reloadDocument(int document) throws IOException {
    return reloadDocument( document, getSentences( new File( sql.selectString("Filename FROM Document WHERE id=" + document))));
  }

  /**
   * Like the above, but also accepts the list of modified sentences.
   * <p>
   * <i>This method is not lock-safe! It does not ensure that
   * the database is write-locked by the user.</i>
   * <p>
   * <b>Pre-Conditions:</b> The document exists in the database. And 
   * the database is write-locked to the user.
   * <b>Post-Conditions:</b> The contents of the document once again agree
   * with the associated file.<p>
   * <b>Invariant:</b> No changes to other documents, or sentences that are
   * not in this document.
   * 
   * @param  document     Document being modified
   * @param  sentences    Sentences to be reloaded
   * @return              New document ID
   */
  public int reloadDocument(int document, List sentences) {
    try {verifyDocument( document);
    } catch (SQLException x) {ReportError.warning(x);}
    ResultSet rs = sql.select("filename, title, language FROM Document"
			      + " WHERE id=" + document);
    SQLConnection.next(rs);
    String qFile = quote( SQLConnection.getString(rs, 1));
    String qTitle = quote( SQLConnection.getString(rs, 2));
    int language = SQLConnection.getInt(rs, 3);
    int result = 0;
    try {
      sql.lock( timeout);
      // Change the old document's file to null, so we can create new document
      // with old filename
      sql.update("Document SET filename=NULL WHERE id=" + document);
      result = sql.insertID("Document", "language=" + language
			    + " AND filename=" + qFile,
			    language + ", " + qFile + ", " + qTitle);
      loadDocument( result, language, sentences);
      sql.update("Suite_Document SET document=" + result
		 + " WHERE document=" + document);
      deleteDocumentAux( document);
    } finally {sql.unlock();}
    return result;
  }

  /**
   * This method replaces the sentence specified with new text. It is
   * faster than Modify-Document for making an intra-sentential change in a
   * single sentence.
   * <p>
   * <i>This method is not lock-safe! It does not ensure that
   * the database is write-locked by the user.</i>
   * <p>
   * <b>Pre-Conditions:</b> The sentence exists in the database. And the user
   * has a write-lock on the database.<p>
   * <b>Post-Conditions:</b> The sentence now contains the associated text.<p>
   * <b>Invariant:</b> No changes to other sentences.
   * 
   * @param  sentence     Sentence being modified
   * @param  text         New sentence text
   * @throws SQLException if sentence doesn't exist in database or new
   *                      sentence relies on context when old sentence didn't
   */
  public void modifySentence(int sentence, String text) throws SQLException {
    // Get old sentence info
    String oldText = sql.selectString("text FROM Sentence WHERE id="+sentence);
    if (oldText == null)
      throw new SQLException("Sentence id=" + sentence
			     + " doesn't exist in KSDB.");
    if (oldText.equals( text)) return;
    if (!reliesOnContext( oldText) && reliesOnContext( text))
      throw new SQLException("Sentence id=" + sentence
			     + " must not rely on context.");

    sql.lock( timeout);
    sql.update("Sentence SET BINARY text=" + quote(text)
	       + " WHERE id=" + sentence);
    deleteAnalyses("sentence=" + sentence);
    sql.unlock();
  }


  /// Server Version touching and connection

  /**
   * Language of Server (id from language table)
   */
  private int language = 0;

  /**
   * Analyzer or Generator Server being connected to
   */
  private KantooConnectionWorker server = null;

  /**
   * Version of Server (id from version table)
   */
  private int version = 0;

  /**
   * Version of Server (id from version table)
   */
  private Timestamp versionTime = null;

  /**
   * Version of Server that stores correct outputs (id from version table)
   */
  private int correctVersion = 0;


  /**
   * Connects to an Analyzer or Generater server, and returns the connection
   * @param  analyzer         if true, use analyzer, otherwise use generator
   * @return                  Conection to server
   * @throws ConnectException if server not accessable,
   */
  public KantooConnectionWorker getKantooConnectionWorker(boolean analyzer) {
    KantooConnectionWorker server;
    if (analyzer) server = new AnalyzerConnectionWorker();
    else server = new GeneratorConnectionWorker();
    new Thread( server).start();
    server.new ConnectCommand().invokeAndWait();
    return server;
  }

  /**
   * Returns the language id used by the server
   * @param  analyzer         if true, use analyzer, otherwise use generator
   * @param  server           KANTOO server to connect to
   * @return                  language ID
   */
  public int getLanguage(boolean analyzer, KantooConnectionWorker server) {
    String languageName = server.getLanguage();
    if (languageName == null) languageName = "CTE";
    return sql.selectInt("id FROM " + (analyzer ? "Source_Language"
				       : "Target_Language")
			 + " WHERE name=" + quote( languageName));
  }

  /**
   * Returns id of current version of server
   * @param  analyzer         if true, use analyzer, otherwise use generator
   * @param  server           KANTOO server to connect to
   * @param  language         ID of language of server (0 means find out)
   * @throws ConnectException if server not accessable,
   */
  public int getCurrentVersionID(boolean analyzer,
				 KantooConnectionWorker server,
				 int language) {
    // add version entry if necessary
    String VersionDatabase = analyzer ? "Analyzer_Version":"Generator_Version";
    String qVersion = quote( server.getVersion());
    int result = sql.insertID( VersionDatabase, "name=" + qVersion 
			       + " AND language=" + language,
			       qVersion + ", " + language + ", NULL");
    return result;
  }

  /**
   * Gets the ID for the correct version for the given language
   * @param  analyzer     if true, use analyzer version, otherwise generator
   * @param  language     Language ID being referenced
   * @return              version ID
   */
  public int getCorrectVersionID(boolean analyzer, int language) {
    String versionDatabase = analyzer ? "Analyzer_Version":"Generator_Version";
    correctVersion = sql.insertID( versionDatabase,
				   "name='CORRECT' AND language=" + language,
				   "'CORRECT', " + language + ", NULL");
    return correctVersion;
  }

  /**
   * Connects to an Analyzer or Generater server, updating the KSDB
   * as to language & version.
   * @param  analyzer         if true, use analyzer, otherwise use generator
   * @throws ConnectException if server not accessable,
   */
  private void connect(boolean analyzer) throws IOException {
    this.server = getKantooConnectionWorker( analyzer);
    sql.lock( timeout);
    this.language = getLanguage( analyzer, server);
    this.version = getCurrentVersionID( analyzer, server, language);
    // get version timestamp too
    String versionDatabase = analyzer ? "Analyzer_Version":"Generator_Version";
    this.versionTime = sql.selectTimestamp("timestamp FROM " + versionDatabase
					   + " WHERE id=" + this.version);
    getCorrectVersionID( analyzer, language); // add correct version, too
    sql.unlock();
  }

  /**
   * Indicates that the analyzer or generator that corresponds to the version
   * of the currently active server has been changed, and so the timestamp
   * on the version should be updated. This will cause any sentences run with
   * the analyzer/generator to be re-run whwnever they are explicitly tested
   * with <code>analyzeSentences</code> or <code>translateSentences</code>.
   * <p>
   * <b>Pre-Condition:</b> the analyzer/generator version is valid<p>
   * <b>Post-Condition:</b> the timestamp of the version is set to the
   * current time.<p>
   * <b>Invariant:</b> No changes to the rest of the database.
   * 
   * @param analyzer If True, use Analyzer; otherwise use Generator.
   * @param version  Version ID in analyzer or generator table.
   *                 If 0, gets string from appropriate server.
   * @throws IOException if server is unavailable
   */
  public void touchServer(boolean analyzer,int version) throws IOException {
    int v = version;
    if (version == 0) {
      connect( analyzer);
      v = this.version;
    }
    sql.updateTimestamp(analyzer ? "Analyzer_Version" : "Generator_Version",v);
  }


  /// Running the Analyzer

  /**
   * Class for when the user cancels a long operation that locks the database
   */
  public class LockInterruptedException extends Exception {
    LockInterruptedException(String message) {super(message);}
  }

  /**
   * Runs the Analyzer on the sentences specified, storing results in the
   * KSDB. Only sentences that don't have Analyses that are newer than the
   * analyzer version are actually run.
   * <p>
   * <i>This method is not lock-safe! It does not ensure that
   * the database is read-locked by the user.</i>
   * <p>
   * <i>Does not validate input ResultSet.</i>
   * <p>
   * <b>Pre-Conditions:</b> The sentences in the list are valid, and the
   * database is read-locked by the user. <p>
   * <b>Post-Conditions:</b> Every sentence has at least one Analysis with the
   * implied analyzer version.<p>
   * <b>Invariant:</b> No changes to other sentences or Analyses with
   * different versions. Also no changes to read or write locks.
   *
   * @param sentences         Sentences to analyze
   * @param monitor           If not null, indicates a ProgressMonitor to
   *                          display and update while working.
   * @throws ConnectException if server not accessable, or language mismatch
   * @throws LockInterruptedException if user aborts locks in-progress
   */
  public void analyzeSentences(ResultSet sentences,  ProgressMonitor monitor)
    throws IOException, LockInterruptedException {

    connect( true);
    AnalyzerConnectionWorker analyzer = (AnalyzerConnectionWorker) server;

    int progress = 0;
    int lastSentence = 0;
    try {
      for (SQLConnection.beforeFirst( sentences); SQLConnection.next( sentences);) {
	int sentence = SQLConnection.getInt( sentences, 1);

	// First see if the analyses for this sentence & version are up-to-date
	if (sql.outdated(versionTime, "Analysis WHERE sentence="
			 + sentence + " AND version=" + version)) {
	  updateContext( lastSentence, sentence);
	  lastSentence = sentence;
	  Sentence text = new TextSentence( sql.selectString("text FROM Sentence WHERE id=" + sentence));
	  AnalyzerConnectionWorker.WorkCommand cmd = analyzer.new WorkCommand( text);
	  cmd.invokeAndWait();

	  List irs = null;
	  if (cmd.getIssue().translatable()) { // try to get IRs
	    AnalyzerConnectionWorker.InterlinguaCommand command = analyzer.new InterlinguaCommand();
	    command.invokeAndWait();
	    irs = command.getInterlinguas();
	  }

	  if (! cmd.getIssue().translatable() || irs.size() == 0) {
	    // add 'no-IR' entry (for ungrammatical or complex S
	    sql.lock( timeout);
	    insertAnalysis( sentence, version, 0);
	    deleteAnalyses("sentence=" + sentence + " AND version=" + version
			   + " AND interlingua<>0");

	  } else {// We got some IRs

	    // Figure out the CORRECT IR, if any.
	    String correctIR
	      = sql.selectString("Interlingua.text FROM Analysis, Interlingua"
				 + " WHERE Analysis.sentence=Interlingua.sentence"
				 + " AND Analysis.interlingua<=>Interlingua.id"
				 + " AND Analysis.sentence=" + sentence
				 + " AND Analysis.version=" + correctVersion);

	    // Insert all IRs and Analyses into the database. Keep track of IR ID's
	    StringBuffer irSet = new StringBuffer();
	    boolean flag = false;
	    sql.lock( timeout);
	    for (Iterator i = irs.iterator(); i.hasNext();) {
	      String irString = (String) i.next();
	      String qIR = quote( irString);
	      String qScore = quote( score( text, irString, correctIR));
	      int ir = sql.insertID("Interlingua", "sentence=" + sentence
				    + " AND BINARY text=" + qIR,
				    sentence + ", " + qIR + ", 0, " + qScore
				    + ", NULL");
	      insertAnalysis( sentence, version, ir);
	      if (flag) irSet.append(',');
	      else flag = true;
	      irSet.append(ir);
	    }

	    // Now delete all Analyses that have an IR not in the set (or no IR)
	    deleteAnalyses("sentence=" + sentence + " AND version=" + version +
			   " AND interlingua NOT IN (" + irSet + ')');
	  }
	  sql.unlock();
	}
	if (monitor != null) {
	  progress++;
	  if (ReportError.Windows) {
	    monitor.setProgress( progress);
	    if (monitor.isCanceled())
	      throw new LockInterruptedException( "Analyses Aborted");
	  } else {
	    if (progress % progressInterval == 0) {
	      System.out.print(".");
	      System.out.flush();
	    }
	  }
	}
      }
    } finally {
      if (monitor != null)
	monitor.close();
    }
  }

  /**
   * Makes sure the Analyzer has the appropriate context
   * @param context  Sentence last ran on the analyzer. May be 0
   * @param sentence Sentence that needs to be run on the analyzer now.
   */
  private void updateContext(int context, int sentence) {
    int requiredContext = sql.selectInt("previous FROM Sentence WHERE id="
					+ sentence);
    if (requiredContext != 0 && requiredContext != context) {
      // uh oh...have to run 'context' thru analyzer.
      updateContext( context, requiredContext);
      Sentence text
	= new TextSentence( sql.selectString("text FROM Sentence WHERE id="
					     + requiredContext));
      ((AnalyzerConnectionWorker) server).new WorkCommand( text.getText()).invokeAndWait(); // discard result
    }
  }


  /**
   * Runs the Generator on the Interlinguas belonging to the sentences
   * specified and the analyzerVersion, storing results in the KSDB.
   * <p>
   * <i>This method is not lock-safe! It does not ensure that
   * the database is read-locked by the user.</i>
   * <p>
   * <i>Does not validate input ResultSet.</i>
   * <p>
   * <b>Pre-Conditions:</b> The sentences are all valid, and each has
   * at least one Analysis with the analyzerVersion. Also the database
   * is read-locked by the user.<p>
   * <b>Post-Conditions:</b> Every IR from every sentence satisfied by this
   * query has exactly one Generation with the implied generator version.<p>
   * <b>Invariant:</b> No Sentence changes. No changes to other IRs or
   * Generations with different versions or languages.
   * 
   * @param sentences         Sentences to translate
   * @param analyzerVersion   Version of analyzer to use to find IRs
   *                          If 0 use version of current analyzer server
   * @param monitor           If not null, indicates a ProgressMonitor to
   *                          display and update while working.
   * @throws ConnectException if server not accessable, or language mismatch
   * @throws SQLException     if Analyzer version doesn't exist in database
   * @throws LockInterruptedException if user aborts locks in-progress
   */
  public void translateSentences(ResultSet sentences, int analyzerVersion,
				 ProgressMonitor monitor) 
    throws IOException, SQLException, LockInterruptedException {

    // Verify analyzer version
    if (analyzerVersion != 0) {
      if (sql.selectString("name FROM Analyzer_Version WHERE id="
			   + analyzerVersion) == null)
	throw new SQLException("Analyzer Version id="
			       + analyzerVersion + " doesn't exist in KSDB.");
    } else { // OK, use current analyzer version
      connect( true);
      analyzerVersion = version;
    }
    connect( false);

    GeneratorConnectionWorker generator = (GeneratorConnectionWorker) server;

    int progress = 0;
    try {
      for (SQLConnection.beforeFirst( sentences); SQLConnection.next( sentences);) {
	int sentence = SQLConnection.getInt( sentences, 1);
	// Get all IRs from sentence and analyzer version
	ResultSet irs = sql.select("Analysis.interlingua FROM Analysis"
				   + " WHERE Analysis.sentence=" + sentence
				   + " AND Analysis.version=" + analyzerVersion);
	while (SQLConnection.next( irs)) {
	  int ir = SQLConnection.getInt( irs, 1);
	  if (ir == 0) continue;

	  // First see if there is an up-to-date generation of this IR
	  if (! sql.outdated(versionTime, "Generation WHERE interlingua=" + ir 
			     + " AND version=" + version))
	    continue;

	  String interlingua = sql.selectString("text FROM Interlingua WHERE id=" + ir);
	  GeneratorConnectionWorker.WorkCommand cmd = generator.new WorkCommand( interlingua);
	  cmd.invokeAndWait();

	  String translation = cmd.getTranslation();
	  if (translation == null) translation = "";
	  String qTranslation = quote( translation);

	  // Figure out the CORRECT translation, if any.
	  String correctTranslation
	    = sql.selectString("Translation.text FROM Generation, Translation"
			       + " WHERE Generation.translation=Translation.id"
			       + " AND Generation.version=" + correctVersion
			       + " AND Translation.source=" + sentence);
	  String qScore = quote( score( ir, translation, correctTranslation));
	  sql.lock( timeout);
	  int trans = sql.insertID("Translation", "source=" + sentence
				   + " AND language=" + language
				   + " AND BINARY text=" + qTranslation,
				   sentence + ", " + language + ", "
				   + qTranslation + ", 0, " + qScore + ", NULL");
	  insertGeneration( ir, version, trans);
	  sql.unlock();
	}

	if (monitor != null) {
	  progress++;
	  if (ReportError.Windows) {
	    monitor.setProgress( progress);
	    if (monitor.isCanceled())
	      throw new LockInterruptedException( "Translations Aborted");
	  } else {
	    if (progress % progressInterval == 0) {
	      System.out.print(".");
	      System.out.flush();
	    }
	  }
	}
      }
    } finally {
      if (monitor != null) monitor.close();
    }
  }


  /// Scoring

  public static final String[] IRScores
    = {"ACCEPTABLE", "CORRECT", "DOMO", "INCORRECT", "NOT-KCE", "OBSOLETE",
       "UNKNOWN"};

  public static final String[] TransScores
    = {"BAD-IR", "CORRECT", "GRAMMAR", "INCORRECT", "LEXICAL", "MINIMAL",
       "MORPHOLOGY", "OBSOLETE", "TRUNCATION", "UNKNOWN"};

  /**
   * Indicates if a score is CORRECT or ACCEPTABLE
   * @param score Score
   * @return      True if score is CORRECT or ACCEPTABLE
   */
  public static boolean correct(String score) {
    return score.equals("CORRECT") || score.equals("ACCEPTABLE");
  }

  /**
   * Indicates if a score is INCORRECT or one of its variants. (Not the same
   * as !correct()!)
   * @param score Score
   * @return      True if score is not CORRECT, ACCEPTABLE, or UNKNOWN
   */
  public static boolean incorrect(String score) {
    return ! score.equals("UNKNOWN") && !correct(score);
  }

  /**
   * Decides on a default score for the ir.
   * @param sentence  Original sentence
   * @param ir        Interlingua being scored
   * @param correctIR Interlingua designated CORRECT. May be null
   */
  private String score(Sentence sentence, String ir, String correctIR) {
    // for now the ir is CORRECT if it matches the correct IR, or INCORRECT
    // if the correct IR is not NULL, or UNKNOWN otherwise
    if (correctIR != null && correctIR.equals( ir))
      return "CORRECT";
    if (correctIR == null) return "UNKNOWN";
    return "INCORRECT";
  }

  /**
   * Decides on a default score for the translation
   * @param ir                 Original IR (id)
   * @param translation        Translation being scored
   * @param correctTranslation designated CORRECT. May be null
   */
  private String score(int ir, String translation, String correctTranslation) {
    // Do the same as for scoring IRs...
    if (correctTranslation != null && correctTranslation.equals( translation))
      return "CORRECT";

    // But if IR score is not UNKNOWN, ACCEPTABLE, CORRECT, set to BAD-IR.
    if (incorrect( sql.selectString("score FROM Interlingua WHERE id=" + ir)))
      return "BAD-IR";

    // If translation is empty, mark it INCORRECT
    if (translation.length() == 0)
      return "INCORRECT";

    // If translation has a star, it should be LEXICAL
    if (translation.indexOf('*') != -1)
      return "LEXICAL";

    if (correctTranslation == null) return "UNKNOWN";
    return "INCORRECT";
  }

  /**
   * This method allows a person to judge the validity of an IR, by
   * assigning a new Analyzer Score to the IR.
   * <p>
   * <i>This method is not lock-safe! It does not ensure that
   * the database is read-locked by the user.</i>
   * <p>
   * <b>Pre-Conditions:</b> The IR exists, and the score is valid. And the
   * database is read-locked by the user.<p>
   * <b>Post-Conditions:</b> The interlingua receives the score assigned. The
   * translations derived from this IR may have their scores altered, and
   * the other IRs from the same sentence may have their scores altered as
   * well...see the pseudo-code for details.<p>
   * <b>Invariant:</b> No changes to other sentences.
   * 
   * @param  interlingua  IR to receive score
   * @param  score        Score to assign IR
   * @throws SQLException if IR doesn't exist, or score is improper.
   */
  public void rateInterlingua(int interlingua, String score)
    throws SQLException {
    String newScore = score.toUpperCase();
    if (Arrays.binarySearch( IRScores, newScore) < 0)
      throw new SQLException("Invalid Interlingua score: " + score);

    // Get old score, bail if matches new score
    String oldScore = sql.selectString("score FROM Interlingua WHERE id="
				       + interlingua);
    if (oldScore == null)
      throw new SQLException("Interlingua id=" + interlingua
			     + " doesn't exist in KSDB.");
    if (oldScore.equals( newScore)) return;

    // What language (and correct version) are we dealing with?
    language = sql.selectInt("language FROM Sentence, Interlingua"
			     + " WHERE Sentence.id=Interlingua.sentence"
			     + " AND Interlingua.id<=>" + interlingua);
    sql.lock( timeout);
    rateInterlinguaAux( interlingua, oldScore, newScore);
    sql.unlock();
  }

  /**
   * Assigns a score to an IR. (Input verified and lock obtained)
   * @param interlingua IR to receive score
   * @param oldScore    Old score that IR had
   * @param newScore    New score to assign IR
   */
  private void rateInterlinguaAux(int interlingua, String oldScore,
				  String newScore) {
    getCorrectVersionID( true, language); // set correct version, too

    // What's the associated sentence?
    int sentence = sql.selectInt("sentence FROM Interlingua WHERE id="
				 + interlingua);

    // Change CORRECT analysis if we are changing correctness
    if (correct( oldScore) && !correct( newScore)) 
      deleteAnalyses("sentence=" + sentence
		     + " AND interlingua<=>" + interlingua
		     + " AND version=" + correctVersion);
    if (!oldScore.equals("CORRECT") && newScore.equals("CORRECT")) {
      ResultSet rs = sql.select("interlingua FROM Analysis WHERE sentence="
				+ sentence);
      while (SQLConnection.next(rs)) {
	int rsIR = SQLConnection.getInt(rs, 1);
	if (rsIR == interlingua) continue;
	String rsScore = sql.selectString("score FROM Interlingua WHERE id="
					  + rsIR);
	if (rsScore.equals("CORRECT"))
	  rateInterlinguaAux( rsIR, rsScore, "OBSOLETE");
	else if (rsScore.equals("ACCEPTABLE") || rsScore.equals("UNKNOWN"))
	  rateInterlinguaAux( rsIR, rsScore, "INCORRECT");
      }
    }

    if (!correct( oldScore) && correct( newScore))
      insertAnalysis( sentence, correctVersion, interlingua);

    // Change the actual score
    sql.update("Interlingua SET score=" + quote(newScore)
	       + " WHERE id=" + interlingua);

    // Change translations
    ResultSet rs
      = sql.select("translation, score FROM Generation, Translation"
		   + " WHERE Generation.translation=Translation.id"
		   + " AND Generation.interlingua=" + interlingua);
    while (SQLConnection.next(rs))
      if (SQLConnection.getString(rs, 2).equals("BAD-IR") && correct( newScore))
	rateTranslationAux( SQLConnection.getInt(rs, 1), "BAD-IR", "UNKNOWN");
  }

  /**
   * This method allows a person to judge the validity of an translation, by
   * assigning a new Analyzer Score to the translation.
   * <p>
   * <i>This method is not lock-safe! It does not ensure that 
   * the database is read-locked by the user.</i>
   * <p>
   * <b>Pre-Conditions:</b> The translation exists, and the score is valid.
   * And the database is read-locked by the user.<p>
   * <b>Post-Conditions:</b> The translation receives the score assigned. The
   * other translations from the same IR may have their scores altered as
   * well, and the IR's score may be altered, too...see the pseudo-code
   * for details.<p>
   * <b>Invariant:</b> No changes to other sentences.
   * 
   * @param  translation  Translation to receive score
   * @param  score        Score to assign translation
   * @throws SQLException if translation doesn't exist, or score is improper.
   */
  public void rateTranslation(int translation, String score)
    throws SQLException {
    String newScore = score.toUpperCase();
    if (Arrays.binarySearch( TransScores, newScore) < 0)
      throw new SQLException("Invalid Translation score: " + score);

    // Get old score, bail if matches new score
    String oldScore = sql.selectString("score FROM Translation WHERE id="
				       + translation);
    if (oldScore == null)
      throw new SQLException("Translation id=" + translation
			     + " doesn't exist in KSDB.");
    if (oldScore.equals( newScore)) return;

    // What language (and correct version) are we dealing with?
    language = sql.selectInt("language FROM Translation WHERE Translation.id="
			     + translation);
    sql.lock( timeout);
    rateTranslationAux( translation, oldScore, newScore);
    sql.unlock();
  }

  /**
   * Assigns a score to a translation. (input verified and lock obtained)
   * @param translation Translation to receive score
   * @param oldScore    Old score that translation had
   * @param newScore    New score being assigned
   */
  private void rateTranslationAux(int translation,
				  String oldScore, String newScore) {
    getCorrectVersionID( false, language); // set correct version, too

    // What's the associated interlingua? and associated sentence?
    int sentence = sql.selectInt("source FROM Translation WHERE id="
				 + translation);
    ResultSet irs = sql.select("interlingua FROM Generation WHERE translation="
			       + translation);

    // Change CORRECT generation if we are changing correctness
    if (correct( oldScore) && !correct( newScore)) 
      deleteGenerations("translation=" + translation
			+ " AND version=" + correctVersion);
    else if (!correct( oldScore) && correct( newScore)) {
      ResultSet rs = sql.select("Generation.translation, Translation.score"
				+ " FROM Analysis, Generation, Translation"
				+ " WHERE Analysis.interlingua<=>Generation.interlingua"
				+" AND Generation.translation=Translation.id"
				+ " AND Translation.language=" + language
				+ " AND Analysis.sentence=" + sentence);
      while (SQLConnection.next(rs)) {
	int rsTrans = SQLConnection.getInt(rs, 1);
	if (rsTrans == translation) continue;
	String rsScore = SQLConnection.getString(rs, 2);
	if (rsScore.equals("CORRECT"))
	  rateTranslationAux( rsTrans, "CORRECT", "OBSOLETE");
	else if (rsScore.equals("UNKNOWN"))
	  rateTranslationAux( rsTrans, "UNKNOWN", "INCORRECT");
      }
      while (SQLConnection.next( irs))
	insertGeneration( SQLConnection.getInt( irs, 1), correctVersion, translation);
      SQLConnection.beforeFirst( irs);
    }

    // Change the actual score
    sql.update("Translation SET score=" + quote( newScore)
	       + " WHERE id=" + translation);

    // Change interlingua scores
    while (SQLConnection.next( irs)) {
      int interlingua = SQLConnection.getInt( irs, 1);
      String irScore = sql.selectString("score FROM Interlingua WHERE id="
					+ interlingua);
      if (correct( newScore) && !correct( irScore))
	rateInterlinguaAux( interlingua, irScore, "ACCEPTABLE");
      else if (newScore.equals("BAD-IR") && !incorrect( irScore))
	rateInterlinguaAux( interlingua, irScore, "INCORRECT");
    }
  }


  /// Commenting

  /**
   * Assigns a comment to an entry
   * @param  table        Table in which entry exists
   * @param  entry        entry to receive comment
   * @param  comment      Comment to assign entry. May be null
   * @throws SQLException if entry doesn't exist
   */
  private void comment(String table, int entry, String comment)
    throws SQLException {
    if (comment.length() == 0) comment = null;
    int id = sql.selectInt("id FROM " + table + " WHERE id=" + entry);
    if (id == 0)
      throw new SQLException(table + " id=" +entry+ " doesn't exist in KSDB.");

    // Change the actual comment
    sql.lock( timeout);
    sql.update(table + " SET comment=" + quote(comment) + " WHERE id=" +entry);
    sql.unlock();
  }


  /**
   * Allows one to add a comment to an IR.
   * <p>
   * <i>This method is not lock-safe! It does not ensure that 
   * the database is read-locked by the user.</i>
   * <p>
   * <b>Pre-Conditions:</b> The IR exists, and the database is
   * read-locked.<p>
   * <b>Post-Conditions:</b> The IR has the associated comment<p>
   * <b>Invariant:</b> No other changes.
   * 
   * @param  interlingua  IR to receive comment
   * @param  comment      Comment to assign IR. May be null
   * @throws SQLException if IR doesn't exist
   */
  public void commentInterlingua(int interlingua, String comment)
    throws SQLException {comment("Interlingua", interlingua, comment);}

  /**
   * Allows one to add a comment to a translation.
   * <p>
   * <i>This method is not lock-safe! It does not ensure that 
   * the database is read-locked by the user.</i>
   * <p>
   * <b>Pre-Conditions:</b> The translation exists, and the database
   * is read-locked.<p>
   * <b>Post-Conditions:</b> The translation has the associated comment.<p>
   * <b>Invariant:</b> No other changes.
   * 
   * @param  translation  translation to receive comment
   * @param  comment      Comment to assign translation. May be null
   * @throws SQLException if translation doesn't exist
   */
  public void commentTranslation(int translation, String comment)
    throws SQLException {comment("Translation", translation, comment);}



  /**
   * Returns the language id given the name
   * @param  ksdb         KANTOO Sentence Database object
   * @param  name         Name of language
   * @return              ID of language
   * @throws SQLException if language doesn't exist
   */
  private static int getLanguage(Ksdb ksdb, String name) throws SQLException {
    int id = ksdb.selectInt("id FROM Source_Language WHERE name="
			    + ksdb.quote( name.toLowerCase()));
    if (id == 0)
      throw new SQLException("Language name=" + name
			     + " doesn't exist in KSDB.");
    return id;
  }

  /**
   * Returns the suite id given its name
   * @param  ksdb         KANTOO Sentence Database object
   * @param  name         Name of suite
   * @return              ID of suite
   * @throws SQLException if suite doesn't exist
   */
  private static int getSuite(Ksdb ksdb, String name) throws SQLException {
    int id = ksdb.selectInt("id FROM Suite WHERE name=" + quote( name));
    if (id == 0)
      throw new SQLException("Suite name=" + name
			     + " doesn't exist in KSDB.");
    return id;
  }

  /**
   * Returns the document id given the language and filename
   * @param  ksdb         KANTOO Sentence Database object
   * @param  langauge     Name of language
   * @param  filename     Name of document file
   * @return              ID of document
   * @throws SQLException if document doesn't exist
   */
  private static int getDocument(Ksdb ksdb, String language, String filename)
    throws SQLException {
    int languageID = getLanguage( ksdb, language);
    int id = ksdb.selectInt("id FROM Document WHERE language=" + languageID
			    + " AND filename=" + ksdb.quote( filename));
    if (id == 0)
      throw new SQLException("Document file=" + filename
			     + " doesn't exist in KSDB.");
    return id;
  }

  /**
   * Joins all the arguments after arg (inclusive) in a space-separated string
   * @param args Args sent to program
   * @param arg  First arg of string
   * @return String joining all args. may be NULL if no args provided
   */
  private static String argsRemainder(String[] args, int arg) {
    StringBuffer result = new StringBuffer();
    boolean space = false;
    for (int i = arg; i < args.length; i++) {
      if (space) result.append(' ');
      else space = true;
      if (args[i] != null) result.append( args[i]);
    }
    return result.length() == 0 ? null : new String(result);
  }

  /**
   * Print help info and exit.
   */
  public static void help() {
    System.err.println("Usage: ksdb <command> [<args>]. Commands:");
    System.err.println("\tnew-suite <name> ['<description>']");
    System.err.println("\t\tCreate new suite with given name and description.");
    System.err.println("\tdelete-suite <name>");
    System.err.println("\t\tDelete suite.");
    System.err.println("\tsuite-contains-document <language> <file> <suite> [<not>]");
    System.err.println("\t\tIndicates whether suite should contain document.");
    System.err.println("\tnew-document <language> <file> ['<title>']");
    System.err.println("\t\tCreate new document from file.");
    System.err.println("\tdelete-document <language> <file>");
    System.err.println("\t\tDelete document.");
    System.err.println("\tshow <args>");
    System.err.println("\t\tExecute a SQL 'show' command on KSDB.");
    System.err.println("\tselect <query>");
    System.err.println("\t\tExecute a SQL 'select' query on KSDB.");
    System.err.println("\tlock-sentences <write-flag>");
    System.err.println("\t\tLock the database");
    System.err.println("\tunlock-sentences <write-flag>");
    System.err.println("\t\tUnlock the database");
    System.err.println("\treload-document <language> <file>");
    System.err.println("\t\tReload file into database document.");
    System.err.println("\tmodify-sentence <sentence-id> <text>");
    System.err.println("\t\tSpecify new text for sentence.");
    System.err.println("\ttouch-analyzer");
    System.err.println("\t\tIndicate that the analyzer version changed");
    System.err.println("\ttouch-generator");
    System.err.println("\t\tIndicate that the generator version changed");
    System.err.println("\tanalyze-sentences <sentence-query>");
    System.err.println("\t\tRun Analyzer on sentences satisfied by query.");
    System.err.println("\ttranslate-sentences <sentence-query>");
    System.err.println("\t\tRun Generator on sentences using current Analyzer");
    System.err.println("\trate-interlingua <interlingua-id> <score>");
    System.err.println("\t\tAssign a score to an IR.");
    System.err.println("\trate-translation <translation-id> <score>");
    System.err.println("\t\tAssign a score to a translation.");
    System.err.println("\tcomment-interlingua <interlingua-id> [<comment>]");
    System.err.println("\t\tAssign a comment to an IR.");
    System.err.println("\tcomment-translation <translation-id> [<comment>]");
    System.err.println("\t\tAssign a comment to a translation.");
    System.err.println("\thelp");
    System.err.println("\t\tPrint this help.");
    System.exit(1);
  }

  /**
   * An interpreter interface to the KANTOO Sentence Database
   *
   * @param args commands to send to the KSDB
   */
  public static void main(String[] args) throws IOException, SQLException {
    PropertyManager.getArgs( args);

    Ksdb ksdb = new Ksdb( System.getProperty("user.name"));
    
    String cmd = args[0];
    if (cmd == null) help();
    cmd = cmd.toLowerCase();
    try {
      if (cmd.equals("new-suite")) {
	String name = (args.length > 1) ? args[1] : null;
	String description = argsRemainder( args, 2);
	int suite = ksdb.newSuite( name, description);
	System.out.println("New suite: id=" + suite
			   + " name="+ name
			   + " description=" + description);

      } else if (cmd.equals("delete-suite")) {
	int suite = (args.length > 1) ? getSuite( ksdb,args[1]) : 0;
	ksdb.deleteSuite( suite);
	System.out.println("Suite id=" + suite + " name="+args[1]+" deleted.");

      } else if (cmd.equals("suite-contains-document")) {
	int document = (args.length>2) ? getDocument(ksdb,args[1],args[2]) : 0;
	int suite = (args.length > 3) ? getSuite( ksdb,args[3]) : 0;
	boolean flag = !(args.length > 4);
	ksdb.suiteContainsDocument( suite, document, flag);
	System.out.println("Suite id=" +suite+ (flag ? " now " : " no longer ")
			   + "contains document id=" + document);

      } else if (cmd.equals("new-document")) {
	int language = (args.length > 1) ? getLanguage( ksdb, args[1]) : 0;
	File filename = (args.length > 2) ? new File( args[2]) : null;
	String title = argsRemainder( args, 3);
	int document = ksdb.newDocument( language, filename, title);
	System.out.println("New document: id=" + document
			   + " language=" + language
			   + " file="+ filename
			   + " title=" + title);

      } else if (cmd.equals("delete-document")) {
	int document = (args.length>2) ? getDocument(ksdb,args[1],args[2]) : 0;
	ksdb.deleteDocument( document);
	System.out.println("Document id=" + document
			   + " language=" + args[1] + " file="+ args[2]
			   + " deleted.");

      } else if (cmd.equals("select")) {
	ResultSet rs = ksdb.select( argsRemainder( args, 1));
	System.out.println("Selection Results:");
	ksdb.sql.outputResultSet( System.out, rs);

      } else if (cmd.equals("show")) {
	ResultSet rs = ksdb.show( argsRemainder( args, 1));
	System.out.println("Show Results:");
	ksdb.sql.outputResultSet( System.out, rs);

      } else if (cmd.equals("lock-sentences")) {
	boolean write = (args[1].charAt(0) == 'w');
	ksdb.lockSentences( write);
	System.out.println("Lock Successful.");

      } else if (cmd.equals("unlock-sentences")) {
	boolean write = (args[1].charAt(0) == 'w');
	ksdb.unlockSentences( write);
	System.out.println("Unlock Successful.");

      } else if (cmd.equals("reload-document")) {
	ksdb.lockSentences( true);
	int document = (args.length>2) ? getDocument(ksdb,args[1],args[2]) : 0;
	int result = ksdb.reloadDocument( document);
	ksdb.unlockSentences( true);
	System.out.println("Document id=" + document
			   + " language=" + args[1] + " file="+ args[2]
			   + " modified. New document id=" + result);

      } else if (cmd.equals("modify-sentence")) {
	ksdb.lockSentences( true);
	int sentence = (args.length > 1) ? Integer.parseInt( args[1]) : 0;
	String text = argsRemainder( args, 2);
	ksdb.modifySentence( sentence, text);
	ksdb.unlockSentences( true);
	System.out.println("Sentence id=" + sentence + " modified.");

      } else if (cmd.equals("touch-analyzer")) {
	ksdb.touchServer(true, 0);
	System.out.println("Touch successful");

      } else if (cmd.equals("touch-generator")) {
	ksdb.touchServer(false, 0);
	System.out.println("Touch successful");

      } else if (cmd.equals("analyze-sentences")) {
	ResultSet rs = ksdb.select( argsRemainder( args, 1));
	ksdb.lockSentences( false);
	SQLConnection.last( rs);
	int size = SQLConnection.getRow( rs) + 1;
	ProgressMonitor monitor = new ProgressMonitor( null, new Object[] { "Analyzing " + size + " sentences:" }, null, 0, size);
	ksdb.analyzeSentences( rs, monitor);
	ksdb.unlockSentences( false);
	System.out.println("Analyses complete.");

      } else if (cmd.equals("translate-sentences")) {
	ResultSet rs = ksdb.select( argsRemainder( args, 1));
	ksdb.lockSentences( false);
	SQLConnection.last( rs);
	int size = SQLConnection.getRow( rs) + 1;
	ProgressMonitor monitor = new ProgressMonitor( null, new Object[] { "Translating " + size + " sentences:" }, null, 0, size);
	ksdb.translateSentences( rs, 0, monitor);
	ksdb.unlockSentences( false);
	System.out.println("Translation complete.");

      } else if (cmd.equals("rate-interlingua")) {
	ksdb.lockSentences( false);
	int ir = (args.length > 1) ? Integer.parseInt( args[1]) : 0;
	String score = (args.length > 2) ? args[2] : null;
	ksdb.rateInterlingua( ir, score);
	ksdb.unlockSentences( false);
	System.out.println("Interlingua id=" + ir + " scored as " + score);

      } else if (cmd.equals("rate-translation")) {
	ksdb.lockSentences( false);
	int trans = (args.length > 1) ? Integer.parseInt( args[1]) : 0;
	String score = (args.length > 2) ? args[2] : null;
	ksdb.rateTranslation( trans, score);
	ksdb.unlockSentences( false);
	System.out.println("Translation id=" + trans + " scored as " + score);

      } else if (cmd.equals("comment-interlingua")) {
	ksdb.lockSentences( false);
	int ir = (args.length > 1) ? Integer.parseInt( args[1]) : 0;
	String comment = argsRemainder( args, 2);
	ksdb.commentInterlingua( ir, comment);
	ksdb.unlockSentences( false);
	System.out.println("Interlingua id=" + ir + " commentd as " + comment);

      } else if (cmd.equals("comment-translation")) {
	ksdb.lockSentences( false);
	int trans = (args.length > 1) ? Integer.parseInt( args[1]) : 0;
	String comment = argsRemainder( args, 2);
	ksdb.commentTranslation( trans, comment);
	System.out.println("Translation id=" + trans + " commentd as " + comment);
	ksdb.unlockSentences( false);

      } else if (cmd.equals("help") || cmd.equals("?")) help();
      else {
	StringBuffer msg = new StringBuffer("Invalid arguments:");
	for (int i = 0; i < args.length; i++)
	  if (args[i] != null)
	    msg.append(" " + args[i]);
	System.err.println( msg);
	help();
      }
    } catch (Exception e) {
      e.printStackTrace();
      System.exit(-1);
    }

    System.exit(0);
  }
}