TestJoinClauses.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.*; 
import junit.framework.TestCase; 
 
import java.sql.SQLException; 
import java.util.LinkedList; 
import java.util.List; 
import java.util.ResourceBundle; 
 
 
// ###################################################################################################### 
// ####   Test code for the dbJAPI build process. This code will stop the build process on ERROR     #### 
// ###################################################################################################### 
 
 
// 
// Class Description : This class tests all the JOIN types for correct functionality. 
// 
// This module tests the JOIN types (INNER, CROSS, NATURAL) against our MySql DB. 
// There are two sets of JOINS for each kind tested. The one is written by hand and the other is generated from 
// the settings in the criteria items. The two sets should be the same. They are run and checked against the records 
// produced by each. 
// 
 
 
public class TestJoinClauses extends TestCase 
{ 
  private String connectionMySqlId = ""; 
  private String joinStatementId = ""; 
  private String crossJoinId = ""; 
  private String leftOuterJoinId = ""; 
  private String innerJoinId = ""; 
  private String naturalJoinId = ""; 
  private List<Integer> statementList = new LinkedList<Integer>(); 
  private List<XLptsDBJoinType> joinList = new LinkedList<XLptsDBJoinType>(); 
  private int joinIndex = 0; 
 
  static public void main(String[] args) 
  { 
    TestJoinClauses jop = new TestJoinClauses(); 
    jop.testJOINS(); 
  } 
 
  public void testJOINS() 
  { 
    // 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 rowsets produced 
    TLptsFactoryRowSet.addListener(new RowSetListener()); 
 
    // register a listener for the statements 
    TLptsFactoryStatement.addListener(new StatementListener()); 
 
    TLptsDBConnectionType connectionType; 
 
    initJoinList(); 
 
    //Connect To MySQL 
    connectionType = connectToDB(); 
    if (connectionType != null) 
    { 
      connectionMySqlId = connectionType.getId(); 
      TLptsFactoryConnection.initiateConnection(connectionType); // This generates another thread!! 
    } 
  } 
 
  // #################################################################################### 
  //            C O N N E C T I O N 
  // #################################################################################### 
 
  private TLptsDBConnectionType connectToDB() 
  { 
    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("MY-SQL DB"); 
 
    dbName = bundle.getString("Testing.database.mysql.testdb1"); 
    userName = bundle.getString("Testing.database.mysql.user"); 
    password = bundle.getString("Testing.database.mysql.password"); 
    driverName = "com.mysql.jdbc.jdbc2.optional.MysqlDataSource"; 
 
    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; 
  } 
 
 
  private void initJoinList() 
  { 
    joinList.add(XLptsDBJoinType.CROSS); 
    joinList.add(XLptsDBJoinType.INNER); 
    joinList.add(XLptsDBJoinType.NATURAL); 
  } 
 
 
  // #################################################################################### 
  //   CREATE THE JOIN STATEMENTS BY HAND 
  // #################################################################################### 
 
