Source code: evt/gui/JspmEvtDbMySql.java
1 /*-----------------------------------------------------------------------------------------------------*/
2 /* */
3 /* Copyright (C) */
4 /* */
5 /* This program is free software; you can redistribute it and/or modify it under the terms of the GNU */
6 /* General Public License as published by the Free Software Foundation; either version 2 of the */
7 /* License, or (at your option) any later version. */
8 /* */
9 /* This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; */
10 /* without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR */
11 /* PURPOSE. See the GNU General Public License for more details. */
12 /* */
13 /* You should have received a copy of the GNU General Public License along with this program; if */
14 /* not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA */
15 /* 02111-1307 USA */
16 /* */
17 /*-----------------------------------------------------------------------------------------------------*/
18 /* */
19 /* $Author: strand01 $ $Revision: 1.3 $ $Date: 2001/12/20 14:23:03 $ */
20 /* */
21 /*-----------------------------------------------------------------------------------------------------*/
22 package evt.gui;
23
24 // Java classes
25 import java.io.*;
26 import java.util.*;
27 import java.sql.*;
28 import java.net.*;
29
30 // JSPM classes
31 import com.jdk.*;
32
33 /**
34 * This class defines the connection to the event management database. It
35 * includes all the methods to create/delete/update events in the JSPM event
36 * management database.
37 *
38 * @author Steve Randall (strand012001@yahoo.com)
39 * @version 0.0.8
40 * @date 31/10/2001
41 */
42 public class JspmEvtDbMySql implements JspmEvtDb
43 {
44 /**
45 * The JSPM home directory. It is taken from the environment variable
46 * JSPM_HOME, supplied with the executing batch file.
47 */
48 private static String JSPM_HOME = JspmConstants.JSPM_HOME;
49
50 /**
51 * The debug level. Only events with a severity higher than the debug
52 * level are written to the log stream.
53 */
54 private static int debugLevel = 0;
55
56 /**
57 * The log writer class
58 */
59 private JspmLogWriter logWriter = null;
60
61 /**
62 * The database connection used to connect to the SQL database
63 */
64 Connection con = null;
65
66 /**
67 * Constructor.
68 */
69 public JspmEvtDbMySql()
70 {
71 // Define the log writer
72 logWriter = new JspmLogWriter(JSPM_HOME+"/evt/log/JspmEvtDb.log", debugLevel);
73
74 // Start logging
75 logWriter.log(4, "JspmEvtDbMySql", "constructor", "JspmEvtDb starting");
76 }
77
78 /**
79 * Constructor.
80 *
81 * @param lw (JspmLogWriter) JSPM log writer.
82 */
83 public JspmEvtDbMySql(JspmLogWriter lw)
84 {
85 // Define the log writer
86 logWriter = lw;
87
88 // Start logging
89 logWriter.log(3, "JspmEvtDbMySql", "constructor", "JspmEvtDb starting");
90 }
91
92 /**
93 * Connects to the local database. The user and password will be provided by the
94 * DB login dialog.
95 */
96 public boolean connect()
97 {
98 String host = null;
99 String user = null;
100 String passwd = null;
101 String url = null;
102
103 // Get the login dialog
104 JspmEvtDbLogin JspmEvtDbLogin = new JspmEvtDbLogin( getLocalHost(), JSPM_HOME+"/evt/help/JspmEvtDb.html");
105 if(JspmEvtDbLogin.getResult()) {
106
107 host = new String(JspmEvtDbLogin.getHost());
108 user = new String(JspmEvtDbLogin.getUser());
109 passwd = new String(JspmEvtDbLogin.getPasswd());
110
111 logWriter.log( 4, "JspmEvtDbMySql", "connect", "Host: "+host+" User: "+user);
112
113 } else {
114 logWriter.log( 2, "JspmEvtDbMySql", "connect", "Connection canceled");
115 return false;
116 }
117
118 try {
119
120 // Load the driver
121 Class.forName("org.gjt.mm.mysql.Driver");
122
123 // Create a URL specifying an ODBC data source name.
124 url = "jdbc:mysql://"+host+"/jspmevt?user="+user+"="+passwd;
125 logWriter.log(4, "JspmEvtDbMySql", "constructor", "URL: "+url);
126
127 // Connect to the database at that URL.
128 con = DriverManager.getConnection(url, user, passwd);
129
130 logWriter.log(4, "JspmEvtDbMySql", "constructor", "Connected to database");
131
132 } catch (ClassNotFoundException e) {
133
134 logWriter.log(4, "JspmEvtDbMySql", "constructor", "ClassNotFoundException: "+e.getMessage());
135 System.exit(1);
136
137 } catch (SQLException e) {
138
139 logWriter.log(1, "JspmEvtDbMySql", "constructor", "Could not connect to database: "+e.getMessage());
140 System.err.println("Could not connect to database");
141 System.exit(1);
142
143 }
144 return true;
145 }
146
147 /**
148 * Connects to the local database using the specified user/passwd
149 *
150 * @param host (String) database host.
151 * @param user (String) database user.
152 * @param passwd (String) database password.
153 */
154 public boolean connect(String host, String user, String passwd)
155 {
156 try {
157
158 // Load the driver
159 Class.forName("org.gjt.mm.mysql.Driver");
160
161 // Create a URL specifying an ODBC data source name.
162 String url = "jdbc:mysql://"+host+"/jspmevt?user="+user+"="+passwd;
163 logWriter.log( 4, "JspmEvtDbMySql", "connect", "URL: "+url );
164
165 // Connect to the database at that URL.
166 con = DriverManager.getConnection( url, user, passwd );
167 logWriter.log( 4, "JspmEvtDbMySql", "connect", "Connected to database" );
168
169 } catch (ClassNotFoundException e) {
170
171 logWriter.log( 1, "JspmEvtDbMySql", "connect", "ClassNotFoundException: "+e.getMessage());
172 System.err.println( "Could not connect to database at "+host+" using login "+user );
173 System.exit(1);
174
175 } catch (SQLException e) {
176
177 logWriter.log( 1, "JspmEvtDbMySql", "connect", "Could not connect to database: "+e.getMessage());
178 System.err.println( "Could not connect to database at "+host+" using login "+user );
179 System.exit(1);
180 }
181 return true;
182 }
183
184 /**
185 * This method return the name of the local host.
186 */
187 private String getLocalHost()
188 {
189 InetAddress localHost = null;
190
191 // Get the local host. If the remote host = local host we can use every available port for the local
192 // port. The remote port will be always fixed.
193 try {
194
195 localHost = InetAddress.getLocalHost();
196
197 } catch (UnknownHostException e) {
198 logWriter.log(1, "JspmEvtDbMySql", "getLocalHost", "UnknownHostException: "+e.getMessage());
199 return "";
200 }
201
202 // Get the names for the hosts
203 return(localHost.getHostName());
204 }
205
206 /**
207 * This method returns the connection to the JSPMEVT database.
208 *
209 * @return con (Connection) database connection.
210 */
211 public Connection getConnection() { return con; }
212
213 /**
214 * This method returns the database type.
215 *
216 * @return type (int) database type.
217 */
218 public int getDbType() { return 1; }
219
220 /**
221 * Closes the connection to the JSPMEVT database.
222 */
223 public void close()
224 {
225 try {
226
227 con.commit();
228 con.close();
229
230 } catch (SQLException e) {
231 logWriter.log(4, "JspmEvtDbMySql", "sqlClose", "SQLException: "+e.getMessage());
232 }
233 }
234
235 /**
236 * This method gets a list of all event management server.
237 */
238 public Vector getServer()
239 {
240 Vector result = new Vector();
241
242 logWriter.log(4, "JspmEvtDbMySql", "getServer", "Starting");
243
244 try {
245 Statement cs = con.createStatement();
246
247 ResultSet rs = cs.executeQuery("select * from server");
248
249 while(rs.next()) {
250
251 Hashtable tmp = new Hashtable();
252 tmp.put("Name", rs.getString("name"));
253 tmp.put("Address", rs.getString("ip_address"));
254 tmp.put("Version", rs.getString("version"));
255 tmp.put("DBType", new Integer(rs.getInt("db_type")));
256 result.add(tmp);
257
258 }
259 rs.close();
260
261 } catch (SQLException e) {
262 logWriter.log(1, "JspmEvtDbMySql", "getServer", "SQLException: "+e.getMessage());
263 }
264 return result;
265 }
266
267 /**
268 * This method adds an event to the event management database.
269 */
270 public void addEvent(String source, String node, int type, String data)
271 {
272 logWriter.log(3, "JspmEvtDbMySql", "addEvent", "Starting");
273 logWriter.log(4, "JspmEvtDbMySql", "addEvent", "Starting - source: "+source+" node: "+node+" type: "+type+" data: "+data);
274
275 try {
276
277 Statement cs = con.createStatement();
278
279 int rs = cs.executeUpdate("insert into event (source, node, type, data) values ('"+source+"','"+node+"','"+type+"','"+data+"')");
280
281 } catch (SQLException e) {
282 logWriter.log(1, "JspmEvtDbMySql", "addEvent", "SQLException: "+e.getMessage());
283 }
284 logWriter.log(3, "JspmEvtDbMySql", "addEvent", "Finished");
285 }
286
287 /**
288 * This method adds an event to the event management database.
289 */
290 public void deleteEvent(int id)
291 {
292 logWriter.log(3, "JspmEvtDbMySql", "deleteEvent", "Starting: "+id);
293
294 try {
295
296 Statement cs = con.createStatement();
297
298 int rs = cs.executeUpdate("delete from event where id="+id);
299
300 } catch (SQLException e) {
301 logWriter.log(1, "JspmEvtDbMySql", "deleteEvent", "SQLException: "+e.getMessage());
302 }
303 logWriter.log(3, "JspmEvtDbMySql", "deleteEvent", "Finished: "+id);
304 }
305
306 /**
307 * This method gets a list messages which meat the spcified pattern.
308 */
309 public Vector findMessage(String data)
310 {
311 Vector result = new Vector();
312
313 logWriter.log(3, "JspmEvtDbMySql", "findMessage", "Starting");
314
315 try {
316 Statement cs = con.createStatement();
317
318 ResultSet rs = cs.executeQuery("select msgid,text from message where '"+data+"' like token");
319 while(rs.next()) {
320 JspmEvtMessage tmpMsg = new JspmEvtMessage(rs.getInt("msgid"), logWriter);
321 tmpMsg.setText(rs.getString("text"));
322 result.addElement(tmpMsg);
323 }
324 rs.close();
325 } catch (SQLException e) {
326 logWriter.log(1, "JspmEvtDbMySql", "findMessage", "SQLException: "+e.getMessage());
327 return null;
328 }
329 return result;
330 }
331
332 /**
333 * This find the action belonging to a specified message.
334 */
335 public Vector findAction(int msgid)
336 {
337 Vector result = new Vector();
338
339 logWriter.log(3, "JspmEvtDbMySql", "findAction", "Starting");
340
341 try {
342 Statement cs = con.createStatement();
343
344 ResultSet rs = cs.executeQuery("select * from action where msgid="+msgid+" order by seq_number");
345 while(rs.next()) {
346 result.addElement(new JspmEvtAction(this,
347 rs.getInt("id"), rs.getInt("action"), rs.getInt("back_color"), rs.getInt("fore_color"),
348 rs.getInt("attribute"), rs.getInt("severity"), rs.getString("text"), logWriter));
349 }
350 rs.close();
351 } catch (SQLException e) {
352 logWriter.log(1, "JspmEvtDbMySql", "findAction", "SQLException: "+e.getMessage());
353 }
354 return result;
355 }
356
357 /**
358 * This set the attributes back_color, fore_color, attr and severity.
359 */
360 public void setAttributes(int id, int bcolor, int fcolor, int attr, int sev)
361 {
362 logWriter.log(3, "JspmEvtDbMySql", "setAttribute", "Starting");
363
364 try {
365 Statement cs = con.createStatement();
366
367 int rs = cs.executeUpdate("update event set back_color="+bcolor+",fore_color="+fcolor+",attr="+attr+",severity="+sev+" where id="+id);
368
369 } catch (SQLException e) {
370 logWriter.log(1, "JspmEvtDbMySql", "setAttributes", "SQLException: "+e.getMessage());
371 }
372 }
373
374 /**
375 * This sets the hold flag for the specified event.
376 */
377 public void sendKeep(int id, boolean keep)
378 {
379 logWriter.log(3, "JspmEvtDbMySql", "sendKeep", "Starting: "+id);
380
381 try {
382 Statement cs = con.createStatement();
383
384 int rs = 0;
385 if(keep)
386 rs = cs.executeUpdate("update event set held=1 where id="+id);
387 else
388 rs = cs.executeUpdate("update event set held=0 where id="+id);
389
390 } catch (SQLException e) {
391 logWriter.log(1, "JspmEvtDbMySql", "sendKeep", "SQLException: "+e.getMessage());
392 }
393 }
394
395 /**
396 * This deletes a sendkeep message
397 */
398 public void delKeep(String token)
399 {
400 logWriter.log(3, "JspmEvtDbMySql", "delKeep", "Starting: "+token);
401
402 try {
403 Statement cs = con.createStatement();
404
405 ResultSet rs = cs.executeQuery("select id from event where data like '"+token+"'");
406 while(rs.next()) {
407
408 int id = rs.getInt("id");
409 sendKeep(id, false);
410
411 }
412 rs.close();
413
414 } catch (SQLException e) {
415 logWriter.log(1, "JspmEvtDbMySql", "delKeep", "SQLException: "+e.getMessage());
416 }
417 }
418
419 /**
420 * This sets the hold flag for the specified event.
421 */
422 public void setAck(int id, boolean ack)
423 {
424 logWriter.log(3, "JspmEvtDbMySql", "setAck", "Starting: "+id);
425
426 try {
427 Statement cs = con.createStatement();
428
429 int rs = 0;
430 if(ack)
431 rs = cs.executeUpdate("update event set ack=1 where id="+id);
432 else
433 rs = cs.executeUpdate("update event set ack=0 where id="+id);
434
435 } catch (SQLException e) {
436 logWriter.log(1, "JspmEvtDbMySql", "setAck", "SQLException: "+e.getMessage());
437 }
438 }
439
440 /**
441 * Returns the id of the last row
442 */
443 public int getLastEventId()
444 {
445 int result = 0;
446 logWriter.log(3, "JspmEvtDbMySql", "getLastId", "Starting");
447
448 try {
449 Statement cs = con.createStatement();
450
451 ResultSet rs = cs.executeQuery("select * from event");
452 while(rs.next()) {
453 result = rs.getInt("id");
454 }
455 rs.close();
456
457 } catch (SQLException e) {
458 logWriter.log(1, "JspmEvtDbMySql", "getLastId", "SQLException: "+e.getMessage());
459 }
460 return result;
461 }
462
463 /**
464 * Returns the id of the last row in the message table
465 */
466 public int getLastMessageId()
467 {
468 int result = 0;
469 logWriter.log(3, "JspmEvtDbMySql", "getLastMessageId", "Starting");
470
471 try {
472 Statement cs = con.createStatement();
473
474 ResultSet rs = cs.executeQuery("select msgid from message");
475 while(rs.next()) {
476 result = rs.getInt("msgid");
477 }
478 rs.close();
479
480 } catch (SQLException e) {
481 logWriter.log(1, "JspmEvtDbMySql", "getLastMessageId", "SQLException: "+e.getMessage());
482 }
483 return result;
484 }
485
486 /**
487 * Adds a message to the message table
488 */
489 public void addMessage(int id, String token, String text, String dsc, String group, String owner, String node, String source)
490 {
491 logWriter.log(3, "JspmEvtDbMySql", "addMessage", "Starting");
492
493 try {
494 Statement cs = con.createStatement();
495 String insert = "insert into message (msgid,token,text,description,msg_group,owner,node,source) "+
496 "values("+id+",'"+token+"','"+text+"','"+dsc+"','"+group+"','"+owner+"','"+node+"','"+source+"')";
497 int rs = cs.executeUpdate(insert);
498
499 } catch (SQLException e) {
500 logWriter.log(1, "JspmEvtDbMySql", "addMessage", "SQLException: "+e.getMessage());
501 }
502 }
503
504 /**
505 * Delete message by id
506 */
507 public int deleteMessageById(int id)
508 {
509 String delete = null;
510 logWriter.log(3, "JspmEvtDbMySql", "deleteMessageById", "Starting");
511
512 int rs = 0;
513 try {
514 Statement cs = con.createStatement();
515
516 if(id == -1)
517 delete = "delete from message";
518 else
519 delete = "delete from message where msgid="+id;
520
521 rs = cs.executeUpdate(delete);
522
523 } catch (SQLException e) {
524 logWriter.log(1, "JspmEvtDbMySql", "deleteMessageById", "SQLException: "+e.getMessage());
525 }
526 return rs;
527 }
528
529 /**
530 * Delete message by token
531 */
532 public int deleteMessageByToken(String token)
533 {
534 logWriter.log(3, "JspmEvtDbMySql", "deleteMessageByToken", "Starting");
535
536 int rs = 0;
537 String delete = null;
538 try {
539 Statement cs = con.createStatement();
540
541 if(token.equals("*"))
542 delete = "delete from message";
543 else
544 delete = "delete from message where token="+token;
545
546 rs = cs.executeUpdate(delete);
547
548 } catch (SQLException e) {
549 logWriter.log(1, "JspmEvtDbMySql", "deleteMessageByToken", "SQLException: "+e.getMessage());
550 }
551 return rs;
552 }
553
554 /**
555 * Delete message by token
556 */
557 public int deleteMessageByGroup(String group)
558 {
559 logWriter.log(3, "JspmEvtDbMySql", "deleteMessageByGroup", "Starting");
560 int rs = 0;
561 try {
562 Statement cs = con.createStatement();
563
564 rs = cs.executeUpdate("delete from message where msg_group='"+group+"'");
565
566 } catch (SQLException e) {
567 logWriter.log(1, "JspmEvtDbMySql", "deleteMessageByGroup", "SQLException: "+e.getMessage());
568 }
569 return rs;
570 }
571
572 /**
573 * Adds a action to the action table
574 */
575 public void addAction(int msgid, String group, int sequence, int fcolor, int bcolor, int attr, int sev, int act,
576 String text)
577 {
578 logWriter.log(3, "JspmEvtDbMySql", "addAction", "Starting");
579
580 try {
581 Statement cs = con.createStatement();
582 String insert = "insert into action (msgid,act_group,seq_number,fore_color,back_color,attribute,severity,action,text) "+
583 "values("+msgid+",'"+group+"',"+sequence+","+fcolor+","+bcolor+","+attr+","+sev+","+act+",'"+text+"')";
584 int rs = cs.executeUpdate(insert);
585
586 } catch (SQLException e) {
587 logWriter.log(1, "JspmEvtDbMySql", "addAction", "SQLException: "+e.getMessage());
588 }
589 }
590
591 /**
592 * Delete action by msgid
593 */
594 public int deleteActionByMsgid(int msgid)
595 {
596 logWriter.log(3, "JspmEvtDbMySql", "deleteActionByMsgid", "Starting");
597
598 int rs = 0;
599 String delete = null;
600 try {
601 Statement cs = con.createStatement();
602
603 if(msgid == -1)
604 delete = "delete from action";
605 else
606 delete = "delete from action where msgid="+msgid;
607
608 rs = cs.executeUpdate(delete);
609
610 } catch (SQLException e) {
611 logWriter.log(1, "JspmEvtDbMySql", "deleteActionByMsgid", "SQLException: "+e.getMessage());
612 }
613 return rs;
614 }
615
616 /**
617 * Delete action by group
618 */
619 public int deleteActionByGroup(String group)
620 {
621 logWriter.log(3, "JspmEvtDbMySql", "deleteActionByGroup", "Starting");
622
623 int rs = 0;
624 try {
625 Statement cs = con.createStatement();
626
627 rs = cs.executeUpdate("delete from action where act_group='"+group+"'");
628
629 } catch (SQLException e) {
630 logWriter.log(1, "JspmEvtDbMySql", "deleteActionByGroup", "SQLException: "+e.getMessage());
631 }
632 return rs;
633 }
634 }