001: /*
002: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
003: * (http://h2database.com/html/license.html).
004: * Initial Developer: H2 Group
005: */
006: package org.h2.command.ddl;
007:
008: import java.sql.SQLException;
009: import org.h2.command.Parser;
010: import org.h2.command.Prepared;
011: import org.h2.constant.ErrorCode;
012: import org.h2.constraint.ConstraintReferential;
013: import org.h2.engine.Database;
014: import org.h2.engine.DbObject;
015: import org.h2.engine.Right;
016: import org.h2.engine.Session;
017: import org.h2.expression.Expression;
018: import org.h2.index.Index;
019: import org.h2.index.IndexType;
020: import org.h2.message.Message;
021: import org.h2.result.LocalResult;
022: import org.h2.schema.Schema;
023: import org.h2.schema.SchemaObject;
024: import org.h2.schema.Sequence;
025: import org.h2.table.Column;
026: import org.h2.table.Table;
027: import org.h2.table.TableData;
028: import org.h2.util.ObjectArray;
029:
030: /**
031: * This class represents the statements
032: * ALTER TABLE ADD,
033: * ALTER TABLE ALTER COLUMN,
034: * ALTER TABLE ALTER COLUMN RESTART,
035: * ALTER TABLE ALTER COLUMN SELECTIVITY,
036: * ALTER TABLE ALTER COLUMN SET DEFAULT,
037: * ALTER TABLE ALTER COLUMN SET NOT NULL,
038: * ALTER TABLE ALTER COLUMN SET NULL,
039: * ALTER TABLE DROP COLUMN
040: */
041: public class AlterTableAlterColumn extends SchemaCommand {
042:
043: public static final int NOT_NULL = 0, NULL = 1, DEFAULT = 2,
044: CHANGE_TYPE = 3;
045: public static final int ADD = 4, DROP = 5, SELECTIVITY = 6;
046:
047: private Table table;
048: private Column oldColumn;
049: private Column newColumn;
050: private int type;
051: private Expression defaultExpression;
052: private Expression newSelectivity;
053: private String addBefore;
054:
055: public AlterTableAlterColumn(Session session, Schema schema) {
056: super (session, schema);
057: }
058:
059: public void setTable(Table table) {
060: this .table = table;
061: }
062:
063: public void setOldColumn(Column oldColumn) {
064: this .oldColumn = oldColumn;
065: }
066:
067: public void setAddBefore(String before) {
068: this .addBefore = before;
069: }
070:
071: public int update() throws SQLException {
072: session.commit(true);
073: Database db = session.getDatabase();
074: session.getUser().checkRight(table, Right.ALL);
075: table.checkSupportAlter();
076: table.lock(session, true, true);
077: Sequence sequence = oldColumn == null ? null : oldColumn
078: .getSequence();
079: switch (type) {
080: case NOT_NULL: {
081: if (!oldColumn.getNullable()) {
082: // no change
083: break;
084: }
085: checkNoNullValues();
086: oldColumn.setNullable(false);
087: db.update(session, table);
088: break;
089: }
090: case NULL: {
091: if (oldColumn.getNullable()) {
092: // no change
093: break;
094: }
095: checkNullable();
096: oldColumn.setNullable(true);
097: db.update(session, table);
098: break;
099: }
100: case DEFAULT: {
101: oldColumn.setSequence(null);
102: oldColumn.setDefaultExpression(session, defaultExpression);
103: removeSequence(session, sequence);
104: db.update(session, table);
105: break;
106: }
107: case CHANGE_TYPE: {
108: // TODO document data type change problems when used with
109: // autoincrement columns.
110: // sequence will be unlinked
111: checkNoViews();
112: oldColumn.setSequence(null);
113: oldColumn.setDefaultExpression(session, null);
114: oldColumn.setConvertNullToDefault(false);
115: if (oldColumn.getNullable() && !newColumn.getNullable()) {
116: checkNoNullValues();
117: } else if (!oldColumn.getNullable()
118: && newColumn.getNullable()) {
119: checkNullable();
120: }
121: convertToIdentityIfRequired(newColumn);
122: copyData();
123: break;
124: }
125: case ADD: {
126: checkNoViews();
127: convertToIdentityIfRequired(newColumn);
128: copyData();
129: break;
130: }
131: case DROP: {
132: checkNoViews();
133: if (table.getColumns().length == 1) {
134: throw Message.getSQLException(
135: ErrorCode.CANNOT_DROP_LAST_COLUMN, oldColumn
136: .getSQL());
137: }
138: table.checkColumnIsNotReferenced(oldColumn);
139: dropSingleColumnIndexes();
140: copyData();
141: break;
142: }
143: case SELECTIVITY: {
144: int value = newSelectivity.optimize(session).getValue(
145: session).getInt();
146: oldColumn.setSelectivity(value);
147: db.update(session, table);
148: break;
149: }
150: default:
151: throw Message.getInternalError("type=" + type);
152: }
153: return 0;
154: }
155:
156: private void convertToIdentityIfRequired(Column c)
157: throws SQLException {
158: if (c.getAutoIncrement()) {
159: c.setOriginalSQL("IDENTITY");
160: }
161: }
162:
163: private void removeSequence(Session session, Sequence sequence)
164: throws SQLException {
165: if (sequence != null) {
166: table.removeSequence(session, sequence);
167: sequence.setBelongsToTable(false);
168: Database db = session.getDatabase();
169: db.removeSchemaObject(session, sequence);
170: }
171: }
172:
173: private void checkNoViews() throws SQLException {
174: ObjectArray children = table.getChildren();
175: for (int i = 0; i < children.size(); i++) {
176: DbObject child = (DbObject) children.get(i);
177: if (child.getType() == DbObject.TABLE_OR_VIEW) {
178: throw Message
179: .getSQLException(
180: ErrorCode.OPERATION_NOT_SUPPORTED_WITH_VIEWS_2,
181: new String[] { table.getName(),
182: child.getName() });
183: }
184: }
185: }
186:
187: private void copyData() throws SQLException {
188: Database db = session.getDatabase();
189: String tempName = db.getTempTableName(session.getId());
190: Column[] columns = table.getColumns();
191: ObjectArray newColumns = new ObjectArray();
192: for (int i = 0; i < columns.length; i++) {
193: Column col = columns[i].getClone();
194: newColumns.add(col);
195: }
196: if (type == DROP) {
197: int position = oldColumn.getColumnId();
198: newColumns.remove(position);
199: } else if (type == ADD) {
200: int position;
201: if (addBefore == null) {
202: position = columns.length;
203: } else {
204: position = table.getColumn(addBefore).getColumnId();
205: }
206: newColumns.add(position, newColumn);
207: } else if (type == CHANGE_TYPE) {
208: int position = oldColumn.getColumnId();
209: newColumns.remove(position);
210: newColumns.add(position, newColumn);
211: }
212: boolean persistent = table.isPersistent();
213: // create a table object in order to get the SQL statement
214: // can't just use this table, because most column objects are 'shared'
215: // with the old table
216: // still need a new id because using 0 would mean: the new table tries
217: // to use the rows of the table 0 (the script table)
218: int id = -1;
219: TableData newTable = getSchema().createTable(tempName, id,
220: newColumns, persistent, false);
221: newTable.setComment(table.getComment());
222: execute(newTable.getCreateSQL(), true);
223: newTable = (TableData) newTable.getSchema().getTableOrView(
224: session, newTable.getName());
225: ObjectArray children = table.getChildren();
226: for (int i = 0; i < children.size(); i++) {
227: DbObject child = (DbObject) children.get(i);
228: if (child instanceof Sequence) {
229: continue;
230: } else if (child instanceof Index) {
231: Index idx = (Index) child;
232: if (idx.getIndexType().belongsToConstraint()) {
233: continue;
234: }
235: }
236: String createSQL = child.getCreateSQL();
237: if (createSQL == null) {
238: continue;
239: }
240: if (child.getType() == DbObject.TABLE_OR_VIEW) {
241: throw Message.getInternalError();
242: }
243: String quotedName = Parser.quoteIdentifier(tempName + "_"
244: + child.getName());
245: String sql = null;
246: if (child instanceof ConstraintReferential) {
247: ConstraintReferential r = (ConstraintReferential) child;
248: if (r.getTable() != table) {
249: sql = r.getCreateSQLForCopy(r.getTable(), newTable,
250: quotedName, false);
251: }
252: }
253: if (sql == null) {
254: sql = child.getCreateSQLForCopy(newTable, quotedName);
255: }
256: if (sql != null) {
257: execute(sql, true);
258: }
259: }
260: StringBuffer columnList = new StringBuffer();
261: for (int i = 0; i < newColumns.size(); i++) {
262: Column nc = (Column) newColumns.get(i);
263: if (type == ADD && nc == newColumn) {
264: continue;
265: }
266: if (columnList.length() > 0) {
267: columnList.append(", ");
268: }
269: columnList.append(nc.getSQL());
270: }
271: // TODO loop instead of use insert (saves memory)
272: /*
273: *
274: * Index scan = table.getBestPlanItem(null).getIndex(); Cursor cursor =
275: * scan.find(null, null); while (cursor.next()) { Row row =
276: * cursor.get(); Row newRow = newTable.getTemplateRow(); for (int i=0,
277: * j=0; i<columns.length; i++) { if(i == position) { continue; }
278: * newRow.setValue(j++, row.getValue(i)); }
279: * newTable.validateAndConvert(newRow); newTable.addRow(newRow); }
280: */
281: StringBuffer buff = new StringBuffer();
282: buff.append("INSERT INTO ");
283: buff.append(newTable.getSQL());
284: buff.append("(");
285: buff.append(columnList);
286: buff.append(") SELECT ");
287: if (columnList.length() == 0) {
288: // special case insert into test select * from test
289: buff.append("*");
290: } else {
291: buff.append(columnList);
292: }
293: buff.append(" FROM ");
294: buff.append(table.getSQL());
295: String sql = buff.toString();
296: newTable.setCheckForeignKeyConstraints(session, false, false);
297: try {
298: execute(sql, false);
299: } catch (SQLException e) {
300: unlinkSequences(newTable);
301: execute("DROP TABLE " + newTable.getSQL(), true);
302: throw e;
303: }
304: newTable.setCheckForeignKeyConstraints(session, true, false);
305: String tableName = table.getName();
306: table.setModified();
307: for (int i = 0; i < columns.length; i++) {
308: // if we don't do that, the sequence is dropped when the table is
309: // dropped
310: Sequence seq = columns[i].getSequence();
311: if (seq != null) {
312: table.removeSequence(session, seq);
313: columns[i].setSequence(null);
314: }
315: }
316: execute("DROP TABLE " + table.getSQL(), true);
317: db.renameSchemaObject(session, newTable, tableName);
318: children = newTable.getChildren();
319: for (int i = 0; i < children.size(); i++) {
320: DbObject child = (DbObject) children.get(i);
321: if (child instanceof Sequence) {
322: continue;
323: }
324: String name = child.getName();
325: if (name == null || child.getCreateSQL() == null) {
326: continue;
327: }
328: if (name.startsWith(tempName + "_")) {
329: name = name.substring(tempName.length() + 1);
330: db.renameSchemaObject(session, (SchemaObject) child,
331: name);
332: }
333: }
334: }
335:
336: private void unlinkSequences(Table table) throws SQLException {
337: Column[] columns = table.getColumns();
338: for (int i = 0; i < columns.length; i++) {
339: // if we don't do that, the sequence is dropped when the table is
340: // dropped
341: Sequence seq = columns[i].getSequence();
342: if (seq != null) {
343: table.removeSequence(session, seq);
344: columns[i].setSequence(null);
345: }
346: }
347: }
348:
349: private void execute(String sql, boolean ddl) throws SQLException {
350: Prepared command = session.prepare(sql);
351: command.update();
352: if (ddl && session.getDatabase().isMultiVersion()) {
353: // TODO this should work without MVCC, but avoid risks at the moment
354: session.commit(true);
355: }
356: }
357:
358: private void dropSingleColumnIndexes() throws SQLException {
359: Database db = session.getDatabase();
360: ObjectArray indexes = table.getIndexes();
361: for (int i = 0; i < indexes.size(); i++) {
362: Index index = (Index) indexes.get(i);
363: if (index.getCreateSQL() == null) {
364: continue;
365: }
366: boolean dropIndex = false;
367: Column[] cols = index.getColumns();
368: for (int j = 0; j < cols.length; j++) {
369: if (cols[j] == oldColumn) {
370: if (cols.length == 1) {
371: dropIndex = true;
372: } else {
373: throw Message.getSQLException(
374: ErrorCode.COLUMN_IS_PART_OF_INDEX_1,
375: index.getSQL());
376: }
377: }
378: }
379: if (dropIndex) {
380: db.removeSchemaObject(session, index);
381: indexes = table.getIndexes();
382: i = -1;
383: }
384: }
385: }
386:
387: private void checkNullable() throws SQLException {
388: ObjectArray indexes = table.getIndexes();
389: for (int i = 0; i < indexes.size(); i++) {
390: Index index = (Index) indexes.get(i);
391: if (index.getColumnIndex(oldColumn) < 0) {
392: continue;
393: }
394: IndexType indexType = index.getIndexType();
395: if (indexType.isPrimaryKey() || indexType.isHash()) {
396: throw Message.getSQLException(
397: ErrorCode.COLUMN_IS_PART_OF_INDEX_1, index
398: .getSQL());
399: }
400: }
401: }
402:
403: private void checkNoNullValues() throws SQLException {
404: String sql = "SELECT COUNT(*) FROM " + table.getSQL()
405: + " WHERE " + oldColumn.getSQL() + " IS NULL";
406: Prepared command = session.prepare(sql);
407: LocalResult result = command.query(0);
408: result.next();
409: if (result.currentRow()[0].getInt() > 0) {
410: throw Message.getSQLException(
411: ErrorCode.COLUMN_CONTAINS_NULL_VALUES_1, oldColumn
412: .getSQL());
413: }
414: }
415:
416: public void setType(int type) {
417: this .type = type;
418: }
419:
420: public void setSelectivity(Expression selectivity) {
421: newSelectivity = selectivity;
422: }
423:
424: public void setDefaultExpression(Expression defaultExpression) {
425: this .defaultExpression = defaultExpression;
426: }
427:
428: public void setNewColumn(Column newColumn) {
429: this.newColumn = newColumn;
430: }
431:
432: }
|