Quadcap Bug Database

com/quadcap/app/bugdb/BugSession.java

Go to the documentation of this file.

package com.quadcap.app.bugdb;

/* Copyright 1999 - 2003 Quadcap Software.  All rights reserved.
 *
 * This software is distributed under the Quadcap Free Software License.
 * This software may be used or modified for any purpose, personal or
 * commercial.  Open Source redistributions are permitted.  Commercial
 * redistribution of larger works derived from, or works which bundle
 * this software requires a "Commercial Redistribution License"; see
 * http://www.quadcap.com/purchase.
 *
 * Redistributions qualify as "Open Source" under  one of the following terms:
 *   
 *    Redistributions are made at no charge beyond the reasonable cost of
 *    materials and delivery.
 *
 *    Redistributions are accompanied by a copy of the Source Code or by an
 *    irrevocable offer to provide a copy of the Source Code for up to three
 *    years at the cost of materials and delivery.  Such redistributions
 *    must allow further use, modification, and redistribution of the Source
 *    Code under substantially the same terms as this license.
 *
 * Redistributions of source code must retain the copyright notices as they
 * appear in each source code file, these license terms, and the
 * disclaimer/limitation of liability set forth as paragraph 6 below.
 *
 * Redistributions in binary form must reproduce this Copyright Notice,
 * these license terms, and the disclaimer/limitation of liability set
 * forth as paragraph 6 below, in the documentation and/or other materials
 * provided with the distribution.
 *
 * The Software is provided on an "AS IS" basis.  No warranty is
 * provided that the Software is free of defects, or fit for a
 * particular purpose.  
 *
 * Limitation of Liability. Quadcap Software shall not be liable
 * for any damages suffered by the Licensee or any third party resulting
 * from use of the Software.
 */

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.PrintWriter;

import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Properties;
import java.util.Vector;


import java.sql.DriverManager;
import java.sql.SQLException;

import java.net.URLDecoder;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import javax.servlet.http.HttpSessionBindingEvent;
import javax.servlet.http.HttpSessionBindingListener;
import javax.servlet.http.HttpSessionContext;

/**
 * A session bean encapsulating access to the Quadcap Bug Database.
 * JSP pages use methods of this bean to perform the various functions
 * of the bug database application.
 * 
 * @author Stan Bailes
 */
public class BugSession implements HttpSessionBindingListener {
    Connection                  conn;

    /** My user name.  'nobody' until logged in. */
    String                      me              = "nobody";

    String                      my_person_type  = null;
    boolean                     isAdmin         = false;
    boolean                     isManager       = false;
    boolean                     isDeveloper     = false;
    boolean                     isUser          = false;

    static Object               lock            = new Object();

    Vector                      debug_history   = new Vector();

    PreparedStatement           getComponentOwner;
    PreparedStatement           insertBugHist;
    PreparedStatement           insertPerson;
    PreparedStatement           getBugHist;
    PreparedStatement           getBug;
    PreparedStatement           getQuery;
    PreparedStatement           saveQuery;
    PreparedStatement           getUser;
    PreparedStatement           updateUser;
    PreparedStatement           deleteUser;
    PreparedStatement           getProject;
    PreparedStatement           newProject;
    PreparedStatement           deleteProject;
    PreparedStatement           updateProject;

    final String                driverName      = "com.quadcap.jdbc.JdbcDriver";
    final String                dbUrl           = "jdbc:qed:bugdb;create=true";
    final String                dbUser          = "bugdb";
    final String                dbPass          = "bugdb";

    /**
     * Create a new session.
     */
    public BugSession() {
        // Make sure we're connected to the database.  If we're absolutely
        // the first user, load the schema.
        try {
            Class.forName(driverName);
            getConnection();
            prepareStatements();
            checkSchema();
        } catch (Exception e) {
            e.printStackTrace(System.out);
            throw new RuntimeException(e.toString());
        }
    }

    /**
     * Return the JDBC connection for this session.
     *
     * @return this session's connection.
     */
    public Connection getConnection() throws SQLException {
        if (conn == null) {
            Properties props = new Properties();
            props.put("user", dbUser);
            props.put("password", dbPass);
            props.put("create", "true");
            this.conn = DriverManager.getConnection(dbUrl, props);
            //conn.setAutoCommit(false);
        }
        return conn;
    }

