MediaSQLQueryBuilder.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.gallery.dao;

import org.silverpeas.core.date.DateTime;
import org.silverpeas.components.gallery.constant.MediaType;
import org.silverpeas.components.gallery.model.Media;
import org.silverpeas.components.gallery.model.MediaCriteriaProcessor;
import org.silverpeas.components.gallery.model.MediaLogicalComparator;
import org.silverpeas.core.admin.user.model.UserDetail;
import org.apache.commons.lang3.time.DateUtils;
import org.silverpeas.components.gallery.model.MediaCriteria;
import org.silverpeas.core.persistence.jdbc.sql.JdbcSqlQuery;
import org.silverpeas.core.util.CollectionUtil;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;

/**
 * A dynamic builder of a SQL query.
 */
public class MediaSQLQueryBuilder implements MediaCriteriaProcessor {

  private final boolean count;
  private StringBuilder orderBy = null;
  private boolean done = false;
  private final StringBuilder sqlQuery = new StringBuilder();
  private final StringBuilder from = new StringBuilder();
  private final StringBuilder where = new StringBuilder();
  private final List<Object> parameters = new ArrayList<>();
  private String conjunction = "";
  private List<MediaCriteria.QUERY_ORDER_BY> logicalOrderBy;
  private boolean distinct = false;
  private int resultLimit = 0;

  private MediaSQLQueryBuilder(boolean count) {
    this.count = count;
  }

  static MediaSQLQueryBuilder selectBuilder() {
    return new MediaSQLQueryBuilder(false);
  }

  static MediaSQLQueryBuilder countBuilder() {
    return new MediaSQLQueryBuilder(true);
  }

  @Override
  public void startProcessing() {
    if (!count) {
      sqlQuery.append("select M.mediaId, M.mediaType, M.instanceId")
          .append(", M.title, M.description, M.author, M.keyWord")
          .append(", M.beginVisibilityDate, M.endVisibilityDate")
          .append(", M.createDate, M.createdBy, M.lastUpdateDate, M.lastUpdatedBy ");
    } else {
      sqlQuery.append("select count(M.mediaId) ");
    }
    from.append("from SC_Gallery_Media M ");
  }

  @Override
  public void endProcessing() {
    if (distinct) {
      sqlQuery.insert((count ? 13 : 7), "distinct ");
    }
    sqlQuery.append(from.toString());
    if (where.length() > 0) {
      sqlQuery.append(" where ").append(where.toString());
    }
    if (orderBy != null && orderBy.length() > 0) {
      sqlQuery.append(orderBy.toString());
    }
    done = true;
  }

  @SuppressWarnings("unchecked")
  @Override
  public JdbcSqlQuery result() {
    return JdbcSqlQuery.create(sqlQuery.toString(), parameters)
        .configure(config -> config.withResultLimit(resultLimit));
  }

  @Override
  public MediaCriteriaProcessor then() {
    if (!done) {
      conjunction = " and ";
    }
    return this;
  }

  @Override
  public MediaCriteriaProcessor processResultLimit(final int resultLimit) {
    this.resultLimit = resultLimit;
    return this;
  }

  @Override
  public MediaCriteriaProcessor processVisibility(final MediaCriteria.VISIBILITY visibility,
      final Date dateReference, final UserDetail creator) {
    switch (visibility) {
      case VISIBLE_ONLY:
      case HIDDEN_ONLY:
      case BY_DEFAULT:
        setVisibilityClause(visibility, dateReference, creator);
        break;
      case FORCE_GET_ALL:
        // No clause
        break;
    }
    conjunction = "";
    return this;
  }

  private void setVisibilityClause(final MediaCriteria.VISIBILITY visibility,
      final Date dateReference, final UserDetail creator) {
    final StringBuilder clause;
    if (visibility == MediaCriteria.VISIBILITY.HIDDEN_ONLY) {
      clause =
          where(conjunction).append("((? < M.beginVisibilityDate or ? > M.endVisibilityDate)");
      parameters.add(dateReference.getTime());
      parameters.add(dateReference.getTime());
    } else {
      clause = where(conjunction)
          .append("((? between M.beginVisibilityDate and M.endVisibilityDate)");
      parameters.add(dateReference.getTime());
    }
    if (creator != null) {
      clause.append(" or M.createdBy = ?");
      parameters.add(creator.getId());
    }
    clause.append(")");
  }

