KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > apache > derbyTesting > functionTests > harness > dbcleanup


1 /*
2
3    Derby - Class org.apache.derbyTesting.functionTests.harness.dbcleanup
4
5    Licensed to the Apache Software Foundation (ASF) under one or more
6    contributor license agreements. See the NOTICE file distributed with
7    this work for additional information regarding copyright ownership.
8    The ASF licenses this file to You under the Apache License, Version 2.0
9    (the "License"); you may not use this file except in compliance with
10    the License. You may obtain a copy of the License at
11
12       http://www.apache.org/licenses/LICENSE-2.0
13
14    Unless required by applicable law or agreed to in writing, software
15    distributed under the License is distributed on an "AS IS" BASIS,
16    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17    See the License for the specific language governing permissions and
18    limitations under the License.
19
20  */
21
22 package org.apache.derbyTesting.functionTests.harness;
23
24 import java.sql.Connection JavaDoc;
25 import java.sql.DriverManager JavaDoc;
26 import java.sql.Statement JavaDoc;
27 import java.sql.PreparedStatement JavaDoc;
28 import java.sql.ResultSet JavaDoc;
29 import java.sql.ResultSetMetaData JavaDoc;
30 import java.sql.SQLException JavaDoc;
31 import java.sql.SQLWarning JavaDoc;
32 import java.io.*;
33 import java.util.*;
34 import java.lang.Long JavaDoc;
35 import java.util.Vector JavaDoc;
36
37 import org.apache.derby.tools.JDBCDisplayUtil;
38
39 /*
40  **
41  ** dbcleanup
42  **
43  ** Preliminary version:
44  ** gets rid of all the items in a database except those that
45  ** are present when a fresh database is created. There are
46  ** some gaps still-- sync objects, and I have not done SYSFILES.
47  ** I have probably missed other things as well. At present this
48  ** is hardwired for jdbc:derby:wombat, the focus of our
49  ** attention in the embedded tests.
50  **
51  */
52 public class dbcleanup {
53
54     static String JavaDoc dbURL = "jdbc:derby:wombat";
55     static String JavaDoc driver = "org.apache.derby.jdbc.EmbeddedDriver";
56     static boolean dbIsDirty = false;
57
58     int thread_id;
59     int ind = 0;
60
61     public static void main(String JavaDoc[] args) throws SQLException JavaDoc, IOException,
62         InterruptedException JavaDoc, Exception JavaDoc {
63         doit(true);
64     }
65
66     public static void doit(boolean dbIsNew) throws SQLException JavaDoc, IOException,
67         InterruptedException JavaDoc, Exception JavaDoc {
68
69         Connection JavaDoc conn = null;
70         Statement JavaDoc s = null;
71         ResultSet JavaDoc rs = null;
72         boolean finished = false;
73         Date d = new Date();
74
75             Properties dbclProps = System.getProperties();
76         String JavaDoc systemHome = dbclProps.getProperty("user.dir") + File.separatorChar +
77             "testCSHome";
78             dbclProps.put("derby.system.home", systemHome);
79             System.setProperties(dbclProps);
80
81         boolean useprocess = true;
82         String JavaDoc up = dbclProps.getProperty("useprocess");
83         if (up != null && up.equals("false"))
84             useprocess = false;
85         
86             PrintStream stdout = System.out;
87             PrintStream stderr = System.err;
88
89         Class.forName(driver).newInstance();
90
91         if (dbIsNew) {
92         try {
93             conn = DriverManager.getConnection(dbURL +
94                 ";create=true");
95             conn.setAutoCommit(false);
96             System.out.println("created " + dbURL + " " + d);
97 //FIX: temporarily we will always cleanup, so skip the shutdown
98 // conn.close();
99 // shutdown required only if 2 processes access database
100 // if (useprocess) doshutdown();
101 // return;
102 } catch (SQLException JavaDoc se) {
103             System.out.println("connect failed for " + dbURL);
104             JDBCDisplayUtil.ShowException(System.out, se);
105             System.exit(1);
106         }
107         }
108         else {
109         try {
110             conn = DriverManager.getConnection(dbURL);
111             conn.setAutoCommit(false);
112             System.out.println("connected to " + dbURL + " " + d);
113         } catch (SQLException JavaDoc se) {
114             System.out.println("connect failed for " + dbURL);
115             JDBCDisplayUtil.ShowException(System.out, se);
116             System.exit(1);
117         }
118         }
119
120         d = new Date();
121         System.out.println("dbcleanup starting: " + d);
122
123         Enumeration schemalist = null;
124         Enumeration list = null;
125         Vector JavaDoc schemavec = new Vector JavaDoc();
126         Vector JavaDoc tablevec = null;
127         // get a list of the user schemas
128 try {
129             s = conn.createStatement();
130             rs = s.executeQuery( " select schemaname from sys.sysschemas " +
131                 " where schemaname <> 'SYS'");
132             while (rs.next()) {
133                 schemavec.addElement(new String JavaDoc(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 JavaDoc se) {
141             System.out.println("select schemas: FAIL -- unexpected exception:");
142             JDBCDisplayUtil.ShowException(System.out, se);
143             System.exit(1);
144         }
145
146         // for each user schema, drop the objects
147 String JavaDoc schema = null;
148         String JavaDoc n = null;
149         boolean viewdependencyFound = false;
150         boolean tabledependencyFound = false;
151         Vector JavaDoc viewvec = null;
152         int count = 0;
153         for (schemalist = schemavec.elements(); schemalist.hasMoreElements();) {
154             schema = (String JavaDoc)schemalist.nextElement();
155             for (viewdependencyFound = true; viewdependencyFound;){
156                 viewdependencyFound = false;
157                 viewvec = findTables(conn, s, 'V', schema);
158                 //for (list = viewvec.elements(); list.hasMoreElements();)
159 // System.out.println("\t" + list.nextElement());
160 if (viewvec.size() > 0) {
161                     System.out.println("schema " + schema);
162                     viewdependencyFound = dropTables(conn, s, viewvec, "view");
163                 }
164             }
165
166             for (tabledependencyFound = true; tabledependencyFound;){
167                 tabledependencyFound = false;
168                 tablevec = findTables(conn, s, 'T', schema);
169                 if (tablevec.size() > 0) {
170                     System.out.println("schema " + schema);
171                     tabledependencyFound =
172                         dropTables(conn, s, tablevec, "table");
173                 }
174             }
175
176             Vector JavaDoc stmtvec = new Vector JavaDoc();
177             try {
178                 rs = s.executeQuery( " select stmtname " +
179                     " from sys.sysstatements t, sys.sysschemas s " +
180                     " where t.schemaid = s.schemaid " +
181                     " and s.schemaname = '" + schema + "'");
182                 for (count = 0; rs.next(); count++) {
183                     dbIsDirty = true;
184                     stmtvec.addElement(new String JavaDoc(rs.getString(1)));
185                 }
186                 rs.close();
187             } catch (SQLException JavaDoc se) {
188                 System.out.println("select statements: FAIL -- unexpected exception:");
189                 JDBCDisplayUtil.ShowException(System.out, se);
190                 System.exit(1);
191             }
192
193             if (count > 1) {
194             try {
195                 System.out.println("schema " + schema);
196                 System.out.println("dropping leftover statements: ");
197                 for (list = stmtvec.elements(); list.hasMoreElements();) {
198                     n = (String JavaDoc)list.nextElement();
199                     s.execute("drop statement " + n);
200                     conn.commit();
201                     System.out.println("\t" + n);
202                 }
203             } catch (SQLException JavaDoc se) {
204                 System.out.println("drop statement: FAIL -- unexpected exception:");
205                 JDBCDisplayUtil.ShowException(System.out, se);
206                 System.exit(1);
207             }
208             }
209         }
210         // drop every user schema except APP
211 if (schemavec.size() > 1) {
212         System.out.println("dropping extra user schemas: ");
213         schemalist = null;
214         for (schemalist = schemavec.elements(); schemalist.hasMoreElements();) {
215             schema = (String JavaDoc)schemalist.nextElement();
216             if (schema.equals("APP")) continue;
217             if (schema == null) {
218                 System.out.println("null schema in schemalist");
219                 continue;
220             }
221             try {
222                 System.out.println("\t" + schema);
223                 s.execute("drop schema \"" + schema + "\"");
224             } catch (SQLException JavaDoc se) {
225                 System.out.println("drop schema: FAIL -- unexpected exception:");
226                 JDBCDisplayUtil.ShowException(System.out, se);
227                 System.exit(1);
228             }
229         }
230         }
231         // drop all method aliases
232 dropAliases(conn, 'M');
233         dropAliases(conn, 'C');
234
235         // DEBUG: help figure out what's going on with extra entries in sysdepends
236 try {
237             rs = s.executeQuery("select count (*) from sys.sysdepends");
238             if (rs.next()) {
239                 int i = rs.getInt(1);
240                 if (i > 0)
241                     System.out.println("found " + i + " leftover dependencies");
242             }
243         } catch (SQLException JavaDoc se) {
244             System.out.println("drop schema: FAIL -- unexpected exception:");
245             JDBCDisplayUtil.ShowException(System.out, se);
246             System.exit(1);
247         }
248
249         // shutdown required only if 2 processes access database
250 if (useprocess) doshutdown();
251         //conn.close();
252 d = new Date();
253         System.out.println("dbcleanup finished: " + d);
254     }
255
256     static void doshutdown() {
257         Connection JavaDoc conn = null;
258         try {
259             conn = DriverManager.getConnection(dbURL +
260                 ";shutdown=true");
261         } catch (SQLException JavaDoc se) {
262             if (se.getSQLState().equals("08006")){
263                 System.out.println("shutting down " + dbURL);
264             }
265             else {
266                 System.out.println("shutdown failed for " + dbURL);
267                 JDBCDisplayUtil.ShowException(System.out, se);
268                 System.exit(1);
269             }
270         }
271     }
272
273     static boolean dropTables(Connection JavaDoc conn, Statement JavaDoc s, Vector JavaDoc tablevec,
274         String JavaDoc tabletype) throws Exception JavaDoc {
275
276         boolean dependencyFound = false;
277         String JavaDoc n = null;
278
279         String JavaDoc objtype = null;
280         System.out.println("dropping " + tabletype + "(s)");
281
282         for (Enumeration list = tablevec.elements(); list.hasMoreElements();) {
283             n = (String JavaDoc)list.nextElement();
284             try {
285                 s.execute("drop " + tabletype + " " + n);
286                 conn.commit();
287                 System.out.println("\t" + n);
288             } catch (SQLException JavaDoc se) {
289                 if (se.getSQLState().equals("X0Y25")){
290                     dependencyFound=true;
291                     //System.out.println("error X0Y25: " + se.getMessage());
292 System.out.println(n + " not droped due to dependency, will retry a bit later");
293                 }
294                 else if (se.getSQLState().equals("X0Y23")){
295                     dependencyFound=true;
296                     //System.out.println("error X0Y23: " + se.getMessage());
297 System.out.println(n + " not droped due to dependency, will retry a bit later");
298                 }
299                 else {
300                     System.out.println("drop table: FAIL -- unexpected exception:");
301                     JDBCDisplayUtil.ShowException(System.out, se);
302                     System.exit(1);
303         //FIX exits
304 }
305             }
306         }
307         return(dependencyFound);
308     }
309
310     static Vector JavaDoc findTables(Connection JavaDoc conn, Statement JavaDoc s, char c, String JavaDoc schema) throws Exception JavaDoc {
311
312         ResultSet JavaDoc rs = null;
313         Vector JavaDoc tableviewvec = new Vector JavaDoc();
314
315         try {
316             rs = s.executeQuery( " select t.tablename " +
317                 " from sys.systables t, sys.sysschemas s " +
318                 " where t.schemaid = s.schemaid " +
319                 " and t.tabletype = '" + c + "'" +
320                 " and s.schemaname = '" + schema + "'" );
321             while (rs.next()) {
322                 dbIsDirty = true;
323                 tableviewvec.addElement(new String JavaDoc(rs.getString(1)));
324             }
325             rs.close();
326         } catch (SQLException JavaDoc se) {
327             System.out.println("select tables: FAIL -- unexpected exception:");
328             JDBCDisplayUtil.ShowException(System.out, se);
329             System.exit(1);
330         //FIX exits
331 }
332         return(tableviewvec);
333     }
334
335     static void dropAliases (Connection JavaDoc conn, char aliastype) throws Exception JavaDoc {
336         
337         ResultSet JavaDoc rs = null;
338         Statement JavaDoc s = null;
339         String JavaDoc typestring = null;
340         Vector JavaDoc aliasvec = new Vector JavaDoc();
341         String JavaDoc n = null;
342         int count = 0;
343
344         if (aliastype == 'M') typestring = "method";
345         else if (aliastype == 'C') typestring = "class";
346
347         try {
348             s = conn.createStatement();
349             rs = s.executeQuery("select alias, aliastype from sys.sysaliases " +
350                 " where systemalias = false " +
351                 " and aliastype = '" + aliastype + "'");
352             for (count = 0; rs.next(); count++) {
353                 dbIsDirty = true;
354                 aliasvec.addElement(new String JavaDoc(rs.getString(1)));
355             }
356             rs.close();
357             conn.commit();
358         } catch (SQLException JavaDoc se) {
359             System.out.println("drop alias: FAIL -- unexpected exception:");
360             JDBCDisplayUtil.ShowException(System.out, se);
361             System.exit(1);
362         }
363
364         if (count > 1) {
365         System.out.println("dropping user aliases, type " + typestring + ": ");
366         for (Enumeration list = aliasvec.elements(); list.hasMoreElements();) {
367             n = (String JavaDoc)list.nextElement();
368             try {
369                 s.execute("drop " + typestring + " alias " + n);
370             } catch (SQLException JavaDoc se) {
371                 System.out.println("drop alias: FAIL -- unexpected exception:");
372                 JDBCDisplayUtil.ShowException(System.out, se);
373                 System.exit(1);
374             }
375             conn.commit();
376             System.out.println("\t" + n);
377         }
378         }
379     }
380 }
381
Popular Tags