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 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); }
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 }); } } }