001: /*
002: * Copyright 2001-2006 C:1 Financial Services GmbH
003: *
004: * This software is free software; you can redistribute it and/or
005: * modify it under the terms of the GNU Lesser General Public
006: * License Version 2.1, as published by the Free Software Foundation.
007: *
008: * This software is distributed in the hope that it will be useful,
009: * but WITHOUT ANY WARRANTY; without even the implied warranty of
010: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
011: * Lesser General Public License for more details.
012: *
013: * You should have received a copy of the GNU Lesser General Public
014: * License along with this library; if not, write to the Free Software
015: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307, USA
016: */
017:
018: package de.finix.contelligent.persistence;
019:
020: import java.io.BufferedReader;
021: import java.io.IOException;
022: import java.io.InputStreamReader;
023: import java.sql.CallableStatement;
024: import java.sql.Connection;
025: import java.sql.SQLException;
026: import java.sql.Statement;
027: import java.util.HashMap;
028: import java.util.Iterator;
029: import java.util.LinkedList;
030: import java.util.List;
031: import java.util.Map;
032: import java.util.StringTokenizer;
033:
034: import javax.sql.DataSource;
035:
036: import de.finix.contelligent.core.SystemFiles;
037: import de.finix.contelligent.logging.LoggingService;
038:
039: public class DBTableBuilder implements TableName {
040:
041: final static org.apache.log4j.Logger log = LoggingService
042: .getLogger(DBTableBuilder.class);
043:
044: final static Map cTableNames = new HashMap();
045:
046: final static String TOKEN = "#";
047:
048: final static String CALL_MARKER = "call";
049:
050: final static String TABLES_CM = "CM";
051:
052: final static String TABLES_RELATIONS = "Relations";
053:
054: final static String TABLES_WORKFLOW = "Workflow";
055:
056: final static String TABLES_TYPE = "Type";
057:
058: final static String TABLES_SYSTEM = "System";
059:
060: final static String TABLES_CM_90 = "CM90";
061:
062: final static String TABLES_SYSTEM_90 = "System90";
063:
064: final static String TABLES_MIG_90_91 = "mig_90_91";
065:
066: final static String[] cTables = new String[] { TABLES_SYSTEM,
067: TABLES_TYPE, TABLES_CM, TABLES_RELATIONS, TABLES_WORKFLOW };
068:
069: final static String[] cTables90 = new String[] { TABLES_SYSTEM_90,
070: TABLES_TYPE, TABLES_CM_90, TABLES_RELATIONS,
071: TABLES_WORKFLOW };
072:
073: static {
074: Map tableNames = new HashMap();
075: tableNames.put("@main@", CMAIN);
076: tableNames.put("@props@", CPROPS);
077: tableNames.put("@owner@", COWNER);
078: tableNames.put("@access@", CACCESS);
079: tableNames.put("@view@", CMAIN);
080: tableNames.put("@resource@", CRESOURCE);
081: tableNames.put("@resourceref@", CRESOURCEREF);
082: tableNames.put("@metadata@", CMETADATA);
083: tableNames.put("@publisher@", CPUBLISHER);
084: tableNames.put("@lock@", LOCK);
085: tableNames.put("@globallock@", GLOBAL_LOCK);
086: tableNames.put("@sensitiveCategories@", SENSITIVE_CATEGORIES);
087: tableNames.put("@supportedCategoryValues@",
088: SUPPORTED_CATEGORY_VALUES);
089: tableNames.put("@typeMain@", TYPE);
090: tableNames.put("@privileges@", PRIVILEGES);
091: cTableNames.put(TABLES_CM, tableNames);
092: cTableNames.put(TABLES_CM_90, tableNames);
093:
094: tableNames = new HashMap();
095: tableNames.put("@typeMain@", TYPE);
096: tableNames.put("@typeProps@", TYPE_PROPS);
097: tableNames.put("@description@", DESCRIPTION);
098: cTableNames.put(TABLES_TYPE, tableNames);
099:
100: tableNames = new HashMap();
101: tableNames.put("@typeMain@", TYPE);
102: tableNames.put("@relations@", RELATIONS);
103: cTableNames.put(TABLES_RELATIONS, tableNames);
104:
105: tableNames = new HashMap();
106: tableNames.put("@main@", WORKFLOW);
107: tableNames.put("@step@", WORKFLOW_STEP);
108: tableNames.put("@task@", TASK);
109: tableNames.put("@principal@", TASK_PRINCIPAL);
110: tableNames.put("@history@", TASK_HISTORY);
111: cTableNames.put(TABLES_WORKFLOW, tableNames);
112:
113: tableNames = new HashMap();
114: tableNames.put("@principal@", PRINCIPAL);
115: tableNames.put("@userroles@", USERROLES);
116: tableNames.put("@sequence@", SEQUENCE);
117: tableNames.put("@cm@", CM);
118: tableNames.put("@package@", PACKAGE);
119: tableNames.put("@packageRequired@", PACKAGE_REQUIRED);
120: tableNames.put("@cmowner@", CM_OWNER);
121: tableNames.put("@cmaccess@", CM_ACCESS);
122: tableNames.put("@events@", EVENTS);
123: tableNames.put("@privileges@", PRIVILEGES);
124: cTableNames.put(TABLES_SYSTEM, tableNames);
125: cTableNames.put(TABLES_SYSTEM_90, tableNames);
126:
127: tableNames = new HashMap();
128: tableNames.put("@access@", CACCESS);
129: tableNames.put("@privileges@", PRIVILEGES);
130: cTableNames.put(TABLES_MIG_90_91, tableNames);
131: }
132:
133: private String config;
134:
135: private SystemFiles files;
136:
137: private DataSource dataSource;
138:
139: private Connection connection;
140:
141: private boolean allowRecreation = false;
142:
143: private boolean closeConnection = true;
144:
145: public DBTableBuilder(Connection con, SystemFiles files,
146: boolean allowRecreation) throws SQLException {
147: this .config = DBDetector.getInstance().getIdentifier(con);
148: this .files = files;
149: this .allowRecreation = allowRecreation;
150: connection = con;
151: closeConnection = false;
152: }
153:
154: public DBTableBuilder(DataSource ds, SystemFiles files,
155: boolean allowRecreation) throws SQLException {
156: this .config = DBDetector.getInstance().getIdentifier(ds);
157: this .files = files;
158: this .allowRecreation = allowRecreation;
159: dataSource = ds;
160: }
161:
162: public void initDatabase() throws SQLException, IOException {
163: for (int i = 0; i < cTables.length; i++) {
164: initTableDomain(cTables[i]);
165: }
166: }
167:
168: public void dropTables() throws SQLException, IOException {
169: for (int i = cTables.length - 1; i >= 0; i--) {
170: try {
171: dropTableDomain(cTables[i]);
172: } catch (SQLException e) {
173: log.error("Error while dropping tables: ", e);
174: }
175: }
176: }
177:
178: public boolean checkDatabase() throws SQLException, IOException {
179: boolean result = true;
180: for (int i = 0; i < cTables.length; i++) {
181: result = result & checkTableDomain(cTables[i]);
182: }
183: return result;
184: }
185:
186: public boolean checkDatabase90() throws SQLException, IOException {
187: boolean result = true;
188: for (int i = 0; i < cTables90.length; i++) {
189: result = result & checkTableDomain(cTables90[i]);
190: }
191: return result;
192: }
193:
194: public void upgrade_90_91() throws SQLException, IOException {
195: Map tableNames = (Map) cTableNames.get(TABLES_MIG_90_91);
196: executeScript(tableNames, "mig_90_91.sql", true);
197: }
198:
199: private void initTableDomain(String tables) throws SQLException,
200: IOException {
201: Map tableNames = (Map) cTableNames.get(tables);
202:
203: if (checkTables(tableNames, tables)) {
204: log.info(tables + " tables are ok.");
205: } else {
206: if (allowRecreation) {
207: log
208: .info(tables
209: + " tables have incompatible structure, try to recreate them (all data will be lost!) ...");
210:
211: try {
212: dropTables(tableNames, tables);
213: } catch (SQLException e) {
214: // ignore
215: }
216:
217: try {
218: createTables(tableNames, tables);
219:
220: if (checkTables(tableNames, tables)) {
221: log.info("(Re)creation of " + tables
222: + " tables successful.");
223: } else {
224: log
225: .error("Recreation of "
226: + tables
227: + " tables failed! Please fix the problem manually.");
228: throw new SQLException(
229: "Recreation of "
230: + tables
231: + " tables failed! Please fix the problem manually.");
232: }
233: } catch (SQLException e) {
234: log
235: .error("Recreation of "
236: + tables
237: + " tables failed! Please fix the problem manually.");
238: throw e;
239: }
240: } else {
241: log
242: .info(tables
243: + " tables have incompatible structure, but recreation flag not set. Please alter your tables manually!");
244: }
245: }
246: }
247:
248: private boolean checkTableDomain(String tables)
249: throws SQLException, IOException {
250: Map tableNames = (Map) cTableNames.get(tables);
251: boolean result = true;
252:
253: if (checkTables(tableNames, tables)) {
254: log.info(tables + " tables are ok.");
255: return true;
256: } else {
257: log.info(tables + " tables have incompatible structure!!!");
258: return false;
259: }
260: }
261:
262: private void dropTableDomain(String tables) throws SQLException,
263: IOException {
264: Map tableNames = (Map) cTableNames.get(tables);
265: dropTables(tableNames, tables);
266: }
267:
268: private void createTables(Map tableNames, String domain)
269: throws SQLException, IOException {
270: executeScript(tableNames, "create" + domain + "Tables.sql",
271: true);
272: }
273:
274: private void dropTables(Map tableNames, String domain)
275: throws SQLException, IOException {
276: executeScript(tableNames, "drop" + domain + "Tables.sql", false);
277: }
278:
279: private boolean checkTables(Map tableNames, String domain)
280: throws SQLException, IOException {
281: StringBuffer buffer = new StringBuffer(20);
282: buffer.append("check").append(domain).append("Tables.sql");
283:
284: try {
285: executeScript(tableNames, buffer.toString(), false);
286: return true;
287: } catch (SQLException e) {
288: return false;
289: }
290: }
291:
292: private void executeScript(Map tableNames, String script,
293: boolean stopOnFailure) throws SQLException, IOException {
294: final String scriptPath = config + "/" + script;
295: final String[] statements = parseScript(scriptPath, tableNames);
296: if (statements == null && statements.length == 0) {
297: log
298: .error("executeScript() - no statements found in script "
299: + scriptPath + "!");
300: throw new IOException(
301: "executeScript() - no statements found in script "
302: + scriptPath + "!");
303: }
304:
305: Connection con = getConnection();
306: Statement stmt = null;
307: CallableStatement cstmt = null;
308: boolean success = true;
309: String command = null;
310: String currentStatement = null;
311: try {
312: int i = 0;
313:
314: while (i < statements.length) {
315: command = statements[i++];
316: currentStatement = statements[i++];
317: try {
318: if (command.startsWith(CALL_MARKER)) {
319: StringTokenizer tokenizer = new StringTokenizer(
320: command);
321:
322: if (tokenizer.countTokens() == 1) {
323: cstmt = con.prepareCall(currentStatement);
324: cstmt.execute();
325: log.debug("Executed statement "
326: + currentStatement + " (command='"
327: + command + "')");
328: } else {
329: tokenizer.nextToken(); // skip CALL
330: String dbConf = tokenizer.nextToken();
331: if (dbConf.equals(config)) {
332: cstmt = con
333: .prepareCall(currentStatement);
334: cstmt.execute();
335: log.debug("Executed statement "
336: + currentStatement
337: + " (command='" + command
338: + "')");
339: }
340: }
341: } else { // we asume a normal statement for all rdbms for
342: // now
343: stmt = con.createStatement(); // do not move this out
344: // of the loop as DB2
345: // does not like it this
346: // way!
347: stmt.execute(currentStatement);
348: log.debug("Executed statement "
349: + currentStatement + " (command='"
350: + command + "')");
351: }
352: } catch (Exception e) {
353: if (stopOnFailure) {
354: log.error("Exception during execution of "
355: + currentStatement + " (command='"
356: + command + "')", e);
357: throw new SQLException(
358: "Exception during execution of "
359: + currentStatement
360: + " (command='" + command
361: + "'): " + e.getMessage());
362: } else {
363: success = false;
364: log.debug("Exception during execution of "
365: + currentStatement + " (command='"
366: + command + "'): " + e.getMessage());
367: }
368: } finally {
369: try {
370: if (cstmt != null) {
371: cstmt.close();
372: }
373: } catch (Exception e) {
374: } finally {
375: cstmt = null;
376: }
377: try {
378: if (stmt != null) {
379: stmt.close();
380: }
381: } catch (Exception e) {
382: } finally {
383: stmt = null;
384: }
385: }
386: }
387: } finally {
388: try {
389: if (con != null && closeConnection) {
390: con.close();
391: }
392: } catch (Exception e) {
393: } finally {
394: con = null;
395: }
396: if (!success) {
397: throw new SQLException(
398: "Statements not sucessfully executed");
399: }
400: }
401: }
402:
403: /**
404: * Returns a <code>String[]</code> containing ... or null if
405: */
406: private String[] parseScript(String path, Map tableNames)
407: throws IOException {
408: BufferedReader reader = new BufferedReader(
409: new InputStreamReader(files.getStream(path)));
410: StringBuffer buffer = new StringBuffer();
411: String tmp;
412: boolean commandRead = false;
413: int line = 0;
414: List pairs = new LinkedList();
415:
416: while ((tmp = reader.readLine()) != null) {
417: line++;
418: if (tmp.startsWith("--") || tmp.trim().length() == 0) { // skip
419: // emtpy
420: // lines and
421: // those
422: // with
423: // comments
424: continue;
425: }
426: if (tmp.startsWith(TOKEN)) {
427: if (!commandRead) {
428: pairs.add(tmp.substring(TOKEN.length()).trim());
429: commandRead = true;
430: } else { // done with one command/statement pair
431: pairs.add(buffer.toString());
432: buffer.setLength(0);
433: commandRead = false;
434: }
435: } else {
436: if (!commandRead) {
437: throw new IOException("script '" + path
438: + "' contains errors in line " + line
439: + ": command expected (line starting with "
440: + TOKEN + ")!");
441: } else {
442: buffer
443: .append(
444: replaceTokens(tmp.trim(),
445: tableNames)).append(" ");
446: }
447: }
448: }
449: if (commandRead && (pairs.size() % 2 != 0)) {
450: throw new IOException(
451: "Missing statement for last command ("
452: + pairs.get(pairs.size()) + ") in script '"
453: + path + "'!");
454: }
455: return (String[]) pairs.toArray(new String[pairs.size()]);
456: }
457:
458: private String replaceTokens(String line, Map tableNames) {
459: Iterator iterator = tableNames.entrySet().iterator();
460: StringBuffer buffer = new StringBuffer();
461: String tail = line;
462:
463: while (iterator.hasNext()) {
464: Map.Entry entry = (Map.Entry) iterator.next();
465: String token = (String) entry.getKey();
466: int tokenLength = token.length();
467: int i = tail.indexOf(token);
468:
469: while (i != -1) {
470: buffer.append(tail.substring(0, i));
471: buffer.append(entry.getValue());
472: tail = tail.substring(i + tokenLength);
473: i = tail.indexOf(token);
474: }
475:
476: buffer.append(tail);
477: tail = buffer.toString();
478: buffer = new StringBuffer();
479: }
480:
481: return tail;
482: }
483:
484: private Connection getConnection() throws SQLException {
485: if (connection == null) {
486: return PersistenceUtils.getConnection(dataSource);
487: }
488: return connection;
489: }
490:
491: }
|