1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 package org.efaps.update.schema.datamodel;
22
23 import java.net.URL;
24 import java.sql.SQLException;
25 import java.sql.Statement;
26 import java.util.ArrayList;
27 import java.util.List;
28 import java.util.Map;
29 import java.util.Set;
30
31 import org.apache.commons.lang3.builder.ToStringBuilder;
32 import org.efaps.ci.CIAdminDataModel;
33 import org.efaps.db.Context;
34 import org.efaps.db.Insert;
35 import org.efaps.db.Instance;
36 import org.efaps.db.InstanceQuery;
37 import org.efaps.db.QueryBuilder;
38 import org.efaps.db.databases.AbstractDatabase;
39 import org.efaps.db.databases.information.ColumnInformation;
40 import org.efaps.db.databases.information.ForeignKeyInformation;
41 import org.efaps.db.databases.information.TableInformation;
42 import org.efaps.db.databases.information.UniqueKeyInformation;
43 import org.efaps.db.transaction.ConnectionResource;
44 import org.efaps.update.AbstractUpdate;
45 import org.efaps.update.UpdateLifecycle;
46 import org.efaps.update.util.InstallationException;
47 import org.efaps.util.EFapsException;
48 import org.slf4j.Logger;
49 import org.slf4j.LoggerFactory;
50
51 import edu.umd.cs.findbugs.annotations.SuppressFBWarnings;
52
53
54
55
56
57
58
59
60 public class SQLTableUpdate
61 extends AbstractUpdate
62 {
63
64
65
66 private static final Logger LOG = LoggerFactory.getLogger(SQLTableUpdate.class);
67
68
69
70
71
72
73
74
75 private boolean created;
76
77
78
79
80
81
82
83 public SQLTableUpdate(final URL _url)
84 {
85 super(_url, "Admin_DataModel_SQLTable");
86 }
87
88
89
90
91
92
93
94 @Override
95 protected AbstractDefinition newDefinition()
96 {
97 return new Definition();
98 }
99
100
101
102
103 private static final class Column
104 {
105
106
107
108 private final String name;
109
110
111
112
113 private final AbstractDatabase.ColumnType type;
114
115
116
117
118 private final int length;
119
120
121
122
123 private final boolean isNotNull;
124
125
126
127
128 private final int scale;
129
130
131
132
133
134
135
136
137 private Column(final String _name,
138 final AbstractDatabase.ColumnType _type,
139 final int _length,
140 final int _scale,
141 final boolean _notNull)
142 {
143 this.name = _name;
144 this.type = _type;
145 this.length = _length;
146 this.scale = _scale;
147 this.isNotNull = _notNull;
148 }
149
150
151
152
153
154
155
156 @Override
157 public String toString()
158 {
159 return new ToStringBuilder(this)
160 .append("name", this.name)
161 .append("type", this.type)
162 .append("isNotNull", this.isNotNull)
163 .toString();
164 }
165 }
166
167
168
169
170 private static final class UniqueKey
171 {
172
173
174
175 private final String name;
176
177
178
179
180 private final String columns;
181
182
183
184
185
186
187
188 private UniqueKey(final String _name,
189 final String _columns)
190 {
191 String nameTmp = null;
192 try {
193 nameTmp = Context.getDbType().getConstrainName(_name);
194 } catch (final EFapsException e) {
195 SQLTableUpdate.LOG.error("UniqueKey could not be retrieved. Name {}, columns: {} ", _name, _columns);
196 }
197 this.name = nameTmp;
198 this.columns = _columns;
199 }
200
201
202
203
204
205
206
207 @Override
208 public String toString()
209 {
210 return new ToStringBuilder(this)
211 .append("name", this.name)
212 .append("columns", this.columns)
213 .toString();
214 }
215 }
216
217
218
219
220 private static final class ForeignKey
221 {
222
223
224
225 private final String name;
226
227
228
229
230 private final String key;
231
232
233
234
235 private final String reference;
236
237
238
239
240 private final boolean cascade;
241
242
243
244
245
246
247
248
249 private ForeignKey(final String _name,
250 final String _key,
251 final String _reference,
252 final boolean _cascade)
253 {
254 String nameTmp = null;
255 try {
256 nameTmp = Context.getDbType().getConstrainName(_name);
257 } catch (final EFapsException e) {
258 SQLTableUpdate.LOG.error("ForeignKey could not be retrieved. Name {}, key: {} ", _name, _key);
259 }
260 this.name = nameTmp;
261 this.key = _key;
262 this.reference = _reference;
263 this.cascade = _cascade;
264 }
265
266
267
268
269
270
271
272 @Override
273 public String toString()
274 {
275 return new ToStringBuilder(this)
276 .append("name", this.name)
277 .append("key", this.key)
278 .append("reference", this.reference)
279 .toString();
280 }
281 }
282
283
284
285
286 private static final class CheckKey
287 {
288
289
290
291 private final String name;
292
293
294
295
296 private final String condition;
297
298
299
300
301
302
303
304 private CheckKey(final String _name,
305 final String _condition)
306 {
307 this.name = _name;
308 this.condition = _condition;
309 }
310
311
312
313
314
315
316
317 @Override
318 public String toString()
319 {
320 return new ToStringBuilder(this)
321 .append("name", this.name)
322 .append("condition", this.condition)
323 .toString();
324 }
325 }
326
327
328
329
330 protected class Definition
331 extends AbstractDefinition
332 {
333
334
335
336 private String parentSQLTableName;
337
338
339
340
341 private String parent;
342
343
344
345
346
347
348
349 private final List<String> sqls = new ArrayList<String>();
350
351
352
353
354 private final List<SQLTableUpdate.Column> columns = new ArrayList<SQLTableUpdate.Column>();
355
356
357
358
359 private final List<SQLTableUpdate.UniqueKey> uniqueKeys = new ArrayList<SQLTableUpdate.UniqueKey>();
360
361
362
363
364 private final List<SQLTableUpdate.ForeignKey> foreignKeys = new ArrayList<SQLTableUpdate.ForeignKey>();
365
366
367
368
369 private final List<SQLTableUpdate.CheckKey> checkKeys = new ArrayList<SQLTableUpdate.CheckKey>();
370
371
372
373
374 private boolean view;
375
376
377
378
379 @Override
380 protected void readXML(final List<String> _tags,
381 final Map<String, String> _attributes,
382 final String _text)
383 throws EFapsException
384 {
385 final String value = _tags.get(0);
386 if ("database".equals(value)) {
387 if (_tags.size() > 1) {
388 final String subValue = _tags.get(1);
389 if ("check".equals(subValue)) {
390 this.checkKeys.add(new CheckKey(_attributes.get("name"),
391 _attributes.get("condition")));
392 } else if ("column".equals(subValue)) {
393 final String lengthStr = _attributes.get("length");
394 final String scaleStr = _attributes.get("scale");
395 final int length = (lengthStr != null)
396 ? Integer.parseInt(lengthStr)
397 : 0;
398 final int scale = (scaleStr != null)
399 ? Integer.parseInt(scaleStr)
400 : 0;
401 this.columns.add(new Column(_attributes.get("name"),
402 Enum.valueOf(AbstractDatabase.ColumnType.class,
403 _attributes.get("type")),
404 length,
405 scale,
406 "true".equals(_attributes.get("not-null"))));
407 } else if ("foreign".equals(subValue)) {
408 this.foreignKeys.add(new ForeignKey(_attributes.get("name"),
409 _attributes.get("key"),
410 _attributes.get("reference"),
411 "true".equals(_attributes.get("cascade"))));
412 } else if ("parent-table".equals(subValue)) {
413 this.parentSQLTableName = Context.getDbType().getTableName(_text);
414 } else if ("sql".equals(subValue)) {
415 this.sqls.add(_text);
416 } else if ("table-name".equals(subValue)) {
417 addValue("SQLTable", Context.getDbType().getTableName(_text));
418 addValue("SQLColumnID", "ID");
419 } else if ("view-name".equals(subValue)) {
420 addValue("SQLTable", Context.getDbType().getTableName(_text));
421 addValue("SQLColumnID", "ID");
422 this.view = true;
423 } else if ("unique".equals(subValue)) {
424 this.uniqueKeys.add(new UniqueKey(_attributes.get("name"),
425 _attributes.get("columns")));
426 }
427 }
428 } else if ("parent".equals(value)) {
429 if ((_text != null) && !"".equals(_text)) {
430 this.parent = _text;
431 }
432 } else if ("typeid-column".equals(value)) {
433 addValue("SQLColumnType", _text);
434 } else {
435 super.readXML(_tags, _attributes, _text);
436 }
437 }
438
439
440
441
442
443
444
445
446
447
448 @Override
449 protected void createInDB(final Insert _insert)
450 throws InstallationException
451 {
452 try {
453 _insert.add("SQLTable", getValue("SQLTable"));
454 } catch (final EFapsException e) {
455 throw new InstallationException("Could not add SQLTable attribute", e);
456 }
457 try {
458 _insert.add("SQLColumnID", getValue("SQLColumnID"));
459 } catch (final EFapsException e) {
460 throw new InstallationException("Could not add SQLColumnID attribute", e);
461 }
462 super.createInDB(_insert);
463 }
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493 @Override
494 public void updateInDB(final UpdateLifecycle _step,
495 final Set<Link> _allLinkTypes)
496 throws InstallationException
497 {
498 try {
499 if (_step == UpdateLifecycle.SQL_CREATE_TABLE) {
500 if (!this.view) {
501 createSQLTable();
502 }
503 super.updateInDB(_step, _allLinkTypes);
504 } else if (_step == UpdateLifecycle.SQL_UPDATE_ID && !this.view) {
505 updateColIdSQLTable();
506 super.updateInDB(_step, _allLinkTypes);
507 } else if (_step == UpdateLifecycle.SQL_UPDATE_TABLE && !this.view) {
508 updateSQLTable();
509 super.updateInDB(_step, _allLinkTypes);
510 } else if (_step == UpdateLifecycle.SQL_RUN_SCRIPT) {
511 executeSQLs();
512 super.updateInDB(_step, _allLinkTypes);
513 } else if (_step == UpdateLifecycle.EFAPS_UPDATE) {
514 if (getValue("Name") != null) {
515
516 if (this.parent != null) {
517 final QueryBuilder queryBldr = new QueryBuilder(CIAdminDataModel.SQLTable);
518 queryBldr.addWhereAttrEqValue(CIAdminDataModel.SQLTable.Name, this.parent);
519 final InstanceQuery query = queryBldr.getQuery();
520 query.executeWithoutAccessCheck();
521 if (query.next()) {
522 final Instance instance = query.getCurrentValue();
523 addValue(CIAdminDataModel.SQLTable.DMTableMain.name, "" + instance.getId());
524 }
525 }
526 super.updateInDB(_step, _allLinkTypes);
527 }
528 } else {
529 super.updateInDB(_step, _allLinkTypes);
530 }
531 } catch (final EFapsException e) {
532 throw new InstallationException(" SQLTable can not be updated", e);
533 }
534 }
535
536 @Override
537 protected void removeObsoleteEvents(final Instance _instance,
538 final List<Instance> _eventInstList)
539 throws EFapsException
540 {
541
542 }
543
544
545
546
547
548
549
550
551 @SuppressFBWarnings(value = { "SQL_NONCONSTANT_STRING_PASSED_TO_EXECUTE" },
552 justification = "The script cannot be made static")
553 protected void executeSQLs()
554 throws InstallationException
555 {
556 if (!this.sqls.isEmpty()) {
557 if (SQLTableUpdate.LOG.isInfoEnabled()) {
558 SQLTableUpdate.LOG.info(" Execute Script for DB SQL '" + getValue("SQLTable") + "'");
559 }
560 ConnectionResource con = null;
561 try {
562 final Context context = Context.getThreadContext();
563 con = context.getConnectionResource();
564 if (this.view) {
565 final String tableName = getValue("SQLTable");
566 if (Context.getDbType().existsView(con.getConnection(), tableName)) {
567 Context.getDbType().deleteView(con.getConnection(), tableName);
568 }
569 }
570
571 final Statement stmt = con.getConnection().createStatement();
572 for (final String sql : this.sqls) {
573 if (SQLTableUpdate.LOG.isDebugEnabled()) {
574 SQLTableUpdate.LOG.debug(" ..SQL> " + sql);
575 }
576 stmt.execute(sql);
577 }
578 stmt.close();
579 con.commit();
580 } catch (final EFapsException e) {
581 if (con != null) {
582 try {
583 con.abort();
584 } catch (final EFapsException e1) {
585 throw new InstallationException("SQLTable can not be updated", e1);
586 }
587 }
588 throw new InstallationException("SQLTable can not be updated", e);
589 } catch (final SQLException e) {
590 throw new InstallationException("SQLTable can not be updated", e);
591 }
592 }
593 }
594
595
596
597
598
599
600
601 protected void createSQLTable()
602 throws EFapsException
603 {
604 final Context context = Context.getThreadContext();
605 ConnectionResource con = null;
606 final String tableName = getValue("SQLTable");
607 try {
608 con = context.getConnectionResource();
609
610 if (!Context.getDbType().existsTable(con.getConnection(), tableName)
611 && !Context.getDbType().existsView(con.getConnection(), tableName)) {
612
613 SQLTableUpdate.LOG.info(" Create DB SQL Table '{}' for '{}'", tableName, getValue("Name"));
614
615 Context.getDbType().createTable(con.getConnection(), tableName);
616
617 SQLTableUpdate.this.created = true;
618 }
619 con.commit();
620
621 } catch (final SQLException e) {
622 SQLTableUpdate.LOG.error("SQLTableUpdate.createSQLTable.EFapsException", e);
623 throw new EFapsException("SQLTableUpdate.createSQLTable.EFapsException", e);
624 } finally {
625 if (con != null && con.isOpened()) {
626 con.abort();
627 }
628 }
629 }
630
631
632
633
634
635
636
637
638
639
640
641 protected void updateColIdSQLTable()
642 throws EFapsException, InstallationException
643 {
644 if (SQLTableUpdate.this.created) {
645 SQLTableUpdate.this.created = false;
646
647 final Context context = Context.getThreadContext();
648 ConnectionResource con = null;
649 final String tableName = getValue("SQLTable");
650 if (SQLTableUpdate.LOG.isInfoEnabled()) {
651 if (this.parentSQLTableName != null) {
652 SQLTableUpdate.LOG.info(" Define ID column for SQL Table '" + tableName + "' "
653 + "(parent '" + this.parentSQLTableName + "')");
654 } else {
655 SQLTableUpdate.LOG.info(" Define ID column for SQL Table '" + tableName + "'");
656 }
657 }
658 try {
659 con = context.getConnectionResource();
660
661 if (this.parentSQLTableName != null) {
662 Context.getDbType().defineTableParent(con.getConnection(), tableName, this.parentSQLTableName);
663 } else {
664 Context.getDbType().defineTableAutoIncrement(con.getConnection(), tableName);
665 }
666 con.commit();
667 } catch (final SQLException e) {
668 SQLTableUpdate.LOG.error("SQLTableUpdate.updateSQLTable.EFapsException", e);
669 throw new EFapsException(getClass(), "updateSQLTable.Throwable", e);
670 } finally {
671 if (con != null && con.isOpened()) {
672 con.abort();
673 }
674 }
675 }
676 }
677
678
679
680
681
682
683
684 protected void updateSQLTable()
685 throws InstallationException
686 {
687 ConnectionResource con = null;
688 final String tableName = getValue("SQLTable");
689 SQLTableUpdate.LOG.info(" Update DB SQL Table '{}'", tableName);
690 try {
691 con = Context.getThreadContext().getConnectionResource();
692 final TableInformation tableInfo = Context.getDbType().getRealTableInformation(con.getConnection(),
693 tableName);
694
695 for (final Column column : this.columns) {
696 final ColumnInformation colInfo = tableInfo.getColInfo(column.name);
697 if (colInfo != null) {
698 SQLTableUpdate.LOG.debug("column '{}' already defined in table '{}'", column.name, tableName);
699
700 if (colInfo.isNullable() == column.isNotNull) {
701 Context.getDbType().updateColumnIsNotNull(con.getConnection(), tableName, column.name,
702 column.isNotNull);
703 }
704
705 if (column.length > 0
706 && (colInfo.getSize() < column.length && colInfo.getScale() <= column.scale)) {
707 try {
708 Context.getDbType().updateColumn(con.getConnection(), tableName, column.name,
709 column.type, column.length, column.scale);
710 } catch (final SQLException e) {
711 SQLTableUpdate.LOG
712 .warn("Catched SQL Exception while trying to update Table: '{}', Column: '{}'",
713 tableName, column.name);
714 }
715 }
716 } else {
717 Context.getDbType().addTableColumn(con.getConnection(), tableName,
718 column.name, column.type, null, column.length, column.scale);
719 if (column.isNotNull) {
720 Context.getDbType().updateColumnIsNotNull(con.getConnection(), tableName, column.name,
721 column.isNotNull);
722 }
723 }
724 }
725
726
727 for (final UniqueKey uniqueKey : this.uniqueKeys) {
728 final UniqueKeyInformation ukInfo = tableInfo.getUKInfo(uniqueKey.name);
729 if (ukInfo != null) {
730 if (SQLTableUpdate.LOG.isDebugEnabled()) {
731 SQLTableUpdate.LOG.debug("unique key '" + uniqueKey.name + "' already defined in "
732 + "table '" + tableName + "'");
733 }
734 } else {
735
736 final UniqueKeyInformation ukInfo2 = tableInfo.getUKInfoByColNames(uniqueKey.columns);
737 if (ukInfo2 != null) {
738 SQLTableUpdate.LOG.error("unique key for columns " + uniqueKey.columns + " exists");
739 } else {
740 Context.getDbType().addUniqueKey(con.getConnection(), tableName,
741 uniqueKey.name, uniqueKey.columns);
742 }
743 }
744 }
745
746
747 for (final ForeignKey foreignKey : this.foreignKeys) {
748 final ForeignKeyInformation fkInfo = tableInfo.getFKInfo(foreignKey.name);
749 if (fkInfo != null) {
750 if (SQLTableUpdate.LOG.isDebugEnabled()) {
751 SQLTableUpdate.LOG.debug("foreign key '" + foreignKey.name + "' already defined in "
752 + "table '" + tableName + "'");
753 }
754 } else {
755 Context.getDbType().addForeignKey(con.getConnection(), tableName,
756 foreignKey.name, foreignKey.key, foreignKey.reference,
757 foreignKey.cascade);
758 }
759 }
760
761 for (final CheckKey checkKey : this.checkKeys) {
762 Context.getDbType().addCheckKey(con.getConnection(), tableName,
763 checkKey.name, checkKey.condition);
764 }
765 con.commit();
766 con = null;
767 } catch (final EFapsException e) {
768 throw new InstallationException("update of the SQL table failed", e);
769 } catch (final SQLException e) {
770 throw new InstallationException("update of the SQL table failed", e);
771 } finally {
772 if (con != null) {
773 try {
774 con.abort();
775 } catch (final EFapsException e) {
776 throw new InstallationException("Abort failed", e);
777 }
778 }
779 }
780 }
781 }
782 }