  @Override
  public MediaCriteriaProcessor processComponentInstance(String componentInstanceId) {
    if (!done) {
      where(conjunction).append("M.instanceId = ?");
      parameters.add(componentInstanceId);
      conjunction = "";
    }
    return this;
  }

  @Override
  public MediaCriteriaProcessor processAlbums(final List<String> albumIds) {
    if (!done) {
      StringBuilder params = new StringBuilder();
      for (String albumId : albumIds) {
        if (params.length() > 0) {
          params.append(",");
        }
        params.append("?");
        parameters.add(Integer.valueOf(albumId));
      }
      distinct = true;
      from.append(
          "join SC_Gallery_Path A on M.mediaId = A.mediaId and M.instanceId = A.instanceId ");
      where(conjunction).append("A.nodeId in (").append(params.toString()).append(")");
      conjunction = "";
    }
    return this;
  }

  @Override
  public MediaCriteriaProcessor processCreator(UserDetail creator) {
    if (!done) {
      where(conjunction).append("M.createdBy = ?");
      parameters.add(creator.getId());
      conjunction = "";
    }
    return this;
  }

  @Override
  public MediaCriteriaProcessor processMediaTypes(List<MediaType> mediaTypes) {
    if (!done) {
      StringBuilder params = new StringBuilder();
      for (MediaType mediaType : mediaTypes) {
        if (params.length() > 0) {
          params.append(",");
        }
        params.append("?");
        parameters.add(mediaType.name());
      }
      where(conjunction).append("M.mediaType in (").append(params.toString()).append(")");
      conjunction = "";
    }
    return this;
  }

  @Override
  public MediaCriteriaProcessor processNbDaysBeforeThatMediaIsNotVisible(final Date referenceDate,
      final int nbDaysBeforeThatMediaIsNotVisible) {
    if (!done) {
      where(conjunction).append("M.endVisibilityDate between ? and ?");
      DateTime date =
          new DateTime(DateUtils.addDays(referenceDate, nbDaysBeforeThatMediaIsNotVisible));
      parameters.add(date.getBeginOfDay().getTime());
      parameters.add(date.getEndOfDay().getTime());
      conjunction = "";
    }
    return this;
  }

  @Override
  public MediaCriteriaProcessor processOrdering(List<MediaCriteria.QUERY_ORDER_BY> orderings) {
    if (!done) {
      for (MediaCriteria.QUERY_ORDER_BY anOrdering : orderings) {
        if (!anOrdering.isApplicableOnSQLQuery()) {
          logicalOrderBy = orderings;
          orderBy = null;
          break;
        }
        if (orderBy == null) {
          orderBy = new StringBuilder(" order by ");
        } else {
          orderBy.append(", ");
        }
        orderBy.append(anOrdering.getInstructionBase());
        orderBy.append(" ");
        orderBy.append(anOrdering.isAsc() ? "asc" : "desc");
      }
      conjunction = "";
    }
    return this;
  }

  @Override
  public MediaCriteriaProcessor processIdentifiers(List<String> identifiers) {
    if (!done) {
      StringBuilder params = new StringBuilder();
      for (String identifier : identifiers) {
        if (params.length() > 0) {
          params.append(",");
        }
        params.append("?");
        parameters.add(identifier);
      }
      where(conjunction).append("M.mediaId in (").append(params.toString()).append(")");
      conjunction = "";
    }
    return this;
  }

  @Override
  public List<Media> orderingResult(final List<Media> media) {
    if (CollectionUtil.isNotEmpty(logicalOrderBy)) {
      Collections.sort(media, MediaLogicalComparator.on(logicalOrderBy));
    }
    return media;
  }

  /**
   * Centralization to perform the where clause.
   * @param conjunction the conjunction.
   * @return the builder completed with conjunction.
   */
  private StringBuilder where(String conjunction) {
    if (where.length() > 0) {
      where.append(conjunction);
    }
    return where;
  }
}