1 package net.sumaris.core.dao.technical.schema;
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.Joiner;
27 import com.google.common.base.Preconditions;
28 import com.google.common.collect.ImmutableSet;
29 import com.google.common.collect.Maps;
30 import com.google.common.collect.Sets;
31 import net.sumaris.core.config.SumarisConfiguration;
32 import net.sumaris.core.dao.technical.SortDirection;
33 import org.apache.commons.collections.CollectionUtils;
34 import org.apache.commons.lang3.StringUtils;
35 import org.hibernate.boot.model.relational.QualifiedTableName;
36 import org.hibernate.dialect.Dialect;
37 import org.springframework.dao.DataIntegrityViolationException;
38
39 import java.sql.DatabaseMetaData;
40 import java.sql.ResultSet;
41 import java.sql.SQLException;
42 import java.util.Collection;
43 import java.util.LinkedHashSet;
44 import java.util.Map;
45 import java.util.Set;
46
47
48
49
50
51
52
53
54
55
56
57
58 public class SumarisTableMetadata {
59
60 public static final String DEFAULT_TABLE_ALIAS = "t";
61
62 protected static final String QUERY_INSERT = "INSERT INTO %s (%s) VALUES (%s)";
63 protected static final String QUERY_UPDATE = "UPDATE %s SET %s WHERE %s";
64 protected static final String QUERY_DELETE = "DELETE FROM %s %s WHERE %s";
65 protected static final String QUERY_SELECT_ALL = "SELECT %s FROM %s %s";
66 protected static final String QUERY_SELECT_PRIMARY_KEYS = "SELECT %s FROM %s";
67 protected static final String QUERY_SELECT_COUNT_ALL = "SELECT count(*) FROM %s %s";
68 protected static final String QUERY_SELECT_MAX = "SELECT max(%s) FROM %s";
69 protected static final String QUERY_HQL_SELECT = "from %s";
70
71 protected final QualifiedTableName tableName;
72 protected final String tableAlias;
73
74 protected String existingPrimaryKeysQuery;
75 protected String maxUpdateDateQuery;
76 protected String countAllQuery;
77 protected Map<String, SumarisColumnMetadata> columns;
78 protected Set<String> pkNames;
79 protected Set<String> notNullNames;
80 protected int[] pkIndexs;
81 protected String selectAllQuery;
82 protected String insertQuery;
83 protected String updateQuery;
84 protected boolean withUpdateDateColumn;
85 protected String sequenceName;
86 protected String sequenceNextValQuery;
87 protected String countDataToUpdateQuery;
88 protected String dataToUpdateQuery;
89
90 protected SumarisTableMetadata(QualifiedTableName tableName,
91 SumarisDatabaseMetadata dbMeta,
92 DatabaseMetaData jdbcDbMeta) throws SQLException{
93 Preconditions.checkNotNull(tableName);
94 Preconditions.checkNotNull(dbMeta);
95 Preconditions.checkNotNull(jdbcDbMeta);
96
97 this.tableAlias = DEFAULT_TABLE_ALIAS;
98 this.tableName = tableName;
99
100 init(dbMeta, jdbcDbMeta);
101 }
102
103 protected void init(SumarisDatabaseMetadata dbMeta, DatabaseMetaData jdbcDbMeta) throws SQLException {
104 try {
105
106 this.columns = initColumns(this.tableName, jdbcDbMeta);
107 this.withUpdateDateColumn = this.columns.containsKey(dbMeta.getDefaultUpdateDateColumnName().toLowerCase());
108 this.pkNames = initPrimaryKeys(jdbcDbMeta);
109 Preconditions.checkNotNull(pkNames);
110 this.pkIndexs = createPkIndex();
111 this.notNullNames = initNotNull(this.columns);
112
113
114 this.countAllQuery = createAllCountQuery();
115 this.selectAllQuery = createSelectAllQuery();
116 this.insertQuery = createInsertQuery();
117 this.updateQuery = createUpdateQuery();
118 this.existingPrimaryKeysQuery = String.format(QUERY_SELECT_PRIMARY_KEYS, Joiner.on(',').join(pkNames), getName());
119
120
121 this.dataToUpdateQuery = createSelectAllToUpdateQuery(dbMeta);
122 this.countDataToUpdateQuery = createCountDataToUpdateQuery(dbMeta);
123 this.maxUpdateDateQuery = String.format(QUERY_SELECT_MAX, dbMeta.getDefaultUpdateDateColumnName(), getName());
124
125
126 this.sequenceName = initSequenceName(dbMeta);
127 this.sequenceNextValQuery = createSequenceNextValQuery(dbMeta.getDialect());
128
129 } catch (Exception e) {
130 throw new SQLException("Could not init metadata on table " + getName(), e);
131 }
132 }
133
134 public Set<String> getPkNames() {
135 return pkNames;
136 }
137
138 public Set<String> getNotNullNames() {
139 return notNullNames;
140 }
141
142 public boolean isWithUpdateDateColumn() {
143 return withUpdateDateColumn;
144 }
145
146 public int getColumnsCount() {
147 return columns.size();
148 }
149
150 public Set<String> getColumnNames() {
151 return columns.keySet();
152 }
153
154 public String getAlias() {
155 return tableAlias;
156 }
157
158 public String getName() {
159 return tableName.getTableName().getText();
160 }
161
162 public SumarisColumnMetadata getColumnMetadata(String columnName) {
163
164 return columns.get(columnName.toLowerCase());
165 }
166
167 public String getSchema() {
168 return tableName.getSchemaName() != null ? tableName.getSchemaName().getText() : null;
169 }
170
171 public String getCatalog() {
172 return tableName.getCatalogName() != null ? tableName.getCatalogName().getText() : null;
173 }
174
175
176
177
178
179
180 public String getSequenceName() {
181 return sequenceName;
182 }
183
184
185
186
187
188
189 public String getSelectAllQuery() {
190 return selectAllQuery;
191 }
192
193 public String getSelectQuery(Collection<String> columnNames,
194 String whereClause) {
195 return getSelectQuery(false, columnNames, whereClause, null, null);
196 }
197
198 public String getSelectQuery(boolean distinct,
199 Collection<String> columnNames,
200 String whereClause,
201 String sort,
202 SortDirection direction) {
203 StringBuilder sb = new StringBuilder();
204 sb.append(String.format(QUERY_SELECT_ALL,
205 (distinct ? "DISTINCT " : "") + createSelectParams(columnNames, tableAlias),
206 tableName.render().toUpperCase(),
207 tableAlias));
208
209
210 if (StringUtils.isNotBlank(whereClause)) {
211 sb.append(" ").append(whereClause);
212 }
213
214
215 if (StringUtils.isNotBlank(sort)) {
216 sb.append(" ORDER BY ")
217 .append(String.format("%s.%s %s", tableAlias, sort, (direction != null ? direction.name() : "")));
218 }
219
220 return sb.toString();
221 }
222
223
224
225
226
227
228
229
230
231 public String getInsertQuery() {
232 return insertQuery;
233 }
234
235 public String getInsertQuery(Set<String> columnNames) {
236 return createInsertQuery(columnNames);
237 }
238
239
240 public String getUpdateQuery() {
241 return updateQuery;
242 }
243
244 public String getExistingPrimaryKeysQuery() {
245 return existingPrimaryKeysQuery;
246 }
247
248 public String getMaxUpdateDateQuery() {
249 return maxUpdateDateQuery;
250 }
251
252 public String getCountAllQuery() {
253 return countAllQuery;
254 }
255
256 public int[] getPkIndexs() {
257 return pkIndexs;
258 }
259
260 public String getSequenceNextValQuery() {
261 return sequenceNextValQuery;
262 }
263
264 public String getDataToUpdateQuery() {
265 return dataToUpdateQuery;
266 }
267
268 public String getCountDataToUpdateQuery() {
269 return countDataToUpdateQuery;
270 }
271
272 public String getInsertQuery(SumarisColumnMetadata[] columns) {
273 Preconditions.checkNotNull(columns);
274 LinkedHashSet<String> columnNames = Sets.newLinkedHashSetWithExpectedSize(columns.length + 1);
275
276 Set<String> pkColumnNames = getPkNames();
277 if (pkColumnNames.size() > 1) {
278 throw new DataIntegrityViolationException("Could not compute a sql insert query when more than one identifier, for table: "
279 + this.getName());
280 }
281 columnNames.addAll(pkColumnNames);
282
283
284 for (SumarisColumnMetadata column : columns) {
285
286 if (column != null) {
287 columnNames.add(column.getName());
288 }
289 }
290
291 return createInsertQuery(columnNames);
292 }
293
294 public String getDeleteQuery(String[] columnNames) {
295
296 String whereClause = null;
297 if (columnNames != null && columnNames.length > 0) {
298 StringBuilder whereClauseBuilder = new StringBuilder();
299 for (String columnName : columnNames) {
300 whereClauseBuilder
301 .append("AND ")
302 .append(tableAlias).append('.').append(columnName)
303 .append(" = ?");
304 }
305 whereClause = whereClauseBuilder.substring(4);
306 }
307
308 return getDeleteQuery(whereClause);
309 }
310
311 public String getDeleteQuery() {
312 return getDeleteQuery((String)null);
313 }
314
315 public String getDeleteQuery(String whereClauseContent) {
316 String result = String.format(QUERY_DELETE,
317 tableName.render().toUpperCase(),
318 tableAlias,
319 whereClauseContent == null ? "1=1" : whereClauseContent);
320 return result;
321 }
322
323
324
325 protected Set<String> initPrimaryKeys(DatabaseMetaData jdbcDbMeta) throws SQLException {
326
327 Set<String> result = Sets.newHashSet();
328 ResultSet rs = jdbcDbMeta.getPrimaryKeys(getCatalog(), getSchema(), getName().toUpperCase());
329 try {
330 while (rs.next()) {
331 result.add(rs.getString("COLUMN_NAME").toLowerCase());
332 }
333 rs.close();
334 return ImmutableSet.copyOf(result);
335 } finally {
336 if (rs != null) {
337 rs.close();
338 }
339 }
340 }
341
342
343 protected Map<String, SumarisColumnMetadata> initColumns(QualifiedTableName tableName, DatabaseMetaData jdbcDbMeta) throws SQLException {
344
345 Map<String, SumarisColumnMetadata> result = Maps.newLinkedHashMap();
346 ResultSet rs = jdbcDbMeta.getColumns(getCatalog(), getSchema(), getName().toUpperCase(), "%");
347
348 try {
349 while(rs.next()) {
350 String columnName = rs.getString("COLUMN_NAME").toLowerCase();
351 String defaultValue = SumarisConfiguration.getInstance().getColumnDefaultValue(getName(), columnName);
352
353
354 SumarisColumnMetadata columnMeta = new SumarisColumnMetadata(rs, defaultValue);
355 result.put(columnName.toLowerCase(), columnMeta);
356 }
357 }
358 finally {
359 if (rs != null) {
360 rs.close();
361 }
362 }
363 if (result.size() == 0) {
364 throw new RuntimeException("Unable to load columns metadata on table " + getName());
365 }
366
367 return result;
368 }
369
370 protected Set<String> initNotNull(Map<String, SumarisColumnMetadata> columns) {
371 Set<String> result = Sets.newHashSet();
372 for (String columnName : columns.keySet()) {
373 SumarisColumnMetadata column = columns.get(columnName);
374 if (!column.isNullable() && !pkNames.contains(columnName)) {
375 result.add(columnName);
376 }
377 }
378 return ImmutableSet.copyOf(result);
379 }
380
381 protected int[] createPkIndex() {
382
383 int[] result = new int[pkNames.size()];
384
385 int pkI = 0;
386 for (String pkName : pkNames) {
387 String pkColumnName = pkName.toLowerCase();
388
389 int i = 1;
390
391 int index = -1;
392 for (String columnName : getColumnNames()) {
393 if (pkColumnName.equals(columnName)) {
394 index = i;
395 } else {
396 i++;
397 }
398 }
399 result[pkI++] = index;
400 }
401
402 return result;
403 }
404
405
406
407
408
409
410 protected String createAllCountQuery() {
411 return String.format(QUERY_SELECT_COUNT_ALL, getName(), tableAlias);
412 }
413
414
415
416
417
418
419 protected String createSelectAllQuery() {
420 return String.format(QUERY_SELECT_ALL,
421 createSelectParams(tableAlias),
422 tableName.render().toUpperCase(),
423 tableAlias);
424 }
425
426 protected String createInsertQuery() {
427 return createInsertQuery(getColumnNames());
428 }
429
430 protected String createInsertQuery(Set<String> columnNames) {
431
432 StringBuilder queryParams = new StringBuilder();
433 StringBuilder valueParams = new StringBuilder();
434
435 for (String columnName : columnNames) {
436 queryParams.append(", ").append(columnName);
437 valueParams.append(", ?");
438 }
439
440 String result = String.format(QUERY_INSERT,
441 tableName.render().toUpperCase(),
442 queryParams.substring(2),
443 valueParams.substring(2));
444 return result;
445 }
446
447 protected String createUpdateQuery() {
448
449
450 if (CollectionUtils.isEmpty(getPkNames())) return null;
451
452 StringBuilder updateParams = new StringBuilder();
453 StringBuilder pkParams = new StringBuilder();
454
455 for (String columnName : getColumnNames()) {
456 updateParams.append(", ").append(columnName).append(" = ?");
457 }
458
459 for (String columnName : getPkNames()) {
460 pkParams.append("AND ").append(columnName).append(" = ?");
461 }
462
463 String result = String.format(QUERY_UPDATE,
464 getName(),
465 updateParams.substring(2),
466 pkParams.substring(4));
467 return result;
468 }
469
470
471
472
473
474
475
476 protected String createSelectParams(String tableAlias) {
477 return createSelectParams(getColumnNames(), tableAlias);
478 }
479
480
481
482
483
484
485
486
487 protected String createSelectParams(Collection<String> columnNames,
488 String tableAlias) {
489 Preconditions.checkArgument(
490 CollectionUtils.isNotEmpty(columnNames),
491 String.format("No column found for table: %s",
492 getName()));
493
494 StringBuilder queryParams = new StringBuilder();
495
496 for (String columnName : columnNames) {
497 queryParams.append(", ");
498 if (tableAlias != null) {
499 queryParams.append(tableAlias).append(".");
500 }
501 queryParams.append(columnName);
502 }
503
504 return queryParams.substring(2);
505 }
506
507
508
509
510
511
512
513 protected String initSequenceName(SumarisDatabaseMetadata dbMeta) {
514 final Set<String> availableSequences = dbMeta.getSequences();
515 final String sequenceSuffix = dbMeta.getSequenceSuffix();
516 final int maxSqlNameLength = dbMeta.getDialect().getMaxAliasLength();
517
518 final String tableName = getName().toLowerCase();
519 String sequenceName;
520
521
522 final int maxLength = maxSqlNameLength - sequenceSuffix.length();
523 if (maxLength > -0) {
524 sequenceName = (SumarisMetadataUtils.ensureMaximumNameLength(
525 tableName, maxLength) + sequenceSuffix).toLowerCase();
526 if (availableSequences.contains(sequenceName)) {
527 return sequenceName;
528 }
529 }
530
531
532 sequenceName = (tableName + sequenceSuffix).toLowerCase();
533 if (availableSequences.contains(sequenceName)) {
534 return sequenceName;
535 }
536
537
538 return null;
539 }
540
541
542
543
544
545
546
547 protected String createSequenceNextValQuery(Dialect dialect) {
548 if (StringUtils.isBlank(sequenceName)) {
549 return null;
550 }
551 return dialect.getSequenceNextValString(sequenceName);
552 }
553
554 protected String createSelectAllToUpdateQuery(SumarisDatabaseMetadata dbMeta) {
555 String query = String.format(QUERY_SELECT_ALL,
556 createSelectParams(tableAlias),
557 tableName.render().toUpperCase(),
558 tableAlias);
559
560
561 if (isWithUpdateDateColumn()) {
562
563 String updateDateColumn = dbMeta.getDefaultUpdateDateColumnName();
564
565 query += String.format(" WHERE (%s.%s IS NULL OR %s.%s > ?)",
566 tableAlias, updateDateColumn,
567 tableAlias, updateDateColumn);
568 }
569 return query;
570 }
571
572 protected String createCountDataToUpdateQuery(SumarisDatabaseMetadata dbMeta) {
573 String query = String.format(QUERY_SELECT_COUNT_ALL,
574 tableName.render().toUpperCase(),
575 tableAlias);
576
577
578 if (isWithUpdateDateColumn()) {
579
580 String updateDateColumn = dbMeta.getDefaultUpdateDateColumnName();
581
582 query += String.format(" WHERE (%s.%s IS NULL OR %s.%s > ?)",
583 tableAlias, updateDateColumn,
584 tableAlias, updateDateColumn);
585 }
586 return query;
587 }
588
589 }