public static void CheckAddConstraintViolations(this ITransaction transaction, ITable table, int[] rowIndices, ConstraintDeferrability deferred) { string curSchema = table.TableInfo.TableName.Parent.Name; IQueryContext queryContext = new SystemQueryContext(transaction, curSchema); // Quick exit case if (rowIndices == null || rowIndices.Length == 0) return; var tableInfo = table.TableInfo; var tableName = tableInfo.TableName; // ---- Constraint checking ---- // Check any primary key constraint. var primaryKey = transaction.QueryTablePrimaryKey(tableName); if (primaryKey != null && (deferred == ConstraintDeferrability.InitiallyDeferred || primaryKey.Deferred == ConstraintDeferrability.InitiallyImmediate)) { // For each row added to this column foreach (int rowIndex in rowIndices) { if (!IsUniqueColumns(table, rowIndex, primaryKey.ColumnNames, false)) { throw new ConstraintViolationException( SqlModelErrorCodes.PrimaryKeyViolation, deferred.AsDebugString() + " primary Key constraint violation (" + primaryKey.ConstraintName + ") Columns = ( " + String.Join(", ", primaryKey.ColumnNames) + " ) Table = ( " + tableName + " )"); } } // For each row being added } // Check any unique constraints. var uniqueConstraints = transaction.QueryTableUniqueKeys(tableName); foreach (var unique in uniqueConstraints) { if (deferred == ConstraintDeferrability.InitiallyDeferred || unique.Deferred == ConstraintDeferrability.InitiallyImmediate) { // For each row added to this column foreach (int rowIndex in rowIndices) { if (!IsUniqueColumns(table, rowIndex, unique.ColumnNames, true)) { throw new ConstraintViolationException( SqlModelErrorCodes.UniqueViolation, deferred.AsDebugString() + " unique constraint violation (" + unique.ConstraintName + ") Columns = ( " + String.Join(", ", unique.ColumnNames) + " ) Table = ( " + tableName + " )"); } } // For each row being added } } // Check any foreign key constraints. // This ensures all foreign references in the table are referenced // to valid records. var foreignConstraints = transaction.QueryTableForeignKeys(tableName); foreach (var reference in foreignConstraints) { if (deferred == ConstraintDeferrability.InitiallyDeferred || reference.Deferred == ConstraintDeferrability.InitiallyImmediate) { // For each row added to this column foreach (int rowIndex in rowIndices) { // Make sure the referenced record exists // Return the count of records where the given row of // table_name(columns, ...) IN // ref_table_name(ref_columns, ...) int rowCount = RowCountOfReferenceTable(transaction, rowIndex, reference.TableName, reference.ColumnNames, reference.ForeignTable, reference.ForeignColumnNames, false); if (rowCount == -1) { // foreign key is NULL } if (rowCount == 0) { throw new ConstraintViolationException( SqlModelErrorCodes.ForeignKeyViolation, deferred.AsDebugString() + " foreign key constraint violation (" + reference.ConstraintName + ") Columns = " + reference.TableName + "( " + String.Join(", ", reference.ColumnNames) + " ) -> " + reference.ForeignTable + "( " + String.Join(", ", reference.ForeignColumnNames) + " )"); } } // For each row being added. } } // Any general checks of the inserted data var checkConstraints = transaction.QueryTableCheckExpressions(tableName); // For each check constraint, check that it evaluates to true. for (int i = 0; i < checkConstraints.Length; ++i) { var check = checkConstraints[i]; if (deferred == ConstraintDeferrability.InitiallyDeferred || check.Deferred == ConstraintDeferrability.InitiallyImmediate) { // TODO: var exp = tableInfo.ResolveColumns(transaction.IgnoreIdentifierCase(), check.CheckExpression); var exp = tableInfo.ResolveColumns(true, check.CheckExpression); // For each row being added to this column for (int rn = 0; rn < rowIndices.Length; ++rn) { var resolver = new TableRowVariableResolver(table, rowIndices[rn]); var evalExp = exp.Evaluate(queryContext, resolver, null); var ob = ((SqlConstantExpression) evalExp).Value; var b = ob.AsBoolean(); if (!b.IsNull) { if (b) { // Evaluated to false so don't allow this row to be added. throw new ConstraintViolationException( SqlModelErrorCodes.CheckViolation, deferred.AsDebugString() + " check constraint violation (" + check.ConstraintName + ") - '" + exp + "' evaluated to false for inserted/updated row."); } } else { // NOTE: This error will pass the row by default // TODO: emit a warning } } } } }
public static void CheckAddConstraintViolations(this ITransaction transaction, ITable table, int[] rowIndices, ConstraintDeferrability deferred) { string curSchema = table.TableInfo.TableName.Parent.Name; using (var session = new SystemSession(transaction, curSchema)) { using (var queryContext = session.CreateQuery()) { // Quick exit case if (rowIndices == null || rowIndices.Length == 0) { return; } var tableInfo = table.TableInfo; var tableName = tableInfo.TableName; // ---- Constraint checking ---- // Check any primary key constraint. var primaryKey = transaction.QueryTablePrimaryKey(tableName); if (primaryKey != null && (deferred == ConstraintDeferrability.InitiallyDeferred || primaryKey.Deferred == ConstraintDeferrability.InitiallyImmediate)) { // For each row added to this column foreach (int rowIndex in rowIndices) { if (!IsUniqueColumns(table, rowIndex, primaryKey.ColumnNames, false)) { throw new PrimaryKeyViolationException(tableName, primaryKey.ConstraintName, primaryKey.ColumnNames, deferred); } } // For each row being added } // Check any unique constraints. var uniqueConstraints = transaction.QueryTableUniqueKeys(tableName); foreach (var unique in uniqueConstraints) { if (deferred == ConstraintDeferrability.InitiallyDeferred || unique.Deferred == ConstraintDeferrability.InitiallyImmediate) { // For each row added to this column foreach (int rowIndex in rowIndices) { if (!IsUniqueColumns(table, rowIndex, unique.ColumnNames, true)) { throw new UniqueKeyViolationException(tableName, unique.ConstraintName, unique.ColumnNames, deferred); } } // For each row being added } } // Check any foreign key constraints. // This ensures all foreign references in the table are referenced // to valid records. var foreignConstraints = transaction.QueryTableForeignKeys(tableName); foreach (var reference in foreignConstraints) { if (deferred == ConstraintDeferrability.InitiallyDeferred || reference.Deferred == ConstraintDeferrability.InitiallyImmediate) { // For each row added to this column foreach (int rowIndex in rowIndices) { // Make sure the referenced record exists // Return the count of records where the given row of // table_name(columns, ...) IN // ref_table_name(ref_columns, ...) int rowCount = RowCountOfReferenceTable(transaction, rowIndex, reference.TableName, reference.ColumnNames, reference.ForeignTable, reference.ForeignColumnNames, false); if (rowCount == -1) { // foreign key is NULL } if (rowCount == 0) { throw new ForeignKeyViolationException(tableName, reference.ConstraintName, reference.ColumnNames, reference.ForeignTable, reference.ForeignColumnNames, deferred); } } // For each row being added. } } // Any general checks of the inserted data var checkConstraints = transaction.QueryTableCheckExpressions(tableName); // For each check constraint, check that it evaluates to true. for (int i = 0; i < checkConstraints.Length; ++i) { var check = checkConstraints[i]; if (deferred == ConstraintDeferrability.InitiallyDeferred || check.Deferred == ConstraintDeferrability.InitiallyImmediate) { var exp = tableInfo.ResolveColumns(transaction.IgnoreIdentifiersCase(), check.CheckExpression); // For each row being added to this column for (int rn = 0; rn < rowIndices.Length; ++rn) { var resolver = new TableRowVariableResolver(table, rowIndices[rn]); var evalExp = exp.Evaluate(queryContext, resolver, null); var ob = ((SqlConstantExpression)evalExp).Value; var b = ob.AsBoolean(); if (!b.IsNull) { if (!((SqlBoolean)b.Value)) { // Evaluated to false so don't allow this row to be added. throw new CheckViolationException(tableName, check.ConstraintName, check.CheckExpression, deferred); } } else { // NOTE: This error will pass the row by default // TODO: emit a warning } } } } } } }