001: package net.sourceforge.squirrel_sql.plugins.sqlscript.table_script;
002:
003: /*
004: * Copyright (C) 2005 Gerd Wagner
005: * gerdwagner@users.sourceforge.net
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:
027: import javax.swing.JOptionPane;
028: import javax.swing.SwingUtilities;
029:
030: import net.sourceforge.squirrel_sql.client.session.DefaultSQLExecuterHandler;
031: import net.sourceforge.squirrel_sql.client.session.ISQLPanelAPI;
032: import net.sourceforge.squirrel_sql.client.session.ISession;
033: import net.sourceforge.squirrel_sql.client.session.SQLExecuterTask;
034: import net.sourceforge.squirrel_sql.fw.sql.IQueryTokenizer;
035: import net.sourceforge.squirrel_sql.fw.sql.ISQLConnection;
036: import net.sourceforge.squirrel_sql.fw.util.StringManager;
037: import net.sourceforge.squirrel_sql.fw.util.StringManagerFactory;
038: import net.sourceforge.squirrel_sql.plugins.sqlscript.FrameWorkAcessor;
039: import net.sourceforge.squirrel_sql.plugins.sqlscript.SQLScriptPlugin;
040:
041: public class CreateTableOfCurrentSQLCommand extends
042: CreateDataScriptCommand {
043: private static final StringManager s_stringMgr = StringManagerFactory
044: .getStringManager(CreateTableOfCurrentSQLCommand.class);
045:
046: /**
047: * Current plugin.
048: */
049: private final SQLScriptPlugin _plugin;
050:
051: /**
052: * Ctor specifying the current session.
053: */
054: public CreateTableOfCurrentSQLCommand(ISession session,
055: SQLScriptPlugin plugin) {
056: super (session, plugin, true);
057: _plugin = plugin;
058: }
059:
060: /**
061: * Execute this command.
062: */
063: public void execute() {
064:
065: CreateTableOfCurrentSQLCtrl ctrl = new CreateTableOfCurrentSQLCtrl(
066: _session);
067:
068: if (false == ctrl.isOK()) {
069: return;
070: }
071:
072: final String sTable = ctrl.getTableName();
073: final boolean scriptOnly = ctrl.isScriptOnly();
074: final boolean dropTable = ctrl.isDropTable();
075:
076: _session.getApplication().getThreadPool().addTask(
077: new Runnable() {
078: public void run() {
079:
080: doCreateTableOfCurrentSQL(sTable, scriptOnly,
081: dropTable);
082: }
083: });
084: showAbortFrame();
085: }
086:
087: private void doCreateTableOfCurrentSQL(final String sTable,
088: final boolean scriptOnly, final boolean dropTable) {
089:
090: final StringBuffer sbScript = new StringBuffer();
091: try {
092: ISQLPanelAPI api = FrameWorkAcessor.getSQLPanelAPI(
093: _session, _plugin);
094:
095: String script = api.getSQLScriptToBeExecuted();
096:
097: IQueryTokenizer qt = _session.getQueryTokenizer();
098: qt.setScriptToTokenize(script);
099:
100: if (false == qt.hasQuery()) {
101: // i18n[CreateTableOfCurrentSQLCommand.noQuery=No query found to create the script from.]
102: _session
103: .showErrorMessage(s_stringMgr
104: .getString("CreateTableOfCurrentSQLCommand.noQuery"));
105: return;
106: }
107:
108: ISQLConnection conn = _session.getSQLConnection();
109: Statement stmt = null;
110: try {
111: StringBuffer sbCreate = new StringBuffer();
112: StringBuffer sbInsert = new StringBuffer();
113: StringBuffer sbDrop = new StringBuffer();
114: String statSep = ScriptUtil
115: .getStatementSeparator(_session);
116:
117: stmt = conn.createStatement();
118: String sql = qt.nextQuery();
119: ResultSet srcResult = stmt.executeQuery(sql);
120:
121: genCreate(srcResult, sTable, sbCreate);
122:
123: genInserts(srcResult, sTable, sbInsert, true);
124: sbInsert.append('\n').append(sql);
125:
126: sbDrop.append("DROP TABLE " + sTable);
127:
128: if (dropTable
129: && _session.getSchemaInfo().isTable(sTable)) {
130: sbScript.append(sbDrop).append(statSep);
131: }
132:
133: sbScript.append(sbCreate).append(statSep);
134: sbScript.append(sbInsert).append(statSep);
135: } finally {
136: try {
137: stmt.close();
138: } catch (Exception e) {
139: }
140: }
141:
142: if (false == scriptOnly) {
143: try {
144: SQLExecuterTask executer = new SQLExecuterTask(
145: _session, sbScript.toString(),
146: new DefaultSQLExecuterHandler(_session));
147: executer.run();
148:
149: // i18n[sqlscript.successCreate=Successfully created table {0}]
150: _session.showMessage(s_stringMgr.getString(
151: "sqlscript.successCreate", sTable));
152: } catch (Exception e) {
153: _session.showErrorMessage(e);
154:
155: // i18n[sqlscript.storeSqlInTableFailed=An error occured during storing SQL result in table {0}. See messages for details.\nI will create the copy script. You may correct errors and run it again.]
156: String msg = s_stringMgr.getString(
157: "sqlscript.storeSqlInTableFailed", sTable);
158: JOptionPane.showMessageDialog(_session
159: .getApplication().getMainFrame(), msg);
160: }
161:
162: }
163: } catch (Exception e) {
164: _session.showErrorMessage(e);
165: e.printStackTrace();
166: } finally {
167: SwingUtilities.invokeLater(new Runnable() {
168: public void run() {
169: hideAbortFrame();
170: if (scriptOnly
171: && 0 < sbScript.toString().trim().length()) {
172: FrameWorkAcessor.getSQLPanelAPI(_session,
173: _plugin).appendSQLScript(
174: sbScript.toString(), true);
175: _session
176: .selectMainTab(ISession.IMainPanelTabIndexes.SQL_TAB);
177: }
178: }
179: });
180: }
181: }
182:
183: private void genCreate(ResultSet srcResult, String sTable,
184: StringBuffer sbCreate) {
185: try {
186: ResultSetMetaData metaData = srcResult.getMetaData();
187:
188: sbCreate.append("\n\nCREATE TABLE ").append(sTable).append(
189: '\n');
190: sbCreate.append("(\n");
191:
192: ScriptUtil su = new ScriptUtil();
193:
194: String sColName = metaData.getColumnName(1);
195: String sColType = metaData.getColumnTypeName(1);
196: int colSize = metaData.getColumnDisplaySize(1);
197: int decimalDigits = metaData.getScale(1);
198: sbCreate.append(" ").append(
199: su.getColumnDef(sColName, sColType, colSize,
200: decimalDigits));
201:
202: for (int i = 2; i <= metaData.getColumnCount(); ++i) {
203: sbCreate.append(",\n");
204:
205: sColName = metaData.getColumnName(i);
206: sColType = metaData.getColumnTypeName(i);
207: colSize = metaData.getColumnDisplaySize(i);
208: decimalDigits = metaData.getScale(i);
209: sbCreate.append(" ").append(
210: su.getColumnDef(sColName, sColType, colSize,
211: decimalDigits));
212: }
213:
214: sbCreate.append("\n)");
215:
216: } catch (SQLException e) {
217: throw new RuntimeException(e);
218: }
219: }
220:
221: private String getTableName() {
222: return "ygwaTest";
223: }
224:
225: private String getNextToken(String selectSQL, int startPos) {
226: int curPos = startPos;
227: while (curPos < selectSQL.length()
228: && true == Character.isWhitespace(selectSQL
229: .charAt(curPos))) {
230: // Move over leading whitespaces
231: ++curPos;
232: }
233:
234: int startPosTrimed = curPos;
235:
236: while (curPos < selectSQL.length()
237: && false == Character.isWhitespace(selectSQL
238: .charAt(curPos))) {
239: ++curPos;
240: }
241:
242: return selectSQL.substring(startPosTrimed, curPos);
243: }
244:
245: private int getTokenBeginIndex(String selectSQL, String token) {
246: String lowerSel = selectSQL.toLowerCase();
247: String lowerToken = token.toLowerCase().trim();
248:
249: int curPos = 0;
250: while (-1 != curPos) {
251: curPos = lowerSel.indexOf(lowerToken);
252:
253: if (-1 < curPos
254: && (0 == curPos || Character.isWhitespace(lowerSel
255: .charAt(curPos - 1)))
256: && (lowerSel.length() == curPos
257: + lowerToken.length() || Character
258: .isWhitespace(lowerSel.charAt(curPos
259: + lowerToken.length())))) {
260: return curPos;
261: }
262: }
263:
264: return curPos;
265: }
266: }
|