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; 
    } 
  } 
}