public override async Task <long> CreateAsync(Document document, CancellationToken cancellationToken)
{
// TODO: cache
var schema = (await FieldStore.FindAllAsync(cancellationToken)).ToDictionary(field => field.Id);
var dynFields = document.Fields.Where(prop => schema[prop.Key].Type.GetSqlDbType() != null).Select(prop => $"{prop.Key}").ToList();
var dynColNames = string.Join("", dynFields.Select(dynField => $",[{dynField}]"));
var dynParamNames = string.Join("", dynFields.Select(dynField => $",@{dynField}"));
var parameters = new List <SqlParameter>
{
Connection.CreateCommandParameter("@CreatedDate", SqlDbType.DateTimeOffset, DateTime.UtcNow),
Connection.CreateCommandParameter("@ModifiedDate", SqlDbType.DateTimeOffset, DateTime.UtcNow),
Connection.CreateCommandParameter("@ParentId", SqlDbType.NVarChar, document.ParentId ?? string.Empty),
};
parameters.AddRange(DocumentHelpers.CreateCommandParameters(document, schema));
var cmdText = $@"INSERT INTO [Document] (
[CreatedDate],
[ModifiedDate],
[ParentId]
{dynColNames}
) VALUES (
@CreatedDate,
@ModifiedDate,
@ParentId
{dynParamNames}
);
SELECT SCOPE_IDENTITY();";
var result = await Connection !.ExecuteScalarAsync(Connection.CreateCommand(cmdText, parameters), cancellationToken);
var id = decimal.ToInt64((decimal)result);
return(id);
}
// TODO: add parameter to define view (e.g.: Id, OwnerId, DynProp1, DynProp4)
public override async Task <IList <Document> > FindAllAsync(DocumentQueryOptions options, CancellationToken cancellationToken)
{
// TODO: cache
var schema = (await FieldStore.FindAllAsync(cancellationToken)).Where(field => !options.FieldNames.Any() || options.FieldNames.Contains(field.Id)).ToDictionary(field => field.Id);
var dynColNames = string.Join("", schema.Keys.Select(dynField => $",[{dynField}]"));
var dynParamNames = string.Join("", schema.Keys.Select(dynField => $",@{dynField}"));
var whereClauses = new List <string>();
var cmdParameters = new List <SqlParameter>();
if (options.Ids.Any())
{
var idsString = string.Join(',', options.Ids);
whereClauses.Add(" Id IN (SELECT convert(int, value) FROM string_split(@ids, ',')) ");
cmdParameters.Add(Connection.CreateCommandParameter("@ids", SqlDbType.NVarChar, idsString));
}
if (options.ParentIds.Any())
{
var parentIdsString = string.Join(',', options.ParentIds);
whereClauses.Add(" ParentId IN (SELECT value FROM string_split(@parentIds, ',')) ");
cmdParameters.Add(Connection.CreateCommandParameter("@parentIds", SqlDbType.NVarChar, parentIdsString));
}
var orderClause = $" ORDER BY {(string.IsNullOrEmpty(options.OrderBy) ? "Id" : options.OrderBy)} {(options.OrderAsc ? string.Empty : "DESC")}";
var skipClause = options.Offset > 0 || options.Limit > 0 ? $" OFFSET {options.Offset} ROWS " : string.Empty;
var takeClause = options.Limit > 0 ? $" FETCH NEXT {options.Limit} ROWS ONLY " : string.Empty;
var cmdText = $@"SELECT [Id],
[CreatedDate],
[ModifiedDate],
[ParentId]
{dynColNames}
FROM [Document]
{(whereClauses.Any() ? $" WHERE {string.Join(" AND ", whereClauses)}" : string.Empty)}
{orderClause} {skipClause} {takeClause}";
var documents = await Connection !.ExecuteQueryAsync(Connection.CreateCommand(cmdText, cmdParameters), (sqlReader) => DocumentHelpers.ReadDocuments(sqlReader, schema.Values), cancellationToken);
return(documents);
}
public override async Task <IDictionary <OptionalValue <object?>, int> > FieldUsageStatsAsync(Field field, CancellationToken cancellationToken)
{
var cmdText = $@"SELECT DISTINCT [{field.Id}], COUNT(1)
FROM [Document]
GROUP BY [{field.Id}]";
var fieldUsageStats = await Connection !.ExecuteQueryAsync(Connection.CreateCommand(cmdText), (sqlReader) => DocumentHelpers.ReadFieldUsageStats(sqlReader, field), cancellationToken);
return(fieldUsageStats);
}
public override async Task <Dictionary <string, int> > CountsByParentsAsync(CancellationToken cancellationToken)
{
var cmdText = $@"SELECT ParentId, COUNT(1)
FROM Document
GROUP BY ParentId";
var countByFolders = await Connection !.ExecuteQueryAsync(Connection.CreateCommand(cmdText), (sqlReader) => DocumentHelpers.ReadCountsByParents(sqlReader), cancellationToken);
return(countByFolders);
}