Fixing MySQL query parsing bug for IoTRelation; adjusting MySQL queries for new inter...
[iot2.git] / iotjava / iotinstaller / Table.java
1 package iotinstaller;
2
3 import iotinstaller.MySQLInterface;
4 import iotinstaller.TableProperty;
5 import iotruntime.master.RuntimeOutput;
6
7 // Java libraries
8 import java.io.*;
9 import java.sql.*;
10 import java.util.Map;
11 import java.util.HashMap;
12 import java.util.Scanner;
13 import java.util.Properties;
14
15 /** A class that does table related operations in a Table object
16  *
17  * @author      Rahmadi Trimananda <rahmadi.trimananda @ uci.edu>
18  * @version     1.0
19  * @since       2015-12-07
20  */
21 public class Table {
22
23         /**
24          * Table class properties
25          */
26         protected MySQLInterface sqlInterface;
27         protected String strTableName;
28         protected String strWhere;
29         protected ResultSet rs;
30         protected ResultSetMetaData rsmd;
31         protected boolean bVerbose;
32
33         /**
34          * Table class constants
35          */
36         protected final static String STR_COMM_TABLE_NAME = "IoTComm";
37         protected final static String STR_MAIN_TABLE_NAME = "IoTMain";
38
39         /**
40          * Class constructor #1
41          */
42         public Table(boolean _bVerbose) {
43
44                 sqlInterface = new MySQLInterface(_bVerbose);
45                 strTableName = null;
46                 strWhere = null;
47                 rs = null;
48                 rsmd = null;
49                 bVerbose = _bVerbose;
50         }
51
52         /**
53          * Class constructor #2 - with table name specified
54          *
55          * @param     strTblName  String table name that this Table object operates on
56          */
57         public Table(String strTblName, boolean _bVerbose) {
58
59                 try {
60                         sqlInterface = new MySQLInterface(_bVerbose);
61                         strTableName = strTblName;
62                         strWhere = null;
63                         rs = sqlInterface.sqlCommandQuery("SELECT * FROM " + strTableName + ";");
64                         rsmd = rs.getMetaData();
65                         bVerbose = _bVerbose;
66                 } catch(SQLException ex) {
67                         System.out.println("Table: Exception: ");
68                         ex.printStackTrace();
69                 }
70         }
71
72         /**
73          * A method to set table name
74          *
75          * @param     strTableName  String table name that this Table object operates on
76          * @return                  void
77          */
78         public void setTableName(String strTblName) {
79
80                 strTableName = strTblName;
81
82         }
83
84         /**
85          * A method to get table name
86          *
87          * @return  String
88          */
89         public String getTableName() {
90
91                 return strTableName;
92
93         }
94
95
96
97         /**
98          * A method to create a new table (Table object)
99          *
100          * @param  tp               array of TableProperty class to construct query
101          * @param  strUniqueField   field that is unique in this table
102          * @return                  void
103          */
104         public void createTable(TableProperty[] tp, String strUniqueField) {
105
106                 // Creating SQL command
107                 String strCommand = "CREATE TABLE " + strTableName + " (";
108                 // Iterate along the array tp to construct '<field> VARCHAR(<length>)' string
109                 for(int i=0; i<tp.length; i++) {
110                         strCommand = strCommand + tp[i].getField() +
111                                 " " + tp[i].getType() + "(" + tp[i].getLength() + ")";
112                         // Add ', ' except for the last entry in the array
113                         if (i<tp.length-1) {
114                                 strCommand = strCommand + ", ";
115                         }
116                 }
117                 strCommand = strCommand + ");";
118                 // Execute SQL command
119                 sqlInterface.sqlCommand(strCommand);
120                 // Assuming that there is always a PK column for each table
121                 // This has to be made unique
122                 if (strUniqueField != null) {
123                         sqlInterface.sqlCommand("ALTER IGNORE TABLE " + strTableName + " ADD UNIQUE(" + strUniqueField +");");
124                 }
125                 RuntimeOutput.print("Table: Creating a new entity/device table", bVerbose);
126         }
127
128         /**
129          * A method to insert a record into a table for a specific device
130          *
131          * @param  strFieldVals  array of String that contains field values of a table
132          * @return               void
133          */
134         public void insertEntry(String[] strFieldVals) {
135
136                 // Creating SQL command
137                 String strCommand = "INSERT INTO " + strTableName + " VALUES (";
138                 // Iterate along the array strFields to construct '<field>' string
139                 for(int i=0; i<strFieldVals.length; i++) {
140                         strCommand = strCommand + "'" + strFieldVals[i] + "'";
141
142                         // Add ', ' except for the last entry in the array
143                         if (i<strFieldVals.length-1) {
144                                 strCommand = strCommand + ", ";
145                         }
146                 }
147                 strCommand = strCommand + ");";
148                 // Execute SQL command
149                 sqlInterface.sqlCommand(strCommand);
150                 RuntimeOutput.print("Table: Inserting a new entry into " + strTableName + "..", bVerbose);
151         }
152
153         /**
154          * A method to delete a record into a table for a specific device
155          *
156          * @param  strWhere  String WHERE part of the query
157          * @return           void
158          */
159         public void deleteEntry(String strWhere) {
160
161                 // Creating SQL command
162                 String strCommand = "DELETE FROM " + strTableName;
163                 if (strWhere == null) {
164                         // No condition for query
165                         strCommand = strCommand + ";";
166                 } else {
167                         // Condition for query
168                         strCommand = strCommand + " WHERE " + strWhere + ";";
169                 }
170                 // Execute SQL command
171                 sqlInterface.sqlCommand(strCommand);
172                 RuntimeOutput.print("Table: Deleting entry from " + strTableName + "..", bVerbose);
173         }
174
175         /**
176          * A method to drop a table
177          *
178          * @return           void
179          */
180         public void dropTable() {
181
182                 // Creating SQL command
183                 String strCommand = "DROP TABLE " + strTableName;
184                 // Execute SQL command
185                 sqlInterface.sqlCommand(strCommand);
186                 RuntimeOutput.print("Table: Dropping table " + strTableName + "..", bVerbose);
187         }
188
189         /**
190          * A method to check table existence in the database
191          *
192          * @return           boolean
193          */
194         public boolean isTableExisting() {
195
196                 // Assume table does not exist
197                 boolean bExist = false;
198                 // Creating SQL command
199                 String strCommand = "SHOW TABLES LIKE '" + strTableName + "';";
200                 // Execute SQL command
201                 rs = sqlInterface.sqlCommandQuery(strCommand);
202                 try {
203                         if (rs != null) {
204                                 rs.beforeFirst();
205                                 if (rs.next()) {
206                                         // Table does exist
207                                         bExist = true;
208                                 }
209                                 rs.beforeFirst();
210                         }
211                 } catch (SQLException ex) {
212                         System.out.println("Table: Exception: ");
213                         ex.printStackTrace();
214                 }
215
216                 return bExist;
217         }
218
219         /**
220          * A method to return ResultSet
221          *
222          * @return           ResultSet
223          */
224         public ResultSet getResultSet() {
225
226                 return rs;
227
228         }
229
230         /**
231          * A method to check if table is empty
232          *
233          * @return           boolean
234          */
235         public boolean isTableEmpty() {
236
237                 if (rs == null) {
238                         return true;
239                 }
240                 return false;
241
242         }
243
244         /**
245          * A method to get number of rows in the table
246          *
247          * @return           integer
248          */
249         public int getNumOfRows() {
250
251                 int iRows = 0;
252                 try {
253                         rs.first();
254                         if (rs.last()) {
255                                 iRows = rs.getRow();
256                                 rs.beforeFirst();
257                         }
258                 } catch (SQLException ex) {
259                         System.out.println("Table: Exception: ");
260                         ex.printStackTrace();
261                 }
262                 return iRows;
263         }
264
265         /**
266          * A method to get number of columns in general table
267          * <p>
268          * This doesn't do 2-round lookup as it does for device driver table
269          *
270          * @return  integer
271          */
272         public int getGeneralNumOfCols() {
273
274                 int iCols = 0;
275                 try {
276                         rsmd = rs.getMetaData();
277                         iCols = rsmd.getColumnCount();
278                 } catch (SQLException ex) {
279                         System.out.println("Table: Exception: ");
280                         ex.printStackTrace();
281                 }
282                 return iCols;
283         }
284
285         /**
286          * A method to return a narray data structure representative for DB table
287          * <p>
288          * This works just like getDBTable() but for other tables in general
289          * It does not do 2-round process as it does for device driver table lookup
290          *
291          * @return  String[][]
292          */
293         public String[][] getGeneralDBTable() {
294
295                 int iCnt = 0;
296                 int iCols = getGeneralNumOfCols();
297                 String[] arrTblElement = new String[iCols];
298                 String[][] arrTbl = new String[getNumOfRows()][];
299
300                 try {
301                         rs.beforeFirst();
302                         while (rs.next()) {
303                                 arrTblElement = new String[iCols];
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(rs.getString(i+1));
308                                 }
309                                 // Insert one row into the table
310                                 arrTbl[iCnt++] = arrTblElement;
311                         }
312                         rs.beforeFirst();
313                 } catch (SQLException ex) {
314                         System.out.println("Table: Exception: ");
315                         ex.printStackTrace();
316                 }
317
318                 return arrTbl;
319         }
320
321         /**
322          * A method to close statement manually
323          */
324         public void closeStmt() {
325
326                 sqlInterface.closeStatement();
327
328         }
329
330         /**
331          * A method to close connection manually
332          */
333         public void closeConn() {
334
335                 sqlInterface.closeConnection();
336
337         }
338
339         /**
340          * A method to close ResultSet manually
341          */
342         public void closeRS() {
343
344                 try {
345                         rs.close();
346                 } catch (SQLException ex) {
347                         System.out.println("Table: Exception: ");
348                         ex.printStackTrace();
349                 }
350         }
351 }