JdbcRequester.java

/*
 * Copyright (C) 2000 - 2024 Silverpeas
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 *
 * As a special exception to the terms and conditions of version 3.0 of
 * the GPL, you may redistribute this Program in connection with Free/Libre
 * Open Source Software ("FLOSS") applications as described in Silverpeas's
 * FLOSS exception.  You should have received a copy of the text describing
 * the FLOSS exception, and it is also available here:
 * "https://www.silverpeas.org/legal/floss_exception.html"
 *
 * 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 Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <https://www.gnu.org/licenses/>.
 */

package org.silverpeas.components.mydb.model;

import org.silverpeas.components.mydb.model.predicates.AbstractColumnValuePredicate;
import org.silverpeas.components.mydb.service.MyDBException;
import org.silverpeas.components.mydb.service.MyDBRuntimeException;
import org.silverpeas.core.admin.PaginationPage;
import org.silverpeas.core.persistence.Transaction;
import org.silverpeas.core.persistence.TransactionRuntimeException;
import org.silverpeas.core.persistence.jdbc.sql.JdbcSqlQuery;
import org.silverpeas.core.util.SilverpeasList;
import org.silverpeas.kernel.util.StringUtil;
import org.silverpeas.kernel.logging.SilverLogger;

import javax.annotation.Nonnull;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;

import static org.silverpeas.kernel.util.StringUtil.isDefined;

/**
 * A requester of a remote enterprise data source by using the yet configured
 * {@link MyDBConnectionInfo} instance. If
 * no {@link MyDBConnectionInfo} instance is
 * available, then no connection is established and no request can be done.
 * @author mmoquillon
 */
class JdbcRequester {

  private final MyDBConnectionInfo currentConnectionInfo;

  /**
   * Constructs a new JDBC requester with the specified {@link MyDBConnectionInfo} instance.
   * @param dsInfo a {@link MyDBConnectionInfo} instance.
   */
  JdbcRequester(final MyDBConnectionInfo dsInfo) {
    this.currentConnectionInfo = dsInfo;
  }

  /**
   * Is there is a data source set with this requester? A data source is set when there is a
   * {@link MyDBConnectionInfo} instance defined for the component instance to which this
   * requester is related.
   * @return true if there is a {@link MyDBConnectionInfo} instance set with this requester.
   * False otherwise.
   */
  private boolean isDataSourceDefined() {
    return currentConnectionInfo.isDefined();
  }

  /**
   * Gets all the tables in the data source (only public tables and views are fetched). If an error
   * occurs while requesting the data source, a {@link MyDBRuntimeException} is thrown.
   * @return a list of table names.
   */
  List<String> getTableNames() {
    try (Connection connection = currentConnectionInfo.openConnection()) {
      return getTableNames(connection);
    } catch (SQLException | MyDBException e) {
      throw new MyDBRuntimeException(e);
    }
  }

  /**
   * Performs the specified database operations within the same connection with the data source and
   * within a same transaction. If no data source is defined, then a {@link MyDBRuntimeException}
   * is thrown. If the database * operations throw an exception, then a
   * {@link MyDBRuntimeException} is thrown.
   * @param operations the operation to execute on the database.
   */
  <T> T perform(final DbOperation<T> operations) {
    Objects.requireNonNull(operations);
    if (!isDataSourceDefined()) {
      throw new MyDBRuntimeException("No data source defined!");
    }
    try {
      return Transaction.performInOne(() -> {
        try (Connection connection = currentConnectionInfo.openConnection()) {
          return operations.execute(this, connection);
        }
      });
    } catch (TransactionRuntimeException e) {
      throw new MyDBRuntimeException(e);
    }
  }

  private List<String> getTableNames(final Connection connection) throws SQLException {
    Objects.requireNonNull(connection);
    final List<String> tableNames = new ArrayList<>();
    DatabaseMetaData dbMetaData = connection.getMetaData();
    ResultSet tables = dbMetaData.getTables(null, null, null, new String[]{"TABLE", "VIEW"});
    while (tables.next()) {
      String table = tables.getString("TABLE_NAME");
      if (isAuthorizedTable(connection, table)) {
        tableNames.add(table);
      }
    }
    return tableNames;
  }

  private boolean isAuthorizedTable(final Connection connection, String tableName) {
    try {
      JdbcSqlQuery.countAll().from(tableName).executeWith(connection);
      return true;
    } catch (SQLException e) {
      return false;
    }
  }

