9859858140

Committed 09 Jul 2024 03:24PM UTC coverage: 56.679% (-0.2%) from 56.916%

Build # 9859858140

Build Type

push

github

Committed by

JFriel

Commit Message

update

Run Details

10912 of 20750 branches covered (52.59%)

Branch coverage included in aggregate %.

30965 of 53135 relevant lines covered (58.28%)

7908.05 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.UtcNow;
        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.UtcNow;

        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,700✔
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);	498✔
33
34	public static string GetRoundhouseSchemaName(DiscoveredDatabase database) =>
35	database.Server.DatabaseType == DatabaseType.MicrosoftSQLServer ? "RoundhousE" : null;	554✔
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)	148✔
43	{
44	Database = database;	148✔
45	}	148✔
46
47	public bool BinaryCollation { get; set; }	26✔
48
49	public bool CreateDatabase(Patch initialCreationPatch, ICheckNotifier notifier)
50	{
51	try
52	{
53	if (Database.Exists()) //make sure database does not already exist	94✔
54	{
55	if (Database.DiscoverTables(false).Any(t => t.GetRuntimeName().Equals(RoundhouseScriptsRunTable)))	84✔
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(	66✔
62	$"Database {Database.GetRuntimeName()} already exists", CheckResult.Warning, null,	66✔
63	"Attempt to create database inside existing database (will cause problems if the database is not empty)?"));	66✔
64
65	if (!createAnyway)	66!
66	throw new Exception("User chose not continue");	×
67	}
68	else
69	{
70	if (Database.Server.DatabaseType == DatabaseType.MicrosoftSQLServer && BinaryCollation)	26!
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();	26✔
82	}
83
84	if (!Database.Exists())	26!
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,	26✔
89	null));	26✔
90	}
91
92	if (Database.Server.DatabaseType == DatabaseType.MicrosoftSQLServer)	92✔
93	Database.CreateSchema(RoundhouseSchemaName);	74✔
94
95	Database.CreateTable("ScriptsRun", new[]	92✔
96	{	92✔
97	new DatabaseColumnRequest("id", new DatabaseTypeRequest(typeof(int)))	92✔
98	{ IsAutoIncrement = true, IsPrimaryKey = true },	92✔
99	new DatabaseColumnRequest("version_id", new DatabaseTypeRequest(typeof(int))),	92✔
100	new DatabaseColumnRequest("script_name", new DatabaseTypeRequest(typeof(string), 255)),	92✔
101	new DatabaseColumnRequest("text_of_script", new DatabaseTypeRequest(typeof(string), int.MaxValue)),	92✔
102	new DatabaseColumnRequest("text_hash", new DatabaseTypeRequest(typeof(string), 512) { Unicode = true }),	92✔
103	new DatabaseColumnRequest("one_time_script", new DatabaseTypeRequest(typeof(bool))),	92✔
104	new DatabaseColumnRequest("entry_date", new DatabaseTypeRequest(typeof(DateTime))),	92✔
105	new DatabaseColumnRequest("modified_date", new DatabaseTypeRequest(typeof(DateTime))),	92✔
106	new DatabaseColumnRequest("entered_by", new DatabaseTypeRequest(typeof(string), 50))	92✔
107	}, RoundhouseSchemaName);	92✔
108
109
110	Database.CreateTable("Version", new[]	92✔
111	{	92✔
112	new DatabaseColumnRequest("id", new DatabaseTypeRequest(typeof(int)))	92✔
113	{ IsAutoIncrement = true, IsPrimaryKey = true },	92✔
114	new DatabaseColumnRequest("repository_path",	92✔
115	new DatabaseTypeRequest(typeof(string), 255) { Unicode = true }),	92✔
116	new DatabaseColumnRequest("version", new DatabaseTypeRequest(typeof(string), 50) { Unicode = true }),	92✔
117	new DatabaseColumnRequest("entry_date", new DatabaseTypeRequest(typeof(DateTime))),	92✔
118	new DatabaseColumnRequest("modified_date", new DatabaseTypeRequest(typeof(DateTime))),	92✔
119	new DatabaseColumnRequest("entered_by", new DatabaseTypeRequest(typeof(string), 50))	92✔
120	}, RoundhouseSchemaName);	92✔
121
122
123	RunSQL(new KeyValuePair<string, Patch>(InitialDatabaseScriptName, initialCreationPatch));	92✔
124
125	notifier.OnCheckPerformed(new CheckEventArgs("Tables created", CheckResult.Success, null));	92✔
126
127	notifier.OnCheckPerformed(new CheckEventArgs("Setup Completed successfully", CheckResult.Success, null));	92✔
128
129	return true;	92✔
130	}
131	catch (Exception e)	2✔
132	{
133	notifier.OnCheckPerformed(new CheckEventArgs("Create failed", CheckResult.Fail, e));	2✔
134	return false;	×
135	}
136	}	92✔
137
138	private void RunSQL(KeyValuePair<string, Patch> kvp)
139	{
140	using (var con = Database.Server.GetConnection())	92✔
141	{
142	con.Open();	92✔
143	UsefulStuff.ExecuteBatchNonQuery(kvp.Value.GetScriptBody(), con, null,	92✔
144	DiscoveredServerHelper.CreateDatabaseTimeoutInSeconds);	92✔
145	}	92✔
146
147	var now = DateTime.UtcNow;	92✔
148	Database.ExpectTable(RoundhouseScriptsRunTable, RoundhouseSchemaName)	92✔
149	.Insert(new Dictionary<string, object>	92✔
150	{	92✔
151	{ "script_name", kvp.Key },	92✔
152	{ "text_of_script", kvp.Value.EntireScript },	92✔
153	{ "text_hash", CalculateHash(kvp.Value.EntireScript) },	92✔
154		92✔
155	{ "entry_date", now },	92✔
156	{ "modified_date", now },	92✔
157	{ "entered_by", Environment.UserName }	92✔
158	});	92✔
159
160	SetVersion(kvp.Key, kvp.Value.DatabaseVersionNumber.ToString());	92✔
161	}	92✔
162
163	private string CalculateHash(string input)
164	{
165	// step 1, calculate SHA512 hash from input
166
167
168	var inputBytes = Encoding.ASCII.GetBytes(input);	92✔
169
170	var hash = SHA512.HashData(inputBytes);	92✔
171
172
173	// step 2, convert byte array to hex string
174
175	var sb = new StringBuilder();	92✔
176	sb.Append(hash.Select(x => x.ToString("X2")));	92✔
177
178	return sb.ToString();	92✔
179
180	}
181
182	private void SetVersion(string name, string version)
183	{
184	var versionTable = Database.ExpectTable(RoundhouseVersionTable, RoundhouseSchemaName);	92✔
185	versionTable.Truncate();	92✔
186
187	//repository_path version entry_date modified_date entered_by
188	//Patching 2.6.0.1 2018-02-05 08:26:54.000 2018-02-05 08:26:54.000 DUNDEE\TZNind
189
190	var now = DateTime.UtcNow;	92✔
191
192	versionTable.Insert(new Dictionary<string, object>	92✔
193	{	92✔
194	{ "repository_path", name },	92✔
195	{ "version", version },	92✔
196		92✔
197	{ "entry_date", now },	92✔
198	{ "modified_date", now },	92✔
199	{ "entered_by", Environment.UserName }	92✔
200	});	92✔
201	}	92✔
202
203	public bool PatchDatabase(SortedDictionary<string, Patch> patches, ICheckNotifier notifier,
204	Func<Patch, bool> patchPreviewShouldIRunIt, bool backupDatabase = true)
205	{
206	if (!patches.Any())	92!
207	{
208	notifier.OnCheckPerformed(new CheckEventArgs("There are no patches to apply so skipping patching",	92✔
209	CheckResult.Success, null));	92✔
210	return true;	92✔
211	}
212
213	var maxPatchVersion = patches.Values.Max(pat => pat.DatabaseVersionNumber);	×
214
215	if (backupDatabase && SupportsBackup(Database))	×
216	try
217	{
218	notifier.OnCheckPerformed(new CheckEventArgs("About to backup database", CheckResult.Success, null));	×
219
220	Database.CreateBackup($"Full backup of {Database}");	×
221
222	notifier.OnCheckPerformed(new CheckEventArgs("Database backed up", CheckResult.Success, null));	×
223	}	×
224	catch (Exception e)	×
225	{
226	notifier.OnCheckPerformed(new CheckEventArgs(	×
227	"Patching failed during setup and preparation (includes failures due to backup creation failures)",	×
228	CheckResult.Fail, e));	×
229	return false;	×
230	}
231
232
233	try
234	{
235	foreach (var patch in patches)	×
236	{
237	var shouldRun = patchPreviewShouldIRunIt(patch.Value);	×
238
239	if (shouldRun)	×
240	{
241	try
242	{
243	RunSQL(patch);	×
244	}	×
245	catch (Exception e)	×
246	{
247	throw new Exception($"Failed to apply patch '{patch.Key}'", e);	×
248	}
249
250
251	notifier.OnCheckPerformed(new CheckEventArgs($"Executed patch {patch.Value}", CheckResult.Success,	×
252	null));	×
253	}
254	else
255	{
256	throw new Exception($"User decided not to execute patch {patch.Key} - aborting ");	×
257	}
258	}
259
260	SetVersion("Patching", maxPatchVersion.ToString());	×
261	notifier.OnCheckPerformed(new CheckEventArgs($"Updated database version to {maxPatchVersion}",	×
262	CheckResult.Success));	×
263
264	return true;	×
265	}
266	catch (Exception e)	×
267	{
268	notifier.OnCheckPerformed(new CheckEventArgs("Error occurred during patching", CheckResult.Fail, e));	×
269	return false;	×
270	}
271	}	×
272
273	private static bool SupportsBackup(DiscoveredDatabase database)
274	{
275	// Only MS SQL Server has a backup implementation in FAnsi currently
276	return database.Server.DatabaseType switch	×
277	{	×
278	// BACKUP doesn't work on Azure SQL Server either:	×
279	DatabaseType.MicrosoftSQLServer => !database.Server.Builder.ContainsKey("Authentication"),	×
280	_ => false	×
281	};	×
282	}
283
284	/// <summary>
285	/// Patches the <see cref="Database"/> with ONLY the patches that are outstanding from <paramref name="patcher"/>
286	/// </summary>
287	/// <param name="patcher"></param>
288	/// <param name="notifier"></param>
289	/// <param name="patchPreviewShouldIRunIt"></param>
290	/// <param name="backupDatabaseFunc"></param>
291	/// <returns></returns>
292	public bool PatchDatabase(IPatcher patcher, ICheckNotifier notifier, Func<Patch, bool> patchPreviewShouldIRunIt,
293	Func<bool> backupDatabaseFunc)
294	{
295	var status = Patch.IsPatchingRequired(Database, patcher, out var databaseVersion, out var patchesInDatabase,	×
296	out var allPatchesInAssembly);	×
297
298	if (status != Patch.PatchingState.Required)	×
299	return false;	×
300
301	var stop = false;	×
302	var hostAssemblyVersion = patcher.GetDbAssembly().GetName().Version;	×
303
304	//start with the assumption that we will apply all patches
305	var toApply = new SortedDictionary<string, Patch>();	×
306
307	foreach (var potentialInstallable in allPatchesInAssembly.Values.Except(patchesInDatabase))	×
308	toApply.Add(potentialInstallable.locationInAssembly, potentialInstallable);	×
309
310	try
311	{
312	//make sure the existing patches in the live database are not freaky phantom patches
313	foreach (var patch in patchesInDatabase)	×
314	//if patch is not in database assembly
315	if (!allPatchesInAssembly.Any(a => a.Value.Equals(patch)))	×
316	{
317	notifier.OnCheckPerformed(new CheckEventArgs(	×
318	$"The database contains an unexplained patch called {patch.locationInAssembly} (it is not in {patcher.GetDbAssembly().FullName} ) so how did it get there?",	×
319	CheckResult.Warning,	×
320	null));	×
321	}
322	else if (!allPatchesInAssembly[patch.locationInAssembly].GetScriptBody().Equals(patch.GetScriptBody()))	×
323	{
324	notifier.OnCheckPerformed(new CheckEventArgs(	×
325	$"The contents of patch {patch.locationInAssembly} are different between live database and the database patching assembly",	×
326	CheckResult.Warning,	×
327	null));	×
328
329	//do not apply this patch
330	toApply.Remove(patch.locationInAssembly);	×
331	}
332	else
333	{
334	//we found it and it was intact
335	notifier.OnCheckPerformed(new CheckEventArgs(	×
336	$"Patch {patch.locationInAssembly} was previously installed successfully so no need to touch it",	×
337	CheckResult.Success, null));	×
338
339	//do not apply this patch
340	toApply.Remove(patch.locationInAssembly);	×
341	}
342	}	×
343	catch (Exception exception)	×
344	{
345	notifier.OnCheckPerformed(new CheckEventArgs("Patch evaluation failed", CheckResult.Fail, exception));	×
346	stop = true;	×
347	}	×
348
349	//if any of the patches we are trying to apply are earlier than the latest in the database
350	var missedOpportunities = toApply.Values.Where(p =>	×
351	p.DatabaseVersionNumber < patchesInDatabase.Max(p2 => p2.DatabaseVersionNumber));	×
352	foreach (var missedOpportunity in missedOpportunities)	×
353	{
354	stop = true;	×
355	notifier.OnCheckPerformed(new CheckEventArgs(	×
356	$"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}"	×
357	, CheckResult.Fail, null));	×
358	}
359
360	//if the patches to be applied would bring the version number above that of the host Library
361	foreach (var futurePatch in toApply.Values.Where(patch => patch.DatabaseVersionNumber > hostAssemblyVersion))	×
362	{
363	notifier.OnCheckPerformed(new CheckEventArgs(	×
364	$"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}). ",	×
365	CheckResult.Fail, null));	×
366	stop = true;	×
367	}
368
369	if (stop)	×
370	{
371	notifier.OnCheckPerformed(new CheckEventArgs(	×
372	"Abandoning patching process (no patches have been applied) because of one or more previous errors",	×
373	CheckResult.Fail, null));	×
374	return false;	×
375	}
376
377	//todo: Only MS SQL has a backup implementation in FAnsi currently
378	var backupDatabase = Database.Server.DatabaseType == DatabaseType.MicrosoftSQLServer &&	×
379	backupDatabaseFunc();	×
380
381	return PatchDatabase(toApply, notifier, patchPreviewShouldIRunIt, backupDatabase);	×
382	}
383
384
385	public Patch[] GetPatchesRun()
386	{
387	var toReturn = new List<Patch>();	56✔
388
389	var scriptsRun = Database.ExpectTable(RoundhouseScriptsRunTable, RoundhouseSchemaName);	56✔
390
391	var dt = scriptsRun.GetDataTable();	56✔
392
393	foreach (DataRow r in dt.Rows)	3,360✔
394	{
395	var text_of_script = r["text_of_script"] as string;	1,624✔
396	var script_name = r["script_name"] as string;	1,624✔
397
398	if (string.IsNullOrWhiteSpace(script_name) \|\|	1,624✔
399	string.IsNullOrWhiteSpace(text_of_script) \|\|	1,624✔
400	script_name.Equals(InitialDatabaseScriptName))	1,624✔
401	continue;
402
403	var p = new Patch(script_name, text_of_script);	1,568✔
404	toReturn.Add(p);	1,568✔
405	}
406
407	return toReturn.ToArray();	56✔
408	}
409
410	/// <summary>
411	/// Creates a new platform database and patches it
412	/// </summary>
413	/// <param name="patcher">Determines the SQL schema created</param>
414	/// <param name="notifier">audit object, can be a ThrowImmediatelyCheckNotifier.Quiet if you aren't in a position to pass one</param>
415	public void CreateAndPatchDatabase(IPatcher patcher, ICheckNotifier notifier)
416	{
417	var initialPatch = patcher.GetInitialCreateScriptContents(Database);	94✔
418	notifier.OnCheckPerformed(	94✔
419	new CheckEventArgs($"About to run:{Environment.NewLine}{initialPatch.EntireScript}", CheckResult.Success));	94✔
420
421	CreateDatabase(initialPatch, notifier);	94✔
422
423	//get everything in the /up/ folder that is .sql
424	var patches = patcher.GetAllPatchesInAssembly(Database);	92✔
425	PatchDatabase(patches, notifier, p => true); //apply all patches without question	92✔
426	}	92✔
427	}

HicServices / RDMP / 9859858140

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