// Maintains an event log as a mySQL table
// by Rowland http://home.comcast.net/~rowland3/

import java.io.*;
import java.net.*;  // InetAddress
import java.sql.*;
import java.util.*;

class SQLConn {
    private Driver driver = null;
    private String userName, userPassword;
    public final String driverName, jdbcURL1, databaseName;

    SQLConn(String _driverName, String _jdbcURL1, String _databaseName) {
	driverName= _driverName;  jdbcURL1= _jdbcURL1;  databaseName= _databaseName;
	try { openDriver(); }
	catch (Exception E) {
	    System.err.println("SQLConn("+ driverName+ "): "+ E.toString());
	}
    }

    public void openDriver() throws Exception {
	if (driver!= null)  return;  // already done
	//println("Instantiating "+ driverName);
	driver = (Driver)Class.forName(driverName).newInstance();
	if (driver== null)  throw new SQLException("newInstance of "+ driverName+ " returned NULL!");
    }

    public void setUserName(String _userName) { userName= _userName; }
    public void setUserPassword(String _userPassword) { userPassword= _userPassword; }

    protected Connection openConnection()  throws Exception {
	// return null if failed to connect
	//println("      Connecting "+ jdbcURL1+ databaseName);
	Connection conn;
	//if (userPassword!= null)  System.err.println("Password: "+ userPassword);
	conn= DriverManager.getConnection(jdbcURL1+ databaseName, userName, userPassword);
	//[Not for Msql or MySql]conn.setAutoCommit(false);
	//println("  ...connected");
	return conn;
    }

    public static void println(String s) { System.out.println(s); }

    public synchronized ResultSet executeQuery(String query)
	throws IOException {
	ResultSet results= null;
	String err= null;
	Connection conn= null;
	try {
	    conn= openConnection();
	    Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
						  ResultSet.CONCUR_READ_ONLY);
	    //println("    "+ query);
	    results= stmt.executeQuery(query);
	    if (results!= null)  results.first();
	}
        catch( Exception e ) {
            e.printStackTrace();
	    err= e.toString();
        }
        finally {
            if ( conn != null ) {
                try { conn.close(); }
                catch( Exception e ) { }
            }
        }
	if (err!= null)  throw new IOException("executeQuery: "+ err);
	return results;
    }

    /** executeUpdate executes the SQL update, then logs an entry in the LOG table. **/
    public synchronized void executeUpdate(String update)
	throws IOException {
	String err= null;
	Connection conn= null;
	try {
	    conn= openConnection();
	    Statement stmt = conn.createStatement();
	    //println("    "+ update);
	    stmt.executeUpdate(update);
	}
        catch (Exception E) {
	    String msg= E.getMessage();
	    int ix= msg.indexOf("already exists");
	    if (ix< 0) { // else can safely ignore, being idempotent
		E.printStackTrace();
		err= E.toString();
	    }
        }
        finally {
            if (conn != null ) {
                try { conn.close(); }
                catch( Exception e ) { }
            }
        }
	if (err!= null)  throw new IOException("executeUpdate: "+ err);
    }

    /** Make a string value safe for building into an SQL query **/
    public static String fixQuotes(String stringval) {
        if( stringval.indexOf('\'') != -1 ) {
            String tmp = "";
	    
            for(int i=0; i<stringval.length(); i++) {
                char c = stringval.charAt(i);
                if( c == '\'' )  tmp = tmp + "\\'";
                else  tmp = tmp + c;
            }
            stringval = tmp;
        }
        return stringval;
    }

}

//---------------------------------------------------------------------------------
public class SQLLogger {
    public final String driverName;
    public final String jdbcURL1;
    public final String databaseName;
    protected SQLConn dbconn;
    
    public SQLLogger(String _driverName, String _jdbcURL1, String _databaseName) {
	driverName= _driverName;
	jdbcURL1= _jdbcURL1;
	databaseName= _databaseName;
	dbconn= new SQLConn(driverName, jdbcURL1, databaseName);
    }
    /** Utility constructor to hook to a mySQL database on local host **/
    public SQLLogger(String _databaseName) {
	driverName= "com.mysql.jdbc.Driver";
	jdbcURL1= "jdbc:mysql://127.0.0.1/";
	databaseName= _databaseName;
	dbconn= new SQLConn(driverName, jdbcURL1, databaseName);
    }

    public void setUserName(String name) { dbconn.setUserName(name); }
    public void setUserPassword(String password) { dbconn.setUserPassword(password); }
    
    public void createTable(String tableName) {
	String create= "CREATE TABLE "+ tableName;
	create+=
	    " (ldate date NOT NULL, ltime time, entrytext varchar(255), PRIMARY KEY (ldate))  TYPE=ISAM";
	try { dbconn.executeUpdate(create); }
	catch (Exception E) {
	    System.err.println(E.getMessage());
	}
    }

    public void log(String tableName, String entryText) {
	String msafe= dbconn.fixQuotes(entryText);
	long millis= System.currentTimeMillis();
	String ldate= new java.sql.Date(millis).toString();
	String ltime= new java.sql.Time(millis).toString();
	String insert= "INSERT "+ tableName+ " (ldate, ltime, entrytext) VALUES ('"+
	    ldate+ "', '"+ ltime+ "', '"+ msafe+ "')";
	try { dbconn.executeUpdate(insert); }
	catch (Exception E) {  }
    }
    
    /** test harness.  pass new string value on command line **/
    public static void main(String args[]) {
	String tableName= "DEMOTABLE";
	SQLLogger us= new SQLLogger("LOGDEMO");
	us.setUserName("rowland");
	if (args.length> 1)  us.setUserPassword(args[1]);
	us.createTable(tableName);
	us.log(tableName, args[0]);
    }
}