package edu.mit.lcs;

import java.sql.*;
import javax.sql.*;
import javax.naming.*;
import java.util.*;


public class SqlAccess {

    public String dbName;
    Context env = null;
    DataSource source = null;
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;
    

    public SqlAccess() throws NamingException,SQLException {
	this("jdbc/admin");
    }

    public SqlAccess(String dbName) throws NamingException,SQLException {
	//System.out.println("Creating SqlAccess with db=" + dbName);
	this.dbName = dbName;
	env = (Context) new InitialContext().lookup("java:comp/env");
	//System.out.println("Got env java:comp/env = " + env.toString());
	source = (DataSource) env.lookup(dbName);
	//System.out.println("Got datasource=" + source.toString());
	con = source.getConnection();
	//System.out.println("Got Connection=" + con.toString());
	//stmt = con.createStatement();
	//System.out.println("Got Statement=" + stmt.toString());
    }

    public void close() {
	try {
	    if (stmt!=null) {
		stmt.close();
		stmt = null;
	    }
	    if (con!=null) {
		con.close();
		con = null;
	    }
	} catch (SQLException e) {
	    System.err.println("Error closing SqlAccess: " + e);
	}
    }

    /** 
     * retrieve retrieves a single row from a SQL relation, as
     * specified by the sql argument.
     * If nothing matched the query or an error occurs, the result is
     * null.
     */
    public Entity retrieve(String sql) {
	try {
	    stmt = con.createStatement();
	    rs = stmt.executeQuery(sql);
	    return (rs.next()) ? new Entity(rs) : null;
	} catch (SQLException e) {
	    return null;
	} finally {
	    if (rs!=null) {
		try { 
		    rs.close();
		    rs = null;
		} 
		catch (Exception e) {}
	    }
	    if (stmt!=null) {
		try {
		    stmt.close(); 
		    stmt = null;
		} 
		catch (Exception e) {}
	    }
	}
    }

    public ArrayList retrieveAll (String sql) {
	try {
	    stmt = con.createStatement();
	    ArrayList a = new ArrayList();
	    rs = stmt.executeQuery(sql);
	    while (rs.next()) {
		a.add(new Entity(rs));
	    }
	    return a;
	} catch (SQLException e) {
	    return null;
	} finally {
	    if (rs!=null) {
		try { 
		    rs.close();
		    rs = null;
		} 
		catch (Exception e) {}
	    }
	    if (stmt!=null) {
		try {
		    stmt.close(); 
		    stmt = null;
		} 
		catch (Exception e) {}
	    }
	}
    }

    /** Retrieves metadata for the current database.
     * for mysql, five columns are retrieved for each table:
     * TABLE_TYPE is always TABLE
     * TABLE_SCHEM is null
     * TABLE_CAT is the name of the database
     * REMARKS are typically null
     * TABLE_NAME the name of the table.
     */

    public ArrayList retrieveMeta () {
	try {
	    stmt = con.createStatement();
	    DatabaseMetaData meta = con.getMetaData();
	    ArrayList a = new ArrayList();
	    rs = meta.getTables(null,null,null,
					  new String [] {"TABLE"});
	    while (rs.next()) {
		a.add(new Entity(rs));
	    }
	    return a;
	} catch (SQLException e) {
	    return null;
	} finally {
	    if (rs!=null) {
		try { 
		    rs.close();
		    rs = null;
		} 
		catch (Exception e) {}
	    }
	    if (stmt!=null) {
		try {
		    stmt.close(); 
		    stmt = null;
		} 
		catch (Exception e) {}
	    }
	}
    }

    /** Retrieves metadata about a particular table in the database.
     */

    public SqlTable getSqlTable (String tableName) {
	try {
	    stmt = con.createStatement();
	    rs = stmt.executeQuery("select * from " + tableName);
	    ResultSetMetaData rsmd = rs.getMetaData();
	    int nCol = rsmd.getColumnCount();
	    String [] colNames = new String[nCol];
	    int [] colTypes = new int[nCol];
	    String [] colTypeNames = new String[nCol];
	    int [] colLengths = new int[nCol];
	    int [] colPrec = new int[nCol];
	    for (int i = 0; i<nCol; i++) {
		colNames[i] = rsmd.getColumnName(i+1);
		colTypes[i] = rsmd.getColumnType(i+1);
		colTypeNames[i] = rsmd.getColumnTypeName(i+1);
		colLengths[i] = rsmd.getColumnDisplaySize(i+1);
		colPrec[i] = rsmd.getPrecision(i+1);
	    }
	    return new SqlTable(tableName, colNames, colTypes, 
				colTypeNames, colLengths, colPrec);
	} catch (SQLException e) {
	    return null;
	} finally {
	    if (rs!=null) {
		try { 
		    rs.close();
		    rs = null;
		} 
		catch (Exception e) {}
	    }
	    if (stmt!=null) {
		try {
		    stmt.close(); 
		    stmt = null;
		} 
		catch (Exception e) {}
	    }
	}
    }
    
    /** Updates a row in a table according to the contents of an
     * Entity. 
     * The procedure returns null if successful, otherwise an
     * ErrorRecord that indicates what problems were encountered in
     * performing the update.
     */

    public ErrorRecord update(String tableName, String id, Entity ent)
    {
	return update(tableName, id, ent, new ErrorRecord());
    }


