|
1
5 package org.h2.command.ddl;
6
7 import java.sql.SQLException  ;
8
9 import org.h2.command.Parser;
10 import org.h2.command.Prepared;
11 import org.h2.constraint.ConstraintReferential;
12 import org.h2.engine.Database;
13 import org.h2.engine.DbObject;
14 import org.h2.engine.Right;
15 import org.h2.engine.Session;
16 import org.h2.expression.Expression;
17 import org.h2.index.Index;
18 import org.h2.index.IndexType;
19 import org.h2.message.Message;
20 import org.h2.result.LocalResult;
21 import org.h2.schema.Schema;
22 import org.h2.schema.SchemaObject;
23 import org.h2.schema.Sequence;
24 import org.h2.table.Column;
25 import org.h2.table.Table;
26 import org.h2.table.TableData;
27 import org.h2.util.ObjectArray;
28
29 public class AlterTableAlterColumn extends SchemaCommand {
30
31 public static final int NOT_NULL = 0, NULL = 1, DEFAULT = 2, RESTART = 3, CHANGE_TYPE = 4;
32 public static final int ADD = 5, DROP = 6, SELECTIVITY = 7;
33
34 private Table table;
35 private Column oldColumn;
36 private Column newColumn;
37 private int type;
38 private Expression defaultExpression;
39 private long newStart;
40 private String addBefore;
41
42 public AlterTableAlterColumn(Session session, Schema schema) {
43 super(session, schema);
44 }
45
46 public void setTable(Table table) {
47 this.table = table;
48 }
49
50 public void setOldColumn(Column oldColumn) {
51 this.oldColumn = oldColumn;
52 }
53
54 public void setAddBefore(String before) {
55 this.addBefore = before;
56 }
57
58 public int update() throws SQLException {
59 session.commit();
60 Database db = session.getDatabase();
61 session.getUser().checkRight(table, Right.ALL);
62 table.checkSupportAlter();
63 table.lock(session, true);
64 Sequence sequence = oldColumn == null ? null : oldColumn.getSequence();
65 switch(type) {
66 case NOT_NULL: {
67 if(!oldColumn.getNullable()) {
68 break;
70 }
71 checkNoNullValues();
72 oldColumn.setNullable(false);
73 db.update(session, table);
74 break;
75 }
76 case NULL: {
77 if(oldColumn.getNullable()) {
78 break;
80 }
81 checkNullable();
82 oldColumn.setNullable(true);
83 db.update(session, table);
84 break;
85 }
86 case DEFAULT: {
87 oldColumn.setSequence(null);
88 oldColumn.setDefaultExpression(session, defaultExpression);
89 removeSequence(session, sequence);
90 db.update(session, table);
91 break;
92 }
93 case RESTART: {
94 if(sequence == null) {
95 throw Message.getSQLException(Message.SEQUENCE_NOT_FOUND_1, oldColumn.getSQL());
96 }
97 sequence.setStartValue(newStart);
98 db.update(session, sequence);
99 break;
100 }
101 case CHANGE_TYPE: {
102 checkNoViews();
105 oldColumn.setSequence(null);
106 oldColumn.setDefaultExpression(session, null);
107 oldColumn.setConvertNullToDefault(false);
108 if(oldColumn.getNullable() && !newColumn.getNullable()) {
109 checkNoNullValues();
110 } else if(!oldColumn.getNullable() && newColumn.getNullable()) {
111 checkNullable();
112 }
113 convertToIdentityIfRequired(newColumn);
114 copyData();
115 break;
116 }
117 case ADD: {
118 checkNoViews();
119 convertToIdentityIfRequired(newColumn);
120 copyData();
121 break;
122 }
123 case DROP: {
124 checkNoViews();
125 if(table.getColumns().length == 1) {
126 throw Message.getSQLException(Message.CANT_DROP_LAST_COLUMN, oldColumn.getSQL());
128 }
129 table.checkColumnIsNotReferenced(oldColumn);
130 dropSingleColumnIndexes();
131 copyData();
132 break;
133 }
134 case SELECTIVITY: {
135 oldColumn.setSelectivity((int)newStart);
136 db.update(session, table);
137 break;
138 }
139 default:
140 throw Message.getInternalError("type="+type);
141 }
142 return 0;
143 }
144
145 private void convertToIdentityIfRequired(Column c) throws SQLException {
146 if(c.getAutoIncrement()) {
147 c.setOriginalSQL("IDENTITY");
148 }
149 }
150
151 private void removeSequence(Session session, Sequence sequence) throws SQLException {
152 if(sequence != null) {
153 table.removeSequence(session, sequence);
154 sequence.setBelongsToTable(false);
155 Database db = session.getDatabase();
156 db.removeSchemaObject(session, sequence);
157 }
158 }
159
160 private void checkNoViews() throws SQLException {
161 ObjectArray children = table.getChildren();
162 for (int i=0; i<children.size(); i++) {
163 DbObject child = (DbObject) children.get(i);
164 if(child.getType() == DbObject.TABLE_OR_VIEW) {
165 throw Message.getSQLException(Message.OPERATION_NOT_SUPPORTED_WITH_VIEWS_2, new String []{table.getName(), child.getName()}, null);
166 }
167 }
168 }
169
170 private void copyData() throws SQLException {
171 Database db = session.getDatabase();
172 String tempName = db.getTempTableName(session.getId());
173 Column[] columns = table.getColumns();
174 ObjectArray newColumns = new ObjectArray();
175 for(int i=0; i<columns.length; i++) {
176 Column col = columns[i].getClone();
177 newColumns.add(col);
178 }
179 if(type == DROP) {
180 int position = oldColumn.getColumnId();
181 newColumns.remove(position);
182 } else if(type == ADD) {
183 int position;
184 if (addBefore == null) {
185 position = columns.length;
186 } else {
187 position = table.getColumn(addBefore).getColumnId();
188 }
189 newColumns.add(position, newColumn);
190 } else if(type == CHANGE_TYPE) {
191 int position = oldColumn.getColumnId();
192 newColumns.remove(position);
193 newColumns.add(position, newColumn);
194 }
195 boolean persistent = table.isPersistent();
196 int id = -1;
200 TableData newTable = new TableData(getSchema(), tempName, id, newColumns, persistent);
201 newTable.setComment(table.getComment());
202 execute(newTable.getCreateSQL());
203 newTable = (TableData) newTable.getSchema().getTableOrView(session, newTable.getName());
204 ObjectArray children = table.getChildren();
205 for (int i=0; i<children.size(); i++) {
206 DbObject child = (DbObject) children.get(i);
207 if(child instanceof Sequence) {
208 continue;
209 }
210 String createSQL = child.getCreateSQL();
211 if (createSQL == null) {
212 continue;
213 }
214 if(child.getType() == DbObject.TABLE_OR_VIEW) {
215 throw Message.getInternalError();
216 }
217 String quotedName = Parser.quoteIdentifier(tempName + "_" + child.getName());
218 String sql = null;
219 if(child instanceof ConstraintReferential) {
220 ConstraintReferential r = (ConstraintReferential) child;
221 if(r.getTable() != table) {
222 sql = r.getCreateSQLForCopy(r.getTable(), newTable, quotedName, false);
223 }
224 }
225 if(sql == null) {
226 sql = child.getCreateSQLForCopy(newTable, quotedName);
227 }
228 if(sql != null) {
229 execute(sql);
230 }
231 }
232 StringBuffer columnList = new StringBuffer ();
233 for(int i=0; i<newColumns.size(); i++) {
234 Column nc = (Column) newColumns.get(i);
235 if(type == ADD && nc == newColumn) {
236 continue;
237 }
238 if(columnList.length() > 0) {
239 columnList.append(", ");
240 }
241 columnList.append(nc.getSQL());
242 }
243
261
262 String sql = "INSERT INTO " + newTable.getSQL() + "(" + columnList+") "
263 + "SELECT " + columnList + " FROM " + table.getSQL();
264 newTable.setCheckForeignKeyConstraints(false);
265 try {
266 execute(sql);
267 } catch(SQLException e) {
268 unlinkSequences(newTable);
269 execute("DROP TABLE " + newTable.getSQL());
270 throw e;
271 }
272 newTable.setCheckForeignKeyConstraints(true);
273 String tableName = table.getName();
274 table.setModified();
275 for(int i=0; i<columns.length; i++) {
276 Sequence seq = columns[i].getSequence();
278 if(seq != null) {
279 table.removeSequence(session, seq);
280 columns[i].setSequence(null);
281 }
282 }
283 execute("DROP TABLE " + table.getSQL());
284 db.renameSchemaObject(session, newTable, tableName);
285 children = newTable.getChildren();
286 for (int i=0; i<children.size(); i++) {
287 DbObject child = (DbObject) children.get(i);
288 if(child instanceof Sequence) {
289 continue;
290 }
291 String name = child.getName();
292 if (name == null || child.getCreateSQL() == null) {
293 continue;
294 }
295 if(name.startsWith(tempName + "_")) {
296 name = name.substring(tempName.length() + 1);
297 db.renameSchemaObject(session, (SchemaObject)child, name);
298 }
299 }
300 }
301
302 private void unlinkSequences(Table table) throws SQLException {
303 Column[] columns = table.getColumns();
304 for(int i=0; i<columns.length; i++) {
305 Sequence seq = columns[i].getSequence();
307 if(seq != null) {
308 table.removeSequence(session, seq);
309 columns[i].setSequence(null);
310 }
311 }
312 }
313
314 private void execute(String sql) throws SQLException {
315 Prepared command = session.prepare(sql);
316 command.update();
317 }
318
319 private void dropSingleColumnIndexes() throws SQLException {
320 Database db = session.getDatabase();
321 ObjectArray indexes = table.getIndexes();
322 for (int i=0; i<indexes.size(); i++) {
323 Index index = (Index) indexes.get(i);
324 if(index.getCreateSQL() == null) {
325 continue;
326 }
327 boolean dropIndex = false;
328 Column[] cols = index.getColumns();
329 for(int j=0; j<cols.length; j++) {
330 if(cols[j] == oldColumn) {
331 if(cols.length == 1) {
332 dropIndex = true;
333 } else {
334 throw Message.getSQLException(Message.COLUMN_IS_PART_OF_INDEX_1, index.getSQL());
335 }
336 }
337 }
338 if(dropIndex) {
339 db.removeSchemaObject(session, index);
340 indexes = table.getIndexes();
341 i = -1;
342 }
343 }
344 }
345
346 private void checkNullable() throws SQLException {
347 ObjectArray indexes = table.getIndexes();
348 for(int i=0; i<indexes.size(); i++) {
349 Index index = (Index)indexes.get(i);
350 if(index.getColumnIndex(oldColumn) < 0) {
351 continue;
352 }
353 IndexType indexType = index.getIndexType();
354 if(indexType.isPrimaryKey() || indexType.isHash()) {
355 throw Message.getSQLException(Message.COLUMN_IS_PART_OF_INDEX_1, index.getSQL());
356 }
357 }
358 }
359
360 private void checkNoNullValues() throws SQLException {
361 String sql = "SELECT COUNT(*) FROM " + table.getSQL() + " WHERE " + oldColumn.getSQL() + " IS NULL";
362 Prepared command = session.prepare(sql);
363 LocalResult result = command.query(0);
364 result.next();
365 if(result.currentRow()[0].getInt() > 0) {
366 throw Message.getSQLException(Message.COLUMN_CONTAINS_NULL_VALUES_1, oldColumn.getSQL());
367 }
368 }
369
370 public void setType(int type) {
371 this.type = type;
372 }
373
374 public void setStartWith(long start) {
375 newStart = start;
376 }
377
378 public void setDefaultExpression(Expression defaultExpression) {
379 this.defaultExpression = defaultExpression;
380 }
381
382 public void setNewColumn(Column newColumn) {
383 this.newColumn = newColumn;
384 }
385
386 }
387 |
Popular Tags |
Java API By Example, From Geeks To Geeks.