iotjava/iotinstaller/TableRelation.java

   1 package iotinstaller;
   2
   3 // Java libraries
   4 import java.io.*;
   5 import java.sql.*;
   6 import java.util.Map;
   7 import java.util.HashMap;
   8 import java.util.Scanner;
   9 import java.util.Properties;
  10
  11 import iotruntime.master.RuntimeOutput;
  12
  13 /** A class that extends Table/TableSet class to do table operations on IoTRelation
  14  *
  15  * @author      Rahmadi Trimananda <rahmadi.trimananda @ uci.edu>
  16  * @version     1.0
  17  * @since       2016-02-29
  18  */
  19 public final class TableRelation extends TableSet {
  20
  21         /**
  22          * TableRelation class properties
  23          */
  24         protected String strOtherTableName;
  25
  26         /**
  27          * Class constructor - for IoTRelation
  28          *
  29          * @param     strTblName        String of first table name that this Table object operates on
  30          * @param     strOthTblName     String of the other table name that this Table object operates on
  31          * @param _bVerbose                             Verboseness of runtime output
  32          */
  33         public TableRelation(String strTblName, String strOthTblName, boolean _bVerbose) {
  34
  35                 super(strTblName, _bVerbose);
  36                 strOtherTableName = strOthTblName;
  37         }
  38
  39         /**
  40          * A method to create a table for IoTRelation - equivalent of selectSetEntry()
  41          * <p>
  42          * We always base our search on the communication (IoTComm) table
  43          * This function is capable of constructing a more complex SQL query
  44          * Note: We check here that strOtherTableName is not NULL; this represents
  45          *       that this use of Table object is for IoTRelation
  46          *
  47          * @return           void
  48          */
  49         public void selectRelationEntry() {
  50
  51                 if (strOtherTableName != null) {
  52
  53                         try {
  54                                 String strCommand = "SELECT " + strTableName + ".*, "
  55                                                                                                                 + strOtherTableName + ".*, "
  56                                                                                                                 + STR_COMM_TABLE_NAME + ".ACCESS "
  57                                                                                                                 + "FROM "
  58                                                                                                                 + strTableName + ", "
  59                                                                                                                 + strOtherTableName + ", "
  60                                                                                                                 + STR_COMM_TABLE_NAME
  61                                                                                                                 + " WHERE "
  62                                                                                                                 + strTableName + ".ID="
  63                                                                                                                 + STR_COMM_TABLE_NAME + ".ID_SOURCE"
  64                                                                                                                 + " AND "
  65                                                                                                                 + strOtherTableName + ".ID="
  66                                                                                                                 + STR_COMM_TABLE_NAME + ".ID_DESTINATION";
  67                                 // Check for strWhere to construct a more complex
  68                                 if (strWhere != null) {
  69                                         strCommand = strCommand + " AND " + strWhere;
  70                                 }
  71                                 strCommand = strCommand + ";";
  72                                 RuntimeOutput.print(strCommand, bVerbose);
  73                                 rs = sqlInterface.sqlCommandQuery(strCommand);
  74                                 rsmd = rs.getMetaData();
  75                         } catch(SQLException ex) {
  76                                 System.out.println("Table: Exception: ");
  77                                 ex.printStackTrace();
  78                         }
  79                 } else {
  80                         RuntimeOutput.print("Table: The other table name is not set! Illegal use of this method!", bVerbose);
  81                 }
  82         }
  83
  84         /**
  85          * A method to create a table for IoTRelation and display just the first table
  86          * <p>
  87          * We always base our search on the communication (IoTComm) table
  88          * This function is capable of constructing a more complex SQL query
  89          * Note: We check here that strOtherTableName is not NULL; this represents
  90          *       that this use of Table object is for IoTRelation
  91          *
  92          * @return           void
  93          */
  94         public void selectRelationOnFirstTable() {
  95
  96                 if (strOtherTableName != null) {
  97
  98                         try {
  99                                 String strCommand = "SELECT " + strTableName + ".* "
 100                                                                                                                 + "FROM "
 101                                                                                                                 + strTableName + ", "
 102                                                                                                                 + strOtherTableName + ", "
 103                                                                                                                 + STR_COMM_TABLE_NAME
 104                                                                                                                 + " WHERE "
 105                                                                                                                 + strTableName + ".ID="
 106                                                                                                                 + STR_COMM_TABLE_NAME + ".ID_SOURCE"
 107                                                                                                                 + " AND "
 108                                                                                                                 + strOtherTableName + ".ID="
 109                                                                                                                 + STR_COMM_TABLE_NAME + ".ID_DESTINATION";
 110                                 // Check for strWhere to construct a more complex
 111                                 if (strWhere != null) {
 112                                         strCommand = strCommand + " AND " + strWhere;
 113                                 }
 114                                 strCommand = strCommand + ";";
 115                                 RuntimeOutput.print(strCommand, bVerbose);
 116                                 rs = sqlInterface.sqlCommandQuery(strCommand);
 117                                 rsmd = rs.getMetaData();
 118                         } catch(SQLException ex) {
 119                                 System.out.println("Table: Exception: ");
 120                                 ex.printStackTrace();
 121                         }
 122                 } else {
 123
 124                         RuntimeOutput.print("Table: The other table name is not set! Illegal use of this method!", bVerbose);
 125                 }
 126         }
 127
 128         /**
 129          * A method to create a table for IoTRelation and display just the second table
 130          * <p>
 131          * We always base our search on the communication (IoTComm) table
 132          * This function is capable of constructing a more complex SQL query
 133          * Note: We check here that strOtherTableName is not NULL; this represents
 134          *       that this use of Table object is for IoTRelation
 135          *
 136          * @return           void
 137          */
 138         public void selectRelationOnOtherTable() {
 139
 140                 if (strOtherTableName != null) {
 141                         try {
 142                                 String strCommand = "SELECT "/*+ strTableName + ".*, "*/
 143                                                                                                                 + strOtherTableName + ".* "
 144                                                                                                                 + "FROM "
 145                                                                                                                 + strTableName + ", "
 146                                                                                                                 + strOtherTableName + ", "
 147                                                                                                                 + STR_COMM_TABLE_NAME
 148                                                                                                                 + " WHERE "
 149                                                                                                                 + strTableName + ".ID="
 150                                                                                                                 + STR_COMM_TABLE_NAME + ".ID_SOURCE"
 151                                                                                                                 + " AND "
 152                                                                                                                 + strOtherTableName + ".ID="
 153                                                                                                                 + STR_COMM_TABLE_NAME + ".ID_DESTINATION";
 154                                 // Check for strWhere to construct a more complex
 155                                 if (strWhere != null) {
 156                                         strCommand = strCommand + " AND " + strWhere;
 157                                 }
 158                                 strCommand = strCommand + ";";
 159                                 RuntimeOutput.print(strCommand, bVerbose);
 160                                 rs = sqlInterface.sqlCommandQuery(strCommand);
 161                                 rsmd = rs.getMetaData();
 162                         } catch(SQLException ex) {
 163                                 System.out.println("Table: Exception: ");
 164                                 ex.printStackTrace();
 165                         }
 166                 } else {
 167                         RuntimeOutput.print("Table: The other table name is not set! Illegal use of this method!", bVerbose);
 168                 }
 169         }
 170
 171         /**
 172          * A method to set table name and select entry from a SQL query config file
 173          *
 174          * @param  strCfgFileName String config file name for device/entity
 175          */
 176         public void setTableRelationFromQueryFile(String strQueryFileName) {
 177
 178                 try {
 179                         // Parse configuration file
 180                         // Assumption here is that .config file is written with the correct syntax (need typechecking)
 181                         File file = new File(strQueryFileName);
 182                         Scanner scanFile = new Scanner(new FileReader(file));
 183                         // String for scanning the file
 184                         String strScan = "";
 185                         while (scanFile.hasNext()) {
 186                                 strScan = scanFile.next();
 187                                 // if this is for IoTSet table
 188                                 if (strScan.equals("SELECT FROM")) {
 189                                         // The next token is definitely the table name
 190                                         strScan = scanFile.next();
 191                                         this.setTableName(strScan);
 192                                 }
 193                                 // it means that this is for IoTRelation table
 194                                 if (strScan.equals("FIRST")) {
 195                                         // The next token is definitely the first table name
 196                                         strScan = scanFile.next();
 197                                         this.setTableName(strScan);
 198                                 }
 199                                 if (strScan.equals("OTHER")) {
 200                                         // The next token is definitely the other table name
 201                                         strScan = scanFile.next();
 202                                         this.setOtherTableName(strScan);
 203                                 }
 204                                 // Scan WHERE for either IoTSet or IoTRelation
 205                                 if (strScan.equals("WHERE")) {
 206                                         // The next token is definitely the WHERE statement
 207                                         strScan = "";
 208                                         String strWhere = scanFile.next();
 209                                         while (!strWhere.equals(";")) {
 210                                                 strScan = strScan + " " + strWhere;
 211                                                 strWhere = scanFile.next();
 212                                         }
 213                                         this.setWhereCondition(strScan);
 214                                 }
 215                         }
 216                 } catch (FileNotFoundException ex) {
 217                         System.out.println("Table: Exception: ");
 218                         ex.printStackTrace();
 219                 }
 220         }
 221
 222         /**
 223          * A method to set the other table name
 224          *
 225          * @param     strOthTblName  String table name that this Table object operates on
 226          * @return                   void
 227          */
 228         public void setOtherTableName(String strOthTblName) {
 229
 230                 strOtherTableName = strOthTblName;
 231
 232         }
 233
 234         /**
 235          * A method to get the other table name
 236          *
 237          * @return  String
 238          */
 239         public String getOtherTableName() {
 240
 241                 return strOtherTableName;
 242
 243         }
 244 }