1
|
/**
|
2
|
*
|
3
|
*/
|
4
|
package eu.etaxonomy.cdm.database.update;
|
5
|
|
6
|
import java.util.ArrayList;
|
7
|
import java.util.List;
|
8
|
|
9
|
import eu.etaxonomy.cdm.common.monitor.IProgressMonitor;
|
10
|
import eu.etaxonomy.cdm.database.ICdmDataSource;
|
11
|
|
12
|
/**
|
13
|
* If we have a OneToMany relationship which holds data which is not reusable or only reusable
|
14
|
* within a certain parent class we may not want to have an MN table for handling the relationship
|
15
|
* but a direct link (foreign key) from the child table to the parent table.
|
16
|
* This class removes MN tables and replaces them by the direct link (if possible)
|
17
|
*
|
18
|
* Tested for: MySQL, H2, PostGres
|
19
|
* Untested: SQL Server (currently not testable due to #4957)
|
20
|
*
|
21
|
* Open issues:
|
22
|
* - validation that no duplicate values exist (which will throw an exception during invoke)
|
23
|
*
|
24
|
* @author a.mueller
|
25
|
\* @since 2015-05-21
|
26
|
*/
|
27
|
public class MnTableRemover extends AuditedSchemaUpdaterStepBase {
|
28
|
|
29
|
private final String fkColumnName; //new column in child table which points to the parent table
|
30
|
private final String mnParentFkColumnName; //column in MN table which points to the parent table
|
31
|
private final String mnChildFkColumnName; //column in MN table which points to the child table
|
32
|
private final String parentTableName;
|
33
|
private final String childTableName;
|
34
|
|
35
|
protected List<ISchemaUpdaterStep> innerStepList = new ArrayList<ISchemaUpdaterStep>();
|
36
|
|
37
|
|
38
|
public static MnTableRemover NewInstance(String stepName, String mnTableName,
|
39
|
String fkColumnName,
|
40
|
String mnParentFkColumnName,
|
41
|
String mnChildFkColumnName,
|
42
|
String parentTableName,
|
43
|
String childTableName,
|
44
|
boolean includeAudited){
|
45
|
|
46
|
MnTableRemover result = new MnTableRemover(stepName,
|
47
|
mnTableName, fkColumnName, mnParentFkColumnName,
|
48
|
mnChildFkColumnName, parentTableName, childTableName, includeAudited);
|
49
|
return result;
|
50
|
}
|
51
|
|
52
|
// public static MnTableRemover NewMnInstance(String stepName, String firstTableName, String firstTableAlias, String secondTableName, String secondTableAlias, boolean includeAudTable, boolean hasSortIndex, boolean secondTableInKey){
|
53
|
// MnTableRemover result = new MnTableRemover(stepName, firstTableName, firstTableAlias, secondTableName, secondTableAlias, new String[]{}, new String[]{}, null, null, includeAudTable, hasSortIndex, secondTableInKey, false, false, false);
|
54
|
// return result;
|
55
|
// }
|
56
|
|
57
|
|
58
|
protected MnTableRemover(String stepName, String mnTableName,
|
59
|
String fkColumnName,
|
60
|
String mnParentFkColumnName,
|
61
|
String mnChildFkColumnName,
|
62
|
String parentTableName,
|
63
|
String childTableName,
|
64
|
boolean includeAudited) {
|
65
|
super(stepName, mnTableName, includeAudited);
|
66
|
this.fkColumnName = fkColumnName;
|
67
|
this.mnParentFkColumnName = mnParentFkColumnName;
|
68
|
this.mnChildFkColumnName = mnChildFkColumnName;
|
69
|
this.parentTableName = parentTableName;
|
70
|
this.childTableName = childTableName;
|
71
|
|
72
|
makeSteps();
|
73
|
}
|
74
|
|
75
|
|
76
|
private void makeSteps() {
|
77
|
String mnTableName = tableName;
|
78
|
|
79
|
//TODO validate
|
80
|
//mn child column must be unique
|
81
|
|
82
|
//Create new column
|
83
|
boolean notNull = false; //??
|
84
|
ISchemaUpdaterStep step = ColumnAdder.NewIntegerInstance(
|
85
|
"Create foreign key column to parent table to replace MN table",
|
86
|
childTableName,
|
87
|
fkColumnName,
|
88
|
includeAudTable,
|
89
|
notNull,
|
90
|
parentTableName);
|
91
|
innerStepList.add(step);
|
92
|
|
93
|
|
94
|
//copy data to new column
|
95
|
String stepName = "Copy data to new column";
|
96
|
String childTable = "@@" + childTableName + "@@ c";
|
97
|
String childTableAud = "@@" + childTableName + "_AUD@@ c";
|
98
|
String mnTable = "@@" + mnTableName + "@@";
|
99
|
String mnTableAud = "@@" + mnTableName + "_AUD@@";
|
100
|
String sql = ""
|
101
|
+ " UPDATE " + childTable
|
102
|
+ " SET " + fkColumnName + " = "
|
103
|
+ " (SELECT " + mnParentFkColumnName
|
104
|
+ " FROM " + mnTable + " MN "
|
105
|
+ " WHERE MN." + mnChildFkColumnName + " = c.id) ";
|
106
|
String sqlAudited = ""
|
107
|
+ " UPDATE " + childTableAud
|
108
|
+ " SET " + fkColumnName + " = "
|
109
|
+ " (SELECT " + mnParentFkColumnName
|
110
|
+ " FROM " + mnTableAud + " MN "
|
111
|
+ " WHERE MN." + mnChildFkColumnName + " = c.id AND c.REV = MN.REV) ";
|
112
|
SimpleSchemaUpdaterStep dataUpdateStep = SimpleSchemaUpdaterStep.NewExplicitAuditedInstance(stepName, sql, sqlAudited, 99);
|
113
|
innerStepList.add(dataUpdateStep);
|
114
|
|
115
|
//delete old table
|
116
|
step = TableDroper.NewInstance("Drop MN table", mnTableName, includeAudTable, true);
|
117
|
innerStepList.add(step);
|
118
|
|
119
|
return;
|
120
|
}
|
121
|
|
122
|
@Override
|
123
|
protected void invokeOnTable(String tableName, ICdmDataSource datasource,
|
124
|
IProgressMonitor monitor, CaseType caseType, SchemaUpdateResult result) {
|
125
|
//we only do have inner steps here
|
126
|
return;
|
127
|
}
|
128
|
|
129
|
|
130
|
@Override
|
131
|
public List<ISchemaUpdaterStep> getInnerSteps() {
|
132
|
List<ISchemaUpdaterStep> result = new ArrayList<ISchemaUpdaterStep>
|
133
|
( this.innerStepList);
|
134
|
return result;
|
135
|
}
|
136
|
}
|
137
|
|