    /**
     * This application object has just been bound into an HttpSession
     *
     * @param event identifies the session and the name in the
     *          session of this object.
     */
    public void valueBound (HttpSessionBindingEvent event) {
        HttpSession session = event.getSession();
        session.setMaxInactiveInterval(6 * 3600);               // six hours
    }
    
    /**
     * This application object has just been removed from an HttpSession
     *
     * @param event identifies the session and the name in the
     *          session of this object.
     */
    public void valueUnbound (HttpSessionBindingEvent event) {
        logout(null);
        try {
            conn.close();
        } catch (Exception e) {}
    }
    

    /**
     * Prepare the various SQL statements we'll be using.
     *
     * @exception SQLException may be thrown
     */
    private final void prepareStatements() throws SQLException {
        getComponentOwner = conn.prepareStatement(
            "select owner from component where component = ?");

        insertBugHist = conn.prepareStatement(
            "insert into bug_history (bug_id, hist_pos, submitter," +
            " abstract, description, comments, state, priority, type," +
            " owner, component, modtime) values(?,?,?,?,?,?,?,?,?,?,?,?)");

        insertPerson = conn.prepareStatement(
                "insert into person(name,password,email,person_type) " +
                "values(?,?,?,?,?)");

        getBugHist = conn.prepareStatement("select hist_size from bug where" +
                                           " bug_id = ? for update",
                                           ResultSet.TYPE_SCROLL_SENSITIVE,
                                           ResultSet.CONCUR_UPDATABLE);
        getBug = conn.prepareStatement("select * from bug_history " +
                                       "where bug_id = ? " +
                                       "order by hist_pos desc");
        getQuery = conn.prepareStatement("select query from queries " +
                                         "where owner = ? and name = ?");
        saveQuery = conn.prepareStatement("insert into queries values(?,?,?)");
        getUser = conn.prepareStatement("select * from person where name=?");
        updateUser = conn.prepareStatement(
            "select * from person where name=? for update",
            ResultSet.TYPE_SCROLL_SENSITIVE,
            ResultSet.CONCUR_UPDATABLE);
        deleteUser = conn.prepareStatement("delete from person where name=?");
        getProject = conn.prepareStatement(
            "select * from component where component=?");
        newProject = conn.prepareStatement(
            "insert into component values(?,?)");
        updateProject = conn.prepareStatement(
            "select * from component where component=? for update",
            ResultSet.TYPE_SCROLL_SENSITIVE,
            ResultSet.CONCUR_UPDATABLE);
        deleteProject = conn.prepareStatement(
            "delete from component where component=?");
    }

    /**
     * Check to make sure the database schema exists.  If not, load it
     * from the <code>bugdb.sql</code> file.
     *
     * @exception SQLException may be thrown.
     */
    private final void checkSchema() throws SQLException {
        synchronized (lock) {
            Statement s = conn.createStatement();
            ResultSet rs = null;
            try {
                rs = s.executeQuery("select * from singleton");
            } catch (SQLException e) {
                Loader loader = new Loader();
                loader.setConnection(conn);
                ClassLoader c = this.getClass().getClassLoader();
                InputStream is = c.getResourceAsStream(
                    "com/quadcap/app/bugdb/bugdb.sql");
                loader.loadStream(is);
            } finally {
                if (rs != null) rs.close();
                s.close();
            }
        }
    }

    /**
     * Is this session associated with a valid user?
     *
     * @return true if the user is valid.
     */
    public boolean isUser() { return isUser; }

    /**
     * Is this session associated with a user with 'developer' privileges?
     *
     * @return true if the user is valid and has developer privileges.
     */
    public boolean isDeveloper() { return isDeveloper; }

    /**
     * Is this session associated with a user with 'manager' privileges?
     *
     * @return true if the user is valid and has manager privileges.
     */
    public boolean isManager() { return isManager; }

    /**
     * Is this session associated with a user with 'administrator' privileges?
     *
     * @return true if the user is valid and has administrator privileges.
     */
    public boolean isAdmin() { return isAdmin; }


    /**
     * Return the saved bug search query with the specified name
     *
     * @param name the name of the query
     * @return the query string
     * @exception ServletException may be thrown
     */
    public String getQuery(String name) throws ServletException {
        try {
            getQuery.clearParameters();
            getQuery.setString(1, me);
            getQuery.setString(2, name);
            ResultSet rs = getQuery.executeQuery();
            try {
                if (rs.next()) {
                    return rs.getString(1);
                } else {
                    return "";
                }
            } finally {
                rs.close();
            }
        } catch (SQLException ex) {
            throw new ServletException(ex.toString());
        }
    }

