public override void ExecuteCommand()
{
Log.Info(
"Deleting package registration and all package versions for '{0}'.",
PackageId);
using (var sqlConnection = new SqlConnection(ConnectionString.ConnectionString))
using (var dbExecutor = new SqlExecutor(sqlConnection))
{
sqlConnection.Open();
var packageRegistration = Util.GetPackageRegistration(
dbExecutor,
PackageId);
var packages = Util.GetPackages(
dbExecutor,
packageRegistration.Key);
foreach (var package in packages)
{
var task = new DeletePackageVersionTask {
ConnectionString = ConnectionString,
BackupStorage = BackupStorage,
StorageAccount = StorageAccount,
PackageId = package.Id,
PackageVersion = package.Version,
Reason = Reason,
WhatIf = WhatIf
};
task.ExecuteCommand();
}
Log.Info(
"Deleting package registration data for '{0}'",
packageRegistration.Id);
if (!WhatIf)
{
dbExecutor.Execute(
"DELETE por FROM PackageOwnerRequests por JOIN PackageRegistrations pr ON pr.[Key] = por.PackageRegistrationKey WHERE pr.[Key] = @packageRegistrationKey",
new { packageRegistrationKey = packageRegistration.Key });
dbExecutor.Execute(
"DELETE pro FROM PackageRegistrationOwners pro JOIN PackageRegistrations pr ON pr.[Key] = pro.PackageRegistrationKey WHERE pr.[Key] = @packageRegistrationKey",
new { packageRegistrationKey = packageRegistration.Key });
dbExecutor.Execute(
"DELETE FROM PackageRegistrations WHERE [Key] = @packageRegistrationKey",
new { packageRegistrationKey = packageRegistration.Key });
}
}
Log.Info(
"Deleted package registration and all package versions for '{0}'.",
PackageId);
}
public override void ExecuteCommand()
{
Log.Info(
"Deleting package registration and all package versions for '{0}'.",
PackageId);
using (var sqlConnection = new SqlConnection(ConnectionString.ConnectionString))
using (var dbExecutor = new SqlExecutor(sqlConnection))
{
sqlConnection.Open();
var packageRegistration = Util.GetPackageRegistration(
dbExecutor,
PackageId);
var packages = Util.GetPackages(
dbExecutor,
packageRegistration.Key);
foreach(var package in packages)
{
var task = new DeletePackageVersionTask {
ConnectionString = ConnectionString,
BackupStorage = BackupStorage,
StorageAccount = StorageAccount,
PackageId = package.Id,
PackageVersion = package.Version,
Reason = Reason,
WhatIf = WhatIf
};
task.ExecuteCommand();
}
Log.Info(
"Deleting package registration data for '{0}'",
packageRegistration.Id);
if (!WhatIf)
{
dbExecutor.Execute(
"DELETE por FROM PackageOwnerRequests por JOIN PackageRegistrations pr ON pr.[Key] = por.PackageRegistrationKey WHERE pr.[Key] = @packageRegistrationKey",
new { packageRegistrationKey = packageRegistration.Key });
dbExecutor.Execute(
"DELETE pro FROM PackageRegistrationOwners pro JOIN PackageRegistrations pr ON pr.[Key] = pro.PackageRegistrationKey WHERE pr.[Key] = @packageRegistrationKey",
new { packageRegistrationKey = packageRegistration.Key });
dbExecutor.Execute(
"DELETE FROM PackageRegistrations WHERE [Key] = @packageRegistrationKey",
new { packageRegistrationKey = packageRegistration.Key });
}
}
Log.Info(
"Deleted package registration and all package versions for '{0}'.",
PackageId);
}
public override void ExecuteCommand()
{
using (var sqlConnection = new SqlConnection(ConnectionString))
using (var dbExecutor = new SqlExecutor(sqlConnection))
{
sqlConnection.Open();
var package = Util.GetPackage(
dbExecutor,
PackageId,
PackageVersion);
if (package == null)
{
Log.Error("Package version does not exist: '{0}.{1}'", PackageId, PackageVersion);
return;
}
Log.Info(
"Deleting package data for '{0}.{1}'",
package.Id,
package.Version);
if (!WhatIf)
{
dbExecutor.Execute(
"DELETE pa FROM PackageAuthors pa JOIN Packages p ON p.[Key] = pa.PackageKey WHERE p.[Key] = @key",
new { key = package.Key });
dbExecutor.Execute(
"DELETE pd FROM PackageDependencies pd JOIN Packages p ON p.[Key] = pd.PackageKey WHERE p.[Key] = @key",
new { key = package.Key });
dbExecutor.Execute(
"DELETE ps FROM PackageStatistics ps JOIN Packages p ON p.[Key] = ps.PackageKey WHERE p.[Key] = @key",
new { key = package.Key });
dbExecutor.Execute(
"DELETE pf FROM PackageFrameworks pf JOIN Packages p ON p.[Key] = pf.Package_Key WHERE p.[Key] = @key",
new { key = package.Key });
dbExecutor.Execute(
"DELETE p FROM Packages p JOIN PackageRegistrations pr ON pr.[Key] = p.PackageRegistrationKey WHERE p.[Key] = @key",
new { key = package.Key });
}
new DeletePackageFileTask {
StorageAccount = StorageAccount,
PackageId = package.Id,
PackageVersion = package.Version,
PackageHash = package.Hash,
WhatIf = WhatIf
}.ExecuteCommand();
}
}
public override void ExecuteCommand()
{
using (var sqlConnection = new SqlConnection(ConnectionString.ConnectionString))
using (var dbExecutor = new SqlExecutor(sqlConnection))
{
sqlConnection.Open();
var package = Util.GetPackage(
dbExecutor,
PackageId,
PackageVersion);
if (package == null)
{
Log.Error("Package version does not exist: '{0}.{1}'", PackageId, PackageVersion);
return;
}
Log.Info(
"Deleting package data for '{0}.{1}'",
package.Id,
package.Version);
if (!WhatIf)
{
dbExecutor.Execute(
"DELETE pa FROM PackageAuthors pa JOIN Packages p ON p.[Key] = pa.PackageKey WHERE p.[Key] = @key",
new { key = package.Key });
dbExecutor.Execute(
"DELETE pd FROM PackageDependencies pd JOIN Packages p ON p.[Key] = pd.PackageKey WHERE p.[Key] = @key",
new { key = package.Key });
dbExecutor.Execute(
"DELETE ps FROM PackageStatistics ps JOIN Packages p ON p.[Key] = ps.PackageKey WHERE p.[Key] = @key",
new { key = package.Key });
dbExecutor.Execute(
"DELETE pf FROM PackageFrameworks pf JOIN Packages p ON p.[Key] = pf.Package_Key WHERE p.[Key] = @key",
new { key = package.Key });
dbExecutor.Execute(
"DELETE p FROM Packages p JOIN PackageRegistrations pr ON pr.[Key] = p.PackageRegistrationKey WHERE p.[Key] = @key",
new { key = package.Key });
}
new DeletePackageFileTask {
StorageAccount = StorageAccount,
PackageId = package.Id,
PackageVersion = package.Version,
PackageHash = package.Hash,
WhatIf = WhatIf
}.ExecuteCommand();
}
}
/// <summary>
/// Executes the task.
/// </summary>
private void ExecuteTask(object formObject)
{
ExecutionForm form = formObject as ExecutionForm;
if (form != null)
{
form.Invoke(new WriteOutputDelegate(WriteOutput), "-----------------------------------" + Environment.NewLine);
form.Invoke(new WriteOutputDelegate(WriteOutput), "Execution is started at " + DateTime.Now.ToString(dateTimeFormat) + Environment.NewLine);
try
{
SqlExecutor sqlExecutor = new SqlExecutor(form.WriteOutputLine, form.solution.ConnectionSetting, form.runningXml);
sqlExecutor.Execute(form.solution.Name);
form.Invoke(new Action(SetButtonAsCompleted));
}
catch (ThreadAbortException taex)
{
Thread.ResetAbort();
form.Invoke(new WriteOutputDelegate(WriteOutput), "Execution is aborted.");
form.Invoke(new Action(SetButtonAsCompleted));
}
catch (Exception ex)
{
WriteOutput(ex.Message);
}
finally
{
form.workThread = null;
form.Invoke(new WriteOutputDelegate(WriteOutput), "Execution is ended at " + DateTime.Now.ToString(dateTimeFormat) + Environment.NewLine);
form.Invoke(new WriteOutputDelegate(WriteOutput), "-----------------------------------" + Environment.NewLine);
}
}
}
public void TestCallAnyMatchSingleParameter()
{
Assert.That(async() =>
{
await SqlExecutor.Execute("SELECT any_match(arr) FROM typetest");
}, Throws.TypeOf <SqlErrorException>().And.Message.EqualTo("any_match must contain two parameters"));
}
public void TestInPredicateCantConvertTypeToObject()
{
Assert.ThrowsAsync <SqlErrorException>(
async() => await SqlExecutor.Execute("SELECT * FROM \"objecttable\" WHERE innerobject in ('test')"),
"Could not convert value: 'test' to type: 'InnerObject'"
);
}
public void TestCallAnyMatchWithoutColumnReference()
{
Assert.That(async() =>
{
await SqlExecutor.Execute("SELECT any_match(1, x -> x = 1) FROM typetest");
}, Throws.TypeOf <SqlErrorException>().And.Message.EqualTo("any_match first parameter must be a column reference"));
}
public async Task TestSelectAnyMatchInMemoryProvider()
{
var actual = (await SqlExecutor.Execute("SELECT any_match(IntList, x -> x = 1) FROM typetest"));
var expected = WebTests.TestData.GetTypeTests().Select(x => new { P0 = (x.IntList != null) && x.IntList.Any(y => y == 1) });
AssertAreEqual(expected, actual.Result);
}
public async Task TestSelectAnyMatchSubfield()
{
var actual = (await SqlExecutor.Execute("SELECT any_match(objectlist, x -> x.stringvalue = 'test') FROM typetest"));
var expected = WebTests.TestData.GetTypeTests().Select(x => new { P0 = (x.ObjectList != null) && x.ObjectList.Any(y => y.StringValue == "test") });
AssertAreEqual(expected, actual.Result);
}
public void TestCallFirstWithNonArrayProperty()
{
Assert.That(async() =>
{
await SqlExecutor.Execute("SELECT first(IntValue, x -> x = 1) FROM typetest");
}, Throws.TypeOf <SqlErrorException>().And.Message.EqualTo("'first' first parameter must be an array/list."));
}
public void TestCallFirstLambdaReturnsNonBoolean()
{
Assert.That(async() =>
{
await SqlExecutor.Execute("SELECT first(IntList, x -> x) FROM typetest");
}, Throws.TypeOf <SqlErrorException>().And.Message.EqualTo("Lambda expression in 'first' must return a boolean."));
}
public void TestCallFirstWithMultiParameterLambda()
{
Assert.That(async() =>
{
await SqlExecutor.Execute("SELECT first(IntList, (x, y) -> x = 1) FROM typetest");
}, Throws.TypeOf <SqlErrorException>().And.Message.EqualTo("'first' lambda expression can only have one input parameter"));
}
public void TestCallNonExistingFunction()
{
Assert.That(async() =>
{
await SqlExecutor.Execute("SELECT not_existing() FROM typetest");
}, Throws.TypeOf <SqlErrorException>().And.Message.EqualTo("No function exists named 'not_existing'."));
}
public async Task TestSelectFirstWithLambda()
{
var actual = (await SqlExecutor.Execute("SELECT first(IntList, x -> x = 2) FROM typetest"));
var expected = WebTests.TestData.GetTypeTests().Select(x => new { P0 = (x.IntList == null) ? null : (int?)x.IntList.FirstOrDefault(y => y == 2) });
AssertAreEqual(expected, actual.Result);
}
public void TestCalFirstThreeParameters()
{
Assert.That(async() =>
{
await SqlExecutor.Execute("SELECT first(arr, 1, 2) FROM typetest");
}, Throws.TypeOf <SqlErrorException>().And.Message.EqualTo("'first' must contain either one or two parameters"));
}
public async Task TestSelectFilter()
{
var actual = (await SqlExecutor.Execute("SELECT filter(IntList, x -> x = 1) FROM typetest"));
var expected = WebTests.TestData.GetTypeTests().Select(x => new { P0 = (x.IntList == null) ? null : x.IntList.Where(y => y == 1) });
AssertAreEqual(expected, actual.Result);
}
public void TestEmptyQuery()
{
Assert.That(async() =>
{
await SqlExecutor.Execute("");
}, Throws.TypeOf <SqlErrorException>());
}
public override void ExecuteCommand()
{
// Verify the name
if (!Force && !AllowedPrefixes.Any(p => ConnectionString.InitialCatalog.StartsWith(p, StringComparison.OrdinalIgnoreCase)))
{
Log.Error("Cannot sanitize database named '{0}' without -Force argument", ConnectionString.InitialCatalog);
return;
}
Log.Info("Ready to sanitize {0} on {1}", ConnectionString.InitialCatalog, Util.GetDatabaseServerName(ConnectionString));
// All we need to sanitize is the user table. Package data is public (EVEN unlisted ones) and not PII
if (WhatIf)
{
Log.Trace("Would execute the following SQL:");
Log.Trace(SanitizeUsersQuery);
Log.Trace("With @emailDomain = " + EmailDomain);
}
else
{
using (SqlConnection connection = new SqlConnection(ConnectionString.ConnectionString))
using (SqlExecutor dbExecutor = new SqlExecutor(connection))
{
connection.Open();
try
{
var count = dbExecutor.Execute(SanitizeUsersQuery, new { emailDomain = EmailDomain });
Log.Info("Sanitization complete. {0} Users affected", count);
}
catch (Exception ex)
{
Log.Error(ex.ToString());
}
}
}
}
public void TestCallAnyMatchWithoutLambda()
{
Assert.That(async() =>
{
await SqlExecutor.Execute("SELECT any_match(IntList, 'test') FROM typetest");
}, Throws.TypeOf <SqlErrorException>().And.Message.EqualTo("any_match second parameter must be a lambda expression"));
}
public override void ExecuteCommand()
{
// Verify the name
if (!Force && !AllowedPrefixes.Any(p => ConnectionString.InitialCatalog.StartsWith(p, StringComparison.OrdinalIgnoreCase)))
{
Log.Error("Cannot sanitize database named '{0}' without -Force argument", ConnectionString.InitialCatalog);
return;
}
Log.Info("Ready to sanitize {0} on {1}", ConnectionString.InitialCatalog, Util.GetDatabaseServerName(ConnectionString));
// All we need to sanitize is the user table. Package data is public (EVEN unlisted ones) and not PII
if (WhatIf)
{
Log.Trace("Would execute the following SQL:");
Log.Trace(SanitizeUsersQuery);
Log.Trace("With @emailDomain = " + EmailDomain);
}
else
{
using (SqlConnection connection = new SqlConnection(ConnectionString.ConnectionString))
using (SqlExecutor dbExecutor = new SqlExecutor(connection))
{
connection.Open();
try
{
var count = dbExecutor.Execute(SanitizeUsersQuery, new { emailDomain = EmailDomain });
Log.Info("Sanitization complete. {0} Users affected", count);
}
catch (Exception ex)
{
Log.Error(ex.ToString());
}
}
}
}
public void TestInPredicateCantConvertTypeToEnum()
{
Assert.ThrowsAsync <SqlErrorException>(
async() => await SqlExecutor.Execute("SELECT * FROM \"enumtable\" WHERE enum in ('test')"),
"Could not find a value in enum 'Enum' that matched: 'test"
);
}
internal static void InsertSolutionEpics(string connectionString, IEnumerable <Capability> capabilities,
string SolutionId)
{
var solEpics = new List <SolutionEpic>();
foreach (var cap in capabilities)
{
var epics = GetAllEpicsForCapability(connectionString, cap.Id);
foreach (var epic in epics)
{
var solEpic = new SolutionEpic
{
CapabilityID = cap.Id,
EpicFinalAssessmentResult = 1,
EpicID = epic.Id
};
solEpics.Add(solEpic);
}
}
var query =
"INSERT INTO SolutionEpic (SolutionId, CapabilityId, EpicId, StatusId, LastUpdated, LastUpdatedBy) VALUES (@SolutionId, @CapabilityId, @EpicId, 1, GetUtcDate(), '00000000-0000-0000-0000-000000000000')";
foreach (var epic in solEpics)
{
SqlExecutor.Execute <SolutionCapability>(connectionString, query,
new { CapabilityId = epic.CapabilityID, SolutionId, EpicId = epic.EpicID });
}
}
public void TestInPredicateColumnInArrayThrowsError()
{
Assert.ThrowsAsync <SqlErrorException>(
async() => await SqlExecutor.Execute("SELECT * FROM \"enumtable\" WHERE enum in (c1)"),
"IN predicate only supports literal or parameter values."
);
}
public void TestInPredicateParameterMissing()
{
Assert.ThrowsAsync <SqlErrorException>(
async() => await SqlExecutor.Execute("SELECT Orderkey, Orderpriority FROM \"order\" WHERE Orderpriority in (@P0)"),
"Could not find a parameter named: '@P0'"
);
}
internal static void SetAsFoundation(string connectionString, string SolutionId)
{
var query =
"INSERT INTO [dbo].[FrameworkSolutions] ([FrameworkId] ,[SolutionId] ,[IsFoundation], [LastUpdated] ,[LastUpdatedBy]) VALUES ('NHSDGP001', @SolutionId ,1 ,GetUtcDate(), '00000000-0000-0000-0000-000000000000')";
SqlExecutor.Execute <Solution>(connectionString, query, new{ SolutionId });
}
public void TestSearchFunction()
{
Assert.That(async() =>
{
await SqlExecutor.Execute("SELECT Orderkey, Orderpriority FROM \"order\" WHERE CONTAINS(*, 'Text')");
}, Throws.InstanceOf <SqlErrorException>().With.Message.EqualTo("Search is not implemented for this table"));
}
internal static void InsertSolution(string connectionString, Solution solution)
{
var query = @"INSERT INTO Solution (
Id,
SupplierId,
Name,
Version,
PublishedStatusId,
AuthorityStatusId,
SupplierStatusId,
OnCatalogueVersion,
LastUpdatedBy,
LastUpdated)
VALUES (
@SolutionId,
@SupplierId,
@SolutionName,
@SolutionVersion,
@PublishedStatusId,
@AuthorityStatusId,
@SupplierStatusId,
0,
@LastUpdatedBy,
@LastUpdated
)";
SqlExecutor.Execute <Solution>(connectionString, query, solution);
}
internal static void InsetSolutionDetail(string connectionString, SolutionDetail solutionDetail)
{
var query = @"INSERT INTO SolutionDetail (
Id,
LastUpdatedBy,
LastUpdated,
SolutionId,
Features,
ClientApplication,
AboutUrl,
Summary,
FullDescription,
RoadMap,
Hosting,
IntegrationsUrl,
ImplementationDetail )
VALUES (
@SolutionDetailId,
@LastUpdatedBy,
@LastUpdated,
@SolutionId,
@Features,
@ClientApplication,
@AboutUrl,
@Summary,
@FullDescription,
@RoadMap,
@Hosting,
@IntegrationsUrl,
@ImplementationDetail)";
SqlExecutor.Execute <SolutionDetail>(connectionString, query, solutionDetail);
}
public async Task TestSetVariable()
{
var result = await SqlExecutor.Execute(@"
SET @test = 'test';
select name from customer;
");
}
private void DeleteDatabaseBackup(Db db, SqlExecutor dbExecutor)
{
if (!WhatIf)
{
dbExecutor.Execute($"DROP DATABASE {db.Name}");
}
Log.Info("Deleted database {0}.", db.Name);
}
private void DeleteDatabaseBackup(Database db, SqlExecutor dbExecutor)
{
if (!WhatIf)
{
dbExecutor.Execute(string.Format("DROP DATABASE {0}", db.Name));
}
Log.Info("Deleted database {0}.", db.Name);
}
public async Task TestSumDividedBySum()
{
var result = await SqlExecutor.Execute("select sum(c.Acctbal) / sum(c.Custkey) from customer c");
var expected = TpchData.Customers.GroupBy(x => 1).Select(x => new { sum = x.Sum(y => y.Acctbal) / x.Sum(y => y.Custkey) }).AsQueryable();
AssertAreEqual(expected, result.Result);
}
private void DeleteDatabaseBackup(Db db, SqlExecutor dbExecutor)
{
if (!WhatIf)
{
dbExecutor.Execute($"DROP DATABASE {db.Name}");
}
Log.Info("Deleted database {0}.", db.Name);
}
public async Task TestSumWithOuterAddition()
{
var result = await SqlExecutor.Execute("select sum(c.Acctbal) + 1 from customer c group by c.name");
var expected = TpchData.Customers.GroupBy(x => x.Name).Select(x => new { sum = x.Sum(y => y.Acctbal) + 1 }).AsQueryable();
AssertAreEqual(expected, result.Result);
}
private string CopyDatabaseForRestore(
SqlExecutor masterDbExecutor)
{
var restoreDbName = string.Format("Restore_{0}", Util.GetTimestamp());
Log.Info("Copying {0} to {1}.", BackupName, restoreDbName);
masterDbExecutor.Execute(string.Format("CREATE DATABASE {0} AS COPY OF {1}", restoreDbName, BackupName));
Log.Info("Waiting for copy to complete.");
WaitForBackupCopy(
masterDbExecutor,
restoreDbName);
return restoreDbName;
}
public override void ExecuteCommand()
{
Log.Trace("Connecting to server '{0}' to back up database '{1}'.", ConnectionString.InitialCatalog, Util.GetDatabaseServerName(ConnectionString));
_startedBackup = false;
var cstr = Util.GetMasterConnectionString(ConnectionString.ConnectionString);
using(var connection = new SqlConnection(cstr))
using(var db = new SqlExecutor(connection))
{
connection.Open();
if (!Force)
{
Log.Trace("Checking for a backup in progress.");
if (Util.BackupIsInProgress(db, BackupNamePrefix))
{
Log.Trace("Found a backup in progress; exiting.");
return;
}
Log.Trace("Found no backup in progress.");
Log.Trace("Getting last backup time.");
var lastBackupTime = Util.GetLastBackupTime(db, BackupNamePrefix);
if (lastBackupTime >= DateTime.UtcNow.Subtract(TimeSpan.FromMinutes(IfOlderThan)))
{
Log.Info("Skipping Backup. Last Backup was less than {0} minutes ago", IfOlderThan);
return;
}
Log.Trace("Last backup time is more than {0} minutes ago. Starting new backup.", IfOlderThan);
}
else
{
Log.Trace("Forcing new backup");
}
// Generate a backup name
var timestamp = Util.GetTimestamp();
_backupName = BackupNamePrefix + timestamp;
if (!WhatIf)
{
db.Execute(string.Format("CREATE DATABASE {0} AS COPY OF {1}", _backupName, ConnectionString.InitialCatalog));
_startedBackup = true;
}
Log.Info("Started Copy of '{0}' to '{1}'", ConnectionString.InitialCatalog, _backupName);
}
}
public string Execute(string query, LoginDetails loginDetails)
{
string result;
using (var ms = new MemoryStream(2048))
using (var tw = new StreamWriter(ms))
{
var executor = new SqlExecutor(tw, loginDetails);
executor.Execute(query);
tw.Flush();
ms.Position = 0;
using (var tr = new StreamReader(ms))
{
result = tr.ReadToEnd();
}
}
return result;
}
public override void ExecuteCommand()
{
var dbServer = Util.GetDbServer(ConnectionString);
var dbName = Util.GetDbName(ConnectionString);
var masterConnectionString = Util.GetMasterConnectionString(ConnectionString);
Log.Trace("Connecting to server '{0}' to back up database '{1}'.", dbServer, dbName);
SkippingBackup = false;
using (var sqlConnection = new SqlConnection(masterConnectionString))
using (var dbExecutor = new SqlExecutor(sqlConnection))
{
sqlConnection.Open();
Log.Trace("Checking for a backup in progress.");
if (Util.BackupIsInProgress(dbExecutor))
{
Log.Trace("Found a backup in progress; exiting.");
return;
}
Log.Trace("Found no backup in progress.");
Log.Trace("Getting last backup time.");
var lastBackupTime = Util.GetLastBackupTime(dbExecutor);
if (lastBackupTime >= DateTime.UtcNow.Subtract(TimeSpan.FromMinutes(IfOlderThan)))
{
Log.Info("Skipping Backup. Last Backup was less than {0} minutes ago", IfOlderThan);
SkippingBackup = true;
return;
}
Log.Trace("Last backup time is more than {0} minutes ago. Starting new backup.", IfOlderThan);
var timestamp = Util.GetTimestamp();
BackupName = string.Format("Backup_{0}", timestamp);
dbExecutor.Execute(string.Format("CREATE DATABASE {0} AS COPY OF NuGetGallery", BackupName));
Log.Info("Starting '{0}'", BackupName);
}
}
public override void ExecuteCommand()
{
using (var sqlConnection = new SqlConnection(ConnectionString.ConnectionString))
using (var dbExecutor = new SqlExecutor(sqlConnection))
{
sqlConnection.Open();
var package = Util.GetPackage(
dbExecutor,
PackageId,
PackageVersion);
if (package == null)
{
Log.Info("Package '{0}.{1}' does not exist; exiting.");
return;
}
new BackupPackageFileTask {
StorageAccount = StorageAccount,
PackageId = package.Id,
PackageVersion = package.Version,
PackageHash = package.Hash
}.ExecuteCommand();
var hash = Util.GenerateHash(ReplacementFile.ReadAllBytes());
Log.Info("Updating hash for package '{0}.{1}' to '{2}'", package.Id, package.Version, hash);
dbExecutor.Execute(
"UPDATE Packages SET Hash = @hash WHERE [Key] = @key",
new { @key = package.Key, hash });
Log.Info("Uploading replacement file for package '{0}.{1}'", package.Id, package.Version);
ReplacementFile.Position = 0;
new UploadPackageTask {
StorageAccount = StorageAccount,
PackageId = package.Id,
PackageVersion = package.Version,
PackageFile = ReplacementFile
}.ExecuteCommand();
}
}
public override void ExecuteCommand()
{
// todo: move the data access from the website to a common lib and use that instead
using (var sqlConnection = new SqlConnection(ConnectionString.ConnectionString))
using (var dbExecutor = new SqlExecutor(sqlConnection))
{
sqlConnection.Open();
var package = dbExecutor.Query<Package>(
"SELECT p.[Key], pr.Id, p.Version, p.ExternalPackageUrl FROM Packages p JOIN PackageRegistrations pr ON pr.[Key] = p.PackageRegistrationKey WHERE pr.Id = @id AND p.Version = @version AND p.ExternalPackageUrl IS NOT NULL",
new { id = PackageId, version = PackageVersion })
.SingleOrDefault();
if (package == null)
{
Log.Info("Package is stored locally: {0} {1}", PackageId, PackageVersion);
}
else
{
using (var httpClient = new HttpClient())
using (var packageStream = httpClient.GetStreamAsync(package.ExternalPackageUrl).Result)
{
new UploadPackageTask
{
StorageAccount = StorageAccount,
PackageId = package.Id,
PackageVersion = package.Version,
PackageFile = packageStream,
WhatIf = WhatIf
}.ExecuteCommand();
}
if (!WhatIf)
{
dbExecutor.Execute(
"UPDATE Packages SET ExternalPackageUrl = NULL WHERE [Key] = @key",
new { key = package.Key });
}
}
}
}
public override void ExecuteCommand()
{
using (var sqlConnection = new SqlConnection(ConnectionString.ConnectionString))
using (var dbExecutor = new SqlExecutor(sqlConnection))
{
sqlConnection.Open();
var package = Util.GetPackage(
dbExecutor,
PackageId,
PackageVersion);
// Multiple queries? Yes. Do I care? No.
var packageRecord = new DataTable();
using (SqlCommand cmd = sqlConnection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM Packages WHERE [Key] = @key";
cmd.Parameters.AddWithValue("@key", package.Key);
var result = cmd.ExecuteReader();
packageRecord.Load(result);
}
var registrationRecord = new DataTable();
using (SqlCommand cmd = sqlConnection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM PackageRegistrations WHERE [ID] = @id";
cmd.Parameters.AddWithValue("@id", package.Id);
var result = cmd.ExecuteReader();
registrationRecord.Load(result);
}
// Write a delete audit record
var auditRecord = new PackageAuditRecord(
package.Id,
package.Version,
package.Hash,
packageRecord,
registrationRecord,
PackageAuditAction.Deleted,
Reason);
if (WhatIf)
{
Log.Info("Would Write Audit Record to " + auditRecord.GetPath());
}
else
{
Log.Info("Writing Audit Record");
var uri = Util.SaveAuditRecord(BackupStorage, auditRecord).Result;
Log.Info("Successfully wrote audit record to: " + uri.AbsoluteUri);
}
if (package == null)
{
Log.Error("Package version does not exist: '{0}.{1}'", PackageId, PackageVersion);
return;
}
if (!AuditOnly)
{
Log.Info(
"Deleting package data for '{0}.{1}'",
package.Id,
package.Version);
if (!WhatIf && !AuditOnly)
{
dbExecutor.Execute(
"DELETE pa FROM PackageAuthors pa JOIN Packages p ON p.[Key] = pa.PackageKey WHERE p.[Key] = @key",
new { key = package.Key });
dbExecutor.Execute(
"DELETE pd FROM PackageDependencies pd JOIN Packages p ON p.[Key] = pd.PackageKey WHERE p.[Key] = @key",
new { key = package.Key });
dbExecutor.Execute(
"DELETE pf FROM PackageFrameworks pf JOIN Packages p ON p.[Key] = pf.Package_Key WHERE p.[Key] = @key",
new { key = package.Key });
dbExecutor.Execute(
"DELETE p FROM Packages p JOIN PackageRegistrations pr ON pr.[Key] = p.PackageRegistrationKey WHERE p.[Key] = @key",
new { key = package.Key });
}
new DeletePackageFileTask
{
BackupStorage = BackupStorage,
StorageAccount = StorageAccount,
PackageId = package.Id,
PackageVersion = package.NormalizedVersion,
PackageHash = package.Hash,
WhatIf = WhatIf
}.ExecuteCommand();
}
else
{
Log.Info("Only wrote audit record. Package was NOT deleted.");
}
}
}
public override void ExecuteCommand()
{
// The backup policy:
// 1. Keep 2 rolling 30 minute backups
// 2. Keep the last backup (from 23:30 - 23:59) of the past two days
// 3. Delete any backup from before the past two days
// The result is:
// 1 Active Database
// 1 30min-old Backup
// 1 60min-old Backup
// 1 24hr-old (at most) Backup
// 1 48hr-old (at most) Backup
// TODO: Parameterize the policy (i.e. BackupPeriod, RollingBackupCount, DailyBackupCount, etc.)
var cstr = Util.GetMasterConnectionString(ConnectionString.ConnectionString);
using (var connection = new SqlConnection(cstr))
using (var db = new SqlExecutor(connection))
{
connection.Open();
// Get the list of backups
var backups = db.Query<Db>(
"SELECT name, state FROM sys.databases WHERE name LIKE 'Backup_%'",
new { state = Util.OnlineState })
.Select(d => new OnlineDatabaseBackup(Util.GetDatabaseServerName(ConnectionString), d.Name, d.State))
.OrderByDescending(b => b.Timestamp)
.ToList();
// Any currently copying are safe
var keepers = new HashSet<string>();
keepers.AddRange(backups.Where(b => b.State == Util.CopyingState).Select(b => b.DatabaseName));
// The last 2 online databases are definitely safe
keepers.AddRange(backups.Where(b => b.State == Util.OnlineState).Take(2).Select(b => b.DatabaseName));
Log.Info("Selected most recent two backups: {0}", String.Join(", ", keepers));
// Group by day, and skip any from today
var days = backups
.GroupBy(b => b.Timestamp.Value.Date)
.OrderByDescending(g => g.Key)
.Where(g => g.Key < DateTime.UtcNow.Date); // .Date gives us the current day at 00:00 hours, so "<" means previous day and earlier
// Keep the last backup from each of the previous two days
var dailyBackups = days
.Take(2) // Grab the last two days
.Select(day => day.OrderByDescending(b => b.Timestamp.Value).First()); // The last backup from each day
Log.Info("Selected most recent two daily backups: {0}", String.Join(", ", dailyBackups.Select(b => b.DatabaseName)));
// Verify data
var brokenDays = dailyBackups.Where(b => b.Timestamp.Value.TimeOfDay < new TimeSpan(23, 30, 00));
if(brokenDays.Any()) {
foreach(var brokenDay in brokenDays) {
Log.Warn("Daily backups for {0} are from earlier than 23:30 hours?", brokenDay.Timestamp.Value.DateTime.ToShortDateString());
}
}
var exportedDailyBackups = days.Skip(2).Select(day => day.Last());
var client = BackupStorage.CreateCloudBlobClient();
var container = client.GetContainerReference("database-backups");
foreach (var exportedDaily in exportedDailyBackups)
{
// We should be able to find a backup blob
string blobName = exportedDaily.DatabaseName + ".bacpac";
var blob = container.GetBlockBlobReference(blobName);
if (!blob.Exists())
{
// Derp?
Log.Warn("Expected {0} blob to exist but it hasn't been exported!", blob.Name);
keepers.Add(exportedDaily.DatabaseName); // Keep it for now.
}
}
// Keep those backups!
keepers.AddRange(dailyBackups.Select(b => b.DatabaseName));
// Figure out how many we're keeping
Log.Info("Keeping the following Backups: {0}", String.Join(", ", keepers));
if (keepers.Count < 2)
{
// Abort!
Log.Warn("About to clean too many backups. Aborting until we have enough to be in-policy.");
}
else
{
// Delete the rest!
foreach (var backup in backups.Where(b => !keepers.Contains(b.DatabaseName)))
{
if (!WhatIf)
{
db.Execute("DROP DATABASE " + backup.DatabaseName);
}
Log.Info("Deleted {0}", backup.DatabaseName);
}
}
}
}
private void ResolveReport(PackageFrameworkReport report)
{
bool error = false;
using (var sqlConnection = new SqlConnection(ConnectionString.ConnectionString))
using (var dbExecutor = new SqlExecutor(sqlConnection))
{
sqlConnection.Open();
foreach (var operation in report.Operations)
{
if (!WhatIf)
{
if (operation.Type == PackageFrameworkOperationType.Add)
{
try
{
dbExecutor.Execute(@"
INSERT INTO PackageFrameworks(TargetFramework, Package_Key)
VALUES (@targetFramework, @packageKey)",
new
{
targetFramework = operation.Framework,
packageKey = report.Key
});
Log.Info(" + Id={0}, Key={1}, Fx={2}", report.Id, report.Key, operation.Framework);
operation.Applied = true;
}
catch (Exception ex)
{
error = true;
operation.Applied = false;
operation.Error = ex.ToString();
}
}
else if (operation.Type == PackageFrameworkOperationType.Remove)
{
try
{
dbExecutor.Execute(@"
DELETE FROM PackageFrameworks
WHERE TargetFramework = @targetFramework AND Package_Key = @packageKey",
new
{
targetFramework = operation.Framework,
packageKey = report.Key
});
Log.Info(" - Id={0}, Key={1}, Fx={2}", report.Id, report.Key, operation.Framework);
operation.Applied = true;
}
catch (Exception ex)
{
error = true;
operation.Applied = false;
operation.Error = ex.ToString();
}
}
}
}
}
if (error)
{
report.State = PackageReportState.Error;
}
else if (report.Operations.All(o => o.Applied))
{
report.State = PackageReportState.Resolved;
}
}
void PopulateFrameworks(
Package package,
IEnumerable<string> targetFrameworks)
{
foreach (var targetFramework in targetFrameworks)
{
using (var sqlConnection = new SqlConnection(ConnectionString))
using (var dbExecutor = new SqlExecutor(sqlConnection))
{
sqlConnection.Open();
dbExecutor.Execute(
@"
INSERT INTO PackageFrameworks
(Package_Key, TargetFramework)
VALUES
(@packageKey, @targetFramework)",
new { packageKey = package.Key, targetFramework});
}
}
}
public override void ExecuteCommand()
{
Log.Info(
"Delete the user account and all packages for '{0}'.",
Username);
using (var sqlConnection = new SqlConnection(ConnectionString))
using (var dbExecutor = new SqlExecutor(sqlConnection))
{
sqlConnection.Open();
var user = Util.GetUser(dbExecutor, Username);
if (user == null)
{
Log.Error("User was not found");
return;
}
Log.Info("User found with EmailAddress '{0}' and UnconfirmedEmailAddress '{1}'",
user.EmailAddress, user.UnconfirmedEmailAddress);
var packageCount = user.PackageRegistrationIds.Count();
var packageNumber = 0;
foreach (var packageId in user.PackageRegistrationIds)
{
Log.Info("Deleting package '{0}' because '{1}' is the sole owner. ({2}/{3})",
packageId, Username, ++packageNumber, packageCount);
var deletePackageTask = new DeleteAllPackageVersionsTask
{
ConnectionString = ConnectionString,
StorageAccount = StorageAccount,
PackageId = packageId,
WhatIf = WhatIf
};
deletePackageTask.Execute();
}
Log.Info("Deleting remaining package ownership records (from shared ownership)");
if (!WhatIf)
{
dbExecutor.Execute(
"DELETE pro FROM PackageRegistrationOwners pro WHERE pro.UserKey = @userKey",
new { userKey = user.Key });
}
Log.Info("Deleting package ownership requests");
if (!WhatIf)
{
dbExecutor.Execute(
"DELETE por FROM PackageOwnerRequests por WHERE @userKey IN (por.NewOwnerKey, por.RequestingOwnerKey)",
new { userKey = user.Key });
}
Log.Info("Deleting the user record itself");
if (!WhatIf)
{
dbExecutor.Execute(
"DELETE u FROM Users u WHERE u.[Key] = @userKey",
new { userKey = user.Key });
}
Log.Info(
"Deleted all packages owned solely by '{0}' as well as the user record."
, user.Username);
}
}
void AddPackageToCuratedFeed(Package package)
{
if (!WhatIf)
{
using (var sqlConnection = new SqlConnection(ConnectionString))
using (var dbExecutor = new SqlExecutor(sqlConnection))
{
sqlConnection.Open();
dbExecutor.Execute(@"
INSERT INTO CuratedPackages
(CuratedFeedKey, PackageRegistrationKey, AutomaticallyCurated, Included)
VALUES
((SELECT [Key] FROM CuratedFeeds WHERE Name = 'webmatrix'), (SELECT [Key] FROM PackageRegistrations WHERE Id = @id), @automaticallyCurated, @included)",
new { id = package.Id, automaticallyCurated = true, included = true });
}
}
}
