Repository: microsoft/sqlmlutils (language: python)

Issue:
R module installation on SQL Managed Instance
I am using sqlmlutils to install R modules onto SQL Managed instance in Azure

The particular issue that I have is that although the log output for the installation of data.table shows as Version 1.14.6 being downloaded, 

2022-11-21 15:04:11.81  Starting package install on SQL server (Driver=SQL Server;Server=<redacted>;Database=master;uid=<redacted>;pwd=<redacted>;)...
2022-11-21 15:04:11.81  Verifying permissions to install packages on SQL server...
2022-11-21 15:04:22.92  Resolving package dependencies for (data.table)...
2022-11-21 15:04:25.88  Downloading package [1/1] data.table (1.14.6)...
2022-11-21 15:04:29.99  Copying package to Sql server [1/1] data.table...
2022-11-21 15:04:32.59  Installing packages to library path, this may take some time...
2022-11-21 15:04:34.79  Successfully installed packages on SQL server (data.table).


from SQL query after module installation, version reported is  1.12.8 (installed into C:/WFRoot/DB8C.1/Fabric/work/Applications/Worker.CL_App14/work/Data/ExternalLibraries/32763/1/1 while all other modules are in C:/WFRoot/Ext/R.9.4.7.1185/library)

Is there any way to get data.table 1.14.6 installed on SQL Managed instance as we'd like to use some of the most recent functions?


Discussion / hints:
Hi @AllenSuttonValocity ,

I'm sorry for the delayed response. 

SQL Managed Instance uses R 3.5.2. Consequentially, when _sqlmlutils_ resolves which Windows binary package to download from CRAN, sqlmlutils uses the binary listed on `https://cran.rstudio.com/bin/windows/contrib/3.5/` which is version **1.12.8**. 

There is a workaround. However, while writing the instructions, I uncovered a bug with how a package name is parsed from a zipped R binary. I'm working on a fix. 

Once fixed, the work-around to install the most recent version of `data.table` for R 3.5.2,  would be to build a binary for R3.5.2 from source. You will need to have [rtools35](https://cran.rstudio.com/bin/windows/Rtools/history.html) to build from source, locally. Once built, you would be able to use sqlmlutils to upload the zipped binary.


Fix patch:
diff --git a/R/R/sqlPackage.R b/R/R/sqlPackage.R
index cb658459..39605920 100644
--- a/R/R/sqlPackage.R
+++ b/R/R/sqlPackage.R
@@ -1738,27 +1738,75 @@ sqlInstallPackagesExtLib <- function(connectionString,
         {
             # caller set repos = NULL, packages are file paths
             #
-            pkgs <- normalizePath(pkgs, mustWork = FALSE)
-            missingPkgs <- pkgs[!file.exists(pkgs)]
+            areValidFilesPaths(pkgs)
+            packages <- enumeratePackagesFromFilePaths(pkgs, topMostPackageFlag)
+            sqlHelperInstallPackages(connectionString, packages, owner, scope, verbose, languageName)
+        }
+    }
+}
 
-            if (length(missingPkgs) > 0)
-            {
-                stop(sprintf("%s packages are missing.", paste0(missingPkgs, collapse = ", ")), call. = FALSE)
-            }
+#
+# Validates that the each file path in pkgs references an existing file.
+#
+areValidFilesPaths <- function(pkgs)
+{
+    pkgs <- normalizePath(pkgs, mustWork = FALSE)
+    missingPkgs <- pkgs[!file.exists(pkgs)]
 
-            packages <- data.frame(matrix(nrow = 0, ncol = 3), stringsAsFactors = FALSE)
-            for( packageFile in pkgs)
-            {
-                packages <- rbind(packages, data.frame(
-                    Package = unlist(lapply(strsplit(basename(packageFile), '\\.|_'), '[[', 1), use.names =  F),
-                    File = packageFile,
-                    Attribute = topMostPackageFlag,
-                    stringsAsFactors = FALSE))
-            }
+    if (length(missingPkgs) > 0)
+    {
+        stop(sprintf("%s packages are missing.", paste0(missingPkgs, collapse = ", ")), call. = FALSE)
+    }
+}
 
