/*
 * Copyright 2015, Yahoo Inc.
 * Copyrights licensed under the Apache License.
 * See the accompanying LICENSE file for terms.
 */
package com.yahoo.dba.perf.myperf.metrics;

import java.nio.ByteBuffer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.TimeZone;
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.TimeUnit;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.yahoo.dba.perf.myperf.common.AlertSettings;
import com.yahoo.dba.perf.myperf.common.AlertSubscribers;
import com.yahoo.dba.perf.myperf.common.ColumnDescriptor;
import com.yahoo.dba.perf.myperf.common.DBInstanceInfo;
import com.yahoo.dba.perf.myperf.common.DBUtils;
import com.yahoo.dba.perf.myperf.common.Metric;
import com.yahoo.dba.perf.myperf.common.MetricDataType;
import com.yahoo.dba.perf.myperf.common.MetricsDefManager;
import com.yahoo.dba.perf.myperf.common.MetricsGroup;
import com.yahoo.dba.perf.myperf.common.MetricsSubscribers;
import com.yahoo.dba.perf.myperf.common.MyPerfContext;
import com.yahoo.dba.perf.myperf.common.ResultList;
import com.yahoo.dba.perf.myperf.common.ResultListUtil;
import com.yahoo.dba.perf.myperf.common.ResultRow;
import com.yahoo.dba.perf.myperf.common.UserDefinedMetrics;
import com.yahoo.dba.perf.myperf.process.MetricsRetentionTask;


/**
 * persistence store for metrics
 * @author xrao
 *
 */
public abstract class MetricsDbBase implements  Runnable
{
	  private static Logger logger = Logger.getLogger(MetricsDbBase.class.getName());  

	  //wrap the metrics data for future extenstion
	  static class MetricsData
	  {
		  String dataKey;//can be null
		  ByteBuffer data;
		  
		  MetricsData(String dataKey, ByteBuffer data)
		  {
			  this.dataKey = dataKey;
			  this.data = data;
		  }
	  }
	  private MyPerfContext frameworkContext; //system context
	  
	  //database access information
	  private String connectionString = "jdbc:derby:metricsdb";//JDBC URL
	  private String username;
	  private String password = "metricsdb";
	  private String schemaName = "METRICSDB";//default to METRICSDB
	  //move db info to metrics db
	  protected static final String DBINFO_TABLENAME="DBINFOS";
	  protected static final String ALERTSETTING_TABLENAME="ALERTSETTINGS";
	  protected static final String ALERT_SUBSCRIPT = "ALERT_SUBSCRIPT";
	  protected static final String ALERT_NOTIFICATION = "ALERT_NOTIFICATION";

  	  protected static final String METRICS_SUBSCRIPT = "METRICS_SUBSCRIPT";

	  //data to store
	  protected Map<String, java.util.concurrent.ArrayBlockingQueue<MetricsData>> dataQueues = new HashMap<String, java.util.concurrent.ArrayBlockingQueue<MetricsData>>();
	  protected LinkedBlockingQueue<Integer> flushQueue = new LinkedBlockingQueue<Integer>();//job controller
	  protected volatile boolean stopped = false;

	  
	  protected Map<String, String> insertSQL = new HashMap<String, String>();
	  
	  //the key will be dbtype+"_"+metric name
	  //metrics definition
	  protected Map<String, MetricsGroup> metricsGroups = new HashMap<String, MetricsGroup>();
	  protected Object metricsDefLock = new Object();
	  //generic metric code
	  private java.util.concurrent.ConcurrentHashMap<String, Integer> metricCodeMap = new java.util.concurrent.ConcurrentHashMap<String, Integer>();
	  
	  
	  protected java.text.DecimalFormat df = new java.text.DecimalFormat("#.###");
	  protected java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyyMMddHHmmss");
	 
	  private transient Connection storeConnection = null;
	  private long lastConnTime = -1L;
	  
	  private Object codeLock = new Object();//sync new code
	  
	  /**
	   * To identify metric db server type, for example, javadb (derby) or mysql
	   * @return
	   */
	  abstract public String getDbType();
	  
	  /**
	   * JDBC connection string
	   * @return
	   */
	  public String getConnectionString() 
	  {
	    return connectionString;
	  }

	  public void setConnectionString(String connectionString) 
	  {
	    this.connectionString = connectionString;
	  }
	  
	  /**
	   * Database user name
	   * @return
	   */
	  public String getUsername() 
	  {
	    return username;
	  }
	  
	  public void setUsername(String username) 
	  {
	    this.username = username;
	  }
	  
	  /**
	   * Database password
	   * @return
	   */
	  public String getPassword() 
	  {
	    return password;
	  }
	  public void setPassword(String password) 
	  {
	    this.password = password;
	  }
	
	  public String getSchemaName()
	  {
		  return this.schemaName;
	  }
	  
	  public void setSchemaName(String schemaName)
	  {
		  this.schemaName = schemaName;
	  }
	  
	  /**
	   * Access other system info
	   * @return
	   */
	  public MyPerfContext getFrameworkContext() 
	  {
		return frameworkContext;
	  }
	  public void setFrameworkContext(MyPerfContext frameworkContext) 
	  {
		this.frameworkContext = frameworkContext;
	  }

	  public Map<String, MetricsGroup> getMetricsGroups() 
	  {
		return metricsGroups;
	  }
	  
	  /**
	   * This is used during startup
	   * @param mgs
	   */
	  public void setMetricsGroups(MetricsDefManager metricsDef) 
	  {
		synchronized(metricsDefLock)
		{
	      String [] groupNames = metricsDef.getGroupNames();
	      for(String grpName: groupNames)
	      {
	        MetricsGroup group = metricsDef.getGroupByName(grpName);
	        if(group == null)continue; //not supposed to be so
	        List<MetricsGroup> mgs = new ArrayList<MetricsGroup>();
	        if (group.getSubGroups().size() == 0) //no sub group, add self
	        {
	    	    mgs.add(group);
  	        }else
	        {
	    	  for(MetricsGroup g: group.getSubGroups())
	    		  mgs.add(g);
	        }
	       for(MetricsGroup g: mgs)
	       {  
	    	 String sinkName = g.getSinkTableName();  
	         this.metricsGroups.put(sinkName, g);
	         this.dataQueues.put(sinkName, new java.util.concurrent.ArrayBlockingQueue<MetricsData>(10000));//2014-02-14, change size to 10K
	         if(!g.isStoreInCommonTable())
	        	this.insertSQL.put(sinkName, this.insertSQL(g));
	         else
	         {
	        	String targetTable = g.getTargetTable();
	        	if(targetTable==null || targetTable.isEmpty())
	        		targetTable = "METRIC_GENERIC";
	        	this.insertSQL.put((g.getDbType()+"_"+g.getGroupName()), "INSERT INTO "+targetTable+" (DBID, METRIC_ID, SNAP_ID, TS, VALUE) VALUES(?,?,?,?,?)");
	         }
	        
	       }
	     }

  	     for(Map.Entry<String, UserDefinedMetrics> entry: metricsDef.getUdmManager().getUdms().entrySet())
	     {
	       MetricsGroup group = entry.getValue().getMetricsGroup();
	       if(group == null)continue; //not supposed to be so
	       String sinkName = group.getSinkTableName();  
	       this.metricsGroups.put(sinkName, group);
	       this.dataQueues.put(sinkName, new java.util.concurrent.ArrayBlockingQueue<MetricsData>(10000));//2014-02-14, change size to 10K	      
	       this.insertSQL.put(sinkName, this.insertSQL(group));	        
	     }
	   }
	  }


	  public boolean addNewUDM(UserDefinedMetrics udm)
	  {		 
	    if(udm == null)return false;
	    MetricsGroup mg = udm.getMetricsGroup();
	    if(!createMetricsTable(mg)) return false;
	    String tblName = mg.getSinkTableName();
	    synchronized(metricsDefLock)
	    {
	      if(!this.metricsGroups.containsKey(tblName))
	      {
	    	this.metricsGroups.put(tblName, mg);
	    	this.dataQueues.put(tblName, 
	    			new java.util.concurrent.ArrayBlockingQueue<MetricsData>(10000));      
		    this.insertSQL.put(tblName, this.insertSQL(mg));	  
	      }
	      else
	    	  return false;
	    }
	    return true;
	  }

	  public boolean removeUDM(UserDefinedMetrics udm)
	  {
		  if(udm == null)return false;
		  MetricsGroup mg = udm.getMetricsGroup();
		  String tblName = mg.getSinkTableName();
		  synchronized(metricsDefLock)
		  {
		      if(this.metricsGroups.containsKey(tblName))
		      {
		    	this.metricsGroups.remove(tblName);
		    	this.dataQueues.remove(tblName);      
			    this.insertSQL.remove(tblName);	  
		      }
		  }
		  return true;
	  }
	  /**
	   * Build insert statement.
	   * TODO db specific optimization
	   * @param mg
	   * @return
	   */
	  private String insertSQL(MetricsGroup mg)
	  {
	    StringBuilder sb = new StringBuilder();
	    sb.append("insert ignore into ");
	    if(mg.isUdmFlagged())
	    	sb.append("UDM");
	    else
	    	sb.append(mg.getDbType().toUpperCase());
	    sb.append("_")
 	      .append(mg.getGroupName().toUpperCase()).append(" (")
	      .append("DBID, SNAP_ID");
	    if(mg.getKeyColumn() != null && !mg.getKeyColumn().isEmpty())
	    {
	    	//add additional column to store key
	    	sb.append(", KEY_COLUMN");
	    }
	    sb.append(", TS, SQL_TIME");
	    for(Metric m: mg.getMetrics())
	    {
	      sb.append(",");
	      sb.append(m.getName().toUpperCase());
	    }
	    sb.append(") values (?,?,?,?");
	    if(mg.getKeyColumn() != null && !mg.getKeyColumn().isEmpty())
	    {
	    	//add additional column to store key
	    	sb.append(",?");
	    }
	    for(Metric m: mg.getMetrics())
	    {
	      sb.append(",?");
	    }
	    sb.append(")");
	    return sb.toString();
	  }

	  /**
	   * Initial method. It will create required table, if not exists, and load metric code and start stop script.
	   * TODO use multiple threads in the future
	   */
	  public void init()
	  {
		//TODO read configuration
		logger.info("Init metrics db");
		sdf.setTimeZone(TimeZone.getTimeZone("UTC"));	  
		createTables();
		loadMetricCode();
		new Thread(this).start();
		logger.info("Init metrics done");
	  }
	  
	  /**
	   * Just set thread to stop
	   */
	  public void destroy()
	  {
		logger.info("Shutting donw metrics DB");
	    this.stopped = true;	  
	  }
	  	  
	  //derbydb does not support limit
	  abstract protected boolean isLimitSupport();
	  
	  abstract protected String[] buildHostDDL();
	  abstract protected String[] buildAlertSettingDDL();

	  abstract protected String buildMetricCodeDDL();
	  abstract protected String buildMetricCodeIndexDDL();

	  abstract protected String[] buildGenericMetricDDL();

	  abstract protected String buildAlertDDL();
	  abstract protected String buildAlertIndexDDL();
	  abstract protected String[] buildSnapshotDDL();//create metrics snapshot table

	  abstract protected String[] buildAlertSubScriptionDDL();//create alert subscription table
	  abstract protected String[] buildMetricsSubscrptionDDL();//create metrics subscription table for UDM and on demand metrics
	  abstract protected String[] buildAlertNotificationDDL();//create alert notification table

	  protected String buildDDL(MetricsGroup mg)
	  {
	    StringBuilder sb = new StringBuilder();
	    String tblName = mg.getSinkTableName();
	    sb.append("CREATE TABLE ");
	    sb.append(tblName);
	    sb.append(" (")
	    .append("DBID INT, SNAP_ID INT");
	    if(mg.getKeyColumn() != null && !mg.getKeyColumn().isEmpty())
	    {
	    	//add additional column to store key
	    	sb.append(", KEY_COLUMN VARCHAR(255)");
	    }
	    sb.append(", TS BIGINT, SQL_TIME INT");
	    for(Metric m: mg.getMetrics())
	    {
	      sb.append(",");
	      sb.append(m.getName().toUpperCase()).append(" ");
	      if(m.getDataType()==MetricDataType.BYTE)
	        sb.append("TINYINT");
	      else if(m.getDataType()==MetricDataType.SHORT)
	          sb.append("SMALLINT");
	      else if(m.getDataType()==MetricDataType.INT)
	          sb.append("INT");
	      else if(m.getDataType()==MetricDataType.LONG)
	          sb.append("BIGINT");
	      else if(m.getDataType()==MetricDataType.FLOAT)
	          sb.append("DECIMAL(22,7)");
	      else if(m.getDataType()==MetricDataType.DOUBLE)
	          sb.append("DECIMAL(22,7)");
	    }
	    if(mg.getKeyColumn() != null && !mg.getKeyColumn().isEmpty())
	    	sb.append(", PRIMARY KEY(DBID, SNAP_ID, KEY_COLUMN))");
	    else
	    	sb.append(", PRIMARY KEY(DBID, SNAP_ID))");
	    return sb.toString();
	  }
	  
