1   package com.atlassian.config.db;
2   
3   import com.atlassian.config.ApplicationConfiguration;
4   import com.atlassian.config.ConfigurationException;
5   import com.atlassian.config.bootstrap.BootstrapException;
6   import net.sf.hibernate.HibernateException;
7   import net.sf.hibernate.connection.ConnectionProvider;
8   import net.sf.hibernate.connection.ConnectionProviderFactory;
9   import org.slf4j.Logger;
10  import org.slf4j.LoggerFactory;
11  
12  import java.sql.Connection;
13  import java.sql.ResultSet;
14  import java.sql.SQLException;
15  import java.sql.Statement;
16  import java.util.Properties;
17  
18  /**
19   * The DatabaseHelper class provides methods used during bootstrap. It should not be used during normal runtime.
20   */
21  public class DatabaseHelper
22  {
23      private final static Logger log = LoggerFactory.getLogger(DatabaseHelper.class);
24  
25      /**
26       * Test whether the database's lower function supports non-ascii characters and sets a hibernate parameter in the
27       * application configuration. This serves as a workaround for postgres which does not support lower casing of
28       * non-ascii characters in certain versions. The property set in this method will be used in
29       * GeneralUtil.specialToLowerCase() to indicate whether the database supports the lowercasing or not. The property
30       * might not be set if the database does not support the query.
31       * @param databaseProperties the hibernate database properties to use.
32       * @param applicationConfig the application configuration to update.
33       * @throws BootstrapException if there was a problem with the database properties given.
34       */
35      public void setDatabaseLowerProperty(Properties databaseProperties, ApplicationConfiguration applicationConfig) throws BootstrapException
36      {
37          Connection conn = null;
38          Statement st = null;
39          ResultSet rs = null;
40          try
41          {
42              conn = getConnection(databaseProperties);
43  
44              st = conn.createStatement();
45              // This query is not supported by all database (eg. HSQL). However, since this is mainly a workaround for postgres, we don't care much.
46              String sqlQuery = "select lower('\u00dcbersicht')";
47              rs = st.executeQuery(sqlQuery);
48              rs.next();
49              if (rs.getString(1).equals("\u00dcbersicht"))
50              {
51                  applicationConfig.setProperty("hibernate.database.lower_non_ascii_supported", Boolean.FALSE);
52              }
53              else
54              {
55                  applicationConfig.setProperty("hibernate.database.lower_non_ascii_supported", Boolean.TRUE);
56              }
57              applicationConfig.save();
58          }
59          catch (SQLException e)
60          {
61              log.info("SQL query could not be excecuted: ", e);
62          }
63          catch (ConfigurationException e)
64          {
65              log.error("Configuration file could not be saved: ", e);
66          }
67          finally
68          {
69              closeResultSetOrLog(rs);
70              closeStatementOrLog(st);
71              closeConnectionOrLog(conn);
72          }
73      }
74  
75      private void closeConnectionOrLog(Connection conn)
76      {
77          try
78          {
79              if (conn == null)
80              {
81                  log.error("Connection was null. We could not successfully connect to the specified database");
82              }
83              else
84              {
85                  conn.close();
86              }
87          }
88          catch (SQLException e)
89          {
90              log.error("Could not close database connection: ", e);
91          }
92      }
93  
94      private void closeStatementOrLog(Statement st)
95      {
96          try
97          {
98              if (st != null)
99                  st.close();
100         }
101         catch (SQLException e)
102         {
103             log.warn("Problem while closing statement", e);
104         }
105     }
106 
107     private void closeResultSetOrLog(ResultSet rs)
108     {
109         try
110         {
111             if (rs != null)
112                 rs.close();
113         }
114         catch (SQLException e)
115         {
116             log.warn("Problem while closing result set", e);
117         }
118     }
119 
120     /**
121      * Returns a connection configured according to the given hibernate database properties. Callers must call
122      * {@link Connection#close()} to avoid resource leaks.
123      * @param databaseProperties the hibernate database properties to use.
124      * @return the new connection.
125      * @throws SQLException if there is a problem opening the connection.
126      * @throws BootstrapException if there is some other problem with the properties provided.
127      */
128     public Connection getConnection(Properties databaseProperties)
129         throws SQLException, BootstrapException
130     {
131         try
132         {
133             final ConnectionProvider connectionProvider = ConnectionProviderFactory.newConnectionProvider(databaseProperties);
134             return new DelegatingConnection(connectionProvider.getConnection())
135             {
136                 public void close() throws SQLException
137                 {
138                     try
139                     {
140                         super.close();
141                     }
142                     finally
143                     {
144                         try
145                         {
146                             connectionProvider.close();
147                         }
148                         catch (HibernateException e)
149                         {
150                             log.warn("Problem while closing hibernate connection provider", e);
151                         }
152                     }
153                 }
154             };
155         }
156         catch (HibernateException e)
157         {
158             throw new BootstrapException(e.getMessage() + ", there may be a configuration problem with your hibernate settings");
159         }
160     }
161 }