  /**
   * Gets the definition of all the columns of the specified database table and passes them to the
   * specified {@link ColumnConsumer} function.
   * @param connection a connection to the database.
   * @param tableName the name of the table from which the columns have to be get.
   * @param consumer the consumer of the column definitions.
   * @throws SQLException if an error occurs while requesting the database.
   */
  void loadColumns(final Connection connection, final String tableName,
      final ColumnConsumer consumer) throws SQLException {
    Objects.requireNonNull(connection);
    final DatabaseMetaData dbMetaData = connection.getMetaData();
    final List<String> columnPks = getPrimaryKeys(connection, tableName, dbMetaData);
    final Map<String, ForeignKeyDescriptor> columnFks =
        getForeignKeys(connection, tableName, dbMetaData);
    final ResultSet columns = dbMetaData.getColumns(connection.getCatalog(), null, tableName, null);
    while (columns.next()) {
      final String name = columns.getString("COLUMN_NAME");
      final int type = columns.getInt("DATA_TYPE");
      final int size = columns.getInt("COLUMN_SIZE");
      final boolean isAutoIncremented =
          StringUtil.getBooleanValue(columns.getString("IS_AUTOINCREMENT"));
      final boolean isNullable = StringUtil.getBooleanValue(columns.getString("IS_NULLABLE"));
      final boolean isPrimaryKey = columnPks.contains(name);
      final DefaultValue defaultValue = getDefaultValue(columns);
      consumer.accept(new ColumnDescriptor().withName(name)
          .withType(type)
          .withSize(size)
          .withPrimaryKey(isPrimaryKey)
          .withForeignKey(columnFks.get(name))
          .withNullable(isNullable)
          .withAutoIncrement(isAutoIncremented)
          .withDefaultValue(defaultValue));
    }
  }

  private Map<String, ForeignKeyDescriptor> getForeignKeys(final Connection connection,
      final String tableName, final DatabaseMetaData dbMetaData) throws SQLException {
    final Map<String, ForeignKeyDescriptor> columnFks = new LinkedHashMap<>(4);
    final ResultSet foreignKeys =
        dbMetaData.getImportedKeys(connection.getCatalog(), connection.getSchema(), tableName);
    while (foreignKeys.next()) {
      final String fkName = foreignKeys.getString("FK_NAME");
      final String columnName = foreignKeys.getString("FKCOLUMN_NAME");
      final String targetTableName = foreignKeys.getString("PKTABLE_NAME");
      final String targetColumnName = foreignKeys.getString("PKCOLUMN_NAME");
      columnFks.put(columnName,
          new ForeignKeyDescriptor(fkName, targetTableName, targetColumnName));
    }
    return columnFks;
  }

  private List<String> getPrimaryKeys(final Connection connection, final String tableName,
      final DatabaseMetaData dbMetaData) throws SQLException {
    final List<String> columnPks = new ArrayList<>(2);
    final ResultSet primaryKeys =
        dbMetaData.getPrimaryKeys(connection.getCatalog(), connection.getSchema(), tableName);
    while (primaryKeys.next()) {
      final String pk = primaryKeys.getString("COLUMN_NAME");
      columnPks.add(pk);
    }
    return columnPks;
  }

  /**
   * Requests the content of the specified table by applying the given predicate and uses the
   * specified converters to convert each row and each row's value to their corresponding business
   * object, ready to be handled by the caller.
   * @param <V> the type of the business objects representing the row's values.
   * @param <R> the type of the business objects representing the rows.
   * @param connection a connection to the database.
   * @param tableName the name of the table to request.
   * @param predicate a predicate to use to filter the table's content.
   * @param orderBy a order by directive already built (without the clause key words).
   * @param converters the converters to use to convert each row and each row's value to a business
   * object.
   * @param pagination a pagination in order to avoid bad performances.
   * @return a list of rows, matching the given predicate, in their business representation. If no
   * rows match the specified predicate or if the table is empty, an empty list is returned.
   * @throws SQLException if an error occurs while requesting the database.
   */
  <V, R> SilverpeasList<R> request(final Connection connection, final String tableName,
      final AbstractColumnValuePredicate predicate, final String orderBy, final DataConverters<V, R> converters,
      final PaginationPage pagination)
      throws SQLException {
    Objects.requireNonNull(connection);
    Objects.requireNonNull(tableName);
    Objects.requireNonNull(predicate);
    JdbcSqlQuery query = JdbcSqlQuery.select("*").from(tableName);
    query = isDefined(orderBy) ? predicate.apply(query).orderBy(orderBy) : predicate.apply(query);
    if (pagination != null) {
      query.withPagination(pagination.asCriterion());
    }
    return query.executeWith(connection, rs -> {
      try {
        final Map<String, V> row = new LinkedHashMap<>();
        ResultSetMetaData rsMetaData = rs.getMetaData();
        for (int i = 1; i <= rsMetaData.getColumnCount(); i++) {
          V value = converters.getValueConverter()
              .convert(rs.getObject(i), rs.getMetaData().getColumnType(i));
          row.put(rsMetaData.getColumnName(i), value);
        }
        return converters.getRowConverter().convert(row);
      } catch (SQLException e) {
        throw new MyDBRuntimeException(e);
      }
    });
  }

