content: /*
   * Copyright (C) 2012-2023 52°North Spatial Information Research GmbH
   *
   * This program is free software; you can redistribute it and/or modify it
   * under the terms of the GNU General Public License version 2 as published
   * by the Free Software Foundation.
   *
   * If the program is linked with libraries which are licensed under one of
   * the following licenses, the combination of the program with the linked
   * library is not considered a "derivative work" of the program:
   *
   *     - Apache License, version 2.0
   *     - Apache Software License, version 1.0
   *     - GNU Lesser General Public License, version 3
   *     - Mozilla Public License, versions 1.0, 1.1 and 2.0
   *     - Common Development and Distribution License (CDDL), version 1.0
   *
   * Therefore the distribution of the program linked with libraries licensed
   * under the aforementioned licenses, is permitted by the copyright holders
   * if the distribution is compliant with both the GNU General Public
   * License version 2 and the aforementioned licenses.
   *
   * This program is distributed in the hope that it will be useful, but
   * WITHOUT ANY WARRANTY; without even the implied warranty of
   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General
   * Public License for more details.
   */
  package org.n52.sos.ds.hibernate.admin;
  
  import java.sql.Connection;
  import java.sql.ResultSet;
  import java.sql.ResultSetMetaData;
  import java.sql.SQLException;
  import java.sql.Statement;
  import java.util.ArrayList;
  import java.util.List;
  import java.util.Locale;
  
  import javax.inject.Inject;
  
  import org.hibernate.HibernateException;
  import org.hibernate.Session;
  import org.hibernate.Transaction;
  import org.hibernate.jdbc.ReturningWork;
  import org.n52.iceland.ds.ConnectionProvider;
  import org.n52.sos.ds.GeneralQueryDAO;
  import org.n52.sos.ds.hibernate.HibernateSessionHolder;
  import org.n52.sos.ds.hibernate.util.TransactionHelper;
  import org.n52.sos.util.SQLHelper;
  import org.slf4j.Logger;
  import org.slf4j.LoggerFactory;
  
  /**
   * class that deals with crud operations related to SOS DB.
   *
   * @author Shubham Sachdeva
   * @author <a href="mailto:c.autermann@52north.org">Christian Autermann</a>
   *
   * @since 4.0.0
   *
   */
  public class HibernateGeneralQueryDAO implements GeneralQueryDAO, TransactionHelper {
      private static final Logger LOG = LoggerFactory.getLogger(HibernateGeneralQueryDAO.class);
  
      private static final String[] MODIFY_COMMANDS = { "alter ", "create ", "drop ", "truncate ", "rename " };
  
      private static final String[] UPDATE_COMMANDS = { "update ", "insert ", "delete " };
  
      private HibernateSessionHolder sessionHolder;
  
      @Inject
      public void setConnectionProvider(ConnectionProvider connectionProvider) {
          this.sessionHolder = new HibernateSessionHolder(connectionProvider);
      }
  
      /**
       * Method which query the SOS DB
       *
       * @param query
       *            normal sql query concerning any table
       *
       * @return query result
       *
       * @throws SQLException
       *             If an error occurs
       */
      @Override
      public QueryResult query(final String query) throws SQLException {
          String q = query.toLowerCase(Locale.ROOT);
          if (contains(MODIFY_COMMANDS, q)) {
              return doTransactionalWork(new ModifyWork(), query);
          } else if (contains(UPDATE_COMMANDS, q)) {
              return doTransactionalWork(new UpdateWork(), query);
          } else {
              return doWork(new SelectWork(), query);
          }
      }
  
      private QueryResult doWork(QueryWork work, String query) {
          Session s = null;
          try {
              s = sessionHolder.getSession();
              return s.doReturningWork(work.setQuery(query));
          } catch (Exception ex) {
              return new ErrorResult(ex);
          } finally {
              sessionHolder.returnSession(s);
          }
      }
  
      private QueryResult doTransactionalWork(QueryWork work, String query) {
          Session s = null;
          try {
              s = sessionHolder.getSession();
              Transaction t = getTransaction(s);
              try {
                  QueryResult result = s.doReturningWork(work.setQuery(query));
                  t.commit();
                  return result;
              } catch (HibernateException e) {
                  t.rollback();
                  throw e;
              }
          } catch (Exception ex) {
              return new ErrorResult(ex);
          } finally {
              sessionHolder.returnSession(s);
          }
      }
  
      private static boolean contains(String[] commands, String query) {
          for (String command : commands) {
              if (query.contains(command)) {
                  return true;
              }
          }
          return false;
      }
  
      private class ErrorResult extends QueryResult {
          ErrorResult(Exception e) {
              super(String.format("Unable to execute the query. Cause: %s", e.getMessage()), true);
              LOG.error("Unable to execute the query.", e);
          }
      }
  
      private abstract class QueryWork implements ReturningWork<QueryResult> {
          private String query;
  
          String getQuery() {
              return query;
          }
  
          QueryWork setQuery(String query) {
              this.query = query;
              return this;
          }
      }
  
      private class SelectWork extends QueryWork {
          @Override
          public QueryResult execute(Connection conn) throws SQLException {
              Statement stmt = null;
              ResultSet rset = null;
              try {
                  stmt = conn.createStatement();
                  rset = stmt.executeQuery(getQuery());
                  ResultSetMetaData meta = rset.getMetaData();
                  int columnCount = meta.getColumnCount();
                  QueryResult q = new QueryResult().setColumnNames(getColumnNames(columnCount, meta));
                  while (rset.next()) {
                      q.addRow(getRow(columnCount, rset));
                  }
                  return q;
              } catch (Exception ex) {
                  return new ErrorResult(ex);
              } finally {
                  SQLHelper.close(rset);
                  SQLHelper.close(stmt);
              }
          }
  
          protected Row getRow(int columnCount, ResultSet rset) throws SQLException {
              ArrayList<String> values = new ArrayList<String>(columnCount);
              for (int i = 1; i <= columnCount; ++i) {
                  values.add(rset.getString(i));
              }
              return new Row().setValues(values);
          }
  
          protected List<String> getColumnNames(int columnCount, ResultSetMetaData meta) throws SQLException {
              ArrayList<String> names = new ArrayList<String>(columnCount);
              for (int i = 1; i <= columnCount; ++i) {
                  names.add(meta.getColumnLabel(i));
              }
              return names;
          }
      }
  
      private class UpdateWork extends QueryWork {
          @Override
          public QueryResult execute(Connection conn) throws SQLException {
              Statement stmt = null;
              try {
                  stmt = conn.createStatement();
                  int result = stmt.executeUpdate(getQuery());
                  return new QueryResult(String.format("%d rows affected!", result));
              } catch (Exception ex) {
                  return new ErrorResult(ex);
              } finally {
                  SQLHelper.close(stmt);
              }
          }
      }
  
      private class ModifyWork extends QueryWork {
          @Override
          public QueryResult execute(Connection conn) throws SQLException {
              Statement stmt = null;
              try {
                  stmt = conn.createStatement();
                  stmt.execute(getQuery());
                  return new QueryResult("Success!");
              } catch (Exception ex) {
                  return new ErrorResult(ex);
              } finally {
                  SQLHelper.close(stmt);
              }
          }
      }
  }
  