View Javadoc
1   package net.sumaris.core.extraction.dao.trip.rdb;
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.Preconditions;
26  import com.google.common.collect.*;
27  import net.sumaris.core.dao.technical.SortDirection;
28  import net.sumaris.core.dao.technical.schema.SumarisDatabaseMetadata;
29  import net.sumaris.core.dao.technical.schema.SumarisTableMetadata;
30  import net.sumaris.core.exception.SumarisTechnicalException;
31  import net.sumaris.core.extraction.dao.technical.ExtractionBaseDaoImpl;
32  import net.sumaris.core.extraction.dao.technical.XMLQuery;
33  import net.sumaris.core.extraction.dao.technical.schema.SumarisTableMetadatas;
34  import net.sumaris.core.extraction.dao.technical.table.ExtractionTableDao;
35  import net.sumaris.core.extraction.dao.trip.AggregationTripDao;
36  import net.sumaris.core.extraction.vo.*;
37  import net.sumaris.core.extraction.vo.trip.rdb.AggregationRdbTripContextVO;
38  import net.sumaris.core.extraction.vo.trip.rdb.ExtractionRdbTripVersion;
39  import net.sumaris.core.model.referential.pmfm.PmfmEnum;
40  import net.sumaris.core.model.technical.extraction.rdb.ProductRdbStation;
41  import net.sumaris.core.service.administration.programStrategy.ProgramService;
42  import net.sumaris.core.service.administration.programStrategy.StrategyService;
43  import net.sumaris.core.util.StringUtils;
44  import net.sumaris.core.vo.technical.extraction.ExtractionProductVO;
45  import org.slf4j.Logger;
46  import org.slf4j.LoggerFactory;
47  import org.springframework.beans.factory.annotation.Autowired;
48  import org.springframework.context.annotation.Lazy;
49  import org.springframework.core.io.Resource;
50  import org.springframework.core.io.ResourceLoader;
51  import org.springframework.stereotype.Repository;
52  
53  import java.io.IOException;
54  import java.net.URL;
55  import java.util.List;
56  import java.util.Map;
57  import java.util.Set;
58  import java.util.stream.Collectors;
59  import java.util.stream.Stream;
60  
61  import static org.nuiton.i18n.I18n.t;
62  
63  /**
64   * @author Benoit Lavenier <benoit.lavenier@e-is.pro>
65   */
66  @Repository("aggregationRdbDao")
67  @Lazy
68  public class AggregationRdbTripDaoImpl<
69          C extends AggregationRdbTripContextVO,
70          F extends ExtractionFilterVO,
71          S extends AggregationStrataVO>
72          extends ExtractionBaseDaoImpl
73          implements AggregationRdbTripDao<C, F, S>, AggregationTripDao {
74  
75      private static final Logger log = LoggerFactory.getLogger(AggregationRdbTripDaoImpl.class);
76  
77      private static final String TR_TABLE_NAME_PATTERN = TABLE_NAME_PREFIX + TR_SHEET_NAME + "_%s";
78      private static final String HH_TABLE_NAME_PATTERN = TABLE_NAME_PREFIX + HH_SHEET_NAME + "_%s";
79      private static final String SL_TABLE_NAME_PATTERN = TABLE_NAME_PREFIX + SL_SHEET_NAME + "_%s";
80      private static final String HL_TABLE_NAME_PATTERN = TABLE_NAME_PREFIX + HL_SHEET_NAME + "_%s";
81      private static final String CA_TABLE_NAME_PATTERN = TABLE_NAME_PREFIX + CA_SHEET_NAME + "_%s";
82  
83      private static List<String> SPACE_STRATA = ImmutableList.of("area", "rect", "square");
84      private static List<String> TIME_STRATA = ImmutableList.of("year", "quarter", "month");
85      private static Map<String, List<String>> AGG_STRATA_BY_SHEETNAME = ImmutableMap.<String, List<String>>builder()
86              .put(HH_SHEET_NAME, ImmutableList.of(COLUMN_TRIP_COUNT, COLUMN_STATION_COUNT))
87              .build();
88  
89  
90      @Autowired
91      protected StrategyService strategyService;
92  
93      @Autowired
94      protected ProgramService programService;
95  
96      @Autowired
97      protected ResourceLoader resourceLoader;
98  
99      @Autowired
100     protected SumarisDatabaseMetadata databaseMetadata;
101 
102     @javax.annotation.Resource(name = "extractionRdbTripDao")
103     protected ExtractionRdbTripDao extractionRdbTripDao;
104 
105     @Autowired
106     protected ExtractionTableDao extractionTableDao;
107 
108     @Override
109     public <R extends C> R aggregate(ExtractionProductVO source, F filter) {
110 
111         // Init context
112         R context = createNewContext();
113         context.setTripFilter(extractionRdbTripDao.toTripFilterVO(filter));
114         context.setFilter(filter);
115         context.setFormatName(RDB_FORMAT);
116         context.setFormatVersion(ExtractionRdbTripVersion.VERSION_1_3.getLabel());
117         context.setId(System.currentTimeMillis());
118 
119         // Compute table names
120         context.setTripTableName(String.format(TR_TABLE_NAME_PATTERN, context.getId()));
121         context.setStationTableName(String.format(HH_TABLE_NAME_PATTERN, context.getId()));
122         context.setSpeciesListTableName(String.format(SL_TABLE_NAME_PATTERN, context.getId()));
123         context.setSpeciesLengthTableName(String.format(HL_TABLE_NAME_PATTERN, context.getId()));
124         context.setSampleTableName(String.format(CA_TABLE_NAME_PATTERN, context.getId()));
125 
126         // Expected sheet name
127         String sheetName = filter != null && filter.isPreview() ? filter.getSheetName() : null;
128 
129         // Trip
130         long rowCount;
131         //rowCount = createTripTable(context);
132         //if (rowCount == 0) throw new DataNotFoundException(t("sumaris.aggregation.noData"));
133         //if (sheetName != null && context.hasSheet(sheetName)) return context;
134 
135         // Station
136         rowCount = createStationTable(source, context);
137         if (rowCount == 0) return context;
138         if (sheetName != null && context.hasSheet(sheetName)) return context;
139 
140         // Species List
141         //rowCount = createSpeciesListTable(context);
142         //if (rowCount == 0) return context;
143         //if (sheetName != null && context.hasSheet(sheetName)) return context;
144 
145         // Species Length
146         //createSpeciesLengthTable(context);
147 
148         return context;
149 
150     }
151 
152     public AggregationResultVO read(String tableName, F filter, S strata, int offset, int size, String sortAttribute, SortDirection direction) {
153         Preconditions.checkNotNull(tableName);
154         Preconditions.checkNotNull(strata);
155 
156         Set<String> groupByColumnNames = Sets.newLinkedHashSet();
157         Map<String, ExtractionTableDao.SQLAggregatedFunction> aggColumns = Maps.newHashMap();
158 
159         // Process space strata
160         {
161             String spaceStrata = strata.getSpaceColumnName() != null ? strata.getSpaceColumnName().toLowerCase() : COLUMN_AREA;
162 
163             // Replace alias
164             if (COLUMN_ALIAS.containsKey(spaceStrata)) spaceStrata = COLUMN_ALIAS.get(spaceStrata);
165 
166             switch (spaceStrata) {
167                 case COLUMN_SQUARE:
168                     groupByColumnNames.add(COLUMN_SQUARE);
169                 case COLUMN_STATISTICAL_RECTANGLE:
170                     groupByColumnNames.add(COLUMN_STATISTICAL_RECTANGLE);
171                 case COLUMN_AREA:
172                 default:
173                     groupByColumnNames.add(COLUMN_AREA);
174             }
175         }
176 
177         // Time strata
178         {
179             String timeStrata = strata.getTimeColumnName() != null ? strata.getTimeColumnName().toLowerCase() : COLUMN_YEAR;
180             switch (timeStrata) {
181                 case COLUMN_MONTH:
182                     groupByColumnNames.add(COLUMN_MONTH);
183                 case COLUMN_QUARTER:
184                     groupByColumnNames.add(COLUMN_QUARTER);
185                 case COLUMN_YEAR:
186                 default:
187                     groupByColumnNames.add(COLUMN_YEAR);
188             }
189         }
190 
191         // Agg strata
192         {
193             String aggStrata = strata.getAggColumnName() != null ? strata.getAggColumnName().toLowerCase() : COLUMN_STATION_COUNT;
194             ExtractionTableDao.SQLAggregatedFunction function = strata.getAggFunction() != null ?
195                     ExtractionTableDao.SQLAggregatedFunction.valueOf(strata.getAggFunction().toUpperCase()) :
196                     ExtractionTableDao.SQLAggregatedFunction.SUM;
197             aggColumns.put(aggStrata, function);
198         }
199 
200         ExtractionResultVO rows = extractionTableDao.getTableGroupByRows(tableName, filter, groupByColumnNames, aggColumns,
201                 offset, size, sortAttribute, direction);
202 
203         AggregationResultVOggregationResultVO.html#AggregationResultVO">AggregationResultVO result = new AggregationResultVO(rows);
204 
205         result.setSpaceStrata(SPACE_STRATA);
206         result.setTimeStrata(TIME_STRATA);
207         if (filter.getSheetName() != null) {
208             result.setAggStrata(AGG_STRATA_BY_SHEETNAME.get(filter.getSheetName()));
209         }
210 
211         return result;
212     }
213 
214 
215     /* -- protected methods -- */
216 
217     protected <C extends AggregationRdbTripContextVO> C createNewContext() {
218         Class<? extends AggregationRdbTripContextVO> contextClass = getContextClass();
219         Preconditions.checkNotNull(contextClass);
220 
221         try {
222             return (C) contextClass.newInstance();
223         } catch (Exception e) {
224             throw new SumarisTechnicalException("Could not create an instance of context class " + contextClass.getName());
225         }
226     }
227 
228     protected Class<? extends AggregationRdbTripContextVO> getContextClass() {
229         return AggregationRdbTripContextVO.class;
230     }
231 
232     protected long createStationTable(ExtractionProductVO source, AggregationRdbTripContextVO context) {
233 
234         String tableName = context.getStationTableName();
235         XMLQuery xmlQuery = createStationQuery(source, context);
236 
237         // aggregate insertion
238         execute(xmlQuery);
239         long count = countFrom(tableName);
240 
241         if (count == 0) return 0;
242 
243         // Clean row using generic tripFilter
244         count -= cleanRow(tableName, context.getFilter(), HH_SHEET_NAME);
245 
246         // Analyze row
247         Map<String, List<String>> columnValues = null;
248         if (context.isEnableAnalyze()) {
249             columnValues = analyzeRow(tableName, xmlQuery, COLUMN_YEAR);
250         }
251 
252         // Add result table to context
253         context.addTableName(tableName, HH_SHEET_NAME,
254                 xmlQuery.getHiddenColumnNames(),
255                 getSpatialColumnNames(xmlQuery),
256                 columnValues);
257         log.debug(String.format("Station table: %s rows inserted", count));
258 
259         return count;
260     }
261 
262     protected XMLQuery createStationQuery(ExtractionProductVO source, AggregationRdbTripContextVO context) {
263 
264         String rawStationTableName = source.getTableNameBySheetName(ExtractionRdbTripDao.HH_SHEET_NAME)
265                 .orElseThrow(() -> new SumarisTechnicalException("Missing HH table"));
266         SumarisTableMetadata rawStationTable = databaseMetadata.getTable(rawStationTableName);
267 
268 
269         XMLQuery xmlQuery = createXMLQuery(context, "createStationTable");
270         xmlQuery.bind("rawTripTableName", source.getTableNameBySheetName(ExtractionRdbTripDao.TR_SHEET_NAME)
271                 .orElseThrow(() -> new SumarisTechnicalException("Missing TR table")));
272         xmlQuery.bind("rawStationTableName", rawStationTableName);
273         xmlQuery.bind("stationTableName", context.getStationTableName());
274 
275         xmlQuery.setGroup("gearType", rawStationTable.getColumnMetadata(ProductRdbStation.COLUMN_GEAR_TYPE) != null);
276 
277         return xmlQuery;
278     }
279 
280     protected long createSpeciesListTable(AggregationRdbTripContextVO context) {
281 
282         XMLQuery xmlQuery = createSpeciesListQuery(context, true/*exclude invalid station*/);
283 
284         // aggregate insertion
285         execute(xmlQuery);
286         long count = countFrom(context.getSpeciesListTableName());
287 
288         // Clean row using generic tripFilter
289         if (count > 0) {
290             count -= cleanRow(context.getSpeciesListTableName(), context.getFilter(), SL_SHEET_NAME);
291         }
292 
293         // Add result table to context
294         if (count > 0) {
295             context.addTableName(context.getSpeciesListTableName(), SL_SHEET_NAME);
296             log.debug(String.format("Species list table: %s rows inserted", count));
297         }
298         return count;
299     }
300 
301     protected XMLQuery createSpeciesListQuery(AggregationRdbTripContextVO context, boolean excludeInvalidStation) {
302         XMLQuery xmlQuery = createXMLQuery(context, "createSpeciesListTable");
303         xmlQuery.bind("stationTableName", context.getStationTableName());
304         xmlQuery.bind("speciesListTableName", context.getSpeciesListTableName());
305 
306         // Bind some ids
307         xmlQuery.bind("catchCategoryPmfmId", String.valueOf(PmfmEnum.DISCARD_OR_LANDING.getId()));
308 
309         // Exclude not valid station
310         xmlQuery.setGroup("excludeInvalidStation", excludeInvalidStation);
311 
312         return xmlQuery;
313     }
314 
315 
316     protected long createSpeciesLengthTable(AggregationRdbTripContextVO context) {
317 
318         XMLQuery xmlQuery = createSpeciesLengthQuery(context);
319 
320         // aggregate insertion
321         execute(xmlQuery);
322         long count = countFrom(context.getSpeciesLengthTableName());
323 
324         // Clean row using generic tripFilter
325         if (count > 0) {
326             count -= cleanRow(context.getSpeciesLengthTableName(), context.getFilter(), HL_SHEET_NAME);
327         }
328 
329         // Add result table to context
330         if (count > 0) {
331             context.addTableName(context.getSpeciesLengthTableName(), HL_SHEET_NAME);
332             log.debug(String.format("Species length table: %s rows inserted", count));
333         }
334         return count;
335     }
336 
337     protected XMLQuery createSpeciesLengthQuery(AggregationRdbTripContextVO context) {
338         XMLQuery xmlQuery = createXMLQuery(context, "createSpeciesLengthTable");
339         xmlQuery.bind("stationTableName", context.getStationTableName());
340         xmlQuery.bind("speciesListTableName", context.getSpeciesListTableName());
341         xmlQuery.bind("speciesLengthTableName", context.getSpeciesLengthTableName());
342 
343         // Bind some ids
344         xmlQuery.bind("sexPmfmId", String.valueOf(PmfmEnum.SEX.getId()));
345         xmlQuery.bind("lengthTotalCmPmfmId", String.valueOf(PmfmEnum.LENGTH_TOTAL_CM.getId()));
346 
347         return xmlQuery;
348     }
349 
350     protected int execute(XMLQuery xmlQuery) {
351         return queryUpdate(xmlQuery.getSQLQueryAsString());
352     }
353 
354     protected long countFrom(String tableName) {
355         return extractionTableDao.getRowCount(tableName);
356     }
357 
358     protected String getQueryFullName(ExtractionContextVO context, String queryName) {
359         Preconditions.checkNotNull(context);
360         Preconditions.checkNotNull(context.getFormatName());
361         Preconditions.checkNotNull(context.getFormatVersion());
362 
363         return String.format("%s/v%s/aggregation/%s",
364                 context.getFormatName(),
365                 context.getFormatVersion().replaceAll("[.]", "_"),
366                 queryName);
367     }
368 
369     protected XMLQuery createXMLQuery(ExtractionContextVO context, String queryName) {
370         return createXMLQuery(getQueryFullName(context, queryName));
371     }
372 
373     protected XMLQuery createXMLQuery(String queryName) {
374         XMLQuery query = createXMLQuery();
375         query.setQuery(getXMLQueryClasspathURL(queryName));
376         return query;
377     }
378 
379     protected URL getXMLQueryURL(ExtractionContextVO context, String queryName) {
380         return getXMLQueryClasspathURL(getQueryFullName(context, queryName));
381     }
382 
383     protected URL getXMLQueryClasspathURL(String queryName) {
384         Resource resource = resourceLoader.getResource(ResourceLoader.CLASSPATH_URL_PREFIX + XML_QUERY_PATH + "/" + queryName + ".xml");
385         if (!resource.exists())
386             throw new SumarisTechnicalException(t("sumaris.extraction.xmlQuery.notFound", queryName));
387         try {
388             return resource.getURL();
389         } catch (IOException e) {
390             throw new SumarisTechnicalException(e);
391         }
392     }
393 
394     protected int cleanRow(String tableName, ExtractionFilterVO filter, String sheetName) {
395         Preconditions.checkNotNull(tableName);
396         if (filter == null) return 0;
397 
398         // TODO add cache
399         SumarisTableMetadata table = databaseMetadata.getTable(tableName.toLowerCase());
400         Preconditions.checkNotNull(table);
401 
402         String whereClauseContent = SumarisTableMetadatas.getSqlWhereClauseContent(table, filter, sheetName, table.getAlias());
403         if (StringUtils.isBlank(whereClauseContent)) return 0;
404 
405         String deleteQuery = table.getDeleteQuery(String.format("NOT(%s)", whereClauseContent));
406         return queryUpdate(deleteQuery);
407     }
408 
409 
410     protected Map<String, List<String>> analyzeRow(final String tableName, XMLQuery xmlQuery, String... includedNumericColumnNames) {
411         Preconditions.checkNotNull(tableName);
412         Preconditions.checkNotNull(xmlQuery);
413 
414         return Stream.concat(xmlQuery.getNotNumericColumnNames().stream(), Stream.of(includedNumericColumnNames))
415                 .collect(Collectors.toMap(
416                         c -> c,
417                         c -> query(String.format("SELECT DISTINCT %s FROM %s where %s IS NOT NULL", c, tableName, c), Object.class)
418                                 .stream().map(String::valueOf).collect(Collectors.toList())
419                         )
420                 );
421     }
422 
423     protected Set<String> getSpatialColumnNames(final XMLQuery xmlQuery) {
424         return xmlQuery.getVisibleColumnNames()
425                 .stream()
426                 .map(c -> c.toLowerCase())
427                 .filter(SPACE_STRATA::contains)
428                 .collect(Collectors.toSet());
429     }
430 }