|
1
5 package org.h2.constraint;
6
7 import java.sql.SQLException  ;
8
9 import org.h2.command.Parser;
10 import org.h2.command.Prepared;
11 import org.h2.engine.Session;
12 import org.h2.expression.Expression;
13 import org.h2.expression.Parameter;
14 import org.h2.index.Cursor;
15 import org.h2.index.Index;
16 import org.h2.message.Message;
17 import org.h2.result.Row;
18 import org.h2.result.SearchRow;
19 import org.h2.schema.Schema;
20 import org.h2.table.Column;
21 import org.h2.table.Table;
22 import org.h2.util.ObjectArray;
23 import org.h2.util.StringUtils;
24 import org.h2.value.Value;
25 import org.h2.value.ValueNull;
26
27
30
31 public class ConstraintReferential extends Constraint {
32 public static final int RESTRICT = 0, CASCADE = 1, SET_DEFAULT = 2, SET_NULL = 3;
33
34
36 private int deleteAction;
37 private int updateAction;
38 private Table refTable;
39 private Index index;
40 private Index refIndex;
41 private boolean indexOwner;
42 private boolean refIndexOwner;
43 protected Column[] columns;
44 protected Column[] refColumns;
45 private String deleteSQL, updateSQL;
46 private boolean skipOwnTable;
47
48 public ConstraintReferential(Schema schema, int id, String name, Table table) {
49 super(schema, id, name, table);
50 }
51
52 public String getConstraintType() {
53 return Constraint.REFERENTIAL;
54 }
55
56 private void appendAction(StringBuffer buff, int action) {
57 switch(action) {
58 case CASCADE:
59 buff.append("CASCADE");
60 break;
61 case SET_DEFAULT:
62 buff.append("SET DEFAULT");
63 break;
64 case SET_NULL:
65 buff.append("SET NULL");
66 break;
67 default:
68 throw Message.getInternalError("action="+action);
69 }
70 }
71
72 public String getCreateSQLForCopy(Table table, String quotedName) {
73 return getCreateSQLForCopy(table, refTable, quotedName, true);
74 }
75
76 public String getCreateSQLForCopy(Table table, Table refTable, String quotedName, boolean internalIndex) {
77 StringBuffer buff = new StringBuffer ();
78 buff.append("ALTER TABLE ");
79 String mainTable = table.getSQL();
80 buff.append(mainTable);
81 buff.append(" ADD CONSTRAINT ");
82 buff.append(quotedName);
83 if(comment != null) {
84 buff.append(" COMMENT ");
85 buff.append(StringUtils.quoteStringSQL(comment));
86 }
87 Column[] cols = columns;
88 Column[] refCols = refColumns;
89 buff.append(" FOREIGN KEY(");
90 for(int i=0; i<cols.length; i++) {
91 if(i>0) {
92 buff.append(", ");
93 }
94 buff.append(cols[i].getSQL());
95 }
96 buff.append(")");
97 if(internalIndex && indexOwner && table == this.table) {
98 buff.append(" INDEX ");
99 buff.append(index.getSQL());
100 }
101 buff.append(" REFERENCES ");
102 String quotedRefTable;
103 if(this.table == this.refTable) {
104 quotedRefTable = table.getSQL();
106 } else {
107 quotedRefTable = refTable.getSQL();
108 }
109 buff.append(quotedRefTable);
110 buff.append("(");
111 for(int i=0; i<refCols.length; i++) {
112 if(i>0) {
113 buff.append(", ");
114 }
115 buff.append(refCols[i].getSQL());
116 }
117 buff.append(")");
118 if(internalIndex && refIndexOwner && table == this.table) {
119 buff.append(" INDEX ");
120 buff.append(refIndex.getSQL());
121 }
122 if(deleteAction != RESTRICT) {
123 buff.append(" ON DELETE ");
124 appendAction(buff, deleteAction);
125 }
126 if(updateAction != RESTRICT) {
127 buff.append(" ON UPDATE ");
128 appendAction(buff, updateAction);
129 }
130 return buff.toString();
131 }
132
133 public String getShortDescription() {
134 StringBuffer buff = new StringBuffer ();
135 buff.append(getName());
136 buff.append(": ");
137 buff.append(table.getSQL());
138 buff.append(" FOREIGN KEY(");
139 for(int i=0; i<columns.length; i++) {
140 if(i>0) {
141 buff.append(", ");
142 }
143 buff.append(columns[i].getSQL());
144 }
145 buff.append(")");
146 buff.append(" REFERENCES ");
147 buff.append(refTable.getSQL());
148 buff.append("(");
149 for(int i=0; i<refColumns.length; i++) {
150 if(i>0) {
151 buff.append(", ");
152 }
153 buff.append(refColumns[i].getSQL());
154 }
155 buff.append(")");
156 return buff.toString();
157 }
158
159 public String getCreateSQLWithoutIndexes() {
160 return getCreateSQLForCopy(table, refTable, getSQL(), false);
161 }
162
163 public String getCreateSQL() {
164 return getCreateSQLForCopy(table, getSQL());
165 }
166
167 public void setColumns(Column[] cols) {
168 columns = cols;
169 }
170
171 public Column[] getColumns() {
172 return columns;
173 }
174
175 public void setRefColumns(Column[] refCols) {
176 refColumns = refCols;
177 }
178
179 public Column[] getRefColumns() {
180 return refColumns;
181 }
182
183 public void setRefTable(Table refTable) {
184 this.refTable = refTable;
185 if(refTable.getTemporary()) {
186 setTemporary(true);
187 }
188 }
189
190 public void setIndex(Index index, boolean isOwner) {
191 this.index = index;
192 this.indexOwner = isOwner;
193 }
194
195 public void setRefIndex(Index refIndex, boolean isRefOwner) {
196 this.refIndex = refIndex;
197 this.refIndexOwner = isRefOwner;
198 }
199
200 public void removeChildrenAndResources(Session session) throws SQLException {
201 table.removeConstraint(this);
202 refTable.removeConstraint(this);
203 if(indexOwner) {
204 database.removeSchemaObject(session, index);
205 }
206 if(refIndexOwner) {
207 database.removeSchemaObject(session, refIndex);
208 }
209 refTable = null;
210 index = null;
211 refIndex = null;
212 columns = null;
213 refColumns = null;
214 deleteSQL = null;
215 updateSQL = null;
216 table = null;
217 invalidate();
218 }
219
220 public void checkRow(Session session, Table t, Row oldRow, Row newRow) throws SQLException {
221 if(!table.getCheckForeignKeyConstraints() || !refTable.getCheckForeignKeyConstraints()) {
222 return;
223 }
224 if(t == table) {
225 if(!skipOwnTable) {
226 checkRowOwnTable(session, newRow);
227 }
228 }
229 if(t == refTable) {
230 checkRowRefTable(session, oldRow, newRow);
231 }
232 }
233
234 private void checkRowOwnTable(Session session, Row newRow) throws SQLException {
235 if(newRow==null) {
236 return;
237 }
238 boolean containsNull = false;
239 for(int i=0; i<columns.length; i++) {
240 int idx = columns[i].getColumnId();
241 Value v = newRow.getValue(idx);
242 if(v == ValueNull.INSTANCE) {
243 containsNull = true;
244 break;
245 }
246 }
247 if(containsNull) {
248 return;
249 }
250 if(refTable == table) {
251 boolean self = true;
253 for(int i=0; i<columns.length; i++) {
254 int idx = columns[i].getColumnId();
255 Value v = newRow.getValue(idx);
256 Column refCol = refColumns[i];
257 int refIdx = refCol.getColumnId();
258 Value r = newRow.getValue(refIdx);
259 if(!database.areEqual(r, v)) {
260 self = false;
261 break;
262 }
263 }
264 if(self) {
265 return;
266 }
267 }
268 Row check = refTable.getTemplateRow();
269 for(int i=0; i<columns.length; i++) {
270 int idx = columns[i].getColumnId();
271 Value v = newRow.getValue(idx);
272 Column refCol = refColumns[i];
273 int refIdx = refCol.getColumnId();
274 check.setValue(refIdx, v.convertTo(refCol.getType()));
275 }
276 if(!found(session, refIndex, check)) {
277 throw Message.getSQLException(Message.CHECK_CONSTRAINT_VIOLATED_1, getShortDescription());
278 }
279 }
280
281 private boolean found(Session session, Index index, SearchRow check) throws SQLException {
282 Cursor cursor = index.find(session, check, check);
283 while(cursor.next()) {
284 Row found = cursor.get();
285 Column[] cols = index.getColumns();
286 boolean allEqual = true;
287 for(int i=0; i<columns.length && i<cols.length; i++) {
288 int idx = cols[i].getColumnId();
289 Value c = check.getValue(idx);
290 Value f = found.getValue(idx);
291 if(database.compareTypeSave(c, f) != 0) {
292 allEqual = false;
293 break;
294 }
295 }
296 if(allEqual) {
297 return true;
298 }
299 }
300 return false;
301 }
302
303 private boolean isEqual(Row oldRow, Row newRow) throws SQLException {
304 return refIndex.compareRows(oldRow, newRow)==0;
305 }
306
307 private void checkRow(Session session, Row oldRow) throws SQLException {
308 if(refTable == table) {
309 boolean self = true;
311 for(int i=0; i<columns.length; i++) {
312 Column refCol = refColumns[i];
313 int refIdx = refCol.getColumnId();
314 Value v = oldRow.getValue(refIdx);
315 int idx = columns[i].getColumnId();
316 Value r = oldRow.getValue(idx);
317 if(!database.areEqual(r, v)) {
318 self = false;
319 break;
320 }
321 }
322 if(self) {
323 return;
324 }
325 }
326 SearchRow check = table.getTemplateSimpleRow(false);
327 for(int i=0; i<columns.length; i++) {
328 Column refCol = refColumns[i];
329 int refIdx = refCol.getColumnId();
330 Column col = columns[i];
331 int idx = col.getColumnId();
332 Value v = oldRow.getValue(refIdx).convertTo(col.getType());
333 check.setValue(idx, v);
334 }
335 if(found(session, index, check)) {
336 throw Message.getSQLException(Message.CHECK_CONSTRAINT_VIOLATED_1, getShortDescription());
337 }
338 }
339
340 private void checkRowRefTable(Session session, Row oldRow, Row newRow) throws SQLException {
341 if(oldRow == null) {
342 return;
344 }
345 if(newRow != null && isEqual(oldRow, newRow)) {
346 return;
348 }
349 if(newRow == null) {
350 if(deleteAction == RESTRICT) {
352 checkRow(session, oldRow);
353 } else {
354 int i=deleteAction == CASCADE ? 0 : columns.length;
355 Prepared deleteCommand = getDelete(session);
356 setWhere(deleteCommand, i, oldRow);
357 updateWithSkipCheck(deleteCommand);
358 }
359 } else {
360 if(updateAction == RESTRICT) {
362 checkRow(session, oldRow);
363 } else {
364 Prepared updateCommand = getUpdate(session);
365 if(updateAction == CASCADE) {
366 ObjectArray params = updateCommand.getParameters();
367 for(int i=0; i<columns.length; i++) {
368 Parameter param = (Parameter)params.get(i);
369 Column refCol = refColumns[i];
370 param.setValue(newRow.getValue(refCol.getColumnId()));
371 }
372 }
373 setWhere(updateCommand, columns.length, oldRow);
374 updateWithSkipCheck(updateCommand);
375 }
376 }
377 }
378
379 private void updateWithSkipCheck(Prepared prep) throws SQLException {
380 try {
382 skipOwnTable = true;
384 prep.update();
385 } finally {
386 skipOwnTable = false;
387 }
388 }
389
390 void setWhere(Prepared command, int pos, Row row) {
391 for(int i=0; i<refColumns.length; i++) {
392 int idx = refColumns[i].getColumnId();
393 Value v = row.getValue(idx);
394 ObjectArray params = command.getParameters();
395 Parameter param = (Parameter) params.get(pos + i);
396 param.setValue(v);
397 }
398 }
399
400 public int getDeleteAction() {
401 return deleteAction;
402 }
403
404 public void setDeleteAction(Session session, int action) throws SQLException {
405 if(action == deleteAction) {
406 return;
407 }
408 if(deleteAction != RESTRICT) {
409 throw Message.getSQLException(Message.CONSTRAINT_ALREADY_EXISTS_1, "ON DELETE");
410 }
411 this.deleteAction = action;
412 StringBuffer buff = new StringBuffer ();
413 if(action == CASCADE) {
414 buff.append("DELETE FROM ");
415 buff.append(table.getSQL());
416 } else {
417 appendUpdate(buff);
418 }
419 appendWhere(buff);
420 deleteSQL = buff.toString();
421 }
422
423 private Prepared getUpdate(Session session) throws SQLException {
424 return prepare(session, updateSQL, updateAction);
425 }
426
427 private Prepared getDelete(Session session) throws SQLException {
428 return prepare(session, deleteSQL, deleteAction);
429 }
430
431 public int getUpdateAction() {
432 return updateAction;
433 }
434
435 public void setUpdateAction(Session session, int action) throws SQLException {
436 if(action == updateAction) {
437 return;
438 }
439 if(updateAction != RESTRICT) {
440 throw Message.getSQLException(Message.CONSTRAINT_ALREADY_EXISTS_1, "ON UPDATE");
441 }
442 this.updateAction = action;
443 StringBuffer buff = new StringBuffer ();
444 appendUpdate(buff);
445 appendWhere(buff);
446 updateSQL = buff.toString();
447 }
448
449 private Prepared prepare(Session session, String sql, int action) throws SQLException {
450 Prepared command = session.prepare(sql);
451 if(action != CASCADE) {
452 ObjectArray params = command.getParameters();
453 for(int i=0; i<columns.length; i++) {
454 Column column = columns[i];
455 Parameter param = (Parameter) params.get(i);
456 Value value;
457 if(action == SET_NULL) {
458 value = ValueNull.INSTANCE;
459 } else {
460 Expression expr = column.getDefaultExpression();
461 if(expr ==null) {
462 throw Message.getSQLException(Message.NO_DEFAULT_SET_1, column.getName());
463 }
464 value = expr.getValue(session);
465 }
466 param.setValue(value);
467 }
468 }
469 return command;
470 }
471
472 private void appendUpdate(StringBuffer buff) {
473 buff.append("UPDATE ");
474 buff.append(table.getSQL());
475 buff.append(" SET ");
476 for(int i=0; i<columns.length; i++) {
477 if(i>0) {
478 buff.append(" , ");
479 }
480 Column column = columns[i];
481 buff.append(Parser.quoteIdentifier(column.getName()));
482 buff.append("=?");
483 }
484 }
485
486 private void appendWhere(StringBuffer buff) {
487 buff.append(" WHERE ");
488 for(int i=0; i<columns.length; i++) {
489 if(i>0) {
490 buff.append(" AND ");
491 }
492 Column column = columns[i];
493 buff.append(Parser.quoteIdentifier(column.getName()));
494 buff.append("=?");
495 }
496 }
497
498 public Table getRefTable() {
499 return refTable;
500 }
501
502 public boolean usesIndex(Index ind) {
503 return ind == index || ind == refIndex;
504 }
505
506 public boolean containsColumn(Column col) {
507 for(int i=0; i<columns.length; i++) {
508 if(columns[i] == col) {
509 return true;
510 }
511 }
512 for(int i=0; i<refColumns.length; i++) {
513 if(refColumns[i] == col) {
514 return true;
515 }
516 }
517 return false;
518 }
519
520 public boolean isBefore() {
521 return false;
522 }
523
524 }
525 |
Popular Tags |
Java API By Example, From Geeks To Geeks.