File D:\maven-qcr-plugin-testsunitaires\qcr4java\h2-1.3.170-sources\target\site\qc\lecode\constraint\ConstraintReferential.java.html

1 /*
2 * Copyright 2004-2011 H2 Group. Multiple-Licensed under the H2 License,
3 * Version 1.0, and under the Eclipse Public License, Version 1.0
4 * (http://h2database.com/html/license.html).
5 * Initial Developer: H2 Group
6 */
7 package org.h2.constraint;
8
9 import java.util.ArrayList;
10 import java.util.HashSet;
11 import org.h2.command.Parser;
12 import org.h2.command.Prepared;
13 import org.h2.constant.ErrorCode;
14 import org.h2.engine.Session;
15 import org.h2.expression.Expression;
16 import org.h2.expression.Parameter;
17 import org.h2.index.Cursor;
18 import org.h2.index.Index;
19 import org.h2.message.DbException;
20 import org.h2.result.ResultInterface;
21 import org.h2.result.Row;
22 import org.h2.result.SearchRow;
23 import org.h2.schema.Schema;
24 import org.h2.table.Column;
25 import org.h2.table.IndexColumn;
26 import org.h2.table.Table;
27 import org.h2.util.New;
28 import org.h2.util.StatementBuilder;
29 import org.h2.util.StringUtils;
30 import org.h2.value.Value;
31 import org.h2.value.ValueNull;
32
33 /**
34 * A referential constraint.
35 */
36 public class ConstraintReferential extends Constraint {
37
38 /**
39 * The action is to restrict the operation.
40 */
41 public static final int RESTRICT = 0;
42
43 /**
44 * The action is to cascade the operation.
45 */
46 public static final int CASCADE = 1;
47
48 /**
49 * The action is to set the value to the default value.
50 */
51 public static final int SET_DEFAULT = 2;
52
53 /**
54 * The action is to set the value to NULL.
55 */
56 public static final int SET_NULL = 3;
57
58 private IndexColumn[] columns;
59 private IndexColumn[] refColumns;
60 private int deleteAction;
61 private int updateAction;
62 private Table refTable;
63 private Index index;
64 private Index refIndex;
65 private boolean indexOwner;
66 private boolean refIndexOwner;
67 private String deleteSQL, updateSQL;
68 private boolean skipOwnTable;
69
70 public ConstraintReferential(Schema schema, int id, String name, Table table) {
71 super(schema, id, name, table);
72 }
73
74 public String getConstraintType() {
75 return Constraint.REFERENTIAL;
76 }
77
78 private static void appendAction(StatementBuilder buff, int action) {
79 switch (action) {
80 case CASCADE:
81 buff.append("CASCADE");
82 break;
83 case SET_DEFAULT:
84 buff.append("SET DEFAULT");
85 break;
86 case SET_NULL:
87 buff.append("SET NULL");
88 break;
89 default:
90 DbException.throwInternalError("action=" + action);
91 }
92 }
93
94 /**
95 * Create the SQL statement of this object so a copy of the table can be made.
96 *
97 * @param forTable the table to create the object for
98 * @param quotedName the name of this object (quoted if necessary)
99 * @return the SQL statement
100 */
101 public String getCreateSQLForCopy(Table forTable, String quotedName) {
102 return getCreateSQLForCopy(forTable, refTable, quotedName, true);
103 }
104
105 /**
106 * Create the SQL statement of this object so a copy of the table can be made.
107 *
108 * @param forTable the table to create the object for
109 * @param forRefTable the referenced table
110 * @param quotedName the name of this object (quoted if necessary)
111 * @param internalIndex add the index name to the statement
112 * @return the SQL statement
113 */
114 public String getCreateSQLForCopy(Table forTable, Table forRefTable, String quotedName, boolean internalIndex) {
115 StatementBuilder buff = new StatementBuilder("ALTER TABLE ");
116 String mainTable = forTable.getSQL();
117 buff.append(mainTable).append(" ADD CONSTRAINT ");
118 if (forTable.isHidden()) {
119 buff.append("IF NOT EXISTS ");
120 }
121 buff.append(quotedName);
122 if (comment != null) {
123 buff.append(" COMMENT ").append(StringUtils.quoteStringSQL(comment));
124 }
125 IndexColumn[] cols = columns;
126 IndexColumn[] refCols = refColumns;
127 buff.append(" FOREIGN KEY(");
128 for (IndexColumn c : cols) {
129 buff.appendExceptFirst(", ");
130 buff.append(c.getSQL());
131 }
132 buff.append(')');
133 if (internalIndex && indexOwner && forTable == this.table) {
134 buff.append(" INDEX ").append(index.getSQL());
135 }
136 buff.append(" REFERENCES ");
137 String quotedRefTable;
138 if (this.table == this.refTable) {
139 // self-referencing constraints: need to use new table
140 quotedRefTable = forTable.getSQL();
141 } else {
142 quotedRefTable = forRefTable.getSQL();
143 }
144 buff.append(quotedRefTable).append('(');
145 buff.resetCount();
146 for (IndexColumn r : refCols) {
147 buff.appendExceptFirst(", ");
148 buff.append(r.getSQL());
149 }
150 buff.append(')');
151 if (internalIndex && refIndexOwner && forTable == this.table) {
152 buff.append(" INDEX ").append(refIndex.getSQL());
153 }
154 if (deleteAction != RESTRICT) {
155 buff.append(" ON DELETE ");
156 appendAction(buff, deleteAction);
157 }
158 if (updateAction != RESTRICT) {
159 buff.append(" ON UPDATE ");
160 appendAction(buff, updateAction);
161 }
162 return buff.append(" NOCHECK").toString();
163 }
164
165
166 /**
167 * Get a short description of the constraint. This includes the constraint
168 * name (if set), and the constraint expression.
169 *
170 * @param searchIndex the index, or null
171 * @param check the row, or null
172 * @return the description
173 */
174 private String getShortDescription(Index searchIndex, SearchRow check) {
175 StatementBuilder buff = new StatementBuilder(getName());
176 buff.append(": ").append(table.getSQL()).append(" FOREIGN KEY(");
177 for (IndexColumn c : columns) {
178 buff.appendExceptFirst(", ");
179 buff.append(c.getSQL());
180 }
181 buff.append(") REFERENCES ").append(refTable.getSQL()).append('(');
182 buff.resetCount();
183 for (IndexColumn r : refColumns) {
184 buff.appendExceptFirst(", ");
185 buff.append(r.getSQL());
186 }
187 buff.append(')');
188 if (searchIndex != null && check != null) {
189 buff.append(" (");
190 buff.resetCount();
191 Column[] cols = searchIndex.getColumns();
192 int len = Math.min(columns.length, cols.length);
193 for (int i = 0; i < len; i++) {
194 int idx = cols[i].getColumnId();
195 Value c = check.getValue(idx);
196 buff.appendExceptFirst(", ");
197 buff.append(c == null ? "" : c.toString());
198 }
199 buff.append(')');
200 }
201 return buff.toString();
202 }
203
204 public String getCreateSQLWithoutIndexes() {
205 return getCreateSQLForCopy(table, refTable, getSQL(), false);
206 }
207
208 public String getCreateSQL() {
209 return getCreateSQLForCopy(table, getSQL());
210 }
211
212 public void setColumns(IndexColumn[] cols) {
213 columns = cols;
214 }
215
216 public IndexColumn[] getColumns() {
217 return columns;
218 }
219
220 public HashSet<Column> getReferencedColumns(Table table) {
221 HashSet<Column> result = New.hashSet();
222 if (table == this.table) {
223 for (IndexColumn c : columns) {
224 result.add(c.column);
225 }
226 } else if (table == this.refTable) {
227 for (IndexColumn c : refColumns) {
228 result.add(c.column);
229 }
230 }
231 return result;
232 }
233
234 public void setRefColumns(IndexColumn[] refCols) {
235 refColumns = refCols;
236 }
237
238 public IndexColumn[] getRefColumns() {
239 return refColumns;
240 }
241
242 public void setRefTable(Table refTable) {
243 this.refTable = refTable;
244 if (refTable.isTemporary()) {
245 setTemporary(true);
246 }
247 }
248
249 /**
250 * Set the index to use for this constraint.
251 *
252 * @param index the index
253 * @param isOwner true if the index is generated by the system and belongs
254 * to this constraint
255 */
256 public void setIndex(Index index, boolean isOwner) {
257 this.index = index;
258 this.indexOwner = isOwner;
259 }
260
261 /**
262 * Set the index of the referenced table to use for this constraint.
263 *
264 * @param refIndex the index
265 * @param isRefOwner true if the index is generated by the system and
266 * belongs to this constraint
267 */
268 public void setRefIndex(Index refIndex, boolean isRefOwner) {
269 this.refIndex = refIndex;
270 this.refIndexOwner = isRefOwner;
271 }
272
273 public void removeChildrenAndResources(Session session) {
274 table.removeConstraint(this);
275 refTable.removeConstraint(this);
276 if (indexOwner) {
277 table.removeIndexOrTransferOwnership(session, index);
278 }
279 if (refIndexOwner) {
280 refTable.removeIndexOrTransferOwnership(session, refIndex);
281 }
282 database.removeMeta(session, getId());
283 refTable = null;
284 index = null;
285 refIndex = null;
286 columns = null;
287 refColumns = null;
288 deleteSQL = null;
289 updateSQL = null;
290 table = null;
291 invalidate();
292 }
293
294 public void checkRow(Session session, Table t, Row oldRow, Row newRow) {
295 if (!database.getReferentialIntegrity()) {
296 return;
297 }
298 if (!table.getCheckForeignKeyConstraints() || !refTable.getCheckForeignKeyConstraints()) {
299 return;
300 }
301 if (t == table) {
302 if (!skipOwnTable) {
303 checkRowOwnTable(session, oldRow, newRow);
304 }
305 }
306 if (t == refTable) {
307 checkRowRefTable(session, oldRow, newRow);
308 }
309 }
310
311 private void checkRowOwnTable(Session session, Row oldRow, Row newRow) {
312 if (newRow == null) {
313 return;
314 }
315 boolean constraintColumnsEqual = oldRow != null;
316 for (IndexColumn col : columns) {
317 int idx = col.column.getColumnId();
318 Value v = newRow.getValue(idx);
319 if (v == ValueNull.INSTANCE) {
320 // return early if one of the columns is NULL
321 return;
322 }
323 if (constraintColumnsEqual) {
324 if (!database.areEqual(v, oldRow.getValue(idx))) {
325 constraintColumnsEqual = false;
326 }
327 }
328 }
329 if (constraintColumnsEqual) {
330 // return early if the key columns didn't change
331 return;
332 }
333 if (refTable == table) {
334 // special case self referencing constraints:
335 // check the inserted row first
336 boolean self = true;
337 for (int i = 0, len = columns.length; i < len; i++) {
338 int idx = columns[i].column.getColumnId();
339 Value v = newRow.getValue(idx);
340 Column refCol = refColumns[i].column;
341 int refIdx = refCol.getColumnId();
342 Value r = newRow.getValue(refIdx);
343 if (!database.areEqual(r, v)) {
344 self = false;
345 break;
346 }
347 }
348 if (self) {
349 return;
350 }
351 }
352 Row check = refTable.getTemplateRow();
353 for (int i = 0, len = columns.length; i < len; i++) {
354 int idx = columns[i].column.getColumnId();
355 Value v = newRow.getValue(idx);
356 Column refCol = refColumns[i].column;
357 int refIdx = refCol.getColumnId();
358 check.setValue(refIdx, refCol.convert(v));
359 }
360 if (!existsRow(session, refIndex, check, null)) {
361 throw DbException.get(ErrorCode.REFERENTIAL_INTEGRITY_VIOLATED_PARENT_MISSING_1,
362 getShortDescription(refIndex, check));
363 }
364 }
365
366 private boolean existsRow(Session session, Index searchIndex, SearchRow check, Row excluding) {
367 Table searchTable = searchIndex.getTable();
368 searchTable.lock(session, false, false);
369 Cursor cursor = searchIndex.find(session, check, check);
370 while (cursor.next()) {
371 SearchRow found;
372 found = cursor.getSearchRow();
373 if (excluding != null && found.getKey() == excluding.getKey()) {
374 continue;
375 }
376 Column[] cols = searchIndex.getColumns();
377 boolean allEqual = true;
378 int len = Math.min(columns.length, cols.length);
379 for (int i = 0; i < len; i++) {
380 int idx = cols[i].getColumnId();
381 Value c = check.getValue(idx);
382 Value f = found.getValue(idx);
383 if (searchTable.compareTypeSave(c, f) != 0) {
384 allEqual = false;
385 break;
386 }
387 }
388 if (allEqual) {
389 return true;
390 }
391 }
392 return false;
393 }
394
395 private boolean isEqual(Row oldRow, Row newRow) {
396 return refIndex.compareRows(oldRow, newRow) == 0;
397 }
398
399 private void checkRow(Session session, Row oldRow) {
400 SearchRow check = table.getTemplateSimpleRow(false);
401 for (int i = 0, len = columns.length; i < len; i++) {
402 Column refCol = refColumns[i].column;
403 int refIdx = refCol.getColumnId();
404 Column col = columns[i].column;
405 Value v = col.convert(oldRow.getValue(refIdx));
406 if (v == ValueNull.INSTANCE) {
407 return;
408 }
409 check.setValue(col.getColumnId(), v);
410 }
411 // exclude the row only for self-referencing constraints
412 Row excluding = (refTable == table) ? oldRow : null;
413 if (existsRow(session, index, check, excluding)) {
414 throw DbException.get(ErrorCode.REFERENTIAL_INTEGRITY_VIOLATED_CHILD_EXISTS_1,
415 getShortDescription(index, check));
416 }
417 }
418
419 private void checkRowRefTable(Session session, Row oldRow, Row newRow) {
420 if (oldRow == null) {
421 // this is an insert
422 return;
423 }
424 if (newRow != null && isEqual(oldRow, newRow)) {
425 // on an update, if both old and new are the same, don't do anything
426 return;
427 }
428 if (newRow == null) {
429 // this is a delete
430 if (deleteAction == RESTRICT) {
431 checkRow(session, oldRow);
432 } else {
433 int i = deleteAction == CASCADE ? 0 : columns.length;
434 Prepared deleteCommand = getDelete(session);
435 setWhere(deleteCommand, i, oldRow);
436 updateWithSkipCheck(deleteCommand);
437 }
438 } else {
439 // this is an update
440 if (updateAction == RESTRICT) {
441 checkRow(session, oldRow);
442 } else {
443 Prepared updateCommand = getUpdate(session);
444 if (updateAction == CASCADE) {
445 ArrayList<Parameter> params = updateCommand.getParameters();
446 for (int i = 0, len = columns.length; i < len; i++) {
447 Parameter param = params.get(i);
448 Column refCol = refColumns[i].column;
449 param.setValue(newRow.getValue(refCol.getColumnId()));
450 }
451 }
452 setWhere(updateCommand, columns.length, oldRow);
453 updateWithSkipCheck(updateCommand);
454 }
455 }
456 }
457
458 private void updateWithSkipCheck(Prepared prep) {
459 // TODO constraints: maybe delay the update or support delayed checks
460 // (until commit)
461 try {
462 // TODO multithreaded kernel: this works only if nobody else updates
463 // this or the ref table at the same time
464 skipOwnTable = true;
465 prep.update();
466 } finally {
467 skipOwnTable = false;
468 }
469 }
470
471 private void setWhere(Prepared command, int pos, Row row) {
472 for (int i = 0, len = refColumns.length; i < len; i++) {
473 int idx = refColumns[i].column.getColumnId();
474 Value v = row.getValue(idx);
475 ArrayList<Parameter> params = command.getParameters();
476 Parameter param = params.get(pos + i);
477 param.setValue(v);
478 }
479 }
480
481 public int getDeleteAction() {
482 return deleteAction;
483 }
484
485 /**
486 * Set the action to apply (restrict, cascade,...) on a delete.
487 *
488 * @param action the action
489 */
490 public void setDeleteAction(int action) {
491 if (action == deleteAction && deleteSQL == null) {
492 return;
493 }
494 if (deleteAction != RESTRICT) {
495 throw DbException.get(ErrorCode.CONSTRAINT_ALREADY_EXISTS_1, "ON DELETE");
496 }
497 this.deleteAction = action;
498 buildDeleteSQL();
499 }
500
501 private void buildDeleteSQL() {
502 if (deleteAction == RESTRICT) {
503 return;
504 }
505 StatementBuilder buff = new StatementBuilder();
506 if (deleteAction == CASCADE) {
507 buff.append("DELETE FROM ").append(table.getSQL());
508 } else {
509 appendUpdate(buff);
510 }
511 appendWhere(buff);
512 deleteSQL = buff.toString();
513 }
514
515 private Prepared getUpdate(Session session) {
516 return prepare(session, updateSQL, updateAction);
517 }
518
519 private Prepared getDelete(Session session) {
520 return prepare(session, deleteSQL, deleteAction);
521 }
522
523 public int getUpdateAction() {
524 return updateAction;
525 }
526
527 /**
528 * Set the action to apply (restrict, cascade,...) on an update.
529 *
530 * @param action the action
531 */
532 public void setUpdateAction(int action) {
533 if (action == updateAction && updateSQL == null) {
534 return;
535 }
536 if (updateAction != RESTRICT) {
537 throw DbException.get(ErrorCode.CONSTRAINT_ALREADY_EXISTS_1, "ON UPDATE");
538 }
539 this.updateAction = action;
540 buildUpdateSQL();
541 }
542
543 private void buildUpdateSQL() {
544 if (updateAction == RESTRICT) {
545 return;
546 }
547 StatementBuilder buff = new StatementBuilder();
548 appendUpdate(buff);
549 appendWhere(buff);
550 updateSQL = buff.toString();
551 }
552
553 public void rebuild() {
554 buildUpdateSQL();
555 buildDeleteSQL();
556 }
557
558 private Prepared prepare(Session session, String sql, int action) {
559 Prepared command = session.prepare(sql);
560 if (action != CASCADE) {
561 ArrayList<Parameter> params = command.getParameters();
562 for (int i = 0, len = columns.length; i < len; i++) {
563 Column column = columns[i].column;
564 Parameter param = params.get(i);
565 Value value;
566 if (action == SET_NULL) {
567 value = ValueNull.INSTANCE;
568 } else {
569 Expression expr = column.getDefaultExpression();
570 if (expr == null) {
571 throw DbException.get(ErrorCode.NO_DEFAULT_SET_1, column.getName());
572 }
573 value = expr.getValue(session);
574 }
575 param.setValue(value);
576 }
577 }
578 return command;
579 }
580
581 private void appendUpdate(StatementBuilder buff) {
582 buff.append("UPDATE ").append(table.getSQL()).append(" SET ");
583 buff.resetCount();
584 for (IndexColumn c : columns) {
585 buff.appendExceptFirst(" , ");
586 buff.append(Parser.quoteIdentifier(c.column.getName())).append("=?");
587 }
588 }
589
590 private void appendWhere(StatementBuilder buff) {
591 buff.append(" WHERE ");
592 buff.resetCount();
593 for (IndexColumn c : columns) {
594 buff.appendExceptFirst(" AND ");
595 buff.append(Parser.quoteIdentifier(c.column.getName())).append("=?");
596 }
597 }
598
599 public Table getRefTable() {
600 return refTable;
601 }
602
603 public boolean usesIndex(Index idx) {
604 return idx == index || idx == refIndex;
605 }
606
607 public void setIndexOwner(Index index) {
608 if (this.index == index) {
609 indexOwner = true;
610 } else if (this.refIndex == index) {
611 refIndexOwner = true;
612 } else {
613 DbException.throwInternalError();
614 }
615 }
616
617 public boolean isBefore() {
618 return false;
619 }
620
621 public void checkExistingData(Session session) {
622 if (session.getDatabase().isStarting()) {
623 // don't check at startup
624 return;
625 }
626 StatementBuilder buff = new StatementBuilder("SELECT 1 FROM (SELECT ");
627 for (IndexColumn c : columns) {
628 buff.appendExceptFirst(", ");
629 buff.append(c.getSQL());
630 }
631 buff.append(" FROM ").append(table.getSQL()).append(" WHERE ");
632 buff.resetCount();
633 for (IndexColumn c : columns) {
634 buff.appendExceptFirst(" AND ");
635 buff.append(c.getSQL()).append(" IS NOT NULL ");
636 }
637 buff.append(" ORDER BY ");
638 buff.resetCount();
639 for (IndexColumn c : columns) {
640 buff.appendExceptFirst(", ");
641 buff.append(c.getSQL());
642 }
643 buff.append(") C WHERE NOT EXISTS(SELECT 1 FROM ").
644 append(refTable.getSQL()).append(" P WHERE ");
645 buff.resetCount();
646 int i = 0;
647 for (IndexColumn c : columns) {
648 buff.appendExceptFirst(" AND ");
649 buff.append("C.").append(c.getSQL()).append('=').
650 append("P.").append(refColumns[i++].getSQL());
651 }
652 buff.append(')');
653 String sql = buff.toString();
654 ResultInterface r = session.prepare(sql).query(1);
655 if (r.next()) {
656 throw DbException.get(ErrorCode.REFERENTIAL_INTEGRITY_VIOLATED_PARENT_MISSING_1,
657 getShortDescription(null, null));
658 }
659 }
660
661 public Index getUniqueIndex() {
662 return refIndex;
663 }
664
665 }
666