View Javadoc
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 				// if (columns.length == 1) {
327 
328 				// final Column column = columns[0];
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 //				} else {
339 
340 //					throw new NotImplementedException("columns.length: " + columns.length);
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 //				if (columns.length == 1) {
371 
372 //					final Column column = columns[0];
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 //				} else {
385 
386 //					throw new NotImplementedException("columns.length: " + columns.length);
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 }