    /**
     * Save a bug search query.
     * 
     * @param name the query name
     * @param query the query string
     * @exception ServletException may be thrown
     */
    public void saveQuery(String name, String query) throws ServletException {
        try {
            saveQuery.clearParameters();
            saveQuery.setString(1, name);
            saveQuery.setString(2, query);
            saveQuery.setString(3, me);
            saveQuery.executeUpdate();
        } catch (SQLException ex) {
            throw new ServletException(ex.toString());
        }
    }

    /**
     * Execute the 'logout' action.
     *
     * @param props the request properties for this action.  Ignored.
     *
     */
    public boolean logout(Properties props) {
        me = "nobody";
        isAdmin = isManager = isDeveloper = isUser = false;
        if (conn != null) {
            try {
                conn.close();
            } catch (Throwable t) {}
            conn = null;
        }
        return true;
    }

    /**
     * Execute the 'login' action.
     *
     * @param props the request properties for this action. 
     *  <dl>
     *  <dt>username</dt><dd>The user's login name</dd>
     *  <dt>password</dt><dd>The user's password</dd>
     *  </dl>
     *
     * @exception SQLException may be thrown
     */
    public boolean login(Properties props) throws SQLException {
        String user = getString(props, "username");
        String pass = getString(props, "password");
        getConnection();
        Statement s = conn.createStatement();
        try {
            ResultSet rs = s.executeQuery(
                "select * from person where name = '" + user + 
                "' and password = '" + pass + "'");
            try {
                if (rs.next()) {
                    me = user;
                    my_person_type = rs.getString("person_type");
                    isAdmin = my_person_type.equals("Administrator");
                    isManager = isAdmin || my_person_type.equals("Manager");
                    isDeveloper = isManager || my_person_type.equals("Developer");
                    isUser = isDeveloper || my_person_type.equals("User");
                    return true;
                }
            } finally {
                rs.close();
            }
        } finally {
            s.close();
        }
        return false;
    }

    /**
     * Return the next integer in the specified sequence. 
     *
     * @return the next integer in this sequence.
     * @exception SQLException may be thrown
     */
    int getNextId(String type) throws SQLException {
        Statement s = conn.createStatement(
            ResultSet.TYPE_SCROLL_SENSITIVE,
            ResultSet.CONCUR_UPDATABLE);
        try {
            int ret = 0;
            ResultSet rs = s.executeQuery(
                "select ivalue from singleton where name = '" + type + "'");
            try {
                if (rs.next()) {
                    ret = rs.getInt(1);
                    rs.updateInt(1, ret + 1);
                    rs.updateRow();
                }
            } finally {
                rs.close();
            }
            return ret;
        } finally {
            s.close();
        }
    }

    /**
     * Return the number of records in the bug history for the specified
     * bug.
     * 
     * @param bug_id the bug number
     * @return the number of history records for this bug.
     * @exception SQLException may be thrown
     */
    int getBugHistSize(int bug_id) throws SQLException {
        getBugHist.clearParameters();
        getBugHist.setInt(1, bug_id);
        ResultSet rs = getBugHist.executeQuery();
        try {
            if (rs.next()) {
                int ret = rs.getInt(1) + 1;
                rs.updateInt(1, ret);
                rs.updateRow();
                return ret;
            } else {
                throw new SQLException("no bug: " + bug_id);
            }
        } finally {
            rs.close();
        }
    }

    /**
     * Execute the 'addUser' action.
     *
     * @param props the request properties for this action:<p> 
     *  <dl>
     *  <dt>name</dt><dd>The user's login name</dd>
     *  <dt>password</dt><dd>The user's password</dd>
     *  <dt>password2</dt><dd>The user's password confirmation</dd>
     *  <dt>email</dt><dd>The user's email address</dd>
     *  <dt>person_type</dt><dd>The user's 'class', one of
     *      <ul><li>User</li>
     *          <li>Developer</li>
     *          <li>Manager</li>
     *          <li>Administrator</li>
     *      </ul></dt>
     *  </dl>
     *
     * @exception SQLException may be thrown
     */
    public boolean addUser(Properties props) 
        throws SQLException, ServletException 
    {
        String p1 = getString(props, "password");
        String p2 = getString(props, "password2");
        if (!p1.equals(p2)) {
            throw new ServletException(
                "Password doesn't match confirm password");
        }
        insertPerson.clearParameters();
        insertPerson.setString(1, getString(props, "name"));
        insertPerson.setString(2, getString(props, "password"));
        insertPerson.setString(3, getString(props, "email"));
        
        String person_type = "User";
        if (isAdmin) {
            String t = getString(props, "person_type");
            if (t != null && t.length() > 0) person_type = t;
        }
        insertPerson.setString(4, person_type);
            
        return 1 == insertPerson.executeUpdate();
    }

