1 /***
2 * Redistribution and use of this software and associated documentation
3 * ("Software"), with or without modification, are permitted provided
4 * that the following conditions are met:
5 *
6 * 1. Redistributions of source code must retain copyright
7 * statements and notices. Redistributions must also contain a
8 * copy of this document.
9 *
10 * 2. Redistributions in binary form must reproduce the
11 * above copyright notice, this list of conditions and the
12 * following disclaimer in the documentation and/or other
13 * materials provided with the distribution.
14 *
15 * 3. The name "Exolab" must not be used to endorse or promote
16 * products derived from this Software without prior written
17 * permission of Exoffice Technologies. For written permission,
18 * please contact info@exolab.org.
19 *
20 * 4. Products derived from this Software may not be called "Exolab"
21 * nor may "Exolab" appear in their names without prior written
22 * permission of Exoffice Technologies. Exolab is a registered
23 * trademark of Exoffice Technologies.
24 *
25 * 5. Due credit should be given to the Exolab Project
26 * (http://www.exolab.org/).
27 *
28 * THIS SOFTWARE IS PROVIDED BY EXOFFICE TECHNOLOGIES AND CONTRIBUTORS
29 * ``AS IS'' AND ANY EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT
30 * NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
31 * FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL
32 * EXOFFICE TECHNOLOGIES OR ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
33 * INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
34 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
35 * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
36 * HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT,
37 * STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
38 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED
39 * OF THE POSSIBILITY OF SUCH DAMAGE.
40 *
41 * Copyright 2001-2005 (C) Exoffice Technologies Inc. All Rights Reserved.
42 *
43 * $Id: RDBMSTool.java,v 1.4 2005/11/12 12:47:37 tanderson Exp $
44 */
45 package org.exolab.jms.tools.db;
46
47 import java.sql.Connection;
48 import java.sql.DriverManager;
49 import java.sql.SQLException;
50 import java.sql.Statement;
51
52 import org.apache.commons.logging.Log;
53 import org.apache.commons.logging.LogFactory;
54
55 import org.exolab.jms.config.Configuration;
56 import org.exolab.jms.config.RdbmsDatabaseConfiguration;
57 import org.exolab.jms.persistence.PersistenceException;
58 import org.exolab.jms.persistence.SQLHelper;
59
60
61 /***
62 * This class provides support for creating and destroying tables in RDBMS
63 * databases.
64 *
65 * @author <a href="mailto:tma@netspace.net.au">Tim Anderson</a>
66 * @version $Revision: 1.4 $ $Date: 2005/11/12 12:47:37 $
67 */
68 public class RDBMSTool {
69
70 /***
71 * The connection to the database.
72 */
73 private Connection _connection = null;
74
75 /***
76 * The schema browser.
77 */
78 private SchemaBrowser _browser = null;
79
80 /***
81 * The logger
82 */
83 private static final Log _log = LogFactory.getLog(RDBMSTool.class);
84
85
86 /***
87 * Construct a new <code>RDBMSTool</code>.
88 *
89 * @param connection the JDBC connection
90 * @throws PersistenceException if database meta-data can't be obtained
91 */
92 public RDBMSTool(Connection connection) throws PersistenceException {
93 init(connection);
94 }
95
96 /***
97 * Construct a new <code>RDBMSTool</code>.
98 *
99 * @param config the configuration
100 * @throws PersistenceException for any error
101 */
102 public RDBMSTool(Configuration config) throws PersistenceException {
103 RdbmsDatabaseConfiguration rdbms =
104 config.getDatabaseConfiguration()
105 .getRdbmsDatabaseConfiguration();
106 if (rdbms == null) {
107 throw new PersistenceException(
108 "Configuration not configured to use an RDBMS");
109 }
110 Connection connection = null;
111 try {
112 Class.forName(rdbms.getDriver());
113 connection = DriverManager.getConnection(rdbms.getUrl(),
114 rdbms.getUser(),
115 rdbms.getPassword());
116 } catch (SQLException exception) {
117 throw new PersistenceException(exception);
118 } catch (ClassNotFoundException exception) {
119 throw new PersistenceException(exception);
120 }
121 init(connection);
122 }
123
124 /***
125 * Determines if a set of tables are present in the dataase.
126 *
127 * @param tables the tables
128 * @return <code>true</code> if all tables are present
129 * @throws PersistenceException for any error
130 */
131 public boolean hasTables(Table[] tables) throws PersistenceException {
132 boolean result = true;
133 for (int i = 0; i < tables.length; ++i) {
134 if (!_browser.getTableExists(tables[i].getName())) {
135 result = false;
136 break;
137 }
138 }
139 return result;
140 }
141
142 /***
143 * Creates the database.
144 *
145 * @param schema the database schema
146 * @throws PersistenceException if elements cannot be created in the
147 * database
148 */
149 public void create(Database schema) throws PersistenceException {
150 Table[] tables = schema.getTable();
151 for (int i = 0; i < tables.length; ++i) {
152 create(tables[i]);
153 }
154 }
155
156 /***
157 * Drops tables from the database.
158 *
159 * @param schema the database schema
160 * @throws PersistenceException if elements cannot be dropped from the
161 * database
162 */
163 public void drop(Database schema) throws PersistenceException {
164 Table[] tables = schema.getTable();
165 for (int i = 0; i < tables.length; ++i) {
166 drop(tables[i]);
167 }
168 Deprecated[] redundant = schema.getDeprecated();
169 for (int i = 0; i < redundant.length; ++i) {
170 dropTable(redundant[i].getName());
171 }
172 }
173
174 /***
175 * Deletes data from all tables in the database.
176 *
177 * @param schema the database schema
178 * @throws PersistenceException if a table can't be truncated
179 */
180 public void delete(Database schema) throws PersistenceException {
181 Table[] tables = schema.getTable();
182 for (int i = 0; i < tables.length; ++i) {
183 deleteTable(tables[i].getName());
184 }
185 }
186
187 /***
188 * Close the connection to the database.
189 */
190 public void close() {
191 SQLHelper.close(_connection);
192 }
193
194 /***
195 * Creates a table in the database.
196 *
197 * @param table the table to create
198 * @throws PersistenceException if the table exists, or cannot be created
199 */
200 public void create(Table table) throws PersistenceException {
201 String name = table.getName();
202 if (_browser.getTableExists(name)) {
203 throw new PersistenceException(
204 "An object already exists in the database named " + name);
205 }
206
207 StringBuffer sql = new StringBuffer("create table ");
208 sql.append(name);
209 sql.append(" (");
210
211 _log.debug("Creating table: " + name);
212 Attribute[] attributes = table.getAttribute();
213 for (int i = 0; i < attributes.length; ++i) {
214 if (i > 0) {
215 sql.append(", ");
216 }
217 Attribute attribute = attributes[i];
218 sql.append(attribute.getName());
219 sql.append(" ");
220 sql.append(getSQLType(attribute));
221 if (attribute.getNotNull()) {
222 sql.append(" not null");
223 }
224 if (attribute.getPrimaryKey()) {
225 sql.append(" primary key");
226 }
227 if (attribute.getUnique()) {
228 sql.append(" unique");
229 }
230 }
231 PrimaryKey key = table.getPrimaryKey();
232 if (key != null) {
233 sql.append(", primary key (");
234 Column[] columns = key.getColumn();
235 for (int i = 0; i < columns.length; ++i) {
236 if (i > 0) {
237 sql.append(", ");
238 }
239 sql.append(columns[i].getName());
240 }
241 sql.append(")");
242 }
243 sql.append(")");
244
245 _log.debug("SQL=" + sql);
246 Statement statement = null;
247 try {
248 statement = _connection.createStatement();
249 statement.executeUpdate(sql.toString());
250 } catch (SQLException exception) {
251 throw new PersistenceException("Failed to create table=" + name,
252 exception);
253 } finally {
254 SQLHelper.close(statement);
255 }
256 createIndexes(table);
257 }
258
259 /***
260 * Drops a table from the database. If the table doesn't exist, then
261 * it will be ignored.
262 *
263 * @param table the table to drop
264 * @throws PersistenceException for any database error
265 */
266 public void drop(Table table) throws PersistenceException {
267 dropTable(table.getName());
268 }
269
270 /***
271 * Returns the schema browser.
272 *
273 * @return the schema browser
274 */
275 public SchemaBrowser getSchemaBrowser() {
276 return _browser;
277 }
278
279 /***
280 * Initialise this.
281 *
282 * @param connection the connection to use
283 * @throws PersistenceException for any error
284 *
285 */
286 private void init(Connection connection) throws PersistenceException {
287 _connection = connection;
288 try {
289 _connection.setAutoCommit(true);
290 } catch (SQLException exception) {
291 throw new PersistenceException("Failed to set auto-commit on",
292 exception);
293 }
294 _browser = new SchemaBrowser(_connection);
295 }
296
297 /***
298 * Create indexes for a table.
299 *
300 * @param table the table to add indexes for
301 * @throws PersistenceException
302 */
303 private void createIndexes(Table table) throws PersistenceException {
304 Index[] indexes = table.getIndex();
305 for (int i = 0; i < indexes.length; ++i) {
306 Index index = indexes[i];
307 StringBuffer sql = new StringBuffer("create ");
308 if (index.getUnique()) {
309 sql.append("unique ");
310 }
311 sql.append("index ");
312 sql.append(index.getName());
313 sql.append(" on ");
314 sql.append(table.getName());
315 sql.append("(");
316 Column[] columns = index.getColumn();
317 for (int j = 0; j < columns.length; ++j) {
318 if (j > 0) {
319 sql.append(", ");
320 }
321 sql.append(columns[j].getName());
322 }
323 sql.append(")");
324 _log.debug("SQL=" + sql);
325 Statement statement = null;
326 try {
327 statement = _connection.createStatement();
328 statement.executeUpdate(sql.toString());
329 } catch (SQLException exception) {
330 throw new PersistenceException("Failed to create index="
331 + index.getName()
332 + " on table "
333 + table.getName(), exception);
334 } finally {
335 SQLHelper.close(statement);
336 }
337 }
338 }
339
340 /***
341 * Drop a table.
342 *
343 * @param name the name of the table to drop
344 * @throws PersistenceException if the drop fails
345 */
346 private void dropTable(String name) throws PersistenceException {
347 if (_browser.getTableExists(name)) {
348 String sql = "drop table " + name;
349 _log.debug("SQL=" + sql);
350 Statement statement = null;
351 try {
352 statement = _connection.createStatement();
353 statement.executeUpdate(sql);
354 } catch (SQLException exception) {
355 throw new PersistenceException("Failed to drop table=" + name,
356 exception);
357 } finally {
358 SQLHelper.close(statement);
359 }
360 }
361 }
362
363 /***
364 * Deletes all data in a table.
365 *
366 * @param name the name of the table to delete from
367 * @throws PersistenceException if the delete fails
368 */
369 private void deleteTable(String name) throws PersistenceException {
370 if (_browser.getTableExists(name)) {
371 String sql = "delete from " + name;
372 _log.debug("SQL=" + sql);
373 Statement statement = null;
374 try {
375 statement = _connection.createStatement();
376 statement.execute(sql);
377 } catch (SQLException exception) {
378 throw new PersistenceException("Failed to delete from table="
379 + name, exception);
380 } finally {
381 SQLHelper.close(statement);
382 }
383 }
384 }
385
386 /***
387 * Returns the SQL type for a given attribute.
388 *
389 * @param attribute the attribute
390 * @return a string representation of the type
391 * @throws PersistenceException if {@link Attribute#getType} is invalid, or
392 * the RDBMS doesn't support the type
393 */
394 private String getSQLType(Attribute attribute)
395 throws PersistenceException {
396 Type result = _browser.getType(attribute);
397 _log.debug("attribute=" + attribute.getName() + "->" + result);
398 return result.getSQL();
399 }
400
401 }