public async Task <ConceptTree> GetTreetopAsync()
{
log.LogInformation("Getting root Concepts and PanelFilters");
using (var cn = new SqlConnection(opts.ConnectionString))
{
await cn.OpenAsync();
var grid = await cn.QueryMultipleAsync(
queryRootsPanelFilters,
new { user = user.UUID, groups = GroupMembership.From(user), admin = user.IsAdmin },
commandTimeout : opts.DefaultTimeout,
commandType : CommandType.StoredProcedure
);
var roots = HydratedConceptReader.Read(grid);
var filterRecords = grid.Read <PanelFilterRecord>();
var filters = filterRecords.Select(f => f.ToPanelFilter());
return(new ConceptTree
{
PanelFilters = filters,
Concepts = roots
});
}
}
public async Task <IEnumerable <IDatasetQuery> > GetDatasetQueriesAsync()
{
using (var cn = new SqlConnection(opts.ConnectionString))
{
await cn.OpenAsync();
var grid = await cn.QueryMultipleAsync(
CRUDQuery.getDatasetQueries,
new { user = user.UUID, groups = GroupMembership.From(user), admin = user.IsAdmin },
commandType : CommandType.StoredProcedure,
commandTimeout : opts.DefaultTimeout
);
var records = grid.Read <DatasetQueryRecord>();
var map = records.ToDictionary(d => d.Id.Value, d => d.DatasetQuery());
var tags = grid.Read <DatasetQueryTag>();
foreach (var tag in tags)
{
if (map.TryGetValue(tag.Id.Value, out var datasetQuery))
{
datasetQuery.Tags.Add(tag.Tag);
}
}
return(map.Values);
}
}
public async Task <Concept> GetAsync(Guid id)
{
log.LogInformation("Getting Concept. Id:{Id}", id);
using (var cn = new SqlConnection(opts.ConnectionString))
{
await cn.OpenAsync();
try
{
var grid = await cn.QueryMultipleAsync(
querySingle,
new
{
id,
user = user.UUID,
groups = GroupMembership.From(user),
admin = user.IsAdmin
},
commandTimeout : opts.DefaultTimeout,
commandType : CommandType.StoredProcedure
);
return(HydratedConceptReader.Read(grid).FirstOrDefault());
}
catch (SqlException se)
{
log.LogError("Could not get concept by Id. Id:{Id} Error:{Error}", id, se.Message);
se.MapThrow();
throw;
}
}
}
public async Task <Query> GetQueryAsync(QueryUrn uid)
{
using (var cn = new SqlConnection(dbOpts.ConnectionString))
{
await cn.OpenAsync();
var r = await cn.QueryFirstOrDefaultAsync <QueryRecord>(
queryQueryByUId,
new { uid = uid.ToString(), user = user.UUID, groups = GroupMembership.From(user), admin = user.IsAdmin },
commandType : CommandType.StoredProcedure,
commandTimeout : dbOpts.DefaultTimeout
);
return(new Query
{
Id = r.Id,
UniversalId = QueryUrn.From(r.UniversalId),
Name = r.Name,
Category = r.Category,
Owner = r.Owner,
Created = r.Created,
Updated = r.Updated,
Count = r.Count,
Definition = r.Definition
});
}
}
public async Task <IEnumerable <Concept> > GetChildrenAsync(Guid parentId)
{
log.LogInformation("Getting child concepts. ParentId:{ParentId}", parentId);
using (var cn = new SqlConnection(opts.ConnectionString))
{
await cn.OpenAsync();
try
{
var grid = await cn.QueryMultipleAsync(
queryChildren,
new
{
parentId,
user = user.UUID,
groups = GroupMembership.From(user),
admin = user.IsAdmin
},
commandTimeout : opts.DefaultTimeout,
commandType : CommandType.StoredProcedure
);
return(HydratedConceptReader.Read(grid));
}
catch (SqlException se)
{
log.LogError("Could not get child concepts. ParentId:{ParentId} Error:{Error}", parentId, se.Message);
se.MapThrow();
throw;
}
}
}
public async Task <IEnumerable <BaseQuery> > GetQueries()
{
logger.LogInformation("Getting queries");
using (var cn = new SqlConnection(dbOpts.ConnectionString))
{
await cn.OpenAsync();
var records = await cn.QueryAsync <BaseQueryRecord>(
queryQueries,
new { user = user.UUID, groups = GroupMembership.From(user) },
commandType : CommandType.StoredProcedure,
commandTimeout : dbOpts.DefaultTimeout
);
return(records.Select(r => new BaseQuery
{
Id = r.Id,
UniversalId = QueryUrn.From(r.UniversalId),
Name = r.Name,
Category = r.Category,
Owner = r.Owner,
Created = r.Created,
Updated = r.Updated,
Count = r.Count
}));
}
}
public async Task <IEnumerable <Concept> > GetWithParentsAsync(HashSet <Guid> ids)
{
log.LogInformation("Getting parent concepts for Ids:{Ids}", ids);
using (var cn = new SqlConnection(opts.ConnectionString))
{
await cn.OpenAsync();
try
{
var grid = await cn.QueryMultipleAsync(
queryParents,
new
{
ids = ResourceIdTable.From(ids),
user = user.UUID,
groups = GroupMembership.From(user),
admin = user.IsAdmin
},
commandTimeout : opts.DefaultTimeout,
commandType : CommandType.StoredProcedure
);
return(HydratedConceptReader.Read(grid));
}
catch (SqlException se)
{
log.LogError("Could not get rooted concepts of children ids {Ids}. Error:{Error}", ids, se.Message);
se.MapThrow();
throw;
}
}
}
public async Task <ImportMetadata> UpdateImportMetadataAsync(ImportMetadata metadata)
{
using (var cn = new SqlConnection(dbOptions.ConnectionString))
{
var grid = await cn.QueryMultipleAsync(
Sql.UpdateImportMetadata,
new
{
id = metadata.Id,
sourceId = metadata.SourceId,
type = metadata.Type,
structure = metadata.StructureJson,
constraints = ResourceConstraintTable.From(metadata),
user = user.UUID,
groups = GroupMembership.From(user),
admin = user.IsAdmin
},
commandType : CommandType.StoredProcedure,
commandTimeout : dbOptions.DefaultTimeout
);
var updated = DbReader.Read(grid);
return(updated);
}
}
public async Task <PreflightResources> GetResourcesByIdsAsync(ResourceRefs refs)
{
using (var cn = new SqlConnection(opts.ConnectionString))
{
await cn.OpenAsync();
var qids = refs.Queries.Select(q => q.Id.Value);
var cids = refs.Concepts.Select(c => c.Id.Value);
var iids = refs.Imports.Select(i => i.Id.Value);
var grid = await cn.QueryMultipleAsync(
ResourcePreflightSql.byIds,
new {
qids = ResourceIdTable.From(qids),
cids = ResourceIdTable.From(cids),
iids = ResourceIdTable.From(iids),
user = user.UUID,
groups = GroupMembership.From(user),
sessionType = user.SessionType,
admin = user.IsAdmin
},
commandTimeout : opts.DefaultTimeout,
commandType : CommandType.StoredProcedure
);
return(PreflightReader.ReadResourcesById(grid, refs));
}
}
public async Task <PreflightResources> GetResourcesByUniversalIdsAsync(ResourceRefs refs)
{
using (var cn = new SqlConnection(opts.ConnectionString))
{
await cn.OpenAsync();
var quids = refs.Queries.Select(q => q.UniversalId.ToString()).ToHashSet();
var cuids = refs.Concepts.Select(q => q.UniversalId.ToString()).ToHashSet();
var grid = await cn.QueryMultipleAsync(
ResourcePreflightSql.byUIds,
new {
quids = ResourceUniversalIdTable.From(quids),
cuids = ResourceUniversalIdTable.From(cuids),
user = user.UUID,
groups = GroupMembership.From(user),
sessionType = user.SessionType,
admin = user.IsAdmin
},
commandTimeout : opts.DefaultTimeout,
commandType : CommandType.StoredProcedure
);
return(PreflightReader.ReadResourcesByUId(grid, refs.Queries));
}
}
async Task <PreflightConcepts> GetByIdAsync(SqlConnection cn, Guid conceptId)
{
var grid = await cn.QueryMultipleAsync(
ConceptPreflightSql.singleId,
new { id = conceptId, user = user.UUID, groups = GroupMembership.From(user), admin = user.IsAdmin },
commandTimeout : opts.DefaultTimeout,
commandType : CommandType.StoredProcedure
);
return(PreflightReader.ReadConcepts(grid));
}
async Task <PreflightConcepts> GetAsync(SqlConnection cn, HashSet <Guid> ids)
{
var grid = await cn.QueryMultipleAsync(
queryManyIds,
new { ids = ResourceIdTable.From(ids), user = user.UUID, groups = GroupMembership.From(user), admin = user.IsAdmin },
commandTimeout : opts.DefaultTimeout,
commandType : CommandType.StoredProcedure
);
return(PreflightReader.ReadConcepts(grid));
}
public async Task <IEnumerable <ImportMetadata> > GetAllImportMetadataAsync()
{
using (var cn = new SqlConnection(dbOptions.ConnectionString))
{
var grid = await cn.QueryMultipleAsync(
Sql.GetAllMetadata,
new { user = user.UUID, groups = GroupMembership.From(user), admin = user.IsAdmin },
commandType : CommandType.StoredProcedure,
commandTimeout : dbOptions.DefaultTimeout
);
var metas = DbReader.ReadMany(grid);
return(metas);
}
}
public async Task <IEnumerable <Concept> > GetRootsAsync()
{
using (var cn = new SqlConnection(opts.ConnectionString))
{
await cn.OpenAsync();
var grid = await cn.QueryMultipleAsync(
queryRoots,
new { user = user.UUID, groups = GroupMembership.From(user), admin = user.IsAdmin },
commandTimeout : opts.DefaultTimeout,
commandType : CommandType.StoredProcedure
);
return(HydratedConceptReader.Read(grid));
}
}
public async Task <PreflightConcepts> GetConceptsByUniversalIdAsync(Urn universalId)
{
using (var cn = new SqlConnection(opts.ConnectionString))
{
await cn.OpenAsync();
var conceptId = universalId.ToString();
var grid = await cn.QueryMultipleAsync(
ConceptPreflightSql.singleId,
new { id = conceptId, user = user.UUID, groups = GroupMembership.From(user), admin = user.IsAdmin },
commandTimeout : opts.DefaultTimeout,
commandType : CommandType.StoredProcedure
);
return(PreflightReader.ReadConcepts(grid));
}
}
async Task <PanelDatasetCompilerContext> ByQueryId(QueryRef queryRef, int panelIdx)
{
log.LogInformation("Getting PanelDatasetCompilerContext by QueryId");
var queryid = queryRef.Id.Value;
using (var cn = new SqlConnection(opts.ConnectionString))
{
await cn.OpenAsync();
var grid = await cn.QueryMultipleAsync(
contextById,
new { queryid, user = user.UUID, groups = GroupMembership.From(user), admin = user.IsAdmin },
commandType : CommandType.StoredProcedure,
commandTimeout : opts.DefaultTimeout
);
return(ReadContextGrid(grid, panelIdx));
}
}
async Task <DemographicCompilerContext> ByQueryUId(QueryRef queryRef)
{
log.LogInformation("Getting DemographicQueryCompilerContext by QueryUId");
var queryuid = queryRef.UniversalId.ToString();
using (var cn = new SqlConnection(opts.ConnectionString))
{
await cn.OpenAsync();
var grid = await cn.QueryMultipleAsync(
contextByUId,
new { queryuid, user = user.UUID, groups = GroupMembership.From(user), admin = user.IsAdmin },
commandType : CommandType.StoredProcedure,
commandTimeout : opts.DefaultTimeout
);
return(ReadContextGrid(grid));
}
}
async Task <PanelDatasetCompilerContext> ByQueryUIdConceptId(ConceptDatasetExecutionRequest request, ConceptRef conceptRef)
{
log.LogInformation("Getting ConceptDatasetCompilerContext by QueryUId and ConceptId");
var queryuid = request.QueryRef.UniversalId.ToString();
var conceptid = conceptRef.Id.Value;
using (var cn = new SqlConnection(opts.ConnectionString))
{
await cn.OpenAsync();
var grid = await cn.QueryMultipleAsync(
contextByQueryUIdConceptId,
new { queryuid, conceptid, user = user.UUID, groups = GroupMembership.From(user), admin = user.IsAdmin },
commandType : CommandType.StoredProcedure,
commandTimeout : opts.DefaultTimeout
);
return(ReadContextGrid(request, grid));
}
}
public async Task <IEnumerable <ImportRecord> > GetImportDataAsync(Guid id)
{
using (var cn = new SqlConnection(dbOptions.ConnectionString))
{
var changed = await cn.QueryAsync <ImportRecord>(
Sql.GetImportData,
new
{
id,
user = user.UUID,
groups = GroupMembership.From(user),
admin = user.IsAdmin
},
commandType : CommandType.StoredProcedure,
commandTimeout : dbOptions.DefaultTimeout
);
return(changed);
}
}
async Task <DatasetCompilerContext> ByDatasetIdQueryId(DatasetExecutionRequest request)
{
log.LogInformation("Getting DatasetQueryCompilerContext by DatasetId and QueryId");
var datasetid = request.DatasetRef.Id.Value;
var queryid = request.QueryRef.Id.Value;
using (var cn = new SqlConnection(opts.ConnectionString))
{
await cn.OpenAsync();
var grid = await cn.QueryMultipleAsync(
ContextQuery.byDatasetIdQueryId,
new { datasetid, queryid, user = user.UUID, groups = GroupMembership.From(user), admin = user.IsAdmin },
commandType : CommandType.StoredProcedure,
commandTimeout : opts.DefaultTimeout
);
return(ReadRequestGrid(grid, request));
}
}
public async Task <DataImporter.IImportDataResult> ImportDataAsync(Guid id, IEnumerable <ImportRecord> records)
{
using (var cn = new SqlConnection(dbOptions.ConnectionString))
{
var changed = await cn.QueryFirstOrDefaultAsync <Result>(
Sql.ImportData,
new
{
id,
data = ImportDataTable.From(id, records),
user = user.UUID,
groups = GroupMembership.From(user),
admin = user.IsAdmin
},
commandType : CommandType.StoredProcedure,
commandTimeout : dbOptions.DefaultTimeout
);
return(changed);
}
}
public async Task <ImportMetadata> GetImportMetadataAsync(Guid id)
{
using (var cn = new SqlConnection(dbOptions.ConnectionString))
{
var grid = await cn.QueryMultipleAsync(
Sql.GetMetadataById,
new
{
id,
user = user.UUID,
groups = GroupMembership.From(user),
admin = user.IsAdmin
},
commandType : CommandType.StoredProcedure,
commandTimeout : dbOptions.DefaultTimeout
);
var metadata = DbReader.Read(grid);
return(metadata);
}
}
public async Task <IEnumerable <Concept> > GetAsync(HashSet <string> universalIds)
{
using (var cn = new SqlConnection(opts.ConnectionString))
{
await cn.OpenAsync();
var grid = await cn.QueryMultipleAsync(
queryManyUniversal,
new
{
uids = ResourceUniversalIdTable.From(universalIds),
user = user.UUID,
groups = GroupMembership.From(user),
admin = user.IsAdmin
},
commandTimeout : opts.DefaultTimeout,
commandType : CommandType.StoredProcedure
);
return(HydratedConceptReader.Read(grid));
}
}
public async Task <Concept> GetAsync(Guid id)
{
using (var cn = new SqlConnection(opts.ConnectionString))
{
await cn.OpenAsync();
var grid = await cn.QueryMultipleAsync(
querySingle,
new
{
id,
user = user.UUID,
groups = GroupMembership.From(user),
admin = user.IsAdmin
},
commandTimeout : opts.DefaultTimeout,
commandType : CommandType.StoredProcedure
);
return(HydratedConceptReader.Read(grid).FirstOrDefault());
}
}
public async Task <PreflightConcepts> GetConceptsByUniversalIdsAsync(HashSet <string> conceptUids)
{
using (var cn = new SqlConnection(opts.ConnectionString))
{
await cn.OpenAsync();
var grid = await cn.QueryMultipleAsync(
ConceptPreflightSql.manyUIds,
new {
uids = ResourceUniversalIdTable.From(conceptUids),
user = user.UUID,
groups = GroupMembership.From(user),
admin = user.IsAdmin,
sessionType = user.SessionType
},
commandTimeout : opts.DefaultTimeout,
commandType : CommandType.StoredProcedure
);
return(PreflightReader.ReadConcepts(grid));
}
}
public async Task <IEnumerable <Concept> > GetWithParentsBySearchTermAsync(Guid?rootId, string[] terms)
{
using (var cn = new SqlConnection(opts.ConnectionString))
{
await cn.OpenAsync();
var grid = await cn.QueryMultipleAsync(
queryParentsBySearchTerm,
new
{
terms = SearchTermTable.From(terms),
rootId,
user = user.UUID,
groups = GroupMembership.From(user),
admin = user.IsAdmin
},
commandTimeout : opts.DefaultTimeout,
commandType : CommandType.StoredProcedure
);
return(HydratedConceptReader.Read(grid));
}
}
/// <summary>
/// Search the concept tree for records matching the provided terms, and stopping at the <paramref name="rootId"/>
/// </summary>
/// <returns>Collection of concepts that match the search terms</returns>
/// <param name="rootId">Root parent identifier.</param>
/// <param name="terms">Search terms.</param>
public async Task <IEnumerable <ConceptHint> > SearchAsync(Guid?rootId, params string[] terms)
{
using (var cn = new SqlConnection(opts.ConnectionString))
{
await cn.OpenAsync();
var records = await cn.QueryAsync <ConceptHintRecord>(
Sql.Query,
new
{
terms = SearchTermTable.From(terms),
rootId,
user = user.UUID,
groups = GroupMembership.From(user),
admin = user.IsAdmin
},
commandTimeout : opts.DefaultTimeout,
commandType : CommandType.StoredProcedure
);
return(records.Select(r => r.ConceptHint()));
}
}
public async Task <IEnumerable <Concept> > GetAsync(HashSet <Guid> ids)
{
log.LogInformation("Getting Concepts. Ids:{Ids}", ids);
using (var cn = new SqlConnection(opts.ConnectionString))
{
await cn.OpenAsync();
var grid = await cn.QueryMultipleAsync(
queryMany,
new
{
ids = ResourceIdTable.From(ids),
user = user.UUID,
groups = GroupMembership.From(user),
admin = user.IsAdmin
},
commandTimeout : opts.DefaultTimeout,
commandType : CommandType.StoredProcedure
);
return(HydratedConceptReader.Read(grid));
}
}
public async Task <Query> GetQuery(QueryUrn uid)
{
logger.LogInformation("Getting query UId:{UId}", uid);
using (var cn = new SqlConnection(dbOpts.ConnectionString))
{
await cn.OpenAsync();
try
{
var r = await cn.QueryFirstOrDefaultAsync <QueryRecord>(
queryQueryByUId,
new { uid = uid.ToString(), user = user.UUID, groups = GroupMembership.From(user) },
commandType : CommandType.StoredProcedure,
commandTimeout : dbOpts.DefaultTimeout
);
return(new Query
{
Id = r.Id,
UniversalId = QueryUrn.From(r.UniversalId),
Name = r.Name,
Category = r.Category,
Owner = r.Owner,
Created = r.Created,
Updated = r.Updated,
Count = r.Count,
Definition = r.Definition
});
}
catch (SqlException se)
{
logger.LogError("Could not get query. UniversalId:{UniversalId} Code:{Code} Error:{Error}", uid, se.ErrorCode, se.Message);
se.MapThrow();
throw;
}
}
}
async Task <PreflightResources> GetByIdsAsync(SqlConnection cn, ResourceRefs refs)
{
var qids = refs.Queries.Select(q => q.Id.Value);
var cids = refs.Concepts.Select(c => c.Id.Value);
var grid = await cn.QueryMultipleAsync(
ResourcePreflightSql.byIds,
new { qids = ResourceIdTable.From(qids), cids = ResourceIdTable.From(cids), user = user.UUID, groups = GroupMembership.From(user), admin = user.IsAdmin },
commandTimeout : opts.DefaultTimeout,
commandType : CommandType.StoredProcedure
);
return(PreflightReader.ReadResourcesByUId(grid, refs.Queries));
}