001: package liquibase.change;
002:
003: import liquibase.database.*;
004: import liquibase.database.sql.RawSqlStatement;
005: import liquibase.database.sql.ReorganizeTableStatement;
006: import liquibase.database.sql.SqlStatement;
007: import liquibase.database.structure.Column;
008: import liquibase.database.structure.DatabaseObject;
009: import liquibase.database.structure.ForeignKey;
010: import liquibase.database.structure.Table;
011: import liquibase.exception.UnsupportedChangeException;
012: import org.w3c.dom.Document;
013: import org.w3c.dom.Element;
014:
015: import java.util.*;
016:
017: /**
018: * Extracts data from an existing column to create a lookup table.
019: * A foreign key is created between the old column and the new lookup table.
020: */
021: public class AddLookupTableChange extends AbstractChange {
022:
023: private String existingTableSchemaName;
024: private String existingTableName;
025: private String existingColumnName;
026:
027: private String newTableSchemaName;
028: private String newTableName;
029: private String newColumnName;
030: private String newColumnDataType;
031: private String constraintName;
032:
033: public AddLookupTableChange() {
034: super ("addLookupTable", "Add Lookup Table");
035: }
036:
037: public String getExistingTableSchemaName() {
038: return existingTableSchemaName;
039: }
040:
041: public void setExistingTableSchemaName(
042: String existingTableSchemaName) {
043: this .existingTableSchemaName = existingTableSchemaName;
044: }
045:
046: public String getExistingTableName() {
047: return existingTableName;
048: }
049:
050: public void setExistingTableName(String existingTableName) {
051: this .existingTableName = existingTableName;
052: }
053:
054: public String getExistingColumnName() {
055: return existingColumnName;
056: }
057:
058: public void setExistingColumnName(String existingColumnName) {
059: this .existingColumnName = existingColumnName;
060: }
061:
062: public String getNewTableSchemaName() {
063: return newTableSchemaName;
064: }
065:
066: public void setNewTableSchemaName(String newTableSchemaName) {
067: this .newTableSchemaName = newTableSchemaName;
068: }
069:
070: public String getNewTableName() {
071: return newTableName;
072: }
073:
074: public void setNewTableName(String newTableName) {
075: this .newTableName = newTableName;
076: }
077:
078: public String getNewColumnName() {
079: return newColumnName;
080: }
081:
082: public void setNewColumnName(String newColumnName) {
083: this .newColumnName = newColumnName;
084: }
085:
086: public String getNewColumnDataType() {
087: return newColumnDataType;
088: }
089:
090: public void setNewColumnDataType(String newColumnDataType) {
091: this .newColumnDataType = newColumnDataType;
092: }
093:
094: public String getConstraintName() {
095: return constraintName;
096: }
097:
098: public String getFinalConstraintName() {
099: if (constraintName == null) {
100: return ("FK_" + getExistingTableName() + "_" + getNewTableName())
101: .toUpperCase();
102: } else {
103: return constraintName;
104: }
105: }
106:
107: public void setConstraintName(String constraintName) {
108: this .constraintName = constraintName;
109: }
110:
111: protected Change[] createInverses() {
112: DropForeignKeyConstraintChange dropFK = new DropForeignKeyConstraintChange();
113: dropFK.setBaseTableSchemaName(getExistingTableSchemaName());
114: dropFK.setBaseTableName(getExistingTableName());
115: dropFK.setConstraintName(getFinalConstraintName());
116:
117: DropTableChange dropTable = new DropTableChange();
118: dropTable.setSchemaName(getNewTableSchemaName());
119: dropTable.setTableName(getNewTableName());
120:
121: return new Change[] { dropFK, dropTable, };
122: }
123:
124: public SqlStatement[] generateStatements(Database database)
125: throws UnsupportedChangeException {
126: if (database instanceof DerbyDatabase) {
127: throw new UnsupportedChangeException(
128: "Add Lookup Table currently not supported in Derby");
129: } else if (database instanceof HsqlDatabase) {
130: throw new UnsupportedChangeException(
131: "Add Lookup Table currently not supported in HSQLDB");
132: } else if (database instanceof CacheDatabase) {
133: throw new UnsupportedChangeException(
134: "Add Lookup Table not currently supported for Cache");
135: } else if (database instanceof FirebirdDatabase) {
136: throw new UnsupportedChangeException(
137: "Add Lookup Table not currently supported for Firebird");
138: }
139:
140: List<SqlStatement> statements = new ArrayList<SqlStatement>();
141:
142: String newTableSchemaName = getNewTableSchemaName() == null ? database
143: .getDefaultSchemaName()
144: : getNewTableSchemaName();
145: String existingTableSchemaName = getExistingTableSchemaName() == null ? database
146: .getDefaultSchemaName()
147: : getExistingTableSchemaName();
148:
149: SqlStatement[] createTablesSQL = { new RawSqlStatement(
150: "CREATE TABLE "
151: + database.escapeTableName(newTableSchemaName,
152: getNewTableName())
153: + " AS SELECT DISTINCT "
154: + getExistingColumnName()
155: + " AS "
156: + getNewColumnName()
157: + " FROM "
158: + database.escapeTableName(
159: existingTableSchemaName,
160: getExistingTableName()) + " WHERE "
161: + getExistingColumnName() + " IS NOT NULL") };
162: if (database instanceof MSSQLDatabase) {
163: createTablesSQL = new SqlStatement[] { new RawSqlStatement(
164: "SELECT DISTINCT "
165: + getExistingColumnName()
166: + " AS "
167: + getNewColumnName()
168: + " INTO "
169: + database.escapeTableName(
170: newTableSchemaName,
171: getNewTableName())
172: + " FROM "
173: + database.escapeTableName(
174: existingTableSchemaName,
175: getExistingTableName()) + " WHERE "
176: + getExistingColumnName() + " IS NOT NULL"), };
177: } else if (database instanceof DB2Database) {
178: createTablesSQL = new SqlStatement[] {
179: new RawSqlStatement("CREATE TABLE "
180: + database.escapeTableName(
181: newTableSchemaName,
182: getNewTableName())
183: + " AS (SELECT "
184: + getExistingColumnName()
185: + " AS "
186: + getNewColumnName()
187: + " FROM "
188: + database.escapeTableName(
189: existingTableSchemaName,
190: getExistingTableName())
191: + ") WITH NO DATA"),
192: new RawSqlStatement("INSERT INTO "
193: + database.escapeTableName(
194: newTableSchemaName,
195: getNewTableName())
196: + " SELECT DISTINCT "
197: + getExistingColumnName()
198: + " FROM "
199: + database.escapeTableName(
200: existingTableSchemaName,
201: getExistingTableName()) + " WHERE "
202: + getExistingColumnName() + " IS NOT NULL"), };
203: }
204:
205: statements.addAll(Arrays.asList(createTablesSQL));
206:
207: if (!(database instanceof OracleDatabase)) {
208: AddNotNullConstraintChange addNotNullChange = new AddNotNullConstraintChange();
209: addNotNullChange.setSchemaName(newTableSchemaName);
210: addNotNullChange.setTableName(getNewTableName());
211: addNotNullChange.setColumnName(getNewColumnName());
212: addNotNullChange.setColumnDataType(getNewColumnDataType());
213: statements.addAll(Arrays.asList(addNotNullChange
214: .generateStatements(database)));
215: }
216:
217: if (database instanceof DB2Database) {
218: statements.add(new ReorganizeTableStatement(
219: newTableSchemaName, getNewTableName()));
220: }
221:
222: AddPrimaryKeyChange addPKChange = new AddPrimaryKeyChange();
223: addPKChange.setSchemaName(newTableSchemaName);
224: addPKChange.setTableName(getNewTableName());
225: addPKChange.setColumnNames(getNewColumnName());
226: statements.addAll(Arrays.asList(addPKChange
227: .generateStatements(database)));
228:
229: if (database instanceof DB2Database) {
230: statements.add(new ReorganizeTableStatement(
231: newTableSchemaName, getNewTableName()));
232: }
233:
234: AddForeignKeyConstraintChange addFKChange = new AddForeignKeyConstraintChange();
235: addFKChange.setBaseTableSchemaName(existingTableSchemaName);
236: addFKChange.setBaseTableName(getExistingTableName());
237: addFKChange.setBaseColumnNames(getExistingColumnName());
238: addFKChange.setReferencedTableSchemaName(newTableSchemaName);
239: addFKChange.setReferencedTableName(getNewTableName());
240: addFKChange.setReferencedColumnNames(getNewColumnName());
241:
242: addFKChange.setConstraintName(getFinalConstraintName());
243: statements.addAll(Arrays.asList(addFKChange
244: .generateStatements(database)));
245:
246: return statements.toArray(new SqlStatement[statements.size()]);
247: }
248:
249: public String getConfirmationMessage() {
250: return "Lookup table added for " + getExistingTableName() + "."
251: + getExistingColumnName();
252: }
253:
254: public Element createNode(Document currentChangeLogFileDOM) {
255: Element node = currentChangeLogFileDOM
256: .createElement(getTagName());
257: if (getExistingTableSchemaName() != null) {
258: node.setAttribute("newTableSchemaName",
259: getExistingTableSchemaName());
260: }
261: node.setAttribute("existingTableName", getExistingTableName());
262: node
263: .setAttribute("existingColumnName",
264: getExistingColumnName());
265:
266: if (getNewTableSchemaName() != null) {
267: node.setAttribute("newTableSchemaName",
268: getNewTableSchemaName());
269: }
270:
271: node.setAttribute("newTableName", getNewTableName());
272: node.setAttribute("newColumnName", getNewColumnName());
273: node.setAttribute("constraintName", getConstraintName());
274:
275: return node;
276: }
277:
278: public Set<DatabaseObject> getAffectedDatabaseObjects() {
279: Set<DatabaseObject> returnSet = new HashSet<DatabaseObject>();
280:
281: Table existingTable = new Table(getExistingTableName());
282: returnSet.add(existingTable);
283:
284: Column existingColumn = new Column();
285: existingColumn.setTable(existingTable);
286: existingColumn.setName(getExistingColumnName());
287: returnSet.add(existingColumn);
288:
289: Table newTable = new Table(getNewTableName());
290: returnSet.add(newTable);
291:
292: Column newColumn = new Column();
293: newColumn.setTable(existingTable);
294: newColumn.setName(getNewColumnName());
295: returnSet.add(newColumn);
296:
297: ForeignKey fk = new ForeignKey();
298: fk.setForeignKeyTable(existingTable);
299: fk.setForeignKeyColumns(existingColumn.getName());
300: fk.setPrimaryKeyTable(newTable);
301: fk.setPrimaryKeyColumns(newColumn.getName());
302: returnSet.add(fk);
303:
304: return returnSet;
305:
306: }
307: }
|