Migration scripts for v5 Repository database

Make sure you have a backup before running the scripts

-- Logs
if COL_LENGTH('Vidyano.Logs', 'ExternalId') is null
begin
	exec sp_rename 'Vidyano.Logs.Id', 'ExternalId', 'COLUMN';
	alter table Vidyano.Logs add Id bigint identity
	alter table Vidyano.Logs alter column Type tinyint not null
	alter table Vidyano.Logs alter column CreatedOn datetimeoffset(3) not null

	alter table Vidyano.Logs drop constraint [PK_Vidyano_Logs]
	alter table Vidyano.Logs add constraint PK_Logs primary key(Id)

	CREATE UNIQUE NONCLUSTERED INDEX [AK_Logs] ON [Vidyano].[Logs] ([ExternalId])

	--CREATE NONCLUSTERED INDEX [IX_Logs_PERF1] ON [Vidyano].[Logs] ([CreatedOn] DESC)
end

-- Settings
if COL_LENGTH('Vidyano.Settings', 'Id') is not null
begin
	declare @keyLength int = COL_LENGTH('Vidyano.Settings', 'Key')
	if @keyLength = -1 or @keyLength > 255
	begin
		alter table Vidyano.Settings alter column [Key] nvarchar(255) not null
	end

	alter table Vidyano.Settings drop constraint [PK_Settings]
	alter table Vidyano.Settings drop column [Id]
	alter table Vidyano.Settings add constraint [PK_Settings] primary key ([Key])
end

-- Users/UserGroup/Users_Group
SET ANSI_PADDING ON

IF (OBJECT_ID('[Vidyano].[Groups]', N'U') is null)
BEGIN 

-- Move Groups to separate table
select u.[Id], u.[Name], u.IsSystem, u.CreationDate, g.TwoFactorRequired
	into [Vidyano].[Groups]
	from [Vidyano].Users u
	inner join [Vidyano].[Users_Group] g on g.[Id] = u.[Id]

alter table [Vidyano].[Groups] add constraint [PK_Groups] primary key ([Id])
alter table [Vidyano].[Groups] add constraint [UQ_Groups_Name] UNIQUE NONCLUSTERED ([Name])

-- Switch to Groups table
ALTER TABLE [Vidyano].[UserGroup] DROP CONSTRAINT [FK_UserGroup_Group]
ALTER TABLE [Vidyano].[UserGroup]  WITH CHECK ADD  CONSTRAINT [FK_UserGroup_Group] FOREIGN KEY([Groups_Id]) REFERENCES [Vidyano].[Groups] ([Id])
ALTER TABLE [Vidyano].[UserGroup] CHECK CONSTRAINT [FK_UserGroup_Group]

END
go

if COL_LENGTH('Vidyano.Users', 'IsSystem') is not null
begin

alter table Vidyano.Users drop column IsSystem

end
go

if COL_LENGTH('Vidyano.Users', 'IsEnabled') is null
begin

alter table Vidyano.Users add
	IsEnabled bit constraint DF_Users_IsEnabled default 1 not null
	,TwoFactorToken varchar(100) null
	,ResetPasswordNextLogin bit constraint DF_Users_ResetPasswordNextLogin default 0 not null

declare @constraint nvarchar(256)

select @constraint=d.name from  sys.tables t
inner join sys.schemas s ON s.schema_id = t.schema_id
inner join sys.default_constraints d on d.parent_object_id = t.object_id
inner join sys.columns c on c.object_id = t.object_id
and c.column_id = d.parent_column_id
where 
t.name = N'Users'
and s.name = N'Vidyano' 
and c.name = N'Version'

IF LEN(ISNULL(@constraint, '')) <> 0
BEGIN
    DECLARE @sqlcmd VARCHAR(MAX)
    SET @sqlcmd = 'ALTER TABLE [Vidyano].[Users] DROP CONSTRAINT' +  QUOTENAME(@constraint);
    EXEC (@sqlcmd);
END

alter table Vidyano.Users alter column Version varchar(100) not null

