dblogger.java

用于传感器网络的节点操作系统 TinyOS 结构设计非常有意思

package net.tinyos.tinydb; 

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

/** DBLogger is repsonsible for logging query results to Postgres.
    When instantiated with a query, it creates a new table to
    hold the results of that query and registers itself as a listener
    to results from that query.

    The name of table that is logged to can be retrieved via the 
    getTableName() method.  It will be uniquely generated for each
    query.

    The schema of the results table is:

   +----------------------------------------------------------+
   |  time stamp  | epoch no  |  field1 |  .... |  field n    |
   +----------------------------------------------------------+


   Additionally, each query is logged to a queries table (which must have been
   created before DBLogger is invoked.  The command to insert the queries table
   is:
   
   create table queries (qname varchar(10), query_time timestamp, query_string varchar(500));
*/


public class DBLogger implements ResultListener, QueryListener
{


    private Connection conn = null;
    private static String urlPSQL= "jdbc:postgresql:";
    private static String m_usr = Config.getParam("postgres-user");
    private static String m_pwd = Config.getParam("postgres-passwd");;
    private final static String insertQueryStmt = "INSERT INTO queries values (?, ?, ?)";
    private TinyDBQuery queryToLog;
    private String queryString;
    private long queryTime;
    private Statement stmt;
    private String logTableName;
    private TinyDBNetwork network;
    

    /* ------------------------------------- Public Methods ------------------------------------------ */
    /** Start logging the specified query
	@param query The query to log
	@param queryStr The SQL string corresponding to query (since not all TinyDB queries have a unique
	                SQL representation)
	@param nw The TinyDBNetwork that will deliver results for this query
	@throw SQLExcpetion if logging failed
    */
    public DBLogger(TinyDBQuery query, String queryStr, TinyDBNetwork nw) throws SQLException
    {
	initDBConn();
	queryToLog = query;
	queryString = queryStr;
	network = nw;
	queryTime = System.currentTimeMillis();
	logTableName =  uniqueName();
	logQuery();
	nw.addResultListener(this, true, query.getId());
    }

    /** @return The name of the table to which this queries results are being logged */
    public String getTableName() {
	return logTableName;
    }

    /** ResultListener Method.  Log the specified query result to the table set up for
	this DBLogger.
	@param qr The result to log
    */
    public void addResult(QueryResult qr)
    {
	String sqlStr = "INSERT INTO " + logTableName + " VALUES (now()";
	Vector resultVector = qr.resultVector();
	for (int i = 0; i < resultVector.size(); i++)
	    {
		sqlStr += ", ";
		if (queryToLog.getFieldType(i) == QueryField.STRING)
		    sqlStr += "'";
		sqlStr += resultVector.elementAt(i);
		if (queryToLog.getFieldType(i) == QueryField.STRING)
		    sqlStr += "'";
	    }
	sqlStr += ")";
	if (TinyDBMain.debug) System.out.println("logging result: " + sqlStr);
	try
	    {
		stmt.executeUpdate(sqlStr);
	    }
	catch (SQLException e)
	    {
		System.err.println("INSERT Result for query " + queryToLog.getId() + " failed.  SQLState = " + e.getSQLState());
	    }
    }

    /** QueryListener Method.  Stop logging for the specified query.
	@param q The query to stop logging for
    */
    public void removeQuery(TinyDBQuery q) {
	if (q == queryToLog) {
	    close();
	}
    }
    
    /** QueryListener Method.  Does nothing.  */
    public void addQuery(TinyDBQuery q) {
    }

    /** Shut down the connection to Postgres. */
    public void close()
    {
	network.removeResultListener(this);
	try
	    {
		if (conn != null)
		    conn.close();
		conn = null;
		if (TinyDBMain.debug)  System.out.println("disconnected from postgres.");
	    }
	catch (Exception e)
	    {
	    }
    }


