All Downloads are FREE. Search and download functionalities are using the official Maven repository.

org.h2.constraint.ConstraintReferential Maven / Gradle / Ivy

The newest version!
/*
 * Copyright 2004-2023 H2 Group. Multiple-Licensed under the MPL 2.0,
 * and the EPL 1.0 (https://h2database.com/html/license.html).
 * Initial Developer: H2 Group
 */
package org.h2.constraint;

import java.util.ArrayList;
import java.util.HashSet;
import org.h2.api.ErrorCode;
import org.h2.command.Prepared;
import org.h2.engine.SessionLocal;
import org.h2.expression.Expression;
import org.h2.expression.Parameter;
import org.h2.index.Cursor;
import org.h2.index.Index;
import org.h2.message.DbException;
import org.h2.result.ResultInterface;
import org.h2.result.Row;
import org.h2.result.SearchRow;
import org.h2.schema.Schema;
import org.h2.table.Column;
import org.h2.table.IndexColumn;
import org.h2.table.Table;
import org.h2.util.StringUtils;
import org.h2.value.Value;
import org.h2.value.ValueNull;

/**
 * A referential constraint.
 */
public class ConstraintReferential extends Constraint {

    private IndexColumn[] columns;
    private IndexColumn[] refColumns;
    private ConstraintActionType deleteAction = ConstraintActionType.RESTRICT;
    private ConstraintActionType updateAction = ConstraintActionType.RESTRICT;
    private Table refTable;
    private Index index;
    private ConstraintUnique refConstraint;
    private boolean indexOwner;
    private String deleteSQL, updateSQL;
    private boolean skipOwnTable;

    public ConstraintReferential(Schema schema, int id, String name, Table table) {
        super(schema, id, name, table);
    }

    @Override
    public Type getConstraintType() {
        return Constraint.Type.REFERENTIAL;
    }

    /**
     * Create the SQL statement of this object so a copy of the table can be
     * made.
     *
     * @param forTable the table to create the object for
     * @param quotedName the name of this object (quoted if necessary)
     * @return the SQL statement
     */
    @Override
    public String getCreateSQLForCopy(Table forTable, String quotedName) {
        return getCreateSQLForCopy(forTable, refTable, quotedName, true);
    }

    /**
     * Create the SQL statement of this object so a copy of the table can be
     * made.
     *
     * @param forTable the table to create the object for
     * @param forRefTable the referenced table
     * @param quotedName the name of this object (quoted if necessary)
     * @param internalIndex add the index name to the statement
     * @return the SQL statement
     */
    public String getCreateSQLForCopy(Table forTable, Table forRefTable,
            String quotedName, boolean internalIndex) {
        StringBuilder builder = new StringBuilder("ALTER TABLE ");
        forTable.getSQL(builder, DEFAULT_SQL_FLAGS).append(" ADD CONSTRAINT ");
        if (forTable.isHidden()) {
            builder.append("IF NOT EXISTS ");
        }
        builder.append(quotedName);
        if (comment != null) {
            builder.append(" COMMENT ");
            StringUtils.quoteStringSQL(builder, comment);
        }
        IndexColumn[] cols = columns;
        IndexColumn[] refCols = refColumns;
        builder.append(" FOREIGN KEY(");
        IndexColumn.writeColumns(builder, cols, DEFAULT_SQL_FLAGS);
        builder.append(')');
        if (internalIndex && indexOwner && forTable == this.table) {
            builder.append(" INDEX ");
            index.getSQL(builder, DEFAULT_SQL_FLAGS);
        }
        builder.append(" REFERENCES ");
        if (this.table == this.refTable) {
            // self-referencing constraints: need to use new table
            forTable.getSQL(builder, DEFAULT_SQL_FLAGS);
        } else {
            forRefTable.getSQL(builder, DEFAULT_SQL_FLAGS);
        }
        builder.append('(');
        IndexColumn.writeColumns(builder, refCols, DEFAULT_SQL_FLAGS);
        builder.append(')');
        if (deleteAction != ConstraintActionType.RESTRICT) {
            builder.append(" ON DELETE ").append(deleteAction.getSqlName());
        }
        if (updateAction != ConstraintActionType.RESTRICT) {
            builder.append(" ON UPDATE ").append(updateAction.getSqlName());
        }
        return builder.append(" NOCHECK").toString();
    }