	  private boolean createMetricsTable(MetricsGroup mg)
	  {
		  Connection conn = null;
		  try
		  {
		      conn = this.createConnection(false);
		      createMetricsTable(mg, conn);
		      return true;
		  }catch(Exception ex)
		  {
			  logger.log(Level.WARNING, "Failed to create table for metrics " + mg.getGroupName(), ex);
		  }finally
		  {
			  DBUtils.close(conn);
		  }
		  return false;
	  }
	  private void createMetricsTable(MetricsGroup mg, Connection conn) throws SQLException
	  {
	    String tblName = mg.getSinkTableName();
	    Statement stmt = null;
	    try
	    {
	    	if(!DBUtils.hasTable(conn, schemaName, tblName))
	        {
	          stmt = conn.createStatement();
	          String ddl = this.buildDDL(mg);
	          logger.info("Create metric table " + tblName+": "+ddl);
	          stmt.execute(ddl);
	          DBUtils.close(stmt); stmt = null;
	          logger.info("Created metric table " + tblName);
	        }
	    }finally
	    {
	    	DBUtils.close(stmt);
	    }
	  }
	  private void createTables()
	  {
	    Connection conn = null;
	    Statement stmt = null;
	    try
	    {
	      conn = this.createConnection(false);
	      for(Map.Entry<String, MetricsGroup> e: this.metricsGroups.entrySet())
	      {
	    	if(e.getValue().isStoreInCommonTable())
	    		continue;//skip it. It will use generic table
	    	createMetricsTable(e.getValue(), conn);
	      }
	      //for(Map.Entry<String, UserDefinedMetrics> e: 
	    //	  this.frameworkContext.getMetricsDef().getUdmManager().getUdms().entrySet())
	    //	  createMetricsTable(e.getValue().getMetricsGroup(), conn);

	      if(!DBUtils.hasTable(conn, schemaName, "METRIC_CODE"))
	      {
	        stmt = conn.createStatement();
	        String ddl = this.buildMetricCodeDDL();
	        logger.info("Create metric table METRIC_CODE: "+ddl);
	        stmt.execute(ddl);
	        stmt.execute(this.buildMetricCodeIndexDDL());
	        DBUtils.close(stmt);
	        logger.info("Created metric table METRIC_CODE" );
	      }
	      if(!DBUtils.hasTable(conn, schemaName, "METRIC_GENERIC"))
	      {
	        stmt = conn.createStatement();
	        String[] ddls = this.buildGenericMetricDDL();
	        for(String ddl: ddls)
	        {
	        	logger.info("Create metric table METRIC_GENERIC: "+ddl);
	        	stmt.execute(ddl);
	        }
	        DBUtils.close(stmt);
	        logger.info("Created metric table METRIC_GENERIC" );
	      }
	      if(!DBUtils.hasTable(conn, schemaName, "SNAPSHOTS"))
	      {
	        stmt = conn.createStatement();
	        String[] ddls = this.buildSnapshotDDL();
	        for(String ddl: ddls)
	        {
	        	logger.info("Create metric table SNAPSHOTS: "+ddl);
	        	stmt.execute(ddl);
	        }
	        DBUtils.close(stmt);
	        logger.info("Created metric table METRIC_GENERIC" );
	      }
	      if(!DBUtils.hasTable(conn, schemaName, "ALERT"))
	      {
	        stmt = conn.createStatement();
	        String ddl = this.buildAlertDDL();
	        logger.info("Create alert table ALERT: "+ddl);
	        stmt.execute(ddl);
	        stmt.execute(this.buildAlertIndexDDL());
	        DBUtils.close(stmt);
	        logger.info("Created alert table ALERT" );
	      }
	      if(!DBUtils.hasTable(conn, schemaName, "ALERT_SUBSCRIPT"))
	      {
	        stmt = conn.createStatement();
	        String[] ddls = this.buildAlertSubScriptionDDL();
	        for(String ddl: ddls)
	        {
	        	logger.info("Create metric table ALERT_SUBSCRIPT: "+ddl);
	        	stmt.execute(ddl);
	        }
	        DBUtils.close(stmt);
	        logger.info("Created metric table ALERT_SUBSCRIPT" );
	      }
	      if(!DBUtils.hasTable(conn, schemaName, "METRICS_SUBSCRIPT"))
	      {
	        stmt = conn.createStatement();
	        String[] ddls = this.buildMetricsSubscrptionDDL();
	        for(String ddl: ddls)
	        {
	        	logger.info("Create metric table METRICS_SUBSCRIPT: "+ddl);
	        	stmt.execute(ddl);
	        }
	        DBUtils.close(stmt);
	        logger.info("Created metric table METRICS_SUBSCRIPT" );
	      }
	      //buildAlertNotificationDDL
	      if(!DBUtils.hasTable(conn, schemaName, "ALERT_NOTIFICATION"))
	      {
	        stmt = conn.createStatement();
	        String[] ddls = this.buildAlertNotificationDDL();
	        for(String ddl: ddls)
	        {
	        	logger.info("Create metric table ALERT_NOTIFICATION: "+ddl);
	        	stmt.execute(ddl);
	        }
	        DBUtils.close(stmt);
	        logger.info("Created metric table ALERT_NOTIFICATION" );
	      }
	      if(!DBUtils.hasTable(conn, schemaName, DBINFO_TABLENAME))
	      {
	  	        stmt = conn.createStatement();
	    	  for(String ddl: this.buildHostDDL())
	    	  {
	    	    if(ddl!=null && !ddl.isEmpty())
	    	    {
		          logger.info("Create dbhost table:  "+ddl);
		          stmt.execute(ddl);
		          logger.info("Created dbhost table "+DBINFO_TABLENAME );
	    	    }
	    	  }
		        DBUtils.close(stmt);
	      }
	      if(!DBUtils.hasTable(conn, schemaName, ALERTSETTING_TABLENAME))
	      {
	  	        stmt = conn.createStatement();
	    	  for(String ddl: this.buildAlertSettingDDL())
	    	  {
	    	    if(ddl!=null && !ddl.isEmpty())
	    	    {
		          logger.info("Create table:  "+ddl);
		          stmt.execute(ddl);
		          logger.info("Created table "+ ALERTSETTING_TABLENAME);
	    	    }
	    	  }
		        DBUtils.close(stmt);
	      }
	    }catch(Exception ex)
	    {
	      logger.log(Level.SEVERE, "Failed to check or create metric db", ex);
	    }
	    finally
	    {
	      DBUtils.close(conn);
	    }
	  }
	  
	  /**
	   * This can be used at server start time to sync with meta db
	   * @param dbInfoManager
	   */
	  public void syncHosts(List<DBInstanceInfo> dbInfos)
	  {
		  if(dbInfos==null || dbInfos.size() == 0)return;//nothing to sync
		  MetaHostSyncer syncer = new MetaHostSyncer();
		  syncer.setDbList(dbInfos);
		  new Thread(syncer).start();//let it run on itself
	  }
	  
	  /**
	   * This can be used to sync with actions on individual db
	   * @param dbInfo
	   * @param action: new, delete, update
	   * @return
	   */
	  public boolean syncHosts(DBInstanceInfo dbInfo, String action)
	  {
		  if(dbInfo==null)return false;
		  Connection conn = null;
		  try
		  {
			  conn = this.createConnection(true);
			  if("delete".equalsIgnoreCase(action))
			  {
				  if(dbInfo.getHostName()==null || "all".equalsIgnoreCase(dbInfo.getHostName()))
					  return this.removeDbGroup(conn, dbInfo.getDbGroupName());//remove whole group
				  else
					  return this.removeDBInfo(conn, dbInfo.getDbGroupName(), dbInfo.getHostName());
			  }else
				  return this.upsertDBInfo(conn, dbInfo, "new".equals(action));
		  }catch(Exception ex)
		  {
			  logger.log(Level.INFO, "Failed to remove host from metrics db ("+dbInfo.getDbGroupName()+", "+dbInfo.getHostName());
		  }finally
		  {
			  DBUtils.close(conn);
		  }
		  return false;
	  }
	  
	  /**
	   * DB specific to create connection
	   * 
	   * @param autocommit If true, the connection use autocommit, otherwise manual.
	   * @return
	   */
	  abstract protected Connection createConnection(boolean autocommit);


	  public void flush()
	  {
	    this.flushQueue.add(new Integer(1));
	  }
	  
	  public void putData(MetricsGroup mg, String key, ByteBuffer buf)
	  {
		String tblName = mg.getSinkTableName();		
		java.util.concurrent.ArrayBlockingQueue<MetricsData> q = this.dataQueues.get(tblName);
	    if(q!=null)
	    {
	      try
	      {
	        q.put(new MetricsData(key, buf));
	      }catch(Exception ex){}
	    }else
	    {
	    	logger.info("Warning: cannot find sink queue "+mg.getGroupName()+", table "+tblName);
	    }
	  }
	  
	  public void run()
	  {
		Thread.currentThread().setName("MetricsDB");
	    while(!stopped)
	    {
	      try
	      {
	    	  //start store either by trigger or timeout
	    	  //2013-12-02: change from 60 seconds to 1sec
	    	  //2014-02-13: change from 1 seconds to 100 msec
	    	Integer i = this.flushQueue.poll(100, TimeUnit.MILLISECONDS);	
	      }catch(Exception ex)
	      {
	    	  
	      }
	      store();
	    }
	    //final store after shutdown
	    store();
	    DBUtils.close(storeConnection);
	    storeConnection = null;
	    logger.info("metrics db stopped");
	  }
	  
	  protected void store()
	  {
		try
		{
	      for(String s: this.dataQueues.keySet())
	      {
	    	java.util.concurrent.ArrayBlockingQueue<MetricsData> q = this.dataQueues.get(s);
	        List<MetricsData> q2 = new ArrayList<MetricsData>(50);
	        while(true)
	        {
	          MetricsData bufData = q.poll();
	          if(bufData == null)//if nothing there, go to next queue.
	            break;
	          q2.add(bufData);//accumulate all data from the same queue
	        }
	        if(q2.size()>0)
	        {
	    	  java.sql.PreparedStatement stmt = null;
	          logger.fine("Store "+q2.size()+" "+s+" metric records.");
	          long currTime = System.currentTimeMillis();
	          if(currTime - this.lastConnTime >30000)
	          {
	        	  DBUtils.close(storeConnection);
	        	  storeConnection = null;
	          }
	          if(storeConnection == null)
	          {
	        	  storeConnection = this.createConnection(true);
  	              this.lastConnTime = currTime;
	          }
	          MetricsGroup mg = this.metricsGroups.get(s);//generic metrics
	          if(mg.isStoreInCommonTable())
	          {
	        	  storeGenericMetric( s, q2,  storeConnection);
		          this.lastConnTime = System.currentTimeMillis();
		          logger.fine("Stored "+q2.size()+" "+s+" metric records.");
	        	  continue;//go to next type
	          }
	          //builtin metrics
	          try
	          {
  	            String sql = this.insertSQL.get(s);
	            stmt = storeConnection.prepareStatement(sql);
	            for(MetricsData mdata:q2)
	            {
	        	  ByteBuffer buf = mdata.data;  
	              int idx = 1;
	              int pos = 0;
	              stmt.setInt(idx++, buf.getInt(pos));pos+=4;//dbid
	              stmt.setInt(idx++, buf.getInt(pos));pos+=4;//snap_id
	              if(mg.getKeyColumn() != null && !mg.getKeyColumn().isEmpty())
	              {
	            	if (mdata.dataKey == null)
	            		stmt.setNull(idx++, java.sql.Types.VARCHAR);
	            	else
	            		stmt.setString(idx++, mdata.dataKey);
	              }
	              stmt.setString(idx++, sdf.format(new java.util.Date(buf.getLong(pos))));pos+=8;//timestamp
	              stmt.setInt(idx++, buf.getInt(pos));pos+=4;//sql time
	            
	              List<Metric> ms = mg.getMetrics();
	              int len = ms.size();
				  for(int i=0;i<len;i++)
				  {
				    Metric m = ms.get(i);
				    if(m.getDataType()==MetricDataType.BYTE)
				    {
				      stmt.setInt(idx++, buf.get(pos));pos++;
				    }else if(m.getDataType()==MetricDataType.SHORT)
				    {
					 stmt.setInt(idx++, buf.getShort(pos));pos+=2;				 
				    }else if(m.getDataType()==MetricDataType.INT)
				    {
					 stmt.setInt(idx++, buf.getInt(pos));pos+=4;
				    }else if(m.getDataType()==MetricDataType.LONG)
				    {
					  stmt.setLong(idx++,buf.getLong(pos));pos+=8;
				    }else if(m.getDataType()==MetricDataType.FLOAT)
				    {
					  stmt.setString(idx++, df.format(buf.getFloat(pos)));pos+=4;
				    }else if(m.getDataType()==MetricDataType.DOUBLE)
				    {
					  stmt.setString(idx++, df.format(buf.getDouble(pos)));pos+=8;
				    }					
			      }//one row
		          stmt.execute();
		          stmt.clearBatch();
	            }//for(MetricsData mdata:q2)
	          }catch(Exception ex)
	          {
	    	      logger.log(Level.WARNING, "Exception when store metrics: " + s, ex);
	        	  
	          }
	            //try{
	        	//  stmt.executeBatch();
	            //}catch(Exception iex)
	            //{
	        	// logger.warning("Failed: "+sql);
	        	//  throw iex;
	            //}
	          //storeConnection.commit();
	          this.lastConnTime = System.currentTimeMillis();
	          stmt.close(); stmt = null;
	          logger.fine("Stored "+q2.size()+" "+s+" metric records.");
	        }
	      }
	    }catch(Exception ex)
	    {
	      logger.log(Level.WARNING, "Exception when store metrics", ex);
	      if(storeConnection!=null){try{storeConnection.rollback();}catch(Exception iex){}}
	    }
		finally
	    {
		  //DBUtils.close(stmt);
	      //DBUtils.close(conn);
	    }	  
	  }
	  
