/*
* Copyright (c) 2002-2003 iReasoning Inc. All Rights Reserved.
*
* This SOURCE CODE FILE, which has been provided by iReasoning Inc. as part
* of an iReasoning Software product for use ONLY by licensed users of the product,
* includes CONFIDENTIAL and PROPRIETARY information of iReasoning Inc.
*
* USE OF THIS SOFTWARE IS GOVERNED BY THE TERMS AND CONDITIONS
* OF THE LICENSE STATEMENT AND LIMITED WARRANTY FURNISHED WITH
* THE PRODUCT.
*
* IN PARTICULAR, YOU WILL INDEMNIFY AND HOLD IREASONING SOFTWARE, ITS
* RELATED COMPANIES AND ITS SUPPLIERS, HARMLESS FROM AND AGAINST ANY
* CLAIMS OR LIABILITIES ARISING OUT OF THE USE, REPRODUCTION, OR
* DISTRIBUTION OF YOUR PROGRAMS, INCLUDING ANY CLAIMS OR LIABILITIES
* ARISING OUT OF OR RESULTING FROM THE USE, MODIFICATION, OR
* DISTRIBUTION OF PROGRAMS OR FILES CREATED FROM, BASED ON, AND/OR
* DERIVED FROM THIS SOURCE CODE FILE.
*/
package agent;
import java.io.*;
import java.util.*;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.SQLException;
import java.util.Properties;
import com.ireasoning.protocol.snmp.DefaultAgentConfig;
import com.ireasoning.protocol.snmp.ProxyEntry;
import com.ireasoning.util.Logger;
/**
* Config settings are stored in the database.
* <p>
* In this reference implementation, data is stored in 8 tables:
* properties, trapsink, snmpv3trapsink, proxy, trapproxy, user, v3group, view.
* The column names are the same as the ones in the XML config file.
* The data types of all those columns are varchar for simplicity considerations.
* <p>
* Tables:<br>
* properties: only one row. A new column "id" is added for being the primary key.<br>
* trapsink: hostname and port are the primary keys.<br>
* snmpv3trapsink: hostname and port are the primary keys.<br>
* user: name is the primary key.<br>
* v3group: name is the primary key.<br>
* view: name is the primary key.<br>
*/
public class DbConfig extends DefaultAgentConfig
{
//Table Names:
public static final String TABLE_PROPERTIES = "properties";
public static final String TABLE_TRAPSINK = "trapsink";
public static final String TABLE_SNMPV3_TRAPSINK = "snmpv3trapsink";
public static final String TABLE_PROXY = "proxy";
public static final String TABLE_TRAP_PROXY = "trapproxy";
public static final String TABLE_USER = "user";
public static final String TABLE_GROUP = "v3group";
public static final String TABLE_VIEW = "view";
/** JDBC URL */
public static final String DB_URL = "jdbc:mysql:///test";//all tables are in the "test" database
/** MySql JDBC driver */
public static final String DB_DRIVER = "com.mysql.jdbc.Driver";
/**
* Connection to server
*/
private Connection conn = null;
/**
* Statement to be used.
*/
private Statement stmt = null;
/**
* Constructor. Initializes dababase connection
*/
public DbConfig () throws Exception
{
super();
init();
}
/**
* Initializes JDBC
*/
private void init() throws Exception
{
Class.forName( DB_DRIVER ).newInstance();
this.conn = DriverManager.getConnection( DB_URL );
this.stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
}
/**
* Retrieves the content of a database table.
* @param tableName database table name
* @return a map array. Each map in it contains column name/value pairs of each table row.
*/
private Map[] selectTable( String tableName )
{
ResultSet rs = null;
try
{
rs = stmt.executeQuery( "SELECT * from " + tableName );
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
ArrayList maps = new ArrayList();
while ( rs.next() )
{
Map map = new HashMap();
for ( int i = 1; i <= columnCount ; i++ )
{
String str = rs.getString( i );
if(str == null) str = "";
str = str.trim();
String colname = rsmd.getColumnName(i);
if(colname.indexOf("_") > 0)
{
colname = colname.replace('_', '.');
}
map.put(colname, str );
}
maps.add(map);
}
rs.close();
if(maps.size() == 0) return null;
Map[] ret = new Map[maps.size()];
for (int i = 0; i < ret.length ; i++)
{
ret[i] = (Map) maps.get(i);
}
return ret;
}
catch ( SQLException e )
{
Logger.error( e );
}
return null;
}
/**
* Inserts a new row
* @param tableName database table name
* @param pairs column name/value pairs of this new row
*/
private boolean insertRow(String tableName, Map pairs)
{
ResultSet rs = null;
try
{
if(pairs == null || pairs.size() == 0) return false;
String columns = "";
String values = "";
Set s = pairs.entrySet();
Iterator it = s.iterator();
boolean isFirst = true;
while(it.hasNext())
{
Map.Entry entry = (Map.Entry) it.next();
if(!isFirst)
{
columns += " , ";
values += " , ";
}
columns += entry.getKey();
values += "'" + entry.getValue() + "'";
isFirst = false;
}
//"INSERT INTO statement_test (strdata1,strdata2) values ('abcdefg', 'poi')");
int updateCount = stmt.executeUpdate( "INSERT INTO " + tableName + " " +
columns + " values (" + values + " )");
if(updateCount == 1) return true;
}
catch ( SQLException e )
{
Logger.error( e );
}
return false;
}
/**
* Removes a row
* @param pairs column name/value pairs of the row to be removed
*/
private boolean removeRow(String tableName, Map pairs)
{
ResultSet rs = null;
try
{
if(pairs == null || pairs.size() == 0) return false;
String conditions = "";
Set s = pairs.entrySet();
Iterator it = s.iterator();
boolean isFirst = true;
while(it.hasNext())
{
Map.Entry entry = (Map.Entry) it.next();
if(!isFirst)
{
conditions += " AND ";
}
conditions += entry.getKey() + "= '" + entry.getValue() + "'";
isFirst = false;
}
int updateCount = stmt.executeUpdate( "DELETE FROM " + tableName
+ " WHERE " + conditions);
if(updateCount >= 1) return true;
}
catch ( SQLException e )
{
Logger.error( e );
}
return false;
}
/**
* Destroys resources created during the test case.
*/
public void close()
{
if ( this.stmt != null )
{
try
{
this.stmt.close();
}
catch ( SQLException SQLE )
{
}
stmt = null;
}
if ( this.conn != null )
{
try
{
this.conn.close();
}
catch ( SQLException SQLE )
{
}
conn = null;
}
}
/**
* Sets element node's attribute's value.
* @param keyvals key/value pairs used to distinguish nodes with identical node names
* @param tableName name of table
* @param attributeName attribute's name
* @param attributeValue attribute's value
*/
protected boolean setAttributeValue(String tableName, Map keyvals, String attributeName, String attributeValue)
{
ResultSet rs = null;
try
{
String conditions = "";
if(keyvals != null && keyvals.size() > 0)
{
Set s = keyvals.entrySet();
Iterator it = s.iterator();
boolean isFirst = true;
while(it.hasNext())
{
Map.Entry entry = (Map.Entry) it.next();
if(!isFirst)
{
conditions += " AND ";
}
conditions += entry.getKey() + "= '" + entry.getValue() + "'";
isFirst = false;
}
}
if(attributeName.indexOf(".") > 0)
{
attributeName = attributeName.replace('.', '_');
}
String sql = "UPDATE " + tableName +
" SET " + attributeName + "='" + attributeValue + "'" ;
if(conditions.length() > 0)
{
sql += " WHERE " + conditions;
}
int updateCount = stmt.executeUpdate( sql );
if(updateCount >= 1) return true;
}
catch ( SQLException e )
{
Logger.error( e );
}
return false;
}
/**
* Returns a Map object which stores the attributes of <i>properties</i> node
*/
protected Map doGetPropertiesProps()
{
return selectTable(TABLE_PROPERTIES)[0];
}
/**
* Adds a trapSink or snmpV3TrapSink element to the config file.
* @param attributes a Map object containing the name/value of attributes, such as "verion" -- "2"
*/
protected boolean doAddTrapSink(Map attributes)
{
return insertRow(TABLE_TRAPSINK, attributes);
}
/**
* Removes a trapSink or snmpV3TrapSink element from the config file.
* @param trapsink a TrapSink or SnmpV3TrapSink object
*/
/**
* Removes a trapSink or snmpV3TrapSink element from the config file.
* @param trapSinkHostName host name or ip address of trap receiver
* @param trapSinkPort port number of trap receiver
* @param trapSinkVersion version of trapSink. 1: SNMPv1, 2: SNMPv2c, 3: SNMPv3
*/
protected boolean doRemoveTrapSink(String trapSinkHostName, int trapSinkPort, int trapSinkVersion)
{
HashMap map = new HashMap();
map.put("port", "" + trapSinkPort);
map.put("version", "" + trapSinkVersion);
return removeRow(TABLE_TRAPSINK, map);
}
/**
* Returns views' properties
* @return null if no view. Otherwise it returns a Map array of view's properties
*/
public Map[] getViews()
{
return selectTable(TABLE_VIEW);
}
/**
* Adds a view
* @param attrs a map containing the name/value pairs of view's attributes
* @return true if view is successfully added; false otherwise(because some of the attributes
* are not present)
*/
protected boolean doAddView(Map attrs)
{
return insertRow(TABLE_VIEW, attrs);
}
/**
* Removes a view. Returns true if group is successfully removed.
* And if the view is removed, all the groups associated with this view will be removed
* as well.
*/
protected boolean doRemoveView(String viewName)
{
Map map = new HashMap();
map.put("name", viewName);
return removeRow(TABLE_VIEW, map);
}
/**
* Returns groups' properties
* @return null if no group. Otherwise it returns a Map array of group's properties
*/
public Map[] getGroups()
{
return selectTable(TABLE_GROUP);
}
/**
* Adds a group, views associated with this group should already exist.
* @return true if group is successfully added; false otherwise (Usually it's because the corresponding
* view does not exist, or some of the necessary attributes are not present.)
*/
protected boolean doAddGroup(Map attrs)
{
return insertRow(TABLE_GROUP, attrs);
}
/**
* Removes a group. Returns true if group is successfully removed.
* And if group is removed, all the users in this group will be removed as well.
*/
protected boolean doRemoveGroup(String groupName)
{
Map map = new HashMap();
map.put("name", groupName);
return removeRow(TABLE_GROUP, map);
}
/**
* Adds a new proxy to the config settings
*/
protected boolean doAddProxy(ProxyEntry entry)
{
return insertRow(TABLE_PROXY, entry.getAttributes());
}
/**
* Adds a new proxy to the config settings
*/
protected boolean doRemoveProxy(ProxyEntry entry)
{
return removeRow(TABLE_GROUP, entry.getAttributes());
}
/**
* Returns users' properties
* @return null if no user. Otherwise it returns a Map array of user's properties
*/
public Map[] getUsers()
{
return selectTable(TABLE_USER);
}
/**
* Returns non-SNMPv3 trapsinks' properties
* @return null if no trapsink. Otherwise it returns a Map array of trapsink's properties
*/
protected Map[] doGetTrapSinkProps()
{
return selectTable(TABLE_TRAPSINK);
}
/**
* Returns trapsinks' properties
* @return null if no trapsink. Otherwise it returns a Map array of trapsink's properties
*/
protected Map[] doGetSnmpV3TrapSinkProps()
{
return selectTable(TABLE_SNMPV3_TRAPSINK);
}
protected Map [] doGetProxyProps()
{
return selectTable(TABLE_PROXY);
}
protected Map doGetTrapProxyProps()
{
Map [] ret = selectTable(TABLE_TRAP_PROXY);
if(ret == null || ret.length == 0) return null;
else return ret[0];
}
/**
* Adds a user. The group this new user belong to should already exist. New user is saved to config file.
* @param attrs user's attributes, such as "name", "authPassword", "privPassword", etc
* @return true if user is successfully added. false otherwise (usually it's because the corresponding
* group does not exist, or some of the attributes are not present)
*/
protected boolean doAddUser(Map attrs)
{
return insertRow(TABLE_USER, attrs);
}
/**
* Removes a user
* @return true if user is successfully removed
*/
protected boolean doRemoveUser(String userName)
{
Map map = new HashMap();
map.put("name", userName);
return removeRow(TABLE_USER, map);
}
/**
* In this class, all changes are saved immediately in the <code>setAttributeValue</code> method.
* So no need to implement this method.
*/
protected void doSave() throws IOException
{
}
/**
* Config settings are already loaded in getXXX methods. Do nothing here.
*/
protected void doReloadConfig() throws IOException
{
}
} // end of class DbConfig