1 package com.explosion.datastream.exql.gui.querywriter;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 import java.awt.BorderLayout;
23 import java.awt.Color;
24 import java.awt.Font;
25 import java.awt.event.ComponentAdapter;
26 import java.awt.event.ComponentEvent;
27 import java.awt.event.KeyEvent;
28 import java.awt.event.MouseAdapter;
29 import java.awt.event.MouseEvent;
30 import java.sql.Connection;
31 import java.sql.PreparedStatement;
32 import java.sql.ResultSet;
33 import java.sql.SQLException;
34 import java.sql.Statement;
35 import java.util.List;
36 import java.util.Vector;
37
38 import javax.swing.JPanel;
39 import javax.swing.JTextArea;
40 import javax.swing.text.JTextComponent;
41
42 import org.apache.log4j.LogManager;
43 import org.apache.log4j.Logger;
44
45 import com.explosion.datastream.exql.EXQLConstants;
46 import com.explosion.datastream.exql.EXQLModuleManager;
47 import com.explosion.datastream.exql.gui.EXQLBaseTool;
48 import com.explosion.datastream.exql.gui.SQLTextActionsListener;
49 import com.explosion.expfmodules.dbstore.DbStoreModuleManager;
50 import com.explosion.expfmodules.rdbmsconn.dbom.utils.Transaction;
51 import com.explosion.expfmodules.texteditor.LineNumberTextEditor;
52 import com.explosion.utilities.GeneralConstants;
53 import com.explosion.utilities.exception.ExceptionManagerFactory;
54
55 public class SQLCommandController extends JPanel
56 {
57 private static Logger log = LogManager.getLogger(SQLCommandController.class);
58 private static final int MODE_EDIT = 0;
59 private static final int MODE_BROWSE_HISTORY = 1;
60 private int mode = MODE_EDIT;
61
62 private LineNumberTextEditor textComponent;
63 private EXQLBaseTool tool;
64 private JTextComponent commandTextComponent;
65 private JTextArea marginTextArea;
66
67 private Vector commandVector = new Vector();
68 private int commandVectorIndex = 0;
69 private int commandNumber = 1;
70 private Connection conn;
71 private String connectionName;
72 private MouseAdapter adapter;
73
74 /***
75 * Constructor
76 */
77 public SQLCommandController(Connection conn, String connectionName, EXQLBaseTool tool) throws Exception
78 {
79 this.conn = conn;
80 this.tool = tool;
81 this.connectionName = connectionName;
82 this.textComponent = new LineNumberTextEditor(tool,"SQL>",false);
83 SQLTextActionsListener listener = new SQLTextActionsListener(textComponent.getEditorTextComponent(),tool);
84 this.commandTextComponent = textComponent.getEditorTextComponent();
85 this.marginTextArea = textComponent.getMarginTextArea();
86 init();
87 }
88
89 /***
90 * Sets up the GUI
91 */
92 private void init() throws Exception
93 {
94 this.setLayout(new BorderLayout());
95 this.add(textComponent, BorderLayout.CENTER);
96
97 commandTextComponent.addKeyListener(new java.awt.event.KeyAdapter()
98 {
99 public void keyTyped(KeyEvent e)
100 {
101 commandTextArea_keyTyped(e);
102 }
103
104 public void keyPressed(KeyEvent e)
105 {
106 commandTextArea_keyPressed(e);
107 }
108 });
109
110 loadCommandHistory();
111
112 showHelpText();
113
114 this.addComponentListener(new ComponentAdapter(){
115 /***
116 * Invoked when the component has been made visible.
117 */
118 public void componentShown(ComponentEvent e) {
119 textComponent.getEditorTextComponent().requestFocus();
120 textComponent.getEditorTextComponent().requestFocusInWindow();
121 }
122 });
123
124 }
125
126 /***
127 * Shows the help text
128 *
129 */
130 private void showHelpText()
131 {
132 commandTextComponent.setText(getHelpString());
133 commandTextComponent.setCaretPosition(commandTextComponent.getText().length());
134
135 adapter = new MouseAdapter(){
136 /***
137 * Invoked when the mouse has been clicked on a component.
138 */
139 public void mouseClicked(MouseEvent e) {
140 if (commandVector.size() > 0)
141 {
142 commandVectorIndex = commandVector.size() - 1;
143 changeModeTo(MODE_BROWSE_HISTORY);
144 commandTextComponent.setText((String)commandVector.get(commandVector.size()-1));
145 commandTextComponent.setCaretPosition(commandTextComponent.getText().length());
146 }
147 else
148 {
149 commandTextComponent.setText("");
150 commandTextComponent.setCaretPosition(0);
151 changeModeTo(MODE_EDIT);
152 }
153 commandTextComponent.removeMouseListener(adapter);
154 }
155 };
156
157 commandTextComponent.addMouseListener(adapter);
158 }
159
160 /***
161 * prints the help tothe message window
162 */
163 private String getHelpString()
164 {
165 return
166 "Welcome" + GeneralConstants.LS +
167 "=-=-=-=" + GeneralConstants.LS +
168 "" + GeneralConstants.LS +
169 " - Press 'Esc' key to toggle between BROWSE-HISTORY and EDIT mode" + GeneralConstants.LS +
170 " - In BROWSE HISTORY mode scroll through previous commands using the up and down arrow keys" + GeneralConstants.LS +
171 " - In EDIT mode, any text terminated with a ';' will be executed when you press 'Enter'. " + GeneralConstants.LS +
172 " - Type 'clear' to clear command history" + GeneralConstants.LS +
173 "" + GeneralConstants.LS +
174 "Click in this window to start" + GeneralConstants.LS;
175 }
176
177 /***
178 * Something is typed onto the command line
179 *
180 * This method behaves in a very specific way. Basically, you are using the cursor
181 * keys to browse through the command history then it works fine.
182 *
183 * If the user starts to edit a command ( this is indicated by a key press other than an
184 * up or down arrow) then the command history mechanism will be switched off until they
185 * execute that command.
186 */
187 void commandTextArea_keyTyped(KeyEvent e)
188 {
189 if (e.getKeyChar() == KeyEvent.VK_ENTER)
190 {
191
192
193 boolean isExecutable = commandTextComponent.getCaretPosition() + 1 >= commandTextComponent.getText().length();
194
195 String command = commandTextComponent.getText().trim();
196
197
198 commandVectorIndex = commandVector.size() - 1;
199
200 if (command.equalsIgnoreCase(";") )
201 {
202 commandVector.addElement(command);
203 commandNumber = 1;
204 }
205 else if (command.equalsIgnoreCase("clear") || command.equalsIgnoreCase("clear;"))
206 {
207 commandTextComponent.setText("");
208 commandVector = new Vector();
209 commandNumber = -1;
210 }
211 else if (command.equalsIgnoreCase("cls") || command.equalsIgnoreCase("cls;"))
212 {
213 commandTextComponent.setText("");
214 commandVector.addElement(command);
215 commandNumber = 1;
216 }
217 else if (command.equalsIgnoreCase("help") || command.equalsIgnoreCase("help;") || command.equalsIgnoreCase("?") || command.equalsIgnoreCase("?;") || command.equalsIgnoreCase("h") || command.equalsIgnoreCase("h;"))
218 {
219 tool.log(getHelpString());
220 commandTextComponent.setText("");
221 commandVector.addElement(command);
222 commandNumber = 1;
223 }
224 else if (command.equalsIgnoreCase("commit") || command.equalsIgnoreCase("commit;"))
225 {
226 try
227 {
228 if (conn == null)
229 tool.log("Query Tool error: Not connected to a database.");
230 else
231 {
232 conn.commit();
233 tool.log("Commit successful.");
234 }
235 }
236 catch (SQLException ex)
237 {
238 tool.log("Error - unable to commit changes : " + ex.getMessage());
239 }
240 commandTextComponent.setText("");
241 commandVector.addElement(command);
242 commandNumber++;
243 }
244 else if (command.equalsIgnoreCase("rollback") || command.equalsIgnoreCase("rollback;"))
245 {
246 try
247 {
248 if (conn == null)
249 tool.log("Simple Query Tool error: Not connected to a database.");
250 else
251 {
252 conn.rollback();
253 tool.log("Rollback successful.");
254 }
255 }
256 catch (SQLException ex)
257 {
258 tool.log("Error - unable to rollback changes : " + ex.getMessage());
259 }
260 commandTextComponent.setText("");
261 commandVector.addElement(command);
262 commandNumber++;
263 }
264 else if (isExecutable && command.endsWith(";"))
265 {
266 tool.log("\n\nQuery " + commandNumber + ": \n" + command);
267 commandTextComponent.setText("");
268 commandVector.addElement(command);
269 commandNumber++;
270 tool.getQueryTool().executeCommand(command.substring(0, command.length() - 1));
271 }
272 else
273 {
274 changeModeTo(MODE_EDIT);
275 }
276 }
277 else if (e.getKeyChar() == KeyEvent.VK_ESCAPE)
278 {
279
280 if (mode == MODE_EDIT)
281 changeModeTo(MODE_BROWSE_HISTORY);
282 else
283 changeModeTo(MODE_EDIT);
284
285 commandTextComponent.setCaretPosition(commandTextComponent.getText().length());
286 commandVectorIndex = commandVector.size() - 1;
287 }
288 else
289 {
290 commandVectorIndex = commandVector.size() - 1;
291 changeModeTo(MODE_EDIT);
292 }
293 }
294
295 /***
296 * Something is typed onto the command line
297 */
298 void commandTextArea_keyPressed(KeyEvent e)
299 {
300 if (mode == MODE_EDIT)
301 {
302 if (commandVector.size() < 1 || commandTextComponent.getText().length() >= 1)
303 {
304 return;
305 }
306 }
307
308
309 switch (e.getKeyCode())
310 {
311 case (38) :
312 if (mode == MODE_BROWSE_HISTORY && commandVectorIndex >= 0)
313 {
314 if (commandVectorIndex > 0)
315 commandVectorIndex--;
316 }
317 else
318 {
319 commandVectorIndex = commandVector.size() - 1;
320 changeModeTo(MODE_BROWSE_HISTORY);
321 }
322
323 if (commandVector.size() > 0)
324 {
325
326 commandTextComponent.setText((String) commandVector.elementAt(commandVectorIndex));
327 commandTextComponent.setCaretPosition(commandTextComponent.getText().length());
328 }
329
330
331 e.consume();
332 break;
333
334 case (40) :
335 if (mode == MODE_BROWSE_HISTORY && commandVectorIndex < (commandVector.size() - 1))
336 {
337 commandVectorIndex++;
338 }
339 else
340 {
341 commandVectorIndex = commandVector.size() - 1;
342 changeModeTo(MODE_BROWSE_HISTORY);
343 }
344
345 if (commandVector.size() > 0)
346 {
347 commandTextComponent.setText((String) commandVector.elementAt(commandVectorIndex));
348 commandTextComponent.setCaretPosition(commandTextComponent.getText().length());
349 }
350
351
352 e.consume();
353 break;
354
355 case (37) :
356 changeModeTo(MODE_EDIT);
357 break;
358
359 case (39) :
360 changeModeTo(MODE_EDIT);
361 break;
362 }
363
364 }
365
366 private void changeModeTo(int newMode)
367 {
368 switch (newMode)
369 {
370 case (MODE_BROWSE_HISTORY) :
371 mode = MODE_BROWSE_HISTORY;
372 commandTextComponent.setForeground((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_BROWSE_MODE_FOREGROUND_COLOR).getValue());
373 commandTextComponent.setBackground((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_BROWSE_MODE_BACKGROUND_COLOR).getValue());
374 commandTextComponent.setCaretColor((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_BROWSE_MODE_FOREGROUND_COLOR).getValue());
375 marginTextArea.setForeground((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_BROWSE_MODE_FOREGROUND_COLOR).getValue());
376 marginTextArea.setBackground((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_BROWSE_MODE_BACKGROUND_COLOR).getValue());
377 marginTextArea.setCaretColor((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_BROWSE_MODE_FOREGROUND_COLOR).getValue());
378 break;
379
380 case (MODE_EDIT) :
381 mode = MODE_EDIT;
382 commandTextComponent.setForeground((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_EDIT_MODE_FOREGROUND_COLOR).getValue());
383 commandTextComponent.setBackground((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_EDIT_MODE_BACKGROUND_COLOR).getValue());
384 commandTextComponent.setCaretColor((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_EDIT_MODE_FOREGROUND_COLOR).getValue());
385 marginTextArea.setForeground((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_EDIT_MODE_FOREGROUND_COLOR).getValue());
386 marginTextArea.setBackground((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_EDIT_MODE_BACKGROUND_COLOR).getValue());
387 marginTextArea.setCaretColor((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_EDIT_MODE_FOREGROUND_COLOR).getValue());
388 break;
389 }
390 }
391
392 public void clear()
393 {
394 commandTextComponent.setText("");
395
396 }
397
398
399 /***
400 * This method returns the text for the margin
401 */
402 private String getMarginText(int numberOfLines)
403 {
404 String returnText = "SQL> ";
405 for (int i = 2; i <= numberOfLines; i++)
406 returnText += GeneralConstants.LS + " " + i + " >";
407 return returnText;
408 }
409
410 public void applyPreferences()
411 {
412
413
414 if (mode == MODE_EDIT)
415 {
416 marginTextArea.setForeground((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_EDIT_MODE_FOREGROUND_COLOR).getValue());
417 marginTextArea.setBackground((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_EDIT_MODE_BACKGROUND_COLOR).getValue());
418 marginTextArea.setCaretColor((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_EDIT_MODE_FOREGROUND_COLOR).getValue());
419
420 commandTextComponent.setForeground((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_EDIT_MODE_FOREGROUND_COLOR).getValue());
421 commandTextComponent.setBackground((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_EDIT_MODE_BACKGROUND_COLOR).getValue());
422 commandTextComponent.setCaretColor((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_EDIT_MODE_FOREGROUND_COLOR).getValue());
423 }
424 else
425 {
426 marginTextArea.setForeground((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_BROWSE_MODE_FOREGROUND_COLOR).getValue());
427 marginTextArea.setBackground((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_BROWSE_MODE_BACKGROUND_COLOR).getValue());
428 marginTextArea.setCaretColor((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_BROWSE_MODE_FOREGROUND_COLOR).getValue());
429
430 commandTextComponent.setForeground((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_BROWSE_MODE_FOREGROUND_COLOR).getValue());
431 commandTextComponent.setBackground((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_BROWSE_MODE_BACKGROUND_COLOR).getValue());
432 commandTextComponent.setCaretColor((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_BROWSE_MODE_FOREGROUND_COLOR).getValue());
433 }
434
435 commandTextComponent.setSelectedTextColor((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_COLOR_SELECTEDFORGROUND).getValue());
436 commandTextComponent.setSelectionColor((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_COLOR_SELECTEDBACKGROUND).getValue());
437
438 commandTextComponent.setFont((Font) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_FONT).getValue());
439
440 marginTextArea.setSelectedTextColor((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_COLOR_SELECTEDFORGROUND).getValue());
441 marginTextArea.setSelectionColor((Color) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_COLOR_SELECTEDBACKGROUND).getValue());
442
443 marginTextArea.setFont((Font) EXQLModuleManager.instance().getPreference(EXQLConstants.COMMANDER_FONT).getValue());
444 }
445
446 public void disableComponent()
447 {
448 commandTextComponent.setEnabled(false);
449 marginTextArea.setEnabled(false);
450 }
451
452 public void enableComponent()
453 {
454 commandTextComponent.setEnabled(true);
455 marginTextArea.setEnabled(true);
456 }
457
458 public JTextComponent getCommandTextComponent()
459 {
460 return commandTextComponent;
461 }
462
463 /***
464 * This method persists the command history for later retrieval the next time the user starts the system.
465 */
466 public void persistCommandHistory()
467 {
468 if (!((Boolean)EXQLModuleManager.instance().getPreference(EXQLConstants.RDBMS_OPTION_REMEMBER_COMMAND_HISTORY).getValue()).booleanValue())
469 return;
470
471 int maxRows = ((Integer) EXQLModuleManager.instance().getPreference(EXQLConstants.RDBMS_OPTION_MAXCOMMANDSPERSISTED).getValue()).intValue();
472
473 List persistMe = null;
474 if (commandVector.size() > maxRows)
475 {
476 persistMe = commandVector.subList(commandVector.size()-maxRows,commandVector.size());
477 }
478 else
479 {
480 persistMe = commandVector;
481 }
482
483 log.debug("Persisting");
484 PreparedStatement st = null;
485 Connection conn = null;
486 Transaction t = null;
487 try
488 {
489 conn = DbStoreModuleManager.instance().getConnection();
490 t = new Transaction(conn);
491 st = conn.prepareStatement("DELETE FROM SQL_COMMAND_HISTORY WHERE CONNECTION_NAME=?");
492 st.setString(1,connectionName);
493 st.executeUpdate();
494 st.close();
495
496 for (int i=0;i<persistMe.size();i++)
497 {
498 st = conn.prepareStatement("INSERT INTO SQL_COMMAND_HISTORY (ID, CONNECTION_NAME, COMMAND) VALUES (NULL,?,?)");
499 st.setString(1,connectionName);
500 st.setString(2,(String)persistMe.get(i));
501 st.executeUpdate();
502 st.close();
503 }
504 t.commit();
505 }
506 catch (SQLException e)
507 {
508 if (t != null)
509 {
510 t.rollback();
511 }
512 ExceptionManagerFactory.getExceptionManager().manageException(e,"Exception caught while saving command history");
513 }
514 finally
515 {
516 if (st != null)
517 {
518 try { st.close(); } catch (SQLException e1) {
519 }
520
521 if (conn != null)
522 {
523 try { conn.close(); } catch (SQLException e1) {
524 }
525 }
526 }
527
528 /***
529 * This method loads the command history for the user when they connect to a particular db.
530 */
531 public void loadCommandHistory()
532 {
533 if (!((Boolean)EXQLModuleManager.instance().getPreference(EXQLConstants.RDBMS_OPTION_REMEMBER_COMMAND_HISTORY).getValue()).booleanValue())
534 return;
535
536 log.debug("loading");
537 Statement st = null;
538 Connection conn = null;
539 try
540 {
541 conn = DbStoreModuleManager.instance().getConnection();
542 st = conn.createStatement();
543 ResultSet set = st.executeQuery("SELECT COMMAND FROM SQL_COMMAND_HISTORY WHERE CONNECTION_NAME='"+connectionName+"' order by ID ASC");
544 while (set.next())
545 {
546 String s = set.getString(1);
547 log.debug(s);
548 commandVector.add(s);
549 }
550 set.close();
551 st.close();
552 }
553 catch (SQLException e)
554 {
555 ExceptionManagerFactory.getExceptionManager().manageException(e,"Exception caught while saving command history");
556
557 }
558 finally
559 {
560 if (st != null)
561 {
562 try { st.close(); } catch (SQLException e1) {
563 }
564
565 if (conn != null)
566 {
567 try { conn.close(); } catch (SQLException e1) {
568 }
569 }
570
571 }
572 }
573