public RuleValidationResult Validate(IQueryModel qm)
{
if (string.IsNullOrWhiteSpace(qm.StoreParameters.DataService))
{
return(new RuleValidationResult($"Settings Parameter 'DataService' cannot be empty"));
}
if (string.IsNullOrWhiteSpace(qm.StoreParameters.Namespace))
{
return(new RuleValidationResult($"Settings Parameter 'Namespace' cannot be empty"));
}
if (string.IsNullOrWhiteSpace(qm.StoreParameters.QueryName))
{
return(new RuleValidationResult($"Settings Parameter 'QueryName' cannot be empty"));
}
if (string.IsNullOrWhiteSpace(qm.StoreParameters.QueryReturnType))
{
return(new RuleValidationResult($"Settings Parameter 'QueryReturnType' cannot be empty"));
}
if (string.IsNullOrWhiteSpace(qm.StoreParameters.StoreDataPath))
{
return(new RuleValidationResult($"Settings Parameter 'StoreDataPath' cannot be empty"));
}
return(null);
}
private void ReloadControllerModels(QueryControllerModel fullQuery)
{
StoreParameters = fullQuery.StoreParameters;
MainQuery = fullQuery.MainQuery;
//MainQuery.FromTables = fullQuery.MainQuery.FromTables;
SubQueryList = fullQuery.SubQueryList;
SubQueryList.Insert(0, fullQuery.MainQuery);
//RegenerateTableLinks();
UpdateLinksFromTableJoins();
//MainQuery.SelectionProperties = fullQuery.MainQuery.SelectionProperties;
foreach (var sp in MainQuery.SelectionProperties)
{
// var t = FindFromTable(sp.);
var prop = sp.FromTable.Properties.SingleOrDefault(p => p.Name == sp.StoreProperty.Name);
if (prop != null)
{
prop.Selected = true;
}
}
FullQuery = fullQuery;
//MainQuery.WhereClause = fullQuery.MainQuery.WhereClause;
}
public Task <string> GetConcreteFilmByID(string ID)
{
IQueryModel QueryToFilmDataServer = _filmPlatform.CreateQueryResponseByUd(ID);
string FullUrl = QueryToFilmDataServer.FullQueryString;
return(_requestToFilmServerStrategy.GetResponseByFullQueryStringAsync(FullUrl));
}
public virtual (Result result, IEnumerable <W> models) List(IQueryModel <R, S, T, U, V, W> query, int maxdepth = 1, int top = 0, IListModel <T, U, V, W> models = null)
{
query.Status = "Loading...";
var enumeration = new List <W>();
var list = query.Domain.List(maxdepth, top, (models?.Domains != null ? models?.Domains : new ListDomain <T, U, V>()));
if (list.result.Success && list.domains != null)
{
foreach (var instance in RaiseDomains(list.domains, maxdepth))
{
instance.Status = string.Empty;
enumeration.Add(instance);
}
query.Status = string.Empty;
return(list.result, enumeration);
}
query.Status = list.result.FilteredAsText("/", x => x.category == (x.category & ResultCategory.OnlyErrors));
return(list.result, default(IList <W>));
}
public Task <string> GetListOfFilmsByFilmNameSearchQuery(string filmName, string page)
{
IQueryModel QueryToFilmDataServer = _filmPlatform.CreateQueryResponseByFilmName(filmName, page);
string FullUrl = QueryToFilmDataServer.FullQueryString;
return(_requestToFilmServerStrategy.GetResponseByFullQueryStringAsync(FullUrl));
}
public IEnumerable <TTarget> SelectSingleColumn <TSource, TTarget>(IQueryModel <TSource> query) where TSource : class
{
List <TTarget> result = new List <TTarget>();
using (var transaction = m_transactionManager.Open(true))
{
var hasBuilder = query as IHasBuilder <TSource>;
if (hasBuilder == null)
{
throw new NotSupportedException(
$"Unsupported query type {query.GetType()}. It must implement {typeof(IHasBuilder<TSource>)} interface");
}
using (var reader = ExecuteReader <TSource>(query, hasBuilder.OwnerBuilder, transaction))
{
while (reader.Read())
{
result.Add(reader.Get <TTarget>(0));
}
}
transaction.Commit();
}
return(result);
}
public void CopyFrom(IQueryModel source)
{
IsSubQuery = true;
Name = source.Name;
//StoreDefinition = source;
Properties = source.SelectionProperties.Select(p => new QueryFromProperty(p)).ToList();
}
private IQueryModel EnsureModel()
{
if (_model == null)
{
if (statement is SelectStatement stmt_sel)
{
_model = QueryModelLoader.LoadModel(batchResolver, "roooot", stmt_sel.QueryExpression, stmt_sel.WithCtesAndXmlNamespaces);
}
else
{
DataModificationSpecification source_specification = null;
DataModificationStatement stmt_mod = (DataModificationStatement)statement;
if (statement is InsertStatement stmt_ins)
{
source_specification = stmt_ins.InsertSpecification;
}
else if (statement is UpdateStatement stmt_upd)
{
source_specification = stmt_upd.UpdateSpecification;
}
else if (statement is DeleteStatement stmt_del)
{
source_specification = stmt_del.DeleteSpecification;
}
else if (statement is MergeStatement stmt_mrg)
{
source_specification = stmt_mrg.MergeSpecification;
}
_model = QueryModelLoader.LoadModificationOutputModel(batchResolver, "roooot", source_specification, stmt_mod.WithCtesAndXmlNamespaces);
}
}
return(_model);
}
public override RuleValidationResult Validate(IQueryModel qm)
{
var badJoins = new List <string>();
var joins = qm.FromTableJoins.Where(j => !j.IsDeleted);
foreach (var join in joins)
{
var leftObject = qm.FromTables.First(t => t.Alias == join.Source.LeftObjectAlias);
var leftProp = leftObject.Properties.First(p => p.Name == join.Source.LeftField).OriginalStoreProperty;
var rightObject = qm.FromTables.First(t => t.Alias == join.Source.RightObjectAlias);
var rightProp = rightObject.Properties.First(p => p.Name == join.Source.RightField).OriginalStoreProperty;
if (GetPropertyDataType(leftProp) != GetPropertyDataType(rightProp))
{
badJoins.Add($"{join.Source.GetJoinString()}({leftProp.Type} = {rightProp.Type})");
}
}
if (badJoins.Any())
{
var result = new RuleValidationResult(
GetType().Name,
$"Query '{qm.Name}' has Joins that compare incompatible types: {string.Join(", ", badJoins)}");
return(result);
}
return(null);
}
/// <inheritdoc />
public async Task CreateIndexAsync()
{
IQueryModel instance = (IQueryModel)Activator.CreateInstance(typeof(TIndexModel), true);
string _idx_name = instance.GetIndexName();
IndexName indexName = new IndexName()
{
Name = _idx_name
};
var indexExistsRequest = new IndexExistsRequest(indexName);
var existsResult = await GetClient().IndexExistsAsync(indexExistsRequest);
if (existsResult.Exists)
{
await GetClient().DeleteIndexAsync(indexName);
}
var createIndexResponse = await GetClient().CreateIndexAsync(indexName, c => c
.Mappings(ms => ms
.Map <TIndexModel>(m => m
.AutoMap()))
.Settings(s => s
.Analysis(a => a
.Analyzers(an => an
.Custom("default", ca => ca
.Tokenizer("lowercase")
.Filters("asciifolding"))))));
}
public ISearchResults <object> SearchProducts(IQueryModel model)
{
var query = (ProductsQueryModel)ProductsQueryModel.Build(model);
var results = _products.GetProducts(query);
return(SearchResults <ProductModel> .BuildGeneric(results));
}
public RuleValidationResult Validate(IQueryModel qm)
{
if (!qm.FromTables.Any())
{
return(new RuleValidationResult($"Query '{qm.Name}' From clause should contain at least one table"));
}
return(null);
}
public RuleValidationResult Validate(IQueryModel qm)
{
if (!qm.SelectionProperties.Where(prop => prop.IsOutput).Any())
{
return(new RuleValidationResult("Select clause should contain at list one output property"));
}
return(null);
}
public QueryModel LoadFromStoreQuery(IQueryModel qm, StoreQuery q)
{
var result = new QueryModel();
// read parameters
result.StoreParameters.DataService = q.DataService;
result.StoreParameters.QueryName = q.Name;
result.StoreParameters.Namespace = q.Namespace;
result.StoreParameters.QueryReturnType = q.ReturnTypeName;
result.Schema.Name = q.SchemaName;
result.Schema.Version = q.SchemaVersion;
// from
foreach (var t in q.Query.Tables)
{
var schemaTable = qm.Schema.Definitions[t.Value.TableName];
var ft = new QueryFromTable(schemaTable);
ft.Alias = t.Value.ObjectAlias;
result.FromTables.Add(ft);
}
// qc.RegenerateTableLinks();
// fields
foreach (var f in q.Query.Fields.Values)
{
var table = result.FromTables.FirstOrDefault(t => t.Alias == f.Field.ObjectAlias);
if (table == null)
{
throw new Exception($"Table with alias '{f.Field.ObjectAlias}' not found");
}
var storeProperty = table.Properties.FirstOrDefault(p => p.StoreProperty.Name == f.Field.FieldName);
if (storeProperty == null)
{
throw new Exception($"Field '{f.Field.FieldName}' not found in table with alias '{f.Field.ObjectAlias}'");
}
var newSelectionProperty = new QuerySelectProperty(table, storeProperty.StoreProperty)
{
IsOutput = f.IsOutput,
GroupByFunction = f.GroupByFunction,
Alias = f.FieldAlias != f.Field.FieldName ? f.FieldAlias : ""
// ToDo: Filter is not stored and cannot be loaded
};
result.SelectionProperties.Add(newSelectionProperty);
}
// Where
result.WhereClause = QueryExpressionHelper.QueryExprToString(q.Query.Where.Expression);
return(result);
}
internal QuerySourceOnCte SetCteQuery(IList <Identifier> cteColumns, IQueryModel query)
{
if (cteColumns == null)
{
throw new ArgumentNullException(nameof(_cteColumns));
}
this._cteColumns = cteColumns;
this.query = query;
return(this);
}
private void SetNewQuery()
{
SelectedQueryIndex = 0;
SubQueryList = new List <IQueryModel>();
MainQuery = new QueryModel {
Name = "Main"
};
MainQuery.Schema = Schema;
SubQueryList.Add(MainQuery);
}
public OutputColumnDescriptor ResolveColumnReference(ColumnReferenceExpression node)
{
IQueryModel model = EnsureModel();
if (TryColumnReference(model, node, out QueryColumnBase col))
{
return(ColumnModelToDescriptor(col));
}
throw new NotImplementedException(node.WhatIsThis());
}
public static QueryModel <TModel> Build(IQueryModel <TModel> model)
{
return(new QueryModel <TModel>()
{
UserId = model.UserId,
Fields = model.Fields,
Filters = model.Filters,
Groups = model.Groups,
Skip = model.Skip,
Sort = model.Sort,
Take = model.Take,
Term = model.Term
});
}
public QueryVM(IQueryModel iQueryModel)
{
model = iQueryModel;
ExecuteQueryCommand = new DelegateCommand<string>(ExecuteQuery);
ExportToExcelCommand = new DelegateCommand(ExportToExcel);
GenerateSnippetCommand = new DelegateCommand<string>(GenerateSnippet);
queryTimer = new DispatcherTimer();
queryTimer.Tick += QueryTimer_Tick;
queryTimer.Interval = new TimeSpan(0, 0, 1);
queryWorker = new BackgroundWorker();
queryWorker.DoWork += QueryWorker_DoWork;
queryWorker.RunWorkerCompleted += QueryWorker_RunWorkerCompleted;
queryWorker.WorkerReportsProgress = true;
}
private StoreProperty FindStoreProperty(IQueryModel qc, string expressionField)
{
var split = expressionField.Split('.');
if (split.Count() < 2)
{
return(null);
}
var alias = split[0];
var name = split[1];
var def = qc.FromTables.First(t => t.Alias == alias);
var result = def.StoreDefinition.Properties[name];
return(result);
}
public QueryVM(IQueryModel iQueryModel)
{
model = iQueryModel;
ExecuteQueryCommand = new DelegateCommand <string>(ExecuteQuery);
ExportToExcelCommand = new DelegateCommand(ExportToExcel);
GenerateSnippetCommand = new DelegateCommand <string>(GenerateSnippet);
queryTimer = new DispatcherTimer();
queryTimer.Tick += QueryTimer_Tick;
queryTimer.Interval = new TimeSpan(0, 0, 1);
queryWorker = new BackgroundWorker();
queryWorker.DoWork += QueryWorker_DoWork;
queryWorker.RunWorkerCompleted += QueryWorker_RunWorkerCompleted;
queryWorker.WorkerReportsProgress = true;
}
protected override IDataReader ExecuteReader <T>(IQueryModel <T> model, IQueryBuilder <T> builder, ITransaction <SqlConnection> transaction)
{
var commandText = SqlQueryRenderer.Render(model, builder);
var connection = transaction.GetConnection();
var command = new SqlCommand(commandText, connection);
var parameters = command.Parameters;
foreach (var p in builder.GetParameters())
{
parameters.AddWithValue(p.Key, p.Value);
}
var sqlReader = command.ExecuteReader();
return(new HierarchicSqlDataReader(sqlReader, null));
}
public static IQueryable <T> DoPaging <T>(this IQueryable <T> query, IQueryModel filters)
{
if (filters?.Paging != null)
{
if (filters.Paging.PageNumber < 1)
{
filters.Paging.PageNumber = 1;
}
query = query.Skip((filters.Paging.PageNumber - 1) * filters.Paging.ItemsPerPage);
if (filters.Paging.ItemsPerPage > 0)
{
query = query.Take(filters.Paging.ItemsPerPage);
}
}
return(query);
}
public RuleValidationResult Validate(IQueryModel qm)
{
var fields = qm.SelectionProperties;
var q = from f in fields
where f.IsOutput
group f by f.OutputName into fg
where fg.Count() > 1
select fg.Key;
var notUnique = q.ToList();
if (notUnique.Any())
{
var result = new RuleValidationResult(GetType().Name, $"Query '{qm.Name}' output fields must have unique names, these fields are not unique: {string.Join(", ", notUnique)}");
return(result);
}
return(null);
}
public override RuleValidationResult Validate(IQueryModel qm)
{
if (qm.SelectionProperties.Any(s => s.GroupByFunction == "Group By"))
{
var badOutputs = qm.SelectionProperties.Where(s => s.IsOutput && s.GroupByFunction == "Where").ToList();
if (badOutputs.Any())
{
var badFields = badOutputs.Select(f => f.OutputName);
var result = new RuleValidationResult(
GetType().Name,
$"Query '{qm.Name}' is a Group By query but it has some output fields included in Where clause: {string.Join(", ", badFields)}");
return(result);
}
}
return(null);
}
public override RuleValidationResult Validate(IQueryModel qm)
{
var emptyOutputs = qm.SelectionProperties.Where(s =>
!string.IsNullOrWhiteSpace(s.Having) &&
(string.IsNullOrWhiteSpace(s.GroupByFunction) || s.GroupByFunction == "Where")
).ToList();
if (emptyOutputs.Any())
{
var badFields = emptyOutputs.Select(f => f.OutputName);
var result = new RuleValidationResult(
GetType().Name,
$"Query '{qm.Name}' cannot have Group By Having not included in Group By clause or aggregate functions: {string.Join(", ", badFields)}");
return(result);
}
return(null);
}
public override RuleValidationResult Validate(IQueryModel qm)
{
if (qm.SelectionProperties.Any(s => s.IsOutput && !string.IsNullOrWhiteSpace(s.GroupByFunction)))
{
var emptyOutputs = qm.SelectionProperties.Where(s => s.IsOutput && string.IsNullOrWhiteSpace(s.GroupByFunction)).ToList();
if (emptyOutputs.Any())
{
var emptyFields = emptyOutputs.Select(f => f.OutputName);
var result = new RuleValidationResult(
GetType().Name,
$"Query '{qm.Name}' uses Aggregate functions but it has some output fields not included in Group By clause and without aggregate functions: {string.Join(", ", emptyFields)}");
return(result);
}
}
return(null);
}
private bool TryColumnReference(IQueryModel model, ColumnReferenceExpression node, out QueryColumnBase col)
{
if (node.ColumnType != ColumnType.Regular)
{
throw new NotImplementedException(node.AsText());
}
if (node.MultiPartIdentifier.Count == 2)
{
// source (table/view) name without schema or alias
string sourceNameOrAlias = node.MultiPartIdentifier[0].Dequote();
string columnName = node.MultiPartIdentifier[1].Dequote();
if (model.TryGetQueryOutputColumnByName(this.batchResolver, columnName, out col))
{
return(true);
}
else
{
throw new NotImplementedException(node.WhatIsThis()); // not resolved?
}
}
else if (node.MultiPartIdentifier.Count == 1)
{
// no source only column name => traverse all source and find t
string columnName = node.MultiPartIdentifier[0].Dequote();
if (model.TryGetQueryOutputColumnByName(this.batchResolver, columnName, out col))
{
return(true);
}
else
{
throw new NotImplementedException(node.WhatIsThis()); // not resolved?
}
}
else
{
// 3 or 4
throw new NotImplementedException(node.AsText() + " ## " + statement.WhatIsThis());
}
}
public IEnumerable <T> Select <T>(IQueryModel <T> query) where T : class
{
List <T> result;
using (var transaction = m_transactionManager.Open(true))
{
var hasBuilder = query as IHasBuilder <T>;
if (hasBuilder == null)
{
throw new NotSupportedException(
$"Unsupported query type {query.GetType()}. It must implement {typeof(IHasBuilder<T>)} interface");
}
using (var reader = ExecuteReader(query, hasBuilder.OwnerBuilder, transaction))
{
result = ResultSetReader.Read <T>(reader, m_locator).ToList();
}
transaction.Commit();
}
return(result);
}
public virtual (Result result, W model) Retrieve(IQueryModel <R, S, T, U, V, W> query, int maxdepth = 1, W model = default(W))
{
query.Status = "Loading...";
var retrieve = query.Domain.Retrieve(maxdepth, (model != null ? model.Domain : default(V)));
if (retrieve.result.Success && retrieve.domain != null)
{
var instance = Presentation.HelperInteractive <T, U, V, W> .CreateInstance(retrieve.domain, maxdepth);
Raise(instance, maxdepth);
instance.Status = string.Empty;
query.Status = string.Empty;
return(retrieve.result, model);
}
query.Status = retrieve.result.FilteredAsText("/", x => x.category == (x.category & ResultCategory.OnlyErrors));
return(retrieve.result, default(W));
}
/// <inheritdoc />
public ISettingsModel GetQuerySettings(IQueryModel query)
{
return this.querySettings.FirstOrDefault(p => p.EntityUrn == query.InternalUrn);
}
/// <inheritdoc />
public IEnumerable<ITaskModel> GetQuery(IQueryModel query, ISettingsModel projectSettings, ISettingsModel querySettings)
{
return this.taskTracker.GetQuery(projectSettings.Values, querySettings.Values).Select(t => new TaskModel(t, query.Project));
}
/// <inheritdoc />
public void SaveQuery(IQueryModel query, ISettingsModel settings)
{
if (query.InternalUrn != settings.EntityUrn)
{
throw new ArgumentException("Settings model does not belong to the supplied query");
}
this.querySettings.RemoveAll(qs => qs.EntityUrn == settings.EntityUrn);
this.querySettings.Add(settings);
this.SaveQuery(query);
}
public ISearchResults <object> Search(IQueryModel model)
{
return(SearchProducts(model));
}
/// <inheritdoc />
public void SaveQuery(IQueryModel query)
{
this.queries.RemoveAll(q => q.InternalUrn == query.InternalUrn);
this.queries.Add(query);
this.SaveSettings();
}