	  /**
	   * Store generic metrics
	   * @param q2
	 * @throws SQLException 
	   */
	  protected void storeGenericMetric(String mg, List<MetricsData> q2, Connection conn) throws SQLException
	  {
		  PreparedStatement stmt = null;
	      String sql = this.insertSQL.get(mg);
	      try
	      {
	    	  stmt = conn.prepareStatement(sql);
	    	  int cnt = 0;
	    	  for(MetricsData mdata:q2)
	    	  {
	    		  ByteBuffer buf = mdata.data;
	    		  int idx = 1;
	    		  int pos = 0;
	    		  stmt.setInt(idx++, buf.getInt(pos));pos+=4;//DB ID
	    		  stmt.setInt(idx++, buf.getInt(pos));pos+=4; //METRICS ID       
	    		  stmt.setInt(idx++, buf.getInt(pos));pos+=4; //SNAP_ID
	    		  stmt.setString(idx++, sdf.format(new java.util.Date(buf.getLong(pos))));pos+=8;//TODO sdf thread safety
	    		  stmt.setString(idx++, df.format(buf.getDouble(pos)));
	    		  stmt.addBatch();
	    		  cnt++;
	    		  if(cnt==20)
	    		  {
	    			  stmt.executeBatch();
	    			  cnt=0;
	    			  //conn.commit();
	    			  stmt.clearBatch();
	    		  }
	    	  }//for loop
	    	  if(cnt>0)
	    	  {
	    		  stmt.executeBatch();
	    		  //conn.commit();
	    	  }
	    	  stmt.close(); stmt = null;
	      }finally
	      {
	    	   DBUtils.close(stmt);
	      }
		  	  
	  }
	  public boolean isStopped() 
	  {
		return stopped;
	  
	  }
	  public void setStopped(boolean stopped) 
	  {	  
		this.stopped = stopped;
	  }
	  
	  /**
	   * remove all old alerts exceeded retention threshold
	   */
	  public void purgeAlerts(long endDate)
	  {
			Connection conn = null;
			PreparedStatement stmt = null;
		    String sql = "delete from ALERT where ts<=?";
			logger.log(Level.INFO, "To purge alerts up to " +endDate);
		    try
		    {
		  	  conn = createConnection(true);
		      stmt = conn.prepareStatement(sql);
		      stmt.setLong(1, endDate);
		      stmt.execute();
		    }catch(Exception ex)
			{
			  logger.log(Level.SEVERE, "Failed to purge alerts up to "+ endDate, ex);
			  try {conn.rollback();}catch(Exception iex){}
			}finally
			{
		      DBUtils.close(stmt);	
			  DBUtils.close(conn);	
			}		  
		  
	  }
	  /**
	   * Purge metric data up to endDate
	   * @param metricGroupName
	   * @param dbid
	   * @param endDate
	   */
	  public void purge(String metricGroupName, int dbid, long endDate)
	  {
		int[] snaps = this.getSnapshostRange(-1, endDate) ;
		if(snaps == null)return;//no data

		Connection conn = null;
		PreparedStatement stmt = null;
	    String sql = "delete from "+metricGroupName+" where dbid=? and snap_id<=?";
		//logger.log(Level.INFO, "To purge metrics "+metricGroupName+" for db "+dbid+" up to " +endDate);
	    try
	    {
	  	  conn = createConnection(true);
	      stmt = conn.prepareStatement(sql);
	      stmt.setInt(1, dbid);
	      stmt.setInt(2, snaps[1]);
	      stmt.execute();
	  	  //logger.log(Level.INFO, "To purge metrics "+metricGroupName+" for db "+dbid+" up to " +endDate+", number of records: "+stmt.getUpdateCount());
	      //conn.commit();
	    }catch(Exception ex)
		{
		  logger.log(Level.SEVERE, "Failed to purge metrics "+metricGroupName+" for db "+dbid+" up to "+ endDate, ex);
		  try {conn.rollback();}catch(Exception iex){}
		}finally
		{
	      DBUtils.close(stmt);	
		  DBUtils.close(conn);	
		}
	  }

	  /**
	   * Purge all metrics data for a given db when it is removed from management
	   * @param metricGroupName
	   * @param dbid
	   */
	  public void purgeAll(String metricGroupName, int dbid)
	  {
		int batchSize = 10000; //TODO make it configurable
		Connection conn = null;
		PreparedStatement stmt = null;
	    String sql = "delete from "+metricGroupName+" where dbid=? limit " + batchSize;
	    if(!this.isLimitSupport())
	    	sql = "delete from "+metricGroupName+" where dbid=?";
	    if(this.isLimitSupport())
	    	logger.log(Level.INFO, "To purge metrics "+metricGroupName+" for db "+dbid+" using batch of " + batchSize);
	    else
	    	logger.log(Level.INFO, "To purge metrics "+metricGroupName+" for db "+dbid);
	    try
	    {
	  	  conn = createConnection(true);
	      stmt = conn.prepareStatement(sql);
	      while(true)
	      {
	    	  stmt.setInt(1, dbid);
	    	  stmt.execute();
	    	  int total = stmt.getUpdateCount();
	    	  if(total <= 0 || !this.isLimitSupport())
	    		  break;
	    	  else
	    		  logger.log(Level.INFO, "To purge metrics "+metricGroupName+" for db "+dbid+": " + total);
	      }
	    }catch(Exception ex)
		{
		  logger.log(Level.SEVERE, "Failed to purge metrics "+metricGroupName+" for db "+dbid+" using batch size  " + batchSize , ex);
		  try {conn.rollback();}catch(Exception iex){}
		}finally
		{
	      DBUtils.close(stmt);	
		  DBUtils.close(conn);	
		}
	  }
	  
	  /**
	   * Remove metrics data from metrics storage for a given db.
	   * It will start a thread to do it.
	   * @param dbid a group of ids to purge
	   */
	  public void purgeMetricsForDbInstance(int[] dbid)
	  {
	    MetricsRetentionTask purgeTask = new MetricsRetentionTask(this.frameworkContext, 0, dbid);
		new Thread(purgeTask).start();  
	  }
	  public ResultList retrieveMetrics(String metricGroupName, int dbid, long startDate, long endDate)
	  {
		int[] snaps = this.getSnapshostRange(startDate, endDate) ;
		if(snaps == null)return null;//no data
		
	    //later, connection pooling
		ResultList rList = null;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String sql = "select * from "+metricGroupName+" where dbid=? and snap_id between ? and ? order by dbid, snap_id";
		//String sql = "select * from "+metricGroupName+" where dbid=?";
		logger.log(Level.FINE, "To retrieve metrics "+metricGroupName+" for db "+dbid+" with time range ("+startDate+", "+endDate+"), snap ("+snaps[0] +", "+snaps[1]+")");
		try
		{
		  conn = createConnection(true);
		  stmt = conn.prepareStatement(sql);
		  stmt.setFetchSize(1000);
		  //stmt.setMaxRows(5000);
		  stmt.setInt(1, dbid);
		  stmt.setInt(2, snaps[0]);
		  stmt.setInt(3, snaps[1]);
		  rs = stmt.executeQuery();
		  rList = ResultListUtil.fromSqlResultSet(rs, 5000);
		}catch(Exception ex)
		{
			logger.log(Level.SEVERE, "Failed to retrieve metrics "+metricGroupName+" for db "+dbid+" with time range ("+startDate+", "+endDate+")", ex);
		}finally
		{
	      DBUtils.close(stmt);	
		  DBUtils.close(conn);	
		}
		return rList;
	  }

	  public ResultList retrieveMetrics(String metricGroupName, String[] metrics, boolean hasKeyColumn, int dbid, long startDate, long endDate)
	  {
		int[] snaps = this.getSnapshostRange(startDate, endDate) ;
		if(snaps == null)return null;//no data

		if(metrics==null||metrics.length==0)//not specify the metrics? Get all
	    	return retrieveMetrics( metricGroupName,  dbid,  startDate,  endDate);
	    //later, connection pooling
		ResultList rList = null;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();//build select list
		
		sb.append("SNAP_ID");
		if(hasKeyColumn)
			sb.append(", KEY_COLUMN");
		sb.append(", TS");
		for(String me: metrics)
		{
			sb.append(", ");
			sb.append(me);
		}
		
		String sql = "select "+sb.toString()+" from "+metricGroupName+" where dbid=? and snap_id between ? and ? order by dbid, snap_id";
		//String sql = "select * from "+metricGroupName+" where dbid=?";
		logger.log(Level.INFO, "To retrieve metrics "+metricGroupName+", metrics ("+sb.toString()+") for db "+dbid+" with time range ("+startDate+", "+endDate+"), snap ("+snaps[0]+", "+snaps[1]+")");
		try
		{
		  conn = createConnection(true);
		  stmt = conn.prepareStatement(sql);
		  stmt.setFetchSize(1000);
		  //stmt.setMaxRows(5000);
		  stmt.setInt(1, dbid);
		  stmt.setInt(2, snaps[0]);
		  stmt.setInt(3,  snaps[1]);
		  rs = stmt.executeQuery();
		  rList = ResultListUtil.fromSqlResultSet(rs, 5000);
		}catch(Exception ex)
		{
			logger.log(Level.SEVERE, "Failed to retrieve metrics "+metricGroupName+" for db "+dbid+" with time range ("+startDate+", "+endDate+")", ex);
		}finally
		{
	      DBUtils.close(stmt);	
		  DBUtils.close(conn);	
		}
		return rList;
	  }

	  public ResultList retrieveMetrics(String metricGroupName, Metric[] metrics, boolean hasKeyColumn, int dbid, long startDate, long endDate, boolean agg)
	  {
		String[] ms = new String[metrics.length];
		for(int i=0; i<metrics.length; i++)ms[i] = metrics[i].getName();
		if(!agg)return retrieveMetrics(metricGroupName, ms, hasKeyColumn, dbid,startDate,endDate);
		int[] snaps = this.getSnapshostRange(startDate, endDate) ;
		if(snaps == null)return null;//no data

		if(metrics==null||metrics.length==0)//not specify the metrics? Get all
	    	return retrieveMetrics( metricGroupName,  dbid,  startDate,  endDate);
	    //later, connection pooling
		ResultList rList = null;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();//build select list
		StringBuilder grpBy = new StringBuilder();//build select list
		
		sb.append("SNAP_ID");
		sb.append(", TS");
		for(Metric me: metrics)
		{
			if(me.isIncremental())
				sb.append(", sum(");
			else
				sb.append(", avg(");
			sb.append(me.getName())
			  .append(") ")
			  .append (me.getName());
		}
		
		String sql = "select "+sb.toString()+" from "+metricGroupName+" where dbid=? and snap_id between ? and ? group by snap_id, ts order by snap_id";
		//String sql = "select * from "+metricGroupName+" where dbid=?";
		logger.log(Level.INFO, "To retrieve metrics "+metricGroupName+", metrics ("+sb.toString()+") for db "+dbid+" with time range ("+startDate+", "+endDate+"), snap ("+snaps[0]+", "+snaps[1]+")");
		try
		{
		  conn = createConnection(true);
		  stmt = conn.prepareStatement(sql);
		  stmt.setFetchSize(1000);
		  //stmt.setMaxRows(5000);
		  stmt.setInt(1, dbid);
		  stmt.setInt(2, snaps[0]);
		  stmt.setInt(3,  snaps[1]);
		  rs = stmt.executeQuery();
		  rList = ResultListUtil.fromSqlResultSet(rs, 5000);
		}catch(Exception ex)
		{
			logger.log(Level.SEVERE, "Failed to retrieve metrics "+metricGroupName+" for db "+dbid+" with time range ("+startDate+", "+endDate+")", ex);
		}finally
		{
	      DBUtils.close(stmt);	
		  DBUtils.close(conn);	
		}
		return rList;
	  }

