def package_files_in_scope(scope=Scope.private_scope()):
envdir = _ENV_NAME_SHARED_PATH if scope == Scope.public_scope() or os.environ.get(_ENV_NAME_USER_PATH, "") == "" \
else _ENV_NAME_USER_PATH
path = os.environ.get(envdir, "")
if os.path.isdir(path):
return os.listdir(path)
return []
def test_scope():
_remove_all_new_packages(pkgmanager)
package = os.path.join(path_to_packages, "testpackageA-0.0.1.zip")
def get_location():
import testpackageA
return testpackageA.__file__
_revotesterconnection = sqlmlutils.ConnectionInfo(server="localhost",
database="AirlineTestDB",
uid="Tester",
pwd="FakeT3sterPwd!")
revopkgmanager = SQLPackageManager(_revotesterconnection)
revoexecutor = SQLPythonExecutor(_revotesterconnection)
revopkgmanager.install(package, scope=Scope.private_scope())
private_location = revoexecutor.execute_function_in_sql(get_location)
pkg_name = "testpackageA"
pyexecutor.execute_function_in_sql(check_package,
package_name=pkg_name,
exists=False)
revopkgmanager.uninstall(pkg_name, scope=Scope.private_scope())
revopkgmanager.install(package, scope=Scope.public_scope())
public_location = revoexecutor.execute_function_in_sql(get_location)
assert private_location != public_location
pyexecutor.execute_function_in_sql(check_package,
package_name=pkg_name,
exists=True,
class_to_check='ClassA')
revopkgmanager.uninstall(pkg_name, scope=Scope.public_scope())
revoexecutor.execute_function_in_sql(check_package,
package_name=pkg_name,
exists=False)
pyexecutor.execute_function_in_sql(check_package,
package_name=pkg_name,
exists=False)
def package_exists_in_scope(sql_package_name: str, scope=None) -> bool:
if scope is None:
# default to user path for every user but DBOs
scope = Scope.public_scope() if (os.environ.get(
_ENV_NAME_USER_PATH, "") == "") else Scope.private_scope()
package_files = package_files_in_scope(scope)
return any([
_is_package_match(sql_package_name, package_file)
for package_file in package_files
])
def _remove_all_new_packages(manager):
libs = {
dic['external_library_id']: (dic['name'], dic['scope'])
for dic in _get_sql_package_table(connection)
}
original_libs = {
dic['external_library_id']: (dic['name'], dic['scope'])
for dic in originals
}
for lib in libs:
pkg, sc = libs[lib]
if lib not in original_libs:
print("uninstalling" + str(lib))
if sc:
manager.uninstall(pkg, scope=Scope.private_scope())
else:
manager.uninstall(pkg, scope=Scope.public_scope())
else:
if sc != original_libs[lib][1]:
if sc:
manager.uninstall(pkg, scope=Scope.private_scope())
else:
manager.uninstall(pkg, scope=Scope.public_scope())
def _get_authorization(scope: Scope) -> str:
return "AUTHORIZATION dbo" if scope == Scope.public_scope() else ""
# pip install sqlmlutils
import sqlmlutils
from sqlmlutils.packagemanagement.scope import Scope
# Connect to the Database
# NOTE: need to do this for *each* database!
# Uncomment this version if you want to connect to a Docker container.
#conn = sqlmlutils.ConnectionInfo(server="localhost,52433", database="ExpenseReports", uid="sa", pwd="SomeBadP@ssword3")
# Uncomment this version if you want to connect to a local installation of SQL Server 2019.
#conn = sqlmlutils.ConnectionInfo(server="localhost", database="ExpenseReports")
sqlpy = sqlmlutils.SQLPythonExecutor(conn)
pkgmanager = sqlmlutils.SQLPackageManager(conn)
# Install a package as dbo, allowing anybody to use it.
# Upgrade if there is a newer version than what is already installed.
pkgmanager.install("werkzeug", upgrade=True, scope=Scope.public_scope())
# By default, install just for your account if you are not a sysadmin.
# You can also specify a private scope.
pkgmanager.install("termcolor")
# We can also install and uninstall our own custom packages.
#pkgmanager.uninstall("my_custom_package", scope = Scope.public_scope())
#pkgmanager.install(package = "C:\\Temp\\MyCustomPackage\\dist\\my_custom_package-0.0.1-py3-none-any.whl", scope = Scope.public_scope())
def _get_default_scope(self):
query = "SELECT IS_SRVROLEMEMBER ('sysadmin') as is_sysadmin"
is_sysadmin = self._pyexecutor.execute_sql_query(query)["is_sysadmin"].iloc[0]
return Scope.public_scope() if is_sysadmin == 1 else Scope.private_scope()