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);
}