    /**
     * Execute the 'updateUser' action.
     * @param props the request properties for this action:<p> 
     *  <dl>
     *  <dt>name</dt><dd>The user's login name</dd>
     *  <dt>password</dt><dd>The user's password</dd>
     *  <dt>email</dt><dd>The user's email address</dd>
     *  <dt>person_type</dt><dd>The user's 'class', one of
     *      <ul><li>User</li>
     *          <li>Developer</li>
     *          <li>Manager</li>
     *          <li>Administrator</li>
     *      </ul></dt>
     *  </dl>
     *
     * @exception SQLException may be thrown
     */
    public boolean updateUser(Properties p) throws SQLException {
        updateUser.clearParameters();
        updateUser.setString(1, p.getProperty("name"));
        ResultSet rs = updateUser.executeQuery();
        try {
            if (rs.next()) {
                if (!p.getProperty("password").equals("password")) {
                    rs.updateString("password", p.getProperty("password"));
                }
                rs.updateString("email", p.getProperty("email"));
                rs.updateString("person_type", p.getProperty("person_type"));
                rs.updateRow();
                return true;
            } else {
                return false;
            }
        } finally {
            rs.close();
        }
    }

    /**
     * Execute the 'deleteUser' action
     *
     * @exception SQLException may be thrown
     */
    public boolean deleteUser(Properties p) throws SQLException {
        deleteUser.clearParameters();
        deleteUser.setString(1, p.getProperty("name"));
        return deleteUser.executeUpdate() == 1;
    }
        
    /**
     * Generate an HTML <code>&lt;SELECT&gt;</code> element listing all
     * elements in the specified table.
     *
     * @param type the table (e.g., priority, state, etc.)
     * @return the HTML string.
     */
    public String listOptions(String type) 
        throws ServletException 
    {
        return listOptions(type, null);
    }

    /**
     * Generate an HTML <code>&lt;SELECT&gt;</code> element listing all
     * elements in the specified table.
     *
     * @param type the table (e.g., priority, state, etc.) Also the name
     *          of the SELECT element, and the name of the column.
     * @param sel if non null, the value to be initially selected
     * @return the HTML string.
     */
    public String listOptions(String type, String sel) 
        throws ServletException 
    {
        return listOptions(type, type, sel);
    }

    /**
     * Generate an HTML <code>&lt;SELECT&gt;</code> element listing all
     * elements in the specified table.
     *
     * @param table the table (e.g., priority, state, etc.) Also the name
     *          of the SELECT element.
     * @param type the name of the column containing the enumerated values.
     * @param sel if non null, the value to be initially selected
     * @return the HTML string.
     */
    public String listOptions(String table, String type, String sel) 
        throws ServletException 
    {
        return listOptions(table, table, type, sel);
    }

    /**
     * Generate an HTML <code>&lt;SELECT&gt;</code> element listing all
     * elements in the specified table.
     *
     * @param selType the name of the SELECT widget.
     * @param table the table (e.g., priority, state, etc.)
     * @param type the name of the column containing the enumerated values.
     * @param sel if non null, the value to be initially selected
     * @return the HTML string.
     */
    public String listOptions(String seltype, String table, 
                              String type, String sel) 
        throws ServletException 
    {
        try {
            Statement s = conn.createStatement();
            StringBuffer sb = new StringBuffer("\n<select name=\"");
            sb.append(seltype);
            sb.append("\">");
            try {
                ResultSet rs = s.executeQuery("select " + type +
                                              " from " + table);
                try {
                    while (rs.next()) {
                        String t = rs.getString(1);
                        sb.append("\n  <option value=\"");
                        sb.append(t);
                        sb.append('"');
                        if (sel != null && t.equals(sel)) {
                            sb.append(" selected");
                        }
                        sb.append(">");
                        sb.append(t);
                        sb.append("</option>");
                    }
                } finally {
                    rs.close();
                }
                sb.append("</select>");
                return sb.toString();
            } finally {
                s.close();
            }
        } catch (SQLException e) {
            throw new ServletException(e.toString());
        }
    }

