public static void AddConstraint(this IQuery query, ObjectName tableName, ConstraintInfo constraintInfo)
{
if (constraintInfo.ConstraintType == ConstraintType.PrimaryKey)
{
var columnNames = constraintInfo.ColumnNames;
if (columnNames.Length > 1)
{
throw new ArgumentException();
}
query.AddPrimaryKey(tableName, columnNames[0], constraintInfo.ConstraintName);
}
else if (constraintInfo.ConstraintType == ConstraintType.Unique)
{
query.AddUniqueKey(tableName, constraintInfo.ColumnNames, constraintInfo.ConstraintName);
}
else if (constraintInfo.ConstraintType == ConstraintType.Check)
{
query.AddCheck(tableName, constraintInfo.CheckExpression, constraintInfo.ConstraintName);
}
else if (constraintInfo.ConstraintType == ConstraintType.ForeignKey)
{
query.AddForeignKey(tableName, constraintInfo.ColumnNames, constraintInfo.ForeignTable,
constraintInfo.ForeignColumnNames, constraintInfo.OnDelete, constraintInfo.OnUpdate, constraintInfo.ConstraintName);
}
}
public static ConstraintInfo ForeignKey(string constraintName, ObjectName tableName, string[] columnNames,
ObjectName refTable, string[] refColumns)
{
if (tableName == null)
{
throw new ArgumentNullException("tableName");
}
if (refTable == null)
{
throw new ArgumentNullException("refTable");
}
if (columnNames == null || columnNames.Length == 0)
{
throw new ArgumentException("At least one column is required", "columnNames");
}
if (refColumns == null || refColumns.Length == 0)
{
throw new ArgumentException("At least one referenced column is required.", "refColumns");
}
if (columnNames.Length != refColumns.Length)
{
throw new ArgumentException("The number of columns in the constraint must match the number of columns referenced.");
}
var constraint = new ConstraintInfo(constraintName, ConstraintType.ForeignKey, tableName, columnNames);
constraint.ForeignTable = refTable;
constraint.ForeignColumnNames = refColumns;
return(constraint);
}
public StatementResult(ICursor cursor, ConstraintInfo[] constraints)
{
if (cursor == null)
throw new ArgumentNullException("cursor");
Cursor = cursor;
Constraints = constraints;
Type = StatementResultType.CursorRef;
}
public StatementResult(ITable result, ConstraintInfo[] constraints)
{
if (result == null)
throw new ArgumentNullException("result");
Result = result;
Constraints = constraints;
Type = StatementResultType.Result;
}
public static void AddConstraint(this IQuery query, ObjectName tableName, ConstraintInfo constraintInfo)
{
if (constraintInfo.ConstraintType == ConstraintType.PrimaryKey) {
var columnNames = constraintInfo.ColumnNames;
if (columnNames.Length > 1)
throw new ArgumentException();
query.AddPrimaryKey(tableName, columnNames[0], constraintInfo.ConstraintName);
} else if (constraintInfo.ConstraintType == ConstraintType.Unique) {
query.AddUniqueKey(tableName, constraintInfo.ColumnNames, constraintInfo.ConstraintName);
} else if (constraintInfo.ConstraintType == ConstraintType.Check) {
query.AddCheck(tableName, constraintInfo.CheckExpression, constraintInfo.ConstraintName);
} else if (constraintInfo.ConstraintType == ConstraintType.ForeignKey) {
query.AddForeignKey(tableName, constraintInfo.ColumnNames, constraintInfo.ForeignTable,
constraintInfo.ForeignColumnNames, constraintInfo.OnDelete, constraintInfo.OnUpdate, constraintInfo.ConstraintName);
}
}
protected override void ExecuteStatement(ExecutionContext context)
{
//if (!context.User.CanAlterTable(TableName))
// throw new InvalidAccessException(context.Request.UserName(), TableName);
var table = context.Request.Access().GetTable(TableName);
if (table == null)
throw new ObjectNotFoundException(TableName);
var tableInfo = table.TableInfo;
var newTableInfo = new TableInfo(tableInfo.TableName);
var checker = ColumnChecker.Default(context.Request, TableName);
bool tableAltered = false;
bool markDropped = false;
for (int n = 0; n < tableInfo.ColumnCount; ++n) {
var column = tableInfo[n];
string columnName = column.ColumnName;
var columnType = column.ColumnType;
var defaultExpression = column.DefaultExpression;
if (Action.ActionType == AlterTableActionType.SetDefault &&
CheckColumnNamesMatch(context.Request, ((SetDefaultAction)Action).ColumnName, columnName)) {
var exp = ((SetDefaultAction)Action).DefaultExpression;
exp = checker.CheckExpression(exp);
defaultExpression = exp;
tableAltered = true;
} else if (Action.ActionType == AlterTableActionType.DropDefault &&
CheckColumnNamesMatch(context.Request, ((DropDefaultAction)Action).ColumnName, columnName)) {
defaultExpression = null;
tableAltered = true;
} else if (Action.ActionType == AlterTableActionType.DropColumn &&
CheckColumnNamesMatch(context.Request, ((DropColumnAction)Action).ColumnName, columnName)) {
// Check there are no referential links to this column
var refs = context.Request.Access().QueryTableImportedForeignKeys(TableName);
foreach (var reference in refs) {
CheckColumnConstraint(columnName, reference.ForeignColumnNames, reference.ForeignTable, reference.ConstraintName);
}
// Or from it
refs = context.Request.Access().QueryTableForeignKeys(TableName);
foreach (var reference in refs) {
CheckColumnConstraint(columnName, reference.ColumnNames, reference.TableName, reference.ConstraintName);
}
// Or that it's part of a primary key
var primaryKey = context.Request.Access().QueryTablePrimaryKey(TableName);
if (primaryKey != null)
CheckColumnConstraint(columnName, primaryKey.ColumnNames, TableName, primaryKey.ConstraintName);
// Or that it's part of a unique set
var uniques = context.Request.Access().QueryTableUniqueKeys(TableName);
foreach (var unique in uniques) {
CheckColumnConstraint(columnName, unique.ColumnNames, TableName, unique.ConstraintName);
}
markDropped = true;
tableAltered = true;
}
var newColumn = new ColumnInfo(columnName, columnType);
if (defaultExpression != null)
newColumn.DefaultExpression = defaultExpression;
newColumn.IndexType = column.IndexType;
newColumn.IsNotNull = column.IsNotNull;
// If not dropped then add to the new table definition.
if (!markDropped) {
newTableInfo.AddColumn(newColumn);
}
}
if (Action.ActionType == AlterTableActionType.AddColumn) {
var col = ((AddColumnAction)Action).Column;
checker.CheckExpression(col.DefaultExpression);
var columnName = col.ColumnName;
var columnType = col.ColumnType;
// If column name starts with [table_name]. then strip it off
columnName = checker.StripTableName(TableName.Name, columnName);
if (tableInfo.IndexOfColumn(col.ColumnName) != -1)
throw new InvalidOperationException("The column '" + col.ColumnName + "' is already in the table '" + tableInfo.TableName + "'.");
var newColumn = new ColumnInfo(columnName, columnType) {
IsNotNull = col.IsNotNull,
DefaultExpression = col.DefaultExpression
};
newTableInfo.AddColumn(newColumn);
tableAltered = true;
}
if (Action.ActionType == AlterTableActionType.DropConstraint) {
string constraintName = ((DropConstraintAction)Action).ConstraintName;
int dropCount = context.Request.Access().DropTableConstraint(TableName, constraintName);
if (dropCount == 0)
throw new InvalidOperationException("Named constraint to drop on table " + TableName + " was not found: " + constraintName);
} else if (Action.ActionType == AlterTableActionType.DropPrimaryKey) {
if (!context.Request.Access().DropTablePrimaryKey(TableName, null))
throw new InvalidOperationException("No primary key to delete on table " + TableName);
}
if (Action.ActionType == AlterTableActionType.AddConstraint) {
var constraint = ((AddConstraintAction)Action).Constraint;
bool foreignConstraint = (constraint.ConstraintType == ConstraintType.ForeignKey);
ObjectName refTname = null;
if (foreignConstraint) {
refTname = context.Request.Access().ResolveTableName(constraint.ReferenceTable);
}
var columnNames = checker.StripColumnList(TableName.FullName, constraint.Columns);
columnNames = checker.StripColumnList(constraint.ReferenceTable, columnNames);
var expression = checker.CheckExpression(constraint.CheckExpression);
columnNames = checker.CheckColumns(columnNames);
IEnumerable<string> refCols = null;
if (foreignConstraint && constraint.ReferenceColumns != null) {
var referencedChecker = ColumnChecker.Default(context.Request, refTname);
refCols = referencedChecker.CheckColumns(constraint.ReferenceColumns);
}
var newConstraint = new ConstraintInfo(constraint.ConstraintName, constraint.ConstraintType, TableName, columnNames.ToArray());
if (foreignConstraint) {
if (refCols == null)
throw new InvalidOperationException("Could not create a Foreign Key constraint with no reference columns");
newConstraint.ForeignTable = refTname;
newConstraint.ForeignColumnNames = refCols.ToArray();
newConstraint.OnDelete = constraint.OnDelete;
newConstraint.OnUpdate = constraint.OnUpdate;
}
if (constraint.ConstraintType == ConstraintType.Check)
newConstraint.CheckExpression = expression;
context.Request.Access().AddConstraint(TableName, newConstraint);
}
// Alter the existing table to the new format...
if (tableAltered) {
if (newTableInfo.ColumnCount == 0)
throw new InvalidOperationException("Can not ALTER table to have 0 columns.");
context.DirectAccess.AlterObject(newTableInfo);
} else {
// If the table wasn't physically altered, check the constraints.
// Calling this method will also make the transaction check all
// deferred constraints during the next commit.
context.Request.Access().CheckConstraintViolations(TableName);
}
}
public static ConstraintInfo ForeignKey(string constraintName, ObjectName tableName, string[] columnNames,
ObjectName refTable, string[] refColumns)
{
if (tableName == null)
throw new ArgumentNullException("tableName");
if (refTable == null)
throw new ArgumentNullException("refTable");
if (columnNames == null || columnNames.Length == 0)
throw new ArgumentException("At least one column is required", "columnNames");
if (refColumns == null || refColumns.Length == 0)
throw new ArgumentException("At least one referenced column is required.", "refColumns");
if (columnNames.Length != refColumns.Length)
throw new ArgumentException("The number of columns in the constraint must match the number of columns referenced.");
var constraint = new ConstraintInfo(constraintName, ConstraintType.ForeignKey, tableName, columnNames);
constraint.ForeignTable = refTable;
constraint.ForeignColumnNames = refColumns;
return constraint;
}
