TestDELETE.java
//
// Lapetus Ltd Java Class. Copyright (c) Lapetus Systems Ltd, 2009, 2010.
// -----------------------------------------------------------------------
// This is the intellectual property of Lapetus Systems Ltd, Artemis, Greece.
// --------------------------------------------------------------------------
// www.lapetus-ltd.com, www.lapetus.com.gr, www.lapetus.eu
// -------------------------------------------------------
// $LastChangedRevision: 1190 $
// $LastChangedDate:: 2010-11-17 13:21:35#$
// ----------------------------------------
//
import com.lapetus_ltd._2009.xml.types.*;
import com.lapetus_ltd.api.TLptsMainDatabase;
import com.lapetus_ltd.api.common.TLptsCryptoUtil;
import com.lapetus_ltd.api.common.logger.ILptsLogListener;
import com.lapetus_ltd.api.common.logger.TLptsLog;
import com.lapetus_ltd.api.common.logger.TLptsLogger;
import com.lapetus_ltd.api.db.control.*;
import com.lapetus_ltd.api.db.utils.TLptsDriverLoader;
import com.lapetus_ltd.api.db.xml.types.TLptsDBConnectionType;
import com.lapetus_ltd.api.db.xml.types.TLptsDBUpInsDelWhereType;
import com.lapetus_ltd.api.db.xml.types.TLptsDriverType;
import junit.framework.TestCase;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.Map;
import java.util.ResourceBundle;
// ######################################################################################################
// #### Test code for the dbJAPI build process. This code will stop the build process on ERROR ####
// ######################################################################################################
//
// Class Description : This is the test module for the delete functionality of dbJAPI.
//
// In this test we have the code for 4 different connections, but only two are used.
// The others are commented out for your reference.
//
// The test starts by making two connections to an MS SQL and Postgres data sources.
// The MS SQL test table is used for programmatic DELETE, while the Postgres is used for dynamic delete
// using the MS SQL to SELECT the data for deletion.
// The two deletes are processed in the rowset listener, and new statements are then generated to select
// and verify the deleted data.
//
public class TestDELETE extends TestCase
{
private String msSqlSelectStatementId;
private String postgreSqlSelectStatementId;
private String connectionMSSqlId = null;
private String connectionPostgresId = null;
LinkedList<Integer> idListPostgreSQL = new LinkedList<Integer>();
private Map<Integer, String> occupationMapPostgreSQL = new HashMap<Integer, String>();
private Map<Integer, String> genderMapPostgreSQL = new HashMap<Integer, String>();
private Map<Integer, Integer> numChildrenMapPostgreSQL = new HashMap<Integer, Integer>();
LinkedList<Integer> idListMsSQL = new LinkedList<Integer>();
private Map<Integer, String> occupationMapMsSQL = new HashMap<Integer, String>();
private Map<Integer, String> genderMapMsSQL = new HashMap<Integer, String>();
private Map<Integer, Integer> numChildrenMapMsSQL = new HashMap<Integer, Integer>();
private int deletesCompleted = 0;
static public void main(String[] args)
{
TestDELETE tdd = new TestDELETE();
tdd.testConnections();
}
public void testConnections()
{
// required so that the API can function correctly.
TLptsMainDatabase.init();
// register a listener for the logging system.
TLptsLogger.addListener(new ILptsLogListener()
{
public void newLogGenerated(TLptsLog log)
{
if (log.getType().equals(TLptsLogger.LOG_TYPE.ERROR))
{
fail("LOG ERROR :" + log.getMessage() + " : " + log.getSupportingText() + " : Exception : " + log.getExceptionMessage());
System.exit(0);
}
if (log.getType().equals(TLptsLogger.LOG_TYPE.WARNING))
System.out.println("LOG WARNING :" + log.getMessage() + " : " + log.getSupportingText());
if (log.getType().equals(TLptsLogger.LOG_TYPE.MESSAGE))
System.out.println("LOG MESSAGE :" + log.getMessage() + " : " + log.getSupportingText());
}
});
// register a listener for the connections
TLptsFactoryConnection.addListener(new ConnectionStatementListener());
// register a listener for the statements
TLptsFactoryStatement.addListener(new StatementListener());
// register a listener for the rowsets produced
TLptsFactoryRowSet.addListener(new RowSetListener());
TLptsDBConnectionType connectionType;
//Connect To PostgreSQL
connectionType = connectToDB("POSTGRESQL_DB");
if (connectionType != null)
{
connectionPostgresId = connectionType.getId();
TLptsFactoryConnection.initiateConnection(connectionType); // This generates another thread!!
}
//Connect To MS SQL
connectionType = connectToDB("MSSQL_DB");
if (connectionType != null)
{
connectionMSSqlId = connectionType.getId();
TLptsFactoryConnection.initiateConnection(connectionType); // This generates another thread!!
}
// ##############################################################################################################
// See ExampleConnectionUsage.connectToDB for an example of how to connect to other Data Sources
// ##############################################################################################################
}
// ####################################################################################
// C O N N E C T I O N
// ####################################################################################
private TLptsDBConnectionType connectToDB(String title)
{
String driverName = "", dbName = "", userName = "", password = "";
ResourceBundle bundle = ResourceBundle.getBundle("resources"); // we have stored all the database info in this bundle
TLptsDBConnectionType connectionType = new TLptsDBConnectionType();
connectionType.setTitle(title);
if (title.equals("MSSQL_DB"))
{
dbName = bundle.getString("Testing.database.mssql.testdb1");
userName = bundle.getString("Testing.database.mssql.user");
password = bundle.getString("Testing.database.mssql.password");
driverName = "com.microsoft.sqlserver.jdbc.SQLServerDataSource";
}
if (title.equals("POSTGRESQL_DB"))
{
dbName = bundle.getString("Testing.database.postgresql.testdb1");
userName = bundle.getString("Testing.database.postgresql.user");
password = bundle.getString("Testing.database.postgresql.password");
driverName = "org.postgresql.ds.PGSimpleDataSource";
}
XLptsDriverType driver = TLptsDriverLoader.getDriverTypeByClassName(driverName);
if (driver == null)
{
System.out.println("Could not find driver for class " + driverName);
return null;
}
TLptsDriverType driverType = new TLptsDriverType(driver);
// changing values that are default in the driver.loader.xml file.
// the DB is now set correctly and will filter through to the Datasource interface execution
driverType.setValueForInterfaceFunctionParameter("setDatabaseName", "Database Name", dbName);
connectionType.setDriverType(driverType);
if (userName == null || password == null)
connectionType.setCredentials(TLptsCryptoUtil.defaultEncryptCredentialsRSA(driverType.getGuestUser(),
driverType.getGuestPassword()));
else
connectionType.setCredentials(TLptsCryptoUtil.defaultEncryptCredentialsRSA(userName, password));
return connectionType;
}
// ####################################################################################
// D E L E T E F U N C T I O N S
// ####################################################################################
private void mssqlSimpleDelete()
{
//Find the Connection to delete data from
TLptsConnection connection = TLptsFactoryConnection.getConnection(connectionMSSqlId);
if (connection == null)
{
TLptsLogger.logError("Could not find the Connection for MS SQL.", null);
return;
}
//Create the statement and set the Type of Statement to DELETE.
TLptsStatement deleteStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.DELETE);
// set scroll sensitive so that compatibility is achieved across the DB types
deleteStatement.setXResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
// ResultSet Concurrency should be updatable
deleteStatement.setXResultSetConcurrency(ResultSet.CONCUR_UPDATABLE);
// set the table for selection
XLptsDBStatementTableType table = deleteStatement.getTableItemByName("testTable");
table.setSelected(true);
XLptsDBStatementColumnType column = deleteStatement.getColumnItemByName(table.getTableName(), "ID");
// We want to delete every row with an ID of 181
if (column != null)
{
// Statically provide the value of 181
TLptsDBUpInsDelWhereType whereType = new TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, "181");
//Set the operation we want as ILPtsStatement.OPERATION_*
whereType.setOperation(TLptsFactoryStatement.OPERATION_EQUAL);
// setAndConnector for AND/OR processing of the where clauses (in this case we only have one. There is therefore no need)
whereType.setAndConnector(true);
// Set the WHERE parameter for Update, Insert and Delete statements
column.setUpInsDelParameterItem(whereType);
}
// set a logical name
deleteStatement.setTitle("Delete Statement");
// print out the statement
System.out.println("SQL Statement " + deleteStatement.getTitle() + ": " + deleteStatement.getSqlStatementFormatted());
TLptsFactoryRowSet.executeDynamicDelete(deleteStatement);
}
// now we are going to delete based on data from a SELECT statement
private void postgressqlDynamicDelete()
{
TLptsConnection mssqlConnection = TLptsFactoryConnection.getConnection(connectionMSSqlId);
if (mssqlConnection == null)
{
TLptsLogger.logError("Could not find the Connection for MS SQL.", null);
return;
}
// We create a SELECT statement that only selects the ID column
// the select is on the MS SQL database and the selected data is used as CRITERIA
// to DELETE the records in the POSTGRES table below.
createMSSelectStatement(mssqlConnection, "ID");
// Find the Connection to delete data from
TLptsConnection connection = TLptsFactoryConnection.getConnection(connectionPostgresId);
TLptsStatement deleteStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.DELETE);
deleteStatement.setXResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
deleteStatement.setXResultSetConcurrency(ResultSet.CONCUR_UPDATABLE);
XLptsDBStatementTableType table = deleteStatement.getTableItemByName("testTable");
table.setSelected(true);
// all the values in the statement called ID will be used as criteria for deletion, and this means that all matching
// rows with the values in the ID column will be removed from this table.
XLptsDBStatementColumnType column = deleteStatement.getColumnItemByName(table.getTableName(), "ID");
if (column != null)
{
TLptsDBUpInsDelWhereType whereType = new TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType.LPTS_STATEMENT, "ID");
whereType.setOperation(TLptsFactoryStatement.OPERATION_EQUAL);
whereType.setAndConnector(true);
column.setUpInsDelParameterItem(whereType);
}
deleteStatement.setTitle("Delete Statement");
System.out.println("SQL Statement " + deleteStatement.getTitle() + ": " + deleteStatement.getSqlStatementFormatted());
TLptsFactoryRowSet.executeDynamicDelete(deleteStatement);
}
private void createMSSelectStatement(TLptsConnection connection, String columnName)
{
TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT);
// the ID values selected are based on a certain occupation.
statement.setSqlStatementUser("SELECT [testTable].[ID] FROM [testTable] WHERE [testTable].[Occupation] <> ?");
// we have set it by hand so use the above SQL statement
statement.setSqlStatementModified(true);
// Here are some extended characters : set this as an object as apposed to writing into the SQL string
// bundles should be used for correct conversions. Writing into the source code causes encoding issues and is not processed correctly.
ResourceBundle bundle = ResourceBundle.getBundle("resources");
statement.setObject(1, bundle.getString("Testing.delete.column1"), Types.NVARCHAR);
statement.setTitle(columnName);
System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted());
TLptsFactoryStatement.createNewStatement(connection, statement);
}
// the two functions below are used to select the data after deletion so
// that the verification functions can check that the operation was completed correctly
private void getPostgreSqlDataToVerify()
{
TLptsConnection connection = TLptsFactoryConnection.getConnection(connectionPostgresId);
if (connection == null)
{
TLptsLogger.logError("PostgreSQL Connection not found or is null", null);
return;
}
TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT);
statement.setSqlStatementUser("SELECT * FROM \"testTable\"");
statement.setSqlStatementModified(true); // will use the USER select above
statement.setTitle("Get PostgreSQL Data");
postgreSqlSelectStatementId = statement.getId();
System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted());
TLptsFactoryStatement.createNewStatement(connection, statement);
}
private void getMsSqlDataToVerify()
{
TLptsConnection connection = TLptsFactoryConnection.getConnection(connectionMSSqlId);
if (connection == null)
{
TLptsLogger.logError("MS SQL Connection not found or is null", null);
return;
}
TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT);
statement.setSqlStatementUser("SELECT * FROM [insurance_policy].[dbo].[testTable]");
statement.setSqlStatementModified(true);
statement.setTitle("Get Ms SQL Data");
msSqlSelectStatementId = statement.getId();
System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted());
TLptsFactoryStatement.createNewStatement(connection, statement);
}
// ###############################################################################################################
// #### V E R I F I C A T I O N F U N C T I O N S
// ###############################################################################################################
private void verifyPostgreSqlData(String db)
{
ResourceBundle bundle = ResourceBundle.getBundle("resources");
String occupationValue1 = bundle.getString("Testing.delete.column1");
String occupationValue2 = bundle.getString("Testing.delete.column2");
if (occupationMapPostgreSQL.isEmpty())
TLptsLogger.logError("Occupation Map for PostgreSQL is Empty", null);
if (genderMapPostgreSQL.isEmpty())
TLptsLogger.logError("Gender Map for PostgreSQL is Empty", null);
if (numChildrenMapPostgreSQL.isEmpty())
TLptsLogger.logError("NumChildren Map for PostgreSQL is Empty", null);
String occupationPostgreSQL;
String genderPostgreSQL;
Integer numChildrenPostgreSQL;
for (Integer id : idListPostgreSQL)
{
occupationPostgreSQL = occupationMapPostgreSQL.get(id);
genderPostgreSQL = genderMapPostgreSQL.get(id);
numChildrenPostgreSQL = numChildrenMapPostgreSQL.get(id);
if (occupationPostgreSQL == null)
TLptsLogger.logError("PostgreSQL occupation for id " + id + " is null", null);
else
{
if (!occupationPostgreSQL.equalsIgnoreCase(occupationValue1)) // some Greek processing, just to liven things up a bit
{
if (id == 181)
{
if (!occupationPostgreSQL.equalsIgnoreCase(occupationValue2))
TLptsLogger.logError("PostgreSQL occupation for id " + id + " is not supposed to be in this data set.", null);
} else
TLptsLogger.logError("PostgreSQL occupation for id " + id + " is not supposed to be in this data set.", null);
}
occupationMapPostgreSQL.remove(id);
}
if (genderPostgreSQL == null)
TLptsLogger.logError("PostgreSQL gender for id " + id + " is null", null);
else
genderMapPostgreSQL.remove(id);
if (numChildrenPostgreSQL == null)
TLptsLogger.logError("PostgreSQL numChildren for id " + id + " is null", null);
else
numChildrenMapPostgreSQL.remove(id);
}
if (!occupationMapPostgreSQL.isEmpty())
TLptsLogger.logError("Occupation Map for " + db + " should be empty", null);
if (!genderMapPostgreSQL.isEmpty())
TLptsLogger.logError("Gender Map for " + db + " should be empty", null);
if (!numChildrenMapPostgreSQL.isEmpty())
TLptsLogger.logError("NumChildren Map for " + db + " should be empty", null);
System.out.println("***************************************************");
System.out.println("Data verified and passed for : " + db);
System.out.println("***************************************************");
getMsSqlDataToVerify();
}
private void verifyMsSqlData(String db)
{
if (occupationMapMsSQL.isEmpty())
TLptsLogger.logError("Occupation Map for MS SQL should not be Empty", null);
if (genderMapMsSQL.isEmpty())
TLptsLogger.logError("Gender Map for MS SQL should not be Empty", null);
if (numChildrenMapMsSQL.isEmpty())
TLptsLogger.logError("NumChildren Map for MS SQL should not be Empty", null);
if (idListMsSQL.contains(181))
TLptsLogger.logError("Id should not be in list", null);
System.out.println("***************************************************");
System.out.println("Data verified and passed for : " + db);
System.out.println("***************************************************");
if (db.equals("MS SQL"))
clearTables();
}
private void clearTables()
{
TLptsConnection connection;
Statement statement;
TLptsStatement lptsStatement;
try
{
// two different ways to execute a DROP statement
connection = TLptsFactoryConnection.getConnection(connectionMSSqlId);
lptsStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.OTHER);
lptsStatement.setSqlStatementUser("DROP TABLE [insurance_policy].[dbo].[testTable]");
lptsStatement.setSqlStatementModified(true);
TLptsFactoryStatement.executeOtherStatement(lptsStatement);
connection = TLptsFactoryConnection.getConnection(connectionPostgresId);
statement = connection.createStatement();
statement.executeUpdate("DROP TABLE \"testTable\" ");
} catch (SQLException e)
{
TLptsLogger.logError("SQL Exception", e.getLocalizedMessage(), e);
}
System.out.println("Tables dropped and cleaned. All tests completed successfully...");
System.exit(0);
}
// ###############################################################################################################
// #### C O N N E C T I O N L I S T E N E R
// ###############################################################################################################
private class ConnectionStatementListener implements ILptsFactoryConnectionListener
{
public synchronized void newConnectionCreated(TLptsConnection connection)
{
System.out.println("New Connection created successfully. Statements can be processed.");
if (TLptsFactoryConnection.getConnectionListSize() == 2)
mssqlSimpleDelete();
}
public void newConnectionProcessStarted()
{
System.out.println("Connecting...");
}
public void newConnectionFailed(TLptsLog log)
{
System.out.println("New Connection Failed. The logger got this.");
}
public void removedAndClosedConnection(TLptsConnection connection)
{
System.out.println("Connection removed from Connection Manager and closed successfully ... " + connection.getTitle());
}
}
// ###############################################################################################################
// #### S T A T E M E N T L I S T E N E R
// ###############################################################################################################
private class StatementListener implements ILptsFactoryStatementListener
{
public void newStatementProcessStarted(TLptsConnection connection)
{
}
public void newStatementCreated(TLptsConnection connection, TLptsStatement statement)
{
// these are executed after the delete for the verification of the operation
if (statement.getId().equals(postgreSqlSelectStatementId) ||
statement.getId().equals(msSqlSelectStatementId))
TLptsFactoryRowSet.executeSelectStatement(statement, 1, 0, false);
}
public void newStatementFailed(TLptsConnection connection)
{
TLptsLogger.logError("Fail to Create Statement for connection " + connection.getTitle(), null);
}
public void removedAndClosedStatement(TLptsConnection connection, TLptsStatement statement)
{
}
}
// ###############################################################################################################
// #### R O W S E T L I S T E N E R
// ###############################################################################################################
private class RowSetListener implements ILptsFactoryRowSetListener
{
public void rowEvent(TLptsRowEvent rowEvent)
{
}
public boolean processNewRowSetRows(TLptsRowSetEvent rowSetEvent)
{
if (rowSetEvent.getEventType() == TLptsRowSetEvent.EVENT_TYPE.DELETE_COMPLETE)
{
// First the static delete, then the dynamic statement delete
try
{
System.out.println("Statement " + rowSetEvent.getStatement().getTitle() + " " + ((TLptsConnection) rowSetEvent.getStatement().getConnection()).getTitle() + " delete execution complete...");
System.out.println(rowSetEvent.getRows() + " rows deleted");
if (rowSetEvent.getRowSetsNotFinished().isEmpty())
System.out.println("All row sets are in sync.");
else
for (String rowSet : rowSetEvent.getRowSetsNotFinished())
System.out.println(rowSet + " is not in sync.");
if (((TLptsConnection) rowSetEvent.getStatement().getConnection()).getId().equals(connectionMSSqlId))
postgressqlDynamicDelete();
deletesCompleted++;
if (deletesCompleted == 2)
{
System.out.println("############################");
System.out.println("# Delete Process Completed #");
System.out.println("############################");
// now we can start the verification process
getPostgreSqlDataToVerify();
}
} catch (SQLException e)
{
TLptsLogger.logError("Cannot get Connection Name", e);
}
}
if (rowSetEvent.getEventType() == TLptsRowSetEvent.EVENT_TYPE.NEW_SELECT_RESULTSET && deletesCompleted == 2)
{
ResultSet rs = rowSetEvent.getRowSet();
try
{
if (rowSetEvent.getStatement().getId().equals(postgreSqlSelectStatementId))
{
// build the maps for verification
while (rs.next())
{
idListPostgreSQL.add(rs.getInt(1));
occupationMapPostgreSQL.put(rs.getInt(1), rs.getString(2));
genderMapPostgreSQL.put(rs.getInt(1), rs.getString(3));
numChildrenMapPostgreSQL.put(rs.getInt(1), rs.getInt(4));
}
verifyPostgreSqlData("PostgreSQL");
} else if (rowSetEvent.getStatement().getId().equals(msSqlSelectStatementId))
{
while (rs.next())
{
idListMsSQL.add(rs.getInt(1));
occupationMapMsSQL.put(rs.getInt(1), rs.getString(2));
genderMapMsSQL.put(rs.getInt(1), rs.getString(3));
numChildrenMapMsSQL.put(rs.getInt(1), rs.getInt(4));
}
verifyMsSqlData("MS SQL");
}
} catch (SQLException sqle)
{
TLptsLogger.logError("SQL Exception", sqle.getLocalizedMessage(), sqle);
}
}
return false; // do not let dbJAPI process the rows of the rowset
}
}
}