public void TestUpdateDatabaseOperation()
{
var operations = new List<MigrationOperation>();
//TODO: fill operations
var statments = new NpgsqlMigrationSqlGenerator().Generate(operations, BackendVersion.ToString());
//TODO: check statments
}
public void TestAddForeignKeyOperationCascadeDelete()
{
var operations = new List<MigrationOperation>();
var operation = new AddForeignKeyOperation();
operation.Name = "someFK";
operation.PrincipalTable = "somePrincipalTable";
operation.DependentTable = "someDependentTable";
operation.DependentColumns.Add("column1");
operation.DependentColumns.Add("column2");
operation.DependentColumns.Add("column3");
operation.CascadeDelete = true;
operations.Add(operation);
var statments = new NpgsqlMigrationSqlGenerator().Generate(operations, BackendVersion.ToString());
Assert.AreEqual(1, statments.Count());
Assert.AreEqual("ALTER TABLE \"someDependentTable\" ADD CONSTRAINT \"someFK\" FOREIGN KEY (\"column1\",\"column2\",\"column3\") REFERENCES \"somePrincipalTable\" ) ON DELETE CASCADE", statments.ElementAt(0).Sql);
}
public void TestRenameColumnOperation()
{
var operations = new List<MigrationOperation>();
operations.Add(new RenameColumnOperation("someTable", "someOldColumnName", "someNewColumnName"));
var statments = new NpgsqlMigrationSqlGenerator().Generate(operations, BackendVersion.ToString());
Assert.AreEqual(1, statments.Count());
Assert.AreEqual("ALTER TABLE \"someTable\" RENAME COLUMN \"someOldColumnName\" TO \"someNewColumnName\"", statments.ElementAt(0).Sql);
}
public void TestSqlOperation()
{
var operations = new List<MigrationOperation>();
operations.Add(new SqlOperation("SELECT someColumn FROM someTable"));
var statments = new NpgsqlMigrationSqlGenerator().Generate(operations, BackendVersion.ToString());
Assert.AreEqual(1, statments.Count());
Assert.AreEqual("SELECT someColumn FROM someTable", statments.ElementAt(0).Sql);
}
public void TestAddPrimaryKeyOperationClustered()
{
var operations = new List<MigrationOperation>();
var operation = new AddPrimaryKeyOperation();
operation.Table = "someTable";
operation.Name = "somePKName";
operation.Columns.Add("column1");
operation.Columns.Add("column2");
operation.Columns.Add("column3");
operation.IsClustered = true;
//TODO: PostgreSQL support something like IsClustered?
operations.Add(operation);
var statments = new NpgsqlMigrationSqlGenerator().Generate(operations, BackendVersion.ToString());
Assert.AreEqual(1, statments.Count());
Assert.AreEqual("ALTER TABLE \"someTable\" ADD CONSTRAINT \"somePKName\" PRIMARY KEY (\"column1\",\"column2\",\"column3\")", statments.ElementAt(0).Sql);
}
public void TestDropPrimaryKeyOperation()
{
var operations = new List<MigrationOperation>();
var operation = new DropPrimaryKeyOperation();
operation.Table = "someTable";
operation.Name = "somePKName";
operations.Add(operation);
var statments = new NpgsqlMigrationSqlGenerator().Generate(operations, BackendVersion.ToString());
Assert.AreEqual(1, statments.Count());
Assert.AreEqual("ALTER TABLE \"someTable\" DROP CONSTRAINT \"somePKName\"", statments.ElementAt(0).Sql);
}
public void TestCreateIndexOperationUnique()
{
var operations = new List<MigrationOperation>();
var operation = new CreateIndexOperation();
operation.Table = "someTable";
operation.Name = "someIndex";
operation.Columns.Add("column1");
operation.Columns.Add("column2");
operation.Columns.Add("column3");
operation.IsUnique = true;
operations.Add(operation);
var statments = new NpgsqlMigrationSqlGenerator().Generate(operations, BackendVersion.ToString());
Assert.AreEqual(1, statments.Count());
Assert.AreEqual("CREATE UNIQUE INDEX \"someTable_someIndex\" ON \"someTable\" (\"column1\",\"column2\",\"column3\")", statments.ElementAt(0).Sql);
}
public void RenameTableOperation()
{
var operations = new List<MigrationOperation>();
operations.Add(new RenameTableOperation("schema.someOldTableName", "someNewTablename"));
var statments = new NpgsqlMigrationSqlGenerator().Generate(operations, BackendVersion.ToString());
Assert.AreEqual(1, statments.Count());
Assert.AreEqual("ALTER TABLE \"schema\".\"someOldTableName\" RENAME TO \"someNewTablename\"", statments.ElementAt(0).Sql);
}
public void TestDropTableOperation()
{
var operations = new List<MigrationOperation>();
operations.Add(new DropTableOperation("someTable"));
var statments = new NpgsqlMigrationSqlGenerator().Generate(operations, BackendVersion.ToString());
Assert.AreEqual(1, statments.Count());
Assert.AreEqual("DROP TABLE \"someTable\"", statments.ElementAt(0).Sql);
}
public void TestDropIndexOperationTableNameWithSchema()
{
var operations = new List<MigrationOperation>();
operations.Add(new DropIndexOperation()
{
Name = "someIndex",
Table = "someSchema.someTable"
});
var statments = new NpgsqlMigrationSqlGenerator().Generate(operations, BackendVersion.ToString());
Assert.AreEqual(1, statments.Count());
Assert.AreEqual("DROP INDEX IF EXISTS someSchema.\"someTable_someIndex\"", statments.ElementAt(0).Sql);
}
public void TestCreateTableOperation()
{
var operations = new List<MigrationOperation>();
var operation = new CreateTableOperation("someSchema.someTable");
operation.Columns.Add(
new ColumnModel(PrimitiveTypeKind.String)
{
Name = "SomeString",
MaxLength = 233,
IsNullable = false
});
operation.Columns.Add(
new ColumnModel(PrimitiveTypeKind.String)
{
Name = "AnotherString",
IsNullable = true
});
operation.Columns.Add(
new ColumnModel(PrimitiveTypeKind.Binary)
{
Name = "SomeBytes"
});
operation.Columns.Add(
new ColumnModel(PrimitiveTypeKind.Int64)
{
Name = "SomeLong",
IsIdentity = true
});
operation.Columns.Add(
new ColumnModel(PrimitiveTypeKind.DateTime)
{
Name = "SomeDateTime"
});
operations.Add(operation);
var statments = new NpgsqlMigrationSqlGenerator().Generate(operations, BackendVersion.ToString());
Assert.AreEqual(2, statments.Count());
if (BackendVersion.Major > 9 || (BackendVersion.Major == 9 && BackendVersion.Minor > 2))
Assert.AreEqual("CREATE SCHEMA IF NOT EXISTS someSchema", statments.ElementAt(0).Sql);
else
Assert.AreEqual("CREATE SCHEMA someSchema", statments.ElementAt(0).Sql);
Assert.AreEqual("CREATE TABLE \"someSchema\".\"someTable\"(\"SomeString\" text NOT NULL DEFAULT '',\"AnotherString\" text,\"SomeBytes\" bytea,\"SomeLong\" serial8,\"SomeDateTime\" timestamp)", statments.ElementAt(1).Sql);
}
public void TestAlterColumnOperationDefaultAndNullable()
{
var operations = new List<MigrationOperation>();
operations.Add(new AlterColumnOperation("tableName", new ColumnModel(PrimitiveTypeKind.Double)
{
Name = "columnName",
DefaultValue = 2.3,
IsNullable = false
}, false));
var statments = new NpgsqlMigrationSqlGenerator().Generate(operations, BackendVersion.ToString());
Assert.AreEqual(3, statments.Count());
Assert.AreEqual("ALTER TABLE \"tableName\" ALTER COLUMN \"columnName\" TYPE float8", statments.ElementAt(0).Sql);
Assert.AreEqual("ALTER TABLE \"tableName\" ALTER COLUMN \"columnName\" SET NOT NULL", statments.ElementAt(1).Sql);
Assert.AreEqual("ALTER TABLE \"tableName\" ALTER COLUMN \"columnName\" SET DEFAULT 2.3", statments.ElementAt(2).Sql);
}
public void TestAddColumnOperationDefaultValueSql()
{
var operations = new List<MigrationOperation>();
operations.Add(new AddColumnOperation("tableName", new ColumnModel(PrimitiveTypeKind.Single)
{
Name = "columnName",
DefaultValueSql = "4.6"
}));
var statments = new NpgsqlMigrationSqlGenerator().Generate(operations, BackendVersion.ToString());
Assert.AreEqual(1, statments.Count());
Assert.AreEqual("ALTER TABLE \"tableName\" ADD \"columnName\" float4 DEFAULT 4.6", statments.ElementAt(0).Sql);
}
public void TestDropForeignKeyOperation()
{
var operations = new List<MigrationOperation>();
var operation = new DropForeignKeyOperation();
operation.Name = "someFK";
operation.DependentTable = "someTable";
operations.Add(operation);
var statments = new NpgsqlMigrationSqlGenerator().Generate(operations, BackendVersion.ToString());
Assert.AreEqual(1, statments.Count());
if (BackendVersion.Major > 8)
Assert.AreEqual("ALTER TABLE \"someTable\" DROP CONSTRAINT IF EXISTS \"someFK\"", statments.ElementAt(0).Sql);
else
Assert.AreEqual("ALTER TABLE \"someTable\" DROP CONSTRAINT \"someFK\"", statments.ElementAt(0).Sql);
}
public void TestMoveTableOperationNewSchemaIsNull()
{
var operations = new List<MigrationOperation>();
operations.Add(new MoveTableOperation("someOldSchema.someTable", null));
var statments = new NpgsqlMigrationSqlGenerator().Generate(operations, BackendVersion.ToString());
Assert.AreEqual(2, statments.Count());
if (BackendVersion.Major > 9 || (BackendVersion.Major == 9 && BackendVersion.Minor > 2))
Assert.AreEqual("CREATE SCHEMA IF NOT EXISTS dbo", statments.ElementAt(0).Sql);
else
Assert.AreEqual("CREATE SCHEMA dbo", statments.ElementAt(0).Sql);
Assert.AreEqual("ALTER TABLE \"someOldSchema\".\"someTable\" SET SCHEMA dbo", statments.ElementAt(1).Sql);
}
public void TestDefaultTypes()
{
var operations = new List<MigrationOperation>();
operations.Add(new AddColumnOperation("someTable",
new ColumnModel(PrimitiveTypeKind.Binary)
{
Name = "someByteaColumn",
DefaultValue = new byte[6] { 1, 2, 127, 128, 254, 255 }
}, false)
);
operations.Add(new AddColumnOperation("someTable",
new ColumnModel(PrimitiveTypeKind.Boolean)
{
Name = "someFalseBooleanColumn",
DefaultValue = false
}, false)
);
operations.Add(new AddColumnOperation("someTable",
new ColumnModel(PrimitiveTypeKind.Boolean)
{
Name = "someTrueBooleanColumn",
DefaultValue = true
}, false)
);
operations.Add(new AddColumnOperation("someTable",
new ColumnModel(PrimitiveTypeKind.Byte)
{
Name = "someByteColumn",
DefaultValue = 15
}, false)
);
operations.Add(new AddColumnOperation("someTable",
new ColumnModel(PrimitiveTypeKind.DateTime)
{
Name = "someDateTimeColumn",
DefaultValue = new DateTime(2014, 1, 31, 5, 15, 23, 435)
}, false)
);
operations.Add(new AddColumnOperation("someTable",
new ColumnModel(PrimitiveTypeKind.DateTimeOffset)
{
Name = "someDateTimeOffsetColumn",
DefaultValue = new DateTimeOffset(new DateTime(2014, 1, 31, 5, 18, 43, 186), TimeSpan.FromHours(1))
}, false)
);
operations.Add(new AddColumnOperation("someTable",
new ColumnModel(PrimitiveTypeKind.Decimal)
{
Name = "someDecimalColumn",
DefaultValue = 23432423.534534m
}, false)
);
operations.Add(new AddColumnOperation("someTable",
new ColumnModel(PrimitiveTypeKind.Double)
{
Name = "someDoubleColumn",
DefaultValue = 44.66
}, false)
);
operations.Add(new AddColumnOperation("someTable",
new ColumnModel(PrimitiveTypeKind.Guid)
{
Name = "someGuidColumn",
DefaultValue = new Guid("de303070-afb8-4ec1-bcb0-c637f3316501")
}, false)
);
operations.Add(new AddColumnOperation("someTable",
new ColumnModel(PrimitiveTypeKind.Int16)
{
Name = "someInt16Column",
DefaultValue = 16
}, false)
);
operations.Add(new AddColumnOperation("someTable",
new ColumnModel(PrimitiveTypeKind.Int32)
{
Name = "someInt32Column",
DefaultValue = 32
}, false)
);
operations.Add(new AddColumnOperation("someTable",
new ColumnModel(PrimitiveTypeKind.Int64)
{
Name = "someInt64Column",
DefaultValue = 64
}, false)
);
operations.Add(new AddColumnOperation("someTable",
new ColumnModel(PrimitiveTypeKind.SByte)
{
Name = "someSByteColumn",
DefaultValue = -24
}, false)
);
operations.Add(new AddColumnOperation("someTable",
new ColumnModel(PrimitiveTypeKind.Single)
{
Name = "someSingleColumn",
DefaultValue = 12.4f
}, false)
);
operations.Add(new AddColumnOperation("someTable",
new ColumnModel(PrimitiveTypeKind.String)
{
Name = "someStringColumn",
DefaultValue = "Hello EF"
}, false)
);
operations.Add(new AddColumnOperation("someTable",
new ColumnModel(PrimitiveTypeKind.Time)
{
Name = "someColumn",
DefaultValue = new TimeSpan(937840050067)//1 day, 2 hours, 3 minutes, 4 seconds, 5 miliseconds, 6 microseconds, 700 nanoseconds
}, false)
);
var statments = new NpgsqlMigrationSqlGenerator().Generate(operations, BackendVersion.ToString());
Assert.AreEqual(16, statments.Count());
Assert.AreEqual("ALTER TABLE \"someTable\" ADD \"someByteaColumn\" bytea DEFAULT E'\\\\01027F80FEFF'", statments.ElementAt(0).Sql);
Assert.AreEqual("ALTER TABLE \"someTable\" ADD \"someFalseBooleanColumn\" boolean DEFAULT FALSE", statments.ElementAt(1).Sql);
Assert.AreEqual("ALTER TABLE \"someTable\" ADD \"someTrueBooleanColumn\" boolean DEFAULT TRUE", statments.ElementAt(2).Sql);
Assert.AreEqual("ALTER TABLE \"someTable\" ADD \"someByteColumn\" int2 DEFAULT 15", statments.ElementAt(3).Sql);
Assert.AreEqual("ALTER TABLE \"someTable\" ADD \"someDateTimeColumn\" timestamp DEFAULT '2014-01-31 05:15:23.435'", statments.ElementAt(4).Sql);
Assert.AreEqual("ALTER TABLE \"someTable\" ADD \"someDateTimeOffsetColumn\" timestamptz DEFAULT '2014-01-31 05:18:43.186+01'", statments.ElementAt(5).Sql);
Assert.AreEqual("ALTER TABLE \"someTable\" ADD \"someDecimalColumn\" numeric DEFAULT 23432423.534534", statments.ElementAt(6).Sql);
Assert.AreEqual("ALTER TABLE \"someTable\" ADD \"someDoubleColumn\" float8 DEFAULT 44.66", statments.ElementAt(7).Sql);
Assert.AreEqual("ALTER TABLE \"someTable\" ADD \"someGuidColumn\" uuid DEFAULT 'de303070-afb8-4ec1-bcb0-c637f3316501'", statments.ElementAt(8).Sql);
Assert.AreEqual("ALTER TABLE \"someTable\" ADD \"someInt16Column\" int2 DEFAULT 16", statments.ElementAt(9).Sql);
Assert.AreEqual("ALTER TABLE \"someTable\" ADD \"someInt32Column\" int4 DEFAULT 32", statments.ElementAt(10).Sql);
Assert.AreEqual("ALTER TABLE \"someTable\" ADD \"someInt64Column\" int8 DEFAULT 64", statments.ElementAt(11).Sql);
Assert.AreEqual("ALTER TABLE \"someTable\" ADD \"someSByteColumn\" int2 DEFAULT -24", statments.ElementAt(12).Sql);
Assert.AreEqual("ALTER TABLE \"someTable\" ADD \"someSingleColumn\" float4 DEFAULT 12.4", statments.ElementAt(13).Sql);
Assert.AreEqual("ALTER TABLE \"someTable\" ADD \"someStringColumn\" text DEFAULT 'Hello EF'", statments.ElementAt(14).Sql);
Assert.AreEqual("ALTER TABLE \"someTable\" ADD \"someColumn\" interval DEFAULT '26:03:04.005007'", statments.ElementAt(15).Sql);
}
public void CreateTableInPublicSchema()
{
var statements = new NpgsqlMigrationSqlGenerator().Generate(new List<MigrationOperation> { new CreateTableOperation("public.some_table") }, BackendVersion.ToString()).ToList();
Assert.That(statements.Count, Is.EqualTo(1));
Assert.That(statements[0].Sql, Is.EqualTo("CREATE TABLE \"public\".\"some_table\"()"));
}