001/*
002 * Stallion Core: A Modern Web Framework
003 *
004 * Copyright (C) 2015 - 2016 Stallion Software LLC.
005 *
006 * This program is free software: you can redistribute it and/or modify it under the terms of the
007 * GNU General Public License as published by the Free Software Foundation, either version 2 of
008 * the License, or (at your option) any later version. This program is distributed in the hope that
009 * it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
010 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public
011 * License for more details. You should have received a copy of the GNU General Public License
012 * along with this program.  If not, see <http://www.gnu.org/licenses/gpl-2.0.html>.
013 *
014 *
015 *
016 */
017
018package io.stallion.dataAccess.filtering;
019
020import io.stallion.dataAccess.Model;
021import io.stallion.dataAccess.db.DB;
022import io.stallion.dataAccess.db.Schema;
023import io.stallion.dataAccess.db.SmartQueryCache;
024import io.stallion.exceptions.UsageException;
025import io.stallion.utils.DateUtils;
026import io.stallion.utils.Literals;
027
028import java.text.MessageFormat;
029import java.time.ZonedDateTime;
030import java.time.format.DateTimeFormatter;
031import java.util.ArrayList;
032import java.util.List;
033
034import static io.stallion.utils.Literals.apply;
035import static io.stallion.utils.Literals.asArray;
036
037/**
038 * A filter chain that generates a MySQL query, rather than operating on
039 * an in memory datastructure
040 *
041 * @param <T>
042 */
043public class MySqlFilterChain<T extends Model> extends FilterChain<T> {
044    private Class<T> clazz;
045    private String table;
046
047    public MySqlFilterChain(String table, String bucket, Class<T> clazz) {
048        super(bucket, null);
049        this.clazz = clazz;
050        this.table = table;
051    }
052
053    public MySqlFilterChain(String table, FilterChain chain) {
054        super(chain, null);
055        this.table = table;
056    }
057
058    public Schema getSchema() {
059        return DB.instance().getSchema(clazz);
060    }
061
062    protected FilterChain<T> newCopy() {
063        FilterChain<T> chain = new MySqlFilterChain<T>(table, this.getBucket(), this.clazz);
064        chain.operations = (ArrayList<FilterOperation>)getOperations().clone();
065        chain.setIncludeDeleted(getIncludeDeleted());
066        return chain;
067    }
068
069    @Override
070    protected void process(int page, int size, boolean fetchTotalCount)  {
071
072        if (page < 1) {
073            page = 1;
074        }
075
076        StringBuilder whereBuilder = new StringBuilder();
077        StringBuilder sqlBuilder = new StringBuilder();
078        sqlBuilder.append("SELECT * FROM " + getSchema().getName());
079
080        int x = 0;
081        List<Object> params = new ArrayList<>();
082        boolean hasDeletedOp = false;
083        for(FilterOperation op: getOperations()) {
084            if (getSchema().getColumns().contains(op.getFieldName())) {
085                throw new UsageException(MessageFormat.format("Trying to filter on a field that is not in the schema: {0} schema:{1}", op.getFieldName(), clazz.getName()));
086            }
087            if (x >= 1) {
088                whereBuilder.append(" AND ");
089            }
090            if (op.getIsExclude() == true) {
091                whereBuilder.append(" NOT ");
092            }
093            if (op.getFieldName().equals("deleted")) {
094                hasDeletedOp = true;
095            }
096
097            if (op.isOrOperation()) {
098                addOrOperation(op, whereBuilder, params);
099            } else {
100                if (op.getOperator().equals(FilterOperator.ANY)) {
101                    addInClause(whereBuilder, op, params);
102                } else {
103                    whereBuilder.append(MessageFormat.format(" ({0} {1} ?) ", op.getFieldName(), op.getOperator().forSql()));
104                    params.add(formatParam(op));
105                }
106
107            }
108            x++;
109        }
110        if (!getIncludeDeleted() && !hasDeletedOp) {
111            if (whereBuilder.length() > 0) {
112                whereBuilder.append(" AND ");
113            }
114            whereBuilder.append(" deleted=0 ");
115        }
116        String whereSql = whereBuilder.toString();
117        if (whereSql.trim().length() > 0) {
118            whereSql = " WHERE " + whereSql;
119            sqlBuilder.append(whereSql);
120        }
121        if (!Literals.empty(getSortField())) {
122            List<String> columnNames = apply(getSchema().getColumns(), col->col.getName());
123            if (!"id".equals(getSortField()) && !columnNames.contains(getSortField().toLowerCase())) {
124                throw new UsageException(MessageFormat.format("Sort field not found in schema: field={0} schema={1}", getSortField(), clazz.getName()));
125            }
126            sqlBuilder.append(" ORDER BY " + getSortField() + " " + getSortDirection().forSql());
127        }
128        sqlBuilder.append(" LIMIT " + (page - 1) * size + ", " + size);
129
130        Object[] paramObjects = params.toArray();
131        setObjects(DB.instance().query(clazz, sqlBuilder.toString(), paramObjects));
132
133        if (page == 1 && getObjects().size() < size) {
134            setMatchingCount(getObjects().size());
135        } else if (fetchTotalCount) {
136            String countSql = "SELECT COUNT(*) FROM " + getSchema().getName() + whereSql;
137            Object count = DB.instance().queryScalar(countSql, paramObjects);
138            Integer countInt = count instanceof Integer ? (Integer)count : ((Long)count).intValue();
139            setMatchingCount(countInt);
140        }
141
142    }
143
144    private void addOrOperation(FilterOperation or, StringBuilder whereBuilder, List<Object> params) {
145        whereBuilder.append("(");
146        for (int x = 0; x < or.getOrSubOperations().size(); x++) {
147            if (x > 0) {
148                whereBuilder.append(" OR ");
149            }
150            FilterOperation subOp = or.getOrSubOperations().get(x);
151            if (subOp.isOrOperation()) {
152                addOrOperation(subOp, whereBuilder, params);
153                continue;
154            }
155            if (subOp.getIsExclude()) {
156                whereBuilder.append(" NOT ");
157            }
158            if (subOp.getOperator().equals(FilterOperator.ANY)) {
159                addInClause(whereBuilder, subOp, params);
160            } else {
161                whereBuilder.append("(`" + subOp.getFieldName() + "`" + subOp.getOperator().forSql() + " ? )");
162                params.add(formatParam(subOp));
163            }
164
165
166
167        }
168        whereBuilder.append(")");
169    }
170
171    private void addInClause(StringBuilder whereBuilder, FilterOperation op, List<Object> params) {
172        StringBuilder placeholders = new StringBuilder();
173        placeholders.append(" (");
174        Object[] inParams = null;
175        if (op.getOriginalValue() instanceof List) {
176            inParams = asArray((List)op.getOriginalValue(), Object.class);
177        } else {
178            inParams = (Object[])op.getOriginalValue();
179        }
180
181        for(int x = 0; x < inParams.length; x++) {
182            if (x >= (inParams.length - 1)) {
183                placeholders.append("?");
184            } else {
185                placeholders.append("?,");
186            }
187            params.add(inParams[x]);
188        }
189        placeholders.append(") ");
190        whereBuilder.append("(`" + op.getFieldName() + "` IN " + placeholders.toString() + ")");
191    }
192
193    private Object formatParam(FilterOperation op) {
194        Object val = op.getOriginalValue();
195        if (op.getOperator().equals(FilterOperator.LIKE)) {
196            return "%" + val.toString() + "%";
197        } else if (val instanceof ZonedDateTime) {
198            return DateUtils.SQL_FORMAT.format(((ZonedDateTime) val).withZoneSameInstant(Literals.UTC));
199        } else if (val != null && val.getClass().isEnum()) {
200            return val.toString();
201        } else {
202            return op.getOriginalValue();
203        }
204    }
205
206    @Override
207    public boolean checkSkipCache(String key) {
208        return SmartQueryCache.checkShouldSkip(getBucket(), key);
209    }
210}