/* * Copyright (c) 2018 Sony Pictures Imageworks Inc. * * 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()); } }