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.constraint;
007:
008: import java.sql.SQLException;
009:
010: import org.h2.command.Parser;
011: import org.h2.command.Prepared;
012: import org.h2.constant.ErrorCode;
013: import org.h2.engine.Session;
014: import org.h2.expression.Expression;
015: import org.h2.expression.Parameter;
016: import org.h2.index.Cursor;
017: import org.h2.index.Index;
018: import org.h2.message.Message;
019: import org.h2.result.LocalResult;
020: import org.h2.result.Row;
021: import org.h2.result.SearchRow;
022: import org.h2.schema.Schema;
023: import org.h2.table.Column;
024: import org.h2.table.IndexColumn;
025: import org.h2.table.Table;
026: import org.h2.util.ObjectArray;
027: import org.h2.util.StringUtils;
028: import org.h2.value.Value;
029: import org.h2.value.ValueNull;
030:
031: /**
032: * A referential constraint.
033: */
034: public class ConstraintReferential extends Constraint {
035: public static final int RESTRICT = 0, CASCADE = 1, SET_DEFAULT = 2,
036: SET_NULL = 3;
037:
038: private int deleteAction;
039: private int updateAction;
040: private Table refTable;
041: private Index index;
042: private Index refIndex;
043: private boolean indexOwner;
044: private boolean refIndexOwner;
045: protected IndexColumn[] columns;
046: protected IndexColumn[] refColumns;
047: private String deleteSQL, updateSQL;
048: private boolean skipOwnTable;
049:
050: public ConstraintReferential(Schema schema, int id, String name,
051: Table table) {
052: super (schema, id, name, table);
053: }
054:
055: public String getConstraintType() {
056: return Constraint.REFERENTIAL;
057: }
058:
059: private void appendAction(StringBuffer buff, int action) {
060: switch (action) {
061: case CASCADE:
062: buff.append("CASCADE");
063: break;
064: case SET_DEFAULT:
065: buff.append("SET DEFAULT");
066: break;
067: case SET_NULL:
068: buff.append("SET NULL");
069: break;
070: default:
071: throw Message.getInternalError("action=" + action);
072: }
073: }
074:
075: /**
076: * Create the SQL statement of this object so a copy of the table can be made.
077: *
078: * @param table the table to create the object for
079: * @param quotedName the name of this object (quoted if necessary)
080: * @return the SQL statement
081: */
082: public String getCreateSQLForCopy(Table table, String quotedName) {
083: return getCreateSQLForCopy(table, refTable, quotedName, true);
084: }
085:
086: /**
087: * Create the SQL statement of this object so a copy of the table can be made.
088: *
089: * @param table the table to create the object for
090: * @param refTable the referenced table
091: * @param quotedName the name of this object (quoted if necessary)
092: * @param internalIndex add the index name to the statement
093: * @return the SQL statement
094: */
095: public String getCreateSQLForCopy(Table table, Table refTable,
096: String quotedName, boolean internalIndex) {
097: StringBuffer buff = new StringBuffer();
098: buff.append("ALTER TABLE ");
099: String mainTable = table.getSQL();
100: buff.append(mainTable);
101: buff.append(" ADD CONSTRAINT ");
102: buff.append(quotedName);
103: if (comment != null) {
104: buff.append(" COMMENT ");
105: buff.append(StringUtils.quoteStringSQL(comment));
106: }
107: IndexColumn[] cols = columns;
108: IndexColumn[] refCols = refColumns;
109: buff.append(" FOREIGN KEY(");
110: for (int i = 0; i < cols.length; i++) {
111: if (i > 0) {
112: buff.append(", ");
113: }
114: buff.append(cols[i].getSQL());
115: }
116: buff.append(")");
117: if (internalIndex && indexOwner && table == this .table) {
118: buff.append(" INDEX ");
119: buff.append(index.getSQL());
120: }
121: buff.append(" REFERENCES ");
122: String quotedRefTable;
123: if (this .table == this .refTable) {
124: // self-referencing constraints: need to use new table
125: quotedRefTable = table.getSQL();
126: } else {
127: quotedRefTable = refTable.getSQL();
128: }
129: buff.append(quotedRefTable);
130: buff.append("(");
131: for (int i = 0; i < refCols.length; i++) {
132: if (i > 0) {
133: buff.append(", ");
134: }
135: buff.append(refCols[i].getSQL());
136: }
137: buff.append(")");
138: if (internalIndex && refIndexOwner && table == this .table) {
139: buff.append(" INDEX ");
140: buff.append(refIndex.getSQL());
141: }
142: if (deleteAction != RESTRICT) {
143: buff.append(" ON DELETE ");
144: appendAction(buff, deleteAction);
145: }
146: if (updateAction != RESTRICT) {
147: buff.append(" ON UPDATE ");
148: appendAction(buff, updateAction);
149: }
150: buff.append(" NOCHECK");
151: return buff.toString();
152: }
153:
154: public String getShortDescription() {
155: StringBuffer buff = new StringBuffer();
156: buff.append(getName());
157: buff.append(": ");
158: buff.append(table.getSQL());
159: buff.append(" FOREIGN KEY(");
160: for (int i = 0; i < columns.length; i++) {
161: if (i > 0) {
162: buff.append(", ");
163: }
164: buff.append(columns[i].getSQL());
165: }
166: buff.append(")");
167: buff.append(" REFERENCES ");
168: buff.append(refTable.getSQL());
169: buff.append("(");
170: for (int i = 0; i < refColumns.length; i++) {
171: if (i > 0) {
172: buff.append(", ");
173: }
174: buff.append(refColumns[i].getSQL());
175: }
176: buff.append(")");
177: return buff.toString();
178: }
179:
180: public String getCreateSQLWithoutIndexes() {
181: return getCreateSQLForCopy(table, refTable, getSQL(), false);
182: }
183:
184: public String getCreateSQL() {
185: return getCreateSQLForCopy(table, getSQL());
186: }
187:
188: public void setColumns(IndexColumn[] cols) {
189: columns = cols;
190: }
191:
192: public IndexColumn[] getColumns() {
193: return columns;
194: }
195:
196: public void setRefColumns(IndexColumn[] refCols) {
197: refColumns = refCols;
198: }
199:
200: public IndexColumn[] getRefColumns() {
201: return refColumns;
202: }
203:
204: public void setRefTable(Table refTable) {
205: this .refTable = refTable;
206: if (refTable.getTemporary()) {
207: setTemporary(true);
208: }
209: }
210:
211: public void setIndex(Index index, boolean isOwner) {
212: this .index = index;
213: this .indexOwner = isOwner;
214: }
215:
216: public void setRefIndex(Index refIndex, boolean isRefOwner) {
217: this .refIndex = refIndex;
218: this .refIndexOwner = isRefOwner;
219: }
220:
221: public void removeChildrenAndResources(Session session)
222: throws SQLException {
223: table.removeConstraint(this );
224: refTable.removeConstraint(this );
225: if (indexOwner) {
226: table.removeIndexOrTransferOwnership(session, index);
227: }
228: if (refIndexOwner) {
229: refTable.removeIndexOrTransferOwnership(session, refIndex);
230: }
231: database.removeMeta(session, getId());
232: refTable = null;
233: index = null;
234: refIndex = null;
235: columns = null;
236: refColumns = null;
237: deleteSQL = null;
238: updateSQL = null;
239: table = null;
240: invalidate();
241: }
242:
243: public void checkRow(Session session, Table t, Row oldRow,
244: Row newRow) throws SQLException {
245: if (!database.getReferentialIntegrity()) {
246: return;
247: }
248: if (!table.getCheckForeignKeyConstraints()
249: || !refTable.getCheckForeignKeyConstraints()) {
250: return;
251: }
252: if (t == table) {
253: if (!skipOwnTable) {
254: checkRowOwnTable(session, newRow);
255: }
256: }
257: if (t == refTable) {
258: checkRowRefTable(session, oldRow, newRow);
259: }
260: }
261:
262: private void checkRowOwnTable(Session session, Row newRow)
263: throws SQLException {
264: if (newRow == null) {
265: return;
266: }
267: boolean containsNull = false;
268: for (int i = 0; i < columns.length; i++) {
269: int idx = columns[i].column.getColumnId();
270: Value v = newRow.getValue(idx);
271: if (v == ValueNull.INSTANCE) {
272: containsNull = true;
273: break;
274: }
275: }
276: if (containsNull) {
277: return;
278: }
279: if (refTable == table) {
280: // special case self referencing constraints: check the inserted row
281: // first
282: boolean self = true;
283: for (int i = 0; i < columns.length; i++) {
284: int idx = columns[i].column.getColumnId();
285: Value v = newRow.getValue(idx);
286: Column refCol = refColumns[i].column;
287: int refIdx = refCol.getColumnId();
288: Value r = newRow.getValue(refIdx);
289: if (!database.areEqual(r, v)) {
290: self = false;
291: break;
292: }
293: }
294: if (self) {
295: return;
296: }
297: }
298: Row check = refTable.getTemplateRow();
299: for (int i = 0; i < columns.length; i++) {
300: int idx = columns[i].column.getColumnId();
301: Value v = newRow.getValue(idx);
302: Column refCol = refColumns[i].column;
303: int refIdx = refCol.getColumnId();
304: check.setValue(refIdx, v.convertTo(refCol.getType()));
305: }
306: if (!found(session, refIndex, check)) {
307: throw Message
308: .getSQLException(
309: ErrorCode.REFERENTIAL_INTEGRITY_VIOLATED_PARENT_MISSING_1,
310: getShortDescription());
311: }
312: }
313:
314: private boolean found(Session session, Index index, SearchRow check)
315: throws SQLException {
316: index.getTable().lock(session, false, false);
317: Cursor cursor = index.find(session, check, check);
318: while (cursor.next()) {
319: SearchRow found;
320: found = cursor.getSearchRow();
321: Column[] cols = index.getColumns();
322: boolean allEqual = true;
323: for (int i = 0; i < columns.length && i < cols.length; i++) {
324: int idx = cols[i].getColumnId();
325: Value c = check.getValue(idx);
326: Value f = found.getValue(idx);
327: if (database.compareTypeSave(c, f) != 0) {
328: allEqual = false;
329: break;
330: }
331: }
332: if (allEqual) {
333: return true;
334: }
335: }
336: return false;
337: }
338:
339: private boolean isEqual(Row oldRow, Row newRow) throws SQLException {
340: return refIndex.compareRows(oldRow, newRow) == 0;
341: }
342:
343: private void checkRow(Session session, Row oldRow)
344: throws SQLException {
345: if (refTable == table) {
346: // special case self referencing constraints: check the deleted row
347: // first
348: boolean self = true;
349: for (int i = 0; i < columns.length; i++) {
350: Column refCol = refColumns[i].column;
351: int refIdx = refCol.getColumnId();
352: Value v = oldRow.getValue(refIdx);
353: int idx = columns[i].column.getColumnId();
354: Value r = oldRow.getValue(idx);
355: if (!database.areEqual(r, v)) {
356: self = false;
357: break;
358: }
359: }
360: if (self) {
361: return;
362: }
363: }
364: SearchRow check = table.getTemplateSimpleRow(false);
365: for (int i = 0; i < columns.length; i++) {
366: Column refCol = refColumns[i].column;
367: int refIdx = refCol.getColumnId();
368: Column col = columns[i].column;
369: int idx = col.getColumnId();
370: Value v = oldRow.getValue(refIdx).convertTo(col.getType());
371: check.setValue(idx, v);
372: }
373: if (found(session, index, check)) {
374: throw Message
375: .getSQLException(
376: ErrorCode.REFERENTIAL_INTEGRITY_VIOLATED_CHILD_EXISTS_1,
377: getShortDescription());
378: }
379: }
380:
381: private void checkRowRefTable(Session session, Row oldRow,
382: Row newRow) throws SQLException {
383: if (oldRow == null) {
384: // this is an insert
385: return;
386: }
387: if (newRow != null && isEqual(oldRow, newRow)) {
388: // on an update, if both old and new are the same, don't do anything
389: return;
390: }
391: if (newRow == null) {
392: // this is a delete
393: if (deleteAction == RESTRICT) {
394: checkRow(session, oldRow);
395: } else {
396: int i = deleteAction == CASCADE ? 0 : columns.length;
397: Prepared deleteCommand = getDelete(session);
398: setWhere(deleteCommand, i, oldRow);
399: updateWithSkipCheck(deleteCommand);
400: }
401: } else {
402: // this is an update
403: if (updateAction == RESTRICT) {
404: checkRow(session, oldRow);
405: } else {
406: Prepared updateCommand = getUpdate(session);
407: if (updateAction == CASCADE) {
408: ObjectArray params = updateCommand.getParameters();
409: for (int i = 0; i < columns.length; i++) {
410: Parameter param = (Parameter) params.get(i);
411: Column refCol = refColumns[i].column;
412: param.setValue(newRow.getValue(refCol
413: .getColumnId()));
414: }
415: }
416: setWhere(updateCommand, columns.length, oldRow);
417: updateWithSkipCheck(updateCommand);
418: }
419: }
420: }
421:
422: private void updateWithSkipCheck(Prepared prep) throws SQLException {
423: // TODO constraints: maybe delay the update or support delayed checks
424: // (until commit)
425: try {
426: // TODO multithreaded kernel: this works only if nobody else updates
427: // this or the ref table at the same time
428: skipOwnTable = true;
429: prep.update();
430: } finally {
431: skipOwnTable = false;
432: }
433: }
434:
435: void setWhere(Prepared command, int pos, Row row) {
436: for (int i = 0; i < refColumns.length; i++) {
437: int idx = refColumns[i].column.getColumnId();
438: Value v = row.getValue(idx);
439: ObjectArray params = command.getParameters();
440: Parameter param = (Parameter) params.get(pos + i);
441: param.setValue(v);
442: }
443: }
444:
445: public int getDeleteAction() {
446: return deleteAction;
447: }
448:
449: public void setDeleteAction(Session session, int action)
450: throws SQLException {
451: if (action == deleteAction) {
452: return;
453: }
454: if (deleteAction != RESTRICT) {
455: throw Message.getSQLException(
456: ErrorCode.CONSTRAINT_ALREADY_EXISTS_1, "ON DELETE");
457: }
458: this .deleteAction = action;
459: StringBuffer buff = new StringBuffer();
460: if (action == CASCADE) {
461: buff.append("DELETE FROM ");
462: buff.append(table.getSQL());
463: } else {
464: appendUpdate(buff);
465: }
466: appendWhere(buff);
467: deleteSQL = buff.toString();
468: }
469:
470: private Prepared getUpdate(Session session) throws SQLException {
471: return prepare(session, updateSQL, updateAction);
472: }
473:
474: private Prepared getDelete(Session session) throws SQLException {
475: return prepare(session, deleteSQL, deleteAction);
476: }
477:
478: public int getUpdateAction() {
479: return updateAction;
480: }
481:
482: public void setUpdateAction(Session session, int action)
483: throws SQLException {
484: if (action == updateAction) {
485: return;
486: }
487: if (updateAction != RESTRICT) {
488: throw Message.getSQLException(
489: ErrorCode.CONSTRAINT_ALREADY_EXISTS_1, "ON UPDATE");
490: }
491: this .updateAction = action;
492: StringBuffer buff = new StringBuffer();
493: appendUpdate(buff);
494: appendWhere(buff);
495: updateSQL = buff.toString();
496: }
497:
498: private Prepared prepare(Session session, String sql, int action)
499: throws SQLException {
500: Prepared command = session.prepare(sql);
501: if (action != CASCADE) {
502: ObjectArray params = command.getParameters();
503: for (int i = 0; i < columns.length; i++) {
504: Column column = columns[i].column;
505: Parameter param = (Parameter) params.get(i);
506: Value value;
507: if (action == SET_NULL) {
508: value = ValueNull.INSTANCE;
509: } else {
510: Expression expr = column.getDefaultExpression();
511: if (expr == null) {
512: throw Message.getSQLException(
513: ErrorCode.NO_DEFAULT_SET_1, column
514: .getName());
515: }
516: value = expr.getValue(session);
517: }
518: param.setValue(value);
519: }
520: }
521: return command;
522: }
523:
524: private void appendUpdate(StringBuffer buff) {
525: buff.append("UPDATE ");
526: buff.append(table.getSQL());
527: buff.append(" SET ");
528: for (int i = 0; i < columns.length; i++) {
529: if (i > 0) {
530: buff.append(" , ");
531: }
532: Column column = columns[i].column;
533: buff.append(Parser.quoteIdentifier(column.getName()));
534: buff.append("=?");
535: }
536: }
537:
538: private void appendWhere(StringBuffer buff) {
539: buff.append(" WHERE ");
540: for (int i = 0; i < columns.length; i++) {
541: if (i > 0) {
542: buff.append(" AND ");
543: }
544: Column column = columns[i].column;
545: buff.append(Parser.quoteIdentifier(column.getName()));
546: buff.append("=?");
547: }
548: }
549:
550: public Table getRefTable() {
551: return refTable;
552: }
553:
554: public boolean usesIndex(Index idx) {
555: return idx == index || idx == refIndex;
556: }
557:
558: public void setIndexOwner(Index index) {
559: if (this .index == index) {
560: indexOwner = true;
561: } else if (this .refIndex == index) {
562: refIndexOwner = true;
563: } else {
564: throw Message.getInternalError();
565: }
566: }
567:
568: public boolean containsColumn(Column col) {
569: for (int i = 0; i < columns.length; i++) {
570: if (columns[i].column == col) {
571: return true;
572: }
573: }
574: for (int i = 0; i < refColumns.length; i++) {
575: if (refColumns[i].column == col) {
576: return true;
577: }
578: }
579: return false;
580: }
581:
582: public boolean isBefore() {
583: return false;
584: }
585:
586: public void checkExistingData(Session session) throws SQLException {
587: if (session.getDatabase().isStarting()) {
588: // don't check at startup
589: return;
590: }
591: StringBuffer buff = new StringBuffer();
592: buff.append("SELECT 1 FROM (SELECT ");
593: for (int i = 0; i < columns.length; i++) {
594: if (i > 0) {
595: buff.append(", ");
596: }
597: buff.append(columns[i].getSQL());
598: }
599: buff.append(" FROM ");
600: buff.append(table.getSQL());
601: buff.append(" WHERE ");
602: for (int i = 0; i < columns.length; i++) {
603: if (i > 0) {
604: buff.append(" AND ");
605: }
606: buff.append(columns[i].getSQL());
607: buff.append(" IS NOT NULL ");
608: }
609: buff.append(" ORDER BY ");
610: for (int i = 0; i < columns.length; i++) {
611: if (i > 0) {
612: buff.append(", ");
613: }
614: buff.append(columns[i].getSQL());
615: }
616: buff.append(") C WHERE NOT EXISTS(SELECT 1 FROM ");
617: buff.append(refTable.getSQL());
618: buff.append(" P WHERE ");
619: for (int i = 0; i < columns.length; i++) {
620: if (i > 0) {
621: buff.append(" AND ");
622: }
623: buff.append("C.");
624: buff.append(columns[i].getSQL());
625: buff.append("=");
626: buff.append("P.");
627: buff.append(refColumns[i].getSQL());
628: }
629: buff.append(")");
630: String sql = buff.toString();
631: LocalResult r = session.prepare(sql).query(1);
632: if (r.next()) {
633: throw Message
634: .getSQLException(
635: ErrorCode.REFERENTIAL_INTEGRITY_VIOLATED_PARENT_MISSING_1,
636: getShortDescription());
637: }
638: }
639:
640: }
|