## Support for new Object Type in Relational Database

The following steps are required to add Relational Database support for the new object type added by the user.

1. Write the new class object as a bean with get and set methods for the variables that has to go into the database.
2. Write the beaninfo for the new class object. The variables got by introspecting the bean are the only one that can be stored in the database. Also, only primitive datatype can be stored in the database. So the result of introspecting the bean should be only primitive java data types.
3. Create configuration files and add the new class object as an entry into it.
4. Generate the create schema file.
5. Generate the intermediate schema file for add, delete, update and get operations.
6. Generate the Relational Object java class from the intermediate schema file.
7. Edit the Relational Object java file, if required, to support filling up of some varibles in the user class from other tables in the RDBMS. Users can also have a complex java object (e.g. Vector) reference in thier class. To add the values in the complex object reference into the database, or to construct the object from the database with correct values for the complex object reference users have to add thier own code into the generated Relational Object java file.

### Writing the new class object

Write the code for the new object and add get and set methods for the variables which have to be stored in one relational database table. The new class should implement the interface com.adventnet.nms.store.DBInterface or extend a class which implements DBInterface. For how to write a new object class, please refer to the example LinkObject.java.

Write a BeanInfo for the new class created according to the conventions used to write a BeanInfo. If the object name is MyObject.java, then its BeanInfo object name should be MyObjectBeanInfo.java. For how to write a BeanInfo object, please refer to the LinkObjectBeanInfo.java example code.

Compile the newly written codes (object class and its corresponding BeanInfo) and put it in classpath.

### Creating the configuration file and adding entries

Go to the WebNMS/dbutil directory.

Create a configuration file for the newly created object. The configuration file should have the suffix of ".conf". Then add the fully qulaified class names of each object as entries into the configuration file (for which tables are to be created) like one given in the example below.

Example:

For the object LinkObject, the entry in the configuration file (say linkobject.conf) will be,

> com.adventnet.nms.topodb.LinkObject

####

####

### Generating output schema file

After adding the object name properly in the configuration file, run the GenerateTables application with the configuration file as the command line option.

> java com.adventnet.dbutil.GenerateTables -m filename

The filename is the one which has been created on step # 4.

Example :

> java com.adventnet.dbutil.GenerateTables -m linkobject.conf

The application automatically generates a ".conf" file and stores in the output directory. If the input conf file name is "linkobject.conf" then the generated output schema filename will be "linkobjectschema.conf".

Append the contents in the dbutil/output/filenameschema.conf file (e.g. linkobjectshema.conf) in the output directory to the conf/DatabaseSchema.conf file. This file is the used by WebNMS to create the table.

### Generating RelationalXXX Classes

Run the Prepare application to generate an intermediate output with the *.conf file as the option. The filename is the one which has been created on step # 4.

Example
java com.adventnet.dbutil.Prepare -m linkobject.conf

The application automatically updates "prepare.conf" file and stores in the output directory.

Run the GenerateClasses application to generate RelationalXXX class.

> Java com.adventnet.dbutil.GenerateClasses

The GenerateClasses application reads the prepare.conf in the output directory and creates a RelationalXXXObject class for it. For the LinkObject example, the output will be a RelationalLinkObject.java which will contain the code for preparing sql statements for the various database operation for the support of LinkObject in the relational database.

Compile the RelationalXXX.java class.

Append the contents in the dbutil/output/relationalclasses.conf to the conf/relationalclasses.conf file.

### Support for custom sql statements in the generated Relational class

Users might have to edit the generated relational class in the following scenarios:

1. When the user class has some variables which has to go into a table (which could be another user table) other than the one used for this object.
2. When the user class has some complex object (e.g. Vector) which the generated code cannot handle.

