1 | /* |
2 | * Copyright 2004-2014 H2 Group. Multiple-Licensed under the MPL 2.0, |
3 | * and the EPL 1.0 (http://h2database.com/html/license.html). |
4 | * Initial Developer: H2 Group |
5 | */ |
6 | package org.h2.command.ddl; |
7 | |
8 | import java.util.ArrayList; |
9 | import java.util.HashSet; |
10 | |
11 | import org.h2.api.ErrorCode; |
12 | import org.h2.command.CommandInterface; |
13 | import org.h2.command.Parser; |
14 | import org.h2.command.Prepared; |
15 | import org.h2.constraint.Constraint; |
16 | import org.h2.constraint.ConstraintReferential; |
17 | import org.h2.engine.Database; |
18 | import org.h2.engine.DbObject; |
19 | import org.h2.engine.Right; |
20 | import org.h2.engine.Session; |
21 | import org.h2.expression.Expression; |
22 | import org.h2.expression.ExpressionVisitor; |
23 | import org.h2.index.Index; |
24 | import org.h2.index.IndexType; |
25 | import org.h2.message.DbException; |
26 | import org.h2.result.ResultInterface; |
27 | import org.h2.schema.Schema; |
28 | import org.h2.schema.SchemaObject; |
29 | import org.h2.schema.Sequence; |
30 | import org.h2.schema.TriggerObject; |
31 | import org.h2.table.Column; |
32 | import org.h2.table.Table; |
33 | import org.h2.table.TableView; |
34 | import org.h2.util.New; |
35 | |
36 | /** |
37 | * This class represents the statements |
38 | * ALTER TABLE ADD, |
39 | * ALTER TABLE ADD IF NOT EXISTS, |
40 | * ALTER TABLE ALTER COLUMN, |
41 | * ALTER TABLE ALTER COLUMN RESTART, |
42 | * ALTER TABLE ALTER COLUMN SELECTIVITY, |
43 | * ALTER TABLE ALTER COLUMN SET DEFAULT, |
44 | * ALTER TABLE ALTER COLUMN SET NOT NULL, |
45 | * ALTER TABLE ALTER COLUMN SET NULL, |
46 | * ALTER TABLE DROP COLUMN |
47 | */ |
48 | public class AlterTableAlterColumn extends SchemaCommand { |
49 | |
50 | private Table table; |
51 | private Column oldColumn; |
52 | private Column newColumn; |
53 | private int type; |
54 | private Expression defaultExpression; |
55 | private Expression newSelectivity; |
56 | private String addBefore; |
57 | private String addAfter; |
58 | private boolean ifNotExists; |
59 | private ArrayList<Column> columnsToAdd; |
60 | |
61 | public AlterTableAlterColumn(Session session, Schema schema) { |
62 | super(session, schema); |
63 | } |
64 | |
65 | public void setTable(Table table) { |
66 | this.table = table; |
67 | } |
68 | |
69 | public void setOldColumn(Column oldColumn) { |
70 | this.oldColumn = oldColumn; |
71 | } |
72 | |
73 | public void setAddBefore(String before) { |
74 | this.addBefore = before; |
75 | } |
76 | |
77 | public void setAddAfter(String after) { |
78 | this.addAfter = after; |
79 | } |
80 | |
81 | @Override |
82 | public int update() { |
83 | session.commit(true); |
84 | Database db = session.getDatabase(); |
85 | session.getUser().checkRight(table, Right.ALL); |
86 | table.checkSupportAlter(); |
87 | table.lock(session, true, true); |
88 | Sequence sequence = oldColumn == null ? null : oldColumn.getSequence(); |
89 | if (newColumn != null) { |
90 | checkDefaultReferencesTable(newColumn.getDefaultExpression()); |
91 | } |
92 | if (columnsToAdd != null) { |
93 | for (Column column : columnsToAdd) { |
94 | checkDefaultReferencesTable(column.getDefaultExpression()); |
95 | } |
96 | } |
97 | switch (type) { |
98 | case CommandInterface.ALTER_TABLE_ALTER_COLUMN_NOT_NULL: { |
99 | if (!oldColumn.isNullable()) { |
100 | // no change |
101 | break; |
102 | } |
103 | checkNoNullValues(); |
104 | oldColumn.setNullable(false); |
105 | db.updateMeta(session, table); |
106 | break; |
107 | } |
108 | case CommandInterface.ALTER_TABLE_ALTER_COLUMN_NULL: { |
109 | if (oldColumn.isNullable()) { |
110 | // no change |
111 | break; |
112 | } |
113 | checkNullable(); |
114 | oldColumn.setNullable(true); |
115 | db.updateMeta(session, table); |
116 | break; |
117 | } |
118 | case CommandInterface.ALTER_TABLE_ALTER_COLUMN_DEFAULT: { |
119 | checkDefaultReferencesTable(defaultExpression); |
120 | oldColumn.setSequence(null); |
121 | oldColumn.setDefaultExpression(session, defaultExpression); |
122 | removeSequence(sequence); |
123 | db.updateMeta(session, table); |
124 | break; |
125 | } |
126 | case CommandInterface.ALTER_TABLE_ALTER_COLUMN_CHANGE_TYPE: { |
127 | // if the change is only increasing the precision, then we don't |
128 | // need to copy the table because the length is only a constraint, |
129 | // and does not affect the storage structure. |
130 | if (oldColumn.isWideningConversion(newColumn)) { |
131 | convertAutoIncrementColumn(newColumn); |
132 | oldColumn.copy(newColumn); |
133 | db.updateMeta(session, table); |
134 | } else { |
135 | oldColumn.setSequence(null); |
136 | oldColumn.setDefaultExpression(session, null); |
137 | oldColumn.setConvertNullToDefault(false); |
138 | if (oldColumn.isNullable() && !newColumn.isNullable()) { |
139 | checkNoNullValues(); |
140 | } else if (!oldColumn.isNullable() && newColumn.isNullable()) { |
141 | checkNullable(); |
142 | } |
143 | convertAutoIncrementColumn(newColumn); |
144 | copyData(); |
145 | } |
146 | break; |
147 | } |
148 | case CommandInterface.ALTER_TABLE_ADD_COLUMN: { |
149 | // ifNotExists only supported for single column add |
150 | if (ifNotExists && columnsToAdd.size() == 1 && |
151 | table.doesColumnExist(columnsToAdd.get(0).getName())) { |
152 | break; |
153 | } |
154 | for (Column column : columnsToAdd) { |
155 | if (column.isAutoIncrement()) { |
156 | int objId = getObjectId(); |
157 | column.convertAutoIncrementToSequence(session, getSchema(), objId, |
158 | table.isTemporary()); |
159 | } |
160 | } |
161 | copyData(); |
162 | break; |
163 | } |
164 | case CommandInterface.ALTER_TABLE_DROP_COLUMN: { |
165 | if (table.getColumns().length == 1) { |
166 | throw DbException.get(ErrorCode.CANNOT_DROP_LAST_COLUMN, |
167 | oldColumn.getSQL()); |
168 | } |
169 | table.dropSingleColumnConstraintsAndIndexes(session, oldColumn); |
170 | copyData(); |
171 | break; |
172 | } |
173 | case CommandInterface.ALTER_TABLE_ALTER_COLUMN_SELECTIVITY: { |
174 | int value = newSelectivity.optimize(session).getValue(session).getInt(); |
175 | oldColumn.setSelectivity(value); |
176 | db.updateMeta(session, table); |
177 | break; |
178 | } |
179 | default: |
180 | DbException.throwInternalError("type=" + type); |
181 | } |
182 | return 0; |
183 | } |
184 | |
185 | private void checkDefaultReferencesTable(Expression defaultExpression) { |
186 | if (defaultExpression == null) { |
187 | return; |
188 | } |
189 | HashSet<DbObject> dependencies = New.hashSet(); |
190 | ExpressionVisitor visitor = ExpressionVisitor |
191 | .getDependenciesVisitor(dependencies); |
192 | defaultExpression.isEverything(visitor); |
193 | if (dependencies.contains(table)) { |
194 | throw DbException.get(ErrorCode.COLUMN_IS_REFERENCED_1, |
195 | defaultExpression.getSQL()); |
196 | } |
197 | } |
198 | |
199 | private void convertAutoIncrementColumn(Column c) { |
200 | if (c.isAutoIncrement()) { |
201 | if (c.isPrimaryKey()) { |
202 | c.setOriginalSQL("IDENTITY"); |
203 | } else { |
204 | int objId = getObjectId(); |
205 | c.convertAutoIncrementToSequence(session, getSchema(), objId, |
206 | table.isTemporary()); |
207 | } |
208 | } |
209 | } |
210 | |
211 | private void removeSequence(Sequence sequence) { |
212 | if (sequence != null) { |
213 | table.removeSequence(sequence); |
214 | sequence.setBelongsToTable(false); |
215 | Database db = session.getDatabase(); |
216 | db.removeSchemaObject(session, sequence); |
217 | } |
218 | } |
219 | |
220 | private void copyData() { |
221 | if (table.isTemporary()) { |
222 | throw DbException.getUnsupportedException("TEMP TABLE"); |
223 | } |
224 | Database db = session.getDatabase(); |
225 | String baseName = table.getName(); |
226 | String tempName = db.getTempTableName(baseName, session); |
227 | Column[] columns = table.getColumns(); |
228 | ArrayList<Column> newColumns = New.arrayList(); |
229 | Table newTable = cloneTableStructure(columns, db, tempName, newColumns); |
230 | try { |
231 | // check if a view would become invalid |
232 | // (because the column to drop is referenced or so) |
233 | checkViews(table, newTable); |
234 | } catch (DbException e) { |
235 | execute("DROP TABLE " + newTable.getName(), true); |
236 | throw DbException.get(ErrorCode.VIEW_IS_INVALID_2, e, getSQL(), e.getMessage()); |
237 | } |
238 | String tableName = table.getName(); |
239 | ArrayList<TableView> views = table.getViews(); |
240 | if (views != null) { |
241 | views = New.arrayList(views); |
242 | for (TableView view : views) { |
243 | table.removeView(view); |
244 | } |
245 | } |
246 | execute("DROP TABLE " + table.getSQL() + " IGNORE", true); |
247 | db.renameSchemaObject(session, newTable, tableName); |
248 | for (DbObject child : newTable.getChildren()) { |
249 | if (child instanceof Sequence) { |
250 | continue; |
251 | } |
252 | String name = child.getName(); |
253 | if (name == null || child.getCreateSQL() == null) { |
254 | continue; |
255 | } |
256 | if (name.startsWith(tempName + "_")) { |
257 | name = name.substring(tempName.length() + 1); |
258 | SchemaObject so = (SchemaObject) child; |
259 | if (so instanceof Constraint) { |
260 | if (so.getSchema().findConstraint(session, name) != null) { |
261 | name = so.getSchema().getUniqueConstraintName(session, newTable); |
262 | } |
263 | } else if (so instanceof Index) { |
264 | if (so.getSchema().findIndex(session, name) != null) { |
265 | name = so.getSchema().getUniqueIndexName(session, newTable, name); |
266 | } |
267 | } |
268 | db.renameSchemaObject(session, so, name); |
269 | } |
270 | } |
271 | if (views != null) { |
272 | for (TableView view : views) { |
273 | String sql = view.getCreateSQL(true, true); |
274 | execute(sql, true); |
275 | } |
276 | } |
277 | } |
278 | |
279 | private Table cloneTableStructure(Column[] columns, Database db, |
280 | String tempName, ArrayList<Column> newColumns) { |
281 | for (Column col : columns) { |
282 | newColumns.add(col.getClone()); |
283 | } |
284 | if (type == CommandInterface.ALTER_TABLE_DROP_COLUMN) { |
285 | int position = oldColumn.getColumnId(); |
286 | newColumns.remove(position); |
287 | } else if (type == CommandInterface.ALTER_TABLE_ADD_COLUMN) { |
288 | int position; |
289 | if (addBefore != null) { |
290 | position = table.getColumn(addBefore).getColumnId(); |
291 | } else if (addAfter != null) { |
292 | position = table.getColumn(addAfter).getColumnId() + 1; |
293 | } else { |
294 | position = columns.length; |
295 | } |
296 | for (Column column : columnsToAdd) { |
297 | newColumns.add(position++, column); |
298 | } |
299 | } else if (type == CommandInterface.ALTER_TABLE_ALTER_COLUMN_CHANGE_TYPE) { |
300 | int position = oldColumn.getColumnId(); |
301 | newColumns.remove(position); |
302 | newColumns.add(position, newColumn); |
303 | } |
304 | |
305 | // create a table object in order to get the SQL statement |
306 | // can't just use this table, because most column objects are 'shared' |
307 | // with the old table |
308 | // still need a new id because using 0 would mean: the new table tries |
309 | // to use the rows of the table 0 (the meta table) |
310 | int id = db.allocateObjectId(); |
311 | CreateTableData data = new CreateTableData(); |
312 | data.tableName = tempName; |
313 | data.id = id; |
314 | data.columns = newColumns; |
315 | data.temporary = table.isTemporary(); |
316 | data.persistData = table.isPersistData(); |
317 | data.persistIndexes = table.isPersistIndexes(); |
318 | data.isHidden = table.isHidden(); |
319 | data.create = true; |
320 | data.session = session; |
321 | Table newTable = getSchema().createTable(data); |
322 | newTable.setComment(table.getComment()); |
323 | StringBuilder buff = new StringBuilder(); |
324 | buff.append(newTable.getCreateSQL()); |
325 | StringBuilder columnList = new StringBuilder(); |
326 | for (Column nc : newColumns) { |
327 | if (columnList.length() > 0) { |
328 | columnList.append(", "); |
329 | } |
330 | if (type == CommandInterface.ALTER_TABLE_ADD_COLUMN && |
331 | columnsToAdd.contains(nc)) { |
332 | Expression def = nc.getDefaultExpression(); |
333 | columnList.append(def == null ? "NULL" : def.getSQL()); |
334 | } else { |
335 | columnList.append(nc.getSQL()); |
336 | } |
337 | } |
338 | buff.append(" AS SELECT "); |
339 | if (columnList.length() == 0) { |
340 | // special case: insert into test select * from |
341 | buff.append('*'); |
342 | } else { |
343 | buff.append(columnList); |
344 | } |
345 | buff.append(" FROM ").append(table.getSQL()); |
346 | String newTableSQL = buff.toString(); |
347 | String newTableName = newTable.getName(); |
348 | Schema newTableSchema = newTable.getSchema(); |
349 | newTable.removeChildrenAndResources(session); |
350 | |
351 | execute(newTableSQL, true); |
352 | newTable = newTableSchema.getTableOrView(session, newTableName); |
353 | ArrayList<String> triggers = New.arrayList(); |
354 | for (DbObject child : table.getChildren()) { |
355 | if (child instanceof Sequence) { |
356 | continue; |
357 | } else if (child instanceof Index) { |
358 | Index idx = (Index) child; |
359 | if (idx.getIndexType().getBelongsToConstraint()) { |
360 | continue; |
361 | } |
362 | } |
363 | String createSQL = child.getCreateSQL(); |
364 | if (createSQL == null) { |
365 | continue; |
366 | } |
367 | if (child instanceof TableView) { |
368 | continue; |
369 | } else if (child.getType() == DbObject.TABLE_OR_VIEW) { |
370 | DbException.throwInternalError(); |
371 | } |
372 | String quotedName = Parser.quoteIdentifier(tempName + "_" + child.getName()); |
373 | String sql = null; |
374 | if (child instanceof ConstraintReferential) { |
375 | ConstraintReferential r = (ConstraintReferential) child; |
376 | if (r.getTable() != table) { |
377 | sql = r.getCreateSQLForCopy(r.getTable(), newTable, quotedName, false); |
378 | } |
379 | } |
380 | if (sql == null) { |
381 | sql = child.getCreateSQLForCopy(newTable, quotedName); |
382 | } |
383 | if (sql != null) { |
384 | if (child instanceof TriggerObject) { |
385 | triggers.add(sql); |
386 | } else { |
387 | execute(sql, true); |
388 | } |
389 | } |
390 | } |
391 | table.setModified(); |
392 | // remove the sequences from the columns (except dropped columns) |
393 | // otherwise the sequence is dropped if the table is dropped |
394 | for (Column col : newColumns) { |
395 | Sequence seq = col.getSequence(); |
396 | if (seq != null) { |
397 | table.removeSequence(seq); |
398 | col.setSequence(null); |
399 | } |
400 | } |
401 | for (String sql : triggers) { |
402 | execute(sql, true); |
403 | } |
404 | return newTable; |
405 | } |
406 | |
407 | /** |
408 | * Check that all views and other dependent objects. |
409 | */ |
410 | private void checkViews(SchemaObject sourceTable, SchemaObject newTable) { |
411 | String sourceTableName = sourceTable.getName(); |
412 | String newTableName = newTable.getName(); |
413 | Database db = sourceTable.getDatabase(); |
414 | // save the real table under a temporary name |
415 | String temp = db.getTempTableName(sourceTableName, session); |
416 | db.renameSchemaObject(session, sourceTable, temp); |
417 | try { |
418 | // have our new table impersonate the target table |
419 | db.renameSchemaObject(session, newTable, sourceTableName); |
420 | checkViewsAreValid(sourceTable); |
421 | } finally { |
422 | // always put the source tables back with their proper names |
423 | try { |
424 | db.renameSchemaObject(session, newTable, newTableName); |
425 | } finally { |
426 | db.renameSchemaObject(session, sourceTable, sourceTableName); |
427 | } |
428 | } |
429 | } |
430 | |
431 | /** |
432 | * Check that a table or view is still valid. |
433 | * |
434 | * @param tableOrView the table or view to check |
435 | */ |
436 | private void checkViewsAreValid(DbObject tableOrView) { |
437 | for (DbObject view : tableOrView.getChildren()) { |
438 | if (view instanceof TableView) { |
439 | String sql = ((TableView) view).getQuery(); |
440 | // check if the query is still valid |
441 | // do not execute, not even with limit 1, because that could |
442 | // have side effects or take a very long time |
443 | session.prepare(sql); |
444 | checkViewsAreValid(view); |
445 | } |
446 | } |
447 | } |
448 | |
449 | private void execute(String sql, boolean ddl) { |
450 | Prepared command = session.prepare(sql); |
451 | command.update(); |
452 | if (ddl) { |
453 | session.commit(true); |
454 | } |
455 | } |
456 | |
457 | private void checkNullable() { |
458 | for (Index index : table.getIndexes()) { |
459 | if (index.getColumnIndex(oldColumn) < 0) { |
460 | continue; |
461 | } |
462 | IndexType indexType = index.getIndexType(); |
463 | if (indexType.isPrimaryKey() || indexType.isHash()) { |
464 | throw DbException.get( |
465 | ErrorCode.COLUMN_IS_PART_OF_INDEX_1, index.getSQL()); |
466 | } |
467 | } |
468 | } |
469 | |
470 | private void checkNoNullValues() { |
471 | String sql = "SELECT COUNT(*) FROM " + |
472 | table.getSQL() + " WHERE " + |
473 | oldColumn.getSQL() + " IS NULL"; |
474 | Prepared command = session.prepare(sql); |
475 | ResultInterface result = command.query(0); |
476 | result.next(); |
477 | if (result.currentRow()[0].getInt() > 0) { |
478 | throw DbException.get( |
479 | ErrorCode.COLUMN_CONTAINS_NULL_VALUES_1, |
480 | oldColumn.getSQL()); |
481 | } |
482 | } |
483 | |
484 | public void setType(int type) { |
485 | this.type = type; |
486 | } |
487 | |
488 | public void setSelectivity(Expression selectivity) { |
489 | newSelectivity = selectivity; |
490 | } |
491 | |
492 | public void setDefaultExpression(Expression defaultExpression) { |
493 | this.defaultExpression = defaultExpression; |
494 | } |
495 | |
496 | public void setNewColumn(Column newColumn) { |
497 | this.newColumn = newColumn; |
498 | } |
499 | |
500 | @Override |
501 | public int getType() { |
502 | return type; |
503 | } |
504 | |
505 | public void setIfNotExists(boolean ifNotExists) { |
506 | this.ifNotExists = ifNotExists; |
507 | } |
508 | |
509 | public void setNewColumns(ArrayList<Column> columnsToAdd) { |
510 | this.columnsToAdd = columnsToAdd; |
511 | } |
512 | } |