    /** 
     * Generate an HTML <code>&lt;SELECT&gt;</code> element listing all
     * users.
     *
     * @param type the name of the SELECT element.
     * @param sel the value (if any) to be initially selected.
     */
    public String listUsers(String type, String sel) throws ServletException {
        return listOptions(type, "person", "name", sel);
    }

    /**
     * Return the name of the user who owns the specified component. 
     *
     * @param component the component name
     * @return the component's owner
     * @exception SQLException may be thrown
     * @exception ServletException may be thrown
     */
    public String getComponentOwner(String component)
        throws SQLException, ServletException
    {
        synchronized (getComponentOwner) {
            getComponentOwner.clearParameters();
            getComponentOwner.setString(1, component);
            ResultSet rs = getComponentOwner.executeQuery();
            try {
                if (rs.next()) {
                    return rs.getString(1);
                } else {
                    return "nobody";
                }
            } finally {
                rs.close();
            }
        }
    }
    
    /**
     * Return an integer valued property
     *
     * @param props a properties object
     * @param name the name of the property to retrieve.
     * @return the property value
     * @exception ServletException is thrown if the property isn't a valid
     *  integer.
     */
    static int getInt(Properties props, String name) throws ServletException {
        try {
            return Integer.parseInt(props.getProperty(name));
        } catch (Throwable e) {
            throw new ServletException("Missing or malformed parameter: " +
                                       name);
        }
    }

    /**
     * Return a string-valued property.
     *
     * @param props a properties object
     * @param name the name of the property to retrieve.
     * @return the property value, or the empty string if the property
     *  isn't defined.
     */
    static String getString(Properties props, String name) {
        String s = props.getProperty(name);
        if (s == null) return "";
        return s;
    }
    
    /**
     * Execute the 'addBug' action.
     *
     * @param props the request properties, contains values for the various
     *   fields in the 'bug' and 'bug_history' tables.
     *
     * @return true if this action succeeded.
     * @exception ServletException may be thrown.
     */
    public boolean addBug(Properties props) throws ServletException {
        if (!isUser()) throw new ServletException("not logged in");
        try {
            Statement s = conn.createStatement();
            try {
                int bug_id = getNextId("bug");
                int ret = s.executeUpdate(
                    "insert into bug(bug_id, hist_size) values(" + 
                    bug_id + ",0)");
                if (ret != 1) {
                    throw new ServletException("add bug failed");
                }

                String component = getString(props, "component");
                String owner = getComponentOwner(component);

                insertBugHist.clearParameters();
                insertBugHist.setInt(1, bug_id);        // bug_id
                insertBugHist.setInt(2, 0);             // hist_pos
                insertBugHist.setString(3, me);         // submitter
                insertBugHist.setString(4, getString(props, "abstract"));
                insertBugHist.setString(5, getString(props, "description"));
                insertBugHist.setString(6, getString(props, "comments"));
                insertBugHist.setString(7, "Active");   // state
                insertBugHist.setString(8, getString(props, "priority"));
                insertBugHist.setString(9, getString(props, "type"));
                insertBugHist.setString(10, owner);     // owner
                insertBugHist.setString(11, component); // component
                insertBugHist.setTimestamp(12, now());  // modtime
                ret = insertBugHist.executeUpdate();
                if (ret != 1) {
                    throw new ServletException("add bug history failed, ret = " + ret);
                }
                return true;
            } finally {
                s.close();
            }
        } catch (SQLException e) {
            throw new ServletException(e.toString());
        }
    }