  private void createCrossJoinSQL(TLptsConnection connection) 
  { 
    TLptsStatement crossJoinStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    crossJoinStatement.setSqlStatementUser("SELECT " + 
                                           "`account`.`idAccount`, " + 
                                           "`account`.`client_id`, " + 
                                           "`account`.`type`, " + 
                                           "`account`.`balance`, " + 
                                           "`clients`.`idClients`, " + 
                                           "`clients`.`First Name`, " + 
                                           "`clients`.`Last Name`, " + 
                                           "`clients`.`Address`, " + 
                                           "`clients`.`Phone Number` " + 
                                           "FROM `account` CROSS JOIN `clients` " + 
                                           "ON `clients`.`idClients` = `account`.`client_id`"); 
 
    crossJoinStatement.setSqlStatementModified(true); 
    crossJoinStatement.setTitle("Cross Join Statement"); 
    crossJoinId = crossJoinStatement.getId(); 
 
    System.out.println("SQL Statement " + crossJoinStatement.getTitle() + ": " + crossJoinStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, crossJoinStatement); 
  } 
 
//  private void createLeftOuterJoinSQL(TLptsConnection connection) 
//  { 
//    TLptsStatement leftOuterJoin = new TLptsStatement(connection,XLptsDBTypeOfStatementType.SELECT); 
//    leftOuterJoin.setSqlStatementUser("SELECT " + 
//                                       "`account`.`idAccount`, " + 
//                                       "`account`.`client_id`, " + 
//                                       "`account`.`type`, " + 
//                                       "`account`.`balance`, " + 
//                                       "`clients`.`idClients`, " + 
//                                       "`clients`.`First Name`, " + 
//                                       "`clients`.`Last Name`, " + 
//                                       "`clients`.`Address`, " + 
//                                       "`clients`.`Phone Number` " + 
//                                       "FROM `account` LEFT OUTER JOIN `clients` " + 
//                                       "ON `clients`.`idClients` = `account`.`client_id`"); 
// 
//    leftOuterJoin.setSqlStatementModified(true); 
//    leftOuterJoin.setTitle("Left Outer Statement"); 
//    leftOuterJoinId = leftOuterJoin.getId(); 
// 
//    System.out.println("SQL Statement " + leftOuterJoin.getTitle() + ": " + leftOuterJoin.getSqlStatementFormatted()); 
//    TLptsFactoryStatement.createNewStatement(connection,leftOuterJoin); 
//  } 
 
 
  private void createInnerJoinSQL(TLptsConnection connection) 
  { 
    TLptsStatement innerJoinStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    innerJoinStatement.setSqlStatementUser("SELECT " + 
                                           "`account`.`idAccount`, " + 
                                           "`account`.`client_id`, " + 
                                           "`account`.`type`, " + 
                                           "`account`.`balance`, " + 
                                           "`clients`.`idClients`, " + 
                                           "`clients`.`First Name`, " + 
                                           "`clients`.`Last Name`, " + 
                                           "`clients`.`Address`, " + 
                                           "`clients`.`Phone Number` " + 
                                           "FROM `account` INNER JOIN `clients` " + 
                                           "ON `clients`.`idClients` = `account`.`client_id`"); 
 
    innerJoinStatement.setSqlStatementModified(true); 
    innerJoinStatement.setTitle("Implicit Statement"); 
    innerJoinId = innerJoinStatement.getId(); 
 
    System.out.println("SQL Statement " + innerJoinStatement.getTitle() + ": " + innerJoinStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, innerJoinStatement); 
  } 
 
 
  private void createNaturalJoinSQL(TLptsConnection connection) 
  { 
    TLptsStatement naturalJoinStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    naturalJoinStatement.setSqlStatementUser("SELECT " + 
                                             "`account`.`idAccount`, " + 
                                             "`account`.`client_id`, " + 
                                             "`account`.`type`, " + 
                                             "`account`.`balance`, " + 
                                             "`clients`.`idClients`, " + 
                                             "`clients`.`First Name`, " + 
                                             "`clients`.`Last Name`, " + 
                                             "`clients`.`Address`, " + 
                                             "`clients`.`Phone Number` " + 
                                             "FROM `account` NATURAL JOIN `clients`"); 
 
    naturalJoinStatement.setSqlStatementModified(true); 
    naturalJoinStatement.setTitle("Natural Statement"); 
    naturalJoinId = naturalJoinStatement.getId(); 
 
    System.out.println("SQL Statement " + naturalJoinStatement.getTitle() + ": " + naturalJoinStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, naturalJoinStatement); 
  } 
 
 
  // #################################################################################### 
  //   CREATE THE JOIN STATEMENTS WITH THE DB-JAPI INTERFACE 
  // #################################################################################### 
 
  // this JOIN takes place on the client id, which is called client_id in the account table 
  // and idClients in the the Clients table. 
 