	  /** 
	   * Retrieve user defined merics
	   * @param metrics
	   * @param dbid
	   * @param startDate
	   * @param endDate
	   * @return
	   */
	  public ResultList retrieveUDMMetrics(String metric, int dbid, long startDate, long endDate)
	  {		  
		int[] snaps = this.getSnapshostRange(startDate, endDate) ;
		if(snaps == null)return null;//no data

		//later, connection pooling
		ResultList rList = null;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		int code = 0;
		if(this.metricCodeMap.containsKey(metric))
		{
			code =	this.metricCodeMap.get(metric);
		}else
		{
			logger.warning("Failed to find metrics code for "+metric+", "+this.metricCodeMap);
			return null;
		}
		
		String sql = "select SNAP_ID, TS, METRIC_ID, VALUE from METRIC_GENERIC where dbid=? and snap_id between ? and ? and METRIC_ID=? order by dbid, METRIC_ID, snap_id";
		//String sql = "select * from "+metricGroupName+" where dbid=?";
		logger.log(Level.INFO, "To retrieve "+metric+", "+ code+" on db "+dbid+" with time range ("+startDate+", "+endDate+"), using "+sql);
		try
		{
		  conn = createConnection(true);
		  stmt = conn.prepareStatement(sql);
		  stmt.setFetchSize(1000);
		  //stmt.setMaxRows(5000);
		  stmt.setInt(1, dbid);
		  stmt.setInt(2, snaps[0]);
		  stmt.setInt(3,  snaps[1]);
		  stmt.setLong(4,  code);
		  rs = stmt.executeQuery();
		  if(rs==null)return rList;
		  rList = new ResultList();
		  //java.sql.ResultSetMetaData meta =  rs.getMetaData();
		  ColumnDescriptor desc = new ColumnDescriptor();
		  desc.addColumn("SNAP_ID", true, 1);
		  desc.addColumn("TS", true, 2);
		  desc.addColumn(metric, true, 3);
		  
		  rList.setColumnDescriptor(desc);
		  int rowCnt = 0;
		  //List<ColumnInfo> cols = desc.getColumns();
		  while(rs.next())
		  {
				//logger.info(new java.util.Date()+": process "+rowCnt+" rows");
				ResultRow row = new ResultRow();
				row.setColumnDescriptor(desc);
				java.util.ArrayList<String> cols2 = new java.util.ArrayList<String>(3);
				cols2.add(rs.getString(1));
				cols2.add(rs.getString(2));
				cols2.add(rs.getString(4));
				row.setColumns(cols2);
				rList.addRow(row);
				rowCnt++;
				if(rowCnt>=5000)break;
			}
			logger.info(new java.util.Date()+": Process results done: "+rList.getRows().size());
		}catch(Exception ex)
		{
			logger.log(Level.SEVERE, "Failed to retrieve UDM "+metric+" for db "+dbid+" with time range ("+startDate+", "+endDate+")", ex);
		}finally
		{
	      DBUtils.close(stmt);	
		  DBUtils.close(conn);	
		}
		return rList;
	  }

	  public ResultList retrieveMetricsStatus(String metricGroupName, int dbid, long startDate, long endDate)
	  {
		int[] snaps = this.getSnapshostRange(startDate, endDate) ;
		if(snaps == null)return null;//no data
	    //later, connection pooling
		ResultList rList = null;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String sql = "select DBID, SNAP_ID, TS, SQL_TIME, SLOW_QUERIES from "+metricGroupName+" where dbid=? and snap_id between ? and ? order by dbid, snap_id";
		//String sql = "select * from "+metricGroupName+" where dbid=?";
		logger.log(Level.FINE, "Retrieve metrics status from "+metricGroupName+" for db "+dbid+" with time range ("+startDate+", "+endDate+")");
		try
		{
		  conn = createConnection(true);
		  stmt = conn.prepareStatement(sql);
		  stmt.setInt(1, dbid);
		  stmt.setInt(2, snaps[0]);
		  stmt.setInt(3,  snaps[1]);
		  rs = stmt.executeQuery();
		  rList = ResultListUtil.fromSqlResultSet(rs, 5000);
		}catch(Exception ex)
		{
			logger.log(Level.SEVERE, "Failed to retrieve metrics "+metricGroupName+" for db "+dbid+" with time range ("+startDate+", "+endDate+")", ex);
		}finally
		{
		  DBUtils.close(rs);	
	      DBUtils.close(stmt);	
		  DBUtils.close(conn);	
		}
		return rList;
	  }

	  
	  private void loadMetricCode()
	  {
			Connection conn = null;
			Statement stmt = null;
			ResultSet rs = null;
			String sql = "select CODE_ID, NAME from METRIC_CODE";
			try
			{
			  conn = createConnection(true);
			  stmt = conn.createStatement();
			  rs = stmt.executeQuery(sql);
			  while(rs!=null && rs.next())
			  {
				  this.metricCodeMap.put(rs.getString("NAME"), rs.getInt("CODE_ID"));
			  }
			}catch(Exception ex)
			{
				logger.log(Level.SEVERE, "Failed to retrieve metrics METRIC_CODE)", ex);
			}finally
			{
			  DBUtils.close(rs);	
		      DBUtils.close(stmt);	
			  DBUtils.close(conn);	
			}
		  
	  }
	  
	  /**
	   * Check if metric existed
	   * @param name
	   * @return
	   */
	  public int checkAndAddMetricCode(String name)
	  {
		   //if(this.metricCodeMap.containsKey(name.toLowerCase()))
		   //	  return this.metricCodeMap.get(name.toLowerCase());
		  if(this.metricCodeMap.containsKey(name)) //make it case sensitive, as the user input
			  return this.metricCodeMap.get(name);
		  else  //add one
		  {
			synchronized(this.codeLock)
			{
				  if(this.metricCodeMap.containsKey(name))
					  return this.metricCodeMap.get(name);
				  Connection conn = null;
				  PreparedStatement stmt = null;
				  ResultSet rs = null;
				  String sql = "insert into METRIC_CODE (NAME) values (?)";
				  try
				  {
					  conn = createConnection(true);
					  stmt = conn.prepareStatement(sql);
					  stmt.setString(1, name);
					  stmt.execute();
					  //conn.commit();
					  stmt.close();stmt = null;
					  stmt = conn.prepareStatement("select NAME, CODE_ID from METRIC_CODE where NAME=?");
					  stmt.setString(1, name);
					  rs = stmt.executeQuery();
					  if(rs!=null && rs.next())
					  {
						  this.metricCodeMap.put(rs.getString("NAME"), rs.getInt("CODE_ID"));
					  }
				  }catch(Exception ex)
				  {
					  logger.log(Level.SEVERE, "Failed to store/retrieve metrics METRIC_CODE)", ex);
				  }finally
				  {
					  DBUtils.close(rs);	
					  DBUtils.close(stmt);	
					  DBUtils.close(conn);	
				  }

			}
			if(this.metricCodeMap.containsKey(name))
			  return this.metricCodeMap.get(name);
		  }
		  return -1;
	  }

	  public boolean addNewAlert(long ts, int dbid, String alert_type, String alert_reason)
	  {
		  logger.info("ALERT INSERT: ("+dbid+", "+ts+", "+alert_type+", "+alert_reason +")");
		  Connection conn = null;
		  PreparedStatement stmt = null;
		  String sql = "insert into ALERT (DBID, TS, ALERT_TYPE, ALERT_REASON) values (?,?,?,?)";
		  try
		  {
			  conn = createConnection(true);
			  stmt = conn.prepareStatement(sql);
			  stmt.setInt(1, dbid);
			  stmt.setLong(2, ts);
			  stmt.setString(3, alert_type);
			  stmt.setString(4, alert_reason);
			  stmt.execute();
			  return true;
		  }catch(Exception ex)
		  {
			  logger.log(Level.SEVERE, "Failed to store alert data", ex);
		  }finally
		  {
			  DBUtils.close(stmt);	
			  DBUtils.close(conn);	
		  }

		  return false;
	  }
	  public boolean markAlertEnd(long ts, int dbid, long end_ts)
	  {
		  logger.info("ALERT UPDATE: ("+dbid+", "+ts+", "+end_ts+")");
		  Connection conn = null;
		  PreparedStatement stmt = null;
		  //mark all alerts for the same db within the same day end
		  String sql = "update ALERT set end_ts=? where dbid=? and ts<=? and ts >= ? - 240000";
		  try
		  {
			  conn = createConnection(true);
			  stmt = conn.prepareStatement(sql);
			  stmt.setLong(1, end_ts);
			  stmt.setInt(2, dbid);
			  stmt.setLong(3, ts);
			  stmt.setLong(4, ts);
			  stmt.execute();
			  return true;
		  }catch(Exception ex)
		  {
			  logger.log(Level.SEVERE, "Failed to update alert data", ex);
		  }finally
		  {
			  DBUtils.close(stmt);	
			  DBUtils.close(conn);	
		  }

		  return false;
	  }

	  public boolean markAlertEnd(long ts, int dbid, long end_ts, String alertName)
	  {
		  logger.info("ALERT UPDATE: ("+dbid+", "+ts+", "+end_ts+")");
		  Connection conn = null;
		  PreparedStatement stmt = null;
		  //mark all alerts for the same db within the same day end
		  String sql = "update ALERT set end_ts=? where dbid=? and ts<=? and ts >= ? - 240000 and ALERT_TYPE=?";
		  try
		  {
			  conn = createConnection(true);
			  stmt = conn.prepareStatement(sql);
			  stmt.setLong(1, end_ts);
			  stmt.setInt(2, dbid);
			  stmt.setLong(3, ts);
			  stmt.setLong(4, ts);
			  stmt.setString(5, alertName);
			  stmt.execute();
			  return true;
		  }catch(Exception ex)
		  {
			  logger.log(Level.SEVERE, "Failed to update alert data", ex);
		  }finally
		  {
			  DBUtils.close(stmt);	
			  DBUtils.close(conn);	
		  }

		  return false;
	  }

	  /**
	   * 
	   * @param startTs
	   * @param endTs
	   * @param dbs a map for look up db info by dbis
	   * @param filteredDbs actual dbs to retrieve alerts
	   * @return
	   */
	  public ResultList retrieveAlerts(String startTs, String endTs, Map<Integer, DBInstanceInfo> dbs, List<Integer> filteredDbs)
	  {
		ResultList rList = null;
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		rList = new ResultList();
		ColumnDescriptor desc = new ColumnDescriptor();
		desc.addColumn("DBGROUP", false, 1);
		desc.addColumn("HOST", false, 2);
		desc.addColumn("TS", false, 3);
		desc.addColumn("END_TS", false, 4);
		desc.addColumn("ALERT_TYPE", false, 5);
		desc.addColumn("ALERT_REASON", false, 6);
		desc.addColumn("BY CPU", true, 7);
		desc.addColumn("BY IO", true, 8);
		desc.addColumn("BY THREAD", true, 9);
		desc.addColumn("BY LOADAVG", true, 10);
		desc.addColumn("BY REPL LAG", true, 11);
		desc.addColumn("BY SLOW QUERY", true, 12);
		desc.addColumn("BY REPL DOWN", true, 13);
		desc.addColumn("BY CONN FAILURE", true, 14);
		desc.addColumn("BY DEADLOCKS", true, 15);
		
		rList.setColumnDescriptor(desc);
		
		StringBuilder sb = new StringBuilder();
		sb.append("select * from ALERT where ts between ")
		  .append(startTs)
		  .append(" and ")
		  .append(endTs);
		if(filteredDbs!=null && filteredDbs.size()>=1)
		{
			sb.append(" and dbid in (");
			boolean isFirst = true;
			for(Integer id:filteredDbs)
			{
				if(!isFirst)
					sb.append(",");
				sb.append(id);
				isFirst = false;
			}
			sb.append(")");
		}
		sb.append(" order by ts desc");
		String sql = sb.toString();
		logger.log(Level.INFO, "Retrieve alerts: "+sql);
		Map<Integer, AlertSummary> sumMap = null;
		try
		{
		  conn = createConnection(true);
		  
		  sumMap = this.retrieve7DaysAlertsSummary(conn,  filteredDbs);
		  
		  stmt = conn.createStatement();
		  rs = stmt.executeQuery(sql);
		  
		  //DBID, TS, END_TS, ALERT_TYPE, ALERT_REASON
		  while(rs!=null && rs.next())
		  {
			  int dbid = rs.getInt("DBID");
			  if(!dbs.containsKey(dbid))
				  continue;//no db records, ignore
			  DBInstanceInfo dbinfo = dbs.get(dbid);
			  ResultRow row = new ResultRow();
			  row.setColumnDescriptor(desc);
			  row.addColumn(dbinfo.getDbGroupName());
			  row.addColumn(dbinfo.getHostName());
			  row.addColumn(formatDatetime(rs.getString("TS")));
			  row.addColumn(formatDatetime(rs.getString("END_TS")));
			  row.addColumn(rs.getString("ALERT_TYPE"));
			  row.addColumn(rs.getString("ALERT_REASON"));
			  AlertSummary sum = sumMap.get(dbid);//don't expect missing
			  row.addColumn(sum!=null?String.valueOf(sum.cpuAlerts):"0");
			  row.addColumn(sum!=null?String.valueOf(sum.ioAlerts):"0");
			  row.addColumn(sum!=null?String.valueOf(sum.threadAlerts):"0");
			  row.addColumn(sum!=null?String.valueOf(sum.LoadAvgAlerts):"0");
			  row.addColumn(sum!=null?String.valueOf(sum.replAlerts):"0");
			  row.addColumn(sum!=null?String.valueOf(sum.slowAlerts):"0");
			  row.addColumn(sum!=null?String.valueOf(sum.replDown):"0");
			  row.addColumn(sum!=null?String.valueOf(sum.connectFailuerAlerts):"0");
			  row.addColumn(sum!=null?String.valueOf(sum.deadlocks):"0");
			  rList.addRow(row);
		  }
		}catch(Exception ex)
		{
			logger.log(Level.SEVERE, "Failed to retrieve alerts "+sql, ex);
		}finally
		{
		  DBUtils.close(rs);	
	      DBUtils.close(stmt);	
		  DBUtils.close(conn);	
		}
		return rList;
	  }

