Fixing MySQL query parsing bug for IoTRelation; adjusting MySQL queries for new inter...
[iot2.git] / iotjava / iotinstaller / TableSet.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 class to do table operations on IoTSet
14  *
15  * @author      Rahmadi Trimananda <rahmadi.trimananda @ uci.edu>
16  * @version     1.0
17  * @since       2016-02-29
18  */
19 public class TableSet extends Table {
20
21         /**
22          * TableSet class properties
23          */
24         protected String strWhere;
25
26         /**
27          * Class constructor - for IoTSet (only one table is needed)
28          *
29          * @param     strTblName  String table name that this Table object operates on
30          */
31         public TableSet(String strTblName, boolean _bVerbose) {
32
33                 super(strTblName, _bVerbose);
34         }
35
36         /**
37          * A method to set table name and select entry from a SQL query config file
38          *
39          * @param  strCfgFileName String config file name for device/entity
40          */
41         public void setTableSetFromQueryFile(String strQueryFileName, String strObjectID) {
42
43                 try {
44                         // Parse configuration file
45                         // Assumption here is that .config file is written with the correct syntax (need typechecking)
46                         File file = new File(strQueryFileName);
47                         Scanner scanFile = new Scanner(new FileReader(file));
48                         // String for scanning the file
49                         String strScan = "";
50                         while (scanFile.hasNext()) {
51                                 strScan = scanFile.next();
52                                 // if this is for IoTSet table
53                                 if (strScan.equals("SELECT FROM")) {
54                                         // The next token is definitely the table name
55                                         strScan = scanFile.next();
56                                         this.setTableName(strScan);
57                                 }
58                                 // Scan WHERE for either IoTSet or IoTRelation
59                                 if (strScan.equals("WHERE")) {
60                                         // The next token is definitely the WHERE statement
61                                         strScan = "";
62                                         String strWhere = scanFile.next();
63                                         while (!strWhere.equals(";")) {
64                                                 strScan = strScan + " " + strWhere;
65                                                 strWhere = scanFile.next();
66                                         }
67                                         RuntimeOutput.print("strScan: " + strScan, bVerbose);
68                                         
69                                         if (strObjectID != null) {
70                                                 // Object ID for IoTDeviceAddress address selection
71                                                 strScan = strScan + " AND ID='" + strObjectID + "'";
72                                         }
73                                         this.setWhereCondition(strScan);
74                                 }
75                         }
76
77                 } catch (FileNotFoundException ex) {
78
79                         System.out.println("Table: Exception: ");
80                         ex.printStackTrace();
81
82                 }
83         }
84
85         /**
86          * A method to set the String WHERE for a more complex query
87          *
88          * @param     strWhr  String WHERE for a more complex query
89          * @return            void
90          */
91         public void setWhereCondition(String strWhr) {
92
93                 strWhere = strWhr;
94
95         }
96
97         /**
98          * A method to select entries by giving more complex WHERE in SQL query for IoTSet
99          *
100          * @param  strTableName String table name to create device table
101          * @param  strWhere     String WHERE part of the query
102          * @return              void
103          */
104         public void selectSetEntry() {
105
106                 // Creating SQL command
107                 String strCommand = "SELECT * FROM " + strTableName;
108                 if (strWhere == null) {
109                         // No condition for query
110                         strCommand = strCommand + ";";
111                 } else {
112                         // Condition for query
113                         strCommand = strCommand + " WHERE " + strWhere + ";";
114                 }
115                 // Execute SQL command
116                 RuntimeOutput.print("Executing: " + strCommand, bVerbose);
117                 rs = sqlInterface.sqlCommandQuery(strCommand);
118                 try {
119                         rsmd = rs.getMetaData();
120                 } catch (SQLException ex) {
121                         System.out.println("Table: Exception: ");
122                         ex.printStackTrace();
123                 }
124         }
125
126         /**
127          * A method to get number of columns in the table
128          *
129          * @param   iIndex Row number in the ResultSet
130          * @return  integer
131          */
132         public int getNumOfCols(int iIndex) {
133
134                 int iCnt = 0;
135                 int iCols = 0;
136                 try {
137                         rs.beforeFirst();
138                         while(rs.next()) {
139                                 iCnt++;
140                                 // Break when reaching the desired location
141                                 if(iCnt > iIndex)
142                                         break;
143                         }
144                         // Get the specific class table name and table ID
145                         // e.g. ProximitySensorBrandC + PS1
146                         String strClassImplTableID = rs.getString(1);
147                         String strClassImplTableName = rs.getString(2);
148                         String strSQLCommand = "SELECT * FROM " + strClassImplTableName +
149                                                                                                                  strClassImplTableID + ";";
150                         ResultSet rsClassImplementation = sqlInterface.sqlCommandQuery(strSQLCommand);
151                         if(rsClassImplementation.next()) {
152                                 // Get the column type name
153                                 rsmd = rsClassImplementation.getMetaData();
154                                 iCols = rsmd.getColumnCount();
155                         }
156                         rs.beforeFirst();
157                 } catch (SQLException ex) {
158                         System.out.println("Table: Exception: ");
159                         ex.printStackTrace();
160                 }
161
162                 return iCols;
163         }
164
165         /**
166          * A method to get column data type
167          *
168          * @param   iCol   Column number
169          * @param   iIndex Row number in the ResultSet
170          * @return         String
171          */
172         public String getFieldType(int iCol, int iIndex) {
173
174                 String strColumnTypeName = "";
175                 int iCnt = 0;
176                 try {
177                         rs.beforeFirst();
178                         while(rs.next()) {
179                                 iCnt++;
180                                 // Break when reaching the desired location
181                                 if(iCnt > iIndex)
182                                         break;
183                         }
184                         // Get the specific class table name and table ID
185                         // e.g. ProximitySensorBrandC + PS1
186                         String strClassImplTableID = rs.getString(1);
187                         String strClassImplTableName = rs.getString(2);
188                         String strCommand = "SELECT * FROM " + strClassImplTableName +
189                                                                                                         strClassImplTableID + ";";
190                         RuntimeOutput.print(strCommand, bVerbose);
191                         ResultSet rsClassImplementation = sqlInterface.sqlCommandQuery(strCommand);
192                         // Get the column type name
193                         rsmd = rsClassImplementation.getMetaData();
194                         strColumnTypeName = rsmd.getColumnTypeName(iCol);
195                         rs.beforeFirst();
196                 } catch (SQLException ex) {
197                         System.out.println("Table: Exception: ");
198                         ex.printStackTrace();
199                 }
200
201                 return strColumnTypeName;
202         }
203
204         /**
205          * A method to return a array of String data structure that
206          * contains the list of ID field values of objects
207          *
208          * @return  String[]
209          */
210         public String[] getFieldObjectIDs() {
211
212                 String[] arrFieldObjectIDs = new String[getNumOfRows()];
213                 try {
214                         int iCnt=0;
215                         rs.beforeFirst();
216                         while (rs.next()) {
217                                 arrFieldObjectIDs[iCnt] = new String(rs.getString(1));
218                                 iCnt++;
219                         }
220                         rs.beforeFirst();
221                 } catch (SQLException ex) {
222                         System.out.println("Table: Exception: ");
223                         ex.printStackTrace();
224                 }
225                 return arrFieldObjectIDs;
226         }
227
228         /**
229          * A method to return a HashMap data structure that contains the list
230          * of device names
231          * <p>
232          * It matches the device ID in the specific table device, e.g. ProximitySensor
233          * with the name of that device/entry in the main IoTMain table, e.g.
234          * AtmelProximitySensor, GEProximitySensor, etc. These also represent the
235          * class names of these objects
236          *
237          * @return  HashMap<String, String>
238          */
239         public HashMap<String, String> getEntryTypes() {
240
241                 HashMap<String, String> hmEntryTypes = new HashMap<String, String>();
242                 try {
243                         rs.beforeFirst();
244                         while (rs.next()) {
245                                 hmEntryTypes.put(rs.getString(1), rs.getString(2));
246                         }
247                         rs.beforeFirst();
248                 } catch (SQLException ex) {
249                         System.out.println("Table: Exception: ");
250                         ex.printStackTrace();
251                 }
252                 return hmEntryTypes;
253         }
254
255         /**
256          * A method to return an array data structure representative for DB table
257          * <p>
258          * The outer array structure indexes the inner array structure that
259          * represents a single database entry.
260          *
261          * @return  String[][]
262          */
263         //public HashMap<Integer, HashMap<Integer, String>> getDBTable() {
264         public String[][] getDBTable() {
265
266                 int iCnt = 0;
267                 int iCols = 0;
268                 String[] arrTblElement;
269                 String[][] arrTbl = new String[getNumOfRows()][];
270                 try {
271                         rs.beforeFirst();
272                         while (rs.next()) {
273                                 // Get the class implementation table name from the second column
274                                 // and we compound it with the ID so that we will get a unique name
275                                 // This is to allow a case where we have more than one instance
276                                 // of a device type
277                                 // e.g. ProximitySensorImplPS1 from the table below
278                                 // +------+-----------------------+
279                                 // | ID   | TYPE                  |
280                                 // +------+-----------------------+
281                                 // | PS1  | ProximitySensorImpl   |
282                                 // | PS2  | ProximitySensorBrandC |
283                                 // | PS3  | ProximitySensorBrandD |
284                                 // +------+-----------------------+
285                                 String strClassImplTableID = rs.getString(1);
286                                 String strClassImplTableName = rs.getString(2);
287                                 // We just select everything because there is only one entry
288                                 // to store all the necessary constructor values (if any)
289                                 // If constructor is empty then it returns nothing
290                                 // e.g. ProximitySensorImplPS1
291                                 // +------+-------+
292                                 // | ZONE | POWER |
293                                 // +------+-------+
294                                 // |    0 |   100 |
295                                 // +------+-------+
296                                 String strCommand = "SELECT * FROM " + strClassImplTableName +
297                                                                                                                 strClassImplTableID + ";";
298                                 RuntimeOutput.print(strCommand, bVerbose);
299                                 ResultSet rsClassImplementation = sqlInterface.sqlCommandQuery(strCommand);
300                                 rsmd = rsClassImplementation.getMetaData();
301                                 iCols = rsmd.getColumnCount();
302                                 arrTblElement = new String[iCols];
303                                 if(rsClassImplementation.next()) {
304                                         for(int i=0; i<iCols; i++) {
305                                                 // Extract field information - columns start from 1
306                                                 // Store each field value into one table element
307                                                 arrTblElement[i] = new String(rsClassImplementation.getString(i+1));
308                                         }
309                                 }
310                                 // Insert one row into the table
311                                 arrTbl[iCnt++] = arrTblElement;
312                         }
313                         rs.beforeFirst();
314                 } catch (SQLException ex) {
315                         System.out.println("Table: Exception: ");
316                         ex.printStackTrace();
317                 }
318
319                 return arrTbl;
320         }
321
322         /**
323          * A method to close statement manually
324          */
325         public void closeStmt() {
326
327                 sqlInterface.closeStatement();
328
329         }
330
331         /**
332          * A method to close connection manually
333          */
334         public void closeConn() {
335
336                 sqlInterface.closeConnection();
337
338         }
339
340         /**
341          * A method to close ResultSet manually
342          */
343         public void closeRS() {
344
345                 try {
346                         rs.close();
347                 } catch (SQLException ex) {
348                         System.out.println("Table: Exception: ");
349                         ex.printStackTrace();
350                 }
351         }
352 }