1 |
4944449a
|
Andreas M��ller
|
/**
|
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 |
da2aed69
|
Andreas M��ller
|
* Tested for: MySQL, H2, PostGres
|
19 |
7a3e3d56
|
Andreas M��ller
|
* Untested: SQL Server (currently not testable due to #4957)
|
20 |
|
|
*
|
21 |
73e6db00
|
Andreas M��ller
|
* Open issues:
|
22 |
|
|
* - validation that no duplicate values exist (which will throw an exception during invoke)
|
23 |
|
|
*
|
24 |
4944449a
|
Andreas M��ller
|
* @author a.mueller
|
25 |
53db84af
|
Andreas Müller
|
* @since 2015-05-21
|
26 |
4944449a
|
Andreas M��ller
|
*/
|
27 |
2668abef
|
Andreas Müller
|
public class MnTableRemover extends AuditedSchemaUpdaterStepBase {
|
28 |
4944449a
|
Andreas M��ller
|
|
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 |
99e01f35
|
Andreas Müller
|
protected List<ISchemaUpdaterStep> innerStepList = new ArrayList<>();
|
36 |
4944449a
|
Andreas M��ller
|
|
37 |
|
|
|
38 |
99e01f35
|
Andreas Müller
|
public static MnTableRemover NewInstance(List<ISchemaUpdaterStep> stepList, String stepName, String mnTableName,
|
39 |
4944449a
|
Andreas M��ller
|
String fkColumnName,
|
40 |
|
|
String mnParentFkColumnName,
|
41 |
|
|
String mnChildFkColumnName,
|
42 |
|
|
String parentTableName,
|
43 |
|
|
String childTableName,
|
44 |
|
|
boolean includeAudited){
|
45 |
|
|
|
46 |
99e01f35
|
Andreas Müller
|
MnTableRemover result = new MnTableRemover(stepList, stepName,
|
47 |
4944449a
|
Andreas M��ller
|
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 |
99e01f35
|
Andreas Müller
|
protected MnTableRemover(List<ISchemaUpdaterStep> stepList, String stepName, String mnTableName,
|
59 |
4944449a
|
Andreas M��ller
|
String fkColumnName,
|
60 |
|
|
String mnParentFkColumnName,
|
61 |
|
|
String mnChildFkColumnName,
|
62 |
|
|
String parentTableName,
|
63 |
|
|
String childTableName,
|
64 |
|
|
boolean includeAudited) {
|
65 |
99e01f35
|
Andreas Müller
|
super(stepList, stepName, mnTableName, includeAudited);
|
66 |
4944449a
|
Andreas M��ller
|
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 |
99e01f35
|
Andreas Müller
|
innerStepList,
|
86 |
4944449a
|
Andreas M��ller
|
"Create foreign key column to parent table to replace MN table",
|
87 |
|
|
childTableName,
|
88 |
|
|
fkColumnName,
|
89 |
|
|
includeAudTable,
|
90 |
|
|
notNull,
|
91 |
|
|
parentTableName);
|
92 |
|
|
|
93 |
|
|
//copy data to new column
|
94 |
dbdcbbba
|
Andreas M��ller
|
String stepName = "Copy data to new column";
|
95 |
4944449a
|
Andreas M��ller
|
String childTable = "@@" + childTableName + "@@ c";
|
96 |
dbdcbbba
|
Andreas M��ller
|
String childTableAud = "@@" + childTableName + "_AUD@@ c";
|
97 |
4944449a
|
Andreas M��ller
|
String mnTable = "@@" + mnTableName + "@@";
|
98 |
dbdcbbba
|
Andreas M��ller
|
String mnTableAud = "@@" + mnTableName + "_AUD@@";
|
99 |
4944449a
|
Andreas M��ller
|
String sql = ""
|
100 |
|
|
+ " UPDATE " + childTable
|
101 |
87f6f730
|
Andreas M��ller
|
+ " SET " + fkColumnName + " = "
|
102 |
|
|
+ " (SELECT " + mnParentFkColumnName
|
103 |
|
|
+ " FROM " + mnTable + " MN "
|
104 |
4944449a
|
Andreas M��ller
|
+ " WHERE MN." + mnChildFkColumnName + " = c.id) ";
|
105 |
dbdcbbba
|
Andreas M��ller
|
String sqlAudited = ""
|
106 |
|
|
+ " UPDATE " + childTableAud
|
107 |
|
|
+ " SET " + fkColumnName + " = "
|
108 |
|
|
+ " (SELECT " + mnParentFkColumnName
|
109 |
|
|
+ " FROM " + mnTableAud + " MN "
|
110 |
|
|
+ " WHERE MN." + mnChildFkColumnName + " = c.id AND c.REV = MN.REV) ";
|
111 |
99e01f35
|
Andreas Müller
|
SimpleSchemaUpdaterStep.NewExplicitAuditedInstance(innerStepList, stepName, sql, sqlAudited, 99);
|
112 |
4944449a
|
Andreas M��ller
|
|
113 |
|
|
//delete old table
|
114 |
65397e52
|
Andreas Müller
|
step = TableDropper.NewInstance(innerStepList, "Drop MN table", mnTableName, includeAudTable, true);
|
115 |
4944449a
|
Andreas M��ller
|
|
116 |
|
|
return;
|
117 |
|
|
}
|
118 |
|
|
|
119 |
|
|
@Override
|
120 |
ba35e2f8
|
Andreas Müller
|
protected void invokeOnTable(String tableName, ICdmDataSource datasource,
|
121 |
|
|
IProgressMonitor monitor, CaseType caseType, SchemaUpdateResult result) {
|
122 |
4944449a
|
Andreas M��ller
|
//we only do have inner steps here
|
123 |
ba35e2f8
|
Andreas Müller
|
return;
|
124 |
4944449a
|
Andreas M��ller
|
}
|
125 |
|
|
|
126 |
|
|
|
127 |
|
|
@Override
|
128 |
|
|
public List<ISchemaUpdaterStep> getInnerSteps() {
|
129 |
|
|
List<ISchemaUpdaterStep> result = new ArrayList<ISchemaUpdaterStep>
|
130 |
|
|
( this.innerStepList);
|
131 |
|
|
return result;
|
132 |
|
|
}
|
133 |
|
|
}
|
134 |
|
|
|