private static void TestInternalSaveTable(QueryTableModel source)
{
QueryTableModel actualTable = GetTestTableModel();
AvrDbHelper.InvalidateQueryCache(actualTable.QueryId, "en");
Assert.IsFalse(AvrDbHelper.GetQueryCacheId(source.QueryId, "en", false).HasValue);
AvrDbHelper.SaveQueryCache(source);
// manager.CommitTransaction();
long?id = AvrDbHelper.GetQueryCacheId(source.QueryId, "en", false);
Assert.IsTrue(id.HasValue);
QueryTableHeaderDTO resultHeader = AvrDbHelper.GetQueryCacheHeader(id.Value, false, false);
Assert.IsNotNull(resultHeader);
Assert.IsNotNull(resultHeader.BinaryHeader);
Assert.AreEqual(id.Value, resultHeader.QueryCacheId);
Assert.AreEqual(source.BodyPackets.Count, resultHeader.PacketCount);
Assert.AreEqual(source.Header.RowCount, resultHeader.BinaryHeader.RowCount);
AssertAreArrayEqual(source.Header.BinaryBody, resultHeader.BinaryHeader.BinaryBody);
for (int i = 0; i < resultHeader.PacketCount; i++)
{
QueryTablePacketDTO resultPacket = AvrDbHelper.GetQueryCachePacket(id.Value, i);
Assert.IsNotNull(resultPacket);
Assert.IsNotNull(resultPacket.BinaryBody);
QueryTablePacketDTO sourcePacket = source.BodyPackets[i];
Assert.IsNotNull(sourcePacket);
Assert.IsNotNull(sourcePacket.BinaryBody);
Assert.AreEqual(sourcePacket.RowCount, resultPacket.RowCount);
AssertAreArrayEqual(sourcePacket.BinaryBody, resultPacket.BinaryBody);
}
}
private static QueryTableHeaderModel GetQueryTableHeaderModel()
{
QueryTableModel tableModel = AvrDbHelper.GetQueryResult(49539640000000, "en", false);
var zippedHeader = new QueryTableHeaderDTO(tableModel.Header, 1, tableModel.BodyPackets.Count);
var original = new QueryTableHeaderModel(zippedHeader);
return(original);
}
public QueryTableHeaderDTO GetConcreteCachedQueryTableHeader(long queryCacheId, long queryId, string lang, bool isArchive)
{
QueryTableHeaderDTO header = AvrDbHelper.GetQueryCacheHeader(queryCacheId, false, isArchive);
return(header.PacketCount > 0
? header
: GetInternalCachedQueryTableHeader(queryId, false, lang, isArchive));
}
public void GetConcreteCachedQueryAndProcessTest()
{
const long queryId = 49539640000000;
const string lang = "en";
var facade = new AVRFacade(m_Container);
QueryTableHeaderDTO header = facade.GetConcreteCachedQueryTableHeader(0, queryId, lang, false);
Assert.IsTrue(header.PacketCount > 0);
}
public AVRFacadeStub(long queryCacheId, int multiplier = 1)
{
QueryTableModel tableModel;
using (DbManagerProxy manager = DbManagerFactory.Factory.Create())
{
using (DbManager command = manager.SetCommand(@"select * from dbo.AVR_HumanCaseReport"))
{
tableModel = BinarySerializer.SerializeFromCommand(command, 123, "en", false, 10);
}
}
m_ZippedBody = tableModel.BodyPackets.Select(BinaryCompressor.Zip).ToList();
m_ZippedHeader = new QueryTableHeaderDTO(BinaryCompressor.Zip(tableModel.Header), queryCacheId, m_ZippedBody.Count * multiplier);
}
public void TestGetNonExistingTable()
{
Assert.IsNull(AvrDbHelper.GetQueryCacheId(1234, "en", false));
Assert.IsNull(AvrDbHelper.GetQueryCacheId(new QueryCacheKey(1234, "en", false)));
QueryTableHeaderDTO header = AvrDbHelper.GetQueryCacheHeader(1234, false, false);
QueryTablePacketDTO packet = AvrDbHelper.GetQueryCachePacket(2310, 0);
Assert.IsNotNull(header);
Assert.IsNotNull(header.BinaryHeader);
Assert.AreEqual(0, header.PacketCount);
Assert.AreEqual(1234, header.QueryCacheId);
Assert.AreEqual(0, header.BinaryHeader.RowCount);
Assert.AreEqual(0, header.BinaryHeader.BinaryBody.Length);
Assert.AreEqual(0, packet.RowCount);
Assert.AreEqual(0, packet.BinaryBody.Length);
}
public void TestExecuteArchiveQuery()
{
QueryTableModel tableModel = AvrDbHelper.GetQueryResult(49539640000000, "en", true);
Assert.IsNotNull(tableModel);
IList <QueryTablePacketDTO> packets = tableModel.BodyPackets;
Assert.IsNotNull(packets);
Assert.IsNotNull(tableModel.Header);
Assert.AreEqual("en", tableModel.Language);
Assert.AreEqual(49539640000000, tableModel.QueryId);
Assert.IsTrue(packets.Count >= 1 * 2);
Assert.IsTrue(packets.Any(p => p.IsArchive));
Assert.IsTrue(packets.Any(p => !p.IsArchive));
var zippedHeader = new QueryTableHeaderDTO(tableModel.Header, 1, packets.Count);
var headerModel = new QueryTableHeaderModel(zippedHeader);
Assert.AreEqual(m_FieldCount, headerModel.ColumnCount);
}
public void TestSaveDeleteFullArchiveTable()
{
QueryTableModel actualTable = GetTestTableModel();
AvrDbHelper.InvalidateQueryCache(actualTable.QueryId, "en");
Assert.IsFalse(AvrDbHelper.GetQueryCacheId(actualTable.QueryId, "en", false).HasValue);
AvrDbHelper.SaveQueryCache(actualTable);
Assert.IsNotNull(AvrDbHelper.GetQueryCacheId(actualTable.QueryId, "en", false));
Assert.IsNull(AvrDbHelper.GetQueryCacheId(actualTable.QueryId, "en", true));
QueryTableModel archiveTable = GetTestTableModel(true);
AvrDbHelper.SaveQueryCache(archiveTable);
long?actualId = AvrDbHelper.GetQueryCacheId(archiveTable.QueryId, "en", false);
long?archiveId = AvrDbHelper.GetQueryCacheId(archiveTable.QueryId, "en", false);
Assert.IsTrue(actualId.HasValue);
Assert.IsTrue(archiveId.HasValue);
QueryTableHeaderDTO archiveHeader = AvrDbHelper.GetQueryCacheHeader(archiveId.Value, false, true);
QueryTableHeaderDTO actualHeader = AvrDbHelper.GetQueryCacheHeader(actualId.Value, false, false);
Assert.AreEqual(archiveHeader.BinaryHeader.BinaryBody.Length, actualHeader.BinaryHeader.BinaryBody.Length);
Assert.AreEqual(1, actualHeader.PacketCount);
Assert.AreEqual(2, archiveHeader.PacketCount);
Assert.IsFalse(AvrDbHelper.GetQueryCachePacket(archiveId.Value, 0).IsArchive);
Assert.IsTrue(AvrDbHelper.GetQueryCachePacket(archiveId.Value, 1).IsArchive);
AvrDbHelper.InvalidateQueryCache(actualTable.QueryId, "en");
Assert.IsNull(AvrDbHelper.GetQueryCacheId(actualTable.QueryId, "en", false));
Assert.IsNull(AvrDbHelper.GetQueryCacheId(actualTable.QueryId, "en", true));
Assert.IsNotNull(AvrDbHelper.GetQueryCacheId(actualTable.QueryId, "en", false, 7, true));
Assert.IsNotNull(AvrDbHelper.GetQueryCacheId(actualTable.QueryId, "en", true, 7, true));
Assert.AreNotEqual(0, AvrDbHelper.GetQueryCacheHeader(archiveId.Value, false, false).BinaryHeader.BinaryBody.Length);
Assert.AreNotEqual(0, AvrDbHelper.GetQueryCacheHeader(archiveId.Value, false, true).BinaryHeader.BinaryBody.Length);
Assert.AreNotEqual(0, AvrDbHelper.GetQueryCachePacket(archiveId.Value, 0).BinaryBody.Length);
}
private QueryTableHeaderDTO GetInternalCachedQueryTableHeader(long queryId, bool isSchedulerCall, string lang, bool isArchive)
{
try
{
string queryName = AvrDbHelper.GetQueryNameForLog(queryId);
Stopwatch watch = TraceMethodCall(queryId, queryName, lang, isArchive);
EidssAvrServiceInitializer.CheckAndInitEidssCore();
var cacheKey = new QueryCacheKey(queryId, lang, isArchive);
long?id = AvrDbHelper.GetQueryCacheId(cacheKey, RefreshedCacheOnUserCallAfterDays);
if (!id.HasValue)
{
bool needExecute;
lock (m_CacheSyncLock)
{
needExecute = !m_QueryCacheList.Contains(cacheKey);
if (needExecute)
{
m_QueryCacheList.Add(cacheKey);
if (!m_QueryCacheSyncLock.ContainsKey(cacheKey))
{
m_QueryCacheSyncLock.Add(cacheKey, new object());
}
if (!m_QueryCacheErrors.ContainsKey(cacheKey))
{
m_QueryCacheErrors.Add(cacheKey, false);
}
}
}
lock (m_QueryCacheSyncLock[cacheKey])
{
try
{
if (needExecute)
{
try
{
id = AvrDbHelper.GetQueryCacheId(cacheKey, RefreshedCacheOnUserCallAfterDays);
if (!id.HasValue)
{
QueryTableModel tableModel = AvrDbHelper.GetQueryResult(queryId, lang, isArchive);
id = AvrDbHelper.SaveQueryCache(tableModel);
}
}
finally
{
lock (m_CacheSyncLock)
{
m_QueryCacheErrors[cacheKey] = !id.HasValue;
m_QueryCacheList.Remove(cacheKey);
}
}
}
else
{
while (true)
{
lock (m_CacheSyncLock)
{
if (!m_QueryCacheList.Contains(cacheKey))
{
break;
}
}
Monitor.Wait(m_QueryCacheSyncLock[cacheKey]);
}
lock (m_CacheSyncLock)
{
if (m_QueryCacheErrors[cacheKey])
{
string message = EidssMessages.Get("msgCouldNotGetQueryCacheHeaderGeneral",
"Could not get header of query cashe table. For detail see previous exception logged");
throw new AvrDataException(message);
}
}
id = AvrDbHelper.GetQueryCacheId(cacheKey, RefreshedCacheOnUserCallAfterDays, true);
}
}
finally
{
Monitor.PulseAll(m_QueryCacheSyncLock[cacheKey]);
}
}
}
if (!id.HasValue)
{
string msg = EidssMessages.Get("msgCouldNotGetQueryCacheId", "Could not get query cashe ID. See log for more details.");
throw new AvrDataException(msg);
}
QueryTableHeaderDTO header = AvrDbHelper.GetQueryCacheHeader(id.Value, isSchedulerCall, isArchive);
TraceMethodCallFinished(watch, queryId, queryName, lang, isArchive);
return(header);
}
catch (Exception ex)
{
m_Trace.TraceMethodException(ex, Utils.GetCurrentMethodName(), m_TraceTitle, queryId, lang);
string format = EidssMessages.Get("msgCouldNotGetQueryCacheHeader",
"Could not get header of query cashe table. Query ID={0}, Language={1}");
throw new AvrDataException(String.Format(format, queryId, lang), ex);
}
}
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);
}
