public AddConstraintAction(SqlTableConstraint constraint)
{
if (constraint == null)
throw new ArgumentNullException("constraint");
Constraint = constraint;
}
public AddConstraintAction(SqlTableConstraint constraint)
{
if (constraint == null)
{
throw new ArgumentNullException("constraint");
}
Constraint = constraint;
}
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;
}
public static void AddUnique()
{
var constraint = new SqlTableConstraint("unique_id", ConstraintType.Unique, new[] { "id" });
var statement = new AlterTableStatement(ObjectName.Parse("test"), new AddConstraintAction(constraint));
var sql = statement.ToString();
var expected = new StringBuilder();
expected.Append("ALTER TABLE test ADD CONSTRAINT unique_id UNIQUE(id)");
Assert.AreEqual(expected.ToString(), sql);
}
public static void AddPrimaryKey()
{
var constraint = new SqlTableConstraint(ConstraintType.PrimaryKey, new[] { "id" });
var statement = new AlterTableStatement(ObjectName.Parse("test"), new AddConstraintAction(constraint));
var sql = statement.ToString();
var expected = new StringBuilder();
expected.Append("ALTER TABLE test ADD CONSTRAINT PRIMARY KEY(id)");
Assert.AreEqual(expected.ToString(), sql);
}
public static void AddUnique()
{
var constraint = new SqlTableConstraint("unique_id", ConstraintType.Unique, new[] { "id" });
var statement = new AlterTableStatement(ObjectName.Parse("test"), new AddConstraintAction(constraint));
var sql = statement.ToString();
var expected = new StringBuilder();
expected.Append("ALTER TABLE test ADD CONSTRAINT unique_id UNIQUE(id)");
Assert.AreEqual(expected.ToString(), sql);
}
public static void AddPrimaryKey()
{
var constraint = new SqlTableConstraint(ConstraintType.PrimaryKey, new[] { "id" });
var statement = new AlterTableStatement(ObjectName.Parse("test"), new AddConstraintAction(constraint));
var sql = statement.ToString();
var expected = new StringBuilder();
expected.Append("ALTER TABLE test ADD CONSTRAINT PRIMARY KEY(id)");
Assert.AreEqual(expected.ToString(), sql);
}
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 static void AddCheck()
{
var constraint = new SqlTableConstraint(ConstraintType.Check) {
CheckExpression = SqlExpression.Equal(SqlExpression.Reference(new ObjectName("a")),
SqlExpression.Constant(2))
};
var statement = new AlterTableStatement(ObjectName.Parse("test"), new AddConstraintAction(constraint));
var sql = statement.ToString();
var expected = new StringBuilder();
expected.Append("ALTER TABLE test ADD CONSTRAINT ");
expected.Append("CHECK a = 2");
Assert.AreEqual(expected.ToString(), sql);
}
public static void AddCheck()
{
var constraint = new SqlTableConstraint(ConstraintType.Check)
{
CheckExpression = SqlExpression.Equal(SqlExpression.Reference(new ObjectName("a")),
SqlExpression.Constant(2))
};
var statement = new AlterTableStatement(ObjectName.Parse("test"), new AddConstraintAction(constraint));
var sql = statement.ToString();
var expected = new StringBuilder();
expected.Append("ALTER TABLE test ADD CONSTRAINT ");
expected.Append("CHECK a = 2");
Assert.AreEqual(expected.ToString(), sql);
}
public static void SimpleAddForeignKey()
{
var constraint = new SqlTableConstraint(ConstraintType.ForeignKey, new[] { "ref_id" })
{
ReferenceTable = "test_table1",
ReferenceColumns = new[] { "id" }
};
var statement = new AlterTableStatement(ObjectName.Parse("test"), new AddConstraintAction(constraint));
var sql = statement.ToString();
var expected = new StringBuilder();
expected.Append("ALTER TABLE test ADD CONSTRAINT FOREIGN KEY(ref_id) ");
expected.Append("REFERENCES test_table1(id) ");
expected.Append("ON DELETE NO ACTION ");
expected.Append("ON UPDATE NO ACTION");
Assert.AreEqual(expected.ToString(), sql);
}
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 static void Serialize(SqlTableConstraint constraint, BinaryWriter writer)
{
throw new NotImplementedException();
}
private static SqlStatement MakeAlterTableAddConstraint(string tableName, SqlTableConstraint constraint)
{
var action = new AddConstraintAction(constraint);
return new AlterTableStatement(ObjectName.Parse(tableName), action);
}
public static void Serialize(SqlTableConstraint constraint, BinaryWriter writer)
{
throw new NotImplementedException();
}
public static void SimpleAddForeignKey()
{
var constraint = new SqlTableConstraint(ConstraintType.ForeignKey, new[] { "ref_id" }) {
ReferenceTable = "test_table1",
ReferenceColumns = new[] { "id" }
};
var statement = new AlterTableStatement(ObjectName.Parse("test"), new AddConstraintAction(constraint));
var sql = statement.ToString();
var expected = new StringBuilder();
expected.Append("ALTER TABLE test ADD CONSTRAINT FOREIGN KEY(ref_id) ");
expected.Append("REFERENCES test_table1(id) ");
expected.Append("ON DELETE NO ACTION ");
expected.Append("ON UPDATE NO ACTION");
Assert.AreEqual(expected.ToString(), sql);
}
