# HG changeset patch # User Matthias Bläsing # Date 1341752460 -7200 # Branch mb-patches2 # Node ID 5d62657d384a55ea06053b007dd33d8e53042947 # Parent 64d1a3983db6a348d59f57022bfcfcd334f9b49f * DerbyDBProvider complete method for dropping schemas * DefaultDBProvider: check that table exists before trying to drop it * DBTestBase: Don't emulate dropping schema by shutting down the derby database and deleting the database files by using the corrected DerbyDBProvider method * DBTestBase: Don't use the ConnectionManager to shutdown the derby database instead use the JDBC driver that is in turn used to create the DatabaseConnection in the first place diff --git a/db/test/unit/src/org/netbeans/modules/db/test/DBTestBase.java b/db/test/unit/src/org/netbeans/modules/db/test/DBTestBase.java --- a/db/test/unit/src/org/netbeans/modules/db/test/DBTestBase.java +++ b/db/test/unit/src/org/netbeans/modules/db/test/DBTestBase.java @@ -41,6 +41,7 @@ import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.Collection; +import java.util.Properties; import java.util.logging.Level; import java.util.logging.Logger; import org.netbeans.api.db.explorer.ConnectionManager; @@ -347,27 +348,8 @@ return; } - if (isDerby()) { - // Trying to remove the schema is very difficult, as it has to - // be completely empty. Easier just to blow away the database directory. - // Next time we connect a new db will be automatically created - shutdownDerby(); - if (! dblocation.equals(this.getWorkDirPath())) { - try { - clearWorkDir(); - } catch (IOException e) { - // ignore on Windows because some files might be locked - if (!Utilities.isWindows()) { - throw e; - } - } - } else { - deleteSubFiles(new File(dblocation)); - } - } else { dbProvider.dropSchema(getConnection(), getSchema()); } - } protected static void deleteSubFiles(File file) throws IOException { if (file.isDirectory() && file.exists()) { @@ -640,19 +622,16 @@ String url = dbUrl + ";shutdown=true"; url = url.replace(";create=true", ""); - DatabaseConnection conn = DatabaseConnection.create(getJDBCDriver(), - url, getSchema(), getUsername(), getPassword(), false); - - ConnectionManager.getDefault().addConnection(conn); + Properties p = new Properties(); + p.put("username", username); + p.put("password", password); // This forces the shutdown try { - ConnectionManager.getDefault().connect(conn); - } catch (DatabaseException dbe) { - // expected, this always happens when you shut it down + getJDBCDriver().getDriver().connect(url, p); + } catch (SQLException ex) { + // Exception is expected } - - ConnectionManager.getDefault().removeConnection(conn); } @Override diff --git a/db/test/unit/src/org/netbeans/modules/db/test/DefaultDBProvider.java b/db/test/unit/src/org/netbeans/modules/db/test/DefaultDBProvider.java --- a/db/test/unit/src/org/netbeans/modules/db/test/DefaultDBProvider.java +++ b/db/test/unit/src/org/netbeans/modules/db/test/DefaultDBProvider.java @@ -77,6 +77,9 @@ } public void dropTable(Connection conn, String schemaName, String tableName) throws Exception { + if(! tableExists(conn, schemaName, tableName)) { + return; + } try { conn.createStatement().executeUpdate("DROP TABLE " + schemaName + "." + tableName); } catch (SQLException sqle) { diff --git a/db/test/unit/src/org/netbeans/modules/db/test/DerbyDBProvider.java b/db/test/unit/src/org/netbeans/modules/db/test/DerbyDBProvider.java --- a/db/test/unit/src/org/netbeans/modules/db/test/DerbyDBProvider.java +++ b/db/test/unit/src/org/netbeans/modules/db/test/DerbyDBProvider.java @@ -39,20 +39,25 @@ * * Portions Copyrighted 2008 Sun Microsystems, Inc. */ - package org.netbeans.modules.db.test; -import java.sql.Connection; -import java.sql.DatabaseMetaData; -import java.sql.ResultSet; -import java.util.Iterator; -import java.util.Vector; +import java.sql.*; /** * * @author David */ public class DerbyDBProvider extends DefaultDBProvider { + + private String quote(String name, boolean identifier) { + String quote = identifier ? "\"" : "'"; + return quote + name.replace(quote, quote + quote) + quote; + } + + private String qualifiedName(String schema, String object) { + return quote(schema, true) + "." + quote(object, true); + } + @Override public void dropSchema(Connection conn, String schemaName) throws Exception { if (!schemaExists(conn, schemaName)) { @@ -64,45 +69,92 @@ // drop views first, as they depend on tables DatabaseMetaData md = conn.getMetaData(); + ResultSet rs; + Statement s = conn.createStatement(); - ResultSet rs = md.getTables(null, schemaName, null, - new String[] { "VIEW" } ); - Vector views = new Vector(); - while ( rs.next() ) { - String view = rs.getString(3); - views.add(view); + rs = md.getFunctions(null, schemaName, "%"); + + while(rs.next()) { + s.execute("DROP FUNCTION " + qualifiedName(schemaName, rs.getString("FUNCTION_NAME"))); } + rs.close(); - setSchema(conn, schemaName); - - Iterator it = views.iterator(); - while (it.hasNext()) { - String view = (String)it.next(); - dropView(conn, schemaName, view); + rs = md.getProcedures(null, schemaName, "%"); + + while(rs.next()) { + s.execute("DROP PROCEDURE " + qualifiedName(schemaName, rs.getString("PROCEDURE_NAME"))); } - // drop all tables - md = conn.getMetaData(); + rs.close(); - rs = md.getTables(null, schemaName, null, null); - Vector tables = new Vector(); + rs = md.getExportedKeys(null, schemaName, "%" ); + while ( rs.next() ) { - String table = rs.getString(3); - tables.add(table); + String referencingTable = qualifiedName(rs.getString("FKTABLE_SCHEMA"), + rs.getString("FKTABLE_NAME")); + s.execute("ALTER TABLE " + referencingTable + + " DROP FOREIGN KEY " + rs.getString("FK_NAME")); } + rs.close(); - setSchema(conn, schemaName); + rs = md.getTables(null, schemaName, "%", new String[] { "VIEW" } ); - it = tables.iterator(); - while (it.hasNext()) { - String table = (String)it.next(); - dropTable(conn, schemaName, table); + while ( rs.next() ) { + s.execute("DROP VIEW " + qualifiedName(schemaName, rs.getString("TABLE_NAME"))); } - // drop schema - conn.createStatement().executeUpdate("DROP SCHEMA " + schemaName + " RESTRICT"); + rs.close(); + rs = md.getTables(null, schemaName, "%", new String[] { "SYNONYM" } ); + + while ( rs.next() ) { + s.execute("DROP SYNONYM " + qualifiedName(schemaName, rs.getString("TABLE_NAME"))); + } + + rs.close(); + + rs = md.getTables(null, schemaName, "%", new String[] { "TABLE" } ); + + while ( rs.next() ) { + s.execute("DROP TABLE " + qualifiedName(schemaName, rs.getString("TABLE_NAME"))); + } + + rs.close(); + + rs = md.getUDTs(null, schemaName, "%", null); + + while (rs.next()) { + s.execute("DROP TYPE " + qualifiedName(schemaName, rs.getString("TYPE_NAME")) + + " RESTRICT "); + } + + rs.close(); + + try { + PreparedStatement psf = conn.prepareStatement( + "SELECT SEQUENCENAME FROM SYS.SYSSEQUENCES A, SYS.SYSSCHEMAS S" + + " WHERE A.SCHEMAID = S.SCHEMAID " + + " AND S.SCHEMANAME = ?"); + psf.setString(1, schemaName); + rs = psf.executeQuery(); + while (rs.next()) { + s.execute("DROP SEQUENCE " + qualifiedName(schemaName, rs.getString(1)) + " RESTRICT"); +} + psf.close(); + } catch (SQLException ex) { + // SQLState 42X05 => Table does not exists => OK, no sequences to drop + if(! ex.getSQLState().equals("42X05")) { + throw ex; + } + } + + if (!schemaName.equals("APP")) { + s.executeUpdate("DROP SCHEMA " + quote(schemaName, true) + " RESTRICT"); + } + + s.close(); } + }