Beispiel #1
0
        private ServiceResponse UpdateDatabaseSchema(DatabaseInfo info, string databaseUserNamePublic, string scriptResourceName, IEnumerable<string> scriptResourceNames, ScriptLoader scriptLoader)
        {
            // Create temp App Domain for loading assemblies
            AppDomain tempAppDomain = AppDomain.CreateDomain("TempAppDomain");

            try
            {
                using (var ts = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 5, 0)))
                {
                    using (var database = new DatabaseManipulator(info))
                    {
                        smo.Server sqlServer = database.SqlServer;

                        // Create the script version info table if needed
                        var versionInfoDao = new SqlScriptVersionInfoDao(info.ConnectionString);
                        versionInfoDao.CreateScriptVersionTable(info);

                        _logger.Info("Verifying database schema...", "Database Name", info.DatabaseName);

                        // Get script execution history as a dictionary
                        var sqlScriptVersionInfoDict = new Dictionary<string, string>();

                        foreach (IVersionInfo sqlScriptVersionInfo in versionInfoDao.ReadList())
                        {
                            sqlScriptVersionInfoDict.Add(sqlScriptVersionInfo.Component, sqlScriptVersionInfo.Version);
                        }

                        // Get all assembly names in the specified directory
                        IEnumerable<string> files = scriptLoader.GetSortedScriptAssemblyList();

                        foreach (string file in files)
                        {
                            // Delegate assembly loading/processing to temp App Domain
                            tempAppDomain.SetData("FileName", file);
                            tempAppDomain.SetData("ResourceFileName", scriptResourceName);
                            tempAppDomain.SetData("ResourceFileNames", scriptResourceNames);

                            var dbScriptsToExecute = new Dictionary<string, string>();

                            try
                            {
                                tempAppDomain.DoCallBack(new CrossAppDomainDelegate(ScriptLoader.GetScriptsFromAssembly));
                                dbScriptsToExecute = (Dictionary<string, string>)tempAppDomain.GetData("DbScriptsToExecute");
                            }
                            catch (Exception)
                            {
                                _logger.Error("Error loading assembly!", "FileName", file);
                            }

                            // Sort the scripts by key
                            string[] sortedScriptKeys = new string[dbScriptsToExecute.Keys.Count];
                            dbScriptsToExecute.Keys.CopyTo(sortedScriptKeys, 0);
                            Array.Sort(sortedScriptKeys);

                            foreach (string scriptKey in sortedScriptKeys)
                            {
                                string scriptValue = dbScriptsToExecute[scriptKey];
                                string[] keyParts = scriptKey.Split("_".ToCharArray());

                                if (keyParts.GetUpperBound(0) != 1)
                                {
                                    throw new InvalidOperationException(string.Format(CultureInfo.InvariantCulture, "Invalid script key format ({0})", scriptKey));
                                }

                                string component = Path.GetFileNameWithoutExtension(file);
                                string version = keyParts[1];

                                // If component has no script history, or current script hasn't been executed, execute it
                                if (!sqlScriptVersionInfoDict.ContainsKey(component) ||
                                    (int.Parse(version, CultureInfo.InvariantCulture) >
                                     int.Parse(sqlScriptVersionInfoDict[component], CultureInfo.InvariantCulture)))
                                {
                                    ConsoleUtils.WriteLine(string.Format(CultureInfo.InvariantCulture,
                                        "        Executing\n          Component: {0}\n          Script:    {1}\n", component, scriptKey), ConsoleColor.Green);

                                    scriptValue = ApplyScriptTokens(scriptValue, databaseUserNamePublic);
                                    sqlServer.Databases[info.DatabaseName].ExecuteNonQuery(scriptValue);

                                    // Check if script version information already exists for this component
                                    IVersionInfo versionInfo = versionInfoDao.Read(component);

                                    AddOrUpdateVersionInfo(component, version, versionInfo, versionInfoDao);
                                }
                            }
                        }

                        ts.Complete();
                    }
                }
            }
            catch (Exception ex)
            {
                _logger.Error("Update of database failed. Please check that you have configured the database server to allow both SQL Server authentication and Windows authentication.", ex, info.GenerateCustomLoggingProperties());
                _response.AddError("Update of database ({0}) on server ({1}) failed.", info.DatabaseName, info.ServerName);
            }
            finally
            {
                // Unload the temp App Domain, which unloads any loaded assemblies
                AppDomain.Unload(tempAppDomain);
            }

            return _response;
        }
Beispiel #2
0
        public ServiceResponse UpdateDatabaseSchema(DatabaseInfo info, string databaseUserNamePublic, string component, string scriptPath)
        {
            try
            {
                using (var ts = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 5, 0)))
                {
                    using (var database = new DatabaseManipulator(info))
                    {
                        smo.Server sqlServer = database.SqlServer;

                        var versionInfoDao = new SqlScriptVersionInfoDao(info.ConnectionString);
                        versionInfoDao.CreateScriptVersionTable(info);

                        _logger.Info("Verifying database schema...", "Database Name", info.DatabaseName);

                        int currentVersion = 0;
                        IVersionInfo sqlScriptVersionInfo = versionInfoDao.ReadList()
                            .Where(v => v.Component == component)
                            .OrderBy(v => v.Version)
                            .LastOrDefault();
                        if (sqlScriptVersionInfo != null)
                        {
                            currentVersion = int.Parse(sqlScriptVersionInfo.Version, CultureInfo.InvariantCulture);
                        }

                        IEnumerable<string> files = Directory.GetFiles(scriptPath, "script_*.sql");

                        foreach (string file in files)
                        {
                            string scriptName = Path.GetFileNameWithoutExtension(file);
                            string scriptValue = File.ReadAllText(file);
                            string[] keyParts = scriptName.Split("_".ToCharArray());

                            string versionString = keyParts[1];
                            int scriptVersion = int.Parse(versionString, CultureInfo.InvariantCulture);

                            if (scriptVersion > currentVersion)
                            {
                                ConsoleUtils.WriteLine(string.Format(CultureInfo.InvariantCulture,
                                    "        Executing          Component: {0}          Script:    {1}\n", component, scriptName), ConsoleColor.Green);

                                scriptValue = ApplyScriptTokens(scriptValue, databaseUserNamePublic);
                                sqlServer.Databases[info.DatabaseName].ExecuteNonQuery(scriptValue);

                                // Check if script version information already exists for this component
                                IVersionInfo versionInfo = versionInfoDao.Read(component);

                                AddOrUpdateVersionInfo(component, versionString, versionInfo, versionInfoDao);
                            }
                        }
                        ts.Complete();
                    }
                }
            }
            catch (Exception ex)
            {
                _logger.Error("Update of database failed. Please check that you have configured the database server to allow both SQL Server authentication and Windows authentication.", ex, info.GenerateCustomLoggingProperties());
                _response.AddError("Update of database ({0}) on server ({1}) failed.", info.DatabaseName, info.ServerName);
            }

            return _response;
        }