public static void AddOracleIdentity(MigrationBase migration,
string table, string id)
{
var seq = table.Replace(" ", "_").Replace("\"", "");
seq = seq.Substring(0, Math.Min(20, seq.Length));
seq = seq + "_SEQ";
migration.IfDatabase("Oracle")
.Execute.Sql("CREATE SEQUENCE " + seq);
migration.IfDatabase("Oracle")
.Execute.Sql(String.Format(@"
CREATE OR REPLACE TRIGGER {2}_TRG
BEFORE INSERT ON {0}
FOR EACH ROW
BEGIN
IF :new.{1} IS NULL THEN
SELECT {2}.nextval INTO :new.{1} FROM DUAL;
END IF;
END;", table, id, seq));
migration.IfDatabase("Oracle")
.Execute.Sql(@"ALTER TRIGGER " + seq + "_TRG ENABLE");
}
public static void CreateContactPersonAddressTable(MigrationBase migrationBase, string tableName = "contact_person_address", string referencedContactPersonTableName = "contact_person")
{
migrationBase.Create.Table(tableName).InSchema("dbo")
.WithColumn("AddressID").AsFixedLengthAnsiString(38).NotNullable().PrimaryKey()
.WithColumn("ContactPersonID").AsFixedLengthAnsiString(38).NotNullable()
.WithColumn("AddressLine1").AsAnsiString(255).Nullable()
.WithColumn("AddressLine2").AsAnsiString(255).Nullable()
.WithColumn("AddressLine3").AsAnsiString(255).Nullable()
.WithColumn("AddressLine4").AsAnsiString(255).Nullable()
.WithColumn("OrganisationID").AsFixedLengthAnsiString(38).Nullable();
migrationBase.Create.ForeignKey("FK_" + tableName + "_1")
.FromTable(tableName).InSchema("dbo").ForeignColumns("ContactPersonID")
.ToTable(referencedContactPersonTableName).InSchema("dbo").PrimaryColumns("ContactPersonID");
//Create.Index("FK_" + tableName + "_1")
// .OnTable(tableName).InSchema("dbo")
// .OnColumn("ContactPersonID").Ascending()
// .WithOptions()
// .NonClustered();
}
public static void CreateContactPersonTable(MigrationBase migrationBase, string tableName = "contact_person")
{
migrationBase.Create.Table(tableName).InSchema("dbo")
.WithColumn("ContactPersonID").AsFixedLengthAnsiString(38).NotNullable().PrimaryKey()
.WithColumn("Surname_field").AsAnsiString(255).Nullable()
.WithColumn("FirstName_field").AsAnsiString(255).Nullable()
.WithColumn("EmailAddress").AsAnsiString(255).Nullable()
.WithColumn("PhoneNumber").AsAnsiString(255).Nullable()
.WithColumn("CellNumber").AsAnsiString(255).Nullable()
.WithColumn("DateOfBirth").AsDateTime().Nullable()
.WithColumn("DateLastUpdated").AsDateTime().Nullable()
.WithColumn("UserLastUpdated").AsAnsiString(255).Nullable()
.WithColumn("MachineLastUpdated").AsAnsiString(255).Nullable()
.WithColumn("VersionNumber").AsInt32().Nullable()
.WithColumn("PK2_Prop1").AsAnsiString(255).Nullable()
.WithColumn("PK2_Prop2").AsAnsiString(255).Nullable()
.WithColumn("PK3_Prop").AsAnsiString(255).Nullable()
.WithColumn("OrganisationID").AsFixedLengthAnsiString(38).Nullable()
.WithColumn("UserLocked").AsAnsiString(45).Nullable()
.WithColumn("DateTimeLocked").AsDateTime().Nullable()
.WithColumn("MachineLocked").AsAnsiString(45).Nullable()
.WithColumn("OperatingSystemUserLocked").AsAnsiString(45).Nullable()
.WithColumn("Locked").AsInt16().Nullable()
.WithColumn("IntegerProperty").AsInt32().Nullable();
migrationBase.Create.ForeignKey("FK_" + tableName + "_1")
.FromTable(tableName).InSchema("dbo").ForeignColumns("OrganisationID")
.ToTable("organisation").InSchema("dbo").PrimaryColumns("OrganisationID");
}
