1 package net.avcompris.commons3.dao;
2
3 import static com.google.common.base.Preconditions.checkArgument;
4 import static com.google.common.base.Preconditions.checkNotNull;
5 import static com.google.common.collect.Maps.newHashMap;
6 import static java.util.Locale.ENGLISH;
7 import static org.apache.commons.lang3.StringUtils.isAllLowerCase;
8 import static org.apache.commons.lang3.StringUtils.isAsciiPrintable;
9 import static org.apache.commons.lang3.StringUtils.join;
10
11 import java.sql.SQLException;
12 import java.util.ArrayList;
13 import java.util.List;
14 import java.util.Map;
15 import java.util.function.Consumer;
16
17 import javax.annotation.Nullable;
18
19 import org.apache.commons.lang3.NotImplementedException;
20 import org.apache.commons.lang3.mutable.MutableBoolean;
21 import org.apache.commons.lang3.tuple.Triple;
22
23 import com.google.common.collect.Iterables;
24 import com.google.common.collect.Lists;
25
26 import net.avcompris.commons3.dao.DbTable.Type;
27
28 public abstract class DbTablesUtils {
29
30 public static ColumnBuilder column(final String columnName) {
31
32 return new ColumnBuilder(columnName);
33 }
34
35 public interface Column {
36
37 String getColumnName();
38
39 Type getType();
40
41 @Nullable
42 Integer getSize();
43
44 boolean isNotNull();
45
46 boolean isSerial();
47
48 boolean isPrimaryKey();
49
50 boolean isUniqueIndex();
51
52 boolean isIndex();
53
54 @Nullable
55 String getRegexp();
56
57 @Nullable
58 DbTable getRefTable();
59
60 @Nullable
61 String getRefColumnName();
62
63 boolean getOnDeleteCascade();
64 }
65
66 private static final class ColumnImpl implements Column {
67
68 private final String columnName;
69 private final Type type;
70 @Nullable
71 private final Integer size;
72 private final boolean notNull;
73 private final boolean serial;
74 private final boolean primaryKey;
75 private final boolean unique;
76 private final boolean index;
77 @Nullable
78 private final String regexp;
79 @Nullable
80 private final DbTable refTable;
81 @Nullable
82 private final String refColumnName;
83 private final boolean onDeleteCascade;
84
85 public ColumnImpl(final ColumnBuilder builder) {
86
87 checkNotNull(builder, "builder");
88
89 this.columnName = builder.columnName;
90 this.type = checkNotNull(builder.type, "type");
91 this.size = builder.size;
92 this.notNull = builder.notNull;
93 this.serial = builder.serial;
94 this.primaryKey = builder.primaryKey;
95 this.index = builder.index;
96 this.unique = builder.unique;
97 this.regexp = builder.regexp;
98 this.refTable = builder.refTable;
99 this.refColumnName = builder.refColumnName;
100 this.onDeleteCascade = builder.onDeleteCascade;
101 }
102
103 @Override
104 public String getColumnName() {
105
106 return columnName;
107 }
108
109 @Override
110 public Type getType() {
111
112 return type;
113 }
114
115 @Override
116 @Nullable
117 public Integer getSize() {
118
119 return size;
120 }
121
122 @Override
123 public boolean isNotNull() {
124
125 return notNull;
126 }
127
128 @Override
129 public boolean isSerial() {
130
131 return serial;
132 }
133
134 @Override
135 public boolean isPrimaryKey() {
136
137 return primaryKey;
138 }
139
140 @Override
141 public boolean isUniqueIndex() {
142
143 return index && unique;
144 }
145
146 @Override
147 public boolean isIndex() {
148
149 return index;
150 }
151
152 @Override
153 @Nullable
154 public String getRegexp() {
155
156 return regexp;
157 }
158
159 @Override
160 @Nullable
161 public DbTable getRefTable() {
162
163 return refTable;
164 }
165
166 @Override
167 @Nullable
168 public String getRefColumnName() {
169
170 return refColumnName;
171 }
172
173 @Override
174 public boolean getOnDeleteCascade() {
175
176 return onDeleteCascade;
177 }
178 }
179
180 public static class ColumnBuilder {
181
182 private final String columnName;
183 private Type type;
184 private Integer size;
185 private String regexp;
186 private boolean serial;
187 private boolean primaryKey;
188 private boolean index;
189 private boolean unique;
190 private DbTable refTable;
191 private String refColumnName;
192 private boolean onDeleteCascade;
193 private boolean notNull;
194
195 private ColumnBuilder(final String columnName) {
196
197 this.columnName = checkNotNull(columnName, "columnName");
198
199 checkArgument(isAsciiPrintable(columnName), "columnName should be US-ASCII, but was: %s", columnName);
200
201 checkArgument(isAllLowerCase(columnName.replace("_", "")), "columnName should be lowercase, but was: %s",
202 columnName);
203 }
204
205 public Column build() {
206
207 return new ColumnImpl(this);
208 }
209
210 public ColumnBuilder type(final Type type) {
211
212 this.type = checkNotNull(type, "type");
213
214 return this;
215 }
216
217 public ColumnBuilder size(final int size) {
218
219 this.size = size;
220
221 return this;
222 }
223
224 public ColumnBuilder notNull() {
225
226 this.notNull = true;
227
228 return this;
229 }
230
231 public ColumnBuilder nullable() {
232
233 this.notNull = false;
234
235 return this;
236 }
237
238 public ColumnBuilder regexp(final String regexp) {
239
240 this.regexp = checkNotNull(regexp, "regexp");
241
242 return this;
243 }
244
245 public ColumnBuilder serial() {
246
247 this.serial = true;
248
249 return this;
250 }
251
252 public ColumnBuilder primaryKey() {
253
254 this.primaryKey = true;
255
256 return this;
257 }
258
259 public ColumnBuilder index() {
260
261 this.index = true;
262
263 return this;
264 }
265
266 public ColumnBuilder unique() {
267
268 this.index = true;
269 this.unique = true;
270
271 return this;
272 }
273
274 public ColumnBuilder refKey(final DbTable refTable, final String refColumnName) {
275
276 return refKey(refTable, refColumnName, false);
277 }
278
279 public ColumnBuilder refKey(final DbTable refTable, final String refColumnName, final boolean onDeleteCascade) {
280
281 this.refTable = checkNotNull(refTable, "refTable");
282 this.refColumnName = checkNotNull(refColumnName, "refColumnName");
283 this.onDeleteCascade = onDeleteCascade;
284
285 return this;
286 }
287 }
288
289 private static List<DbTable> getSubDbTables(final DbTable dbTable) {
290
291 checkNotNull(dbTable, "dbTable");
292
293 final List<DbTable> alikeDbTables = new ArrayList<>();
294
295 final DbTable[] enumConstants = dbTable.getClass().getEnumConstants();
296
297 if (enumConstants == null) {
298
299 alikeDbTables.add(dbTable);
300
301 } else {
302
303 for (final DbTable enumConstant : enumConstants) {
304
305 if (enumConstant == dbTable || enumConstant.name().startsWith(dbTable.name() + "_")) {
306
307 alikeDbTables.add(enumConstant);
308 }
309 }
310 }
311
312 return alikeDbTables;
313 }
314
315 public static String[] composeSQLDropCommands(final String dbTableNamePrefix, final DbTable dbTable)
316 throws SQLException {
317
318 final List<String> sqls = new ArrayList<>();
319
320 for (final DbTable dt : Lists.reverse(getSubDbTables(dbTable))) {
321
322 final String actualDbTableName = dt.getRuntimeDbTableNameWithPrefix(dbTableNamePrefix);
323
324 parseIndexes(dt, columns -> {
325
326
327
328
329
330 for (final Column column : columns) {
331
332 final String columnName = column.getColumnName();
333
334 final String indexName = actualDbTableName + "_" + columnName + "_idx";
335
336 sqls.add("DROP INDEX " + indexName);
337
338
339
340
341 }
342 });
343
344 sqls.add(composeDropTableSQL(dbTableNamePrefix, dt));
345 }
346
347 return Iterables.toArray(sqls, String.class);
348 }
349
350 private static String composeDropTableSQL(final String dbTableNamePrefix, final DbTable dbTable) {
351
352 final String runtimeDbTableName = dbTable.getRuntimeDbTableNameWithPrefix(dbTableNamePrefix);
353
354 return "DROP TABLE " + runtimeDbTableName + " CASCADE";
355 }
356
357 public static String[] composeSQLCreateCommands(final String dbTableNamePrefix, final DbTable dbTable)
358 throws SQLException {
359
360 final List<String> sqls = new ArrayList<>();
361
362 for (final DbTable dt : getSubDbTables(dbTable)) {
363
364 final String actualDbTableName = dt.getRuntimeDbTableNameWithPrefix(dbTableNamePrefix);
365
366 sqls.add(composeCreateTableSQL(dbTableNamePrefix, dt));
367
368 parseIndexes(dt, columns -> {
369
370
371
372
373
374 for (final Column column : columns) {
375
376 final String columnName = column.getColumnName();
377
378 final String indexName = actualDbTableName + "_" + columnName + "_idx";
379
380 sqls.add((column.isUniqueIndex() ? "CREATE UNIQUE INDEX " : "CREATE INDEX ")
381
382 + indexName + " ON " + actualDbTableName + " (" + columnName + ")");
383
384
385
386
387 }
388 });
389 }
390
391 return Iterables.toArray(sqls, String.class);
392 }
393
394 private static void parseIndexes(final DbTable table, final Consumer<Column[]> consumer) {
395
396 final List<Column> uniqueIndexes = new ArrayList<Column>();
397
398 for (final Column column : table.columns()) {
399
400 final String columnName = column.getColumnName();
401 final boolean primaryKey = column.isPrimaryKey();
402 final boolean uniqueIndex = column.isUniqueIndex();
403 final boolean index = column.isIndex();
404
405 if (columnName != null && index && !primaryKey) {
406
407 if (uniqueIndex) {
408
409 uniqueIndexes.add(column);
410
411 } else {
412
413 consumer.accept(new Column[] { column });
414 }
415 }
416 }
417
418 if (!uniqueIndexes.isEmpty()) {
419 consumer.accept(Iterables.toArray(uniqueIndexes, Column.class));
420 }
421 }
422
423 private static String composeCreateTableSQL(final String dbTableNamePrefix, final DbTable dbTable) {
424
425 final String runtimeDbTableName = dbTable.getRuntimeDbTableNameWithPrefix(dbTableNamePrefix);
426
427 final StringBuilder sb = new StringBuilder();
428
429 sb.append("CREATE TABLE " + runtimeDbTableName + " (");
430
431 final List<String> primaryKeys = new ArrayList<>();
432
433 boolean start = true;
434
435 for (final Column column : dbTable.columns()) {
436
437 if (start) {
438 start = false;
439 } else {
440 sb.append(", ");
441 }
442
443 final String columnName = column.getColumnName();
444
445 sb.append(columnName + " ");
446
447 switch (column.getType()) {
448
449 case VARCHAR:
450 sb.append("VARCHAR(" + column.getSize() + ")");
451 break;
452
453 case INTEGER:
454
455 if (column.isSerial()) {
456 sb.append("SERIAL");
457 } else {
458 sb.append("INTEGER");
459 }
460
461 break;
462
463 case LONG:
464 sb.append("BIGINT");
465 break;
466
467 case BOOLEAN:
468 sb.append("BOOLEAN");
469 break;
470
471 case TIMESTAMP_WITH_TIMEZONE:
472 sb.append("TIMESTAMPTZ");
473 break;
474
475 case BYTE_ARRAY:
476 sb.append("BYTEA");
477 break;
478
479 case TEXT:
480 sb.append("TEXT");
481 break;
482
483 default:
484 throw new NotImplementedException("Unknown type: " + column.getType());
485 }
486
487 if (column.isNotNull()) {
488
489 sb.append(" NOT NULL");
490
491 } else {
492
493 sb.append(" NULL");
494 }
495
496 if (column.isPrimaryKey()) {
497
498 primaryKeys.add(columnName);
499 }
500 }
501
502 if (!primaryKeys.isEmpty()) {
503
504 sb.append(", PRIMARY KEY (" + join(primaryKeys, ", ") + ")");
505 }
506
507 final Map<DbTable, Triple<List<String>, List<String>, MutableBoolean>> foreignColumnNames = newHashMap();
508
509 final List<DbTable> refTables = new ArrayList<>();
510
511 for (final Column column : dbTable.columns()) {
512
513 final DbTable refTable = column.getRefTable();
514
515 if (refTable != null) {
516
517 final Triple<List<String>, List<String>, MutableBoolean> columnNames;
518
519 if (foreignColumnNames.containsKey(refTable)) {
520
521 columnNames = foreignColumnNames.get(refTable);
522
523 } else {
524
525 columnNames = Triple.of(new ArrayList<>(), new ArrayList<>(), new MutableBoolean(false));
526
527 foreignColumnNames.put(refTable, columnNames);
528
529 refTables.add(refTable);
530 }
531
532 columnNames.getLeft().add(column.getColumnName());
533 columnNames.getMiddle().add(column.getRefColumnName());
534
535 if (column.getOnDeleteCascade()) {
536 columnNames.getRight().setTrue();
537 }
538 }
539 }
540
541 for (final DbTable refTable : refTables) {
542
543 final Triple<List<String>, List<String>, MutableBoolean> columnNames = foreignColumnNames.get(refTable);
544
545 final boolean onDeleteCascade = columnNames.getRight().booleanValue();
546
547 sb.append(", FOREIGN KEY (" + join(columnNames.getLeft(), ", ") + ") REFERENCES "
548 + refTable.getRuntimeDbTableNameWithPrefix(dbTableNamePrefix)
549 + " (" + join(columnNames.getMiddle(), ", ") + ")"
550 + (onDeleteCascade ? " ON DELETE CASCADE" : ""));
551
552 }
553
554 sb.append(")");
555
556 return sb.toString();
557 }
558
559 public static String composeActualDbTableName(final String tableNamePrefix, final DbTable dbTable) {
560
561 checkNotNull(tableNamePrefix, "tableNamePrefix");
562 checkNotNull(dbTable, "dbTable");
563
564 return (tableNamePrefix + dbTable.name()).toLowerCase(ENGLISH);
565 }
566 }