    /**
     * Get a short description of the constraint. This includes the constraint
     * name (if set), and the constraint expression.
     *
     * @param searchIndex the index, or null
     * @param check the row, or null
     * @return the description
     */
    private String getShortDescription(Index searchIndex, SearchRow check) {
        StringBuilder builder = new StringBuilder(getName()).append(": ");
        table.getSQL(builder, TRACE_SQL_FLAGS).append(" FOREIGN KEY(");
        IndexColumn.writeColumns(builder, columns, TRACE_SQL_FLAGS);
        builder.append(") REFERENCES ");
        refTable.getSQL(builder, TRACE_SQL_FLAGS).append('(');
        IndexColumn.writeColumns(builder, refColumns, TRACE_SQL_FLAGS);
        builder.append(')');
        if (searchIndex != null && check != null) {
            builder.append(" (");
            Column[] cols = searchIndex.getColumns();
            int len = Math.min(columns.length, cols.length);
            for (int i = 0; i < len; i++) {
                int idx = cols[i].getColumnId();
                Value c = check.getValue(idx);
                if (i > 0) {
                    builder.append(", ");
                }
                builder.append(c == null ? "" : c.toString());
            }
            builder.append(')');
        }
        return builder.toString();
    }

    @Override
    public String getCreateSQLWithoutIndexes() {
        return getCreateSQLForCopy(table, refTable, getSQL(DEFAULT_SQL_FLAGS), false);
    }

    @Override
    public String getCreateSQL() {
        return getCreateSQLForCopy(table, getSQL(DEFAULT_SQL_FLAGS));
    }

    public void setColumns(IndexColumn[] cols) {
        columns = cols;
    }

    public IndexColumn[] getColumns() {
        return columns;
    }

    @Override
    public HashSet getReferencedColumns(Table table) {
        HashSet result = new HashSet<>();
        if (table == this.table) {
            for (IndexColumn c : columns) {
                result.add(c.column);
            }
        } else if (table == this.refTable) {
            for (IndexColumn c : refColumns) {
                result.add(c.column);
            }
        }
        return result;
    }

    public void setRefColumns(IndexColumn[] refCols) {
        refColumns = refCols;
    }

    public IndexColumn[] getRefColumns() {
        return refColumns;
    }

    public void setRefTable(Table refTable) {
        this.refTable = refTable;
        if (refTable.isTemporary()) {
            setTemporary(true);
        }
    }

    /**
     * Set the index to use for this constraint.
     *
     * @param index the index
     * @param isOwner true if the index is generated by the system and belongs
     *            to this constraint
     */
    public void setIndex(Index index, boolean isOwner) {
        this.index = index;
        this.indexOwner = isOwner;
    }

    /**
     * Set the unique constraint of the referenced table to use for this
     * constraint.
     *
     * @param refConstraint
     *            the unique constraint
     */
    public void setRefConstraint(ConstraintUnique refConstraint) {
        this.refConstraint = refConstraint;
    }

    @Override
    public void removeChildrenAndResources(SessionLocal session) {
        table.removeConstraint(this);
        refTable.removeConstraint(this);
        if (indexOwner) {
            table.removeIndexOrTransferOwnership(session, index);
        }
        database.removeMeta(session, getId());
        refTable = null;
        index = null;
        refConstraint = null;
        columns = null;
        refColumns = null;
        deleteSQL = null;
        updateSQL = null;
        table = null;
        invalidate();
    }

    @Override
    public void checkRow(SessionLocal session, Table t, Row oldRow, Row newRow) {
        if (!database.getReferentialIntegrity()) {
            return;
        }
        if (!table.getCheckForeignKeyConstraints() ||
                !refTable.getCheckForeignKeyConstraints()) {
            return;
        }
        if (t == table) {
            if (!skipOwnTable) {
                checkRowOwnTable(session, oldRow, newRow);
            }
        }
        if (t == refTable) {
            checkRowRefTable(session, oldRow, newRow);
        }
    }

