1 package net.sumaris.core.extraction.dao.technical.table;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26 import com.google.common.base.Preconditions;
27 import com.google.common.base.Predicate;
28 import com.google.common.collect.ImmutableList;
29 import net.sumaris.core.dao.technical.SortDirection;
30 import net.sumaris.core.dao.technical.schema.*;
31 import net.sumaris.core.exception.SumarisTechnicalException;
32 import net.sumaris.core.extraction.dao.technical.ExtractionBaseDaoImpl;
33 import net.sumaris.core.extraction.dao.technical.schema.SumarisTableMetadatas;
34 import net.sumaris.core.extraction.vo.*;
35 import net.sumaris.core.util.Beans;
36 import net.sumaris.core.util.ExtractionBeans;
37 import net.sumaris.core.vo.technical.extraction.ExtractionProductColumnVO;
38 import org.apache.commons.lang3.ArrayUtils;
39 import org.apache.commons.lang3.StringUtils;
40 import org.slf4j.Logger;
41 import org.slf4j.LoggerFactory;
42 import org.springframework.beans.factory.annotation.Autowired;
43 import org.springframework.context.annotation.Lazy;
44 import org.springframework.jdbc.datasource.DataSourceUtils;
45 import org.springframework.stereotype.Repository;
46 import org.springframework.util.CollectionUtils;
47
48 import javax.sql.DataSource;
49 import java.sql.Connection;
50 import java.sql.SQLException;
51 import java.sql.Statement;
52 import java.sql.Types;
53 import java.util.*;
54 import java.util.stream.Collectors;
55
56
57
58
59
60
61 @Repository("extractionTableDao")
62 @Lazy
63 public class ExtractionTableDaoImpl extends ExtractionBaseDaoImpl implements ExtractionTableDao {
64
65 private static final Logger log = LoggerFactory.getLogger(ExtractionTableDaoImpl.class);
66
67 @Autowired
68 protected SumarisDatabaseMetadata databaseMetadata;
69
70 @Autowired
71 protected DataSource dataSource = null;
72
73 @Override
74 public List<String> getAllTableNames() {
75 return ImmutableList.copyOf(databaseMetadata.getTableNames());
76 }
77
78 @Override
79 public ExtractionResultVO getTable(String tableName) {
80 return getTableRows(tableName, null, 0, 0, null, null);
81 }
82
83 @Override
84 public List<ExtractionProductColumnVO> getColumns(String tableName) {
85 SumarisTableMetadata table = databaseMetadata.getTable(tableName);
86 return toProductColumnVOs(table, table.getColumnNames());
87 }
88
89 @Override
90 public ExtractionResultVO getTableRows(String tableName, ExtractionFilterVO filter, int offset, int size, String sort, SortDirection direction) {
91 Preconditions.checkNotNull(tableName);
92
93 SumarisTableMetadata table = databaseMetadata.getTable(tableName.toLowerCase());
94 Preconditions.checkNotNull(table, "Unknown table: " + tableName);
95
96 ExtractionResultVOExtractionResultVO.html#ExtractionResultVO">ExtractionResultVO result = new ExtractionResultVO();
97
98 List<String> columnNames = table.getColumnNames().stream()
99
100 .filter(createIncludeExcludePredicate(filter))
101 .collect(Collectors.toList());
102
103
104 List<ExtractionProductColumnVO> columns = toProductColumnVOs(table, columnNames);
105 result.setColumns(columns);
106
107 String whereClause = SumarisTableMetadatas.getSqlWhereClause(table, filter);
108
109
110 Number total = getRowCount(table, whereClause);
111 result.setTotal(total);
112
113 if (size > 0 && total.longValue() > 0) {
114 List<String[]> rows = getRows(table, filter.isDistinct(), columnNames, whereClause, offset, size, sort, direction);
115 result.setRows(rows);
116 }
117
118 return result;
119 }
120
121 @Override
122 public void dropTable(String tableName) {
123 Preconditions.checkNotNull(tableName);
124 Preconditions.checkArgument(tableName.toUpperCase().startsWith("EXT_"));
125
126 log.debug(String.format("Dropping extraction table {%s}...", tableName));
127 Connection conn = DataSourceUtils.getConnection(dataSource);
128 try {
129 Statement stmt = conn.createStatement();
130 stmt.executeUpdate("DROP TABLE " + tableName.toUpperCase());
131
132 } catch (SQLException e) {
133 throw new SumarisTechnicalException(String.format("Cannot drop extraction table {%s}...", tableName), e);
134 } finally {
135 DataSourceUtils.releaseConnection(conn, dataSource);
136 }
137 }
138
139 @Override
140 public ExtractionResultVO getTableGroupByRows(String tableName,
141 ExtractionFilterVO filter,
142 Set<String> groupByColumnNames,
143 final Map<String, SQLAggregatedFunction> otherColumnNames,
144 int offset, int size, String sort, SortDirection direction) {
145 Preconditions.checkNotNull(tableName);
146
147 ExtractionResultVOExtractionResultVO.html#ExtractionResultVO">ExtractionResultVO result = new ExtractionResultVO();
148
149 List<String> columnNames = ImmutableList.<String>builder()
150 .addAll(groupByColumnNames)
151 .addAll(otherColumnNames.keySet())
152 .build();
153
154 StringBuilder whereBuilder = new StringBuilder();
155
156
157 SumarisTableMetadata table = databaseMetadata.getTable(tableName);
158 if (table != null && table.getColumnsCount() > 0) {
159 List<ExtractionProductColumnVO> columns = toProductColumnVOs(table, columnNames);
160 result.setColumns(columns);
161
162 whereBuilder.append(SumarisTableMetadatas.getSqlWhereClause(table, filter));
163 } else {
164 log.warn("Unable to find metadata for table " + tableName);
165 }
166
167 final String tableAlias = table != null ? table.getAlias() : null;
168
169 if (whereBuilder.length() == 0) {
170 whereBuilder.append("WHERE 1=1");
171 }
172
173 List<String> columnNamesWithFunction = columnNames.stream()
174 .map(c -> {
175 SQLAggregatedFunction sqlAggregatedFunction = otherColumnNames.get(c);
176 if (sqlAggregatedFunction == null) {
177 return SumarisTableMetadatas.getAliasedColumnName(tableAlias, c);
178 } else {
179
180
181
182
183 return String.format("%s(COALESCE(%s, 0))",
184 sqlAggregatedFunction.name().toLowerCase(),
185 SumarisTableMetadatas.getAliasedColumnName(tableAlias, c));
186 }
187 })
188 .collect(Collectors.toList());
189
190 String sql = SumarisTableMetadatas.getSelectGroupByQuery(
191 SumarisTableMetadatas.getAliasedTableName(tableAlias, tableName),
192 columnNamesWithFunction,
193 whereBuilder.toString(),
194
195 SumarisTableMetadatas.getAliasedColumns(tableAlias, groupByColumnNames),
196
197 SumarisTableMetadatas.getAliasedColumns(tableAlias, groupByColumnNames),
198 direction);
199
200
201 int columnCount = columnNamesWithFunction.size();
202 List<String[]> rows = query(sql, r -> this.toTableRowVO(r, columnCount), offset, size);
203 result.setRows(rows);
204
205 return result;
206 }
207
208 @Override
209 public long getRowCount(String tableName) {
210 Preconditions.checkNotNull(tableName);
211
212 String sql = String.format("SELECT COUNT(*) from %s", tableName);
213 return queryCount(sql);
214 }
215
216
217
218 protected Number getRowCount(SumarisTableMetadata table, String whereClause) {
219
220 String sql = table.getCountAllQuery();
221
222 if (StringUtils.isNotBlank(whereClause)) {
223 sql += whereClause;
224 }
225
226 Number total = (Number) getEntityManager()
227 .createNativeQuery(sql)
228 .getSingleResult();
229 return total;
230 }
231
232 protected List<String[]> getRows(SumarisTableMetadata table,
233 boolean distinct,
234 List<String> columnNames,
235 String whereClause, int offset, int size, String sort, SortDirection direction) {
236 String sql = table.getSelectQuery(distinct, columnNames, whereClause, sort, direction);
237 int columnCount = columnNames.size();
238 return query(sql, r -> toTableRowVO(r, columnCount), offset, size);
239 }
240
241 protected List<String[]> toTableRowsVO(List<Object[]> rows, final int resultLength) {
242 return rows.stream().map(r -> toTableRowVO(r, resultLength))
243 .collect(Collectors.toList());
244 }
245
246 protected String[] toTableRowVO(Object[] row, int columnCount) {
247 String[] result = new String[columnCount];
248 if (columnCount <= 0) columnCount = row.length;
249 for (int i = 0; i < columnCount; i++) {
250 if (row[i] != null) {
251 result[i] = row[i].toString();
252 } else {
253 result[i] = null;
254 }
255 }
256 return result;
257 }
258
259 protected List<ExtractionProductColumnVO> toProductColumnVOs(SumarisTableMetadata table,
260 Collection<String> columnNames) {
261 List<ExtractionProductColumnVO> columns = columnNames.stream()
262
263 .map(table::getColumnMetadata)
264 .filter(Objects::nonNull)
265
266 .map(ExtractionBeans::toProductColumnVO)
267 .collect(Collectors.toList());
268
269
270 String tableNameUppercase = table.getName().toUpperCase();
271
272
273 if (tableNameUppercase.startsWith("P01_ICES")) {
274 tableNameUppercase.replaceAll("P01_ICES_", "P01_RDB_");
275 }
276
277 String[] orderedColumnNames = ExtractionTableColumnOrder.COLUMNS_BY_TABLE.get(tableNameUppercase);
278 if (ArrayUtils.isNotEmpty(orderedColumnNames)) {
279 int maxRankOrder = -1;
280 for (ExtractionProductColumnVO column : columns) {
281 int rankOrder = ArrayUtils.indexOf(orderedColumnNames, column.getName().toLowerCase());
282 if (rankOrder != -1) {
283 column.setRankOrder(rankOrder + 1);
284 maxRankOrder = Math.max(maxRankOrder, rankOrder + 1);
285 }
286 }
287
288 for (ExtractionProductColumnVO column : columns) {
289 if (column.getRankOrder() == null) {
290 column.setRankOrder(++maxRankOrder);
291 }
292 }
293 }
294
295 return columns;
296 }
297
298 protected Predicate<String> createIncludeExcludePredicate(ExtractionFilterVO filter) {
299 return createIncludeExcludePredicate(filter.getIncludeColumnNames(), filter.getExcludeColumnNames());
300 }
301
302 protected Predicate<String> createIncludeExcludePredicate(Set<String> includes, Set<String> excludes) {
303 final boolean includeAll = CollectionUtils.isEmpty(includes);
304 final boolean excludeNone = CollectionUtils.isEmpty(excludes);
305
306 if (includeAll && excludeNone) {
307 return (column) -> true;
308 }
309
310 return (column) -> {
311 boolean isInclude = includeAll || includes.stream().anyMatch((include) -> {
312 if (include.contains("*")) {
313 final String regexp = include.replaceAll("[*]", ".*");
314 return column.matches(regexp);
315 }
316 return column.equalsIgnoreCase(include);
317 });
318
319 boolean isExclude = !excludeNone && excludes.stream().anyMatch((exclude) -> {
320 if (exclude.contains("*")) {
321 final String regexp = exclude.replaceAll("[*]", ".*");
322 return column.matches(regexp);
323 }
324 return column.equalsIgnoreCase(exclude);
325 });
326
327 return isInclude && !isExclude;
328 };
329 }
330
331 }