For example, in the Node.java class which is used to represent a IP network element, we have a variabl ipaddrs, of type java.util.Vector, which is used to store the list of interfaces for a particular node. When a node object is added to the relational database, this vector filed is ignored by the generated RelationalNode.java code. But when a node object is constructed from the Node table in the RDBMS, this vector has to be filled in from the information contained in the IpAddress table where all the interface objects are stores. The generated RelationalNode.java has to be edited to support this requirement. Whenever a Node object is constructed from the database, the RelationalNode's getStatementsForGet() method is invoked from where the sql statement for getting the values for the fields of the Node object is returned. Also, the getCustomStatements() method is invoked from where users can return a vector of sql queries. The Vector can contain both Sql strings or PreparedStatement objects. For each query in the vector, the fillCustomProperties() method is invoked with the ResultSet object of the query as argument. Users can manipulate the ResultSet passed and fillup the additional attributes (e.g. ipaddrs Vector in the Node object) for the object. The following code snippet illustrates the filling of ipaddrs filed and parentNet field in the Node object.

/**
* This returns a Vector of Custom sql queries. The vector can have both PrparedStatement
* Object or sql query as a String.
*/

public Vector getCustomStatements(DBInterface dbObj,String[] args)
{
Node obj = (Node)dbObj;
Vector v = super.getCustomStatements(dbObj,args);
if (preparedCustomStatement1!=null) {
try {
preparedCustomStatement1.setString(1,db2str(args[0]));
v.addElement(preparedCustomStatement1);
} catch (SQLException sqle) {
System.err.println("Exception setting preparedStatement for "+"NODE:"+sqle);
}
}
return v;
}

/**
* This method is called for every prepared statement in the vector returned by getCustomStatement() method
* Users should manipulate the ResultSet passed and fill up the custom Properties.
*/
public void fillCustomProperties(DBInterface dbObj,ResultSet rs,PreparedStatement prstmt)
{
Node obj = (Node)dbObj;
//Check the prepared statement
if (prstmt ==preparedCustomStatement1)
{
try {
String list1 = "";
String parentNet = "";
Properties p = new Properties();
//Manipulate ResultSet
while(rs.next()) {
list1 = list1+rs.getString(1)+" ";
parentNet = parentNet+rs.getString(2)+" ";
}
//Fill up the custom properties
p.put("InterfaceList",list1);
p.put("parentNet",parentNet);
( (Node)obj).setProperties(p);
} catch(Exception ex) {
System.err.println("fillCustomProperties relnode Exception"+ex);
ex.printStackTrace();
}
return;
}
else {
//Call super to see if the resultset is meant for the super class.
super.fillCustomProperties(dbObj,rs,prstmt);
}
}

/**
* The init method in which the sql statements are constructed and prepared.
*/
public void init(RelationalAPI relapi)
{
super.init(relapi);
String prepareSqlStringForGet ="select * from NODE where ( \"name\" = ? )";
preparedStatementForGet=relapi.getPreparedStatement(prepareSqlStringForGet);
String prepareSqlStringForAdd ="insert into NODE values ("
+"?,?"
+")";
preparedStatementForAdd=relapi.getPreparedStatement(prepareSqlStringForAdd);
String prepareSqlStringForUpdate ="update NODE set "
+"\"name\"" + " = ?,"
+"\"isRouter\"" + " = ?"
+" where (" + "\"name\"" + "= ?"
+")";
preparedStatementForUpdate=relapi.getPreparedStatement(prepareSqlStringForUpdate);
String prepareSqlStringForDelete ="delete from NODE "
+" where (" + "\"name\"" + "= ?"
+")";
preparedStatementForDelete=relapi.getPreparedStatement(prepareSqlStringForDelete);
//This is the custom sql query which is not generated.
String prepareSqlStringForCustom1 = "select "+
" TOPOOBJECT.\"ipAddress\" , IPADDRESS.\"parentNet\" from " +
"IPADDRESS " +" , "+ "TOPOOBJECT " +
" where ( "+
" TOPOOBJECT.\"name\" = IPADDRESS.\"name\" "+
" AND "+
" IPADDRESS.\"parentNode\" = ?"+ ")";
preparedCustomStatement1 =relapi.getPreparedStatement(prepareSqlStringForCustom1);
}

The RelationalPolledData.java illustrates the use of a new table to store some of the attributes of the PolledData object. This is useful if there is a logical sepration of attributes in the same class. In the PolledData object case, the attributes related to Threshold events are stored in a new ThresholdEvents table.

