public static void AddForeignKey(this IQuery query, ObjectName tableName, string constraintName, string[] columnNames,
ObjectName foreignTableName, string[] foreignColumns, ForeignKeyAction onDelete, ForeignKeyAction onUpdate)
{
query.AddConstraint(tableName,
SqlTableConstraint.ForeignKey(constraintName, columnNames, foreignTableName.FullName, foreignColumns, onDelete,
onUpdate));
}
public ICreateTableStatementBuilder WithConstraint(SqlTableConstraint constraint)
{
if (constraint != null)
{
var constraintName = String.IsNullOrEmpty(constraint.ConstraintName) ? "#ANON#" : constraint.ConstraintName;
SqlTableConstraint existing;
if (constraints.TryGetValue(constraintName, out existing))
{
var constraintColumns = new List <string>(existing.Columns);
foreach (var column in constraint.Columns)
{
if (!constraintColumns.Contains(column))
{
constraintColumns.Add(column);
}
}
constraints[constraintName] = new SqlTableConstraint(constraintName, existing.ConstraintType, constraintColumns.ToArray())
{
CheckExpression = constraint.CheckExpression,
OnDelete = constraint.OnDelete,
OnUpdate = constraint.OnUpdate,
ReferenceTable = constraint.ReferenceTable
};
}
else
{
constraints[constraintName] = constraint;
}
}
return(this);
}
internal AddConstraintAction AsAddConstraintAction()
{
var constraint = new SqlTableConstraint(ConstraintType, new [] { ColumnName });
if (ConstraintType == ConstraintType.Check &&
!String.IsNullOrEmpty(CheckExpression))
{
constraint.CheckExpression = SqlExpression.Parse(CheckExpression);
}
// TODO: Implement the foreign keys
return(new AddConstraintAction(constraint));
}
public SqlTableConstraint BuildConstraint()
{
if (String.Equals(ConstraintTypeNames.Check, ConstraintType, StringComparison.OrdinalIgnoreCase))
{
var exp = ExpressionBuilder.Build(CheckExpression);
return(new SqlTableConstraint(ConstraintName, Tables.ConstraintType.Check, Columns.ToArray())
{
CheckExpression = exp
});
}
if (String.Equals(ConstraintTypeNames.PrimaryKey, ConstraintType, StringComparison.OrdinalIgnoreCase))
{
return(SqlTableConstraint.PrimaryKey(ConstraintName, Columns.ToArray()));
}
if (String.Equals(ConstraintTypeNames.UniqueKey, ConstraintType, StringComparison.OrdinalIgnoreCase))
{
return(SqlTableConstraint.UniqueKey(ConstraintName, Columns.ToArray()));
}
if (String.Equals(ConstraintTypeNames.ForeignKey, ConstraintType, StringComparison.OrdinalIgnoreCase))
{
var fTable = ReferencedTableName.Name;
var fColumns = ReferencedColumns;
var onDelete = ForeignKeyAction.NoAction;
var onUpdate = ForeignKeyAction.NoAction;
if (!String.IsNullOrEmpty(OnDeleteAction))
{
onDelete = SqlCodeObjectBuilder.GetForeignKeyAction(OnDeleteAction);
}
if (!String.IsNullOrEmpty(OnUpdateAction))
{
onUpdate = SqlCodeObjectBuilder.GetForeignKeyAction(OnUpdateAction);
}
var fkey = SqlTableConstraint.ForeignKey(ConstraintName, Columns.ToArray(), fTable,
fColumns.ToArray(), onDelete, onUpdate);
return(fkey);
}
throw new NotSupportedException();
}
public void AddForeignKeyConstraint()
{
var tableName = ObjectName.Parse("APP.test_table2");
var constraint = new SqlTableConstraint("FK_1", ConstraintType.ForeignKey, new[] { "person_id" })
{
ReferenceTable = "APP.test_table",
ReferenceColumns = new[] { "id" }
};
AdminQuery.AddConstraint(tableName, constraint);
var fkeys = AdminQuery.Session.Access().QueryTableForeignKeys(tableName);
Assert.IsNotNull(fkeys);
Assert.IsNotEmpty(fkeys);
var fkey = fkeys.FirstOrDefault(x => x.ConstraintName == "FK_1");
Assert.IsNotNull(fkey);
Assert.IsNotNull(fkey.ForeignTable);
Assert.AreEqual("APP.test_table", fkey.ForeignTable.FullName);
Assert.IsNotNull(fkey.ForeignColumnNames);
Assert.IsNotEmpty(fkey.ForeignColumnNames);
}
public ICreateTableStatementBuilder WithConstraint(SqlTableConstraint constraint)
{
if (constraint != null) {
var constraintName = String.IsNullOrEmpty(constraint.ConstraintName) ? "#ANON#" : constraint.ConstraintName;
SqlTableConstraint existing;
if (constraints.TryGetValue(constraintName, out existing)) {
var constraintColumns = new List<string>(existing.Columns);
foreach (var column in constraint.Columns) {
if (!constraintColumns.Contains(column))
constraintColumns.Add(column);
}
constraints[constraintName] = new SqlTableConstraint(constraintName, existing.ConstraintType, constraintColumns.ToArray()) {
CheckExpression = constraint.CheckExpression,
OnDelete = constraint.OnDelete,
OnUpdate = constraint.OnUpdate,
ReferenceTable = constraint.ReferenceTable
};
} else {
constraints[constraintName] = constraint;
}
}
return this;
}
public static void AddUniqueKey(this IQuery query, ObjectName tableName, string constraintName, params string[] columnNames)
{
query.AddConstraint(tableName, SqlTableConstraint.UniqueKey(constraintName, columnNames));
}
public static void AddCheck(this IQuery query, ObjectName tableName, string constraintName, SqlExpression checkExpression)
{
query.AddConstraint(tableName, SqlTableConstraint.Check(constraintName, checkExpression));
}
public static void AddConstraint(this IQuery query, ObjectName tableName, SqlTableConstraint constraint)
{
query.AlterTable(tableName, new AddConstraintAction(constraint));
}
private static SqlStatement MakeAlterTableAddConstraint(string tableName, SqlTableConstraint constraint)
{
var action = new AddConstraintAction(constraint);
return(new AlterTableStatement(ObjectName.Parse(tableName), action));
}
public SqlTableColumn BuildColumn(ITypeResolver typeResolver, string tableName, IList <SqlTableConstraint> constraints)
{
var dataType = DataTypeBuilder.Build(typeResolver, DataType);
var columnInfo = new SqlTableColumn(ColumnName, dataType);
if (Default != null)
{
columnInfo.DefaultExpression = ExpressionBuilder.Build(Default);
}
if (IsIdentity)
{
columnInfo.DefaultExpression = SqlExpression.FunctionCall("UNIQUEKEY",
new[] { SqlExpression.Constant(tableName) });
columnInfo.IsIdentity = true;
}
foreach (var constraint in Constraints)
{
if (String.Equals(ConstraintTypeNames.Check, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase))
{
var exp = ExpressionBuilder.Build(constraint.CheckExpression);
constraints.Add(SqlTableConstraint.Check(null, exp));
}
else if (String.Equals(ConstraintTypeNames.ForeignKey, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase))
{
var fTable = constraint.ReferencedTable.Name;
var fColumn = constraint.ReferencedColumn.Text;
var onDelete = ForeignKeyAction.NoAction;
var onUpdate = ForeignKeyAction.NoAction;
if (!String.IsNullOrEmpty(constraint.OnDeleteAction))
{
onDelete = SqlCodeObjectBuilder.GetForeignKeyAction(constraint.OnDeleteAction);
}
if (!String.IsNullOrEmpty(constraint.OnUpdateAction))
{
onUpdate = SqlCodeObjectBuilder.GetForeignKeyAction(constraint.OnUpdateAction);
}
constraints.Add(SqlTableConstraint.ForeignKey(null, new[] { ColumnName }, fTable, new[] { fColumn }, onDelete, onUpdate));
}
else if (String.Equals(ConstraintTypeNames.PrimaryKey, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase))
{
constraints.Add(SqlTableConstraint.PrimaryKey(null, new[] { ColumnName }));
}
else if (String.Equals(ConstraintTypeNames.UniqueKey, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase))
{
constraints.Add(SqlTableConstraint.UniqueKey(null, new[] { ColumnName }));
}
else if (String.Equals(ConstraintTypeNames.NotNull, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase))
{
columnInfo.IsNotNull = true;
}
else if (String.Equals(ConstraintTypeNames.Null, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase))
{
columnInfo.IsNotNull = false;
}
}
return(columnInfo);
}
public static SqlTableConstraint Form(PlSqlParser.TableConstraintContext context)
{
string constraintName = Name.Simple(context.id());
ConstraintType type;
string[] columns = null;
string refTable = null;
string[] refColumns = null;
SqlExpression checkExp = null;
ForeignKeyAction?onDelete = null;
ForeignKeyAction?onUpdate = null;
if (context.primaryKeyConstraint() != null)
{
type = ConstraintType.PrimaryKey;
columns = context.primaryKeyConstraint().columnList().columnName().Select(Name.Simple).ToArray();
}
else if (context.uniqueKeyConstraint() != null)
{
type = ConstraintType.Unique;
columns = context.uniqueKeyConstraint().columnList().columnName().Select(Name.Simple).ToArray();
}
else if (context.checkConstraint() != null)
{
type = ConstraintType.Check;
checkExp = Expression.Build(context.checkConstraint().expression());
}
else if (context.foreignKeyConstraint() != null)
{
type = ConstraintType.ForeignKey;
columns = context.foreignKeyConstraint().columns.columnName().Select(Name.Simple).ToArray();
refColumns = context.foreignKeyConstraint().refColumns.columnName().Select(Name.Simple).ToArray();
refTable = Name.Object(context.foreignKeyConstraint().objectName()).ToString();
var refActions = context.foreignKeyConstraint().referentialAction();
if (refActions != null && refActions.Length > 0)
{
foreach (var action in refActions)
{
if (action.onDelete() != null)
{
var actionType = GetForeignKeyAction(action.onDelete().referentialActionType().GetText());
onDelete = actionType;
}
else if (action.onUpdate() != null)
{
var actionType = GetForeignKeyAction(action.onUpdate().referentialActionType().GetText());
onUpdate = actionType;
}
}
}
}
else
{
throw new ParseCanceledException("Invalid ");
}
var constraint = new SqlTableConstraint(constraintName, type, columns);
if (type == ConstraintType.ForeignKey)
{
constraint.ReferenceTable = refTable;
constraint.ReferenceColumns = refColumns;
constraint.OnUpdate = onUpdate ?? ForeignKeyAction.NoAction;
constraint.OnDelete = onDelete ?? ForeignKeyAction.NoAction;
}
else if (type == ConstraintType.Check)
{
constraint.CheckExpression = checkExp;
}
return(constraint);
}