    private void checkRowOwnTable(SessionLocal session, Row oldRow, Row newRow) {
        if (newRow == null) {
            return;
        }
        boolean constraintColumnsEqual = oldRow != null;
        for (IndexColumn col : columns) {
            int idx = col.column.getColumnId();
            Value v = newRow.getValue(idx);
            if (v == ValueNull.INSTANCE) {
                // return early if one of the columns is NULL
                return;
            }
            if (constraintColumnsEqual) {
                if (!session.areEqual(v, oldRow.getValue(idx))) {
                    constraintColumnsEqual = false;
                }
            }
        }
        if (constraintColumnsEqual) {
            // return early if the key columns didn't change
            return;
        }
        if (refTable == table) {
            // special case self referencing constraints:
            // check the inserted row first
            boolean self = true;
            for (int i = 0, len = columns.length; i < len; i++) {
                int idx = columns[i].column.getColumnId();
                Value v = newRow.getValue(idx);
                Column refCol = refColumns[i].column;
                int refIdx = refCol.getColumnId();
                Value r = newRow.getValue(refIdx);
                if (!session.areEqual(r, v)) {
                    self = false;
                    break;
                }
            }
            if (self) {
                return;
            }
        }
        Row check = refTable.getTemplateRow();
        for (int i = 0, len = columns.length; i < len; i++) {
            int idx = columns[i].column.getColumnId();
            Value v = newRow.getValue(idx);
            Column refCol = refColumns[i].column;
            int refIdx = refCol.getColumnId();
            check.setValue(refIdx, refCol.convert(session, v));
        }
        Index refIndex = refConstraint.getIndex();
        if (!existsRow(session, refIndex, check, null)) {
            throw DbException.get(ErrorCode.REFERENTIAL_INTEGRITY_VIOLATED_PARENT_MISSING_1,
                    getShortDescription(refIndex, check));
        }
    }

    private boolean existsRow(SessionLocal session, Index searchIndex,
            SearchRow check, Row excluding) {
        Table searchTable = searchIndex.getTable();
        searchTable.lock(session, Table.READ_LOCK);
        Cursor cursor = searchIndex.find(session, check, check);
        while (cursor.next()) {
            SearchRow found;
            found = cursor.getSearchRow();
            if (excluding != null && found.getKey() == excluding.getKey()) {
                continue;
            }
            Column[] cols = searchIndex.getColumns();
            boolean allEqual = true;
            int len = Math.min(columns.length, cols.length);
            for (int i = 0; i < len; i++) {
                int idx = cols[i].getColumnId();
                Value c = check.getValue(idx);
                Value f = found.getValue(idx);
                if (searchTable.compareValues(session, c, f) != 0) {
                    allEqual = false;
                    break;
                }
            }
            if (allEqual) {
                return true;
            }
        }
        return false;
    }

    private boolean isEqual(Row oldRow, Row newRow) {
        return refConstraint.getIndex().compareRows(oldRow, newRow) == 0;
    }

    private void checkRow(SessionLocal session, Row oldRow) {
        SearchRow check = table.getRowFactory().createRow();
        for (int i = 0, len = columns.length; i < len; i++) {
            Column refCol = refColumns[i].column;
            int refIdx = refCol.getColumnId();
            Column col = columns[i].column;
            Value v = col.convert(session, oldRow.getValue(refIdx));
            if (v == ValueNull.INSTANCE) {
                return;
            }
            check.setValue(col.getColumnId(), v);
        }
        // exclude the row only for self-referencing constraints
        Row excluding = (refTable == table) ? oldRow : null;
        if (existsRow(session, index, check, excluding)) {
            throw DbException.get(ErrorCode.REFERENTIAL_INTEGRITY_VIOLATED_CHILD_EXISTS_1,
                    getShortDescription(index, check));
        }
    }