    public ErrorRecord update(String tableName, String id, 
			      Entity ent, ErrorRecord eR) {
	SqlTable sqlt = getSqlTable(tableName);
	// Check all values to see if they fit in the right field.
	Entity newEnt = checkVals(sqlt, ent, eR);
	if (!eR.isEmpty()) return eR;
	String sql = "update " + tableName + " set "
	    + makeUpdater(sqlt, newEnt, false)
	    + " where " + id + "=" + SQLEncode(ent.getS(id));
	try {
	    eR.addWarning("sql", sql); // just for debugging
	    stmt = con.createStatement();
	    int nUp = stmt.executeUpdate(sql);
	    if (nUp!=1) eR.addWarning("database", nUp + " records updated");
	}
	catch (SQLException e) {
	    eR.add("database", e.getMessage());
	}
	finally {
	    if (rs!=null) {
		try { 
		    rs.close();
		    rs = null;
		} 
		catch (Exception e) {}
	    }
	    if (stmt!=null) {
		try {
		    stmt.close(); 
		    stmt = null;
		} 
		catch (Exception e) {}
	    }
	}
	return eR;
    }

    public ErrorRecord update(String sql, ErrorRecord eR) {
	try {
	    stmt = con.createStatement();
	    int nUp = stmt.executeUpdate(sql);
	}
	catch (SQLException e) {
	    eR.add("Sql update error", e.toString());
	}
	return eR;
    }

    public ErrorRecord insert(String tableName, String id, 
			      Entity ent, boolean updateId) {
	return insert(tableName, id, ent, updateId, new ErrorRecord());
    }

    /** Insert a record corresponding to an Entity into tableName.
     * @param tableName String holding name of table to insert into
     * @param id String holding column name that identifies the key
     * @param ent Entity that describes the content of the row
     * @param updateId Boolean tells whether to update ent's id when
     *     the new row has an auto_number key.
     * @param eR ErrorRecord accumulates errors and warnings.
     */

    public ErrorRecord insert(String tableName, String id, 
			      Entity ent, boolean updateId, ErrorRecord eR)
    {
	SqlTable sqlt = getSqlTable(tableName);
	Entity newEnt = checkVals(sqlt, ent, eR);
	if (!eR.isEmpty()) return eR;
	String sql = "insert into " + tableName
	    + makeInserter(sqlt, newEnt, false);
	try {
	    eR.addWarning("sql", sql); // debugging
	    stmt = con.createStatement();
	    int nUp = stmt.executeUpdate(sql);
	    if (nUp!=1) eR.addWarning("database", nUp 
				      + " records inserted");
	    // Update the Entity so that its id is the newly
	    // auto-incremented id from the insert statement.
	    if (updateId) {
		rs = stmt.executeQuery("select last_insert_id() id");
		if (rs.next()) ent.put(id, rs.getString(1));
		eR.addWarning("auto_increment", id + "=" + rs.getString(1));
	    }
	}
	catch (SQLException e) {
	    eR.add("database", e.getMessage());
	}
	finally {
	    if (rs!=null) {
		try { 
		    rs.close();
		    rs = null;
		} 
		catch (Exception e) {eR.add("database", e.getMessage());}
	    }
	    if (stmt!=null) {
		try {
		    stmt.close(); 
		    stmt = null;
		} 
		catch (Exception e) {eR.add("database", e.getMessage());}
	    }
	}
	return eR;
    }


    /* Checks values for appropriate format to insert into a SQL
     * database, and returns a new Entity that has the properly
     * HTML-encoded string values and properly formatted date values
     * for insertion.
     */

    public Entity checkVals(SqlTable sqlt, Entity ent,
				 ErrorRecord eR) {
	Entity newEnt = new Entity();
	// Check all values to see if they fit in the right field.
	for (int i = 0; i<sqlt.colNames.length; i++) {
	    String col = sqlt.colNames[i];
	    String val = ent.getS(col);
	    if (sqlt.colTypes[i]==Types.DATE) {
		newEnt.put(col, eR.checkDate(col, val));
	    } else {
		String valEncoded = Entity.HTMLEncode(val);
		eR.checkString(col, val, sqlt.colPrecs[i]);
		newEnt.put(col,valEncoded);
	    }
	}
	return newEnt;
    }
	

    public String makeUpdater(SqlTable t, Entity ent) {
	return makeUpdater(t, ent, true);
    }

    public String makeUpdater(SqlTable t, Entity ent, boolean encode) {
	String sep = "";
	StringBuffer sb = new StringBuffer();
	for (int i = 0; i<t.colNames.length; i++) {
	    String col = t.colNames[i];
	    String val = (String)ent.get(col);
	    if (val!=null && encode) val = Entity.HTMLEncode(val);
	    sb.append(sep);
	    sep = ",";
	    sb.append(col);
	    sb.append("=");
	    sb.append(SQLEncode(val));
	}
	return sb.toString();
    }

    public String makeInserter(SqlTable t, Entity ent) {
	return makeInserter(t, ent, true);
    }

    public String makeInserter(SqlTable t, Entity ent, boolean encode) {
	String sep = "";
	StringBuffer sbc = new StringBuffer();
	StringBuffer sbv = new StringBuffer();
	for (int i = 0; i<t.colNames.length; i++) {
	    String col = t.colNames[i];
	    String val = (String)ent.get(col);
	    if (val!=null && encode) val = Entity.HTMLEncode(val);
	    sbc.append(sep);
	    sbc.append(col);
	    sbv.append(sep);
	    sbv.append(SQLEncode(val));
	    sep = ",";
	}
	return "(" + sbc + ") values(" + sbv + ")";
    }

    /**
     * Encodes a String to a form acceptable as (part of) a sql
     * command.  Single-quotes are doubled and the string is enclosed
     * in single-quotes.
     */
    
    public static String SQLEncode(String s) {
	if (s==null) return s;
	StringBuffer st = new StringBuffer();
	st.append('\'');
	for (int i = 0; i<s.length(); i++) {
	    char c = s.charAt(i);
	    if (c=='\'') st.append('\'');
	    st.append(c);
	}
	st.append('\'');
	return st.toString();
    }

}