public static void AddPrimaryKey(this ITransaction transaction, ObjectName tableName, string[] columns,
ConstraintDeferrability deferred, string constraintName)
{
var t = transaction.GetMutableTable(SystemSchema.PrimaryKeyInfoTableName);
var tcols = transaction.GetMutableTable(SystemSchema.PrimaryKeyColumnsTableName);
try {
// Insert a value into PrimaryInfoTable
var row = t.NewRow();
var uniqueId = transaction.NextTableId(SystemSchema.PrimaryKeyInfoTableName);
constraintName = MakeUniqueConstraintName(constraintName, uniqueId);
row.SetValue(0, uniqueId);
row.SetValue(1, constraintName);
row.SetValue(2, tableName.Parent.Name);
row.SetValue(3, tableName.Name);
row.SetValue(4, (short)deferred);
t.AddRow(row);
// Insert the columns
for (int i = 0; i < columns.Length; ++i)
{
row = tcols.NewRow();
row.SetValue(0, uniqueId); // unique id
row.SetValue(1, columns[i]); // column name
row.SetValue(2, i); // Sequence number
tcols.AddRow(row);
}
} catch (UniqueKeyViolationException) {
// This means we gave a constraint name that's already being used
// for a primary key.
throw new Exception(String.Format("Primary key constraint name '{0}' is already being used.", constraintName));
}
}
internal PrimaryKeyViolationException(ObjectName tableName, string constraintName, string[] columnNames, ConstraintDeferrability deferrability)
: base(SystemErrorCodes.PrimaryKeyViolation, FormatMessage(tableName, constraintName, columnNames, deferrability))
{
TableName = tableName;
ConstraintName = constraintName;
ColumnNames = columnNames;
Deferrability = deferrability;
}
internal PrimaryKeyViolationException(ObjectName tableName, string constraintName, string[] columnNames, ConstraintDeferrability deferrability)
: base(SystemErrorCodes.PrimaryKeyViolation, FormatMessage(tableName, constraintName, columnNames, deferrability))
{
TableName = tableName;
ConstraintName = constraintName;
ColumnNames = columnNames;
Deferrability = deferrability;
}
public void AddPrimaryKey(ObjectName tableName, string[] columns, ConstraintDeferrability deferred,
string constraintName)
{
//if (!Session.User.CanAlterTable(tableName))
// throw new MissingPrivilegesException(Session.User.Name, tableName, Privileges.Alter);
Session.Transaction.AddPrimaryKey(tableName, columns, deferred, constraintName);
}
public static void CheckAddConstraintViolations(this ITransaction transaction, ITable table,
ConstraintDeferrability deferred)
{
// Get all the rows in the table
var rows = table.Select(x => x.RowId.RowNumber).ToArray();
// Check the constraints of all the rows in the table.
CheckAddConstraintViolations(transaction, table, rows, deferred);
}
internal CheckViolationException(ObjectName tableName, string constraintName, SqlExpression expression,
ConstraintDeferrability deferrability)
: base(SystemErrorCodes.CheckViolation, FormatMessage(tableName, constraintName, expression, deferrability))
{
TableName = tableName;
ConstraintName = constraintName;
CheckExpression = expression;
Deferrability = deferrability;
}
internal CheckViolationException(ObjectName tableName, string constraintName, SqlExpression expression,
ConstraintDeferrability deferrability)
: base(SystemErrorCodes.CheckViolation, FormatMessage(tableName, constraintName, expression, deferrability))
{
TableName = tableName;
ConstraintName = constraintName;
CheckExpression = expression;
Deferrability = deferrability;
}
public static void AddForeignKey(this IQueryContext context, ObjectName table, string[] columns, ObjectName refTable,
string[] refColumns, ForeignKeyAction deleteRule, ForeignKeyAction updateRule, ConstraintDeferrability deferred,
String constraintName)
{
// TODO: throw a specialized exception
if (!context.UserCanAlterTable(table))
throw new InvalidOperationException();
context.Session.AddForeignKey(table, columns, refTable, refColumns, deleteRule, updateRule, deferred, constraintName);
}
public static void AddUniqueKey(this IQuery query, ObjectName tableName, string[] columns,
ConstraintDeferrability deferrability, string constraintName)
{
if (!query.UserCanAlterTable(tableName))
{
throw new MissingPrivilegesException(query.UserName(), tableName, Privileges.Alter);
}
query.Session.AddUniqueKey(tableName, columns, deferrability, constraintName);
}
public static void AddForeignKey(this IQuery query, ObjectName table, string[] columns, ObjectName refTable,
string[] refColumns, ForeignKeyAction deleteRule, ForeignKeyAction updateRule, ConstraintDeferrability deferred,
String constraintName)
{
if (!query.UserCanAlterTable(table))
throw new MissingPrivilegesException(query.UserName(), table, Privileges.Alter);
if (!query.UserCanReferenceTable(refTable))
throw new MissingPrivilegesException(query.UserName(), refTable, Privileges.References);
query.Session.AddForeignKey(table, columns, refTable, refColumns, deleteRule, updateRule, deferred, constraintName);
}
internal ForeignKeyViolationException(ObjectName tableName, string constraintName, string[] columnNames,
ObjectName refTableName, string[] refColumnNames, ConstraintDeferrability deferrability)
: base(SystemErrorCodes.ForeignKeyViolation, FormatMessage(tableName, constraintName, columnNames, refTableName, refColumnNames, deferrability))
{
TableName = tableName;
ConstraintName = constraintName;
ColumnNames = columnNames;
LinkedTableName = refTableName;
LinkedColumnNames = refColumnNames;
Deferrability = deferrability;
}
internal ForeignKeyViolationException(ObjectName tableName, string constraintName, string[] columnNames,
ObjectName refTableName, string[] refColumnNames, ConstraintDeferrability deferrability)
: base(SystemErrorCodes.ForeignKeyViolation, FormatMessage(tableName, constraintName, columnNames, refTableName, refColumnNames, deferrability))
{
TableName = tableName;
ConstraintName = constraintName;
ColumnNames = columnNames;
LinkedTableName = refTableName;
LinkedColumnNames = refColumnNames;
Deferrability = deferrability;
}
public static string AsDebugString(this ConstraintDeferrability deferred)
{
switch (deferred)
{
case (ConstraintDeferrability.InitiallyImmediate):
return("Immediate");
case (ConstraintDeferrability.InitiallyDeferred):
return("Deferred");
default:
throw new InvalidOperationException("Unknown deferred string.");
}
}
public static void CheckRemoveConstraintViolations(this ITransaction transaction, ITable table, int[] rowIndices,
ConstraintDeferrability deferred)
{
// Quick exit case
if (rowIndices == null || rowIndices.Length == 0)
{
return;
}
var tableInfo = table.TableInfo;
var tableName = tableInfo.TableName;
// Check any imported foreign key constraints.
// This ensures that a referential reference can not be removed making
// it invalid.
var foreignConstraints = transaction.QueryTableImportedForeignKeys(tableName);
foreach (var reference in foreignConstraints)
{
if (deferred == ConstraintDeferrability.InitiallyDeferred ||
reference.Deferred == ConstraintDeferrability.InitiallyImmediate)
{
// For each row removed from this column
foreach (int rowIndex in rowIndices)
{
// Make sure the referenced record exists
// Return the count of records where the given row of
// ref_table_name(columns, ...) IN
// table_name(ref_columns, ...)
int rowCount = RowCountOfReferenceTable(transaction,
rowIndex,
reference.ForeignTable, reference.ForeignColumnNames,
reference.TableName, reference.ColumnNames,
true);
// There must be 0 references otherwise the delete isn't allowed to
// happen.
if (rowCount > 0)
{
throw new ForeignKeyViolationException(tableName, reference.ConstraintName, reference.ColumnNames, reference.ForeignTable, reference.ForeignColumnNames, deferred);
}
}
}
}
}
public static void AddCheck(this ITransaction transaction, ObjectName tableName, SqlExpression expression,
ConstraintDeferrability deferrability, string constraintName)
{
var tn = SystemSchema.CheckInfoTableName;
var t = transaction.GetMutableTable(tn);
int colCount = t.TableInfo.ColumnCount;
try {
byte[] binExp;
using (var stream = new MemoryStream()) {
using (var writer = new BinaryWriter(stream, Encoding.Unicode)) {
SqlExpression.Serialize(expression, writer);
writer.Flush();
binExp = stream.ToArray();
}
}
// Insert check constraint data.
var uniqueId = transaction.NextTableId(tn);
constraintName = MakeUniqueConstraintName(constraintName, uniqueId);
var rd = t.NewRow();
rd.SetValue(0, uniqueId);
rd.SetValue(1, constraintName);
rd.SetValue(2, tableName.ParentName);
rd.SetValue(3, tableName.Name);
rd.SetValue(4, expression.ToString());
rd.SetValue(5, (short)deferrability);
if (colCount > 6) {
rd.SetValue(6, DataObject.Binary(new SqlBinary(binExp)));
}
t.AddRow(rd);
} catch (ConstraintViolationException e) {
// Constraint violation when inserting the data. Check the type and
// wrap around an appropriate error message.
if (e.ErrorCode == SqlModelErrorCodes.UniqueViolation) {
// This means we gave a constraint name that's already being used.
throw new InvalidOperationException("Check constraint name '" + constraintName + "' is already being used.");
}
throw;
}
}
public static void AddCheck(this ITransaction transaction, ObjectName tableName, SqlExpression expression,
ConstraintDeferrability deferrability, string constraintName)
{
var tn = SystemSchema.CheckInfoTableName;
var t = transaction.GetMutableTable(tn);
int colCount = t.TableInfo.ColumnCount;
try {
byte[] binExp;
using (var stream = new MemoryStream()) {
using (var writer = new BinaryWriter(stream, Encoding.Unicode)) {
SqlExpression.Serialize(expression, writer);
writer.Flush();
binExp = stream.ToArray();
}
}
// Insert check constraint data.
var uniqueId = transaction.NextTableId(tn);
constraintName = MakeUniqueConstraintName(constraintName, uniqueId);
var rd = t.NewRow();
rd.SetValue(0, uniqueId);
rd.SetValue(1, constraintName);
rd.SetValue(2, tableName.ParentName);
rd.SetValue(3, tableName.Name);
rd.SetValue(4, expression.ToString());
rd.SetValue(5, (short)deferrability);
if (colCount > 6)
{
rd.SetValue(6, Field.Binary(new SqlBinary(binExp)));
}
t.AddRow(rd);
} catch (UniqueKeyViolationException) {
throw new InvalidOperationException("Check constraint name '" + constraintName + "' is already being used.");
}
}
public static void AddCheck(this ITransaction transaction, ObjectName tableName, SqlExpression expression,
ConstraintDeferrability deferrability, string constraintName)
{
var tn = SystemSchema.CheckInfoTableName;
var t = transaction.GetMutableTable(tn);
int colCount = t.TableInfo.ColumnCount;
try {
byte[] binExp;
using (var stream = new MemoryStream()) {
using (var writer = new BinaryWriter(stream, Encoding.Unicode)) {
SqlExpression.Serialize(expression, writer);
writer.Flush();
binExp = stream.ToArray();
}
}
// Insert check constraint data.
var uniqueId = transaction.NextTableId(tn);
constraintName = MakeUniqueConstraintName(constraintName, uniqueId);
var rd = t.NewRow();
rd.SetValue(0, uniqueId);
rd.SetValue(1, constraintName);
rd.SetValue(2, tableName.ParentName);
rd.SetValue(3, tableName.Name);
rd.SetValue(4, expression.ToString());
rd.SetValue(5, (short) deferrability);
if (colCount > 6) {
rd.SetValue(6, Field.Binary(new SqlBinary(binExp)));
}
t.AddRow(rd);
} catch (UniqueKeyViolationException) {
throw new InvalidOperationException("Check constraint name '" + constraintName + "' is already being used.");
}
}
public static void AddCheck(this IQuery query, ObjectName tableName, SqlExpression expression, ConstraintDeferrability deferred, string constraintName)
{
query.Session.AddCheck(tableName, expression, deferred, constraintName);
}
public void AddForeignKey(ObjectName table, string[] columns,
ObjectName refTable, string[] refColumns, ConstraintDeferrability deferred, string constraintName)
{
AddForeignKey(table, columns, refTable, refColumns, ForeignKeyAction.Cascade, ForeignKeyAction.Cascade, deferred,
constraintName);
}
public static void CheckRemoveConstraintViolations(this ITransaction transaction, ITable table, long[] rowIndices, ConstraintDeferrability deferred)
{
// TODO:
}
public static void AddForeignKey(this ISession session, ObjectName table, string[] columns,
ObjectName refTable, string[] refColumns,
ForeignKeyAction deleteRule, ForeignKeyAction updateRule, ConstraintDeferrability deferred, String constraintName)
{
session.Transaction.AddForeignKey(table, columns, refTable, refColumns, deleteRule, updateRule, deferred, constraintName);
}
public static void AddForeignKey(this ITransaction transaction, ObjectName table, string[] columns,
ObjectName refTable, string[] refColumns, ConstraintDeferrability deferred, String constraintName)
{
AddForeignKey(transaction, table, columns, refTable, refColumns, ForeignKeyAction.NoAction, ForeignKeyAction.NoAction,
deferred, constraintName);
}
private static string FormatMessage(ObjectName tableName, string constraintName, string[] columnNames, ObjectName refTableName, string[] refColumnNames, ConstraintDeferrability deferrability)
{
return(String.Format("{0} FOREIGN KEY violation for constraint '{1}({2})' referencing '{3}({4})'",
deferrability.AsDebugString(), constraintName, String.Join(", ", columnNames), refTableName,
String.Join(", ", refColumnNames)));
}
public void AddCheck(ObjectName tableName, SqlExpression expression, ConstraintDeferrability deferrability,
string constraintName)
{
Session.Transaction.AddCheck(tableName, expression, deferrability, constraintName);
}
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
}
}
}
}
}
}
}
public static void AddPrimaryKey(this ITransaction transaction, ObjectName tableName, string[] columns,
ConstraintDeferrability deferred, string constraintName)
{
var t = transaction.GetMutableTable(SystemSchema.PrimaryKeyInfoTableName);
var tcols = transaction.GetMutableTable(SystemSchema.PrimaryKeyColumnsTableName);
try {
// Insert a value into PrimaryInfoTable
var row = t.NewRow();
var uniqueId = transaction.NextTableId(SystemSchema.PrimaryKeyInfoTableName);
constraintName = MakeUniqueConstraintName(constraintName, uniqueId);
row.SetValue(0, uniqueId);
row.SetValue(1, constraintName);
row.SetValue(2, tableName.Parent.Name);
row.SetValue(3, tableName.Name);
row.SetValue(4, (short) deferred);
t.AddRow(row);
// Insert the columns
for (int i = 0; i < columns.Length; ++i) {
row = tcols.NewRow();
row.SetValue(0, uniqueId); // unique id
row.SetValue(1, columns[i]); // column name
row.SetValue(2, i); // Sequence number
tcols.AddRow(row);
}
} catch (UniqueKeyViolationException) {
// This means we gave a constraint name that's already being used
// for a primary key.
throw new Exception(String.Format("Primary key constraint name '{0}' is already being used.", constraintName));
}
}
public static void AddUniqueKey(this IQueryContext context, ObjectName tableName, string[] columns,
ConstraintDeferrability deferrability, string constraintName)
{
if (!context.UserCanAlterTable(tableName))
throw new InvalidOperationException();
context.Session.AddUniqueKey(tableName, columns, deferrability, constraintName);
}
public static void AddForeignKey(this ITransaction transaction, ObjectName table, string[] columns,
ObjectName refTable, string[] refColumns, ConstraintDeferrability deferred, String constraintName)
{
AddForeignKey(transaction, table, columns, refTable, refColumns, ForeignKeyAction.NoAction, ForeignKeyAction.NoAction,
deferred, constraintName);
}
public static void AddPrimaryKey(this IUserSession session, ObjectName tableName, string[] columns, ConstraintDeferrability deferred, string constraintName)
{
session.Transaction.AddPrimaryKey(tableName, columns, deferred, constraintName);
}
public static void AddForeignKey(this ITransaction transaction, ObjectName table, string[] columns,
ObjectName refTable, string[] refColumns,
ForeignKeyAction deleteRule, ForeignKeyAction updateRule, ConstraintDeferrability deferred, String constraintName)
{
var t = transaction.GetMutableTable(SystemSchema.ForeignKeyInfoTableName);
var tcols = transaction.GetMutableTable(SystemSchema.ForeignKeyColumnsTableName);
try {
// If 'ref_columns' empty then set to primary key for referenced table,
// ISSUE: What if primary key changes after the fact?
if (refColumns.Length == 0)
{
var set = transaction.QueryTablePrimaryKey(refTable);
if (set == null)
{
throw new Exception(String.Format("No primary key defined for referenced table '{0}'", refTable));
}
refColumns = set.ColumnNames;
}
if (columns.Length != refColumns.Length)
{
throw new Exception(String.Format("Foreign key reference '{0}' -> '{1}' does not have an equal number of " +
"column terms.", table, refTable));
}
// If delete or update rule is 'SET NULL' then check the foreign key
// columns are not constrained as 'NOT NULL'
if (deleteRule == ForeignKeyAction.SetNull ||
updateRule == ForeignKeyAction.SetNull)
{
var tableInfo = transaction.GetTableInfo(table);
for (int i = 0; i < columns.Length; ++i)
{
var columnInfo = tableInfo[tableInfo.IndexOfColumn(columns[i])];
if (columnInfo.IsNotNull)
{
throw new NotNullColumnViolationException(tableInfo.TableName, columnInfo.ColumnName);
}
}
}
// Insert a value into ForeignInfoTable
var row = t.NewRow();
var uniqueId = transaction.NextTableId(SystemSchema.ForeignKeyInfoTableName);
constraintName = MakeUniqueConstraintName(constraintName, uniqueId);
row.SetValue(0, uniqueId);
row.SetValue(1, constraintName);
row.SetValue(2, table.Parent.Name);
row.SetValue(3, table.Name);
row.SetValue(4, refTable.Parent.Name);
row.SetValue(5, refTable.Name);
row.SetValue(6, ((int)updateRule));
row.SetValue(7, ((int)deleteRule));
row.SetValue(8, ((short)deferred));
t.AddRow(row);
// Insert the columns
for (int i = 0; i < columns.Length; ++i)
{
row = tcols.NewRow();
row.SetValue(0, uniqueId); // unique id
row.SetValue(1, columns[i]); // column name
row.SetValue(2, refColumns[i]); // ref column name
row.SetValue(3, i); // sequence number
tcols.AddRow(row);
}
} catch (UniqueKeyViolationException) {
// This means we gave a constraint name that's already being used
// for a primary key.
throw new Exception(String.Format("Foreign key constraint name '{0}' is already being used.", constraintName));
}
}
public static void AddUniqueKey(this IQuery query, ObjectName tableName, string[] columns,
ConstraintDeferrability deferrability)
{
AddUniqueKey(query, tableName, columns, deferrability, null);
}
public static void CheckAddConstraintViolations(this ITransaction transaction, ITable table, ConstraintDeferrability deferred)
{
// Get all the rows in the table
var rows = table.Select(x => x.RowId.RowNumber).ToArray();
// Check the constraints of all the rows in the table.
CheckAddConstraintViolations(transaction, table, rows, deferred);
}
private static string FormatMessage(ObjectName tableName, string constraintName, SqlExpression expression, ConstraintDeferrability deferrability)
{
return(String.Format("{0} CHECK violation for constraint '{1}' ({2}) on table '{3}'", deferrability, constraintName, expression, tableName));
}
public static void CheckRemoveConstraintViolations(this ITransaction transaction, ITable table, int[] rowIndices, ConstraintDeferrability deferred)
{
// Quick exit case
if (rowIndices == null || rowIndices.Length == 0)
return;
var tableInfo = table.TableInfo;
var tableName = tableInfo.TableName;
// Check any imported foreign key constraints.
// This ensures that a referential reference can not be removed making
// it invalid.
var foreignConstraints = transaction.QueryTableImportedForeignKeys(tableName);
foreach (var reference in foreignConstraints) {
if (deferred == ConstraintDeferrability.InitiallyDeferred ||
reference.Deferred == ConstraintDeferrability.InitiallyImmediate) {
// For each row removed from this column
foreach (int rowIndex in rowIndices) {
// Make sure the referenced record exists
// Return the count of records where the given row of
// ref_table_name(columns, ...) IN
// table_name(ref_columns, ...)
int rowCount = RowCountOfReferenceTable(transaction,
rowIndex,
reference.ForeignTable, reference.ForeignColumnNames,
reference.TableName, reference.ColumnNames,
true);
// There must be 0 references otherwise the delete isn't allowed to
// happen.
if (rowCount > 0) {
throw new ConstraintViolationException(SqlModelErrorCodes.ForeignKeyViolation,
deferred.AsDebugString() + " foreign key constraint violation " +
"on delete (" +
reference.ConstraintName + ") Columns = " +
reference.TableName + "( " +
String.Join(", ", reference.ColumnNames) + " ) -> " +
reference.ForeignTable + "( " +
String.Join(", ", reference.ForeignColumnNames) + " )");
}
}
}
}
}
private static string FormatMessage(ObjectName tableName, string constraintName, string[] columnNames, ConstraintDeferrability deferrability)
{
return(String.Format("{0} UNIQUE KEY violation for constraint '{1}({2})' on table '{3}'.",
deferrability.AsDebugString(), constraintName, String.Join(", ", columnNames), tableName));
}
public static void AddForeignKey(this ITransaction transaction, ObjectName table, string[] columns,
ObjectName refTable, string[] refColumns,
ForeignKeyAction deleteRule, ForeignKeyAction updateRule, ConstraintDeferrability deferred, String constraintName)
{
var t = transaction.GetMutableTable(SystemSchema.ForeignKeyInfoTableName);
var tcols = transaction.GetMutableTable(SystemSchema.ForeignKeyColumnsTableName);
try {
// If 'ref_columns' empty then set to primary key for referenced table,
// ISSUE: What if primary key changes after the fact?
if (refColumns.Length == 0) {
var set = transaction.QueryTablePrimaryKey(refTable);
if (set == null)
throw new Exception(String.Format("No primary key defined for referenced table '{0}'", refTable));
refColumns = set.ColumnNames;
}
if (columns.Length != refColumns.Length) {
throw new Exception(String.Format("Foreign key reference '{0}' -> '{1}' does not have an equal number of " +
"column terms.", table, refTable));
}
// If delete or update rule is 'SET NULL' then check the foreign key
// columns are not constrained as 'NOT NULL'
if (deleteRule == ForeignKeyAction.SetNull ||
updateRule == ForeignKeyAction.SetNull) {
var tableInfo = transaction.GetTableInfo(table);
for (int i = 0; i < columns.Length; ++i) {
var columnInfo = tableInfo[tableInfo.IndexOfColumn(columns[i])];
if (columnInfo.IsNotNull) {
throw new Exception(String.Format("Foreign key reference '{0}' -> '{1}' update or delete triggered " +
"action is SET NULL for columns that are constrained as " +
"NOT NULL.", table, refTable));
}
}
}
// Insert a value into ForeignInfoTable
var row = t.NewRow();
var uniqueId = transaction.NextTableId(SystemSchema.ForeignKeyInfoTableName);
constraintName = MakeUniqueConstraintName(constraintName, uniqueId);
row.SetValue(0, uniqueId);
row.SetValue(1, constraintName);
row.SetValue(2, table.Parent.Name);
row.SetValue(3, table.Name);
row.SetValue(4, refTable.Parent.Name);
row.SetValue(5, refTable.Name);
row.SetValue(6, ((int) updateRule));
row.SetValue(7, ((int) deleteRule));
row.SetValue(8, ((short) deferred));
t.AddRow(row);
// Insert the columns
for (int i = 0; i < columns.Length; ++i) {
row = tcols.NewRow();
row.SetValue(0, uniqueId); // unique id
row.SetValue(1, columns[i]); // column name
row.SetValue(2, refColumns[i]); // ref column name
row.SetValue(3, i); // sequence number
tcols.AddRow(row);
}
} catch (ConstraintViolationException e) {
// Constraint violation when inserting the data. Check the type and
// wrap around an appropriate error message.
if (e.ErrorCode == SqlModelErrorCodes.UniqueViolation)
// This means we gave a constraint name that's already being used
// for a primary key.
throw new Exception(String.Format("Foreign key constraint name '{0}' is already being used.", constraintName));
throw;
}
}
public static void AddUniqueKey(this IQuery query, ObjectName tableName, string[] columns,
ConstraintDeferrability deferrability)
{
AddUniqueKey(query, tableName, columns, deferrability, null);
}
private static string FormatMessage(ObjectName tableName, string constraintName, string[] columnNames, ObjectName refTableName, string[] refColumnNames, ConstraintDeferrability deferrability)
{
return String.Format("{0} FOREIGN KEY violation for constraint '{1}({2})' referencing '{3}({4})'",
deferrability.AsDebugString(), constraintName, String.Join(", ", columnNames), refTableName,
String.Join(", ", refColumnNames));
}
public static void AddUniqueKey(this IQuery query, ObjectName tableName, string[] columns,
ConstraintDeferrability deferrability, string constraintName)
{
if (!query.UserCanAlterTable(tableName))
throw new MissingPrivilegesException(query.UserName(), tableName, Privileges.Alter);
query.Session.AddUniqueKey(tableName, columns, deferrability, constraintName);
}
public static void AddCheck(this ISession session, ObjectName tableName, SqlExpression expression, ConstraintDeferrability deferrability,
string constraintName)
{
session.Transaction.AddCheck(tableName, expression, deferrability, constraintName);
}
public static void AddCheck(this IQuery query, ObjectName tableName, SqlExpression expression, ConstraintDeferrability deferred, string constraintName)
{
query.Session.AddCheck(tableName, expression, deferred, constraintName);
}
public static void AddUniqueKey(this ISession session, ObjectName tableName, string[] columns, ConstraintDeferrability deferrability, string constraintName)
{
session.Transaction.AddUniqueKey(tableName, columns, deferrability, constraintName);
}
public static void AddPrimaryKey(this ISession session, ObjectName tableName, string[] columns, ConstraintDeferrability deferred, string constraintName)
{
session.Transaction.AddPrimaryKey(tableName, columns, deferred, constraintName);
}
public void AddForeignKey(ObjectName table, string[] columns,
ObjectName refTable, string[] refColumns,
ForeignKeyAction deleteRule, ForeignKeyAction updateRule, ConstraintDeferrability deferred, string constraintName)
{
Session.Transaction.AddForeignKey(table, columns, refTable, refColumns, deleteRule, updateRule, deferred,
constraintName);
}
public static void AddUniqueKey(this ITransaction transaction, ObjectName tableName, string[] columns, ConstraintDeferrability deferred, string constraintName)
{
var t = transaction.GetMutableTable(SystemSchema.UniqueKeyInfoTableName);
var tcols = transaction.GetMutableTable(SystemSchema.UniqueKeyColumnsTableName);
try {
// Insert a value into UniqueInfoTable
var row = t.NewRow();
var uniqueId = transaction.NextTableId(SystemSchema.UniqueKeyInfoTableName);
constraintName = MakeUniqueConstraintName(constraintName, uniqueId);
row.SetValue(0, uniqueId);
row.SetValue(1, constraintName);
row.SetValue(2, tableName.Parent.Name);
row.SetValue(3, tableName.Name);
row.SetValue(4, (short)deferred);
t.AddRow(row);
// Insert the columns
for (int i = 0; i < columns.Length; ++i) {
row = tcols.NewRow();
row.SetValue(0, uniqueId); // unique id
row.SetValue(1, columns[i]); // column name
row.SetValue(2, i); // sequence number
tcols.AddRow(row);
}
} catch (ConstraintViolationException e) {
// Constraint violation when inserting the data. Check the type and
// wrap around an appropriate error message.
if (e.ErrorCode == SqlModelErrorCodes.UniqueViolation)
// This means we gave a constraint name that's already being used
// for a primary key.
throw new Exception(String.Format("Unique constraint name '{0}' is already being used.", constraintName));
throw;
}
}
public void AddUniqueKey(ObjectName tableName, string[] columns, ConstraintDeferrability deferrability,
string constraintName)
{
Session.Transaction.AddUniqueKey(tableName, columns, deferrability, constraintName);
}
private static string FormatMessage(ObjectName tableName, string constraintName, SqlExpression expression, ConstraintDeferrability deferrability)
{
return String.Format("{0} CHECK violation for constraint '{1}' ({2}) on table '{3}'", deferrability, constraintName, expression, tableName);
}
private static string FormatMessage(ObjectName tableName, string constraintName, string[] columnNames, ConstraintDeferrability deferrability)
{
return String.Format("{0} UNIQUE KEY violation for constraint '{1}({2})' on table '{3}'.",
deferrability.AsDebugString(), constraintName, String.Join(", ", columnNames), tableName);
}
public static void AddForeignKey(this IQuery query, ObjectName table, string[] columns, ObjectName refTable,
string[] refColumns, ForeignKeyAction deleteRule, ForeignKeyAction updateRule, ConstraintDeferrability deferred,
String constraintName)
{
if (!query.UserCanAlterTable(table))
{
throw new MissingPrivilegesException(query.UserName(), table, Privileges.Alter);
}
if (!query.UserCanReferenceTable(refTable))
{
throw new MissingPrivilegesException(query.UserName(), refTable, Privileges.References);
}
query.Session.AddForeignKey(table, columns, refTable, refColumns, deleteRule, updateRule, deferred, constraintName);
}
