001: /**
002: * Licensed to the Apache Software Foundation (ASF) under one or more
003: * contributor license agreements. See the NOTICE file distributed with
004: * this work for additional information regarding copyright ownership.
005: * The ASF licenses this file to You under the Apache License, Version 2.0
006: * (the "License"); you may not use this file except in compliance with
007: * the License. You may obtain a copy of the License at
008: *
009: * http://www.apache.org/licenses/LICENSE-2.0
010: *
011: * Unless required by applicable law or agreed to in writing, software
012: * distributed under the License is distributed on an "AS IS" BASIS,
013: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014: * See the License for the specific language governing permissions and
015: * limitations under the License.
016: */package org.apache.geronimo.console.internaldb;
017:
018: import org.apache.commons.logging.Log;
019: import org.apache.commons.logging.LogFactory;
020:
021: import java.io.File;
022: import java.sql.Connection;
023: import java.sql.SQLException;
024: import java.sql.Statement;
025:
026: public class RunSQLHelper {
027:
028: private final static Log log = LogFactory
029: .getLog(RunSQLHelper.class);
030:
031: public static final String SQL_SUCCESS_MSG = "SQL command/s successful";
032:
033: public static final String SQL_EMPTY_MSG = "SQL Command/s can't be empty";
034:
035: private static final String DB_CREATED_MSG = "Database created";
036:
037: private static final String DB_DELETED_MSG = "Database deleted";
038:
039: private static final String DERBY_BACKUP_FOLDER = "derby.backup";
040:
041: private static final String PARENT_FOLDER = "..";
042:
043: private static final String BAK_EXTENSION = ".bak";
044:
045: private static final String BAK_PREFIX = "BAK_";
046:
047: public String createDB(String dbName) {
048: String result = DB_CREATED_MSG + ": " + dbName;
049:
050: Connection conn = null;
051: try {
052: conn = DerbyConnectionUtil.getDerbyConnection(dbName,
053: DerbyConnectionUtil.CREATE_DB_PROP);
054: } catch (Throwable e) {
055: if (e instanceof SQLException) {
056: result = getSQLError((SQLException) e);
057: } else {
058: result = e.getMessage();
059: }
060: } finally {
061: // close DB connection
062: try {
063: if (conn != null) {
064: conn.close();
065: }
066: } catch (SQLException e) {
067: result = "Problem closing DB connection";
068: }
069: }
070:
071: return result;
072: }
073:
074: public String backupDB(String derbyHome, String dbName) {
075: return "";
076: }
077:
078: public String restoreDB(String derbyHome, String dbName) {
079: return "";
080: }
081:
082: public String deleteDB(String derbyHome, String dbName) {
083: String result = DB_DELETED_MSG + ": " + dbName;
084:
085: // shutdown database before deleting it
086: if (!shutdownDB(dbName)) {
087: result = "Database not deleted: " + dbName
088: + " Couldn't shutdown db: " + dbName;
089: return result;
090: }
091:
092: try {
093: // create backup folder if not created
094: File derbyBackupFolder = new File(derbyHome
095: + File.separatorChar + PARENT_FOLDER
096: + File.separatorChar + DERBY_BACKUP_FOLDER);
097: if (!derbyBackupFolder.exists()) {
098: if (!derbyBackupFolder.mkdirs()) {
099: result = "Database not deleted: " + dbName
100: + " Derby backup folder not created: "
101: + derbyBackupFolder;
102: return result;
103: }
104: }
105:
106: File oldDBFolder = new File(derbyHome + File.separatorChar
107: + dbName);
108: if (oldDBFolder.exists()) {
109: // Need to add a prefix because File.createTempFile's first
110: // argument must be a String at least three characters long.
111: File tmpFile = File.createTempFile(BAK_PREFIX + dbName,
112: BAK_EXTENSION, derbyBackupFolder);
113: File newDBFolder = new File(tmpFile.getAbsolutePath());
114: /*
115: * Delete temp file and create a temp folder using the temp
116: * filename
117: */
118: if (tmpFile.delete()) {
119: if (newDBFolder.mkdirs()) {
120: if (!oldDBFolder.renameTo(new File(newDBFolder,
121: oldDBFolder.getName()))) {
122: result = "Database not deleted: " + dbName
123: + " DB folder not renamed";
124: return result;
125: }
126: }
127: }
128: }
129: } catch (Exception e) {
130: e.printStackTrace();
131: }
132:
133: return result;
134: }
135:
136: public String runSQL(String dbName, String sql) {
137: String result = SQL_SUCCESS_MSG;
138:
139: if ((sql == null) || (sql.trim().length() == 0)) {
140: result = SQL_EMPTY_MSG;
141: return result;
142: }
143:
144: Connection conn = null;
145: Statement s = null;
146: try {
147:
148: conn = DerbyConnectionUtil.getDerbyConnection(dbName);
149: conn.setAutoCommit(false);
150:
151: s = conn.createStatement();
152: String[] sqlCmds = sql.split(";");
153: for (int i = 0; i < sqlCmds.length; i++) {
154: if (sqlCmds[i].trim().length() > 0) {
155: // debug printout (remove later)
156: log.debug("SQL" + i + ": <" + sqlCmds[i].trim()
157: + ">");
158: s.execute(sqlCmds[i]);
159: }
160: }
161: conn.commit();
162: } catch (Throwable e) {
163: if (e instanceof SQLException) {
164: result = getSQLError((SQLException) e);
165: } else {
166: result = e.getMessage();
167: }
168: } finally {
169: // close DB connection
170: try {
171: if (s != null) {
172: s.close();
173: }
174: if (conn != null) {
175: conn.close();
176: }
177: } catch (SQLException e) {
178: if (SQL_SUCCESS_MSG.equals(result)) {
179: result = "Problem closing DB connection: "
180: + e.getMessage();
181: }
182: }
183: }
184:
185: return result;
186: }
187:
188: private boolean shutdownDB(String dbName) {
189: boolean ok = true;
190:
191: boolean gotSQLExc = false;
192: try {
193: DerbyConnectionUtil.getDerbyConnection(dbName,
194: DerbyConnectionUtil.SHUTDOWN_DB_PROP);
195: } catch (SQLException se) {
196: gotSQLExc = true;
197: }
198:
199: if (!gotSQLExc) {
200: ok = false;
201: }
202:
203: return ok;
204: }
205:
206: private String getSQLError(SQLException e) {
207: StringBuffer errorMsg = new StringBuffer();
208: while (e != null) {
209: //errorMsg.append(e.toString());
210: errorMsg.append(e.getMessage());
211: errorMsg.append(" * ");
212: e = e.getNextException();
213: }
214:
215: return errorMsg.toString();
216: }
217:
218: public static void main(String[] args) {
219: new RunSQLHelper()
220: .runSQL(
221: "derbyDB4",
222: "create table derbyTbl1(num int, addr varchar(40));"
223: + "create table derbyTbl2(num int, addr varchar(40));"
224: + "create table derbyTbl3(num int, addr varchar(40));"
225: + "insert into derb");
226: }
227: }
|