  private void createJoinStatement(TLptsConnection connection, XLptsDBJoinType joinType) 
  { 
    TLptsStatement joinStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
 
    joinStatement.getTableItemByName("clients").setSelected(true); 
    joinStatement.setSelectOnAllColumns("clients", true); 
    joinStatement.getTableItemByName("account").setSelected(true); 
    joinStatement.setSelectOnAllColumns("account", true); 
 
    joinStatement.getCriteriaType().setType(XLptsDBCriteriaType.JOIN); 
 
    TLptsDBStatementCriteriaItemType spit = new TLptsDBStatementCriteriaItemType(); 
    spit.setJoinType(joinType); 
    if (!joinType.equals(XLptsDBJoinType.NATURAL)) 
    { 
      spit.setJoinOnUsingType(XLptsDBJoinOnUsingType.ON); 
      TLptsDBStatementJoinType spjot = new TLptsDBStatementJoinType(); 
      spjot.setFromColumn(joinStatement.getColumnItemByName("clients", "idClients")); 
      spjot.setOperation(TLptsFactoryStatement.OPERATION_EQUAL); 
      spjot.addToColumnListItem(joinStatement.getColumnItemByName("account", "client_id")); 
      spit.addJoinOnListItem(spjot); 
    } 
 
    spit.setJoinLeftTable(joinStatement.getTableItemByName("account")); 
    spit.setJoinRightTable(joinStatement.getTableItemByName("clients")); 
    ((TLptsDBStatementCriteriaType) joinStatement.getCriteriaType()).addCriteriaListItem(spit); 
 
    joinStatement.setTitle("Join"); 
    joinStatement.setExecutable(true); 
    joinStatementId = joinStatement.getId(); 
 
    System.out.println("SQL Statement " + joinStatement.getTitle() + ": " + joinStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, joinStatement); 
  } 
 
 
//  ############################################################################################################### 
//  ####             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 (connection.getId().equals(connectionMySqlId)) 
        createCrossJoinSQL(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) 
    { 
      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) 
    { 
      if (rowEvent.getStatement().getId().equals(joinStatementId)) 
      { 
        if (rowEvent.getEventType() == TLptsRowEvent.EVENT_TYPE.MOVED_TO_NEXT_RECORD) 
        { 
          if (statementList.contains(((Integer) rowEvent.getRowObjectList().get(0)))) 
            statementList.remove(((Integer) rowEvent.getRowObjectList().get(0))); 
          else 
            TLptsLogger.logError("Object is not in list for join with id: " + rowEvent.getRowObjectList().get(0), null); 
        } 
 
        if (rowEvent.getEventType() == TLptsRowEvent.EVENT_TYPE.PROCESSING_COMPLETE) 
        { 
          if (statementList.isEmpty()) 
          { 
            System.out.println("################################################"); 
            System.out.println("#Test completed successfully for " + joinList.get(joinIndex - 1) + " #"); 
            System.out.println("################################################"); 
 
//            if(leftOuterJoinId.equals("")) 
//              createLeftOuterJoinSQL(((TLptsConnection) rowEvent.getStatement().getConnectionWE())); 
//            else 
            if (innerJoinId.equals("")) 
              createInnerJoinSQL(((TLptsConnection) rowEvent.getStatement().getConnectionWE())); 
            else if (naturalJoinId.equals("")) 
              createNaturalJoinSQL(((TLptsConnection) rowEvent.getStatement().getConnectionWE())); 
            else 
              System.exit(0); 
          } 
        } 
      } 
    } 
 
    public boolean processNewRowSetRows(TLptsRowSetEvent rowSetEvent) 
    { 
      if (rowSetEvent.getStatement().getId().equals(crossJoinId) || 
          rowSetEvent.getStatement().getId().equals(leftOuterJoinId) || 
          rowSetEvent.getStatement().getId().equals(innerJoinId) || 
          rowSetEvent.getStatement().getId().equals(naturalJoinId)) 
      { 
        try 
        { 
          statementList.clear(); 
          while (rowSetEvent.getRowSet().next()) 
            statementList.add(rowSetEvent.getRowSet().getInt(1)); 
          createJoinStatement(((TLptsConnection) rowSetEvent.getStatement().getConnection()), joinList.get(joinIndex++)); 
        } catch (SQLException e) 
        { 
          TLptsLogger.logError("Sql Exception", e.getLocalizedMessage(), e); 
        } 
        return false; 
 
      } 
      return true; 
    } 
  } 
}