exec ('update [Vidyano].[Users] set
	IsEnabled = case when JSON_VALUE(Profile, ''$.Disabled'') = ''true'' then 0 else 1 end
	, TwoFactorToken = JSON_VALUE(Profile, ''$.TwoFactorToken'')
	, ResetPasswordNextLogin = case when JSON_VALUE(Profile, ''$.ResetPasswordNextLogin'') = ''true'' then 1 else 0 end
	, Profile = JSON_MODIFY(JSON_MODIFY(JSON_MODIFY(Profile, ''$.Disabled'', NULL), ''$.TwoFactorToken'', NULL), ''$.ResetPasswordNextLogin'', NULL)
	from Vidyano.Users 
	where Profile is not null')

end
go

declare @keyLength int = COL_LENGTH('Vidyano.Users', 'Language')
if @keyLength = -1 or @keyLength > 10
begin
	update [Vidyano].[Users] set [Language] = '' where [Language] is null
	alter table [Vidyano].[Users] alter column [Language] varchar(10) not null
end

set @keyLength = COL_LENGTH('Vidyano.Users', 'CultureInfo')
if @keyLength = -1 or @keyLength > 10
begin
	update [Vidyano].[Users] set [CultureInfo] = '' where [CultureInfo] is null
	alter table [Vidyano].[Users] alter column [CultureInfo] varchar(10) not null
end
go

IF (OBJECT_ID('[Vidyano].[UserSettings]', N'U') is null)
BEGIN 

exec('select Id, [Settings]
	into [Vidyano].[UserSettings]
	from [Vidyano].Users
	where len([Settings]) > 2')

alter table [Vidyano].[UserSettings] add constraint PK_UserSettings primary key (Id)
alter table [Vidyano].[UserSettings] alter column [Settings] nvarchar(max) not null
alter table [Vidyano].[UserSettings] add constraint [FK_UserSettings_Users]
    foreign key ([Id])
    references [Vidyano].[Users] ([Id])
    on delete cascade on update no action;

alter table [Vidyano].Users drop column [Settings]

end
go

IF (OBJECT_ID('[Vidyano].[UserProfiles]', N'U') is null)
BEGIN 

exec('select Id, [Profile]
	into [Vidyano].[UserProfiles]
	from [Vidyano].Users
	where len([Profile]) > 2')

alter table [Vidyano].[UserProfiles] add constraint PK_UserProfiles primary key (Id)
alter table [Vidyano].[UserProfiles] alter column [Profile] nvarchar(max) not null
alter table [Vidyano].[UserProfiles] add constraint [FK_UserProfiles_Users]
    foreign key ([Id])
    references [Vidyano].[Users] ([Id])
    on delete cascade on update no action;

alter table [Vidyano].Users drop column [Profile]

end

-- ClientCodeSnippets (was used in v1 web client)
drop table if exists Vidyano.ClientCodeSnippets

-- Group flattening (groups are no longer member of another group)
IF (OBJECT_ID('[Vidyano].[TmpGroupGroups]', N'U') is null)
BEGIN
exec('select Users_Id, Groups_Id
	into Vidyano.TmpGroupGroups
	from Vidyano.UserGroup where Users_Id in (select Id from Vidyano.Groups)');
END

-- Drop old inherited Users_Group table
drop table if exists [Vidyano].[Users_Group]

-- Delete groups from Vidyano.Users
delete from Vidyano.UserGroup where Users_Id in (select Id from Vidyano.Groups)
delete from Vidyano.Users where Id in (select Id from Vidyano.Groups)

-- Flatten groups
;WITH cte AS (
SELECT tmp.[Users_Id]
	,ug.Name GroupName
      ,tmp.[Groups_Id]
	  ,gg.Name MemberOf
  FROM [Vidyano].[TmpGroupGroups] tmp
	inner join Vidyano.Groups ug on ug.Id = tmp.Users_Id
	inner join Vidyano.Groups gg on gg.Id = tmp.Groups_Id

union all

SELECT cte.[Users_Id]
	,cte.GroupName
      ,tmp.Groups_Id
	  ,gg.Name MemberOf
  FROM [Vidyano].[TmpGroupGroups] tmp
  inner join cte on cte.Groups_Id = tmp.[Users_Id]
	inner join Vidyano.Groups gg on gg.Id = tmp.Groups_Id
)
insert into Vidyano.UserGroup (Users_Id, Groups_Id)
select distinct ug.Users_Id, cte.Groups_Id
	from Vidyano.UserGroup ug
	inner join cte on cte.Users_Id = ug.Groups_Id
except
select ug.Users_Id, ug.Groups_Id from Vidyano.UserGroup ug

-- Update repository version
update Vidyano.Settings set Value='61' where [Key]='RepositoryVersion'