1 package liquibase.change.core;
2
3 import liquibase.change.AbstractChange;
4 import liquibase.change.Change;
5 import liquibase.change.ChangeMetaData;
6 import liquibase.database.Database;
7 import liquibase.database.core.DB2Database;
8 import liquibase.database.core.InformixDatabase;
9 import liquibase.database.core.MSSQLDatabase;
10 import liquibase.database.core.OracleDatabase;
11 import liquibase.database.core.SybaseASADatabase;
12 import liquibase.statement.SqlStatement;
13 import liquibase.statement.core.RawSqlStatement;
14 import liquibase.statement.core.ReorganizeTableStatement;
15
16 import java.util.ArrayList;
17 import java.util.Arrays;
18 import java.util.List;
19
20
21
22
23
24 public class AddLookupTableChange extends AbstractChange {
25
26 private String existingTableSchemaName;
27 private String existingTableName;
28 private String existingColumnName;
29
30 private String newTableSchemaName;
31 private String newTableName;
32 private String newColumnName;
33 private String newColumnDataType;
34 private String constraintName;
35
36 public AddLookupTableChange() {
37 super("addLookupTable", "Add Lookup Table", ChangeMetaData.PRIORITY_DEFAULT);
38 }
39
40 public String getExistingTableSchemaName() {
41 return existingTableSchemaName;
42 }
43
44 public void setExistingTableSchemaName(String existingTableSchemaName) {
45 this.existingTableSchemaName = existingTableSchemaName;
46 }
47
48 public String getExistingTableName() {
49 return existingTableName;
50 }
51
52 public void setExistingTableName(String existingTableName) {
53 this.existingTableName = existingTableName;
54 }
55
56 public String getExistingColumnName() {
57 return existingColumnName;
58 }
59
60 public void setExistingColumnName(String existingColumnName) {
61 this.existingColumnName = existingColumnName;
62 }
63
64 public String getNewTableSchemaName() {
65 return newTableSchemaName;
66 }
67
68 public void setNewTableSchemaName(String newTableSchemaName) {
69 this.newTableSchemaName = newTableSchemaName;
70 }
71
72 public String getNewTableName() {
73 return newTableName;
74 }
75
76 public void setNewTableName(String newTableName) {
77 this.newTableName = newTableName;
78 }
79
80 public String getNewColumnName() {
81 return newColumnName;
82 }
83
84 public void setNewColumnName(String newColumnName) {
85 this.newColumnName = newColumnName;
86 }
87
88 public String getNewColumnDataType() {
89 return newColumnDataType;
90 }
91
92 public void setNewColumnDataType(String newColumnDataType) {
93 this.newColumnDataType = newColumnDataType;
94 }
95
96 public String getConstraintName() {
97 return constraintName;
98 }
99
100 public String getFinalConstraintName() {
101 if (constraintName == null) {
102 return ("FK_" + getExistingTableName() + "_" + getNewTableName()).toUpperCase();
103 } else {
104 return constraintName;
105 }
106 }
107
108 public void setConstraintName(String constraintName) {
109 this.constraintName = constraintName;
110 }
111
112 @Override
113 protected Change[] createInverses() {
114 DropForeignKeyConstraintChange dropFK = new DropForeignKeyConstraintChange();
115 dropFK.setBaseTableSchemaName(getExistingTableSchemaName());
116 dropFK.setBaseTableName(getExistingTableName());
117 dropFK.setConstraintName(getFinalConstraintName());
118
119 DropTableChange dropTable = new DropTableChange();
120 dropTable.setSchemaName(getNewTableSchemaName());
121 dropTable.setTableName(getNewTableName());
122
123 return new Change[] { dropFK, dropTable, };
124 }
125
126 public SqlStatement[] generateStatements(Database database) {
127 List<SqlStatement> statements = new ArrayList<SqlStatement>();
128
129 String newTableSchemaName = getNewTableSchemaName() == null ? database.getDefaultSchemaName()
130 : getNewTableSchemaName();
131 String existingTableSchemaName = getExistingTableSchemaName() == null ? database.getDefaultSchemaName()
132 : getExistingTableSchemaName();
133
134 SqlStatement[] createTablesSQL = { new RawSqlStatement("CREATE TABLE "
135 + database.escapeTableName(newTableSchemaName, getNewTableName()) + " AS SELECT DISTINCT "
136 + getExistingColumnName() + " AS " + getNewColumnName() + " FROM "
137 + database.escapeTableName(existingTableSchemaName, getExistingTableName()) + " WHERE "
138 + getExistingColumnName() + " IS NOT NULL") };
139 if (database instanceof MSSQLDatabase) {
140 createTablesSQL = new SqlStatement[] { new RawSqlStatement("SELECT DISTINCT " + getExistingColumnName()
141 + " AS " + getNewColumnName() + " INTO "
142 + database.escapeTableName(newTableSchemaName, getNewTableName()) + " FROM "
143 + database.escapeTableName(existingTableSchemaName, getExistingTableName()) + " WHERE "
144 + getExistingColumnName() + " IS NOT NULL"), };
145 } else if (database instanceof SybaseASADatabase) {
146 createTablesSQL = new SqlStatement[] { new RawSqlStatement("SELECT DISTINCT " + getExistingColumnName()
147 + " AS " + getNewColumnName() + " INTO "
148 + database.escapeTableName(newTableSchemaName, getNewTableName()) + " FROM "
149 + database.escapeTableName(existingTableSchemaName, getExistingTableName()) + " WHERE "
150 + getExistingColumnName() + " IS NOT NULL"), };
151 } else if (database instanceof DB2Database) {
152 createTablesSQL = new SqlStatement[] {
153 new RawSqlStatement("CREATE TABLE "
154 + database.escapeTableName(newTableSchemaName, getNewTableName()) + " AS (SELECT "
155 + getExistingColumnName() + " AS " + getNewColumnName() + " FROM "
156 + database.escapeTableName(existingTableSchemaName, getExistingTableName())
157 + ") WITH NO DATA"),
158 new RawSqlStatement("INSERT INTO "
159 + database.escapeTableName(newTableSchemaName, getNewTableName()) + " SELECT DISTINCT "
160 + getExistingColumnName() + " FROM "
161 + database.escapeTableName(existingTableSchemaName, getExistingTableName()) + " WHERE "
162 + getExistingColumnName() + " IS NOT NULL"), };
163 } else if (database instanceof InformixDatabase) {
164 createTablesSQL = new SqlStatement[] {
165 new RawSqlStatement("CREATE TABLE "
166 + database.escapeTableName(newTableSchemaName, getNewTableName()) + " ( "
167 + getNewColumnName() + " " + getNewColumnDataType() + " )"),
168 new RawSqlStatement("INSERT INTO "
169 + database.escapeTableName(newTableSchemaName, getNewTableName()) + " ( "
170 + getNewColumnName() + " ) SELECT DISTINCT " + getExistingColumnName() + " FROM "
171 + database.escapeTableName(existingTableSchemaName, getExistingTableName()) + " WHERE "
172 + getExistingColumnName() + " IS NOT NULL"), };
173 }
174
175 statements.addAll(Arrays.asList(createTablesSQL));
176
177 if (!(database instanceof OracleDatabase)) {
178 AddNotNullConstraintChange addNotNullChange = new AddNotNullConstraintChange();
179 addNotNullChange.setSchemaName(newTableSchemaName);
180 addNotNullChange.setTableName(getNewTableName());
181 addNotNullChange.setColumnName(getNewColumnName());
182 addNotNullChange.setColumnDataType(getNewColumnDataType());
183 statements.addAll(Arrays.asList(addNotNullChange.generateStatements(database)));
184 }
185
186 if (database instanceof DB2Database) {
187 statements.add(new ReorganizeTableStatement(newTableSchemaName, getNewTableName()));
188 }
189
190 AddPrimaryKeyChange addPKChange = new AddPrimaryKeyChange();
191 addPKChange.setSchemaName(newTableSchemaName);
192 addPKChange.setTableName(getNewTableName());
193 addPKChange.setColumnNames(getNewColumnName());
194 statements.addAll(Arrays.asList(addPKChange.generateStatements(database)));
195
196 if (database instanceof DB2Database) {
197 statements.add(new ReorganizeTableStatement(newTableSchemaName, getNewTableName()));
198 }
199
200 AddForeignKeyConstraintChange addFKChange = new AddForeignKeyConstraintChange();
201 addFKChange.setBaseTableSchemaName(existingTableSchemaName);
202 addFKChange.setBaseTableName(getExistingTableName());
203 addFKChange.setBaseColumnNames(getExistingColumnName());
204 addFKChange.setReferencedTableSchemaName(newTableSchemaName);
205 addFKChange.setReferencedTableName(getNewTableName());
206 addFKChange.setReferencedColumnNames(getNewColumnName());
207
208 addFKChange.setConstraintName(getFinalConstraintName());
209 statements.addAll(Arrays.asList(addFKChange.generateStatements(database)));
210
211 return statements.toArray(new SqlStatement[statements.size()]);
212 }
213
214 public String getConfirmationMessage() {
215 return "Lookup table added for " + getExistingTableName() + "." + getExistingColumnName();
216 }
217 }