private static void UpdateDateCacheRequest(long query)
{
using (var avrTran = new AvrDbTransaction())
{
DbManagerProxy manager = avrTran.Manager;
DbManager command = manager.SetCommand(
@"update dbo.QueryCache set datQueryCacheRequest = '2010-01-01' where idfQuery = @idflQuery",
manager.Parameter("idflQuery", query)
);
command.ExecuteNonQuery();
avrTran.CommitTransaction();
}
}
private static void UpdateQueryRefreshDate(long?id, int days)
{
using (var avrTran = new AvrDbTransaction())
{
DbManagerProxy manager = avrTran.Manager;
DbManager command = manager.SetCommand(
@" update QueryCache
set datQueryRefresh = DATEADD(day, @intDays, GETDATE())
where [idfQueryCache] = @idflQueryCache",
manager.Parameter("idflQueryCache", id),
manager.Parameter("intDays", days)
);
command.ExecuteNonQuery();
avrTran.CommitTransaction();
}
}
private static object GetDateUserQueryCacheRequest(long?id)
{
object datUserQueryCacheRequest;
using (var avrTran = new AvrDbTransaction())
{
DbManagerProxy manager = avrTran.Manager;
DbManager command = manager.SetCommand(
@" select datUserQueryCacheRequest from QueryCache
where [idfQueryCache] = @idflQueryCache",
manager.Parameter("idflQueryCache", id)
);
datUserQueryCacheRequest = command.ExecuteScalar();
}
return(datUserQueryCacheRequest);
}
public void TestGetUserQueryRefershDateHeader()
{
QueryTableModel table = GetTestTableModel();
AvrDbHelper.SaveQueryCache(table);
var queryId = table.QueryId;
long?id = AvrDbHelper.GetQueryCacheId(queryId, "en", false);
Assert.IsTrue(id.HasValue);
using (var avrTran = new AvrDbTransaction())
{
DbManagerProxy manager = avrTran.Manager;
DbManager command = manager.SetCommand(
@" update QueryCache
set datUserQueryCacheRequest = NULL
where [idfQuery] = @idflQuery",
manager.Parameter("idflQuery", queryId)
);
command.ExecuteNonQuery();
avrTran.CommitTransaction();
}
var date = AvrDbHelper.GetsQueryCacheUserRequestDate(queryId);
Assert.IsFalse(date.HasValue);
AvrDbHelper.GetQueryCacheHeader(id.Value, true, false);
date = AvrDbHelper.GetsQueryCacheUserRequestDate(queryId);
Assert.IsFalse(date.HasValue);
AvrDbHelper.GetQueryCacheHeader(id.Value, false, false);
date = AvrDbHelper.GetsQueryCacheUserRequestDate(queryId);
Assert.IsTrue(date.HasValue);
}
public void TestExistingTable()
{
using (var avrTran = new AvrDbTransaction())
{
DbManagerProxy manager = avrTran.Manager;
DbManager command = manager.SetCommand(
@" DELETE FROM [dbo].[QueryCachePacket]
where [idfQueryCache] = @idflQueryCache
and ([idfQueryCachePacket] = @idflQueryCachePacket1 or [idfQueryCachePacket] = @idflQueryCachePacket2)
DELETE FROM [dbo].[QueryCache]
where [idfQueryCache] = @idflQueryCache
and [strLanguage] = @strLanguage",
manager.Parameter("idflQueryCache", 1),
manager.Parameter("strLanguage", "en"),
manager.Parameter("idflQueryCachePacket1", 1),
manager.Parameter("idflQueryCachePacket2", 2)
);
command.ExecuteNonQuery();
avrTran.CommitTransaction();
}
Assert.IsNull(AvrDbHelper.GetQueryCacheId(2, "en", false));
Assert.AreEqual(0, AvrDbHelper.GetQueryCacheHeader(1, false, false).BinaryHeader.BinaryBody.Length);
Assert.AreEqual(0, AvrDbHelper.GetQueryCachePacket(1, 0).BinaryBody.Length);
var header = new byte[10000];
for (int i = 0; i < header.Length; i++)
{
header[i] = (byte)i;
}
var packet = new byte[20000];
for (int i = 0; i < packet.Length; i++)
{
packet[i] = (byte)(i + 10);
}
using (var avrTran = new AvrDbTransaction())
{
DbManagerProxy manager = avrTran.Manager;
DbManager command = manager.SetCommand(
@"
SET IDENTITY_INSERT [dbo].[QueryCachePacket] OFF
SET IDENTITY_INSERT [dbo].[QueryCache] ON
INSERT INTO [dbo].[QueryCache]
([idfQueryCache],[idfQuery],[strLanguage],[blbQuerySchema],[intQueryColumnCount],[datQueryRefresh],[datQueryCacheRequest],[blnUseArchivedData],[blnActualQueryCache])
VALUES (@idflQueryCache, @idflQuery, @strLanguage, @binHeaderCache, @intHeaderRowCount, GETDATE(), GETDATE(), 1, 0)
SET IDENTITY_INSERT [dbo].[QueryCache] OFF
SET IDENTITY_INSERT [dbo].[QueryCachePacket] ON
INSERT INTO [dbo].[QueryCachePacket]
([idfQueryCachePacket],[idfQueryCache],[intQueryCachePacketNumber],[blbQueryCachePacket], [intTableRowCount],[blnArchivedData])
VALUES(@idflQueryCachePacket1, @idflQueryCache, 0, @binPacketCache, @intPacketRowCount, 0)
INSERT INTO [dbo].[QueryCachePacket]
([idfQueryCachePacket],[idfQueryCache],[intQueryCachePacketNumber],[blbQueryCachePacket], [intTableRowCount],[blnArchivedData])
VALUES(@idflQueryCachePacket2, @idflQueryCache, 1, @binPacketCache, @intPacketRowCount, 0)
SET IDENTITY_INSERT [dbo].[QueryCachePacket] OFF",
manager.Parameter("idflQueryCache", 1),
manager.Parameter("idflQuery", 2),
manager.Parameter("strLanguage", "en"),
manager.Parameter("idflQueryCachePacket1", 1),
manager.Parameter("idflQueryCachePacket2", 2),
manager.Parameter("binHeaderCache", header),
manager.Parameter("binPacketCache", packet),
manager.Parameter("intHeaderRowCount", 10),
manager.Parameter("intPacketRowCount", 100)
);
command.ExecuteNonQuery();
avrTran.CommitTransaction();
}
Assert.AreEqual(1, AvrDbHelper.GetQueryCacheId(2, "en", false, 7, true));
Assert.AreEqual(1, AvrDbHelper.GetQueryCacheId(new QueryCacheKey(2, "en", true), 7, true));
QueryTableHeaderDTO resultHeader = AvrDbHelper.GetQueryCacheHeader(1, false, false);
Assert.AreEqual(10, resultHeader.BinaryHeader.RowCount);
Assert.AreEqual(2, resultHeader.PacketCount);
Assert.AreEqual(1, resultHeader.QueryCacheId);
AssertAreArrayEqual(new ChunkByteArray(header), resultHeader.BinaryHeader.BinaryBody);
Assert.AreEqual(20000, AvrDbHelper.GetQueryCachePacket(1, 0).BinaryBody.Length);
Assert.AreEqual(20000, AvrDbHelper.GetQueryCachePacket(1, 1).BinaryBody.Length);
Assert.AreEqual(0, AvrDbHelper.GetQueryCachePacket(1, 2).BinaryBody.Length);
QueryTablePacketDTO resultPacket = AvrDbHelper.GetQueryCachePacket(1, 0);
Assert.AreEqual(100, resultPacket.RowCount);
AssertAreArrayEqual(new ChunkByteArray(packet), resultPacket.BinaryBody);
DateTime dateTime = AvrDbHelper.GetQueryRefreshDateTime(1, "en");
Assert.IsTrue(DateTime.Now.Subtract(dateTime).Seconds < 2);
AvrDbHelper.InvalidateQueryCache(2, "en");
Assert.IsNull(AvrDbHelper.GetQueryCacheId(2, "en", false));
Assert.IsNotNull(AvrDbHelper.GetQueryCacheId(2, "en", false, 7, true));
Assert.AreNotEqual(0, AvrDbHelper.GetQueryCacheHeader(1, false, false).BinaryHeader.BinaryBody.Length);
Assert.AreNotEqual(0, AvrDbHelper.GetQueryCachePacket(1, 0).BinaryBody.Length);
AvrDbHelper.DeleteQueryCache(2, "en", true);
Assert.IsNull(AvrDbHelper.GetQueryCacheId(2, "en", false));
Assert.IsNotNull(AvrDbHelper.GetQueryCacheId(2, "en", false, 7, true));
}
public void TestArchiveExistingTable()
{
using (var avrTran = new AvrDbTransaction())
{
DbManagerProxy manager = avrTran.Manager;
DbManager command = manager.SetCommand(
@" DELETE FROM [dbo].[QueryCachePacket]
where [idfQueryCache] = @idflQueryCache
and ([idfQueryCachePacket] = @idflQueryCachePacket1 or [idfQueryCachePacket] = @idflQueryCachePacket2)
DELETE FROM [dbo].[QueryCache]
where [idfQueryCache] = @idflQueryCache
and [strLanguage] = @strLanguage",
manager.Parameter("idflQueryCache", 1),
manager.Parameter("strLanguage", "en"),
manager.Parameter("idflQueryCachePacket1", 1),
manager.Parameter("idflQueryCachePacket2", 2)
);
command.ExecuteNonQuery();
avrTran.CommitTransaction();
}
Assert.IsNull(AvrDbHelper.GetQueryCacheId(2, "en", false));
Assert.AreEqual(0, AvrDbHelper.GetQueryCacheHeader(1, false, false).BinaryHeader.BinaryBody.Length);
Assert.AreEqual(0, AvrDbHelper.GetQueryCachePacket(1, 0).BinaryBody.Length);
var header = new byte[10000];
var packet = new byte[20000];
using (var avrTran = new AvrDbTransaction())
{
DbManagerProxy manager = avrTran.Manager;
DbManager command = manager.SetCommand(
@"
SET IDENTITY_INSERT [dbo].[QueryCachePacket] OFF
SET IDENTITY_INSERT [dbo].[QueryCache] ON
INSERT INTO [dbo].[QueryCache]
([idfQueryCache],[idfQuery],[strLanguage],[blbQuerySchema],[intQueryColumnCount],[datQueryRefresh],[datQueryCacheRequest],[blnUseArchivedData],[blnActualQueryCache])
VALUES (@idflQueryCache, @idflQuery, @strLanguage, @binHeaderCache, @intHeaderRowCount, GETDATE(), GETDATE(), 1, 1)
SET IDENTITY_INSERT [dbo].[QueryCache] OFF
SET IDENTITY_INSERT [dbo].[QueryCachePacket] ON
INSERT INTO [dbo].[QueryCachePacket]
([idfQueryCachePacket],[idfQueryCache],[intQueryCachePacketNumber],[blbQueryCachePacket], [intTableRowCount],[blnArchivedData])
VALUES(@idflQueryCachePacket1, @idflQueryCache, 0, @binPacketCache, @intPacketRowCount, 0)
INSERT INTO [dbo].[QueryCachePacket]
([idfQueryCachePacket],[idfQueryCache],[intQueryCachePacketNumber],[blbQueryCachePacket], [intTableRowCount],[blnArchivedData])
VALUES(@idflQueryCachePacket2, @idflQueryCache, 1, @binPacketCache, @intPacketRowCount, 1)
SET IDENTITY_INSERT [dbo].[QueryCachePacket] OFF",
manager.Parameter("idflQueryCache", 1),
manager.Parameter("idflQuery", 2),
manager.Parameter("strLanguage", "en"),
manager.Parameter("idflQueryCachePacket1", 1),
manager.Parameter("idflQueryCachePacket2", 2),
manager.Parameter("binHeaderCache", header),
manager.Parameter("binPacketCache", packet),
manager.Parameter("intHeaderRowCount", 10),
manager.Parameter("intPacketRowCount", 100)
);
command.ExecuteNonQuery();
avrTran.CommitTransaction();
}
Assert.AreEqual(1, AvrDbHelper.GetQueryCacheId(2, "en", false));
QueryTableHeaderDTO resultHeader = AvrDbHelper.GetQueryCacheHeader(1, false, false);
Assert.AreEqual(10, resultHeader.BinaryHeader.RowCount);
Assert.AreEqual(1, resultHeader.PacketCount);
Assert.AreEqual(1, resultHeader.QueryCacheId);
Assert.AreEqual(false, resultHeader.BinaryHeader.IsArchive);
Assert.AreEqual(1, AvrDbHelper.GetQueryCacheId(2, "en", true));
resultHeader = AvrDbHelper.GetQueryCacheHeader(1, false, true);
Assert.AreEqual(10, resultHeader.BinaryHeader.RowCount);
Assert.AreEqual(2, resultHeader.PacketCount);
Assert.AreEqual(1, resultHeader.QueryCacheId);
Assert.AreEqual(true, resultHeader.BinaryHeader.IsArchive);
QueryTablePacketDTO resultPacket1 = AvrDbHelper.GetQueryCachePacket(1, 0);
Assert.AreEqual(20000, resultPacket1.BinaryBody.Length);
Assert.AreEqual(false, resultPacket1.IsArchive);
QueryTablePacketDTO resultPacket2 = AvrDbHelper.GetQueryCachePacket(1, 1);
Assert.AreEqual(20000, resultPacket2.BinaryBody.Length);
Assert.AreEqual(true, resultPacket2.IsArchive);
}
public void GetCachedMultiThreadQueryTest()
{
using (var avrTran = new AvrDbTransaction())
{
DbManager command =
avrTran.Manager.SetCommand(
@" delete from dbo.ViewCachePacket delete from dbo.ViewCache delete from dbo.QueryCachePacket delete from dbo.QueryCache");
command.ExecuteNonQuery();
}
var facade = new AVRFacade(m_Container);
//fn_AVR_HumanCaseReport
Func <int, string, QueryTableHeaderDTO> createDataTable = (timeout, lang) =>
{
Thread.Sleep(timeout);
try
{
return(facade.GetCachedQueryTableHeader(49539640000000, lang, false));
}
catch (Exception ex)
{
Console.WriteLine(ex);
return(new QueryTableHeaderDTO());
}
};
Action <int, string> invalidate = (timeout, lang) =>
{
Thread.Sleep(timeout);
try
{
facade.InvalidateQueryCacheForLanguage(49539640000000, lang);
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
};
Func <QueryTableHeaderDTO> getHeader = () =>
{
try
{
return(facade.GetCachedQueryTableHeader(49540070000000, "en", false));
}
catch (Exception ex)
{
Console.WriteLine(ex);
return(new QueryTableHeaderDTO());
}
};
var runningTasks = new List <Task <QueryTableHeaderDTO> >();
// TODO: [ivan] make call of 100 tasks, but no more than 10 of them should run simultaneously
for (int i = 0; i < 10; i++)
{
int tmp = i;
Task.Factory.StartNew(() => invalidate(10 * tmp, "en"));
runningTasks.Add(Task.Factory.StartNew(() => createDataTable(tmp, "en")));
Task.Factory.StartNew(() => invalidate(10 * tmp, "ru"));
runningTasks.Add(Task.Factory.StartNew(() => createDataTable(tmp, "ru")));
Task.Factory.StartNew(getHeader);
}
Task.WaitAll(runningTasks.Select(t => (Task)t).ToArray());
Task <QueryTableHeaderDTO> taskEn = runningTasks[0];
Assert.IsTrue(taskEn.Result.PacketCount > 0);
Assert.IsTrue(taskEn.Result.QueryCacheId > 0);
Assert.IsTrue(taskEn.Result.BinaryHeader.RowCount > 0);
Task <QueryTableHeaderDTO> taskRu = runningTasks[1];
Assert.IsTrue(taskRu.Result.PacketCount > 0);
Assert.IsTrue(taskRu.Result.QueryCacheId > 0);
Assert.IsTrue(taskRu.Result.BinaryHeader.RowCount > 0);
}
