content: 
  /*
   * Copyright Contributors to the OpenCue Project
   *
   * Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except
   * in compliance with the License. You may obtain a copy of the License at
   *
   * http://www.apache.org/licenses/LICENSE-2.0
   *
   * Unless required by applicable law or agreed to in writing, software distributed under the License
   * is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express
   * or implied. See the License for the specific language governing permissions and limitations under
   * the License.
   */
  
  package com.imageworks.spcue.dao.postgres;
  
  import java.sql.CallableStatement;
  import java.sql.Connection;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.util.ArrayList;
  import java.util.List;
  
  import org.springframework.jdbc.core.CallableStatementCreator;
  import org.springframework.jdbc.core.RowMapper;
  import org.springframework.jdbc.core.SqlParameter;
  import org.springframework.jdbc.core.support.JdbcDaoSupport;
  
  import com.imageworks.spcue.FilterEntity;
  import com.imageworks.spcue.FilterInterface;
  import com.imageworks.spcue.ShowInterface;
  import com.imageworks.spcue.dao.FilterDao;
  import com.imageworks.spcue.grpc.filter.FilterType;
  import com.imageworks.spcue.util.SqlUtil;
  
  /**
   * A DAO class for loading Filters, Actions, and Matchers. Part of the job filtering system.
   *
   * @category DAO
   */
  public class FilterDaoJdbc extends JdbcDaoSupport implements FilterDao {
  
      private static final String GET_FILTER = "SELECT " + "filter.* " + "FROM " + "filter ";
  
      private static final String GET_ACTIVE_FILTERS = "SELECT " + "filter.* " + "FROM " + "filter "
              + "WHERE " + "b_enabled = true " + "AND " + "pk_show=? " + "ORDER BY " + "f_order ASC";
  
      private static final String GET_FILTERS = "SELECT " + "filter.* " + "FROM " + "filter "
              + "WHERE " + "pk_show=? " + "ORDER BY " + "f_order ASC";
  
      public static final RowMapper<FilterEntity> FILTER_DETAIL_MAPPER =
              new RowMapper<FilterEntity>() {
                  public FilterEntity mapRow(ResultSet rs, int rowNum) throws SQLException {
                      FilterEntity d = new FilterEntity();
                      d.type = FilterType.valueOf(rs.getString("str_type"));
                      d.id = rs.getString("pk_filter");
                      d.name = rs.getString("str_name");
                      d.showId = rs.getString("pk_show");
                      d.enabled = rs.getBoolean("b_enabled");
                      d.order = rs.getFloat("f_order");
                      return d;
                  }
              };
  
      public List<FilterEntity> getActiveFilters(ShowInterface show) {
          return getJdbcTemplate().query(GET_ACTIVE_FILTERS, FILTER_DETAIL_MAPPER, show.getShowId());
      }
  
      public List<FilterEntity> getFilters(ShowInterface show) {
          return getJdbcTemplate().query(GET_FILTERS, FILTER_DETAIL_MAPPER, show.getShowId());
      }
  
      public void deleteFilter(FilterInterface f) {
          getJdbcTemplate().update("DELETE FROM action WHERE pk_filter=?", f.getFilterId());
          getJdbcTemplate().update("DELETE FROM matcher WHERE pk_filter=?", f.getFilterId());
          getJdbcTemplate().update("DELETE FROM filter WHERE pk_filter=?", f.getFilterId());
          reorderFilters(f);
      }
  
      private static final String INSERT_FILTER = "INSERT INTO " + "filter " + "(" + "pk_filter,"
              + "pk_show," + "str_name," + "str_type," + "f_order "
              + ") VALUES (?,?,?,?,(SELECT COALESCE(MAX(f_order)+1,1) FROM filter WHERE pk_show=?))";
  
      public void insertFilter(FilterEntity f) {
          f.id = SqlUtil.genKeyRandom();
          getJdbcTemplate().update(INSERT_FILTER, f.id, f.getShowId(), f.name, f.type.toString(),
                  f.getShowId());
          reorderFilters(f);
      }
  
      public void updateSetFilterEnabled(FilterInterface f, boolean enabled) {
          getJdbcTemplate().update("UPDATE filter SET b_enabled=? WHERE pk_filter=?", enabled,
                  f.getFilterId());
      }
  
      public void updateSetFilterName(FilterInterface f, String name) {
          getJdbcTemplate().update("UPDATE filter SET str_name=? WHERE pk_filter=?", name,
                  f.getFilterId());
      }
  
      public void updateSetFilterOrder(FilterInterface f, double order) {
          getJdbcTemplate().update("UPDATE filter SET f_order=? - 0.1 WHERE pk_filter=?", order,
                  f.getFilterId());
          reorderFilters(f);
      }
  
      public void lowerFilterOrder(FilterInterface f, int by) {
          double lower_by = by + 0.1;
          getJdbcTemplate().update("UPDATE filter SET f_order=f_order + ? WHERE pk_filter=?",
                  lower_by, f.getFilterId());
          reorderFilters(f);
      }
  
      public void raiseFilterOrder(FilterInterface f, int by) {
          double raise_by = (by * -1) - 0.1;
          getJdbcTemplate().update("UPDATE filter SET f_order=f_order + ? WHERE pk_filter=?",
                  raise_by, f.getFilterId());
          reorderFilters(f);
      }
  
      public void updateSetFilterType(FilterInterface f, FilterType type) {
          getJdbcTemplate().update("UPDATE filter SET str_type=? WHERE pk_filter=?", type.toString(),
                  f.getFilterId());
      }
  
      public void reorderFilters(final ShowInterface s) {
          getJdbcTemplate().update("LOCK TABLE filter IN SHARE MODE");
          getJdbcTemplate().call(new CallableStatementCreator() {
  
              public CallableStatement createCallableStatement(Connection con) throws SQLException {
                  CallableStatement c = con.prepareCall("{ call reorder_filters(?) }");
                  c.setString(1, s.getShowId());
                  return c;
              }
          }, new ArrayList<SqlParameter>());
      }
  
      public FilterEntity findFilter(ShowInterface show, String name) {
          return getJdbcTemplate().queryForObject(GET_FILTER + " WHERE pk_show=? AND str_name=?",
                  FILTER_DETAIL_MAPPER, show.getShowId(), name);
      }
  
      public FilterEntity getFilter(String id) {
          return getJdbcTemplate().queryForObject(GET_FILTER + " WHERE pk_filter=?",
                  FILTER_DETAIL_MAPPER, id);
      }
  
      public FilterEntity getFilter(FilterInterface filter) {
          return getJdbcTemplate().queryForObject(GET_FILTER + " WHERE pk_filter=?",
                  FILTER_DETAIL_MAPPER, filter.getFilterId());
      }
  }
  