6245535001

Committed 20 Sep 2023 07:44AM UTC coverage: 57.013%. First build

Build # 6245535001

Build Type

push

github

Committed by

web-flow

Commit Message

8.1.0 Release (#1628)

* Bump Newtonsoft.Json from 13.0.1 to 13.0.2

Bumps [Newtonsoft.Json](https://github.com/JamesNK/Newtonsoft.Json) from 13.0.1 to 13.0.2.
- [Release notes](https://github.com/JamesNK/Newtonsoft.Json/releases)
- [Commits](https://github.com/JamesNK/Newtonsoft.Json/compare/13.0.1...13.0.2)

---
updated-dependencies:
- dependency-name: Newtonsoft.Json
  dependency-type: direct:production
  update-type: version-update:semver-patch
...

Signed-off-by: dependabot[bot] <support@github.com>

* Bump NLog from 5.0.5 to 5.1.0

Bumps [NLog](https://github.com/NLog/NLog) from 5.0.5 to 5.1.0.
- [Release notes](https://github.com/NLog/NLog/releases)
- [Changelog](https://github.com/NLog/NLog/blob/dev/CHANGELOG.md)
- [Commits](https://github.com/NLog/NLog/compare/v5.0.5...v5.1.0)

---
updated-dependencies:
- dependency-name: NLog
  dependency-type: direct:production
  update-type: version-update:semver-minor
...

Signed-off-by: dependabot[bot] <support@github.com>

* Bump NLog from 5.0.5 to 5.1.0

* Fix -r flag - should have been --results-directory all along

* Bump Newtonsoft.Json from 13.0.1 to 13.0.2

* Bump YamlDotNet from 12.0.2 to 12.1.0

Bumps [YamlDotNet](https://github.com/aaubry/YamlDotNet) from 12.0.2 to 12.1.0.
- [Release notes](https://github.com/aaubry/YamlDotNet/releases)
- [Commits](https://github.com/aaubry/YamlDotNet/compare/v12.0.2...v12.1.0)

---
updated-dependencies:
- dependency-name: YamlDotNet
  dependency-type: direct:production
  update-type: version-update:semver-minor
...

Signed-off-by: dependabot[bot] <support@github.com>

* Bump Moq from 4.18.2 to 4.18.3

Bumps [Moq](https://github.com/moq/moq4) from 4.18.2 to 4.18.3.
- [Release notes](https://github.com/moq/moq4/releases)
- [Changelog](https://github.com/moq/moq4/blob/main/CHANGELOG.md)
- [Commits](https://github.com/moq/moq4/compare/v4.18.2...v4.18.3)

---
updated-dependencies:
- dependency-name: Moq
... (continued)

Run Details

10732 of 20257 branches covered (0.0%)

Branch coverage included in aggregate %.

48141 of 48141 new or added lines in 1086 files covered. (100.0%)

30685 of 52388 relevant lines covered (58.57%)

7387.88 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

51.14

/Rdmp.Core/MapsDirectlyToDatabaseTable/Versioning/MasterDatabaseScriptExecutor.cs

// Copyright (c) The University of Dundee 2018-2019
// This file is part of the Research Data Management Platform (RDMP).
// RDMP is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
// RDMP is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
// You should have received a copy of the GNU General Public License along with RDMP. If not, see <https://www.gnu.org/licenses/>.

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using FAnsi;
using FAnsi.Discovery;
using Rdmp.Core.ReusableLibraryCode;
using Rdmp.Core.ReusableLibraryCode.Checks;
using TypeGuesser;

namespace Rdmp.Core.MapsDirectlyToDatabaseTable.Versioning;

/// <summary>
/// Creates new databases with a fixed (versioned) schema (determined by an <see cref="IPatcher"/>) into a database server (e.g. localhost\sqlexpress).
/// </summary>
public class MasterDatabaseScriptExecutor
{
    public DiscoveredDatabase Database { get; }

    /// <summary>
    /// Returns the name of the schema we expect to create/store the Version / ScriptsRun tables in.  Returns null if
    /// <see cref="Database"/> is not a DBMS that supports schemas (e.g. MySql).
    /// </summary>
    public string RoundhouseSchemaName => GetRoundhouseSchemaName(Database);

    public static string GetRoundhouseSchemaName(DiscoveredDatabase database) =>
        database.Server.DatabaseType == DatabaseType.MicrosoftSQLServer ? "RoundhousE" : null;

    public const string RoundhouseVersionTable = "Version";
    public const string RoundhouseScriptsRunTable = "ScriptsRun";

    private const string InitialDatabaseScriptName = @"Initial Database Setup";

    public MasterDatabaseScriptExecutor(DiscoveredDatabase database)
    {
        Database = database;
    }

    public bool BinaryCollation { get; set; }

    public bool CreateDatabase(Patch initialCreationPatch, ICheckNotifier notifier)
    {
        try
        {
            if (Database.Exists()) //make sure database does not already exist
            {
                if (Database.DiscoverTables(false).Any(t => t.GetRuntimeName().Equals(RoundhouseScriptsRunTable)))
                    throw new Exception(
                        $"The database '{Database}' is already set up as a platform database for another schema (it has the '{RoundhouseScriptsRunTable}' table)");

                // it doesn't look like this database already contains RDMP curated schemas (hopefully it is blank but if not - meh).
                // ask the user if they want to create it in this db even though it already exists and might not be empty
                var createAnyway = notifier.OnCheckPerformed(new CheckEventArgs(
                    $"Database {Database.GetRuntimeName()} already exists", CheckResult.Warning, null,
                    "Attempt to create database inside existing database (will cause problems if the database is not empty)?"));

                if (!createAnyway)
                    throw new Exception("User chose not continue");
            }
            else
            {
                if (Database.Server.DatabaseType == DatabaseType.MicrosoftSQLServer && BinaryCollation)
                {
                    var master = Database.Server.ExpectDatabase("master");
                    using var con = master.Server.GetConnection();
                    con.Open();
                    using var cmd = Database.Server.GetCommand(
                        $"CREATE DATABASE {Database} COLLATE Latin1_General_BIN2", con);
                    cmd.ExecuteNonQuery();
                }
                else
                {
                    Database.Create();
                }

                if (!Database.Exists())
                    throw new Exception(
                        "Create database failed without Exception! (It did not Exist after creation)");

                notifier.OnCheckPerformed(new CheckEventArgs($"Database {Database} created", CheckResult.Success,
                    null));
            }

            if (Database.Server.DatabaseType == DatabaseType.MicrosoftSQLServer)
                Database.CreateSchema(RoundhouseSchemaName);

            Database.CreateTable("ScriptsRun", new[]
            {
                new DatabaseColumnRequest("id", new DatabaseTypeRequest(typeof(int)))
                    { IsAutoIncrement = true, IsPrimaryKey = true },
                new DatabaseColumnRequest("version_id", new DatabaseTypeRequest(typeof(int))),
                new DatabaseColumnRequest("script_name", new DatabaseTypeRequest(typeof(string), 255)),
                new DatabaseColumnRequest("text_of_script", new DatabaseTypeRequest(typeof(string), int.MaxValue)),
                new DatabaseColumnRequest("text_hash", new DatabaseTypeRequest(typeof(string), 512) { Unicode = true }),
                new DatabaseColumnRequest("one_time_script", new DatabaseTypeRequest(typeof(bool))),
                new DatabaseColumnRequest("entry_date", new DatabaseTypeRequest(typeof(DateTime))),
                new DatabaseColumnRequest("modified_date", new DatabaseTypeRequest(typeof(DateTime))),
                new DatabaseColumnRequest("entered_by", new DatabaseTypeRequest(typeof(string), 50))
            }, RoundhouseSchemaName);


            Database.CreateTable("Version", new[]
            {
                new DatabaseColumnRequest("id", new DatabaseTypeRequest(typeof(int)))
                    { IsAutoIncrement = true, IsPrimaryKey = true },
                new DatabaseColumnRequest("repository_path",
                    new DatabaseTypeRequest(typeof(string), 255) { Unicode = true }),
                new DatabaseColumnRequest("version", new DatabaseTypeRequest(typeof(string), 50) { Unicode = true }),
                new DatabaseColumnRequest("entry_date", new DatabaseTypeRequest(typeof(DateTime))),
                new DatabaseColumnRequest("modified_date", new DatabaseTypeRequest(typeof(DateTime))),
                new DatabaseColumnRequest("entered_by", new DatabaseTypeRequest(typeof(string), 50))
            }, RoundhouseSchemaName);


            RunSQL(new KeyValuePair<string, Patch>(InitialDatabaseScriptName, initialCreationPatch));

            notifier.OnCheckPerformed(new CheckEventArgs("Tables created", CheckResult.Success, null));

            notifier.OnCheckPerformed(new CheckEventArgs("Setup Completed successfully", CheckResult.Success, null));

            return true;
        }
        catch (Exception e)
        {
            notifier.OnCheckPerformed(new CheckEventArgs("Create failed", CheckResult.Fail, e));
            return false;
        }
    }

    private void RunSQL(KeyValuePair<string, Patch> kvp)
    {
        using (var con = Database.Server.GetConnection())
        {
            con.Open();
            UsefulStuff.ExecuteBatchNonQuery(kvp.Value.GetScriptBody(), con, null,
                DiscoveredServerHelper.CreateDatabaseTimeoutInSeconds);
        }

        var now = DateTime.Now;

        Database.ExpectTable(RoundhouseScriptsRunTable, RoundhouseSchemaName)
            .Insert(new Dictionary<string, object>
            {
                { "script_name", kvp.Key },
                { "text_of_script", kvp.Value.EntireScript },
                { "text_hash", CalculateHash(kvp.Value.EntireScript) },

                { "entry_date", now },
                { "modified_date", now },
                { "entered_by", Environment.UserName }
            });

        SetVersion(kvp.Key, kvp.Value.DatabaseVersionNumber.ToString());
    }

    private string CalculateHash(string input)
    {
        // step 1, calculate SHA512 hash from input


        var inputBytes = Encoding.ASCII.GetBytes(input);

        var hash = SHA512.HashData(inputBytes);


        // step 2, convert byte array to hex string

        var sb = new StringBuilder();
        sb.Append(hash.Select(x => x.ToString("X2")));

        return sb.ToString();

    }

    private void SetVersion(string name, string version)
    {
        var versionTable = Database.ExpectTable(RoundhouseVersionTable, RoundhouseSchemaName);
        versionTable.Truncate();

        //repository_path        version        entry_date        modified_date        entered_by
        //Patching        2.6.0.1        2018-02-05 08:26:54.000        2018-02-05 08:26:54.000        DUNDEE\TZNind

        var now = DateTime.Now;

        versionTable.Insert(new Dictionary<string, object>
        {
            { "repository_path", name },
            { "version", version },

            { "entry_date", now },
            { "modified_date", now },
            { "entered_by", Environment.UserName }
        });
    }

    public bool PatchDatabase(SortedDictionary<string, Patch> patches, ICheckNotifier notifier,
        Func<Patch, bool> patchPreviewShouldIRunIt, bool backupDatabase = true)
    {
        if (!patches.Any())
        {
            notifier.OnCheckPerformed(new CheckEventArgs("There are no patches to apply so skipping patching",
                CheckResult.Success, null));
            return true;
        }

        var maxPatchVersion = patches.Values.Max(pat => pat.DatabaseVersionNumber);

        if (backupDatabase && SupportsBackup(Database))
            try
            {
                notifier.OnCheckPerformed(new CheckEventArgs("About to backup database", CheckResult.Success, null));

                Database.CreateBackup($"Full backup of {Database}");

                notifier.OnCheckPerformed(new CheckEventArgs("Database backed up", CheckResult.Success, null));
            }
            catch (Exception e)
            {
                notifier.OnCheckPerformed(new CheckEventArgs(
                    "Patching failed during setup and preparation (includes failures due to backup creation failures)",
                    CheckResult.Fail, e));
                return false;
            }


        try
        {
            foreach (var patch in patches)
            {
                var shouldRun = patchPreviewShouldIRunIt(patch.Value);

                if (shouldRun)
                {
                    try
                    {
                        RunSQL(patch);
                    }
                    catch (Exception e)
                    {
                        throw new Exception($"Failed to apply patch '{patch.Key}'", e);
                    }


                    notifier.OnCheckPerformed(new CheckEventArgs($"Executed patch {patch.Value}", CheckResult.Success,
                        null));
                }
                else
                {
                    throw new Exception($"User decided not to execute patch {patch.Key} - aborting ");
                }
            }

            SetVersion("Patching", maxPatchVersion.ToString());
            notifier.OnCheckPerformed(new CheckEventArgs($"Updated database version to {maxPatchVersion}",
                CheckResult.Success));

            return true;
        }
        catch (Exception e)
        {
            notifier.OnCheckPerformed(new CheckEventArgs("Error occurred during patching", CheckResult.Fail, e));
            return false;
        }
    }

    private static bool SupportsBackup(DiscoveredDatabase database)
    {
        // Only MS SQL Server has a backup implementation in FAnsi currently
        return database.Server.DatabaseType switch
        {
            // BACKUP doesn't work on Azure SQL Server either:
            DatabaseType.MicrosoftSQLServer => !database.Server.Builder.ContainsKey("Authentication"),
            _ => false
        };
    }

    /// <summary>
    /// Patches the <see cref="Database"/> with ONLY the patches that are outstanding from <paramref name="patcher"/>
    /// </summary>
    /// <param name="patcher"></param>
    /// <param name="notifier"></param>
    /// <param name="patchPreviewShouldIRunIt"></param>
    /// <param name="backupDatabaseFunc"></param>
    /// <returns></returns>
    public bool PatchDatabase(IPatcher patcher, ICheckNotifier notifier, Func<Patch, bool> patchPreviewShouldIRunIt,
        Func<bool> backupDatabaseFunc)
    {
        var status = Patch.IsPatchingRequired(Database, patcher, out var databaseVersion, out var patchesInDatabase,
            out var allPatchesInAssembly);

        if (status != Patch.PatchingState.Required)
            return false;

        var stop = false;
        var hostAssemblyVersion = patcher.GetDbAssembly().GetName().Version;

        //start with the assumption that we will apply all patches
        var toApply = new SortedDictionary<string, Patch>();

        foreach (var potentialInstallable in allPatchesInAssembly.Values.Except(patchesInDatabase))
            toApply.Add(potentialInstallable.locationInAssembly, potentialInstallable);

        try
        {
            //make sure the existing patches in the live database are not freaky phantom patches
            foreach (var patch in patchesInDatabase)
                //if patch is not in database assembly
                if (!allPatchesInAssembly.Any(a => a.Value.Equals(patch)))
                {
                    notifier.OnCheckPerformed(new CheckEventArgs(
                        $"The database contains an unexplained patch called {patch.locationInAssembly} (it is not in {patcher.GetDbAssembly().FullName} ) so how did it get there?",
                        CheckResult.Warning,
                        null));
                }
                else if (!allPatchesInAssembly[patch.locationInAssembly].GetScriptBody().Equals(patch.GetScriptBody()))
                {
                    notifier.OnCheckPerformed(new CheckEventArgs(
                        $"The contents of patch {patch.locationInAssembly} are different between live database and the database patching assembly",
                        CheckResult.Warning,
                        null));

                    //do not apply this patch
                    toApply.Remove(patch.locationInAssembly);
                }
                else
                {
                    //we found it and it was intact
                    notifier.OnCheckPerformed(new CheckEventArgs(
                        $"Patch {patch.locationInAssembly} was previously installed successfully so no need to touch it",
                        CheckResult.Success, null));

                    //do not apply this patch
                    toApply.Remove(patch.locationInAssembly);
                }
        }
        catch (Exception exception)
        {
            notifier.OnCheckPerformed(new CheckEventArgs("Patch evaluation failed", CheckResult.Fail, exception));
            stop = true;
        }

        //if any of the patches we are trying to apply are earlier than the latest in the database
        var missedOpportunities = toApply.Values.Where(p =>
            p.DatabaseVersionNumber < patchesInDatabase.Max(p2 => p2.DatabaseVersionNumber));
        foreach (var missedOpportunity in missedOpportunities)
        {
            stop = true;
            notifier.OnCheckPerformed(new CheckEventArgs(
                $"Patch {missedOpportunity.locationInAssembly} cannot be applied because its version number is {missedOpportunity.DatabaseVersionNumber} but the current database is at version {databaseVersion}{Environment.NewLine} Contents of patch was:{Environment.NewLine}{missedOpportunity.EntireScript}"
                , CheckResult.Fail, null));
        }

        //if the patches to be applied would bring the version number above that of the host Library
        foreach (var futurePatch in toApply.Values.Where(patch => patch.DatabaseVersionNumber > hostAssemblyVersion))
        {
            notifier.OnCheckPerformed(new CheckEventArgs(
                $"Cannot apply patch {futurePatch.locationInAssembly} because its database version number is {futurePatch.DatabaseVersionNumber} which is higher than the currently loaded host assembly ({patcher.GetDbAssembly().FullName}). ",
                CheckResult.Fail, null));
            stop = true;
        }

        if (stop)
        {
            notifier.OnCheckPerformed(new CheckEventArgs(
                "Abandoning patching process (no patches have been applied) because of one or more previous errors",
                CheckResult.Fail, null));
            return false;
        }

        //todo: Only MS SQL has a backup implementation in FAnsi currently
        var backupDatabase = Database.Server.DatabaseType == DatabaseType.MicrosoftSQLServer &&
                             backupDatabaseFunc();

        return PatchDatabase(toApply, notifier, patchPreviewShouldIRunIt, backupDatabase);
    }


    public Patch[] GetPatchesRun()
    {
        var toReturn = new List<Patch>();

        var scriptsRun = Database.ExpectTable(RoundhouseScriptsRunTable, RoundhouseSchemaName);

        var dt = scriptsRun.GetDataTable();

        foreach (DataRow r in dt.Rows)
        {
            var text_of_script = r["text_of_script"] as string;
            var script_name = r["script_name"] as string;

            if (string.IsNullOrWhiteSpace(script_name) ||
                string.IsNullOrWhiteSpace(text_of_script) ||
                script_name.Equals(InitialDatabaseScriptName))
                continue;

            var p = new Patch(script_name, text_of_script);
            toReturn.Add(p);
        }

        return toReturn.ToArray();
    }

    /// <summary>
    /// Creates a new platform database and patches it
    /// </summary>
    /// <param name="patcher">Determines the SQL schema created</param>
    /// <param name="notifier">audit object, can be a ThrowImmediatelyCheckNotifier.Quiet if you aren't in a position to pass one</param>
    public void CreateAndPatchDatabase(IPatcher patcher, ICheckNotifier notifier)
    {
        var initialPatch = patcher.GetInitialCreateScriptContents(Database);
        notifier.OnCheckPerformed(
            new CheckEventArgs($"About to run:{Environment.NewLine}{initialPatch.EntireScript}", CheckResult.Success));

        CreateDatabase(initialPatch, notifier);

        //get everything in the /up/ folder that is .sql
        var patches = patcher.GetAllPatchesInAssembly(Database);
        PatchDatabase(patches, notifier, p => true); //apply all patches without question
    }
}

1	// Copyright (c) The University of Dundee 2018-2019
2	// This file is part of the Research Data Management Platform (RDMP).
3	// RDMP is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
4	// RDMP is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
5	// You should have received a copy of the GNU General Public License along with RDMP. If not, see <https://www.gnu.org/licenses/>.
6
7	using System;
8	using System.Collections.Generic;
9	using System.Data;
10	using System.Linq;
11	using System.Security.Cryptography;
12	using System.Text;
13	using FAnsi;
14	using FAnsi.Discovery;
15	using Rdmp.Core.ReusableLibraryCode;
16	using Rdmp.Core.ReusableLibraryCode.Checks;
17	using TypeGuesser;
18
19	namespace Rdmp.Core.MapsDirectlyToDatabaseTable.Versioning;
20
21	/// <summary>
22	/// Creates new databases with a fixed (versioned) schema (determined by an <see cref="IPatcher"/>) into a database server (e.g. localhost\sqlexpress).
23	/// </summary>
24	public class MasterDatabaseScriptExecutor
25	{
26	public DiscoveredDatabase Database { get; }	1,664✔
27
28	/// <summary>
29	/// Returns the name of the schema we expect to create/store the Version / ScriptsRun tables in. Returns null if
30	/// <see cref="Database"/> is not a DBMS that supports schemas (e.g. MySql).
31	/// </summary>
32	public string RoundhouseSchemaName => GetRoundhouseSchemaName(Database);	488✔
33
34	public static string GetRoundhouseSchemaName(DiscoveredDatabase database) =>
35	database.Server.DatabaseType == DatabaseType.MicrosoftSQLServer ? "RoundhousE" : null;	544✔
36
37	public const string RoundhouseVersionTable = "Version";
38	public const string RoundhouseScriptsRunTable = "ScriptsRun";
39
40	private const string InitialDatabaseScriptName = @"Initial Database Setup";
41
42	public MasterDatabaseScriptExecutor(DiscoveredDatabase database)	146✔
43	{
44	Database = database;	146✔
45	}	146✔
46
47	public bool BinaryCollation { get; set; }	25✔
48
49	public bool CreateDatabase(Patch initialCreationPatch, ICheckNotifier notifier)
50	{
51	try
52	{
53	if (Database.Exists()) //make sure database does not already exist	92✔
54	{
55	if (Database.DiscoverTables(false).Any(t => t.GetRuntimeName().Equals(RoundhouseScriptsRunTable)))	83✔
56	throw new Exception(	2✔
57	$"The database '{Database}' is already set up as a platform database for another schema (it has the '{RoundhouseScriptsRunTable}' table)");	2✔
58
59	// it doesn't look like this database already contains RDMP curated schemas (hopefully it is blank but if not - meh).
60	// ask the user if they want to create it in this db even though it already exists and might not be empty
61	var createAnyway = notifier.OnCheckPerformed(new CheckEventArgs(	65✔
62	$"Database {Database.GetRuntimeName()} already exists", CheckResult.Warning, null,	65✔
63	"Attempt to create database inside existing database (will cause problems if the database is not empty)?"));	65✔
64
65	if (!createAnyway)	65!
66	throw new Exception("User chose not continue");	×
67	}
68	else
69	{
70	if (Database.Server.DatabaseType == DatabaseType.MicrosoftSQLServer && BinaryCollation)	25!
71	{
72	var master = Database.Server.ExpectDatabase("master");	×
73	using var con = master.Server.GetConnection();	×
74	con.Open();	×
75	using var cmd = Database.Server.GetCommand(	×
76	$"CREATE DATABASE {Database} COLLATE Latin1_General_BIN2", con);	×
77	cmd.ExecuteNonQuery();	×
78	}
79	else
80	{
81	Database.Create();	25✔
82	}
83
84	if (!Database.Exists())	25!
85	throw new Exception(	×
86	"Create database failed without Exception! (It did not Exist after creation)");	×
87
88	notifier.OnCheckPerformed(new CheckEventArgs($"Database {Database} created", CheckResult.Success,	25✔
89	null));	25✔
90	}
91
92	if (Database.Server.DatabaseType == DatabaseType.MicrosoftSQLServer)	90✔
93	Database.CreateSchema(RoundhouseSchemaName);	72✔
94
95	Database.CreateTable("ScriptsRun", new[]	90✔
96	{	90✔
97	new DatabaseColumnRequest("id", new DatabaseTypeRequest(typeof(int)))	90✔
98	{ IsAutoIncrement = true, IsPrimaryKey = true },	90✔
99	new DatabaseColumnRequest("version_id", new DatabaseTypeRequest(typeof(int))),	90✔
100	new DatabaseColumnRequest("script_name", new DatabaseTypeRequest(typeof(string), 255)),	90✔
101	new DatabaseColumnRequest("text_of_script", new DatabaseTypeRequest(typeof(string), int.MaxValue)),	90✔
102	new DatabaseColumnRequest("text_hash", new DatabaseTypeRequest(typeof(string), 512) { Unicode = true }),	90✔
103	new DatabaseColumnRequest("one_time_script", new DatabaseTypeRequest(typeof(bool))),	90✔
104	new DatabaseColumnRequest("entry_date", new DatabaseTypeRequest(typeof(DateTime))),	90✔
105	new DatabaseColumnRequest("modified_date", new DatabaseTypeRequest(typeof(DateTime))),	90✔
106	new DatabaseColumnRequest("entered_by", new DatabaseTypeRequest(typeof(string), 50))	90✔
107	}, RoundhouseSchemaName);	90✔
108
109
110	Database.CreateTable("Version", new[]	90✔
111	{	90✔
112	new DatabaseColumnRequest("id", new DatabaseTypeRequest(typeof(int)))	90✔
113	{ IsAutoIncrement = true, IsPrimaryKey = true },	90✔
114	new DatabaseColumnRequest("repository_path",	90✔
115	new DatabaseTypeRequest(typeof(string), 255) { Unicode = true }),	90✔
116	new DatabaseColumnRequest("version", new DatabaseTypeRequest(typeof(string), 50) { Unicode = true }),	90✔
117	new DatabaseColumnRequest("entry_date", new DatabaseTypeRequest(typeof(DateTime))),	90✔
118	new DatabaseColumnRequest("modified_date", new DatabaseTypeRequest(typeof(DateTime))),	90✔
119	new DatabaseColumnRequest("entered_by", new DatabaseTypeRequest(typeof(string), 50))	90✔
120	}, RoundhouseSchemaName);	90✔
121
122
123	RunSQL(new KeyValuePair<string, Patch>(InitialDatabaseScriptName, initialCreationPatch));	90✔
124
125	notifier.OnCheckPerformed(new CheckEventArgs("Tables created", CheckResult.Success, null));	90✔
126
127	notifier.OnCheckPerformed(new CheckEventArgs("Setup Completed successfully", CheckResult.Success, null));	90✔
128
129	return true;	90✔
130	}
131	catch (Exception e)	2✔
132	{
133	notifier.OnCheckPerformed(new CheckEventArgs("Create failed", CheckResult.Fail, e));	2✔
134	return false;	×
135	}
136	}	90✔
137
138	private void RunSQL(KeyValuePair<string, Patch> kvp)
139	{
140	using (var con = Database.Server.GetConnection())	90✔
141	{
142	con.Open();	90✔
143	UsefulStuff.ExecuteBatchNonQuery(kvp.Value.GetScriptBody(), con, null,	90✔
144	DiscoveredServerHelper.CreateDatabaseTimeoutInSeconds);	90✔
145	}	90✔
146
147	var now = DateTime.Now;	90✔
148
149	Database.ExpectTable(RoundhouseScriptsRunTable, RoundhouseSchemaName)	90✔
150	.Insert(new Dictionary<string, object>	90✔
151	{	90✔
152	{ "script_name", kvp.Key },	90✔
153	{ "text_of_script", kvp.Value.EntireScript },	90✔
154	{ "text_hash", CalculateHash(kvp.Value.EntireScript) },	90✔
155		90✔
156	{ "entry_date", now },	90✔
157	{ "modified_date", now },	90✔
158	{ "entered_by", Environment.UserName }	90✔
159	});	90✔
160
161	SetVersion(kvp.Key, kvp.Value.DatabaseVersionNumber.ToString());	90✔
162	}	90✔
163
164	private string CalculateHash(string input)
165	{
166	// step 1, calculate SHA512 hash from input
167
168
169	var inputBytes = Encoding.ASCII.GetBytes(input);	90✔
170
171	var hash = SHA512.HashData(inputBytes);	90✔
172
173
174	// step 2, convert byte array to hex string
175
176	var sb = new StringBuilder();	90✔
177	sb.Append(hash.Select(x => x.ToString("X2")));	90✔
178
179	return sb.ToString();	90✔
180
181	}
182
183	private void SetVersion(string name, string version)
184	{
185	var versionTable = Database.ExpectTable(RoundhouseVersionTable, RoundhouseSchemaName);	90✔
186	versionTable.Truncate();	90✔
187
188	//repository_path version entry_date modified_date entered_by
189	//Patching 2.6.0.1 2018-02-05 08:26:54.000 2018-02-05 08:26:54.000 DUNDEE\TZNind
190
191	var now = DateTime.Now;	90✔
192
193	versionTable.Insert(new Dictionary<string, object>	90✔
194	{	90✔
195	{ "repository_path", name },	90✔
196	{ "version", version },	90✔
197		90✔
198	{ "entry_date", now },	90✔
199	{ "modified_date", now },	90✔
200	{ "entered_by", Environment.UserName }	90✔
201	});	90✔
202	}	90✔
203
204	public bool PatchDatabase(SortedDictionary<string, Patch> patches, ICheckNotifier notifier,
205	Func<Patch, bool> patchPreviewShouldIRunIt, bool backupDatabase = true)
206	{
207	if (!patches.Any())	90!
208	{
209	notifier.OnCheckPerformed(new CheckEventArgs("There are no patches to apply so skipping patching",	90✔
210	CheckResult.Success, null));	90✔
211	return true;	90✔
212	}
213
214	var maxPatchVersion = patches.Values.Max(pat => pat.DatabaseVersionNumber);	×
215
216	if (backupDatabase && SupportsBackup(Database))	×
217	try
218	{
219	notifier.OnCheckPerformed(new CheckEventArgs("About to backup database", CheckResult.Success, null));	×
220
221	Database.CreateBackup($"Full backup of {Database}");	×
222
223	notifier.OnCheckPerformed(new CheckEventArgs("Database backed up", CheckResult.Success, null));	×
224	}	×
225	catch (Exception e)	×
226	{
227	notifier.OnCheckPerformed(new CheckEventArgs(	×
228	"Patching failed during setup and preparation (includes failures due to backup creation failures)",	×
229	CheckResult.Fail, e));	×
230	return false;	×
231	}
232
233
234	try
235	{
236	foreach (var patch in patches)	×
237	{
238	var shouldRun = patchPreviewShouldIRunIt(patch.Value);	×
239
240	if (shouldRun)	×
241	{
242	try
243	{
244	RunSQL(patch);	×
245	}	×
246	catch (Exception e)	×
247	{
248	throw new Exception($"Failed to apply patch '{patch.Key}'", e);	×
249	}
250
251
252	notifier.OnCheckPerformed(new CheckEventArgs($"Executed patch {patch.Value}", CheckResult.Success,	×
253	null));	×
254	}
255	else
256	{
257	throw new Exception($"User decided not to execute patch {patch.Key} - aborting ");	×
258	}
259	}
260
261	SetVersion("Patching", maxPatchVersion.ToString());	×
262	notifier.OnCheckPerformed(new CheckEventArgs($"Updated database version to {maxPatchVersion}",	×
263	CheckResult.Success));	×
264
265	return true;	×
266	}
267	catch (Exception e)	×
268	{
269	notifier.OnCheckPerformed(new CheckEventArgs("Error occurred during patching", CheckResult.Fail, e));	×
270	return false;	×
271	}
272	}	×
273
274	private static bool SupportsBackup(DiscoveredDatabase database)
275	{
276	// Only MS SQL Server has a backup implementation in FAnsi currently
277	return database.Server.DatabaseType switch	×
278	{	×
279	// BACKUP doesn't work on Azure SQL Server either:	×
280	DatabaseType.MicrosoftSQLServer => !database.Server.Builder.ContainsKey("Authentication"),	×
281	_ => false	×
282	};	×
283	}
284
285	/// <summary>
286	/// Patches the <see cref="Database"/> with ONLY the patches that are outstanding from <paramref name="patcher"/>
287	/// </summary>
288	/// <param name="patcher"></param>
289	/// <param name="notifier"></param>
290	/// <param name="patchPreviewShouldIRunIt"></param>
291	/// <param name="backupDatabaseFunc"></param>
292	/// <returns></returns>
293	public bool PatchDatabase(IPatcher patcher, ICheckNotifier notifier, Func<Patch, bool> patchPreviewShouldIRunIt,
294	Func<bool> backupDatabaseFunc)
295	{
296	var status = Patch.IsPatchingRequired(Database, patcher, out var databaseVersion, out var patchesInDatabase,	×
297	out var allPatchesInAssembly);	×
298
299	if (status != Patch.PatchingState.Required)	×
300	return false;	×
301
302	var stop = false;	×
303	var hostAssemblyVersion = patcher.GetDbAssembly().GetName().Version;	×
304
305	//start with the assumption that we will apply all patches
306	var toApply = new SortedDictionary<string, Patch>();	×
307
308	foreach (var potentialInstallable in allPatchesInAssembly.Values.Except(patchesInDatabase))	×
309	toApply.Add(potentialInstallable.locationInAssembly, potentialInstallable);	×
310
311	try
312	{
313	//make sure the existing patches in the live database are not freaky phantom patches
314	foreach (var patch in patchesInDatabase)	×
315	//if patch is not in database assembly
316	if (!allPatchesInAssembly.Any(a => a.Value.Equals(patch)))	×
317	{
318	notifier.OnCheckPerformed(new CheckEventArgs(	×
319	$"The database contains an unexplained patch called {patch.locationInAssembly} (it is not in {patcher.GetDbAssembly().FullName} ) so how did it get there?",	×
320	CheckResult.Warning,	×
321	null));	×
322	}
323	else if (!allPatchesInAssembly[patch.locationInAssembly].GetScriptBody().Equals(patch.GetScriptBody()))	×
324	{
325	notifier.OnCheckPerformed(new CheckEventArgs(	×
326	$"The contents of patch {patch.locationInAssembly} are different between live database and the database patching assembly",	×
327	CheckResult.Warning,	×
328	null));	×
329
330	//do not apply this patch
331	toApply.Remove(patch.locationInAssembly);	×
332	}
333	else
334	{
335	//we found it and it was intact
336	notifier.OnCheckPerformed(new CheckEventArgs(	×
337	$"Patch {patch.locationInAssembly} was previously installed successfully so no need to touch it",	×
338	CheckResult.Success, null));	×
339
340	//do not apply this patch
341	toApply.Remove(patch.locationInAssembly);	×
342	}
343	}	×
344	catch (Exception exception)	×
345	{
346	notifier.OnCheckPerformed(new CheckEventArgs("Patch evaluation failed", CheckResult.Fail, exception));	×
347	stop = true;	×
348	}	×
349
350	//if any of the patches we are trying to apply are earlier than the latest in the database
351	var missedOpportunities = toApply.Values.Where(p =>	×
352	p.DatabaseVersionNumber < patchesInDatabase.Max(p2 => p2.DatabaseVersionNumber));	×
353	foreach (var missedOpportunity in missedOpportunities)	×
354	{
355	stop = true;	×
356	notifier.OnCheckPerformed(new CheckEventArgs(	×
357	$"Patch {missedOpportunity.locationInAssembly} cannot be applied because its version number is {missedOpportunity.DatabaseVersionNumber} but the current database is at version {databaseVersion}{Environment.NewLine} Contents of patch was:{Environment.NewLine}{missedOpportunity.EntireScript}"	×
358	, CheckResult.Fail, null));	×
359	}
360
361	//if the patches to be applied would bring the version number above that of the host Library
362	foreach (var futurePatch in toApply.Values.Where(patch => patch.DatabaseVersionNumber > hostAssemblyVersion))	×
363	{
364	notifier.OnCheckPerformed(new CheckEventArgs(	×
365	$"Cannot apply patch {futurePatch.locationInAssembly} because its database version number is {futurePatch.DatabaseVersionNumber} which is higher than the currently loaded host assembly ({patcher.GetDbAssembly().FullName}). ",	×
366	CheckResult.Fail, null));	×
367	stop = true;	×
368	}
369
370	if (stop)	×
371	{
372	notifier.OnCheckPerformed(new CheckEventArgs(	×
373	"Abandoning patching process (no patches have been applied) because of one or more previous errors",	×
374	CheckResult.Fail, null));	×
375	return false;	×
376	}
377
378	//todo: Only MS SQL has a backup implementation in FAnsi currently
379	var backupDatabase = Database.Server.DatabaseType == DatabaseType.MicrosoftSQLServer &&	×
380	backupDatabaseFunc();	×
381
382	return PatchDatabase(toApply, notifier, patchPreviewShouldIRunIt, backupDatabase);	×
383	}
384
385
386	public Patch[] GetPatchesRun()
387	{
388	var toReturn = new List<Patch>();	56✔
389
390	var scriptsRun = Database.ExpectTable(RoundhouseScriptsRunTable, RoundhouseSchemaName);	56✔
391
392	var dt = scriptsRun.GetDataTable();	56✔
393
394	foreach (DataRow r in dt.Rows)	3,136✔
395	{
396	var text_of_script = r["text_of_script"] as string;	1,512✔
397	var script_name = r["script_name"] as string;	1,512✔
398
399	if (string.IsNullOrWhiteSpace(script_name) \|\|	1,512✔
400	string.IsNullOrWhiteSpace(text_of_script) \|\|	1,512✔
401	script_name.Equals(InitialDatabaseScriptName))	1,512✔
402	continue;
403
404	var p = new Patch(script_name, text_of_script);	1,456✔
405	toReturn.Add(p);	1,456✔
406	}
407
408	return toReturn.ToArray();	56✔
409	}
410
411	/// <summary>
412	/// Creates a new platform database and patches it
413	/// </summary>
414	/// <param name="patcher">Determines the SQL schema created</param>
415	/// <param name="notifier">audit object, can be a ThrowImmediatelyCheckNotifier.Quiet if you aren't in a position to pass one</param>
416	public void CreateAndPatchDatabase(IPatcher patcher, ICheckNotifier notifier)
417	{
418	var initialPatch = patcher.GetInitialCreateScriptContents(Database);	92✔
419	notifier.OnCheckPerformed(	92✔
420	new CheckEventArgs($"About to run:{Environment.NewLine}{initialPatch.EntireScript}", CheckResult.Success));	92✔
421
422	CreateDatabase(initialPatch, notifier);	92✔
423
424	//get everything in the /up/ folder that is .sql
425	var patches = patcher.GetAllPatchesInAssembly(Database);	90✔
426	PatchDatabase(patches, notifier, p => true); //apply all patches without question	90✔
427	}	90✔
428	}

HicServices / RDMP / 6245535001

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous