TestCreateTables.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.XLptsDBCreateColumnConstraintParameterType;
import com.lapetus_ltd._2009.xml.types.XLptsDBCreateStatementTableGlobalLocalType;
import com.lapetus_ltd._2009.xml.types.XLptsDBTypeOfStatementType;
import com.lapetus_ltd._2009.xml.types.XLptsDriverType;
import com.lapetus_ltd.api.TLptsMainDatabase;
import com.lapetus_ltd.api.common.TLptsCryptoUtil;
import com.lapetus_ltd.api.common.TLptsFileUtil;
import com.lapetus_ltd.api.common.TLptsSysInfoUtil;
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.*;
import junit.framework.TestCase;
import java.util.ResourceBundle;
// ######################################################################################################
// #### Test code for the dbJAPI build process. This code will stop the build process on ERROR ####
// ######################################################################################################
// Class Description : Creates the tables for testing the INSERT, UPDATE and DELETE of the dbJAPI.
//
// This is the first step of many to check the CLEAR, INSERT, UPDATE and DELETE capabilities of dbJAPI.
// In this module the Tables for the above tests are created with their relative parameters.
// The last module of the complete test cycle deletes all the tables that are created in this module.
//
// Note: the create capability has many classes, with nearly every setting being another class, which needs to
// be initiated, set and then set against TLptsDBCreateStatementColumnType. In some case this may seem to be
// an overkill, but the main reason for this is the requirement for the create capability to be compatible with
// the JTree component. With the many classes, the Tree can use 'instanceof' for the various types so that it
// can effectively control all the data and settings.
//
public class TestCreateTables extends TestCase
{
private String connectionMSSqlId = null;
private String connectionPostgresId = null;
private String connectionMsAccessId = null;
private int tablesCreated = 0;
static public void main(String[] args)
{
TestCreateTables tct = new TestCreateTables();
tct.testCreation();
}
public void testCreation()
{
// 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!!
}
// the odbc drivers are Windows only
if (TLptsSysInfoUtil.isHostWindows())
{
System.out.println("This is a Windows HOST. Able to test ODBC");
connectionType = connectToDB("ACCESS_DB");
if (connectionType != null)
{
connectionMsAccessId = 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";
}
if (title.equals("ACCESS_DB"))
{
String fileName = TLptsFileUtil.getCurrentDirectory() + bundle.getString("Testing.database.access.testdb1");
connectionType.setDataFileUrl(fileName); // Access file from sample-data directory
userName = bundle.getString("Testing.database.access.user");
password = bundle.getString("Testing.database.access.password");
driverName = "sun.jdbc.odbc.JdbcOdbcDriver";
}
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
if (!title.equals("ACCESS_DB"))
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;
}
// ####################################################################################
// C R E A T E F U N C T I O N
// ####################################################################################
private void mssqlCreateTable()
{
//Find the Connection to Create table
TLptsConnection connection = TLptsFactoryConnection.getConnection(connectionMSSqlId);
if (connection == null)
{
TLptsLogger.logError("Could not find the connection for the MSSQL database.", null);
return;
}
// type of statement is CREATE
TLptsStatement createStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.CREATE);
TLptsDBCreateStatementRootType rootType = new TLptsDBCreateStatementRootType();
rootType.setTitle("root");
// table to be created
TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType();
table.setTable("testTable");
rootType.setTable(table);
// permanent table
TLptsDBCreateStatementTableTemporaryType temporary = new TLptsDBCreateStatementTableTemporaryType();
temporary.setTemporary(false);
TLptsDBCreateStatementTableGlobalLocal globalLocal = new TLptsDBCreateStatementTableGlobalLocal();
globalLocal.setGlobalLocal(XLptsDBCreateStatementTableGlobalLocalType.GLOBAL);
temporary.setGlobalLocal(globalLocal);
table.setTemporary(temporary);
// ### ID COLUMN ###
TLptsDBCreateStatementColumnType idColumn = new TLptsDBCreateStatementColumnType();
idColumn.setColumn("ID");
TLptsDBCreateStatementColumnSqlType sqlType = new TLptsDBCreateStatementColumnSqlType(idColumn);
sqlType.setSqlType(java.sql.Types.INTEGER);
idColumn.setSqlType(sqlType);
TLptsDBCreateColumnConstraintType columnConstraintType = new TLptsDBCreateColumnConstraintType();
columnConstraintType.setColumnId(idColumn.getId());
columnConstraintType.setConstraintType(XLptsDBCreateColumnConstraintParameterType.PRIMARY_KEY);
idColumn.addConstraint(columnConstraintType);
table.addColumn(idColumn);
// ### OCCUPATION COLUMN ###
TLptsDBCreateStatementColumnType occupation = new TLptsDBCreateStatementColumnType();
occupation.setColumn("Occupation");
TLptsDBCreateStatementColumnSqlType sqlTypeOccupation = new TLptsDBCreateStatementColumnSqlType(occupation);
sqlTypeOccupation.setSqlType(java.sql.Types.NVARCHAR);
occupation.setSqlType(sqlTypeOccupation);
TLptsDBCreateStatementColumnSizeType occupationSize = new TLptsDBCreateStatementColumnSizeType(occupation);
occupationSize.setSize1("50");
occupation.setSizes(occupationSize);
table.addColumn(occupation);
// ### GENDER COLUMN ###
TLptsDBCreateStatementColumnType gender = new TLptsDBCreateStatementColumnType();
gender.setColumn("Gender");
TLptsDBCreateStatementColumnSqlType sqlTypeGender = new TLptsDBCreateStatementColumnSqlType(gender);
sqlTypeGender.setSqlType(java.sql.Types.NCHAR);
gender.setSqlType(sqlTypeGender);
TLptsDBCreateStatementColumnSizeType genderSize = new TLptsDBCreateStatementColumnSizeType(gender);
genderSize.setSize1("1");
gender.setSizes(genderSize);
table.addColumn(gender);
// ### NUMCHILDREN COLUMN ###
TLptsDBCreateStatementColumnType numChildren = new TLptsDBCreateStatementColumnType();
numChildren.setColumn("NumChildren");
TLptsDBCreateStatementColumnSqlType sqlTypeNumChildren = new TLptsDBCreateStatementColumnSqlType(numChildren);
sqlTypeNumChildren.setSqlType(java.sql.Types.INTEGER);
numChildren.setSqlType(sqlTypeNumChildren);
table.addColumn(numChildren);
createStatement.setCreateRootItem(rootType);
System.out.println("SQL Statement " + createStatement.getTitle() + ": " + createStatement.getSqlStatementFormatted());
TLptsFactoryStatement.createNewStatement(connection, createStatement);
}
private void postgreSqlAndAccessCreateTable(TLptsConnection connection)
{
TLptsStatement createStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.CREATE);
TLptsDBCreateStatementRootType rootType = new TLptsDBCreateStatementRootType();
rootType.setTitle("root");
TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType();
table.setTable("testTable");
rootType.setTable(table);
// permanent table
TLptsDBCreateStatementTableTemporaryType temporary = new TLptsDBCreateStatementTableTemporaryType();
temporary.setTemporary(false);
TLptsDBCreateStatementTableGlobalLocal globalLocal = new TLptsDBCreateStatementTableGlobalLocal();
globalLocal.setGlobalLocal(XLptsDBCreateStatementTableGlobalLocalType.GLOBAL);
temporary.setGlobalLocal(globalLocal);
table.setTemporary(temporary);
// ### ID COLUMN ###
TLptsDBCreateStatementColumnType idColumn = new TLptsDBCreateStatementColumnType();
idColumn.setColumn("ID");
TLptsDBCreateStatementColumnSqlType sqlType = new TLptsDBCreateStatementColumnSqlType(idColumn);
sqlType.setSqlType(java.sql.Types.INTEGER);
idColumn.setSqlType(sqlType);
TLptsDBCreateColumnConstraintType columnConstraintType = new TLptsDBCreateColumnConstraintType();
columnConstraintType.setColumnId(idColumn.getId());
columnConstraintType.setConstraintType(XLptsDBCreateColumnConstraintParameterType.PRIMARY_KEY);
idColumn.addConstraint(columnConstraintType);
table.addColumn(idColumn);
// ### OCCUPATION COLUMN ###
TLptsDBCreateStatementColumnType occupation = new TLptsDBCreateStatementColumnType();
occupation.setColumn("Occupation");
TLptsDBCreateStatementColumnSqlType sqlTypeOccupation = new TLptsDBCreateStatementColumnSqlType(occupation);
sqlTypeOccupation.setSqlType(java.sql.Types.VARCHAR);
occupation.setSqlType(sqlTypeOccupation);
TLptsDBCreateStatementColumnSizeType occupationSize = new TLptsDBCreateStatementColumnSizeType(occupation);
occupationSize.setSize1("50");
occupation.setSizes(occupationSize);
table.addColumn(occupation);
// ### GENDER COLUMN ###
TLptsDBCreateStatementColumnType gender = new TLptsDBCreateStatementColumnType();
gender.setColumn("Gender");
TLptsDBCreateStatementColumnSqlType sqlTypeGender = new TLptsDBCreateStatementColumnSqlType(gender);
sqlTypeGender.setSqlType(java.sql.Types.CHAR);
gender.setSqlType(sqlTypeGender);
TLptsDBCreateStatementColumnSizeType genderSize = new TLptsDBCreateStatementColumnSizeType(gender);
genderSize.setSize1("1");
gender.setSizes(genderSize);
table.addColumn(gender);
// ### NUMCHILDREN COLUMN ###
TLptsDBCreateStatementColumnType numChildren = new TLptsDBCreateStatementColumnType();
numChildren.setColumn("NumChildren");
TLptsDBCreateStatementColumnSqlType sqlTypeNumChildren = new TLptsDBCreateStatementColumnSqlType(numChildren);
sqlTypeNumChildren.setSqlType(java.sql.Types.INTEGER);
numChildren.setSqlType(sqlTypeNumChildren);
table.addColumn(numChildren);
createStatement.setCreateRootItem(rootType);
System.out.println("SQL Statement " + createStatement.getTitle() + ": " + createStatement.getSqlStatementFormatted());
TLptsFactoryStatement.createNewStatement(connection, createStatement);
}
// ###############################################################################################################
// #### 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)
{
// three connections to different data sources
// so we need to check the connection ID before we create the statement
if (connection.getId().equals(connectionMSSqlId))
mssqlCreateTable();
else if (connection.getId().equals(connectionPostgresId))
postgreSqlAndAccessCreateTable(connection);
else if (connection.getId().equals(connectionMsAccessId))
postgreSqlAndAccessCreateTable(connection);
}
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)
{
if (statement.getTypeOfStatement() == XLptsDBTypeOfStatementType.CREATE)
{
TLptsFactoryStatement.executeOtherStatement(statement);
System.out.println("Table Created for connection : " + connection.getTitle());
tablesCreated++;
}
if ((tablesCreated == 3 && TLptsSysInfoUtil.isHostWindows()) ||
(tablesCreated == 2 && !TLptsSysInfoUtil.isHostWindows()))
{
System.out.println("#############################");
System.out.println("# Table Creation Successful #");
System.out.println("#############################");
System.exit(0);
}
}
public void newStatementFailed(TLptsConnection connection)
{
TLptsLogger.logError("Failed 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)
{
return false;
}
}
}