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 */
017
018 package org.apache.geronimo.console.internaldb;
019
020 import org.apache.commons.logging.Log;
021 import org.apache.commons.logging.LogFactory;
022
023 import java.io.File;
024 import java.sql.Connection;
025 import java.sql.SQLException;
026 import java.sql.Statement;
027
028 public class RunSQLHelper {
029
030 private final static Log log = LogFactory.getLog(RunSQLHelper.class);
031
032 public static final String SQL_SUCCESS_MSG = "SQL command/s successful";
033
034 public static final String SQL_EMPTY_MSG = "SQL Command/s can't be empty";
035
036 private static final String DB_CREATED_MSG = "Database created";
037
038 private static final String DB_DELETED_MSG = "Database deleted";
039
040 private static final String DERBY_BACKUP_FOLDER = "derby.backup";
041
042 private static final String PARENT_FOLDER = "..";
043
044 private static final String BAK_EXTENSION = ".bak";
045
046 private static final String BAK_PREFIX = "BAK_";
047
048 public String createDB(String dbName) {
049 String result = DB_CREATED_MSG + ": " + dbName;
050
051 Connection conn = null;
052 try {
053 conn = DerbyConnectionUtil.getDerbyConnection(dbName,
054 DerbyConnectionUtil.CREATE_DB_PROP);
055 } catch (Throwable e) {
056 if (e instanceof SQLException) {
057 result = getSQLError((SQLException) e);
058 } else {
059 result = e.getMessage();
060 }
061 } finally {
062 // close DB connection
063 try {
064 if (conn != null) {
065 conn.close();
066 }
067 } catch (SQLException e) {
068 result = "Problem closing DB connection";
069 }
070 }
071
072 return result;
073 }
074
075 public String backupDB(String derbyHome, String dbName) {
076 return "";
077 }
078
079 public String restoreDB(String derbyHome, String dbName) {
080 return "";
081 }
082
083 public String deleteDB(String derbyHome, String dbName) {
084 String result = DB_DELETED_MSG + ": " + dbName;
085
086 // shutdown database before deleting it
087 if (!shutdownDB(dbName)) {
088 result = "Database not deleted: " + dbName
089 + " Couldn't shutdown db: " + dbName;
090 return result;
091 }
092
093 try {
094 // create backup folder if not created
095 File derbyBackupFolder = new File(derbyHome + File.separatorChar
096 + PARENT_FOLDER + 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 + dbName);
107 if (oldDBFolder.exists()) {
108 // Need to add a prefix because File.createTempFile's first
109 // argument must be a String at least three characters long.
110 File tmpFile = File.createTempFile(BAK_PREFIX + dbName,
111 BAK_EXTENSION, derbyBackupFolder);
112 File newDBFolder = new File(tmpFile.getAbsolutePath());
113 /*
114 * Delete temp file and create a temp folder using the temp
115 * filename
116 */
117 if (tmpFile.delete()) {
118 if (newDBFolder.mkdirs()) {
119 if (!oldDBFolder.renameTo(new File(newDBFolder,
120 oldDBFolder.getName()))) {
121 result = "Database not deleted: " + dbName
122 + " DB folder not renamed";
123 return result;
124 }
125 }
126 }
127 }
128 } catch (Exception e) {
129 e.printStackTrace();
130 }
131
132 return result;
133 }
134
135 public String runSQL(String dbName, String sql) {
136 String result = SQL_SUCCESS_MSG;
137
138 if ((sql == null) || (sql.trim().length() == 0)) {
139 result = SQL_EMPTY_MSG;
140 return result;
141 }
142
143 Connection conn = null;
144 Statement s = null;
145 try {
146
147 conn = DerbyConnectionUtil.getDerbyConnection(dbName);
148 conn.setAutoCommit(false);
149
150 s = conn.createStatement();
151 String[] sqlCmds = sql.split(";");
152 for (int i = 0; i < sqlCmds.length; i++) {
153 if (sqlCmds[i].trim().length() > 0) {
154 // debug printout (remove later)
155 log.debug("SQL" + i + ": <" + sqlCmds[i].trim() + ">");
156 s.execute(sqlCmds[i]);
157 }
158 }
159 conn.commit();
160 } catch (Throwable e) {
161 if (e instanceof SQLException) {
162 result = getSQLError((SQLException) e);
163 } else {
164 result = e.getMessage();
165 }
166 } finally {
167 // close DB connection
168 try {
169 if (s != null) {
170 s.close();
171 }
172 if (conn != null) {
173 conn.close();
174 }
175 } catch (SQLException e) {
176 if (SQL_SUCCESS_MSG.equals(result)) {
177 result = "Problem closing DB connection: " + e.getMessage();
178 }
179 }
180 }
181
182 return result;
183 }
184
185 private boolean shutdownDB(String dbName) {
186 boolean ok = true;
187
188 boolean gotSQLExc = false;
189 try {
190 DerbyConnectionUtil.getDerbyConnection(dbName,
191 DerbyConnectionUtil.SHUTDOWN_DB_PROP);
192 } catch (SQLException se) {
193 gotSQLExc = true;
194 }
195
196 if (!gotSQLExc) {
197 ok = false;
198 }
199
200 return ok;
201 }
202
203 private String getSQLError(SQLException e) {
204 StringBuffer errorMsg = new StringBuffer();
205 while (e != null) {
206 //errorMsg.append(e.toString());
207 errorMsg.append(e.getMessage());
208 errorMsg.append(" * ");
209 e = e.getNextException();
210 }
211
212 return errorMsg.toString();
213 }
214
215 public static void main(String[] args) {
216 new RunSQLHelper().runSQL("derbyDB4",
217 "create table derbyTbl1(num int, addr varchar(40));"
218 + "create table derbyTbl2(num int, addr varchar(40));"
219 + "create table derbyTbl3(num int, addr varchar(40));"
220 + "insert into derb");
221 }
222 }