1 package fr.ifremer.adagio.synchro.meta;
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 static org.nuiton.i18n.I18n.t;
27
28 import java.lang.reflect.Field;
29 import java.math.BigDecimal;
30 import java.sql.ResultSet;
31 import java.sql.SQLException;
32 import java.sql.Types;
33 import java.util.Date;
34 import java.util.List;
35 import java.util.Map;
36 import java.util.Set;
37
38 import oracle.sql.TIMESTAMP;
39
40 import org.apache.commons.collections.CollectionUtils;
41 import org.apache.commons.lang3.StringUtils;
42 import org.apache.commons.logging.Log;
43 import org.apache.commons.logging.LogFactory;
44 import org.hibernate.dialect.Dialect;
45 import org.hibernate.internal.util.StringHelper;
46 import org.hibernate.mapping.ForeignKey;
47 import org.hibernate.tool.hbm2ddl.ColumnMetadata;
48 import org.hibernate.tool.hbm2ddl.ForeignKeyMetadata;
49 import org.hibernate.tool.hbm2ddl.IndexMetadata;
50 import org.hibernate.tool.hbm2ddl.TableMetadata;
51
52 import com.google.common.base.Preconditions;
53 import com.google.common.base.Predicate;
54 import com.google.common.collect.ImmutableSet;
55 import com.google.common.collect.Lists;
56 import com.google.common.collect.Maps;
57 import com.google.common.collect.Sets;
58
59 import fr.ifremer.adagio.synchro.SynchroTechnicalException;
60 import fr.ifremer.adagio.synchro.config.SynchroConfiguration;
61 import fr.ifremer.adagio.synchro.dao.DaoUtils;
62 import fr.ifremer.adagio.synchro.intercept.SynchroInterceptor;
63 import fr.ifremer.adagio.synchro.service.SynchroContext;
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85 public class SynchroTableMetadata {
86 private static final Log log = LogFactory.getLog(SynchroTableMetadata.class);
87
88 public enum TableInsertStrategy {
89 GENERATE_ID_FIRST,
90 INLINE_INSERT
91 };
92
93 public static final String PK_SEPARATOR = "~~";
94
95 public static final String COLUMN_SYNCHRONIZATION_STATUS = "synchronization_status";
96
97 public static final String COLUMN_ID = "id";
98
99 public static final String COLUMN_REMOTE_ID = "remote_id";
100
101 public static final String COLUMN_UPDATE_DATE = "update_date";
102
103 public static final String SEQUENCE_SUFFIX = "_seq";
104
105 public static Set<String> PROTECTED_COLUMN_NAMES = ImmutableSet.<String> builder().add(
106 SynchroTableMetadata.COLUMN_REMOTE_ID,
107 SynchroTableMetadata.COLUMN_SYNCHRONIZATION_STATUS
108 ).build();
109
110 private static Field delegateTableMetadataColumnsField = null;
111
112 @SuppressWarnings({ "unchecked", "rawtypes" })
113 public static Map<String, ColumnMetadata> getColumns(TableMetadata delegate) {
114 try {
115 if (delegateTableMetadataColumnsField == null) {
116 delegateTableMetadataColumnsField = TableMetadata.class.getDeclaredField("columns");
117 delegateTableMetadataColumnsField.setAccessible(true);
118 }
119 return Maps.<String, ColumnMetadata> newLinkedHashMap((Map) delegateTableMetadataColumnsField.get(delegate));
120 } catch (Exception e) {
121 throw new SynchroTechnicalException(e.getMessage(), e);
122 }
123 }
124
125 protected final String selectPrimaryKeysAsStringQuery;
126
127 protected final String selectPrimaryKeysQuery;
128
129 protected final String selectMaxUpdateDateQuery;
130
131 protected final String countQuery;
132
133 protected final TableMetadata delegate;
134
135 protected final Map<String, SynchroColumnMetadata> columns;
136
137 protected List<SynchroJoinMetadata> childJoins;
138
139 protected List<SynchroJoinMetadata> parentJoins;
140
141 protected List<SynchroJoinMetadata> joins;
142
143 protected boolean hasJoins;
144
145 protected boolean hasChildJoins;
146
147 protected final List<String> protectedColumnNames;
148
149 protected final List<String> columnNames;
150
151 protected final Set<String> pkNames;
152
153 protected final int[] pkIndexs;
154
155 protected String insertQuery;
156
157 protected String insertWithGeneratedIdQuery;
158
159 protected String updateQuery;
160
161 protected final boolean withUpdateDateColumn;
162
163 protected final boolean withSynchronizationStatusColumn;
164
165 protected final boolean withIdColumn;
166
167 protected final boolean withRemoteIdColumn;
168
169 protected boolean isRoot;
170
171 protected final String countDataToUpdateQuery;
172
173 protected final String selectDataToUpdateQuery;
174
175 protected final String sequenceName;
176
177 protected final String selectSequenceNextValueString;
178
179 protected final String sequenceNextValString;
180
181 protected final String selectAllQuery;
182
183 protected final String selectDataQueryFromPk;
184
185 protected final Map<String, String> selectDataFromFkQueries;
186
187 protected final Map<String, String> selectDataFromFkQueriesBigParams;
188
189 protected final String selectIdFromRemoteIdQuery;
190
191 protected final String selectRemoteIdsQuery;
192
193 protected List<SynchroInterceptor> interceptors;
194
195 protected final SynchroContext context;
196
197 protected TableInsertStrategy insertStrategy;
198
199 protected final SynchroDatabaseMetadata dbMeta;
200
201 protected SynchroTableMetadata(
202 SynchroDatabaseMetadata dbMeta,
203 TableMetadata delegate,
204 List<SynchroInterceptor> interceptors,
205 String tableName,
206 Set<String> availableSequences,
207 Predicate<SynchroColumnMetadata> columnFilter) {
208
209 Preconditions.checkNotNull(delegate);
210 Preconditions.checkNotNull(dbMeta);
211
212 this.delegate = delegate;
213 this.interceptors = interceptors != null ? interceptors : Lists.<SynchroInterceptor> newArrayList();
214 this.dbMeta = dbMeta;
215 this.context = dbMeta.getContext();
216
217 try {
218 this.columns = initColumns(tableName, dbMeta, columnFilter);
219 this.columnNames = initColumnNames(columns);
220 this.protectedColumnNames = initProtectedColumnNames(columns);
221 this.pkNames = initPrimaryKeys(dbMeta);
222 Preconditions.checkNotNull(pkNames);
223 this.joins = Lists.newArrayList();
224 this.hasJoins = !joins.isEmpty();
225 this.withUpdateDateColumn = columnNames.contains(COLUMN_UPDATE_DATE);
226 this.withSynchronizationStatusColumn = protectedColumnNames.contains(COLUMN_SYNCHRONIZATION_STATUS);
227 this.withIdColumn = columnNames.contains(COLUMN_ID);
228 this.withRemoteIdColumn = protectedColumnNames.contains(COLUMN_REMOTE_ID);
229 this.sequenceName = initSequenceName(availableSequences);
230 Preconditions
231 .checkArgument(!withRemoteIdColumn || sequenceName != null,
232 String.format("Columns %s and %s found on table %s, but unable to retrieve a sequence !", COLUMN_REMOTE_ID, COLUMN_ID,
233 tableName));
234 Preconditions.checkArgument(!withRemoteIdColumn || (pkNames.size() == 1 && withIdColumn),
235 String.format("Columns %s found on table %s, but more than one PK columns exists. Only %s column must be a PK.",
236 COLUMN_REMOTE_ID, tableName, COLUMN_ID));
237
238
239 this.isRoot = false;
240 } catch (Exception e) {
241 throw new SynchroTechnicalException(t("adagio.persistence.tableMetadata.instanciation.error", this), e);
242 }
243
244 this.pkIndexs = createPkIndex();
245 this.selectSequenceNextValueString = createSelectSequenceNextValString(dbMeta.getDialect());
246 this.sequenceNextValString = createSequenceNextValString(dbMeta.getDialect());
247 this.insertQuery = createInsertQuery();
248 this.insertWithGeneratedIdQuery = createInsertWithGeneratedIdQuery();
249 this.updateQuery = createUpdateQuery();
250 this.selectMaxUpdateDateQuery = createSelectMaxUpdateDateQuery();
251 this.selectPrimaryKeysAsStringQuery = createSelectPrimaryKeysAsStringQuery();
252 this.selectPrimaryKeysQuery = createSelectPrimaryKeysQuery();
253 this.selectRemoteIdsQuery = createSelectRemoteIdsQuery();
254 this.selectAllQuery = createSelectAllQuery();
255 this.selectDataQueryFromPk = createSelectDataFromPkQuery();
256 this.selectDataToUpdateQuery = createSelectDataToUpdateQuery();
257 this.selectDataFromFkQueries = createSelectDataFromFkQueries();
258 this.selectDataFromFkQueriesBigParams = createSelectDataFromFkQueriesBigParams(dbMeta);
259 this.selectIdFromRemoteIdQuery = createSelectIdFromRemoteIdQuery();
260 this.countQuery = createCountQuery();
261 this.countDataToUpdateQuery = createCountDataToUpdateQuery();
262 this.insertStrategy = TableInsertStrategy.INLINE_INSERT;
263 }
264
265
266 SynchroTableMetadata() {
267
268 delegate = null;
269 context = null;
270 dbMeta = null;
271 columns = null;
272 columnNames = null;
273 protectedColumnNames = null;
274 joins = null;
275 pkNames = null;
276 pkIndexs = null;
277 withUpdateDateColumn = false;
278 withSynchronizationStatusColumn = false;
279 withIdColumn = false;
280 withRemoteIdColumn = false;
281 isRoot = false;
282 insertQuery = null;
283 insertWithGeneratedIdQuery = null;
284 updateQuery = null;
285 countQuery = null;
286 countDataToUpdateQuery = null;
287 selectPrimaryKeysAsStringQuery = null;
288 selectPrimaryKeysQuery = null;
289 selectRemoteIdsQuery = null;
290 selectMaxUpdateDateQuery = null;
291 selectDataToUpdateQuery = null;
292 selectDataFromFkQueries = null;
293 selectDataFromFkQueriesBigParams = null;
294 selectIdFromRemoteIdQuery = null;
295 selectSequenceNextValueString = null;
296 sequenceNextValString = null;
297 sequenceName = null;
298 selectAllQuery = null;
299 selectDataQueryFromPk = null;
300 }
301
302
303
304
305
306
307
308 public void initJoins(SynchroDatabaseMetadata dbMeta) {
309 try {
310 this.joins = initJoins(getName(), dbMeta, this.columns, this.interceptors);
311 this.hasJoins = !joins.isEmpty();
312 this.childJoins = initChildJoins(joins);
313 this.parentJoins = initParentJoins(joins);
314 this.hasChildJoins = !childJoins.isEmpty();
315 this.insertQuery = createInsertQuery();
316 this.insertStrategy = !hasJoins || !withRemoteIdColumn
317 ? TableInsertStrategy.INLINE_INSERT
318 : TableInsertStrategy.GENERATE_ID_FIRST;
319 } catch (Exception e) {
320 throw new SynchroTechnicalException(t("adagio.persistence.tableMetadata.instanciation.error", this), e);
321 }
322 }
323
324 public Set<String> getPkNames() {
325 return pkNames;
326 }
327
328 public boolean isWithUpdateDateColumn() {
329 return withUpdateDateColumn;
330 }
331
332 public boolean isWithSynchronizationStatusColumn() {
333 return withSynchronizationStatusColumn;
334 }
335
336 public boolean isWithIdColumn() {
337 return withIdColumn;
338 }
339
340 public boolean isWithRemoteIdColumn() {
341 return withRemoteIdColumn;
342 }
343
344 public boolean isRoot() {
345 return isRoot;
346 }
347
348 public int getColumnsCount() {
349 return columnNames.size();
350 }
351
352 public Set<String> getColumnNames() {
353 return ImmutableSet.copyOf(columnNames);
354 }
355
356 public int getColumnIndex(String name) {
357 return columnNames.indexOf(name.toLowerCase());
358 }
359
360 public TableMetadata getDelegate() {
361 return delegate;
362 }
363
364 public SynchroDatabaseMetadata getDatabaseMetadata() {
365 return dbMeta;
366 }
367
368 public String getName() {
369 return delegate.getName();
370 }
371
372 public ForeignKeyMetadata getForeignKeyMetadata(ForeignKey fk) {
373 return delegate.getForeignKeyMetadata(fk);
374 }
375
376 public SynchroColumnMetadata getColumnMetadata(String columnName) {
377 return columns.get(StringHelper.toLowerCase(columnName));
378 }
379
380 public String getSchema() {
381 return delegate.getSchema();
382 }
383
384 public String getCatalog() {
385 return delegate.getCatalog();
386 }
387
388 public ForeignKeyMetadata getForeignKeyMetadata(String keyName) {
389 return delegate.getForeignKeyMetadata(keyName);
390 }
391
392 public IndexMetadata getIndexMetadata(String indexName) {
393 return delegate.getIndexMetadata(indexName);
394 }
395
396 public SynchroColumnMetadata getColumn(String columnName) {
397 return columns.get(columnName);
398 }
399
400 public List<SynchroJoinMetadata> getJoins() {
401 return joins;
402 }
403
404 public List<SynchroJoinMetadata> getChildJoins() {
405 return childJoins;
406 }
407
408 public List<SynchroJoinMetadata> getParentJoins() {
409 return parentJoins;
410 }
411
412 public boolean hasJoins() {
413 return hasJoins;
414 }
415
416 public boolean hasChildJoins() {
417 return hasChildJoins;
418 }
419
420 public String getTableLogPrefix() {
421 return "[" + getName() + "]";
422 }
423
424 public TableInsertStrategy getInsertStrategy() {
425 return this.insertStrategy;
426 }
427
428 @Override
429 public String toString() {
430 return delegate.toString();
431 }
432
433
434
435
436
437 public String getInsertQuery() {
438 return insertQuery;
439 }
440
441 public String getInsertWithGeneratedIdQuery() {
442 return insertWithGeneratedIdQuery;
443 }
444
445 public String getUpdateQuery() {
446 return updateQuery;
447 }
448
449
450
451
452
453
454 public String getSelectPrimaryKeysAsStringQuery() {
455 return selectPrimaryKeysAsStringQuery;
456 }
457
458 public String getSelectPrimaryKeysQuery() {
459 return selectPrimaryKeysQuery;
460 }
461
462 public String getSelectRemoteIdsQuery() {
463 return selectRemoteIdsQuery;
464 }
465
466 public String getSequenceNextValString() {
467 return sequenceNextValString;
468 }
469
470 public String getSelectMaxUpdateDateQuery() {
471 return selectMaxUpdateDateQuery;
472 }
473
474 public String getSelectDataQueryFromPk() {
475 return selectDataQueryFromPk;
476 }
477
478 public String getSelectDataToUpdateQuery(Date fromDate) {
479 String sql;
480 if (fromDate == null) {
481 sql = selectAllQuery;
482 }
483 else {
484 sql = selectDataToUpdateQuery;
485 }
486
487 return sql;
488 }
489
490 public String getSelectDataFromFkQuery(String columnName, int nbValues) {
491 String sql = selectDataFromFkQueries.get(columnName.toLowerCase());
492 StringBuilder builder = new StringBuilder();
493 for (int i = 0; i < nbValues; i++) {
494 builder.append(",?");
495 }
496 return String.format(sql,
497 builder.substring(1));
498 }
499
500 public String getSelectDataFromFkQueryBigParams(String columnName) {
501 return selectDataFromFkQueriesBigParams.get(columnName.toLowerCase());
502 }
503
504 public String getCountQuery() {
505 return countQuery;
506 }
507
508 public String getCountDataToUpdateQuery(Date fromDate) {
509 String sql;
510 if (fromDate == null) {
511 sql = countQuery;
512 }
513 else {
514 sql = countDataToUpdateQuery;
515 }
516 return sql;
517 }
518
519 public String getSelectIdFromRemoteIdQuery(String tableName) {
520 return String.format(selectIdFromRemoteIdQuery, tableName);
521 }
522
523 public Object[] getData(ResultSet incomingData) throws SQLException {
524 Object[] result = new Object[columnNames.size()];
525 for (int c = 1; c <= columnNames.size(); c++) {
526 Object object = getObject(incomingData, c);
527
528 result[c - 1] = object;
529 }
530
531 return result;
532 }
533
534 public Object getObject(ResultSet incomingData, int index) throws SQLException {
535 Object object = incomingData.getObject(index);
536 if (object instanceof TIMESTAMP) {
537 object = ((TIMESTAMP) object).timestampValue();
538 }
539 if (object instanceof BigDecimal) {
540 object = ((BigDecimal) object).intValue();
541 }
542 return object;
543 }
544
545
546
547
548
549 public int[] getPkIndexs() {
550 return pkIndexs;
551 }
552
553 public boolean isSimpleKey() {
554 return pkIndexs.length == 1;
555 }
556
557 public List<Object> getPk(ResultSet incomingData) throws SQLException {
558 List<Object> result = Lists.newArrayListWithCapacity(pkIndexs.length);
559 for (int pkIndex : pkIndexs) {
560 Object pk = getObject(incomingData, pkIndex);
561 result.add(pk);
562 }
563 return result;
564 }
565
566 public boolean isSelectPrimaryKeysAsStringQueryEnable() {
567 return selectPrimaryKeysAsStringQuery != null;
568 }
569
570 public List<Object> getPk(Object[] incomingData) throws SQLException {
571 List<Object> result = Lists.newArrayListWithCapacity(pkIndexs.length);
572 for (int pkIndex : pkIndexs) {
573 Object pk = incomingData[pkIndex - 1];
574 result.add(pk);
575 }
576 return result;
577 }
578
579 public Integer getId(ResultSet incomingData) throws SQLException {
580 return incomingData.getInt(pkIndexs[0]);
581 }
582
583
584
585
586
587
588
589 public String toPkStr(List<Object> pkList) {
590 StringBuilder sb = new StringBuilder();
591 for (Object pk : pkList) {
592 sb.append(PK_SEPARATOR).append(pk);
593 }
594 return sb.substring(PK_SEPARATOR.length());
595 }
596
597 public List<Object> fromPkStr(String pk) {
598 List<Object> pkList = Lists.newArrayList();
599 String[] split = pk.split(PK_SEPARATOR);
600 for (String s : split) {
601 if ("null".equals(s)) {
602 s = null;
603 }
604 pkList.add(s);
605 }
606 return pkList;
607 }
608
609
610
611
612
613 protected Map<String, SynchroColumnMetadata> initColumns(
614 String tableName,
615 SynchroDatabaseMetadata dbMeta,
616 Predicate<SynchroColumnMetadata> columnFilter
617 ) throws NoSuchFieldException, SecurityException, IllegalArgumentException, IllegalAccessException {
618
619 Map<String, ColumnMetadata> delegateColumns = getColumns(delegate);
620 Map<String, SynchroColumnMetadata> columns = Maps.newHashMap();
621 int columnIndex = 1;
622 for (String columnName : delegateColumns.keySet()) {
623 ColumnMetadata delegateColumn = delegateColumns.get(columnName);
624 SynchroColumnMetadata column = new SynchroColumnMetadata(
625 delegateColumn,
626 tableName,
627 columnIndex,
628 PROTECTED_COLUMN_NAMES.contains(columnName));
629 if (columnFilter == null || columnFilter.apply(column)) {
630 columns.put(columnName, column);
631 columnIndex++;
632 }
633 }
634
635 return columns;
636 }
637
638 protected List<String> initProtectedColumnNames(Map<String, SynchroColumnMetadata> columns) {
639 List<String> result = Lists.newArrayListWithExpectedSize(PROTECTED_COLUMN_NAMES.size());
640 for (String name : columns.keySet()) {
641 if (PROTECTED_COLUMN_NAMES.contains(name.toLowerCase())) {
642 result.add(name.toLowerCase());
643 }
644 }
645 return result;
646 }
647
648 protected List<String> initColumnNames(Map<String, SynchroColumnMetadata> columns) {
649 List<String> result = Lists.newArrayListWithExpectedSize(columns.size());
650 for (String name : columns.keySet()) {
651 if (PROTECTED_COLUMN_NAMES.contains(name.toLowerCase()) == false) {
652 result.add(name.toLowerCase());
653 }
654 }
655 return result;
656 }
657
658 protected Set<String> initPrimaryKeys(SynchroDatabaseMetadata dbMeta) throws SQLException {
659
660 Set<String> result = Sets.newLinkedHashSet();
661 ResultSet rs = dbMeta.getPrimaryKeys(getCatalog(), getSchema(), getName());
662 try {
663
664 while (rs.next()) {
665 result.add(rs.getString("COLUMN_NAME").toLowerCase());
666 }
667 return ImmutableSet.copyOf(result);
668 } finally {
669 DaoUtils.closeSilently(rs);
670 }
671 }
672
673 protected int[] createPkIndex() {
674
675 int[] result = new int[pkNames.size()];
676
677 int pkI = 0;
678 for (String pkName : pkNames) {
679 String pkColumnName = pkName.toLowerCase();
680
681 int i = 1;
682
683 int index = -1;
684 for (String columnName : columnNames) {
685 if (pkColumnName.equals(columnName)) {
686 index = i;
687 } else {
688 i++;
689 }
690 }
691 result[pkI++] = index;
692 }
693 return result;
694 }
695
696 protected List<SynchroJoinMetadata> initJoins(
697 String tableName,
698 SynchroDatabaseMetadata dbMeta,
699 Map<String, SynchroColumnMetadata> columns,
700 List<SynchroInterceptor> interceptors
701 ) throws SQLException {
702
703 List<SynchroJoinMetadata> result = Lists.newArrayList();
704
705
706 ResultSet rs = dbMeta.getExportedKeys(delegate.getCatalog(), delegate.getSchema(), tableName);
707 while (rs.next()) {
708 String columnName = rs.getString("PKCOLUMN_NAME");
709 if (columns.get(columnName.toLowerCase()) != null) {
710 SynchroJoinMetadata join = new SynchroJoinMetadata(rs, this, dbMeta);
711
712
713 fireOnJoinLoad(join);
714
715 if (join.isValid()) {
716 result.add(join);
717 }
718 }
719 }
720
721
722 rs = dbMeta.getImportedKeys(delegate.getCatalog(), delegate.getSchema(), tableName);
723 while (rs.next()) {
724 String columnName = rs.getString("FKCOLUMN_NAME");
725 if (columns.get(columnName.toLowerCase()) != null) {
726 SynchroJoinMetadata join = new SynchroJoinMetadata(rs, this, dbMeta);
727
728
729 fireOnJoinLoad(join);
730
731 if (join.isValid()) {
732 result.add(join);
733 }
734 }
735 }
736
737 return result;
738 }
739
740 protected List<SynchroJoinMetadata> initChildJoins(
741 List<SynchroJoinMetadata> joins
742 ) throws SQLException {
743
744 List<SynchroJoinMetadata> result = Lists.newArrayListWithExpectedSize(joins.size());
745
746 for (SynchroJoinMetadata join : joins) {
747 if (join.isChild()) {
748 result.add(join);
749 }
750 }
751 return result;
752 }
753
754 protected List<SynchroJoinMetadata> initParentJoins(
755 List<SynchroJoinMetadata> joins
756 ) throws SQLException {
757
758 List<SynchroJoinMetadata> result = Lists.newArrayListWithExpectedSize(joins.size());
759
760 for (SynchroJoinMetadata join : joins) {
761 if (!join.isChild()) {
762 result.add(join);
763 }
764 }
765 return result;
766 }
767
768 protected String createInsertQuery() {
769 return createInsertQuery(false);
770 }
771
772 protected String createInsertWithGeneratedIdQuery() {
773 if (!withRemoteIdColumn) {
774 return null;
775 }
776 return createInsertQuery(true);
777 }
778
779 protected String createInsertQuery(boolean generateId) {
780 if (CollectionUtils.isEmpty(columnNames)) {
781 return null;
782 }
783
784 StringBuilder queryParams = new StringBuilder();
785 StringBuilder valueParams = new StringBuilder();
786
787 for (String columnName : columnNames) {
788 if (withRemoteIdColumn && COLUMN_ID.equals(columnName)) {
789 queryParams.append(", ").append(COLUMN_REMOTE_ID);
790 }
791 else {
792 queryParams.append(", ").append(columnName);
793 }
794 valueParams.append(", ?");
795 }
796
797 if (withRemoteIdColumn) {
798 queryParams.append(", ").append(COLUMN_ID);
799 if (generateId) {
800 valueParams.append(", ").append(selectSequenceNextValueString);
801 }
802 else {
803 valueParams.append(", ?");
804 }
805 }
806
807 if (withSynchronizationStatusColumn) {
808 queryParams.append(", ").append(COLUMN_SYNCHRONIZATION_STATUS);
809 valueParams.append(", '")
810 .append(SynchroConfiguration.getInstance().getSynchronizationStatusSynchronized())
811 .append("'");
812 }
813
814 String result = String.format("INSERT INTO %s (%s) VALUES (%s)",
815 getName(),
816 queryParams.substring(2),
817 valueParams.substring(2));
818 return result;
819 }
820
821 protected String createUpdateQuery() {
822
823 if (CollectionUtils.isEmpty(pkNames)) {
824 return null;
825 }
826 if (CollectionUtils.isEmpty(columnNames)) {
827 return null;
828 }
829
830 StringBuilder updateParams = new StringBuilder();
831
832 for (String columnName : columnNames) {
833 if (withRemoteIdColumn && COLUMN_ID.equals(columnName)) {
834 updateParams.append(", ").append(COLUMN_REMOTE_ID);
835 }
836 else {
837 updateParams.append(", ").append(columnName);
838 }
839 updateParams.append(" = ?");
840 }
841
842 if (withSynchronizationStatusColumn) {
843 updateParams.append(", ").append(COLUMN_SYNCHRONIZATION_STATUS)
844 .append(" = '")
845 .append(SynchroConfiguration.getInstance().getSynchronizationStatusSynchronized())
846 .append("'");
847 }
848
849 String result = String.format("UPDATE %s SET %s WHERE %s",
850 getName(),
851 updateParams.substring(2),
852 createPkWhereClause());
853 return result;
854 }
855
856 protected String createSelectDataFromPkQuery() {
857
858 if (CollectionUtils.isEmpty(pkNames)) {
859 return null;
860 }
861
862 String sql = String.format("SELECT %s FROM %s WHERE %s",
863 createSelectParams(),
864 getName(),
865 createPkWhereClause());
866
867 sql = fireOnCreateSelectQuery("selectDataFromPkQuery", sql);
868 return sql;
869 }
870
871 protected Map<String, String> createSelectDataFromFkQueries() {
872 Map<String, String> result = Maps.newHashMap();
873
874 for (SynchroColumnMetadata column : columns.values()) {
875 String sql = String.format("SELECT %s FROM %s WHERE %s IN (%s)",
876 createSelectParams(),
877 getName(),
878 column.getName(),
879 "%s");
880
881 sql = fireOnCreateSelectQuery("selectDataFromFkQuery", sql);
882 result.put(column.getName().toLowerCase(), sql);
883 }
884
885 return result;
886 }
887
888 protected Map<String, String> createSelectDataFromFkQueriesBigParams(SynchroDatabaseMetadata dbMeta) {
889 Map<String, String> result = Maps.newHashMap();
890
891 for (SynchroColumnMetadata column : columns.values()) {
892 String sql = String.format(
893 "SELECT %s FROM %s t INNER JOIN TEMP_QUERY_PARAMETER p on t.%s = p.NUMERICAL_VALUE AND p.PARAMETER_NAME=? AND p.PERSON_FK=?",
894 createSelectParams("t"),
895 getName(),
896 column.getName());
897
898 sql = fireOnCreateSelectQuery("selectDataFromFkQueryBigParams", sql);
899 result.put(column.getName().toLowerCase(), sql);
900 }
901
902 return result;
903 }
904
905 protected String createSelectPrimaryKeysAsStringQuery() {
906
907 if (CollectionUtils.isEmpty(pkNames)) {
908 return null;
909 }
910
911 String prefix = " || '" + PK_SEPARATOR + "' || ";
912 StringBuilder pkParams = new StringBuilder();
913
914 boolean allowUniqueOutputColumn = true;
915
916 for (String columnName : pkNames) {
917
918
919 SynchroColumnMetadata column = columns.get(columnName);
920
921
922
923 if (column.getTypeCode() == Types.TIMESTAMP
924 || column.getTypeCode() == Types.DATE) {
925 allowUniqueOutputColumn = false;
926 }
927 pkParams.append(prefix).append(columnName);
928 }
929
930 if (!allowUniqueOutputColumn) {
931 return null;
932 }
933
934 String sql = String.format("SELECT %s FROM %s",
935 pkParams.substring(prefix.length()),
936 getName());
937
938 sql = fireOnCreateSelectQuery("selectPrimaryKeysAsStringQuery", sql);
939 return sql;
940 }
941
942 protected String createSelectPrimaryKeysQuery() {
943
944 if (CollectionUtils.isEmpty(pkNames)) {
945 return null;
946 }
947
948 StringBuilder pkParams = new StringBuilder();
949
950 for (String columnName : pkNames) {
951 pkParams.append(", ").append(columnName);
952 }
953
954 String sql = String.format("SELECT %s FROM %s",
955 pkParams.substring(2),
956 getName());
957
958 sql = fireOnCreateSelectQuery("selectPrimaryKeysQuery", sql);
959
960 return sql;
961 }
962
963 protected String createSelectRemoteIdsQuery() {
964
965 if (!withRemoteIdColumn || !withIdColumn) {
966 return null;
967 }
968
969 String sql = String.format("SELECT %s, %s FROM %s WHERE %s IS NOT NULL",
970 COLUMN_ID,
971 COLUMN_REMOTE_ID,
972 getName(),
973 COLUMN_REMOTE_ID);
974
975 sql = fireOnCreateSelectQuery("selectRemoteIdsQuery", sql);
976
977 return sql;
978 }
979
980 protected String createSelectAllQuery() {
981 String sql = String.format("SELECT %s FROM %s t",
982 createSelectParams("t"),
983 getName()
984 );
985
986 sql = fireOnCreateSelectQuery("selectAllQuery", sql);
987
988 return sql;
989 }
990
991 protected String createSelectMaxUpdateDateQuery() {
992 if (!withUpdateDateColumn) {
993 return null;
994 }
995 String sql = String.format("SELECT max(%s) FROM %s",
996 COLUMN_UPDATE_DATE,
997 getName());
998
999 sql = fireOnCreateSelectQuery("selectMaxUpdateDateQuery", sql);
1000
1001 return sql;
1002 }
1003
1004 protected String createSelectDataToUpdateQuery() {
1005 String sql = String.format("SELECT %s FROM %s t%s",
1006 createSelectParams("t"),
1007 getName(),
1008 createWithUpdateDateWhereClause("t"));
1009
1010 sql = fireOnCreateSelectQuery("selectDataToUpdateQuery", sql);
1011
1012 return sql;
1013 }
1014
1015 protected String createCountQuery() {
1016 String sql = String.format("SELECT count(*) FROM %s t",
1017 getName());
1018
1019 sql = fireOnCreateSelectQuery("countQuery", sql);
1020
1021 return sql;
1022 }
1023
1024 protected String createSelectIdFromRemoteIdQuery() {
1025 if (!withIdColumn || withRemoteIdColumn) {
1026 return null;
1027 }
1028 String sql = String.format("SELECT %s FROM %s WHERE %s=?",
1029 SynchroTableMetadata.COLUMN_ID,
1030 "%s",
1031 SynchroTableMetadata.COLUMN_REMOTE_ID
1032 );
1033
1034 sql = fireOnCreateSelectQuery("selectIdFromRemoteIdQuery", sql);
1035
1036 return sql;
1037 }
1038
1039 protected String createCountDataToUpdateQuery() {
1040 String sql = String.format("SELECT count(*) FROM %s t%s",
1041 getName(),
1042 createWithUpdateDateWhereClause("t"));
1043
1044 sql = fireOnCreateSelectQuery("countDataToUpdateQuery", sql);
1045
1046 return sql;
1047 }
1048
1049 protected String createPkWhereClause() {
1050 Preconditions.checkArgument(CollectionUtils.isNotEmpty(pkNames));
1051 StringBuilder pkParams = new StringBuilder();
1052
1053 for (String columnName : pkNames) {
1054 pkParams.append("AND ").append(columnName).append(" = ?");
1055 }
1056
1057 return pkParams.substring(4);
1058 }
1059
1060 protected String createWithUpdateDateWhereClause() {
1061 return createWithUpdateDateWhereClause(null);
1062 }
1063
1064 protected String createWithUpdateDateWhereClause(String tableAlias) {
1065 String whereClause;
1066
1067 if (isWithUpdateDateColumn()) {
1068 String prefix = tableAlias != null ? tableAlias + "." : "";
1069
1070 whereClause = String.format(
1071 " WHERE (%supdate_date IS NULL OR %supdate_date > ?)",
1072 prefix,
1073 prefix);
1074 } else {
1075 whereClause = "";
1076 }
1077 return whereClause;
1078 }
1079
1080 protected String createSelectParams() {
1081 return createSelectParams(null);
1082 }
1083
1084 protected String createSelectParams(String tableAlias) {
1085 Preconditions.checkArgument(CollectionUtils.isNotEmpty(columnNames),
1086 String.format("No column found for table: %s", delegate.getName()));
1087
1088 StringBuilder queryParams = new StringBuilder();
1089
1090 for (String columnName : columnNames) {
1091 queryParams.append(", ");
1092 if (tableAlias != null) {
1093 queryParams.append(tableAlias)
1094 .append(".");
1095 }
1096 queryParams.append(columnName);
1097 }
1098
1099 return queryParams.substring(2);
1100 }
1101
1102 protected String initSequenceName(Set<String> availableSequences) {
1103
1104 String tableName = getName().toLowerCase();
1105 String sequenceName = tableName + SEQUENCE_SUFFIX;
1106 if (availableSequences.contains(sequenceName.toLowerCase())) {
1107 return sequenceName;
1108 }
1109
1110 int maxLengthWithoutSuffix = 30 - SEQUENCE_SUFFIX.length();
1111 if (tableName.length() > maxLengthWithoutSuffix) {
1112 sequenceName = tableName.substring(0, maxLengthWithoutSuffix) + SEQUENCE_SUFFIX;
1113 if (availableSequences.contains(sequenceName.toLowerCase())) {
1114 return sequenceName;
1115 }
1116 }
1117
1118 return null;
1119 }
1120
1121 public void setIsRoot(boolean isRoot) {
1122 this.isRoot = isRoot;
1123 }
1124
1125 protected String createSelectSequenceNextValString(Dialect dialect) {
1126 if (StringUtils.isBlank(sequenceName)) {
1127 return null;
1128 }
1129 return dialect.getSelectSequenceNextValString(sequenceName);
1130 }
1131
1132 protected String createSequenceNextValString(Dialect dialect) {
1133 if (StringUtils.isBlank(sequenceName)) {
1134 return null;
1135 }
1136 return dialect.getSequenceNextValString(sequenceName);
1137 }
1138
1139 public List<SynchroInterceptor> getInterceptors() {
1140 return this.interceptors;
1141 }
1142
1143 protected String fireOnCreateSelectQuery(String queryName, String sql) {
1144 if (CollectionUtils.isNotEmpty(interceptors)) {
1145 for (SynchroInterceptor interceptor : interceptors) {
1146 String newSql = interceptor.onCreateSelectQuery(this, queryName, sql);
1147 if (newSql != null) {
1148 sql = newSql;
1149 }
1150 }
1151 }
1152 return sql;
1153 }
1154
1155 protected void fireOnJoinLoad(SynchroJoinMetadata join) {
1156 if (CollectionUtils.isNotEmpty(interceptors)) {
1157 for (SynchroInterceptor interceptor : interceptors) {
1158 interceptor.onJoinLoad(this, join);
1159 }
1160 }
1161 }
1162 }