1 package fr.ifremer.quadrige3.synchro.intercept.data.measurement;
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.collect.ImmutableSet;
27 import com.google.common.collect.Lists;
28 import com.google.common.eventbus.Subscribe;
29 import fr.ifremer.common.synchro.dao.SynchroBaseDao;
30 import fr.ifremer.common.synchro.dao.SynchroTableDao;
31 import fr.ifremer.common.synchro.intercept.SynchroInterceptorBase;
32 import fr.ifremer.common.synchro.intercept.SynchroOperationRepository;
33 import fr.ifremer.common.synchro.meta.SynchroJoinMetadata;
34 import fr.ifremer.common.synchro.meta.SynchroTableMetadata;
35 import fr.ifremer.common.synchro.meta.event.LoadJoinEvent;
36 import fr.ifremer.common.synchro.meta.event.LoadTableEvent;
37 import fr.ifremer.quadrige3.core.dao.referential.ObjectTypeCode;
38 import fr.ifremer.quadrige3.core.dao.technical.Daos;
39 import fr.ifremer.quadrige3.core.exception.QuadrigeTechnicalException;
40 import fr.ifremer.quadrige3.synchro.intercept.data.AbstractDataInterceptor;
41 import fr.ifremer.quadrige3.synchro.meta.DatabaseColumns;
42 import fr.ifremer.quadrige3.synchro.meta.data.DataSynchroTables;
43 import fr.ifremer.quadrige3.synchro.meta.referential.ReferentialSynchroTables;
44 import fr.ifremer.quadrige3.synchro.service.SynchroDirection;
45 import fr.ifremer.quadrige3.synchro.service.data.DataSynchroDatabaseConfiguration;
46
47 import java.io.IOException;
48 import java.math.BigDecimal;
49 import java.sql.PreparedStatement;
50 import java.sql.ResultSet;
51 import java.sql.SQLException;
52 import java.util.List;
53
54
55
56
57
58
59
60 public abstract class MeasurementAbstractInterceptor extends AbstractDataInterceptor {
61
62 private boolean enableIntegrityConstraints = true;
63
64 private String selectParentIdFromSamplingOperIdQuery;
65 private String selectRemoteIdAndParentIdsFromSampleIdQuery;
66 private String selectPmfmQuery;
67 private String selectRemoteIdBySurveyIdQuery;
68 private PreparedStatement selectParentIdFromSamplingOperIdStatement = null;
69 private PreparedStatement selectRemoteIdAndParentIdsFromSampleIdStatement = null;
70
71 private ObjectTypeCode objectTypeCode;
72 private int measIdIndex = -1;
73 private int surveyIdIndex = -1;
74 private int samplingOperIdIndex = -1;
75 private int sampleIdIndex = -1;
76 private int pmfmIdIndex = -1;
77 private int objectIdIndex = -1;
78 private int measDigitNumberIndex = -1;
79 private int measNumerValueIndex = -1;
80 private final String measIdColumnName;
81 private final String measDigitNumberColumnName;
82 private final String measNumerValueColumnName;
83 private SynchroDirection direction;
84
85 public MeasurementAbstractInterceptor() {
86 this(null, null, null, null);
87 }
88
89
90
91
92
93
94
95
96
97
98
99 public MeasurementAbstractInterceptor(ObjectTypeCode objectTypeCode, String measIdColumnName, String measDigitNumberColumnName, String measNumerValueColumnName) {
100 super();
101 this.objectTypeCode = objectTypeCode;
102 this.measIdColumnName = measIdColumnName;
103 this.selectParentIdFromSamplingOperIdQuery = initSelectRemoteIdAndParentIdFromSamplingOperIdQuery();
104 this.selectRemoteIdAndParentIdsFromSampleIdQuery = initSelectRemoteIdAndParentIdFromSampleIdQuery();
105 this.selectPmfmQuery = initSelectPmfm();
106 this.selectRemoteIdBySurveyIdQuery = initSelectRemoteIdFromSurveyId();
107 this.measDigitNumberColumnName = measDigitNumberColumnName;
108 this.measNumerValueColumnName = measNumerValueColumnName;
109 setEnableOnWrite(true);
110 }
111
112
113 @Override
114 public void init(DataSynchroDatabaseConfiguration config) {
115 super.init(config);
116 this.direction = config.getDirection();
117 this.enableIntegrityConstraints = direction == SynchroDirection.EXPORT_TEMP2SERVER
118 || direction == SynchroDirection.IMPORT_TEMP2LOCAL;
119 }
120
121
122 @Override
123 public SynchroInterceptorBase clone() {
124 MeasurementAbstractInterceptor newBean = (MeasurementAbstractInterceptor) super.clone();
125 newBean.direction = this.direction;
126 newBean.enableIntegrityConstraints = this.enableIntegrityConstraints;
127 newBean.selectParentIdFromSamplingOperIdQuery = this.selectParentIdFromSamplingOperIdQuery;
128 newBean.selectRemoteIdAndParentIdsFromSampleIdQuery = this.selectRemoteIdAndParentIdsFromSampleIdQuery;
129 newBean.selectPmfmQuery = this.selectPmfmQuery;
130 newBean.selectRemoteIdBySurveyIdQuery = this.selectRemoteIdBySurveyIdQuery;
131 newBean.measIdIndex = this.measIdIndex;
132 newBean.objectTypeCode = this.objectTypeCode;
133 newBean.surveyIdIndex = this.surveyIdIndex;
134 newBean.samplingOperIdIndex = this.samplingOperIdIndex;
135 newBean.sampleIdIndex = this.sampleIdIndex;
136 newBean.pmfmIdIndex = this.pmfmIdIndex;
137 newBean.objectIdIndex = this.objectIdIndex;
138 newBean.measDigitNumberIndex = this.measDigitNumberIndex;
139 newBean.measNumerValueIndex = this.measNumerValueIndex;
140 return newBean;
141 }
142
143
144
145
146
147
148
149
150
151 @Subscribe
152 public void handleJoinLoad(LoadJoinEvent e) {
153 SynchroJoinMetadata join = e.join;
154
155 if (!join.isValid()) {
156 return;
157 }
158
159 SynchroTableMetadata pkTable = join.getPkTable();
160 String pkTableName = pkTable.getName();
161 SynchroDirection direction = getConfig().getDirection();
162
163
164 if (direction == SynchroDirection.IMPORT_SERVER2TEMP) {
165
166
167 if (DataSynchroTables.SAMPLING_OPERATION.name().equalsIgnoreCase(pkTableName)
168 || DataSynchroTables.SAMPLE.name().equalsIgnoreCase(pkTableName)) {
169
170 join.setIsValid(false);
171 }
172 }
173 }
174
175
176
177
178
179
180
181
182
183 @Subscribe
184 public void handleTableLoad(LoadTableEvent e) {
185 SynchroTableMetadata table = e.table;
186
187 surveyIdIndex = table.getSelectColumnIndex(DatabaseColumns.SURVEY_ID.name());
188 samplingOperIdIndex = table.getSelectColumnIndex(DatabaseColumns.SAMPLING_OPER_ID.name());
189 sampleIdIndex = table.getSelectColumnIndex(DatabaseColumns.SAMPLE_ID.name());
190 pmfmIdIndex = table.getSelectColumnIndex(DatabaseColumns.PMFM_ID.name());
191 objectIdIndex = table.getSelectColumnIndex(DatabaseColumns.OBJECT_ID.name());
192 if (measNumerValueColumnName != null) measNumerValueIndex = table.getSelectColumnIndex(measNumerValueColumnName);
193 if (measDigitNumberColumnName != null) measDigitNumberIndex = table.getSelectColumnIndex(measDigitNumberColumnName);
194 if (measIdColumnName != null) measIdIndex = table.getSelectColumnIndex(measIdColumnName);
195
196
197 if (direction == SynchroDirection.EXPORT_TEMP2SERVER) {
198
199
200 table.addSelectByFksWhereClause(DatabaseColumns.SURVEY_ID.name(),
201 String.format("t.%s IS NULL AND t.%s IS NULL",
202 DatabaseColumns.SAMPLING_OPER_ID.name(),
203 DatabaseColumns.SAMPLE_ID.name()));
204
205
206 table.addSelectByFksWhereClause(DatabaseColumns.SAMPLING_OPER_ID.name(),
207 String.format("t.%s IS NULL",
208 DatabaseColumns.SAMPLE_ID.name()));
209 }
210 }
211
212
213 @Override
214 protected void doOnWrite(Object[] data, List<Object> pk, SynchroTableDao sourceDao, SynchroTableDao targetDao, SynchroOperationRepository buffer,
215 boolean insert)
216 throws SQLException {
217
218
219 if (direction == SynchroDirection.IMPORT_SERVER2TEMP) {
220
221 if (data[samplingOperIdIndex] != null) {
222 data[surveyIdIndex] = null;
223 }
224
225
226 if (data[sampleIdIndex] != null) {
227 data[samplingOperIdIndex] = null;
228 }
229 }
230
231
232 else if (direction == SynchroDirection.EXPORT_LOCAL2TEMP) {
233
234
235
236 if (measNumerValueIndex != -1 && measDigitNumberIndex != -1) {
237 BigDecimal measNumerValue = (BigDecimal) data[measNumerValueIndex];
238 if (measNumerValue != null && data[measDigitNumberIndex] != null) {
239 Double measDigitNumber = Double.parseDouble(data[measDigitNumberIndex].toString());
240 data[measNumerValueIndex] = Daos.convertToBigDecimal(measNumerValue, measDigitNumber.intValue());
241 }
242 }
243 }
244
245
246 else if (direction == SynchroDirection.EXPORT_TEMP2SERVER) {
247
248 {
249 Number objectId;
250
251
252 if (data[sampleIdIndex] != null) {
253 Long localSampleId = Long.parseLong(data[sampleIdIndex].toString());
254 Number[] ids = getRemoteIdAndParentIdFromSampleId(sourceDao, localSampleId);
255 objectId = ids[0];
256 data[surveyIdIndex] = ids[1];
257 data[samplingOperIdIndex] = ids[2];
258 }
259
260
261 else if (data[samplingOperIdIndex] != null) {
262 Long localSamplingOperId = Long.parseLong(data[samplingOperIdIndex].toString());
263 Number[] ids = getRemoteIdAndParentIdFromSamplingOperId(sourceDao, localSamplingOperId);
264 objectId = ids[0];
265 data[surveyIdIndex] = ids[1];
266 }
267
268
269 else if (data[surveyIdIndex] != null) {
270 objectId = getRemoteIdFromSurveyId(sourceDao, Long.parseLong(data[surveyIdIndex].toString()));
271 }
272
273
274 else {
275 throw new QuadrigeTechnicalException("Measurement found, without any parent filled. This should never append.");
276 }
277
278 data[objectIdIndex] = objectId;
279 }
280 }
281
282 if (buffer != null && objectTypeCode != null && measIdIndex != -1) {
283 Object measId = data[measIdIndex];
284 if (measId != null) {
285 buffer.addChildToUpdateFromManyColumns(DataSynchroTables.QUALIFICATION_HISTORY.name(),
286 ImmutableSet.of(DatabaseColumns.OBJECT_TYPE_CD.name(), DatabaseColumns.QUAL_HIST_ELEMENT_ID.name()),
287 Lists.newArrayList(objectTypeCode.value(), Long.parseLong(measId.toString())));
288 }
289 }
290 }
291
292
293
294
295
296
297
298
299
300
301 protected String initSelectRemoteIdAndParentIdFromSamplingOperIdQuery() {
302 return String.format("select %s, %s from %s where %s = ?",
303 DatabaseColumns.REMOTE_ID.name(),
304 DatabaseColumns.SURVEY_ID.name(),
305 DataSynchroTables.SAMPLING_OPERATION.name(),
306 DatabaseColumns.SAMPLING_OPER_ID.name()
307 );
308 }
309
310
311
312
313
314
315
316
317 protected String initSelectRemoteIdAndParentIdFromSampleIdQuery() {
318 return String.format("select %s, %s, %s from %s where %s = ?",
319 DatabaseColumns.REMOTE_ID.name(),
320 DatabaseColumns.SURVEY_ID.name(),
321 DatabaseColumns.SAMPLING_OPER_ID.name(),
322 DataSynchroTables.SAMPLE.name(),
323 DatabaseColumns.SAMPLE_ID.name()
324 );
325 }
326
327
328
329
330
331
332
333
334 protected String initSelectPmfm() {
335 return String.format("select %s || '%s' || %s || '%s' || %s || '%s' || %s from %s where %s = ?",
336 DatabaseColumns.PAR_CD.name(),
337 SynchroTableMetadata.PK_SEPARATOR,
338 DatabaseColumns.MATRIX_ID.name(),
339 SynchroTableMetadata.PK_SEPARATOR,
340 DatabaseColumns.FRACTION_ID.name(),
341 SynchroTableMetadata.PK_SEPARATOR,
342 DatabaseColumns.METHOD_ID.name(),
343 ReferentialSynchroTables.PMFM.name(),
344 DatabaseColumns.PMFM_ID.name()
345 );
346 }
347
348
349
350
351
352
353
354
355 protected String initSelectRemoteIdFromSurveyId() {
356 return String.format("select %s from %s where %s = ?",
357 DatabaseColumns.REMOTE_ID.name(),
358 DataSynchroTables.SURVEY.name(),
359 DatabaseColumns.SURVEY_ID.name());
360 }
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375 protected Number[] getRemoteIdAndParentIdFromSamplingOperId(SynchroBaseDao dao, long samplingOperId) throws SQLException {
376 if (selectParentIdFromSamplingOperIdStatement == null || selectParentIdFromSamplingOperIdStatement.isClosed()) {
377 selectParentIdFromSamplingOperIdStatement = dao.getPreparedStatement(selectParentIdFromSamplingOperIdQuery);
378 }
379 selectParentIdFromSamplingOperIdStatement.setLong(1, samplingOperId);
380 ResultSet resultSet = null;
381 try {
382 resultSet = selectParentIdFromSamplingOperIdStatement.executeQuery();
383 if (!resultSet.next()) {
384 return null;
385 }
386 Number remoteId = null;
387 if (resultSet.getObject(1) != null) {
388 remoteId = (Number) resultSet.getObject(1);
389 }
390 Number surveyId = null;
391 if (resultSet.getObject(2) != null) {
392 surveyId = (Number) resultSet.getObject(2);
393 }
394 return new Number[] { remoteId, surveyId };
395 } finally {
396 Daos.closeSilently(resultSet);
397 }
398 }
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413 protected Number[] getRemoteIdAndParentIdFromSampleId(SynchroBaseDao dao, long sampleId) throws SQLException {
414 if (selectRemoteIdAndParentIdsFromSampleIdStatement == null || selectRemoteIdAndParentIdsFromSampleIdStatement.isClosed()) {
415 selectRemoteIdAndParentIdsFromSampleIdStatement = dao.getPreparedStatement(selectRemoteIdAndParentIdsFromSampleIdQuery);
416 }
417
418 selectRemoteIdAndParentIdsFromSampleIdStatement.setLong(1, sampleId);
419 ResultSet resultSet = null;
420 try {
421 resultSet = selectRemoteIdAndParentIdsFromSampleIdStatement.executeQuery();
422 if (!resultSet.next()) {
423 return null;
424 }
425 Number remoteId = null;
426 if (resultSet.getObject(1) != null) {
427 remoteId = (Number) resultSet.getObject(1);
428 }
429 Number surveyId = null;
430 if (resultSet.getObject(2) != null) {
431 surveyId = (Number) resultSet.getObject(2);
432 }
433 Number samplingOperId = null;
434 if (resultSet.getObject(3) != null) {
435 samplingOperId = (Number) resultSet.getObject(3);
436 }
437
438 return new Number[] { remoteId, surveyId, samplingOperId };
439 } finally {
440 Daos.closeSilently(resultSet);
441 }
442 }
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457 protected List<Object> getPmfmParts(SynchroBaseDao dao, long pmfmId) throws SQLException {
458 String resultStr = dao.getUniqueTyped(selectPmfmQuery, new Object[] { pmfmId });
459 return SynchroTableMetadata.fromPkStr(resultStr);
460 }
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475 protected Number getRemoteIdFromSurveyId(SynchroBaseDao dao, long surveyId) throws SQLException {
476 return dao.getUniqueTyped(selectRemoteIdBySurveyIdQuery, new Object[] { surveyId });
477 }
478
479
480 @Override
481 protected void doClose() throws IOException {
482 super.doClose();
483
484
485 Daos.closeSilently(selectParentIdFromSamplingOperIdStatement);
486 selectParentIdFromSamplingOperIdStatement = null;
487
488
489 Daos.closeSilently(selectRemoteIdAndParentIdsFromSampleIdStatement);
490 selectRemoteIdAndParentIdsFromSampleIdStatement = null;
491 }
492 }