private IDatabaseExpression GetFilterString(IDatabaseExpression sql)
{
var groupFilter = (this.Library.IsGroupingEnabled)
? DatabaseExpression.Or(
DatabaseExpression.IsTrue(nameof(Record.IsGroup)),
DatabaseExpression.IsNull(nameof(Record.GroupKey)))
: DatabaseExpression.IsFalse(nameof(Record.IsGroup));
var filter = (sql != null)
? DatabaseExpression.And(groupFilter, sql)
: groupFilter;
return(filter);
}
/// <summary>
/// 指定タグを含まないアイテムをカウント
/// </summary>
/// <param name="connection"></param>
/// <param name="items"></param>
/// <param name="bufferLength"></param>
/// <param name="filter"></param>
/// <returns></returns>
private async Task <long> CountItemsWithoutTag
(IDbConnection connection, IEnumerable <string> items, int bufferLength, IDatabaseExpression filter)
{
foreach (var ids in items.Buffer(bufferLength))
{
var param = new Tuple <string[]>(ids.ToArray());
var woTag = await this.Table.CountAsync(connection, filter, param);
if (woTag > 0)
{
return(woTag);
}
}
return(0);
}
/// <summary>
/// 指定検索条件下でのインデックスを調べる
/// </summary>
/// <param name="criteria"></param>
/// <param name="target"></param>
/// <returns></returns>
private async Task <long> FindIndexMainAsync
(IDbConnection connection, ISearchCriteria criteria, IDatabaseExpression filterSql, Record target)
{
var sql = SortSetting.GetReferenceSelectorSql(criteria.GetSort());
var reference = await this.Records
.GetDynamicParametersAsync(connection, sql, target)
.ConfigureAwait(false);
if (reference == null)
{
return(-1);
}
var filter = DatabaseExpression.And(filterSql,
SortSetting.GetOrderFilterSql(criteria.GetSort()));
return((await this.Records.CountAsync(connection, filter, reference).ConfigureAwait(false)) - 1);
}
/// <summary>
/// 検索
/// </summary>
/// <param name="filter"></param>
/// <param name="sort"></param>
/// <param name="skip"></param>
/// <param name="take"></param>
/// <returns></returns>
public async Task <Record[]> SearchMainAsync
(IDatabaseExpression filter, string[] sort, long skip, long take, object param = null)
{
Record[] records = null;
using (var connection = await this.Database.ConnectAsync().ConfigureAwait(false))
{
records = await this.Records
.AsQueryable(connection)
.Where(filter)
.OrderBy(sort.Append(FileProperty.Id.ToSort(false)).ToArray())
.Skip(skip)
.Take(take)
.ToArrayAsync(param)
.ConfigureAwait(false);
}
foreach (var item in records)
{
this.RecordTracker.Track(item);
}
return(records);
}
/// <summary>
/// Remove a lot of records
/// </summary>
/// <param name="connection"></param>
/// <param name="ids"></param>
public async Task RemoveRangeBufferedWithFilter
(IDbConnection connection, IEnumerable <TKey> ids, IDatabaseExpression filter)
{
foreach (var buffer in ids.Distinct().Buffer(this.BufferSize))
{
using (var transaction = connection.BeginTransaction())
{
try
{
var param = new Tuple <TKey[]>(buffer.ToArray());
await connection.ExecuteAsync
($"DELETE FROM {this.Name} WHERE (({IdName} IN @{nameof(param.Item1)}) AND {filter.GetSql()})",
param, transaction).ConfigureAwait(false);
transaction.Commit();
}
catch
{
transaction.Rollback();
}
}
}
}
public async Task <string[]> GetRegionIdsMainAsync
(IDbConnection connection, ISearchCriteria criteria, long index1, long index2, IDatabaseExpression filter)
{
if ((index1 < 0 && index2 < 0) ||
(index1 < 0 || index1 == index2) ||
(index2 < 0))
{
return(null);
}
var startIndex = index1;
var endIndex = index2;
//var startRecord = record1;
//var endRecord = record2;
if (index2 < index1)
{
startIndex = index2;
endIndex = index1;
//startRecord = record2;
//endRecord = record1;
}
if (index2 - index1 == 1)
{
return(null);
}
var sort = SortSetting.GetFullSql(criteria.GetSort());
return(await this.Records
.AsQueryable(connection)
.Where(filter)
.OrderBy(sort.Append(FileProperty.Id.ToSort(false)).ToArray())
.Select <string>(nameof(Record.Id))
.Skip(startIndex + 1)
.Take(endIndex - startIndex - 1)
.ToArrayAsync()
.ConfigureAwait(false));
}
public Task <TResult> MinAsync <TResult>
(IDbConnection connection, string column, IDatabaseExpression whereSql, object parameter = null)
{
return(connection
.ExecuteScalarAsync <TResult>($@"SELECT MIN({column}) FROM {this.Name} WHERE {whereSql.GetSql()}", parameter));
}
/// <summary>
/// Count records with parameter
/// </summary>
/// <param name="connection"></param>
/// <param name="sql"></param>
/// <param name="parameter"></param>
/// <returns></returns>
public Task <long> CountAsync(IDbConnection connection, IDatabaseExpression sql, object parameter)
{
return(connection
.ExecuteScalarAsync <long>($@"SELECT COUNT({IdName}) FROM {this.Name} WHERE {sql.GetSql()}", parameter));
}
/// <summary>
/// Count records
/// </summary>
/// <param name="connection"></param>
/// <param name="sql"></param>
/// <returns></returns>
public long Count(IDbConnection connection, IDatabaseExpression sql)
{
return(connection
.ExecuteScalar <long>($@"SELECT COUNT({IdName}) FROM {this.Name} WHERE {sql.GetSql()}"));
}
public TableQueryable <T> Where(IDatabaseExpression sql)
{
this.WhereSql = sql.GetSql();
return(this);
}
