1 files changed, 378 insertions, 0 deletions
diff --git a/Software/Visual_Studio/Azure/Tango.AzureUtils/Database/DatabaseManager.cs b/Software/Visual_Studio/Azure/Tango.AzureUtils/Database/DatabaseManager.cs
new file mode 100644
index 000000000..cb1a608a8
--- /dev/null
+++ b/Software/Visual_Studio/Azure/Tango.AzureUtils/Database/DatabaseManager.cs
@@ -0,0 +1,378 @@
+using System;
+using System.Collections.Generic;
+using System.ComponentModel.DataAnnotations;
+using System.Data.Entity;
+using System.Diagnostics;
+using System.IO;
+using System.Linq;
+using System.Text;
+using System.Threading.Tasks;
+using System.Xml.Linq;
+using Microsoft.Azure.Management.AppService.Fluent;
+using Microsoft.Azure.Management.Fluent;
+using Microsoft.Azure.Management.Sql.Fluent;
+using Microsoft.Azure.Management.Sql.Fluent.Models;
+using Tango.BL;
+using Tango.BL.Entities;
+using Tango.Core;
+using Tango.Core.DB;
+using Tango.Core.Helpers;
+
+namespace Tango.AzureUtils.Database
+{
+    public class DatabaseManager : AzureUtilsComponentBase
+    {
+        #region Constructors
+
+        /// <summary>
+        /// Initializes a new instance of the <see cref="DatabaseManager"/> class.
+        /// </summary>
+        /// <param name="azure">The azure.</param>
+        public DatabaseManager(IAzure azure) : base(azure)
+        {
+
+        }
+
+        #endregion
+
+        #region SQL Examiner
+
+        /// <summary>
+        /// Opens the SQL examiner schema using the specified source app and target app configuration.
+        /// </summary>
+        /// <param name="sourceApp">The source application.</param>
+        /// <param name="targetApp">The target application.</param>
+        /// <returns></returns>
+        public async Task OpenSQLExaminerSchema(IWebAppBase sourceApp, IWebAppBase targetApp)
+        {
+            String projectFile = Path.Combine(AssemblyHelper.GetCurrentAssemblyFolder(), "Database", "GENERAL_ENV_UPGRADE.seproj");
+
+            using (Stream stream = GetProjectFileStream(projectFile))
+            {
+                XElement projectXml = XElement.Load(stream);
+                var sourceSettings = await sourceApp.GetMachineServiceSettingsAsync();
+                var targetSettings = await targetApp.GetMachineServiceSettingsAsync();
+                ApplyDatabaseSettingsToProjectXml(projectXml, sourceSettings, targetSettings);
+
+                var tempFile = TemporaryManager.CreateImaginaryFile(".seproj");
+                tempFile.Persist = true;
+
+                File.WriteAllText(tempFile, projectXml.ToString());
+
+                Process.Start(tempFile);
+            }
+        }
+
+        /// <summary>
+        /// Opens the SQL examiner data using the specified source app and target app configuration.
+        /// </summary>
+        /// <param name="sourceApp">The source application.</param>
+        /// <param name="targetApp">The target application.</param>
+        /// <param name="forCreation">if set to <c>true</c> [for creation].</param>
+        /// <returns></returns>
+        public async Task OpenSQLExaminerData(IWebAppBase sourceApp, IWebAppBase targetApp, bool forCreation = false)
+        {
+            String projectFile = Path.Combine(AssemblyHelper.GetCurrentAssemblyFolder(), "Database", "GENERAL_ENV_UPGRADE.sdeproj");
+
+            if (forCreation)
+            {
+                projectFile = Path.Combine(AssemblyHelper.GetCurrentAssemblyFolder(), "Database", "GENERAL_ENV_CREATE.sdeproj");
+            }
+
+            using (Stream stream = GetProjectFileStream(projectFile))
+            {
+                XElement projectXml = XElement.Load(stream);
+                var sourceSettings = await sourceApp.GetMachineServiceSettingsAsync();
+                var targetSettings = await targetApp.GetMachineServiceSettingsAsync();
+                ApplyDatabaseSettingsToProjectXml(projectXml, sourceSettings, targetSettings);
+
+                var tempFile = TemporaryManager.CreateImaginaryFile(".sdeproj");
+                tempFile.Persist = true;
+
+                File.WriteAllText(tempFile, projectXml.ToString());
+
+                Process.Start(tempFile);
+            }
+        }
+
+        private Stream GetProjectFileStream(String projectFile)
+        {
+            byte[] projectBytes = File.ReadAllBytes(projectFile);
+            MemoryStream stream = new MemoryStream(projectBytes);
+            return stream;
+        }
+
+        private void ApplyDatabaseSettingsToProjectXml(XElement projectXml, MachineServiceSettings sourceSettings, MachineServiceSettings targetSettings)
+        {
+            var sourceElement = projectXml.Elements().SelectMany(x => x.Descendants()).SingleOrDefault(x => x.Name == "Source" && x.Attributes().SingleOrDefault(y => y.Name == "id").Value == "1");
+            var targetElement = projectXml.Elements().SelectMany(x => x.Descendants()).SingleOrDefault(x => x.Name == "Source" && x.Attributes().SingleOrDefault(y => y.Name == "id").Value == "2");
+
+            ApplyDatabaseSettingsToSourceElement(sourceElement, sourceSettings);
+            ApplyDatabaseSettingsToSourceElement(targetElement, targetSettings);
+        }
+
+        private void ApplyDatabaseSettingsToSourceElement(XElement sourceElement, MachineServiceSettings settings)
+        {
+            sourceElement.Element("ServerName").SetValue(settings.DB_ADDRESS);
+            sourceElement.Element("Database").SetValue(settings.DB_CATALOG);
+            sourceElement.Element("Login").SetValue(settings.DB_USER_NAME);
+            sourceElement.Element("Password").SetValue(settings.DB_PASSWORD);
+        }
+
+        #endregion
+
+        #region Azure SQL Database
+
+        public async Task<bool> IsDatabaseExists(String serverName, String databaseName)
+        {
+            OnProgress(AzureUtilsStage.Database, $"Checking {serverName} database server...");
+            var sqlServer = (await Azure.SqlServers.ListAsync()).SingleOrDefault(x => x.Name == serverName);
+
+            var database = (await sqlServer.Databases.ListAsync()).SingleOrDefault(x => x.Name == databaseName);
+
+            return database != null;
+        }
+
+        public async Task<ISqlDatabase> AddDatabase(String serverName, String databaseName)
+        {
+            OnProgress(AzureUtilsStage.Database, $"Checking {serverName} database server...");
+            var sqlServer = (await Azure.SqlServers.ListAsync()).SingleOrDefault(x => x.Name == serverName);
+
+            OnProgress(AzureUtilsStage.Database, $"Creating new database '{databaseName}'...");
+            var database = await sqlServer.Databases.Define(databaseName).WithEdition(DatabaseEdition.Standard).CreateAsync();
+
+            return database;
+        }
+
+        public Task<ISqlDatabase> AddDatabase(String databaseName)
+        {
+            return AddDatabase("twine", databaseName);
+        }
+
+        public async Task RemoveDatabase(String serverName, String databaseName)
+        {
+            OnProgress(AzureUtilsStage.Database, $"Checking {serverName} database server...");
+            var sqlServer = (await Azure.SqlServers.ListAsync()).SingleOrDefault(x => x.Name == serverName);
+
+            OnProgress(AzureUtilsStage.Database, $"Removing database '{databaseName}'...");
+            await sqlServer.Databases.DeleteAsync(databaseName);
+        }
+
+        #endregion
+
+        #region Users & Permissions
+
+        public async Task AddEnvironmentGroupPermissions(String address, String databaseName, String groupName, String email, String password)
+        {
+            OnProgress(AzureUtilsStage.Database, $"Adding environment group permissions on '{databaseName}'...");
+
+            using (DbManager db = DbManager.FromDataSource(new DataSource()
+            {
+                Type = DataSourceType.Azure,
+                Address = address,
+                Catalog = databaseName,
+                UserName = email,
+                Password = password,
+                IntegratedSecurity = false
+            }))
+            {
+                await db.ExecuteCommandAsync($"CREATE USER [{groupName}] FROM  EXTERNAL PROVIDER");
+                await db.ExecuteCommandAsync($"ALTER ROLE db_datareader ADD MEMBER [{groupName}];");
+                await db.ExecuteCommandAsync($"ALTER ROLE db_datawriter ADD MEMBER [{groupName}];");
+            }
+        }
+
+        public async Task AddBackupUser(String address, String databaseName, String userName, String password)
+        {
+            OnProgress(AzureUtilsStage.Database, $"Adding BackupUser permissions on '{databaseName}'...");
+
+            using (DbManager db = DbManager.FromCredentials(address, databaseName, userName, password))
+            {
+                await db.ExecuteCommandAsync("CREATE USER [BackupUser] FOR LOGIN [BackupUser] WITH DEFAULT_SCHEMA=[dbo]");
+                await db.ExecuteCommandAsync("EXEC sp_addrolemember N'db_owner', N'BackupUser'");
+                await db.ExecuteCommandAsync("EXEC sp_addrolemember N'db_accessadmin', N'BackupUser'");
+                await db.ExecuteCommandAsync("EXEC sp_addrolemember N'db_securityadmin', N'BackupUser'");
+                await db.ExecuteCommandAsync("EXEC sp_addrolemember N'db_backupoperator', N'BackupUser'");
+                await db.ExecuteCommandAsync("EXEC sp_addrolemember N'db_datareader', N'BackupUser'");
+                await db.ExecuteCommandAsync("EXEC sp_addrolemember N'db_datawriter', N'BackupUser'");
+            }
+        }
+
+        #endregion
+
+        #region Versions
+
+        public async Task UpgradeMachineStudioVersion(IWebAppBase sourceApp, IWebAppBase targetApp)
+        {
+            var latestMachineStudioVersion = await GetLatestMachineStudioVersion(sourceApp);
+            var targetDataSource = (await targetApp.GetMachineServiceSettingsAsync()).ToDataSource();
+
+            OnProgress(AzureUtilsStage.Database, $"Adding machine studio database entry for version '{latestMachineStudioVersion.Version}'...");
+
+            using (var db = ObservablesContext.CreateDefault(targetDataSource))
+            {
+                db.MachineStudioVersions.Add(latestMachineStudioVersion);
+                await db.SaveChangesAsync();
+            }
+        }
+
+        public async Task UpgradePPCVersion(IWebAppBase sourceApp, IWebAppBase targetApp)
+        {
+            var latestPPCVersion = await GetLatestPPCVersion(sourceApp);
+
+            var targetDataSource = (await targetApp.GetMachineServiceSettingsAsync()).ToDataSource();
+
+            OnProgress(AzureUtilsStage.Database, $"Adding machine studio database entry for version '{latestPPCVersion.Version}'...");
+
+            using (var db = ObservablesContext.CreateDefault(targetDataSource))
+            {
+                db.TangoVersions.Add(latestPPCVersion);
+                await db.SaveChangesAsync();
+            }
+        }
+
+        public async Task DowngradeMachineStudioVersion(IWebAppBase app)
+        {
+            var latestMachineStudioVersion = await GetLatestMachineStudioVersion(app);
+            var dataSource = (await app.GetMachineServiceSettingsAsync()).ToDataSource();
+
+            OnProgress(AzureUtilsStage.Database, $"Removing machine studio database entry for version '{latestMachineStudioVersion.Version}'...");
+
+            using (var db = ObservablesContext.CreateDefault(dataSource))
+            {
+                var latest = await db.MachineStudioVersions.SingleOrDefaultAsync(x => x.Guid == latestMachineStudioVersion.Guid);
+                db.MachineStudioVersions.Remove(latest);
+                await db.SaveChangesAsync();
+            }
+        }
+
+        public async Task DowngradePPCVersion(IWebAppBase app)
+        {
+            var latestPPCVersion = await GetLatestPPCVersion(app);
+            var dataSource = (await app.GetMachineServiceSettingsAsync()).ToDataSource();
+
+            OnProgress(AzureUtilsStage.Database, $"Removing PPC database entry for version '{latestPPCVersion.Version}'...");
+
+            using (var db = ObservablesContext.CreateDefault(dataSource))
+            {
+                var latest = await db.TangoVersions.SingleOrDefaultAsync(x => x.Guid == latestPPCVersion.Guid);
+                db.TangoVersions.Remove(latest);
+                await db.SaveChangesAsync();
+            }
+        }
+
+        public async Task<MachineStudioVersion> GetLatestMachineStudioVersion(IWebAppBase app)
+        {
+            OnProgress(AzureUtilsStage.Database, $"Getting latest machine studio version on '{app.Name}'...");
+
+            MachineServiceSettings settings = null;
+
+            try
+            {
+                settings = await app.GetMachineServiceSettingsAsync();
+            }
+            catch (Exception ex)
+            {
+                throw new ArgumentException("Could not fetch machine service settings. Please check that all settings are available.", ex);
+            }
+
+            try
+            {
+                DataSource dataSource = settings.ToDataSource();
+
+                using (var db = ObservablesContext.CreateDefault(dataSource))
+                {
+                    var versions = await db.MachineStudioVersions.ToListAsync();
+                    var latest_machine_version = versions.OrderByDescending(x => Version.Parse(x.Version)).FirstOrDefault();
+                    return latest_machine_version;
+                }
+            }
+            catch (Exception ex)
+            {
+                throw new InvalidDataException($"Could not retrieve '{app.Name}' latest Machine Studio version from database.", ex);
+            }
+        }
+
+        public async Task<TangoVersion> GetLatestPPCVersion(IWebAppBase app)
+        {
+            OnProgress(AzureUtilsStage.Database, $"Getting latest PPC version on '{app.Name}'...");
+
+            MachineServiceSettings settings = null;
+
+            try
+            {
+                settings = await app.GetMachineServiceSettingsAsync();
+            }
+            catch (Exception ex)
+            {
+                throw new ArgumentException("Could not fetch machine service settings. Please check that all settings are available.", ex);
+            }
+
+            try
+            {
+                DataSource dataSource = settings.ToDataSource();
+
+                using (var db = ObservablesContext.CreateDefault(dataSource))
+                {
+                    var versions = await db.TangoVersions.ToListAsync();
+                    var latest_machine_version = versions.OrderByDescending(x => Version.Parse(x.Version)).FirstOrDefault();
+                    return latest_machine_version;
+                }
+            }
+            catch (Exception ex)
+            {
+                throw new InvalidDataException($"Could not retrieve '{app.Name}' latest PPC version from database.", ex);
+            }
+        }
+
+        public async Task ValidateMachineStudioDatabaseUpgrade(IWebAppBase sourceApp, IWebAppBase targetApp)
+        {
+            OnProgress(AzureUtilsStage.Validating, "Validating machine studio database upgrade...");
+
+            var sourceSettings = await sourceApp.GetMachineServiceSettingsAsync();
+            var targetSettings = await targetApp.GetMachineServiceSettingsAsync();
+
+            var latestSourceMachineStudioVersion = await GetLatestMachineStudioVersion(sourceApp);
+
+            //Check if there is any source machine studio version.
+            if (latestSourceMachineStudioVersion == null)
+            {
+                throw new ValidationException("Could not locate a Machine Studio version entry on the source database.");
+            }
+
+            var latestTargetMachineStudioVersion = await GetLatestMachineStudioVersion(targetApp);
+
+            //Check target latest machine studio version is older if there is any.
+            if (latestTargetMachineStudioVersion != null && Version.Parse(latestSourceMachineStudioVersion.Version) <= Version.Parse(latestTargetMachineStudioVersion.Version))
+            {
+                throw new ValidationException($"Machine Studio source version is '{latestSourceMachineStudioVersion.Version}' while target version is '{latestTargetMachineStudioVersion.Version}'.");
+            }
+        }
+
+        public async Task ValidatePPCDatabaseUpgrade(IWebAppBase sourceApp, IWebAppBase targetApp)
+        {
+            OnProgress(AzureUtilsStage.Validating, "Validating PPC database upgrade...");
+
+            var sourceSettings = await sourceApp.GetMachineServiceSettingsAsync();
+            var targetSettings = await targetApp.GetMachineServiceSettingsAsync();
+
+            var latestSourcePPCVersion = await GetLatestPPCVersion(sourceApp);
+
+            //Check if there is any source PPC version.
+            if (latestSourcePPCVersion == null)
+            {
+                throw new ValidationException("Could not locate a PPC version entry on the source database.");
+            }
+
+            var latestTargetPPCVersion = await GetLatestPPCVersion(targetApp);
+
+            //Check target latest PPC version is older if there is any.
+            if (latestTargetPPCVersion != null && Version.Parse(latestSourcePPCVersion.Version) <= Version.Parse(latestTargetPPCVersion.Version))
+            {
+                throw new ValidationException($"PPC source version is '{latestSourcePPCVersion.Version}' while target version is '{latestTargetPPCVersion.Version}'.");
+            }
+        }
+
+        #endregion
+    }
+}