1. Создайте скрипты для модификации БД.
2. Не изменяйте чужих данных только добавляйте.
3. Создайте скрипты для дропа данных см п2
4. создайте скрипты для seeddata
Структура папок. По идее любое ваше изменение можно откатить просто запустив нужный скрипт.
- SQL
- up
- up_2020_10_28_01.sql
- up_2020_10_28_02.sql
- down
- down_2020_10_28_01.sql
- down_2020_10_28_02.sql
- seed
- seed_2020_10_28_01.sql
- seed_2020_10_28_02.sql
Ниже пример MSSQL но это применимо к любой базе
CREATE TABLE [Telemetry] (
[Id] bigint NOT NULL IDENTITY,
[Login] nvarchar(200) NULL,
[DisplayName] nvarchar(200) NULL,
[Url] nvarchar(400) NULL,
[Start] datetime2 NOT NULL,
[End] datetime2 NULL,
[Duration] int NULL,
[PageName] nvarchar(400) NULL,
[CourseId] uniqueidentifier NOT NULL,
[PageNumber] nvarchar(200) NULL,
CONSTRAINT [PK_Telemetry] PRIMARY KEY ([Id])
);
GO
CREATE INDEX [IX_Telemetry_CourseId] ON [Telemetry] ([CourseId]);
GO
CREATE INDEX [IX_Telemetry_Login] ON [Telemetry] ([Login]);
GO
CREATE INDEX [IX_Telemetry_PageNumber] ON [Telemetry] ([PageNumber]);
GO
CREATE INDEX [IX_Telemetry_Status] ON [Telemetry] ([Start]);
GO
------ Изменение размера -----------
DROP INDEX [IX_Telemetry_PageNumber] ON [Telemetry];
DECLARE @var0 sysname;
SELECT @var0 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Telemetry]') AND [c].[name] = N'PageNumber');
IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [Telemetry] DROP CONSTRAINT [' + @var0 + '];');
ALTER TABLE [Telemetry] ALTER COLUMN [PageNumber] nvarchar(20) NULL;
CREATE INDEX [IX_Telemetry_PageNumber] ON [Telemetry] ([PageNumber]);
GO
DROP INDEX [IX_Telemetry_Login] ON [Telemetry];
DECLARE @var1 sysname;
SELECT @var1 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Telemetry]') AND [c].[name] = N'Login');
IF @var1 IS NOT NULL EXEC(N'ALTER TABLE [Telemetry] DROP CONSTRAINT [' + @var1 + '];');
ALTER TABLE [Telemetry] ALTER COLUMN [Login] nvarchar(20) NOT NULL;
CREATE INDEX [IX_Telemetry_Login] ON [Telemetry] ([Login]);
GO
DECLARE @var2 sysname;
SELECT @var2 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Telemetry]') AND [c].[name] = N'DisplayName');
IF @var2 IS NOT NULL EXEC(N'ALTER TABLE [Telemetry] DROP CONSTRAINT [' + @var2 + '];');
ALTER TABLE [Telemetry] ALTER COLUMN [DisplayName] nvarchar(100) NULL;
GO
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20200922061931_ChangeSizes', N'3.1.8');
GO
-- изменение курса с гуида на строку
EXEC sp_rename N'[Telemetry].[IX_Telemetry_Status]', N'IX_Telemetry_Start', N'INDEX';
GO
DROP INDEX [IX_Telemetry_CourseId] ON [Telemetry];
DECLARE @var0 sysname;
SELECT @var0 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Telemetry]') AND [c].[name] = N'CourseId');
IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [Telemetry] DROP CONSTRAINT [' + @var0 + '];');
ALTER TABLE [Telemetry] ALTER COLUMN [CourseId] nvarchar(38) NOT NULL;
CREATE INDEX [IX_Telemetry_CourseId] ON [Telemetry] ([CourseId]);
GO
GO