	  public static class AlertSummary
	  {
		  int dbid;
		  int cpuAlerts = 0;
		  int LoadAvgAlerts = 0;
		  int ioAlerts = 0;
		  int threadAlerts = 0;
		  int replAlerts = 0;
		  int slowAlerts = 0;
		  int connectFailuerAlerts = 0;
		  int replDown = 0;
		  int deadlocks = 0;
		  AlertSummary()
		  {
			  
		  }
	  }
	  
	  private Map<Integer, AlertSummary> retrieve7DaysAlertsSummary(Connection conn,  List<Integer> filteredDbs)
	  {
		Statement stmt = null;
		ResultSet rs = null;
		
		Map<Integer, AlertSummary> resMap = new HashMap<Integer, AlertSummary> ();
		java.text.SimpleDateFormat alertsdf = new java.text.SimpleDateFormat("yyyyMMddHHmmss");//metricsDB TS column format
		alertsdf.setTimeZone(TimeZone.getTimeZone("UTC"));
		String startTs = null;
		Calendar c = Calendar.getInstance();
		c.add(Calendar.DATE, -7);
		startTs = sdf.format( c.getTime());
		
		StringBuilder sb = new StringBuilder();
		sb.append("select DBID, ALERT_TYPE, COUNT(*) AS TOTAL from ALERT where ts >= ")
		  .append(startTs);
		if(filteredDbs!=null && filteredDbs.size()>=1)
		{
			sb.append(" and dbid in (");
			boolean isFirst = true;
			for(Integer id:filteredDbs)
			{
				if(!isFirst)
					sb.append(",");
				sb.append(id);
				isFirst = false;
			}
			sb.append(")");
		}
		sb.append(" GROUP By DBID, ALERT_TYPE");
		String sql = sb.toString();
		logger.log(Level.INFO, "Retrieve alerts summary: "+sql);
		try
		{
		  //conn = createConnection(true);
		  stmt = conn.createStatement();
		  rs = stmt.executeQuery(sql);
		  
		  //DBID, TS, END_TS, ALERT_TYPE, ALERT_REASON
		  while(rs!=null && rs.next())
		  {
			  int dbid = rs.getInt("DBID");
			  String alertType = rs.getString("ALERT_TYPE");
			  int acount = rs.getInt(3);
			  if(!resMap.containsKey(dbid))
			  {
				AlertSummary sum = new AlertSummary();
				sum.dbid = dbid;
				resMap.put(dbid, sum);
			  }
			  {
				  AlertSummary sum = resMap.get(dbid);
				  if("CPU".equalsIgnoreCase(alertType))
					  sum.cpuAlerts = acount;
				  else if("LOADAVG".equalsIgnoreCase(alertType))
					  sum.LoadAvgAlerts = acount;
				  else if("IO".equalsIgnoreCase(alertType))
					  sum.ioAlerts = acount;
				  else if("THREAD".equalsIgnoreCase(alertType))
					  sum.threadAlerts = acount;
				  else if("REPLLAG".equalsIgnoreCase(alertType))
					  sum.replAlerts = acount;
				  else if("SLOW".equalsIgnoreCase(alertType))
					  sum.slowAlerts = acount;				  
				  else if("REPLDOWN".equalsIgnoreCase(alertType))
					  sum.replDown = acount;
				  else if("CONNECT_FAILURE".equalsIgnoreCase(alertType))
					  sum.connectFailuerAlerts = acount;
				  else if("DEADLOCKS".equalsIgnoreCase(alertType))
					  sum.deadlocks = acount;
			  }
		  }
		}catch(Exception ex)
		{
			logger.log(Level.SEVERE, "Failed to retrieve alerts "+sql, ex);
		}finally
		{
		  DBUtils.close(rs);	
	      DBUtils.close(stmt);	
		}
		return resMap;
	  }

	  private String formatDatetime(String str)
	  {
		  try
		  {
		    if(str!=null && str.length()==14)
		    {
			  StringBuilder sb = new StringBuilder();
			  sb.append(str.substring(0, 4))
			    .append('-')
			    .append(str.substring(4, 6))
			    .append('-')
			    .append(str.substring(6, 8))
			    .append(' ')
			    .append(str.substring(8, 10))
			    .append(':')
			    .append(str.substring(10, 12))
			    .append(':')
			    .append(str.substring(12, 14));
			  return sb.toString();
		    }
		  }catch(Exception ex){}
		  
		  return "";
	  }
	  
	  private boolean removeDBInfoById(Connection conn, int dbid)
	  {
		String sql = "delete from "+DBINFO_TABLENAME+" where dbid=?";
		
		PreparedStatement pstmt = null;
		try
		{
		  pstmt = conn.prepareStatement(sql);
		  pstmt.setInt(1, dbid);
		  pstmt.execute();
		  logger.info("Removed the database host : ("+dbid+")");
		  return pstmt.getUpdateCount()>0;
		}catch(Exception ex)
		{
		  logger.log(Level.SEVERE,"Exception", ex);
		}finally
		{
		  DBUtils.close(pstmt);
		}
		return false;
	  }

	  private boolean removeDBInfo(Connection conn, String dbGroupName, String hostName)
	  {
		String sql = "delete from "+DBINFO_TABLENAME+" where dbgroupname=? and hostname=?";
		
		PreparedStatement pstmt = null;
		try
		{
		  pstmt = conn.prepareStatement(sql);
		  pstmt.setString(1, dbGroupName.toLowerCase());
		  pstmt.setString(2, hostName.toLowerCase());
		  pstmt.execute();
		  logger.info("Removed the database host : ("+dbGroupName+", "+hostName+")");
		  return pstmt.getUpdateCount()>0;
		}catch(Exception ex)
		{
		  logger.log(Level.SEVERE,"Exception", ex);
		}finally
		{
		  DBUtils.close(pstmt);
		}
		return false;
	  }

	  public boolean removeDbGroup(Connection conn, String dbGroupName)
	  {
	    String sql = "delete from "+DBINFO_TABLENAME+" where dbgroupname=?";
		PreparedStatement pstmt = null;
		try
		{
		  pstmt = conn.prepareStatement(sql);
		  pstmt.setString(1, dbGroupName.toLowerCase());
		  pstmt.execute();
		  logger.info("Removed the database host : ("+dbGroupName+")");
		  return pstmt.getUpdateCount()>0;
		}catch(Exception ex)
		{
		  logger.log(Level.SEVERE,"Exception", ex);
		}finally
		{
		  DBUtils.close(pstmt);
		}
		return false;
	  }

	  /**
	   * Store db info into db. If exists, update the info. If not, insert new one.
	   * @param dbinfo
	   */
	  public boolean upsertDBInfo(Connection conn, DBInstanceInfo dbinfo, boolean insert)
	  {
	    if(dbinfo==null)return false;
		String sql2 = "update " +DBINFO_TABLENAME+" set dbgroupname=?, hostname=?, dbtype=?,instance=?, port=?, database_name=?,USE_SSHTUNNEL=?, LOCAL_HOSTNAME=?, LOCAL_PORT=?, CONNECTION_VERIFIED=?, VIRTUAL_HOST=?, OWNER=? where DBID=?";
		String sql3 = "insert into "+DBINFO_TABLENAME+" (dbgroupname,hostname,dbtype,instance, port, database_name,USE_SSHTUNNEL, LOCAL_HOSTNAME, LOCAL_PORT,CONNECTION_VERIFIED,VIRTUAL_HOST, OWNER, DBID) values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
			
		logger.info("Store or update db "+dbinfo.toString());
		PreparedStatement pstmt = null;
		boolean findOne = false;
		try
		{
		  //first, check if we have record
		  findOne = !insert;
		  pstmt = conn.prepareStatement(findOne?sql2:sql3);
		  int idx = 1;
		  pstmt.setString(idx++, dbinfo.getDbGroupName().toLowerCase());
		  pstmt.setString(idx++, dbinfo.getHostName().toLowerCase());
		  pstmt.setString(idx++, dbinfo.getDbType());
		  pstmt.setString(idx++, String.valueOf(dbinfo.getInstance()));
		  if(dbinfo.getPortShort() != 0)
			  pstmt.setShort(idx++, dbinfo.getPortShort());
		  else 
			  pstmt.setNull(idx++, java.sql.Types.SMALLINT);
		  pstmt.setString(idx++, dbinfo.getDatabaseName());
		  pstmt.setString(idx++, dbinfo.isUseTunneling()?"1":"0");
		  pstmt.setString(idx++, dbinfo.getLocalHostName());
		  if(dbinfo.getLocalPortShort() != 0)
			  pstmt.setShort(idx++, dbinfo.getLocalPortShort());
		  else 
			  pstmt.setNull(idx++, java.sql.Types.SMALLINT);
		  pstmt.setString(idx++, dbinfo.isConnectionVerified()?"1":"0");
		  pstmt.setString(idx++, dbinfo.isVirtualHost()?"1":"0");
		  pstmt.setString(idx++, dbinfo.getOwner());
		  pstmt.setInt(idx++, dbinfo.getDbid());
		  pstmt.execute();
		  return true;
		}catch(Exception ex)
		{
		  logger.info("Failed to save "+dbinfo.toString()+", "+ex.getMessage());
		  if(conn!=null)try{conn.rollback();}catch(Exception iex){}
		  return false;
		}finally
		{
		  DBUtils.close(pstmt);
		}			
	  }
	  
	  public void renameDbGroup(String oldName, String newName)
	  {
		  Connection conn = null;
		  PreparedStatement stmt = null;
		  String[] renameDbinfoSql = new String[]{"update " +DBINFO_TABLENAME+" set dbgroupname=? where dbgroupname=?",
				  "update " + ALERTSETTING_TABLENAME + " set dbgroupname=? where dbgroupname=?",
				  "update ALERT_SUBSCRIPT set DBGROUP=? where DBGROUP=?",
				  "update METRICS_SUBSCRIPT set DBGROUP=? where DBGROUP=?"};
		  try
		  {
			  conn = this.createConnection(true);
			  for(int i=0; i<renameDbinfoSql.length; i++)
			  {
				  logger.info("Rename dbgroup: " + renameDbinfoSql[i]);
				  stmt = conn.prepareStatement(renameDbinfoSql[i]);
				  stmt.setString(1, newName.toLowerCase());
				  stmt.setString(2, oldName.toLowerCase());
				  stmt.execute();
				  stmt.close(); stmt = null;
			  }

		  }catch(Exception ex)
		  {
			  logger.log(Level.WARNING, "Failed to rename dbgroup " + oldName+" to " + newName, ex);
			  throw new RuntimeException(ex);
		  }finally
		  {
			  DBUtils.close(stmt);
			  DBUtils.close(conn);
		  }
	  }
	  public java.util.Map<Integer, DBInstanceInfo> loadDbInfo(Connection conn)
	  {
		String sql = "select * from " + DBINFO_TABLENAME;
			
		Statement stmt = null;
		ResultSet rs = null;
		java.util.Map<Integer, DBInstanceInfo> dbMap = new java.util.HashMap<Integer, DBInstanceInfo>();
		try
		{
		  stmt = conn.createStatement();
		  rs = stmt.executeQuery(sql);
		  while(rs!=null && rs.next())
		  {
		    DBInstanceInfo dbinfo = new DBInstanceInfo();
		    dbinfo.setDbid(rs.getInt("DBID"));
		    dbinfo.setDbType(rs.getString("DBTYPE"));
			dbinfo.setDbGroupName(rs.getString("DBGROUPNAME"));
			dbinfo.setInstance(rs.getShort("INSTANCE"));
			dbinfo.setHostName(rs.getString("HOSTNAME"));
			dbinfo.setPort(rs.getString("PORT"));
			dbinfo.setDatabaseName(rs.getString("DATABASE_NAME"));
			dbinfo.setUseTunneling(rs.getShort("USE_SSHTUNNEL")==1?true:false);
			dbinfo.setLocalHostName(rs.getString("LOCAL_HOSTNAME"));
			dbinfo.setLocalPort(rs.getString("LOCAL_PORT"));
			dbinfo.setConnectionVerified(rs.getInt("CONNECTION_VERIFIED")==1);
			dbinfo.setVirtualHost(rs.getInt("VIRTUAL_HOST")==1);
			dbinfo.setSnmpEnabled(rs.getShort("SNMP_ENABLED")==1?true:false);
			dbinfo.setMetricsEnabled(rs.getShort("METRICS_ENABLED")==1?true:false);
			dbinfo.setAlertEnabled(rs.getShort("ALERT_ENABLED")==1?true:false);
			dbinfo.setOwner(rs.getString("OWNER"));
			dbMap.put(dbinfo.getDbid(), dbinfo);
		  }
		}catch(Exception ex)
		{
		}finally
		{
		  DBUtils.close(rs);
		  DBUtils.close(stmt);
		}
		return dbMap;
	  }

	  class MetaHostSyncer implements Runnable
	  {
		private List<DBInstanceInfo> dbList;
		
