public InteractiveSqlApplicationTests()
{
var sqlExecutor = new SqlExecutor(SqlServerConfiguration.ReadAppSettings());
var hintGenerator = new HintGenerator();
m_Application = new InteractiveSqlApplication(m_Logger, sqlExecutor, hintGenerator, CreateCourse());
m_Application.Start();
}
public async Task <IBookingIncidentData> BuildAux(IBookingIncidentData result)
{
var auxQueryStore = QueryStoreFactory.GetQueryStore();
#region KarveCode Generator for query multiple
// Code Generated that concatains multiple queries to be executed by QueryMultipleAsync.
auxQueryStore.AddParamCount(QueryType.QueryOffice, dto.OFICINA);
auxQueryStore.AddParamCount(QueryType.QuerySupplierSummary, dto.PROVEE);
auxQueryStore.AddParamCount(QueryType.QueryVehicleSummary, dto.VEHI);
auxQueryStore.AddParamCount(QueryType.ClientSummaryExtended, dto.CLIENTE);
auxQueryStore.AddParamCount(QueryType.IncidentType, dto.COINRE);
#endregion
var query = auxQueryStore.BuildQuery();
using (var connection = SqlExecutor.OpenNewDbConnection())
{
if (connection != null)
{
var multipleResult = await connection.QueryMultipleAsync(query).ConfigureAwait(false);
result.Valid = ParseResult(result, multipleResult);
}
}
return(result);
}
public async Task <IEnumerable <DtoTransfer> > GetPagedAsyncHelper <DtoTransfer, T>(string query, int startIndex, int pageSize) where DtoTransfer : class where T : class
{
IEnumerable <DtoTransfer> result = new List <DtoTransfer>();
var splittedQuery = query.Trim().Split(' ');
var outstring = "SELECT TOP {0} START AT {1} ";
var stringBuilder = new StringBuilder();
stringBuilder.Append(outstring);
for (int i = 1; i < splittedQuery.Length; ++i)
{
stringBuilder.Append(query[i]);
stringBuilder.Append(" ");
}
outstring = string.Format(stringBuilder.ToString(), startIndex, pageSize);
using (var dbConnection = SqlExecutor.OpenNewDbConnection())
{
try
{
var values = await dbConnection.QueryAsync <T>(query).ConfigureAwait(false);
result = _mapper.Map <IEnumerable <DtoTransfer> >(values);
}
catch (System.Exception e)
{
throw new DataLayerException("Error during mapping an entity", e);
}
}
return(result);
}
/// <summary>
/// Get the helper dataset for the async layer.
/// </summary>
/// <param name="assistQuery">Assist query.</param>
/// <param name="assitTableName">Assist table</param>
/// <returns></returns>
public async Task <DataSet> GetAsyncHelper(string assistQuery, string assitTableName)
{
DataSet set = await SqlExecutor.AsyncDataSetLoad(assistQuery);
set.Tables[0].TableName = assitTableName;
return(set);
}
/// <summary>
/// This returns the way of data layer.
/// </summary>
/// <param name="assistQuery">Assist query</param>
/// <returns></returns>
public async Task <IEnumerable <T> > GetAsyncHelper <T>(string assistQuery)
{
IDbConnection connection = SqlExecutor.Connection;
IEnumerable <T> result = null;
bool isOpen = false;
if (connection == null)
{
isOpen = SqlExecutor.Open();
}
else
{
if (connection.State != ConnectionState.Open)
{
isOpen = SqlExecutor.Open();
}
}
if (isOpen)
{
connection = SqlExecutor.Connection;
try
{
result = await connection.QueryAsync <T>(assistQuery);
}
finally
{
connection.Close();
}
}
return(result);
}
/// <summary>
/// GetUniqueMappedId
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public async Task <string> GetMappedUniqueId <DtoTransfer, T>(DtoTransfer entity) where T : class
{
string uniqueId;
if (entity == null)
{
return(String.Empty);
}
var entityValue = _mapper.Map <DtoTransfer, T>(entity);
if (SqlExecutor.Connection.State != ConnectionState.Open)
{
using (var connection = SqlExecutor.OpenNewDbConnection())
{
uniqueId = await GetScopedUniqueId <T>(connection, entityValue);
}
}
else
{
using (IDbConnection connection = SqlExecutor.OpenNewDbConnection())
{
uniqueId = await GetScopedUniqueId <T>(connection, entityValue);
}
}
return(uniqueId);
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public async Task <string> GetUniqueId <T>(T entity) where T : class
{
string uniqueId = string.Empty;
if (SqlExecutor.Connection.State != ConnectionState.Open)
{
using (var connection = SqlExecutor.OpenNewDbConnection())
{
if (connection != null)
{
uniqueId = await GetScopedUniqueId <T>(connection, entity);
}
}
}
else
{
using (IDbConnection connection = SqlExecutor.OpenNewDbConnection())
{
uniqueId = await GetScopedUniqueId <T>(connection, entity);
}
}
if (uniqueId == string.Empty)
{
throw new DataAccessLayerException("Not possible to generate an unique identifer");
}
return(uniqueId);
}
IList <Package> GetPackagesToBackUp(long?lastBackupId, bool forcedRecheck)
{
using (var sqlConnection = new SqlConnection(ConnectionString.ConnectionString))
using (var dbExecutor = new SqlExecutor(sqlConnection))
{
sqlConnection.Open();
Log.Info("Getting {1} packages to back up (since Package #{0})...", lastBackupId.HasValue ? lastBackupId.Value.ToString() : "?", forcedRecheck ? "all" : "1000");
StringBuilder uglySqlInjectionyStringBuilder = new StringBuilder(); // We trust our own code so it's not so SQL Injectiony...
uglySqlInjectionyStringBuilder.Append("SELECT ");
if (!forcedRecheck)
{
// Back up in 1000 package chunks
uglySqlInjectionyStringBuilder.Append("TOP 1000 ");
}
uglySqlInjectionyStringBuilder.Append("p.[Key], pr.Id, p.Version, p.Hash ");
uglySqlInjectionyStringBuilder.Append("FROM Packages p ");
uglySqlInjectionyStringBuilder.Append("JOIN PackageRegistrations pr ON pr.[Key] = p.PackageRegistrationKey ");
uglySqlInjectionyStringBuilder.Append("WHERE p.ExternalPackageUrl IS NULL ");
if (lastBackupId != null)
{
uglySqlInjectionyStringBuilder.Append("AND p.[Key] > " + lastBackupId.Value + " ");
}
uglySqlInjectionyStringBuilder.Append("ORDER BY Id, Version, Hash");
var list = dbExecutor.Query <Package>(uglySqlInjectionyStringBuilder.ToString()).ToList();
Log.Info("Got {0} packages.", list.Count);
return(list);
}
}
public int RunScriptsToUpdate(Server disposableDbServer, string scriptsFolderPath, string dbname)
{
SqlExecutor = new SqlExecutor(disposableDbServer);
if (!scriptsFolderPath.TrimEnd('/').TrimEnd('\\').EndsWith(FolderName))
{
var type = GetType();
var suffixPos = type.Name.IndexOf("Manager");
if (suffixPos < 1) {
throw new InvalidOperationException("Derived classes of UpdatableManager should have the -Manager suffix as well");
}
var scriptType = type.Name.Substring(0, suffixPos);
throw new ArgumentException(String.Format("scriptsFolderPath must contain {0}s", scriptType),
"scriptsFolderPath");
}
int count = 0;
IEnumerable<string> scriptPaths = Directory.GetFileSystemEntries(scriptsFolderPath, "*.sql");
foreach (var scriptPath in scriptPaths)
{
var scriptFilename = Path.GetFileName(scriptPath);
var scriptContents = File.ReadAllText(scriptPath);
var scriptContentsWithAlter = ReplaceCreateWithAlter(scriptContents);
try{
Run(scriptContentsWithAlter, dbname, scriptFilename);
} catch {
//maybe ALTER failed because the element is still not there...
Run(scriptContents, dbname, scriptFilename);
}
count++;
}
return count;
}
private void CreateWeeklyStatReportFor(string connectionString,string sqlQuery,string reportName)
{
startingTime = new DateTime(Year, UnixTimeStampUtility.GetMonthNumber(Month), 01); //initialize to day 01 of the given month.
DateTime monthEndTime = new DateTime(Year, UnixTimeStampUtility.GetMonthNumber(Month), UnixTimeStampUtility.GetDaysInMonth(Month));
List<Tuple<string, string>> uploadsDataPoints = new List<Tuple<string, string>>();
int week = 1;
using (var sqlConnection = new SqlConnection(connectionString))
{
using (var dbExecutor = new SqlExecutor(sqlConnection))
{
sqlConnection.Open();
while (startingTime <= monthEndTime)
{
DateTime endTime = startingTime.AddDays(7);
if (endTime > monthEndTime) endTime = monthEndTime;
try
{
var count = dbExecutor.Query<Int32>(string.Format(sqlQuery, startingTime.ToString("yyyy-MM-dd"), endTime.ToString("yyyy-MM-dd"))).SingleOrDefault();
uploadsDataPoints.Add(new Tuple<string, string>("Week" + week++, count.ToString()));
}
catch (NullReferenceException)
{
uploadsDataPoints.Add(new Tuple<string, string>("Week" + week++, "0"));
}
startingTime = startingTime.AddDays(7);
}
}
}
JArray reportObject = ReportHelpers.GetJson(uploadsDataPoints);
ReportHelpers.CreateBlob(ReportStorage, reportName + Month + "MonthlyReport.json", "dashboard", "application/json", ReportHelpers.ToStream(reportObject));
}
public override void ExecuteCommand()
{
using (var masterDbConnection = new SqlConnection(Util.GetMasterConnectionString(ConnectionString)))
using (var masterDbExecutor = new SqlExecutor(masterDbConnection))
{
masterDbConnection.Open();
var restoreDbName = CopyDatabaseForRestore(
masterDbExecutor);
using (var restoreDbConnection = new SqlConnection(Util.GetConnectionString(ConnectionString, restoreDbName)))
using (var restoreDbExecutor = new SqlExecutor(restoreDbConnection))
{
restoreDbConnection.Open();
PrepareDataForRestore(
restoreDbExecutor);
RenameLiveDatabase(
masterDbExecutor);
RenameDatabaseBackup(
masterDbExecutor,
restoreDbName);
}
}
}
public DisposableDbManager(string dbCreationPath, Server disposableDbServer, string dbNameInVcs)
{
_dbCreationPath = dbCreationPath;
_disposableDbServer = disposableDbServer;
_sqlExecutor = new SqlExecutor(_disposableDbServer);
_dbNameInVcs = dbNameInVcs;
}
public override void ExecuteCommand()
{
var dbServer = ConnectionString.DataSource;
var masterConnectionString = Util.GetMasterConnectionString(ConnectionString.ConnectionString);
Log.Info("Listing backups for server '{0}':", dbServer);
using (var sqlConnection = new SqlConnection(masterConnectionString))
using (var dbExecutor = new SqlExecutor(sqlConnection))
{
sqlConnection.Open();
var dbs = dbExecutor.Query<Db>(
"SELECT name FROM sys.databases WHERE name LIKE 'Backup_%' AND state = @state",
new { state = Util.OnlineState });
foreach(var db in dbs)
{
var timestamp = Util.GetDatabaseNameTimestamp(db);
var date = Util.GetDateTimeFromTimestamp(timestamp);
Log.Info("{0} ({1})", timestamp, date);
}
}
}
public override void ExecuteCommand()
{
var dbServer = ConnectionString.DataSource;
var masterConnectionString = Util.GetMasterConnectionString(ConnectionString.ConnectionString);
Log.Trace("Deleting old warehouse backups for server '{0}':", dbServer);
using (var sqlConnection = new SqlConnection(masterConnectionString))
{
sqlConnection.Open();
using (var dbExecutor = new SqlExecutor(sqlConnection))
{
var dbs = dbExecutor.Query<Db>(
"SELECT name FROM sys.databases WHERE name LIKE 'WarehouseBackup_%' AND state = @state",
new { state = Util.OnlineState });
foreach (var db in dbs)
{
var timestamp = Util.GetDatabaseNameTimestamp(db);
var date = Util.GetDateTimeFromTimestamp(timestamp);
if (DateTime.UtcNow.Subtract(TimeSpan.FromDays(7)) > date)
DeleteDatabaseBackup(db, dbExecutor);
}
}
}
}
private void CreateReportForVSTask()
{
string[] VsQuery = new JavaScriptSerializer().Deserialize<string[]>(ReportHelpers.Load(StorageAccount, "VsVersion.json", ContainerName));
using (var sqlConnection = new SqlConnection(ConnectionString.ConnectionString))
{
using (var dbExecutor = new SqlExecutor(sqlConnection))
{
sqlConnection.Open();
DateTime date = DateTime.UtcNow.AddDays(-LastNDays);
List<VsRequest> requests = new List<VsRequest>();
foreach (string each in VsQuery)
{
try
{
var request = dbExecutor.Query<Int32>(string.Format(sqlQueryForVS, date.ToString("yyyy-MM-dd"), each)).SingleOrDefault();
requests.Add(new VsRequest("VS" + each, request.ToString()));
}
catch
{
requests.Add(new VsRequest("VS" + each, "0"));
}
}
var json = new JavaScriptSerializer().Serialize(requests);
ReportHelpers.CreateBlob(StorageAccount, "VsTrend" + LastNDays.ToString() + "Day.json", ContainerName, "application/json", ReportHelpers.ToStream(json));
}
}
}
public static bool BackupIsInProgress(SqlExecutor dbExecutor)
{
return dbExecutor.Query<Database>(
"SELECT name, state FROM sys.databases WHERE name LIKE 'Backup_%' AND state = @state",
new { state = CopyingState })
.Any();
}
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 async Task Should_Load_AValidReservation()
{
var codigo = string.Empty;
using (var dbConnection = SqlExecutor.OpenNewDbConnection())
{
var connection = await dbConnection.GetPagedAsync <RESERVAS1>(1, 2).ConfigureAwait(false);
var item = connection.FirstOrDefault <RESERVAS1>();
codigo = item.NUMERO_RES;
}
IBookingData booking = null;
Stopwatch start = new Stopwatch();
start.Start();
booking = await _bookingDataServices.GetDoAsync(codigo).ConfigureAwait(false);
//Assert.DoesNotThrowAsync(async () => booking = await _bookingDataServices.GetDoAsync(codigo).ConfigureAwait(false));
var elapse = start.ElapsedMilliseconds;
start.Stop();
TestContext.Out.WriteLine("Elapsed booking retrieval time " + elapse);
Assert.NotNull(booking);
Assert.IsTrue(booking.Valid);
Assert.NotNull(booking.Value);
Assert.AreEqual(booking.Value.NUMERO_RES, codigo);
}
public static TicketCounts[] GetData()
{
List <TicketCounts> result = new List <TicketCounts>();
SqlCommand command = new SqlCommand(@"select p.name as ProductName,
(select count(*) from tickets as t, products as p2, ticketstatuses as ts, tickettypes as tt
where t.productid = p2.productid
and t.organizationid = @OrganizationID
and t.ticketstatusid = ts.ticketstatusid
and ts.isclosed = 0
and t.tickettypeid = tt.tickettypeid
and tt.name = 'issues'
and p.productid = p2.productid) as NumIssues,
(select count(*) from tickets as t, products as p2, ticketstatuses as ts, tickettypes as tt
where t.productid = p2.productid
and t.organizationid = @OrganizationID
and t.ticketstatusid = ts.ticketstatusid
and ts.isclosed = 0
and t.tickettypeid = tt.tickettypeid
and tt.name = 'tasks'
and p.productid = p2.productid) as NumTasks,
(select count(*) from tickets as t, products as p2, ticketstatuses as ts, tickettypes as tt
where t.productid = p2.productid
and t.organizationid = @OrganizationID
and t.ticketstatusid = ts.ticketstatusid
and ts.isclosed = 0
and t.tickettypeid = tt.tickettypeid
and tt.name = 'bugs'
and p.productid = p2.productid) as NumBugs,
(select count(*) from tickets as t, products as p2, ticketstatuses as ts, tickettypes as tt
where t.productid = p2.productid
and t.organizationid = @OrganizationID
and t.ticketstatusid = ts.ticketstatusid
and ts.isclosed = 0
and t.tickettypeid = tt.tickettypeid
and tt.name = 'features'
and p.productid = p2.productid) as NumFeatures
From Products as p
where p.organizationid = @OrganizationID
order by p.name");
command.Parameters.AddWithValue("@OrganizationID", UserSession.LoginUser.OrganizationID);
DataTable productTickets = SqlExecutor.ExecuteQuery(UserSession.LoginUser, command);
foreach (DataRow thisRow in productTickets.Rows)
{
TicketCounts counts = new TicketCounts();
counts.Product = (string)thisRow["ProductName"];
counts.Issues = (int)thisRow["NumIssues"];
counts.Tasks = (int)thisRow["NumTasks"];
counts.Bugs = (int)thisRow["NumBugs"];
counts.Features = (int)thisRow["NumFeatures"];
result.Add(counts);
}
return(result.ToArray());
}
protected void WithConnection(Action <SqlConnection, SqlExecutor> act)
{
using (var c = OpenConnection())
using (var e = new SqlExecutor(c))
{
act(c, e);
}
}
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);
}
private void DeleteDatabaseBackup(Db db, SqlExecutor dbExecutor)
{
if (!WhatIf)
{
dbExecutor.Execute($"DROP DATABASE {db.Name}");
}
Log.Info("Deleted database {0}.", db.Name);
}
protected virtual void ExecuteStatementsWithoutTransaction(
[NotNull] IEnumerable <SqlBatch> sqlStatements, [NotNull] RelationalConnection connection)
{
Check.NotNull(sqlStatements, "sqlStatements");
Check.NotNull(connection, "connection");
SqlExecutor.ExecuteNonQuery(connection, null, sqlStatements);
}
public async Task ThenTheNameOfTheSelectedAssociatedServiceIsDisplayedOnTheAssociatedServiceEditForm()
{
var itemId = (string)Context[ContextKeys.ChosenItemId];
var query = "Select Name FROM [dbo].[CatalogueItem] where CatalogueItemId=@itemId";
var expectedSolutionName = (await SqlExecutor.ExecuteAsync <string>(Test.BapiConnectionString, query, new { itemId })).Single();
Test.Pages.OrderForm.TextDisplayedInPageTitle(expectedSolutionName).Should().BeTrue();
}
public QueryMapper(SqlExecutor <TArgs, TResult> query, Expression <Func <TResult, TArgs, TMapped> > mapper)
{
Query = query ?? throw new ArgumentNullException(nameof(query));
var(rpu, m) = EnsureComplexMap(mapper ?? throw new ArgumentNullException(nameof(mapper)));
RequiresPropertyUnwrap = rpu;
Mapper = m;
}
public async Task Delete(string connectionString)
{
var relatedQuery = @"DELETE FROM dbo.RelatedOrganisations WHERE OrganisationId=@organisationId OR RelatedOrganisationId=@organisationId;";
await SqlExecutor.ExecuteAsync <Organisation>(connectionString, relatedQuery, this);
var query = @"DELETE FROM dbo.Organisations WHERE OrganisationId=@organisationId OR OdsCode=@odsCode";
await SqlExecutor.ExecuteAsync <Organisation>(connectionString, query, this);
}
public static async Task DeleteContactsForOrdersNoLongerInDb(string connectionString)
{
var query = "DELETE FROM dbo.Contact " +
"WHERE ContactId NOT IN (SELECT OrganisationContactId FROM dbo.[Order])" +
"OR ContactId NOT IN (SELECT SupplierContactId FROM dbo.[Order];";
await SqlExecutor.ExecuteAsync <string>(connectionString, query, null);
}
public static async Task <OrderingParty> GetByOdsCode(this OrderingParty orderingParty, string connectionString)
{
var query = "SELECT * FROM OrderingParty WHERE OdsCode=@OdsCode";
var results = await SqlExecutor.ExecuteAsync <OrderingParty>(connectionString, query, orderingParty);
return(results.SingleOrDefault());
}
public LessonModule(InteractiveSqlApplication application, SqlExecutor sqlExecutor)
{
m_Application = application;
m_SqlExecutor = sqlExecutor;
var firstLesson = m_Application.Course.Lessons.First();
Get["/"] = parameters => Response.AsRedirect(firstLesson.Url());
Get["/lesson/{lessonName}"] = parameters => RenderLesson(parameters.lessonName);
}
protected bool WithMasterConnection(Func <SqlConnection, SqlExecutor, bool> act)
{
using (var c = OpenMasterConnection())
using (var e = new SqlExecutor(c))
{
return(act(c, e));
}
}
public async Task TestStringEqualsIgnoreCaseIsNullEqualsNull()
{
var result = await SqlExecutor.Execute("SELECT * FROM nulltest WHERE isnull is null");
var expected = TestData.GetNullTestData().Where(x => x.IsNull == null).Select(x => new { x.IsNull }).AsQueryable();
AssertAreEqual(expected, result.Result);
}
public static async Task <SupplierDetails> GetSupplierWithId(string supplierId, string connectionString)
{
var query = "SELECT [Id] as 'SupplierId', [Name], [Address] FROM Supplier WHERE Id = @supplierId";
var result = await SqlExecutor.ExecuteAsync <SupplierDetails>(connectionString, query, new { supplierId });
return(result.Single());
}
public async Task TestSumGroupBySingle()
{
var result = await SqlExecutor.Execute("select sum(Acctbal) from customer group by name");
var expected = TpchData.Customers.GroupBy(x => x.Name).Select(x => new { sum = x.Sum(y => y.Acctbal) }).AsQueryable();
AssertAreEqual(expected, result.Result);
}
public async Task TestExecuteScalar()
{
var result = await SqlExecutor.ExecuteScalar($"SELECT count(*) FROM customer");
var expected = TpchData.Customers.Count;
Assert.That(result, Is.EqualTo(expected));
}
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 TestSelectCustomerName()
{
var result = await SqlExecutor.Execute("select name from customer");
var expected = TpchData.Customers.Select(x => new { x.Name }).AsQueryable();
AssertAreEqual(expected, result.Result);
}
public static bool BackupIsInProgress(SqlExecutor dbExecutor, string backupPrefix)
{
return(dbExecutor.Query <Db>(
// Not worried about SQL Injection here :). This is an admin tool.
"SELECT name, state FROM sys.databases WHERE name LIKE '" + backupPrefix + "%' AND state = @state",
new { state = CopyingState })
.Any());
}
public void TestParameterNotFound()
{
Assert.That(async() =>
{
SqlParameters sqlParameters = new SqlParameters();
await SqlExecutor.Execute("SELECT Orderkey, Orderpriority FROM \"order\" WHERE orderkey > @P0", sqlParameters);
}, Throws.InstanceOf <SqlErrorException>().With.Message.EqualTo("The parameter @P0 could not be found, did you have include @ before the parameter 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 void EmptyQueriesCauseFailedResult()
{
var application = new SqlExecutor(m_SqlServerConfiguration);
application.SetUpDatabase(" ");
var result = application.ExecuteQuery("");
Assert.Equal("Query is empty", ExpectError(result));
}
public void BadlyFormedQueriesCauseFailedResult()
{
var application = new SqlExecutor(m_SqlServerConfiguration);
application.SetUpDatabase(" ");
var result = application.ExecuteQuery("SELECTEROO");
Assert.Equal("Could not find stored procedure 'SELECTEROO'.", ExpectError(result));
}
public static bool BackupIsInProgress(SqlExecutor dbExecutor, string backupPrefix)
{
return dbExecutor.Query<Db>(
// Not worried about SQL Injection here :). This is an admin tool.
"SELECT name, state FROM sys.databases WHERE name LIKE '" + backupPrefix + "%' AND state = @state",
new { state = CopyingState })
.Any();
}
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);
}
public override void ExecuteCommand()
{
var cstr = Util.GetMasterConnectionString(ConnectionString.ConnectionString);
using (var connection = new SqlConnection(cstr))
using (var db = new SqlExecutor(connection))
{
connection.Open();
// Snap the current date just in case we are running right on the cusp
var today = DateTime.UtcNow;
// Get the list of database backups
var backups = db.Query <Db>(
"SELECT name, state FROM sys.databases WHERE name LIKE 'Backup_%'")
.Select(d => new OnlineDatabaseBackup(Util.GetDatabaseServerName(ConnectionString), d.Name, d.State))
.OrderByDescending(b => b.Timestamp)
.ToList();
// Grab end-of-day backups from days before today
var dailyBackups = backups
.GroupBy(b => b.Timestamp.Value.Date)
.Where(g => g.Key < today.Date)
.Select(g => g.OrderByDescending(b => b.Timestamp.Value).Last())
.ToList();
Log.Info("Found {0} daily backups to export", dailyBackups.Count);
// Start exporting them
foreach (var dailyBackup in dailyBackups)
{
if (dailyBackup.State != Util.OnlineState)
{
Log.Info("Skipping '{0}', it is still being copied", dailyBackup.DatabaseName);
}
else
{
if (dailyBackup.Timestamp.Value.TimeOfDay < new TimeSpan(23, 30, 00))
{
Log.Warn("Somehow, '{0}' is the only backup from {1}. Exporting it to be paranoid",
dailyBackup.DatabaseName,
dailyBackup.Timestamp.Value.Date.ToShortDateString());
}
Log.Info("Exporting '{0}'...", dailyBackup.DatabaseName);
(new ExportDatabaseTask()
{
ConnectionString = new SqlConnectionStringBuilder(ConnectionString.ConnectionString)
{
InitialCatalog = dailyBackup.DatabaseName
},
DestinationStorage = StorageAccount,
DestinationContainer = "database-backups",
SqlDacEndpoint = SqlDacEndpoint,
WhatIf = WhatIf
}).Execute();
}
}
}
}
public async Task TestSubQueryAdditionInOuter()
{
var result = await SqlExecutor.Execute($"select c.sum + 1 from (select sum(Acctbal) AS sum from customer) c");
var expected = TpchData.Customers
.GroupBy(x => 1).Select(x => new { sum = x.Sum(y => y.Acctbal) + 1 }).AsQueryable();
AssertAreEqual(expected, result.Result);
}
public void TestSearchFunctionWithParameter()
{
Assert.That(async() =>
{
SqlParameters sqlParameters = new SqlParameters()
.Add(SqlParameter.Create("P0", "test"));
await SqlExecutor.Execute("SELECT Orderkey, Orderpriority FROM \"order\" WHERE CONTAINS(*, @P0)", sqlParameters);
}, Throws.InstanceOf <SqlErrorException>().With.Message.EqualTo("Search is not implemented for this table"));
}
public override void ExecuteCommand()
{
var cstr = Util.GetMasterConnectionString(ConnectionString.ConnectionString);
using (var connection = new SqlConnection(cstr))
using (var db = new SqlExecutor(connection))
{
connection.Open();
// Snap the current date just in case we are running right on the cusp
var today = DateTime.UtcNow;
// Get the list of database backups
var backups = db.Query<Db>(
"SELECT name, state FROM sys.databases WHERE name LIKE 'Backup_%'")
.Select(d => new OnlineDatabaseBackup(Util.GetDatabaseServerName(ConnectionString), d.Name, d.State))
.OrderByDescending(b => b.Timestamp)
.ToList();
// Grab end-of-day backups from days before today
var dailyBackups = backups
.GroupBy(b => b.Timestamp.Value.Date)
.Where(g => g.Key < today.Date)
.Select(g => g.OrderByDescending(b => b.Timestamp.Value).Last())
.ToList();
Log.Info("Found {0} daily backups to export", dailyBackups.Count);
// Start exporting them
foreach (var dailyBackup in dailyBackups)
{
if (dailyBackup.State != Util.OnlineState)
{
Log.Info("Skipping '{0}', it is still being copied", dailyBackup.DatabaseName);
}
else
{
if (dailyBackup.Timestamp.Value.TimeOfDay < new TimeSpan(23, 30, 00))
{
Log.Warn("Somehow, '{0}' is the only backup from {1}. Exporting it to be paranoid",
dailyBackup.DatabaseName,
dailyBackup.Timestamp.Value.Date.ToShortDateString());
}
Log.Info("Exporting '{0}'...", dailyBackup.DatabaseName);
(new ExportDatabaseTask()
{
ConnectionString = new SqlConnectionStringBuilder(ConnectionString.ConnectionString)
{
InitialCatalog = dailyBackup.DatabaseName
},
DestinationStorage = StorageAccount,
DestinationContainer = "database-backups",
SqlDacEndpoint = SqlDacEndpoint,
WhatIf = WhatIf
}).Execute();
}
}
}
}
public static DateTime GetLastBackupTime(SqlExecutor dbExecutor, string backupNamePrefix)
{
var lastBackup = GetLastBackup(dbExecutor, backupNamePrefix);
if (lastBackup == null)
return DateTime.MinValue;
var timestamp = lastBackup.Name.Substring(backupNamePrefix.Length);
return GetDateTimeFromTimestamp(timestamp);
}
private static void RunScriptsInForDatabase(Server server, DbParams dbParams, IEnumerable<string> sqlFiles)
{
var executor = new SqlExecutor(server);
var sqlFilesSortedAlphabetically = Order(sqlFiles);
foreach(var sqlFile in sqlFilesSortedAlphabetically) {
Console.WriteLine("Going to run " + sqlFile);
executor.ExecuteNonQuery(File.ReadAllText(sqlFile), dbParams.DbName);
}
}
public override void ExecuteCommand()
{
using (var sqlConnection = new SqlConnection(ConnectionString.ConnectionString))
using (var dbExecutor = new SqlExecutor(sqlConnection))
{
sqlConnection.Open();
var externalPackages = dbExecutor.Query<Package>(@"
SELECT pr.Id, p.Version, p.ExternalPackageUrl
FROM Packages p
JOIN PackageRegistrations pr ON pr.[Key] = p.PackageRegistrationKey
WHERE p.ExternalPackageUrl IS NOT NULL
ORDER BY Id, Version");
foreach (Package pkg in externalPackages)
{
Console.WriteLine();
HttpClient client = new HttpClient();
var responseTask = client.GetAsync(pkg.ExternalPackageUrl);
var response = responseTask.Result;
if (!response.IsSuccessStatusCode)
{
Console.WriteLine("Found broken package: " + response.StatusCode + " " + pkg.ExternalPackageUrl);
Console.WriteLine("You should ask the package owner to unlist the package " + pkg.Id + " " + pkg.Version);
}
var bytesTask = response.Content.ReadAsByteArrayAsync();
byte[] bytes = bytesTask.Result;
var blobClient = CreateBlobClient();
var packagesBlobContainer = Util.GetPackagesBlobContainer(blobClient);
var packageFileBlob = Util.GetPackageFileBlob(
packagesBlobContainer,
pkg.Id,
pkg.Version);
var fileName = Util.GetPackageFileName(
pkg.Id,
pkg.Version);
if (packageFileBlob.Exists())
{
Console.WriteLine("SKIPPED! Package file blob " + fileName + " already exists");
}
else
{
Console.WriteLine("Saving the package file " + pkg.ExternalPackageUrl + " to blob storage as " + fileName);
if (!WhatIf)
{
packageFileBlob.UploadFromStream(
new MemoryStream(bytes),
AccessCondition.GenerateIfNoneMatchCondition("*"));
}
}
}
}
}
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);
}
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 static Db GetLastBackup(SqlExecutor dbExecutor, string backupNamePrefix)
{
var allBackups = dbExecutor.Query<Db>(
"SELECT name, state FROM sys.databases WHERE name LIKE '" + backupNamePrefix + "%' AND state = @state",
new { state = OnlineState });
var orderedBackups = from db in allBackups
let t = ParseNewTimestamp(BackupNameFormat.Match(db.Name).Groups["timestamp"].Value)
where t != null
orderby t descending
select db;
return orderedBackups.FirstOrDefault();
}
public List<DatabaseRequest> RefreshDatebaseRequest()
{
List<Tuple<string, string>> connectionCountDataPoints = new List<Tuple<string, string>>();
using (var sqlConnection = new SqlConnection(ConnectionString))
{
using (var dbExecutor = new SqlExecutor(sqlConnection))
{
sqlConnection.Open();
var requests = dbExecutor.Query<DatabaseRequest>("SELECT t.text, r.start_time, r.status, r.command, r.wait_type, r.wait_time FROM sys.dm_exec_requests r OUTER APPLY sys.dm_exec_sql_text(sql_handle) t");
return requests.ToList();
}
}
}
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 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();
}
}
private void CreateReportForDataBaseEvents()
{
var masterConnectionString = Util.GetMasterConnectionString(ConnectionString.ConnectionString);
var currentDbName = Util.GetDbName(ConnectionString.ConnectionString);
using (var sqlConnection = new SqlConnection(masterConnectionString))
{
using (var dbExecutor = new SqlExecutor(sqlConnection))
{
sqlConnection.Open();
var usageSeconds = dbExecutor.Query<DatabaseEvent>(string.Format("select start_time, end_time,event_type,event_count,description from sys.event_log where start_time>='{0}' and start_time<='{1}' and database_name = '{2}' and severity = 2", DateTime.UtcNow.AddHours(-LastNHours).ToString("yyyy-MM-dd hh:mm:ss"),DateTime.UtcNow.ToString("yyyy-MM-dd hh:mm:ss"), currentDbName));
var json = new JavaScriptSerializer().Serialize(usageSeconds);
ReportHelpers.CreateBlob(StorageAccount, "DBDetailed" + LastNHours.ToString() + "Hour.json", ContainerName, "application/json", ReportHelpers.ToStream(json));
var throttlingEventCount = dbExecutor.Query<Int32>(string.Format("select count(*) from sys.event_log where start_time>='{0}' and start_time<='{1}' and database_name = '{2}' and (event_type Like 'throttling%' or event_type Like 'deadlock')", DateTime.UtcNow.AddHours(-1).ToString("yyyy-MM-dd hh:mm:ss"),DateTime.UtcNow.ToString("yyyy-MM-dd hh:mm:ss"),currentDbName)).SingleOrDefault();
var additional_data = dbExecutor.Query<string>(string.Format("select additional_data from sys.event_log where start_time>='{0}' and start_time<='{1}' and database_name = '{2}' and (event_type Like 'throttling%' or event_type Like 'deadlock')", DateTime.UtcNow.AddHours(-1).ToString("yyyy-MM-dd hh:mm:ss"), DateTime.UtcNow.ToString("yyyy-MM-dd hh:mm:ss"), currentDbName));
AlertThresholds thresholdValues = new JavaScriptSerializer().Deserialize<AlertThresholds>(ReportHelpers.Load(StorageAccount, "Configuration.AlertThresholds.json", ContainerName));
StringBuilder sb = new StringBuilder();
foreach (string data in additional_data)
{
if (data != null) sb.Append(data+"\n");
}
if(throttlingEventCount > thresholdValues.DatabaseThrottlingEventErrorThreshold && LastNHours == 1)
{
new SendAlertMailTask
{
AlertSubject = "Error: SQL Azure DB alert activated for throttling/deadlock event",
Details = string.Format("Number of events exceeded threshold for DB throttling/deadlock events. Error Threshold count : {0}, events noticed in last hour : {1}, all additional data is {2}", thresholdValues.DatabaseThrottlingEventErrorThreshold, throttlingEventCount,sb.ToString()),
AlertName = "Error: SQL Azure DB throttling/deadlock event",
Component = "SQL Azure Database",
Level = "Error"
}.ExecuteCommand();
}
else if (throttlingEventCount > thresholdValues.DatabaseThrottlingEventWarningThreshold && LastNHours == 1)
{
new SendAlertMailTask
{
AlertSubject = "Warning: SQL Azure DB alert activated for throttling/deadlock event",
Details = string.Format("Number of events exceeded threshold for DB throttling/deadlock events. Warning Threshold count : {0}, events noticed in last hour : {1}, all additional data is {2}", thresholdValues.DatabaseThrottlingEventWarningThreshold, throttlingEventCount,sb.ToString()),
AlertName = "Warning: SQL Azure DB throttling/deadlock event",
Component = "SQL Azure Database",
Level = "Warning"
}.ExecuteCommand();
}
}
}
}
private void CreateReportForIndexFragmentation()
{
using (var sqlConnection = new SqlConnection(ConnectionString.ConnectionString))
{
using (var dbExecutor = new SqlExecutor(sqlConnection))
{
sqlConnection.Open();
AlertThresholds thresholdValues = new JavaScriptSerializer().Deserialize<AlertThresholds>(ReportHelpers.Load(StorageAccount, "Configuration.AlertThresholds.json", ContainerName));
var fragmentationDetails = dbExecutor.Query<DatabaseIndex>(string.Format(sqlQueryForIndexFragmentation,thresholdValues.DatabaseIndexFragmentationPercentErrorThreshold));
var json = new JavaScriptSerializer().Serialize(fragmentationDetails);
ReportHelpers.CreateBlob(StorageAccount, "DBIndexFragmentation.json", ContainerName, "application/json", ReportHelpers.ToStream(json));
}
}
}
public void SetUpRunsCreationSql()
{
var creationSql = @"
CREATE TABLE JustTesting (
Id INT IDENTITY NOT NULL,
Answer INT NOT null
)
INSERT INTO JustTesting (Answer) VALUES (42)
";
var application = new SqlExecutor(m_SqlServerConfiguration);
application.SetUpDatabase(creationSql);
var result = application.ExecuteQuery("SELECT Answer FROM JustTesting").ExpectSuccess();
Assert.Equal(42, result.Rows[0].ValueAtIndex<int>(0));
}
public List<DatabaseEvent> RefreshDatabaseEvent()
{
var masterConnectionString = new SqlConnectionStringBuilder(ConnectionString) { InitialCatalog = "master" }.ToString();
var currentDbName = new SqlConnectionStringBuilder(ConnectionString).InitialCatalog;
using (var sqlConnection = new SqlConnection(masterConnectionString))
{
using (var dbExecutor = new SqlExecutor(sqlConnection))
{
sqlConnection.Open();
var usageSeconds = dbExecutor.Query<DatabaseEvent>(string.Format("select start_time, end_time,event_type,event_count,description from sys.event_log where start_time>='{0}' and start_time<='{1}' and database_name = '{2}' and severity = 2", DateTime.UtcNow.AddHours(-LastNHours).ToString("yyyy-MM-dd hh:mm:ss"), DateTime.UtcNow.ToString("yyyy-MM-dd hh:mm:ss"), currentDbName));
return usageSeconds.ToList();
}
}
}
