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);
}
}
private void CreateTables(IQuery query, string testsName)
{
var columns = new List <SqlTableColumn> {
new SqlTableColumn("id", PrimitiveTypes.Integer()),
new SqlTableColumn("name", PrimitiveTypes.String())
};
if (testsName.Equals("NotNullColumnViolation"))
{
columns.Add(new SqlTableColumn("age", PrimitiveTypes.Integer())
{
IsNotNull = true
});
}
query.CreateTable(new ObjectName("a"), columns.ToArray());
query.AddPrimaryKey(new ObjectName("a"), new [] { "id" });
query.AddUniqueKey(new ObjectName("a"), new[] { "name" });
query.CreateTable(new ObjectName("b"), new SqlTableColumn("id", PrimitiveTypes.Integer()),
new SqlTableColumn("other_id", PrimitiveTypes.Integer()));
query.AddPrimaryKey(new ObjectName("b"), new[] { "id" });
query.AddForeignKey(new ObjectName("b"), new[] { "other_id" }, new ObjectName("a"), new[] { "id" },
ForeignKeyAction.Cascade, ForeignKeyAction.NoAction);
}
private void CreateTestTables(IQuery context)
{
var tn1 = ObjectName.Parse("APP.test_table1");
var tableInfo1 = new TableInfo(tn1);
tableInfo1.AddColumn(new ColumnInfo("id", PrimitiveTypes.Integer()));
tableInfo1.AddColumn(new ColumnInfo("name", PrimitiveTypes.String()));
tableInfo1.AddColumn(new ColumnInfo("date", PrimitiveTypes.DateTime()));
context.CreateTable(tableInfo1);
context.AddPrimaryKey(tn1, "id");
var tn2 = ObjectName.Parse("APP.test_table2");
var tableInfo2 = new TableInfo(tn2);
tableInfo2.AddColumn(new ColumnInfo("id", PrimitiveTypes.Integer()));
tableInfo2.AddColumn(new ColumnInfo("other_id", PrimitiveTypes.Integer()));
tableInfo2.AddColumn(new ColumnInfo("count", PrimitiveTypes.Integer()));
context.CreateTable(tableInfo2);
context.AddPrimaryKey(tn2, "id");
context.AddForeignKey(tn2, new[] { "other_id" }, tn1, new[] { "id" }, ForeignKeyAction.Cascade, ForeignKeyAction.Cascade, null);
}
private void CreateTables(IQuery query, string testsName)
{
var columns = new List<SqlTableColumn> {
new SqlTableColumn("id", PrimitiveTypes.Integer()),
new SqlTableColumn("name", PrimitiveTypes.String())
};
if (testsName.Equals("NotNullColumnViolation"))
columns.Add(new SqlTableColumn("age", PrimitiveTypes.Integer()) {
IsNotNull = true
});
query.CreateTable(new ObjectName("a"), columns.ToArray());
query.AddPrimaryKey(new ObjectName("a"), new []{"id"});
query.AddUniqueKey(new ObjectName("a"), new[] {"name"});
query.CreateTable(new ObjectName("b"), new SqlTableColumn("id", PrimitiveTypes.Integer()),
new SqlTableColumn("other_id", PrimitiveTypes.Integer()));
query.AddPrimaryKey(new ObjectName("b"), new[] {"id"});
query.AddForeignKey(new ObjectName("b"), new[] {"other_id"}, new ObjectName("a"), new[] {"id"},
ForeignKeyAction.Cascade, ForeignKeyAction.NoAction);
}
private void CreateTestTables(IQuery context)
{
var tn1 = ObjectName.Parse("APP.test_table1");
var tableInfo1 = new TableInfo(tn1);
tableInfo1.AddColumn(new ColumnInfo("id", PrimitiveTypes.Integer()));
tableInfo1.AddColumn(new ColumnInfo("name", PrimitiveTypes.String()));
tableInfo1.AddColumn(new ColumnInfo("date", PrimitiveTypes.DateTime()));
context.CreateTable(tableInfo1);
context.AddPrimaryKey(tn1, "id");
var tn2 = ObjectName.Parse("APP.test_table2");
var tableInfo2 = new TableInfo(tn2);
tableInfo2.AddColumn(new ColumnInfo("id", PrimitiveTypes.Integer()));
tableInfo2.AddColumn(new ColumnInfo("other_id", PrimitiveTypes.Integer()));
tableInfo2.AddColumn(new ColumnInfo("count", PrimitiveTypes.Integer()));
context.CreateTable(tableInfo2);
context.AddPrimaryKey(tn2, "id");
context.AddForeignKey(tn2, new[] { "other_id" }, tn1, new[] { "id" }, ForeignKeyAction.Cascade, ForeignKeyAction.Cascade, null);
}
private static void CreateSecurityTables(IQuery context)
{
var tableInfo = new TableInfo(UserTableName);
tableInfo.AddColumn("name", PrimitiveTypes.String());
// TODO: User table must be completed ...
tableInfo = tableInfo.AsReadOnly();
context.CreateSystemTable(tableInfo);
context.AddPrimaryKey(UserTableName, new [] { "name" }, "SYSTEM_USER_PK");
tableInfo = new TableInfo(PasswordTableName);
tableInfo.AddColumn("user", PrimitiveTypes.String());
tableInfo.AddColumn("method", PrimitiveTypes.String());
tableInfo.AddColumn("method_args", PrimitiveTypes.Binary());
tableInfo.AddColumn("identifier", PrimitiveTypes.String());
tableInfo = tableInfo.AsReadOnly();
context.CreateSystemTable(tableInfo);
tableInfo = new TableInfo(UserGroupTableName);
tableInfo.AddColumn("user", PrimitiveTypes.String());
tableInfo.AddColumn("group", PrimitiveTypes.String());
tableInfo.AddColumn("admin", PrimitiveTypes.Boolean());
tableInfo = tableInfo.AsReadOnly();
context.CreateSystemTable(tableInfo);
tableInfo = new TableInfo(GroupsTableName);
tableInfo.AddColumn("name", PrimitiveTypes.String(), true);
tableInfo = tableInfo.AsReadOnly();
context.CreateSystemTable(tableInfo);
context.AddPrimaryKey(GroupsTableName, new[] { "name" }, "SYSTEM_GROUP_PK");
tableInfo = new TableInfo(UserGrantsTableName);
tableInfo.AddColumn("priv_bit", PrimitiveTypes.Numeric());
tableInfo.AddColumn("object", PrimitiveTypes.Numeric());
tableInfo.AddColumn("name", PrimitiveTypes.String());
tableInfo.AddColumn("user", PrimitiveTypes.String());
tableInfo.AddColumn("grant_option", PrimitiveTypes.Boolean());
tableInfo.AddColumn("granter", PrimitiveTypes.String());
tableInfo = tableInfo.AsReadOnly();
context.CreateSystemTable(tableInfo);
tableInfo = new TableInfo(GroupGrantsTable);
tableInfo.AddColumn("priv_bit", PrimitiveTypes.Numeric());
tableInfo.AddColumn("object", PrimitiveTypes.Numeric());
tableInfo.AddColumn("name", PrimitiveTypes.String());
tableInfo.AddColumn("group", PrimitiveTypes.String());
tableInfo.AddColumn("grant_option", PrimitiveTypes.Boolean());
tableInfo.AddColumn("granter", PrimitiveTypes.String());
var fkCol = new[] { "user" };
var gfkCol = new[] { "group" };
var refCol = new[] { "name" };
const ForeignKeyAction onUpdate = ForeignKeyAction.NoAction;
const ForeignKeyAction onDelete = ForeignKeyAction.Cascade;
context.AddForeignKey(PasswordTableName, fkCol, UserTableName, refCol, onDelete, onUpdate, "USER_PASSWORD_FK");
context.AddForeignKey(UserGroupTableName, fkCol, UserTableName, refCol, onDelete, onUpdate, "USER_PRIV_FK");
context.AddForeignKey(UserGroupTableName, gfkCol, GroupsTableName, refCol, onDelete, onUpdate, "USER_GROUP_FK");
context.AddForeignKey(UserGrantsTableName, fkCol, UserTableName, refCol, onDelete, onUpdate, "USER_GRANTS_FK");
context.AddForeignKey(GroupGrantsTable, gfkCol, GroupsTableName, refCol, onDelete, onUpdate, "GROUP_GRANTS_FK");
}
