public async Task Test2(TimeSpan offset) { //if (offset != DateTimeOffset.Now.Offset) { DatabaseFunction.SetDateOffset(offset); } using (var connection = this.database.Connect()) { this.table1.Drop(connection); await this.database.InitializeAsync(connection); } //同じ時間が復元されるか //異なるオフセット //localタイムゾーンで取り出して同じ時間を指しているか //1970年より前 using (var connection = this.database.Connect()) { var record = new Table1 { Id = Guid.NewGuid().ToString(), DateOffset = new DateTimeOffset(1800, 1, 2, 3, 4, 5, TimeSpan.FromHours(-3.5)), Date = new DateTime(1800, 6, 7, 8, 9, 10, DateTimeKind.Local), DateOffsetNull = new DateTimeOffset(1800, 1, 2, 3, 4, 5, TimeSpan.FromHours(-13)), DateNull = new DateTime(1800, 6, 7, 8, 9, 10, DateTimeKind.Local), }; using (var tr = connection.BeginTransaction()) { await table1.ReplaceAsync(record, connection, tr); tr.Commit(); } var r2 = await table1.GetRecordFromKeyAsync(connection, record.Id); this.AreEqual(record.DateOffset.ToLocalTime().ToString(), r2.DateOffset.ToString()); this.AreEqual(record.Date.ToLocalTime().ToString(), r2.Date.ToString()); this.AreEqual(record.DateOffsetNull?.ToLocalTime().ToString(), r2.DateOffsetNull?.ToString()); this.AreEqual(record.DateNull?.ToLocalTime().ToString(), r2.DateNull.ToString()); } //3000年 using (var connection = this.database.Connect()) { var record = new Table1 { Id = Guid.NewGuid().ToString(), DateOffset = new DateTimeOffset(3900, 1, 2, 3, 4, 5, TimeSpan.FromHours(3.5)), Date = new DateTime(3900, 6, 7, 8, 9, 10, DateTimeKind.Local), DateOffsetNull = new DateTimeOffset(3900, 11, 12, 13, 14, 15, TimeSpan.FromHours(14)), DateNull = new DateTime(3900, 6, 17, 18, 19, 20, DateTimeKind.Local), }; using (var tr = connection.BeginTransaction()) { await table1.ReplaceAsync(record, connection, tr); tr.Commit(); } var r2 = await table1.GetRecordFromKeyAsync(connection, record.Id); this.AreEqual(record.DateOffset.ToLocalTime().ToString(), r2.DateOffset.ToString()); this.AreEqual(record.Date.ToLocalTime().ToString(), r2.Date.ToString()); this.AreEqual(record.DateOffsetNull?.ToLocalTime().ToString(), r2.DateOffsetNull?.ToString()); this.AreEqual(record.DateNull?.ToLocalTime().ToString(), r2.DateNull.ToString()); } //null //recordで入れてrecordで取り出す //recordで入れてlong?で取り出す using (var connection = this.database.Connect()) { var record = new Table1 { Id = Guid.NewGuid().ToString(), DateOffsetNull = null, DateNull = null, }; using (var tr = connection.BeginTransaction()) { await table1.ReplaceAsync(record, connection, tr); tr.Commit(); } var r2 = await table1.GetRecordFromKeyAsync(connection, record.Id); this.AreEqual(record.DateOffset.ToLocalTime().ToString(), r2.DateOffset.ToString()); this.AreEqual(record.Date.ToLocalTime().ToString(), r2.Date.ToString()); Assert.IsNull(r2.DateOffsetNull); Assert.IsNull(r2.DateNull); this.AreEqual(0L, (await table1.QueryAsync <long> (connection, $"Select DateOffset FROM {table1.Name} WHERE Id=='{record.Id}'")).First()); this.AreEqual(0L, (await table1.QueryAsync <long> (connection, $"Select Date FROM {table1.Name} WHERE Id=='{record.Id}'")).First()); Assert.IsNull((await table1.QueryAsync <long?> (connection, $"Select DateOffsetNull FROM {table1.Name} WHERE Id=='{record.Id}'")).First()); Assert.IsNull((await table1.QueryAsync <long?> (connection, $"Select DateNull FROM {table1.Name} WHERE Id=='{record.Id}'")).First()); } //現在地の日付で検索 //前日23:59 当日0:00 3:00 12:00 20:00 23:59 翌日0:00 using (var connection = this.database.Connect()) { //var offset = DateTimeOffset.Now.Offset; var records = new[] { new Table1 { DateOffset = new DateTimeOffset(2200, 4, 30, 23, 59, 59, offset) }, new Table1 { DateOffset = new DateTimeOffset(2200, 5, 1, 0, 0, 0, offset) }, new Table1 { DateOffset = new DateTimeOffset(2200, 5, 1, 3, 30, 10, offset) }, new Table1 { DateOffset = new DateTimeOffset(2200, 5, 1, 12, 55, 30, offset) }, new Table1 { DateOffset = new DateTimeOffset(2200, 5, 1, 20, 44, 2, offset) }, new Table1 { DateOffset = new DateTimeOffset(2200, 5, 1, 23, 59, 59, offset) }, new Table1 { DateOffset = new DateTimeOffset(2200, 5, 2, 0, 0, 0, offset) }, } .Select(x => { x.Id = Guid.NewGuid().ToString(); return(x); }) .ToArray(); using (var tr = connection.BeginTransaction()) { await table1.ReplaceRangeAsync(records, connection, tr); tr.Commit(); } var reference = (offset == DateTimeOffset.Now.Offset) ? DatabaseReference.DateOffsetReference(new DateTimeOffset(2200, 5, 1, 1, 22, 3, offset)) : DatabaseReference.DateOffsetReference(new DateTimeOffset(2200, 5, 1, 1, 22, 3, offset), offset); var r2 = await table1.AsQueryable(connection) .Where(DatabaseExpression.AreEqual(DatabaseFunction.GetDate("DateOffset"), reference)) .OrderBy("DateOffset") .ToArrayAsync(); this.AreEqual(records.Length - 2, r2.Length); foreach (var c in records.Skip(1).Zip(r2, (a, b) => new { a, b })) { this.AreEqual(c.a.Id, c.b.Id); this.AreEqual(c.a.DateOffset.ToString(), c.b.DateOffset.ToOffset(offset).ToString()); } } }
/// <summary> /// 各プロパティによる検索方法の定義 /// </summary> /// <returns></returns> private static Dictionary <int, PropertySearch> MakeSearchDictionary() { var dictionary = new Dictionary <int, PropertySearch>(); dictionary[(int)FileProperty.Id] = new PropertySearch(nameof(Record.Id), true, o => DatabaseReference.ToEqualsString(o)); dictionary[(int)FileProperty.DirectoryPath] = new PropertySearch(DatabaseFunction.ToLower(nameof(Record.Directory)), true, o => DatabaseReference.ToLowerEqualsString(o)); dictionary[(int)FileProperty.DirectoryPathStartsWith] = new PropertySearch(DatabaseFunction.ToLower(nameof(Record.Directory)), false, o => { var str = o.ToString(); var separator = System.IO.Path.DirectorySeparatorChar.ToString(); return((str.EndsWith(separator)) ? DatabaseReference.Match(str) : DatabaseReference.StartsWith(str + separator)); }); dictionary[(int)FileProperty.DirectoryPathContains] = new PropertySearch(DatabaseFunction.ToLower(nameof(Record.Directory)), false, o => DatabaseReference.Contains(o.ToString())); dictionary[(int)FileProperty.FullPath] = new PropertySearch( DatabaseFunction.ToLower (DatabaseFunction.Combine(nameof(Record.Directory), nameof(Record.FileName))), true, o => DatabaseReference.ToLowerEqualsString(o)); dictionary[(int)FileProperty.FileName] = new PropertySearch(true, (o, mode) => { var column = DatabaseFunction.ToLower(nameof(Record.FileName)); if (mode == CompareMode.Equal || mode == CompareMode.NotEqual) { var converted = DatabaseReference.Match(o.ToString()); if (mode == CompareMode.Equal) { return(DatabaseExpression.Is(column, converted)); } else { return(DatabaseExpression.IsNot(column, converted)); } } else { var converted = DatabaseReference.ToLowerEqualsString(o); return(DatabaseExpression.Compare(column, mode, converted)); } }); dictionary[(int)FileProperty.FileNameContains] = new PropertySearch( DatabaseFunction.ToLower(nameof(Record.FileName)), false, o => DatabaseReference.Contains(o.ToString())); dictionary[(int)FileProperty.DateTimeCreated] = new PropertySearch(nameof(Record.DateCreated), true, o => DatabaseReference.DateTimeOffsetReference((DateTimeOffset)o)); dictionary[(int)FileProperty.DateTimeModified] = new PropertySearch(nameof(Record.DateModified), true, o => DatabaseReference.DateTimeOffsetReference((DateTimeOffset)o)); dictionary[(int)FileProperty.DateTimeRegistered] = new PropertySearch(nameof(Record.DateRegistered), true, o => DatabaseReference.DateTimeOffsetReference((DateTimeOffset)o)); dictionary[(int)FileProperty.DateCreated] = new PropertySearch(DatabaseFunction.GetDate(nameof(Record.DateCreated)), true, o => DatabaseReference.DateOffsetReference((DateTimeOffset)o)); dictionary[(int)FileProperty.DateModified] = new PropertySearch(DatabaseFunction.GetDate(nameof(Record.DateModified)), true, o => DatabaseReference.DateOffsetReference((DateTimeOffset)o)); dictionary[(int)FileProperty.DateRegistered] = new PropertySearch(DatabaseFunction.GetDate(nameof(Record.DateRegistered)), true, o => DatabaseReference.DateOffsetReference((DateTimeOffset)o)); dictionary[(int)FileProperty.Width] = new PropertySearch(nameof(Record.Width), true); dictionary[(int)FileProperty.Height] = new PropertySearch(nameof(Record.Height), true); dictionary[(int)FileProperty.Size] = new PropertySearch(nameof(Record.Size), true); dictionary[(int)FileProperty.ContainsTag] = new PropertySearch(nameof(Record.TagEntry), false, o => DatabaseReference.Contains($",{(int)o},")); dictionary[(int)FileProperty.HasTag] = new PropertySearch(false, (o, c) => { var column = DatabaseFunction.Length(nameof(Record.TagEntry)); var mode = c.ContainsEqual() ? CompareMode.GreatEqual : CompareMode.Less; return(DatabaseExpression.Compare(column, mode, new DatabaseReference("2"))); }); dictionary[(int)FileProperty.Rating] = new PropertySearch(nameof(Record.Rating), true, o => new DatabaseReference(RateConvertingHelper.Reverse((int)o).ToString())); dictionary[(int)FileProperty.Group] = new PropertySearch(nameof(Record.GroupKey), true, o => DatabaseReference.ToEqualsString(o)); dictionary[(int)FileProperty.AspectRatio] = new PropertySearch( DatabaseFunction.Divide(nameof(Record.Width), nameof(Record.Height)), true); return(dictionary); }
private async Task TestUnixDate(DateTimeOffset date, IDbConnection connection, TimeSpan offset) { { var dateNum = await table1.QueryAsync <long> (connection, $"Select strftime('%s','{date.ToString("yyyy-MM-dd HH:mm:ss zzz")}')"); this.AreEqual(date.ToUnixTimeSeconds(), dateNum.FirstOrDefault()); } this.AreEqual(date.ToUniversalTime().ToUnixTimeSeconds(), date.ToUnixTimeSeconds()); { var dateNum = await table1.QueryAsync <long> (connection, $"Select strftime('%s','{date.ToString("yyyy-MM-dd HH:mm:ss")}')"); if (date.Offset == default(TimeSpan)) { this.AreEqual(date.ToUnixTimeSeconds(), dateNum.FirstOrDefault()); } else { this.AreNotEqual(date.ToUnixTimeSeconds(), dateNum.FirstOrDefault()); } } { var dateNum = await table1.QueryAsync <long> (connection, $"Select strftime('%s','{date.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss")}')"); this.AreEqual(date.ToUnixTimeSeconds(), dateNum.FirstOrDefault()); } { var unixTime = $"strftime('%s','{date.ToString("yyyy-MM-dd HH:mm:ss zzz")}')"; var unixDate = DatabaseFunction.GetDate(unixTime); var dateNum = await table1.QueryAsync <long> (connection, $"Select {unixDate}"); if (DateTimeOffset.Now.Offset == offset) { this.AreEqual(DatabaseReference.DateOffsetReference(date).ToString(), dateNum.FirstOrDefault().ToString()); } else { this.AreEqual(DatabaseReference.DateOffsetReference(date, offset).ToString(), dateNum.FirstOrDefault().ToString()); } } { var unixTime = $"strftime('%s','{date.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss")}')"; var unixDate = DatabaseFunction.GetDate(unixTime); var dateNum = await table1.QueryAsync <long> (connection, $"Select {unixDate}"); if (DateTimeOffset.Now.Offset == offset) { this.AreEqual(DatabaseReference.DateOffsetReference(date.ToUniversalTime()).ToString(), dateNum.FirstOrDefault().ToString()); } else { this.AreEqual(DatabaseReference.DateOffsetReference(date.ToUniversalTime(), offset).ToString(), dateNum.FirstOrDefault().ToString()); } } { var container = new Table1 { DateOffset = date }; using (var tr = connection.BeginTransaction()) { await table1.ReplaceAsync(container, connection, tr); tr.Commit(); } container.DateOffset = date.AddSeconds(1); var dateNum = await table1.QueryAsync <string> (connection, $"Select DateOffset FROM {table1.Name} WHERE (DateOffset+1) == @DateOffset", container); Assert.AreEqual(1, dateNum.Count()); this.AreEqual(UnixTime.FromDateTime(date).ToString(), dateNum.FirstOrDefault()); } }