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()
{
if (_dbContext.Database.TableExists("DocumentListWidget", "plugin"))
{
return(MigrationResult.Skipped());
}
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[DocumentListWidget](
[Id] [nvarchar](50) NOT NULL,
[SiteId] [nvarchar](50) NOT NULL,
[Title] [nvarchar](500) NULL,
CONSTRAINT [PK_DocumentListWidget] PRIMARY KEY CLUSTERED ([Id] ASC)
)
");
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[DocumentListDocument](
[Id] [nvarchar](50) NOT NULL,
[WidgetId] [nvarchar](50) NOT NULL,
[DocumentId] [nvarchar](50) NULL,
[Title] [nvarchar](500) NULL,
[Url] [nvarchar](2048) NULL,
[ThumbnailUrl] [nvarchar](2048) NULL
CONSTRAINT [PK_DocumentListDocument] PRIMARY KEY CLUSTERED ([Id] ASC)
)
");
return(MigrationResult.Success());
}
public async Task <MigrationResult> ExecuteAsync()
{
if (_dbContext.Database.TableExists("VideoWidget", "plugin"))
{
return(MigrationResult.Skipped());
}
// else create the tables
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[VideoWidget](
[Id] [nvarchar](50) NOT NULL,
[Title] [nvarchar](500) NULL,
[VideoSourceType] [nvarchar](500) NULL,
[VideoId] [nvarchar](500) NULL,
[VideoUrl] [nvarchar](500) NULL,
CONSTRAINT [PK_VideoLink] PRIMARY KEY CLUSTERED ([Id] ASC)
)
");
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[VideoStreamLink](
[Id] [nvarchar](50) NOT NULL,
[ClientId] [nvarchar](50) NULL,
[Title] [nvarchar](500) NULL,
[Path] [nvarchar](500) NULL,
CONSTRAINT [PK_VideoLinkSource] PRIMARY KEY CLUSTERED ([Id] ASC)
)
");
return(MigrationResult.Success());
}
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()
{
// 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()
{
// 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 (_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()
{
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()
{
string schemaName = "app";
string tableName = "WebCache";
var output = new System.Text.StringBuilder();
// create schema if needed
if (_connectDb.Database.SchemaExists(schemaName) == false)
{
await _connectDb.Database.ExecuteSqlCommandAsync($@"
CREATE SCHEMA [{schemaName}]
");
output.Append($"Created schema [{schemaName}]. ");
}
// CREATE TABLE SCHEMA (if needed)
if (_connectDb.Database.TableExists(tableName, schemaName) == false)
{
await _connectDb.Database.ExecuteSqlCommandAsync($@"
CREATE TABLE [{schemaName}].[{tableName}](
[Id] nvarchar(450) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
[Value] varbinary(MAX) NOT NULL,
[ExpiresAtTime] datetimeoffset NOT NULL,
[SlidingExpirationInSeconds] bigint NULL,
[AbsoluteExpiration] datetimeoffset NULL,
CONSTRAINT [PK_Id] PRIMARY KEY (Id));
CREATE NONCLUSTERED INDEX [IX_ExpiresAtTime] ON [{schemaName}].[{tableName}](ExpiresAtTime);
");
output.Append($"Created [{schemaName}].[{tableName}]. ");
}
// Cleanup old dbo version if present
if (_connectDb.Database.TableExists("WebCache", "dbo") == true)
{
// drop it
await _connectDb.Database.ExecuteSqlCommandAsync(@"
DROP TABLE [dbo].[WebCache]
");
output.Append("Removed [dbo].[WebCache] (old version). ");
}
// 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()
{
// 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()
{
if (_loggerDb.RequiresMigration())
{
await _loggerDb.Database.MigrateAsync();
return(MigrationResult.Success("Created entity models."));
}
return(MigrationResult.Skipped("No action required"));
}
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()
{
if (_identityDb.RequiresMigration())
{
await _identityDb.Database.MigrateAsync();
// EF migrations sometimes report back complete before the underlying DB transaction
// has completed so stalling a bit. This only affects drop / recreate scenarios.
await Task.Delay(1200);
return(MigrationResult.Success("Created entity models"));
}
return(MigrationResult.Skipped("No action required"));
}
public async Task <MigrationResult> ExecuteAsync()
{
var schemaName = "plugin";
// if schema exists, we bail out
if (_connectDb.Database.SchemaExists(schemaName))
{
return(MigrationResult.Skipped());
}
// otherwise create the schema
await _connectDb.Database.ExecuteSqlCommandAsync($@"
EXEC('CREATE SCHEMA [{schemaName}]')
");
return(MigrationResult.Success($"Created schema [{schemaName}]"));
}
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()
{
//[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 (_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()
{
if (_dbContext.Database.TableExists("CarouselWidget", "plugin"))
{
return(MigrationResult.Skipped());
}
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[CarouselWidget](
[Id] [nvarchar](50) NOT NULL,
[SiteId] [nvarchar](50) NOT NULL,
[Title] [nvarchar](500) NULL,
CONSTRAINT [PK_CarouselWidget] PRIMARY KEY CLUSTERED ([Id] ASC)
)
");
if (_dbContext.Database.TableExists("CarouselSlide", "plugin"))
{
return(MigrationResult.Skipped());
}
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[CarouselSlide](
[Id] [nvarchar](50) NOT NULL,
[WidgetId] [nvarchar](50) NOT NULL,
[Title] [nvarchar](500) NOT NULL,
[Description] [nvarchar](MAX) NULL,
[LinkText] [nvarchar](2048) NULL,
[LinkUrl] [nvarchar](2048) NULL,
[LinkTarget] [nvarchar] (7) NOT NULL DEFAULT '_self',
[Sort] [int] NOT NULL DEFAULT(0),
CONSTRAINT [PK_CarouselSlide] PRIMARY KEY CLUSTERED ([Id] ASC)
)
");
return(MigrationResult.Success());
}
public async Task <MigrationResult> ExecuteAsync()
{
if (_dbContext.Database.TableExists("UpcomingEventsWidget", "plugin"))
{
return(MigrationResult.Skipped());
}
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[UpcomingEventsWidget](
[Id] [nvarchar](50) NOT NULL,
[Title] [nvarchar](500) NULL,
[PostsToDisplay] int NOT NULL,
[UseTextColor] bit NOT NULL DEFAULT 0,
[TextColor] [nvarchar](10) NULL,
CONSTRAINT [PK_UpcomingEventsWidget] PRIMARY KEY CLUSTERED ([Id] ASC)
)
");
if (_dbContext.Database.TableExists("UpcomingEventsWidgetEventGroup", "plugin"))
{
return(MigrationResult.Skipped());
}
await _dbContext.Database.ExecuteSqlCommandAsync(@"
CREATE TABLE [plugin].[UpcomingEventsWidgetEventGroup](
[Id] [nvarchar](50) NOT NULL,
[WidgetId] [nvarchar](50) NOT NULL,
[EventGroupId] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_UpcomingEventsWidgetEventGroup] PRIMARY KEY CLUSTERED ([WidgetId] ASC, [EventGroupId] ASC)
)
");
_dbContext.Database.ExecuteNonQuery("ALTER TABLE [plugin].[UpcomingEventsWidgetEventGroup] ADD CONSTRAINT [AK_Id] UNIQUE NONCLUSTERED ([Id] ASC)");
return(MigrationResult.Success());
}
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()
{
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()
{
// 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("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()
{
// 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()
{
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()
{
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()
{
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()
{
// 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());
}
