import java.io.*;
import java.util.*;
import java.sql.*;


/* 
Here's a small Java sample that demonstrates the basics of accessing the 
WebcamSat internal SQL database.  Compilation requires no special flags (as 
usual: 'javac WstreamdDBSample.java')  but the SQL classes are required at 
runtime: 'java -classpath wstreamd.jar WstreamdDBSample <host> <user> <pass>'

'host' is the IP or hostname of the machine on which wstreamd is running. The 
user and password are as specified in wstreamd.properties when wstreamd was 
started.
*/


public class WstreamdDBSample
{
    private Connection conn = null;
    
    public static void main(String[] args)
    {
	if (args.length < 3) {
	    System.out.println("Usage: java WstreamdDBSample <host> <user> <password>");
	    return;
	}

	WstreamdDBSample w = new WstreamdDBSample(args[0], args[1], args[2]);
	w.insertSampleUser();
	w.listUsers();
	w.removeSampleUser();
	w.close();
    }

    public WstreamdDBSample(String host, String user, String pass) {

	try {
	    Class.forName("org.hsqldb.jdbcDriver");
	    conn = DriverManager.getConnection("jdbc:hsqldb:hsql://" + host, user, pass);
        } catch (SQLException e) {
	    System.err.println("WstreamdDBSample() - Error getting connection to DB " + e);
        } catch (ClassNotFoundException nf) {
	    System.err.println("WstreamdDBSample() - JDBC driver not found in classpath " + nf);
	}

    }

    private void listUsers()
    {
	String q = "SELECT id, name FROM user";

	System.out.println("Listing users...");

	try {
	    Statement st = conn.createStatement();
	    ResultSet r = st.executeQuery(q);
	    while (r.next()) {
		System.out.println(r.getInt("id") + "\t| " + r.getString("name"));
	    }
	    st.close();
	} catch (SQLException e) {
	    System.err.println("WstreamdDBSample::listUsers() - Unable to retrieve user list - " + e);
	}
    }

    private void insertSampleUser()
    {
	String i = "INSERT INTO user (id, name, password) VALUES ('512', 'sampleUser', 'sample')";

	System.out.println("Creating 'sampleUser'...");

	try {
	    Statement st = conn.createStatement();
	    if (st.executeUpdate(i) != 1)
		throw new SQLException("");
	    st.close();
	} catch (SQLException e) {
	    System.err.println("WstreamdDBSample::insertSampleUser() - Unable to insert sample user - " + e);
	}
    }

    private void removeSampleUser()
    {
	String d = "DELETE FROM user WHERE name = 'sampleUser'";

	System.out.println("Deleting 'sampleUser'...");

	try {
	    Statement st = conn.createStatement();
	    if (st.executeUpdate(d) != 1)
		throw new SQLException("");
	    st.close();
	} catch (SQLException e) {
	    System.err.println("WstreamdDBSample::removeSampleUser() - Unable to remove sample user - " + e);
	}
    }


    private void close()
    {
	try {
	    System.out.println("Closing DB connection...");
	    conn.close();
	} catch (SQLException e) {
	    System.err.println("WstreamdDBSample::close() - Unable to close DB connection - " + e);
	}
    }
}