public async Task <MigrationResult> ExecuteAsync()
{
// Fail if cannot connect to db
if (_identityDb.Database.TryTestConnection() == false)
{
return(MigrationResult.Failed("Cannot connect to database."));
}
// Skip if User.TenantId already exists
if (_identityDb.Database.HasColumn("User", "auth", "TenantId"))
{
return(MigrationResult.Skipped("Column [User].[TenantId] already exists."));
}
// Create the column - allowing nulls temporarily
// This will be changed in the 2nd part of this job (A26015)
await _identityDb.Database.ExecuteNonQueryAsync("ALTER TABLE [auth].[User] ADD [TenantId] nvarchar(450) NULL");
return(MigrationResult.Success("Added column [auth].[User].[TenantId]."));
}
public async Task <MigrationResult> ExecuteAsync()
{
// Fail if cannot connect to db
if (_dbContext.Database.TryTestConnection() == false)
{
return(MigrationResult.Failed("Cannot connect to database."));
}
// Skip if bg color already exists
if (_dbContext.Database.HasColumn("SlideShowLayer", "plugin", "BgColor"))
{
return(MigrationResult.Skipped("Column SlideShowLayer.BgColor already exists."));
}
await _dbContext.Database.ExecuteNonQueryAsync("ALTER TABLE [plugin].[SlideShowLayer] ADD [BgColor] [nvarchar](50) NULL");
await _dbContext.Database.ExecuteNonQueryAsync("ALTER TABLE [plugin].[SlideShowLayer] ADD [FontWeight] [nvarchar](50) NULL");
await _dbContext.Database.ExecuteNonQueryAsync("ALTER TABLE [plugin].[SlideShowLayer] ADD [FontStyle] [nvarchar](50) NULL");
await _dbContext.Database.ExecuteNonQueryAsync("ALTER TABLE [plugin].[SlideShowLayer] ADD [TextDecoration] [nvarchar](50) NULL");
return(MigrationResult.Success("Added columns [SlideShowLayer].[BgColor], [FontWeight], [FontStyle], [TextDecoration] "));
}
public async Task <MigrationResult> ExecuteAsync()
{
if (_dbContext.Database.TableExists("VideoBackgroundWidget", "plugin"))
{
return(MigrationResult.Skipped());
}
// else create the tables
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[VideoBackgroundWidget](
[Id] [nvarchar](50) NOT NULL,
[VideoSourceType] [nvarchar](500) NULL,
[YoutubeVideoId] [nvarchar](50) NULL,
[VimeoVideoId] [nvarchar](50) NULL,
[Positioning] [nvarchar](500) NULL,
[Autoplay] [bit] DEFAULT (1) NOT NULL,
[ShowPlayerControls] [bit] DEFAULT (0) NOT NULL,
[VideoUrl] [nvarchar](500) NULL,
CONSTRAINT [PK_VideoBackgroundLink] PRIMARY KEY CLUSTERED ([Id] ASC)
)
");
return(MigrationResult.Success());
}
public async Task <MigrationResult> ExecuteAsync()
{
if (_dbContext.Database.TableExists("AnnouncementPost", "plugin"))
{
return(MigrationResult.Skipped());
}
// else create the tables
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[AnnouncementPost](
[Id] [nvarchar](450) NOT NULL,
[DocumentId] [nvarchar](50) NOT NULL,
[ContentTreeId] [nvarchar](50) NULL,
[Title] [nvarchar](500) NOT NULL,
[Image] [nvarchar](255) NULL,
[Caption] [nvarchar](1000) NULL,
[Excerp] [nvarchar](MAX) NULL,
[Content] [nvarchar](MAX) NULL,
[Posted] [datetime] NOT NULL,
[UserId] [nvarchar](450) NOT NULL,
[PrivateCommentsAllowed] [bit] NOT NULL,
[PrivateCommentsModerated] [bit] NOT NULL,
[PublicCommentsAllowed] [bit] NOT NULL,
[PublicCommentsModerated] [bit] NOT NULL,
CONSTRAINT [PK_AnnouncementPost] PRIMARY KEY CLUSTERED ([Id] ASC)
)
");
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[AnnouncementPostTag](
[Id] [nvarchar](450) NOT NULL,
[PostId] [nvarchar](450) NOT NULL,
[TagId] [nvarchar](450) NOT NULL,
[IsActive] [bit] NOT NULL,
CONSTRAINT [PK_AnnouncementPostTag] PRIMARY KEY CLUSTERED ([Id] ASC)
)
");
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[AnnouncementPostCategory](
[Id] [nvarchar](450) NOT NULL,
[PostId] [nvarchar](450) NOT NULL,
[CategoryId] [nvarchar](450) NOT NULL,
[IsActive] [bit] NOT NULL,
CONSTRAINT [PK_AnnouncementPostCategory] PRIMARY KEY CLUSTERED ([Id] ASC)
)
");
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[AnnouncementCategory](
[Id] [nvarchar](450) NOT NULL,
[Title] [nvarchar](450) NOT NULL,
[UserId] [nvarchar](450) NOT NULL,
[IsActive] [bit] NOT NULL,
CONSTRAINT [PK_AnnouncementCategory] PRIMARY KEY CLUSTERED ([Id] ASC)
)
");
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[AnnouncementWidget](
[Id] [nvarchar](450) NOT NULL,
[Title] [nvarchar](100) NOT NULL,
[PageSize] [int] NOT NULL,
[CreateAnnouncement] [bit] NOT NULL,
[AnnouncementId] [nvarchar] (450),
CONSTRAINT [PK_AnnouncementWidget] PRIMARY KEY CLUSTERED ([Id] ASC)
)
");
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[AnnouncementWidgetTag](
[Id] [nvarchar](450) NOT NULL,
[WidgetId] [nvarchar](450) NOT NULL,
[TagId] [nvarchar](450) NOT NULL,
CONSTRAINT [PK_AnnouncementWidgetTag] PRIMARY KEY CLUSTERED ([Id] ASC)
)
");
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[AnnouncementWidgetCategory](
[Id] [nvarchar](450) NOT NULL,
[WidgetId] [nvarchar](450) NOT NULL,
[CategoryId] [nvarchar](450) NOT NULL,
CONSTRAINT [PK_AnnouncementWidgetCategory] PRIMARY KEY CLUSTERED ([Id] ASC)
)
");
return(MigrationResult.Success());
}
public async Task <MigrationResult> ExecuteAsync()
{
// If any calendar table exists then skip
if (_dbContext.Database.TableExists("CalendarEvent", "plugin"))
{
return(MigrationResult.Skipped());
}
// CalendarEvent table
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[CalendarEvent](
[EventId] [nvarchar](50) NOT NULL,
[DocumentId] [nvarchar](50) NOT NULL,
[Title] [nvarchar](500) NOT NULL,
[Posted] [datetime] NOT NULL,
[Style] [nvarchar] (50),
[BackgroundColor] [nvarchar] (50),
[Description] [nvarchar] (max),
[EventStart] [datetime] NOT NULL,
[EventEnd] [datetime] NOT NULL,
[SiteId] [nvarchar](450) NOT NULL,
[UserId] [nvarchar] (450) NULL,
[Phone] [nvarchar] (20) NULL,
[AllDayEvent] [bit] NULL,
[Url] [nvarchar] (450) NULL,
[IsRecurrent] [bit] NOT NULL,
[Location] [nvarchar] (450) NULL,
[ShowOrganizerName] [bit] NULL,
[ShowPhoneNumber] [bit] NULL,
CONSTRAINT [PK_CalendarEvent] PRIMARY KEY CLUSTERED ([EventId] ASC)
)
");
// Calendar Widget Table
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[CalendarWidgetSetting](
[Id] [nvarchar](50) NOT NULL,
[CalendarId] [nvarchar](50),
[Title] [nvarchar] (200) NULL,
[DefaultView] [nvarchar](100) NOT NULL,
[StartDayOfWeek] [nvarchar] (50),
[Format12] [bit],
[SiteId] [nvarchar](50) NOT NULL,
[HideWeekends] [bit] NOT NULL,
CONSTRAINT [PK_CalendarWidgetSetting] PRIMARY KEY CLUSTERED ([Id] ASC)
)
");
// CalendarEventGroup Table
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[CalendarEventGroup](
[EventGroupId] [nvarchar](50) NOT NULL,
[SiteId] [nvarchar](450) NOT NULL,
[UserId] [nvarchar] (450) NULL,
[Title] [nvarchar](200) NULL,
CONSTRAINT [PK_CalendarEventGroup] PRIMARY KEY CLUSTERED ([EventGroupId] ASC)
)
");
// CalendarEvent to CalendarEventGroup Mapping Table
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[CalendarEventGroupEvent](
[EventId] [nvarchar](450) NOT NULL,
[EventGroupId] [nvarchar] (450) NOT NULL,
CONSTRAINT [PK_CalendarEventGroupEvent] PRIMARY KEY CLUSTERED ([EventId], [EventGroupId])
)
");
// CalendarWidget to CalendarEventGroup Mapping Table
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[CalendarWidgetEventGroup](
[Id] [nvarchar](50) NOT NULL,
[WidgetId] [nvarchar](450) NOT NULL,
[EventGroupId] [nvarchar] (450) NULL,
[Color] [nvarchar] (20) NULL,
[Title] [nvarchar] (20) NULL,
CONSTRAINT [PK_CalendarWidgetEventGroup] PRIMARY KEY CLUSTERED ([Id] ASC)
)
");
// CalendarEventTag Table
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[CalendarEventTag](
[Id] [nvarchar](50) NOT NULL,
[EventId] [nvarchar](50),
[TagId] [nvarchar](50) NULL,
CONSTRAINT [PK_CalendarEventTag] PRIMARY KEY CLUSTERED ([Id] ASC)
)
");
// CalendarEventRecurrence Table
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[CalendarEventRecurrence](
[Id] [nvarchar](50) NOT NULL,
[EventId] [nvarchar](50),
[Frequency] [nvarchar](50) NOT NULL,
[Interval] [int] NOT NULL,
[EndDate] [datetime] NULL,
[Count] [int] NULL,
[DaysOfWeek] [nvarchar](50) NULL,
[DayOfMonth] [int] NULL,
[Months] [nvarchar] (50) NULL,
CONSTRAINT [PK_CalendarEventRecurrence] PRIMARY KEY CLUSTERED ([EventId] ASC)
)
");
return(MigrationResult.Success());
}
public async Task <MigrationResult> ExecuteAsync()
{
var output = new System.Text.StringBuilder();
var oldSchema = "jobs";
var newSchema = "app";
var chronJobTable = "CronJobs";
var delayedJobTable = "DelayedJobs";
var jobMetaTable = "Schema";
// If the database does not yet exist, we can skip because will be
// built correctly on new environments
if (_connectDb.Database.TryTestConnection() == true)
{
// insert new schema (if needed)
if (_connectDb.Database.SchemaExists(newSchema) == false)
{
_connectDb.Database.CreateSchema(newSchema);
output.Append($"Created schema [{newSchema}]. ");
}
// Move tables to new schema (if needed)
if (_connectDb.Database.TableExists(chronJobTable, newSchema) == false)
{
if (_connectDb.Database.TableExists(chronJobTable, oldSchema) == true)
{
_connectDb.Database.AlterTableSchema(chronJobTable, oldSchema, newSchema);
output.Append($"Altered table [{chronJobTable}]. ");
}
}
if (_connectDb.Database.TableExists(delayedJobTable, newSchema) == false)
{
if (_connectDb.Database.TableExists(delayedJobTable, oldSchema) == true)
{
_connectDb.Database.AlterTableSchema(delayedJobTable, oldSchema, newSchema);
output.Append($"Altered table [{delayedJobTable}]. ");
}
}
// It's possible that new tables and old tables exist side-by-side
// If so, let's remove the old ones
if (_connectDb.Database.TableExists(jobMetaTable, oldSchema) == true)
{
_connectDb.Database.DropTable(jobMetaTable, oldSchema);
_connectDb.Database.DropTable(chronJobTable, oldSchema);
_connectDb.Database.DropTable(delayedJobTable, oldSchema);
output.Append($"Removed old jobs tables. ");
}
// finally, let's drop the old "jobs" schema if it exists
if (_connectDb.Database.SchemaExists(oldSchema) == true)
{
_connectDb.Database.DropSchema(oldSchema);
output.Append($"Removed old jobs schema. ");
}
}
// if we have any messages, then we did something.
// otherwise everything got skipped
var message = output.ToString().Trim();
var result = string.IsNullOrEmpty(message)
? MigrationResult.Skipped()
: MigrationResult.Success(message);
return(await Task.FromResult(result));
}
public async Task <MigrationResult> ExecuteAsync()
{
if (_dbContext.Database.TableExists("SlideShowWidget", "plugin"))
{
return(MigrationResult.Skipped());
}
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[SlideShowWidget](
[Id] [nvarchar](50) NOT NULL,
[SiteId] [nvarchar](50) NOT NULL,
[Title] [nvarchar](500) NULL,
[Description] [nvarchar](MAX) NULL,
[BackgroundColor] [nvarchar] (100) NULL,
[Transition] [INT] NULL,
[Duration] [INT] NOT NULL,
[Height] [nvarchar] (10) NULL,
CONSTRAINT [PK_SlideShowWidget] PRIMARY KEY CLUSTERED ([Id] ASC)
)
");
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[SlideShowSlide](
[WidgetId] [nvarchar](50) NOT NULL,
[DocumentId] [nvarchar](50) NOT NULL,
[Title] [nvarchar](500) NULL,
[Description] [nvarchar](MAX) NULL,
[Delay] [INT] NULL,
[State] [INT] NULL,
[VisibleFrom] [datetime2] NULL,
[VisibleTo] [datetime2] NULL,
[ThumbnailUrl] [nvarchar] (2048) NULL,
[ImageUrl] [nvarchar] (2048) NULL,
[Color] [nvarchar] (100) NULL,
[ImageSourceSize] [nvarchar] (100) NULL,
[Position] [INT] NULL,
[Tiling] [INT] NULL,
[BackgroundFit] [INT] NOT NULL,
[ParallaxId] [nvarchar] (50) NULL,
[KenBurnsEffectId] [nvarchar] (50) NULL,
[Transition] [INT] NULL,
[SlotBoxAmount] [INT] NULL,
[SlotRotation] [INT] NULL,
[Direction] [INT] NULL,
[Duration] [INT] NULL,
[IsLinkEnabled] [bit] NULL,
[SlideLinkUrl] [nvarchar] (2048) NULL,
[LinkTarget] [INT] NULL,
CONSTRAINT [PK_SlideShowSlide] PRIMARY KEY CLUSTERED ([DocumentId] ASC)
)
");
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[SlideShowLayer](
[Id] [nvarchar](50) NOT NULL,
[SlideId] [nvarchar](50) NOT NULL,
[Title] [nvarchar](50) NOT NULL,
[SourceUrl] [nvarchar](50) NULL,
[Target] [nvarchar] (50) NULL,
[LayerType] [nvarchar] (50) NULL,
[X] [INT] NULL,
[Y] [INT] NULL,
[Height] [INT] NULL,
[Width] [INT] NULL,
[Transition] [nvarchar] (50) NULL,
[Position] [nvarchar] (50) NULL,
[Delay] [nvarchar] (50) NULL,
[FontFamily] [nvarchar](100) NULL,
[FontSize] [INT] NULL,
[Color] [nvarchar](100) NULL,
[HorizontalAlignment] [INT] NULL,
[VerticalAlignment] [INT] NULL,
[FadeInTransition] [INT] NULL,
[FadeInDirection] [INT] NULL,
[FadeInDuration] [INT] NULL,
[FadeInDelay] [INT] NULL,
[FadeOutTransition] [INT] NULL,
[FadeOutDirection] [INT] NULL,
[FadeOutDuration] [INT] NULL,
[FadeOutDelay] [INT] NULL,
CONSTRAINT [PK_SlideShowLayer] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_SlideShowLayer_SlideShowSlide] FOREIGN KEY (SlideId) REFERENCES [plugin].[SlideShowSlide](DocumentId)
)
");
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[SlideShowParallax](
[Id] [nvarchar](50) NOT NULL,
[SlideId] [nvarchar](50) NOT NULL,
[IsEnabled] [BIT] NOT NULL,
[ParallaxEvent] [INT] NOT NULL,
[ParallaxOrigin] [INT] NOT NULL,
[AnimationSpeed] [INT] NOT NULL,
[Level] [INT] NOT NULL,
[IsMobileDisabled] [BIT] NOT NULL,
[LevelDepth1] [INT] NULL,
[LevelDepth2] [INT] NULL,
[LevelDepth3] [INT] NULL,
[LevelDepth4] [INT] NULL,
[LevelDepth5] [INT] NULL,
[LevelDepth6] [INT] NULL,
[LevelDepth7] [INT] NULL,
[LevelDepth8] [INT] NULL,
[LevelDepth9] [INT] NULL,
[LevelDepth10] [INT] NULL,
CONSTRAINT [PK_Parallax] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_Parallax_SlideShowSlide] FOREIGN KEY (SlideId) REFERENCES [plugin].[SlideShowSlide](DocumentId)
)
");
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[SlideShowKenBurnsEffect](
[Id] [nvarchar](50) NOT NULL,
[SlideId] [nvarchar](50) NOT NULL,
[IsEnabled] [BIT] NOT NULL,
[ScaleFrom] [INT] NOT NULL,
[ScaleTo] [INT] NOT NULL,
[HorizontalOffsetFrom] [INT] NOT NULL,
[HorizontalOffsetTo] [INT] NOT NULL,
[VerticalOffsetFrom] [INT] NOT NULL,
[VerticalOffsetTo] [INT] NOT NULL,
[RotationFrom] [INT] NOT NULL,
[RotationTo] [INT] NOT NULL,
[Duration] [INT] NOT NULL,
CONSTRAINT [PK_KenBurnsEffect] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_KenBurnsEffect_SlideShowSlide] FOREIGN KEY (SlideId) REFERENCES [plugin].[SlideShowSlide](DocumentId)
)
");
return(MigrationResult.Success());
}
public async Task <MigrationResult> ExecuteAsync()
{
var output = new System.Text.StringBuilder();
var newSchema = "app";
var newTableName = "MigrationsEF";
var oldSchema = "dbo";
var oldTableName = "__EFMigrationsHistory";
// If the database does not yet exist, we can skip because EF
// will build it's internal migration tables correctly using new names
if (_connectDb.Database.TryTestConnection() == true)
{
// otherwise, manually insert schema (if needed)
if (_connectDb.Database.SchemaExists(newSchema) == false)
{
_connectDb.Database.CreateSchema(newSchema);
output.Append("Created Connect App Schema. ");
}
// then alter old EF migration table (if old table exists)
if (_connectDb.Database.TableExists(oldTableName, oldSchema) == true)
{
_connectDb.Database.AlterTableName(oldTableName, oldSchema, newTableName);
_connectDb.Database.AlterTableSchema(newTableName, oldSchema, newSchema);
output.Append("Altered Connect EF Table. ");
}
}
// Rinse & repeat for identity db
if (_identityDb.Database.TryTestConnection() == true)
{
// manually insert schema (if needed)
if (_identityDb.Database.SchemaExists(newSchema) == false)
{
_identityDb.Database.CreateSchema(newSchema);
output.Append("Created Identity App Schema. ");
}
// alter EF migration table (if old schema.table exists)
if (_identityDb.Database.TableExists(oldTableName, oldSchema))
{
_identityDb.Database.AlterTableName(oldTableName, oldSchema, newTableName);
_identityDb.Database.AlterTableSchema(newTableName, oldSchema, newSchema);
output.Append("Altered Identity EF Table. ");
}
}
// check our log to determine if we did any work and return result
var message = output.ToString().Trim();
var result = string.IsNullOrEmpty(message)
? MigrationResult.Skipped()
: MigrationResult.Success(message);
return(await Task.FromResult(result));
}
public async Task <MigrationResult> ExecuteAsync()
{
var output = new System.Text.StringBuilder();
// Fail if cannot connect to db
if (_identityDb.Database.TryTestConnection() == false)
{
return(MigrationResult.Failed("Cannot connect to database."));
}
var existingTenants = await _identityDb.Tenants.ToListAsync();
// Skip if any Tenant.Key data exists (all should be null)
if (existingTenants.Any(x => x.Key != null))
{
return(MigrationResult.Skipped("No action required"));
}
// 1) Remove Index and Foreign Key Constraints for Tenant.Id (temporarily)
//-------------------------------------------------------------------------------
// Directories
_identityDb.Database.ExecuteNonQuery("ALTER TABLE [auth].[Directory] DROP CONSTRAINT [FK_Directory_Tenant_TenantId]");
_identityDb.Database.ExecuteNonQuery("DROP INDEX [IX_Directory_TenantId] ON [auth].[Directory]");
// Security Pools
_identityDb.Database.ExecuteNonQuery("ALTER TABLE [auth].[SecurityPool] DROP CONSTRAINT [FK_SecurityPool_Tenant_TenantId]");
_identityDb.Database.ExecuteNonQuery("DROP INDEX [IX_SecurityPool_TenantId] ON [auth].[SecurityPool]");
// Tenant Uris
_identityDb.Database.ExecuteNonQuery("ALTER TABLE [auth].[TenantUri] DROP CONSTRAINT [PK_TenantUri]");
_identityDb.Database.ExecuteNonQuery("ALTER TABLE [auth].[TenantUri] DROP CONSTRAINT [FK_TenantUri_Tenant_TenantId]");
// Tenant
_identityDb.Database.ExecuteNonQuery("ALTER TABLE [auth].[Tenant] DROP CONSTRAINT [PK_Tenant]");
output.Append("Dropping Constraints. ");
// 2) Update existing data tenant and related data
//-------------------------------------------------------------------------------
foreach (var tenant in existingTenants)
{
var tenantId = KeyGen.NewGuid();
var tenantKey = tenant.Id;
_identityDb.Database.ExecuteNonQuery($@"
UPDATE [auth].[Tenant] SET [Id] = '{tenantId}', [Key] = '{tenantKey}'
WHERE [Id] = '{tenantKey}';
UPDATE [auth].[TenantUri] SET [TenantId] = '{tenantId}'
WHERE [TenantId] = '{tenantKey}';
UPDATE [auth].[SecurityPool] SET [TenantId] = '{tenantId}'
WHERE [TenantId] = '{tenantKey}';
UPDATE [auth].[Directory] SET [TenantId] = '{tenantId}'
WHERE [TenantId] = '{tenantKey}';
");
}
output.Append($"Migrated {existingTenants.Count} tenants. ");
// 3) Add back all previously existing Constraints
//-------------------------------------------------------------------------------
// Tenants (also adding new alternate key constraint on Tenant.Key)
_identityDb.Database.ExecuteNonQuery("ALTER TABLE [auth].[Tenant] ADD CONSTRAINT [PK_Tenant] PRIMARY KEY CLUSTERED ([Id] ASC)");
_identityDb.Database.ExecuteNonQuery("ALTER TABLE [auth].[Tenant] ADD CONSTRAINT [AK_Tenant_Key] UNIQUE NONCLUSTERED ([Key] ASC)");
// Tenant Uris
_identityDb.Database.ExecuteNonQuery("ALTER TABLE [auth].[TenantUri] ADD CONSTRAINT [PK_TenantUri] PRIMARY KEY CLUSTERED ([TenantId] ASC, [Type] ASC, [Uri] ASC)");
_identityDb.Database.ExecuteNonQuery("ALTER TABLE [auth].[TenantUri] ADD CONSTRAINT [FK_TenantUri_Tenant_TenantId] FOREIGN KEY ([TenantId]) REFERENCES [auth].[Tenant] ([Id])");
// Directories
_identityDb.Database.ExecuteNonQuery("ALTER TABLE [auth].[Directory] ADD CONSTRAINT [FK_Directory_Tenant_TenantId] FOREIGN KEY ([TenantId]) REFERENCES [auth].[Tenant] ([Id])");
_identityDb.Database.ExecuteNonQuery("CREATE NONCLUSTERED INDEX [IX_Directory_TenantId] ON [auth].[Directory]([TenantId] ASC)");
// Security Pools
_identityDb.Database.ExecuteNonQuery("ALTER TABLE [auth].[SecurityPool] ADD CONSTRAINT [FK_SecurityPool_Tenant_TenantId] FOREIGN KEY ([TenantId]) REFERENCES [auth].[Tenant] ([Id])");
_identityDb.Database.ExecuteNonQuery("CREATE NONCLUSTERED INDEX [IX_SecurityPool_TenantId] ON [auth].[SecurityPool]([TenantId] ASC)");
output.Append("Recreated constraints.");
// DONE
return(MigrationResult.Success(output.ToString()));
}
public async Task <MigrationResult> ExecuteAsync()
{
// Fail if cannot connect to db
if (_connectDb.Database.TryTestConnection() == false)
{
return(MigrationResult.Failed("Cannot connect to database."));
}
// Skip if Tenant.Key already exists
if (_connectDb.Database.HasColumn("NavigationMenu", "cms", "Scope"))
{
return(MigrationResult.Skipped("Column [NavigationMenu].[Scope] already exists."));
}
// [1]
// Add the new column
await _connectDb.Database.ExecuteNonQueryAsync("ALTER TABLE [cms].[NavigationMenu] ADD [Scope] nvarchar(450) NULL");
// [2]
// Fix NavMenus that previously had their ID "hacked" as a way to identify the main menu
// Use the new Scope column instead to identify the main menu
// [2.1] DROP FK and PK ID constraints since we'll be operating on Ids
await _connectDb.Database.ExecuteNonQueryAsync(@"
ALTER TABLE [cms].[NavigationMenuItem]
DROP CONSTRAINT [FK_NavigationMenuItem_NavigationMenu_NavMenuId]
");
await _connectDb.Database.ExecuteNonQueryAsync(@"
ALTER TABLE [cms].[NavigationMenu]
DROP CONSTRAINT [PK_NavigationMenu]
");
// [2.2]
// UPDATE the Hacky 'main-' Ids. Use scope = main instead
await _connectDb.Database.ExecuteNonQueryAsync(@"
UPDATE [cms].[NavigationMenu]
SET
[Id] = REPLACE([Id], 'main-', ''),
[Scope] = 'main'
WHERE
[Id] LIKE 'main-%'
");
await _connectDb.Database.ExecuteNonQueryAsync(@"
UPDATE [cms].[NavigationMenuItem]
SET
[NavMenuId] = REPLACE([NavMenuId], 'main-', '')
WHERE
[NavMenuId] LIKE 'main-%'
");
// [2.3]
// Add the PK and FK constraints back
await _connectDb.Database.ExecuteNonQueryAsync(@"
ALTER TABLE [cms].[NavigationMenu]
ADD CONSTRAINT [PK_NavigationMenu] PRIMARY KEY CLUSTERED ([Id] ASC)
");
await _connectDb.Database.ExecuteNonQueryAsync(@"
ALTER TABLE [cms].[NavigationMenuItem]
ADD CONSTRAINT [FK_NavigationMenuItem_NavigationMenu_NavMenuId]
FOREIGN KEY ([NavMenuId])
REFERENCES [cms].[NavigationMenu] ([Id])
");
// [2.4]
// Fix NavMenuId's in the Navigation Widget Table
// Note: The widget table does not have a FK constraint on Navigation Menu
// so okay to do this last
if (_connectDb.Database.TableExists("NavMenuWidget", "plugin"))
{
await _connectDb.Database.ExecuteNonQueryAsync(@"
UPDATE
[plugin].[NavMenuWidget]
SET
[NavMenuId] = REPLACE([NavMenuId], 'main-', '')
WHERE
[NavMenuId] LIKE 'main-%'
");
}
return(MigrationResult.Success("Added column [NavigationMenu].[Scope]. Updated scope values for site menus."));
}
public async Task <MigrationResult> ExecuteAsync()
{
var output = new System.Text.StringBuilder();
// Fail if cannot connect to db
if (_identityDb.Database.TryTestConnection() == false)
{
return(MigrationResult.Failed("Cannot connect to database."));
}
// We'll be introducing a new FK constraint.
// If it already exist, that means we should skip this process
if (_identityDb.Database.ConstraintExists("User", "auth", "FK_User_Tenant_TenantId"))
{
return(MigrationResult.Skipped());
}
// [1] Update existing records with TenantId
await _identityDb.Database.ExecuteNonQueryAsync(@"
UPDATE [auth].[User]
SET
TenantId = dir.TenantId
FROM
[auth].[Directory] dir
WHERE
DirectoryId = dir.Id
");
// [2] Update column to not allow nulls
await _identityDb.Database.ExecuteNonQueryAsync(@"
ALTER TABLE [auth].[User] ALTER COLUMN [TenantId] nvarchar(450) NOT NULL
");
// [3] Add FK Constraint on Tenant table
await _identityDb.Database.ExecuteNonQueryAsync(@"
ALTER TABLE [auth].[User]
ADD CONSTRAINT [FK_User_Tenant_TenantId]
FOREIGN KEY ([TenantId])
REFERENCES [auth].[Tenant] ([Id])
");
// [4] Change UserName from varchar(max) to varchar(450) max can't be used in constraints
await _identityDb.Database.ExecuteNonQueryAsync(@"
ALTER TABLE [auth].[User] ALTER COLUMN [UserName] nvarchar(450) NOT NULL
");
// [5] Add a unique constraint (alternate key) on TenantId & UserName
await _identityDb.Database.ExecuteNonQueryAsync(@"
ALTER TABLE [auth].[User]
ADD CONSTRAINT [AK_TenantId_UserName] UNIQUE (TenantId, UserName);
");
// [6] Create an index on TenantId
await _identityDb.Database.ExecuteNonQueryAsync(@"
CREATE NONCLUSTERED INDEX [IX_User_TenantId]
ON [auth].[User]([TenantId] ASC);
");
return(MigrationResult.Success("Updated table data and created constraints."));
}