-            sqlHelperInstallPackages(connectionString, packages, owner, scope, verbose, languageName)
-        }
+#
+# Returns a data.frame consisting of metadata from the argument pkgs
+# pkgs -> list of binary package file paths provided by a user to sql_install.packages()
+# Returned data.frame consists of:
+# [Package]   -> 'packageName'
+# [File]      -> 'PackageFilePath'
+# [Attribute] -> 'topMostPackageFlag'
+#
+enumeratePackagesFromFilePaths <- function(pkgs, topMostPackageFlag)
+{
+    packages <- data.frame(matrix(nrow = 0, ncol = 3), stringsAsFactors = FALSE)
+    for( packageFilePath in pkgs)
+    {
+        packages <- rbind(packages, data.frame(
+            Package = getPackageNameFromFilePath(packageFilePath),
+            File = packageFilePath,
+            Attribute = topMostPackageFlag,
+            stringsAsFactors = FALSE))
     }
+
+    return (packages)
+}
+
+#
+# Returns the package name parsed from the file path
+# of a binary package.
+# Returns my.package from C:\packages\my.package_1.0.0.zip
+#
+# A package name "should contain only (ASCII) letters, numbers and dot, have at least two
+# characters and start with a letter and not end in a dot.
+# Source: https://cran.r-project.org/doc/manuals/r-devel/R-exts.html#The-DESCRIPTION-file
+#
+getPackageNameFromFilePath <- function(packageFilePath)
+{
+    # Drops the file path prefixing the file name
+    # C:\packages\my.package_1.0.0.zip becomes my.package_1.0.0.zip
+    fileNameFromPath <- basename(packageFilePath)
+
+    # Splits path at instances of an underscore (_)
+    # my.package_1.0.0.zip becomes [0] my.package [1] 1.0.0.zip
+    packageFileNameComponents <- strsplit(fileNameFromPath, '\\_')
+
+    # Applies subsetting operator [[ to return a list
+    # with just the element my.package from [0] my.package [1] 1.0.0.zip
+    packageName <- lapply(packageFileNameComponents, '[[', 1)
+
+    # Gets the character element my.package which represents the package name
+    unlist(packageName, use.names =  F)
 }
 
 #


Test patch:
diff --git a/R/tests/testthat/test.sqlPackage.fileNameParse.unit.R b/R/tests/testthat/test.sqlPackage.fileNameParse.unit.R
new file mode 100644
index 00000000..bfd594e7
--- /dev/null
+++ b/R/tests/testthat/test.sqlPackage.fileNameParse.unit.R
@@ -0,0 +1,38 @@
+# Copyright(c) Microsoft Corporation. All rights reserved.
+# Licensed under the MIT license.
+
+library(sqlmlutils)
+library(testthat)
+
+context("Tests for sqlmlutils package management file path parsing helpers")
+
+#
+# A package name "should contain only (ASCII) letters, numbers and dot, have at least two
+# characters and start with a letter and not end in a dot.
+# Source: https://cran.r-project.org/doc/manuals/r-devel/R-exts.html#The-DESCRIPTION-file
+# Consequentially, this test ensures that the parsed package name are the characters
+# that appear before the first underscore (_)
+#
+test_that("getPackageNameFromFilePath outputs correct package name", {
+    expect_equal(sqlmlutils:::getPackageNameFromFilePath(c('C:\\packages\\binaries\\data.table_1.14.6.zip')), 'data.table')
+    expect_equal(sqlmlutils:::getPackageNameFromFilePath(c('C:\\packages\\binaries\\sqlmlutils_1.2.0.zip')), 'sqlmlutils')
+    expect_equal(sqlmlutils:::getPackageNameFromFilePath(c('C:\\packages\\binaries\\mypackage_metadata_1.14.6.zip')), 'mypackage')
+    expect_equal(sqlmlutils:::getPackageNameFromFilePath(c('C:\\packages\\binaries\\st_1.2.7.zip')), 'st')
+})
+
+#
+# Tests that checking for file existance on invalid filepaths properly fails.
+#
+test_that("areValidFilesPaths fails when files do not exist", {
+    # Using 1 as a default value, functions under test don't use the value
+    # for any calculations
+    topMostPackageFlagAttribute <- 1
+
+    # Generate list of sample file paths that would be provided by a user to sql_install.packages()
+    fileList <- c('C:\\packages\\binaries\\data.table_1.14.6.zip')
+    fileList <- append(fileList, c('C:\\packages\\binaries\\sqlmlutils_1.2.0.zip'))
+    fileList <- append(fileList, c('C:\\packages\\binaries\\st_1.2.7.zip'))
+
+    # As this is a unit test, the sample files in fileList do not actually exist
+    expect_error(sqlmlutils:::areValidFilesPaths(pkgs = fileList))
+})