  /**
   * Deletes all the rows in the specified table name that match the specified predicate. If no
   * criteria is given, then the table is emptied. If the table is already empty, nothing is done.
   * @param connection a connection to the database.
   * @param tableName the name of the table in which some of the rows have to be deleted.
   * @param criteria the criteria the rows to delete have to match.
   * @throws SQLException if an error occurs while deleting the rows in the specified table.
   */
  long delete(final Connection connection, final String tableName,
      final Map<String, Object> criteria) throws SQLException {
    final JdbcSqlQuery query = JdbcSqlQuery.deleteFrom(tableName);
    return applyCriteria(query, criteria).executeWith(connection);
  }

  /**
   * Updates the rows in the specified table that match the given criteria with the values defined
   * in the specified dictionary <code>values</code>.
   * @param connection a connection to the database.
   * @param tableName the name of the table in which the rows will be updated.
   * @param values the values with which the rows will be updated.
   * @param criteria the criteria the rows have to match.
   * @throws SQLException if an error occurs while updating the rows in the specified table.
   */
  long update(final Connection connection, final String tableName, final Map<String, Object> values,
      Map<String, Object> criteria) throws SQLException {
    final JdbcSqlQuery query = JdbcSqlQuery.update(tableName);
    values.forEach(query::withUpdateParam);
    return applyCriteria(query, criteria).executeWith(connection);
  }

  /**
   * Inserts into the specified table a new row with the specified values.
   * @param connection a connection to the database.
   * @param tableName the name of the table into which the row will be inserted.
   * @param values the values of the row to insert as a map of column names to column values.
   */
  void insert(final Connection connection, final String tableName, final Map<String, Object> values)
      throws SQLException {
    final JdbcSqlQuery query = JdbcSqlQuery.insertInto(tableName);
    values.forEach(query::withInsertParam);
    query.executeWith(connection);
  }

  private JdbcSqlQuery applyCriteria(final JdbcSqlQuery query, final Map<String, Object> criteria) {
    final StringBuilder clauses = new StringBuilder(criteria.size());
    final List<Object> values = new ArrayList<>(criteria.size());
    final String conjunction = " and ";
    criteria.forEach((key, value) -> {
      if (value == null) {
        clauses.append(key).append(" is null").append(conjunction);
      } else {
        clauses.append(key).append(" = ?").append(conjunction);
        values.add(value);
      }
    });
    clauses.setLength(clauses.length() - conjunction.length());
    return query.where(clauses.toString(), values);
  }

  private DefaultValue getDefaultValue(final ResultSet resultSet)
      throws SQLException {
    String defaultValue = resultSet.getString("COLUMN_DEF");
    return new DefaultValue(defaultValue);
  }

  /**
   * A provider of converters of data. They convert database data to their corresponding business
   * data.
   * @param <V> the business type representing a column's value in a given row of a database table.
   * @param <R> the business type representing a given row of a database table.
   */
  static class DataConverters<V, R> {
    private final ValueConverter<V> valueConverter;
    private final RowConverter<V, R> rowConverter;

    DataConverters(final ValueConverter<V> valueConverter, final RowConverter<V, R> rowConverter) {
      this.valueConverter = valueConverter;
      this.rowConverter = rowConverter;
    }

    /**
     * Converter of a database table row's value to a business object representing that value.
     * @return a {@link ValueConverter} instance.
     */
    ValueConverter<V> getValueConverter() {
      return valueConverter;
    }

    /**
     * Converter of a database table row to a business object representing that row.
     * @return a {@link RowConverter} instance.
     */
    RowConverter<V, R> getRowConverter() {
      return rowConverter;
    }
  }

  /**
   * Descriptor of a foreign key. It should be mapped to a column in a given table for which it
   * references another column of another table.
   */
  static class ForeignKeyDescriptor {
    private final String name;
    private final String targetTableName;
    private final String targetColumnName;

    private ForeignKeyDescriptor(final String name, final String targetTableName,
        final String targetColumnName) {
      this.name = name;
      this.targetTableName = targetTableName;
      this.targetColumnName = targetColumnName;
    }

    /**
     * Gets the name of the foreign key. Useful to retrieve the columns that made up a composite
     * foreign keys.
     * @return the name of the foreign key.
     */
    String getName() {
      return name;
    }

    /**
     * Gets the name of the table targeted by this key.
     * @return the name of the targeted table.
     */
    String getTargetTableName() {
      return targetTableName;
    }

    /**
     * Gets the name of the column in the targeted table that is targeted by this key.
     * @return the name of the targeted column.
     */
    String getTargetColumnName() {
      return targetColumnName;
    }
  }

  class DefaultValue {
    private final String pattern;

    public DefaultValue(final String valuePattern) {
      this.pattern = valuePattern;
    }