		MetaHostSyncer()
		{
			  
		}
		@Override
		public void run() {
			Connection conn = null;
			Map<Integer, DBInstanceInfo> metaHosts = new java.util.HashMap<Integer, DBInstanceInfo>(dbList.size());
			for(DBInstanceInfo info: dbList)
				metaHosts.put(info.getDbid(), info);
			try
			{
				logger.info("Start to sync meta DB host info with metrics DB host info");
				conn = createConnection(true);
				Map<Integer, DBInstanceInfo> metricsHosts = loadDbInfo(conn);
				
				for(DBInstanceInfo info: dbList)
				{
					int dbid = info.getDbid();
					if(!metricsHosts.containsKey(dbid))
					{
						upsertDBInfo(conn, info, true);
					}
					else
					{
						DBInstanceInfo info2 = metricsHosts.get(dbid);
						try
						{
							if(!info2.getDbGroupName().equals(info.getDbGroupName()) || !info2.getHostName().equals(info.getHostName()))
								upsertDBInfo(conn, info, false);
						}catch(Exception ex)
						{
							
						}
					}
				}
			}catch(Exception ex)
			{
				
			}finally
			{
				DBUtils.close(conn);
			}
			logger.info("Ended: sync meta DB host info with metrics DB host info");
		}
		public void setDbList(List<DBInstanceInfo> dbList) {
			this.dbList = dbList;
		}
		  
	  }
	  
	  /**
	   * remove one entry from meta db
	   * @param dbGroupName
	   * @param hostName
	   * @param owner
	   * @param force If false, only delete the entry owned by owner
	   * @return
	   */
	  public boolean removeDBInfo(String dbGroupName, String hostName,String owner, boolean force)
	  {
	    Connection conn = null;
		try
		{		
		  conn = this.createConnection(true);
		  if( removeDBInfo(conn, dbGroupName, hostName,owner,force) )
			  return this.removeAlertSetting(conn, dbGroupName, hostName);
		  return false;
		}catch(Exception ex)
		{
		  logger.log(Level.SEVERE,"Exception", ex);
	    }
		finally
		{
		  DBUtils.close(conn);
		}
		return false;
	  }

	  private boolean removeDBInfo(Connection conn, String dbGroupName, String hostName, String owner, boolean force)
	  {
		String sql = "delete from "+ DBINFO_TABLENAME +" where dbgroupname=? and hostname=?";
		if(!force)sql = "delete from "+ DBINFO_TABLENAME +" where dbgroupname=? and hostname=? and owner=?";
		
		PreparedStatement pstmt = null;
		try
		{
		  pstmt = conn.prepareStatement(sql);
		  pstmt.setString(1, dbGroupName.toLowerCase());
		  pstmt.setString(2, hostName.toLowerCase());
		  if(!force)
			pstmt.setString(3, owner);
		  pstmt.execute();
		  logger.info("Removed the database host : ("+dbGroupName+", "+hostName+"), owenr "+owner+", force "+force);
		  return pstmt.getUpdateCount()>0;
		}catch(Exception ex)
		{
		  logger.log(Level.SEVERE,"Exception", ex);
		}finally
		{
		  DBUtils.close(pstmt);
		}
		return false;
	  }

	  public boolean removeAlertSetting(String dbGroupName, String hostName)
	  {
	    Connection conn = null;
		try
		{		
		  conn = this.createConnection(true);
		  return removeAlertSetting(conn, dbGroupName, hostName);
		}catch(Exception ex)
		{
		  logger.log(Level.SEVERE,"Exception", ex);
	    }
		finally
		{
		  DBUtils.close(conn);
		}
		return false;
	  }

	  private boolean removeAlertSetting(Connection conn, String dbGroupName, String hostName)
	  {
		String sql = "delete from " +ALERTSETTING_TABLENAME+ " where dbgroupname=? and hostname=?";
		
		PreparedStatement pstmt = null;
		try
		{
		  pstmt = conn.prepareStatement(sql);
		  pstmt.setString(1, dbGroupName.toLowerCase());
		  pstmt.setString(2, hostName.toLowerCase());
		  pstmt.execute();
		  logger.info("Removed alert setting for the database host : ("+dbGroupName+", "+hostName+")");
		  return true;
		}catch(Exception ex)
		{
		  logger.log(Level.SEVERE,"Exception", ex);
		}finally
		{
		  DBUtils.close(pstmt);
		}
		return false;
	  }

	  /**
	   * 
	   * @param dbGroupName
	   * @param owner
	   * @param force if false, only remove entries with the same owner as owner
	   * @return
	   */
	  public boolean removeDbGroup(String dbGroupName, String owner, boolean force)
	  {
	    Connection conn = null;
		try
		{			
		  conn = this.createConnection(true);
		  return removeDbGroup(conn, dbGroupName, owner, force);
		}catch(Exception ex)
		{
		  logger.log(Level.SEVERE,"Exception", ex);
		}
		finally
		{
		  DBUtils.close(conn);
		}
		return false;
	  }

	  public boolean removeDbGroup(Connection conn, String dbGroupName, String owner, boolean force)
	  {
	    String sql = "delete from "+DBINFO_TABLENAME+" where dbgroupname=?";
		if(!force)
		  sql = "delete from "+ DBINFO_TABLENAME +" where dbgroupname=? and owner=?";
		PreparedStatement pstmt = null;
		try
		{
		  pstmt = conn.prepareStatement(sql);
		  pstmt.setString(1, dbGroupName.toLowerCase());
		  if(!force) pstmt.setString(2, owner);
		  pstmt.execute();
		  logger.info("Removed the database host : ("+dbGroupName+")");
		  return pstmt.getUpdateCount()>0;
		}catch(Exception ex)
		{
		  logger.log(Level.SEVERE,"Exception", ex);
		}finally
		{
		  DBUtils.close(pstmt);
		}
		return false;
	  }

	  /**
	   * Retrieve a database entry identified by a dbGroupName and hostname
	   * @param dbGroupName
	   * @param hostName
	   * @return
	   */
	  public DBInstanceInfo retrieveDBInfo(String dbGroupName, String hostName)
	  {
	    Connection conn = null;
		try
		{
		  conn = createConnection(true);
		  return retrieveDBInfo(conn, dbGroupName, hostName);
		}catch(Exception ex)
		{
		  logger.log(Level.SEVERE,"Exception", ex);
		}
		finally
		{
		  DBUtils.close(conn);
		}
		return null;
	  }

	  private DBInstanceInfo retrieveDBInfo(Connection conn, String dbGroupName, String hostName)
	  {
	    String sql = "select * from "+ DBINFO_TABLENAME +" where dbgroupname=? and hostname=?";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try
		{
		  pstmt = conn.prepareStatement(sql);
		  pstmt.setString(1, dbGroupName.toLowerCase());
		  pstmt.setString(2, hostName.toLowerCase());
		  rs = pstmt.executeQuery();
		  if(rs!=null && rs.next())
		  {
		    DBInstanceInfo dbinfo = new DBInstanceInfo();
		    dbinfo.setDbid(rs.getInt("DBID"));
		    dbinfo.setDbType(rs.getString("DBTYPE"));
			dbinfo.setDbGroupName(rs.getString("DBGROUPNAME"));
			dbinfo.setInstance(rs.getShort("INSTANCE"));
			dbinfo.setHostName(rs.getString("HOSTNAME"));
			dbinfo.setPort(rs.getString("PORT"));
			dbinfo.setDatabaseName(rs.getString("DATABASE_NAME"));
			dbinfo.setUseTunneling(rs.getShort("USE_SSHTUNNEL")==1?true:false);
			dbinfo.setLocalHostName(rs.getString("LOCAL_HOSTNAME"));
			dbinfo.setLocalPort(rs.getString("LOCAL_PORT"));
			dbinfo.setConnectionVerified(1==rs.getInt("CONNECTION_VERIFIED"));
			dbinfo.setVirtualHost(rs.getShort("VIRTUAL_HOST")==1?true:false);
			dbinfo.setSnmpEnabled(rs.getShort("SNMP_ENABLED")==1?true:false);
			dbinfo.setMetricsEnabled(rs.getShort("METRICS_ENABLED")==1?true:false);
			dbinfo.setAlertEnabled(rs.getShort("ALERT_ENABLED")==1?true:false);
			dbinfo.setOwner(rs.getString("OWNER"));
			return dbinfo;
		  }
	    }catch(Exception ex)
	    {
	      logger.log(Level.SEVERE,"Exception", ex);
		}finally
		{
		  DBUtils.close(rs);
		  DBUtils.close(pstmt);
		}
		return null;
	  }

	  /**
	   * Store db info into db. If exists, update the info. If not, insert new one.
	   * @param dbinfo
	   */
	  public void upsertDBInfo(DBInstanceInfo dbinfo)
	  {
	    if(dbinfo==null)return;
		String sql2 = "update "+DBINFO_TABLENAME+" set dbtype=?,instance=?, port=?, database_name=?,USE_SSHTUNNEL=?, LOCAL_HOSTNAME=?, LOCAL_PORT=?, CONNECTION_VERIFIED=?, VIRTUAL_HOST=? where DBGROUPNAME=? and HOSTNAME=?";
		String sql3 = "insert into "+DBINFO_TABLENAME+" (dbgroupname,hostname,dbtype,instance, port, database_name,USE_SSHTUNNEL, LOCAL_HOSTNAME, LOCAL_PORT,CONNECTION_VERIFIED,VIRTUAL_HOST, OWNER) values(?,?,?,?,?,?,?,?,?,?,?,?)";
			
		logger.info("Store or update db "+dbinfo.toString());
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean findOne = false;
		try
		{
		  conn = createConnection(true);
		  //first, check if we have record
		  findOne = this.retrieveDBInfo(conn, dbinfo.getDbGroupName().toLowerCase(), dbinfo.getHostName().toLowerCase())!=null;
		  if(findOne)
		  {
		    pstmt = conn.prepareStatement(sql2);
		    int idx = 1;
			pstmt.setString(idx++, dbinfo.getDbType());
			pstmt.setString(idx++, String.valueOf(dbinfo.getInstance()));
			if(dbinfo.getPortShort() != 0)
				pstmt.setShort(idx++, dbinfo.getPortShort());
			else pstmt.setNull(idx++, java.sql.Types.SMALLINT);
			pstmt.setString(idx++, dbinfo.getDatabaseName());
			pstmt.setString(idx++, dbinfo.isUseTunneling()?"1":"0");
			if(dbinfo.getLocalHostName()!=null)
			  pstmt.setString(idx++, dbinfo.getLocalHostName().toLowerCase());
			else 
			  pstmt.setNull(idx++, java.sql.Types.VARCHAR);
			if(dbinfo.getLocalPortShort() != 0)
			  pstmt.setShort(idx++, dbinfo.getLocalPortShort());
			else 
			  pstmt.setNull(idx++, java.sql.Types.SMALLINT);
			pstmt.setString(idx++, dbinfo.isConnectionVerified()?"1":"0");
			pstmt.setString(idx++, dbinfo.isVirtualHost()?"1":"0");
			pstmt.setString(idx++, dbinfo.getDbGroupName().toLowerCase());
			pstmt.setString(idx++, dbinfo.getHostName().toLowerCase());
			pstmt.execute();
			//conn.commit();
		  }else
		  {
			int idx = 1;
		    pstmt = conn.prepareStatement(sql3);
			pstmt.setString(idx++, dbinfo.getDbGroupName().toLowerCase());
			pstmt.setString(idx++, dbinfo.getHostName().toLowerCase());
			pstmt.setString(idx++, dbinfo.getDbType());
			pstmt.setString(idx++, String.valueOf(dbinfo.getInstance()));
			if(dbinfo.getPortShort() != 0)
			  pstmt.setShort(idx++, dbinfo.getPortShort());
			else 
			  pstmt.setNull(idx++, java.sql.Types.SMALLINT);
			pstmt.setString(idx++, dbinfo.getDatabaseName());
			pstmt.setString(idx++, dbinfo.isUseTunneling()?"1":"0");
			pstmt.setString(idx++, dbinfo.getLocalHostName());
			if(dbinfo.getLocalPortShort() != 0)
			  pstmt.setShort(idx++, dbinfo.getLocalPortShort());
			else 
			  pstmt.setNull(idx++, java.sql.Types.SMALLINT);
			pstmt.setString(idx++, dbinfo.isConnectionVerified()?"1":"0");
			pstmt.setString(idx++, dbinfo.isVirtualHost()?"1":"0");
			pstmt.setString(idx++, dbinfo.getOwner());
			pstmt.execute();
			//conn.commit();
		  }
		}catch(Exception ex)
		{
		  logger.info("Failed to save "+dbinfo.toString());
		  logger.log(Level.SEVERE,"Exception", ex);
		  if(conn!=null)try{conn.rollback();}catch(Exception iex){}
		  throw new RuntimeException(ex);
		}finally
		{
		  DBUtils.close(pstmt);
		  DBUtils.close(conn);
		}			
	  }

