Search code examples
continuous-integrationintegration-testingdevtoolsr-packageappveyor

Setting up R package SQL-integration tests on Appveyor

I'm looking to run a few SQL integration tests in my R package on Appveyor. I'm using an mdf file to set up the database of interest, but I'm seeing issues with having an mdf file in my R package. Here are the relevant steps:

1) Before building, call a PowerShell script to create the db using the mdf file in my package.

  • This step occurs without error

2) The yml file runs R tests via travis-tool.sh run_tests and tries to copy the R project files from c:\projects\HCRTools to the build directory, ie C:\Users\appveyor\AppData\Local\Temp

  • This step fails because the mdf is now tied to SQL Server and can't be copied:

    "Warning in file.copy(pkgname, Tdir, recursive = TRUE) : problem copying .\hcrtools\inst\CIDatabase\SAM_log.ldf to C:\Users\appveyor\AppData\Local\Temp\1\RtmpCq659a\Rbuild92c6989d34\hcrtools\inst\CIDatabase\SAM_log.ldf: Permission denied ERROR copying to build directory failed"

Would greatly appreciate any help in uploading the mdf artifact without it also being in the R package! Couldn't make this work.

yml file:

init:
  ps: |
        $ErrorActionPreference = "Stop"
        Invoke-WebRequest http://raw.github.com/krlmlr/r-appveyor/master/scripts/appveyor-tool.ps1 -OutFile "..\appveyor-tool.ps1"
        Import-Module '..\appveyor-tool.ps1'
install:
  ps: Bootstrap

services:
  - mssql2012sp1

build_script:
  - ps: iex .\inst\CIDatabase\CIBuild_DBCreate.ps1 #<-- Create DB (w/ no error)!
  - Rscript -e "source('http://bioconductor.org/biocLite.R')" -e "chooseCRANmirror(ind=81)"
  - travis-tool.sh install_deps

test_script:
  - travis-tool.sh run_tests #<-- mdf copy error occurs!

on_failure:
  - 7z a failure.zip *.Rcheck\*
  - appveyor PushArtifact failure.zip

environment:
  global:
    WARNINGS_ARE_ERRORS: 0
    _R_CHECK_FORCE_SUGGESTS_: 1
    R_ARCH: x64

  matrix:
    - R_VERSION: release

PowerShell file to upload artifacts and create database:

#-- Push artifacts up
Push-AppveyorArtifact inst/CIDatabase/SAM.mdf
Push-AppveyorArtifact inst/CIDatabase/SAM_log.ldf

#-- Use mdf/ldf to create SAM db
$mdfFile = "c:\projects\HCRTools\inst\CIDatabase\SAM.mdf"
$ldfFile = "c:\projects\HCRTools\inst\CIDatabase\SAM_log.ldf"

sqlcmd -S "(local)\SQL2012SP1" -Q "CREATE DATABASE [SAM] ON (FILENAME = '$mdfFile'), (FILENAME = '$ldfFile') for ATTACH"

Note: have been using https://github.com/hadley/devtools as an R package appveyor example. Thanks for any direction!

Solution

  • I cloned your repository and was able to overcome this issue by stopping SQL server before failing command and starting it after. It looks like this in travis-tool.sh inside RunTests() function.

    net stop “SQL Server (SQL2012SP1)” R CMD build ${R_BUILD_ARGS} . net start “SQL Server (SQL2012SP1)”

    This helped to avoid copy file issue. However, I really don’t know if SQL server in running state required for that failing step or not. Or maybe SQL server is not required at all for tests and can be stopped before test outside of travis-tool.sh.

    In any case I hope this can give an idea of what you can do.

    --ilya.