    private void checkRowRefTable(SessionLocal session, Row oldRow, Row newRow) {
        if (oldRow == null) {
            // this is an insert
            return;
        }
        if (newRow != null && isEqual(oldRow, newRow)) {
            // on an update, if both old and new are the same, don't do anything
            return;
        }
        if (newRow == null) {
            // this is a delete
            if (deleteAction == ConstraintActionType.RESTRICT) {
                checkRow(session, oldRow);
            } else {
                int i = deleteAction == ConstraintActionType.CASCADE ? 0 : columns.length;
                Prepared deleteCommand = getDelete(session);
                setWhere(deleteCommand, i, oldRow);
                updateWithSkipCheck(deleteCommand);
            }
        } else {
            // this is an update
            if (updateAction == ConstraintActionType.RESTRICT) {
                checkRow(session, oldRow);
            } else {
                Prepared updateCommand = getUpdate(session);
                if (updateAction == ConstraintActionType.CASCADE) {
                    ArrayList params = updateCommand.getParameters();
                    for (int i = 0, len = columns.length; i < len; i++) {
                        Parameter param = params.get(i);
                        Column refCol = refColumns[i].column;
                        param.setValue(newRow.getValue(refCol.getColumnId()));
                    }
                }
                setWhere(updateCommand, columns.length, oldRow);
                updateWithSkipCheck(updateCommand);
            }
        }
    }

    private void updateWithSkipCheck(Prepared prep) {
        // TODO constraints: maybe delay the update or support delayed checks
        // (until commit)
        try {
            // TODO multithreaded kernel: this works only if nobody else updates
            // this or the ref table at the same time
            skipOwnTable = true;
            prep.update();
        } finally {
            skipOwnTable = false;
        }
    }

    private void setWhere(Prepared command, int pos, Row row) {
        for (int i = 0, len = refColumns.length; i < len; i++) {
            int idx = refColumns[i].column.getColumnId();
            Value v = row.getValue(idx);
            ArrayList params = command.getParameters();
            Parameter param = params.get(pos + i);
            param.setValue(v);
        }
    }

    public ConstraintActionType getDeleteAction() {
        return deleteAction;
    }

    /**
     * Set the action to apply (restrict, cascade,...) on a delete.
     *
     * @param action the action
     */
    public void setDeleteAction(ConstraintActionType action) {
        if (action == deleteAction && deleteSQL == null) {
            return;
        }
        if (deleteAction != ConstraintActionType.RESTRICT) {
            throw DbException.get(ErrorCode.CONSTRAINT_ALREADY_EXISTS_1, "ON DELETE");
        }
        this.deleteAction = action;
        buildDeleteSQL();
    }

    /**
     * Update the constraint SQL when a referenced column is renamed.
     */
    public void updateOnTableColumnRename() {
        if (deleteAction != null) {
            deleteSQL = null;
            buildDeleteSQL();
        }
        if (updateAction != null) {
            updateSQL = null;
            buildUpdateSQL();
        }
    }

    private void buildDeleteSQL() {
        if (deleteAction == ConstraintActionType.RESTRICT) {
            return;
        }
        StringBuilder builder = new StringBuilder();
        if (deleteAction == ConstraintActionType.CASCADE) {
            builder.append("DELETE FROM ");
            table.getSQL(builder, DEFAULT_SQL_FLAGS);
        } else {
            appendUpdate(builder);
        }
        appendWhere(builder);
        deleteSQL = builder.toString();
    }

    private Prepared getUpdate(SessionLocal session) {
        return prepare(session, updateSQL, updateAction);
    }

    private Prepared getDelete(SessionLocal session) {
        return prepare(session, deleteSQL, deleteAction);
    }

    public ConstraintActionType getUpdateAction() {
        return updateAction;
    }

    /**
     * Set the action to apply (restrict, cascade,...) on an update.
     *
     * @param action the action
     */
    public void setUpdateAction(ConstraintActionType action) {
        if (action == updateAction && updateSQL == null) {
            return;
        }
        if (updateAction != ConstraintActionType.RESTRICT) {
            throw DbException.get(ErrorCode.CONSTRAINT_ALREADY_EXISTS_1, "ON UPDATE");
        }
        this.updateAction = action;
        buildUpdateSQL();
    }