	  public void upsertAlertSetting(String dbgroup, String dbhost, String alertType, float threshold, String comments)
	  {
		String sql2 = "update " + ALERTSETTING_TABLENAME+ " set ALERT_TYPE=?,THRESHOLD=?, RESERVED=? where DBGROUPNAME=? and HOSTNAME=?";
		String sql3 = "insert into " +ALERTSETTING_TABLENAME+" (DBGROUPNAME,HOSTNAME,ALERT_TYPE,THRESHOLD, RESERVED) values(?,?,?,?,?)";
			
		logger.info("Store or update alert settings for db "+ dbgroup+", "+dbhost);
		Connection conn = null;	
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean findOne = false;
		try
		{
		  conn = this.createConnection(true);
		  //first, check if we have record
		  pstmt = conn.prepareStatement("select ALERT_TYPE, THRESHOLD from "+ ALERTSETTING_TABLENAME +" where DBGROUPNAME=? and HOSTNAME=?");
		  pstmt.setString(1, dbgroup);
		  pstmt.setString(2, dbhost);
		  rs = pstmt.executeQuery();
		  if(rs!=null && rs.next())
		  {
			  findOne = true;
		  }
		  DBUtils.close(rs);
		  DBUtils.close(pstmt); pstmt = null;
		  
		  if(findOne)
		  {
		    pstmt = conn.prepareStatement(sql2);
		    int idx = 1;
			pstmt.setString(idx++, alertType);
			pstmt.setFloat(idx++, threshold);
			if(comments!=null && !comments.isEmpty())
				pstmt.setString(idx++, comments);
			else pstmt.setNull(idx++, java.sql.Types.VARCHAR);
			pstmt.setString(idx++, dbgroup);
			pstmt.setString(idx++, dbhost);
			pstmt.execute();
			//conn.commit();
		  }else
		  {
			int idx = 1;
		    pstmt = conn.prepareStatement(sql3);
			pstmt.setString(idx++, dbgroup);
			pstmt.setString(idx++, dbhost);
			pstmt.setString(idx++, alertType);
			pstmt.setFloat(idx++, threshold);
			if(comments!=null && !comments.isEmpty())
			  pstmt.setString(idx++, comments);
			else 
			  pstmt.setNull(idx++, java.sql.Types.VARCHAR);
			
			pstmt.execute();
			//conn.commit();
		  }
		}catch(Exception ex)
		{
		  logger.info("Failed to save alerts for "+dbgroup+", "+dbhost);
		  logger.log(Level.SEVERE,"Exception", ex);
		  if(conn!=null)try{conn.rollback();}catch(Exception iex){}
		  throw new RuntimeException(ex);
		}finally
		{
		  DBUtils.close(pstmt);
		  DBUtils.close(conn);
		}			
	  }

	  public void upsertAlertNotification(String dbgroup, String dbhost, String emails)
	  {
		String sql2 = "update " + ALERT_NOTIFICATION+ " set EMAILS=? where DBGROUP=? and HOSTNAME=?";
		String sql3 = "insert into " + ALERT_NOTIFICATION+" (DBGROUP,HOSTNAME,EMAILS) values(?,?,?)";
			
		logger.info("Store or update alert notifications for db "+ dbgroup+", "+dbhost);
		Connection conn = null;	
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean findOne = false;
		try
		{
		  conn = this.createConnection(true);
		  //first, check if we have record
		  pstmt = conn.prepareStatement("select EMAILS from "+ ALERT_NOTIFICATION +" where DBGROUP=? and HOSTNAME=?");
		  pstmt.setString(1, dbgroup);
		  pstmt.setString(2, dbhost);
		  rs = pstmt.executeQuery();
		  if(rs!=null && rs.next())
		  {
			  findOne = true;
		  }
		  DBUtils.close(rs);
		  DBUtils.close(pstmt); pstmt = null;
		  
		  if(findOne)
		  {
		    pstmt = conn.prepareStatement(sql2);
		    int idx = 1;
			pstmt.setString(idx++, emails);
			pstmt.setString(idx++, dbgroup);
			pstmt.setString(idx++, dbhost);
			pstmt.execute();
			//conn.commit();
		  }else
		  {
			int idx = 1;
		    pstmt = conn.prepareStatement(sql3);
			pstmt.setString(idx++, dbgroup);
			pstmt.setString(idx++, dbhost);
			pstmt.setString(idx++, emails);			
			pstmt.execute();
			//conn.commit();
		  }
		}catch(Exception ex)
		{
		  logger.info("Failed to save alert notification for "+dbgroup+", "+dbhost);
		  logger.log(Level.SEVERE,"Exception", ex);
		  if(conn!=null)try{conn.rollback();}catch(Exception iex){}
		  throw new RuntimeException(ex);
		}finally
		{
		  DBUtils.close(pstmt);
		  DBUtils.close(conn);
		}			
	  }

	  
	  public void loadAlertSetting(AlertSettings alertSettings)
	  {
		logger.info("Loadalert settings from db");
		Connection conn = null;	
	    Statement pstmt = null;
		ResultSet rs = null;
		int count = 0;
		try
		{
		  conn = this.createConnection(true);
		  //first, check if we have record
		  pstmt = conn.createStatement();
		  rs = pstmt.executeQuery("select DBGROUPNAME, HOSTNAME, ALERT_TYPE, THRESHOLD from "+ ALERTSETTING_TABLENAME);
		  while(rs!=null && rs.next())
		  {
			  String dbgroup = rs.getString("DBGROUPNAME");
			  String dbhost = rs.getString("HOSTNAME");
			  String alertType = rs.getString("ALERT_TYPE");
			  Float threshold = rs.getFloat("THRESHOLD");
			  alertSettings.updateAlertThreshold(dbgroup, dbhost, alertType, threshold, false);//we don't want to store it back, so last arg is false
			  count++;
		  }
		  DBUtils.close(rs);
		  rs = pstmt.executeQuery("select DBGROUP, HOSTNAME, EMAILS from "+ ALERT_NOTIFICATION);
		  while(rs!=null && rs.next())
		  {
			  String dbgroup = rs.getString("DBGROUP");
			  String dbhost = rs.getString("HOSTNAME");
			  String emails = rs.getString("EMAILS");
			  alertSettings.updateAlertNotification(dbgroup, dbhost, emails, false);//we don't want to store it back, so last arg is false
			  count++;
		  }
		  
		}catch(Exception ex)
		{
		  logger.info("Failed to load alert settings ");
		  logger.log(Level.SEVERE,"Exception", ex);
		}finally
		{
		  DBUtils.close(rs);
		  DBUtils.close(pstmt);
		  DBUtils.close(conn);
		}
		logger.info("Load " + count + " customized alert settings.");
	  }

	  /**
	   * Enable or disable snmp query
	   * @param dbinfo
	   * @param enabled
	   */
	  public boolean enableSnmp(String dbGroup, String hostname, boolean enabled,String owner, boolean force)
	  {
		String sql2 = "update "+DBINFO_TABLENAME+" set SNMP_ENABLED=? where DBGROUPNAME=? and HOSTNAME=?";
		if(!force)sql2 = "update "+DBINFO_TABLENAME+" set SNMP_ENABLED=? where DBGROUPNAME=? and HOSTNAME=? AND OWNER=?";	
		logger.info("Update snmp metrics gathering for "+dbGroup+", "+hostname);
		Connection conn = null;
		PreparedStatement pstmt = null;
		try
		{
		  conn = this.createConnection(true);
		   pstmt = conn.prepareStatement(sql2);
		   pstmt.setInt(1, enabled?1:0);
		   pstmt.setString(2, dbGroup);
		   pstmt.setString(3, hostname);
		   if(!force)
			   pstmt.setString(4, owner);
		   pstmt.execute();
		   //conn.commit();	 
		   return pstmt.getUpdateCount()>0;
		}catch(Exception ex)
		{
		  logger.info("Failed to update snmp_enabled "+dbGroup+", "+hostname);
		  logger.log(Level.SEVERE,"Exception", ex);
		  if(conn!=null)try{conn.rollback();}catch(Exception iex){}
			return false;
		}finally
		{
		  DBUtils.close(pstmt);
		  DBUtils.close(conn);
		}
	  }

	  public boolean enableMetrics(String dbGroup, String hostname, boolean enabled,String owner, boolean force)
	  {
		String sql2 = "update "+DBINFO_TABLENAME+" set METRICS_ENABLED=? where DBGROUPNAME=? and HOSTNAME=?";
		if(!force)sql2 = "update "+DBINFO_TABLENAME+" set METRICS_ENABLED=? where DBGROUPNAME=? and HOSTNAME=? AND OWNER=?";	
		logger.info("Update metrics gathering for "+dbGroup+", "+hostname);
		Connection conn = null;
		PreparedStatement pstmt = null;
		try
		{
		  conn = this.createConnection(true);
		   pstmt = conn.prepareStatement(sql2);
		   pstmt.setInt(1, enabled?1:0);
		   pstmt.setString(2, dbGroup);
		   pstmt.setString(3, hostname);
		   if(!force)
			   pstmt.setString(4, owner);
		   pstmt.execute();
		   //conn.commit();	 
		   return pstmt.getUpdateCount()>0;
		}catch(Exception ex)
		{
		  logger.info("Failed to update metrics_enabled "+dbGroup+", "+hostname);
		  logger.log(Level.SEVERE,"Exception", ex);
		  if(conn!=null)try{conn.rollback();}catch(Exception iex){}
			return false;
		}finally
		{
		  DBUtils.close(pstmt);
		  DBUtils.close(conn);
		}
	  }

	  public boolean enableAlerts(String dbGroup, String hostname, boolean enabled,String owner, boolean force)
	  {
		String sql2 = "update "+DBINFO_TABLENAME+" set ALERT_ENABLED=? where DBGROUPNAME=? and HOSTNAME=?";
		if(!force)sql2 = "update "+DBINFO_TABLENAME+" set ALERT_ENABLED=? where DBGROUPNAME=? and HOSTNAME=? AND OWNER=?";	
		logger.info("Update alert gathering for "+dbGroup+", "+hostname);
		Connection conn = null;
		PreparedStatement pstmt = null;
		try
		{
		  conn = this.createConnection(true);
		   pstmt = conn.prepareStatement(sql2);
		   pstmt.setInt(1, enabled?1:0);
		   pstmt.setString(2, dbGroup);
		   pstmt.setString(3, hostname);
		   if(!force)
			   pstmt.setString(4, owner);
		   pstmt.execute();
		   //conn.commit();	 
		   return pstmt.getUpdateCount()>0;
		}catch(Exception ex)
		{
		  logger.info("Failed to update alert_enabled "+dbGroup+", "+hostname);
		  logger.log(Level.SEVERE,"Exception", ex);
		  if(conn!=null)try{conn.rollback();}catch(Exception iex){}
			return false;
		}finally
		{
		  DBUtils.close(pstmt);
		  DBUtils.close(conn);
		}
	  }

	  /**
	   * Retrieve all DB entries based on keyword. 
	   * To retrieve all entries, use blank keyword (null string,
	   * empty string, or %)
	   * @param keyword
	   * @return
	   */
	  public java.util.List<DBInstanceInfo> SearchDbInfo(String keyword)
	  {
		String sql = null;
		if(keyword==null || keyword.isEmpty() || "%".equals(keyword.trim()))
			sql = "select * from "+DBINFO_TABLENAME+" order by dbtype, dbgroupname, hostname";
		else
		{
			keyword = keyword.trim();
			sql = "select * from "+DBINFO_TABLENAME+" where hostname like '%"+keyword.toLowerCase()
					    +"%' or lower(database_name) like '%"+keyword.toLowerCase()
					    +"%' or lower(dbgroupname) like '%"+keyword.toLowerCase()
					    +"%' order by dbtype, dbgroupname, hostname";
		}
		logger.info("Excute: " + sql);
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		java.util.ArrayList<DBInstanceInfo> dbList = new java.util.ArrayList<DBInstanceInfo>();
		try
		{
		  conn = this.createConnection(true);
		  stmt = conn.createStatement();
		  rs = stmt.executeQuery(sql);
		  while(rs!=null && rs.next())
		  {
		    DBInstanceInfo dbinfo = new DBInstanceInfo();
		    dbinfo.setDbid(rs.getInt("DBID"));
		    dbinfo.setDbType(rs.getString("DBTYPE"));
			dbinfo.setDbGroupName(rs.getString("DBGROUPNAME"));
			dbinfo.setInstance(rs.getShort("INSTANCE"));
			dbinfo.setHostName(rs.getString("HOSTNAME"));
			dbinfo.setPort(rs.getString("PORT"));
			dbinfo.setDatabaseName(rs.getString("DATABASE_NAME"));
			dbinfo.setUseTunneling(rs.getShort("USE_SSHTUNNEL")==1?true:false);
			dbinfo.setLocalHostName(rs.getString("LOCAL_HOSTNAME"));
			dbinfo.setLocalPort(rs.getString("LOCAL_PORT"));
			dbinfo.setConnectionVerified(rs.getInt("CONNECTION_VERIFIED")==1);
			dbinfo.setVirtualHost(rs.getInt("VIRTUAL_HOST")==1);
			dbinfo.setSnmpEnabled(rs.getShort("SNMP_ENABLED")==1?true:false);
			dbinfo.setMetricsEnabled(rs.getShort("METRICS_ENABLED")==1?true:false);
			dbinfo.setAlertEnabled(rs.getShort("ALERT_ENABLED")==1?true:false);
			dbList.add(dbinfo);
		  }
		}catch(Exception ex)
		{
		  logger.log(Level.SEVERE, "Failed to retrieve db info", ex);
		}finally
		{
		  DBUtils.close(rs);
		  DBUtils.close(stmt);
		  DBUtils.close(conn);
		}
		return dbList;
	  }

