001: package net.sourceforge.squirrel_sql.plugins.sqlscript.table_script;
002:
003: /*
004: * Copyright (C) 2001 Johan Compagner
005: * jcompagner@j-com.nl
006: *
007: * This program is free software; you can redistribute it and/or
008: * modify it under the terms of the GNU General Public License
009: * as published by the Free Software Foundation; either version 2
010: * of the License, or any later version.
011: *
012: * This program is distributed in the hope that it will be useful,
013: * but WITHOUT ANY WARRANTY; without even the implied warranty of
014: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
015: * GNU General Public License for more details.
016: *
017: * You should have received a copy of the GNU General Public License
018: * along with this program; if not, write to the Free Software
019: * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
020: */
021:
022: import java.sql.ResultSet;
023: import java.sql.ResultSetMetaData;
024: import java.sql.SQLException;
025: import java.sql.Statement;
026: import java.sql.Types;
027: import java.util.Calendar;
028:
029: import javax.swing.JInternalFrame;
030: import javax.swing.JOptionPane;
031: import javax.swing.SwingUtilities;
032: import javax.swing.event.InternalFrameEvent;
033: import javax.swing.event.InternalFrameListener;
034:
035: import net.sourceforge.squirrel_sql.client.session.IObjectTreeAPI;
036: import net.sourceforge.squirrel_sql.client.session.ISession;
037: import net.sourceforge.squirrel_sql.fw.dialects.DialectFactory;
038: import net.sourceforge.squirrel_sql.fw.sql.IDatabaseObjectInfo;
039: import net.sourceforge.squirrel_sql.fw.sql.ISQLConnection;
040: import net.sourceforge.squirrel_sql.fw.sql.ITableInfo;
041: import net.sourceforge.squirrel_sql.fw.sql.JDBCTypeMapper;
042: import net.sourceforge.squirrel_sql.fw.sql.TableColumnInfo;
043: import net.sourceforge.squirrel_sql.fw.util.ICommand;
044: import net.sourceforge.squirrel_sql.fw.util.StringManager;
045: import net.sourceforge.squirrel_sql.fw.util.StringManagerFactory;
046: import net.sourceforge.squirrel_sql.plugins.sqlscript.FrameWorkAcessor;
047: import net.sourceforge.squirrel_sql.plugins.sqlscript.SQLScriptPlugin;
048:
049: public class CreateDataScriptCommand implements ICommand,
050: InternalFrameListener {
051: private static final StringManager s_stringMgr = StringManagerFactory
052: .getStringManager(CreateDataScriptCommand.class);
053:
054: protected JInternalFrame _statusFrame;
055: protected boolean _bStop = false;
056:
057: /**
058: * Current session.
059: */
060: protected ISession _session;
061:
062: /**
063: * Current plugin.
064: */
065: private final SQLScriptPlugin _plugin;
066: private boolean _templateScriptOnly;
067:
068: /**
069: * Ctor specifying the current session.
070: */
071: public CreateDataScriptCommand(ISession session,
072: SQLScriptPlugin plugin, boolean templateScriptOnly) {
073: super ();
074: _session = session;
075: _plugin = plugin;
076: _templateScriptOnly = templateScriptOnly;
077: }
078:
079: protected void showAbortFrame() {
080: if (_statusFrame == null) {
081: // i18n[sqlscript.abort=Abort?]
082: JOptionPane optionPane = new JOptionPane(s_stringMgr
083: .getString("sqlscript.abort"),
084: JOptionPane.INFORMATION_MESSAGE,
085: JOptionPane.DEFAULT_OPTION);
086:
087: // i18n[sqlscript.creatingDataScript=Creating data script]
088: _statusFrame = optionPane.createInternalFrame(_session
089: .getSessionSheet(), s_stringMgr
090: .getString("sqlscript.creatingDataScript"));
091: _statusFrame.addInternalFrameListener(this );
092: }
093: _bStop = false;
094: _statusFrame.setVisible(true);
095: }
096:
097: protected void hideAbortFrame() {
098: if (_statusFrame != null) {
099: _statusFrame.removeInternalFrameListener(this );
100: try {
101: _statusFrame.setClosed(true);
102: } catch (Exception e) {
103: }
104: _statusFrame.setVisible(false);
105: }
106: }
107:
108: /**
109: * Execute this command.
110: */
111: public void execute() {
112: final StringBuffer sbRows = new StringBuffer(1000);
113: _session.getApplication().getThreadPool().addTask(
114: new Runnable() {
115: public void run() {
116: ISQLConnection conn = _session
117: .getSQLConnection();
118: try {
119: final Statement stmt = conn
120: .createStatement();
121: try {
122: //IObjectTreeAPI api = _session.getObjectTreeAPI(_plugin);
123: IObjectTreeAPI api = FrameWorkAcessor
124: .getObjectTreeAPI(_session,
125: _plugin);
126:
127: IDatabaseObjectInfo[] dbObjs = api
128: .getSelectedDatabaseObjects();
129:
130: for (int k = 0; k < dbObjs.length; k++) {
131: if (dbObjs[k] instanceof ITableInfo) {
132: if (_bStop)
133: break;
134: ITableInfo ti = (ITableInfo) dbObjs[k];
135: String sTable = ScriptUtil
136: .getTableName(ti);
137: StringBuffer sql = new StringBuffer();
138: sql.append("select * from ");
139: sql.append(ti
140: .getQualifiedName());
141:
142: // Some databases cannot order by LONG/LOB columns.
143: if (!JDBCTypeMapper
144: .isLongType(getFirstColumnType(ti))) {
145: sql.append(" order by ");
146: sql
147: .append(getFirstColumnName(ti));
148: sql.append(" asc ");
149: }
150: ResultSet srcResult = stmt
151: .executeQuery(sql
152: .toString());
153: genInserts(srcResult, sTable,
154: sbRows, false);
155: }
156: }
157: } finally {
158: try {
159: stmt.close();
160: } catch (Exception e) {
161: }
162: }
163: } catch (Exception e) {
164: _session.showErrorMessage(e);
165: }
166: SwingUtilities.invokeLater(new Runnable() {
167: public void run() {
168: if (sbRows.length() > 0) {
169:
170: //_session.getSQLPanelAPI(_plugin).appendSQLScript(sbRows.toString(), true);
171: FrameWorkAcessor.getSQLPanelAPI(
172: _session, _plugin)
173: .appendSQLScript(
174: sbRows.toString(),
175: true);
176:
177: _session
178: .selectMainTab(ISession.IMainPanelTabIndexes.SQL_TAB);
179: }
180: hideAbortFrame();
181: }
182: });
183: }
184: });
185: showAbortFrame();
186: }
187:
188: protected String getFirstColumnName(ITableInfo ti)
189: throws SQLException {
190: TableColumnInfo[] infos = _session.getSQLConnection()
191: .getSQLMetaData().getColumnInfo(ti);
192: return infos[0].getColumnName();
193: }
194:
195: protected int getFirstColumnType(ITableInfo ti) throws SQLException {
196: TableColumnInfo[] infos = _session.getSQLConnection()
197: .getSQLMetaData().getColumnInfo(ti);
198: return infos[0].getDataType();
199: }
200:
201: protected void genInserts(ResultSet srcResult, String sTable,
202: StringBuffer sbRows, boolean headerOnly)
203: throws SQLException {
204: ResultSetMetaData metaData = srcResult.getMetaData();
205:
206: int iColumnCount = metaData.getColumnCount();
207: ColumnInfo[] colInfo = new ColumnInfo[iColumnCount];
208:
209: for (int i = 1; i <= iColumnCount; i++) {
210: colInfo[i - 1] = new ColumnInfo(metaData.getColumnName(i),
211: metaData.getColumnType(i));
212: }
213:
214: // Just a helper to make the fromResultSet ? ... below
215: // look nicer.
216: boolean fromResultSet = !_templateScriptOnly && !headerOnly;
217:
218: sbRows.append("\n\n");
219: while (srcResult.next() || _templateScriptOnly || headerOnly) {
220: if (_bStop)
221: break;
222: sbRows.append("INSERT INTO ");
223: StringBuffer sbValues = new StringBuffer();
224: sbRows.append(sTable);
225: sbRows.append(" (");
226: sbValues.append(" VALUES (");
227:
228: ScriptUtil su = new ScriptUtil();
229:
230: for (int i = 0; i < iColumnCount; i++) {
231: int iIndexPoint = colInfo[i].columnName
232: .lastIndexOf('.');
233: sbRows.append(su
234: .makeColumnNameUnique(colInfo[i].columnName
235: .substring(iIndexPoint + 1)));
236:
237: if (Types.TINYINT == colInfo[i].sqlType
238: || Types.BIGINT == colInfo[i].sqlType
239: || Types.SMALLINT == colInfo[i].sqlType
240: || Types.INTEGER == colInfo[i].sqlType
241: || Types.BIGINT == colInfo[i].sqlType
242: || Types.FLOAT == colInfo[i].sqlType
243: || Types.REAL == colInfo[i].sqlType
244: || Types.DOUBLE == colInfo[i].sqlType
245: || Types.NUMERIC == colInfo[i].sqlType
246: || Types.DECIMAL == colInfo[i].sqlType
247: || Types.NUMERIC == colInfo[i].sqlType
248: || Types.NUMERIC == colInfo[i].sqlType) {
249: Object value = fromResultSet ? srcResult
250: .getObject(i + 1) : "0"
251: + getNullableComment(metaData, i + 1);
252: sbValues.append(value);
253: } else if (Types.DATE == colInfo[i].sqlType
254: || Types.TIME == colInfo[i].sqlType
255: || Types.TIMESTAMP == colInfo[i].sqlType) {
256: Calendar calendar = Calendar.getInstance();
257: java.util.Date timestamp = null;
258: if (Types.DATE == colInfo[i].sqlType) {
259: timestamp = fromResultSet ? srcResult
260: .getDate(i + 1) : new java.util.Date();
261: } else if (Types.TIME == colInfo[i].sqlType) {
262: timestamp = fromResultSet ? srcResult
263: .getTime(i + 1) : new java.util.Date();
264: } else if (Types.TIMESTAMP == colInfo[i].sqlType) {
265: timestamp = fromResultSet ? srcResult
266: .getTimestamp(i + 1)
267: : new java.util.Date();
268: }
269:
270: if (timestamp == null) {
271: sbValues.append("null");
272: } else {
273: calendar.setTime(timestamp);
274:
275: if (Types.DATE == colInfo[i].sqlType) {
276: String esc = "{d '"
277: + prefixNulls(calendar
278: .get(Calendar.YEAR), 4)
279: + "-"
280: + prefixNulls(calendar
281: .get(Calendar.MONTH) + 1, 2)
282: + "-"
283: + prefixNulls(
284: calendar
285: .get(Calendar.DAY_OF_MONTH),
286: 2) + "'}";
287: esc = fromResultSet ? esc : esc
288: + getNullableComment(metaData,
289: i + 1);
290: sbValues.append(esc);
291: } else if (Types.TIME == colInfo[i].sqlType) {
292: String esc = "{t '"
293: + prefixNulls(calendar
294: .get(Calendar.HOUR_OF_DAY),
295: 2)
296: + ":"
297: + prefixNulls(calendar
298: .get(Calendar.MINUTE), 2)
299: + ":"
300: + prefixNulls(calendar
301: .get(Calendar.SECOND), 2)
302: + "'}";
303: esc = fromResultSet ? esc : esc
304: + getNullableComment(metaData,
305: i + 1);
306: sbValues.append(esc);
307: } else if (Types.TIMESTAMP == colInfo[i].sqlType) {
308: String esc = "{ts '"
309: + prefixNulls(calendar
310: .get(Calendar.YEAR), 4)
311: + "-"
312: + prefixNulls(calendar
313: .get(Calendar.MONTH) + 1, 2)
314: + "-"
315: + prefixNulls(
316: calendar
317: .get(Calendar.DAY_OF_MONTH),
318: 2)
319: + " "
320: + prefixNulls(calendar
321: .get(Calendar.HOUR_OF_DAY),
322: 2)
323: + ":"
324: + prefixNulls(calendar
325: .get(Calendar.MINUTE), 2)
326: + ":"
327: + prefixNulls(calendar
328: .get(Calendar.SECOND), 2)
329: + "."
330: + prefixNulls(calendar
331: .get(Calendar.MILLISECOND),
332: 3) + "'}";
333: esc = fromResultSet ? esc : esc
334: + getNullableComment(metaData,
335: i + 1);
336: sbValues.append(esc);
337: }
338:
339: }
340: } else if (Types.BIT == colInfo[i].sqlType
341: || Types.BOOLEAN == colInfo[i].sqlType) {
342: boolean iBoolean = fromResultSet ? srcResult
343: .getBoolean(i + 1) : false;
344:
345: if (fromResultSet && srcResult.wasNull()) {
346: sbValues.append("null");
347: } else if (iBoolean) {
348: // PostgreSQL uses literal values true/false instead of 1/0.
349: if (DialectFactory.isPostgreSQL(_session
350: .getMetaData())) {
351: sbValues.append("true");
352: } else {
353: sbValues.append(1);
354: }
355: } else {
356: // PostgreSQL uses literal values true/false instead of 1/0.
357: if (DialectFactory.isPostgreSQL(_session
358: .getMetaData())) {
359: sbValues.append("false");
360: } else {
361: sbValues.append(0);
362: }
363: }
364:
365: if (false == fromResultSet) {
366: sbValues.append(getNullableComment(metaData,
367: i + 1));
368: }
369: } else // Types.CHAR,
370: // Types.VARCHAR,
371: // Types.LONGVARCHAR,
372: // Types.BINARY,
373: // Types.VARBINARY
374: // Types.LONGVARBINARY
375: // Types.NULL
376: // Types.JAVA_OBJECT
377: // Types.DISTINCT
378: // Types.ARRAY
379: // Types.BLOB
380: // Types.CLOB
381: // Types.REF
382: // Types.DATALINK
383: {
384: String sResult = fromResultSet ? srcResult
385: .getString(i + 1) : "s";
386: if (sResult == null) {
387: sbValues.append("null");
388: } else {
389: int iIndex = sResult.indexOf("'");
390: if (iIndex != -1) {
391: int iPrev = 0;
392: StringBuffer sb = new StringBuffer();
393: sb.append(sResult.substring(iPrev, iIndex));
394: sb.append('\'');
395: iPrev = iIndex;
396: iIndex = sResult.indexOf("'", iPrev + 1);
397: while (iIndex != -1) {
398: sb.append(sResult.substring(iPrev,
399: iIndex));
400: sb.append('\'');
401: iPrev = iIndex;
402: iIndex = sResult
403: .indexOf("'", iPrev + 1);
404: }
405: sb.append(sResult.substring(iPrev));
406: sResult = sb.toString();
407: }
408:
409: iIndex = sResult.indexOf('\n');
410: if (iIndex != -1) {
411: int iPrev = 0;
412: StringBuffer sb = new StringBuffer();
413: sb.append(sResult.substring(iPrev, iIndex));
414: sb.append("\\n");
415: iPrev = iIndex + 1;
416: iIndex = sResult.indexOf('\n', iPrev + 1);
417: while (iIndex != -1) {
418: sb.append(sResult.substring(iPrev,
419: iIndex));
420: sb.append("\\n");
421: iPrev = iIndex + 1;
422: iIndex = sResult.indexOf('\n',
423: iPrev + 1);
424: }
425: sb.append(sResult.substring(iPrev));
426: sResult = sb.toString();
427: }
428: sbValues.append("\'");
429: sbValues.append(sResult);
430: sbValues.append("\'");
431:
432: if (false == fromResultSet) {
433: sbValues.append(getNullableComment(
434: metaData, i + 1));
435: }
436: }
437: }
438: sbValues.append(",");
439: sbRows.append(",");
440: }
441: // delete last ','
442: sbValues.setLength(sbValues.length() - 1);
443: sbRows.setLength(sbRows.length() - 1);
444:
445: // close it.
446: sbValues.append(")").append(getStatementSeparator())
447: .append("\n");
448: sbRows.append(")");
449:
450: if (false == headerOnly) {
451: sbRows.append(sbValues.toString());
452: }
453:
454: if (_templateScriptOnly || headerOnly) {
455: break;
456: }
457: }
458: srcResult.close();
459: }
460:
461: private String getNullableComment(ResultSetMetaData metaData,
462: int colIndex) throws SQLException {
463: if (ResultSetMetaData.columnNoNulls == metaData
464: .isNullable(colIndex)) {
465: return " /*not nullable*/";
466: } else {
467: return "";
468: }
469: }
470:
471: private String prefixNulls(int toPrefix, int digitCount) {
472: String ret = "" + toPrefix;
473:
474: while (ret.length() < digitCount) {
475: ret = 0 + ret;
476: }
477:
478: return ret;
479: }
480:
481: private String getStatementSeparator() {
482: String statementSeparator = _session.getQueryTokenizer()
483: .getSQLStatementSeparator();
484:
485: if (1 < statementSeparator.length()) {
486: statementSeparator = "\n" + statementSeparator + "\n";
487: }
488:
489: return statementSeparator;
490: }
491:
492: /**
493: * @see InternalFrameListener#internalFrameActivated(InternalFrameEvent)
494: */
495: public void internalFrameActivated(InternalFrameEvent e) {
496: }
497:
498: /**
499: * @see InternalFrameListener#internalFrameClosed(InternalFrameEvent)
500: */
501: public void internalFrameClosed(InternalFrameEvent e) {
502: _bStop = true;
503: }
504:
505: /**
506: * @see InternalFrameListener#internalFrameClosing(InternalFrameEvent)
507: */
508: public void internalFrameClosing(InternalFrameEvent e) {
509: }
510:
511: /**
512: * @see InternalFrameListener#internalFrameDeactivated(InternalFrameEvent)
513: */
514: public void internalFrameDeactivated(InternalFrameEvent e) {
515: }
516:
517: /**
518: * @see InternalFrameListener#internalFrameDeiconified(InternalFrameEvent)
519: */
520: public void internalFrameDeiconified(InternalFrameEvent e) {
521: }
522:
523: /**
524: * @see InternalFrameListener#internalFrameIconified(InternalFrameEvent)
525: */
526: public void internalFrameIconified(InternalFrameEvent e) {
527: }
528:
529: /**
530: * @see InternalFrameListener#internalFrameOpened(InternalFrameEvent)
531: */
532: public void internalFrameOpened(InternalFrameEvent e) {
533: }
534:
535: private static class ColumnInfo {
536: int sqlType; // As in java.sql.Types
537: String columnName;
538:
539: public ColumnInfo(String columnName, int sqlType) {
540: this.columnName = columnName;
541: this.sqlType = sqlType;
542: }
543: }
544:
545: }
|