    public boolean isDefined() {
      return pattern != null;
    }

    public String get() {
      String value = null;
      if (isDefined()) {
        try (Connection connection = currentConnectionInfo.openConnection()) {
          value = computeSQLFunction(connection, pattern);
        } catch (MyDBException | SQLException e) {
          throw new MyDBRuntimeException(e);
        }
        int index = value.indexOf("::");
        if (index != -1) {
          value = parseDefaultValue(value, index);
        }
      }
      return value;
    }

    @Nonnull
    private String parseDefaultValue(String defaultValue, final int valueLength) {
      defaultValue = defaultValue.substring(0, valueLength);
      if (defaultValue.startsWith("'") && defaultValue.endsWith("'")) {
        defaultValue = defaultValue.substring(1, defaultValue.length() - 1);
      }
      return defaultValue;
    }

    @Nonnull
    private String computeSQLFunction(final Connection connection, final String function) {
      try {
        return JdbcSqlQuery.select(function)
            .executeUniqueWith(connection, r -> r.getString(1));
      } catch (SQLException e) {
        // not a function or cannot be computed by the database
        SilverLogger.getLogger(this).silent(e);
        return function;
      }
    }
  }

  /**
   * A descriptor of a column in a database table.
   */
  class ColumnDescriptor {
    private String name;
    private int type;
    private int size;
    private boolean primaryKey;
    private ForeignKeyDescriptor foreignKey;
    private boolean nullable;
    private boolean autoIncrementable;
    private DefaultValue defaultValue;

    private ColumnDescriptor() {
    }

    /**
     * Gets the name of the column.
     * @return the unique name of the column in the table.
     */
    String getName() {
      return name;
    }

    private ColumnDescriptor withName(final String name) {
      this.name = name;
      return this;
    }

    /**
     * Gets the SQL type code of this column.
     * @return the code of the SQL type of the values that column accepts.
     * @see java.sql.Types
     */
    int getType() {
      return type;
    }

    private ColumnDescriptor withType(final int type) {
      this.type = type;
      return this;
    }

    /**
     * Gets the size of the column SQL type.
     * @return the size of the column type.
     */
    int getSize() {
      return size;
    }

    private ColumnDescriptor withSize(final int size) {
      this.size = size;
      return this;
    }

    /**
     * Is this column a primary key?
     * @return true if the values of this column are primary keys. False otherwise.
     */
    boolean isPrimaryKey() {
      return primaryKey;
    }

    private ColumnDescriptor withPrimaryKey(final boolean primaryKey) {
      this.primaryKey = primaryKey;
      return this;
    }

    /**
     * Is this column can be nullable.
     * @return true if this column can be valued with null, false otherwise.
     */
    boolean isNullable() {
      return nullable;
    }

    private ColumnDescriptor withNullable(final boolean nullable) {
      this.nullable = nullable;
      return this;
    }

    /**
     * Is the valuation of this column can be incrementable?
     * @return true of the valuation of this column is taken in charge by the database. False
     * otherwise.
     */
    boolean isAutoIncrementable() {
      return autoIncrementable;
    }

    private ColumnDescriptor withAutoIncrement(final boolean isAutoIncrement) {
      this.autoIncrementable = isAutoIncrement;
      return this;
    }

    /**
     * Gets the default value of this column if any.
     * @return the default value set with this column, null if no such a default value was set.
     */
    DefaultValue getDefaultValue() {
      return defaultValue;
    }

    private ColumnDescriptor withDefaultValue(final DefaultValue defaultValue) {
      this.defaultValue = defaultValue;
      return this;
    }

    /**
     * Gets a descriptor of the foreign key that is mapped with this column.
     * @return a foreign key descriptor or null if this column isn't a foreign key.
     */
    ForeignKeyDescriptor getForeignKey() {
      return this.foreignKey;
    }

    private ColumnDescriptor withForeignKey(final ForeignKeyDescriptor foreignKey) {
      this.foreignKey = foreignKey;
      return this;
    }
  }

  /**
   * An operation on the behalf of the database.
   * @param <T> the type of the result.
   */
  @FunctionalInterface
  interface DbOperation<T> {
    T execute(final JdbcRequester requester, final Connection connection) throws SQLException;
  }

  /**
   * A consumer of a database column description.
   */
  @FunctionalInterface
  interface ColumnConsumer {
    void accept(final ColumnDescriptor column);
  }

  /**
   * A converter of the database row to a business object.
   * @param <V> the type of the business value in a row.
   * @param <R> the type of the business row.
   */
  @FunctionalInterface
  interface RowConverter<V, R> {
    R convert(final Map<String, V> row);
  }

  /**
   * A converter of a value in a column to a business object.
   * @param <V> the type of the business value.
   */
  @FunctionalInterface
  interface ValueConverter<V> {
    V convert(final Object value, final int valueType);
  }
}