DB Updater R4

R4 systems from 2019-07-18 is switched to use new sql updater.

The new updater is the same updater as is used in 365 system.

Before you begin

  • Make sure that database is fully updated with old updater. Last updates deployed were on 2019-07-18

  • Make sure that user running updates is a member of [af_updater] database role.

    ALTER ROLE [af_updater] ADD MEMBER [some_user@omega.no]

  • User applying updates should be in database_managers table

  • User applying updates should have full permissions to sys namespace (create, alter, drop)

  • In order to use web sql updater, Pims Web Server should have access to https://update-api.omega.no/ on port 443, otherwise you should use Win project Sql Updater

  • To avoid any issues with permissions, we recommend refreshing all permissions

    sstp_Database_Maintenance_ApplyPermissions2 @all = 1

  • Web app is using server side updates download, win project - client side.

R4 system switch overview

Old updater will deploy updates until "2019-07-18". After this date, when you open "Appframe updates" form you should see label with link to this article ad link to SQL Updater win form.

After deploying all updates from the old updater the following stored procedure needs to be run:

sstp_Database_Maintenance_ApplyPermissions2 @all = 1

First time, run SQL Updater

SQL Updater UI has web app, but for the first time, before site upgrade, you must use Win project SQL Updater, which will look similar to web app

Usual upgrade

Usual upgrade can be done in article /db-updater or Win project SQL Updater.

  • Download - will download all available updates.
  • Apply - will apply downloaded updates.

If during an update an error is thrown, it should be fixed and then the status changed to 0 for that update.

After update completes

After running download and apply the system will be upgraded.

Web articles, scripts and etc, will update only Appframe hostname. To update your site, you will need to use Updating Website sys objects procedure, described below.

If you have production only hosts you should update them using procedure bellow.

Updating Website sys objects (Pimp my site)

This can be achieved in 3 ways:

  • Direct procedure call:
    sstp_WebSiteCMS_PimpMySite @SourceHost = 'Appframe', @TargetHost = @TargetHost
    • @TargetHost
      - site id which needs to be upgraded
  • Using win client. Choosing target site, then Appframe ->Web Setup->WebSite ->Update Sys Objects. As copy from site "Appframe" should be selected.
  • Using web /SiteSetup app. Site -> Update sys objects

Updating Production only host

To update production only host a following stored procedure needs to be run:

 exec sstp_WebSiteCMS_PublishAllSystemObjects @SourceHostname = @SourceHostname

  • @SourceHostname
    - production site id which needs to be updated

Update source database

  • In order to make your database as a update source, Website Anonymous user should have view permissions on stbl_System_CompatibilityLevel and stbl_Database_Deployment_SqlUpdate

Using updater for app deployment

This article describes how to set up application (IsSys = 0) update flow in R4 solutions. You are most probably using Updater for Appframe (IsSys = 1) updates already.

To be able to generate application updates in a source database you first need compatibility level record present:

