private static CypherResult ExecuteCypherRecommendation(string query, ContentTypeDistribution distribution, AggregateFilter filters, AlgoContext context, out string requestString) { try { Log.Debug("ExecuteRecommendationQuery", "Building query from parameters", query); string filteredQuery = BuildCypherRecommendationQuery(query, filters, distribution); Log.Debug("ExecuteRecommendationQuery", "Query built from parameters", filteredQuery); Neo4jContext client = new Neo4jContext(false); Dictionary <string, string> parameters = new Dictionary <string, string>(); foreach (RecommendationContext item in context.Context.Keys) { parameters[item.ToString()] = context.Context[item]; } string response = client.GetWithCypherQuery(filteredQuery, parameters, out requestString); return(JsonConvert.DeserializeObject <CypherResult>(response)); } catch (Exception e) { Log.Error("ExecuteRecommendationQuery", "Error executing query", e); throw; } }
public static CypherResult ExecuteGremlinRecommendation(string query, ContentTypeDistribution distribution, AggregateFilter filters, AlgoContext context, out string requestString) { try { string filteredQuery = BuildGremlinQuery(query, filters, distribution); Log.Debug("ExecuteGremlinRecommendation", "Query built from parameters"); Neo4jContext client = new Neo4jContext(false); Dictionary <string, object> parameters = new Dictionary <string, object>(); foreach (RecommendationContext item in context.Context.Keys) { parameters["pMap" + item.ToString()] = context.Context[item]; } Script gremlinScript = new Script { ScriptStr = filteredQuery, ParameterMap = parameters }; string response = client.ExecuteGremlinScript(gremlinScript, out requestString); if (!string.IsNullOrEmpty(response)) { return(JsonConvert.DeserializeObject <CypherResult>(response)); } else { return(null); } } catch (Exception e) { Log.Error("ExecuteRecommendationQuery", "Error executing query", e); throw; } }
public static string BuildGremlinQuery(string query, AggregateFilter filters, ContentTypeDistribution distribution) { IEnumerable <string> distribFieldNames = Enumerable.Empty <string>(); if (distribution != null && distribution.Quotas != null && distribution.Quotas.Count > 0) { distribFieldNames = distribution.GetFieldNames(); } List <string> queryParts = new List <string>(); int distribCount = distribFieldNames.Count(); int cursor = 0; foreach (GremlinFormatKey formatKey in Enum.GetValues(typeof(GremlinFormatKey))) { string formatKeyStr = "{" + formatKey.ToString() + "}"; int formatKeyPos = query.IndexOf(formatKeyStr, cursor); if (formatKeyPos >= 0) { queryParts.Add(query.Substring(cursor, formatKeyPos - cursor)); switch (formatKey) { case GremlinFormatKey.ContentTypeFields: if (distribCount > 0) { string columnsName = "," + string.Join(", ", distribFieldNames.Select(f => "'" + f + "'")); queryParts.Add(columnsName); Log.Debug("BuildGremlinQuery", "Name of the columns added in the CypherResult", columnsName); } break; case GremlinFormatKey.Filter: if (filters != null && filters.Filters.Count > 0) { string gremlinCondition = filters.ToGremlinConditions(); queryParts.Add(gremlinCondition); Log.Debug("BuildGremlinQuery", "The if statement created for filtering the results", gremlinCondition); } break; case GremlinFormatKey.FilterEnd: if (filters != null && filters.Filters.Count > 0) { queryParts.Add("\n}\n"); } break; case GremlinFormatKey.ContentTypeFilling: if (distribCount > 0) { string fillingValues = string.Join("\n", distribFieldNames.Select(f => "d" + f + " = endNode.getProperty('" + f + "', null);")); queryParts.Add(fillingValues); Log.Debug("BuildGremlinQuery", "The lines used to gather node data used by distribution", fillingValues); } break; case GremlinFormatKey.ContentTypeKey: if (distribCount > 0) { string mapKey = "+';'+" + string.Join("+';'+", distribFieldNames.Select(f => "d" + f)); queryParts.Add(mapKey); Log.Debug("BuildGremlinQuery", "The key added to the HashMap to return all the values needed", mapKey); } break; } cursor = formatKeyPos + formatKeyStr.Length; } } queryParts.Add(query.Substring(cursor)); return(string.Concat(queryParts)); }
public static CypherResult ExecuteRecommendationQuery(GraphAction graphAction, ContentTypeDistribution distribution, AggregateFilter filters, AlgoContext context, out string requestString) { try { if (graphAction.Query != null) { return(ExecuteCypherRecommendation(graphAction.Query.QueryContent, distribution, filters, context, out requestString)); } else if (graphAction.Script != null) { return(ExecuteGremlinRecommendation(graphAction.Script.Script, distribution, filters, context, out requestString)); } else { Log.Error("ExecuteRecommendationQuery", "This graph actions defines neither a Cypher query, nor a Gremlin query", graphAction.Id); requestString = null; return(null); } } catch (Exception e) { Log.Error("ExecuteRecommendationQuery", "Error executing query", e); throw; } }
private static string BuildCypherRecommendationQuery(string query, AggregateFilter filters, ContentTypeDistribution distribution) { string where = string.Empty; if (filters != null) { where = filters.ToConditions(); } Log.Debug("BuildRecommendationQuery", "where", where); var filteredQueryText = query.Replace("\r\n", " ").Replace("\n", " "); if (!string.IsNullOrEmpty(where) && filteredQueryText.Contains(AggregateFilter.Where)) { Log.Debug("BuildRecommendationQuery", "Query before where", filteredQueryText); int wherePos = filteredQueryText.LastIndexOf(AggregateFilter.Where); string left = filteredQueryText.Substring(0, wherePos); Log.Debug("BuildRecommendationQuery", "left", left); string right = filteredQueryText.Substring(wherePos + 6); Log.Debug("BuildRecommendationQuery", "right", right); filteredQueryText = left + AggregateFilter.Where + where + " " + AggregateFilter.And + right; Log.Debug("BuildRecommendationQuery", "Query with where clause", filteredQueryText); } // Added the distribution configured field in the queries if (distribution != null && distribution.Quotas != null && distribution.Quotas.Count > 0) { Log.Debug("BuildRecommendationQuery", "Query before replace for quota", filteredQueryText); filteredQueryText = filteredQueryText.Replace("RETURN", string.Format("RETURN {0},", string.Join(",", distribution.GetFieldNames().Select(f => string.Format("c.{0}? as {0}", f))))); } Log.Debug("BuildRecommendationQuery", "Query generated", filteredQueryText); return(filteredQueryText); }
public static CommonConfiguration GetRecommendationConfiguration() { SqlServerContext _client = new SqlServerContext(Mars_Database_Key); CommonConfiguration result = new CommonConfiguration(); try { DbCommand command = _client.GetStoredProcCommand("ps_Config_GetCommonConfig"); using (IDataReader reader = _client.ExecuteReader(command)) { #region Config_WebSite while (reader.Read()) { int webSiteId = SqlDataHelper.GetIntValue("WebSiteId", reader); result.WebSitesValues.Add((WebSites)webSiteId, new WebSite { WebSiteId = (WebSites)webSiteId, Name = SqlDataHelper.GetStringValue("Name", reader), SelectionMethodId = (SelectionMethods)SqlDataHelper.GetIntValue("SelectionMethodId", reader), StrongestInteractionName = SqlDataHelper.GetStringValue("StrongestInteractionName", reader), DownloadFriendsLikes = SqlDataHelper.GetBoolValue("DownloadFriendsLikes", reader) }); } #endregion #region WebSiteAlgorithms reader.NextResult(); while (reader.Read()) { WebSites webSiteId = (WebSites)SqlDataHelper.GetIntValue("WebSiteId", reader); int algorithmId = SqlDataHelper.GetIntValue("AlgorithmId", reader); result.DeclareAlgorithm(webSiteId, algorithmId); } reader.NextResult(); #endregion #region AlgorithmGraphActions while (reader.Read()) { int algorithmId = SqlDataHelper.GetIntValue("AlgorithmId", reader); int graphActionId = SqlDataHelper.GetIntValue("GraphActionId", reader); result.DeclareGraphAction(algorithmId, graphActionId); } reader.NextResult(); #endregion #region AggregationFunctions List <AggregationFunction> aggregationFunctions = new List <AggregationFunction>(); while (reader.Read()) { AggregationFunction af = new AggregationFunction(); af.Id = SqlDataHelper.GetIntValue("Id", reader); af.Name = SqlDataHelper.GetStringValue("Name", reader); aggregationFunctions.Add(af); } reader.NextResult(); #endregion #region ScoreFunctions List <ScoreFunction> scoreFunctions = new List <ScoreFunction>(); while (reader.Read()) { ScoreFunction sf = new ScoreFunction(); sf.Id = SqlDataHelper.GetIntValue("Id", reader); sf.Name = SqlDataHelper.GetStringValue("Name", reader); scoreFunctions.Add(sf); } reader.NextResult(); #endregion #region ScoreFunctionParameters while (reader.Read()) { ScoreFunctionParameter sfp = new ScoreFunctionParameter(); sfp.Id = SqlDataHelper.GetIntValue("Id", reader); sfp.Parameter2 = SqlDataHelper.GetStringValue("Parameter2", reader); sfp.Parameter1 = SqlDataHelper.GetStringValue("Parameter1", reader); sfp.Value = SqlDataHelper.GetDoubleValue("Value", reader); sfp.WebSiteId = SqlDataHelper.GetIntValue("WebSiteId", reader); int graphActionId = SqlDataHelper.GetIntValue("GraphActionId", reader); int algorithmId = SqlDataHelper.GetIntValue("AlgorithmId", reader); WebSites webSiteId = (WebSites)SqlDataHelper.GetIntValue("WebSiteId", reader); result.AddScoreFunctionParameterToGraphAction(webSiteId, algorithmId, graphActionId, sfp); } reader.NextResult(); #endregion #region AggregationFunctionParameters while (reader.Read()) { AggregationFunctionParameter afp = new AggregationFunctionParameter(); afp.Id = SqlDataHelper.GetIntValue("Id", reader); afp.Name = SqlDataHelper.GetStringValue("Name", reader); afp.Value = SqlDataHelper.GetDoubleValue("Value", reader); int algorithmId = SqlDataHelper.GetIntValue("AlgorithmId", reader); WebSites webSiteId = (WebSites)SqlDataHelper.GetIntValue("WebSiteId", reader); result.AddAggregationFunctionParameterToAlgorithm(webSiteId, algorithmId, afp); } reader.NextResult(); List <RecommendationQuery> queries = new List <RecommendationQuery>(); while (reader.Read()) { RecommendationQuery query = new RecommendationQuery(); query.Id = SqlDataHelper.GetIntValue("Id", reader); query.QueryContent = SqlDataHelper.GetStringValue("Query", reader); queries.Add(query); } reader.NextResult(); List <GremlinScript> scripts = new List <GremlinScript>(); while (reader.Read()) { GremlinScript script = new GremlinScript(); script.Id = SqlDataHelper.GetIntValue("Id", reader); script.Script = SqlDataHelper.GetStringValue("Script", reader); scripts.Add(script); } reader.NextResult(); List <MirrorQuery> mirrorQueries = new List <MirrorQuery>(); while (reader.Read()) { MirrorQuery query = new MirrorQuery(); query.Id = SqlDataHelper.GetIntValue("Id", reader); query.QueryContent = SqlDataHelper.GetStringValue("MirrorQuery", reader); mirrorQueries.Add(query); } reader.NextResult(); List <MirrorScripts> mirrorScripts = new List <MirrorScripts>(); while (reader.Read()) { MirrorScripts script = new MirrorScripts(); script.Id = SqlDataHelper.GetIntValue("Id", reader); script.MirrorScript = SqlDataHelper.GetStringValue("MirrorScript", reader); mirrorScripts.Add(script); } reader.NextResult(); #endregion #region GraphActions while (reader.Read()) { int graphActionId = SqlDataHelper.GetIntValue("Id", reader); string name = SqlDataHelper.GetStringValue("Name", reader); int expiration = SqlDataHelper.GetIntValue("Expiration", reader); int?queryId = SqlDataHelper.GetNullableInt("QueryId", reader); int?scriptId = SqlDataHelper.GetNullableInt("GremlinScriptId", reader); int scoreFunctionId = SqlDataHelper.GetIntValue("ScoreFunctionId", reader); int?mirrorQueryId = SqlDataHelper.GetNullableInt("MirrorQueryId", reader); int?mirrorScriptId = SqlDataHelper.GetNullableInt("MirrorScriptId", reader); RecommendationQuery query = queries.Find(q => q.Id == queryId); GremlinScript script = scripts.Find(s => s.Id == scriptId); ScoreFunction scoreFunction = scoreFunctions.Find(sf => sf.Id == scoreFunctionId); MirrorQuery mirrorQuery = null; MirrorScripts mirrorScript = null; if (mirrorQueryId.HasValue) { mirrorQuery = mirrorQueries.Find(mq => mq.Id == mirrorQueryId.Value); } if (mirrorScriptId.HasValue) { mirrorScript = mirrorScripts.Find(ms => ms.Id == mirrorScriptId.Value); } foreach (GraphAction ga in result.GetGraphActions(graphActionId)) { ga.Name = name; ga.Expiration = expiration; ga.Query = query; ga.Script = script; ga.ScoreFunction = scoreFunction; ga.MirrorQuery = mirrorQuery; ga.MirrorScript = mirrorScript; } } reader.NextResult(); #endregion #region Algorithms while (reader.Read()) { int algorithmId = SqlDataHelper.GetIntValue("Id", reader); string name = SqlDataHelper.GetStringValue("Name", reader); bool isStandardQuery = SqlDataHelper.GetBoolValue("IsStandardQuery", reader); int aggregationFunctionId = SqlDataHelper.GetIntValue("AggregationFunctionId", reader); AggregationFunction af = aggregationFunctions.Find(a => a.Id == aggregationFunctionId); foreach (Algorithm algorithm in result.GetAlgorithms(algorithmId)) { algorithm.Name = name; algorithm.IsStandardQuery = isStandardQuery; algorithm.AggregationFunction = af; } } reader.NextResult(); #endregion #region ContentTypeDistribution while (reader.Read()) { ContentTypeDistribution distribution = new ContentTypeDistribution(); distribution.Id = SqlDataHelper.GetIntValue("Id", reader); distribution.Name = SqlDataHelper.GetStringValue("Name", reader); result.Distributions[distribution.Id] = distribution; } reader.NextResult(); #endregion #region ContentTypeQuota while (reader.Read()) { ContentTypeQuota quota = new ContentTypeQuota(); quota.Id = SqlDataHelper.GetIntValue("Id", reader); quota.Name = SqlDataHelper.GetStringValue("Name", reader); quota.Quota = SqlDataHelper.GetDoubleValue("Quota", reader); int distributionId = SqlDataHelper.GetIntValue("DistributionId", reader); result.AddQuotaToDistribution(distributionId, quota); } reader.NextResult(); #endregion #region ContentTypeValues while (reader.Read()) { ContentTypeValues value = new ContentTypeValues(); value.Id = SqlDataHelper.GetIntValue("Id", reader); value.Name = SqlDataHelper.GetStringValue("Name", reader); value.MongoName = SqlDataHelper.GetStringValue("MongoName", reader); value.IsString = SqlDataHelper.GetBoolValue("IsString", reader); value.Value = SqlDataHelper.GetStringValue("Value", reader); int quotaId = SqlDataHelper.GetIntValue("QuotaId", reader); result.AddValueToQuota(quotaId, value); } #endregion } } catch (Exception e) { Logger.Current.Error("GetRecommendationConfiguration", "Error loading configuration", e); } return(result); }