Test code for simple SELECT
//
// 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: 1210 $
// $LastChangedDate:: 2010-11-29 15:22:58#$
// ----------------------------------------
//
import com.lapetus_ltd._2009.xml.types.*;
import com.lapetus_ltd.api.TLptsMainDatabase;
import com.lapetus_ltd.api.common.*;
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.TLptsDriverType;
import junit.framework.TestCase;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Date;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.util.LinkedList;
import java.util.List;
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 : Test simple SELECT on Database.
//
// AIM : To test the mechanisms for the creation of many Connections, Statements and RowSets on two data files.
//
// It connects to files that are on the disk and attempts 200 connections, with multiple statements and row sets for each.
// Two different data sources are connected to (Access files) and processed 100 times each.
// The database files consist of small user tables.
//
// For the first connection we print out the table and column names for each table.
//
// There is only one instance of each listener type, namely Connection, Statement and RowSet.
// The listener for the successful connection creations, creates statements for every table.
// The listener for the successful statement creations, executes the created statements.
// The listener for the rowsets then processes the rows for monitoring and verification purposes.
// There is also a listener for the data output to file for later analysis (TLptsRowSetOutputListener).
//
// The output is grouped using a lock object (outputGroupingLock) as there are many threads generating output and it become is jumbled otherwise.
//
// How it all works : see the Connection Manager for a description of the work process (see com.lapetus_ltd.api.db.control.TLptsFactoryConnection in JavaDoc)
//
// The memory is monitored with every connection, with all readings being kept in the memory module class.
// A summary is shown at the end of execution.
//
public class TestDatabase extends TestCase
{
private MemoryMonitor memoryMonitor = new MemoryMonitor();
private final Object outputGroupingLock = new Object();
private final Object removeStatementLock = new Object();
private final int NO_OF_CONNECTIONS = 100;
private int connectionCounter = 0;
private int removeCounter = 0;
private int statementCounter = 0;
private int tableCounter = 0;
private int rowSetCounter = 0;
private int rowCounter = 0;
private TLptsRowSetOutputListener rowSetOutputListener = null;
private String outputFileName = null;
private ConnectionThread connectionThread;
static public void main(String[] args)
{
TestDatabase tdb = new TestDatabase();
tdb.testConnection();
}
public void testConnection()
{
try
{
// required so that the API can function correctly.
TLptsMainDatabase.init();
// show all the logs generated by the booting of the system, to see if everything is OK.
synchronized (outputGroupingLock)
{
for (TLptsLog log : TLptsLogger.getLogList(false))
System.out.println("Log: " + log.getMessage() + " : " + log.getSupportingText() + " : " + log.getExceptionMessage());
}
// register a listener for the logging system.
// all log types are reported through the logger, but we are only interested in errors.
TLptsLogger.addListener(new ILptsLogListener()
{
public void newLogGenerated(TLptsLog log)
{
if (log.getType().equals(TLptsLogger.LOG_TYPE.ERROR))
synchronized (outputGroupingLock)
{
fail("LOG ERROR :" + log.getMessage() + " : " + log.getSupportingText() + " : Exception : " + log.getExceptionMessage());
System.exit(0);
}
if (log.getType().equals(TLptsLogger.LOG_TYPE.WARNING))
synchronized (outputGroupingLock)
{
System.out.println("LOG WARNING :" + log.getMessage() + " : " + log.getSupportingText());
}
if (log.getType().equals(TLptsLogger.LOG_TYPE.MESSAGE))
synchronized (outputGroupingLock)
{
System.out.println("LOG MESSAGE :" + log.getMessage() + " : " + log.getSupportingText());
}
}
});
// outputGroupingLock is used to group the print output
synchronized (outputGroupingLock)
{
String charSets = "Available Charsets on this HOST : ";
for (String cs : TLptsCharSetLocaleUtil.getCharsetNameList())
charSets += cs + " ";
System.out.println(charSets);
}
// This listener will create statements for every new connection
TLptsFactoryConnection.addListener(new StatementCreatorListener());
// Gets the status of the statement creation and then executes the statement for resultset generation
TLptsFactoryStatement.addListener(new ResultSetGeneratorListener());
// lets listen for any new rowsets that are generated
TLptsFactoryRowSet.addListener(new RowSetProcessorListener());
// lets output the data to a file. We will output debug format for analysis
outputFileName = TLptsFileUtil.createDirectoryPath(TLptsFileUtil.getCurrentDirectory() + "test/");
outputFileName += "TestDatabase.resultset.txt";
TLptsFactoryRowSet.addListener(rowSetOutputListener = new TLptsRowSetOutputListener(outputFileName, TLptsRowSetOutputListener.OUTPUT_TYPE.DATA_OUTPUT,"UTF-8"));
// the odbc drivers are Windows only
if (TLptsSysInfoUtil.isHostWindows())
{
synchronized (outputGroupingLock)
{
System.out.println("This is a Windows HOST. Able to test ODBC");
}
connectionThread = new ConnectionThread();
connectionThread.start();
}
} finally
{
memoryMonitor.showFinalStats();
}
}
// ####################################################################################
// C O N N E C T I O N
// ####################################################################################
// create a seperate thread for creating all the connections.
// this way we can control the flow of creation of the connections, statements and rowsets.
// NOTE : this is not necessary in a normal application.
private class ConnectionThread extends Thread
{
public synchronized void releaseWait()
{
notifyAll();
}
public synchronized void run()
{
ResourceBundle bundle = ResourceBundle.getBundle("resources"); // we have stored all the database info in here
synchronized (outputGroupingLock)
{
System.out.println("******************************************************");
System.out.println("Testing connections with statements - ENGLISH - GREEK");
System.out.println("******************************************************");
}
// Open 200 connections - which is actually 2 connections that are pooled and used 100 times each
for (int x = 0; x < NO_OF_CONNECTIONS; x++)
{
// Each connection creates its own thread, so we can hold this thread while the rowsets are processed on another thread.
// We therefore synchronise on this thread object and release it from the row set listener.
connectToOdbcFile(TLptsFileUtil.getCurrentDirectory() + bundle.getString("Testing.database.access.testdb1"));
waitForResultSetToBeProcessed();
connectToOdbcFile(TLptsFileUtil.getCurrentDirectory() + bundle.getString("Testing.database.access.testdb2"));
waitForResultSetToBeProcessed();
}
synchronized (outputGroupingLock)
{
System.out.println("******************************************************");
System.out.println(" CONNECTIONS COMPLETED ...");
System.out.println("******************************************************");
}
}
// synchronising on this thread - stopping until the RowSet processor has completed.
private void waitForResultSetToBeProcessed()
{
try
{
wait();
} catch (Exception e)
{
TLptsLogger.logError("COULD NOT PUT CONNECTION THREAD INTO WAIT STATE", null);
}
}
}
// ###############################################################################################################
// #### C R E A T I O N O F O D B C C O N N E C T I O N
// ###############################################################################################################
private synchronized void connectToOdbcFile(String fileName)
{
TLptsDBConnectionType connectionType;
synchronized (outputGroupingLock)
{
System.out.println();
System.out.println("Connecting to file with ODBC : " + fileName);
System.out.println();
}
connectionType = new TLptsDBConnectionType();
connectionType.setTitle(fileName);
XLptsDriverType driverType = TLptsDriverLoader.getDriverTypeByClassName("sun.jdbc.odbc.JdbcOdbcDriver");
if (driverType != null)
connectionType.setDriverType(new TLptsDriverType(driverType));
else
synchronized (outputGroupingLock)
{
fail("Could not find driver for class : sun.jdbc.odbc.JdbcOdbcDriver");
return;
}
connectionType.setDataFileUrl(fileName);
connectionType.setConnectionString(driverType.getConnectionStringFormat());
// Note the passwords are never stored in the OPEN. They are decrypted whenever required and the credentials are thrown away after usage.
connectionType.setCredentials(TLptsCryptoUtil.defaultEncryptCredentialsRSA("", ""));
TLptsFactoryConnection.initiateConnection(connectionType); // This generates another thread and is controlled by the connection listener
}
// ##############################################################################################################
// See ExampleConnectionUsage.connectToDB for an example of how to connect to other Data Sources
// ##############################################################################################################
// ###############################################################################################################
// #### C O N N E C T I O N L I S T E N E R
// ###############################################################################################################
private class StatementCreatorListener implements ILptsFactoryConnectionListener
{
public void newConnectionProcessStarted()
{
synchronized (outputGroupingLock)
{
System.out.println("Connection process has started...");
}
}
public void newConnectionCreated(TLptsConnection connection)
{
connectionCounter++;
if (connectionCounter == 1)
{
// print out the tables and columns for the 1st connection
Map<String, List<String>> tableMap = connection.getAllTableNames();
for (String tableType : tableMap.keySet())
{
synchronized (outputGroupingLock)
{
System.out.println("Table name list for type : " + tableType);
}
for (String tableName : tableMap.get(tableType))
{
List<Integer> columnSqlTypes = connection.getColumnTypes(tableName);
List<String> columnNames = connection.getColumnNames(tableName);
synchronized (outputGroupingLock)
{
System.out.println("Column name list for table : " + tableName);
for (int i = 0; i < columnNames.size(); i++)
{
System.out.println("Column " + columnNames.get(i) + " has SQL type " + columnSqlTypes.get(i) +
"(" + TLptsFactoryStatement.getSqlTypeString(columnSqlTypes.get(i)) + ")");
}
}
}
}
}
int connections = TLptsFactoryConnection.getConnectionListSize();
synchronized (outputGroupingLock)
{
System.out.println("===================== CONNECTION SUMMARY =========================");
System.out.println("New Connection created successfully (Sequence=" + connectionCounter + "). Statements can be processed.");
System.out.println("Process Simple SQL SELECT for all columns of all tables");
System.out.println("Number of Overall Connections : " + connections);
if (connections > 2)
fail("--> Number of Connections should not be more than 2");
memoryMonitor.recordMemoryDynamics(false);
}
// we will mark the items as selected and let TLptsStatement create the SQL statement.
// Then we will execute and see the results.
// Find all the user tables
List<String> tableList = connection.getTableListForType(TLptsFactoryConnection.TABLE_TYPE_TABLES);
for (String tableName : tableList)
{
tableCounter++;
TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT);
statement.setTitle(tableName);
statement.setSqlDatabaseFormat(XLptsDBSqlDatabaseFormat.DEFAULT_ODBC);
statement.setXResultSetType(ResultSet.TYPE_FORWARD_ONLY);
statement.setXResultSetConcurrency(ResultSet.CONCUR_READ_ONLY);
XLptsDBStatementTableType table = statement.getTableItemByName(tableName);
table.setSelected(true);
statement.setSelectOnAllColumns(table.getTableName(), true);
statement.getCriteriaType().setType(XLptsDBCriteriaType.NONE);
statement.setExecutable(true);
TLptsFactoryStatement.createNewStatement(connection, statement); // spawns a seperate thread
// give some time to the rowset listener to process the data source records
while (TLptsFactoryStatement.getStatementListSize() > 1)
try
{
wait(50);
} catch (Exception e)
{
break;
}
}
}
public void newConnectionFailed(TLptsLog log)
{
synchronized (outputGroupingLock)
{
fail("New Connection failed! Reason :" + log.getMessage() + " : " + log.getSupportingText() + " : Exception : " + log.getExceptionMessage());
}
}
// when the last statement is closed on the last connection, we verify all the counters and pass it or not.
public void removedAndClosedConnection(TLptsConnection connection)
{
synchronized (outputGroupingLock)
{
System.out.println("** Connection removed from Connection Manager and closed successfully ... " + connection.getTitle());
}
if (TLptsFactoryStatement.getStatementListSize() == 0)
{
memoryMonitor.showFinalStats();
rowSetOutputListener.closeOutputFile();
if (!verifySimpleCounters())
fail("Test failed due to above-mentioned counter error.");
if (!verifyFileRows(rowCounter))
fail("Test failed due to file row counter error.");
System.exit(0);
}
}
}
// ###############################################################################################################
// #### S T A T E M E N T L I S T E N E R
// ###############################################################################################################
private class ResultSetGeneratorListener implements ILptsFactoryStatementListener
{
public void newStatementProcessStarted(TLptsConnection connection)
{
synchronized (outputGroupingLock)
{
System.out.println("Creating a new Statement for the connection : " + connection.getTitle());
}
}
public void newStatementCreated(TLptsConnection connection, TLptsStatement statement)
{
statementCounter++;
int statements = connection.getNumberOfConnectedStatements();
synchronized (outputGroupingLock)
{
System.out.println("===================== STATEMENT SUMMARY =========================");
System.out.println("Successful creation of a statement called : " + statement.getTitle());
System.out.println("Number of Statements on this Connection : " + statements);
System.out.println("SQL Statement : " + statement.getSqlStatementFormatted());
System.out.println("Generating RowSet ...");
}
TLptsFactoryRowSet.executeSelectStatement(statement, 1, 0, false); // spawns another thread.
}
public void newStatementFailed(TLptsConnection connection)
{
synchronized (outputGroupingLock)
{
System.out.println("Could not create a Statement for the connection : " + connection.getTitle());
System.out.println("The logs should be checked for an error or exception.");
}
}
public void removedAndClosedStatement(TLptsConnection connection, TLptsStatement statement)
{
removeCounter++;
synchronized (outputGroupingLock)
{
System.out.println("** Statement removed from Connection Manager and closed successfully ... " + statement.getTitle());
System.out.println("** Number of Statements left on this connection : " + connection.getNumberOfConnectedStatements());
System.out.println("** No of statements removed so far : " + removeCounter);
System.out.println("** Total Statements in the system : " + TLptsFactoryStatement.getStatementListSize());
System.out.println("** Total connections made so far : " + connectionCounter);
System.out.println("** Total Statements made so far : " + statementCounter);
System.out.println("** Total Tables opened so far : " + tableCounter);
System.out.println("** Total RowSets created so far : " + rowSetCounter);
System.out.println("** Total Rows processed so far : " + rowSetCounter);
}
if (connectionCounter >= (NO_OF_CONNECTIONS * 2))
if (TLptsFactoryStatement.getStatementList(connection).size() == 0)
{
// this causes the connection listener to verify and end the application
TLptsFactoryConnection.removeConnection(connection, false);
}
}
}
// ###############################################################################################################
// #### R O W S E T L I S T E N E R
// ###############################################################################################################
private class RowSetProcessorListener implements ILptsFactoryRowSetListener
{
public boolean processNewRowSetRows(TLptsRowSetEvent rowSetEvent)
{
rowSetCounter++;
return true;
}
public void rowEvent(final TLptsRowEvent rowEvent)
{
if (rowEvent.getEventType().equals(TLptsRowEvent.EVENT_TYPE.PROCESSING_COMPLETE))
{
// there are no related (dynamic) statements so we can remove it as it has finished.
synchronized (removeStatementLock) // no interruptions from another thread in the same block of code
{
TLptsFactoryStatement.removeStatement(rowEvent.getStatement());
if (TLptsFactoryStatement.getStatementList((TLptsConnection) rowEvent.getStatement().getConnectionWE()).size() == 0)
connectionThread.releaseWait(); // let the connection thread continue with it's connections
}
synchronized (outputGroupingLock)
{
System.out.println("#######################################");
System.out.println("Execution of one statement complete ...");
System.out.println("#######################################");
}
return;
}
if (rowEvent.getEventType().equals(TLptsRowEvent.EVENT_TYPE.MOVED_TO_NEXT_PRIMARY_RECORD) ||
rowEvent.getEventType().equals(TLptsRowEvent.EVENT_TYPE.MOVED_TO_NEXT_RECORD))
synchronized (outputGroupingLock)
{
rowCounter++;
if (rowEvent.getStatement() == null)
{
fail("New RowSet created, but statement not found.");
return;
}
}
if (rowEvent.getEventType().equals(TLptsRowEvent.EVENT_TYPE.ERROR_OCCURRED))
{
synchronized (outputGroupingLock)
{
fail("Error occurred during processing of the resultset.");
}
}
}
}
// ###############################################################################################################
// #### V E R I F I C A T I O N F U N C T I O N S
// ###############################################################################################################
// verifying the rows processes against the rows stored in the file by the external XML listener
private boolean verifyFileRows(int rows)
{
int lineCounter = 0;
char[] buffer = new char[200];
try
{
FileInputStream fos = new FileInputStream(outputFileName);
InputStreamReader inFile = new InputStreamReader(fos, "UTF-8");
int charCounter = 0;
while ((charCounter = inFile.read(buffer, 0, 100)) > 0)
for (int i = 0; i < charCounter; i++)
if (buffer[i] == '\n')
lineCounter++;
return rows == lineCounter;
} catch (Exception e)
{
return false;
}
}
// verifying simple counters for connections, statements and rowsets.
private boolean verifySimpleCounters()
{
System.out.println("############### COUNTER SUMMARY ###############");
System.out.println("Total connections made : " + connectionCounter);
System.out.println("Total Statements created : " + statementCounter);
System.out.println("Total Tables opened : " + tableCounter);
System.out.println("Total RowSets generated : " + rowSetCounter);
System.out.println("Total Rows read : " + rowCounter);
int totalExpectedConnections = (NO_OF_CONNECTIONS * 2);
if (connectionCounter != totalExpectedConnections)
{
synchronized (outputGroupingLock)
{
System.out.println("The connection counter is incorrect. Expecting : " + totalExpectedConnections + " , but got " + connectionCounter);
}
return false;
}
if (tableCounter != statementCounter)
{
synchronized (outputGroupingLock)
{
System.out.println("The statement count does not match the table count. Expecting : " + tableCounter + " , but got " + statementCounter);
}
return false;
}
if (statementCounter != rowSetCounter)
{
synchronized (outputGroupingLock)
{
System.out.println("The rowset count does not match the statement count. Expecting : " + statementCounter + " , but got " + rowSetCounter);
}
return false;
}
synchronized (outputGroupingLock)
{
System.out.println("The counters have been verified and are correct.");
}
return true;
}
// keeps the stats of memory while the connections, statements and rowsets are being processed.
// At the end of processing we show the final stats.
private class MemoryMonitor
{
private List<MemoryUnit> memoryList = new LinkedList<MemoryUnit>();
private Runtime runtime;
private MemoryMonitor()
{
runtime = Runtime.getRuntime();
}
private class MemoryUnit
{
private double total;
private double max;
private double free;
private int id;
private long time;
private MemoryUnit(boolean showMemory, int id)
{
this.id = id;
time = TLptsTimeUtil.getCurrentTime().getTime();
total = runtime.totalMemory();
max = runtime.maxMemory();
free = runtime.freeMemory();
total /= (1024 * 1024); // convert to Mb
max /= (1024 * 1024); // convert to Mb
free /= (1024 * 1024); // convert to Mb
synchronized (outputGroupingLock)
{
System.out.println("######## ");
System.out.println("######## MEMORY MONITOR POINT CREATED : ID = " + id);
System.out.println("######## ");
}
if (showMemory)
{
synchronized (outputGroupingLock)
{
System.out.println("/////////// MEMORY DYNAMICS \\\\\\\\\\\\\"");
System.out.println("Total memory in Mb is :" + total);
System.out.println("Maximum memory in Mb is :" + max);
System.out.println("Free memory in Mb is :" + free);
System.out.println("/////////// --------------- \\\\\\\\\\\\\"");
}
}
}
private void printDebugString()
{
DecimalFormat df = new DecimalFormat("0000.0000");
// 1234 1234.1234 1234.1234 1234.1234
System.out.println(String.format(" %04d " + df.format(max) + " " + df.format(total) + " " + df.format(free) + " " +
TLptsTimeUtil.getTimeString(new Date(time)), id));
}
}
public synchronized void recordMemoryDynamics(boolean showMemory)
{
memoryList.add(new MemoryUnit(showMemory, memoryList.size() + 1));
if (runtime.freeMemory() < 512 * 1024)
fail("Free Memory is less than 512 kb");
}
public synchronized void showFinalStats()
{
synchronized (outputGroupingLock)
{
System.out.println("");
System.out.println("######## MEMORY STATS PRINTOUT ######### (in Mb) ########");
System.out.println(" ID MAX TOTAL FREE Timestamp");
System.out.println(" -- --------- --------- --------- ---------");
// 1234 1234.1234 1234.1234 1234.1234
for (MemoryUnit mu : memoryList)
mu.printDebugString();
System.out.println("###########################################################");
}
}
}
}