public async Task <MigrationResult> ExecuteAsync()
{
// Fail if cannot connect to db
if (_blogDbContext.Database.TryTestConnection() == false)
{
return(MigrationResult.Failed("Cannot connect to database."));
}
// Skip if column already exists
if (_blogDbContext.Database.HasColumn("BlogPost", "plugin", "Published"))
{
return(MigrationResult.Skipped("Column already exists."));
}
// create column initially and allow null
await _blogDbContext.Database.ExecuteNonQueryAsync("ALTER TABLE [plugin].[BlogPost] ADD [Published] BIT NULL");
// update the table, set default value
await _blogDbContext.Database.ExecuteNonQueryAsync("UPDATE [plugin].[BlogPost] SET [Published] = 1");
// add the not null constraint
await _blogDbContext.Database.ExecuteNonQueryAsync("ALTER TABLE [plugin].[BlogPost] ALTER COLUMN [Published] BIT NOT NULL");
return(MigrationResult.Success("Added column [BlogPost].[Published]"));
}
public async Task <MigrationResult> ExecuteAsync()
{
if (_connectDb.Database.TryTestConnection() == true)
{
return(MigrationResult.Skipped());
}
var cnInfo = _connectDb.Database.GetDbConnection();
if (!string.IsNullOrEmpty(cnInfo?.ConnectionString))
{
var cnBuilder = new SqlConnectionStringBuilder(cnInfo.ConnectionString);
var databaseName = cnBuilder.InitialCatalog;
// can't connect to the db specified because it doesn't exist so setting
// thus, connecting to master instead so we can create
cnBuilder.InitialCatalog = "master";
var connection = new SqlConnection(cnBuilder.ToString());
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = $"CREATE DATABASE [{databaseName}]";
await command.ExecuteNonQueryAsync();
}
connection.Close();
return(MigrationResult.Success($"Created [{databaseName}]"));
}
//else failed
return(MigrationResult.Failed("Connetion string missing"));
}
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 MonthView already exists
if (_dbContext.Database.HasColumn("CalendarWidgetSetting", "plugin", "MonthView"))
{
return(MigrationResult.Skipped("Column BlogPost.IsPrivate already exists."));
}
//
await _dbContext.Database.ExecuteNonQueryAsync("ALTER TABLE [plugin].[CalendarWidgetSetting] ADD [MonthView] BIT NOT NULL DEFAULT(1)");
await _dbContext.Database.ExecuteNonQueryAsync("ALTER TABLE [plugin].[CalendarWidgetSetting] ADD [WeekView] BIT NOT NULL DEFAULT(1)");
await _dbContext.Database.ExecuteNonQueryAsync("ALTER TABLE [plugin].[CalendarWidgetSetting] ADD [DayView] BIT NOT NULL DEFAULT(1)");
await _dbContext.Database.ExecuteNonQueryAsync("ALTER TABLE [plugin].[CalendarWidgetSetting] ADD [ListView] BIT NOT NULL DEFAULT(1)");
return(MigrationResult.Success("Successfully added view columns."));
}
public async Task <MigrationResult> ExecuteAsync()
{
// Fail if cannot connect to db
if (_NewsDbContext.Database.TryTestConnection() == false)
{
return(MigrationResult.Failed("Cannot connect to database."));
}
// Skip if Tenant.Key already exists
if (_NewsDbContext.Database.HasColumn("NewsPost", "plugin", "IsActive"))
{
return(MigrationResult.Skipped("Column NewsPost.IsActive already exists."));
}
// create column initially and allow null
await _NewsDbContext.Database.ExecuteNonQueryAsync("ALTER TABLE [plugin].[NewsPost] ADD [IsActive] BIT NULL DEFAULT (1)");
// update the table, set all values to 0
await _NewsDbContext.Database.ExecuteNonQueryAsync("UPDATE [plugin].[NewsPost] SET IsActive = 1");
// add the not null constraint
await _NewsDbContext.Database.ExecuteNonQueryAsync("ALTER TABLE [plugin].[Newspost] ALTER COLUMN IsActive bit NOT NULL");
return(MigrationResult.Success("Added column [NewsPost].[IsActive]"));
}
public async Task <MigrationResult> ExecuteAsync()
{
var output = new System.Text.StringBuilder();
// Fail if cannot connect to db
if (_connectDb.Database.TryTestConnection() == false)
{
return(MigrationResult.Failed("Cannot connect to database."));
}
// site for each environment
List <string> siteDomains = new List <string>(new string[] { "mcpss.Mysites.com", "localhost:60002", "cityscapetemplateqa.Mysites.com", "cityscapetemplateuat.Mysites.com" });
List <string> siteIds = new List <string>();
Site site = new Site();
siteIds = _connectDb.SiteDomains.Where(x => siteDomains.Any(y => y == x.DomainKey)).Select(id => id.SiteId).ToList();
foreach (var siteId in siteIds)
{
site.Id = siteId;
_sitePublisher.CreateSysMasterPageV0(site);
}
return(MigrationResult.Success($"System Master Page created for Mobile County"));
}
public async Task <MigrationResult> ExecuteAsync()
{
// Fail if cannot connect to db
if (_connectDb.Database.TryTestConnection() == false)
{
return(MigrationResult.Failed("Cannot connect to database."));
}
if (_connectDb.Database.HasColumn("Page", "cms", "Keywords"))
{
return(MigrationResult.Skipped("Column [Page].[Keywords] already exists."));
}
// Keywords column
await _connectDb.Database.ExecuteNonQueryAsync(
"ALTER TABLE [cms].[Page] ADD [Keywords] nvarchar(1000) NULL"
);
// Summary column
await _connectDb.Database.ExecuteNonQueryAsync(
"ALTER TABLE [cms].[Page] ADD [Summary] nvarchar(2000) NULL"
);
return(MigrationResult.Success("Added Keywords & Summary columns."));
}
public async Task <MigrationResult> ExecuteAsync()
{
var announcementContentType = AnnouncementManager.CONTENT_TYPE_ANNOUNCEMENTPOST;
// Fail if cannot connect to db
if (_announcementDbContext.Database.TryTestConnection() == false || _connectDbContext.Database.TryTestConnection() == false)
{
return(MigrationResult.Failed("Cannot connect to database."));
}
// remove any versioning records that are in an invalid state
await _connectDbContext.Database.ExecuteNonQueryAsync($@"
DELETE FROM cms.ContentVersion WHERE ContentType = '{announcementContentType}' AND JsonData IS NULL
");
// Ensure UserId is set for remaining versions
// NOTE: Will only affect QA / Dev environments where UserIds weren't being saved during early test versions
await _announcementDbContext.Database.ExecuteNonQueryAsync(@"
UPDATE cms.ContentVersion SET UserId = bp.UserId
FROM plugin.AnnouncementPost bp
WHERE cms.ContentVersion.ContentId = bp.Id
AND cms.ContentVersion.UserId IS NULL
");
// Identify announcements with valid version data to skip in next step
// NOTE: These won't exist on production but might exist in dev / test environments
var announcementIdsToSkip = await _connectDbContext.ContentVersions
.Where(x =>
x.ContentType == announcementContentType &&
x.JsonData != null
)
.Select(x => x.ContentId).ToArrayAsync();
// Identify announcement posts that have missing version data
var announcementPostsToMigrate = await _announcementDbContext.AnnouncementPosts
.Where(x => !announcementIdsToSkip.Contains(x.Id))
.ToListAsync();
// Insert the missing verision data
foreach (var announcementPost in announcementPostsToMigrate)
{
var version = new ContentVersion(announcementContentType, announcementPost.Id);
version.VersionCode = announcementPost.VersionCode;
version.Created = announcementPost.Posted;
version.Status = announcementPost.Published ? ContentStatus.Published : ContentStatus.Draft;
version.UserId = announcementPost.UserId;
version.JsonData = SerializeVersionData(announcementPost);
_connectDbContext.ContentVersions.Add(version);
}
await _connectDbContext.SaveChangesAsync();
return(MigrationResult.Success($"Inserted {announcementPostsToMigrate.Count} missing AnnouncementPost version records"));
}
public async Task <MigrationResult> ExecuteAsync()
{
var output = new System.Text.StringBuilder();
// Fail if cannot connect to db
if (_connectDb.Database.TryTestConnection() == false)
{
return(MigrationResult.Failed("Cannot connect to database."));
}
// Check if internal product already exists
var exists = await _connectDb.Products.AnyAsync(x => x.Id == DbKeys.ProductIds.Internal);
if (exists)
{
// early terminate
return(MigrationResult.Skipped("Product already exists"));
}
// Register the product
var internalProduct = new Product
{
Id = DbKeys.ProductIds.Internal,
CategoryId = DbKeys.ProductCategoryIds.Angelo,
Name = "Internal Corp Product",
Description = "Internal product used for building PC|Mac demo sites.",
SchemaFile = "/schemas/products/Product-0-Internal.json",
Active = true
};
_connectDb.Products.Add(internalProduct);
await _connectDb.SaveChangesAsync();
// Update the PcMac Product Mapping
var productMapping = await _connectDb.ClientProductApps
.Include(x => x.AddOns)
.FirstOrDefaultAsync(
x => x.Id == DbKeys.ClientProductAppIds.PcMacApp1
);
productMapping.ProductId = DbKeys.ProductIds.Internal;
productMapping.Title = internalProduct.Name;
productMapping.MaxSiteCount = 500; // was previously 5
// Remove any previously seeded add-ons (the new product doesn't support any)
if (productMapping.AddOns != null)
{
(productMapping.AddOns as List <ClientProductAddOn>).RemoveAll(x => true);
}
// Done
await _connectDb.SaveChangesAsync();
return(MigrationResult.Success("Created product and mapped to PcMac client."));
}
public async Task <MigrationResult> ExecuteAsync()
{
// Fail if cannot connect to db
if (_NewsDbContext.Database.TryTestConnection() == false)
{
return(MigrationResult.Failed("Cannot connect to database."));
}
// Skip if column already exists
if (_NewsDbContext.Database.HasColumn("NewsPost", "plugin", "VersionCode"))
{
return(MigrationResult.Skipped("Versioning columns already exists."));
}
// drop documentid column
await _NewsDbContext.Database.ExecuteNonQueryAsync("ALTER TABLE [plugin].[NewsPost] DROP COLUMN [DocumentId]");
// create column initially and allow null
await _NewsDbContext.Database.ExecuteNonQueryAsync("ALTER TABLE [plugin].[NewsPost] ADD [VersionCode] NVARCHAR(50) NULL");
await _NewsDbContext.Database.ExecuteNonQueryAsync("ALTER TABLE [plugin].[NewsPost] ADD [Status] INT NULL");
// update the value of the status column
await _NewsDbContext.Database.ExecuteNonQueryAsync("UPDATE [plugin].[NewsPost] SET [Status] = 1 WHERE [Published] = 0");
await _NewsDbContext.Database.ExecuteNonQueryAsync("UPDATE [plugin].[NewsPost] SET [Status] = 2 WHERE [Published] = 1");
// update values for version code - tricky
await _NewsDbContext.Database.ExecuteNonQueryAsync(@"
UPDATE plugin.NewsPost SET VersionCode = ct.VersionCode
FROM cms.ContentTree ct
WHERE ContentTreeId = ct.Id"
);
await _NewsDbContext.Database.ExecuteNonQueryAsync(@"
UPDATE plugin.NewsPost SET VersionCode =
SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR, Posted, 121), '-', ''), ':', ''), '.', ''), ' ', '-'), 1, 17)
WHERE VersionCode IS NULL
");
await _NewsDbContext.Database.ExecuteNonQueryAsync(@"
UPDATE cms.ContentTree SET VersionCode = bp.VersionCode
FROM plugin.NewsPost bp
WHERE cms.ContentTree.Id = bp.ContentTreeId
");
// add the not null constraint
await _NewsDbContext.Database.ExecuteNonQueryAsync("ALTER TABLE [plugin].[NewsPost] ALTER COLUMN [VersionCode] NVARCHAR(50) NOT NULL");
await _NewsDbContext.Database.ExecuteNonQueryAsync("ALTER TABLE [plugin].[NewsPost] ALTER COLUMN [Status] INT NOT NULL");
return(MigrationResult.Success("Added [NewsPost].[VersionCode], Added [NewsPost].[Status], Removed [NewsPost].[DocumentId]"));
}
public async Task <MigrationResult> ExecuteAsync()
{
// Fail if cannot connect to db
if (_connectDb.Database.TryTestConnection() == false)
{
return(MigrationResult.Failed("Cannot connect to database."));
}
await _connectDb.Database.ExecuteNonQueryAsync("ALTER TABLE [cms].[NavigationMenuItem] ALTER COLUMN [TargetType] bit");
return(MigrationResult.Success("Altered [TargetType] to bit"));
}
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("SlideShowSlide", "plugin", "UseVideoBackground"))
{
return(MigrationResult.Skipped("Column SlideShowSlide.UseVideoBackground already exists."));
}
// Skip if VideoUrl already exists
if (_dbContext.Database.HasColumn("SlideShowSlide", "plugin", "VideoUrl"))
{
return(MigrationResult.Skipped("Column SlideShowSlide.VideoUrl already exists."));
}
// Skip if VideoSource already exists
if (_dbContext.Database.HasColumn("SlideShowSlide", "plugin", "VideoSource"))
{
return(MigrationResult.Skipped("Column SlideShowSlide.VideoSource already exists."));
}
// Skip if EnableVideoSound already exists
if (_dbContext.Database.HasColumn("SlideShowSlide", "plugin", "EnableVideoSound"))
{
return(MigrationResult.Skipped("Column SlideShowSlide.EnableVideoSound already exists."));
}
await _dbContext.Database.ExecuteNonQueryAsync(
"ALTER TABLE [plugin].[SlideShowSlide] ADD [UseVideoBackground] [Bit] NULL Default(0)");
await _dbContext.Database.ExecuteNonQueryAsync(
"ALTER TABLE [plugin].[SlideShowSlide] ADD [VideoUrl][nvarchar](500) NULL");
await _dbContext.Database.ExecuteNonQueryAsync(
"ALTER TABLE [plugin].[SlideShowSlide] ADD [VideoSource][nvarchar](20) NULL");
await _dbContext.Database.ExecuteNonQueryAsync(
"ALTER TABLE [plugin].[SlideShowSlide] ADD [EnableVideoSound][Bit] NULL Default(0)");
// update the table, set all values to 0
await _dbContext.Database.ExecuteNonQueryAsync("UPDATE [plugin].[SlideShowSlide] SET UseVideoBackground = 0");
// update the table, set all values to 0
await _dbContext.Database.ExecuteNonQueryAsync("UPDATE [plugin].[SlideShowSlide] SET EnableVideoSound = 0");
return(MigrationResult.Success(
"Added Video Background supporting columns"));
}
public async Task <MigrationResult> ExecuteAsync()
{
// Fail if cannot connect to db
if (_connectDb.Database.TryTestConnection() == false)
{
return(MigrationResult.Failed("Cannot connect to database."));
}
await _connectDb.Database.ExecuteNonQueryAsync("ALTER TABLE [cms].[PageMaster] ADD [IsSystemPage] bit NOT NULL DEFAULT 0");
return(MigrationResult.Success("Added [IsSystemPage] to PageMaster"));
}
public async Task <MigrationResult> ExecuteAsync()
{
// Fail if cannot connect to db
if (_identityDb.Database.TryTestConnection() == false)
{
return(MigrationResult.Failed("Cannot connect to database."));
}
await AddCorpUserClaimToCorpRoles();
return(MigrationResult.Success("Successfully added the corp User"));
}
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."));
}
// Insert claim into Client RoleClaims
var clients = await _connectDb.Clients.ToListAsync();
foreach (Client client in clients)
{
var roles = await _identityDb.Roles.Where(x => x.PoolId == client.SecurityPoolId && x.Name != "Registered Users").ToListAsync();
var clientId = client.Id;
foreach (Role role in roles)
{
await _identityDb.Database.ExecuteSqlCommandAsync($@"
INSERT INTO auth.RoleClaim (RoleId, ClaimType, ClaimValue)
VALUES ('{role.Id}', 'user-library-owner', '{clientId}')
");
}
}
// Insert claim into Site RoleClaims
var sites = await _connectDb.Sites.ToListAsync();
foreach (Site site in sites)
{
var roles = await _identityDb.Roles.Where(x => x.PoolId == site.SecurityPoolId && x.Name != "Registered Users").ToListAsync();
var siteId = site.Id;
foreach (Role role in roles)
{
await _identityDb.Database.ExecuteSqlCommandAsync($@"
INSERT INTO auth.RoleClaim (RoleId, ClaimType, ClaimValue)
VALUES ('{role.Id}', 'user-library-owner', '{siteId}')
");
}
}
return(MigrationResult.Success("Added user-library-owner claim to roles."));
}
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."));
}
// Update the name for the wireless provider
await _identityDb.Database.ExecuteNonQueryAsync($"UPDATE [auth].[WirelessProvider] SET [Name] = 'Boost Mobile' WHERE [Id] = '{DbKeys.WirelessProviderIds.BoostMobile}'");
return(MigrationResult.Success($"Updated Boost Mobile Name from Boos Mobile to Boost Mobile"));
}
public async Task <MigrationResult> ExecuteAsync()
{
// Fail if cannot connect to db
if (_dbContext.Database.TryTestConnection() == false)
{
return(MigrationResult.Failed("Cannot connect to database."));
}
await _dbContext.Database.ExecuteNonQueryAsync("ALTER TABLE [plugin].[SlideShowLayer] ALTER COLUMN [Title] [nvarchar](MAX) NULL");
await _dbContext.Database.ExecuteNonQueryAsync("ALTER TABLE [plugin].[SlideShowLayer] ALTER COLUMN [SourceUrl] [nvarchar](MAX) NULL");
return(MigrationResult.Success("Successful Increase Title And Url Column Size "));
}
public async Task <MigrationResult> ExecuteAsync()
{
// Fail if cannot connect to db
if (_connectDb.Database.TryTestConnection() == false)
{
return(MigrationResult.Failed("Cannot connect to database."));
}
// Add the new column
await _connectDb.Database.ExecuteNonQueryAsync("ALTER TABLE [cms].[Page] ADD [IsHomePage] BIT NOT NULL DEFAULT(0)");
await UpdateFlagOnExistingSites();
return(MigrationResult.Success("Successfully added Column IsHomePage to Pages."));
}
public async Task <MigrationResult> ExecuteAsync()
{
string schemaName = "cms";
string tableName = "SiteAlert";
var output = new System.Text.StringBuilder();
// Fail if cannot connect to db
if (_connectDb.Database.TryTestConnection() == false)
{
return(MigrationResult.Failed("Cannot connect to database."));
}
try
{
// CREATE TABLE
if (_connectDb.Database.TableExists(tableName, schemaName) == false)
{
await _connectDb.Database.ExecuteSqlCommandAsync($@"
CREATE TABLE [{schemaName}].[{tableName}](
[Id] nvarchar(50) NOT NULL,
[VersionCode] nvarchar(50) NOT NULL,
[SiteId] nvarchar(50) NOT NULL,
[UserId] nvarchar(50) NOT NULL,
[ContentTreeId] nvarchar(50) NOT NULL,
[Title] nvarchar(450) NULL,
[StartDate] datetime NOT NULL,
[EndDate] datetime NOT NULL,
[Status] int NOT NULL,
[Posted] datetime NOT NULL,
CONSTRAINT [PK_SiteAlert_Id] PRIMARY KEY (Id));
");
}
else
{
return(await Task.FromResult(MigrationResult.Failed($"Table already created: [{schemaName}].[{tableName}]. ")));
}
}
catch (Exception e)
{
return(await Task.FromResult(MigrationResult.Failed($"Error creating table. {e.Message}")));
}
return(await Task.FromResult(MigrationResult.Success("Created table [cms].[SiteAlert]")));
}
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."));
}
// Skip if Tenant.Key already exists
if (_identityDb.Database.HasColumn("Tenant", "auth", "Key"))
{
return(MigrationResult.Skipped("Column Tenant.Key already exists."));
}
await _identityDb.Database.ExecuteNonQueryAsync("ALTER TABLE [auth].[Tenant] ADD [Key] nvarchar(450) NULL");
return(MigrationResult.Success("Added column [Tenant].[Key]"));
}
public async Task <MigrationResult> ExecuteAsync()
{
// Fail if cannot connect to db
if (_connectDb.Database.TryTestConnection() == false)
{
return(MigrationResult.Failed("Cannot connect to database."));
}
if (_connectDb.Database.HasColumn("ContentVersion", "cms", "JsonData"))
{
return(MigrationResult.Skipped("Column [ContentVersion].[JsonData] already exists."));
}
await _connectDb.Database.ExecuteNonQueryAsync(
"ALTER TABLE [cms].[ContentVersion] ADD [JsonData] nvarchar(max) NULL"
);
return(MigrationResult.Success("Added Keywords & Summary columns."));
}
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 sort already exists
if (_dbContext.Database.HasColumn("DocumentListDocument", "plugin", "Sort"))
{
return(MigrationResult.Skipped("Column DocumentListDocument.Sort already exists."));
}
await _dbContext.Database.ExecuteNonQueryAsync("ALTER TABLE [plugin].[DocumentListDocument] ADD [Sort] INT NULL");
await _dbContext.Database.ExecuteNonQueryAsync("ALTER TABLE [plugin].[DocumentListDocument] ADD [FolderId] [nvarchar](50) NULL");
if (_dbContext.Database.TableExists("DocumentListFolder", "plugin"))
{
return(MigrationResult.Skipped("Table DocumentListFolder is already created."));
}
//add the folder table
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[DocumentListFolder](
[Id] [nvarchar](50) NOT NULL,
[WidgetId] [nvarchar](50) NULL,
[Title] [nvarchar](500) NULL,
[Sort] [int] NULL,
CONSTRAINT [PK_DocumentListFolder] PRIMARY KEY CLUSTERED ([Id] ASC)
)
");
//Update the sort column default value (fix issue in SQL 2008)
await _dbContext.Database.ExecuteNonQueryAsync("UPDATE [plugin].[DocumentListDocument] SET Sort = 0");
return(MigrationResult.Success("Added column [DocumentListDocument].[Sort] and created DocumentListFolder"));
}
public async Task <MigrationResult> ExecuteAsync()
{
//[LinkTarget]
// [nvarchar] (7) NOT NULL DEFAULT '_self',
// Fail if cannot connect to db
if (_dbContext.Database.TryTestConnection() == false)
{
return(MigrationResult.Failed("Cannot connect to database."));
}
// Skip if MonthView already exists
if (_dbContext.Database.HasColumn("CalendarEvent", "plugin", "LinkTarget"))
{
return(MigrationResult.Skipped("Column CalendarEvent.LinkTarget already exists."));
}
//
await _dbContext.Database.ExecuteNonQueryAsync("ALTER TABLE [plugin].[CalendarEvent] ADD [LinkTarget] [nvarchar] (7) NOT NULL DEFAULT '_self'");
return(MigrationResult.Success("Successfully Add Target To Event Url."));
}
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 (_identityDb.Database.TryTestConnection() == false)
{
return(MigrationResult.Failed("Cannot connect to database."));
}
var oldClaimType = "resource-calendar-event-edit";
var newClaimType = Calendar.Security.CalendarClaimTypes.CalendarAuthor;
// Update any existing user claims
await _identityDb.Database.ExecuteNonQueryAsync($"UPDATE [auth].[UserClaim] SET [ClaimType] = '{newClaimType}' WHERE [ClaimType] = '{oldClaimType}'");
// Update any existing role claims
await _identityDb.Database.ExecuteNonQueryAsync($"UPDATE [auth].[RoleClaim] SET [ClaimType] = '{newClaimType}' WHERE [ClaimType] = '{oldClaimType}'");
// Update any existing group claims
await _identityDb.Database.ExecuteNonQueryAsync($"UPDATE [auth].[GroupClaim] SET [ClaimType] = '{newClaimType}' WHERE [ClaimType] = '{oldClaimType}'");
return(MigrationResult.Success($"Updated ClaimType {oldClaimType} to {newClaimType}"));
}
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()
{
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."));
}
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."));
}
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()));
}
