001: /***************************************************************
002: * This file is part of the [fleXive](R) project.
003: *
004: * Copyright (c) 1999-2008
005: * UCS - unique computing solutions gmbh (http://www.ucs.at)
006: * All rights reserved
007: *
008: * The [fleXive](R) project is free software; you can redistribute
009: * it and/or modify it under the terms of the GNU General Public
010: * License as published by the Free Software Foundation;
011: * either version 2 of the License, or (at your option) any
012: * later version.
013: *
014: * The GNU General Public License can be found at
015: * http://www.gnu.org/copyleft/gpl.html.
016: * A copy is found in the textfile GPL.txt and important notices to the
017: * license from the author are found in LICENSE.txt distributed with
018: * these libraries.
019: *
020: * This library is distributed in the hope that it will be useful,
021: * but WITHOUT ANY WARRANTY; without even the implied warranty of
022: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
023: * GNU General Public License for more details.
024: *
025: * For further information about UCS - unique computing solutions gmbh,
026: * please see the company website: http://www.ucs.at
027: *
028: * For further information about [fleXive](R), please see the
029: * project website: http://www.flexive.org
030: *
031: *
032: * This copyright notice MUST APPEAR in all copies of the file!
033: ***************************************************************/package com.flexive.core;
034:
035: import static com.flexive.core.DatabaseConst.DS_GLOBAL_CONFIG;
036: import static com.flexive.core.DatabaseConst.ML;
037: import com.flexive.shared.EJBLookup;
038: import com.flexive.shared.FxContext;
039: import com.flexive.shared.FxLanguage;
040: import com.flexive.shared.configuration.DBVendor;
041: import com.flexive.shared.configuration.DivisionData;
042: import com.flexive.shared.exceptions.*;
043: import com.flexive.shared.interfaces.GlobalConfigurationEngine;
044: import com.flexive.shared.value.FxString;
045: import org.apache.commons.logging.Log;
046: import org.apache.commons.logging.LogFactory;
047:
048: import javax.naming.Context;
049: import javax.naming.InitialContext;
050: import javax.naming.NamingException;
051: import javax.sql.DataSource;
052: import java.sql.*;
053: import java.util.ArrayList;
054: import java.util.HashMap;
055: import java.util.List;
056: import java.util.Map;
057:
058: /**
059: * Class handling Database stuff
060: *
061: * @author Gregor Schober (gregor.schober@flexive.com), UCS - unique computing solutions gmbh (http://www.ucs.at)
062: * @author Markus Plesser (markus.plesser@flexive.com), UCS - unique computing solutions gmbh (http://www.ucs.at)
063: */
064: public final class Database {
065: /**
066: * Maximum number of divisions
067: */
068: public static final int MAX_DIVISIONS = 256;
069:
070: private static final transient Log LOG = LogFactory
071: .getLog(Database.class);
072: private static DataSource globalDataSource = null;
073: private static DataSource testDataSource = null;
074: // cached data source references - index = division ID
075: private static DataSource[] dataSources = new DataSource[MAX_DIVISIONS];
076:
077: /**
078: * Empty default constructor.
079: */
080: private Database() {
081: // empty default constructor
082: }
083:
084: /**
085: * Retrieves a database connection.
086: *
087: * @param divisionId the requested division Id
088: * @return a database connection
089: * @throws SQLException If no connection could be retrieved
090: */
091: public static Connection getDbConnection(int divisionId)
092: throws SQLException {
093: // Try to obtain a connection
094: try {
095: return getDataSource(divisionId).getConnection();
096: } catch (SQLException exc) {
097: String sErr = "FxDbException, unable to retrieve DB Connection: "
098: + exc.getMessage();
099: LOG.error(sErr);
100: throw new SQLException(sErr);
101: }
102: }
103:
104: /**
105: * Return a database connection for the current user's division.
106: *
107: * @return a database connection for the current user's division.
108: * @throws SQLException if a DB error occured
109: */
110: public static Connection getDbConnection() throws SQLException {
111: return getDbConnection(FxContext.get().getDivisionId());
112: }
113:
114: /**
115: * Retrieves a database connection for the global configuration table, regardless
116: * of the current request's division id.
117: *
118: * @return a database connection
119: * @throws SQLException if no connection could be retrieved
120: */
121: public static Connection getGlobalDbConnection()
122: throws SQLException {
123: try {
124: return getGlobalDataSource().getConnection();
125: } catch (SQLException exc) {
126: String sErr = "FxDbException, unable to retrieve global DB Connection: "
127: + exc.getMessage();
128: LOG.error(sErr);
129: throw new SQLException(sErr);
130: }
131: }
132:
133: /**
134: * Returns the data source for the calling user's division.
135: *
136: * @return the data source for the calling user's division
137: * @throws SQLException if a DB error occured
138: */
139: public static DataSource getDataSource() throws SQLException {
140: return getDataSource(FxContext.get().getDivisionId());
141: }
142:
143: /**
144: * Retrieves a database connection.
145: *
146: * @param divisionId the division id
147: * @return a database connection
148: * @throws SQLException If no connection could be retrieved
149: */
150: public static DataSource getDataSource(int divisionId)
151: throws SQLException {
152: // Check division
153: if (!DivisionData.isValidDivisionId(divisionId)) {
154: throw new SQLException(
155: "Unable to obtain connection: Division not defined ("
156: + divisionId + ").");
157: }
158: // use cached datasource, if available
159: if (divisionId == DivisionData.DIVISION_TEST
160: && testDataSource != null) {
161: return testDataSource;
162: } else if (divisionId != DivisionData.DIVISION_TEST
163: && dataSources[divisionId] != null) {
164: return dataSources[divisionId];
165: }
166: synchronized (Database.class) {
167: // Try to obtain a connection
168: String finalDsName = null;
169: try {
170: Context c = new InitialContext();
171: if (divisionId == DivisionData.DIVISION_GLOBAL) {
172: // Special case: global config database
173: finalDsName = DS_GLOBAL_CONFIG;
174: } else {
175: // else: get data source from global configuration
176: GlobalConfigurationEngine globalConfiguration = EJBLookup
177: .getGlobalConfigurationEngine();
178: finalDsName = globalConfiguration.getDivisionData(
179: divisionId).getDataSource();
180: }
181: LOG.info("Looking up datasource for division "
182: + divisionId + ": " + finalDsName);
183: DataSource dataSource = (DataSource) c
184: .lookup(finalDsName);
185: if (divisionId == DivisionData.DIVISION_TEST) {
186: testDataSource = dataSource;
187: return testDataSource;
188: } else {
189: dataSources[divisionId] = dataSource;
190: return dataSources[divisionId];
191: }
192: } catch (NamingException exc) {
193: String sErr = "Naming Exception, unable to retrieve Connection to ["
194: + finalDsName + "]: " + exc.getMessage();
195: LOG.error(sErr);
196: throw new SQLException(sErr);
197: } catch (FxNotFoundException exc) {
198: String sErr = "Failed to retrieve datasource for division "
199: + divisionId + " (not configured).";
200: LOG.error(sErr);
201: throw new SQLException(sErr);
202: } catch (FxLoadException exc) {
203: String sErr = "Failed to load datasource configuration: "
204: + exc.getMessage();
205: LOG.error(sErr);
206: throw new SQLException(sErr);
207: } catch (FxApplicationException exc) {
208: String sErr = "Unknown error while loading datasource for division "
209: + divisionId + ": " + exc.getMessage();
210: LOG.error(sErr);
211: throw new SQLException(sErr);
212: }
213: }
214: }
215:
216: /**
217: * Retrieves DivisionData for current division
218: *
219: * @return DivisionData
220: * @throws SQLException If no DivisionData could be retrieved
221: */
222: public static DivisionData getDivisionData() throws SQLException {
223: final FxContext inf = FxContext.get();
224: // Check division
225: if (!DivisionData.isValidDivisionId(inf.getDivisionId())) {
226: throw new SQLException(
227: "Unable to obtain DivisionData: Division not defined ("
228: + inf.getDivisionId() + ")");
229: }
230: try {
231: GlobalConfigurationEngine globalConfiguration = EJBLookup
232: .getGlobalConfigurationEngine();
233: return globalConfiguration.getDivisionData(inf
234: .getDivisionId());
235: } catch (FxNotFoundException exc) {
236: String sErr = "Failed to retrieve DivisionData for division "
237: + inf.getDivisionId() + " (not configured).";
238: LOG.error(sErr);
239: throw new SQLException(sErr);
240: } catch (FxApplicationException exc) {
241: String sErr = "Failed to load configuration: "
242: + exc.getMessage();
243: LOG.error(sErr);
244: throw new SQLException(sErr);
245: }
246: }
247:
248: /**
249: * Retrieve data source for global configuration table, regardless
250: * of the current request's division id.
251: *
252: * @return a database connection
253: * @throws SQLException if no connection could be retrieved
254: */
255: public static synchronized DataSource getGlobalDataSource()
256: throws SQLException {
257: // Try to obtain a connection
258: if (globalDataSource != null) {
259: return globalDataSource;
260: }
261: try {
262: Context c = new InitialContext();
263: try {
264: globalDataSource = (DataSource) c
265: .lookup(DS_GLOBAL_CONFIG);
266: } catch (NamingException e) {
267: //try once more in local java namespace
268: globalDataSource = (DataSource) c.lookup("java:"
269: + DS_GLOBAL_CONFIG);
270: }
271: return globalDataSource;
272: } catch (NamingException exc) {
273: String sErr = "Naming Exception, unable to retrieve Connection to ["
274: + DS_GLOBAL_CONFIG + "]: " + exc.getMessage();
275: LOG.error(sErr);
276: throw new SQLException(sErr);
277: }
278: }
279:
280: /**
281: * Helper function to close connections and statements.
282: * A FxDbException is thrown if the close of the connection failed.
283: * No Exception is thrown if the Statement failed to close, but a error is logged.
284: *
285: * @param caller class calling function/module, or null
286: * @param con the connection to close, or null
287: * @param stmt the statement to close, or null
288: */
289: public static void closeObjects(Class caller, Connection con,
290: Statement stmt) {
291: closeObjects(caller.getName(), con, stmt);
292: }
293:
294: /**
295: * Helper function to close connections and statements.
296: * A FxDbException is thrown if the close of the connection failed.
297: * No Exception is thrown if the Statement failed to close, but a error is logged.
298: *
299: * @param caller a string representing the calling function/module, or null
300: * @param con the connection to close, or null
301: * @param stmt the statement to close, or null
302: */
303: public static void closeObjects(String caller, Connection con,
304: Statement stmt) {
305: try {
306: if (stmt != null)
307: stmt.close();
308: } catch (Exception exc) {
309: //noinspection ThrowableInstanceNeverThrown
310: StackTraceElement[] se = new Throwable().getStackTrace();
311: LOG.error(((caller != null) ? caller + " f" : "F")
312: + "ailed to close the statement(s): "
313: + exc.getMessage() + " Calling line: "
314: + se[2].toString());
315: }
316: if (con != null) {
317: try {
318: if (!con.isClosed()) {
319: con.close();
320: }
321: } catch (SQLException exc) {
322: //noinspection ThrowableInstanceNeverThrown
323: FxDbException dbExc = new FxDbException(
324: ((caller != null) ? caller + " is u" : "U")
325: + "nable to close the db connection");
326: LOG.error(dbExc);
327: System.err.println(dbExc.getMessage());
328: }
329: }
330: }
331:
332: /**
333: * Returns true if the SqlError is a unique constraint violation.
334: *
335: * @param exc the exception
336: * @return true if the SqlError is a unique constraint violation
337: */
338: public static boolean isUniqueConstraintViolation(Exception exc) {
339: if (!(exc instanceof SQLException)) {
340: return false;
341: }
342: try {
343: if (getDivisionData().getDbVendor() == DBVendor.MySQL) {
344: //see http://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html
345: // 1582 Example error: Duplicate entry 'ABSTRACT' for key 'UK_TYPEPROPS_NAME'
346: int sqlErr = ((SQLException) exc).getErrorCode();
347: return (sqlErr == 1062 || sqlErr == 1582);
348: }
349: } catch (SQLException e) {
350: return false;
351: }
352:
353: // final String sMsg = (exc.getMessage() == null) ? "" : exc.getMessage().toLowerCase();
354:
355: // Oracle:
356: // msg: "unique constraint (XXXX) violated"
357: // return sMsg.indexOf("unique constraint") != -1 && sMsg.indexOf("violated") != -1;
358:
359: // MySQL5:
360: // msg="Duplicate key or integrity constraint violation message from server: "Cannot delete or update a
361: // parent row: a foreign key constraint fails"
362: // SQLState: 23000
363: return ((SQLException) exc).getSQLState().equalsIgnoreCase(
364: "23000");
365: }
366:
367: /**
368: * Returns true if the SqlError is a foreign key violation.
369: *
370: * @param exc the exception
371: * @return true if the SqlError is a foreign key violation
372: */
373: public static boolean isForeignKeyViolation(Exception exc) {
374: if (!(exc instanceof SQLException)) {
375: return false;
376: }
377: try {
378: if (getDivisionData().getDbVendor() == DBVendor.MySQL) {
379: //see http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html
380: int errorCode = ((SQLException) exc).getErrorCode();
381: return errorCode == 1451 || errorCode == 1217;
382: }
383: } catch (SQLException e) {
384: throw new FxDbException(LOG, e, "ex.db.sqlError", e
385: .getMessage()).asRuntimeException();
386: }
387: return false;
388: }
389:
390: /**
391: * Load a FxString from a translation table
392: *
393: * @param con an open connection
394: * @param table the base table (NOT the one with translations!)
395: * @param column the name of the columns from the translations table to load
396: * @param whereClause mandatory where clause
397: * @return FxString created from the data table
398: * @throws SQLException if a database error occured
399: */
400: public static FxString loadFxString(Connection con, String table,
401: String column, String whereClause) throws SQLException {
402: Statement stmt = null;
403: Map<Long, String> hmTrans = new HashMap<Long, String>(10);
404: long defaultLanguageId = -1;
405: try {
406: stmt = con.createStatement();
407: ResultSet rs = stmt.executeQuery("SELECT LANG, DEFLANG, "
408: + column + " FROM " + table + DatabaseConst.ML
409: + " WHERE " + whereClause);
410: while (rs != null && rs.next()) {
411: hmTrans.put(rs.getLong(1), rs.getString(3));
412: if (rs.getBoolean(2)) {
413: defaultLanguageId = rs.getInt(1);
414: }
415: }
416: } finally {
417: if (stmt != null)
418: stmt.close();
419: }
420: return new FxString(defaultLanguageId, hmTrans);
421: }
422:
423: /**
424: * Load a FxString from the content data
425: *
426: * @param con an open connection
427: * @param column the name of the column from the translations table to load
428: * @param whereClause mandatory where clause
429: * @return FxString created from the data table
430: * @throws SQLException if a database error occured
431: */
432: public static FxString loadContentDataFxString(Connection con,
433: String column, String whereClause) throws SQLException {
434: Statement stmt = null;
435: Map<Long, String> hmTrans = new HashMap<Long, String>(10);
436: int defaultLanguageId = -1;
437: try {
438: stmt = con.createStatement();
439: ResultSet rs = stmt.executeQuery("SELECT LANG, ISMLDEF, "
440: + column + " FROM "
441: + DatabaseConst.TBL_CONTENT_DATA + " WHERE "
442: + whereClause);
443: while (rs != null && rs.next()) {
444: hmTrans.put(rs.getLong(1), rs.getString(3));
445: if (rs.getBoolean(2)) {
446: defaultLanguageId = rs.getInt(1);
447: }
448: }
449: } finally {
450: if (stmt != null)
451: stmt.close();
452: }
453: return new FxString(defaultLanguageId, hmTrans);
454: }
455:
456: /**
457: * Load an FxString array from multiple fields
458: *
459: * @param con open and valid connection
460: * @param table table to use
461: * @param columns names of the columns containing the translations
462: * @param whereClause where clause for the table (like id=x)
463: * @return FxString array a string array representing <code>columns</code>
464: * @throws SQLException if a database error occured
465: */
466: public static FxString[] loadFxString(Connection con, String table,
467: String[] columns, String whereClause) throws SQLException {
468: Statement stmt = null;
469: Map<Long, String[]> hmTrans = new HashMap<Long, String[]>(10);
470: StringBuffer sql = new StringBuffer(200);
471: try {
472: sql.append("SELECT LANG");
473: for (String column : columns)
474: sql.append(',').append(column);
475: sql.append(" FROM ").append(table).append(ML).append(
476: " WHERE ").append(whereClause);
477: stmt = con.createStatement();
478: ResultSet rs = stmt.executeQuery(sql.toString());
479: while (rs != null && rs.next()) {
480: String[] curr = new String[columns.length];
481: for (int i = 0; i < columns.length; i++) {
482: curr[i] = rs.getString(2 + i);
483: if (rs.wasNull())
484: curr[i] = null;
485: }
486: hmTrans.put(rs.getLong(1), curr);
487: }
488: } finally {
489: if (stmt != null)
490: stmt.close();
491: }
492: FxString[] ret = new FxString[columns.length];
493: for (int i = 0; i < ret.length; i++)
494: ret[i] = new FxString(hmTrans, i);
495: return ret;
496: }
497:
498: /**
499: * Loads all FxString entries stored in the given table.
500: *
501: * @param con an existing connection
502: * @param table table to use
503: * @param columns name of the columns containing the translations
504: * @return all FxString entries stored in the given table, indexed by the ID field.
505: * @throws SQLException if the query was not successful
506: */
507: public static Map<Long, FxString[]> loadFxStrings(Connection con,
508: String table, String[] columns) throws SQLException {
509: Statement stmt = null;
510: final StringBuilder sql = new StringBuilder();
511: final Map<Long, FxString[]> result = new HashMap<Long, FxString[]>();
512: try {
513: sql.append("SELECT id, lang");
514: for (String column : columns) {
515: sql.append(',').append(column);
516: }
517: sql.append(" FROM ").append(table).append(ML).append(
518: " ORDER BY id");
519: stmt = con.createStatement();
520: final ResultSet rs = stmt.executeQuery(sql.toString());
521: while (rs.next()) {
522: final long id = rs.getLong(1);
523: final int lang = rs.getInt(2);
524: if (lang == FxLanguage.SYSTEM_ID) {
525: continue; // TODO how to deal with system language?
526: }
527: if (!result.containsKey(id)) {
528: final FxString[] newValues = new FxString[columns.length];
529: for (int i = 0; i < newValues.length; i++) {
530: newValues[i] = new FxString(true, "");
531: }
532: result.put(id, newValues);
533: }
534: final FxString[] entry = result.get(id);
535: for (int i = 0; i < columns.length; i++) {
536: final String translation = rs.getString(3 + i);
537: entry[i].setTranslation(lang, translation);
538: }
539: }
540: } finally {
541: closeObjects(Database.class, null, stmt);
542: }
543: return result;
544: }
545:
546: /**
547: * Store a FxString in a translation table that only consists of one(!) translation column
548: *
549: * @param string string to be stored
550: * @param con existing connection
551: * @param table storage table
552: * @param dataColumn name of the data column
553: * @param idColumn name of the id column
554: * @param id id of the given string
555: * @throws SQLException if a database error occured
556: */
557: public static void storeFxString(FxString string, Connection con,
558: String table, String dataColumn, String idColumn, long id)
559: throws SQLException {
560: if (!string.isMultiLanguage()) {
561: throw new FxInvalidParameterException("string", LOG,
562: "ex.db.fxString.store.multilang", table)
563: .asRuntimeException();
564: }
565: PreparedStatement ps = null;
566: try {
567: ps = con.prepareStatement("DELETE FROM " + table + ML
568: + " WHERE " + idColumn + "=?");
569: ps.setLong(1, id);
570: ps.execute();
571: ps.close();
572: if (string.getTranslatedLanguages().length > 0) {
573: ps = con.prepareStatement("INSERT INTO " + table + ML
574: + " (" + idColumn + ",LANG,DEFLANG,"
575: + dataColumn + ") VALUES (?,?,?,?)");
576: ps.setLong(1, id);
577: String curr;
578: for (long lang : string.getTranslatedLanguages()) {
579: curr = string.getTranslation(lang);
580: if (curr != null && curr.trim().length() > 0) {
581: ps.setInt(2, (int) lang);
582: ps.setBoolean(3, lang == string
583: .getDefaultLanguage());
584: ps.setString(4, curr);
585: ps.executeUpdate();
586: }
587: }
588: }
589: } finally {
590: if (ps != null)
591: ps.close();
592: }
593: }
594:
595: /**
596: * Store a FxString in a translation table that only consists of n translation columns
597: *
598: * @param string string to be stored
599: * @param con existing connection
600: * @param table storage table
601: * @param dataColumn names of the data columns
602: * @param idColumn name of the id column
603: * @param id id of the given string
604: * @throws SQLException if a database error occured
605: */
606: public static void storeFxString(FxString[] string, Connection con,
607: String table, String[] dataColumn, String idColumn, long id)
608: throws SQLException {
609: PreparedStatement ps = null;
610: if (string.length != dataColumn.length)
611: throw new SQLException("string.length != dataColumn.length");
612: for (FxString param : string) {
613: if (!param.isMultiLanguage()) {
614: throw new FxInvalidParameterException("string", LOG,
615: "ex.db.fxString.store.multilang", table)
616: .asRuntimeException();
617: }
618: }
619: try {
620: ps = con.prepareStatement("DELETE FROM " + table + ML
621: + " WHERE " + idColumn + "=?");
622: ps.setLong(1, id);
623: ps.execute();
624:
625: //find languages to write
626: List<Long> langs = new ArrayList<Long>(5);
627: for (FxString curr : string)
628: for (long currLang : curr.getTranslatedLanguages())
629: if (curr.translationExists(currLang)) {
630: if (!langs.contains(currLang))
631: langs.add(currLang);
632: }
633: if (langs.size() > 0) {
634: StringBuffer sql = new StringBuffer(300);
635: sql.append("INSERT INTO ").append(table).append(
636: ML + "(").append(idColumn).append(",LANG");
637: for (String dc : dataColumn)
638: sql.append(',').append(dc);
639: sql.append(")VALUES(?,?");
640: //noinspection UnusedDeclaration
641: for (FxString aString : string)
642: sql.append(",?");
643: sql.append(')');
644: ps.close();
645: ps = con.prepareStatement(sql.toString());
646:
647: for (long lang : langs) {
648: ps.setLong(1, id);
649: ps.setInt(2, (int) lang);
650: for (int i = 0; i < string.length; i++) {
651: if (FxString.EMPTY.equals(string[i]
652: .getTranslation(lang)))
653: ps.setNull(3 + i, java.sql.Types.VARCHAR);
654: else
655: ps.setString(3 + i, string[i]
656: .getTranslation(lang)); //get translation or empty string
657: }
658: ps.executeUpdate();
659: }
660: }
661: } finally {
662: if (ps != null)
663: ps.close();
664: }
665: }
666: }
|