package com.adventnet.nms.store.relational;
import java.sql.*;
import java.util.*;
import com.adventnet.nms.store.*;
import com.adventnet.nms.poll.*;
class RelationalPolledData extends RelationalObject
{
PreparedStatement preparedStatementForGet;
PreparedStatement preparedStatementForGet1; //Custom get
PreparedStatement preparedStatementForAdd;
PreparedStatement preparedStatementForAdd1; //Custom Add
PreparedStatement preparedStatementForDelete;
PreparedStatement preparedStatementForDelete1; //Custom Delete
PreparedStatement preparedStatementForUpdate;
PreparedStatement preparedStatementForUpdate1; //Custom Update
public void init(RelationalAPI relapi) {
super.init(relapi);
String prepareSqlStringForGet ="select * from polleddata where ( \"name\" = ? "+
" AND \"agent\" = ? "+
" AND \"oid\" = ? "+
")";
preparedStatementForGet=relapi.getPreparedStatement(prepareSqlStringForGet);
String prepareSqlStringForGet1 ="select * from ThresholdEvents where ("+
" \"thresholdEventName\" = ? "+
")";
preparedStatementForGet1=relapi.getPreparedStatement(prepareSqlStringForGet1);
String prepareSqlStringForAdd ="insert into polleddata values ("
+"?,?,?,?,?,"
+"?,?,?,?,?,"
+"?,?,?,?,?,"
+"?,?,?,?,?,"
+"?,?,?,?"
+")";
preparedStatementForAdd=relapi.getPreparedStatement(prepareSqlStringForAdd);
String prepareSqlStringForAdd1 ="insert into ThresholdEvents values ("
+"?,?,?,?,?,"
+"?,?,?,?,?,"
+"?,?,?,?,?"
+")";
preparedStatementForAdd1=relapi.getPreparedStatement(prepareSqlStringForAdd1);
String prepareSqlStringForUpdate = "update polleddata set "
+"\"name\"" + " = ?,"
+"\"id\"" + " = ?,"
+"\"agent\"" + " = ?,"
+"\"community\"" + " = ?,"
+"\"period\"" + " = ?,"
+"\"active\"" + " = ?,"
+"\"oid\"" + " = ?,"
+"\"logDirectly\"" + " = ?,"
+"\"logFile\"" + " = ?,"
+"\"save\"" + " = ?,"
+"\"threshold\"" + " = ?,"
+"\"isMultiplePolledData\"" + " = ?,"
+"\"previousSeverity\"" + " = ?,"
+"\"instance\"" + " = ?,"
+"\"extension\"" + " = ?,"
+"\"armed\"" + " = ?,"
+"\"numeric\"" + " = ?,"
+"\"saveAbsolutes\"" + " = ?,"
+"\"timeAvg\"" + " = ?,"
+"\"filename\"" + " = ?,"
+"\"managed\"" + " = ?,"
+"\"port\"" + " = ?,"
+"\"webNMS\"" + " = ?,"
+"\"groupName\"" + " = ?"
+" where ("
+ "\"name\"" + "= ?"
+" AND "
+ "\"agent\"" + "= ?"
+" AND "
+ "\"oid\"" + "= ?"
+")";
preparedStatementForUpdate=relapi.getPreparedStatement(prepareSqlStringForUpdate);
String prepareSqlStringForUpdate1 ="update ThresholdEvents set "
+"\"thresholdEventName\"" + " = ?,"
+"\"thresholdType\"" + " = ?,"
+"\"category\"" + " = ?,"
+"\"warningThreshold\"" + " = ?,"
+"\"warningRearm\"" + " = ?,"
+"\"minorThreshold\"" + " = ?,"
+"\"minorRearm\"" + " = ?,"
+"\"majorThreshold\"" + " = ?,"
+"\"majorRearm\"" + " = ?,"
+"\"criticalThreshold\"" + " = ?,"
+"\"criticalRearm\"" + " = ?,"
+"\"triggerSeverity\"" + " = ?,"
+"\"resetSeverity\"" + " = ?,"
+"\"message\"" + " = ?,"
+"\"clrMessage\"" + " = ?"
+" where (" + "\"thresholdEventName\"" + "= ?"
+")";
preparedStatementForUpdate1=relapi.getPreparedStatement(prepareSqlStringForUpdate1);
String prepareSqlStringForDelete ="delete from polleddata "
+" where ("
+ "\"name\"" + "= ?"
+" AND "
+ "\"agent\"" + "= ?"
+" AND "
+ "\"oid\"" + "= ?"
+")";
preparedStatementForDelete=relapi.getPreparedStatement(prepareSqlStringForDelete);
String prepareSqlStringForDelete1 ="delete from ThresholdEvents "
+" where (" + "\"thresholdEventName\"" + "= ?"
+")";
preparedStatementForDelete1=relapi.getPreparedStatement(prepareSqlStringForDelete1);
}
public boolean fillProperties(DBInterface dbObj,Properties p)
{
PolledData obj = (PolledData)dbObj;
if (p == null) {
return false;
}
if ( (p.getProperty("name") == null) ||
(p.getProperty("agent") == null) ||
(p.getProperty("oid") == null) )
{
return false;
}
obj.setName(p.getProperty("name"));
obj.setAgent(p.getProperty("agent"));
obj.setOid(p.getProperty("oid"));
if (p.getProperty("id")!=null)
obj.setId(Long.parseLong(p.getProperty("id")));
obj.setProperties(p);
return true;
}
public Vector getStatementsForGet(String[] args)
{
Vector v = new Vector();
if (preparedStatementForGet!=null) {
try {
preparedStatementForGet.setString(1,args[0]);
preparedStatementForGet.setString(2,args[1]);
preparedStatementForGet.setString(3,args[2]);
v.addElement(preparedStatementForGet);
} catch (SQLException sqle) {
System.err.println("Exception setting prepared statement for polleddata "+args[0]+" "+args[1]+" "+args[2]+ ":"+sqle);
}
}
if (preparedStatementForGet1!=null) {
try {
preparedStatementForGet1.setString(1,args[0]+" "+args[1]+" "+args[2]);
v.addElement(preparedStatementForGet1);
} catch (SQLException sqle) {
System.err.println("Exception setting prepared statement for polleddata "+args[0]+" "+args[1]+" "+args[2]+ ":"+sqle);
}
}
return v;
}
public void fillCustomProperties(DBInterface dbObj,ResultSet rs,PreparedStatement prstmt)
{
return;
}
public void fillCustomProperties(DBInterface dbObj,ResultSet rs,String str)
{
return;
}
public Vector getCustomStatements(DBInterface dbObj,String[] args)
{
PolledData obj = (PolledData)dbObj;
Vector v = new Vector();
return v;
}
public Vector getStatementsForAdd(DBInterface dbObj)
{
PolledData obj = (PolledData)dbObj;
Vector v = new Vector();
Properties p = obj.getProperties();
if (preparedStatementForAdd!=null) {
try {
preparedStatementForAdd.setString(1,db2str(p.getProperty("name")) );
preparedStatementForAdd.setString(2,db2str(p.getProperty("id")) );
preparedStatementForAdd.setString(3,db2str(p.getProperty("agent")) );
preparedStatementForAdd.setString(4,db2str(p.getProperty("community")) );
preparedStatementForAdd.setInt(5,Integer.parseInt(p.getProperty("period")));
preparedStatementForAdd.setString(6,db2str(p.getProperty("active")));
preparedStatementForAdd.setString(7,db2str(p.getProperty("oid")) );
preparedStatementForAdd.setString(8,db2str(p.getProperty("logDirectly")) );
preparedStatementForAdd.setString(9,db2str(p.getProperty("logFile")) );
preparedStatementForAdd.setString(10,db2str(p.getProperty("save")) );
preparedStatementForAdd.setString(11,db2str(p.getProperty("threshold")));
preparedStatementForAdd.setString(12,db2str(p.getProperty("isMultiplePolledData")) );
preparedStatementForAdd.setInt(13,Integer.parseInt(p.getProperty("previousSeverity")));
preparedStatementForAdd.setString(14,db2str(p.getProperty("instance")));
preparedStatementForAdd.setString(15,db2str(p.getProperty("extension")) );
preparedStatementForAdd.setString(16,db2str(p.getProperty("armed")) );
preparedStatementForAdd.setString(17,db2str(p.getProperty("numeric")) );
preparedStatementForAdd.setString(18,db2str(p.getProperty("saveAbsolutes")) );
preparedStatementForAdd.setString(19,db2str(p.getProperty("timeAvg")) );
preparedStatementForAdd.setString(20,db2str(p.getProperty("filename")) );
preparedStatementForAdd.setString(21,db2str(p.getProperty("managed")) ) ;
preparedStatementForAdd.setInt(22,Integer.parseInt(p.getProperty("port")));
preparedStatementForAdd.setString(23,db2str(p.getProperty("webNMS")) );
preparedStatementForAdd.setString(24,db2str(p.getProperty("groupName")));
v.addElement(preparedStatementForAdd);
} catch (SQLException sqle) {
System.err.println("Exception setting preparedStatement for "+"PolledData:"+sqle);
sqle.printStackTrace();
}
}
if (preparedStatementForAdd1!=null) {
try {
String thresholdEventName = db2str(p.getProperty("name"))+
" "+
db2str(p.getProperty("agent")) +
" "+
db2str(p.getProperty("oid")) ;
preparedStatementForAdd1.setString(1,thresholdEventName);
preparedStatementForAdd1.setString(2,db2str(p.getProperty("thresholdType")));
preparedStatementForAdd1.setString(3,db2str(p.getProperty("category")) );
preparedStatementForAdd1.setString(4,db2str(p.getProperty("warningThreshold")) );
preparedStatementForAdd1.setString(5,db2str(p.getProperty("warningRearm")) );
preparedStatementForAdd1.setString(6,db2str(p.getProperty("minorThreshold")) );
preparedStatementForAdd1.setString(7,db2str(p.getProperty("minorRearm")) );
preparedStatementForAdd1.setString(8,db2str(p.getProperty("majorThreshold")) );
preparedStatementForAdd1.setString(9,db2str(p.getProperty("majorRearm")) );
preparedStatementForAdd1.setString(10,db2str(p.getProperty("criticalThreshold")) );
preparedStatementForAdd1.setString(11,db2str(p.getProperty("criticalRearm")) );
preparedStatementForAdd1.setInt(12,Integer.parseInt(p.getProperty("triggerSeverity")) );
preparedStatementForAdd1.setInt(13,Integer.parseInt(p.getProperty("resetSeverity")) );
preparedStatementForAdd1.setString(14,db2str(p.getProperty("message")) );
preparedStatementForAdd1.setString(15,db2str(p.getProperty("clrMessage")) ) ;
v.addElement(preparedStatementForAdd1);
} catch (SQLException sqle) {
System.err.println("Exception setting preparedStatement for "+"PolledData:"+sqle);
sqle.printStackTrace();
}
}
return v;
}
public Vector getStatementsForDelete(DBInterface dbObj)
{
PolledData obj = (PolledData)dbObj;
Vector v = new Vector();
if (preparedStatementForDelete!=null) {
try {
preparedStatementForDelete.setString(1,obj.getName());
preparedStatementForDelete.setString(2,obj.getAgent());
preparedStatementForDelete.setString(3,obj.getOid());
v.addElement(preparedStatementForDelete);
} catch (SQLException sqle) {
System.err.println("Exception setting prepared statement for polleddata :"+sqle);
}
}
if (preparedStatementForDelete1!=null) {
try {
preparedStatementForDelete1.setString(1,obj.getName()+" "+obj.getAgent()+" "+obj.getOid());
v.addElement(preparedStatementForDelete1);
} catch (SQLException sqle) {
System.err.println("Exception setting prepared statement for polleddata :"+sqle);
}
}
return v;
}
public Vector getStatementsForUpdate(DBInterface dbObj)
{
PolledData obj = (PolledData)dbObj;
Vector v = new Vector();
Properties p = obj.getProperties();
if (preparedStatementForUpdate!=null) {
try {
preparedStatementForUpdate.setString(1,db2str(p.getProperty("name")) );
preparedStatementForUpdate.setString(2,db2str(p.getProperty("id")) );
preparedStatementForUpdate.setString(3,db2str(p.getProperty("agent")) );
preparedStatementForUpdate.setString(4,db2str(p.getProperty("community")) );
preparedStatementForUpdate.setInt(5,Integer.parseInt(p.getProperty("period")));
preparedStatementForUpdate.setString(6,db2str(p.getProperty("active")));
preparedStatementForUpdate.setString(7,db2str(p.getProperty("oid")) );
preparedStatementForUpdate.setString(8,db2str(p.getProperty("logDirectly")) );
preparedStatementForUpdate.setString(9,db2str(p.getProperty("logFile")) );
preparedStatementForUpdate.setString(10,db2str(p.getProperty("save")) );
preparedStatementForUpdate.setString(11,db2str(p.getProperty("threshold")));
preparedStatementForUpdate.setString(12,db2str(p.getProperty("isMultiplePolledData")) );
preparedStatementForUpdate.setInt(13,Integer.parseInt(p.getProperty("previousSeverity")));
preparedStatementForUpdate.setString(14,db2str(p.getProperty("instance")));
preparedStatementForUpdate.setString(15,db2str(p.getProperty("extension")) );
preparedStatementForUpdate.setString(16,db2str(p.getProperty("armed")) );
preparedStatementForUpdate.setString(17,db2str(p.getProperty("numeric")) );
preparedStatementForUpdate.setString(18,db2str(p.getProperty("saveAbsolutes")) );
preparedStatementForUpdate.setString(19,db2str(p.getProperty("timeAvg")) );
preparedStatementForUpdate.setString(20,db2str(p.getProperty("filename")) );
preparedStatementForUpdate.setString(21,db2str(p.getProperty("managed")) ) ;
preparedStatementForUpdate.setInt(22,Integer.parseInt(p.getProperty("port")));
preparedStatementForUpdate.setString(23,db2str(p.getProperty("webNMS")) );
preparedStatementForUpdate.setString(24,db2str(p.getProperty("groupName")));
preparedStatementForUpdate.setString(25,db2str(p.getProperty("name")) );
preparedStatementForUpdate.setString(26,db2str(p.getProperty("agent")) );
preparedStatementForUpdate.setString(27,db2str(p.getProperty("oid")) );
v.addElement(preparedStatementForUpdate);
} catch (SQLException sqle) {
System.err.println("Exception setting preparedStatement for "+"PolledData:"+sqle);
sqle.printStackTrace();
}
}
if (preparedStatementForUpdate1!=null) {
try {
String thresholdEventName = db2str(p.getProperty("name"))+
" "+
db2str(p.getProperty("agent")) +
" "+
db2str(p.getProperty("oid")) ;
preparedStatementForUpdate1.setString(1,thresholdEventName);
preparedStatementForUpdate1.setString(2,db2str(p.getProperty("thresholdType")));
preparedStatementForUpdate1.setString(3,db2str(p.getProperty("category")) );
preparedStatementForUpdate1.setString(4,db2str(p.getProperty("warningThreshold")) );
preparedStatementForUpdate1.setString(5,db2str(p.getProperty("warningRearm")) );
preparedStatementForUpdate1.setString(6,db2str(p.getProperty("minorThreshold")) );
preparedStatementForUpdate1.setString(7,db2str(p.getProperty("minorRearm")) );
preparedStatementForUpdate1.setString(8,db2str(p.getProperty("majorThreshold")) );
preparedStatementForUpdate1.setString(9,db2str(p.getProperty("majorRearm")) );
preparedStatementForUpdate1.setString(10,db2str(p.getProperty("criticalThreshold")) );
preparedStatementForUpdate1.setString(11,db2str(p.getProperty("criticalRearm")) );
preparedStatementForUpdate1.setInt(12,Integer.parseInt(p.getProperty("triggerSeverity")) );
preparedStatementForUpdate1.setInt(13,Integer.parseInt(p.getProperty("resetSeverity")) );
preparedStatementForUpdate1.setString(14,db2str(p.getProperty("message")) );
preparedStatementForUpdate1.setString(15,db2str(p.getProperty("clrMessage")) ) ;
preparedStatementForUpdate1.setString(16,thresholdEventName);
v.addElement(preparedStatementForUpdate1);
} catch (SQLException sqle) {
System.err.println("Exception setting preparedStatement for "+"PolledData:"+sqle);
sqle.printStackTrace();
}
}
return v;
}
}