if not exists (
select null
    from dbo.stbl_System_CompatibilityLevel with (nolock)
    where IsSys = 0
    insert into dbo.stbl_System_CompatibilityLevel
        (IsSys, ApiKey, Offset)
    select 0, dbo.sfnc_System_GetRandomSymbols(64, null), '2020-03-03';

This will add application compatibility level to your database that does not contain source url. So it will serve as Application update source. We are including [Offset] so we are not generating updates from the beginning of time.

Next we need to configure SQL Update Source table. We need to add records with 0 (zero) IsSys value.  Below is a vanilla object type support setup:

declare @_tmp_stbl_Database_Deployment_SqlUpdateSource table (
	[IsSys] bit not null,
	[SqlTemplate] nvarchar(128) not null,
	[SqlFragment] nvarchar(128) not null,
	[VersionView] nvarchar(128) not null,
	[VersionViewFilter] nvarchar(128) null,
	[Type] tinyint not null,
	[SqlFragmentCheck] nvarchar(128) null,
	[SqlFragmentAfter] nvarchar(128) null
insert into @_tmp_stbl_Database_Deployment_SqlUpdateSource select 0 , 'Appframe365.SqlUpdate' , 'library_r4' , 'sviw_Assemblies_ProjectVersion' , 'Name like ''App.%''' , 1 , NULL , NULL;
insert into @_tmp_stbl_Database_Deployment_SqlUpdateSource select 0 , 'Appframe365.SqlUpdate' , 'winproject' , 'sviw_WinClient_ProjectVersion' , 'Name like ''App.%''' , 2 , NULL , NULL;
insert into @_tmp_stbl_Database_Deployment_SqlUpdateSource select 0 , 'Appframe365.SqlUpdate' , 'dbobject' , 'sviw_Database_ObjectVersion' , 'Prefix = ''a''' , 3 , 'dbobject_check' , 'dbobject_after';
insert into @_tmp_stbl_Database_Deployment_SqlUpdateSource select 0 , 'Appframe365.SqlUpdate' , 'webarticle_r4' , 'sviw_WebSiteCMS_ArticleVersion' , 'HostName in (''Appframe'') and IsSys = 0' , 4 , 'webarticle_check_r4' , 'webarticle_after';
insert into @_tmp_stbl_Database_Deployment_SqlUpdateSource select 0 , 'Appframe365.SqlUpdate' , 'webcodemodule_r4' , 'sviw_WebSiteCMS_ModuleVersion' , 'IsSys = 0' , 5 , NULL , NULL;
insert into @_tmp_stbl_Database_Deployment_SqlUpdateSource select 0 , 'Appframe365.SqlUpdate' , 'report' , 'sviw_WinClient_ReportVersion' , 'Name like ''arpt[_]%''' , 6 , NULL , NULL;
insert into @_tmp_stbl_Database_Deployment_SqlUpdateSource select 0 , 'Appframe365.SqlUpdate' , 'sqltemplate_r4' , 'sviw_Database_SqlTemplate_ProjectVersion' , 'Name like ''PimsR4.%''' , 7 , NULL , NULL;
insert into @_tmp_stbl_Database_Deployment_SqlUpdateSource select 0 , 'Appframe365.SqlUpdate' , 'webscript' , 'sviw_WebSiteCMS_ScriptVersion' , 'HostName in (''Appframe'') and IsSys = 0' , 9 , NULL , NULL;
insert into @_tmp_stbl_Database_Deployment_SqlUpdateSource select 0 , 'Appframe365.SqlUpdate' , 'webstyle' , 'sviw_WebSiteCMS_StyleVersion' , 'HostName in (''Appframe'') and IsSys = 0' , 10 , NULL , NULL;
insert into @_tmp_stbl_Database_Deployment_SqlUpdateSource select 0 , 'Appframe365.SqlUpdate' , 'webtemplate' , 'sviw_WebSiteCMS_TemplateVersion' , 'HostName in (''Appframe'') and IsSys = 0' , 11 , NULL , NULL;
insert into @_tmp_stbl_Database_Deployment_SqlUpdateSource select 0 , 'Appframe365.SqlUpdate' , 'datachange' , 'sviw_Database_ObjectDataChangeVersion' , 'Prefix = ''a''' , 12 , NULL , NULL;

insert into dbo.stbl_Database_Deployment_SqlUpdateSource
    (IsSys, SqlTemplate, SqlFragment, VersionView, 
    VersionViewFilter, Type, SqlFragmentCheck, SqlFragmentAfter)
select IsSys, SqlTemplate, SqlFragment, VersionView, 
    VersionViewFilter, Type, SqlFragmentCheck, SqlFragmentAfter
from @_tmp_stbl_Database_Deployment_SqlUpdateSource;

You can also manipulate update sources in /db-manager articles Update sources tab:

After Update sources are added, updater engine will be able to pick up changes as defined in version view and generate updates using SQL template engine using fragments supplied in columns: [SqlFragment], [SqlFragmentCheck] and [SqlFragmentAfter].

Now when update generation is in place destination solution can start fetching updates from the source solution. For that a record in [stbl_System_CompatibilityLevel] is needed that contains source url. You can generate SQL that inserts into mentioned table using /api/sqlupdate/listentome.sql endpoint in source solution. it will generate SQL similar to this:

declare @_compatibility_source table (
    [IsSys] bit not null,
    [SourceUrl] nvarchar(1024) not null,
    [ApiKey] nvarchar(64) not null
-- NOTE: comment out insert of compatibility source you do not need.
insert into @_compatibility_source select 1, N'https://mmdev-test.omega.no', N'notshownhere';
insert into @_compatibility_source select 0, N'https://mmdev-test.omega.no', N'notshownhere2';

-- NOTE: when swaping to source that does not belong to the same generation of updates
-- you must investigate appropriate compatibility level and set it manually.

update c
set c.SourceUrl = cs.SourceUrl,
    c.ApiKey = cs.ApiKey    
from dbo.stbl_System_CompatibilityLevel as c with (nolock)
inner join @_compatibility_source as cs
    on cs.IsSys = c.IsSys;

insert into dbo.stbl_System_CompatibilityLevel
    (IsSys, SourceUrl, ApiKey)
select cs.IsSys, cs.SourceUrl, cs.ApiKey
from @_compatibility_source as cs
left join dbo.stbl_System_CompatibilityLevel as c
    on c.IsSys  = cs.IsSys
where c.PrimKey is null;

Fix SourceUrl if needed and execute it in destination solution. Now you should be able download application updates in your destination solution.

Related articles