001: package isql;
002:
003: import java.io.*;
004: import java.util.List;
005: import java.awt.*;
006: import java.awt.event.ActionEvent;
007: import java.awt.event.ActionListener;
008: import java.text.*;
009: import javax.swing.*;
010: import javax.swing.KeyStroke;
011: import javax.swing.SwingConstants;
012: import javax.swing.text.*;
013: import java.util.*;
014: import util.*;
015:
016: /**
017: * @author Rahul Kumar $Author: rahul_kumar $
018: * @version $Id: SQLActions.java,v 1.6 2004/01/26 15:25:05 rahul_kumar Exp rahul $
019: */
020: public class SQLActions {
021:
022: SQLForm _form = null;
023: public static final boolean STRICTLINKING = false;
024:
025: public static String[] driverslist = {
026: "oracle.jdbc.driver.OracleDriver",
027: "org.gjt.mm.mysql.Driver", "org.postgresql.Driver",
028: "COM.ibm.db2.jdbc.net.DB2Driver" };
029:
030: public static String[] urllist = {
031: "jdbc:oracle:thin:@172.1.1.111:1521:ORCL",
032: "jdbc:mysql://localhost/test",
033: "jdbc:postgresql://172.1.1.222/test",
034: "jdbc:db2://172.16.2.21/test" };
035:
036: /** constructor passing whatever is needed. Do we need to pass
037: * SQLString or give a method or what.
038: */
039: public SQLActions(SQLForm form) {
040: _form = form;
041: }
042:
043: /**
044: * Gets the MIME type of the data that this
045: * kit represents support for. The default
046: * is <code>text/plain</code>.
047: *
048: * @return the type
049: */
050: public String getContentType() {
051: return "text/plain";
052: }
053:
054: public Action[] getActions() {
055: // let caller augment, why are we bothering ?
056: return new Action[] { new RunAction(_form),
057: new RunBatchAction(_form), new ExitAction(_form),
058: new ConnectAction(_form), new NewConnectAction(_form),
059: new MetaAction(_form), new ClearOutputAction(_form),
060: new SaveOutputAction(_form),
061: new SaveInputAction(_form), new SaveTableAction(_form),
062: new SaveHistoryAction(_form),
063: new ShowDataAction(_form), new HelpAction(_form),
064: new SettingsAction(_form),
065: new ShowFilteredDataAction(_form),
066: new DeleteRowAction(_form) };
067: }
068:
069: public static final String runAction = "run";
070: public static final String runBatchAction = "run-batch";
071: public static final String exitAction = "exit";
072: public static final String connectAction = "connect";
073: public static final String newconnectAction = "new-connection";
074: public static final String metaAction = "meta";
075: public static final String clearOutputAction = "clear-output";
076: public static final String saveOutputAction = "save-output";
077: public static final String saveInputAction = "save-input";
078: public static final String saveTableAction = "save-table";
079: public static final String saveHistoryAction = "save-history";
080: public static final String deleteRowAction = "delete-row";
081: public static final String editTableAction = "edit-table";
082: public static final String showDataAction = "show-data";
083: public static final String showFilteredDataAction = "showfiltered-data";
084: public static final String helpAction = "help";
085: public static final String settingsAction = "settings";
086:
087: public static class RunAction extends AbstractAction {
088:
089: public RunAction(SQLForm form) {
090: super (runAction);
091: this ._form = form;
092: }
093:
094: final SQLForm _form;
095:
096: public void actionPerformed(ActionEvent e) {
097: /*
098: String content = target.getText();
099: if ((content != null) && (content.length() > 0)){
100: _form.Run( content );
101: }
102: */
103: JTextArea jt = (JTextArea) _form.tp.getActualComponent(0);
104: String s = null;
105: if ((s = jt.getText()) != null)
106: _form.Run(s);
107: else
108: _form.popup("No query in input area. Nowhere to run!");
109: }
110: }
111:
112: public static class RunBatchAction extends AbstractAction {
113:
114: /**
115: * Creates this object with the appropriate identifier.
116: */
117: public RunBatchAction(SQLForm form) {
118: super (runBatchAction);
119: this ._form = form;
120: }
121:
122: final SQLForm _form;
123:
124: /**
125: * The operation to perform when this action is triggered.
126: *
127: * @param e the action event
128: */
129: public void actionPerformed(ActionEvent e) {
130: JTextArea jt = (JTextArea) _form.tp.getActualComponent(0);
131: String content = null;
132: if ((content = jt.getSelectedText()) == null
133: || content.trim().length() == 0)
134: content = jt.getText();
135: if ((content != null) && (content.length() > 0)) {
136: //&& ((mod & ActionEvent.ALT_MASK) == 0))
137: // these commands should get moved outa form
138: ArrayList al = new ArrayList(16);
139: //String batchsep = "(;$|^\\/\\s*$)";
140: String batchsep = "(;\n|\n/\n)";
141: batchsep = (String) _form.getAttribute("batchsep",
142: batchsep);
143:
144: PerlWrapper
145: .perlSplit(al, '/' + batchsep + '/', content);
146: if (((String) al.get(al.size() - 1)).trim().length() == 0)
147: al.remove(al.size() - 1);
148: String[] batch = new String[al.size()];
149: al.toArray(batch);
150:
151: System.out.println("size:" + batch.length);
152: if (batch.length > 0) {
153: try {
154: _form.myjdbc.runBatch(batch);
155: _form.setErrorArea("Done");
156: } catch (Exception exc) {
157: System.err.println("128:SQLAction:"
158: + exc.toString());
159: }
160: } else
161: System.err.println("Empty batch:" + content);
162: } else
163: System.err.println("Empty batch:" + content);
164: } // action
165: } // runBatchAction
166:
167: public static class ExitAction extends AbstractAction {
168:
169: public ExitAction(SQLForm form) {
170: super (exitAction);
171: this ._form = form;
172: }
173:
174: final SQLForm _form;
175:
176: public void actionPerformed(ActionEvent e) {
177: _form.exitAction();
178: }
179: }
180:
181: /** action for printing help on all commands. calls the help
182: * command.
183: * here's where a design issue comes up. Command is not an actual
184: * action, so i needed to make this class.
185: */
186: public static class HelpAction extends AbstractAction {
187:
188: public HelpAction(SQLForm form) {
189: super (helpAction);
190: this ._form = form;
191: }
192:
193: final SQLForm _form;
194:
195: public void actionPerformed(ActionEvent e) {
196: _form.Run("help");
197: }
198: }
199:
200: public static class SettingsAction extends AbstractAction {
201:
202: public SettingsAction(SQLForm form) {
203: super (settingsAction);
204: this ._form = form;
205: }
206:
207: final SQLForm _form;
208:
209: public void actionPerformed(ActionEvent e) {
210: _form.Run("settings");
211: }
212: }
213:
214: public static class ConnectAction extends AbstractAction {
215:
216: public ConnectAction(SQLForm form) {
217: super (connectAction);
218: this ._form = form;
219: }
220:
221: final SQLForm _form;
222:
223: public void actionPerformed(ActionEvent e) {
224: _form.connectAction();
225: }
226:
227: }
228:
229: public static class NewConnectAction extends AbstractAction {
230:
231: public NewConnectAction(SQLForm form) {
232: super (newconnectAction);
233: this ._form = form;
234: }
235:
236: final SQLForm _form;
237:
238: public void actionPerformed(ActionEvent e) {
239: String aliasstr = _form.getAttribute("connectors", "");
240: String[] aliaslist = aliasstr.split(",");
241: JLabel lalias = new JLabel("Alias:", JLabel.LEFT);
242: JComboBox alias = new JComboBox(aliaslist);
243: alias.setEditable(true);
244: JLabel ldrivers = new JLabel("Driver:", JLabel.LEFT);
245: final JComboBox drivers = new JComboBox(driverslist);
246: drivers.setEditable(true);
247: JLabel lurl = new JLabel("Url:", JLabel.LEFT);
248: final JComboBox urls = new JComboBox(urllist);
249: urls.setEditable(true);
250: JLabel luser = new JLabel("User:", JLabel.LEFT);
251: final JTextField user = new JTextField(20);
252: JLabel lpassword = new JLabel("Password:", JLabel.LEFT);
253: final JPasswordField password = new JPasswordField(20);
254: final Boolean[] saveentry = new Boolean[1];
255: final String[] fconnectparams = new String[4];
256:
257: alias.addActionListener(new ActionListener() {
258: public void actionPerformed(ActionEvent e) {
259: String item = (String) ((JComboBox) e.getSource())
260: .getSelectedItem();
261: String connector;
262: if ((connector = _form.getAttribute(item)) != null) {
263: if (connector.charAt(0) == '(')
264: connector = connector.substring(1,
265: connector.length() - 1);
266: String[] connectparams = connector.split(",");
267: fconnectparams[0] = connectparams[0];
268: fconnectparams[1] = connectparams[1];
269: fconnectparams[2] = connectparams[2];
270: fconnectparams[3] = connectparams[3];
271: urls.setSelectedItem(connectparams[0]);
272: drivers.setSelectedItem(connectparams[1]);
273: user.setText(connectparams[2]);
274: password.setText(connectparams[3]);
275: saveentry[0] = new Boolean(false);
276: } else
277: //System.err.println(" Could not find:"+ item + " in ini");
278: // new entry, it should be saved.
279: saveentry[0] = new Boolean(true);
280: }
281: });
282:
283: JCheckBox savepassword = new JCheckBox("Save password:",
284: false);
285:
286: JPanel p = new JPanel();
287: p.setLayout(new GridLayout(6, 2, 2, 5)); // rows, cols, hgap, vgap
288: p.add(lalias);
289: p.add(alias);
290: p.add(ldrivers);
291: p.add(drivers);
292: p.add(lurl);
293: p.add(urls);
294: p.add(luser);
295: p.add(user);
296: p.add(lpassword);
297: p.add(password);
298: p.add(savepassword);
299:
300: int i = JOptionPane.showOptionDialog(null, p,
301: "Connect Properties", JOptionPane.OK_CANCEL_OPTION,
302: JOptionPane.PLAIN_MESSAGE, null, null, null);
303: if (i == JOptionPane.CANCEL_OPTION)
304: return;
305: //String salias = alias.getText().trim();
306: String salias = (String) alias.getSelectedItem();
307: String sdriver = (String) drivers.getSelectedItem();
308: String surl = (String) urls.getSelectedItem();
309: String suser = user.getText().trim();
310: String spassword = password.getText().trim();
311: boolean bsavepassword = savepassword.isSelected();
312: try {
313: _form.connect(sdriver, surl, suser, spassword);
314: } catch (Exception exc) {
315: System.err.println("SQLActions L232 EXC:"
316: + exc.toString());
317:
318: _form.popup("Error in connection properties. "
319: + exc.toString());
320: return;
321: }
322:
323: // actuall we should save entries even if edited.
324: // currently we are only saving if a new alias was added.
325: if (saveentry[0].booleanValue()) {
326: if (bsavepassword == false)
327: spassword = "?";
328: String appendstr = "## Added on "
329: + new java.util.Date() + "\n" + "set " + salias
330: + " (" + surl + "," + sdriver + "," + suser
331: + "," + spassword + ")\n"
332: + "append connectors " + salias;
333: _form.appendToIni(appendstr);
334: _form.setErrorArea("\nSaved new entry in ini");
335: } else {
336: boolean shouldsave = false;
337: if (!sdriver.equals(fconnectparams[1])) {
338: shouldsave = true;
339: System.out.println(" modified driver:" + sdriver
340: + ":" + fconnectparams[1]);
341: }
342: if (!surl.equals(fconnectparams[0])) {
343: shouldsave = true;
344: System.out.println(" modified url:" + surl + ":"
345: + fconnectparams[0]);
346: }
347: if (!suser.equals(fconnectparams[2])) {
348: shouldsave = true;
349: System.out.println(" modified user:" + suser
350: + ":" + fconnectparams[2]);
351: }
352: if (!spassword.equals(fconnectparams[3])) {
353: shouldsave = true;
354: System.out.println(" modified pass:" + spassword
355: + ":" + fconnectparams[3]);
356: }
357: if (shouldsave)
358: _form
359: .setErrorArea("\nEntry not saved. Pls update ini if necessary.");
360: }
361: } //actionP
362:
363: }
364:
365: public static class MetaAction extends AbstractAction {
366:
367: public MetaAction(SQLForm form) {
368: super (metaAction);
369: this ._form = form;
370: }
371:
372: final SQLForm _form;
373:
374: public void actionPerformed(ActionEvent e) {
375: _form.metaAction();
376: }
377:
378: }
379:
380: public static class ClearOutputAction extends AbstractAction {
381:
382: public ClearOutputAction(SQLForm form) {
383: super (clearOutputAction);
384: this ._form = form;
385: }
386:
387: final SQLForm _form;
388:
389: public void actionPerformed(ActionEvent e) {
390: //_form.metaAction();
391: System.out.println("TODO");
392: }
393:
394: }
395:
396: public static class SaveInputAction extends AbstractAction {
397:
398: public SaveInputAction(SQLForm form) {
399: super (saveInputAction);
400: this ._form = form;
401: }
402:
403: final SQLForm _form;
404:
405: public void actionPerformed(ActionEvent e) {
406: //_form.metaAction();
407: System.out.println("TODO");
408: }
409: }
410:
411: public static class SaveTableAction extends AbstractAction {
412: public SaveTableAction(SQLForm form) {
413: super (saveTableAction);
414: this ._form = form;
415: }
416:
417: final SQLForm _form;
418:
419: public void actionPerformed(ActionEvent e) {
420: //_form.metaAction();
421: System.out.println("TODO");
422: }
423: }
424:
425: public static class DeleteRowAction extends AbstractAction {
426: public DeleteRowAction(SQLForm form) {
427: super (deleteRowAction);
428: this ._form = form;
429: }
430:
431: final SQLForm _form;
432:
433: public void actionPerformed(ActionEvent e) {
434: //_form.metaAction();
435: System.out.println("TODO");
436: }
437: }
438:
439: /** runs an sql based on selected table name.
440: * if one table, then does a construct sql first,
441: * if more then shows all data from first table.
442: */
443: public static class ShowDataAction extends AbstractAction {
444: public ShowDataAction(SQLForm form) {
445: super (showDataAction);
446: this ._form = form;
447: }
448:
449: final SQLForm _form;
450:
451: public void actionPerformed(ActionEvent e) {
452: Object[] o = _form.tablePanel.getSelectedValues();
453: if (o != null && o.length > 0) {
454: String s = null;
455: if (o.length == 1)
456: s = _form.constructSQL();
457: else
458: s = "select * from " + o[0].toString();
459: _form.Run(s);
460: } else
461: _form.popup("Do select a table.");
462: }
463: }
464:
465: /** Shows data after looking for remembered values in the table.
466: * If no exact match is found, it looks for fields ending with the
467: * remembered field, or whether it ends with one of the tables
468: * fields.
469: * This is run on the tablePanel, a similar one should exist on
470: * frame, that fetches linked table, or asks for it, and then
471: * displays based on remembered value.
472: *
473: * If none found then show all data.
474: */
475: public static class ShowFilteredDataAction extends AbstractAction {
476: public ShowFilteredDataAction(SQLForm form) {
477: super (showFilteredDataAction);
478: this ._form = form;
479: }
480:
481: final SQLForm _form;
482:
483: public void actionPerformed(ActionEvent e) {
484: Object[] o = _form.tablePanel.getSelectedValues();
485: if (o != null && o.length > 0) {
486: for (int i = 0; i < o.length; i++) {
487: String table = (String) o[i];
488: //if there are remembered values then i try matching
489: //else why bother ?
490: Map yanked = _form.htRemembered;
491: StringBuffer sbwhere = new StringBuffer(48);
492: int count = 0;
493: if (yanked != null && yanked.size() > 0) {
494: List colnames = null;
495: try {
496: colnames = _form.myjdbc
497: .getColumnNames(table);
498: } catch (Exception exc) {
499: System.out.println("SQLActions 285:"
500: + exc.toString());
501: continue;
502: }
503: Iterator it = yanked.keySet().iterator();
504: String ycol;
505: while (it.hasNext()) { // for each field
506: ycol = (String) it.next();
507: if (count > 0)
508: sbwhere.append(" AND ");
509:
510: if (colnames.indexOf(ycol) > -1) { // if in this table
511: count = formatWhereCondition(_form,
512: yanked, ycol, ycol, sbwhere,
513: count);
514: /*
515: List yvals = (List)yanked.get(ycol);
516: if (yvals.length>1)
517: sbwhere.append(" ( ");
518: for( int j = 0; j < yvals.size(); j++){
519: Object ycolrow = (Object) yvals.get(j);
520: ycolrow = formatData(_form,ycolrow);
521: // We dont know the value this we
522: // must guess
523: if (j>0) sbwhere.append(" OR ");
524: count++;
525: //sbwhere.append(" ").append(ycol).append('=').append('\'').append(ycolrow).append('\'');
526: sbwhere.append(" ").append(ycol).append('=').append(ycolrow);
527: } // for each value remembered
528: if (yvals.length>1)
529: sbwhere.append(" ) ");
530: */
531: } // if in this table
532: else if (!SQLActions.STRICTLINKING) // hunt for ends with
533: {
534: //String colnamesarray[] = colnames.toArray( new String[colnames.size()] );
535: String colnamesarray[] = ArrayUtil
536: .toStringArray(colnames);
537: int found = ArrayUtil.indexOfEndsWith(
538: colnamesarray, ycol);
539: if (found > -1) {
540: String similarycol = colnamesarray[found];
541: System.out.println(" #2"
542: + similarycol);
543: count = formatWhereCondition(_form,
544: yanked, ycol, similarycol,
545: sbwhere, count);
546: System.out.println(" #2 GOT"
547: + sbwhere);
548: // copied from the top
549: /*
550: List yvals = (List)yanked.get(ycol);
551: for( int j = 0; j < yvals.size(); j++){
552: Object ycolrow = (Object) yvals.get(j);
553: ycolrow = formatData(_form,ycolrow);
554: if (count++>0) sbwhere.append(" AND ");
555: //sbwhere.append(" ").append(similarycol).append('=').append('\'').append(ycolrow).append('\'');
556: sbwhere.append(" ").append(ycol).append('=').append(ycolrow);
557: } // for each value remembered
558: */
559: } // if found
560: else { // do a reverse find
561: found = ArrayUtil.indexOfEndsWith(
562: ycol, colnamesarray);
563: if (found == -1)
564: continue; // phew !!
565: String similarycol = colnamesarray[found];
566: // copied from the top
567: System.out.println("got similar:"
568: + similarycol + ".");
569: System.out.println("orig ycol:"
570: + ycol + ".");
571: count = formatWhereCondition(_form,
572: yanked, ycol, similarycol,
573: sbwhere, count);
574: /*
575: List yvals = (List)yanked.get(ycol);
576: if (yvals == null) continue;
577: for( int j = 0; j < yvals.size(); j++){
578: Object ycolrow = (Object) yvals.get(j);
579: ycolrow = formatData(_form,ycolrow);
580: if (count++>0) sbwhere.append(" OR ");
581: sbwhere.append(" ").append(similarycol).append('=').append('\'').append(ycolrow).append('\'');
582: } // for each value remembered
583: */
584:
585: }
586: // end of copy
587: } // if not strict
588:
589: } // while for each remembered field
590: } // if yanked not null
591: String sqlstring = "select * from " + table;
592: if (sbwhere.length() > 0)
593: sqlstring += " WHERE " + sbwhere.toString();
594: System.out.println("SQL:" + sqlstring);
595: _form.Run(sqlstring);
596:
597: } // for each table
598: //s = _form.constructSQL();
599: } else
600: _form.popup("Do select a table.");
601: } // method
602: } // class showFiltered
603:
604: public static class SaveOutputAction extends AbstractAction {
605:
606: public SaveOutputAction(SQLForm form) {
607: super (saveOutputAction);
608: this ._form = form;
609: }
610:
611: final SQLForm _form;
612:
613: public void actionPerformed(ActionEvent e) {
614: String filename = (String) _form.getAttribute(
615: "outputfilename", "out.lst");
616: BufferedWriter bw = null;
617: try {
618: bw = new BufferedWriter(new FileWriter(filename, true));
619: ((JTextArea) _form.tp.getActualComponent(1)).write(bw);
620: _form.setErrorArea('\n' + "Written output to "
621: + filename);
622: } catch (IOException exc) {
623: System.err.println("EXC:" + exc.toString());
624: _form.setErrorArea('\n' + exc.toString());
625: _form
626: .popup("Couldn't save to file:"
627: + filename
628: + ". You may change the output path by setting outputfilename");
629: } finally {
630: try {
631: if (bw != null)
632: bw.close();
633: } catch (Exception exc) {
634: }
635: }
636: }
637: }
638:
639: public static class SaveHistoryAction extends AbstractAction {
640:
641: public SaveHistoryAction(SQLForm form) {
642: super (saveHistoryAction);
643: this ._form = form;
644: }
645:
646: final SQLForm _form;
647:
648: public void actionPerformed(ActionEvent e) {
649: String filename = (String) _form.getAttribute(
650: "historyfilename", "hist.lst");
651: BufferedWriter bw = null;
652: try {
653: bw = new BufferedWriter(new FileWriter(filename, true));
654: ((JTextArea) _form.tp.getActualComponent(3)).write(bw);
655: _form.setErrorArea('\n' + "Written history to "
656: + filename);
657: } catch (IOException exc) {
658: System.err.println("EXC:" + exc.toString());
659: _form.setErrorArea('\n' + exc.toString());
660: _form
661: .popup("Couldn't save history to file:"
662: + filename
663: + ". You may change the output path by setting historyfilename");
664: } finally {
665: try {
666: if (bw != null)
667: bw.close();
668: } catch (Exception exc) {
669: }
670: }
671: }
672: }
673:
674: /** guess format based on content since we dont have the datatype.
675: * this should be improved to save the datatype as well.
676: */
677: public static String formatData(Map htParams, Object o) {
678: String s = o.toString();
679: String ret = null;
680: if (isDateTime(s)) {
681: ret = htParams.get("fs_timestamp") + s
682: + htParams.get("fe_timestamp");
683: } else if (isTime(s)) {
684: ret = htParams.get("fs_time") + s + htParams.get("fe_time");
685: } else if (isDate(s)) {
686: ret = htParams.get("fs_date") + s + htParams.get("fe_date");
687: } else
688: ret = "'" + s + "'";
689: return ret;
690: }
691:
692: public static boolean isDate(String d) {
693: d = d.replace('/', '-');
694: return parseDateFormat(d, "yyyy'-'MM'-'dd");
695: }
696:
697: public static boolean isDateTime(String d) {
698: d = d.replace('/', '-');
699: return parseDateFormat(d, "yyyy'-'MM'-'dd hh':'mm':'ss");
700: }
701:
702: public static boolean isTimeStamp(String d) {
703: return isDateTime(d);
704: }
705:
706: public static boolean isTime(String d) {
707: return parseDateFormat(d, "hh':'mm':'ss");
708: }
709:
710: public static boolean parseDateFormat(String d, String format) {
711: try {
712: SimpleDateFormat sdf = new SimpleDateFormat(format);
713: sdf.setLenient(false); // this is required else it will convert
714: java.util.Date dt = sdf.parse(d);
715: } catch (Exception exc) {
716: return false;
717: }
718: return true;
719: }
720:
721: private static int formatWhereCondition(SQLForm _form, Map yanked,
722: String ycol, String ycol2, StringBuffer sbwhere, int count) {
723: List yvals = (List) yanked.get(ycol);
724: if (yvals.size() > 1)
725: sbwhere.append(" ( ");
726: for (int j = 0; j < yvals.size(); j++) {
727: Object ycolrow = (Object) yvals.get(j);
728: ycolrow = formatData(_form.getParams(), ycolrow);
729: if (j > 0)
730: sbwhere.append(" OR ");
731: count++;
732: sbwhere.append(" ").append(ycol2).append('=').append(
733: ycolrow);
734: } // for each value remembered
735: if (yvals.size() > 1)
736: sbwhere.append(" ) ");
737: return count;
738: }//
739:
740: public void discoverDrivers(SQLForm _form) {
741: }
742:
743: } // end of class
|