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());
}
}
