View Javadoc
1   package net.sumaris.core.extraction.dao.technical.schema;
2   
3   /*-
4    * #%L
5    * SUMARiS:: Core Extraction
6    * %%
7    * Copyright (C) 2018 - 2019 SUMARiS Consortium
8    * %%
9    * This program is free software: you can redistribute it and/or modify
10   * it under the terms of the GNU General Public License as
11   * published by the Free Software Foundation, either version 3 of the
12   * License, or (at your option) any later version.
13   * 
14   * This program is distributed in the hope that it will be useful,
15   * but WITHOUT ANY WARRANTY; without even the implied warranty of
16   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
17   * GNU General Public License for more details.
18   * 
19   * You should have received a copy of the GNU General Public
20   * License along with this program.  If not, see
21   * <http://www.gnu.org/licenses/gpl-3.0.html>.
22   * #L%
23   */
24  
25  import com.google.common.base.Joiner;
26  import com.google.common.base.Preconditions;
27  import net.sumaris.core.dao.technical.SortDirection;
28  import net.sumaris.core.dao.technical.schema.SumarisColumnMetadata;
29  import net.sumaris.core.dao.technical.schema.SumarisTableMetadata;
30  import net.sumaris.core.exception.SumarisTechnicalException;
31  import net.sumaris.core.extraction.vo.ExtractionFilterCriterionVO;
32  import net.sumaris.core.extraction.vo.ExtractionFilterOperatorEnum;
33  import net.sumaris.core.extraction.vo.ExtractionFilterVO;
34  import org.apache.commons.collections4.CollectionUtils;
35  import org.apache.commons.lang3.ArrayUtils;
36  import org.apache.commons.lang3.StringUtils;
37  
38  import javax.annotation.Nullable;
39  import java.sql.Types;
40  import java.util.Collection;
41  import java.util.stream.Collectors;
42  
43  /**
44   * Helper class for extraction
45   *
46   * @author Benoit Lavenier <benoit.lavenier@e-is.pro>*
47   */
48  public class SumarisTableMetadatas {
49  
50      public static String getSqlWhereClause(SumarisTableMetadata table, ExtractionFilterVO filter) {
51  
52          String whereContent = getSqlWhereClauseContent(table, filter);
53  
54          // Prepend with WHERE keyword
55          if (StringUtils.isBlank(whereContent) || "1=1".equals(whereContent)) return "";
56          return " WHERE " + whereContent;
57      }
58  
59      public static String getSqlWhereClauseContent(SumarisTableMetadata table, ExtractionFilterVO filter) {
60          return getSqlWhereClauseContent(table, filter, filter != null ? filter.getSheetName() : null);
61      }
62  
63      public static String getSqlWhereClauseContent(SumarisTableMetadata table, ExtractionFilterVO filter, String sheetName) {
64          return getSqlWhereClauseContent(table, filter, sheetName, table != null ? table.getAlias() : null);
65      }
66  
67      public static String getSqlWhereClauseContent(SumarisTableMetadata table, ExtractionFilterVO filter, String sheetName, String tableAlias) {
68  
69          if (filter == null || CollectionUtils.isEmpty(filter.getCriteria())) return "";
70  
71          StringBuilder sql = new StringBuilder();
72  
73          StringBuilder logicalOperator = new StringBuilder();
74          String aliasWithPoint = tableAlias != null ? (tableAlias + ".") : "";
75  
76          filter.getCriteria().stream()
77                  .filter(criterion -> sheetName == null || sheetName.equals(criterion.getSheetName()))
78                  .forEach(criterion -> {
79  
80                      // Get the column to tripFilter
81                      Preconditions.checkNotNull(criterion.getName());
82                      SumarisColumnMetadata column = table != null ? table.getColumnMetadata(criterion.getName().toLowerCase()) : null;
83                      if (column == null) {
84                          if (sheetName != null) {
85                              throw new SumarisTechnicalException(String.format("Invalid criterion: column '%s' not found", criterion.getName()));
86                          } else {
87                              // Continue (=skip)
88                          }
89                      } else {
90                          sql.append(logicalOperator.toString())
91                                  .append(aliasWithPoint)
92                                  .append(column.getName());
93  
94                          // Affect logical operator, for the next criterion
95                          if (logicalOperator.length() == 0) {
96                              if ("OR".equalsIgnoreCase(StringUtils.trim(filter.getOperator()))) {
97                                  logicalOperator.append(" OR ");
98                              } else {
99                                  logicalOperator.append(" AND ");
100                             }
101                         }
102 
103                         ExtractionFilterOperatorEnum operator = criterion.getOperator() == null ? ExtractionFilterOperatorEnum.EQUALS : ExtractionFilterOperatorEnum.fromSymbol(criterion.getOperator());
104 
105                         if (criterion.getValue() == null && ArrayUtils.isEmpty(criterion.getValues())) {
106                             switch (operator) {
107                                 case NOT_IN:
108                                 case NOT_EQUALS:
109                                     sql.append(" IS NOT NULL");
110                                     break;
111                                 default:
112                                     sql.append(" IS NULL");
113                             }
114                         } else {
115                             switch (operator) {
116                                 case IN:
117                                     sql.append(String.format(" IN (%s)", getInValues(column, criterion)));
118                                     break;
119                                 case NOT_IN:
120                                     sql.append(String.format(" NOT IN (%s)", getInValues(column, criterion)));
121                                     break;
122                                 case EQUALS:
123                                     sql.append(String.format(" = %s", getSingleValue(column, criterion)));
124                                     break;
125                                 case NOT_EQUALS:
126                                     sql.append(String.format(" <> %s", getSingleValue(column, criterion)));
127                                     break;
128                                 case LESS_THAN:
129                                     sql.append(String.format(" < %s", getSingleValue(column, criterion)));
130                                     break;
131                                 case LESS_THAN_OR_EQUALS:
132                                     sql.append(String.format(" <= %s", getSingleValue(column, criterion)));
133                                     break;
134                                 case GREATER_THAN:
135                                     sql.append(String.format(" > %s", getSingleValue(column, criterion)));
136                                     break;
137                                 case GREATER_THAN_OR_EQUALS:
138                                     sql.append(String.format(" >= %s", getSingleValue(column, criterion)));
139                                     break;
140                                 case BETWEEN:
141                                     sql.append(String.format(" BETWEEN %s AND %s", getBetweenValueByIndex(column, criterion, 0), getBetweenValueByIndex(column, criterion, 1)));
142                                     break;
143                             }
144                         }
145                     }
146                 });
147 
148         return sql.toString();
149     }
150 
151     public static String getSingleValue(SumarisColumnMetadata column, ExtractionFilterCriterionVO criterion) {
152         return isNumericColumn(column) ? criterion.getValue() : ("'" + criterion.getValue() + "'");
153     }
154 
155     public static String getInValues(SumarisColumnMetadata column, ExtractionFilterCriterionVO criterion) {
156         if (ArrayUtils.isEmpty(criterion.getValues())) {
157             if (criterion.getValue() != null) {
158                 return getSingleValue(column, criterion);
159             }
160             Preconditions.checkArgument(false, "Invalid criterion: 'values' is required for operator 'IN' or 'NOT IN'");
161         }
162         return isNumericColumn(column) ?
163                 Joiner.on(',').join(criterion.getValues()) :
164                 "'" + Joiner.on("','").skipNulls().join(criterion.getValues()) + "'";
165     }
166 
167     public static String getBetweenValueByIndex(SumarisColumnMetadata column, ExtractionFilterCriterionVO criterion, int index) {
168         Preconditions.checkNotNull(criterion.getValues(), "Invalid criterion: 'values' is required for operator 'BETWEEN'");
169         Preconditions.checkArgument(criterion.getValues().length == 2, "Invalid criterion: 'values' array must have 2 values, for operator 'BETWEEN'");
170         Preconditions.checkArgument(index == 0 || index == 1);
171         String value = criterion.getValues()[index];
172         return isNumericColumn(column) ? value : ("'" + value + "'");
173     }
174 
175 
176     public static boolean isNumericColumn(SumarisColumnMetadata column) {
177         return column.getTypeCode() == Types.NUMERIC
178                 || column.getTypeCode() == Types.INTEGER
179                 || column.getTypeCode() == Types.DOUBLE
180                 || column.getTypeCode() == Types.BIGINT
181                 || column.getTypeCode() == Types.DECIMAL
182                 || column.getTypeCode() == Types.FLOAT;
183     }
184 
185     public static boolean isNotNumericColumn(SumarisColumnMetadata column) {
186         return !isNumericColumn(column);
187     }
188 
189     public static String getAliasedColumnName(@Nullable String alias, String columnName) {
190         return StringUtils.isNotBlank(alias) ? alias + "." + columnName : columnName;
191     }
192 
193     public static String getAliasedTableName(@Nullable String alias, String tableName) {
194         return StringUtils.isNotBlank(alias) ? tableName + " AS " + alias : tableName;
195     }
196 
197     public static String getSelectGroupByQuery(String tableName,
198                                                Collection<String> columnNames,
199                                                String whereClause,
200                                                Collection<String> groupByColumnNames,
201                                                Collection<String> sortColumnNames,
202                                                SortDirection direction) {
203         StringBuilder sb = new StringBuilder();
204         sb.append("SELECT ")
205                 .append(Joiner.on(',').join(columnNames))
206                 .append(" FROM ")
207                 .append(tableName);
208 
209         // Where clause
210         if (StringUtils.isNotBlank(whereClause)) {
211             if (!whereClause.trim().startsWith("WHERE")) {
212                 sb.append(" WHERE ").append(whereClause.trim());
213             }
214             else {
215                 sb.append(" ").append(whereClause.trim());
216             }
217         }
218 
219         // Group by clause
220         if (org.apache.commons.collections.CollectionUtils.isNotEmpty(groupByColumnNames)) {
221             sb.append(" GROUP BY ").append(Joiner.on(',').join(groupByColumnNames));
222         }
223         // Add order by
224         if (org.apache.commons.collections.CollectionUtils.isNotEmpty(sortColumnNames)) {
225             String directionStr = direction != null ? (" " + direction.name()) : "";
226             sb.append(" ORDER BY ")
227                     .append(Joiner.on(directionStr + ",").join(sortColumnNames))
228                     .append(directionStr);
229         }
230 
231         return sb.toString();
232     }
233 
234     public static Collection<String> getAliasedColumns(String tableAlias,
235                                            Collection<String> columnNames) {
236         if (StringUtils.isBlank(tableAlias)) return columnNames;
237         return columnNames.stream().map(c -> tableAlias + "." + c).collect(Collectors.toList());
238     }
239 }