	  /**
	   * add a record in snapshots table for a given timestamp and retrieve the snapid 
	   * @param ts
	   * @return
	   */
	  public int getNextSnapshotId(long ts)
	  {
			Connection conn = null;
			PreparedStatement stmt = null;
			ResultSet rs = null;
			String insertSQL = "insert into SNAPSHOTS (START_TS) values(?)";
			String retrieveSQL = "select SNAP_ID from SNAPSHOTS where START_TS=?";
			try
			{
			  conn = this.createConnection(true);
			  stmt = conn.prepareStatement(insertSQL);
			  stmt.setLong(1, ts);
			  stmt.execute();
			  stmt.close();
			  stmt = conn.prepareStatement(retrieveSQL);
			  stmt.setLong(1, ts);
			  rs = stmt.executeQuery();
			  if(rs!=null && rs.next())
			  {
				  return rs.getInt(1);
			  }
			}catch(Exception ex)
			{
			}finally
			{
			  DBUtils.close(rs);
			  DBUtils.close(stmt);
			  DBUtils.close(conn);
			}
		  return -1;//error
	  }
	  
	  /**
	   * Retrieve snapshot range based on given timestamp range 
	   * @param startTs
	   * @param endTs
	   * @return
	   */
	  public int[] getSnapshostRange(long startTs, long endTs)
	  {
			Connection conn = null;
			PreparedStatement stmt = null;
			ResultSet rs = null;
			String retrieveSQL = "select min(SNAP_ID) min_snap_id, max(snap_id) max_snap_id from SNAPSHOTS where START_TS between ? and ?";
			try
			{
			  conn = this.createConnection(true);
			  stmt = conn.prepareStatement(retrieveSQL);
			  stmt.setLong(1, startTs);
			  stmt.setLong(2, endTs);
			  rs = stmt.executeQuery();
			  if(rs!=null && rs.next())
			  {
				  return new int[] {rs.getInt(1), rs.getInt(2)};
			  }
			}catch(Exception ex)
			{
			}finally
			{
			  DBUtils.close(rs);
			  DBUtils.close(stmt);
			  DBUtils.close(conn);
			}
		  return null;//failed
	  }
	  
	  /**
	   * Update snapshot end timestamp
	   * @param snap_id
	   * @param ts
	   */
	  public void updateSnapCompleteTime(int snap_id, long ts)
	  {
			Connection conn = null;
			PreparedStatement stmt = null;
			String sql = "update SNAPSHOTS set END_TS=? where SNAP_ID=?";
			try
			{
			  conn = this.createConnection(true);
			  stmt = conn.prepareStatement(sql);
			  stmt.setLong(1, ts);
			  stmt.setInt(1, snap_id);
			  stmt.execute();
			}catch(Exception ex)
			{
			}finally
			{
			  DBUtils.close(stmt);
			  DBUtils.close(conn);
			}	  
	  }

	  public List<AlertSubscribers.Subscription> loadAlertSubscriptions() throws SQLException
	  {
		  List<AlertSubscribers.Subscription> subscribers = new ArrayList<AlertSubscribers.Subscription>();
		  Connection conn = null;
		  Statement stmt = null;
		  ResultSet rs = null;
		  String sql = "select * from ALERT_SUBSCRIPT";
		  try
		  {
			 conn = this.createConnection(true);
			 stmt = conn.createStatement();
			 rs = stmt.executeQuery(sql);
			 while(rs!=null && rs.next())
			 {
				 AlertSubscribers.Subscription sub = new AlertSubscribers.Subscription();
				 sub.group = rs.getString("DBGROUP");
				 sub.host = rs.getString("HOSTNAME");
				 sub.alertName = rs.getString("ALERT_NAME");
				 sub.setParams(rs.getString("PARAMS"));
				 subscribers.add(sub);
			 }
			 
		  }finally
		  {
			  DBUtils.close(rs);
			  DBUtils.close(stmt);
			  DBUtils.close(conn);
		  }	  
		  
		  return subscribers;
		  
	  }
	  public boolean upsertAlertSubscription(AlertSubscribers.Subscription sub)
	  {
		String sql2 = "update " + ALERT_SUBSCRIPT+ " set PARAMS=? where DBGROUP=? and HOSTNAME=? AND ALERT_NAME=?";
		String sql3 = "insert into " +ALERT_SUBSCRIPT+" (DBGROUP,HOSTNAME,ALERT_NAME,PARAMS) values(?,?,?,?)";
			
		logger.info("Store or update alert sub " + sub.alertName + "for db "+ sub.group+", "+sub.host);
		Connection conn = null;	
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean findOne = false;
		try
		{
		  conn = this.createConnection(true);
		  //first, check if we have record
		  pstmt = conn.prepareStatement("select ID from "+ ALERT_SUBSCRIPT +" where DBGROUP=? and HOSTNAME=? and ALERT_NAME=?");
		  pstmt.setString(1, sub.group);
		  if(sub.host != null && !sub.host.isEmpty())
			  pstmt.setString(2, sub.host);
		  else
			  pstmt.setNull(2, java.sql.Types.VARCHAR);
		  pstmt.setString(3, sub.alertName);
		  rs = pstmt.executeQuery();
		  if(rs!=null && rs.next())
		  {
			  findOne = true;
		  }
		  DBUtils.close(rs);
		  DBUtils.close(pstmt); pstmt = null;
		  
		  if(findOne)
		  {
		    pstmt = conn.prepareStatement(sql2);
		    String s = sub.paramToJSON();
		    if(s!=null)
		    	pstmt.setString(1, sub.paramToJSON());
		    else
				pstmt.setNull(1, java.sql.Types.VARCHAR);
			pstmt.setString(2, sub.group);
			if(sub.host != null && !sub.host.isEmpty())
				pstmt.setString(3, sub.host);
			else
				pstmt.setNull(3, java.sql.Types.VARCHAR);
			pstmt.setString(4, sub.alertName);
			pstmt.execute();
		  }else
		  {
		    pstmt = conn.prepareStatement(sql3);
			pstmt.setString(1, sub.group);
			if(sub.host != null && !sub.host.isEmpty())
				pstmt.setString(2, sub.host);
			else
				pstmt.setNull(2, java.sql.Types.VARCHAR);
			pstmt.setString(3, sub.alertName);
		    String s = sub.paramToJSON();
		    if(s!=null)
		    	pstmt.setString(4, sub.paramToJSON());
		    else
				pstmt.setNull(4, java.sql.Types.VARCHAR);
			
			pstmt.execute();
		  }
		  return true;
		}catch(Exception ex)
		{
		  logger.info("Failed to save alert sub " +sub.alertName + " for "+sub.group+", "+sub.host);
		  logger.log(Level.SEVERE,"Exception", ex);
		  if(conn!=null)try{conn.rollback();}catch(Exception iex){}
		}finally
		{
		  DBUtils.close(pstmt);
		  DBUtils.close(conn);
		}
		return false;
	  }
	  
	  public boolean deleteAlertSubscription(AlertSubscribers.Subscription sub)
	  {
		String sql2 = "delete from " + ALERT_SUBSCRIPT+ " where DBGROUP=? and HOSTNAME=? AND ALERT_NAME=?";
			
		logger.info("Delete alert sub " + sub.alertName + "for db "+ sub.group+", "+sub.host);
		Connection conn = null;	
		PreparedStatement pstmt = null;
		try
		{
		  conn = this.createConnection(true);
		  
		  pstmt = conn.prepareStatement(sql2);
		  pstmt.setString(1, sub.group);
		  if(sub.host != null && !sub.host.isEmpty())
				pstmt.setString(2, sub.host);
		  else
		  pstmt.setNull(2, java.sql.Types.VARCHAR);
		  pstmt.setString(3, sub.alertName);
		  pstmt.execute();
		  return true;
		}catch(Exception ex)
		{
		  logger.info("Failed to delete alert sub " +sub.alertName + " for "+sub.group+", "+sub.host);
		  logger.log(Level.SEVERE,"Exception", ex);
		  if(conn!=null)try{conn.rollback();}catch(Exception iex){}
		}finally
		{
		  DBUtils.close(pstmt);
		  DBUtils.close(conn);
		}
		return false;
	  }

	  public List<MetricsSubscribers.Subscription> loadMetricsSubscriptions() throws SQLException
	  {
		  List<MetricsSubscribers.Subscription> subscribers = new ArrayList<MetricsSubscribers.Subscription>();
		  Connection conn = null;
		  Statement stmt = null;
		  ResultSet rs = null;
		  String sql = "select * from " + METRICS_SUBSCRIPT;
		  try
		  {
			 conn = this.createConnection(true);
			 stmt = conn.createStatement();
			 rs = stmt.executeQuery(sql);
			 while(rs!=null && rs.next())
			 {
				 MetricsSubscribers.Subscription sub = new MetricsSubscribers.Subscription();
				 sub.group = rs.getString("DBGROUP");
				 sub.host = rs.getString("HOSTNAME");
				 sub.mGroup = rs.getString("MGROUP");
				 sub.mSubGroup = rs.getString("MSUBGROUP");
				 subscribers.add(sub);
			 }
			 
		  }finally
		  {
			  DBUtils.close(rs);
			  DBUtils.close(stmt);
			  DBUtils.close(conn);
		  }	  
		  
		  return subscribers;
		  
	  }
	  public boolean addMetricsSubscription(MetricsSubscribers.Subscription sub)
	  {
		String sql3 = "insert into " +METRICS_SUBSCRIPT+" (DBGROUP,HOSTNAME,MGROUP,MSUBGROUP) values(?,?,?,?)";
			
		logger.info("Store metricst sub " + sub.mGroup + ", " + sub.mSubGroup + " for db "+ sub.group+", "+sub.host);
		Connection conn = null;	
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean findOne = false;
		try
		{
		  conn = this.createConnection(true);
		  //first, check if we have record
		  pstmt = conn.prepareStatement("select ID from "+ METRICS_SUBSCRIPT +" where DBGROUP=? and HOSTNAME=? and MGROUP=? and MSUBGROUP=?");
		  pstmt.setString(1, sub.group);
		  if(sub.host != null && !sub.host.isEmpty())
			  pstmt.setString(2, sub.host);
		  else
			  pstmt.setNull(2, java.sql.Types.VARCHAR);
		  pstmt.setString(3, sub.mGroup);
		  if(sub.mSubGroup != null && !sub.mSubGroup.isEmpty())
			  pstmt.setString(4, sub.mSubGroup);
		  else
			  pstmt.setNull(4, java.sql.Types.VARCHAR);
		  
		  rs = pstmt.executeQuery();
		  if(rs!=null && rs.next())
		  {
			  findOne = true;
		  }
		  DBUtils.close(rs);
		  DBUtils.close(pstmt); pstmt = null;
		  
		  if(!findOne)
		  {
		    pstmt = conn.prepareStatement(sql3);
			pstmt.setString(1, sub.group);
			if(sub.host != null && !sub.host.isEmpty())
				pstmt.setString(2, sub.host);
			else
				pstmt.setNull(2, java.sql.Types.VARCHAR);
			pstmt.setString(3, sub.mGroup);
		    if(sub.mSubGroup != null)
		    	pstmt.setString(4,sub.mSubGroup);
		    else
				pstmt.setNull(4, java.sql.Types.VARCHAR);
			
			pstmt.execute();
		  }
		  return true;
		}catch(Exception ex)
		{
		  logger.info("Failed to save metrics sub " +sub.mGroup + ", "+ sub.mSubGroup +" for "+sub.group+", "+sub.host);
		  logger.log(Level.SEVERE,"Exception", ex);
		  if(conn!=null)try{conn.rollback();}catch(Exception iex){}
		}finally
		{
		  DBUtils.close(pstmt);
		  DBUtils.close(conn);
		}
		return false;
	  }
	  
	  public boolean deleteMetricsSubscription(MetricsSubscribers.Subscription sub)
	  {
		String sql2 = "delete from " + METRICS_SUBSCRIPT+ " where DBGROUP=? and HOSTNAME=? AND MGROUP=? AND MSUBGROUP=?";
			
		logger.info("Delete metrics sub " + sub.mGroup + ", " + sub.mSubGroup+ " for db "+ sub.group+", "+sub.host);
		Connection conn = null;	
		PreparedStatement pstmt = null;
		try
		{
		  conn = this.createConnection(true);
		  
		  pstmt = conn.prepareStatement(sql2);
		  pstmt.setString(1, sub.group);
		  if(sub.host != null && !sub.host.isEmpty())
				pstmt.setString(2, sub.host);
		  else
		  pstmt.setNull(2, java.sql.Types.VARCHAR);
		  pstmt.setString(3, sub.mGroup);
		    if(sub.mSubGroup != null)
		    	pstmt.setString(4,sub.mSubGroup);
		    else
				pstmt.setNull(4, java.sql.Types.VARCHAR);
		  pstmt.execute();
		  return true;
		}catch(Exception ex)
		{
		  logger.info("Failed to delete alert sub " +sub.mGroup + ", " +sub.mSubGroup +" for "+sub.group+", "+sub.host);
		  logger.log(Level.SEVERE,"Exception", ex);
		  if(conn!=null)try{conn.rollback();}catch(Exception iex){}
		}finally
		{
		  DBUtils.close(pstmt);
		  DBUtils.close(conn);
		}
		return false;
	  }
}