001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.harness.dbcleanup
004:
005: Licensed to the Apache Software Foundation (ASF) under one or more
006: contributor license agreements. See the NOTICE file distributed with
007: this work for additional information regarding copyright ownership.
008: The ASF licenses this file to You under the Apache License, Version 2.0
009: (the "License"); you may not use this file except in compliance with
010: the License. You may obtain a copy of the License at
011:
012: http://www.apache.org/licenses/LICENSE-2.0
013:
014: Unless required by applicable law or agreed to in writing, software
015: distributed under the License is distributed on an "AS IS" BASIS,
016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
017: See the License for the specific language governing permissions and
018: limitations under the License.
019:
020: */
021:
022: package org.apache.derbyTesting.functionTests.harness;
023:
024: import java.sql.Connection;
025: import java.sql.DriverManager;
026: import java.sql.Statement;
027: import java.sql.PreparedStatement;
028: import java.sql.ResultSet;
029: import java.sql.ResultSetMetaData;
030: import java.sql.SQLException;
031: import java.sql.SQLWarning;
032: import java.io.*;
033: import java.util.*;
034: import java.lang.Long;
035: import java.util.Vector;
036:
037: import org.apache.derby.tools.JDBCDisplayUtil;
038:
039: /*
040: **
041: ** dbcleanup
042: **
043: ** Preliminary version:
044: ** gets rid of all the items in a database except those that
045: ** are present when a fresh database is created. There are
046: ** some gaps still-- sync objects, and I have not done SYSFILES.
047: ** I have probably missed other things as well. At present this
048: ** is hardwired for jdbc:derby:wombat, the focus of our
049: ** attention in the embedded tests.
050: **
051: */
052: public class dbcleanup {
053:
054: static String dbURL = "jdbc:derby:wombat";
055: static String driver = "org.apache.derby.jdbc.EmbeddedDriver";
056: static boolean dbIsDirty = false;
057:
058: int thread_id;
059: int ind = 0;
060:
061: public static void main(String[] args) throws SQLException,
062: IOException, InterruptedException, Exception {
063: doit(true);
064: }
065:
066: public static void doit(boolean dbIsNew) throws SQLException,
067: IOException, InterruptedException, Exception {
068:
069: Connection conn = null;
070: Statement s = null;
071: ResultSet rs = null;
072: boolean finished = false;
073: Date d = new Date();
074:
075: Properties dbclProps = System.getProperties();
076: String systemHome = dbclProps.getProperty("user.dir")
077: + File.separatorChar + "testCSHome";
078: dbclProps.put("derby.system.home", systemHome);
079: System.setProperties(dbclProps);
080:
081: boolean useprocess = true;
082: String up = dbclProps.getProperty("useprocess");
083: if (up != null && up.equals("false"))
084: useprocess = false;
085:
086: PrintStream stdout = System.out;
087: PrintStream stderr = System.err;
088:
089: Class.forName(driver).newInstance();
090:
091: if (dbIsNew) {
092: try {
093: conn = DriverManager.getConnection(dbURL
094: + ";create=true");
095: conn.setAutoCommit(false);
096: System.out.println("created " + dbURL + " " + d);
097: //FIX: temporarily we will always cleanup, so skip the shutdown
098: // conn.close();
099: // shutdown required only if 2 processes access database
100: // if (useprocess) doshutdown();
101: // return;
102: } catch (SQLException se) {
103: System.out.println("connect failed for " + dbURL);
104: JDBCDisplayUtil.ShowException(System.out, se);
105: System.exit(1);
106: }
107: } else {
108: try {
109: conn = DriverManager.getConnection(dbURL);
110: conn.setAutoCommit(false);
111: System.out.println("connected to " + dbURL + " " + d);
112: } catch (SQLException se) {
113: System.out.println("connect failed for " + dbURL);
114: JDBCDisplayUtil.ShowException(System.out, se);
115: System.exit(1);
116: }
117: }
118:
119: d = new Date();
120: System.out.println("dbcleanup starting: " + d);
121:
122: Enumeration schemalist = null;
123: Enumeration list = null;
124: Vector schemavec = new Vector();
125: Vector tablevec = null;
126: // get a list of the user schemas
127: try {
128: s = conn.createStatement();
129: rs = s
130: .executeQuery(" select schemaname from sys.sysschemas "
131: + " where schemaname <> 'SYS'");
132: while (rs.next()) {
133: schemavec.addElement(new String(rs.getString(1)));
134: }
135: rs.close();
136: if (schemavec.size() > 1) {
137: // there is at least one schema to clean up
138: dbIsDirty = true;
139: }
140: } catch (SQLException se) {
141: System.out
142: .println("select schemas: FAIL -- unexpected exception:");
143: JDBCDisplayUtil.ShowException(System.out, se);
144: System.exit(1);
145: }
146:
147: // for each user schema, drop the objects
148: String schema = null;
149: String n = null;
150: boolean viewdependencyFound = false;
151: boolean tabledependencyFound = false;
152: Vector viewvec = null;
153: int count = 0;
154: for (schemalist = schemavec.elements(); schemalist
155: .hasMoreElements();) {
156: schema = (String) schemalist.nextElement();
157: for (viewdependencyFound = true; viewdependencyFound;) {
158: viewdependencyFound = false;
159: viewvec = findTables(conn, s, 'V', schema);
160: //for (list = viewvec.elements(); list.hasMoreElements();)
161: // System.out.println("\t" + list.nextElement());
162: if (viewvec.size() > 0) {
163: System.out.println("schema " + schema);
164: viewdependencyFound = dropTables(conn, s, viewvec,
165: "view");
166: }
167: }
168:
169: for (tabledependencyFound = true; tabledependencyFound;) {
170: tabledependencyFound = false;
171: tablevec = findTables(conn, s, 'T', schema);
172: if (tablevec.size() > 0) {
173: System.out.println("schema " + schema);
174: tabledependencyFound = dropTables(conn, s,
175: tablevec, "table");
176: }
177: }
178:
179: Vector stmtvec = new Vector();
180: try {
181: rs = s
182: .executeQuery(" select stmtname "
183: + " from sys.sysstatements t, sys.sysschemas s "
184: + " where t.schemaid = s.schemaid "
185: + " and s.schemaname = '" + schema
186: + "'");
187: for (count = 0; rs.next(); count++) {
188: dbIsDirty = true;
189: stmtvec.addElement(new String(rs.getString(1)));
190: }
191: rs.close();
192: } catch (SQLException se) {
193: System.out
194: .println("select statements: FAIL -- unexpected exception:");
195: JDBCDisplayUtil.ShowException(System.out, se);
196: System.exit(1);
197: }
198:
199: if (count > 1) {
200: try {
201: System.out.println("schema " + schema);
202: System.out
203: .println("dropping leftover statements: ");
204: for (list = stmtvec.elements(); list
205: .hasMoreElements();) {
206: n = (String) list.nextElement();
207: s.execute("drop statement " + n);
208: conn.commit();
209: System.out.println("\t" + n);
210: }
211: } catch (SQLException se) {
212: System.out
213: .println("drop statement: FAIL -- unexpected exception:");
214: JDBCDisplayUtil.ShowException(System.out, se);
215: System.exit(1);
216: }
217: }
218: }
219: // drop every user schema except APP
220: if (schemavec.size() > 1) {
221: System.out.println("dropping extra user schemas: ");
222: schemalist = null;
223: for (schemalist = schemavec.elements(); schemalist
224: .hasMoreElements();) {
225: schema = (String) schemalist.nextElement();
226: if (schema.equals("APP"))
227: continue;
228: if (schema == null) {
229: System.out.println("null schema in schemalist");
230: continue;
231: }
232: try {
233: System.out.println("\t" + schema);
234: s.execute("drop schema \"" + schema + "\"");
235: } catch (SQLException se) {
236: System.out
237: .println("drop schema: FAIL -- unexpected exception:");
238: JDBCDisplayUtil.ShowException(System.out, se);
239: System.exit(1);
240: }
241: }
242: }
243: // drop all method aliases
244: dropAliases(conn, 'M');
245: dropAliases(conn, 'C');
246:
247: // DEBUG: help figure out what's going on with extra entries in sysdepends
248: try {
249: rs = s.executeQuery("select count (*) from sys.sysdepends");
250: if (rs.next()) {
251: int i = rs.getInt(1);
252: if (i > 0)
253: System.out.println("found " + i
254: + " leftover dependencies");
255: }
256: } catch (SQLException se) {
257: System.out
258: .println("drop schema: FAIL -- unexpected exception:");
259: JDBCDisplayUtil.ShowException(System.out, se);
260: System.exit(1);
261: }
262:
263: // shutdown required only if 2 processes access database
264: if (useprocess)
265: doshutdown();
266: //conn.close();
267: d = new Date();
268: System.out.println("dbcleanup finished: " + d);
269: }
270:
271: static void doshutdown() {
272: Connection conn = null;
273: try {
274: conn = DriverManager
275: .getConnection(dbURL + ";shutdown=true");
276: } catch (SQLException se) {
277: if (se.getSQLState().equals("08006")) {
278: System.out.println("shutting down " + dbURL);
279: } else {
280: System.out.println("shutdown failed for " + dbURL);
281: JDBCDisplayUtil.ShowException(System.out, se);
282: System.exit(1);
283: }
284: }
285: }
286:
287: static boolean dropTables(Connection conn, Statement s,
288: Vector tablevec, String tabletype) throws Exception {
289:
290: boolean dependencyFound = false;
291: String n = null;
292:
293: String objtype = null;
294: System.out.println("dropping " + tabletype + "(s)");
295:
296: for (Enumeration list = tablevec.elements(); list
297: .hasMoreElements();) {
298: n = (String) list.nextElement();
299: try {
300: s.execute("drop " + tabletype + " " + n);
301: conn.commit();
302: System.out.println("\t" + n);
303: } catch (SQLException se) {
304: if (se.getSQLState().equals("X0Y25")) {
305: dependencyFound = true;
306: //System.out.println("error X0Y25: " + se.getMessage());
307: System.out
308: .println(n
309: + " not droped due to dependency, will retry a bit later");
310: } else if (se.getSQLState().equals("X0Y23")) {
311: dependencyFound = true;
312: //System.out.println("error X0Y23: " + se.getMessage());
313: System.out
314: .println(n
315: + " not droped due to dependency, will retry a bit later");
316: } else {
317: System.out
318: .println("drop table: FAIL -- unexpected exception:");
319: JDBCDisplayUtil.ShowException(System.out, se);
320: System.exit(1);
321: //FIX exits
322: }
323: }
324: }
325: return (dependencyFound);
326: }
327:
328: static Vector findTables(Connection conn, Statement s, char c,
329: String schema) throws Exception {
330:
331: ResultSet rs = null;
332: Vector tableviewvec = new Vector();
333:
334: try {
335: rs = s.executeQuery(" select t.tablename "
336: + " from sys.systables t, sys.sysschemas s "
337: + " where t.schemaid = s.schemaid "
338: + " and t.tabletype = '" + c + "'"
339: + " and s.schemaname = '" + schema + "'");
340: while (rs.next()) {
341: dbIsDirty = true;
342: tableviewvec.addElement(new String(rs.getString(1)));
343: }
344: rs.close();
345: } catch (SQLException se) {
346: System.out
347: .println("select tables: FAIL -- unexpected exception:");
348: JDBCDisplayUtil.ShowException(System.out, se);
349: System.exit(1);
350: //FIX exits
351: }
352: return (tableviewvec);
353: }
354:
355: static void dropAliases(Connection conn, char aliastype)
356: throws Exception {
357:
358: ResultSet rs = null;
359: Statement s = null;
360: String typestring = null;
361: Vector aliasvec = new Vector();
362: String n = null;
363: int count = 0;
364:
365: if (aliastype == 'M')
366: typestring = "method";
367: else if (aliastype == 'C')
368: typestring = "class";
369:
370: try {
371: s = conn.createStatement();
372: rs = s
373: .executeQuery("select alias, aliastype from sys.sysaliases "
374: + " where systemalias = false "
375: + " and aliastype = '" + aliastype + "'");
376: for (count = 0; rs.next(); count++) {
377: dbIsDirty = true;
378: aliasvec.addElement(new String(rs.getString(1)));
379: }
380: rs.close();
381: conn.commit();
382: } catch (SQLException se) {
383: System.out
384: .println("drop alias: FAIL -- unexpected exception:");
385: JDBCDisplayUtil.ShowException(System.out, se);
386: System.exit(1);
387: }
388:
389: if (count > 1) {
390: System.out.println("dropping user aliases, type "
391: + typestring + ": ");
392: for (Enumeration list = aliasvec.elements(); list
393: .hasMoreElements();) {
394: n = (String) list.nextElement();
395: try {
396: s.execute("drop " + typestring + " alias " + n);
397: } catch (SQLException se) {
398: System.out
399: .println("drop alias: FAIL -- unexpected exception:");
400: JDBCDisplayUtil.ShowException(System.out, se);
401: System.exit(1);
402: }
403: conn.commit();
404: System.out.println("\t" + n);
405: }
406: }
407: }
408: }
|