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 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                                                                                                                 /*+ strOtherTableName + ".*, "
 101                                                                                                                  + STR_COMM_TABLE_NAME + ".ACCESS "*/
 102                                                                                                                 + "FROM "
 103                                                                                                                 + strTableName + ", "
 104                                                                                                                 + strOtherTableName + ", "
 105                                                                                                                 + STR_COMM_TABLE_NAME
 106                                                                                                                 + " WHERE "
 107                                                                                                                 + strTableName + ".ID="
 108                                                                                                                 + STR_COMM_TABLE_NAME + ".ID_SOURCE"
 109                                                                                                                 + " AND "
 110                                                                                                                 + strOtherTableName + ".ID="
 111                                                                                                                 + STR_COMM_TABLE_NAME + ".ID_DESTINATION";
 112                                 // Check for strWhere to construct a more complex
 113                                 if (strWhere != null) {
 114                                         strCommand = strCommand + " AND " + strWhere;
 115                                 }
 116                                 strCommand = strCommand + ";";
 117                                 RuntimeOutput.print(strCommand, bVerbose);
 118                                 rs = sqlInterface.sqlCommandQuery(strCommand);
 119                                 rsmd = rs.getMetaData();
 120                         } catch(SQLException ex) {
 121                                 System.out.println("Table: Exception: ");
 122                                 ex.printStackTrace();
 123                         }
 124                 } else {
 125
 126                         RuntimeOutput.print("Table: The other table name is not set! Illegal use of this method!", bVerbose);
 127                 }
 128         }
 129
 130         /**
 131          * A method to create a table for IoTRelation and display just the second table
 132          * <p>
 133          * We always base our search on the communication (IoTComm) table
 134          * This function is capable of constructing a more complex SQL query
 135          * Note: We check here that strOtherTableName is not NULL; this represents
 136          *       that this use of Table object is for IoTRelation
 137          *
 138          * @return           void
 139          */
 140         public void selectRelationOnOtherTable() {
 141
 142                 if (strOtherTableName != null) {
 143                         try {
 144                                 String strCommand = "SELECT "/*+ strTableName + ".*, "*/
 145                                                                                                                 + strOtherTableName + ".* "
 146                                                                                                                 /*+ STR_COMM_TABLE_NAME + ".ACCESS "*/
 147                                                                                                                 + "FROM "
 148                                                                                                                 + strTableName + ", "
 149                                                                                                                 + strOtherTableName + ", "
 150                                                                                                                 + STR_COMM_TABLE_NAME
 151                                                                                                                 + " WHERE "
 152                                                                                                                 + strTableName + ".ID="
 153                                                                                                                 + STR_COMM_TABLE_NAME + ".ID_SOURCE"
 154                                                                                                                 + " AND "
 155                                                                                                                 + strOtherTableName + ".ID="
 156                                                                                                                 + STR_COMM_TABLE_NAME + ".ID_DESTINATION";
 157                                 // Check for strWhere to construct a more complex
 158                                 if (strWhere != null) {
 159                                         strCommand = strCommand + " AND " + strWhere;
 160                                 }
 161                                 strCommand = strCommand + ";";
 162                                 RuntimeOutput.print(strCommand, bVerbose);
 163                                 rs = sqlInterface.sqlCommandQuery(strCommand);
 164                                 rsmd = rs.getMetaData();
 165                         } catch(SQLException ex) {
 166                                 System.out.println("Table: Exception: ");
 167                                 ex.printStackTrace();
 168                         }
 169                 } else {
 170                         RuntimeOutput.print("Table: The other table name is not set! Illegal use of this method!", bVerbose);
 171                 }
 172         }
 173
 174         /**
 175          * A method to set table name and select entry from a SQL query config file
 176          *
 177          * @param  strCfgFileName String config file name for device/entity
 178          */
 179         public void setTableRelationFromQueryFile(String strQueryFileName) {
 180
 181                 try {
 182                         // Parse configuration file
 183                         // Assumption here is that .config file is written with the correct syntax (need typechecking)
 184                         File file = new File(strQueryFileName);
 185                         Scanner scanFile = new Scanner(new FileReader(file));
 186                         // String for scanning the file
 187                         String strScan = "";
 188                         while (scanFile.hasNext()) {
 189                                 strScan = scanFile.next();
 190                                 // if this is for IoTSet table
 191                                 if (strScan.equals("SELECT FROM")) {
 192                                         // The next token is definitely the table name
 193                                         strScan = scanFile.next();
 194                                         this.setTableName(strScan);
 195                                 }
 196                                 // it means that this is for IoTRelation table
 197                                 if (strScan.equals("FIRST")) {
 198                                         // The next token is definitely the first table name
 199                                         strScan = scanFile.next();
 200                                         this.setTableName(strScan);
 201                                 }
 202                                 if (strScan.equals("OTHER")) {
 203                                         // The next token is definitely the other table name
 204                                         strScan = scanFile.next();
 205                                         this.setOtherTableName(strScan);
 206                                 }
 207                                 // Scan WHERE for either IoTSet or IoTRelation
 208                                 if (strScan.equals("WHERE")) {
 209                                         // The next token is definitely the WHERE statement
 210                                         strScan = scanFile.next();
 211                                         this.setWhereCondition(strScan);
 212                                 }
 213                         }
 214                 } catch (FileNotFoundException ex) {
 215                         System.out.println("Table: Exception: ");
 216                         ex.printStackTrace();
 217                 }
 218         }
 219
 220         /**
 221          * A method to set the other table name
 222          *
 223          * @param     strOthTblName  String table name that this Table object operates on
 224          * @return                   void
 225          */
 226         public void setOtherTableName(String strOthTblName) {
 227
 228                 strOtherTableName = strOthTblName;
 229
 230         }
 231
 232         /**
 233          * A method to get the other table name
 234          *
 235          * @return  String
 236          */
 237         public String getOtherTableName() {
 238
 239                 return strOtherTableName;
 240
 241         }
 242 }