到目前为止，对于一个有点奇怪的sql查询，我有两个简单的测试。有两件事我不明白。我的第一个测试没有插入任何数据并运行查询，我希望 if (reader.HasRows) 返回false（确实如此），并且它正确地返回0。第二个测试我插入2条记录，一条是“付费”的，另一条不是（一秒钟后会更有意义）。我只希望看到付款行的总和，但什么也得不到。问题是它是一个对时间敏感的查询，会改变时区。我不知道为什么会这样，但我不能改变这个事实。因为这和工作有关，我得改变一下。易趣模拟器听起来是个很好的例子。
在mysql中创建一个简单的表
CREATE TABLE `order` (
`id` int(11) unsigned NOT NULL,
`Total` decimal(8,2) DEFAULT NULL,
`OrderDate` datetime(6) DEFAULT NULL,
`Paid` tinyint(3) unsigned NOT NULL,
`Deleted` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`),
);现在用nunit做两个简单的测试
[TestFixture]
public class DbAccountingStatisticsTests
{
private readonly DateTime TodayStartOrDay = DateTime.Today;
private readonly DateTime TodayEndOfDay = DateTime.Today.AddDays(1).AddTicks(-1);
private LoggingAssert _logging;
private DeferredMultiTableDatabaseActions _dbActions;
private static readonly TimeZoneInfo Pst = TimeZoneInfo.FindSystemTimeZoneById("Pacific Standard Time");
private static readonly TimeZoneInfo Est = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");
[SetUp]
public void Setup()
{
_logging = LoggingAssert.Setup();
_dbActions = new DeferredMultiTableDatabaseActions();
}
[TearDown]
public void TearDown()
{
_dbActions.SubmitChanges();
_logging.FailIfAnyLoggedErrors();
}
[Test]
public void WithZeroShippedOrders()
{
var db = new DbAccountingStatistics();
var stats = db.GetOrderStats(
TimeZoneInfo.ConvertTime(TodayStartOrDay, Pst),
TimeZoneInfo.ConvertTime(TodayEndOfDay, Pst)
);
Assert.That(stats.NumberofOrders, Is.Zero);
Assert.That(stats.TotalOrders, Is.Zero);
}
[Test]
public void NonPaidOrdersNotIncluded()
{
AddOrder(orderTotal: 100, paid: false);
AddOrder(orderTotal: 100, paid: true);
var db = new DbAccountingStatistics();
var stats = db.GetOrderStats(
TimeZoneInfo.ConvertTime(TodayStartOrDay, Est),
TimeZoneInfo.ConvertTime(TodayEndOfDay, Est)
);
Assert.That(stats.NumberofOrders, Is.EqualTo(1));
Assert.That(stats.TotalOrders, Is.EqualTo(100));
}
private void AddOrder(decimal orderTotal, bool paid)
{
var db = new DbOrder();
var id = db.Insert(new PocoOrder
{
Paid = paid,
Total = orderTotal,
OrderDate = DateTime.Today//TimeZoneInfo.ConvertTime(DateTime.Today, Est)
});
_logging.FailIfAnyLoggedErrors();
_dbActions.AddSimpleChange($"delete from order where id = {id}");
}
}现在被测试的类和给我带来麻烦的sql
public class DbAccountingStatistics
{
private readonly DeferredMultiTableDatabaseActions _dataAdapter = new DeferredMultiTableDatabaseActions();
private const string SqlGetOrderStats =
"SELECT count(id) as orderNum, sum(total) as Total " +
"FROM order " +
"WHERE Date(CONVERT_TZ(OrderDate,'US/Eastern','US/Pacific')) >= @STARTDATE " +
"AND Date(CONVERT_TZ(OrderDate,'US/Eastern','US/Pacific')) <= @ENDDATE " +
"AND Deleted = 0 and paid = 1";
public PocoAccountingStatistics GetOrderStats(DateTime startDate, DateTime endDate)
{
var stats = new PocoAccountingStatistics();
_dataAdapter.AddDeferredAction(new DeferredAction<PocoAccountingStatistics>
{
Sql = SqlGetOrderStats,
SqlParameters = new Dictionary<string, object>
{
{"@STARTDATE", startDate},
{"@ENDDATE", endDate},
},
ParseAction = FillOrderStats
});
_dataAdapter.Fill(stats);
return stats;
}
private void FillOrderStats(PocoAccountingStatistics stats, IDataRecord record)
{
var orderCountOrdinal = record.GetOrdinal("orderNum");
var totalOrdinal = record.GetOrdinal("Total");
stats.NumberofOrders = (long)record[orderCountOrdinal];
stats.TotalOrders = record.IsDBNull(totalOrdinal)? 0 : record.GetDecimal(totalOrdinal);
}
}
public class PocoAccountingStatistics
{
public long NumberofOrders { get; set; }
public decimal TotalOrders { get; set; }
}
public class DeferredAction
{
public string Sql { get; set; }
public Dictionary<string, object> SqlParameters { get; set; }
}
public class DeferredAction<T> : DeferredAction
{
public Action<T, IDataRecord> ParseAction { get; set; }
}
public class DeferredMultiTableDatabaseActions
{
private static readonly Logger logger = LogManager.GetLogger(nameof(DeferredMultiTableDatabaseActions));
private readonly List<DeferredAction> _deferredActions = new List<DeferredAction>();
public void AddSimpleChange(string sql)
{
_deferredActions.Add(new DeferredAction
{
Sql = sql,
});
}
public void AddDeferredAction(DeferredAction deferredAction)
{
_deferredActions.Add(deferredAction);
}
public void SubmitChanges()
{
using (var connection = new BfConnection().DbConnection)
{
var db = new Database(connection);
connection.Open();
foreach (var clown in _deferredActions)
{
using (var command = db.GetCommand(clown.Sql))
{
var rowsAffected = db.ExecuteNonQuery(command);
if(rowsAffected < 1)
logger.Error($"Expected atleast 1 rows to be affected for statment '{clown.Sql}'");
}
}
}
}
public void Fill<T>(T poco)
{
using (var connection = new BfConnection().DbConnection)
{
var db = new Database(connection);
connection.Open();
foreach (var deferredAction in _deferredActions)
{
using (var command = db.GetCommand(deferredAction.Sql))
{
foreach (var parameter in deferredAction.SqlParameters)
AddParameter(command, parameter.Key, parameter.Value);
using (var reader = db.ExecuteReader(command))
{
if (!reader.HasRows)
continue;
if (reader.Read())
((DeferredAction<T>) deferredAction).ParseAction(poco, reader);
}
}
}
}
}
private static void AddParameter(DbCommand command, string name, object value)
{
var parameter = command.CreateParameter();
parameter.ParameterName = name;
parameter.Value = value;
command.Parameters.Add(parameter);
}
}我第一次做这个测试的时候 DbNull 因为我的时区没有在本地设置，所以我去了mysql的网站，得到了这个设置。然后我研究了 Convert_TZ 所有的一切都在改变 OrderDate 从est到pst并与我的两个参数进行比较。在测试中，我将日期插入为pst、est和我的当前时间（cst）。我也改变了测试转换时间和不转换时间。每次我得到0表示总数和计数。我不明白为什么 HasRows 如果where子句与不插入任何内容相比是错误的，则在插入两条记录时为true HasRows 返回false。我已经和它斗争了几个小时，即使在睡觉后，我仍然不能找出什么是错的。
暂无答案！
目前还没有任何答案，快来回答吧！