Thread: [Squirrel-sql-commits] sql12/plugins/sqlscript/src/net/sourceforge/squirrel_sql/plugins/sqlscript/ta
A Java SQL client for any JDBC compliant database
Brought to you by:
colbell,
gerdwagner
From: Gerd W. <ger...@us...> - 2005-01-31 21:06:27
|
Update of /cvsroot/squirrel-sql/sql12/plugins/sqlscript/src/net/sourceforge/squirrel_sql/plugins/sqlscript/table_script In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv26516 Modified Files: CreateDataScriptCommand.java CreateDataScriptOfCurrentSQLCommand.java CreateTableScriptCommand.java Added Files: CreateTableOfCurrentSQLAction.java CreateTableOfCurrentSQLCommand.java CreateTableOfCurrentSQLCtrl.java CreateTableOfCurrentSQLDialog.java ScriptUtil.java Log Message: Added 'Store current SQL in table' function. --- NEW FILE: ScriptUtil.java --- package net.sourceforge.squirrel_sql.plugins.sqlscript.table_script; import net.sourceforge.squirrel_sql.client.session.ISession; import java.util.Hashtable; public class ScriptUtil { Hashtable _uniqueColNames = new Hashtable(); /** * This method provides unique column names. * Use a new instance of this class for * every meta data result set */ public String getColumnDef(String sColumnName, String sType, int columnSize, int decimalDigits) { String decimalDigitsString = 0 == decimalDigits ? "" : "," + decimalDigits; sColumnName = makeColumnNameUnique(sColumnName); StringBuffer sbColDef = new StringBuffer(); String sLower = sType.toLowerCase(); sbColDef.append(sColumnName).append(" "); sbColDef.append(sType); if (sLower.indexOf("char") != -1) { sbColDef.append("("); sbColDef.append(columnSize).append(decimalDigitsString); sbColDef.append(")"); } else if (sLower.equals("numeric")) { sbColDef.append("("); sbColDef.append(columnSize).append(decimalDigitsString); sbColDef.append(")"); } else if (sLower.equals("number")) { sbColDef.append("("); sbColDef.append(columnSize).append(decimalDigitsString); sbColDef.append(")"); } else if (sLower.equals("decimal")) { sbColDef.append("("); sbColDef.append(columnSize).append(decimalDigitsString); sbColDef.append(")"); } return sbColDef.toString(); } /** * This method provides unique column names. * Use a new instance of this class for * every meta data result set. * * */ public String makeColumnNameUnique(String sColumnName) { return makeColumnNameUniqueIntern(sColumnName, 0); } private String makeColumnNameUniqueIntern(String sColumnName, int postFixSeed) { String upperCaseColumnName = sColumnName.toUpperCase(); String sRet = sColumnName; if(0 < postFixSeed) { sRet += "_" + postFixSeed; upperCaseColumnName += "_" + postFixSeed; } if(null == _uniqueColNames.get(upperCaseColumnName)) { _uniqueColNames.put(upperCaseColumnName,upperCaseColumnName); return sRet; } else { return makeColumnNameUniqueIntern(sColumnName, ++postFixSeed); } } public static String getStatementSeparator(ISession session) { String statementSeparator = session.getProperties().getSQLStatementSeparator(); if (1 < statementSeparator.length()) { statementSeparator = "\n" + statementSeparator + "\n"; } return statementSeparator; } } --- NEW FILE: CreateTableOfCurrentSQLDialog.java --- package net.sourceforge.squirrel_sql.plugins.sqlscript.table_script; import javax.swing.*; import java.awt.*; public class CreateTableOfCurrentSQLDialog extends JDialog { JButton btnOK; JButton btnCancel; JTextField txtTableName; JCheckBox chkScriptOnly; JCheckBox chkDropTable; public CreateTableOfCurrentSQLDialog(JFrame parentFrame) { super(parentFrame, "Create table of SQL", true); getContentPane().setLayout(new GridLayout(5,1,5,0)); getContentPane().add(new JLabel("Enter name of table:")); txtTableName = new JTextField(); getContentPane().add(txtTableName); chkDropTable = new JCheckBox("Drop table if exists"); getContentPane().add(chkDropTable); chkScriptOnly = new JCheckBox("Generate script only"); getContentPane().add(chkScriptOnly); JPanel pnlButtons = new JPanel(); pnlButtons.setLayout(new GridLayout(1,2,0,5)); btnOK = new JButton("OK"); pnlButtons.add(btnOK); btnCancel = new JButton("Cancel"); pnlButtons.add(btnCancel); getContentPane().add(pnlButtons); } } Index: CreateDataScriptCommand.java =================================================================== RCS file: /cvsroot/squirrel-sql/sql12/plugins/sqlscript/src/net/sourceforge/squirrel_sql/plugins/sqlscript/table_script/CreateDataScriptCommand.java,v retrieving revision 1.6 retrieving revision 1.7 diff -C2 -d -r1.6 -r1.7 *** CreateDataScriptCommand.java 12 Dec 2004 13:21:01 -0000 1.6 --- CreateDataScriptCommand.java 31 Jan 2005 21:05:47 -0000 1.7 *************** *** 124,128 **** String sTable = ti.getSimpleName(); ResultSet srcResult = stmt.executeQuery("select * from " + ti.getQualifiedName()); ! genInserts(srcResult, sTable, sbRows); } } --- 124,128 ---- String sTable = ti.getSimpleName(); ResultSet srcResult = stmt.executeQuery("select * from " + ti.getQualifiedName()); ! genInserts(srcResult, sTable, sbRows, false); } } *************** *** 163,167 **** } ! protected void genInserts(ResultSet srcResult, String sTable, StringBuffer sbRows) throws SQLException { --- 163,167 ---- } ! protected void genInserts(ResultSet srcResult, String sTable, StringBuffer sbRows, boolean headerOnly) throws SQLException { *************** *** 179,199 **** // Just a helper to make the fromResultSet ? ... below // look nicer. ! boolean fromResultSet = !_templateScriptOnly; ! while (srcResult.next() || _templateScriptOnly) { if (_bStop) break; ! sbRows.append("insert into "); StringBuffer sbValues = new StringBuffer(); sbRows.append(sTable); sbRows.append(" ("); ! sbValues.append(" values ("); for (int i = 0; i < iColumnCount; i++) { String sColumnTypeName = typeAndName[i][0]; ! String sName = typeAndName[i][1]; ! int iIndexPoint = sName.lastIndexOf('.'); ! sName = sName.substring(iIndexPoint + 1); ! sbRows.append(sName); if (sColumnTypeName.equals("INTEGER") --- 179,203 ---- // Just a helper to make the fromResultSet ? ... below // look nicer. ! boolean fromResultSet = !_templateScriptOnly && !headerOnly; ! sbRows.append("\n\n"); ! while (srcResult.next() || _templateScriptOnly || headerOnly) { if (_bStop) break; ! sbRows.append("INSERT INTO "); StringBuffer sbValues = new StringBuffer(); sbRows.append(sTable); sbRows.append(" ("); ! sbValues.append(" VALUES ("); ! ! ScriptUtil su = new ScriptUtil(); ! for (int i = 0; i < iColumnCount; i++) { String sColumnTypeName = typeAndName[i][0]; ! String sColumnName = typeAndName[i][1]; ! int iIndexPoint = sColumnName.lastIndexOf('.'); ! sColumnName = sColumnName.substring(iIndexPoint + 1); ! sbRows.append(su.makeColumnNameUnique(sColumnName)); if (sColumnTypeName.equals("INTEGER") *************** *** 354,360 **** sbValues.append(")").append(getStatementSeparator()).append("\n"); sbRows.append(")"); - sbRows.append(sbValues.toString()); ! if(_templateScriptOnly) { break; --- 358,368 ---- sbValues.append(")").append(getStatementSeparator()).append("\n"); sbRows.append(")"); ! if(false == headerOnly) ! { ! sbRows.append(sbValues.toString()); ! } ! ! if(_templateScriptOnly || headerOnly) { break; Index: CreateDataScriptOfCurrentSQLCommand.java =================================================================== RCS file: /cvsroot/squirrel-sql/sql12/plugins/sqlscript/src/net/sourceforge/squirrel_sql/plugins/sqlscript/table_script/CreateDataScriptOfCurrentSQLCommand.java,v retrieving revision 1.4 retrieving revision 1.5 diff -C2 -d -r1.4 -r1.5 *** CreateDataScriptOfCurrentSQLCommand.java 12 Dec 2004 13:21:01 -0000 1.4 --- CreateDataScriptOfCurrentSQLCommand.java 31 Jan 2005 21:05:54 -0000 1.5 *************** *** 81,85 **** sTable = getNextToken(selectSQL, iFromIndex + "from".length()); } ! genInserts(srcResult, sTable, sbRows); } finally --- 81,85 ---- sTable = getNextToken(selectSQL, iFromIndex + "from".length()); } ! genInserts(srcResult, sTable, sbRows, false); } finally Index: CreateTableScriptCommand.java =================================================================== RCS file: /cvsroot/squirrel-sql/sql12/plugins/sqlscript/src/net/sourceforge/squirrel_sql/plugins/sqlscript/table_script/CreateTableScriptCommand.java,v retrieving revision 1.6 retrieving revision 1.7 diff -C2 -d -r1.6 -r1.7 *** CreateTableScriptCommand.java 22 Dec 2004 20:54:05 -0000 1.6 --- CreateTableScriptCommand.java 31 Jan 2005 21:05:55 -0000 1.7 *************** *** 131,178 **** rsPks.close(); } ResultSet rsColumns = conn.getSQLMetaData().getColumns(ti); while (rsColumns.next()) { ! String decimalDigitsString = ""; if (false == isJdbcOdbc) { ! int decimalDigits = rsColumns.getInt(9); ! decimalDigitsString = 0 == decimalDigits ? "" : "," + decimalDigits; } String sColumnName = rsColumns.getString(4); - sbScript.append("\n "); - sbScript.append(sColumnName); - sbScript.append(" "); - // int iType = rsColumns.getInt() String sType = rsColumns.getString(6); ! sbScript.append(sType); ! String sLower = sType.toLowerCase(); ! if (sLower.indexOf("char") != -1) ! { ! sbScript.append("("); ! sbScript.append(rsColumns.getString(7)).append(decimalDigitsString); ! sbScript.append(")"); ! } ! else if (sLower.equals("numeric")) ! { ! sbScript.append("("); ! sbScript.append(rsColumns.getString(7)).append(decimalDigitsString); ! sbScript.append(")"); ! } ! else if (sLower.equals("number")) ! { ! sbScript.append("("); ! sbScript.append(rsColumns.getString(7)).append(decimalDigitsString); ! ; ! sbScript.append(")"); ! } ! else if (sLower.equals("decimal")) ! { ! sbScript.append("("); ! sbScript.append(rsColumns.getString(7)).append(decimalDigitsString); ! sbScript.append(")"); ! } if (pks.size() == 1 && pks.get(0).equals(sColumnName)) { --- 131,153 ---- rsPks.close(); } + + ScriptUtil su = new ScriptUtil(); ResultSet rsColumns = conn.getSQLMetaData().getColumns(ti); while (rsColumns.next()) { ! int decimalDigits = 0; if (false == isJdbcOdbc) { ! decimalDigits = rsColumns.getInt(9); } String sColumnName = rsColumns.getString(4); String sType = rsColumns.getString(6); ! int colSize = rsColumns.getInt(7); ! ! sbScript.append("\n "); ! sbScript.append(su.getColumnDef(sColumnName, sType, colSize, decimalDigits)); ! if (pks.size() == 1 && pks.get(0).equals(sColumnName)) { *************** *** 203,207 **** sbScript.setLength(sbScript.length() - 1); ! sbScript.append("\n)").append(getStatementSeparator()).append("\n"); --- 178,182 ---- sbScript.setLength(sbScript.length() - 1); ! sbScript.append("\n)").append(ScriptUtil.getStatementSeparator(_session)).append("\n"); *************** *** 260,263 **** --- 235,239 ---- } + private String createIndexes(ITableInfo ti) throws SQLException *************** *** 348,352 **** } } ! sbToAppend.append(")").append(getStatementSeparator()).append("\n"); } --- 324,328 ---- } } ! sbToAppend.append(")").append(ScriptUtil.getStatementSeparator(_session)).append("\n"); } *************** *** 471,490 **** } ! sbToAppend.append(")").append(getStatementSeparator()).append("\n"); } return sbToAppend.toString(); } - - private String getStatementSeparator() - { - String statementSeparator = _session.getProperties().getSQLStatementSeparator(); - - if (1 < statementSeparator.length()) - { - statementSeparator = "\n" + statementSeparator + "\n"; - } - - return statementSeparator; - } } \ No newline at end of file --- 447,454 ---- } ! sbToAppend.append(")").append(ScriptUtil.getStatementSeparator(_session)).append("\n"); } return sbToAppend.toString(); } } \ No newline at end of file --- NEW FILE: CreateTableOfCurrentSQLAction.java --- package net.sourceforge.squirrel_sql.plugins.sqlscript.table_script; /* * Copyright (C) 2005 Gerd Wagner * ger...@us... * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License * as published by the Free Software Foundation; either version 2 * of the License, or any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program; if not, write to the Free Software * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. */ import java.awt.event.ActionEvent; import net.sourceforge.squirrel_sql.fw.util.Resources; import net.sourceforge.squirrel_sql.plugins.sqlscript.SQLScriptPlugin; import net.sourceforge.squirrel_sql.client.IApplication; import net.sourceforge.squirrel_sql.client.action.SquirrelAction; import net.sourceforge.squirrel_sql.client.session.ISession; import net.sourceforge.squirrel_sql.client.session.action.ISessionAction; public class CreateTableOfCurrentSQLAction extends SquirrelAction implements ISessionAction { /** * Current session. */ private ISession _session; /** * Current plugin. */ private final SQLScriptPlugin _plugin; public CreateTableOfCurrentSQLAction(IApplication app, Resources rsrc, SQLScriptPlugin plugin) { super(app, rsrc); _plugin = plugin; } public void actionPerformed(ActionEvent evt) { if (_session != null) { new CreateTableOfCurrentSQLCommand(_session, _plugin).execute(); } } /** * Set the current session. * * @param session The current session. */ public void setSession(ISession session) { _session = session; } } --- NEW FILE: CreateTableOfCurrentSQLCommand.java --- package net.sourceforge.squirrel_sql.plugins.sqlscript.table_script; /* * Copyright (C) 2005 Gerd Wagner * ger...@us... * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License * as published by the Free Software Foundation; either version 2 * of the License, or any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program; if not, write to the Free Software * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. */ import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; import java.sql.SQLException; import javax.swing.JOptionPane; import javax.swing.SwingUtilities; import net.sourceforge.squirrel_sql.fw.sql.SQLConnection; import net.sourceforge.squirrel_sql.fw.sql.IDatabaseObjectInfo; import net.sourceforge.squirrel_sql.fw.sql.ITableInfo; import net.sourceforge.squirrel_sql.fw.util.ICommand; import net.sourceforge.squirrel_sql.plugins.sqlscript.SQLScriptPlugin; import net.sourceforge.squirrel_sql.plugins.sqlscript.FrameWorkAcessor; import net.sourceforge.squirrel_sql.client.session.ISession; public class CreateTableOfCurrentSQLCommand extends CreateDataScriptCommand { /** * Current plugin. */ private final SQLScriptPlugin _plugin; /** * Ctor specifying the current session. */ public CreateTableOfCurrentSQLCommand(ISession session, SQLScriptPlugin plugin) { super(session, plugin, true); _plugin = plugin; } /** * Execute this command. */ public void execute() { CreateTableOfCurrentSQLCtrl ctrl = new CreateTableOfCurrentSQLCtrl(_session); if(false == ctrl.isOK()) { return; } final String sTable = ctrl.getTableName(); final boolean scriptOnly = ctrl.isScriptOnly(); final boolean dropTable = ctrl.isDropTable(); _session.getApplication().getThreadPool().addTask(new Runnable() { public void run() { SQLConnection conn = _session.getSQLConnection(); String selectSQL = FrameWorkAcessor.getSQLPanelAPI(_session, _plugin).getSQLScriptToBeExecuted(); final StringBuffer sbCreate = new StringBuffer(); try { final Statement stmt = conn.createStatement(); try { ResultSet srcResult = stmt.executeQuery(selectSQL); genCreate(srcResult, sTable, sbCreate); StringBuffer sbInsert = new StringBuffer(); genInserts(srcResult, sTable, sbInsert, true); sbInsert.append('\n').append(selectSQL); if(false == scriptOnly) { try { if(dropTable) { try { stmt.execute("DROP TABLE " + sTable); } catch(Exception e) { _session.getMessageHandler().showMessage("Drop table " + sTable + " failed:"); _session.getMessageHandler().showMessage(e); } } stmt.execute(sbCreate.toString()); stmt.execute(sbInsert.toString()); hideAbortFrame(); _session.getMessageHandler().showMessage("Successfully created table " + sTable); return; } catch(Exception e) { _session.getMessageHandler().showErrorMessage(e); String msg = "An error occured during storing SQL result in table " + sTable + ". See messages for details.\n" + "I will create the copy script. You may correct errors and run it again."; JOptionPane.showMessageDialog(_session.getApplication().getMainFrame(), msg); } } String statSep = ScriptUtil.getStatementSeparator(_session); sbCreate.append(statSep).append(sbInsert).append(statSep); } finally { try { stmt.close(); } catch (Exception e) { } } } catch (Exception e) { _session.getMessageHandler().showErrorMessage(e); e.printStackTrace(); } SwingUtilities.invokeLater(new Runnable() { public void run() { FrameWorkAcessor.getSQLPanelAPI(_session, _plugin).appendSQLScript(sbCreate.toString(), true); _session.selectMainTab(ISession.IMainPanelTabIndexes.SQL_TAB); hideAbortFrame(); } }); } }); showAbortFrame(); } private void genCreate(ResultSet srcResult, String sTable, StringBuffer sbCreate) { try { ResultSetMetaData metaData = srcResult.getMetaData(); sbCreate.append("\n\nCREATE TABLE ").append(sTable).append('\n'); sbCreate.append("(\n"); ScriptUtil su = new ScriptUtil(); String sColName = metaData.getColumnName(1); String sColType = metaData.getColumnTypeName(1); int colSize = metaData.getColumnDisplaySize(1); int decimalDigits = metaData.getScale(1); sbCreate.append(" ").append(su.getColumnDef(sColName, sColType, colSize, decimalDigits)); for(int i=2; i <= metaData.getColumnCount(); ++i) { sbCreate.append(",\n"); sColName = metaData.getColumnName(i); sColType = metaData.getColumnTypeName(i); colSize = metaData.getColumnDisplaySize(i); decimalDigits = metaData.getScale(i); sbCreate.append(" ").append(su.getColumnDef(sColName, sColType, colSize, decimalDigits)); } sbCreate.append("\n)"); } catch (SQLException e) { throw new RuntimeException(e); } } private String getTableName() { return "ygwaTest"; } private String getNextToken(String selectSQL, int startPos) { int curPos = startPos; while(curPos < selectSQL.length() && true == Character.isWhitespace(selectSQL.charAt(curPos))) { // Move over leading whitespaces ++curPos; } int startPosTrimed = curPos; while(curPos < selectSQL.length() && false == Character.isWhitespace(selectSQL.charAt(curPos))) { ++curPos; } return selectSQL.substring(startPosTrimed, curPos); } private int getTokenBeginIndex(String selectSQL, String token) { String lowerSel = selectSQL.toLowerCase(); String lowerToken = token.toLowerCase().trim(); int curPos = 0; while(-1 != curPos) { curPos = lowerSel.indexOf(lowerToken); if( -1 < curPos && (0 == curPos || Character.isWhitespace(lowerSel.charAt(curPos-1))) && (lowerSel.length() == curPos + lowerToken.length() || Character.isWhitespace(lowerSel.charAt(curPos + lowerToken.length()))) ) { return curPos; } } return curPos; } } --- NEW FILE: CreateTableOfCurrentSQLCtrl.java --- package net.sourceforge.squirrel_sql.plugins.sqlscript.table_script; import net.sourceforge.squirrel_sql.client.session.ISession; import net.sourceforge.squirrel_sql.fw.gui.GUIUtils; import java.awt.event.ActionListener; import java.awt.event.ActionEvent; import java.util.prefs.Preferences; public class CreateTableOfCurrentSQLCtrl { private CreateTableOfCurrentSQLDialog _dlg; private boolean _isOk; private static final String PREFS_KEY_LAST_TABLE_NAME = "squirrel_sqlscript_tempSqlResultTable"; private static final String PREFS_KEY_SCRIPT_ONLY = "squirrel_sqlscript_script_only"; private static final String PREFS_KEY_DROP_TABLE = "squirrel_sqlscript_drop_table"; public CreateTableOfCurrentSQLCtrl(ISession session) { _dlg = new CreateTableOfCurrentSQLDialog(session.getApplication().getMainFrame()); _dlg.btnOK.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { onOK(); } }); _dlg.btnCancel.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { onCancel(); } }); String tempSqlResultTable = Preferences.userRoot().get(PREFS_KEY_LAST_TABLE_NAME, "tempSqlResultTable"); boolean dropTable = Preferences.userRoot().getBoolean(PREFS_KEY_DROP_TABLE, false); boolean scriptOnly = Preferences.userRoot().getBoolean(PREFS_KEY_SCRIPT_ONLY, true); _dlg.txtTableName.setText(tempSqlResultTable); _dlg.chkDropTable.setSelected(dropTable); _dlg.chkScriptOnly.setSelected(scriptOnly); _dlg.setSize(360,160); GUIUtils.centerWithinParent(_dlg); _dlg.setVisible(true); } private void onCancel() { //System.out.println(_dlg.getSize()); close(); } private void onOK() { _isOk = true; Preferences.userRoot().put(PREFS_KEY_LAST_TABLE_NAME, _dlg.txtTableName.getText()); Preferences.userRoot().putBoolean(PREFS_KEY_DROP_TABLE, _dlg.chkDropTable.isSelected()); Preferences.userRoot().putBoolean(PREFS_KEY_SCRIPT_ONLY, _dlg.chkScriptOnly.isSelected()); close(); } private void close() { _dlg.setVisible(false); _dlg.dispose(); } public boolean isOK() { return _isOk; } public String getTableName() { return _dlg.txtTableName.getText(); } public boolean isScriptOnly() { return _dlg.chkScriptOnly.isSelected(); } public boolean isDropTable() { return _dlg.chkDropTable.isSelected(); } } |