    /**
     * Execute the 'updateBug' action.
     *
     * @param props the request properties, contains values for the various
     *   fields in the 'bug' and 'bug_history' tables.
     *
     * @return true if this action succeeded.
     * @exception ServletException may be thrown.
     */
    public boolean updateBug(Properties props) throws ServletException {
        if (!isUser()) throw new ServletException("not logged in");
        try {
            int bug_id = getInt(props, "bug_id");
            int hist_pos = getBugHistSize(bug_id);

            String component = getString(props, "component");
            String owner = getComponentOwner(component);

            insertBugHist.clearParameters();
            insertBugHist.setInt(1, bug_id);    // bug_id
            insertBugHist.setInt(2, hist_pos);          // hist_pos
            insertBugHist.setString(3, me);             // submitter
            insertBugHist.setString(4, getString(props, "abstract"));
            insertBugHist.setString(5, getString(props, "description"));
            insertBugHist.setString(6, getString(props, "comments"));
            insertBugHist.setString(7, getString(props, "state"));
            insertBugHist.setString(8, getString(props, "priority"));
            insertBugHist.setString(9, getString(props, "type"));
            insertBugHist.setString(10, getString(props, "owner"));
            insertBugHist.setString(11, getString(props, "component"));
            insertBugHist.setTimestamp(12, now());      // modtime
            if (insertBugHist.executeUpdate() != 1) {
                throw new ServletException("update bug history failed");
            }
            return true;
        } catch (SQLException e) {
            throw new ServletException(e.toString());
        }
    }

    /**
     * Make a sql timestamp value representing the current time.
     *
     * @return a timestamp value.
     */
    static final java.sql.Timestamp now() {
        return new java.sql.Timestamp(System.currentTimeMillis());
    }

    /**
     * Utility to do URL decoding without any exception handling. 
     * Lazy bastard.
     * @param s the url to decode
     * @return the decoded url
     */
    public final String urlDecode(String s) {
        try {
            if (s == null) return "";
            return java.net.URLDecoder.decode(s);
        } catch (Throwable e) {
            return s;
        }
    }

    /**
     * Perform the bug search operation.
     *
     * @param query the query string, a boolean predicate selecting bugs
     *  to be returned.
     * @param ob an optional column name to use with an 'order by' clause,
     *  to cause the list of bugs to be sorted based on that column.
     * @return a <code>Vector</code> of <code>Properties</code> objects,
     *  where each Properties element contains entries for each column
     *  in the search result.
     * @exception ServletException may be thrown
     */
    public Vector searchBugs(String query, String ob) throws ServletException {
        Vector v = new Vector();
        try {
            Statement s = conn.createStatement();
            if (query == null) query = "";
            query = query.trim();
            if (query.length() > 0) {
                query = "and " + query;
            }
            if (ob != null) {
                query = query + " order by " + ob;
            }
            StringBuffer sb = new StringBuffer();
            sb.append("select * from bug natural join bug_history where ");
            sb.append("(hist_size = hist_pos) " + query);
            ResultSet rs = s.executeQuery(sb.toString());
            ResultSetMetaData rm = rs.getMetaData();
            try {
                while (rs.next()) {
                    Properties p = new Properties();
                    for (int i = 1; i <= rm.getColumnCount(); i++) {
                        String name = rm.getColumnLabel(i);
                        Object obj = rs.getObject(i);
                        String val = (obj == null) ? "" : obj.toString();
                        p.put(name.toLowerCase(), val);
                    }
                    v.addElement(p);
                }
            } finally {
                rs.close();
            }
        } catch (SQLException e) {
            throw new ServletException(e.toString());
        } catch (Throwable t) {
            t.printStackTrace(System.out);
        }
        return v;
    }

    /**
     * Turn the current row in the result set into a Hashtable.
     *
     * @param rs a result set
     * @return a Hashtable where the keys are the string-valued column labels
     *  of the result set and the values are the string-valued column values
     *  from the current row of the result set.
     *
     * @exception SQLException may be thrown
     */
    final Hashtable getProps(ResultSet rs) throws SQLException {
        Hashtable p = new Hashtable();
        ResultSetMetaData rm = rs.getMetaData();
        for (int i = 1; i <= rm.getColumnCount(); i++) {
            String name = rm.getColumnLabel(i);
            String val = String.valueOf(rs.getObject(i));
            p.put(name.toLowerCase(), val);
        }
        return p;
    }

    /**
     * Return a list of valid users, as a Vector of Properties objects,
     * where each Hashtable object contains all of the user's properties
     * (except the password!)
     *
     * @return a list of valid users
     * @exception SQLException may be thrown
     */
    public Vector getUsers() throws SQLException {
        Vector v = new Vector();
        Statement s = conn.createStatement();
        try {
            ResultSet rs = s.executeQuery("select * from person");
            try {
                while (rs.next()) {
                    Hashtable t = getProps(rs);
                    t.remove("password");
                    v.addElement(t);
                }
            } finally {
                rs.close();
            }
        } finally {
            s.close();
        }
        return v;
    }