    /* ------------------------------------- Private Methods ------------------------------------------ */
    /** Open the connection to Postgres */
    private void initDBConn()
    {
	try {
	    urlPSQL += "//" + Config.getParam("postgres-host") + "/" + Config.getParam("postgres-db");
	    Class.forName ( "org.postgresql.Driver" );
	    conn = DriverManager.getConnection(urlPSQL, m_usr, m_pwd);
	    stmt = conn.createStatement();
	    if (TinyDBMain.debug) System.out.println("connected to " + urlPSQL);
	} catch (Exception ex) {
	    System.out.println("failed to connect to Postgres!\n");
	    ex.printStackTrace();
	}
	if (TinyDBMain.debug) System.out.println("Connected to Postgres!\n");
    }

    /** Do the setup work for starting to log a query
	@throw SQLException if an error occurred creating / logging a query 
    */
    private void logQuery() throws SQLException 
    {
	try
	    {
		PreparedStatement  pstmt = null;
		pstmt = conn.prepareStatement(insertQueryStmt);
		pstmt.setString(1, getTableName());
		pstmt.setTimestamp(2, new Timestamp(queryTime));
		pstmt.setString(3, queryString);
		pstmt.executeUpdate();
		pstmt.close();
	    }
	catch (SQLException ex) {
	    System.out.println("logQuery failed.\n");
	    ex.printStackTrace();
	    throw ex;
	}
	// create log table for the query
	String dropOldTable = "DROP TABLE " + logTableName;
	try {
	    stmt.executeUpdate(dropOldTable);
	} catch (SQLException e){
	    //ignore exceptions
	}

	String createTabStr = "CREATE TABLE " + logTableName + "(result_time timestamp";
	Vector columns = queryToLog.getColumnHeadings();
	for (int i = 0; i < columns.size(); i++)
	    {
		String colName = (String)columns.elementAt(i);
		createTabStr += ", ";
			
		createTabStr += colName.replace('(','_').replace(')',' ').trim();
		createTabStr += " ";
		if (queryToLog.getFieldType(i) == QueryField.STRING)
		    createTabStr += "varchar(32)"; // XXX should be sufficient for now
		else
		    createTabStr += "int";
	    }
	createTabStr += ")";
	if (TinyDBMain.debug) System.out.println(createTabStr);
	try
	    {
		stmt.executeUpdate(createTabStr);
	    }
	catch (SQLException e)
	    {
		System.err.println("INSERT Result for query " + queryToLog.getId() + " failed.  SQLState = " + e.getSQLState());
		throw e;
	    }
    }


    /** Generate a unique name for a new query result table */
    private String uniqueName() throws SQLException {
	String createSeqNoTab = "CREATE TABLE seqno (seqno int)";
	String makeSeqNo = "INSERT INTO seqno VALUES(0)";
	String getSeqNo = "SELECT seqno FROM seqno";
	String setSeqNo = "UPDATE seqno SET seqno = ";
	ResultSet rs;
	int seqNo = 0;
	boolean didCreate = false;
	if (TinyDBMain.debug) System.out.println("Trying to create seqno table:" + createSeqNoTab);
	try {
	    stmt.executeUpdate(createSeqNoTab);
	    //note that the above statement will generate an exception if the table already exists!
	    didCreate = true;
	} catch (SQLException e) { //ignore exceptions
	}


	try {
	    if (didCreate)
		stmt.executeUpdate(makeSeqNo);
	    if (TinyDBMain.debug) System.out.println("Fetching seqno:" + getSeqNo);
	    rs = stmt.executeQuery(getSeqNo);
	    rs.first();
	    seqNo = rs.getInt(1);
	    seqNo++;
	    setSeqNo += seqNo;
	    if (TinyDBMain.debug) System.out.println("Setting seqno:" + setSeqNo);
	    stmt.executeUpdate(setSeqNo);
	} catch (SQLException e) {
	    if (TinyDBMain.debug) System.out.println("Error fetching/setting seqNo:" + e);
	    throw e;
	}
	
	return "q"+seqNo;
	
	
    }


}