Foreign key constraint violation for tblPropertyDefintion causing SqlException while automatically restoring EPiServer database for UI tests

Previously I wrote an article about Keeping reliable test data in EPiServer content database for automated UI tests, but recently ran into some issues in regards to new EPiServer properties.

A call to the IContentRepository method Save as below.

_contentRepository.Save(page, SaveAction.Publish, AccessLevel.NoAccess);

Causes the following error.

Exception Details: System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblWorkContentProperty_tblPropertyDefinition". The conflict occurred in database "dbEPiServerDatabase", table "dbo.tblPropertyDefinition", column 'pkID'.

Cause of SqlException foreign key constraint tblWorkContentProperty tblPropertyDefinition in EPiServer database

After adding new EPiServer properties to a page type class, the EPiServer initialization system will try to synchronize these to the content database. This is all fine and good, if you’re not happening to switch out the database to a new one right afterwards. Apparently, our problem was this:

1. Request to database controller to restore template EPiServer database is made to the freshly built solution.
2. EPiServer initialization kicks in and synchronizes the new property definitions with the database.
3. The request reaches the restore database action, and the template database is restored to the SQL server.
4. As far as EPiServer is concerned, the new properties are already added to the database, because it’s not aware that it was switched out.
5. When we try adding EPiServer content using IContentRepository, we get an exception telling us about the missing property definitions.

Resolving conflicting foreign key constraint for property definitions in EPiServer database

So, the solution is either to forcibly trigger EPiServer’s property definition synchronization process again after restoring the database, or just restart the website and have EPiServer initialization do it for us. In our case, it was way easier to do the latter.

Since it is somewhat tricky to have the running action method trigger an application pool recycle, and then continue on with it’s work, I put together a small PowerShell script to handle that for us.

param(
  $website = "MySiteTests",
  $resetDbsUrl = "http://se.MySite.build/database/restoredbs",
  $warmupUrl = "http://se.MySite.build/episerver/cms/"
)

function ResetDatabases() {
  Write-Host "Restoring DBs on $website through URL $resetDbsUrl" -ForegroundColor Yellow -NoNewline
  $request = [System.Net.WebRequest]::Create($resetDbsUrl)
  $request.Method = "GET"
  $response = $request.GetResponse()
  $code = [int]$response.StatusCode
  $text = $response.StatusCode
  Write-Host " - $code $text" -ForegroundColor Green
}

function RestartWebsite(){
  Get-Website > $null # Ensure access to IIS sites
  Write-Host "Stopping $website" -ForegroundColor Yellow -NoNewline
  Stop-WebSite $website
  Write-Host " - Done" -ForegroundColor Green
  $appPool = (Get-Item "IIS:\Sites\$website" | Select-Object applicationPool).applicationPool
  Write-Host "Stopping application pool $appPool" -ForegroundColor Yellow -NoNewline
  Stop-WebAppPool -Name $appPool
  Write-Host " - Done" -ForegroundColor Green
  
  Write-Host "Chill for a bit (Give IIS time getting ready to accept control messages again)" -ForegroundColor Yellow -NoNewline
  Start-Sleep -Seconds 3
  Write-Host " - Done chillin'" -ForegroundColor Green
  
  Write-Host "Starting application pool $appPool" -ForegroundColor Yellow -NoNewline
  Start-WebAppPool -Name $appPool
  Write-Host " - Done" -ForegroundColor Green
  Write-Host "Starting $website" -ForegroundColor Yellow -NoNewline
  Start-WebSite $website
  Write-Host " - Done" -ForegroundColor Green
}

function MakeWarmUpRequest() {
  Write-Host "Warming up $website through URL $warmupUrl (Needed to sync EPi code with EPi DBs)" -ForegroundColor Yellow -NoNewline
  $request = [System.Net.WebRequest]::Create($warmupUrl)
  $request.Method = "GET"
  $response = $request.GetResponse()
  $code = [int]$response.StatusCode
  $text = $response.StatusCode
  Write-Host " - $code $text" -ForegroundColor Green
}

Import-Module WebAdministration

ResetDatabases
RestartWebsite
MakeWarmUpRequest

The PowerShell script first makes a GET request to the method restoring the database, followed by a stop and a start of the website’s application pool. When this is done, a warm up request is used to have the initialization process synchronize the property definitions. Of course, this would be done anyway on the first request to the website, but in this way everything is ready once the tests start.

The global test setup for our Selenium test suite now only makes a call to the generate-test-data method, and not the restore-database one as well, as it did before.

GlobalTestSetUp.cs

using System;
using NUnit.Framework;
using OpenQA.Selenium.PhantomJS;

namespace MySite.Tests.UI.Selenium
{
  [SetUpFixture]
  public class GlobalTestSetUp
  {
    [SetUp]
    public void RunOnlyOnce()
    {
      var baseUrl = Properties.MySiteSettings.Default.BaseUrlSE;
      var initContentUrl = string.Concat(baseUrl, "/database/rebuild/");

      using (var service = PhantomJSDriverService.CreateDefaultService())
      {
        service.HideCommandPromptWindow = true;
        using (var driver = new PhantomJSDriver(service) { Url = baseUrl })
        {
          driver.Manage().Timeouts()
            .SetPageLoadTimeout(TimeSpan.MinValue)
            .ImplicitlyWait(TimeSpan.FromSeconds(10));

          driver.Navigate().GoToUrl(initContentUrl);
        }
      }
    }
  }
}

After this, it was easy to have our Jenkins build server execute the new PowerShell script before running the tests.