    /**
     * Return a list of valid project/components, as a Vector of Hashtable
     * objects,
     * where each Hashtable object contains all of the user's properties
     * (except the password!)
     *
     * @return a list of valid components
     * @exception SQLException may be thrown
     */
    public Vector getProjects() throws SQLException {
        Vector v = new Vector();
        Statement s = conn.createStatement();
        try {
            ResultSet rs = s.executeQuery("select * from component");
            try {
                while (rs.next()) {
                    Hashtable t = getProps(rs);
                    v.addElement(t);
                }
            } finally {
                rs.close();
            }
        } finally {
            s.close();
        }
        return v;
    }

    /**
     * Return the properties for a single user.
     *
     * @param user the user name
     * @return the user's properties (except the password)
     * @exception SQLException may be thrown
     */
    public Hashtable getUser(String user) throws SQLException {
        getUser.clearParameters();
        getUser.setString(1, user);
        ResultSet rs = getUser.executeQuery();
        try {
            if (rs.next()) {
                Hashtable t = getProps(rs);
                t.remove("password");
                return t;
            } else {
                return null;
            }
        } finally {
            rs.close();
        }
    }

    /**
     * Return the properties for a single component.
     *
     * @param name the component name
     * @return the component's properties
     * @exception SQLException may be thrown
     */
    public Hashtable getProject(String user) throws SQLException {
        getProject.clearParameters();
        getProject.setString(1, user);
        ResultSet rs = getProject.executeQuery();
        try {
            if (rs.next()) {
                Hashtable t = getProps(rs);
                return t;
            } else {
                return null;
            }
        } finally {
            rs.close();
        }
    }

    /**
     * Execute the 'newProject' action.
     *
     * @param props the request properties for this action:<p> 
     *  <dl>
     *  <dt>component</dt><dd>The name of the project</dd>
     *  <dt>owner</dt><dd>The owner of the project.</dd>
     *  </dl>
     *
     * @exception SQLException may be thrown
     */
    public boolean newProject(Properties p) throws SQLException {
        newProject.clearParameters();
        newProject.setString(1, p.getProperty("component"));
        newProject.setString(2, p.getProperty("owner"));
        return 1 == newProject.executeUpdate();
    }

    /**
     * Execute the 'updateProject' action.
     * @param props the request properties for this action:<p> 
     *  <dl>
     *  <dt>component</dt><dd>The name of the project</dd>
     *  <dt>owner</dt><dd>The owner of the project.</dd>
     *  </dl>
     *
     * @exception SQLException may be thrown
     */
    public boolean updateProject(Properties p) throws SQLException {
        updateProject.clearParameters();
        updateProject.setString(1, p.getProperty("component"));
        ResultSet rs = updateProject.executeQuery();
        try {
            if (rs.next()) {
                rs.updateString("owner", p.getProperty("owner"));
                rs.updateRow();
                return true;
            } else {
                return false;
            }
        } finally {
            rs.close();
        }
    }

    /**
     * Execute the 'deleteProject' action
     *
     * @exception SQLException may be thrown
     */
    public boolean deleteProject(Properties p) throws SQLException {
        deleteProject.clearParameters();
        deleteProject.setString(1, p.getProperty("component"));
        return deleteProject.executeUpdate() == 1;
    }
        
    /**
     * The properties that we ignore when computing deltas between
     * successive bug history records.
     */
    static Hashtable nonDeltas          = new Hashtable();
    static {
        nonDeltas.put("modtime", "");
        nonDeltas.put("comments", "");
    }

    /**
     * Given two bug history records, return a hashtable containing entries
     * for each field which changed between the two records.
     *
     * @param a the newer history record
     * @param b the older history record
     * @return a Hashtable where the keys are the names of the changed fields
     *  and the values are strings of the form:<p>
     *  <pre>Changed from <i>bval</i> to <i>aval</i></pre>
     */
    Hashtable diffProps(Hashtable a, Hashtable b) {
        Hashtable t = new Hashtable();
        Enumeration e = a.keys();
        while (e.hasMoreElements()) {
            String key = (String)e.nextElement();
            if (key.equals("history")) continue;
            if (key.equals("hist_pos")) continue;
            String aval = (String)a.get(key);
            String bval = (String)b.get(key);
            if (!aval.equals(bval)) {
                if (nonDeltas.get(key) != null) {
                    t.put(key, bval);
                } else {
                    t.put(key, "Changed from " + bval + " to " + aval);
                }
            }
        }
        return t;
    }