    private void buildUpdateSQL() {
        if (updateAction == ConstraintActionType.RESTRICT) {
            return;
        }
        StringBuilder builder = new StringBuilder();
        appendUpdate(builder);
        appendWhere(builder);
        updateSQL = builder.toString();
    }

    @Override
    public void rebuild() {
        buildUpdateSQL();
        buildDeleteSQL();
    }

    private Prepared prepare(SessionLocal session, String sql, ConstraintActionType action) {
        Prepared command = session.prepare(sql);
        if (action != ConstraintActionType.CASCADE) {
            ArrayList params = command.getParameters();
            for (int i = 0, len = columns.length; i < len; i++) {
                Column column = columns[i].column;
                Parameter param = params.get(i);
                Value value;
                if (action == ConstraintActionType.SET_NULL) {
                    value = ValueNull.INSTANCE;
                } else {
                    Expression expr = column.getEffectiveDefaultExpression();
                    if (expr == null) {
                        throw DbException.get(ErrorCode.NO_DEFAULT_SET_1, column.getName());
                    }
                    value = expr.getValue(session);
                }
                param.setValue(value);
            }
        }
        return command;
    }

    private void appendUpdate(StringBuilder builder) {
        builder.append("UPDATE ");
        table.getSQL(builder, DEFAULT_SQL_FLAGS).append(" SET ");
        IndexColumn.writeColumns(builder, columns, ", ", "=?", IndexColumn.SQL_NO_ORDER);
    }

    private void appendWhere(StringBuilder builder) {
        builder.append(" WHERE ");
        IndexColumn.writeColumns(builder, columns, " AND ", "=?", IndexColumn.SQL_NO_ORDER);
    }

    @Override
    public Table getRefTable() {
        return refTable;
    }

    @Override
    public boolean usesIndex(Index idx) {
        return idx == index;
    }

    @Override
    public void setIndexOwner(Index index) {
        if (this.index == index) {
            indexOwner = true;
        } else {
            throw DbException.getInternalError(index + " " + toString());
        }
    }

    @Override
    public boolean isBefore() {
        return false;
    }

    @Override
    public void checkExistingData(SessionLocal session) {
        if (session.getDatabase().isStarting()) {
            // don't check at startup
            return;
        }
        StringBuilder builder = new StringBuilder("SELECT 1 FROM (SELECT ");
        IndexColumn.writeColumns(builder, columns, IndexColumn.SQL_NO_ORDER);
        builder.append(" FROM ");
        table.getSQL(builder, DEFAULT_SQL_FLAGS).append(" WHERE ");
        IndexColumn.writeColumns(builder, columns, " AND ", " IS NOT NULL ", IndexColumn.SQL_NO_ORDER);
        builder.append(" ORDER BY ");
        IndexColumn.writeColumns(builder, columns, DEFAULT_SQL_FLAGS);
        builder.append(") C WHERE NOT EXISTS(SELECT 1 FROM ");
        refTable.getSQL(builder, DEFAULT_SQL_FLAGS).append(" P WHERE ");
        for (int i = 0, l = columns.length; i < l; i++) {
            if (i > 0) {
                builder.append(" AND ");
            }
            builder.append("C.");
            columns[i].column.getSQL(builder, DEFAULT_SQL_FLAGS).append('=').append("P.");
            refColumns[i].column.getSQL(builder, DEFAULT_SQL_FLAGS);
        }
        builder.append(')');

        session.startStatementWithinTransaction(null);
        try {
            ResultInterface r = session.prepare(builder.toString()).query(1);
            if (r.next()) {
                throw DbException.get(ErrorCode.REFERENTIAL_INTEGRITY_VIOLATED_PARENT_MISSING_1,
                        getShortDescription(null, null));
            }
        } finally {
            session.endStatement();
        }
    }

    @Override
    public Index getIndex() {
        return index;
    }

    @Override
    public ConstraintUnique getReferencedConstraint() {
        return refConstraint;
    }

}




© 2015 - 2024 Weber Informatics LLC | Privacy Policy