    /**
     * Return the current information and history for the specified bug.
     * 
     * @param bug_id
     * @return a Hashtable where the keys are the names of the bug
     *  properties and the values are the current (i.e., most recent)
     *  values for those properties.  The special key <i>"history"</i>
     *  has a Vector of Hashtables value containing the reverse-chronological
     *  history deltas for the bug.
     *
     * @exception ServletException may be thrown
     */
    public Hashtable getBug(String bug_id) throws ServletException {
        try {
            getBug.clearParameters();
            getBug.setInt(1, Integer.parseInt(bug_id));
            ResultSet rs = getBug.executeQuery();
            try {
                Hashtable ret = null;
                Vector hist = new Vector();
                Hashtable last = null;
                String description = "";
                while (rs.next()) {
                    Hashtable props = getProps(rs);
                    description = rs.getString("Description");
                    if (ret == null) {
                        ret = props;
                        ret.put("history", hist);
                    } else {
                        hist.addElement(diffProps(last, props));
                    }
                    last = props;
                }
                // take the 'last' description.  Cause the results are returned
                // newest first, this gives us the original bug description.
                if (ret == null) ret = new Hashtable();
                ret.put("description", description);
                return ret;
            } finally {
                rs.close();
            }
        } catch (SQLException e) {
            throw new ServletException(e.toString());
        }
    }

    /**
     * Replace occurrences of &lt;, &gt;, and &amp; with their HTML
     * entity-representation equivalents.
     * 
     * @param the the string to encode
     * @return the encoded string
     */
    public String htmlEncode(String s) {
        try {
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            PrintWriter pw = new PrintWriter(bos);
            HtmlWriter w = new HtmlWriter(pw);
            for (int i = 0; i < s.length(); i++) {
                w.write(s.charAt(i));
            }
            w.flush();
            return bos.toString();
        } catch (Exception e) {
            return s;
        }
    }

    /**
     * The main request handler.  Determine which action is to be invoked
     * and dispatch the appropriate routine.
     *
     * @param req the http request
     * @exception ServletException may be thrown
     * @exception SQLException may be thrown
     */
    public void handleRequest(HttpServletRequest req) 
        throws ServletException, SQLException 
    {
        String action = req.getParameter("action");
        boolean commit = false;
        try {
            if (action != null) {
                Properties p = getRequestProperties(req);
                if (action.equals("login")) {
                    commit = login(p);
                } else if (action.equals("logout")) {
                    commit = logout(p);
                } else if (!isUser()) {
                    throw new ServletException("not logged in");
                } else if (action.equals("newbug")) {
                    commit = addBug(p);
                } else if (action.equals("modify")) {
                    commit = updateBug(p);
                } else if (action.equals("register")) {
                    commit =addUser(p);
                } else if (action.equals("updateUser")) {
                    commit = updateUser(p);
                } else if (action.equals("deleteUser")) {
                    commit = deleteUser(p);
                } else if (action.equals("newUser")) {
                    commit = addUser(p);
                } else if (action.equals("newProject")) {
                    commit = newProject(p);
                } else if (action.equals("updateProject")) {
                    commit = updateProject(p);
                } else if (action.equals("deleteProject")) {
                    commit = deleteProject(p);
                } else if (action.equals("noop")) {
                } else {
                    throw new ServletException("Bad action: " + action);
                }
            } else if (!isUser()) {
                throw new ServletException("not logged in");
            }
        } finally {
//              if (commit && conn != null) {
//                  conn.commit();
//              } else {
//                  conn.rollback();
//              }
        }
    }

    /**
     * Build a Properties object containing all of the request parameters.
     *
     * @param req the http request
     * @return the request parameters
     */
    public Properties getRequestProperties(HttpServletRequest req) {
        Properties p = new Properties();
        Enumeration e = req.getParameterNames();
        while (e.hasMoreElements()) {
            String name = (String)e.nextElement();
            String val = req.getParameter(name);
            try {
                val = URLDecoder.decode(val);
            } catch (Throwable ex) {}
            p.put(name, val);
        }
        return p;
    }
}